MySQL基础自查手册

本文最后更新于：2021年9月28日上午

前言

查看Mysql版本：mysql --version

1.基本规范

SQL语句可以单行或多行书写，以分号结尾；
可以用空格和缩进来来增强语句的可读性；
关键字不区别大小写，建议使用大写；

2.注释

单行注释(两种)
```
#
-- 此种注释需要加空格
```
多行注释
```
/* 不可嵌套注释 */
```

3.基础查询

语法： select 查询列表 from 表名;

特点：
1、查询的结果集是一个虚拟表
2、select 查询列表类似于System.out.println(打印内容);
（1）select后面跟的查询列表，可以有多个部分组成，中间用逗号隔开
例如：select 字段1,字段2,表达式 from 表;
（2）System.out.println()的打印内容，只能有一个。

执行顺序：
① from子句 ② select子句

查询列表可以是：字段、表达式、常量、函数等

（1）示例

USE myemployees;

# 查询常量
SELECT 100;
SELECT 100%3;

# 查询单个、多个、所有字段
SELECT last_name FROM employees; 
SELECT last_name,email,employee_id FROM employees;
SELECT * FROM employees;


SELECT DATABASE();  # 查询当前数据库
SELECT VERSION();  # 当前SQL版本
SELECT USER();  # 当前用户

# 为 函数或者属性 起别名（AS可以省略）
SELECT USER() AS 用户名;
SELECT USER() AS '用户名';
SELECT USER() AS "用户名";
SELECT last_name AS "姓名" FROM employees;

SELECT USER()   用户名;
SELECT USER()   "用户名";
SELECT USER()   '用户名';
SELECT last_name   "姓 名" FROM employees;

（2）+ 与concat拼接元素

Java中+的作用：
1、加法运算
100+1.5 ‘a’+2 1.3+’2’
2、拼接符，至少有一个操作数为字符串
“hello”+’a’ ‘’ +1

mysql中+的作用：
1、加法运算
①两个操作数都是数值型
100+1.5
②其中一个操作数为字符型, 将字符型数据强制转换成数值型

如果无法转换，则直接当做0处理

‘张无忌’ + 100 ===>100

③其中一个操作数为null

null+null====》null

null+100====》 null

2、加号无法实现拼接，如需要拼接字符串需使用 CONCAT

-- 需求：查询 first_name 和last_name 拼接成的全名，最终起别名为：姓 名
SELECT first_name+last_name AS 姓名 FROM employees;  # 返回为空
SELECT CONCAT(first_name,last_name) AS "姓 名" FROM employees;

SELECT 100+1.5;
SELECT '张无忌' + 100;

（3）distinct的使用（去除重复元素）

-- 需求：查询员工涉及到的部门编号有哪些
SELECT DISTINCT department_id FROM employees;

（4）查看表的结构

DESC employees;
SHOW COLUMNS FROM employees;

# 只有salary被重命名为sal
SELECT last_name,job_id,salary AS sal FROM employees;   
SELECT * FROM employees;

# 将salary增大12倍，并重命名为 ANNUAL SALARY
SELECT employee_id , last_name, salary * 12 "ANNUAL SALARY"  FROM employees;

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

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

# 显示出表 employees 的全部列，各个列之间用逗号连接，列头显示成 OUT_PUT
SELECT CONCAT(employee_id,',',first_name,',', 'last_name', ',', 'salary', ',', 
              IFNULL(commission_pct, '') ) AS "OUT_PUT" FROM employees;

（5）ifnull(表达式1，表达式2)

/*
功能：如果表达式1为null，则显示表达式2，否则显示表达式1
*/
SELECT commission_pct, IFNULL(commission_pct,'空') FROM employees;

4.条件查询

语法： select 查询列表 from 表名 where 筛选条件;

执行顺序：
①from子句
②where子句
③select子句

用法：
select last_name,first_name from employees where salary>20000;

特点：

1、按关系表达式筛选

关系运算符：> < >= <= = <> 补充：也可以使用!=，但不建议使用 <>

2、按逻辑表达式筛选
逻辑运算符：and or not 补充：也可以使用&& || ! ，但不建议

3、模糊查询
like, in, between and, is null

（1）按关系表达式筛选

# 案例1：查询 部门编号不是100的员工信息 和 部门变换是100的员工信息
SELECT * FROM employees WHERE department_id <> 100;
SELECT * FROM employees WHERE department_id = 100;  # 不要用 ==

#案例2：查询 工资<15000 员工的姓名、工资
SELECT first_name,last_name,salary FROM employees WHERE salary < 15000;

（2）按逻辑表达式筛选

#案例1：查询部门编号不是 50-100之间员工姓名、部门编号、邮箱
SELECT first_name,last_name,department_id,email FROM employees WHERE department_id > 100 OR department_id < 50;
SELECT first_name,last_name,department_id,email FROM employees WHERE NOT(department_id >= 50 AND department_id <= 100);

#案例2：查询奖金率>0.03 或者 员工编号在60-110之间的员工信息
SELECT * FROM employees WHERE commission_pct > 0.03 OR (employee_id >= 60 AND employee_id <= 110);

（3）模糊查询

① LIKE

一般和通配符搭配使用，对字符型数据进行部分匹配查询
常见的通配符：
_ 任意单个字符
% 任意多个字符,支持0 ~ 多个
用法：like/not like

#案例1：查询姓名中 包含字符a 的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';   # 前后任意一个字符

#案例2：查询姓名中 包含最后一个字符为e 的员工信息
SELECT * FROM employees WHERE last_name LIKE '%e';    # e前面任意n个字符

#案例3：查询姓名中包含 第一个字符为e 的员工信息
SELECT * FROM employees WHERE last_name LIKE 'e%';    # e后面n个字符

#案例4：查询姓名中包含 第三个字符为x 的员工信息
SELECT * FROM employees WHERE last_name LIKE '__x%';  # 前面两个字符

#案例5：查询姓名中包含第二个字符为_的员工信息
SELECT * FROM employees WHERE last_name LIKE '_\_%';
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

② IN

功能：查询某字段的值是否属于指定的列表之内
用法：in/not in
a in(常量值1,常量值2,常量值3,…)
a not in(常量值1,常量值2,常量值3,…)

#案例1：查询部门编号是30/50/90的员工名、部门编号
SELECT last_name,department_id FROM employees WHERE department_id IN (30,50,90);
SELECT last_name,department_id FROM employees WHERE department_id = 30 OR department_id = 50 OR department_id = 90;

#案例2：查询工种编号不是SH_CLERK或IT_PROG的员工信息
SELECT * FROM employees WHERE job_id NOT IN ('SH_CLERK','IT_PROG');
SELECT * FROM employees WHERE NOT (job_id = 'SH_CLERK' OR job_id = 'IT_PROG');

③ BETWEEN … AND …

功能：判断某个字段的值是否介于xx之间

用法：between and/not between and

#案例1：查询部门编号是30-90之间的部门编号、员工姓名
SELECT department_id,last_name FROM employees WHERE department_id BETWEEN 30 AND 90;
SELECT department_id,last_name FROM employees WHERE department_id >= 30 AND department_id <= 90;

#案例2：查询年薪不是100000-200000之间的员工姓名、工资、年薪
SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) FROM employees 
WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;

SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) FROM employees 
WHERE salary*12*(1+IFNULL(commission_pct,0)) < 100000 OR salary*12*(1+IFNULL(commission_pct,0)) > 200000;

④ is null/is not null

#案例1：查询没有奖金的员工信息
SELECT * FROM employees WHERE commission_pct IS NULL;

#案例2：查询有奖金的员工信息
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

⑤ 几种比较符号

= 只能判断普通的内容

IS 只能判断NULL值

<=> 安全等于，既能判断普通内容，又能判断NULL值

SELECT * FROM employees WHERE salary = 10000;
SELECT * FROM employees WHERE salary <=> 10000;
SELECT * FROM employees WHERE salary <=> NULL;

（4）作业题

#1. 查询工资大于12000的员工姓名和工资
SELECT first_name,last_name,salary FROM employees WHERE salary > 12000;

#2. 查询员工号为176的员工的姓名和部门号和年薪
SELECT first_name,last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) FROM employees WHERE employee_id = 176;

#3. 选择工资不在5000到12000的员工的姓名和工资
SELECT first_name,last_name,salary FROM employees WHERE salary > 5000 AND salary < 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 first_name,last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

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

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

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

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

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

5.排序查询

语法： select 查询列表 from 表名【where 筛选条件】 order by 排序列表

执行顺序：
①from子句 ②where子句
③select子句 ④order by 子句

举例：
select last_name,salary from employees where salary>20000 order by salary ;

特点：
1、排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合
2、升序，通过 asc，默认行为
降序，通过 desc

（1）示例

一、按单个字段排序

#案例1：将 员工编号>120 的员工信息进行 工资 的升序、降序
SELECT * FROM employees WHERE employee_id > 120 ORDER BY salary;   # 默认升序
SELECT * FROM employees WHERE employee_id > 120 ORDER BY salary ASC;
SELECT * FROM employees WHERE employee_id > 120 ORDER BY salary DESC;

二、按表达式排序

案例1：对有奖金的员工，按年薪降序

SELECT * FROM employee WHERE commission_pct IS NOT FULL 
   ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

三、按别名排序

#案例1：对有奖金的员工，按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees WHERE commission_pct IS NOT NULL ORDER BY 年薪 DESC;

四、按函数的结果排序

#案例1：按姓名的字数长度进行升序
SELECT last_name FROM employees ORDER BY LENGTH(last_name);

五、按多个字段排序

#案例1：查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序
SELECT last_name,salary,department_id FROM employees ORDER BY salary, department_id DESC;

六、补充选学：按列数排序

SELECT * FROM employees ORDER BY first_name;
SELECT * FROM employees ORDER BY 2;

（2）作业

#1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC, last_name;  # 通过逗号隔开

#2. 选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT 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;

6.常见函数

函数：

类似于java中学过的“方法”，为了解决某个问题，将编写的一系列的命令集合封装在一起，对外仅仅暴露方法名，供外部调用

常见函数：
（1）字符函数
concat, substr, length（str）, char_length, upper, lower, trim, left, right, lpad, rpad, instr, strcmp
（2）数学函数
abs, ceil, floor, round, truncate, mod
（3）日期函数
now, curtime, curdate, datediff, date_format, str_to_date
（4）流程控制函数
if, case

（1）字符函数

#1、CONCAT 拼接字符
SELECT CONCAT('hello',first_name,last_name) 备注 FROM employees;

#2、LENGTH 获取字节长度
SELECT LENGTH('郭襄');  # 1个汉字 = 3个字节
SELECT LENGTH('hello,郭襄');

#3、CHAR_LENGTH 获取字符个数
SELECT CHAR_LENGTH('郭襄'); # 1个汉字 = 1个字符
SELECT CHAR_LENGTH('hello,郭襄');

#4、SUBSTRING 截取子串
/*
    substr(str,起始索引，截取的字符长度)
    substr(str,起始索引)
    注意：起始索引从1开始！！！
*/
SELECT SUBSTR('张三丰爱上了郭襄',1,3);  # 张三丰
SELECT SUBSTR('张三丰爱上了郭襄',7);    # 郭襄
SELECT SUBSTR('张三丰爱上了郭襄',-2);   # 郭襄

#5、LEFT/RIGHT  截取子串
SELECT LEFT('鸠摩智',1);   # 鸠
SELECT RIGHT('鸠摩智',1);  # 智

#6、INSTR获取字符第一次出现的索引
SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');

#7、TRIM去前后指定的字符，默认是去空格
SELECT TRIM(' 虚  竹    ')  AS a;
SELECT TRIM('x' FROM 'xxxxxx虚xxx竹xxxxxxxxxxxxxxxxxx')  AS a;

#8、LPAD/RPAD  左填充/右填充字符，使总长度符合要求
SELECT LPAD('木婉清',10,'a');    # aaaaaaa木婉清
SELECT RPAD('木婉清',10,'a');    # 木婉清aaaaaaa

#9、UPPER/LOWER  变大写/变小写
#案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”
SELECT 

#10、STRCMP 比较两个字符大小
SELECT STRCMP('aec','aec');  # 0

（2）数学函数

#1、ABS 绝对值
SELECT ABS(-2.4);

#2、CEIL 向上取整  返回>=该参数的最小整数
SELECT CEIL(-1.09);  # -1
SELECT CEIL(0.09);   # 1
SELECT CEIL(1.00);   # 1

#3、FLOOR 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-1.09);  # -2
SELECT FLOOR(0.09);   # 0
SELECT FLOOR(1.00);   # 1

#4、ROUND 四舍五入，把数值字段舍入为指定的小数位数。
SELECT ROUND(1.8712345);    # 2
SELECT ROUND(1.8712345,2);  # 1.87

#5、TRUNCATE 直接截断
SELECT TRUNCATE(1.8712345,1);  # 1.8

#6、MOD 取余
SELECT MOD(-10,3); # -1
-- a%b = a-(INT)a/b*b
-- -10%3 = -10 - (INT)(-10)/3*3   = -1

SELECT -10%3;    # -1
SELECT 10%3;     # 1
SELECT -10%-3;   # -1
SELECT 10%-3;    # 1

（3）日期函数

#1、NOW
SELECT NOW();

#2、CURDATE
SELECT CURDATE();

#3、CURTIME
SELECT CURTIME();

#4、DATEDIFF
SELECT DATEDIFF('1998-7-16','2019-7-13');

#5、DATE_FORMAT
SELECT DATE_FORMAT('1998-7-16','%Y年%M月%d日 %H小时%i分钟%s秒') 出生日期;   # 1998年July月16日 00小时00分钟00秒
SELECT DATE_FORMAT(hiredate,'%Y年%M月%d日 %H小时%i分钟%s秒')入职日期 FROM employees;

#6、STR_TO_DATE 按指定格式解析字符串为日期类型
SELECT * FROM employees WHERE hiredate < STR_TO_DATE('3/15 1998','%m/%d %Y');

（4）流程控制函数

① IF函数

SELECT IF(100>9,'好','坏');

#需求：如果有奖金，则显示最终奖金，如果没有，则显示0
SELECT IF(commission_pct IS NULL, 0, salary*12*commission_pct) 奖金, commission_pct FROM employees;

② CASE函数

①情况1 ：类似于switch语句，可以实现 等值判断
CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
…
ELSE 结果n
END

②情况2：类似于多重IF语句，实现 区间判断
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
…

ELSE 结果n

END

# 案例1：

-- 部门编号是30，工资显示为2倍
-- 部门编号是50，工资显示为3倍
-- 部门编号是60，工资显示为4倍
-- 否则不变

-- 显示 部门编号，新工资，旧工资

SELECT department_id, salary,
CASE department_id
	WHEN 30 THEN salary*2
	WHEN 50 THEN salary*3
	WHEN 60 THEN salary*4
	ELSE salary
END  newSalary
FROM employees;

SELECT department_id,
CASE department_id
	WHEN 30 THEN salary*2
	WHEN 50 THEN salary*3
	WHEN 60 THEN salary*4
	ELSE salary
END  newSalary,   # 重命名为newSalary
salary  
FROM employees;

SELECT department_id,
CASE 
	WHEN department_id=30 THEN salary*2
	WHEN department_id=50 THEN salary*3
	WHEN department_id=60 THEN salary*4
	ELSE salary
END newSalary,
salary
FROM employees;



#案例2：如果工资>20000,显示级别A
--       工资>15000,显示级别B
--       工资>10000,显示级别C
--       否则，显示D
      
SELECT salary,
CASE 
	 WHEN salary>20000 THEN 'A'
	 WHEN salary>15000 THEN 'B'
	 WHEN salary>10000 THEN 'C'
	 ELSE 'D'
END 
AS  级别
FROM employees;

7.统计函数

说明：分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数

分组函数清单：
sum(字段名)：求和
avg(字段名)：求平均数
max(字段名)：求最大值
min(字段名)：求最小值
count(字段名)：计算非空字段值的个数

（1）示例

# 案例1 ：查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数
SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM employees;

# 案例2：添加筛选条件
# ①查询emp表中记录数：
SELECT COUNT(employee_id) FROM employees;
# ②查询emp表中有佣金的人数：
SELECT COUNT(salary) FROM employees;		
# ③查询emp表中月薪大于2500的人数：
SELECT COUNT(*) FROM employees WHERE salary > 2500;
# ④查询有领导的人数：
SELECT COUNT(manager_id) FROM employees;

（2）count的补充介绍

#1、统计结果集的行数，推荐使用count(*)
SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM employees WHERE department_id = 30;

SELECT COUNT(1) FROM employees;
SELECT COUNT(1) FROM employees WHERE department_id = 30;
		
#2、搭配distinct实现去重的统计
#需求：查询有员工的部门个数
SELECT COUNT(DISTINCT department_id) FROM employees;

# 思考：每个部门的总工资、平均工资？
# GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
SELECT department_id, SUM(salary), AVG(salary) FROM employees GROUP BY department_id;

（3）作业

#1.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary) FROM employees;

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

#3.查询部门编号为90的员工个数
SELECT COUNT(employee_id) FROM employees WHERE department_id = 90;

8.分组筛选

语法：
select 查询列表 from 表名
where 筛选条件 group by 分组列表 having 分组后筛选
order by 排序列表;

执行顺序：
①from子句 ②where子句 ③group by 子句
④having子句 ⑤select子句 ⑥order by子句

特点：
①查询列表往往是 分组函数和被分组的字段 ★
②分组查询中的筛选分为两类：
筛选的基表使用的关键词位置
分组前筛选原始表 where group by 的前面

分组后筛选分组后的结果集 having group by的后面

★★★ where——group by ——having

问题：分组函数做条件只可能放在having后面！！！

（1）简单的分组

#案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id FROM employees GROUP BY job_id;

#案例2：查询每个领导的手下人数
SELECT COUNT(*), manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id;

（2）可以实现分组前的筛选

#案例1：查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary) 最高工资,department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;

#案例2：查询每个领导手下有奖金的员工的平均工资
SELECT AVG(salary) 平均工资, manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;

（3）可以实现分组后的筛选

#案例1：查询哪个部门的员工个数>5
SELECT COUNT(*) 员工个数, department_id FROM employees GROUP BY department_id HAVING 员工个数 > 5;

#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id, MAX(salary) FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000;

#案例3：领导编号>102的 每个领导手下的最低工资大于5000的最低工资
SELECT manager_id, MIN(salary) FROM employees WHERE manager_id > 102 GROUP BY manager_id HAVING MIN(salary)>5000;

（4）可以实现排序

#案例：查询没有奖金的员工的最高工资>6000的工种编号和最高工资，按最高工资升序
SELECT MAX(salary) 最高工资, job_id FROM employees WHERE commission_pct IS NULL GROUP BY job_id 
HAVING MAX(salary)>6000 ORDER BY MAX(salary);

（5）按多个字段分组

#案例：查询每个工种每个部门的最低工资,并按最低工资降序（工种和部门都一样，才是一组）
SELECT job_id, department_id, MIN(salary) 最低工资 FROM employees GROUP BY job_id,department_id;

（6）作业

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

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

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

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

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

9.连接查询

/*
说明：又称 多表查询，当查询语句涉及到的字段来自于 多个表 时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
	发生原因：没有有效的连接条件
	如何避免：添加有效的连接条件

分类：
	（1）按年代分类：
			1、sql92标准:仅仅支持内连接
			2、sql99标准【推荐】：支持内连接 + 外连接（左外和右外）+ 交叉连接
	（2）按功能分类：
			内连接：
				等值连接、非等值连接、自连接
			外连接：
				左外连接、右外连接、全外连接
			交叉连接
*/

sql92标准

语法:
select 查询列表 from 表1 别名, 表2 别名 where 连接条件 and 筛选条件
group by 分组列表 having 分组后筛选
order by 排序列表

执行顺序：
1、from子句 2、where子句 3、and子句
4、group by子句 5、having子句
6、select子句 7、order by子句
PS.仅仅支持内连接

一、等值连接

① 多表等值连接的结果为多表的交集部分
② n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④ 一般需要为表起别名
⑤ 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

# 案例1：查询女神名和对应的男神名
SELECT NAME, boyName FROM boys, beauty WHERE beauty.boyfriend_id = boys.id;

# 案例2：查询员工名和对应的部门名
USE myemployees;
SELECT last_name,department_name FROM employees,departments WHERE employees.department_id = departments.department_id;

# 2、为表起别名
/*
	① 提高语句的简洁度
	② 区分多个重名的字段
	注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
# 查询员工名、工种号、工种名
SELECT e.last_name, e.job_id, j.job_title FROM employees e, jobs j WHERE e.job_id = j.job_id;

# 3、两个表的顺序 是否 可以调换
SELECT e.last_name, e.job_id, j.job_title FROM jobs j, employees e WHERE e.job_id = j.job_id;  # 实践证明可以

# 4、可以加筛选
# 案例1：查询有奖金的员工名、部门名
SELECT last_name, department_name FROM employees, departments WHERE commission_pct IS NOT NULL AND employees.department_id = departments.department_id;
# 案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city FROM departments,locations WHERE departments.location_id = locations.location_id AND city LIKE '_o%';

# 5、可以加分组
# 案例1：查询每个城市的部门个数
SELECT city, COUNT(department_id) FROM locations, departments WHERE locations.location_id = departments.location_id GROUP BY city;
# 案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name, departments.manager_id, MIN(salary) FROM departments, employees WHERE employees.department_id = departments.department_id
AND commission_pct IS NOT NULL GROUP BY department_name;     # 由于多个表中都有manager_id，因此为了避免出错，需指定来源

# 6、可以加排序
# 案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(employee_id) 员工个数 FROM jobs,employees  WHERE jobs.job_id = employees.job_id 
GROUP BY job_title ORDER BY 员工个数 DESC;

# 7、可以实现三表连接？
# 案例：查询员工名、部门名和所在的城市
SELECT last_name, department_name, city FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

二、非等值连接

不再通过 相等条件 来连接两个表

# 案例1：查询员工的工资和工资级别
# 创建表
CREATE TABLE job_grades(grade_level VARCHAR(3), lowest_sal  int, highest_sal int);
INSERT INTO job_grades VALUES ('A', 1000, 2999); 
INSERT INTO job_grades VALUES ('B', 3000, 5999);
INSERT INTO job_grades VALUES('C', 6000, 9999);
INSERT INTO job_grades VALUES('D', 10000, 14999);
INSERT INTO job_grades VALUES('E', 15000, 24999);
INSERT INTO job_grades VALUES('F', 25000, 40000);

# 非等值连接
SELECT salary,grade_level FROM employees,job_grades WHERE employees.salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal ORDER BY grade_level;

三、自连接

表自己连接自己

# 案例：查询 员工名和上级的名称(员工表中，每个员工只有自己的上级的id，并不知道姓名)
SELECT e.employee_id, e.last_name, m.employee_id, m.last_name FROM employees e,employees m 
WHERE e.manager_id = m.employee_id;   # 虽然是上级，但其也为员工，有id。

SQL99语法

🐳 内连接

语法：
SELECT 查询列表 FROM 表名1 别名【INNER】 JOIN 表名2 别名 ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表 HAVING 分组后筛选
ORDER BY 排序列表;

SQL92和99的区别：
后者使用JOIN代替了之前的逗号，并且将连接条件和筛选条件进行了分离。

一、等值连接

# (1)简单连接
# 案例：查询员工名和部门名
USE myemployees;
SELECT last_name,department_name FROM departments d  JOIN  employees e ON e.department_id = d.department_id;

# (2)添加筛选条件
# 案例：查询部门编号>100的部门名和所在的城市名
SELECT department_name,city FROM departments d JOIN locations l ON d.location_id = l.location_id WHERE d.department_id > 100;

# (3)添加分组+筛选
# 案例：查询每个城市的部门个数
SELECT city,COUNT(*)  FROM departments JOIN locations ON departments.location_id = locations.location_id GROUP BY city;

# (4)添加分组+筛选+排序
# 案例1：查询部门中员工个数>10的部门名，并按员工个数降序
SELECT department_name, COUNT(*) 员工个数 FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY department_name HAVING 员工个数 > 10 ORDER BY 员工个数 DESC;

二、非等值连接

# 案例：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组
SELECT * FROM sal_grade;

SELECT COUNT(*) 个数, grade FROM employees e JOIN sal_grade g ON e.salary BETWEEN g.min_salary AND g.max_salary WHERE e.department_id BETWEEN 10 AND 90 GROUP BY g.grade;

三、自连接

# 案例：查询员工名和对应的领导名
SELECT e.`last_name`,m.`last_name` FROM employees e JOIN employees m ON e.`manager_id`=m.`employee_id`;

🐳 外连接(左右连接)

说明：查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则显示null

应用场景：一般用于查询主表中有但从表没有的记录

特点：
1、外连接分主从表，两表的顺序不能任意调换
2、左连接的话，left join 左边为主表
右连接的话，right join 右边为主表

left join : 左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
right join : 右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
inner join/join : 内连接，又叫等值连接，只返回两个表中连接字段相等的行。
full join : 外连接，返回两个表中的行：left join + right join。
cross join : 结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

语法：
select 查询列表 from 表1 别名 left/right/full outer join 表2 别名 on 连接条件
where 筛选条件;

USE girls;
# 案例1：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示为null
# 左连接
SELECT g.*,b.boyName FROM beauty g LEFT JOIN boys b ON g.boyfriend_id = b.id;
# 右连接
SELECT g.*,b.boyName FROM boys b RIGHT JOIN beauty g ON g.boyfriend_id = b.id;

# 案例2：查哪个女神没有男朋友
# 左连接
SELECT g.name FROM beauty g LEFT JOIN boys b ON g.boyfriend_id = b.id WHERE boyName IS NULL;
# 右连接
SELECT g.name FROM boys b RIGHT JOIN beauty g ON g.boyfriend_id = b.id WHERE boyName IS NULL;

# 案例3：查询哪个部门没有员工，并显示其部门编号和部门名
# 分析：表departments中存在一些部门，但在employee表对应过来时，某些部门 不存在员工。
USE myemployees;
# 左连接
SELECT d.department_id, department_name, e.employee_id 
FROM departments d LEFT JOIN employees e ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;
# 右连接
SELECT d.department_id,department_name,e.employee_id 
FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

📚 作业

# 1.显示所有员工的姓名，部门号和部门名称。
# 通过ON后面的条件 建立起两个表间的桥梁。
SELECT last_name, e.department_id, department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;

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

# 3. 选择所有有奖金的员工的last_name , department_name , location_id , city
# 支持通过多个JOIN和ON，多次添加表
SELECT last_name, department_name, d.location_id, city FROM employees e 
JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id
WHERE commission_pct IS NOT NULL;

SELECT last_name, department_name, d.location_id, city FROM employees e, departments d,locations l 
WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND commission_pct IS NOT NULL; # 通过将条件一次性放到where中，看起来更简洁

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

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

# 6.查询每个国家下的部门个数大于2的国家编号
SELECT country_id, COUNT(*) 部门个数 FROM departments d, locations l WHERE d.location_id = l.location_id 
GROUP BY country_id HAVING 部门个数 > 2;

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

SELECT e.last_name "employees", e.employee_id "Emp#", m.last_name "manager", m.employee_id "Mgr#"
FROM  employees e JOIN employees m ON e.manager_id = m.employee_id
WHERE e.employee_id = 101;

SELECT e.`last_name`,m.`last_name` FROM employees e JOIN employees m ON e.`manager_id`=m.`employee_id`;

# 8.查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
USE girls;
# 先连接，LEFT JOIN是在左边表的基础上添加达成条件的右侧表选项
SELECT beauty.id 女生ID, beauty.name 女生名字, boys.* FROM beauty LEFT JOIN boys ON beauty.boyfriend_id = boys.id  WHERE beauty.id > 3;  

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

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

10.子查询

说明：
当 一个查询语句中又嵌套了另一个完整的select语句，则『被嵌套的select语句』称为子查询或内查询，『外面的select语句』称为主查询或外查询。

分类：
按子查询出现的位置进行分类：
1、select后面
要求：子查询的结果为单行单列（标量子查询）
2、from后面
要求：子查询的结果可以为多行多列
3、where或having后面 ★
要求：子查询的结果必须为单列
单行子查询、多行子查询
4、exists后面
要求：子查询结果必须为单列（相关子查询）

特点：
1、子查询放在条件中，要求必须放在 条件的右侧
2、子查询一般放在小括号中
3、子查询的执行优先于主查询
4、单行子查询对应了单行操作符：> < >= <= = <>
多行子查询对应了多行操作符：any/some all in

（1）放在where或having后面

# 单行子查询
# 案例1：谁的工资比 Abel 高?
USE myemployees;
SELECT employee_id,last_name,salary FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');   # 括号中，子查询 Abel的工资

# 案例2：返回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);
     
# 案例3：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

# 案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
# ① 查询50号部门最低工资  ② 查询各部门的最低工资，然后再筛选
SELECT department_id,MIN(salary) FROM employees GROUP BY department_id
WHERE MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50)

# 多行子查询
/*
	in:判断某字段是否在指定列表内   x in(10,30,50)
	any/some:判断某字段的值是否满足其中任意一个   x>any(10,30,50)   x>min()
	all:判断某字段的值是否满足里面所有的  x >all(10,30,50)       x>max()
	*/
# 案例1：返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name FROM employees WHERE department_id IN (
       SELECT department_id FROM departments WHERE location_id IN (1400,1700));
       
# 案例2：返回其它部门中比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 DISTINCT salary FROM employees WHERE job_id = 'IT_PROG');
SELECT employee_id,last_name,job_id,salary FROM employees WHERE job_id <> 'IT_PROG' AND 
				salary < (SELECT MAX(salary) FROM employees WHERE job_id = 'IT_PROG');	
                
#案例3：返回其它部门中比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 DISTINCT salary FROM employees WHERE job_id = 'IT_PROG');

SELECT employee_id,last_name,job_id,salary FROM employees WHERE job_id <> 'IT_PROG' AND 
salary < (SELECT MIN(salary) FROM employees WHERE job_id = 'IT_PROG');

（2）放在select后面

# 案例；查询部门编号是50的员工个数
SELECT (SELECT COUNT(*) FROM employees WHERE department_id = 50) 个数

（3）放在from后面

# 案例：查询每个部门的平均工资的工资级别 ---> 先查询平均工资，再查询级别
# 此处，两表连接的条件为 表1的某个属性在表2的某个区间内
SELECT Avg_S_dep_id.department_id, Avg_S_dep_id.Avg_S, sal_grade.grade FROM sal_grade 
		JOIN  (SELECT AVG(salary) Avg_S, department_id FROM employees GROUP BY department_id)  Avg_S_dep_id
ON  Avg_S_dep_id.Avg_S BETWEEN sal_grade.min_salary AND sal_grade.max_salary;

（4）放在exists后面

# 案例1 ：查询有无名字叫“张三丰”的员工信息   有，返回1；无，返回0
SELECT EXISTS (	SELECT * FROM employees WHERE last_name = "张三丰")；

# 案例2：查询没有女朋友的男神信息
USE girls;
SELECT boys.* FROM boys WHERE NOT EXISTS (
		SELECT boyfriend_id FROM beauty g,boys b WHERE b.id = g.boyfriend_id);
		
SELECT b.* FROM boys b LEFT JOIN beauty g ON b.id = g.boyfriend_id WHERE b.boyName IS NULL;

# 案例3：查询没有男朋友的女生信息		
SELECT g.* FROM beauty g WHERE g.boyfriend_id NOT IN (
		SELECT boyfriend_id FROM beauty g,boys b WHERE g.boyfriend_id = b.id);

SELECT g.* FROM beauty g LEFT JOIN boys b ON b.id = g.boyfriend_id WHERE b.boyName IS NULL;

（5）作业

# 1. 查询和Zlotkey相同部门的员工姓名和工资
USE myemployees;
SELECT last_name,salary FROM employees WHERE department_id = (
			SELECT department_id FROM employees WHERE last_name = "Zlotkey");
# 2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id,last_name,salary FROM employees WHERE salary > (
			SELECT MIN(salary) FROM employees);
			
# 3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

SELECT employee_id,last_name,salary ,aaa.department_id, aaa.min__salary
FROM employees JOIN (SELECT department_id,MIN(salary) min__salary FROM employees GROUP BY department_id) aaa ON employees.department_id = aaa.department_id WHERE salary>min__salary ORDER BY employee_id;
			
# 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 employee_id FROM employees WHERE department_id = (
			SELECT department_id FROM departments WHERE location_id = 1700);
# 6. 查询管理者是King的员工姓名和工资
SELECT last_name,salary FROM employees WHERE manager_id = (
			SELECT employee_id FROM employees WHERE last_name = "king"); 
# 7. 查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
SELECT CONCAT(first_name, last_name) 姓名 FROM employees WHERE salary IN (
   SELECT MAX(salary) FROM employees );

11.分页查询 LIMIT

应用场景：当页面上的数据，一页显示不全，则需要分页显示

分页查询的sql命令请求数据库服务器—->服务器响应查询到的多条数据—->前台页面

语法：
select 查询列表 from 表1 别名 join 表2 别名 on 连接条件
where 筛选条件
group by 分组 having 分组后筛选
order by 排序列表
limit 起始条目索引,显示的条目数

执行顺序：
1》from子句 2》join子句 3》on子句
4》where子句 5》group by子句 6》having子句
7》select子句 8》order by子句
9》limit子句

特点：
①起始条目索引如果不写，默认是0 !!!!
②limit后面支持两个参数
参数1：显示的起始条目索引
参数2：条目数

公式：
假如要显示的页数是page，每页显示的条目数为size，则
select from employees limit (page-1)size,size;
page size=10
1 limit 0,10
2 limit 10,10
3 limit 20,10
4 limit 30,10

# 案例1：查询员工信息表的前5条
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;

#案例2：查询有奖金的，且工资较高的第11名到第20名
SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary LIMIT 10,10;

# 练习：查询年薪最高的前10名
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC LIMIT 10;

12.联合查询 UNION

说明：当查询结果来自于 多张表 ，但『多张表之间没有关联』，这个时候往往使用联合查询，也称为 union查询

语法：
select 查询列表 from 表1 where 筛选条件
union
select 查询列表 from 表2 where 筛选条件

特点：
1、多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致
2、union实现去重查询
union all 实现全部查询，包含重复项

# 案例1：查询所有国家的年龄>20岁的用户信息
SELECT * FROM usa WHERE uage >20 UNION 	SELECT * FROM chinese WHERE age >20 ; 
# 案例2：查询所有国家的用户姓名和年龄
SELECT uname,uage FROM usa UNION  SELECT age,`name` FROM chinese;	 
# 案例3：union自动去重 / union all 可以支持重复项
SELECT 1,'范冰冰';
SELECT 1,'范冰冰' UNION SELECT 1,'范冰冰';
SELECT 1,'范冰冰' UNION ALL SELECT 1,'范冰冰' UNION  ALL SELECT 1,'范冰冰' UNION  ALL SELECT 1,'范冰冰' ;

13.DDL-库和表的管理

DDL语言，即Data Define Language数据定义语言，用于对数据库和表的管理和操作

（1）库的管理

创建数据库 CREATE

CREATE DATABASE stuDB;
CREATE DATABASE IF NOT EXISTS stuDB;

删除数据库 DROP

DROP DATABASE stuDB;
DROP DATABASE IF EXISTS stuDB;

（2）表的管理

创建表 CREATE

语法：
CREATE TABLE [IF NOT EXISTS] 表名(
字段名字段类型【字段约束】,
字段名字段类型【字段约束】,
字段名字段类型【字段约束】,
字段名字段类型【字段约束】,
字段名字段类型【字段约束】);

数据类型：
1、整型：
TINYINT SMALLINT INT BIGINT
2、浮点型：
FLOAT(m,n) DOUBLE(m,n) DECIMAL(m,n) m和n可选
3、字符型：
CHAR(n):n可选； VARCHAR(n)：n必选； TEXT
其中，n表示最多字符个数
4、日期型：
DATE TIME DATETIME TIMESTAMP
5、二进制型：
BLOB 存储图片数据

常见约束：

1、NOT NULL 非空：用于限制该字段为必填项
2、DEFAULT 默认：用于限制该字段没有显式插入值，则直接显式默认值
3、PRIMARY KEY 主键：用于限制该字段值不能重复，设置为主键列的字段默认不能为空；一个表只能有一个主键，当然可以是组合主键
4、UNIQUE 唯一：用于限制该字段值不能重复
字段是否可以为空一个表可以有几个
主键 × 1个
唯一 √ n个
5、CHECK检查：用于限制该字段值必须满足指定条件
CHECK(age BETWEEN 1 AND 100)
6、FOREIGN KEY 外键:用于限制两个表的关系,要求『外键列的值』必须来自于『主表的关联列』
① 主表的关联列和从表的关联列的类型必须一致，意思一样，名称无要求
② 主表的关联列要求必须是主键

# 案例1：没有添加约束
CREATE TABLE IF NOT EXISTS stuinfo(
	stu_id INT,
	stu_gender CHAR(1),
	stu_name VARCHAR(20),
	email VARCHAR(20),
	borndtae DATETIME
);
# 案例2：添加约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
	stu_id INT PRIMARY KEY,   # 添加了『主键约束』
	stu_name VARCHAR(20) UNIQUE NOT NULL,   # 添加了『唯一约束』+ 非空
	stu_gender CHAR(1) DEFAULT '男',  # 添加了『默认约束』
	email VARCHAR(20) NOT NULL,  # 非空
	age INT CHECK (age BETWEEN 0 AND 100)   # 添加了检测约束
)

修改表 ALTER

ALTER TABLE 表名 ADD/MODIFY/CHANGE/DROP COLUMN 字段名字段类型【字段约束】

# 1.修改表名 RENAME TO
ALTER TABLE stuinfo RENAME TO students;
# 2.添加字段 ADD 
ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL;
DESC students;
# 3.修改字段名 CHANGE
ALTER TABLE students CHANGE COLUMN borndate birthday DATETIME NULL;
# 4.修改字段类型 MODIFY
ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP ;
# 5.删除字段 DROP
ALTER TABLE students DROP COLUMN birthday;

DESC students;

复制表 LIKE/SELECT

通过 LIKE 可以仅复制表的结构；

通过 SELECT 可以结构和数据一起复制，也可仅复制结构（WHERE筛选下即可）

# 仅仅复制表的结构
CREATE TABLE newTable2 LIKE major;
# 复制表的结构+数据
CREATE TABLE newTable3 SELECT * FROM girls.`beauty`;

# 案例：复制employees表中的last_name,department_id,salary字段到新表 emp表，但不复制数据
CREATE TABLE emp 
	SELECT last_name,department_id,salary FROM myemployees.`employees` WHERE 1=2;

删除表 DROP/TRUNCATE/DELETE

# 删除表的结构、数据，释放内存空间，删的一干二净
DROP TABLE IF EXISTS students;

# TRUNCATE 删除表的数据，并释放空间（不可以回滚恢复），但表的定义还在，即表的结构还在
TRUNCATE test;

# DELETE 可以删除指定数据，也可仅删除表的数据，保留表的结构,不释放空间（可以回滚恢复）
DELETE FROM test WHERE age=30 AND country='US';
DELETE * FROM test 或者 DELETE * FROM test;
DELETE FROM test

（3）作业

# 【作业-创建和管理表】
# 1.创建表dep1
CREATE TABLE IF NOT EXISTS dept1(
	id INT(7),
	name VARCHAR(25));
DESC dept1;
DROP table IF EXISTS dept1;
# 2.将表departments中的数据插入到新表dept2
CREATE TABLE dept2 SELECT * FROM departments;
# 3.创建表emp5
CREATE TABLE IF NOT EXISTS emp5(
	id INT(7),
	First_name VARCHAR(25),
	Last_name VARCHAR(25),
	Dept_id INT(7)
);
DESC emp5;
# 4. 将列Last_name的长度增加到50
ALTER TABLE emp5 MODIFY COLUMN Last_name VARCHAR(50);
DESC emp5;
# 5. 根据表employees创建employees2
CREATE TABLE employees2 SELECT * FROM employees WHERE 1=2;
# 6. 删除表emp5
DROP TABLE IF EXISTS emp5;
# 7. 将表employees2重命名为emp5
ALTER TABLE employees2 RENAME TO emp5;
# 8.在表dept1和emp5中添加新列test_column，并检查所作的操作
ALTER TABLE dept1 ADD COLUMN test_cOLUMN VARCHAR(20);

ALTER TABLE dept1 DROP COLUMN test_column;
ALTER TABLE dept1,emp5 ADD COLUMN test_COLUMN VARCHAR(20);  # 会报错

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


# 【作业-约束】
# 1. 向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk）
ALTER TABLE emp2 ADD CONSISTENT my_emp_id_pk PRIMARY KEY(id);
# 2. 向表dept2的id列中添加PRIMARY KEY约束（my_dept_id_pk）
ALTER TABLE dept2 ADD CONSISTENT my_dept_id_pk PRIMARY KEY(id);;
DESC dept2;
# 3. 向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
ALTER TABLE emp2 ADD (dep_id number(10) CONSISTENT emp2_dept_id_fk REFERENCES dept2(id));

14.DML数据操纵语言

（1）数据插入 INSERT

语法：
插入单行：
insert into 表名(字段名1,字段名2 ,…) values (值1，值2,…);
插入多行：
insert into 表名(字段名1,字段名2 ,…) values (值1，值2,…),(值1，值2,…);

特点：
①字段和值列表一一对应,包含类型、约束等必须匹配

②数值型的值，不用单引号;非数值型的值，必须使用单引号

③字段顺序无要求

# 案例1：要求字段和值列表一一对应，且遵循类型和约束的限制
INSERT INTO stuinfo(stuid, stuname, stugender, email, age) VALUES(1,'吴倩','男','wuqian@qq.com',12); 
INSERT INTO stuinfo(stuid, stuname, stugender, email, age) VALUES(6,'李宗盛','女','wuqian@qq.com',45); 

# 案例2：插入空字段
# 1.字段名和值都不写
INSERT INTO stuinfo(stuid, stuname, email) VALUES(5,'齐鱼','qiqin@qq.com'); 
# 2.字段名写上，值使用null
DELETE FROM stuinfo WHERE stuname LIKE '齐%';
INSERT INTO stuinfo(stuid, stuname, email, age) VALUES(5,'齐鱼','qiqin@qq.com', NULL); 

# 案例3：默认字段如何插入
# 1.字段名写上，值使用default
INSERT INTO stuinfo(stuid,stuname,email,stugender) VALUES(7,'齐小鱼','qiqin@qq.com',DEFAULT);   # 性别在创建表格时，默认default为男
# 2.字段名和值都不写
INSERT INTO stuinfo(stuid,stuname,email,majorid) VALUES(7,'齐小鱼','qiqin@qq.com',2); 

# 案例4：可以省略字段列表，默认所有字段
INSERT INTO stuinfo VALUES(8,'林忆莲','女','lin@126.com',12);
INSERT INTO stuinfo VALUES(NULL,'小黄','男','dd@12.com',12);

（2）数据修改 UPDATE

update 表名 set 字段名 = 新值,字段名=新值,… where 筛选条件;

# 案例1：修改年龄<20的专业编号为3号，且邮箱更改为 xx@qq.com
UPDATE stuinfo SET majorid = 3,email='xx@qq.com' WHERE age<20;

（3）数据的删除 DELETE/TRUNCATE

方式1：delete from 表名 where 筛选条件;
方式2：truncate table 表名;

#案例1：删除姓李所有信息
DELETE FROM stuinfo WHERE stuname LIKE '李%';

#案例2：删除表中所有数据
TRUNCATE TABLE stuinfo ;
DELETE * FROM stuinfo;

/*
【面试题】delete和truncate的区别
	1.delete可以添加WHERE条件
  		TRUNCATE不能添加WHERE条件，一次性清除所有数据
	2.truncate的效率较高
	3.如果删除带自增长列的表，
  		使用DELETE删除后，重新插入数据，记录从断点处开始
  		使用TRUNCATE删除后，重新插入数据，记录从1开始
  
  		SELECT * FROM gradeinfo;
  		TRUNCATE TABLE gradeinfo;
  		INSERT INTO gradeinfo(gradename)VALUES('一年级'),('2年级'),('3年级');
  
	4.delete 删除数据，会返回受影响的行数
  	  TRUNCATE删除数据，不返回受影响的行数
  
	5.delete删除数据，可以支持事务回滚
  	  TRUNCATE删除数据，不支持事务回滚	
*/

（4）作业

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);
# 显示表my_employees的结构
DESC my_employees;
# 向my_employees表中插入数据
INSERT INTO my_employees VALUES(1,'patel','Ralph','Rpatel',895);
INSERT INTO my_employees VALUES(2,'Dancs','Betty','Bdancs',860);
INSERT INTO my_employees VALUES(3,'Biri','Ben','Bbiri',1100);
INSERT INTO my_employees VALUES(4,'Newman','Chad','Cnewman',750);
INSERT INTO my_employees VALUES(5,'Ropeburn','Audrey','Aropebur',1550);
# 向User中插入数据
INSERT INTO users VALUES(1,'Rpatl',10);
INSERT INTO users VALUES(2,'Bdnacs',10);
INSERT INTO users VALUES(3,'Bbiri',20);
INSERT INTO users VALUES(4,'Cnewman',30);
INSERT INTO users VALUES(5,'Aropebur',40);
# 将3号员工的last_name修改为“drelxer”
UPDATE my_employees SET last_name = 'drelxer' WHERE ID = 3;
# 将所有工资少于900的员工的工资修改为1000
UPDATE my_employees SET salary = 1000 WHERE salary < 1000;
# 将userid 为Bbiri的user表和my_employees表的记录全部删除
DELETE FROM my_employees,users WHERE users.userid = 'Bbiri' AND my_employees.Id = users.id;
DELETE FROM users WHERE users.userid = 'Bbiri';
DELETE FROM my_employees WHERE Userid = 'Bbiri';

# 删除所有数据
DELETE FROM users;
# 检查所作的修正

# 清空表my_employees
TRUNCATE my_employees;

15.设置自增长列 AUTO_INCREMENT

1、自增长列要求必须设置在一个键上，比如主键或唯一键
2、自增长列要求数据类型为数值型
3、一个表至多有一个自增长列

CREATE TABLE gradeinfo(
	gradeID INT PRIMARY KEY AUTO_INCREMENT,
	gradeName VARCHAR(20)
);

SELECT * FROM gradeinfo;

INSERT INTO gradeinfo VALUES(NULL,'一年级'),(NULL,'2年级'),(NULL,'3年级');
INSERT INTO gradeinfo(gradename)VALUES('一年级'),('2年级'),('3年级');

16.事务

概念：
由一条或多条sql语句组成，要么都成功，要么都失败

特性：ACID，即原子性、一致性、隔离性、持久性

分类：
隐式事务：没有明显的开启和结束标记
比如dml语句的insert、update、delete语句本身就是一条事务
insert into stuinfo values(1,’john’,’男’,’ert@dd.com’,12);
显式事务：具有明显的开启和结束标记
一般由多条sql语句组成，必须具有明显的开启和结束标记

步骤：
取消隐式事务自动开启的功能
1、开启事务
2、编写事务需要的sql语句（1条或多条）
insert into stuinfo values(1,’john’,’男’,’ert@dd.com’,12);
insert into stuinfo values(1,’john’,’男’,’ert@dd.com’,12);
3、结束事务

SHOW VARIABLES LIKE '%auto%'

# 演示事务的使用步骤
# 1、取消事务自动开启
SET autocommit = 0;
# 2、开启事务
START TRANSACTION;
# 3、编写事务的sql语句
# 将张三丰的钱-5000、灭绝的钱+5000
UPDATE stuinfo SET balance=balance-5000 WHERE stuid = 1;
UPDATE stuinfo SET balance=balance+5000 WHERE stuid = 2;
# 4、结束事务

#提交
commit;
#回滚
ROLLBACK;
SELECT * FROM stuinfo;

数据库

MySQL

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

Java基础自查手册上一篇

1.基本规范

2.注释

3.基础查询

（1） 示例

（2）+ 与concat拼接元素

（3）distinct的使用（去除重复元素）

（4）查看表的结构

（5）ifnull(表达式1，表达式2)

4.条件查询

（1）按关系表达式筛选

（2）按逻辑表达式筛选

（3）模糊查询

① LIKE

② IN

③ BETWEEN … AND …

④ is null/is not null

⑤ 几种比较符号

（4）作业题

5.排序查询

（1）示例

一、按单个字段排序

二、按表达式排序

三、按别名排序

四、按函数的结果排序

五、按多个字段排序

六、补充选学：按列数排序

（2） 作业

6.常见函数

（1）字符函数

（2）数学函数

（3）日期函数

（4）流程控制函数

① IF函数

② CASE函数

7.统计函数

（1） 示例

（2）count的补充介绍

（3） 作业

8.分组筛选

（1）简单的分组

（2）可以实现分组前的筛选

（3）可以实现分组后的筛选

（4）可以实现排序

（5）按多个字段分组

（6） 作业

9.连接查询

sql92标准

一、等值连接

二、非等值连接

三、自连接

SQL99语法

🐳 内连接

一、等值连接

二、非等值连接

三、自连接

🐳 外连接(左右连接)

📚 作业

10.子查询

（1）放在where或having后面

（2）放在select后面

（3）放在from后面

（4）放在exists后面

（5）作业

11.分页查询 LIMIT

12.联合查询 UNION

13.DDL-库和表的管理

（1）库的管理

创建数据库 CREATE

删除数据库 DROP

（2）表的管理

创建表 CREATE

修改表 ALTER

复制表 LIKE/SELECT

删除表 DROP/TRUNCATE/DELETE

（3）作业

14.DML数据操纵语言

（1）数据插入 INSERT

（2）数据修改 UPDATE

（3）数据 的删除 DELETE/TRUNCATE

（1）示例

（2）作业

（1）示例

（3）作业

（6）作业

（3）数据的删除 DELETE/TRUNCATE