MySQL_练习

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()函数中