01、查询语法
-- 01、基本语法
SELECT DISTINCT <select_list>
FROM <left_table> <join_type> JOIN <right_table> ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
-- 02、字段含义
FROM：需要从哪个数据表检索数据
JOIN：联合多表查询返回记录时，并生成一张临时表
ON：在生成临时表时使用的条件
WHERE：过滤表中数据的条件
GROUP BY：如何将上面过滤出的数据分组
HAVING：对上面已经分组的数据进行过滤的条件
SELECT：查看结果集中的哪个列，或列的计算结果
ORDER BY：按照什么样的顺序来查看返回的数据
LIMIT：限制查询结果返回的数量
-- 03、执行顺序
FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
02、基本操作
-- 01、查询当前DBMS中所有的DB
SHOW DATABASES;
-- 02、进入指定的DB
USE 数据库名;
-- 03、查看当前DB中的所有table
SHOW TABLES;
-- 04、在当前DB中查看指定DB中的所有table
SHOW TABLES FROM 数据库名;
-- 05、查看table的结构
DESC 表名;
-- 06、查看MySQL服务器的版本
SELECT version();
03、查询列表
-- 01、查询常量
SELECT 100;
-- 02、查询字段
SELECT last_name, email, employee_id FROM employees;
-- 03、查询表达式
SELECT 100 % 3;
-- 04、查询函数
SELECT vesion();
04、字段别名
-- 方式一：使用AS关键字
SELECT USER() AS "用户名";
SELECT USER() AS '用户名';
SELECT USER() AS 用户名;
-- 方式二：使用空格
SELECT USER() 用户名;
SELECT USER() "用户名";
SELECT USER() '用户名';
05、字段去重
-- DISTINCT关键字的作用是针对查询结果去除重复列
-- 需求：查询员工涉及到的部门编号有哪些
SELECT DISTINCT department_id FROM employees;
06、加号作用
mysql中 "+" 的作用（加法运算）：
①两个操作数都是数值型：
100 + 1.5 ==> 101.5；
②其中一个操作数为字符型：
将字符型数据强制转换成数值型，如果无法转换，则直接当做0处理：'张无忌' + 100 ==> 100
③其中一个操作数为null：
null + null ==> null
null + 100 ==> null
07、过滤筛选
/*
1、关系运算
>
<
>=
<=
=
<>
注意：
= 只能判断普通的内容
IS 只能判断NULL值
<=> 安全等于，既能判断普通内容，又能判断NULL值
2、逻辑运算
AND
OR
NOT
3、模糊查询
LIKE
BETWEEN AND
IN
NOT IN
IS NULL
IS NOT NULL
*/
--------------------关系运算案例--------------------
-- 案例1：查询工资小于15000的员工信息：姓名与工资。
SELECT last_name, salary
FROM employees
WHERE salary < 15000;
-- 案例2：查询部门编号不是100的员工信息。
SELECT *
FROM employees
WHERE employee_id <> 100;
--------------------逻辑运算案例--------------------
-- 案例1：查询部门编号不是50到100之间的员工信息：姓名、部门编号、邮箱。
-- 方式1：
SELECT last_name, department_id, email
FROM employees
WHERE department_id < 50 OR department_id > 100;
-- 方式2:
SELECT last_name, department_id, email
FROM employees
WHERE NOT (department_id >= 50 AND department_id <= 100);
-- 案例2：查询奖金率大于0.03或者员工编号在60到110之间的员工信息。
SELECT *
FROM employees
WHERE commission_pct > 0.03 OR (employee_id >= 60 AND employee_id <= 110);
--------------------模糊查询案例--------------------
----------LIKE/NOT LIKE----------
/*
LIKE/NOT LIKE
功能：一般和通配符搭配使用，对字符型数据进行部分匹配查询
常见的通配符：
_ 任意单个字符
% 任意多个字符,支持0-多个
*/
-- 案例1：查询姓名中包含字符a的员工信息。
SELECT *
FROM employees
WHERE last_name LIKE '%a%';
-- 案例2：查询姓名中包含最后一个字符为e的员工信息。
SELECT *
FROM employees
WHERE last_name LIKE '%e';
-- 案例3：查询姓名中包含第一个字符为e的员工信息。
SELECT *
FROM employees
WHERE last_name LIKE 'e%';
-- 案例4：查询姓名中包含第三个字符为x的员工信息。
SELECT *
FROM employees
WHERE last_name LIKE '__x%';
-- 案例5：查询姓名中包含第二个字符为_的员工信息。
SELECT *
FROM employees
WHERE last_name LIKE '_\_%';
----------IN/NOT IN----------
/*
IN/NOT IN
功能：查询某字段的值是否属于指定的列表之内
a IN (常量值1,常量值2,常量值3,...)
a NOT IN (常量值1,常量值2,常量值3,...)
*/
-- 案例1：查询部门编号是30/50/90的员工名、部门编号。
-- 方式1：
SELECT last_name,department_id
FROM employees
WHERE department_id IN (30,50,90);
-- 方式2：
SELECT last_name,department_id
FROM employees
WHERE department_id = 30
OR department_id = 50
OR department_id = 90;
-- 案例2：查询工种编号不是SH_CLERK或IT_PROG的员工信息。
-- 方式1：
SELECT *
FROM employees
WHERE job_id NOT IN('SH_CLERK','IT_PROG');
-- 方式2：
SELECT *
FROM employees
WHERE NOT(job_id ='SH_CLERK'
OR job_id = 'IT_PROG');
----------BETWEEN AND----------
/*
BETWEEN AND
功能：判断某个字段的值是否介于xx之间
between and
not between and
*/
-- 案例1：查询部门编号是30-90之间的部门编号、员工姓名。
-- 方式1：
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;
-- 方式2：
SELECT department_id,last_name
FROM employees
WHERE department_id>=30 AND department_id<=90;
-- 案例2：查询年薪不是100000-200000之间的员工姓名、工资、年薪。
-- 方式1：
SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;
-- 方式2：
SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0))<100000 OR salary*12*(1+IFNULL(commission_pct,0))>200000;
----------IS NULL/IS NOT NULL----------
/*
IS NULL/IS NOT NULL
功能：判断某个字段的值是否为空/非空
*/
-- 案例1：查询没有奖金的员工信息。
SELECT *
FROM employees
WHERE commission_pct IS NULL;
-- 案例2：查询有奖金的员工信息。
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;
-- 案例3：查询工资为10000的员工信息。
SELECT *
FROM employees
WHERE salary IS 10000;
08、单行函数
/*
1）字符函数
concat
substr
trim
instr
strcmp
upper/lower
length/char_length
left/right
lpad/rpad
2）数学函数
abs
ceil
floor
round
truncate
mod
3）日期函数
now
curtime
curdate
datediff
date_format
str_to_date
4）流程控制
if
case
*/
--------------------字符函数--------------------
-- 1、CONCAT 拼接字符
SELECT CONCAT('hello,',first_name,last_name) 备注 FROM employees;
-- 2、SUBSTR 拼接字符
/*
注意：起始索引从1开始！！！
substr(str,起始索引，截取的字符长度)
substr(str,起始索引)
*/
-- 查询结果为：张三丰
SELECT SUBSTR('张三丰爱上了郭襄',1,3);
-- 查询结果为：郭襄
SELECT SUBSTR('张三丰爱上了郭襄',7);
-- 3、TRIM 去前后指定的字符，默认是去空格
-- 查询结果为：虚 竹
SELECT TRIM(' 虚 竹 ') AS a;
-- 查询结果为：虚xxx竹
SELECT TRIM('x' FROM 'xxxxxx虚xxx竹xxxxxxxxxxxxxxxxxx') AS a;
-- 4、INSTR 获取子串第一次出现的索引
-- 查询结果为：3
SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');
-- 5、STRCMP 比较两个字符大小
-- 查询结果为：1
SELECT STRCMP('afc','aec');
-- 查询结果为：0
SELECT STRCMP('aec','aec');
-- 查询结果为：-1
SELECT STRCMP('aec','afc');
-- 6、UPPER/LOWER 变大写/变小写
-- 查询结果为：HELLO
SELECT UPPER('Hello');
-- 查询结果为：hello
SELECT LOWER('Hello');
-- 7、LENGTH/CHAR_LENGTH 获取字节长度/获取字节长度
-- 查询结果为：12，原因是Mysql使用UTF-8编码，在UTF-8编码中，一个中文占用3个字节长度
SELECT LENGTH('hello,郭襄');
-- 查询结果为：8
SELECT CHAR_LENGTH('hello,郭襄');
-- 8、LEFT/RIGHT 截取子串
-- 查询结果为：鸠
SELECT LEFT('鸠摩智',1);
-- 查询结果为：智
SELECT RIGHT('鸠摩智',1);
-- 9、LPAD/RPAD 左填充/右填充
-- 查询结果为：aaaaaaa木婉清
SELECT LPAD('木婉清',10,'a');
-- 查询结果为：木婉清aaaaaaa
SELECT RPAD('木婉清',10,'a');
--------------------数学函数--------------------
-- 1、ABS 绝对值
-- 查询结果为：2.4
SELECT ABS(-2.4);
-- 2、CEIL 向上取整
-- 查询结果为：-1
SELECT CEIL(-1.09);
-- 查询结果为：1
SELECT CEIL(0.09);
-- 查询结果为：1
SELECT CEIL(1.00);
-- 3、FLOOR 向下取整
-- 查询结果为：-2
SELECT FLOOR(-1.09);
-- 查询结果为：0
SELECT FLOOR(0.09);
-- 查询结果为：1
SELECT FLOOR(1.00);
-- 4、ROUND 四舍五入
-- 查询结果为：2
SELECT ROUND(1.8712345);
-- 查询结果为：1.87
SELECT ROUND(1.8712345,2);
-- 5、TRUNCATE 截断
-- 查询结果为：1
SELECT TRUNCATE(1.8712345,0);
-- 查询结果为：1.8
SELECT TRUNCATE(1.8712345,1);
-- 6、MOD 取余
-- 查询结果为：1
SELECT MOD(10,3);
-- 查询结果为：-1
SELECT MOD(-10,3);
-- 查询结果为：1
SELECT MOD(10,-3);
--------------------日期函数--------------------
-- 1、NOW
-- 查询结果为：2020-09-10 14:30:28
SELECT NOW();
-- 2、CURDATE
-- 查询结果为：2020-09-10
SELECT CURDATE();
-- 3、CURTIME
-- 查询结果为：14:30:28
SELECT CURTIME();
-- 4、DATEDIFF 函数返回两个日期之间的天数
-- 查询结果为：-7667
SELECT DATEDIFF('1998-7-16','2019-7-13');
-- 查询结果为：7667
SELECT DATEDIFF('2019-7-13','1998-7-16');
-- 5、DATE_FORMAT 将日期转为指定格式的字符串
-- 查询结果为：1998年July月16日 00小时00分钟00秒
SELECT DATE_FORMAT('1998-7-16','%Y年%M月%d日 %H小时%i分钟%s秒');
-- 6、STR_TO_DATE 将字符串转为指定格式的日期
-- 查询结果为：1998-03-15
SELECT STR_TO_DATE('3/15 1998','%m/%d %Y')
--------------------流程函数--------------------
-- 1、IF函数
-- 查询结果为：大
SELECT IF(100>9,'大','小');
-- 需求：如果有奖金，则显示最终奖金，如果没有，则显示0
SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct) 奖金,commission_pct
FROM employees;
-- 2、CASE函数
/*
语法：类似于switch语句，可以实现等值判断
CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
...
ELSE 结果n
END
案例：
部门编号是30，工资显示为2倍
部门编号是50，工资显示为3倍
部门编号是60，工资显示为4倍
否则不变
*/
SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END newSalary
FROM employees;
/*
语法：类似于多重IF语句，实现区间判断
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
...
ELSE 结果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 a
FROM employees;
09、聚合函数
/*
1）说明
分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数
2）清单：
sum(字段名)：求和
avg(字段名)：求平均数
max(字段名)：求最大值
min(字段名)：求最小值
count(字段名)：计算非空字段值的个数
*/
-- 案例1 ：查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数
SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM employees;
-- 案例2：添加筛选条件
-- ①查询emp表中记录数：
SELECT COUNT(employee_id) FROM employees;
-- ②查询emp表中有佣金的人数：
SELECT COUNT(salary) FROM employees;
-- ③查询emp表中月薪大于2500的人数：
SELECT COUNT(salary) FROM employees WHERE salary>2500;
-- #④查询有领导的人数：
SELECT COUNT(manager_id) FROM employees;
-- COUNT的补充介绍
-- 1、统计结果集的行数，推荐使用count(*)
SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM employees WHERE department_id = 30;
SELECT COUNT(1) FROM employees;
SELECT COUNT(1) FROM employees WHERE department_id = 30;
-- 2、搭配distinct实现去重的统计
-- 需求：查询有员工的部门个数
SELECT COUNT(DISTINCT department_id) FROM employees;
10、分组查询
--------------------简单的分组--------------------
-- 案例1：查询每个工种的员工平均工资
SELECT AVG(salary), job_id
FROM employees
GROUP BY job_id;
-- 案例2：查询每个领导的手下人数
SELECT COUNT(*), manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;
--------------------可以实现分组前的筛选--------------------
-- 案例1：查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary), department_id
FROM employees
WHERE email LIke '%a%'
GROUP BY department_id;
-- 案例2：查询每个领导手下有奖金的员工的平均工资
SELECT AVG(salary), manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
--------------------可以实现分组后的筛选--------------------
-- 案例1：查询哪个部门的员工个数大于5
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;
-- 案例2：每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资
SELECT job_id, MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;
-- 案例3：领导编号大于102的每个领导手下的最低工资大于5000的最低工资
SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;
--------------------可以实现排序--------------------
-- 案例：查询没有奖金的员工的最高工资大于6000的工种编号和最高工资,按最高工资升序
SELECT job_id, MAX(salary)
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id
HAVING MAX(salary) > 6000
ORDER BY MAX(salary) ASC;
--------------------按多个字段分组--------------------
-- 案例：查询每个工种每个部门的最低工资,并按最低工资降序
SELECT department_id, job_id, MIN(salary)
FROM employees
GROUP BY department_id, job_id
ORDER BY MIN(salary) DESC;
11、连接查询
/*
1、什么是连接查询：
连接查询又称多表查询，当查询语句涉及到的字段来自于多个表时，就会用到连接查询
2、什么是笛卡尔乘积现象：
表1有m行，表2有n行，当表1和表2进行连接查询时，结果为m*n行
3、为什么会有笛卡尔现象，怎么解决：
1）发生原因：没有有效的连接条件
2）如何避免：添加有效的连接条件
4、连接查询的分类：
1）按年代分类：
1、sql92标准:仅仅支持内连接
内连接：
等值连接
非等值连接
自连接
2、sql99标准：
内连接
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
全外连接（MySQL不支持全外连接）
交叉连接
2）按功能分类：
1、内连接：
等值连接
非等值连接
自连接
2、外连接：
左外连接
右外连接
全外连接
3、交叉连接
*/
--------------------SQL92语法--------------------
-- 1.1）等值连接
-- 案例1(简单连接)：查询员工对应的部门名
SELECT e.last_name, d.department_name
FROM employees AS e, departments AS d
WHERE e.department_id = d.department_id;
-- 案例2(条件查询)：查询有奖金的员工名、部门名
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;
-- 案例3(模糊查询)：查询城市名中第二个字符为o的部门名和城市名
SELECT d.department_name, l.city
FROM departments d, locations l
WHERE d.location_id = l.location_id
AND l.city LIKE '_o%';
-- 案例4(分组查询)：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT d.department_name, d.manager_id, MIN(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.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 j.job_title
ORDER BY COUNT(*) DESC;
-- 案例6(三表连接)：查询员工名、部门名和所在的城市
SELECT e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.city LIKE 's%'
ORDER BY d.department_name DESC;
-- 1.2）非等值连接
-- 案例1：查询员工的工资和工资级别
SELECT e.salary, s.grade
FROM employees e, sal_grade s
WHERE e.salary BETWEEN s.min_salary AND s.max_salary;
-- 1.3）自连接
-- 案例1：查询员工名和上级的名称
SELECT e.last_name, m.last_name
FROM employees e, employees m
WHERE e.employee_id = m.manager_id;
--------------------SQL99语法--------------------
----------内连接----------
-- 1.1）等值连接
-- 案例1(简单查询)：查询员工名和部门名
SELECT e.last_name, d.department_name
FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;
-- 案例2(条件查询)：查询部门编号大于100的部门名和所在的城市名
SELECT d.department_name, l.city
FROM departments d INNER JOIN locations l ON d.location_id = l.location_id
WHERE d.department_id > 100;
-- 案例3(分组查询)：查询每个城市的部门个数
SELECT l.city, COUNT(*)
FROM locations l INNER JOIN departments d ON l.location_id = d.location_id
GROUP BY l.location_id;
-- 案例4(分组查询)：查询部门中员工个数大于10的部门名，并按员工个数降序
SELECT d.department_name, COUNT(*)
FROM employees e INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY e.department_id
HAVING COUNT(*) > 10
ORDER BY COUNT(*) DESC;
-- 1.2）非等值连接
-- 案例1：查询部门编号在10-90之间的员工的工资级别与个数，并按级别进行分组
SELECT s.grade, COUNT(*)
FROM employees e INNER JOIN sal_grade s ON e.salary BETWEEN s.min_salary AND s.max_salary
WHERE e.department_id BETWEEN 10 AND 90
GROUP BY s.grade;
-- 1.3）自连接
-- 案例1：查询员工名和对应的领导名
SELECT e.last_name, m.last_name
FROM employees e INNER JOIN employees m ON e.employee_id = m.manager_id
----------外连接----------
/*
1）说明：查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则显示null
2）应用：一般用于查询主表中有但从表没有的记录
3）特点：
1、外连接分主从表，两表的顺序不能任意调换
2、左连接的话，left join左边为主表，右连接的话，right join右边为主表
4）语法：
select 查询列表
from 表1 别名
left|right|full 【outer】 join 表2 别名
on 连接条件
where 筛选条件;
*/
2.1）左外连接
-- 案例1：查询哪个部门没有员工，并显示其部门编号和部门名
SELECT COUNT(*) 部门个数
FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
2.2）右外连接
2.3）全外连接
12、嵌套查询
/*
1）说明：
1、当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询
2、外面的select语句称为主查询或外查询。
2）分类：
1、select后面
要求：子查询的结果为单行单列（标量子查询）
2、from后面
要求：子查询的结果为多行多列（子表）
3、where或having后面
要求：子查询的结果为单列（标量子查询、多行子查询）
4、exists后面
要求：子查询结果为单列（相关子查询）
3）特点：
1、子查询放在条件中，要求必须放在条件的右侧
2、子查询一般放在小括号中
3、子查询的执行优先于主查询
4、单行子查询对应了 单行操作符：> < >= <= = <>
多行子查询对应了 多行操作符：any/some all in
*/
--------------------子查询放在SELECT后面--------------------
#案例；查询部门编号是50的员工个数
SELECT (
SELECT COUNT(*)
FROM employees
WHERE department_id = 50
) 员工个数;
--------------------子查询放在FROM后面--------------------
#案例：查询每个部门的平均工资的工资级别
SELECT das.*, s.grade
FROM (
SELECT IFNULL(department_id, 0) AS deptId, AVG(salary) AS avgSalary
FROM employees
GROUP BY department_id
) das INNER JOIN sal_grade s ON das.avgSalary BETWEEN s.min_salary AND s.max_salary;
--------------------子查询放在WHERE后面--------------------
#案例1：工资比Abel高的员工信息（标量子查询）
SELECT *
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
#案例2：返回location_id是1400或1700的部门中的所有员工姓名（多行子查询）
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
)
--------------------子查询放在HAVING后面--------------------
#案例1：查询最低工资大于50号部门最低工资的部门id和其最低工资（标量子查询）
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
#案例2：返回location_id是1400或1700的部门中的所有员工姓名（多行子查询）
--------------------子查询放在EXISTS后面--------------------
#案例：查询有无名字叫Abel的员工信息
SELECT EXISTS(
SELECT 1
FROM employees
WHERE last_name = 'Abel'
) 有无Abel;
13、分页查询
/*
1）语法：
SELECT DISTINCT <select_list>
FROM <left_table> <join_type> JOIN <right_table> ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
2）顺序
FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
3）特点：
①起始条目索引如果不写，默认是0
②limit后面支持两个参数
参数1：显示的起始条目索引
参数2：条目数
4）公式：
假如要显示的页数是page，每页显示的条目数为size
SELECT *
FROM 表
LIMIT (page-1)*size,size;
*/
-- 案例1：查询员工信息表的前5条
SELECT * FROM employees LIMIT 0,5;
-- 案例2：查询有奖金的，且工资较高的第11名到第20名
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10,10;
14、联合查询
/*
1）说明：
当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称为union查询
2）语法：
SELECT 查询列表 FROM 表1 WHERE 筛选条件
UNION
SELECT 查询列表 FROM 表1 WHERE 筛选条件
3）特点：
1、多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致
2、UNION实现去重查询、UNION ALL实现全部查询(包含重复项)
*/
-- 案例：查询所有国家的年龄>20岁的用户信息
SELECT * FROM usa WHERE uage > 20
UNION
SELECT * FROM chinese WHERE age > 20 ;
15、数据操纵
/*
一、插入语句：
1）语法：
插入单行：
insert into 表名(字段名1,字段名2 ,...) values(值1，值2,...);
插入多行：
insert into 表名(字段名1,字段名2 ,...) values(值1，值2,...),(值1，值2,...);
2）特点：
①字段和值列表一一对应，包含类型、约束等必须匹配；
②数值型的值，不用单引号，非数值型的值，必须使用单引号；
③字段顺序无要求；
*/
-- 案例1：要求字段和值列表一一对应，且遵循类型和约束的限制
INSERT INTO stuinfo(stuid,stuname,stugender,email,age,majorid) VALUES(1,'吴倩','男','wuqian@qq.com',12,1);
-- 案例2：可以为空字段可以不插入
INSERT INTO stuinfo(stuid,stuname,email,majorid) VALUES(5,'齐鱼','qiqin@qq.com',2);
-- 案例3：字段可以插入空值（字段名写上，值使用null）
INSERT INTO stuinfo(stuid,stuname,email,age,majorid) VALUES(5,'齐鱼','qiqin@qq.com',NULL,2);
-- 案例4：字段可以插入默认值（字段名写上，值使用default）
INSERT INTO stuinfo(stuid,stuname,email,stugender,majorid) VALUES(7,'齐小鱼','qiqin@qq.com',DEFAULT,2);
-- 案例5：可以省略字段列表，默认所有字段
INSERT INTO stuinfo VALUES(8,'林忆莲','女','lin@126.com',12,3);
/*
二、删除语句：
1）说明：
①delete不加条件删除时，删除的是整张表的数据，加条件时只会删除指定行的数据。
②truncate是直接删除整张表的数据.
2）语法：
方式一：delete from 表名 【where 筛选条件】;
方式二：truncate table 表名;
*/
-- 案例1：删除姓李所有信息
DELETE FROM stuinfo WHERE stuname LIKE '李%';
-- 案例2：删除表中所有数据
TRUNCATE TABLE stuinfo;
-- 案例3：delete和truncate的区别
1.delete可以添加WHERE条件删除指定的数据，truncate不能添加WHERE条件，一次性清除所有数据;
2.truncate的效率较高;
3.如果删除带自增长列的表：
①使用delete删除后，重新插入数据，记录从断点处开始;
②使用truncate删除后，重新插入数据，记录从1开始;
4.delete删除数据，会返回受影响的行数，truncate删除数据，不返回受影响的行数;
5.delete删除数据，可以支持事务回滚，truncate删除数据，不支持事务回滚;
/*
三、修改数据
1）语法：
update 表名 set 字段名 = 新值, 字段名=新值,... where 筛选条件;
*/
-- 案例1：修改年龄<20的专业编号为3号，且邮箱更改为 xx@qq.com
UPDATE stuinfo
SET majorid = 3,
email='xx@qq.com'
WHERE age < 20;
16、数据定义
--------------------库的创建--------------------
CREATE DATABASE 【IF NOT EXISTS】 库名;
--------------------库的删除--------------------
DROP DATABASE 【IF EXISTS】 库名;
--------------------表的创建--------------------
CREATE TABLE 【IF NOT EXISTS【 表名(
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】,
...
字段名 字段类型 【字段约束】
);
-- 案例1：
CREATE TABLE IF NOT EXISTS stuinfo(
-- 添加了主键约束
stuid INT PRIMARY KEY,
-- 添加了唯一约束与非空约束
stuname VARCHAR(20) UNIQUE NOT NULL,
-- 添加了默认约束
stugender CHAR(1) DEFAULT '男',
-- 添加了非空约束
email VARCHAR(20) NOT NULL,
-- 添加了检查约束，mysql不支持
age INT CHECK( age BETWEEN 0 AND 100),
majorid INT,
-- 添加了外键约束
CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id)
);
--------------------表的删除--------------------
DROP TABLE 【IF EXISTS】 表名;
--------------------表的修改--------------------
ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE| COLUMN 字段名 字段类型 【字段约束】;
-- 案例1：增加字段
ALTER TABLE 表名 ADD COLUMN 字段名称 字段类型 字段约束;
ALTER TABLE stuinfo ADD COLUMN birthdate TIMESTAMP NOT NULL
-- 案例2：删除字段
ALTER TABLE 表名 DROP COLUMN 字段名称;
ALTER TABLE stuinfo DROP COLUMN birthdate;
-- 案例3：修改字段类型或约束
ALTER TABLE 表名 MODIFIES COLUMN 字段名称 字段类型 【约束类型】;
ALTER TABLE stuinfo MODIFIES COLUMN birthdate DATATIME;
-- 案例4：修改字段(包括字段名、字段类型、字段约束都可以通过这个语法修改)
ALTER TABLE 表名 CHANGE COLUMN 旧字段名称 新字段名称 字段类型 【字段约束】;
ALTER TABLE stuinfo CHANGE COLUMN birthdate birthday DATETIME NOT NULL;
--------------------表的复制--------------------
-- 表结构复制
CREATE TABLE 新表 LIKE 旧表;
-- 表结构与表数据复制
CREATE TABLE 新表 SELECT * FROM 旧表;
-- 复制部分字段，且不复制数据
-- 案例1：复制employees表中的last_name,department_id,salary字段到新表emp表，且不复制数据
CREATE TABLE emp
SELECT last_name,department_id,salary
FROM myemployees.`employees`
WHERE 1=2;
17、数据类型
/*
一、类型分类：
1）数值型
整型
Tinyint 1字节
Smallint 2字节
Mediumint 3字节
Int/Integer 4字节
Bigint 8字节
小数
浮点数：
float 4字节
double 8字节
定点数
decimal(M,D) 8字节
2）字符型
char
varchar
3）日期型
date
time
year
datetime
timestamp
二、选择原则：
1）更小的通常更好，尽量使用可以正确存储数据的最小数据类型。
2）简单就好，简单数据类型的操作通常需要更少的cpu周期。
3）尽量避免null，如果查询中包含可为null的列，对MySQL来说更难优化，因为可为null的列使得索引、索引统计和值比较都更复杂
*/
/*
整型数特点：
①整型数存在有符号数与无符号数之分，如果不指定是无符号还是有符号，则默认是有符号。可以手动添加UNSIGNED指定为无符号整型，限制负数；
②如果超出类型范围的操作，会发生"out of range"错误提示。插入的值显示为最大值；
示例：
字段id，字段类型为tinyint，插入数值：128。 （超过最大值127）
错误提示："out of range"
插入表中的值为：127
③MySQL支持在类型名称后面的小括号内指定显示宽度。如果数字位数足够或大于，则忽略宽度，如果数字位数不够的空间用字符"0"填满，但要搭配zerofill使用，如果不手动指定宽度，则有默认宽度；
④AUTO_INCREMENT字段自增属性，该属性只能用于整型。AUTO_INCREMENT标识列一般从1开始，每行增加1，不用我们手动插入，但必须定义为PRIMARY_KEY或UNIQUE键
*/
/*
小数特点：
①浮点数与定点数都可以使用类型名称后加(M,D)的方式来表示小数位的精度；
M：精度 ，整数位+小数位一共显示M位数字
D：标度， 小数位数一共显示D位数字，如果不够后面用0补齐，如果超过，则四舍五入
②浮点数和定点数的区别：
1.定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用于表示货币等精度高的数据
2.在不指定精度时，浮点数默认会按照实际的精度来显示，而定点数在不指定精度时，默认M=10，D=0
3.浮点数如果数据超越了精度和标度值，则自动将四舍五入后的结果插入，系统不会报错；
定点数则会报错！
*/
/*
字符型特点：
①存储的列长度
char：列的长度固定为创建表时字段的声明的长度
varchar:列的长度为可变，只要不超过声明的长度M
②检索的时候
char：删除了尾部的空格
varchar：保留这些空格
*/
18、常见约束
/*
1）什么是约束含义：
一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
2）常见的约束有哪些：
1⃣️NOT NULL
非空约束，用于保证该字段的值不能为空；
2⃣️DEFAULT
默认约束，用于保证该字段有默认值；
3⃣️CHECK
检查约束【mysql中不支持】
4⃣️UNIQUE
唯一约束，用于保证该字段的值具有唯一性，可以为空；
5⃣️PRIMARY KEY
主键约束，用于保证该字段的值具有唯一性，并且非空；
6⃣️FOREIGN KEY
外键约束，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值
3）添加约束的时机：
1.创建表时
2.修改表时
4）约束的添加分类：
列级约束：
六大约束语法上都支持，但外键约束没有效果
表级约束：
除了非空、默认，其他的都支持
*/
--------------------创建表时添加约束条件的使用案例--------------------
CREATE TABLE IF NOT EXISTS stuinfo(
-- 添加了主键约束
stuid INT PRIMARY KEY,
-- 添加了唯一约束与非空约束
stuname VARCHAR(20) UNIQUE NOT NULL,
-- 添加了默认约束
stugender CHAR(1) DEFAULT '男',
-- 添加了非空约束
email VARCHAR(20) NOT NULL,
-- 添加了检查约束，mysql不支持
age INT CHECK( age BETWEEN 0 AND 100),
majorid INT,
-- 添加了外键约束
CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id)
);
--------------------在表已经创建好时修改字段的约束案例--------------------
-- 案例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);
--------------------修改表时删除约束案例--------------------
-- 案例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.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
19、自增长列
/*
1）说明：
1、自增长列要求必须设置在一个键上，比如主键或唯一键
2、一个表至多有一个自增长列
3、自增长列要求数据类型为数值型
*/
-- 1）案例：
CREATE TABLE gradeinfo(
gradeID INTEGER PRIMARY KEY AUTO_INCREMENT,
gradeName VARCHAR(20)
);
SELECT * FROM gradeinfo;
INSERT INTO gradeinfo VALUES(NULL,'一年级'),(NULL,'2年级'),(NULL,'3年级');
INSERT INTO gradeinfo(gradename)VALUES('一年级'),('2年级'),('3年级');
-- 2）修改
-- 修改表时设置增常长列
ALTER TABLE gradeinfo MODIFY COLUMN gradeID INTEGER PRIMARY KEY AUTO_INCREMENT;
-- 修改表时删除增常长列
ALTER TABLE gradeinfo MODIFY COLUMN gradeID INTEGER PRIMARY KEY;
-- 3）说明：
1、在Mysql中子增长列的起始索引是从1开始，且无法修改；
2、在MySql中自增长列的增长的步长默认是1，即每次使用，自增常列累加1，该值可以修改：
-- 查询当前连接会话下自增常列的步长信息：
SHOW VARIABLES LIKE '%auto_increment%';
-- 设置当前连接会话下自增常列的步长的值：
SET AUTO_INCREMENT_INCREMENT = 2;
3、如果只是想要使某张表的自增常列的值从某个值开始，可以先插入一条自增常列为指定值的记录，之后的数据使用自增列进行自动插入，这样就可以实现从指定索引开始自增了；
20、事务特性
-- 1、什么是事务
事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。可以理解为：更新数据库中各种数据项的程序执行单元。就比如：同一根绳上的蚂蚱，要活一起活，要死一起死。
-- 2、事务的四大特性ACID
2.1 原子性(Atomicity)
事务中所有操作是不可再分割的原子单元。事务中所有操作要么都执行成功,要么都执行失败。
2.2 一致性(Consistency)
事务执行后,数据库状态与其他业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账户余额之和应该保持不变。
2.3 隔离性(Isolation)
隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会互相干扰。
2.4 持久性(Durability)
一旦事务提交成功，事务中所有的数据操作都必须被持久化保存到数据库中，即使提交事务后，数据库崩溃，在数据库重启时，也必须能保证通过某种机制恢复数
-- 3、不考虑隔离性会产生的三个问题
3.1 脏读
脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
3.2 不可重复读
一个事务两次读取同一行的数据，结果得到不同状态的结果，中间正好另一个事务更新了该数据，两次结果相异，不可被信任。通俗来讲就是：事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。
3.3 幻读（虚读）
一个事务执行两次查询，第二次结果集包含第一次中没有或某些行已经被删除的数据，造成两次结果不一致，只是另一个事务在这两次查询中间插入或删除了数据造成的。通俗来讲就是：例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
-- 4、解决办法：四种隔离级别
4.1 Read Uncommited（读取未提交内容）
未提交读，顾名思义，就是一个事务可以读取另一个未提交事务的数据。但是，存在脏读、不可重复读、幻读问题。
4.2 Read Committed（读取提交内容）
提交读，顾名思义，就是一个事务要等另一个事务提交后才能读取数据，可以解决脏读问题，但是存在不可重复读、幻读问题。
4.3 Repeatable Read（重复读）
重复读，就是在开始读取数据（事务开启）时，不再允许修改操作。重复读可以解决不可重复读问题，但是存在幻读问题。应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。采用Serializable可以解决幻读问题
4.4 Serializable（可串行化）
Serializable是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
-- 5、Mysql开启显示事物提交操作
SET AUTOCOMMIT = 0;
START TRANSACTION;
要执行的SQL语句;
COMMIT/ROLLBACK;
-- 6、事物回滚点设置
SET AUTOCOMMIT = 0;
START TRANSACTION;
DELETE FROM employees WHERE employee_id = 100;
-- 设置回滚点，当DML操作进行回滚操作时，该语句之前的操作不会被回滚，该语句之后的操作会被回滚
SAVEPOINT rollbackTo;
DELETE FROM employees WHERE employee_id = 101;
-- 回滚到回滚点出
ROLLBACK TO rollbackTo;
-- 7、MySql设置事物隔离级别
1、每启动一个MySql会话，就会获得一个单独的数据库连接，每个数据库连接都有一个全局的@@tx_isolation，表示当前的事物隔离级别。
2、查看当前会话的事物隔离级别：
SELECT @@tx_isolation;
3、设置当前会话的事物隔离级别：
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
4、设置数据库系统的全局隔离级别：
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
5、MySql中默认的事物隔离级别为：
REPEATABLE READ;
6、Oracle中默认的事物隔离级别为：
READ COMMITTED;
21、数据视图
-- 1、视图概述
视图是一个虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义。同真实表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。
-- 2、视图作用
简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
-- 3、视图创建
-- 视图的创建语法
CREATE 【OR REPLACE】 VIEW 视图名 AS 子查询;
-- 视图的创建案例：
CREATE OR REPLACE VIEW empView AS (SELECT * FROM employees WHERE department_id = 10);
-- 4、视图删除
-- 视图的删除语法
DROP VIEW 【IF EXISTS】 视图名;
-- 视图的删除案例
DROP VIEW IF EXISTS empView;
-- 5、视图修改
-- 视图的修改语法
方式一：CREATE OR REPLACE VIEW AS 自查询;
CREATE OR REPLACE VIEW empView
AS (SELECT * FROM employees WHERE department_id = 20);
方式二：ALTER VIEW 视图名 AS 自查询;
ALTER VIEW empView
AS (SELECT * FROM employees WHERE department_id = 30);
-- 6、视图查询
-- 从MySQL5.1版本开始，使用"SHOW TABLES"命令的时候不仅显示表的名字，同时也会显示视图的名字
SHOW TABLES;
-- 查询视图的创建详情
SHOW CREATE VIEW 视图名;
-- 7、注意事项
1、MySQL 视图的定义有一些限制，例如，在FROM 关键字后面不能包含子查询，这和其他数据库是不同的，如果视图是从其他数据库迁移过来的，那么可能需要因此做一些改动，可以将子查询的内容先定义成一个视图，然后对该视图再创建视图就可以实现类似的功能了。
2、视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可更新的：
1）包含以下关键字的SQL语句：聚合函数（SUM、MIN、MAX、COUNT 等）、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL;
2）常量视图;
3）SELECT中包含子查询;
4）JION;
5）FROM一个不能更新的视图;
6）WHERE字句的子查询引用了FROM字句中的表;
22、变量使用
/*
根据MySQL手册，MySQL的变量分为两种：
1）系统变量：变量由DBMS提供，不是用户定义的，属于服务器层面的变量，根据变量的作用域不同又细分为全局变量与会话变量
全局变量：
会话变量：
2）自定义变量：用户自己定义的变量，不是系统提供的
用户变量
局部变量
*/
--------------------全局变量--------------------
-- 1、全局变量的作用域
DBMS服务器每次启动将为所有的全局变量赋初始值，并且所有的会话(连接)共享这些全局变量。
-- 2、全局变量的操作
-- 2.1 查询所有的全局变量
SHOW GLOBAL VARIABLES;
-- 2.2 查询指定全局变量的值
SELECT @@GLOBAL.AUTOCOMMIT;
-- 2.2 修改全局变量的两种方式
方式一：
SET GLOBAL AUTOCOMMIT = 0;
方式二：
SET @@GLOBAL.AUTOCOMMIT = 0;
--------------------会话变量--------------------
-- 1、会话变量的作用域
DBMS服务器为每个会话单独提供了一份会话变量，其作用域仅限于当前连接，即每个连接中的会话变量是独立的。
-- 2、会话变量的操作
-- 2.1 查询所有的会话变量
SHOW SESSION VARIABLES;
-- 2.2 查询指定会话变量的值的两种方式
方式一：
SELECT @@SESSION.AUTOCOMMIT;
方式二：
SELECT @@AUTOCOMMIT;
-- 2.3 修改会话变量的两种方式
方式一：
SET @@SESSION.AUTOCOMMIT = 1;
方式二：
SET @@AUTOCOMMIT = 0;
--------------------用户变量--------------------
-- 1、用户变量的作用域
当前会话(连接)有效，其作用域与会话变量相同
-- 2、用户变量的操作
-- 2.1 用户变量的使用步骤
1）声明
2）赋值
3）使用（查询、比较、运算）
-- 2.2 用户变量的声明并初始化的三种方式
方式一：
SET @用户变量名 = 值;
具体案例：
SET @countNum = 0;
方式二：
SET @用户变量名 := 值;
具体案例：
SET @countNum := 0;
方式三：
SELECT @用户变量名 := 值;
具体案例：
SELECT @countNum := 0;
-- 2.3 用户变量赋值的两种方式
方式一：还是通过SET或SELECT关键字赋值操作
SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名 := 值;
方式二：通过SELECT INTO语句进行赋值操作
SELECT 字段 INTO 变量名 FROM 表;
具体案例：
SELECT COUNT(*) INTO @countNum FROM employees;
-- 2.4 用户变量的使用
SELECT 用户变量名;
具体案例：
SELECT @countNum;
-- 2.5案例：声明两个变量并求和
SET @a = 1;
SET @b = 2;
SET @sum = @a + @b;
SELECT @sum;
--------------------局部变量--------------------
-- 1、局部变量的作用域
只能用在BEGIN/END语句块中使用，且必须在语句块的第一句，比如存储过程中的BEGIN/END语句块。
-- 2、局部变量的操作
-- 2.1 用户变量的使用步骤
1）声明
2）赋值
3）使用（查询、比较、运算）
-- 2.2 局部变量声明语法
DECLARE 变量名 变量类型 【DEFAULT 默认值】;
-- 2.3 局部变量赋值的两种方式
方式一：还是通过SET或SELECT关键字赋值操作
SET 用户变量名 = 值;
SET 用户变量名 := 值;
SELECT @用户变量名 := 值;
方式二：通过SELECT INTO语句进行赋值操作
SELECT 字段 INTO 变量名 FROM 表;
具体案例：
SELECT COUNT(*) INTO @countNum FROM employees;
-- 2.4案例：声明两个变量并求和
BEGIN
DECLARE a INTEGER DEFAULT 1;
DECLARE b INTEGER DEFAULT 2;
DECLARE sum INTEGER DEFAULT 0;
SET sum = a + b;
SELECT sum;
END
23、存储过程
--------------------存储过程简介--------------------
存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
--------------------储存过程优缺--------------------
--------------------存储过程语法--------------------
-- 1、删除存储过程的语法
DROP PROCEDURE IF EXISTS 存储过程名;
-- 2、存储过程创建语法
DELIMITER & -- 定义存储过程语句结束标记，使用什么字符作为标记任意
CREATE PROCEDURE 存储过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]])
BEGIN
存储过程体;
END & -- 存储过程结束时使用结束标记表示定义结束
DELIMITER ; -- 重新将SQL语句的结束标记定义为逗号
-- 参数模式说明
IN：表示输入参数
OUT：表示输出参数
INOUT：表示既可以输入也可以输出的参数
-- 3、查询存储过程的信息
SHOW CREATE PROCEDURE 存储过程名;
--------------------存储过程案例--------------------
-- 案例1(空参数列表)：插入登陆用户数据到admin表
-- 定义存储过程
DELIMITER & -- 重新定义SQL的结束标记
CREATE PROCEDURE adminProcedure()
BEGIN
INSERT INTO `admin` VALUES(NULL,'Scott','Tiger');
INSERT INTO `admin` VALUES(NULL,'Root','Root');
END & -- 使用结束标记结束存储过程的定义
DELIMITER ; -- 将SQL的结束标记重新定义成逗号
-- 执行存储过程
CALL adminProcedure(); -- 调用存储过程
SELECT * FROM `admin`; -- 查询登陆表发现数据已经进去，成功
-- 案例2(带IN参数列表)：根据用户编号更新用户名与密码
-- 定义存储过程
DELIMITER & -- 重新定义SQL的结束标记
CREATE PROCEDURE adminProcedure(IN id INTEGER, IN username VARCHAR(32), IN pwd VARCHAR(32))
BEGIN
UPDATE admin
SET admin.username = username,
admin.password = pwd
WHERE admin.id = id;
END & -- 使用结束标记结束存储过程的定义
DELIMITER ; -- 将SQL的结束标记重新定义成逗号
-- 定义用户变量
SET @id := '1';
SET @username := 'scott';
SET @pwd := 'tiger';
-- 执行存储过程
CALL adminProcedure(@id, @username, @pwd);
-- 查询执行结果
SELECT * FROM admin; --scott, tiger
-- 案例3(带OUT参数列表)：根据用户编号查询用户的账号与密码，并返回到用户变量中
DELIMITER & -- 重新定义SQL的结束标记
CREATE PROCEDURE adminProcedure(IN id INTEGER, OUT username VARCHAR(32), OUT pwd VARCHAR(32))
BEGIN
SELECT admin.username, admin.password INTO username, pwd
FROM admin
WHERE admin.id = id;
END & -- 使用结束标记结束存储过程的定义
DELIMITER ; -- 将SQL的结束标记重新定义成逗号
-- 定义用户变量
SET @id := '1';
SET @username := '';
SET @pwd := '';
-- 执行存储过程
CALL adminProcedure(@id, @username, @pwd);
-- 查询用户变量
SELECT @username, @pwd; -- Scott, Tiger
-- 案例4(带INOUT参数列表)：传入两个整型变量，将变量的值分别翻倍并返回
DELIMITER & -- 重新定义SQL的结束标记
CREATE PROCEDURE adminProcedure(INOUT x INTEGER , INOUT y INTEGER)
BEGIN
SET x := 2 * x;
SET y := 2 * y;
END & -- 使用结束标记结束存储过程的定义
DELIMITER ; -- 将SQL的结束标记重新定义成逗号
-- 定义用户变量
SET @a := 20;
SET @b := 40;
-- 执行存储过程
CALL adminProcedure(@a, @b);
-- 查询用户变量
SELECT @a, @b; -- 40, 80
24、存储函数
--------------------什么是存储函数--------------------
存储函数与存储过程一样，是由SQL语句和过程式语句组成的代码片段
--------------------存储函数和存储过程的区别--------------------
--------------------存储函数的语法--------------------
-- 1、删除存储函数的语法
DROP PROCEDURE IF EXISTS 存储过程名;
-- 2、存储函数创建语法
DELIMITER & -- 定义存储过程语句结束标记，使用什么字符作为标记任意
CREATE FUNCTION 存储函数名([参数名 数据类型[,参数名 数据类型…]]) RETURNS 返回值类型
BEGIN
函数过程体;
RETURN 返回值; -- 有且仅有一个返回值
END & -- 存储过程结束时使用结束标记表示定义结束
DELIMITER ; -- 重新将SQL语句的结束标记定义为逗号
-- 3、调用存储函数的语法
SELECT 函数名(参数列表);
-- 4、查询存储函数的定义
SHOW CREATE FUNCTION 函数名;
--------------------存储函数的使用案例--------------------
-- 案例1(空参数列表)：查询admin表中的用户数量
-- 定义存储函数
DELIMITER & -- 定义存储过程语句结束标记，使用什么字符作为标记任意
CREATE FUNCTION adminFunction() RETURNS INTEGER
BEGIN
DECLARE result INTEGER DEFAULT 0; -- 定义局部局部变量result
SELECT COUNT(*) INTO result -- 将查询结果赋值给局部变量result
FROM admin;
RETURN result; -- 将局部变量result的值返回给函数调用者
END & -- 存储过程结束时使用结束标记表示定义结束
DELIMITER ; -- 重新将SQL语句的结束标记定义为逗号
-- 调用存储函数
SELECT adminFunction();
-- 案例2(多参列表)：根据用户名查询用户密码，并将结果存放到用户变量中
DELIMITER & -- 定义存储过程语句结束标记，使用什么字符作为标记任意
CREATE FUNCTION adminFunction(username VARCHAR(32)) RETURNS VARCHAR(32)
BEGIN
SET @password := ''; -- 定义用户变量@password
SELECT admin.password INTO @password -- 将查询结果赋值给用变量@password
FROM admin
WHERE admin.username = username;
RETURN @password; -- 有且仅有一个返回值
END & -- 存储过程结束时使用结束标记表示定义结束
DELIMITER ; -- 重新将SQL语句的结束标记定义为逗号
-- 定义用户变量
SET @username := 'scott';
-- 调用存储函数
SELECT adminFunction(@username);
-- 查询用户变量
SELECT @password; -- tiger
25、流程控制
--------------------IF函数--------------------
1）语法形式：
IF(表达式1,表达式2,表达式3);
2）语法说明：
如果表示1成立，则返回表达式2的值，否则返回表达式3的值;
--------------------CASE结构--------------------
1）语法形式一：类似于java中的switch语句，一般用于实现等值判断
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1或语句1;
WHEN 要判断的值 THEN 返回的值2或语句2;
。。。
[ELSE 以上都不满足时返回的默认值;]
END CASE;
2）语法形式二：类似于java中的多重if语句，一般用于实现区间判断
CASE
WHEN 要判断的条件1 THEN 返回的值1或语句1;
WHEN 要判断的条件2 THEN 返回的值2或语句2;
。。。
[ELSE 以上都不满足时返回的默认值;]
END CASE;
--------------------多重IF分支结构--------------------
-- 1）语法形式：
IF 条件1 THEN 表达式1;
ELSEIF 条件2 THEN 表达式2;
...
ELSEIF 条件N THEN 表达式N;
[ELSE 当以上条件都不满足时的默认结果]
END IF;
--------------------IF函数、CASE结构、多重IF分支结构的使用案例--------------------
-- 1）IF分支使用案例：
SELECT IF(commission_pct IS NULL,0,commission_pct) FROM employees
-- 作用等同于：
SELECT IFNULL(commission_pct,0) FROM employees;
-- 2）CASE语法形式一案例：类似于java中的switch语句，一般用于实现等值判断
/*
需求：
部门编号是30，工资显示为2倍
部门编号是50，工资显示为3倍
部门编号是60，工资显示为4倍
否则不变
*/
SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END newSalary
FROM employees;
-- 3）CASE语法形式二案例：类似于java中的多重if语句，一般用于实现区间判断
/*
需求：
创建存储过程，根据传入的，显示对应的等级：
90-100 -> 显示A；
80-90 -> 显示B；
70-80 -> 显示C；
60-70 -> 显示D；
0-60 -> 显示E；
*/
-- 定义存储过程
DELIMITER &
CREATE PROCEDURE showGrade(IN score INTEGER)
BEGIN
CASE
WHEN score BETWEEN 90 AND 100 THEN SELECT 'A';
WHEN score BETWEEN 80 AND 90 THEN SELECT 'B';
WHEN score BETWEEN 70 AND 80 THEN SELECT 'C';
WHEN score BETWEEN 60 AND 70 THEN SELECT 'D';
ELSE SELECT 'D';
END CASE;
END &
DELIMITER ;
-- 调用存储过程
CALL showGrade(90); -- A
-- 4）多重IF案例
/*
需求：
创建存储过程，根据传入的，返回对应的等级：
90-100 -> 返回A；
80-90 -> 返回B；
70-80 -> 返回C；
60-70 -> 返回D；
0-60 -> 返回E；
*/
-- 定义存储函数
DELIMITER &
CREATE FUNCTION showGrade(score INTEGER) RETURNS CHAR(1)
BEGIN
IF score BETWEEN 90 AND 100 THEN RETURN 'A';
ELSEIF score BETWEEN 80 AND 90 THEN RETURN 'B';
ELSEIF score BETWEEN 70 AND 80 THEN RETURN 'C';
ELSEIF score BETWEEN 60 AND 70 THEN RETURN 'D';
ELSE RETURN 'D';
END IF;
END &
DELIMITER ;
-- 调用存储函数
SELECT showGrade(90); -- A
--------------------循环结构--------------------
-- 1）WHILE循环语法（类似于java的while循环）
[标签名:] WHILE 循环条件 DO
循环体；
END WHILE [标签名];
-- 2）REPEAT循环语法（类似于java的do while循环）
[标签名:] REPEAT
循环体;
UNTIL 循环结束条件;
END REPEAT [标签名];
-- 3）LOOP循环语法（想要结束需要配合循环控制语句，可以用来模拟简单的死循环）
[标签名:] LOOP
循环体;
END LOOP [标签名];
-- 4）循环控制：
ITERATE：
类似java的continue关键字，结束本次循环，继续下一次；
LEAVE：
类似于java的break关键字，结束当前所在的循环；
--------------------循环结构的使用案例--------------------
-- 案例：向stringContent表插入指定个数(只插入偶数次)的随机字符串
-- 删除表
DROP TABLE IF EXISTS stringContent;
-- 创建表
CREATE TABLE stringContent (
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(32)
)
-- 删除存储过程
DROP PROCEDURE IF EXISTS stringContentPro;
DELIMITER &
-- 定义存储过程（此处不使用函数是因为本需求没有返回值-->函数有且仅有一个返回值）
CREATE PROCEDURE stringContentPro(IN insertCount INTEGER)
BEGIN
-- 定义循环的起始索引
DECLARE i INTEGER DEFAULT 0;
-- 定义要随机插入的内容范围
DECLARE content VARCHAR(32) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
-- 定义要随机插入的具体内容
DECLARE insertContent VARCHAR(32) DEFAULT '';
-- 定义截取的开始索引（1-26的随机数）
DECLARE startIndex INTEGER DEFAULT 0;
-- 定义截取内容的长度
DECLARE length INT DEFAULT 1;
ret: WHILE i < insertCount DO
-- 循环累加
SET i := i+1;
IF MOD(i,2) != 0 THEN ITERATE ret;
END IF;
-- 获取开始索引随机整数
SET startIndex := FLOOR(RAND()*26+1);
-- 获取截取长度随机整数
SET length := FLOOR(RAND()*(26-startIndex+1)+1);
-- 获取截取内容
SET insertContent := SUBSTR(content,startIndex,length);
-- 数据落地
INSERT INTO stringContent VALUES(NULL,insertContent);
END WHILE ret;
END &
DELIMITER ;
-- 调用存储函数
CALL stringContentPro(10);
-- 查询结果集
SELECT * FROM stringContent;
26、数据索引
mysql
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。