SQL22 统计各个部门的工资记录数
# 请你统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，
# 按照dept_no升序排序，以上例子输出如下:
SELECT
de.dept_no,
dept_name,
count( * ) as sum
FROM
departments de,
dept_emp mp,
salaries sa
WHERE
de.dept_no = mp.dept_no
AND mp.emp_no = sa.emp_no
GROUP BY
dept_no
ORDER BY
dept_no
SQL23 对所有员工的薪水按照salary降序进行1-N的排名
# 对所有员工的薪水按照salary降序先进行1-N的排名，如果salary相同，再按照emp_no升序排列：
# SQL23 对所有员工的薪水按照salary降序进行1-N的排名
# 窗口函数
SELECT
emp_no,
salary,
DENSE_RANK ( ) OVER ( ORDER BY salary DESC ) t_rank
FROM
salaries
ORDER BY
t_rank,
emp_no
SQL24 获取所有非manager员工当前的薪水情况
SELECT
dept_no,
es.emp_no,
salary
FROM
employees es,
dept_emp mp,
salaries sa
WHERE
es.emp_no = mp.emp_no
AND es.emp_no = sa.emp_no
AND es.emp_no NOT IN ( SELECT DISTINCT emp_no FROM dept_manager )
SQL25 获取员工其当前的薪水 比其manager当前薪水还高的相关信息
#先查出员工的薪水
SELECT
mp.emp_no,
sa.salary,
dept_no
FROM
dept_emp mp,
salaries sa
WHERE
mp.emp_no = sa.emp_no
AND mp.emp_no NOT IN ( SELECT emp_no FROM dept_manager WHERE mp.dept_no = dept_no )
# 查出manager的薪水 一个员工对应一个部门
SELECT
mg.emp_no,
sa.salary,
dept_no
FROM
dept_manager mg ,
salaries sa
WHERE
mg.emp_no = sa.emp_no
#-----------------
SELECT
emp.emp_no,
manager.emp_no AS manager_no,
emp.salary AS emp_salary,
manager.salary AS manager_salary
FROM
(
SELECT
mp.emp_no,
sa.salary,
dept_no
FROM
dept_emp mp,
salaries sa
WHERE
mp.emp_no = sa.emp_no
AND mp.emp_no NOT IN ( SELECT emp_no FROM dept_manager WHERE mp.dept_no = dept_no )
) AS emp,
( SELECT mg.emp_no, sa.salary, dept_no FROM dept_manager mg, salaries sa WHERE mg.emp_no = sa.emp_no ) AS manager
WHERE
emp.dept_no = manager.dept_no
AND emp.salary > manager.salary
SQL26 汇总各个部门当前员工的title类型的分配数目
SELECT
de.dept_no,
dept_name,
title ,
te.count
FROM
departments de,
(
SELECT
count( * ) as count,
title,
emp.dept_no
FROM
dept_emp emp,
departments de,
titles te
WHERE
emp.emp_no = te.emp_no
AND de.dept_no = emp.dept_no
GROUP BY
emp.dept_no,
title
) te
WHERE
de.dept_no = te.dept_no
ORDER BY
de.dept_no ASC,
title ASC;
#SQL29 使用join查询方式找出没有分类的电影id以及名称
SELECT
m.film_id,
title
FROM
film m
LEFT JOIN film_category ca ON m.film_id = ca.film_id
WHERE ca.film_id is null;
SQL30 使用子查询的方式找出属于Action分类的所有电影对应的title,description
SELECT
title,
description
FROM
film m,
film_category ca
WHERE
m.film_id = ca.film_id
AND ca.category_id = ( SELECT category_id FROM category WHERE `name` = 'Action' )
将employees表的所有员工的last_name和first_name拼接起来作为Name
SELECT CONCAT(last_name,' ',first_name) FROM employees
SQL33 创建一个actor表，包含如下列信息
CREATE TABLE actor (
actor_id SMALLINT ( 5 ) PRIMARY KEY COMMENT '主键id',
first_name VARCHAR ( 45 ) NOT NULL COMMENT '名字',
last_name VARCHAR ( 45 ) NOT NULL COMMENT '姓氏',
last_update date NOT NULL COMMENT '日期'
);
批量插入数据
INSERT INTO actor
VALUES
( 1, 'PENELOPE', 'GUINESS', STR_TO_DATE( '2006-02-15 12:34:33', '%Y-%m-%d %H:%i:%s' ) ),
( 2, 'NICK', 'WAHLBERG', STR_TO_DATE( '2006-02-15 12:34:33', '%Y-%m-%d %H:%i:%s' ) )
mysql中常用的三种插入数据的语句:
# insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
# replace into表示插入替换数据，需求表中有PrimaryKey，
# 或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
# insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");
sql36 请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.
# actor_name表结构如下，题目最后会查询actor_name表里面的数据来对比结果输出：
CREATE TABLE actor_name (
first_name VARCHAR(45) not null,
last_name VARCHAR(45) NOT NULL
);
INSERT INTO actor_name
SELECT first_name,last_name FROM actor
SELECT * FROM actor_name
SQL37 对first_name创建唯一索引uniq_idx_firstname
DROP TABLE IF EXISTS actor
CREATE TABLE actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update datetime NOT NULL);
#表级约束 唯一索引
ALTER TABLE actor ADD CONSTRAINT uniq_idx_firstname UNIQUE( first_name) ;
# 对last_name创建普通索引idx_lastname
ALTER TABLE actor ADD INDEX idx_lastname (last_name) USING BTREE;
窗口函数
1、窗口函数组内排序的三种方法：
① RANK()，对应 1, 2, 2, 4
② ROW_NUMBER()，对应 1, 2, 3, 4
③ DENSE_RANK，对应 1, 2, 2, 3
2、排序的两种方法：
① 全局排序：ORDER BY
② 分组排序：ORDER BY 置于 OVER()函数中
|
