【尚硅谷MySQL-李玉婷-练习】

01-基本SQL-SELECT 语句

1. 下面的语句是否可以执行成功 
 select last_name , job_id , salary as sal from employees;   
【成功】


2. 下面的语句是否可以执行成功   select  *  from employees;    
【成功】

3. 找出下面语句中的错误  
select employee_id , last_name,salary * 12  “ANNUAL  SALARY” from employees; 
【last_name后的逗号是中文符的逗号，错误；别名的双引号也是中文符的，也是错误的】

4. 显示表 departments 的结构，并查询其中的全部数据 
【DESC departments ; SELECT * FROM departments;】

5. 显示出表 employees 中的全部 job_id（不能重复） 
【SELECT DISTINCT job_id FROM employees;】

6. 显示出表 employees 的全部列，各个列之间用逗号连接，列头显示成 OUT_PUT 
【SELECT CONCAT(employee_id,",",first_name,"-",last_name,",",email,",",phone_number,",",job_id,",",salary,",",IFNULL(commission_pct,0),",",IFNULL(manager_id,0),",",department_id,",",hiredate) OUT_PUT 
FROM employees;】

02-过滤数据

1. 查询工资大于 12000 的员工姓名和工资 
SELECT first_name,last_name,salary FROM employees;
2. 查询员工号为 176 的员工的姓名和部门号和年薪 
SELECT first_name,last_name,department_id,salary*12 annul FROM employees WHERE employee_id=176;

3. 选择工资不在 5000 到 12000 的员工的姓名和工资
SELECT first_name,last_name,salary FROM employees WHERE salary NOT BETWEEN 5000 AND 12000; 
 
4. 选择在 20 或 50 号部门工作的员工姓名和部门号 
SELECT first_name,last_name,department_id FROM employees WHERE department_id IN (20,50);

5. 选择公司中没有管理者的员工姓名及 job_id 
SELECT first_name,last_name,job_id FROM employees WHERE manager_id IS NULL;

6. 选择公司中有奖金的员工姓名，工资和奖金级别 
SELECT e.first_name,e.last_name,e.salary ,jg.grade_level FROM employees e
INNER JOIN job_grades jg ON e.salary BETWEEN jg.lowest_sal AND jg.highest_sal;

7. 选择员工姓名的第三个字母是 a 的员工姓名 
SELECT first_name ,last_name FROM employees WHERE first_name LIKE '__a%';

8. 选择姓名中有字母 a 和 e 的员工姓名 
SELECT first_name,last_name FROM employees WHERE first_name LIKE '%a%' OR '%e%';

9. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息 
SELECT * FROM employees WHERE first_name LIKE '%e';

10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位 
SELECT first_name,last_name,job_id ,department_id FROM employees WHERE department_id BETWEEN 80 AND 100;

11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位 
SELECT first_name,last_name,job_id FROM employees e WHERE manager_id IN (100,101,110);

03-排序数据

1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序 
SELECT first_name,last_name ,department_id , salary*12 annul FROM employees ORDER BY annul DESC,first_name ASC;

2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
SELECT first_name,last_name , salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC;
 
3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT * FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC,department_id ASC;

04-单行函数

1、显示系统时间(注：日期+时间) 
 SELECT NOW() FROM DUAL;
 

2、查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary） 
SELECT employee_id,first_name,last_name,salary ,salary * (1+0.2) bonus FROM employees;

3、 将员工的姓名按首字母排序，并写出姓名的长度（length） 
SELECT employee_id,first_name,LENGTH(first_name) FROM employees ORDER BY first_name ASC;

4、做一个查询，产生下面的结果
/* 提示：<last_name> earns <salary> monthly but wants <salary*3> 
    效果：	
				Dream Salary 
	King earns 24000 monthly but wants 72000 
*/
SELECT CONCAT(last_name," earns ",salary," monthly but wants ",salary*3) "Dream Salary" FROM employees;

5、使用case-when ，按照下面的条件：
/*
	job                  grade 
	AD_PRES            	A 
	ST_MAN            	B 
	IT_PROG             C 
	SA_REP              D 
	ST_CLERK           	E 
	
产生下面的结果：
	Last_name 	Job_id		Grade 
	king 		AD_PRES		 A 
*/
SELECT last_name,job_id,
	CASE job_id
		WHEN 'AD_PRES' THEN 'A'
		WHEN 'ST_MAN ' THEN 'B'
		WHEN 'IT_PROG' THEN 'C'
		WHEN 'SA_REP ' THEN 'D'
		WHEN 'ST_CLERK' THEN 'E'
	ELSE JOB_ID END 'Grade'
FROM employees ;

05-分组函数

1. 查询各 job_id 的员工工资的最大值，最小值，平均值，总和，并按 job_id 升序 
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees GROUP BY job_id;

2. 查询员工最高工资和最低工资的差距（DIFFERENCE） 
SELECT MAX(salary),MIN(salary),MAX(salary)-MIN(salary) Difference FROM employees;

3. 查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员 工不计算在内 
SELECT MIN(salary) ,employee_id FROM employees GROUP BY manager_id HAVING MIN(salary) >= 6000 AND manager_id IS NOT NULL;

4. 查询所有部门的编号，员工数量和工资平均值,并按平均工资降序 
SELECT department_id,COUNT(*),AVG(salary) FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC;

5. 选择具有各个 job_id 的员工人数 
SELECT job_id, COUNT(*) FROM employees GROUP BY job_id;

6. 查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE） 
SELECT MAX(hiredate),MIN(hiredate),(MAX(hiredate)-MIN(hiredate))/43200 Difference FROM employees;

7. 查询部门编号为 90 的员工个数 
SELECT department_id,COUNT(*) FROM employees GROUP BY department_id HAVING department_id = 90;

07-SQL99语法连接查询

一、查询编号>3 的女神的男朋友信息，如果有则列出详细，如果没有，用 NULL 填充 
SELECT g.id,g.name,b.* 
FROM beauty g
LEFT OUTER JOIN boys b
ON g.boyfriend_id = b.id
WHERE g.id > 3;

二、查询哪个城市没有部门 
SELECT l.`location_id`,l.`city`,d.`department_id`,d.`location_id` 
FROM locations l
LEFT OUTER JOIN departments d 
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL;

三、查询部门名为 SAL 或 IT 的员工信息 
SELECT e.*,d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name IN ('SAL','IT');

08-SQL92语法连接查询

1. 显示所有员工的姓名，部门号和部门名称。 
SELECT e.first_name,e.department_id,d.department_name 
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

2. 查询 90 号部门员工的 job_id 和 90 号部门的 location_id 
SELECT d.department_id,e.job_id,d.location_id
FROM departments d
INNER JOIN employees e
ON d.department_id = e.department_id
WHERE d.department_id = 90;

3. 选择所有有奖金的员工的 last_name , department_name , location_id , city 
SELECT e.last_name,e.`commission_pct`,d.department_name,d.location_id,l.city
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id 
INNER JOIN locations l
ON d.location_id = l.location_id
WHERE e.`commission_pct` IS NOT NULL;

4. 选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name  
 SELECT e.last_name,e.job_id,e.department_id,d.department_name,l.city
 FROM employees e
 INNER JOIN departments d
 ON e.department_id  = d.department_id
 INNER JOIN locations l
 ON d.location_id = l.location_id 
 WHERE l.city = 'Toronto';


5.查询每个工种、每个部门的部门名、工种名和最低工资 
SELECT d.department_name,e.job_id,MIN(salary)
 FROM employees e
 INNER JOIN departments d
 ON e.`department_id` = d.`department_id`
 INNER JOIN jobs j
 ON e.`job_id` = j.`job_id` 
 GROUP BY d.department_id,e.job_id ;

6.查询每个国家下的部门个数大于 2 的国家编号 
SELECT * FROM city;
SELECT * FROM country;
SELECT * FROM countrylanguage;

7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的
	格 式 employees  Emp#  manager   Mgr# 
	       kochhar   101   king     100 
SELECT e.last_name,e.employee_id,m.last_name,m.employee_id
FROM employees e
LEFT OUTER JOIN employees m
ON e.manager_id = m.employee_id;

09-子查询

1. 查询和 Zlotkey 相同部门的员工姓名和工资 
SELECT e.last_name,e.salary 
FROM employees e
WHERE department_id = (
	SELECT department_id
	FROM employees 
	WHERE last_name = 'Zlotkey'
	) 
AND last_name <> 'Zlotkey';

2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。 
SELECT e.employee_id,e.last_name,e.salary
FROM employees e
WHERE salary > (
	SELECT AVG(salary)
	FROM employees 
	);
	
3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资 
SELECT e.employee_id,e.last_name,e.salary 
FROM employees e 
INNER JOIN 
	(
	SELECT department_id,AVG(salary) avg_sal
	FROM employees 
	GROUP BY department_id
	) dep_sal
ON e.department_id  = dep_sal.department_id
WHERE e.salary > dep_sal.avg_sal
ORDER BY employee_id DESC;

SELECT e1.employee_id,e1.last_name,e1.salary
FROM employees e1
WHERE salary > (
	SELECT AVG(salary)
	FROM employees e2
	WHERE e1.`department_id` = e2.`department_id`
	GROUP BY department_id
	)
	ORDER BY employee_id DESC;
4. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名 
SELECT employee_id,last_name 
FROM employees 
WHERE department_id IN (
	SELECT department_id 
	FROM employees 
	WHERE last_name LIKE '%u%'
	);

5.  查询在部门的 location_id 为 1700 的部门工作的员工的员工号 
SELECT e.employee_id 
FROM employees e
WHERE department_id IN (
	SELECT department_id 
	FROM departments 
	WHERE location_id = 1700
	);

6.  查询管理者是 K_ing 的员工姓名和工资 
SELECT last_name,salary
FROM employees
WHERE manager_id = (
	SELECT employee_id
	FROM employees 
	WHERE last_name = 'K_ing' AND manager_id IS NULL
	);
	
7.  查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为 姓.名 
SELECT CONCAT(first_name,last_name) AS "姓名"
FROM employees 
WHERE salary = (
	SELECT MAX(salary)
	FROM employees
	);

10-数据处理

CREATE DATABASE IF NOT EXISTS homeWork;
1. 运行以下脚本创建表 my_employees 
CREATE TABLE my_employees(  
	Id INT(10),  
	First_name VARCHAR(10),  
	Last_name VARCHAR(10),  
	Userid VARCHAR(10),  
	Salary DOUBLE(10,2) 
	) ;
	
CREATE TABLE users(  
	id INT,  
	userid VARCHAR(10),  
	department_id INT  
) ;

#2. 显示表 my_employees 的结构 
DESC my_employees;

3. 向 my_employees 表中插入下列数据 
ID FIRST_NAME LAST_NAME USERID SALARY 
1 patel Ralph Rpatel 895 
2 Dancs Betty Bdancs 860 
3 Biri Ben Bbiri 1100 
4 Newman Chad Cnewman 750 
5 Ropeburn Audrey Aropebur 1550      

INSERT INTO my_employees VALUES
(1,'patel','Ralph', 'Rpatel', 895),
(2,'Dancs','Betty', 'Bdancs', 860),
(3,'Biri','Ben', 'Bbiri', 1100),
(4,'Newman','Chad', 'Cnewman', 860),
(5,'Ropeburn','Audrey', 'Aropebur', 1550);

4.  向 users 表中插入数据 
 1 Rpatel 10 
 2,'Bdancs', 10 
 3, 'Bbiri' ,20 
 4, 'Cnewman' ,30   
 5, 'Aropebur', 40
 
 INSERT INTO users VALUES
(1,'Rpatel', 10),
(2,'Bdancs', 10),
(3,'Bbiri' , 20), 
(4,'Cnewman',30),  
(5,'Aropebur',40);
			  
5. 将 3 号员工的 last_name 修改为“drelxer” 
UPDATE my_employees SET last_name="drelxer" WHERE id = 3;

6. 将所有工资少于 900 的员工的工资修改为 1000 
UPDATE my_employees SET salary = 1000 WHERE salary < 900;

7. 将 userid 为 Bbiri 的 USER 表和 my_employees 表的记录全部删除 
DELETE FROM users WHERE userid = 'Bbiri';
DELETE FROM my_employees WHERE userid = 'Bbiri';

8. 删除所有数据
DELETE FROM users;
DELETE FROM my_employees;

9. 检查所作的修正 
SELECT * FROM users;
SELECT * FROM my_employees;

10. 清空表 my_employees 
DELETE FROM my_employees;

11-创建和管理表

1. 创建表 dept1 
NAME NULL? TYPE 
id         INT(7) 
NAME       VARCHAR(25) 
 
CREATE TABLE dept1(
	id INT(7),
	`name` VARCHAR(25)
	);
 
2. 将表 departments 中的数据插入新表 dept2 中 
CREATE TABLE dept2 AS SELECT * FROM departments;

3. 创建表 emp5 
NAME    NULL?     TYPE 
id                INT(7) 
First_name        VARCHAR (25) 
Last_name         VARCHAR(25) 
Dept_id           INT(7) 

CREATE TABLE emp5(
	id INT(7),
	First_name VARCHAR (25), 
	Last_name VARCHAR(25), 
	Dept_id INT(7)
	); 
	
4. 将列 Last_name 的长度增加到 50 
ALTER TABLE emp5 MODIFY last_name VARCHAR(50);

5. 根据表 employees 创建 employees2 
CREATE TABLE employees2 AS SELECT * FROM employees WHERE 1=2;
DESC employees2;

6. 删除表 emp5 
DROP TABLE emp5;

7. 将表 employees2 重命名为 emp5 
RENAME TABLE employees2 TO emp5; 
ALTER TABLE employees2 RENAME TO emp5;

8 在表 dept1 和 emp5 中添加新列 test_column，并检查所作的操作 
ALTER TABLE dept1 ADD COLUMN test_column VARCHAR(32);
ALTER TABLE emp5 ADD COLUMN test_column VARCHAR(32);

9.直接删除表 emp5 中的列 department_id 
ALTER TABLE emp5 DROP COLUMN department_id;

12-约束

1. 向表 emp5 的 id 列中添加 PRIMARY KEY 约束（my_emp_id_pk） 
ALTER TABLE emp5 MODIFY employee_id INT(7) PRIMARY KEY; 
ALTER TABLE emp5 ADD CONSTRAINT 'my_emp_id_pk' PRIMARY KEY(employee_id);
 
2. 向表 dept2 的 id 列中添加 PRIMARY KEY 约束（my_dept_id_pk） 
 ALTER TABLE dept2 ADD CONSTRAINT 'my_dept_id_pk' PRIMARY KEY(department_id);
 ALTER TABLE dept2 ADD PRIMARY KEY(department_id);
 
3. 向表 emp5 中添加列 dept_id，并在其中定义 FOREIGN KEY 约束，与之相关联的列是 dept2 表中的 id 列
DESC emp5;
ALTER TABLE emp5 ADD COLUMN dept_id INT(7);
ALTER TABLE emp5 ADD FOREIGN KEY(dept_id) REFERENCES dept2(department_id);

13-事务

1.创建一个表，里面有 id 为主键，stuname 唯一键，seat 座位号，要求将 id 设置成自增 
CREATE TABLE IF NOT EXISTS students(
	id INT PRIMARY KEY AUTO_INCREMENT,
	stuname VARCHAR(32) UNIQUE,
	seat INT
	);

2.要求用事务的方式插入 3 行数据 
SET autocommit = 0;
START TRANSACTION;
INSERT INTO students VALUES
(1,'张三',1),
(2,'李四',2),
(3,'王麻子',3);
SAVEPOINT insert_1;
COMMIT;
SELECT * FROM students;
ROLLBACK TO insert_1;

3.要求用事务的方式删除数据，并回滚
SET autocommit=0;
START TRANSACTION;
SAVEPOINT save1;
DELETE FROM students;
SELECT * FROM students;
SAVEPOINT save2;
ROLLBACK TO save1;
SELECT * FROM students;