MySQL笔记
数据库的好处
- 实现数据持久化
- 使用完整的管理系统统一管理，易于查询
数据库的概念
DB
数据库(datebase)：存储数据的“仓库”。他保存了一系列有组织的数据。
DBMS
数据库管理系统(Datebase Management System)。数据库是通过DBMS创建和操作的容器
常见的数据库管理系统：MySQL、Oracle（太贵了）、DB2（适合处理海量数据）、SqlServer（微软的，只能在安装在Windows）等
SQL
结构化查询语言(Structure Query Language)：专门用来与数据库通信的语言。
SQL的优点：
1、不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL
2、简单易学
3、虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。
数据库的特点
1、将数据放到表中，表再放到库中
2、一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
3、表具有一些特性，这些特性定义了数据在表中如何存储，类似 JAVA 中“类”的设计。
4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似 JAVA 中的“属性”
5、表中的数据是按行存储的，每一行类似于JAVA中的“对象”
MySQL产品的特点
- MySQL数据库隶属于MySQLAB公司，总部位于瑞典，08年被sun收购，09年sun被oracle收购。
- 优点：
- 成本低：开放源代码，一般可以免费试用
- 性能高：执行很快
- 简单：很容易安装和使用
DBMS分类
- 基于共享文件系统的DBMS (Access)
- 基于客户机——服务器的DBMS(MySQL、Oracle、SqlServer)
MySQL服务的停止与启动
- 方法一：计算机 -> 管理 -> 服务与应用程序 -> 服务 -> 找到MySQL ->启动或停止
- 方法二：以管理员身份运行cmd ->
net stop mysqlxxx停止 ->net start mysqlxxx启动
进入MySQL方法
方式一 ： 通过mysql自带的客户端 （只限于root用户）
方法二 ： 通过windows的cmd
完整的输入为：mysql -h localhost(主机名) -P3306(端口号) -u(用户名) root -p(密码)
简略的输入为：mysql -u root -p
MySQL的常见命令
show databases; 查看当前所有的库
user 库名; 打开指定的库
show tables; 查看当前库下的表
show tables from 库名; 显示指定库下的表
-- 创建表
create table 表名(
列名 列类型，
列名 列类型，
列名 列类型
)
select database(); 显示当前处于哪个库
desc 表名; 查看表格结构
select * from 表名; 查看表格数据
select version(); 查看MySQL版本
mysql --version 或 mysql -V cmd下查看MySQL版本
MySQL的语法规范
1、不区分大小写，但建议关键字大写，表名、列名小写
2、建议每条命令用 ; 结尾（用\g也可以）
3、每条命令根据需要，可以进行缩进或换行
4、注释
单行注释：#注释文字
单行注释：-- 注释文字
多行注释： /* 注释文字 */
DQL(Data Query Language)
``代表着重号，加不加都可以，当字段和关键字混淆时需要为字段加上着重号
基础查询
#进阶1：基础查询
/*
语法：
select 查询列表
from 表名;
特点：
1、查询列表可以是：表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格
*/
USE myemployees;
#1.查询表中的单个字段
SELECT last_name FROM employees;
#2.查询表中的多个字段
SELECT last_name,salary,email FROM employees;
#3.查询表中的所有字段
SELECT
`employee_id`,
`first_name`,
`last_name`,
`phone_number`,
`salary`
FROM
employees ;
#方式二
SELECT * FROM employees;
#4.查询常量值
#注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
SELECT 100;
SELECT 'john'; #''和""不加以区分，统一使用''
#5.查询表达式
SELECT 100*98;
#6.查询函数
SELECT VERSION();
#7.起别名
/*
1.便于理解
2.如果要查询的字段有重名的情况，使用别名可以区分开来
*/
#方式一：AS
SELECT 100%98 AS result;
SELECT last_name AS 姓, first_name AS 名 FROM employees;
#方式二：空格
SELECT last_name 姓, first_name 名 FROM employees;
#案例：查询salary，显示结果为out put
SELECT salary AS "out put" FROM employees; # 也可以用''，建议用""
#8.去重
#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;
#9.+的作用
/*
java中的+：
1.运算符，两个操作数都为数值型
2.连接符，只要有一个操作数为字符串
mysql中的+：
仅仅只有一个功能：运算符
select 100+90; 两个操作数都为数值型，则做加法运算
select '123'+90; 其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算
select 'john'+90; 如果转换失败，则将字符型数值转换为0
select null+10; 只要其中一方为null，则结果肯定为null
*/
#案例：查询员工名和员工姓连接成一个字段，并显示为 姓名
SELECT CONCAT('a','b','c') AS result;
SELECT
CONCAT(last_name,first_name) AS 姓名
FROM
employees ;
#题目
#显示表dapartments的结构，并查询其中的全部数据
DESC departments;
SELECT * FROM departments;
#显示出表employees的全部序列，各个列之间用逗号连接，列头显示成OUT_PUT
#出现null的解决方法
SELECT
IFNULL(commission_pct,0) AS 奖金率,
commission_pct
FROM
employees;
#---------------------------------------
SELECT
CONCAT(`first_name`,',',`last_name`,',',`email`,',',`phone_number`,',',`job_id`,',',`salary`,',',IFNULL(commission_pct,0),',',`manager_id`,',',`department_id`,',',`hiredate`) AS out_put
FROM
employees;
条件查询
#进阶2：条件查询
/*
语法：
select
查询列表
from
表名
where
筛选条件;
分类：
一、按条件表达式筛选
简单条件运算符：> < = <>(和!=一样) >= <=
二、按逻辑表达式筛选
逻辑运算符：
作用：用于连接条件表达式
&& || !
and or not
三、模糊查询
like
between and
in
is null
*/
#一、按条件表达式筛选
#案例1：查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE
salary>12000;
#案例2：查询部门编号不等于90号的员工名和部门编号
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id <> 90;
#二、按逻辑表达式筛选
#案例1：查询工资在10000和20000之间的员工名、工资以及奖金
SELECT
first_name,
salary,
commission_pct
FROM
employees
WHERE salary >= 10000
AND salary <= 20000 ;
#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT
*
FROM
employees
WHERE
NOT(department_id>=90 AND department_id<=110) OR salary>15000;
#三、模糊查询
/*
like
between and
in
is null | is not null
*/
#1.like
/*
特点：
1.一般和通配符搭配使用
通配符：
% 任意多个字符，包含0个字符
_ 任意单个字符
*/
#案例1：查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%'; # %表示通配符
#案例2：查询员工名中第三个字符为n，第五个字符为l的员工名和工资
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE '__n_l%';
#案例3：查询员工名中第二个字符为_的员工名
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';
#2.between and
/*
1.使用between and可以提高语句的简洁度
2.包含临界值
3.两个临界值不要调换顺序
*/
#案例1： 查询员工编号在100-120之间的员工信息
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 120;
#3.in
/*
含义：判断某字段的值是否属于in列表中的一项
特点：
1.使用in提高语句简洁度
2.in列表的值类型必须统一或兼容 '123'和123兼容
3.
*/
#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN('IT_PROG','AD_VP','AD_PRES');
#4.is null
/*
=或<>不能用于判断null
is null 和 is not null可以判断
*/
#案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NOT NULL;
#安全等于 <=>
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
#案例2：查询工资为12000的员工信息
SELECT
last_name,
salary
FROM
employees
WHERE
salary <=> 12000;
/*
is null 与 <=>
is null:仅仅可以判断NULL值，可读性较高，建议使用
<=> :既可以判断NULL值，又可以判断普通的数值，可读性较低
*/
#题目
#一、查询没有奖金，且工资小于18000的salary,last_name
SELECT
salary,
last_name
FROM
employees
WHERE
commission_pct IS NULL AND salary < 18000;
#二、查询employees表中，job_id不为'IT'或者工资为12000的员工信息
SELECT
*
FROM
employees
WHERE
job_id <> 'IT' OR salary = 12000;
#经典面试题
/*
试问：select * from employees;和
select * from employees where commission_pct like '%%' and last_name like '%%';
结果是否一样？并说明原因
*/
#不一样！如果判断的字段有null就不一样
#select * from employees where commission_pct like '%%' or last_name like '%%';
#将and 改成 or就一样了
#拼接字符
concat(String1,String2,Stirng3)
#判断某字段或表达式是否为null，如果为null返回指定的值，否则返回原来的值
ifnull(字段,指定的值)
#判断某字段或表达式是否为null,如果是则返回1，否则返回0
isnull(字段)
排序查询
#进阶3：排序查询
/*
语法：
select 查询列表
from 表
[where 筛选条件]
order by 排序列表 [asc | desc]
特点：
1、asc代表的是升序，desc代表的是降序
2、如果不写，默认为asc升序
3、order by 字句中可以支持单个字段、多个字段、表达式、函数、别名
4、order by 字句一般是放在查询语句的最后面，limit字句除外
*/
#案例1：查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY salary ASC;
SELECT * FROM employees ORDER BY salary;
#案例2：查询部门编号>=90的员工信息，按入职时间先后进行排序
SELECT *
FROM employees
WHERE department_id >= 90
ORDER BY hiredate ASC;
#案例3：【按表达式排序】按年薪的高低显示员工信息和年薪
SELECT *,salary * 12 * ( 1 + IFNULL(commission_pct,0) ) AS 年薪
FROM employees
ORDER BY salary * 12 * ( 1 + IFNULL(commission_pct,0) ) DESC;
#案例4：【按别名排序】按年薪的高低显示员工信息和年薪
SELECT *,salary * 12 * ( 1 + IFNULL(commission_pct,0) ) AS 年薪
FROM employees
ORDER BY 年薪 DESC;
#案例5：按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
#案例6：查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;
#题目1：查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name,departmeny_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC,last_name ASC;
#题目2：查询工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
#题目3：查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT *,LENGTH(email)
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;
常见函数
日期函数
str_to_date：将日期格式的字符转换成指定格式的日期
str_to_date('9-13-1999','%m-%d-%Y')
date_format：将日期转换成字符
date_format('2018/6/6'),'%Y年%m月%d日'
#进阶4：常见函数
/*
概念：类似java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处： 1、隐藏了实现细节
2、提高了代码的重用性
调用： select 函数名(实参列表) [from 表];X
特点： 1、叫什么（函数名）
2、干什么（函数功能）
分类： 1、单行函数
如concat、length、ifnull等
2、分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
常见函数：
字符函数：
length 返回字符长度
concat 拼接字符
substr 截取字符
instr 返回字符串第一次出现的索引
trim 用来移除掉一个字串中的字头或字尾
upper 变大写
lower 变小写
lpad 左填充
rpad 右填充
replace 替换
数学函数：
round 四舍五入
ceil 向上取整
floor 向下取整
truncate 截断数字
mod 取余
日期函数：
now
curdate
curtime
year
month
monthname
day
hour
minute
second
str_to_date
date_formate
其他函数：
version() 当前数据库服务器的版本
database() 当前打开的数据库
user() 当前用户
password('字符') 返回该字符的密码形式
md5('字符') 返回该字符的md5加密形式
流程控制函数:
if(条件表达式，表达式1，表达式2)：成立返回1，不成立返回2
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
*/
#一、字符函数
#length 获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('无敌是多么寂寞'); # utf-8 一个字母占一个字节 一个汉字三个字节
SHOW VARIABLES LIKE '%char%'
#二、concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees;
#三、upper、lower
SELECT UPPER('john');
SELECT LOWER('john');
#案例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name), '_', LOWER(first_name)) 姓名 FROM employees;
#四、substr、substring
#注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',1) out_put;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
#案例：姓名中首字符大写，其他字符小写，然后用_拼接
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) AS out_put
FROM employees;
#五、instr 返回字串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;
#六、TRIM ( [ [位置] [要移除的字串] FROM ] 字串)
#[位置] 的可能值为 LEADING (起头), TRAILING (结尾), or BOTH (起头及结尾)。
#这个函数将把 [要移除的字串] 从字串的起头、结尾，或是起头及结尾移除。
#如果我们没有列出 [要移除的字串] 是什么的话，那空白就会被移除。
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
SELECT TRIM('a' FROM 'aaaaaaaaaa张a翠a山aaaaaaaaaaaaaaa') AS out_put;
#七、lpad 用指定的字符实现 左填充 指定长度
SELECT LPAD('殷素素',5,'*') AS out_put;
#八、rpad 用指定的字符实现 右填充 指定长度
SELECT RPAD('殷素素',5,'*') AS out_put;
#九、replace 替换
SELECT REPLACE('张无忌爱上了周芷若周芷若周芷若','周芷若','赵敏') AS out_put;
#二、数学函数
#round 四舍五入
SELECT ROUND(-1.46);
SELECT ROUND(1.567,2);
#ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.050);
#floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99);
#truncate 截断
SELECT TRUNCATE(0.65321,1);
#mod 取余
SELECT MOD(-10,3);
SELECT 10%3;
#三、日期函数
#now 返回当前系统日期+时间
SELECT NOW();
#curdate 返回当前系统日期
SELECT CURDATE();
#curtime 返回当前时间
SELECT CURTIME();
#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) AS 年;
SELECT YEAR('1998-1-1') AS 年;
SELECT YEAR(hiredate) AS 年 FROM employees;
SELECT MONTH(NOW()) AS 月;
SELECT MONTHNAME(NOW()) AS 月;
#str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
#查询有奖金的员工名和入职日期（xx月/xx日 xx年）
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;
#四、其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
#五、流程控制函数
#1. if函数： if else的效果
SELECT IF(10<5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金 呵呵','有奖金 嘻嘻')
FROM employees;
#2. case函数的使用一：switch case 的效果
/*
switch(变量或表达式){
case 常量1: 语句1; break;
....
default: 语句n; break;
}
mysql中
case 要判断的字段或表达式
when 常量一 then 要显示的值1或语句1;
when 常量二 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
*/
/*案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
*/
SELECT salary AS 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
#3.case函数的使用二：类似于 多重if
/*
java中：
if (条件1) {
语句1:
}else if(条件2){
语句2:
}else{
语句n;
}
mysql中:
case
when 条件1 then 要显示的值1(或语句1;)
when 条件2 then 要显示的值2(或语句2;)
...
else 要显示的值n(或语句n;)
end
*/
#案例；查询员工的工资情况
/*
如果工资>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;
#测试
#1.显示系统时间（注： 日期+时间）
SELECT NOW();
#2.查询员工号，姓名，工资，以及工资提高20%后的结果(new salary)
SELECT employee_id,last_name,salary,salary*1.2 AS "new salary"
FROM employees;
#3.将员工的姓名按首字母排序，并写出姓名的长度(length)
SELECT last_name,LENGTH(last_name) AS 长度,SUBSTR(last_name,1,1) AS 首字母
FROM employees
ORDER BY 首字母;
#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) AS "Dream Salary"
FROM employees
WHERE salary=24000;
#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 AS job,
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'
END AS Grade
FROM employees
WHERE job_id='AD_PRES';
分组函数
#二、分组函数
/*
功能：用于统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数
特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配使用,实现去重的统计
4、count函数
count(字段)：统计该字段非空值的个数
count(*)：统计结果集的行数
count(1)也可用于统计结果集的行数
5、和分组函数一同查询的字段要求是group by后的字段
*/
#1、简单的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT
SUM(salary) AS 和,
ROUND(AVG(salary),2) AS 平均,
MAX(salary) AS 最高,
MIN(salary) AS 最低,
COUNT(salary) AS 个数
FROM
employees ;
#2、参数支持哪些类型
SELECT SUM(last_name),AVG(last_name) FROM employees;
SELECT SUM(hiredate), AVG(hiredate) FROM employees;
SELECT MAX(last_name),MIN(last_name),COUNT(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(last_name) FROM employees;
#3、是否忽略null
SELECT SUM(commission_pct), AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107
FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT MAX(commission_pct), MIN(commission_pct) FROM employees;
#4、和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
#5、count函数的详细介绍
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;
效率：
MYISAM 存储引擎下， COUNT(*)的效率最高
INNODB 存储引擎下， COUNT(*)和count(1)的效率差不多，比count(字段)要高一些
#6、和分组函数一同查询的字段有限制，要求是group by后出现的字段
SELECT AVG(salary),employee_id FROM employees;
#测试
#一、查询公司员工工资的最大值、最小值、平均值、总和
SELECT MAX(salary),MIN(salary),ROUND(AVG(salary),2),SUM(salary)
FROM employees;
#二、查询员工表中的最大入职时间和最小入职时间的相差天数(difference)
SELECT DATEDIFF('2021-3-18','2000-4-6');
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) AS difference
FROM employees;
#三、查询部门编号为90的员工个数
SELECT COUNT(*)
FROM employees
WHERE department_id=90;
分组查询
可以用GROUP BY子句将表中的数据分成若干组
SELECT column,group_function(colunmn)
from table
[where condition]
[group by group_by_expression]
[order by column];
明确：WHERE一定放在FROM后面
#进阶5：分组查询
/*
语法：
SELECT 分组函数,列（要求出现在group by的后面）
FROM 表
[WHERE 筛选条件]
[GROUP BY 分组的列表]
[ORDER BY 子句];
注意：
查询列表比较特殊，要求是分组函数和group by后出现的字段
特点：
1、分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by 子句的前面 where
分组后筛选 分组后的结果集 group by 子句的后面 having
①分组函数做条件肯定是放在 having 子句中
②能用分组前筛选的，就优先考虑使用分组前筛选
2、group by 子句支持单个字段，多个字段分组（用，隔开，无顺序要求），表达式或函数
3、也可以添加排序，放在整个分组查询最后
*/
#引入：查询每个部门的平均工资
SELECT AVG(salary) FROM employees;
#案例1：查询每个工种的最高工资
#简单的分组查询
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
#案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
#添加筛选条件
#案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#案例2：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
#添加复杂的筛选条件
#案例1：查询哪个部门的员工个数>2
#1.查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
#2.根据1的结果进行筛选，查询哪个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2;
#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
SELECT manager_id,MIN(salary) AS 最低工资
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING 最低工资>5000;
#按表达式或函数分组
#案例；按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*) AS 员工个数,LENGTH(last_name) AS 姓名长度
FROM employees
GROUP BY 姓名长度
HAVING 员工个数>5;
#按多个字段分组
#案例：查询每个部门每个工种的员工的平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
#添加排序
#案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示
SELECT department_id,job_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC;
#测试
#1.查询各job_id的员工工资的最大值、最小值、平均值、总和，并按job_id升序
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id;
#2.查询员工最高工资和最低工资的差距(difference)
SELECT MAX(salary)-MIN(salary) AS difference
FROM employees;
#3.查询每个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(salary) AS 最低工资,manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING 最低工资>=6000;
#4.查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;
#5.查询各个job_id的员工人数
SELECT COUNT(*) AS 员工个数,job_id
FROM employees
GROUP BY job_id;
连接查询
#进阶6：连接查询
/*
含义：又称多表查询，当查询的字段来自多个表时，就会用到连接查询
笛卡尔乘积现象：表1有m行，表2有n行，结果m*n行
发生原因：没有有效的连接条件，导致多个表所有行实现完全连接
如何避免：添加有效的连接条件
分类：
按年代分类：
sql92标准：仅仅支持内连接(mysql)，也支持一部分外连接(oracle、sqlserver)
sql99标准[推荐]：支持内连接+外连接（左外和右外）+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接(mysql不支持)
交叉连接
*/
SELECT * FROM beauty;
SELECT * FROM boys;
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id;
#一、sql92标准
#1、等值连接
/*
①多表等值连接的结果为多表的交集部分
②n表连接至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/
#案例1：查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.`boyfriend_id` = boys.`id`;
#案例2：查询员工名和对应的部门名
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 AS e,jobs AS j
WHERE e.`job_id` = j.`job_id`;
#3、两个表的顺序是否可以调换
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM jobs AS j,employees AS e
WHERE e.`job_id` = j.`job_id`;
#4、可以加筛选？
#案例：查询有奖金的员工名、部门名
SELECT last_name AS 员工名,department_name AS 部门名,commission_pct
FROM employees AS e,departments AS d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL;
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments AS d,locations AS l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';
#5、可以加分组？
#案例1；查询每个城市的部门个数
SELECT COUNT(*) AS 部门个数, city
FROM departments AS d,locations AS l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;
#案例2：查询有奖金的 每个部门的部门名 和 部门的领导编号 和 该部门的最低工资
SELECT department_name AS 部门名,d.manager_id AS 领导编号,MIN(salary) AS 最低工资
FROM departments AS d,employees AS e
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY department_name,d.manager_id;
#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees AS e,jobs AS j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) 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`
AND city LIKE '%s%'
ORDER BY department_name DESC;
#2、非等值连接
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`
AND j.`grade_level` = 'A';
SELECT * FROM job_grades;
/*
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);
*/
#3、自连接
#案例：查询 员工名和上级的名称
SELECT e.employee_id AS 员工编号,e.last_name AS 员工名,m.employee_id AS 领导编号,m.last_name AS 领导名
FROM employees AS e, employees AS m
WHERE e.`manager_id`=m.`employee_id`;
#测试1
一、显示员工表的最大工资，工资平均值
SELECT MAX(salary),AVG(salary)
FROM employees;
二、查询员工表的employee_id,job_id,last_name,按department_id降序,salary升序
SELECT employee_id,job_id,last_name,department_id,salary
FROM employees
ORDER BY department_id DESC,salary ASC;
三、查询员工表的job_id中包含a和e的，并且a在e的前面
SELECT job_id
FROM employees
WHERE job_id LIKE '%a%e%' ;
四、已知表student,里面有id(学号),NAME,gradeId(年级编号)
已知表grade,里面有id(年级编号),NAME(年级名）
已知表result，里面有id,score,studentNo(学号)
要求查询姓名、年级名、成绩
SELECT s.name,g.name,r.score
FROM student AS s,grade AS g,result AS r
WHERE s.gradeId=g.id
AND s.id=r.studentNo;
五、显示当前日期，以及去前后空格，截取子字符串的函数
SELECT NOW();
SELECT TRIM('a' FROM ' aaa ');
SELECT SUBSTR(str,startIndex);
SELECT SUBSTR(str,startIndex,LENGTH);
#测试2
#1.显示所有员工的姓名，部门号和部门名称
SELECT last_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;
#2.查询90号部门员工的job_id和90号部门的location_id
SELECT e.job_id,d.location_id, d.department_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;
#3.查询所有有奖金的员工的last_name,department_name,location_id,city
SELECT e.last_name,d.department_name,l.location_id,l.city
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id`= l.`location_id`
AND e.`commission_pct`IS NOT NULL;
#4.查询city在Toronto工作的员工的last_name,job_id,department_id,department_name
SELECT e.last_name,e.job_id,d.department_id,d.department_name
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND l.`city`='Toronto';
#5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name,job_title,MIN(salary)
FROM employees e,departments d, jobs j
WHERE e.`department_id` = d.`department_id`
AND e.`job_id` = j.`job_id`
GROUP BY department_name,job_title;
#6.查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*) AS 部门个数
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,employees m
WHERE e.manager_id = m.employee_id
AND e.last_name = 'kochhar';
#二、sql99语法
/*
语法：
select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]
分类：
内连接:inner
外连接
左外:left [outer]
右外:right [outer]
全外:full[outer]
交叉连接:cross
*/
#一、内连接
/*
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件
分类：
等值
非等值
自连接
特点：
1.添加排序、分组、筛选
2.inner可以省略
3.筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
4.inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
*/
#1、等值连接
#案例1：查询员工名、部门名（调换位置）
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
#案例2：查询名字中包含e的员工名和工种名（筛选）
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE e.last_name LIKE '%e%';
#案例3：查询部门个数>3的城市名和部门个数(分组+筛选)
SELECT l.`city`,COUNT(*) 部门个数
FROM locations l
INNER JOIN departments d
ON d.`location_id`=l.`location_id`
GROUP BY l.`city`
HAVING COUNT(*)>3;
#案例4：查询部门员工个数>3的部门名和员工个数，并按个数降序（排序）
SELECT department_name,COUNT(*) AS 员工个数
FROM departments d
INNER JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
#案例5：查询员工名、部门名、工种名，并按部门名降序
SELECT last_name,department_name,job_title
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`
ORDER BY department_name DESC;
#2.非等值连接
#查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.lowest_sal AND g.highest_sal;
#查询人数>20的工资级别，并且按工资级别降序
SELECT salary,grade_level,COUNT(*)
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
#3.自连接
#查询员工的名字和上级的名字
SELECT e.last_name 员工名,m.last_name 上级名
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`;
#查询姓名中包含字符K的员工名字和上级的名字
SELECT e.last_name 员工名,m.last_name 上级名
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`
WHERE e.`last_name` LIKE '%k%';
#二、外连接
/*
应用场景：用于查询一个表中有，另一个表中没有的记录
特点：
1、外连接的查询结果为主表中所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
2、左外连接，left join 左边的是主表
右外连接，right join 右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接 = 内连接的结果 + 表1中有但表2没有的 + 表2中有但表1没有的
*/
#引入：查询男朋友不在boys表的女生名
SELECT * FROM beauty;
SELECT * FROM boys;
#左外连接
SELECT b.name,bo.`boyName`
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;
#右外连接
SELECT b.name,bo.`boyName`
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.id IS NULL;
#案例1：查询哪个部门没有员工
#左外
SELECT department_name,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
WHERE e.`employee_id` IS NULL;
#右外
SELECT department_name,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`employee_id` IS NULL;
#全外
USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id`= bo.id;
#交叉连接(笛卡尔乘积)
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
#sql92 vs sql99
/*
功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高
*/
#外连接题目
#1.查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，则用null填充
SELECT b.`id`,b.`name`,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.`id`>3;
#2.查询哪个城市没有部门
SELECT l.`city`
FROM locations l
LEFT OUTER JOIN departments d
ON l.`location_id`=d.`location_id`
WHERE d.`department_id` IS NULL;
#3.查询部门名为SAL或IT的员工信息
SELECT d.`department_name`,e.*
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE d.`department_name` IN('SAL','IT');
#内连接查询，比外连接少2条记录（主表与从表不匹配的项）
SELECT d.`department_name`,e.*
FROM employees e
INNER JOIN departments d
ON e.`department_id`= d.`department_id`
WHERE d.`department_name` IN('SAL','IT');
七种连接方式
子查询
#进阶7：子查询
/*
含义：
出现在其他语句中的select语句，成为子查询或内查询
内部嵌套其他select语句的查询，称为主查询或外查询
分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面：★
标量子查询（单行）√
列子查询 （多行）√
行子查询
exists后面（相关子查询）：
所有子查询都可以
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集可以有一行多列/多列多行）
表子查询（结果集一般为多行多列）
*/
#一、where或having后面
#特点：
#①子查询放在小括号内
#②子查询一般放在条件的右侧
#③标量子查询，一般搭配着单行操作符使用
单行操作符：> < >= <= = <>
# 列子查询，一般搭配着多行操作符使用
多行操作符： IN/NOT IN 等于列表中的任意一个
ANY/ SOME 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较
#④子查询的执行优先于主查询，主查询的条件用到了子查询的结果
#1、标量子查询（单行子查询）
#案例1：谁的工资比Abel高
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = '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及其最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
#2、列子查询（多行子查询）
#案例1：返回 location_id 是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT DISTINCT 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 salary < ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';
#或
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';
#案例2：返回其他工种中比job_id为'IT_PROG'工种所有工资低的员工的：工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';
#或
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (
SELECT MIN(salary)
FROM employees
WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';
#3、行子查询（结果集一行多列或多行多列）用的很少
#案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE employee_id = (
SELECT MIN(employee_id)
FROM employees
)AND salary = (
SELECT MAX(salary)
FROM employees
);
SELECT *
FROM employees
WHERE (employee_id,salary) = (
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
#二、select 后面
/*
仅仅支持标量子查询
*/
#案例：查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) AS 员工个数
FROM departments d;
SELECT d.*,COUNT(*)
FROM departments d
INNER JOIN employees e
ON e.`department_id`=d.`department_id`
GROUP BY department_id;
#案例2：查询员工号=102的部门名
SELECT (
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id=102
) AS 部门名;
#三、from后面
/*
将子查询结果充当一张表，要求必须起别名
*/
#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*, g.grade_level
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) AS ag_dep
INNER JOIN job_grades AS g
ON ag_dep.ag BETWEEN g.lowest_sal AND g.highest_sal;
#四、exists后面的子查询（相关子查询）
/*
语法：
exists(完整的查询语句)
结果：
1或0
*/
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=30000);
#案例1：查询有员工的部门名
#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN (
SELECT department_id
FROM employees
);
SELECT department_name,d.`department_id`
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
#案例2：查询没有女朋友的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.`id` NOT IN(
SELECT boyfriend_id
FROM beauty
);
#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE b.`boyfriend_id`=bo.`id`
);
#1、查询和Zlotkey相同部门的员工姓名和工资
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 AVG(salary)
FROM employees
);
#3、查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
SELECT AVG(salary) AS 平均工资,department_id
FROM employees
GROUP BY department_id
) AS ag
ON e.`department_id`=ag.department_id
WHERE e.`salary`>ag.平均工资;
#4、查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
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 =ANY(
SELECT DISTINCT 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 = 'K_ing'
);
#7、查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
SELECT CONCAT(first_name,last_name) "姓.名"
FROM employees
WHERE salary=(
SELECT MAX(salary)
FROM employees
);
分页查询
#进阶8：分页查询 ★
/*
应用场景：当我们要显示的数据 ，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
[join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段]
limit offset,size;
offset 要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
①limit语句放在查询语句的最后
②公式：
要显示的页数page,每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;
*/
#案例1：查询前五条员工信息
SELECT *
FROM employees
LIMIT 0,5;
#如果起始索引是0，可以省略
SELECT *
FROM employees
LIMIT 5;
#案例2：查询第11条-第25条
SELECT *
FROM employees
LIMIT 11,15;
#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
#复习小测试
/*
已知表stuinfo
id 学号
name 姓名
email 邮箱 john@126.com
gradeId 年级编号
sex 性别 男 女
age 年龄
已知表 grade
id 年级编号
gradeName 年级名称
*/
#一、查询所有学员的邮箱的用户名（注：邮箱中@前面的字符）
SELECT SUBSTR(email,1,INSTR(email,'@')-1) 用户名
FROM stuinfo;
#二、查询男生和女生的个数
SELECT COUNT(*) AS 个数,sex
FROM stuinfo
GROUP BY sex;
#三、查询年龄>18岁的所有学生的姓名和年级名称
SELECT NAME,gradeName
FROM stuinfo s
INNER JOIN grade g
ON s.gradeId = g.id
WHERE age>18;
#四、查询哪个年级的学生最小年龄>20岁
SELECT MIN(age),gradeId
FROM stuinfo
GROUP BY gradeId
HAVING MIN(age)>20;
#五、试说出查询语句中涉及到的所有关键字，以及执行先后顺序
SELECT 查询列表
FROM 表
连接类型 JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后的筛选
ORDER BY 排序列表
LIMIT 起始索引/偏移，条目数；
执行顺序： FROM->JOIN->ON->WHERE->GROUP BY->HAVING->SELECT->ORDER BY->LIMIT
子查询经典案例
#1、查询工资最低的员工信息：last_name,salary
SELECT last_name,.salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#2、查询平均工资最低的部门信息
#方式一：
SELECT d.*
FROM departments d
WHERE d.`department_id`=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
)
);
#方式二
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
);
#3、查询平均工资最低的部门信息和该部门的平均工资
SELECT d.*,ag
FROM departments d
JOIN(
SELECT department_id,AVG(salary) ag
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
) ag_dep
ON d.`department_id`=ag_dep.department_id;
#4、查询平均工资最高的job信息
SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
);
#5、查询平均工资高于公司平均工资的部门有哪些？
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM employees
);
#6、查询公司中所有manager的详细信息
SELECT *
FROM employees
WHERE employee_id IN (
SELECT manager_id
FROM employees
);
#7、各个部门中 最高工资中最低的那个部门的最低工资是多少
SELECT MIN(salary)
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
);
#8、查询平均工资最高的部门的manager的详细信息：last_name,department_id,email,salary
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id = (
SELECT manager_id
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
)
);
复习题
#一、查询每个专业的学生人数
SELECT majorid,COUNT(*)
FROM student
GROUP BY majorid;
#二、查询参加考试的学生中，每个学生的平均分、最高分
SELECT AVG(score),MAX(score),studentno
FROM result
GROUP BY studentno;
#三、查询姓张的学生中最低分大于60的学生的学号、姓名
SELECT s.`studentname`,s.`studentno` ,MIN(score)
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
WHERE s.`studentname` LIKE '张%'
GROUP BY studentno
HAVING MIN(score)>60;
#四、查询每个专业生日在‘1988-1-1’后的学生姓名、专业名称
SELECT studentname, majorname,borndate
FROM student s
JOIN major m
ON s.`majorid`=m.`majorid`
WHERE DATEDIFF(s.`borndate`,'1988-1-1')>0;
#五、查询每个专业的男生人数和女生人数分别是多少
#方式一：
SELECT COUNT(*),sex,majorid
FROM student
GROUP BY sex,majorid;
#方式二：
SELECT s.majorid,
(SELECT COUNT(*) FROM student WHERE sex='男' AND majorid=s.`majorid`) 男,
(SELECT COUNT(*) FROM student WHERE sex='女' AND majorid=s.`majorid`) 女
FROM student s
GROUP BY majorid;
#六、查询专业和张翠山一样的学生的最低分
SELECT MIN(score)
FROM result
WHERE studentno IN(
SELECT studentno
FROM student
WHERE majorid=(
SELECT majorid
FROM student
WHERE studentname='张翠山'
)
);
#七、查询大于60分的学生的姓名、密码、专业名
SELECT studentname,loginpwd,majorname
FROM student s
INNER JOIN major m ON s.`majorid`=m.`majorid`
JOIN result r ON s.`studentno`=r.`studentno`
WHERE r.`score`>60;
#八、按邮箱位数分组，查询每组的学生个数
SELECT COUNT(*),LENGTH(email)
FROM student
GROUP BY LENGTH(email);
#九、查询学生名、专业名、分数
SELECT studentname,majorname,score
FROM student s
INNER JOIN major m ON s.`majorid`=m.`majorid`
LEFT JOIN result r ON s.`studentno`=r.`studentno`;
#十、查询哪个专业没有学生，分别用左连接和右连接实现
SELECT m.`majorid`,m.`majorname`,studentno
FROM major m
LEFT JOIN student s ON m.`majorid`=s.`majorid`
WHERE studentno IS NULL;
SELECT m.`majorid`,m.`majorname`,studentno
FROM student s
RIGHT JOIN major m ON m.`majorid`=s.`majorid`
WHERE studentno IS NULL;
#十一、查询没有成绩的学生人数
SELECT COUNT(*)
FROM student s
LEFT JOIN result r
ON s.`studentno`=r.`studentno`
WHERE r.score IS NULL;s
联合查询
#进阶9：联合查询
/*
union 联合 合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
...
应用场景：
要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：
1、要求多条查询语句的查询列表是一致的！
2、多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用 union all 可以包含重复项
*/
#引入的案例：查询部门编号>90或邮箱中包含a和员工信息
SELECT *
FROM employees
WHERE email LIKE '%a%'
OR department_id>90;
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
#案例：查询中国用户中男性的用户信息 以及 外国用户男性的用户信息
SELECT id,cname,csex FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tName,tGender FROM t_ua WHERE tGender='male';
DML(Data Manipulation Language)
#DML语言
/*
数据操作语言：
插入：insert
修改：update
删除：delete
*/
#一、插入语句
#方式一：
/*
语法：
insert into 表名(列名, ....)
values(值1, .....);
特点：
1、要求值的类型和字段的类型要一致或兼容
2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致
但必须保持值和字段一一对应
3、假如表中有可以为NULL的字段，可以通过以下两种方式插入null值
①字段和值都省略
②字段写上，值使用null
4、字段和值的个数必须一致
5、字段名可以省略，默认所有列
*/
SELECT * FROM beauty;
#1、插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','18989898989',NULL,2);
#2、不可以为Null的列必须插入值。可以为Null的列如何插入值？
#方式一：
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','18989898989',NULL,2);
#方式二：
INSERT INTO beauty(id,NAME,sex,borndate,phone,boyfriend_id)
VALUES(14,'金星','女','1990-4-23','19889898989',9);
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'古力娜扎','女','13889898989');
#3、列的顺序是否可以调换
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('蒋欣','女',16,'110');
#4、列数和值的个数必须一致
INSERT INTO beauty(NAME,sex,id,phone,boyfriend_id)
VALUES('关晓彤','女',17,'110');
#5、可以省略列名，默认所有列
INSERT INTO beauty()
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);
#方式二：
/*
insert into 表名
set 列名=值，列名=值, ....
*/
#1、
INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';
#两种方式的区别：
#1、方式一支持插入多行,方式二不支持
INSERT INTO beauty
VALUES(20,'唐艺昕1','女','1990-4-23','18989898989',NULL,2),
(21,'唐艺昕2','女','1990-4-23','18989898989',NULL,2),
(22,'唐艺昕3','女','1990-4-23','18989898989',NULL,2);
#2、方式一支持子查询，方式二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','123456';
INSERT INTO beauty(id,NAME,phone)
SELECT id,boyName,'123456789'
FROM boys WHERE id<3;
#二、修改语句
/*
1、修改单表中的记录★
语法：
update 表名
set 列=新值,列=新值, ...
[where 筛选条件];
执行顺序：update -> where -> set
2、修改多表的记录[补充]
sql92语法：
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;
sql99语法：
update 表1 别名
inner | left | right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;
*/
#1、修改单表的记录
#案例1：修改beauty表中姓唐的女神的电话为13883388337
UPDATE beauty
SET phone='13883388337'
WHERE NAME LIKE '唐%';
#案例2：修改boys表中id为2的名称为张飞，魅力值为10
UPDATE boys
SET boyName='张飞',userCP=10
WHERE id=2;
#2、修改多表的记录
#案例1：修改张无忌的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='114'
WHERE bo.`boyName`='张无忌';
#案例2：修改没有男朋友的女神的男朋友编号都为2号
UPDATE boys bo
RIGHT JOIN beauty b
ON b.`boyfriend_id`=bo.`id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;
#三、删除语句
/*
方式一：delete
语法：
1、单表的删除 ★
delete from 表名 [where 筛选条件][limit ];
2、多表的删除[补充]
sql92语法：
delete 表1的别名,表2的别名
from 表1 别名, 表2 别名
where 连接条件
and 筛选条件;
sql99语法：
delete 表1的别名,表2的别名
from 表1 别名
inner | left | rigth join 表2 别名 on 连接条件
[where 筛选条件];
方式二：truncate
语法：truncate table 表名;
*/
SELECT * FROM beauty;
#方式一：delete
#1、单表的删除
#案例1：删除手机号以9结尾的女神信息
DELETE FROM beauty
WHERE phone LIKE '%9';
#2、多表的删除
#案例1：删除张无忌的女朋友的信息
DELETE b
FROM boys bo
INNER JOIN beauty b
ON bo.id = b.boyfriend_id
WHERE bo.boyName='张无忌';
#案例2：删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM boys bo
INNER JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
WHERE bo.`boyName`='黄晓明';
#方式二：truncate语句
#案例1：将魅力值>100的男神信息删除
TRUNCATE TABLE boys;
#delete和truncate的区别
/*
1、delete 可以加where条件,TRUNCATE 不能加
2、truncate 效率高
3、假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据，自增长列的值从1开始
4、truncate 删除没有返回值,delete删除返回受影响的行数
5、truncate 删除不能回滚，delete删除可以回滚
*/
SELECT * FROM boys;
DELETE FROM boys;
INSERT INTO boys(boyName,usercp)
VALUES('刘备',100),('张飞',200),('关云长',300);
TRUNCATE TABLE boys;
案例
#1、运行以下脚本创建表my_employees
USE myemployees;
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表中插入下列数据
#方式一：
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdances',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
DELETE FROM my_employees;
#方式二：
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION
SELECT 2,'Dancs','Betty','Bdances',860 UNION
SELECT 3,'Biri','Ben','Bbiri',1100 UNION
SELECT 4,'Newman','Chad','Cnewman',750 UNION
SELECT 5,'Ropeburn','Audrey','Aropebur',1550;
#4、向users表中插入数据
INSERT INTO users
VALUES(1,'Rpatel',10),
(2,'Bdances',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的users表和my_employees表的记录全部删除
DELETE u,e
FROM users u
JOIN my_employees e
ON u.`userid`=e.`Userid`
WHERE e.userid='Bbiri';
#8、删除所有数据
DELETE FROM my_employees;
DELETE FROM users;
#9、检查所作的修正
SELECT * FROM my_employees;
SELECT * FROM users;
#10、清空表my_employees
TRUNCATE TABLE my_employees;
DDL(Data Definition Language)
#DDL数据定义语言
/*
库和表的管理
一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除
创建： create
修改： alter
删除： drop
*/
#一、库的管理
#1、库的创建
/*
语法：
create database [if not exists] 库名 [character set 字符集名];
*/
#案例：创建库Books
CREATE DATABASE IF NOT EXISTS Books;
#2、库的修改
RENAME DATABASE books TO 新库名;
#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
#3、库的删除
DROP DATABASE IF EXISTS books;
#二、表的管理
#1、表的创建 ★
/*
语法：
create table 表名(
列名 列的类型 [ (长度) 约束],
列名 列的类型 [ (长度) 约束],
列名 列的类型 [ (长度) 约束],
...
列名 列的类型 [ (长度) 约束]
)
*/
#案例：创建表Book
CREATE TABLE IF NOT EXISTS book(
id INT, #书的编号
bName VARCHAR(20), #图书名
price DOUBLE, #价格
authorId INT, #作者编号
publishDate DATETIME #出版日期
);
DESC book;
CREATE TABLE author(
id INT,
aName VARCHAR(20),
nation VARCHAR(10)
);
DESC author;
#2、表的修改
/*
alter table 表名 add|drop|modify|change [column] 列名 [列类型 约束] [first|after 字段名];
*/
#①修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;
#②修改列的类型或约束
ALTER TABLE 表名 MODIFY COLUMN 列名 新类型【新约束】;
#③添加新列
ALTER TABLE 表名 ADD COLUMN 列名 类型;
#④删除列(不可以加if exists)
ALTER TABLE 表名 DROP COLUMN 列名;
#⑤修改表名
ALTER TABLE 表名 RENAME TO 新表名;
#3、表的删除
DROP TABLE [IF EXISTS] 表名;
SHOW TABLES;
#
#通用的写法：
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
#4、表的复制
INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
#①仅仅复制表的结构
CREATE TABLE 表名 LIKE 旧表名;
#②复制表的结构+数据
CREATE TABLE 表名
SELECT * FROM 旧表名;
#只复制部分数据
CREATE TABLE 表名
SELECT id,aName
FROM author
WHERE nation='中国';
#仅仅复制某些字段
CREATE TABLE 表名
SELECT id,aname
FROM author
WHERE 0;#恒不成立
案例
#一、创建表dept1
USE test;
CREATE TABLE dept1(
id INT(7),
NAME VARCHAR(25)
);
#二、将表departments中的数据插入到新表dept2中
CREATE TABLE dept2
SELECT department_id,department_name
FROM myemployees.departments;
#三、创建表emp5
CREATE TABLE emp5(
id INT(7),
First_name VARCHAR(25),
Last_name VARCHAR(25),
Dept_id INT(7)
);
#四、将Last_name的长度增加到50
ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50);
#五、根据表employees创建employees2
CREATE TABLE employees2 LIKE myemployees.`employees`;
#六、删除表emp5
DROP TABLE IF EXISTS emp5;
#七、将表employees2重命名为emp5
ALTER TABLE employees2 RENAME TO emp5;
#八、在表dept和emp5中添加新列test_column，并检查所作的操作
ALTER TABLE dept2 ADD COLUMN test_column INT;
ALTER TABLE emp5 ADD COLUMN test_column INT;
#九、直接删除表emp5中的列dept_id
ALTER TABLE emp5 DROP COLUMN test_column;
数据类型
#常见的数据类型
/*
数值型：
整数
小数：
定点数
浮点数
字符型：
较短的文本：char、varchar
较长的文本：text、blob(较长的二进制数据)
日期型：
*/
#一、整型
/*
分类：
tinyint 1byte
smallint 2byte
mediumint 3byte
int/integer 4byte
bigint 8byte
特点：
①如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
②如果插入的数值超出了整型的范围，会报out of range异常，插入失败
③如果不设置长度，会有默认的长度（经过尝试发现新版Mysql已经去除对int的长度限制）
经过尝试发现zerofill也被废除，mysql建议用lpad进行左填充
*/
#1、如何设置无符号和有符号
CREATE TABLE tab_int(
t1 INT ,
t2 INT UNSIGNED
);
INSERT INTO tab_int VALUES(-123456,-123456);#报错
INSERT INTO tab_int VALUES(2147483648,4294967296); #报错
#二、小数：
/*
分类：
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M,D)
decimal(M,D)
特点：
①
M：整数部位+小数部分 位数
D：小数部分位数
如果超过范围，则插入失败
②
M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double,则会根据插入数值的精度来决定精度
③
定点型的精确度较高，如果要求插入数值精度较高，如货币运算等考虑使用
*/
#测试M和D
#新版本mysql提示不赞成为浮点数据类型指定位数，并将在将来的版本中删除
CREATE TABLE tab_float(
f1 FLOAT(5,2),
f2 DOUBLE(5,2),
f3 DEC(5,2)
);
#不带M和D
CREATE TABLE tab_float(
f1 FLOAT,
f2 DOUBLE,
f3 DEC
);
#成功插入数据
INSERT INTO tab_float VALUES(123.45,123.45,123.45);
#插入的数据小数部分被四舍五入
INSERT INTO tab_float VALUES(123.456,123.456,123.456);
#自动为小数部分补0
INSERT INTO tab_float VALUES(123.4,123.4,123.4);
#报错：Out of range value for column 'f1' at row 1
INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4);
#原则：
/*
所选择的类型越简单越好，能保存数值的类型越小越好
*/
#三、字符型
/*
较短的文本：
char(M) M为最大字符数 0~255
varchar(M) 0~65535
其他：
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本：
text
blob（较大的二进制）
特点：
写法 M的意思 特点 空间的耗费 效率
char char(M) 最大的字符数，可以省略，默认为1 固定长度的字符 比较耗费 高
varchar varchar(M) 最大的字符数，不可省略 可变长度的字符 比较节省 低
*/
#枚举类型，要求插入的值必须属于列表中指定的值之一
/*
如果列表成员为1~255,则需要1个字节存储
如果列表成员为255~65535，则需要2个字节存储
*/
CREATE TABLE tab_char(
c1 ENUM('a','b','c')
);
SELECT * FROM tab_char;
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');#插入失败
INSERT INTO tab_char VALUES('A');
#Set类型
/*
和Enum类型类似，里面可以保存0~64个成员。和Enum类型最大的区别是：
Set类型一次可以选取多个成员，而Enum只能选一个
根据成员个数不用，存储所占的字节也不同
成员数 字节数
1~8 1
9~16 2
17~24 3
25~32 4
33~64 8
*/
CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
SELECT * FROM tab_set;
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('a,b');
INSERT INTO tab_set VALUES('a,c,d');
#四、日期型
/*
分类：
date只保存日期
time只保存时间
year只保存年
datetime保存日期+时间
timestamp保存日期+时间
特点：
字节 范围 时区等的影响
datetime 8 1000-9999 不受
timestamp 4 1970-2038 受
datatime 和 timestamp 的区别
1、timestamp支持的时间范围较小，取值范围：1970 01 01 08 0001~2038年的某个时间
datatime的取值范围：1000-1-1 ~ 9999-12-31
2、timestamp和实际时区有关，更能反映实际的日期，而datatime则只能反映出插入时的当地时区
3、timestamp的属性受mysql版本的SQLMode的影响很大
*/
CREATE TABLE tab_date(
t1 DATETIME,
t2 TIMESTAMP
);
SELECT * FROM tab_date;
INSERT INTO tab_date VALUES(NOW(),NOW());
SHOW VARIABLES LIKE 'time_zone';
SET time_zone = '+9:00';
约束
#常见约束
/*
含义：一种限制，用于限制表中的数据，为了保证表中数据的准确和可靠性（一致性）
分类：六大约束
NOT NULL：非空约束，用于保证该字段的值不能为空，比如姓名、学号等
DEFAULT：默认约束，用于保证该字段的值有默认值，比如性别
PRIMARY KEY：主键约束，用于保证该字段的值具有唯一性，并且非空，比如学号、员工编号
UNIQUE KEY：唯一约束，用于保证该字段的值具有唯一性，但是可以为空，比如座位号
CHECK：检查约束[mysql不支持]，比如年龄、性别
FOREIGN KEY：外键约束，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值，比如学生表的专业编号，员工表的部门编号、工种编号
添加约束的时机：
1、创建表时
2、修改表时
约束的添加分类：
列级约束：
六大约束语法上都支持，但外键约束没有效果
表级约束：
除了非空、默认，其他的都支持
主键和唯一的对比：
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多有1个 √，但不推荐
唯一 √ √（只能有1个） 可以有多个 √，但不推荐
外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、要求主表中的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表的数据，再插入从表
删除数据时，先删除从表，再删除主表
*/
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
);
#一、创建表时添加约束
#1、添加列级约束
/*
语法：
直接在字段名和字段类型后面追加约束类型即可
只支持：默认、非空、主键、唯一
支持多个约束
*/
CREATE TABLE stuinfo(
id INT PRIMARY KEY, #主键
stuName VARCHAR(20) NOT NULL, #非空
gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查约束
seat INT UNIQUE, #唯一约束
age INT DEFAULT 18, #默认约束
majorId INT REFERENCES major(id) #外键约束
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
DESC stuinfo;
#查看stuinfo表中所有的索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
#2、添加表级约束
/*
语法：在各个字段的最下面
[constraint 约束名] 约束类型(字段名)
前面可以省略，会有默认名
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuName VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorId INT,
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender='男' OR gender = '女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)#外键
);
SHOW INDEX FROM stuinfo;
CREATE TABLE stuinfo(
id INT,
stuName VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorId INT,
PRIMARY KEY(id),#主键
UNIQUE(seat),#唯一键
CHECK(gender='男' OR gender = '女'),#检查
FOREIGN KEY(majorId) REFERENCES major(id)#外键
);
#通用写法
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuName VARCHAR(20) NOT NULL,
gender CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorId INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)
);
#二、修改表时添加约束
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用];
*/
CREATE TABLE IF NOT EXISTS stuinfo(
id INT ,
stuName VARCHAR(20),
gender CHAR(1),
age INT,
seat INT,
majorId INT
);
desc stuinfo;
#1、添加非空约束
alter table stuinfo modify column stuName varchar(20) not null;
#2、添加默认约束
alter table stuinfo modify column age int default 18;
#3、添加主键
#①列级约束
alter table stuinfo modify column id int primary key ;
#②表级约束
alter table stuinfo add primary key (id);
#4、添加唯一键
#①列级约束
alter table stuinfo modify column seat int unique ;
#②表级约束
alter table stuinfo add unique (seat);
#5、添加外键
alter table stuinfo
add constraint fk_stuinfo_major foreign key (majorId) references major(id);
#三、修改表时删除约束
desc stuinfo;
#1、删除非空约束
alter table stuinfo modify column stuName varchar(20) null;
#2、删除默认约束
alter table stuinfo modify column age int;
#3、删除主键
alter table stuinfo drop primary key ;
#4、删除唯一
alter table stuinfo drop index seat;
#5、删除外键
show index from stuinfo;
alter table stuinfo drop constraint fk_stuinfo_major;
alter table stuinfo drop foreign key fk_stuinfo_major;
drop index fk_stuinfo_major on stuinfo;
标识列
#标识列
/*
又称为自增长列
含义：可以不用手动插入值，系统提供默认的序列值
特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表中可以有几个标识列？至多一个！
3、标识列的类型只能是数值型
4、标识列可以通过set auto_increment_increment = 1;设置步长
通过set auto_increment_offset = 1;修改起始值
*/
#一、创建表时设置标识列
drop table tab_identity;
create table tab_identity(
id int ,
name varchar(20)
);
truncate table tab_identity;
insert into tab_identity values(null,'john');
select * from tab_identity;
show variables like '%auto_increment%';
set auto_increment_increment = 1;#
set auto_increment_offset = 1;#起始值
#二、修改表时设置标识列
desc tab_identity;
alter table tab_identity modify column id int primary key auto_increment;
#三、修改表时删除标识列
alter table tab_identity modify column id int ;
TCL(Transaction Control Language)
事务
事务由单独单元的一个或多个sql语句组成，在这个单元中，每条sql语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条sql语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事务开始以前的状态；如果单元中的所有sql语句均执行成功，则事务被顺利执行。
MySQL中的存储引擎
- 在mysql中的数据用各种不同的技术存储在文件（或内存）中。
- 通过
show engines;来查看mysql支持的存储引擎 - 在mysql中用的最多的存储引擎有：innodb, myisam, memory 等。其中innodb支持事务，而myisam、memory等不支持事务。
事务的ACID属性
-
原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
-
一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
-
隔离性(Isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
-
持久性(Durability)
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响
数据库的隔离级别
对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。
脏读：对于两个事务T1、T2,T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的。
不可重复读：对于两个事务T1、T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了。
幻读：对于两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行之后，如果T1再次读取同一个表，就会多出几行。
数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题。
一个事务与其他事务隔离的程度称为隔离级别，数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱。
- Oracle支持的2种事务隔离级别：READ COMMITED,SERIALIZABLE。Oracle默认的事务隔离级别为：READ COMMITED
- Mysql支持4种事务隔离级别。Mysql默认的事务隔离级别为：REPEATABLE READ
注意：
- 经过测试，发现新版本mysql的REPEATABLE READ已经可以避免幻读
常用命令
- 每启动一个mysql程序，就会获得一个单独的数据库连接，每个数据库连接都有一个全局变量@transaction_isolation，表示当前的事务隔离级别
- 查看当前的事务隔离级别：
show variables like 'transaction_isolation'; - 设置当前mysql连接的隔离级别：
set session transaction isolation level 级别; - 设置数据库系统的全局的隔离级别：
set global transaction isolation level 级别;
视图
视图：MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果
应用场景：
- 多个地方用到同样的查询结果
- 该查询结果使用的sql语句较复杂
好处：
- 重用sql语句
- 简化复杂的sql操作，不必知道它的查询细节
- 保护数据，提高安全性
权限：
视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的。
- 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
- 常量视图
- Select中包含子查询
- join
- from一个不能更新的视图
- where子句的子查询引用了from子句中的表
