一、数据库的好处
- 持久化数据到本地
- 可以实现结构化查询，方便管理
二、数据库相关概念
- DB：数据库，保存一组有组织的数据的容器
- DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
- SQL:结构化查询语言，用于和DBMS通信的语言
三、数据库存储数据的特点
- 将数据放到表中，表再放到库中
- 一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性
- 表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计
- 表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
- 表中的数据是按行存储的，每一行类似于java中的“对象”。
四、MySQL产品的介绍和安装
1. MySQL服务的启动和停止
- 方式一：计算机——右击管理——服务
- 方式二：通过管理员身份运行命令行终端
net start 服务名（启动服务）
net stop 服务名（停止服务）
2. MySQL服务的登录和退出
- 方式一：通过mysql自带的客户端，只限于root用户
- 方式二：通过windows自带的客户端
- 登录：mysql 【-h主机名 -P端口号 】-u用户名 -p密码
- 退出：exit或ctrl+C
3. My SQL的常见命令
1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(
列名 列类型,
列名 列类型，
...
);
6.查看表结构
desc 表名;
6.显示表中的所有数据
select * from 表名;
7.往表中插入数据
insert into 表名 (列名1,列名2) values(值1,值2)；
8.更新表中指定数据项
update 表名 set 列名2=值x where 列名1=值x;
9.删除表中指定项
delete from 表名 where 列名1=值x;
10.查看服务器的版本
方式一：登录到mysql服务端, mysql指令
select version();
方式二：没有登录到mysql服务端，dos指令
mysql --version 或 mysql --V
4. MySQL的语法规范
- 不区分大小写, 但建议关键字大写，表名、列名小写
- 每条命令最好用分号结尾
- 每条命令根据需要，可以进行缩进 或 换行
- 注释
- 单行注释：#注释文字
- 单行注释：-- 注释文字
- 多行注释：/* 注释文字 */
五、SQL的语言分类
1. 分类
- DQL(Data Query Language)：数据查询语言
- 如 select
- DML(Data Manipulate Language)：数据操作语言
- 如 insert 、update、delete
- DDL(Data Define Languge)：数据定义语言
- 如 create、drop、alter
- TCL(Transaction Control Language)：事务控制语言
- 如 commit、rollback
2. DQL语言
2.1 进阶1：基础查询
-
语法：
SELECT 要查询的东西 FROM 表名; -
特点：
- 通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
- 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数
-
案例：
0. 选择库
USE myemployees;
1. 查询表中的单个字段
SELECT lastname FROM employees;
2.查询表中的多个字段
SELECT lastname,salary,emial FROM employees;
3.查询表中的所有字段
SELECT * FROM employees;
4.查询常量值
SELECT 数值;
5.查询函数
SELECT VERSION();
6.起别名: 便于理解，若要查询的字段有重名的情况，则使用别名可以区分开来
方式一：使用AS
SELECT 100%98 AS 结果;
SELECT lastname AS 姓,firstname AS 名 FROM employees;
方式二：使用空格
SELECT lastname 姓,firstname 名 FROM employees;
#案例：查询salary，显示结果为out put
SELECT salary AS “OUT put” from employees //有关键字的用双引号括起来
7.去重 DISTINCT
#案例：查询员工表中涉及到的所有部门编号
SELECT DISTINCT departmentid FROM employees
8. +号的作用
mysql中的+号仅有一个功能，就是运算符
SELECT 100+90; 两个操作数都为数值型，则做加法运算
SELECT '123'+90; 只要其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算
SELECT 'john'+90; 如果转换失败，则将字符型数值转换成0
SELECT null+10; 只要其中一方为null，则结果肯定为null
#案例：查询员工名和姓连接成一个字段，并显示为 姓名
SELECT CONCAT('A', 'B','C') AS 结果;
//CONCAT函数功能是拼接字符
2.2 进阶2：条件查询
-
定义：条件查询：根据条件过滤原始表的数据，查询到想要的数据
-
语法：
SELECT 要查询的字段|表达式|常量值|函数 //查询列表 FROM 表名 WHERE 筛选条件; -
分类：
-
按条件表达式筛选：
- 条件运算符：> 、< 、>= 、<= 、= 、不等于：!= 或 <>
#案例1：查询工资>1200的员工信息 SELECT * FROM employees WHERE salary>12000; #案例2：查询部门编号不等于90号的员工名和部门编号 SELECT last_name,department_id FROM employees WHERE department_id<>90;- 安全等于 <=>
#案例1：查询没有奖金的员工名和奖金率 SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL; #案例2：查询薪资为12000的员工名 SELECT last_name,salary FROM employees WHERE salary <=> 12000; -
按逻辑表达式筛选：
-
逻辑运算符：
- and(&&): 两个条件如果同时成立，结果为true，否则为false
- or(||): 两个条件只要有一个成立，结果为true，否则为false
- not(!): 如果条件成立，则not后为false，否则为true
-
示例：salary>10000 && salary<20000
-
-
模糊查询
- LIKE :
- 一般和通配符%搭配使用，可以判断字符型或数值型
- 通配符：%:任意多个字符，_:任意单个字符
#案例1：查询员工中包含字符a的员工信息 SELECT * FROM employees WHERE last_name LIKE '%a%'; #案例2：查询员工名中第3个字符为n, 第五个字符为l 的员工名 SELECT last_name,salary FROM employees WHERE last_name LIKE '__n_l%'; #案例3：查询员工名中第二个字符为_的员工名 //ESCAPE：用于声明其后的字符为转义字符 SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';- BETWEEN AND：
- 使用 BETWEEN AND 可以提高语句的简洁度
- 包含临界值
- 两个临界值不要调换顺序
#案例：查询员工编号在100到120之间的员工信息 SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;- IN：
- 使用 IN 提高语句简洁度
- IN 列表的值类型必须一致或兼容
- IN 列表的值不支持通配符
#案例：查询员工的工种编号是 IT_PROG、AD_VP 中一个的员工名和工种编号 SELECT last_name,job_id FROM employees WHERE job_id IN('IT_PROG','AD_VP');- IS NULL | IS NOT NULL
#案例：查询没有奖金的员工名和奖金率 SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL; - LIKE :
-
IS NULL 和 <=>对比
- IS NULL：仅仅可以判断NULL值，可读性较高，建议使用
- <=> ：既可以判断NULL值，又可以判断普通数值，可读性较低
-
2.3 进阶3：排序查询
- 语法：
SELECT 要查询的东西（查询列表）
FROM 表
【WHERE 筛选条件】 //可选
ORDER BY 排序的字段|表达式|函数|别名 【ASC | DESC】
-
特点：
- ASC代表的是升序，DESC代表的是降序，如果不写，默认是升序
- ORDER BY 子句中可以支持单个字段，多个字段，表达式，函数，别名
- ORDER BY 子句一般是放在查询语句的最后面，limit子句除外
-
案例：
#案例1: 查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
#案例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;
#案例4：按姓名的长度显示员工的姓名和工资 【按函数排序】
SELECT LENGTH(last_name),last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
#案例5：查询员工信息，先按工资升序，再按员工编号降序【按多个字段排序】
SELECT * FROM employees
ORDER BY salary ASC, employee_id DESC;
#练习题
#1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name,department_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;
2.4 进阶4：常见函数
-
定义：将一组逻辑语句封装在方法体中，对外暴露方法名
-
好处：
- 隐藏了实现细节
- 提高了代码的重用性
-
语法：
SELECT 函数名（实参列表）【FROM 表】;
-
特点：
- 叫什么（函数名）
- 干什么（函数功能）
-
分类：
-
单行函数：
-
字符函数：
-
length 获取参数值的 字节个数
SELECT LENGTH('john'); -
concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees; -
substr、substring 截取子串
#案例1：截取从指定索引处后面的所有字符 SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put; #案例2：截取从指定索引处指定**字符长度**的字符 ( **注意索引从1开始**) SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put; -
upper 转换成大写
SELECT UPPER('john'); -
lower 转换成小写
#案例1：SELECT LOWER('JOHN'); #案例2：将姓变大写，名变小写，然后拼接 SELECT CONCAT(UPPER(last_name),LOWER(first_name)) AS 姓名 FROM employees; #案例3：将姓名中首字符大写，其他字符小写，然后拼接 SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) AS 姓名 FROM employees; -
instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不梅爱上了殷六侠','殷六侠') AS out_put; -
trim去前后指定的空格和字符
# 默认去掉空格 SELECT TRIM(' 张翠山 ') AS out_put; #去掉指定字符 SELECT TRIM('a', ’aaaaaaaaaaaa张翠山aaaaaaaaaaaaaa‘) AS out_put; -
ltrim去左边空格
SELECT LTRIM(' 张翠山 ') AS out_put; -
rtrim去右边空格
SELECT RTRIM(' 张翠山 ') AS out_put; -
lpad 左填充：用指定的字符实现左填充指定长度
SELECT LPAD('AAA', 10,'*') AS out_put; -
rpad右填充：用指定的字符实现右填充指定长度
SELECT RPAD('AAA', 10,'ab') AS out_put; -
replace 替换
SELECT REPLACE('AABBCC', BB,'DD') AS out_put;
-
-
数学函数
- round 四舍五入
SELECT ROUND(1.45); SELECT ROUND(1.456, 2); //第二个参数为保留位数- ceil 向上取整，返回>=该参数的最小整数
SELECT CEIL(1.02);- floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99);- truncate 截断
SELECT TRUNCATE(1.6999, 1); //第二个参数为截断后的字符长度-
mod 取余
-
内部计算过程： mod(a, b ) ==> a-a/b*b;
SELECT MOD(10, -3);- rand 随机数
SELECT RAND(10); -
日期函数
-
now 返回当前系统日期+时间
SELECT NOW(); -
curdate返回当前系统日期，不包含时间
SELECT CURDATE(); -
curtime 返回当前系统时间，不包含日期
SELECT CURTIME(); -
获取指定的部分
- 年YEAR, 月MONTH, 日DAY, 小时HOUR, 分钟MINUTES, 秒SECOND
SELECT YEAR(NOW()) AS 年; SELECT YEAR('1999-1-1') AS 年; SELECT MONTH(NOW()); //返回中文 SELECT MONTHNAME(NOW()); // 返回英文 -
STR_TO_DATE 将日期格式的字符转换成日期类型
SELECT STR_TO_DATE('1988-3-2','%Y-%c-%d') AS out_put; -
DATE_FORMAT 将日期转换成字符
SELECT DATE_FORMAT(NOW(), '%y年%m月%d日)) AS out_put; -
DATEDIFF 返回两个日期相差的天数
SELECT DATEDIFF(NOW(),'1990-10-19') AS out_put; -
MONTHNAME 以英文格式返回月
SELECT MONTHNAME('1990-10-19') AS out_put;
-
-
流程控制函数
-
**if 函数：**if(条件表达式1,表达式1,表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2； 如下案例所示
SELECT IF(10<5, '大','小'); -
case函数：
-
样式1：(判断等值)
case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1 when 常量2 then 要显示的值2或语句2 ... else 要显示的值n或语句n end;/* 案例：查询员工的工资，要求 部门号=30，显示的工资为1.1倍 部门号=40，显示的工资为1.2倍 部门号=50，显示的工资为1.3倍 其他部门，显示原工资 */ SELECT salary 原始工资,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; -
样式2：（判断区间）
case when 条件1 then 要显示的值1或语句1 when 条件2 then 要显示的值2或语句2 ... else 要显示的值n或语句n end;/* 案例：查询员工工资情况 如果工资>20000,则显示A级别 如果工资>15000,则显示B级别 如果工资>10000,则显示C级别 其他显示D级别 */ SELECT salary,department_id, CASE WHEN salary>20000 THEN 'A' WHEN salary>15000 THEN 'B' WHEN salary>10000 THEN 'C' ELSE 'D' END AS salarylevel FROM employees;
-
-
-
其他函数
- version 获取当前数据库的版本
- database 当前库
- user 当前连接用户
- password(‘字符’)：返回该字符的密码形式，相当于自动加密
- MD5(‘字符’)：返回该字符的MD5加密形式
-
-
分组函数
-
功能：用作做统计使用，又称为统计函数，聚合函数，组函数
-
语法:
SELECT FUNC(字段) FROM 表名 -
分类：
- sum 求和
- avg 求平局值
- max 最大值
- min 最小值
- count 计数
-
特点：
- sum、avg 一般用于处理数值型数据
- max、min、count可以处理任何数据
- 以上分组函数都忽略null值，除了count(*)
- 都可以搭配distinct使用，用于去重的统计
- SELECT SUM(DISTINCT 字段) FROM 表；
- count的参数可以支持：字段、*****、常量值，一般放1，建议使用 count(*)
- count(字段)：统计该字段非空值的个数
- count(*)：统计结果集的行数
- MYISAM存储引擎下，COUNT(*)的效率高
- INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
- 和分组函数一同查询的字段，要求是group by后的字段
-
使用方法：
SELECT SUM(salary) FROM employees; SELECT AVG(salary) FROM employees; SELECT MAX(salary) FROM employees; SELECT MIN(salary) FROM employees; SELECT COUNT(salary) FROM employees; SELECT SUM(last_name),AVG(last_name) FROM employees; SELECT MAX(last_name),MIN(last_name),COUNT(last_name) FROM employees; SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35 FROM employees; SELECT SUM(DISTINCT(salary)),SUM(salary) FROM employees; SELECT COUNT(salary) FROM employees; SELECT COUNT(*) FROM employees; SELECT COUNT(1) FROM employees; #练习 #1. 查询公司员工工资的最大值，最小值，平均值，总和 SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees; #2. 查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE） SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) AS DIFFRENCE FROM employees; #3. 查询部门编号为 90 的员工个数 SELECT COUNT(*) FROM employees WHERE department_id=90;
-
-
2.5 进阶5：分组查询
-
语法：
SELECT 分组函数,分组后的字段（要求出现在group by的后面） FROM 表 【WHERE 筛选条件】 GROUP BY 分组的字段 【HAVING 分组后的筛选】 【ORDER BY 子句】 -
注意：要查询列表必须特殊，要求是分组函数和group by后出现的字段
-
特点：
- 分组筛选
- 分组函数做条件的肯定优先放在having子句中
- 能用分组前筛选的，就优先考虑使用分组前筛选
数据源（针对的表） 位置 关键字 分组前筛选 原始表 group by子句的前面 where 分组后筛选 分组后的结果集 group by 子句的后面 having - group by 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数（用的较少）
- 也可以添加排序（排序放在整个分组查询的最后）
- having 子句后可以支持别名
- 分组筛选
-
案例：
#简单的分组查询 #案例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的工种编号和最高工资 #拆分 #1）查询每个工种有奖金的员工的工种编号和最高工资 SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id; #2)根据1的结果进行筛选出最高工资>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的 领导编号是哪个，以及其最低工资 #拆分 #1)查询领导编号>102的 每个领导手下的最低工资和领导编号 SELECT MIN(salary),manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id; #2)根据1的结果进行筛选最低工资>5000 SELECT MIN(salary),manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000; #按表达式或函数分组 #案例：按员工姓名的长度分组，查询每组的员工个数，筛选员工个数>5的有哪些 #拆分 #1)按员工姓名的长度分组，查询每组的员工个数 SELECT COUNT(*),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name); #2)根据1的结果，筛选出员工个数>5的有哪些 SELECT COUNT(*),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5; #按多个字段分组 #案例:查询每个部门每个工种的员工的平均工资 SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id; #添加排序 #案例:查询每个部门每个工种的员工的平均工资，并按平均工资的高低显示 SELECT AVG(salary),department_id,job_id 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), MAX(salary)-MIN(salary) AS DIFFENENCE FROM employees; #3. 查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员工不计算在内 #拆分 #1) 查询各个管理者手下员工的最低工资,没有管理者的员工不计算在内 SELECT MIN(salary),manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id; #2)根据1的结果筛选出最低工资不低于6000 SELECT MIN(salary),manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary)>=6000; #4. 查询所有部门的编号，员工数量和工资平均值,并按平均工资降序 SELECT department_id,COUNT(*),AVG(salary) FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC; #5. 选择具有各个 job_id 的员工人数 SELECT COUNT(*),job_id FROM employees GROUP BY job_id;
2.6 进阶6：多表连接查询
-
定义：又称多表查询，当查询的字段来自于多个表时，就会用到多表连接查询
-
语法：
SELECT 字段1,字段2 FROM 表1,表2,....;
2.6.1 笛卡尔乘积现象
- 定义：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
- 发生原因：没有有效的连接条件
- 如何避免：添加有效的连接条件
2.6.2 分类
-
按年代分类
- sql92标准：仅仅支持内连接
- sql99标准【推荐使用】：支持内连接 + 外连接（左外和右外）+ 交叉连接
-
按功能分类
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接
- 右外连接
- 全外连接（mysql不支持）
- 交叉连接
- 内连接
2.6.3 sql92标准
-
等值连接
-
语法
SELECT 查询列表 FROM 表1 别名1,表2 别名2 WHERE 表1.key=表2.key 【AND 筛选条件】 【GROUP BY 分组字段】 【HAVING 分组后的筛选】 【ORDER BY 排序字段】 -
特点：
- 一般需要为表起别名，提高阅读性和性能
- 多表的顺序可以调换
- n表连接，至少需要n-1个连接条件
- 等值连接的结果为多表的交集部分
#1.简单的等值连接 #案例1：查询女神名和对应的男神名 SELECT NAME,boyname FROM beauty,boys WHERE beauty.`boyfriend_id` = boys.`id`; #案例2；查询员工名和对应的部门名 SELECT last_name,department_name FROM employees,departments WHERE departments.`department_id`=employees.`department_id`; #2.为表起别名，建议别名取短些 #好处：提高语句的简洁度，区分多个重名的字段 #注意1：如果为表起了别名，则查询的字段就不能使用原来的表名去限定 #注意2：两个表的顺序可以调换 #案例:查询员工名，工种id，工种名 SELECT last_name,e.`job_id`,j.`job_title` FROM employees e,jobs j WHERE e.`job_id`=j.`job_id`; #3.添加筛选条件 #案例1：查询有奖金的员工名，部门名 SELECT last_name, department_name, e.`commission_pct` FROM employees e,departments d WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL; #案例2：查询城市名中第二个字符为o的部门名和城市名 SELECT department_name,city FROM departments d,locations l WHERE d.`location_id`=l.`location_id` AND l.`city` LIKE '_o%'; #4.添加分组 #案例1：查询每个城市的部门个数 SELECT COUNT(*) ,city FROM departments d, locations l WHERE d.`location_id`=l.`location_id` GROUP BY city; #案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资 SELECT d.`department_name`,d.`manager_id`,MIN(salary) FROM departments d, employees e WHERE d.`department_id`=e.`department_id` AND e.`commission_pct` IS NOT NULL GROUP BY d.`department_name`,d.`manager_id`; #5.添加排序 #案例：查询每个工种的工种名和员工的个数，并且按员工个数排序 SELECT job_title,COUNT(*) FROM employees e,jobs j WHERE e.`job_id`=j.`job_id` GROUP BY job_title ORDER BY COUNT(*) DESC; #6.实现三表连接 #案例：查询员工名，部门名和所在的城市 SELECT last_name,department_name,city FROM employees e,departments d,locations l WHERE l.`location_id`=d.`location_id` AND d.`department_id`=e.`department_id`; -
-
非等值连接
-
语法：
SELECT 查询列表 FROM 表1 别名,表2 别名2 WHERE 非等值的连接条件 【AND 筛选条件】 【GROUP BY 分组字段】 【HAVING 分组后的筛选】 【ORDER BY 排序字段】 -
案例：
#案例：查询员工的工资和工资级别 SELECT salary,grade_level FROM employees e,job_grades j WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal` AND j.`grade_level`='A' ORDER BY salary DESC;
-
-
自连接
-
语法：
SELECT 查询列表 FROM 表 别名1,表 别名2 WHERE 表1.key=表2.key 【AND 筛选条件】 【GROUP BY 分组字段】 【HAVING 分组后的筛选】 【ORDER BY 排序字段】 -
案例：
#案例：查询员工名和上级的名称 SELECT e.last_name,e.employee_id,m.last_name,m.employee_id FROM employees e, employees m WHERE e.`manager_id`=m.`employee_id`;
-
-
案例
#1. 显示所有员工的姓名，部门号和部门名称。 USE myemployees; SELECT e.last_name,e.department_id,d.department_name FROM departments d, employees e WHERE d.`department_id`=e.`department_id`; #2. 查询 90 号部门员工的 job_id 和 90 号部门的 location_id SELECT job_id,location_id,e.`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,d.location_id,l.city,e.`commission_pct` 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,l.city FROM employees e,departments d,locations l WHERE l.`location_id`=d.`location_id` AND e.`department_id`=d.`department_id` AND l.`city`='Toronto'; #5.查询每个工种、每个部门的部门名、工种名和最低工资 SELECT d.department_name,j.job_title,MIN(salary) FROM departments d,jobs j,employees e WHERE d.`department_id`=e.`department_id` AND e.`job_id`=j.`job_id` GROUP BY d.department_name,j.job_title; #6.查询每个国家下的部门个数大于 2 的国家编号 SELECT country_id,COUNT(*) department_num FROM departments d, locations l WHERE d.`location_id`=l.`location_id` GROUP BY country_id HAVING COUNT(*)>2; #7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式 #employees Emp# manager Mgr# #kochhar 101 king 100 #自连接 SELECT e.last_name,e.employee_id,e.`manager_id`,m.last_name,m.employee_id FROM employees e, employees m WHERE e.`manager_id`=m.`employee_id` AND e.`last_name`='kochhar';
2.6.4 sql99标准
-
定义：1999年推出的sql语法
-
语法：
SELECT 查询列表 FROM 表1 别名 【连接类型 inner|left outer|right outer|cross】 JOIN 表2 别名 ON 连接条件 【WHERE 筛选条件】 【GROUP BY 分组】 【HAVING 筛选条件】 【ORDER BY 排序列表】 -
分类：
- 内连接：inner
- 外连接
- 左外：left【outer】
- 右外：right【outer】
- 全外：full【outer】
- 交叉连接：cross
- 自连接
-
内连接
-
语法：
SELECT 查询列表 FROM 表1 别名 【INNER】 JOIN 表2 别名 ON 连接条件; -
特点：
- 可以添加排序，分组，筛选
- INNER关键字可以省略
- 筛选条件放在WHERE后面，连接条件放在ON后面，提高分离性
- INNER JOIN 连接和sq92语法中等值连接效果是一样的，都是多表连接的交集
- 表的顺序可以调换
- 内连接的结果=多表的交集
- n表连接至少需要n-1个连接条件
-
分类
-
等值连接
#案例1：查询员工名，部门名（调换位置） #1)范式一 SELECT last_name,department_name FROM employees e INNER JOIN departments d ON e.`department_id`=d.`department_id`; #2)范式二 SELECT last_name,department_name FROM departments d INNER JOIN employees e 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的城市名和部门个数（分组+筛选） #拆分 #1)查询所有城市名的部门个数 SELECT city,COUNT(*) 部门个数 FROM locations l INNER JOIN departments d ON l.`location_id`=d.`location_id` GROUP BY city; #2)在1)的结果基础上筛选部门个数>3 SELECT city,COUNT(*) 部门个数 FROM locations l INNER JOIN departments d ON l.`location_id`=d.`location_id` GROUP BY city HAVING COUNT(*)>3; #案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（排序） #拆分 #1）查询所有部门的员工个数 SELECT department_name, COUNT(*) FROM departments d INNER JOIN employees e ON d.`department_id`=e.`department_id` GROUP BY department_name; #2）在1的结果基础上筛选出员工个数>3的部门并按个数降序（排序） SELECT department_name, COUNT(*) FROM departments d INNER JOIN employees e ON d.`department_id`=e.`department_id` GROUP BY department_name HAVING COUNT(*)>3 ORDER BY COUNT(*); #案例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; -
非等值连接
#案例1：查询员工的工资等级 SELECT last_name,salary, grade_level FROM employees e JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`; #案例2：查询工资级别的个数>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; -
自连接
#案例1：查询员工名字及其上级的名字 SELECT e.last_name,m.last_name FROM employees e JOIN employees m ON e.`manager_id`=m.`employee_id`; #案例2：查询员工名字中包含k字符的名字及其上级的名字 SELECT e.last_name,m.last_name FROM employees e JOIN employees m ON e.`manager_id`=m.`employee_id` WHERE e.`last_name` LIKE '%k%';
-
-
-
外连接
-
应用场景：用于查询一个表中有，另一个表没有的记录
-
语法：
SELECT 查询列表 FROM 表1 别名 LEFT|RIGHT|FULL【OUTER】 JOIN 表2 别名 ON 连接条件; -
分类：
- 左外连接
- 右外连接
- 全外连接：结果为内连接的结果+表1有但表2没有+表2有但表1没有的 (mysql不支持)
-
特点：
- 外连接的查询结果为主表中的所有记录
- 如果从表中有和它匹配的，则显示匹配的值
- 如果从表中没有和它匹配的，则显示NULL
- 外连接查询结果=内连接结果+主表中有而从表没有的记录
- 如何区分主从表
- 要查询的信息主要来自哪个表，那么那个表就是主表
- 技巧：查询xxx的yyy的信息，那么xxx为主表，yyy为从表
- 左外连接：left join左边的是主表
- 右外连接：right join右边的是主表
- 全外连接：两边都是主表
- 要查询的信息主要来自哪个表，那么那个表就是主表
- 左外和右外交换两个表的顺序，可以实现同样的效果
- 外连接的查询结果为主表中的所有记录
-
案例
#案例1:查询哪个部门没有员工 #左外 SELECT d.*,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 d.*,e.`employee_id` FROM employees e RIGHT OUTER JOIN departments d ON d.`department_id`=e.`department_id` WHERE e.`employee_id` IS NULL; #案例2. 查询编号>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; #案例3：查询哪个城市没有部门 SELECT city,d.* FROM departments d RIGHT OUTER JOIN locations l ON d.`location_id`=l.`location_id` WHERE d.department_id IS NULL; #案例3：查询部门名为 SAL 或 IT 的员工信息 SELECT department_name,e.* FROM departments d LEFT OUTER JOIN employees e ON d.`department_id`=e.`department_id` WHERE d.`department_name`='SAL' OR d.`department_name`='IT';
-
-
交叉连接
-
语法：
SELECT 查询列表 FROM 表1 别名 CROSS JOIN 表2 别名; -
特点：
- 类似于笛卡尔乘积
-
案例
#案例： SELECT e.*,d.* FROM employees e CROSS JOIN departments d;
-
-
自连接
-
语法：
SELECT 查询列表 FROM 表 别名1 JOIN 表 别名2 ON 连接条件; -
案例：
#案例：查询员工名和直接上级的名称 SELECT e.last_name,m.last_name FROM employees e JOIN employees m ON e.`manager_id`=m.`employee_id`;
-
2.6.5 sql92 与 sql99对比
- 功能：sql99支持的功能较多
- 可读性：sql99实现连接条件和筛选条件分离，可读性较高
2.7 进阶7：子查询
-
定义：
- 出现在其他语句内的select语句，称为子查询或内查询
- 内部嵌套其他select语句的查询，称为主查询或外查询
-
特点：
- 子查询放在小括号内，一般放在条件的右侧
- 子查询的执行优先于主查询执行，主查询的条件需要用到子查询的结果
- 非法使用子查询的情况：
- 子查询的结果为一组值
- 子查询的结果为空
-
分类
- 按结果集的行列数不同
- 标量子查询（单行子查询）（结果集只有一行一列）
- 列子查询（多行子查询）（结果集只有一列多行）
- 行子查询（多列子查询）（结果集为多行多列）
- 表子查询（结果集一般为多行多列）
- 按子查询出现的位置
- SELECT 后面：仅支持标量子查询
- FROM 后面：支持表子查询
- WHERE或HAVING后面：支持标量子查询，列子查询，行子查询
- EXISTS后面（相关子查询）：支持表子查询
- 按结果集的行列数不同
-
WHERE 或 HAVING 后面
-
分类：
-
标量子查询（单行子查询）：一般搭配着单行操作符使用，如>、<、>=、<=、= 、<>
#案例1：谁的工资比 Abel 高？ #1）查询Abel的工资 SELECT salary FROM employees WHERE last_name='Abel'; #2)查询员工的信息，满足salary > 1)的结果 SELECT * FROM employees WHERE salary>( SELECT salary FROM employees WHERE last_name='Abel' ); #案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资 #1）查询141号员工的job_id SELECT job_id FROM employees WHERE employee_id=141; #2)查询143号员工的salary SELECT salary FROM employees WHERE employee_id=143; #3)查询员工姓名、job_id和工资，要求job_id = 1)，salary > 2) 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 #1）查询公司的最低工资 SELECT MIN(salary) FROM employees; #2）查询last_name,job_id和salary,要求工资 = 1) SELECT last_name,job_id,salary FROM employees WHERE salary=( SELECT MIN(salary) FROM employees ); #案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资 #1）查询50号部门的最低工资 SELECT MIN(salary) FROM employees WHERE department_id=50; #2)查询每个部门的最低工资， SELECT department_id,MIN(salary) FROM employees GROUP BY department_id; #3)在2)基础上筛选，满足min(salary) > 1) SELECT department_id,MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary)>( SELECT MIN(salary) FROM employees WHERE department_id=50 ); -
列子查询（一列多行子查询）：一般搭配着多行操作符使用，如in、any/some、all
- IN / NOT IN：等于或不等于列表中的任意一个
- ANY | SOME：和子查询返回的某一个值比较，相当于MIN
- ALL：和子查询返回的所有值比较，相当于MAX
- ANY 和 ALL 往往可以用其他查询代替
#案例1；返回location_id 是1400或1700的部门中的所有员工姓名 #1)查询location_id 是1400或1700的部门编号 SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700); #2)查询部门编号 = 1）结果的所有员工姓名 SELECT last_name FROM employees WHERE department_id IN( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700) ) #或 SELECT last_name FROM employees WHERE department_id = ANY ( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700) ) #案例2: 返回其他工种中比job_id为'IT_PROG'部门任一工资低的员工的工号，姓名，job_id以及salary #1）查询job_id为'IT_PROG'的员工的工资 SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG'; #2）查询工资比 1)中的任一工资低的员工的工号，姓名，job_id以及salary SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary < ANY( SELECT 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'; #案例3: 返回其他工种中比job_id为'IT_PROG'部门所有工资低的员工的工号，姓名，job_id以及salary #1）查询job_id为'IT_PROG'的员工的工资 SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG'; #2）查询工资比 1)中的所有工资低的员工的工号，姓名，job_id以及salary SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary < ALL( SELECT 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'; -
行子查询（多行多列）：结果集一行多列或多行多列
#案例：查询员工编号最小并且工资最高的员工信息 #方法1： SELECT * FROM employees WHERE(employee_id,salary)=( SELECT MIN(employee_id),MAX(salary) FROM employees ); #方法2： #1）查询最小的员工编号 SELECT MIN(employee_id) FROM employees; #2）查询最高的工资 SELECT MAX(salary) FROM employees; #3）查询员工信息，要求编号=1），工资=2） SELECT * FROM employees WHERE employee_id=( SELECT MIN(employee_id) FROM employees ) AND salary=( SELECT MAX(salary) FROM employees )
-
-
-
SELECT 后面：仅仅支持标量子查询
#案例1：查询每个部门的员工个数 SELECT d.*,( SELECT COUNT(*) FROM employees e WHERE e.`department_id`=d.department_id ) 个数 FROM departments d; #案例2：查询员工编号=102的部门名 SELECT ( SELECT department_name FROM departments d INNER JOIN employees e ON e.department_id = d.department_id WHERE e.employee_id=102 ) 部门名; -
FROM 后面：支持表子查询，将子查询的结果充当一张表，要求必须为表起别名
#案例：查询每个部门的平均工资的工资等级 #1)查询每个部门的平均工资 SELECT department_id,AVG(salary) ag FROM employees e GROUP BY department_id; #2)连接1）的结果集合job_gardes表，筛选平均工资在 low - high之间 SELECT ag_dep.*,j.`grade_level` FROM ( SELECT department_id,AVG(salary) ag FROM employees e GROUP BY department_id ) ag_dep INNER JOIN job_grades j WHERE ag_dep.ag BETWEEN j.`lowest_sal` AND j.`highest_sal`; -
EXISTS / NOT EXISTS后面（相关子查询）：支持表子查询
-
语法：
EXISTS(完整的查询语句) 结果: 1 或 0 -
特点：
- 上述三种语句都是先执行内查询，再执行外存，而EXISTS语句跟这三不同
- EXISTS是先执行主查询，再根据条件筛选主查询结果
-
案例
#案例1：查询有员工的部门名 #方法1：in SELECT department_name FROM departments d WHERE d.`department_id` IN( SELECT DISTINCT department_id FROM employees ); #方法2：EXIST SELECT department_name FROM departments d WHERE EXISTS( SELECT * FROM employees e WHERE d.`department_id`=e.`department_id` ); #案例2：查询没有女朋友的男神信息 #方法1：in SELECT bo.* FROM boys bo WHERE bo.id NOT IN( SELECT boyfriend_id FROM beauty ); #方法2：exists SELECT bo.* FROM boys bo WHERE NOT EXISTS( SELECT boyfriend_id FROM beauty b WHERE b.`boyfriend_id`=bo.`id` );
-
-
练习
#练习 #案例1. 查询和 Zlotkey 相同部门的员工姓名和工资 #1)查询zlotkey部门ID SELECT department_id FROM employees WHERE last_name='Zlotkey'; #2）查询员工姓名和工资，要求部门id = 1）的结果 SELECT m.last_name,m.salary FROM employees m WHERE m.`department_id`= ( SELECT department_id FROM employees WHERE last_name='Zlotkey'; ) AND m.`last_name`<>'Zlotkey'; #案例2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。 #1）查询公司平均工资 SELECT AVG(salary) avs FROM employees; #2）查询员工的工号，姓名，工资，要求工资 > 1）的结果 SELECT e.employee_id,e.last_name,e.salary FROM employees e WHERE e.`salary`>( SELECT AVG(salary) avs FROM employees ); #案例3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资 #1）查询各部门的平均工资 SELECT AVG(salary),department_id FROM employees GROUP BY department_id; #2）连接1）的结果 和 employees ，查询工号，姓名，工资，要求工资 > 1）的结果 SELECT e.`department_id`,e.employee_id,e.last_name,e.salary FROM employees e INNER JOIN ( SELECT AVG(salary) avs,department_id FROM employees GROUP BY department_id )avgtab ON e.`department_id`=avgtab.department_id WHERE e.`salary`>avgtab.avs ORDER BY e.`department_id`; #案例4. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名 #1）查询姓名中包含字母u的员工所在的部门id SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%'; #2)查询员工的工号和姓名，要求部门id in 1）的结果 SELECT e.employee_id,e.last_name,e.department_id FROM employees e WHERE e.`department_id` IN ( SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%' ); #案例5. 查询在部门的 location_id 为 1700 的部门工作的员工的员工号 #1）查询部门的 location_id 为 1700 的部门id SELECT d.department_id FROM departments d WHERE d.`location_id`=1700; #2)查询员工的工号，要求部门id in 1）的结果 SELECT employee_id,department_id FROM employees WHERE department_id IN ( SELECT d.department_id FROM departments d WHERE d.`location_id`=1700 ); #或 SELECT employee_id,department_id FROM employees WHERE department_id = ANY ( SELECT d.department_id FROM departments d WHERE d.`location_id`=1700 ); #案例6. 查询管理者是 K_ing 的员工姓名和工资 #1）查询KING的工号 SELECT employee_id FROM employees WHERE last_name='K_ing'; #2）查询员工姓名和工资，要求管理者工号 = 1) 结果 SELECT e.last_name,e.salary FROM employees e WHERE e.`manager_id` IN ( SELECT employee_id FROM employees WHERE last_name='K_ing' ); #案例7. 查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为 姓.名 #1）查询最高工资 SELECT MAX(salary) FROM employees; #2）查询员工的姓名，要求工资 = 1）的结果 SELECT CONCAT(e.last_name,'.',e.`first_name`) '姓.名',e.`salary` FROM employees e WHERE e.`salary`=( SELECT MAX(salary) FROM employees );
2.8 进阶8：分页查询
-
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
-
语法：
SELECT 查询列表 FROM 表 【join type JOIN 表2】 【ON 连接条件】 【WHERE 筛选条件】 【GROUP BY 分组字段】 【HAVING 分组后的筛选条件】 【ORDER BY 排序的字段】 LIMIT 【offset】,size; //offset要显示条目的开始索引（起始索引从0开始） //size 要显示的条目个数 -
特点：
-
起始条目索引从 0 开始
-
limit 子句放在查询语句的最后，无论在语法上还是执行顺序上都是最后
-
公式：
SELECT 查询列表 FROM 表 LIMIT（page-1）*sizePerPage, sizePerPage //page:要显示的页数 //sizePerPage:每页的条目数
-
-
案例：
#案例1：查询前5条员工信息 SELECT * FROM employees LIMIT 0,5; SELECT * FROM employees LIMIT 5; #案例2：查询第11-25条员工信息 SELECT * FROM employees LIMIT 10,15; #案例3：有奖金的员工信息，并且工资较高的前10名显示出来 SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 0,10;
2.9 进阶9：联合查询
-
定义：union联合，合并，将多条查询语句的结果合并成一个结果
-
语法：
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】 select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】 select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】 ..... select 字段|常量|表达式|函数 【from 表】 【where 条件】 -
应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
-
特点：
- 要求多条查询语句的查询列数必须是一致的
- 要求多条查询语句的查询的每一列的类型和顺序最好一致
- union关键字默认是去重的，如果使用union all 可以包含重复项
-
案例：
#案例1： 查询部门编号>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; #案例2：查询中国用户中男性的信息以及外国用户中男性的信息 SELECT id,name FROM t_ca WHERE csex='男' UNION SELECT t_id,t_name FROM t_ua WHERE sex='man'; #案例3： 不去重显示所有用户信息 SELECT id,name FROM t_ca WHERE csex='男' UNION ALL SELECT t_id,t_name FROM t_ua WHERE sex='man';
2.10 总结
- 查询语句中涉及到的关键字以及执行顺序
| 关键字 | 执行顺序 | 流程解析 |
|---|---|---|
| SELECT 查询列表 | 7 | 在6的基础上选择虚拟表部分数据显示出来，生成一个新虚拟表 |
| FROM 表1 | 1 | 把原始表生成一个虚拟表 |
| jointype JOIN 表2 | 2 | 两个表连接起来，出现了笛卡尔乘积现象，生成一个新虚拟表 |
| ON 连接条件 | 3 | 在2的基础上将满足连接条件的数据筛选出来，生成一个新虚拟表 |
| WHERE 筛选条件 | 4 | 在3的基础上将满足筛选条件的数据筛选出来，生成一个新虚拟表 |
| GROUP BY 分组条件 | 5 | 在4的基础上根据分组条件进行分组，生成一个新虚拟表 |
| HAVING 分组后的筛选条件 | 6 | 在5的基础上进行进一步筛选，生成一个新虚拟表 |
| ORDER BY 排序条件 | 8 | 在7的基础上进行排序，生成一个新虚拟表 |
| LIMIT offset size | 9 | 在8的基础上进行分页显示，生成一个新虚拟表 |
3. DML语言
3.1 插入 insert
-
语法：
-
方式一：
INSERT INTO 表名(列名,.....) VALUES(值1,....); -
方式二：
INSERT INTO 表名 SET 列名1=值1,列名2=值2,...... -
方式三：增加子查询
INSERT INTO 表名 SELECT 值1,..... UNION SELECT 值2,..... UNION .... SELECT 值N,..... ;
-
-
特点
-
插入的值的类型必须与列的类型一致或兼容，而且一 一对应
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) VALUES(13,'lucy','女','1990-01-01','19888888',NULL,2); -
不可以为NULL的列必须插入值，可以为NULL的列可插入NULL，也可列名和值一起省略
#方式一 INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) VALUES(13,'lucy','女','1990-01-01','19888888',NULL,2); #方式二 INSERT INTO beauty(id,NAME,sex,borndate,phone,boyfriend_id) VALUES(14,'lily','女','1990-01-01','19888888',9); -
列的顺序可以调换
INSERT INTO beauty(NAME,id,sex,boyfriend_id,phone) VALUES("hanmei",15,'女',3,'19999999'); -
字段个数/列数 和值的个数必须一致
-
可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
INSERT INTO beauty VALUES(16,'sunne','女','1990-01-01','19888888',NULL,2);
-
-
两种插入方式对比
-
方式1支持插入多行，方式2不支持
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) VALUES(20,'lucy1','女','1990-01-01','19888888',NULL,2) ,(21,'lucy2','女','1990-01-01','19888888',NULL,2) ,(22,'lucy3','女','1990-01-01','19888888',NULL,2); -
方式1支持子查询，方式2不支持
INSERT INTO beauty(id,NAME,phone) SELECT 26,'lucy666','18888888'; INSERT INTO beauty(id,NAME,phone) SELECT id,boyname,'123456789' FROM boys WHERE id<3;
-
3.2 修改 update
- 修改单表语法：
UPDATE 表名
SET 字段=新值,字段=新值, ....
【WHERE 筛选条件】
- 修改多表语法：（级联更新）
#sql92语法
UPDATE 表1 别名1,表2 别名2
SET 字段=新值，字段=新值
【WHERE 连接条件】
【AND 筛选条件】
#sql99语法
UPDATE 表1 别名1
INNER|LEFT|RIGHT JOIN 表2 别名2
ON 连接条件
SET 列=值,....
【WHERE 筛选条件】
- 案例
#单表
#案例：修改beauty表中首字母为l的女神的电话为13888888888888
UPDATE beauty SET phone='13888888888888'
WHERE NAME LIKE 'l%';
#多表
#案例1：修改张无忌的女朋友手机号为114
UPDATE boys bo
INNER JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='114'
WHERE b.`boyfriend_id`=1;
#案例2：修改没有男盆友的女神的boyfriendid为2
UPDATE boys bo
RIGHT JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE b.`boyfriend_id` IS NULL;
3.3 删除 delete
-
方式1：delete语句
-
单表的删除：
-
语法：
DELETE FROM 表名 【WHERE 筛选条件】【LIMIT size】
-
-
多表的删除
-
语法：
#sql92语法 DELETE 别名1,别名2 FROM 表1 别名1,表2 别名2 WHERE 连接条件 AND 筛选条件; #sql99语法 DELETE 别名1,别名2 FROM 表1 别名1 INNER|LEFT|RIGHT JOIN 表2 别名2 ON 连接条件 WHERE 筛选条件;
-
-
案例
#案例1：删除张无忌的女朋友信息 DELETE b FROM boys bo INNER JOIN beauty b ON b.`boyfriend_id`=bo.`id` WHERE bo.`boyName`='张无忌'; #案例2：删除黄晓明的信息及其女朋友的信息 DELETE b,bo FROM boys bo INNER JOIN beauty b ON b.`boyfriend_id`=bo.`id` WHERE bo.`boyName`='黄晓明';
-
-
方式2：truncate语句 (清空数据)
-
语法
TRUNCATE TABLE 表名 -
特点：
- 要删除就删除整个表，不能加where 筛选条件
-
-
两种方式的对比【经典面试题】
-
truncate 不可以加where 筛选条件，而delete 可以加
-
truncate 删除效率比delete高
-
truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始，而delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
DELETE FROM boys; TRUNCATE TABLE boys; -
truncate 删除没有返回值，delete删除有返回值
-
truncate删除不能回滚，delete删除可以回滚
-
4. DDL语言
- 功能：数据定义语言，主要负责库和表的管理，创建（create），修改（alter），删除（drop）
4.1 库的管理
-
创建库：
#语法 CREATE DATABASE 【IF NOT EXISTS】 库名【CHARACTER SET 字符集名】; #案例 CREATE DATABASE IF NOT EXISTS books; -
修改库：库一般不修改，因为容易导致数据库崩溃，一般用于修改库的字符集
ALTER DATABASE books CHARACTER SET gbk; -
删除库：
#语法 DROP DATABASE 【IF EXISTS】 库名
4.2 表的管理
-
创建表：
-
语法
CREATE TABLE 表名( 列名 列的类型【(长度) 约束】, 列名 列的类型【(长度) 约束】, ... 列名 列的类型【(长度) 约束】 ); -
案例
CREATE TABLE book( id INT, bname VARCHAR(20), price DOUBLE, authorid INT, publishDate DATETIME ); CREATE TABLE author( id INT, au_name VARCHAR(20), nation VARCHAR(10) );
-
-
修改表
-
语法：
ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 列名 【字段类型】; -
修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型; -
修改列的类型及约束
ALTER TABLE 表名 MODIFY COLUMN 列名 类型; -
增加新列
ALTER TABLE 表名 ADD COLUMN 新列名 类型 【FIRST|AFTER 字段名】; -
删除列
ALTER TABLE 表名 DROP COLUMN 列名; -
修改表名
ALTER TABLE 旧表名 RENAME 【TO】 新表名;
-
-
删除表
DROP TABLE 【IF EXISTS】 表名; -
复制表
-
仅仅复制表的结构
CREATE TABLE copy LIKE author; -
复制表的结构+数据
CREATE TABLE copy2 SELECT * FROM author; -
只复制部分数据
CREATE TABLE copy3 SELECT id,au_name FROM author WHERE nation='中国'; -
仅仅复制某些字段
CREATE TABLE copy4 SELECT id,au_name FROM author WHERE 0;
-
-
通用写法
DROP DATABASE IF EXISTS 旧库名; CREATE DATABASE 新库名; DROP TABLE IF EXISTS 旧表名; CREATE TABLE 新表名(); -
常见类型
-
原则：所选择的类型越简单越好，能保存数值的类型越小越好
-
数值型
-
整型
-
分类：tinyint (1), smallint (2), mediumint (3), int/integer (4), bigint (8)
-
特点：
- 如果不设置符号类型，默认是有符号，如果想设置无符号，需要添加unsigned关键字
- 如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
- 如果不设置长度，会有默认长度
- 长度代表了显示的最大宽度，如果不够则在左边补0填充，但必须搭配zerofill使用，并且默认变成无符号整型
DROP TABLE IF EXISTS tabint CREATE TABLE tabint( t1 INT(7), t2 INT UNSIGNED, t3 INT(7) ZEROFILL );
-
-
小数：
-
分类：
- 浮点型： float(M, D) 、 double(M, D)
- 定点型：dec(M, D)、decimal(M, D)
-
特点
- M 和 D：M为整数部位的长度+小数部位的长度，D为小数部位的长度，如果M超过范围，则插入临界值，如果D超过范围则四舍五入
- M 和 D 都可省略，如果是decimal，则M默认为10，D默认为0；如果是float和double，则会根据插入的数值的精度来决定精度
- 定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
DROP TABLE IF EXISTS tab_float; CREATE TABLE tab_float( f1 FLOAT(5,2), f2 DOUBLE(5,2), f3 DECIMAL(5,2), f4 FLOAT, f5 DOUBLE, f6 DECIMAL );
-
-
-
字符型
-
较短的文本：
- char & varchar
写法 M的意思 特点 空间耗费 效率 char char(M) 最大的字符数，可以省略，默认为1 固定长度的字符 比较耗费 高 varchar varchar(M) 最大的字符数，不可以省略 可变长度的字符 比较节省 低 -
较长的文本：text，blob（较长的二进制数据，如图片）
-
其他类型
- binary 和 varbinary 用于保存较短的二进制数据
- enum 用于保存枚举
- set 用于保存集合
-
-
日期型
-
date：只保存日期
-
time：只保存时间
-
year：只保存年
-
datetime：保存日期+时间
-
timestamp：保存日期+时间
字节 范围 时区等的影响 datetime 8 1000–9999 不受 timestamp 4 1970–2038 受
-
-
-
常见约束
-
定义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
-
分类：
- NOT NULL：非空，用于保证该字段的值不能为空，比如姓名，学号等
- DEFAULT：默认，用于保证该字段有默认值，比如性别
- PRIMARY KEY：主键，用于保证该字段的值具有唯一性且非空，比如学号，员工编号
- UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空，比如座位号
- CHECK：检查约束【mysql中不支持】，比如年龄，性别
- FOREIGN KEY：外键，该字段的值引用了另外一个表的字段
-
添加约束的时机
-
创建表时
-
添加列级约束
- 语法：直接在字段名和类型后面追加约束类型即可，可以一次添加多个约束，用空格隔开只支持默认、非空、主键、唯一
USE myemployees; 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) ); #查看表中的所有索引，包括主键，外键，唯一 SHOW INDEX FROM 表名 -
添加表级约束
- 语法：
在各个字段的最下面添加 【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 ck CHECK(gender='男' OR gender='女'), #检查约束 CONSTRAINT uq UNIQUE(seat), #唯一 CONSTRAINT fk_stuinfo_majorid FOREIGN KEY(majorId) REFERENCES major(id) #外键 ); -
通用的写法
DROP TABLE IF EXISTS stuinfo; CREATE TABLE stuinfo( id INT PRIMARY KEY, #主键 stuname VARCHAR(20) NOT NULL, #非空 gender CHAR(1), seat INT UNIQUE, age INT DEFAULT 18, majorId INT, CONSTRAINT fk_stuinfo_majorid FOREIGN KEY(majorId) REFERENCES major(id) #外键 );
-
-
修改表时
-
语法
#1.添加列级约束 ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束; #2.添加表级约束 ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 约束类型(字段名) 【外键的引用】 -
添加非空约束
ALTER TABLE 表名 MODIFY COLUMN 字段 类型 NOT NULL; -
添加默认约束
ALTER TABLE 表名 MODIFY COLUMN age INT DEFAULT 18; -
添加主键
-
列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段 类型 PRIMARY KEY; -
表级约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段);
-
-
添加唯一
-
列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段 类型 UNIQUE; -
表级约束
ALTER TABLE 表名 ADD UNIQUE(字段);
-
-
添加外键
ALTER TABLE 表名 ADD FOREIGN KEY(字段名) REFERENCES 主表名(被引用列);
-
-
-
删除约束
-
修改表时
-
删除非空约束
ALTER TABLE 表名 MODIFY COLUMN 字段 字段类型 NULL; -
删除默认约束
sALTER TABLE 表名 MODIFY COLUMN 字段 字段类型; -
删除主键
ALTER TABLE 表名 DROP PRIMARY KEY; -
删除唯一
ALTER TABLE 表名 DROP INDEX 索引名; -
删除外键
ALTER TABLE 表名 DROP FOREIGN 约束名;
-
-
-
约束的添加分类
CREATE TABLE 表名( 字段名 字段类型 列级约束, 字段名 字段类型, 表级约束 );-
列级约束
- 六大约束语法上都支持，但外键约束没有效果
- 列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求
-
表级约束
- 除了非空，默认，其他的都支持
-
对比
位置 支持的约束类型 是否可以起约束名 列级约束 列的后面 语法都支持，但外键没有效果 不可以 表级约束 所有列的下面 默认和非空不支持，其他都支持 可以，但主键没效果
-
-
主键的唯一的对比【经典面试题】
是否具有唯一性 是否允许为空 一个表中可以有多少个 是否允许组合key 主键 是 不允许 至多有一个 允许但不推荐 唯一 是 允许 可以有多个 允许但不推荐 INSERT INTO major VALUES(1,'java'); INSERT INTO major VALUES(2,'h5'); INSERT INTO stuinfo VALUES(1,'john','男',NULL,19,1); INSERT INTO stuinfo VALUES(2,'lily','男',NULL,19,1); SELECT * FROM major; SELECT * FROM stuinfo; DROP TABLE IF EXISTS stuinfo; CREATE TABLE stuinfo( id INT, subid INT, stuname VARCHAR(20), gender CHAR(1), seat INT, seat2 INT, age INT, majorId INT, PRIMARY KEY(id),#主键 PRIMARY KEY(id,stuname),#主键 CHECK(gender='男' OR gender='女'), #检查约束 UNIQUE(seat), #唯一 UNIQUE(seat2), #可支持多个唯一键 UNIQUE(seat1,seat2), #可支持多个唯一键 PRIMARY KEY(subid) #会报错，因为支持一个主键 FOREIGN KEY(majorId) REFERENCES major(id) #外键 ); -
外键：
- 用于限制两个表的关系，从表的字段值引用了主表的某字段值
- 从表的外键列和主表的被引用列要求类型一致，意义一致，名称无要求
- 主表的被引用列要求是一个key（一般就是主键）
- 插入数据时，先插入主表，再插入从表
- 删除数据时，先删除从表，再删除主表
-
可以通过以下两种方式来删除主表的记录
- 方式1：级联删除
ALTER TABLE stuinfo ADD CONSTRAINT 外键别名 FOREIGN KEY(从表外键字段) REFERENCES 主表名(主表被引用字段) ON DELETE CASCADE;- 方式2：级联置空
ALTER TABLE stuinfo ADD CONSTRAINT 外键别名 FOREIGN KEY(从表外键字段) REFERENCES 主表名(主表被引用字段) ON DELETE SET NULL;
-
4.3 标识列
-
定义：又称为自增长列，不用手动插入值，系统自动提供序列值，默认从1开始，步长为1
-
创建表时设置标识列
DROP TABLE IF EXISTS 表名; CREATE TABLE 表名( 字段名 字段类型 约束 auto_increment, 字段名 字段类型 ); INSERT INTO 表名 VALUES(NULL,字段值); INSERT INTO 表名 VALUES(NULL,字段值); INSERT INTO 表名 VALUES(NULL,字段值); .... -
修改表时设置标识列
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 约束 AUTO_INCREMENT; -
修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT; -
特点：
- 标识列不一定需要和主键搭配，但必须是个key
- 一个表至多只能有一个标识列
- 标识列的类型只能是数值型
- 可以通过 SHOW VARIABLES LIKE ‘%auto_increment%’ 查看标识列的步长
- 标识列可以通过 SET auto_increment_increment=x 来设置步长，也可以通过手动插入值，设置起始值
5. TCL 语言
全称：Transaction Control Language 事务控制语言
5.1 事务
- 定义：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部对不执行，如果单元中某条sql语句执行失败则整个单元全部回滚
- 存储引擎
- 定义：在mysql中的数据用各种技术存储在文件或内存中
- 分类：mysql5.5之前用的myisam，mysql5.5用的是innodb
- 通过 show engines 可以查看mysql支持的存储引擎
- 在mysql中用的最多的存储引擎有 innodb，myisam，memory，其中innodb支持事务，其他两个不支持
5.2 事务的ACID属性
- 原子性(Atomicity)：一个事务是一个不可分割的工作单位，事务中的操作要么都执行要么都不执行
- 一致性(Consistenc)：一个事务执行会使数据库从一个一致性状态变换到另一个一致性状态
- 隔离性(Isolation)：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受其他事务的干扰，多个事务互相隔离
- 持久性(Durability)：一个事务一旦被提交，则会永久的改变数据库的数据
5.3 事务的创建
-
隐式事务：事务没有明显的开启和结束的标记，比如insert， update，delete语句
-
显式事务：事务具有明显的开启和结束的标记，但前提是必须先设置自动提交功能为禁用
- 如何禁用自动提交： 执行语句
SET autocommit=0;，该设置只对本次事务有效 - 查询自动提交状态：执行语句
SHOW VARIABLES LIKE 'autocommit'; - 流程：
步骤1：开启事务 SET autocommit=0;//取消自动提交事务的功能 【start transaction;】 步骤2：编写事务的一组逻辑操作单元（多条sql语句） select 语句; insert 语句; update 语句; delete 语句; ... 设置回滚点： savepoint 回滚点名; 步骤3：提交事务或回滚事务 commit; #提交事务 commit to 回滚点名; rollback; #回滚事务 rollback to 回滚点名; //回滚到指定的地方- 案例：
DROP TABLE account; CREATE TABLE account( username VARCHAR(20), balance INT ); INSERT INTO account VALUES('ken',1200),('lucy',800); #开启事务 SET autocommit=0; #编写一组事务的语句 UPDATE account SET balance = 1000 WHERE username='ken'; UPDATE account SET balance = 1000 WHERE username='lucy'; #结束事务：//在结束事务之前，所有修改的数据都只保存在内存 COMMIT; ROLLBACK; SELECT * FROM account; - 如何禁用自动提交： 执行语句
-
delete 和truncate 在事务使用时的区别：delete 支持回滚，而truncate不支持
#delete SET autocommit=0; START TRANSACTION; DELETE FROM account; ROLLBACK; #truncate SET autocommit=0; START TRANSACTION; TRUNCATE TABLE account; ROLLBACK
5.4 事务的隔离级别
- 事务并发问题的产生原因：多个事务同时操作同一个数据库的相同数据
- 并发问题分类：
- 脏读：一个事务读取到了其他事务还没提交的数据，读到的是其他事务“更新”的数据
- 不可重复读：同一个事务中，多次读取到的数据不一致
- 幻读：一个事务读取了其他事务还没提交的数据，读到的是其他事务“插入”的数据
- 解决事务并发问题：通过设置事务的隔离级别（事务间的隔离程度）
- 隔离级别分类：
- 数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度
- 隔离级别越高，数据一致性越好，但并发性越弱
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| read uncommitted 读未提交 | 存在 | 存在 | 存在 |
| read committed 读已提交 | 不存在 | 存在 | 存在 |
| repeatable read 可重复读 | 不存在 | 不存在 | 存在 |
| serializable 串行化 | 不存在 | 不存在 | 不存在 |
- mysql中默认使用第三个隔离级别 repeatable read
- Oracle中默认使用第二个隔离级别 read committed
- 每个mysql连接都有一个事务隔离级别
@@tx_isolation- 查看当前连接隔离级别：使用
select @@tx_isolation; - 设置当前连接隔离级别：使用
set session transaction isolation level 隔离级别;
- 查看当前连接隔离级别：使用
- 设置数据库系统全局的隔离级别：使用
set global transaction isolation level 隔离级别; - 设置保存点 savepoint， 保存点一般与rollback配套使用，用将事务回滚到保存点的状态
6. 视图
6.1 创建视图
-
定义：mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表通过执行时动态生成
-
特点：
- 视图数据来自自定义视图查询中使用的表
- 只保存sql逻辑，不保存查询结果
-
好处：
- 简化了sql语句
- 提高了sql的重用性
- 和基表实现了分离，保护了基表的数据，提高了安全性
-
应用场景：
- 多个地方用到同样的查询结果
- 该查询使用的sql语句较复杂
-
语法：
CREATE VIEW 视图名 AS 复杂的查询语句; -
案例：
#普通方式 SELECT stuname,majorname,majorId FROM stuinfo s INNER JOIN major m ON s.`majorId`=m.`id` WHERE s.`majorId`=1; #视图方式 #创建视图 CREATE VIEW v2 AS SELECT stuname,majorname,majorId FROM stuinfo s INNER JOIN major m ON s.`majorId`=m.`id`; #使用视图 SELECT * FROM v2 WHERE majorId=1; #案例1：查询姓名中包含a字符的员工名，部门名和工种信息 CREATE VIEW V3 AS SELECT last_name,department_name,job_title FROM employees e JOIN departments d ON e.`department_id`=d.`department_id` JOIN jobs j ON e.`job_id`=j.`job_id`; SELECT * FROM V3 WHERE last_name LIKE '%a%'; #案例2：查询各部门的平均工资级别 CREATE VIEW v8 AS SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ORDER BY AVG(salary); SELECT v.*, j.grade_level FROM v8 v JOIN job_grades j ON v.ag BETWEEN j.`lowest_sal` AND j.`highest_sal`; #案例3. 查询平均工资最低的部门 SELECT * FROM v8 LIMIT 1; #案例4. 查询平均工资最低的部门名和工资 CREATE VIEW v9 AS SELECT * FROM v8 LIMIT 1; SELECT d.department_name, v.ag FROM v9 v JOIN departments d ON v.`department_id`=d.`department_id`; #案例5：创建视图emp_v1，要求查询电话号码以011开头的员工姓名、工资、邮箱 CREATE OR REPLACE VIEW emp_v1 AS SELECT last_name,salary,email FROM employees WHERE phone_number LIKE '011%'; SELECT * FROM emp_v1; #案例6：创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息 CREATE OR REPLACE VIEW emp_v2 AS SELECT MAX(salary) max_sal, department_id FROM employees GROUP BY department_id HAVING max_sal>12000; SELECT * FROM emp_v2; SELECT d.* FROM departments d JOIN emp_v2 v2 ON d.`department_id`=v2.department_id;
6.2 修改视图
-
修改方式：
-
方式一：
- 语法：
CREATE OR REPLACE VIEW 视图名 AS 查询语句;- 案例
CREATE OR REPLACE VIEW myv3 AS SELECT * FROM employees; -
方式二：
- 语法
ALTER VIEW 视图名 AS 查询语句；- 案例
ALTER VIEW myv3 AS SELECT * FROM employees;
-
6.3 删除视图
-
语法：
DROP VIEW 视图1,视图2....;
6.4 查看视图
-
语法
#方式1： DESC 视图名; #方式2： SHOW CREATE VIEW 视图名;
6.5 更新视图
-
定义：增删改查视图中的数据
-
分类：
-
插入
INSERT INTO 视图名 VALUES(值1,值2); -
修改
UPDATE 视图名 SET 列名=值 WHERE 筛选条件; -
删除
DELETE FROM 视图名 WHERE 筛选条件;
-
-
特点：
- 更新视图中的数据，会同时更新原始表中的数据，所以更新的数据列名必须在原始表中存在
- 由于会更新原始表中数据，为防止误操作引起数据异常，所以创建视图时一般设置只读权限
- 具备以下特点的视图不允许更新
- 包含以下关键字的sql语句：分组函数，distinct，group by，having，union或union all
- 常量视图
- select 中包含子查询
- join 语句
- from 一个不能更新的视图
- where 子句的子查询引用了from子句中的表
6.6 视图与表
- 对比
| 创建语法的关键字 | 是否实际占用物理空间 | 使用 | |
|---|---|---|---|
| 视图 | create view | 只是保存了sql逻辑 | 增删改查，一般不能增删改 |
| 表 | create table | 保存了数据 | 增删改查 |
7. 变量
7.1 系统变量
-
说明：变量由系统提供，不是用户定义，属于服务器层面
-
注意：如果变量是全局级别，则需要加GLOBAL，如果是会话级别，则需要加SESSION，如果不写，则默认SESSION
-
分类：根据作用域不同区分
- 全局变量
- 会话变量
-
语法：
-
查看所有的系统变量
SHOW GLOBAL|SESSION VARIABLES; -
查看满足条件的部分系统变量
SHOW GLOBAL|【SESSION】 VARIABLES like '匹配条件'; -
查看指定的某个系统变量的值
SELECT @@global|【session】.系统变量名 -
为某个系统变量赋值
-
方式1：
SET GLOBAL|【SESSION】系统变量名=值; -
方式2：
SET @@global|【SESSION】.系统变量名=值；
-
-
-
全局变量
-
服务器器层面上的，必须拥有super权限才能为系统变量赋值
-
作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但不能跨重启
-
示例：
#1. 查看所有的全局变量 SHOW GLOBAL VARIABLES; #2. 查看部分的全局变量 SHOW GLOBAL VARIABLES LIKE '%char%'; #3. 查看指定的全局变量的值 SELECT @@global.autocommit; SELECT @@tx_isolation; #4. 为某个指定的全局变量赋值 SET @@global.autocommit=0; SET GLOBAL autocommit=0;
-
-
会话变量
-
服务器为每一个连接的客户端都提供了系统变量
-
作用域：仅仅针对于当前会话（连接）有效
-
示例：
#1. 查看所有的会话变量 SHOW VARIABLES; SHOW SESSION VARIABLES; #2. 查看部分的会话变量 SHOW VARIABLES LIKE '%char%'; SHOW SESSION VARIABLES LIKE '%char%'; #3. 查看指定的会话变量的值 SELECT @@autocommit; SELECT @@session.tx_isolation; #4. 为某个会话变量赋值 #方式1： SET @@tx_isolation='read-uncommitted'; #方式2： SET SESSION tx_isolation='read-committed'
-
7.2 自定义变量
-
说明：变量是用户自定义的，不是由系统定义
-
使用步骤：
-
声明
-
赋值
-
使用（查看，比较，运算等）
-
用户变量
-
作用域：针对于当前会话（连接）有效，同于会话变量的作用域
-
应用地方：begin end里面或者外面
-
步骤：
#1.声明并初始化 SET @用户变量名=值; 或者 SET @用户变量名:=值; 或者 SELECT @用户变量名:=值; #2.赋值（更新用户变量的值） #方式1：通过SET或SELECT，一般用于赋值简单的值 SET @用户变量名=值; 或者 SET @用户变量名:=值; 或者 SELECT @用户变量名:=值; #方式2：通过SELECT INTO，一般用于赋值表中的字段值 SELECT 字段 INTO @用户变量名 FROM 表; #3.使用（查看用户变量的值） SELECT @用户变量名; #案例： SET @name='john'; SET @name=100; SET @count=1; SELECT COUNT(*) INTO @count FROM employees; SELECT @count;
-
-
局部变量
-
作用域：仅仅在定义它的begin end中有效
-
使用位置：只能放在begin end 中，且只能放在在begin end中的第一句话
-
步骤：
#1.声明 DECLARE 变量名 类型; DECLARE 变量名 类型 DEFAULT 值; #2.赋值 #方式1： #方式1：通过SET或SELECT SET 局部变量名=值; 或者 SET 局部变量名:=值; 或者 SELECT @局部变量名:=值; #方式2：通过SELECT INTO SELECT 字段 INTO 局部变量名 FROM 表; #3. 使用 SELECT 局部变量名;
-
-
用户变量 vs 局部变量
| 作用域 | 定义和使用的位置 | 语法 | |
|---|---|---|---|
| 用户变量 | 当前会话 | 会话中的任何地方 | 必须加@符号，不用限定类型 |
| 局部变量 | BEGIN END 中 | 只能在BEGIN END 中，且为第一句话 | 一般不用加@符号，需要限定类型 |
-
案例
#案例：声明两个变量并赋初始值，求和并打印 #1.用户变量 SET @m=1; SET @n=2; SET @sum=@m+@n; SELECT @sum; #2.局部变量 DECLARE m INT DEFAULT 1; DECLARE n INT DEFAULT 2; DECLARE SUM INT; SET SUM=m+n; SELECT SUM;
8. 存储过程
-
定义：一组预先编译好的sql语句的集合，理解成批处理语句
-
好处：
- 提高代码的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
-
创建存储过程：
CREATE PROCEDURE 存储过程名（参数模式 参数名 参数类型） BEGIN 存储过程体（一组合法的SQL语句） END-
注意：
-
参数模式：
- IN：表示该参数可以作为输入，即该参数需要调用方传入值（可省略）
- OUT：表示该参数可以作为输出，即该参数可以作为返回值
- INOUT：表示该参数既可以作为输入又可以作为输出，即该参数既需要调用方传入值又可以返回值
-
如果存储过程仅仅只有一句话，则BEGIN END 可以省略
-
存储过程体中的每条sql语句的结尾都要求必须加分号
-
存储过程的结尾可以使用
DELIMITER重新设置新的结束标记#语法 DELIMITER 结束标记 #案例 DELIMITER $
-
-
-
调用存储过程
CALL 存储过程名(实参列表)； -
分类
- 空参列表的存储过程
#案例：插入到admin表中的5条数据 DELIMITER $ CREATE PROCEDURE myp1() BEGIN INSERT INTO amdin(username,`password`) VALUES('aa','0000'),('bb','0000'),('cc','0000'),('dd','0000'),('ee','0000'); END $ #调用 CALL myp1()$ #查看 SELECT * FROM admin$- 创建带 in 模式参数的存储过程
#案例1：创建存储过程实现 根据女神名，查询对应的男神名 CREATE PROCEDURE myp2(IN beautyName VARCHAR(20)) BEGIN SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id=b.boyfriend_id WHERE b.name=beautyName; END $ #调用 CALL myp2('小昭')$ #案例2：创建存储过程实现，用户是否登录成功 CREATE PROCEDURE myp4(IN username VARCHAR(20), IN PASSWORD VARCHAR(20)) BEGIN DECLARE result INT DEFAULT 0; #声明并初始化 SELECT COUNT(*) INTO result #赋值 FROM admin WHERE admin.`username`=username AND admin.`password`=PASSWORD; SELECT IF(result>0,'成功','失败');#使用 END $ #调用 CALL myp4('张飞','8888888888’)$- 创建带out模式的存储过程
#案例1：根据女神名，返回对应的男神名 DELIMITER $ CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20)) BEGIN SELECT bo.boyName INTO boyName FROM boys bo INNER JOIN beauty b ON bo.id=b.boyfriend_id WHERE b.name=beautyName; END $ #调用 CALL myp5('小昭',@bName)$ SELECT @bName$ #案例2： #案例1：根据女神名，返回对应的男神名和男神魅力值 CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT) BEGIN SELECT bo.boyName ,bo.userCP INTO boyName,userCP FROM boys bo INNER JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name=beautyName; END $ #调用 CALL myp6('小昭',@bName,@usercp)$ SELECT @bName,@usecp$- 创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍返回 DELIMITER $ CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT) BEGIN SET a=a*2; SET b=b*2; END $ #调用 SET @m=10$ SET @n=20$ CALL myp8(@m,@n)$ SELECT @m,@n$ -
删除存储过程
-
语法：
DROP PROCEDURE 存储过程名 #案例： DROP PROCEDURE myp1; DROP PROCEDURE myp2,myp5; //会报错，注意一次只能删除一个存储过程
-
-
查看存储过程的信息
- 语法：
SHOW CREATE PROCEDURE 存储过程名; #案例： SHOW CREATE PROCEDURE myp6;
- 语法：
-
存储过程设置中文字符集
SET NAMES gbk -
案例：
#1、创建存储过程或函数实现传入用户名和密码，插入到 admin 表中 DELIMITER $ CREATE PROCEDURE myp1(IN username VARCHAR(20), IN PASSWORD VARCHAR(20)) BEGIN INSERT INTO admin(admin.username, admin.PASSWORD) VALUES(username, PASSWORD); END $ #调用 CALL myp1('ken','123456')$ SELECT * FROM admin$ #2、创建存储过程或函数实现传入女神编号，返回女神名称和女神电话 DELIMITER $ CREATE PROCEDURE myp2(IN id INT, OUT NAME VARCHAR(20), OUT phone VARCHAR(20)) BEGIN SELECT b.name,b.phone INTO NAME,phone FROM beauty b WHERE id=b.id; END $ #调用 CALL myp2(1,@name,@phone)$ SELECT @name,@phone$ #3、创建存储存储过程或函数实现传入两个女神生日，返回大小 DELIMITER $ CREATE PROCEDURE myp3(IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT) BEGIN SELECT DATEDIFF(birth1, birth2) INTO result; END $ #调用 CALL myp3('1990-1-1', NOW(), @result)$ SELECT @result$ #4、创建存储过程或函数实现传入一个日期，格式化成 xx 年 xx 月 xx 日并返回 DELIMITER $ CREATE PROCEDURE myp4(IN time_date DATETIME, OUT time_str VARCHAR(60)) BEGIN SELECT DATE_FORMAT(time_date,'%y年%m月%d日') INTO time_str; END $ #调用 CALL myp4(NOW(),@time_str)$ SELECT @time_str$ #5、创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串 #如 传入 ：小昭 #返回： 小昭 and 张无忌 DROP PROCEDURE myp5 DELIMITER $ CREATE PROCEDURE myp5(IN NAME VARCHAR(20), OUT couple VARCHAR(60)) BEGIN SELECT CONCAT(NAME, 'and', IFNULL(bo.boyName, 'null') INTO couple FROM boys bo RIGHT JOIN beauty b ON bo.id=b.boyfriend_id WHERE b.name=NAME; END $ #调用 CALL myp5('lucy',@couple)$ SELECT @couple$ #6、创建存储过程或函数，根据传入的条目数和起始索引，查询 beauty 表的记录 DROP PROCEDURE myp6 DELIMITER $ CREATE PROCEDURE myp6(IN size INT, IN startoffset INT) BEGIN SELECT * FROM beauty LIMIT startoffset,size; END $ #调用 CALL myp6(3,5)$
9. 函数
-
定义：一组预先编译好的sql语句的集合，理解成批处理语句
-
好处：
- 提高代码的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
-
与存储过程的对比
关键字 调用语法 返回值 应用场景 存储过程 RPOCEDURE CALL 存储过程名( ) 可以有0个或多个返回 适合做批量插入，批量更新 函数 FUNCTION SELECT 函数名( ) 有且仅有1个返回 适合做处理数据后返回一个结果 -
语法：
-
创建函数
CREATE FUNCTION 函数名(参数 参数类型) RETURNS 返回类型 BEGIN 函数体 END $- 函数体: 肯定会有 RETURN 语句，如果没有会报错，另外 RETURN 语句没有放在函数体最后也不会报错，但不建议
- 当函数体中仅有一句话，则可以省略begin end
- 使用 DELIMITER 语句设置结束标记
-
调用函数
SELECT 函数名(实参列表); -
查看函数
SHOW CREATE FUNCTION 函数名; -
删除函数
DROP FUNCTION 函数名;
-
-
案例：
- 无参有返回
#案例：返回公司的员工个数 USE myemployees DELIMITER $ CREATE FUNCTION myf1() RETURNS INT BEGIN DECLARE c INT DEFAULT 0; #定义变量 SELECT COUNT(*) INTO c #赋值 FROM employees; RETURN c; END $ SELECT myf1()$- 有参有返回
#案例1：根据员工名，返回它的工资 DELIMITER $ CREATE FUNCTION myf2(empname VARCHAR(20)) RETURNS DOUBLE BEGIN SET @sal=0; #定义用户变量 SELECT salary INTO @sal #赋值 FROM employees WHERE last_name=empname; RETURN @sal; END $ SELECT myf2('kochhar') $ #案例2：根据部门名，返回该部门的平均工资 DELIMITER $ CREATE FUNCTION myf3(depname VARCHAR(10)) RETURNS DOUBLE BEGIN DECLARE avg_sal DOUBLE; SELECT AVG(e.salary) INTO @avg_sal FROM employees e INNER JOIN departments d ON e.department_id = d.department_id WHERE d.department_name=depname; RETURN @avg_sal; END $ SELECT myf3('IT') $ #案例3：创建函数，实现传入两个 float，返回二者之和 DELIMITER $ CREATE FUNCTION myf5(num1 FLOAT, num2 FLOAT) RETURNS FLOAT BEGIN DECLARE SUM FLOAT DEFAULT 0; SET SUM=num1+num2; RETURN SUM; END $ SELECT myf5(1.1,2.2) $ #案例4：创建函数，实现传入工种名，返回该工种的员工人数 DELIMITER $ CREATE FUNCTION myf6(jobname VARCHAR(20)) RETURNS INT BEGIN SET @count=0; SELECT COUNT(*) INTO @count FROM employees e JOIN jobs j ON e.job_id=j.job_id WHERE j.job_title=jobname; RETURN @count; END $ SELECT myf6('Public Accountant') $ #案例5：创建函数，实现传入员工名，返回该员工的领导名 DELIMITER $ CREATE FUNCTION myf7(empname VARCHAR(20)) RETURNS VARCHAR(20) BEGIN DECLARE manager_name VARCHAR(20); SELECT m.last_name INTO manager_name FROM employees e JOIN employees m ON e.manager_id=m.employee_id WHERE e.last_name=empname; RETURN manager_name; END $ SELECT myf7('kochhar') $
10. 流程控制结构
10.1 分类
- 顺序结构：程序从上往下依次执行
- 分支结构：程序从两条或多条路径中选择一条去执行
- 循环结构：程序在满足一定条件的基础上，重复执行一段代码
10.2. 分支结构
-
IF 函数
- 功能：实现简单的双分支
- 语法：
IF(表达式1,表达式2,表达式3) 执行顺序: 如果表达式1成立，则IF函数返回表达式2的值，否则返回表达式3的值- 应用场景：可以作为表达式，放在任何地方
-
CASE 结构
-
功能：实现多分支
-
情况1：一般用于实现等值判断
-
语法
CASE 变量|表达式|字段 WHEN 要判断的值1 THEN 返回的值1或语句1; WHEN 要判断的值2 THEN 返回的值2或语句2; ... ELSE 要返回的值n或语句n; END 【CASE】; #如果是放在begin end中需要加上case，如果放在select后面则不需要
-
-
情况2：一般用于实现区间判断
-
语法
CASE WHEN 要判断的条件1 THEN 返回的值1或语句1; WHEN 要判断的条件2 THEN 返回的值2或语句2; ... ELSE 要返回的值n或语句n; END 【CASE】; #如果是放在begin end中需要加上case，如果放在select后面则不需要
-
-
特点：
- 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，如 BEGIN END中 或 BEGIN END的外面
- 如果放在BEGIN END 外面，作为表达式结合着其他语句使用
- 如果放在BEGIN END 里面，一般作为独立的语句使用
- 如果WHEN 中的值满足或条件成立，则执行对应的THEN后面的语句并且结束CASE，如果都不满足则执行else中的语句或值
- ELSE语句可以省略，如果ELSE语句省略了，并且所有WHEN条件都不满足，则返回NULL-
-
案例
#案例：创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100:显示A,80-90:显示B,60-80:显示C，否则显示D CREATE PROCEDURE testcase(IN score INT) BEGIN CASE WHEN score>=90 AND score<=100 THEN SELECT 'A'; WHEN score>=80 THEN SELECT 'B'; WHEN score>=60 THEN SELECT 'C'; ELSE SELECT 'D'; END CASE; END $ CALL testcase(95)$
-
-
IF ELSEIF 结构
-
功能：实现多重分支
-
语法：
IF 条件1 THEN 语句1; ELSEIF 条件2 THEN 语句2; ... 【ELSE 语句n;】 END IF; -
应用场合：只能放在 BEGIN END中
-
案例：
#案例1：根据传入的成绩，来显示等级，比如传入的成绩 90-100：返回A，80-90：返回B，60-80：返回C，否则：返回D DELIMITER $ CREATE FUNCTION test_if(score INT) RETURNS CHAR BEGIN IF score>=90 AND score<=100 THEN RETURN 'A'; ELSEIF score>=80 THEN RETURN 'B'; ELSEIF score>=60 THEN RETURN 'C'; ELSE RETURN 'D'; END IF; END $ SELECT test_if(95)$
-
-
三种分支结构对比
应用场合 IF 函数 简单双分支 CASE 结构 等值判断的多分支 IF ELSEIF 结构 区间判断的多分支
10.3 循环结构
-
分类：
-
while：先判断后执行
-
语法：
【标签:】 WHILE 循环条件 DO 循环体; END WHILE 【标签】; #如果要搭配leave跳转语句，需要使用标签，否则可以不用标签
-
-
loop：没有条件的死循环
-
语法：
【标签:】 LOOP 循环体; END LOOP 【标签】; #如果要搭配leave跳转语句，需要使用标签，否则可以不用标签
-
-
repeat：先执行后判断，类似于do while
-
语法
【标签:】 REPEAT 循环体; UNTIL 结束循环的条件 END REPEAT 【标签】; #如果要搭配leave跳转语句，需要使用标签，否则可以不用标签
-
-
-
循环控制
iterate类似于 continue 继续，用于结束本次循环，继续下一次leave类似于 break 退出，用于跳出当前所在的循环
-
三种循环对比
- 三种循环都可以省略名称，但如果循环中添加了循环控制语句（iterate 或 leave），则必须添加名称
- loop 一般用于实现简单的死循环，while 先判断后执行，repeat 先执行后判断，无条件至少执行一次
-
案例
#1. 没有添加循环控制语句 #案例：批量插入，根据次数插入到admin表中多条记录 DROP PROCEDURE test_while$ DELIMITER $ CREATE PROCEDURE test_while0(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 1; a: WHILE i<=insertCount DO INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('Rose',i), '666'); SET i=i+1; END WHILE a; END $ CALL test_while0(100)$ #2.添加leave 语句 #案例：批量插入，根据次插入到admin表中多条记录，如果次数>20则停止 TRUNCATE TABLE admin$ DELIMITER $ CREATE PROCEDURE test_while1(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 1; a:WHILE i<=insertCount DO INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('lucy',i),'0000000'); IF i>=20 THEN LEAVE a; END IF; SET i=i+1; END WHILE a; END $ CALL test_while1(100)$ #3. 添加iterate 语句 #案例：批量插入，根据次插入到admin表中多条记录，只插入偶数次 TRUNCATE TABLE admin$ DELIMITER $ CREATE PROCEDURE test_while2(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 0; a:WHILE i<=insertCount DO SET i=i+1; IF MOD(i,2)!=0 THEN ITERATE a; END IF; INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('lily',i),'0000000'); END WHILE a; END $ CALL test_while2(100)$ #4：已知表stringContent，其中字段id自增长，content varchar(20),向该表插入指定个数的、随机的字符串 USE test; DROP TABLE IF EXISTS stringContent; CREATE TABLE stringContent( id INT PRIMARY KEY AUTO_INCREMENT, content VARCHAR(20) ); DELIMITER $ DROP PROCEDURE test_rand_insert$ CREATE PROCEDURE test_rand_insert(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 1; #定义一个循环变量i,表示插入次数 DECLARE randstr VARCHAR (26) DEFAULT 'abcdefghijklmnopqrstuvwsyz'; DECLARE startindex INT DEFAULT 1; #代表起始索引 DECLARE size INT DEFAULT 1; #代表截取的字符的长度 WHILE i <=insertCount DO SET size = FLOOR(RAND()*(20-startindex+1)+1); #产生一个随机整数，代表截取长度，1-（26-startindex+1） SET startindex = FLOOR(RAND()*26+1); #产生一个随机的整数，代表起始索引1-26 INSERT INTO stringContent(content) VALUES(SUBSTR(randstr,startindex,size)); SET i=i+1; #循环变量更新 END WHILE; END $ CALL test_rand_insert(10)$
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
