MySQL基础篇（day05，复习自用）

最新推荐文章于 2024-08-02 23:39:06 发布

今天你写代码了吗？2333

最新推荐文章于 2024-08-02 23:39:06 发布

阅读量286

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Singularity_233/article/details/131494437

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

MySQL第五天

子查询
- 内容
- 练习
创建和管理表
- 内容
- 练习

子查询

内容

#第09章_子查询

#1.由一个具体的需求，引入子查询
#需求：谁的工资比Abel的高
#方式1：
SELECT last_name,salary
FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary
FROM employees
WHERE salary > 11000;

#方式2:自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e2.salary > e1.salary #多表的连接条件
AND e1.last_name = 'Abel'

#方式3：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE last_name = 'Abel'
		);
		
#2.称谓的规范：外查询（或主查询）、内查询(或子查询)
/*
子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用 。
注意事项
子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行操作符对应多行子查询

*/

/*
3.子查询的分类
角度1：从内查询返回的结果的条目数
单行子查询 vs 多行子查询

角度2：内查询是否被执行多次
	相关子查询 vs 不相关子查询
比如：相关子查询的需求：查询工资大于本部门平均工资的员工信息。
      不相关子查询的需求：查询工资大于本公司平均工资的员工信息。
*/

#4.单行子查询
#子查询的编写技巧（步骤）：①从里往外写；②从外往里写

#4.1单行操作符: = <> > >= < <=

#题目：查询工资大于149号员工工资的员工信息

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE employee_id = 149
	       );
	       
#题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
		SELECT job_id
		FROM employees
		WHERE employee_id = 141
		)
AND salary >(
		SELECT salary
		FROM employees
		WHERE employee_id = 143 
		);

#题目：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
		);
		
#题目：查询与141号员工的manager_id和department_id相同的其他员工的employee_id，
#manager_id，department_id
#方式1：
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id =(
		   SELECT manager_id
		   FROM employees
		   WHERE employee_id = 141
		   )
AND department_id = (
		   SELECT department_id
		   FROM employees
		   WHERE employee_id = 141
		    )
AND employee_id <> 141;

#方式2：了解
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id) = (
				SELECT manager_id,department_id
				FROM employees
				WHERE employee_id = 141
				)
AND employee_id <> 141;

#题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
			SELECT MIN(salary)
			FROM employees
			WHERE department_id = 50
			);
			
#题目：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800
#的department_id相同，则location为’Canada’，其余则为’USA’。
SELECT employee_id,last_name,CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada'
						ELSE 'USA' END 'location'
FROM employees

#4.2子查询中的控制问题
SELECT last_name, job_id
FROM employees
WHERE job_id =
		(SELECT job_id
		FROM employees
		WHERE last_name = 'Haas');

#4.3非法使用子查询
#错误情况：Subquery returns more than 1 row
SELECT employee_id, last_name
FROM employees
WHERE salary =
		(SELECT MIN(salary)
		FROM employees
		GROUP BY department_id);


#5.多行子查询
#5.1多行子查询的操作符：IN ANY ALL SOME(同ANY)

#5.2举例：
# IN：
SELECT employee_id, last_name
FROM employees
WHERE salary IN
		(SELECT MIN(salary)
		FROM employees
		GROUP BY department_id);

#ANY / ALL：
#题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG' 
AND salary < ANY(
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG'
		);
		
#题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG' 
AND salary < ALL(
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG'
		);
		
#题目：查询平均工资最低的部门id
#MySQL中聚合函数是不能嵌套使用的。如：MIN(AVG(sal))
#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
			SELECT MIN(avg_sal)
			FROM(
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
				) t_dept_avg_sal

			);

#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
			SELECT AVG(salary)
			FROM employees
			GROUP BY department_id
			)

#5.3 空值问题
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
			SELECT manager_id
			FROM employees
			#where manager_id is not null
			);
			
#6.相关子查询
#回顾：查询员工中工资大于本公司平均工资的员工的last_name,salary和其department_id
#6.1
SELECT last_name,salary,department_id
FROM employees
WHERE salary > (
		SELECT AVG(salary)
		FROM employees
		);


#题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#方式1：使用相关子查询
SELECT last_name,salary,department_id
FROM employees e
WHERE salary > (
		SELECT AVG(salary)
		FROM employees
		WHERE department_id = e.department_id
		);

#方式2：在FROM中声明子查询
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal

#题目：查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
	SELECT department_name
	FROM departments d
	WHERE d.department_id = e.department_id
	) ASC;

#结论：在SELECT中，除了GROUP BY和LIMIT之外，其他位置都可以声明子查询
/*
SELECT ....,....,....(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件 
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....
*/

#题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同
#id的员工的employee_id,last_name和其job_id
SELECT * FROM job_history;

SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (
		SELECT COUNT(*)
		FROM job_history j
		WHERE e.employee_id = j.employee_id
		);
		
#6.2 EXITS 与 NOT EXISTS关键字

#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息
#方式1：自连接
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id

#方式2：子查询
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id  IN (
			SELECT manager_id 
			FROM employees
			);
			
#方式3：使用EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS(
		SELECT *
		FROM employees e2
		WHERE e1.employee_id = e2.manager_id
		);

#题目：查询departments表中，不存在于employees表中的部门的department_id和department_name

#方式1：
SELECT d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

#方式2：
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS(
		SELECT *
		FROM employees e
		WHERE e.department_id = d.department_id
		);

练习

#倒数五六题要认真看

#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE department_id IN (#用IN或者=号都行
			SELECT department_id
			FROM employees
			WHERE last_name = 'Zlotkey'
			);

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
		SELECT AVG(salary)
		FROM employees
		);

#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary > ALL(
		SELECT salary
		FROM employees
		WHERE job_id = 'SA_MAN'
		);

#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
#不会
SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id IN (
			SELECT DISTINCT department_id
			FROM employees
			WHERE last_name LIKE '%u%'
			);


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

#6.查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
			SELECT employee_id
			FROM employees
			WHERE last_name = 'King'
			);

#7.查询工资最低的员工信息: last_name, salary
SELECT last_name,salary
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
		);

#8.查询平均工资最低的部门信息
#我的做法：
#方式2：
SELECT d.department_id,d.department_name
FROM departments d JOIN employees e
ON d.department_id = e.department_id
GROUP BY e.department_id
HAVING AVG(salary) <= ALL(
			SELECT AVG(salary)
			FROM employees
			GROUP BY department_id
			)

#康师傅的做法：
#方式1：
SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) = (
						SELECT MIN(avg_sal)
						FROM (
							SELECT AVG(salary) avg_sal
							FROM employees
							GROUP BY department_id
							) t_dept_avg_sal
						)
			);

#方式3：LIMIT

SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) = (
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
						ORDER BY avg_sal ASC
						LIMIT 1
						));
						
#方式4：
SELECT *
FROM departments d,(
			SELECT department_id,AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id
			ORDER BY avg_sal ASC
			LIMIT 1
			) t_dept_avg_sal
WHERE d.department_id = t_dept_avg_sal.department_id


#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
SELECT d.department_id,d.department_name,AVG(salary)
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY e.department_id
HAVING AVG(salary) <= ALL(
			SELECT AVG(salary)
			FROM employees
			GROUP BY department_id
			)  

#康师傅做法：
#方式1：
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) = (
						SELECT MIN(avg_sal)
						FROM (
							SELECT AVG(salary) avg_sal
							FROM employees
							GROUP BY department_id
							) t_dept_avg_sal
						)
			);

#10.查询平均工资最高的 job 信息
#方式1：
SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) = (
					SELECT MAX(avg_sal)
					FROM(
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY job_id
						) t_job_avg_sal
					)
		);

#方式2：
#我的做法：
SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id 
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) >= ALL(
					SELECT AVG(salary)
					FROM employees
					GROUP BY job_id
					)
		);
		
#方式3：
SELECT j.*
FROM jobs j,(
		SELECT job_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY job_id
		ORDER BY avg_sal DESC
		LIMIT 1
		) t_job_avg_sal
WHERE j.job_id = t_job_avg_sal.job_id


#11.查询平均工资高于公司平均工资的部门有哪些?
SELECT d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_id
HAVING AVG(salary) >  (
			SELECT AVG(salary)
			FROM employees
			);

#12.查询出公司中所有 manager 的详细信息
#方式1：自连接
SELECT DISTINCT e1.employee_id,e1.last_name,e1.salary
FROM employees e1 JOIN employees e2
ON e1.employee_id = e2.manager_id;

#方式2：子查询
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id  IN (
			SELECT manager_id 
			FROM employees
			);
			
#方式3：使用EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS(
		SELECT *
		FROM employees e2
		WHERE e1.employee_id = e2.manager_id
		);

#13.各个部门中 （在）最高工资（排序）中最低的那个部门的 最低工资是多少?
#没看懂
#我的做法：
SELECT *
FROM employees
WHERE salary <= ALL(
			SELECT salary
			FROM employees
			WHERE department_id = (
						SELECT department_id
						FROM employees
						GROUP BY department_id
						ORDER BY MAX(salary) ASC
						LIMIT 1
						)
			) 
AND department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			ORDER BY MAX(salary) ASC
			LIMIT 1
			)

#康师傅的做法：
#方式1：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) = (
						SELECT MIN(max_sal)
						FROM(
							SELECT MAX(salary) max_sal
							FROM employees
							GROUP BY department_id
							) t_dept_max_sal
						)
			
			)

#方式2：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) <= ALL(
						SELECT MAX(salary) 
						FROM employees
						GROUP BY department_id
						)
			)

#方式3：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) =(
						SELECT MAX(salary) max_sal
						FROM employees
						GROUP BY department_id
						ORDER BY max_sal ASC
						LIMIT 1
						)
			)
			
#方式4：
SELECT MIN(salary)
FROM employees e,(
		SELECT department_id,MAX(salary) max_sal
		FROM employees
		GROUP BY department_id
		ORDER BY max_sal ASC
		LIMIT 1
		)t_dept_max_sal
WHERE e.department_id = t_dept_max_sal.department_id


SELECT * FROM departments;
#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#不会
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
			SELECT manager_id
			FROM employees
			WHERE department_id = (
						SELECT department_id
						FROM employees
						GROUP BY department_id
						ORDER BY AVG(salary) DESC
						LIMIT 1
						)
			);
			
	
#另一种方法
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id IN(
			SELECT DISTINCT manager_id
			FROM employees e,(
					SELECT department_id,AVG(salary) avg_sal
					FROM employees
					GROUP BY department_id
					ORDER BY avg_sal DESC
					LIMIT 1
					) t_dept_avg_sal
			WHERE e.department_id = t_dept_avg_sal.department_id
			);	



#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
#方式1：
SELECT  department_id 
FROM departments
WHERE department_id NOT IN(
			SELECT DISTINCT department_id 
			FROM employees
			WHERE job_id = 'ST_CLERK'
			)

#方式2：
SELECT  department_id 
FROM departments d
WHERE NOT EXISTS(
		SELECT *
		FROM employees e
		WHERE d.department_id = e.department_id
		AND e.job_id = 'ST_CLERK'
		)



#16. 选择所有没有管理者的员工的last_name
SELECT last_name
FROM employees
WHERE manager_id IS NULL;


#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
SELECT employee_id,last_name,hire_date,salary
FROM employees 
WHERE manager_id IN (
			SELECT employee_id
			FROM employees
			WHERE last_name = 'De Haan'
			)


#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
#不会
#方式1：使用相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
		SELECT AVG(salary)
		FROM employees e2
		WHERE e1.department_id = e2.department_id
		);

#方式2：在FROM中声明子查询
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id
		)t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal


#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
#我的方法
SELECT department_name
FROM departments
WHERE department_id IN (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING COUNT(employee_id) >5
			)

#康师傅的方法：
SELECT department_name
FROM departments d
WHERE 5 < (
		SELECT COUNT(*)
		FROM employees e
		WHERE d.department_id = e.department_id
		);

#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
#不会

SELECT country_id
FROM locations l
WHERE 2 < (
		SELECT COUNT(*)
		FROM departments d
		WHERE l.location_id = d.location_id
		);
 
/*
如果子查询相对较简单，建议从外往里写
一旦子查询结构较复杂，则建议从里往外写

如果是相关子查询的话，通常是从外往里写

*/

创建和管理表

内容

#第10章_创建和管理表
 
#1. 创建和管理数据库
#1.1 如何创建数据库
#方式1：创建的此数据库使用的是默认的字符集
CREATE DATABASE mytest1;

#查看创建数据库的结构
SHOW CREATE DATABASE mytest1;


#方式2：显式地指明了要创建的数据库的字符集
CREATE DATABASE mytest2 CHARACTER SET "gbk";

#
SHOW CREATE DATABASE mytest2;

#方式3（推荐）：如果要创建的数据库已经存在，则创建不成功，但不会报错
CREATE DATABASE IF NOT EXISTS mytest2 CHARACTER SET 'utf8';
#如果要创建的数据库不存在，则创建成功
CREATE DATABASE IF NOT EXISTS mytest3 CHARACTER SET 'utf8';

SHOW DATABASES;

#1.2管理数据库
#查看当前连接中的数据库都有哪些
SHOW DATABASES;

#切换数据库
USE atguigudb;

#查看当前数据库中保存的数据表
SHOW TABLES;

#查看当前使用的数据库
SELECT DATABASE() FROM DUAL;

#查看指定数据库下保存的数据表
SHOW TABLES FROM mysql;

#1.3 修改数据库
#更改数据库的字符集
SHOW CREATE DATABASE mytest2;

ALTER DATABASE mytest2 CHARACTER SET "utf8";

#1.4 删除数据库
#方式1：
DROP DATABASE mytest1;

SHOW DATABASES;
#方式2：推荐。如果要删除的数据库存在，则删除成功。如果不存在，则默默结束，不会报错。
DROP DATABASE IF EXISTS mytest1;

DROP DATABASE IF EXISTS mytest2;

#2.如何创建数据表
USE atguigudb;

SHOW CREATE DATABASE atguigudb;#默认使用的是UTF8

SHOW TABLES;
#方式1：“白手起家”的方式
#需要用户具备创建表的权限
CREATE TABLE IF NOT EXISTS myemp1(
id INT,
emp_name VARCHAR(15),#使用VARCHAR来定义字符串，必须在使用VARCHAR时指明
hire_date DATE
); 

#查看表结构
DESC myemp1;
#查看创建表的语句结构
SHOW CREATE TABLE myemp1;#如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集。
#查看表数据
SELECT * FROM myemp1;

#方式2：基于现有的表,同时导入数据
CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;

DESC myemp2;

SELECT * 
FROM myemp2;

DROP TABLE myemp3;
#说明1：查询语句中字段的别名，可以作为新创建的表的字段的名称。
#说明2：此时的查询语句可以结构比较丰富，使用前面章节讲过的SELECT
CREATE TABLE myemp3
AS
SELECT e.employee_id emp_id,e.last_name lname,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

SELECT * 
FROM myemp3;

DESC myemp3;

#练习1：创建一个表employees_copy,实现对employees表的复制，包括表数据
CREATE TABLE employees_copy
AS
SELECT *
FROM employees

SELECT *
FROM employees_copy;

#练习2：创建一个表employees_blank,实现对employees表的复制，不包括表数据

CREATE TABLE employees_blank
AS
SELECT *
FROM employees
#where department_id > 10000;
WHERE 1 = 2;#山无棱，天地合，乃敢与君绝。

DROP TABLE employees_blank

SELECT * FROM employees_blank;

#3.修改表 --> ALTER TABLE
DESC myemp1;

# 3.1 添加一个字段
ALTER TABLE myemp1
ADD salary DOUBLE(10,2);#默认添加到表中的最后一个字段的位置

ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;

ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;

# 3.2 修改一个字段：数据类型、长度、默认值（略）
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25);

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';

SELECT *
FROM myemp1;

# 3.3 重命名一个字段
ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);

DESC myemp1

ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);

# 3.4 删除一个字段
ALTER TABLE myemp1
DROP COLUMN my_email;

#4.重命名表
#方式1：
RENAME TABLE myemp1
TO myemp11;

DESC myemp11;

#方式2：
ALTER TABLE myemp2
RENAME TO myemp12;

DESC myemp12;

#5.删除表
#不光将表结构删除掉，同时表中的数据也删除掉，释放表空间
DROP TABLE IF EXISTS myemp12;

#6.清空表

#清空表意味着清空表中的所有数据，但是表结构保留
SELECT * FROM employees_copy;

TRUNCATE TABLE employees_copy;

DESC employees_copy;

#7.DCL中 COMMIT 和 ROLLBACK 
# COMMIT：提交数据。一但执行了COMMIT，则数据就被永久地保存在数据库当中，意味着数据不可以回滚。
# ROLLBACK：回滚数据。一但执行ROLLBACK，则可以实现数据的回滚。回滚到最近的一次COMMIT之后。

#8.对比TRUNCATE TABLE 和DELETE FROM
# 相同点：都可以实现对表中所有数据的删除，同时保留表结构。
# 不同点：
#	TRUNCATE TABLE：一旦执行此操作，表数据全部删除。同时，数据是不可以回滚的。
#	DELETE FROM：	一旦执行此操作，表数据可以全部删除（不带WHERE）。同时，数据是可以实现回滚的。

/*
DDL：数据库定义语言
DML：数据库操作语言
DCL：数据库控制语言
9. DDL 和 DML 的说明
① DDL 的操作一旦执行，就不可以回滚。指令SET autocommit = FALSE对DDL操作失效。
  (因为在执行完DDL操作之后，一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影响的。)
② DML 的操作默认情况，一旦执行，也是不可以回滚的。
  但是，如果在执行DML之前，执行了
  SET autocommit = FALSE，则执行的DML操作就可以实现回滚。
  

*/

#演示：DELETE FROM
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
DELETE FROM myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;

#演示：TRUNCATE TABLE
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
TRUNCATE TABLE myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;




#9.测试MySQL8.0的新特性：DDL的原子化
CREATE DATABASE mytest;
USE mytest;
CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(255)
);
SHOW TABLES;

DROP TABLE book1,book2;#MySQl8.0这里报错会回滚
SHOW TABLES;

练习

#1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作
CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET "utf8" 
USE test01_office

#2. 创建表dept01
/*
字段 类型
id INT(7)
NAME VARCHAR(25)
*/
CREATE TABLE IF NOT EXISTS dept01(
id INT(7),
`NAME` VARCHAR(25)
);

#3. 将表departments中的数据插入新表dept02中
CREATE TABLE dept02
AS
SELECT *
FROM atguigudb.departments;

#4. 创建表emp01
/*
字段 类型
id INT(7)
first_name VARCHAR (25)
last_name VARCHAR(25)
dept_id INT(7)
*/
CREATE TABLE IF NOT EXISTS emp01(
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
);

#5. 将列last_name的长度增加到50
DESC emp01;

ALTER TABLE emp01
MODIFY last_name VARCHAR(50);


#6. 根据表employees创建emp02
CREATE TABLE emp02
AS
SELECT *
FROM atguigudb.employees

SHOW TABLES FROM test01_office;


#7. 删除表emp01
DROP TABLE IF EXISTS emp01;

#8. 将表emp02重命名为emp01
#法一：
RENAME TABLE emp02
TO emp01;

#法二：
ALTER TABLE emp02
TO emp01;

#9.在表dept02和emp01中添加新列test_column，并检查所作的操作
ALTER TABLE dept02
ADD test_column VARCHAR(10)
DESC dept02
ALTER TABLE emp01
ADD test_column VARCHAR(10)
DESC emp01


#10.直接删除表emp01中的列 department_id
ALTER TABLE emp01
DROP COLUMN department_id;

#练习2：
# 1、创建数据库 test02_market
CREATE DATABASE IF NOT EXISTS test02_market CHARACTER SET 'utf8';
USE test02_market;
SHOW CREATE DATABASE test02_market;
# 2、创建数据表 customers
CREATE TABLE IF NOT EXISTS customers(
c_num INT,
c_name VARCHAR(50),
c_contact VARCHAR(50),
c_city VARCHAR(50),
c_birth DATE
);

# 3、将 c_contact 字段移动到 c_birth 字段后面
DESC customers;

ALTER TABLE customers
MODIFY c_contact VARCHAR(50) AFTER c_birth;

# 4、将 c_name 字段数据类型改为 varchar(70)
ALTER TABLE customers
MODIFY c_name VARCHAR(70);

# 5、将c_contact字段改名为c_phone
ALTER TABLE customers
CHANGE c_contact c_phone VARCHAR(50);

# 6、增加c_gender字段到c_name后面，数据类型为char(1)
ALTER TABLE customers
ADD c_gender CHAR(1) AFTER c_name;

# 7、将表名改为customers_info
RENAME TABLE customers 
TO customers_info;

DESC customers_info;

# 8、删除字段c_city
ALTER TABLE customers_info
DROP COLUMN c_city;


#练习3：
# 1、创建数据库test03_company
CREATE DATABASE IF NOT EXISTS test03_company CHARACTER SET 'utf8';
USE test03_company;

# 2、创建表offices
CREATE TABLE IF NOT EXISTS offices(
officeCode INT,
city VARCHAR(30),
address VARCHAR(50),
country VARCHAR(50),
postalCode VARCHAR(25)
);

DESC offices;

# 3、创建表employees
CREATE TABLE IF NOT EXISTS employees(
empNum INT,
lastName VARCHAR(50),
firstName VARCHAR(50),
mobile VARCHAR(25),
`code` INT,
jobTitle VARCHAR(50),
birth DATE,
note VARCHAR(255),
sex VARCHAR(5)
);

DESC employees;
# 4、将表employees的mobile字段修改到code字段后面

ALTER TABLE employees
MODIFY mobile VARCHAR(20) AFTER `code`;

# 5、将表employees的birth字段改名为birthday
ALTER TABLE employees
CHANGE birth birthday DATE;

# 6、修改sex字段，数据类型为char(1)
ALTER TABLE employees
MODIFY sex CHAR(1);

# 7、删除字段note
ALTER TABLE employees
DROP COLUMN note;

# 8、增加字段名favoriate_activity，数据类型为varchar(100)
ALTER TABLE employees
ADD favoriate_activity VARCHAR(100);

# 9、将表employees的名称修改为 employees_info
RENAME TABLE employees 
TO employees_info;

DESC employees_info;