MySQL

一、数据库的好处

1. 可以持久话数据到本地
2. 结构化查询（SQL）

二、数据库的常见概念 *

1. DB：数据库 存储有组织的数据的容器
2. DBMS：数据库管理系统，又称为数据库软件或者数据库产品（eg. MySQL），用于创建和管理数据库
3. SQL：结构化查询语言：与数据库通信，实现数据库操作，几乎所有的主流的数据库软件通用的语言

 

三、数据库存储的特点

1. 数据存到表中，表再放到库中
2. 一个库中可以有多张表，每张表具有唯一的表名来标识自己
3. 表中有一个或多个列，列又称为“字段”（相当于Java类中的属性）
4. 表中的每一行数据，相当于Java中的“对象”

 

四、MySQL的安装

　　属于c/s架构的软件，一般来讲，安装的是服务端

 

五、MySQL的启动、停止、登陆和退出

net start 服务名
net stop 服务名

mysql [-h 主机名 -P 端口号] -u 用户名 -p密码

exit

 

六、DQL（数据查询）语言

#进阶1：基础查询
/*
语法：select 查询列表 from 表名；(MySql 不区分大小写)
特点：
    1、查询列表为：表中的字段、常量值、表达式和函数
    2、查询结果是一个虚拟的表格
*/

#启动制定的库(安全)
USE  myemployees;

#查询表中的单个字段

SELECT last_name FROM employees;

#查询表中的多个字段
SELECT last_name, salary, email FROM employees;


#查询表中的所有字段, *代表所有的字段（字段的顺序与原始表相同）
SELECT * FROM employees;

#查询常量
SELECT 100;
SELECT 'a';

#查询表达式
select 100%98;

#查询函数
select version();


#更该字段的名称(1、便于理解；2、使用别名能区分重名的字段)
#方法一：使用as
select 100%98 as 结果;
select last_name as 姓, first_name as 名 from employees;

#方法二：使用空格
select 100%98 结果;

#特例：查询salary，显示结果为out put（这里有空格）
select salary as "out put" from employees;

#去重 DISTINCT
#select department_id from employees;
select distinct department_id from employees;


# +号的作用

/*
mysql中的+号仅表示运算符
eg. select 100 + 9;
select 'john' + 90; #试图将字符(串)123转换为数值。
如果成功，则将字符型转换为数值，继续做加法运算；
如果不成功，则将字符型转换为0；
select null + 90;   null 加任何值均为null
                        
*/


#案例：连接姓和名，组合成姓名，使用函数 CONCAT()
select concat(last_name, first_name) as 姓名
from employees;


#显示表的结构
desc employees;

#进阶2：条件查询
/*
语法： 
    select 
        查询列表 #查询
    from 
        表名     #第一步
    where        
        筛选条件 #筛选
        
分类：
    1、按条件表达式筛选
        条件运算符：>,<,＝,<>,>=,<=
    2、按照逻辑表达式筛选
        逻辑运算符：and or not（&& || !)
        作用：用于连接条件表达式
    3、模糊查询：like、between and、in、is null
*/

#按条件表达式筛选

#案例1、查询工资>12000的员工信息
SELECT * FROM employees WHERE salary > 12000;

#案例1、查询部门编号不等于90的员工名和部门编号
SELECT last_name, department_id
FROM employees
WHERE department_id <> 90;

#按照逻辑表达式筛选
#案例1、查询工资在10000-20000的员工信息
SELECT * FROM employees WHERE salary >= 10000 AND salary <= 20000;


SELECT * FROM employees 
WHERE  NOT(department_id >= 90 AND department_id <= 110) OR salary >15000;

#进阶3：模糊查询
/*
like、between and、in、is null、is not null
特点：1、一般和通配符搭配使用
    通配符：
        % 任意多个字符（包含0个字符）
        _ 任意单个字符
        \ 表示转义字符; ESCAPE '*' 指定一个字符为转义字符
*/

#案例1、查询员工名中包含字符a的员工信息，%表示通配符
SELECT * FROM employees WHERE first_name LIKE '%a%' OR last_name LIKE '%a%';


#案例2、查询员工名中第三个字符为e，第五个字符为a的员工名和工资
SELECT last_name, salary FROM employees 
WHERE last_name LIKE '__n_l%';

#案例3、查询员工名中第2个字符为下划线的员工名和工资（转义字符\）
SELECT last_name, salary FROM employees
#where last_name like '_\_%';
WHERE last_name LIKE '_*_%' ESCAPE '*';

/*
between and 
1、可以提高语句的简洁度；
2、左右都是闭区间
3、两个临界值不能颠倒
*/


#案例4、查询员工编号在100到200之间的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 200;

/* in
含义：用于判断某字段的值是否属于列表中的某一项
特点：1、使用in做筛选，能提高语句的简洁度
      2、in列表中的值类型必须统一，或者兼容
      3、IN等价于是否等于，列表中的每一项不支持通配符（通配符表示某一范围）

*/

#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT last_name, job_id FROM employees 
WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES');


/*is null
1、 =或者<> NULL 这种写法是不对的
2、is null 或者 is not null 可以判断null
*/

#案例：查询奖金率不为null的员工名和奖金率
SELECT 
    last_name,
    commission_pct
FROM 
    employees
WHERE
    commission_pct IS NOT NULL;

/*
安全等于   <=>
既可以判断null值，又可以判断普通的数值
可读性较is null低
*/
SELECT
    last_name,
    salary
FROM 
    employees
#WHERE commission_pct <=> NULL;
WHERE salary <=> 12000;

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

#案例
SELECT * 
FROM employees
WHERE job_id <> 'IT' OR salary = 12000;

#查询表的结构信息
DESC departments;

#查询部门departments中涉及到哪些位置编号
SELECT DISTINCT location_id FROM departments;

#面试题：语句1和语句2的结果是否相同
#语句1
SELECT * FROM employees;

#语句2
SELECT * FROM employees WHERE last_name LIKE '%';

#答案：不相同，因为通配符 % 不能匹配null的情况

 注意：比较一下 is null 和<=>

             普通类型的数值    null值    可读性
is null        no             yes       yes
<=>            yes            yes       no

 

七、排序查询

一、语法

1. select 查询列表
2. from 表
3. [where 筛选条件]
4. 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;    #表达式
ORDER BY 年薪 DESC;    #别名

#查询4：查询员工信息，要求先按工资升序，再按员工编号降序（按多个字段排序）
SELECT * 
FROM employees
ORDER BY salary ASC, employee_id DESC;

#测试1：查询员工的姓名、部门号和年薪，按照年薪降序、姓名升序
SELECT last_name, department_id, salary*12*(1+IFNULL(commission_pct, 0)) 年薪
FROM employees
ORDER BY 年薪 DESC, last_name ASC;

#测试2：选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name, salary
FROM employees
#where not (salary>= 8000 and salary<=17000)
WHERE NOT salary BETWEEN 8000 AND 17000
ORDER BY salary DESC;

#测试3：查询邮箱中包含e的员工信息，并先按照邮箱的字节数降序，再按部门号升序
#length()函数用于求字节数
SELECT *, LENGTH(email) 
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC, department_id ASC;

 

八、常见函数

1、好处：提高重用性和隐藏实现细节

2、调用：select 函数名(实参列表) [from 表]

3、单行函数：字符函数、数学函数、日期函数、流程控制函数

#length(utf8下，一个字母占一个字节，一个汉字占三个字节)：获取参数值的字节个数
#select length('john');
SELECT LENGTH('张三丰');

#concat 拼接字符
SELECT CONCAT(last_name, '_', first_name)
FROM employees;

#upper()|lower()
SELECT UPPER('john');
SELECT LOWER('JOHN');

#substr：获取子串(索引从1开始到字符串尾的所有字符)
SELECT SUBSTR('Xia Zhenbin', 5) out_put;

SELECT SUBSTR('Xia Zhenbin', 3, 3) out_put;


#instr：返回子串第一次出现的起始索引，如果找不到返回0
SELECT INSTR('我尹柳霞是好人', '尹柳霞') AS out_put;


#trim：去掉指定的字符
SELECT LENGTH(TRIM('      xia     ')) AS out_put;
SELECT TRIM('&' FROM '&&&&&&&&夏振斌&&&&&&&') AS out_put;

#lpad：用指定的字符进行左填充
SELECT LPAD('Xia', 10,'*') AS out_put;

#rpad
SELECT RPAD('Xia', 10, '*') AS out_put;

#替换函数
SELECT REPLACE('XiazhenbinXiazhenbinXiazhenbn', 'Xia', 'Handsome') AS out_put;

#round：四舍五入
SELECT ROUND(-2.46);
SELECT ROUND(-2.467, 2);

#ceil：向上取整，返回>=该参数的最小整数
SELECT CEIL(1.2);

#floor：向下取整
SELECT FLOOR(-1.2);

#truncate：截断
SELECT TRUNCATE(2.877777, 1); #2.8

#mod取余 mod(a, b): a-a/b*b
SELECT MOD(-10, -3);

#日期函数

#now 返回当前系统日期+时间
SELECT NOW();

#curdate 返回当前系统的日期(不包含时间)
SELECT CURDATE();

#curtime  返回当前系统时间
SELECT CURTIME();

#获取指定的部分
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());

#str_to_date 将字符通过指定的格式转换为日期 
SELECT STR_TO_DATE('1998-3-2', '%Y-%c-%d') AS out_put;

SELECT * FROM employees WHERE hiredate =  STR_TO_DATE('1992-4-3', '%Y-%m-%d');

#date_format：将日期转换为字符
/*
%m: 01、02、...11、12
%c: 1、2、3..、11、12
*/
SELECT DATE_FORMAT(NOW(), '%y年%m月%d日') AS out_put;

#其他函数
SELECT VERSION();    #查看版本号
SELECT DATABASE();    #查看库
SELECT USER();        #查看使用的用户

#五 流程控制函数
/*
一、if 函数

二、使用一
    case 要判断的字段或表达式
    when 常量1 then 要显示的值1或语句1(语句时要加分号;)
    when 常量2 then 要显示的值2或语句2
    when 常量3 then 要显示的值3或语句3
    ...
    else 要显示的值N或语句N
    end
    使用二
    case
    when 条件1 then 要显示的值1或语句1
    when 条件2 then 要显示的值2或语句2
    when 条件3 then 要显示的值3或语句3
    ...
    else 要显示的值N或语句N
    end
*/

#if
SELECT IF(10<2, '大', '小');

#case函数的使用(使用一)
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;

#case函数的使用(使用二)
                                                                                                                         
SELECT salary,
CASE
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

#习题1：显示系统时间(日期+时间)
SELECT NOW();

#习题2：查询员工号、姓名、工资以及工资提高百分之20后的结果(new salary)
SELECT employee_id, last_name, salary, salary*1.2 AS 'new salary'
FROM employees;

#习题3：将员工的姓名按照首字母排序，并写出姓名的长度
SELECT last_name, LENGTH(last_name), SUBSTR(last_name, 1, 1) AS 首字母
FROM employees
ORDER BY 首字母 DESC;

 

九、分组函数

#分组函数
/*
功能：用作统计使用，又称为聚合函数或统计函数
分类：sum、avg、max、min、count
特点：
1、sum、avg一般用于处理数值型
   max、min、count可以处理任何类型
2、以上分组函数都会忽略null
3、可以和distinct搭配使用
4、一般使用COUNT(*)用来统计行数
5、和分组函数一同查询的字段要求是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 COUNT(salary) FROM employees;


SELECT SUM(salary) 和, ROUND(AVG(salary), 3) 平均值, MAX(salary) 最大值, MIN(salary) 最小值, COUNT(salary) 共计
FROM employees;

SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;

#count函数的详细介绍
SELECT COUNT(salary) FROM employees;    
SELECT COUNT(*) FROM employees;    #统计行数(同一行的不同列 只要有一个非null 就能统计上)
SELECT COUNT(1) FROM employees;    #统计行数

#效率
#MYISAM存储引擎下，COUNT(*)的效率高
#INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，但比COUNT(字段)要高

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

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

 

十、分组查询

#进阶5：分组查询
/*
group by 
功能：将表中的数据分成若干组 
语法：
    select 分组函数(max min)，列(要求出现在group by的后面)
    from 表
    [where 筛选条件]
    group by 分组的列表
    [order by 子句]
注意：
    查询列表必须特殊，要求是分组函数和group by后出现的字段
    
特点：
    1、分组查询中的筛选条件分为两类
            原始源        位置            关键字
    分组前筛选    原始表        group by子句的前面    where
    分组后筛选    分组后的结果    group by子句的后面    having
        1、分组函数作条件肯定是放在having子句中
        2、能用分组前筛选的，就优先考虑使用分组前筛选
    
    2、group by子句支持单个字段分组、多个字段分组(多个字段之间用逗号隔开没有顺序要求)、表达式或函数
    3、排序 放到最后
    
    
    
*/

#简单的分组查询
#案例1：查询每个工种的最高工资
SELECT    MAX(salary), job_id
FROM employees
GROUP BY job_id;

#案例2：查询每个位置上的部门个数`myemployees`
SELECT COUNT(*), location_id
FROM departments
GROUP BY location_id;

#添加筛选条件
select AVG(salary), department_id
from employees
where email like '%a%' and department_id is not null
group by department_id;

#案例2：查询有奖金的每个领导手下员工的最高工资
select max(salary), manager_id
from employees
where commission_pct is not null
group by manager_id; 

#添加分组后的筛选
#案例1：查询哪个部门的员工个数>2 (分组后进行筛选)
select count(*) as 部门员工个数, department_id
from employees
Group by department_id
Having count(*) > 20;

#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary), job_id
from employees
where commission_pct is not null
group by job_id
having max(salary) > 12000;

#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及最低工资
select min(salary), manager_id
from employees
where manager_id > 102
group by manager_id
having min(salary) > 5000;

#测试：查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
select avg(salary), count(*), department_id
from employees
group by department_id
order by avg(salary) desc;

#选择各个job_id的员工人数
select count(*) 数量, job_id
from employees
group by job_id;

 

十一、连接查询

#进阶6：连接查询
/*
含义：又称为多表查询，当要查询的字段来自于多个表
笛卡尔乘积：表1：m行；表2：n行；  结果 = m*n行
产生原因：没有有效的连接条件
如何避免；添加有效的连接条件

分类；
    按年代分类：
    sql92标准：仅仅支持内连接
    sql99标准 [推荐]：支持内连接+外连接（左外和右外）+交叉连接
    
    按功能分类：
        内连接：
            等值连接：多表等值连接结果为多表的交集；多表的顺序没有要求；为了避免命名冲突，一般会起别名
            非等值连接
            自连接
        外连接：
            左外连接
            右外连接
            全外连接
        交叉连接        
*/
#sql92标准
#内连接-等值连接
#案例1
SELECT NAME, boyName FROM boys, beauty
WHERE beauty.boyfriend_id = boys.id; 

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

#案例3：查询员工名、工种号、工种名
SELECT last_name, employees.job_id, job_title
FROM employees, jobs
WHERE jobs.job_id = employees.job_id;

#加筛选

#案例1：查询有奖金的员工名、部门名（筛选）
SELECT last_name, department_name, 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 city LIKE '_o%';

#加分组

#案例1：查询每个城市的部门个数
SELECT COUNT(*) 部门数, city
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY city;

#查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title, COUNT(*) 个数
FROM employees e, jobs j
WHERE e.`job_id` = j.`job_id`
GROUP BY e.`job_id`
ORDER BY 个数 DESC;

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

#非等值连接

#先创建job_grades表
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);

INSERT INTO job_grades
VALUES ('A',1000,2999);

INSERT INTO job_grades
VALUES ('B',3000,5999);

INSERT INTO job_grades
VALUES ('C',6000,9999);

INSERT INTO job_grades
VALUES ('D',10000,14999);

INSERT INTO job_grades
VALUES ('E',15000,24999);

INSERT INTO job_grades
VALUES ('F',25000,40000);

SELECT * FROM job_grades;

#案例1：查询员工的工资和工资级别(非等值连接)
SELECT salary, grade_level
FROM employees e, job_grades j
#where e.salary between j.`lowest_sal` and j.`highest_sal`; 
WHERE e.salary >= j.`lowest_sal` AND e.salary <= j.`highest_sal`
ORDER BY e.`salary` DESC;

#自连接(自己连接自己)
#案例：查询员工名和上级的名称
SELECT e1.last_name 员工, e1.employee_id, e2.last_name 老板, e2.employee_id 
FROM employees e1, employees e2
WHERE e1.`manager_id` = e2.`employee_id`;

#测试：查询员工表中的job_id中包含a和e的,并且a在e的前面
SELECT job_id 
FROM employees
WHERE job_id LIKE '%a%e%';

#测试：显示当前日期，以及去除前后空格，截取子字符串的函数
SELECT DATE(NOW());
SELECT REPLACE('   Xia Zhen Bin   ', ' ','');
SELECT TRIM(' ' FROM '   Xia Zhen Bin   '); #去除首尾的空格
SELECT LTRIM(RTRIM('   Xia Zhen Bin   '));
SELECT TRIM(' 8  Xia Zhen Bin   ');

 

十二、SQL199语法

 

#sql99语法
/*
语法：
    select 查询列表
    from 表1 别名[连接类型]
    join 表2 别名 
    on 连接条件
    [where 筛选条件]
    [group by 分组]
    [having 筛选条件]
    [order by 排序列表]

内连接（*）：inner
外连接
    左外（*）：left [outer]
    右外（*）：right [outer]
    全外：full [outer]
交叉连接 cross


特点：
1、添加排序、分组、筛选
2、inner可以省略
3、筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
4、inner join连接和sql192语法中的等值连接效果相同
*/

#一、内连接
#分类：等值、非等值、自连接

#案例1：查询员工名、部门名
SELECT last_name, department_name
FROM employees e 
INNER JOIN departments d
ON e.`department_id` = d.`department_id`;

#案例2：查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name, job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id` = j.`job_id`
WHERE last_name LIKE '%e%';

#案例3：查询部门个数>3的城市名和部门个数（分组+筛选）
SELECT city, COUNT(*) num
FROM departments d
INNER JOIN locations l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING num > 3;

#案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按这个数降序
SELECT department_name, COUNT(*) 员工个数
FROM departments d
INNER JOIN employees e
ON e.`department_id` = d.`department_id`
GROUP BY department_name
HAVING 员工个数 > 3
ORDER BY 员工个数 DESC;

#案例5：查询员工名、部门名、工种名，并按部门名降序（多表连接）
SELECT last_name, department_name, job_title
FROM employees e
INNER JOIN departments d ON e.`department_id` = d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;

#二、非等值连接

#查询员工的工资级别
SELECT grade_level, salary
FROM employees e 
INNER JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
ORDER BY salary DESC;

#查询工资级别的个数>2的个数，并且按工资级别降序
SELECT COUNT(*) num, grade_level
FROM employees e
INNER JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY grade_level
HAVING num > 20
ORDER BY grade_level DESC;

#三、自连接
#查询姓名中包含字符k的员工的名字、上级的名字
SELECT e1.`last_name`, e2.`last_name`
FROM employees e1
INNER JOIN employees e2 
ON e1.`manager_id` = e2.`employee_id`
WHERE e1.`last_name` LIKE '%k%';


#四、外连接
/*
应用场景：用于查询一个表中有，另一个表没有的记录    
特点：
1、外连接的查询结果为主表中的所有记录
    如果从表中有和它匹配的，则显示匹配的值
    如果从表中没有和他匹配的，则显示null
    外连接的查询结果=内连接结果+主表中有但是从表没有的记录
2、左外连接：left join左边的是主表
   右外连接：right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接 = 内连接 + 表1中有但表2中没有 + 表2中有但表1中没有

*/
#案例：查询男朋友不在男神表的女神名(左外连接)
SELECT b.name, bs.*
FROM beauty b
LEFT OUTER JOIN boys bs
ON b.`boyfriend_id` = bs.`id`
WHERE bs.`id` IS NULL;

#案例：查询女朋友不在女神表的男生名(左外连接)
SELECT bs.*, b.*
FROM boys bs
LEFT OUTER JOIN beauty b
ON b.`boyfriend_id` = bs.`id`
WHERE b.`id` IS NULL;

#案例：查询没有员工的部门名(按主表分组)
SELECT e.`department_id`, d.`department_name`,COUNT(*)
FROM employees e
RIGHT OUTER JOIN departments d 
ON   e.`department_id` = d.`department_id`
GROUP BY d.`department_id`
HAVING COUNT(*) <> 0
ORDER BY COUNT(*) DESC;

#全外
#结果就是两个表的并集

#交叉连接(笛卡尔乘积)
SELECT b.*, bo.*
FROM beauty b
CROSS JOIN boys bo;

#sql192 和 sql99
#功能：sql199支持的较多
#可读性：sql199实现连接条件和筛选条件的分离，可读性较高

#测试：查询编号>3的女神的男朋友信息，如果有则列出详细信息；如果没有，用null填充
SELECT boys.*, beauty.`id`, beauty.`name`
FROM beauty
LEFT OUTER JOIN boys
ON boys.`id` = beauty.`boyfriend_id`
WHERE beauty.`id` > 3;

#测试：查询哪个城市没有部门
SELECT l.`city`, d.*
FROM departments d
RIGHT OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id` IS NULL;

#测试：查询部门名为SAL或IT的员工信息(员工信息可能为null)
SELECT e.*, d.`department_name`
FROM departments d
LEFT OUTER JOIN employees e
ON e.`department_id` = d.`department_id`
WHERE d.`department_name` IN('sal', 'it');

 

十三、子查询

#子查询
/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
    select后面：
        仅仅支持标量子查询
    from后面：
        支持表子查询
    where或having后面
        标量子查询（单行）*
        列子查询  （多行）*
        行子查询
    exists后面（相关子查询）
        表子查询

按结果集的行列数不同：
    标量子查询（结果集只有一行一列）
    列子查询（结果集只有一列多行）
    行子查询（结果集只有一行多列）
    表子查询（结果集一般为多行多列）
*/

#一、where/having之后
/*特点
1、子查询放在小括号内
2、子查询一般放在条件的右侧
3、标量子查询，一般搭配着单行操作符使用：> < >= <= = <>
4、列子查询：一般搭配着多行操作符使用：in、any/some、all
5、 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

*/

#案例1：谁的工资比Abel高？

#1、先找到Abel的工资
SELECT salary 
FROM employees
WHERE last_name = 'Abel'

#2、找到比Abel工资高的人
SELECT last_name, salary
FROM employees
WHERE salary > (
    SELECT salary 
    FROM employees
    WHERE last_name = 'Abel'
)
ORDER BY salary DESC;

#案例2：查询job_id与141号员工相同，salary比143号员工多的员工姓名、job_id和工资
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
    SELECT job_id
    FROM employees
    WHERE employee_id = 141
) 
AND salary > (
    SELECT salary
    FROM employees
    WHERE employee_id = 143    
)
ORDER BY salary DESC;

#案例3：返回公司工资最少的员工的last_name, job_id和salary(分组函数)
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
    SELECT MIN(salary) 
    FROM employees
);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
    #group by department_id #这一句加不加都行，因为上面where已经判断了
)
ORDER BY MIN(salary) DESC;

#非法使用标量子查询
#单行操作符只能使用标量子查询

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT salary #这里的结果为一列子
    FROM employees
    WHERE department_id = 50
    #group by department_id #这一句加不加都行，因为上面where已经判断了
)
ORDER BY MIN(salary) DESC;


#列子查询（多行）
#多行比较操作符 
#IN|NOT IN：等于列表中的任意一个 
#ANY|SOME：和子查询返回的某一个值比较 
#ALL：和子查询返回的所有值比较

#案例1：返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN (
    SELECT department_id #查询结果为一列多行
    FROM departments
    WHERE location_id IN(1400, 1700)
);

#返回其他工种中比job_id为’IT_PROG‘部门任一工资低的员工的工号、姓名、job_id以及salary
#注意：任一 != 任意
#查询job_id为'IT_PROG'部门的任一工资 

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

#又可以写成
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';

#案例3：返回其他工种中比job_id为’IT_PROG‘部门任意工资低的员工的工号、姓名、job_id以及salary
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL(
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';

#又可以写成
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < (
    SELECT MIN(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';

#行子查询 （结果集为一行多列）用得少
#案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
) 
AND employee_id = (
    SELECT MIN(employee_id)
    FROM employees
);

#用的较少，查询结果可能不存在
SELECT * 
FROM employees
WHERE (salary, employee_id) = (
    SELECT  MAX(salary), MIN(employee_id)
    FROM employees
);

#select后面
#案例：查询每个部门的员工个数
SELECT d.*, (
    SELECT COUNT(*) 
    FROM employees e
    WHERE e.`department_id` = d.`department_id`
) 个数
FROM departments d;

#案例2：查询员工号=102的部门名
SELECT d.department_name
FROM departments d
WHERE d.department_id = (
    SELECT e.department_id
    FROM employees e
    WHERE e.employee_id = 102
);

#select后必须跟标量子查询 
SELECT (
    SELECT department_name
    FROM departments d
    INNER JOIN employees e
    ON d.department_id = e.department_id 
    WHERE e.employee_id = 102
) 部门名;

#from后面：表子查询(结果集为一张表，要求必须起别名)
#案例：查询每个部门的平均工资的工资等级
#非等值连接
SELECT grade_level
FROM job_grades
INNER JOIN (
    SELECT AVG(salary) ag, department_id
    FROM employees
    GROUP BY department_id
) AS avg_fig
ON avg_fig.ag BETWEEN lowest_sal AND highest_sal;

SELECT avg_fig.*, g.`grade_level`
FROM (
    SELECT AVG(salary) ag, department_id
    FROM employees
    GROUP BY department_id
)AS avg_fig
INNER JOIN job_grades g
ON avg_fig.ag BETWEEN lowest_sal AND highest_sal;


#exists后面（相关子查询）
/*
语法：
exists（完整的查询语句）
结果：
1或0
*/
#查询有员工名的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
    SELECT COUNT(*)
    FROM employees e
    WHERE e.`department_id` = d.`department_id`
);

#也可以用in来代替
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
    SELECT e.`department_id`
    FROM employees e
);

 

十四、分页查询

#进阶：分页查询
/*
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
    select 查询列表        7
    from   表         1
    [join type] join 表2     2
    on 连接条件        3
    where 筛选条件        4
    group by 分组字段    5
    having 分组后的筛选    6
    order by 排序的字段    8
    limit offset, size;    9
    
    offset：要显示条目的起始索引（起始索引从0开始）
    size：要显示的条目个数
特点：
    1、limit语句放在查询语句的最后，执行在最后
    2、公式
       要显示的页数 page，每页的条目数size
       select 查询列表
       from   表
       limit  (page-1)*size, size;
       
*/

#案例1：查询前5条员工信息
SELECT * FROM employees LIMIT 0,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 10;

 
 

 

十五、DML语言（数据操纵语言）

1. 插入语言
2. 修改语言
3. 删除语言

#DML 语言（数据操纵语言）
/*
数据操纵语言
插入：insert
修改：update
删除：delete
*/

#一、插入语句
/*
语法：
方式一：insert into 表名(列名,...)
    values(值1,...);
方式二：insert into 表名
    set 列名=值，列名=值,...
    
*/

SELECT * FROM beauty;

#1.要求插入的值的类型要与列的类型一致或者兼容
INSERT INTO beauty(id, NAME, sex, borndate, phone, photo, boyfriend_id) 
VALUES(13, 'XiaZhenbin', '男', '1998-01-07', '15603912991', NULL, 2);

#2.不可以为null的列必须插入值，可以为null的列如何插入值？
#方式一：
INSERT INTO beauty(id, NAME, sex, borndate, phone, photo, boyfriend_id) 
VALUES(13, 'XiaZhenbin', '男', '1998-01-07', '15603912991', NULL, 2);

#方式二：（可以不写）
INSERT INTO beauty(id, NAME, sex, borndate, phone, boyfriend_id) 
VALUES(14, 'Bytedance', '女', '2011-01-07', '15603912990', 9);

#列的顺序可以颠倒
INSERT INTO beauty(borndate, phone, boyfriend_id, id, NAME, sex) 
VALUES('2011-01-07', '15603912990', 9, 15, 'Alibaba', '男');

#列和值的个数必须匹配
#可以省略列名，默认是所有列，而且列的顺序和表中列的顺序一致

INSERT INTO beauty
VALUES(17, 'XiaZhenbin', '男', '1998-01-07', '15603912991', NULL, 2);

INSERT INTO beauty
SET id = 19, NAME = 'Taotao', phone = '999';

#两种方式
/*
1、方式一支持插入多行；方式二不支持
2、方式一支持子查询，方式二不支持

*/

#方式一插入三条语句
INSERT INTO beauty
VALUES(20, 'XiaZhenbin', '男', '1998-01-07', '15603912991', NULL, 2),
(21, 'XiaZhenbin', '男', '1998-01-07', '15603912991', NULL, 2),
(22, 'XiaZhenbin', '男', '1998-01-07', '15603912991', NULL, 2);

#方式一支持子查询，方式二不支持
INSERT INTO beauty(id, NAME, phone)
SELECT 26, '宋茜', '11809866';

#二、修改语句
/*
1、修改单表的记录
语法：
update 表名
set 列=新值, 列=新值, 列=新值
where 筛选条件

2、修改多表的记录
语法：
sql192语法
update 表1 别名, 表2 别名
set 列=值...
where 连接条件
and 筛选条件


sql199语法：
update 表1，别名
inner|left|right join 表2 别名
on 连接条件
set 列=值...
where 筛选条件;


*/

#1、修改单表
#修改beauty表中姓夏的女神的电话
UPDATE beauty
SET phone = '15603912991'
WHERE NAME LIKE '%夏%';

SELECT * FROM beauty;

#修改boys表中id号为2的姓名为张飞、魅力值为10
UPDATE boys 
SET boyname = '张飞', usercp = 10
WHERE id = 2;

SELECT * FROM boys;

#2、修改多表的记录
#案例1：修改张无忌的女朋友的手机号为XXX
UPDATE boys bo
INNER JOIN beauty b 
ON bo.`id` = b.`boyfriend_id` 
SET b.`phone` = '114'
WHERE bo.`boyname` = '张无忌';

SELECT * FROM beauty;

#3、删除语句
/*
方法一：delete
语法：
1、单表的删除
delete from 表名 where 筛选条件(删除某一行记录)

2、多表的删除
sql192
delete 表1的别名
from 表1 别名，表2 别名
where 连接条件
and 筛选条件

sql199
delete 表1的别名，表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件

方式二：truncate
语法：truncate table 表名；
*/

#方式一：delete 单表的删除
#案例1：删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';

SELECT * FROM beauty;

#delete 多表的删除
#案例：删除张无忌的女朋友的信息
DELETE b
FROM beauty b
INNER JOIN boys bo
ON b.`boyfriend_id` = bo.`id` 
WHERE bo.`boyname` = '张无忌';

SELECT * FROM beauty;

#删除黄晓明的信息以及他女朋友的信息
DELETE b, bo
FROM beauty b
INNER JOIN boys bo 
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyname` = '黄晓明';

#delete pk truncate
#1、delete 可以加where条件，truncate不能加
#2、truncate删除，效率较高
#3、假设要删除表中有自增长列，
#如果用delete删除后，再插入数据，自增长的值从断点开始；
#而truncate删除后，再插入数据后，自增长列的值从1开始
#4、truncate删除没有返回值, delete删除有返回值（会有提示信息）

 

十六、DDL语言（数据定义语言）

1. 库和表的管理
2. 常见数据类型介绍
3. 常见约束

#DDL：数据定义语言
/*
库和表的管理

一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除

创建：create
修改：alter    
删除：drop
*/

#一、库的管理
#1、库的创建
/*
语法：create database 库名;
*/

#案例：创建库books
CREATE DATABASE IF NOT EXISTS books;

#更改库的字符集
ALTER DATABASE books CHARACTER SET utf8;


#库的删除
DROP DATABASE IF EXISTS books;

#二、表的管理
#1、表的创建
/*
create table 表名(
    列名 列的类型[(长度) 约束],
    列名 列的类型[(长度) 约束],
    ...
    列名 列的类型[(长度) 约束]
) 
*/
#案例1
CREATE TABLE IF NOT EXISTS book(
    id INT, #编号
    bName VARCHAR(20), #书名，20个字符长度
    price DOUBLE, #价格
    authorId VARCHAR(20), #作者
    publishDate DATETIME #出版日期
);

DESC book;

#案例2
CREATE TABLE author(
    id INT,
    au_name VARCHAR(20),
    nation VARCHAR(10)
);
DESC author;

#2、表的修改
/*
alter table 表名 add|drop|modify|change column
*/

#修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
DESC book;

#修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

#添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
DESC author;

#删除列
ALTER TABLE book_author DROP COLUMN annual;
DESC book_author;

#修改表名
ALTER TABLE book_author RENAME TO author;

#3、表的删除
DROP TABLE book_author;
SHOW TABLES;

#4、表的复制
INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国');

SELECT * FROM author;

#仅仅复制表的结构
CREATE TABLE copy LIKE author;
SELECT * FROM copy;

#复制表的结构和信息
CREATE TABLE copy_author
SELECT * FROM author;

SELECT * FROM copy_author;

#只复制部分数据
CREATE TABLE copy3
SELECT id, au_name
FROM author 
WHERE nation = '中国';

SELECT * FROM copy3;

#只复制某些字段
CREATE TABLE copy4
SELECT id, au_name
FROM author
WHERE 1 = 2;

SELECT * FROM copy4;
DESC copy4;

#测试题
#1.创建dept1
CREATE TABLE dept1(
    id INT(7),
    NAME VARCHAR(25)
);

#2.将表departments中的数据插入新表dept2(跨库复制数据)
CREATE TABLE dept2
SELECT department_id, department_name
FROM myemployees.departments;

SELECT * FROM employees;

#3.创建表emp5
CREATE TABLE emp5(
    id INT(7),
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    dept_id    INT(7)
);

SELECT * FROM emp5;

#4.将列last_name的长度增加到6
ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(6);

DESC emp5;

#5.根据表employees创建表employees2
DROP TABLE myemployees.employees2;

CREATE TABLE employees2 LIKE employees;

SELECT * FROM employees2;

#6.删除表emp5
DROP TABLE IF EXISTS emp5;

#7.将表employees2重命名为emp5
ALTER TABLE emp5 RENAME TO emp6;

#8.在表dept2和emp5中添加新列test_column, 并检查所有操作
ALTER TABLE dept2 ADD COLUMN test_column DOUBLE;

DESC dept2;

ALTER TABLE emp5 ADD COLUMN test_column DOUBLE;

DESC emp5;

#直接删除表emp5中的列test_column
ALTER TABLE emp5 DROP COLUMN test_column;
DESC emp5;

#常见的数据类型
/*
数值型：
    整型：int、bigint
    小数：
        定点数
        浮点数
字符型：
    较短的文本：char、varchar
    较长的文本：text、blob（较长的二进制数据）
日期型：
*/

#一、整型
/*
分类：
tinyint、smallint、mediumint、int/integer、bigint
   1         2         3          4           5
特点：
1、如果不声明无符号还是有符号，默认是有符号；如果想设置无符号，需要添加unsigned
2、如果插入的数值超出了整型的范围，会报out of range异常
3、如果不设置长度（显示结果的宽度），会有默认的长度
4、如果长度不否最大宽度，会用0在左边填充，但必须搭配zerofill使用
*/
#1. 如何设置无符号和有符号
DROP TABLE t_int;
CREATE TABLE t_int(
    t1 INT,
    t2 INT UNSIGNED
);

DESC t_int;
INSERT INTO t_int VALUES(-12345, -12345);

SELECT * FROM t_int;

#二、小数
/*
1.浮点型
    float(M,D)：4个字节
    double(M,D)：8个字节

2.定点型（浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值）
    dec(M,D):
    decimal(M,D):

特点：
    1、M和D：M表示整数部位外加小数部位；D表示小数部位；如果超出范围，会报错
    2、M和D都能省略，但是如果是decimal，则M和D分别默认为10和0；如果是float和double，则会根据插入的数值的精度来决定精度
    3、定点型的精度较高，如果要求插入的数值的精度较高，则使用定点型，eg货币大小 
*/
DROP TABLE tab_float;
CREATE TABLE tab_float(
    f1 FLOAT,
    f2 DOUBLE,
    f3 DECIMAL
);

DESC tab_float;

INSERT INTO tab_float VALUES(123.4523, 123.45, 123.45);
INSERT INTO tab_float VALUES(123.45, 123.45, 123.45);
INSERT INTO tab_float VALUES(123.45, 123.45, 12345.4);

SELECT * FROM tab_float;

#使用原则
/*
所选择的类型越简单越好，能保存的数值类型越小越好
*/

#三、字符型
/*
较短的文本：
    char(M)、varchar(M)：M为最多的字符数
较长的文本：
    text、blob(较大的二进制)
    
其他：
binary和varbinary用于保存较短的二进制
enum用于保存美剧
set用于保存集合

特点：
    写法         M的意思                特点        空间的耗费    性能
char    char(M)      最大的字符数，可以省略，默认为1    固定长度的字符    比较耗费    高
varchar varchar(M)    最大的字符数，不可以省略    可变长度的字符    比较节省    低
*/

#四、枚举型(不区分大小写)
#enum
CREATE TABLE tab_char(
    c1 ENUM('a','b','c','d')
);

SELECT * FROM tab_char;
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('e');    #会报错
INSERT INTO tab_char VALUES('A');

#Set 集合(不区分大小写, 一次能插入多个)
DROP TABLE tab_set;
CREATE TABLE tab_set(
    s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('a,b');
INSERT INTO tab_set VALUES('a,b,d');

SELECT * FROM tab_set;

#日期型
/*
date:只保存日期
time：只保存时间
year：只保存年

datetime：保存日期+时间
timestamp：保存日期+时间

特点：
        字节    范围        时区等的影响
datetime    8    1000——9999    不受时区影响
timestamp    4    1970-2038    受时区影响
*/

CREATE TABLE tab_date(
    t1 DATETIME,
    t2 TIMESTAMP
);

INSERT INTO tab_date VALUES(NOW(), NOW());

SELECT * FROM tab_date;
 

#常见约束
/*
含义：一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性

分类：六大约束
NOT NULL: 非空约束，
比如姓名，学号等
DEFAULT: 用于保证该段有默认值，
比如性别
PRIMARY KEY: 主键约束，用于保证该字段的值具有唯一性，并且非空
比如学号，员工编号
UNIQUE: 唯一约束，用于保证该字段的值具有唯一性，可以为空

CHECK: 检查约束[mysql中不支持] 比如性别
FOREIGN KEY: 从表添加外键约束，引用主表中某列的值，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值
添加约束
1、创建表时
2、修改表时
数据添加之前

约束的添加分类
列级约束：六大约束都支持，但外键约束没有效果
表级约束：出了非空、默认，其他都支持

*/

/*
create table 表名(
字段名 字段类型 列级约束，
字段名 字段类型，
字段名 字段类型，
表级约束
);
*/

#一、创建表时添加约束
#1.添加列级约束
/*
语法：
直接在字段名和；类型后面追加约束类型即可
只支持：默认、非空、主键和唯一
*/

CREATE DATABASE students;

USE students;

CREATE TABLE stuinfo(
id INT PRIMARY KEY, #主键
stuName VARCHAR(20) NOT NULL, #非空
gender CHAR(1) CHECK(gender='男' OR gender='女'), #检查
seat INT UNIQUE, #唯一
age INT DEFAULT 18, #默认的约束
majorId INT REFERENCES major(id) #外键
);

CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);

DESC stuinfo;
#查看stuinfo中所有的索引
SHOW INDEX FROM stuinfo; #主键、外键、唯一键自动设置成索引

DESC major;

#2.添加表级约束
/*
语法：
create table 表名(
字段名 字段类型，
字段名 字段类型，
字段名 字段类型，
[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, stuname), #主键
CONSTRAINT uq UNIQUE(seat), #唯一键
CONSTRAINT ck CHECK(gender = '男' OR gender = '女'), #检查（没有作用）
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)
);
INSERT INTO major VALUES(1, 'java');
INSERT INTO major VALUES(2, 'c++');

SELECT * FROM major;

INSERT INTO stuinfo VALUES(1, 'john', '女', NULL, 19, 1); #主键 1+john
INSERT INTO stuinfo VALUES(2, 'john', '男', NULL, 19, 2); #主键 2+john

SELECT * FROM stuinfo;
DESC stuinfo
SHOW INDEX FROM stuinfo

#通用的写法
DROP TABLE stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

#主键和唯一的区别
/* 保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 PRIMARY KEY 是 否 至多一个主键 是，不推荐
唯一 UNIQUE 是 是（只能插一个null） 可以有多个 是，不推荐
*/

#外键
/*
1、要求在从表中设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表中的关联列必须是一个key
4、插入数据时，应该先插入主表，再插入从表；删除数据时，先删除从表，再删除主表
*/

#修改表时添加约束
/*
1、添加列级约束: ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;
2、添加表级约束: ALTER TABLE 表名 add [constraint 约束名] 约束类型（字段名）[外键的引用];
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT, #主键
stuName VARCHAR(20),
gender CHAR(1), #检查
seat INT, #唯一
age INT,
majorId INT #外键
);

#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);

#添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_majorId FOREIGN KEY(majorId) REFERENCES major(id);

DESC stuinfo;
SHOW INDEX FROM stuinfo;

#修改表时删除元素

#1、删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2、删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;

#3、删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4、删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

SHOW INDEX FROM stuinfo;

#5、删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_majorId;

SHOW INDEX FROM stuinfo;

#测试1、向表emp2的id列中添加主键约束（my_emp_id_pk）
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY; #不支持命名 列级
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id); #表级

#测试2、向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的列id
ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);

#总结
/* 位置 支持的约束类型 是否可以起别名
列级约束： 列的后面 语法都支持，但外键没有效果 不可以
表级约束： 所有列的下面 默认和非空不支持，其他支持 可以（主键没有效果）
*/

 

十七：标识列

#标识列
#there can be only one auto column and it must be defined as a key
/*
自增长列的含义：可以不用手动的插入值，系统提供默认的序列值
特点：
1、标示列必须和主键搭配吗？不一定，但要求是一个key（主键、外键和唯一）
2、一个表至多有一个标识列
3、标识列的类型只能是数值型
4、标识列可以通过SET auto_increment_increment =设置步长
*/

#一、创建表时设置标志列
DROP TABLE IF EXISTS tab_identity;

CREATE TABLE tab_identity(
    id INT UNIQUE AUTO_INCREMENT,
    seat INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL
);

INSERT INTO tab_identity VALUES(15, 'XiaZhenbin');
INSERT INTO tab_identity VALUES(2, 'Alibaba');

INSERT INTO tab_identity VALUES(NULL, 'Alibaba');
SELECT * FROM tab_identity;

SHOW VARIABLES LIKE '%auto_increment%';

SET auto_increment_increment = 3; #步长

#二、修改表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
    id INT,
    seat INT,
    NAME VARCHAR(20)
);
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;


#三、修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;

 

十八：事务控制

#TCL 事务控制语言，类似于原子操作
/*
Transaction Control Language 事务控制语言

事务：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要不全部不执行
案例：转账

在mysql中用的最多的存储引擎有：innodb、myisam和memory等。其中innodb支持事务
而myisam、memory不支持事务。

事务的ACID属性
1.原子性（Atomicity）：原子性是指一个不可分割的工作单位，事务的操作要么都发生，要么不发生；
2.一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态；
3.隔离性（Isolation）：一个事务的执行不能被其他事务干扰；
4.持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变是永久性的，接下来的其他操作
和数据库故障不应该对其有任何影响；

*/

SHOW ENGINES
#事务的创建
/*
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句，自动开启

显示事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
SET autocommit = 0;

步骤1：开启事务
SET autocommit = 0;
start transaction （可选）
步骤2：编写事务中的sql语句（增删改查 select insert update delete）
语句1
语句2
...

步骤3：结束事务
commit: 提交事务
rollback：回滚事务
*/

set autocommit = 0;
show variables like 'autocommit';


drop table if exists accounts;
create table accounts(
    id int primary key auto_increment,
    username varchar(20),
    balance double
);

insert into accounts(username, balance) 
values('张无忌', 1000), ('赵敏',1000);

select * from accounts;

#演示事务的使用步骤

#开启事务
set autocommit = 0;
start transaction;

#编写一组事务的语句
update accounts set balance = 1000 where username = '张无忌';
update accounts set balance = 1000 where username = '赵敏';

#结束事务
commit;
#rollback; 回滚事务

#为什么要采取必要的隔离机制
#脏读 不可重复读 幻读
#因此数据库必须具有隔离并发运行各个事务的能力，使他们不会相互影响，避免各种并发问题
#隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE
/*
READ UNCOMMITTED(读未提交数据) 允许事务读取未被其他事务提交的变更；脏读、不可重复读和幻读的问题可能出现
READ COMMITTED(读已提交数据) 只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读和幻读的问题仍然可能出现
REPEATABLE READ(可重复读) 在这个事务持续期间，禁止其他事务对这个字段进行更新，可以避免脏读和不可重复读，但幻读的问题依然存在
SERIALIZABLE(串行化) 确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事物对该表执行插入、更新和删除操作，所有并发问题都可以避免，但是性能低下
*/
#设置当前mysql连接的隔离级别：set transaction isolation level read committed

/*
总结：事务的隔离级别
            脏读  不可重复读    幻读
READ UNCOMMITTED         是       是         是
READ COMMITTED         否       是         是
REPEATABLE READ          否       否         是        mysql中默认的隔离级别
SERIALIZABLE             否       否         否
查看隔离级别
select @@transaction_isolation
设置隔离级别
set session|global transaction isolation level 隔离级别；
*/

#rollback和savepoint结合使用
#设置保存点savepoint 可以使rollback回滚到最近的保存点

 

十九：视图

0.#视图
/*
含义：虚拟表，和普通表一样使用，是通过表动态生成的数据，只保存了sql逻辑，不保存查询的结果

应用场景：
—— 多个地方用到同样的查询结果
—— 该查询结果使用的sql语句没有改变

好处：
1.重用了sql语句
2.简化复杂的sql操作，不必知道它的查询细节
3.保护数据，提高安全性
*/

#案例：查询性张的学生名和专业名
#一、创建视图
/*
语法：
create view 视图名
as 
查询语句
*/

SELECT stuname, majorname
FROM stuinfo s
INNER JOIN major m 
on s.`majorId` = m.`id`
where s.`stuname` like '%张%';

create view v1
as 
SELECT stuname, majorName
FROM stuinfo s
INNER JOIN major m ON s.`majorId` = m.`id`;

select * from v1 
where stuname LIKE '%张%';

#1.查询邮箱中包含a字符的员工名、部门名和工种信息
create view v3
as
select last_name, department_name, job_title, email
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`;

SELECT last_name, department_name, job_title 
from v3
where email like '%a%'; 

#2.查询各部门的平均工资级别
#创建视图，查看每个部门的平均工资
create view myv1
as
select avg(salary) ag, department_id
from employees
group by department_id;

#查询视图
select grade_level, myv1.ag
from job_grades, myv1
where myv1.ag >= lowest_sal and myv1.ag <= highest_sal;

#3.查询平均工资最低的部门信息
select *
from departments d
join myv1
on myv1.`department_id` = d.`department_id`
order by myv1.`ag` 
limit 1;

#二、修改视图
/*
方式一：
create or replace view 视图名
as
查询语句；

方式二：
alter view 视图名
as 
查询语句
*/

#三、删除视图
/*
语法：drop view 视图名，视图名，...；
*/
drop view v2, v3;

#四、查看视图
desc myv1;
show create view myv1;

#案例一：创建视图emp_v1，要求查询电话号码以'011'开头的员工姓名和工资、邮箱
create view emp_v1
as
select last_name, salary, email
from employees
where phone_number like '011%';

select * from emp_v1;

#案例二：创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息

#查询每个部门的最高工资
create or replace view emp_v2
as 
select max(salary) mx_sal, department_id
from employees
group by department_id
having mx_sal > 12000
order by mx_sal;

#查询最高工资高于12000的部门信息
select d.* 
from departments d
inner join emp_v2 e
on e.`department_id` = d.`department_id`;

#五、视图的更新
/*更改视图中的数据*/
create or replace view myv1
as
select last_name, email
from employees;

select * from myv1;

#1.插入
insert into myv1 values('张飞', 'zf@qq.com');

#2.修改
update myv1 set last_name = '张无忌' where last_name = '张飞'

#3.删除
delete from myv1 where last_name = '张无忌'

/*具备以下特点的视图不允许更新
1、包含以下关键字的sql语句：分组函数、distinct、group、by、having、union或者union all
2、select中包含子查询
*/

/*    创建语法的关键字        是否实际占用物理空间                        
视图：      create view            没有（没有为数据开辟新的空间，只是保存了sql逻辑）
    
表：      create table            占用            

                                    
视图：      一般不能增删改查
    
表：          可以
*/
use students;
select * from accounts;

#1.delete和truncate在事务使用时的区别
#通过实验可以发现，delete不支持回滚，而truncate支持回滚
set autocommit = 0;
start transaction;
delete from accounts;
rollback;

SET autocommit = 0;
START TRANSACTION;
truncate table accounts;    
ROLLBACK;

 

二十：变量

#变量
/*
系统变量：不说明的话，默认为session
    全局变量 global variables
    会话变量 SESSION VARIABLES
自定义变量：
    用户变量
    局部变量
*/

#一、系统变量
#说明：变量由系统提供，不是用户定义，属于服务器层面
#使用语法：
#1.查看所有的系统变量
SHOW GLOBAL VARIABLES;

#查看会话变量
SHOW SESSION VARIABLES;

#2.查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';

#3.查看指定的某个系统变量的值
SELECT @@global|session.系统变量名 #eg.transaction_isolation;

#4.为某个系统变量赋值
#方法一：set global|session 系统变量名 = 值;
#方法二：set @@global|session.系统变量名= 值

#全局变量的作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但是不能跨重启
#会话变量：仅仅针对当前会话（连接）有效

show variables;
show session variables;

show variables like '%char%';
SHOW session VARIABLES LIKE '%char%';

select @@transaction_isolation;

set @@session.transaction_isolation = 'read-uncommitted';
set session transaction_isolation = 'read-committed';

#二、自定义用户变量
/*
说明：变量是用户自定义的，不是系统的
作用域：针对当前会话（连接）有效，等同于会话变量的作用域
*/

#声明并初始化
set @用户变量名=值;

#赋值
SET @用户变量名=值;

select 字段 into @变量名
from 表;

#查看
select @用户变量名;

#例子
use myemployees;

select count(*) into @count
from employees;
select @count;

#局部变量
/*
作用域：仅仅在定义它的begin end中有效
应用在begin end中的第一句话
*/

#声明
declare 变量名 类型;
declare 变量名 类型 default 值;

#赋值
SET 局部变量名 = 值;

SELECT 字段 INTO 局部变量名
FROM 表;

#查询
select 局部变量名

 

二十一：存储过程、函数、流程结构

#存储过程和函数
/*
存储过程和函数：类似于java中的方法
好处：
1、提高代码的重用性
2、简化操作
*/

#存储过程
/*
含义：一组预先编译好的sql语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
*/

#一、创建
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    方法体
END

注意：
1、参数列表包含三部分
参数模式     参数名    参数类型
eg. IN stuname VARCHAR(20)

参数模式：
IN：该参数可以作为输入，也就是该参数需要调用方传入值
OUT：该参数可以作为输出，也就是该参数可以作为返回值
INOUT：即可以作为输入(传入值)，又可以作为输出(返回值)


#二、使用
CALL 存储过程名(实参列表)


#1.空参列表
#案例：插入到admin表中五条记录
SELECT * FROM `admin`;

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
    INSERT INTO `admin`(username, `password`) 
    VALUES('eter', '1111'), ('tom', '1111'), ('jack', '1111'), ('lili', '1111'), ('lucy', '1111');

END $ 

#调用
CALL myp1();

#2.创建带in模式参数的存储过程
#案例1：创建存储过程实现 根据女神名 查询对应的男神信息
SELECT * FROM beauty; 
SELECT * FROM boys;

INSERT INTO boys(id, boyName, userCP) VALUES(8, '大鹏', 150);

DELIMITER $
CREATE PROCEDURE myp3(IN beautyName VARCHAR(20))
BEGIN
    SELECT bo.*
    FROM boys bo
    LEFT JOIN beauty b ON bo.id = b.boyfriend_id
    WHERE b.name = beautyName;
END $

CALL myp3('柳岩');

SELECT * FROM `admin`;
#案例2：创建存储过程实现，用户是否登陆成功
DELIMITER $
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('张飞', '8888');

#3.创建带out模式的存储过程
#案例1：根据女神名，返回对应的男神名

SELECT * FROM boys;

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 $

#调用
SET @bname = 0;
CALL myp5('柳岩', @b_name);
SELECT @b_name;

SELECT * FROM boys;

#案例1：根据女神名，返回对应的男神名
DROP PROCEDURE myp6;
DELIMITER $
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('柳岩', @b_name, @b_usercp);
SELECT @b_name, @b_usercp;


#4. 创建带inout模式参数的存储过程
#案例
DELIMITER $
CREATE PROCEDURE myp7(INOUT n1 INT, INOUT n2 INT)
BEGIN
    SET n1 = n1*2;
    SET n2 = n2*3;
END $

SET @num1 = 3;
SET @num2 = 4;
CALL myp7(@num1, @num2);
SELECT @num1, @num2;

#测试题
#1.创建存储过程实现传入用户名和密码，插入到admin表中
SELECT * FROM `admin`;
DELIMITER $
CREATE PROCEDURE myp8(IN username VARCHAR(20), IN `password` INT)
BEGIN
    INSERT INTO `admin` VALUES(NULL, username, `password`);  
END $

CALL myp8('nancy', 9999);

#2.创建存储过程或函数实现传入女神编号，返回女神名和女神电话
SELECT * FROM beauty;
DROP PROCEDURE myp9;
DELIMITER $
CREATE PROCEDURE myp9(IN id INT, OUT `name` VARCHAR(20), OUT phone LONG)
BEGIN
    SELECT b.name, b.phone INTO `name`, `phone`
    FROM beauty b
    WHERE b.id = id;
END $

CALL myp9(2, @b_name, @b_phone);
SELECT @b_name, @b_phone;

#3. 创建存储过程或函数实现输入两个女神的生日，并返回大小
SELECT * FROM beauty;
DESC beauty;
DROP PROCEDURE myp10;

DELIMITER $
CREATE PROCEDURE myp10(IN name1 VARCHAR(20), IN name2 VARCHAR(20))
BEGIN
    #声明变量并初始化
    DECLARE borndate1 DATETIME;
    DECLARE borndate2 DATETIME;

    SELECT b.borndate INTO borndate1
    FROM beauty b
    WHERE b.`name` = name1;
    
    SELECT b.borndate INTO borndate2
    FROM beauty b
    WHERE b.`name` = name2;
    
    SELECT IF(borndate1 > borndate2, CONCAT(name1, '小于', name2), CONCAT(name1, '大于', name2));
END $

CALL myp10('柳岩','周冬雨')

#4.创建存储过程或函数实现传入一个日期，格式化成XX年XX月XX日并返回
DROP PROCEDURE test_pro1;

DELIMITER $
CREATE PROCEDURE test_pro1(IN mydate DATETIME, OUT strDate VARCHAR(50))
BEGIN
    SELECT DATE_FORMAT(mydate, '%y年%m月%d日') INTO strDate;
END $

CALL test_pro1(NOW(), @date);
SELECT @date;

#5.创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串
DROP PROCEDURE test_pro2;

DELIMITER $
CREATE PROCEDURE test_pro2(IN gName VARCHAR(20), OUT resName VARCHAR(20))
BEGIN
    SELECT bo.boyName INTO resName
    FROM boys bo
    WHERE bo.id = (
        SELECT b.boyfriend_id
        FROM beauty b
        WHERE b.name = gName
    );
    
    SELECT CONCAT(gName, ' and ', IFNULL(resName, 'null')) INTO resName;
END $

CALL test_pro2('小昭', @result);
SELECT @result;

#6.创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录
DROP PROCEDURE test_pro3;

DELIMITER $
CREATE PROCEDURE test_pro3(IN size INT, IN startIndex INT)
BEGIN
    SELECT * FROM beauty
    LIMIT startIndex, size;
END $
CALL test_pro3(3,2);

#函数
/*
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1个返回，适合做处理数据返回一个结果
*/
/*
create function 函数名(参数列表) returns 返回类型
begin
    函数体
end
注意:
1.参数列表 包含两部分：
参数名    参数类型

2.函数题：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不会报错，但是不建议

return 值

3.函数体中仅有一句话，则可以省略begin end
4.使用delimiter语句设置结束标记
*/

#二、调用语法
SELECT 函数名(参数列表)


#案例演示

#1.无参有返回
#案例：返回公司的员工个数

SET GLOBAL log_bin_trust_function_creators = TRUE;

DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
    DECLARE num INT DEFAULT 0;    #定义变量
    
    SELECT COUNT(*) INTO num 
    FROM employees;
    
    RETURN num;
END $

SELECT myf1() $

#2.有参有返回
#案例1：根据员工名，返回它的工资
SELECT * FROM employees;
DELIMITER $
CREATE FUNCTION myf3(empname VARCHAR(20)) RETURNS DOUBLE
BEGIN
    DECLARE sal DOUBLE DEFAULT 0;
    
    SELECT salary INTO sal
    FROM employees 
    WHERE last_name = empname;
    
    RETURN sal;
END $

SELECT myf3('Austin') $

#2.根据部门名，返回该部门的平均工资
SELECT * FROM departments

DELIMITER $
CREATE FUNCTION myf2(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
    SET @aveSal = 0;
    
    SELECT AVG(salary) INTO @aveSal
    FROM employees e
    INNER JOIN departments d
    ON e.department_id = d.department_id
    WHERE d.department_name = deptName;
    
    RETURN @aveSal;
END $

SELECT myf2('IT') $

#查看函数
SHOW CREATE FUNCTION myf2;

#删除函数
DROP FUNCTION myf3;

#案例
#一、创建函数，实现传入两个float，返回二者之和
DELIMITER $
CREATE FUNCTION myf4(num_1 FLOAT, num_2 FLOAT) RETURNS FLOAT
BEGIN
    DECLARE result FLOAT DEFAULT 0;    #定义变量
    
    SET result = num_1 + num_2;
    
    RETURN result;
END $

SELECT myf4(5, 6);

#流程控制结构
/*
顺序结构：程序从上往下一次执行
分支结构：if-else
循环结构：for、while
*/

#一、分支结构
#if函数：语法 select if(表达式1，表达式2，表达式3)：执行顺序：如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值
#case结构
情况1：类似于java中的switch语句，一般用于等值判断
语法：
    CASE 变量|表达式|字段
    WHEN 要判断的值 THEN 返回值1或语句1;
    WHEN 要判断的值 THEN 返回值2或语句2;
    ...
    ELSE 返回值n或语句n;
    END CASE;

情况2：类似于java中的多重if语句，一般用于区间判断
    CASE 
    WHEN 要判断的条件1 THEN 返回值1或者语句1;
    WHEN 要判断的条件2 THEN 返回值2或者语句2;
    ...
    ELSE 返回值或者语句n;
    END CASE;

特点：
可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，begin end中或begin end的外面
可以作为独立的语句去使用，只能放在begin end中


#案例1：创建存储过程，根据传入的成绩，来显示等级
DROP PROCEDURE IF EXISTS test_p1;

DELIMITER $
CREATE PROCEDURE test_p1(IN score INT)
BEGIN
    DECLARE print VARCHAR(10) DEFAULT '';
    CASE 
    WHEN score BETWEEN 90 AND 100 THEN SET print = 'A';
    WHEN score BETWEEN 80 AND 89 THEN SET print = 'B';
    WHEN score BETWEEN 70 AND 79 THEN SET print = 'C';
    ELSE SET print = 'D';
    END CASE;
    
    SELECT print;
END $
CALL test_p1(80);

#if结构
/*
功能：实现多重分支
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句n;
end if;

应用场合：应用在begin end中
*/

#案例1：
DELIMITER $
CREATE FUNCTION test_f(score INT) RETURNS VARCHAR(10)
BEGIN
    DECLARE result VARCHAR(10) DEFAULT '';

    IF score BETWEEN 90 AND 100 THEN SET result = 'A'; 
    ELSEIF score BETWEEN 80 AND 89 THEN SET result = 'B'; 
    ELSEIF score BETWEEN 70 AND 79 THEN SET result = 'C'; 
    ELSE SET result = 'D'; 
    END IF;
    
    RETURN result;
END $

SELECT test_f(89) $

#二、循环结构
/*
分类：
while loop repeat

循环控制：
iterate类似于continue；
leave类似于break

语法：
[标签a：]while 循环条件 do
    循环体;
end while[标签a];
    
loop
    循环体;
end loop;    #可以用来模拟死循环

repeat 
    循环体
until 结束循环的条件
end repeat;
*/

#案例：批量插入，根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1;

DELIMITER $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i < insertCount DO
        INSERT INTO `admin`(username, `password`) VALUES(CONCAT('Rose', i), '666');
        SET i = i+1;
    END WHILE;
END $

CALL pro_while1(100)

SELECT * FROM ADMIN;

#案例1：已知表stringcontent
#向该表插入指定个数的，随机字符串
DROP TABLE stringcontent;

CREATE TABLE IF NOT EXISTS stringcontent(
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(26)
);

SELECT * FROM stringcontent;

USE students;
DROP PROCEDURE test_randstr_insert;

DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1; #循环次数
    DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
    DECLARE startindex INT DEFAULT 1; 
    DECLARE len INT DEFAULT 1;
    WHILE i <= insertCount DO
        
        SET startindex = FLOOR(RAND()*26+1); #产生一个随机的整数，代表起始索引1-26
        SET len = FLOOR(RAND()*(26-startindex+1) + 1);

        INSERT INTO stringcontent(content) VALUES(SUBSTR(str, startindex, len));
        SET i = i+1;
        
    END WHILE;
END $

CALL test_randstr_insert(10)

SELECT * FROM stringcontent;