MySQL 8.0 学习记录

net start/stop mysql80(服务器名)

quit（退出登录）
exit

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

mysql -hlocalhost -P3306 -uroot -pabc123

mysql -h localhost -P 3306 -u root -p
Enter password:****

mysql -u root -p
Enter password:****

mysql --version
mysql -V(大写)

mysql> select version();

show databases;

create database 数据库名;   

create table 表名称(
	字段名 数据类型，
	字段名 数据类型
)；	

#创建学生表
create table student(
id int,
name varchar(20) #说名字最长不超过20个字符
);

use 数据库名;

show tables from  数据库名;//查看某个库的所有表格
select * from 表名；//要事先use 数据库名;

insert into 表名 values (值列表)，(值列表)...;

show create table/database 表名称/库名称\G

drop database 数据库名/表名;

show variables like 'char_%';
show variables like 'character_%';
show variables like 'collation_%';

select ....;#(没有任何子句)

SELECT 标识选择哪些列
FROM 标识从哪个表中选择

#例
select killname as name,/*列名和别名之间加入关键字AS*/
uid id,/*紧跟列名*/
last_name "name"
from kills;

select distinct uid from kills; 

SELECT DISTINCT department_id,salary
FROM employees;

SELECT employee_id,salary,commission_pct,
12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;#值为null

#例kill是数据库的关键字
select order from kill;/*报语法错误*/
#例
select order from `kill`;/*对的*/

describe 表名；
或
desc 表名；

select 列名，...
from 表名
where 过滤子句；
#例
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;

selcet last_name name ，uid 
from student 
order by uid asc;
/*查询列last_name 别名name 从student表，根据uid升序排序*/

# order by +属性+后面不写默认为升序
select device_id,gpa,age from user_profile order by gpa desc,age desc;# 这样才能让两个都降序
order by gpa,age desc; # 这里的代码跑出来的结果翻译应该是：gpa默认升序，age降序 所以不对

select last_name,id ,uid 
from student 
order by id,uid desc;
/*第一列的last_name若都唯一则排不了序；
  排序优先级为id，uid（根据书写的前后排）
*/

select * from 表名 limit startIndex,pageSize;
select * from 表名 limit pageSize;#(默认从第一条开始，即索引为0开始)

--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录：
SELECT * FROM 表名 LIMIT 20,10;

SELECT * FROM table
LIMIT(PageNo - 1)*PageSize,PageSize;

SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC

SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY

SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC;

#例(错误的)：查询员工的姓名及其部门名称
select last_name ,department_id
from employees, departments;

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件

select 别名1.column,别名2.column
from table1 别名1，table2 别名2
where 别名1.column = 别名2.column2；
/*否则系统分辨不出是谁的列*/

select student.last_name,departments.name,employees.departments_id
from stduent,emlpoyees，department
where student.`departments_id`=employees.`departments_id`;
/*查找学生表的last_name ，部门表的name，员工表的员工id 当学生表的departments_id和员工表的departments_id相等时*/

/*学生表别名s，部门表别名d，员工表别名e*/
select s.last_name,d.name,e.departments_id
from stduent s,emlpoyees e，department d
where student.departments_id=employees.departments_id;
/*查找学生表的last_name ，部门表的name，员工表的员工id 当学生表的departments_id和员工表的departments_id相等时*/

/*查找两个部门中的关系，用where子句筛选条件，n=2，连接条件为2-1=1*/

#练习：查询员工id,员工姓名及其管理者的id和姓名

SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

where student.id =departments.uid;

#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id

SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句

#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

select <select_list>
from tableA A inner join tableB B#inner join 与join与cross join等价 
on A.key = B.key

select <select_list>
from TableA A full outer join TableB B
on A.key = B.key;

select <select_list>
from TableA A left join TableB B
on A.key = B.key;

select <select_list>
from TableA A right join TableB B
on A.key = B.key;

select <select_list>
from TableA A left join TableB B
on A.key = B.key;
where B.key is null;

select <select_list>
from TableA A right join TableB B
on A.key = B.key;
where A.key is null;

select <select_list>
from TableA A full outer join TableB B
on A.key = B.key;
where A.key is null
or B.key is null;

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

select <select_list>
from TableA A natural join TableB B

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
on (department_id = d.department_id);

#关联条件
#把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;

#把关联条件写在on后面，只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;

#把关联字段写在using()中，只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同，而且只能表示=
#查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);

#n张表关联，需要n-1个关联条件
#查询员工姓名，基本工资，部门名称
SELECT last_name,job_title,department_name 
FROM employees,departments,jobs
WHERE employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;

SELECT last_name,job_title,department_name
FROM employees INNER JOIN departments INNER JOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;

SELECT
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;

mysql> SELECT UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
| 1576380910 |
+-----------------------+
1 row in set (0.01 sec)

mysql> SELECT UNIX_TIMESTAMP(CURDATE());
+---------------------------+
| UNIX_TIMESTAMP(CURDATE()) |
+---------------------------+
| 1576339200 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP(CURTIME());
+---------------------------+
| UNIX_TIMESTAMP(CURTIME()) |
+---------------------------+
| 1576380969 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11')
+---------------------------------------+
| UNIX_TIMESTAMP('2011-11-11 11:11:11') |
+---------------------------------------+
| 1320981071 |
+---------------------------------------+
1 row in set (0.00 sec)

| FROM_UNIXTIME(1576380910) |
+---------------------------+
| 2019-12-15 11:35:10 |
+---------------------------+
1 row in set (0.00 sec)

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

SELECT
ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-
01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,
10)
FROM DUAL;

SELECT IF(1 > 0,'正确','错误')
->正确

SELECT CASE
WHEN 1 > 0
THEN '1 > 0'
WHEN 2 > 0
THEN '2 > 0'
ELSE '3 > 0'
END
->1 > 0

SELECT CASE 1
WHEN 1 THEN '我是1'
WHEN 2 THEN '我是2'
ELSE '你是谁'
END  "描述"

SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪'
WHEN salary>=10000 THEN '潜力股'
WHEN salary>=8000 THEN '屌丝'
ELSE '草根' END "描述"
FROM employees;

SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款'
WHEN 2 THEN '已付款'
WHEN 3 THEN '已发货'
WHEN 4 THEN '确认收货'
ELSE '无效订单' 
END  "描述"
FROM t_order;

COALESCE(value1, value2, ..., valueN)
-- 1. 基本用法
SELECT COALESCE(NULL, NULL, 'First Non-NULL', 'Another Value') 
AS Result;-- 'First Non-NULL'
 
-- 2.处理列值
SELECT employee_id, COALESCE(phone_number, 'No Phone Number') AS phone
FROM employees;
-- 如果 phone_number 列的值是 NULL，则结果将是 'No Phone Number'。
 
-- 3.与其他函数配合使用
SELECT employee_id, COALESCE(start_date, CURRENT_DATE) AS start
FROM employees;
-- 如果 start_date 列的值是 NULL，则使用当前日期作为默认值。

mysql> SELECT PASSWORD('mysql'), PASSWORD(NULL);
+-------------------------------------------+----------------+
| PASSWORD('mysql') | PASSWORD(NULL) |
+-------------------------------------------+----------------+
| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | |
+-------------------------------------------+----------------+
1 row in set, 1 warning (0.00 sec)

SELECT md5('123')
->202cb962ac59075b964b07152d234b70

SELECT SHA('Tom123')
->c7c506980abc31cc390a2438c90861d0f1216d50

mysql> SELECT ENCODE('mysql', 'mysql');
+--------------------------+
| ENCODE('mysql', 'mysql') |
+--------------------------+
| íg ¼ ìÉ |
+--------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
+-----------------------------------------+
| DECODE(ENCODE('mysql','mysql'),'mysql') |
+-----------------------------------------+
| mysql |
+-----------------------------------------+
1 row in set, 2 warnings (0.00 sec)

# 如果n的值小于或者等于0，则只保留整数部分
mysql> SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2);
+--------------------+--------------------+---------------------+
| FORMAT(123.123, 2) | FORMAT(123.523, 0) | FORMAT(123.123, -2) |
+--------------------+--------------------+---------------------+
| 123.12 | 124 | 123 |
+--------------------+--------------------+---------------------+
1 row in set (0.00 sec)


mysql> SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);
+-----------------+------------------+-------------------+
| CONV(16, 10, 2) | CONV(8888,10,16) | CONV(NULL, 10, 2) |
+-----------------+------------------+-------------------+
| 10000 | 22B8 | NULL |
+-----------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT INET_ATON('192.168.1.100');
+----------------------------+
| INET_ATON('192.168.1.100') |
+----------------------------+
| 3232235876 |
+----------------------------+
1 row in set (0.00 sec)
# 以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上
100。
| INET_NTOA(3232235876) |
+-----------------------+
| 192.168.1.100 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT BENCHMARK(1, MD5('mysql'));
+----------------------------+
| BENCHMARK(1, MD5('mysql')) |
+----------------------------+
| 0 |
+----------------------------+
1 row in set (0.00 sec)
mysql> SELECT BENCHMARK(1000000, MD5('mysql'));
+----------------------------------+
| BENCHMARK(1000000, MD5('mysql')) |
+----------------------------------+
| 0 |
+----------------------------------+
1 row in set (0.20 sec)

mysql> SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));
+------------------+----------------------------------------+
| CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) |
+------------------+----------------------------------------+
| utf8mb4 | utf8 |
+------------------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*)
FROM employees ;

SELECT COUNT(commission_pct)
FROM employees;

SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct) / 107
FROM employees;

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

/*select中出现要查询的列可以写可以不写，但是group by中的要分组的列一定要写*/
select column1，column2，avg(column3)#column1，column2可以省略
from table
group by column1,column2;#不能省略column1，column2

select department_id,avg(salary)
from employees
group by department_id with rollup;

SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;

#sql92语法：
SELECT ....,....,....(存在聚合函数)
FROM ...,....,....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

/*SQL99*/
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
    WHERE 不包含组函数的过滤条件
    AND/OR 不包含组函数的过滤条件
    GROUP BY ...,...
    HAVING 包含组函数的过滤条件
    ORDER BY ... ASC/DESC
    LIMIT ...,...

#其中：
#（1）from，join，：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by ，avg，sum...组函数：分组依据
#（5）having：在统计结果中再次筛选
# (6)select    distinct  查找
#（7）order by：排序
#（8）limit：分页
先查表，再select 最后分页排序

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

select select_list
from table where expr operator 
                              (select select_list 
                              from table)

#查询工资大于149号员工工资的员工的名字和工资
select last_name,salary
from employees
where salary > (select salary from employees where employee_id = 149)

#查询(最低工资大于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);	

CASE case_value 
WHEN when_value1 THEN statement_list1 #当为when_value1时，判定为statement_list1
WHEN when_value2 THEN statement_list2.....
ELSE statement_list   #上面都不符合即为statement_list
END '别名';

#显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800
的department_id相同，则location为’Canada’，其余则为’USA’。

#1.显式员工的employee_id,last_name和location。
select employee_id, last_name,location
from employees;
#2.其中，若员工department_id与location_id为1800
的department_id相同，则location为’Canada’，其余则为’USA’。
case department_id 
when (select department_id from departments where location_id =1800 then 'Canade' else 'USA' end) location


#3.总
select employee_id, last_name,
(case department_id when (select department_id from departments where location_id =1800) then 'Canade' else 'USA' end) location
from employees;

#返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
#1子查询.job_id为‘IT_PROG’部门工资
select salary from employees where job_id = 'IT_PROG'
#2.其它job_id比IT_PROG的工资低的员工的员工号、姓名、job_id 以及salary
select employee_id ,last_name, job_id ,salary
from employees 
from salary < any	(job_id为‘IT_PROG’部门工资)；

#3.总
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';

#查询平均工资最低的部门id
#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) dept_avg_sal
)/*错误，MySQL中聚合函数是不能嵌套使用的。orcl中可以使用*/

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)

SELECT last_name
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
#where manager_id != null
)；

select select_list
from table1 outer
where colunm operator(
selcet colunm from table2 where expr1 = outer.expr2)

#查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#相关子查询
select last_name,salary,department_id
from emlpoyees 
where salary >(select avg(salary) from emlpoyees where department_id = outer.department_id);
#在 FROM 中使用子查询
select last_name,salary,e1.department_id
from emlpoyees e1,(select avg(salary) s1 from emlpoyees group by department_id) e2
where e1.'department_id' = e2.'department_id'
and e2.s1 < e1.salary;
/*from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别
名， 把它当成一张“临时的虚拟的表”来使用。(空间换时间)*/

#查询员工的id,salary,按照department_name 排序
select id ,salary 
from employees e1 
order by (select department_name from departments d1 where e1.department_id = d1.department_id )asc;

#结论：在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询！
/*
SELECT ....,....,....(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件 
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....
*/

#若employees表中employee_id与job_history表中employee_id相同的数目不小于2，
#输出这些相同id的员工的employee_id,last_name和其job_id
select employee_id,last_name,job_id
from employees e
where 2 <= (select count(*) from job_history j where e.`employee_id` = j.`employee_id`);

#查询公司管理者的employee_id，last_name，job_id，department_id信息
#方式一：
select employee_id，last_name，job_id，department_id
from employees e1
where exists (select * from employees e2 where e1.manager_id = e2.employee_id );

#方式二：自连接
select distinct e1.employee_id,e1.last_name,e1.department_id 
from employees e1 join employees e2
where e1.employee_id = e2.manager_id;
#方式三：多行子查询
select employee_id，last_name，job_id，department_id
from departments 
where employee_id in (select manager_id from employees);

#使用相关子查询依据一个表中的数据更新另一个表的数据。
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
#题目：在employees中增加一个department_name字段，数据为员工对应的部门名称
update employees e 
set department_name = (select department_name from departments d where e.department_id = d.department_id );

#使用相关子查询依据一个表中的数据删除另一个表的数据。
DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
#题目：删除表employees中，其与emp_history表皆有的数据(department_id)
delete from employees e 
where employee_id in (select employee_id from emp_history e_h where e_h.employee_id =e.employee_id )

#例：谁的工资比Abel的高？
#方式1：自连接
    select e1.last_name ,e1.salary from employees e1,employees e2 
    where e2.last_name = 'Abel' and e1.salary > e2.salary;
#方式2：子查询
select last_name ,salary 
from employees
where salary > (select salary from employees where last_name = 'Abel') ;

SET sql_mode = “REAL_AS_FLOAT”;

create database 数据库名；

create database 数据库名 character set 字符集

create database if not exists 数据库名；

show databases; #有一个S，代表多个数据库

select database();#使用的一个 mysql 中的全局函数

show tables from 数据库名；

show create database/table 数据库名;
或者：
show create database/table  数据库名\G

use 数据库名;

alter database 数据库名 character set 字符集;#gbk，utf8

drop database if exists 数据库名;

create table if not exists 表名(
    字段1，数据类型 [约束条件][默认值]
    ......
);

#例
create table emp (
         emp_id int ,
         emp_name varchar(20),
         salary double
         emp_time date
           );
           DESC emp;

#利用select 等语句，先查询，再把查询的数据形成新表
create table 表名 as 
select * from student ;
#这样所有student的数据会放在新表名上
#那如何创建只有只有字段数据，无数据的表
create table 表名 as
select * from student 
where 1=2;
#利用过滤条件where 1 = 2;即死条件

desc 表名;
或：
show create table 表名;

alter table 表名 add [column] 字段名	 字段类型 [first|after字段名]

#例
alter table department
add job_id varchar(20) 
after job;
/*first|after 表示放在存在字段的前|后*/

alter table 表名 modify [colunm] 字段1 字段类型 [default 默认值] [first|after 字段名2]

#例
alter table kills modify last_name varchar(30) default 'kill';

alter table 表名 drop [colunm] 字段名

#例
alter table departments 
drop department_id ;

alter table 表名 change 【colunm】 列名 新列名 新数据类型

#例
alter table department 
change department_id depet_id varchar(20);

rename table 表名
to 新表名；

alter table 表名
rename [to] 新表名；-- [TO]可以省略

drop table [if exists] 数据库1，...;

truncate table 表名;

#例
commit;
语句....
rollback;

insert into 表名 (column1,column2,...)#column为指定插入的字段名
values 
(value1,value2,...)#对应column的数据类型
(value1,value2,...)

INSERT INTO emp1(id,NAME,salary,hire_date)
#查询语句
SELECT employee_id,last_name,salary,hire_date  # 查询的字段一定要与添加到的表的字段一一对应
FROM employees
WHERE department_id IN (70,60);

UPDATE table_name
SET column1=value1, column2=value2, … , column=valueN
[WHERE condition]

UPDATE employees
SET department_id = 55
WHERE department_id = 110;

delete from table_name [where <condition>];
#table_name指定要执行删除操作的表；“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句，
DELETE语句将删除表中的所有记录。

#使用 WHERE 子句删除指定的记录。
delete from departments
where department_name = 'Finance';
#如果省略 WHERE 子句，则表中的全部数据将被删除
delete from departments;
#删除中的数据完整性错误
#在删除数据时，也有可能因为约束的影响，导致删除失败
DELETE FROM departments
WHERE department_id = 50;

#小结：DML操作默认情况下，执行完以后都会自动提交数据。
# 如果希望执行完以后不自动提交数据，则需要使用 SET autocommit = FALSE.

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

列名 类型
[GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
[NOT NULL | NULL] 
[UNIQUE [KEY]] 
[[PRIMARY] KEY]
[COMMENT 'string']

#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名称';

#（1）建表时
#对某字段添加非空，在字段后加not null（列级约束）无表级标记约束
create table 表名（
    字段名，数据类型  ,
     字段名2，数据类型  not null,
      字段3，数据类型  not null
      ）；
#建表后
alter table 表名称 modify(
字段名1 数据类型 not null,
字段名2 数据类型 not null,
...
) ;

alter table 表名 
modify 字段名 数据类型 null;
或
alter table 表名称 
modify 字段名 数据类型; 

#（1）建表时
create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)#表级约束
)；

#例:列级约束
create table student (
s_id int unique,
s_tel char(11) unique
);
#表级约束
CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
-- 使用表级约束语法
constraint uk_name_pwd UNIQUE(NAME,PASSWORD)#表示用户名和密码组合不能重复（复合约束）
);

#建表后指定唯一键约束
#字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯
一的
#方式1：
alter table 表名称 add unique key(字段列表);

#方式2：
alter table 表名称 modify 字段名 字段类型 unique;

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
unique key(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
);

ALTER TABLE test2
DROP INDEX last_name;#未指定名称

ALTER TABLE test2
DROP INDEX uk_test2_sal;#指定名称

#（1）建表时指定主键约束
create table 表名称(
字段名 数据类型 primary key, #列级模式
字段名 数据类型,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名) #表级模式
);
#（2）建表后增加主键约束
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
primary key(字段名1,字段名2) #表示字段1和字段2的组合是唯一的，也可以有更多个字段
);

alter table 表名称 drop primary key;

#（1）建表时
create table 表名称(
字段名 数据类型 primary key auto_increment,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 unique key auto_increment,
字段名 数据类型 not null default 默认值,,
primary key(字段名)
);
#例
create table employee (
 eid int primary key auto_increment
 ename varchar(20)
 );
 
 #（2）建表后
 alter table 表名称 modify 字段名 数据类型 auto_increment;
#例
create table employee(
eid int primary key ,
ename varchar(20)
);
alter table employee modify eid int auto_increment;

#alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除
#例
alter table employee modify eid int;

（1）建表时
create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列
#例
create table dept( #主表
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(#从表
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) #在从表中指定外键约束
#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
);
/*说明：
（1）主表dept必须先创建成功，然后才能创建emp表，指定外键成功。
（2）删除表时，先删除从表emp，再删除主表dept*/

#（2）建表后
/*一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定义好。不
过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那
么，就要用修改表的方式来补充定义。
*/

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用
字段) [on update xx][on delete xx];#xx是约束等级

#（1）演示1：on update cascade on delete set null 即on update (更新) cascade(联级)=主表和从表同步更新，on delete set null 主表数据删除，从表数据对应null
/*把修改操作设置为级联修改等级，把删除操作设置为set null等级,即主表的数据修改后，从表也一起修改，主表的数据删除后，从表的数据为null*/
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) on update cascade on delete set null
#把修改操作设置为级联修改等级，把删除操作设置为set null等级
);

#（2）演示2：on update set null on delete cascad
/*把修改操作设置为set null等级，把删除操作设置为级联删除等级
主表的修改，从表不会变，主表的删除，从表也会删除
*/
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) on update set null on delete cascade
#把修改操作设置为set null等级，把删除操作设置为级联删除等级
);
#（3）演示：on update cascade on delete cascade
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) on update cascade on delete cascade
#把修改操作设置为级联修改等级，把删除操作也设置为级联删除等级
);

#(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
#（2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;

create table employee(
eid int primary key,
ename varchar(5),
gender char check ('男' or '女')
);
insert into employee values(1,'张三','妖');

#（1）建表时
create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
再举例：
字段名 数据类型 default 默认值 ,
字段名 数据类型 not null default 默认值,
字段名 数据类型 not null default 默认值,
primary key(字段名),
unique key(字段名)
);
#说明：默认值约束一般不在唯一键和主键列上加
#例：
create table employee(
eid int primary key,
ename varchar(20) not null,
gender char default '男',
tel char(11) not null default '' #默认是空字符串
);
#（2）建表后
alter table 表名称 modify 字段名 数据类型 default 默认值;
#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;
#例
alter table employee modify tel char(11) default '' not null;#给tel字段增加默认值约束，并保留非空约束

#例
alter table employee modify gender char; #删除gender字段默认值约束，如果有非空约束，也一并删除
alter table employee modify tel char(11) not null;#删除tel字段默认值约束，保留非空约束

#在 CREATE VIEW 语句中嵌入子查询
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

#精简版
CREATE VIEW 视图名称
AS 查询语句

#例查询语句中字段的别名会作为视图中字段的名称出现
create view vu_depts
as
select employee_id,last_name,salary 
from empts;

#在视图名称后添加字段名，对应as语句后的字段类型，创建的视图的名字为：视图名称后添加字段名
creat view vu_emp2(emp_id , name ,monthly_sal)
as 
select employee_id,last_name,salary
from emps
where salary >8000;
#视图中的字段在基表中可能没有对应的字段
CREATE VIEW vu_emp_sal
AS
SELECT department_id,AVG(salary) avg_sal
FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id;

CREATE VIEW empview
AS
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;


#利用视图对数据进行格式化
/*我们经常需要输出某个格式的内容，比如我们想输出员工姓名和对应的部门名，对应格式为
emp_name(department_name)，就可以使用视图来完成数据格式化的操作：*/
CREATE VIEW emp_depart
AS
SELECT CONCAT(last_name,'(',department_name,')') AS emp_dept
FROM employees e JOIN departments d
WHERE e.department_id = d.department_id

/*当我们创建好一张视图之后，还可以在它的基础上继续创建视图。
举例：联合“emp_dept”视图和“emp_year_salary”视图查询员工姓名、部门名称、年薪信息创建
“emp_dept_ysalary”视图。*/
CREATE VIEW emp_dept_ysalary
AS
SELECT emp_dept.ename,dname,year_salary
FROM emp_dept INNER JOIN emp_year_salary
ON emp_dept.ename = emp_year_salary.ename;

#语法1：查看数据库的表对象、视图对象
SHOW TABLES;
#语法2：查看视图的结构
DESC / DESCRIBE 视图名称;
#语法3：查看视图的属性信息
# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G
/*执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。*/
#语法4：查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;

# update ... set ...
#delete from ....where 
#insert into ....values()

/*要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。另外当视图定义出现如
下情况时，视图不支持更新操作：
在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
在定义视图的SELECT语句中使用了 JOIN联合查询 ，视图将不支持INSERT和DELETE操作；
在定义视图的SELECT语句后的字段列表中使用了 数学表达式 或 子查询 ，视图将不支持INSERT，也
不支持UPDATE使用了数学表达式、子查询的字段值；
在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、
UNION 等，视图将不支持INSERT、UPDATE、DELETE；
在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持
INSERT、UPDATE、DELETE；
视图定义基于一个 不可更新视图 ；
常量视图。*/


#例
mysql> CREATE OR REPLACE VIEW emp_dept
-> (ename,salary,birthday,tel,email,hiredate,dname)
-> AS SELECT ename,salary,birthday,tel,email,hiredate,dname
-> FROM t_employee INNER JOIN t_department
-> ON t_employee.did = t_department.did ;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO emp_dept(ename,salary,birthday,tel,email,hiredate,dname)
-> VALUES('张三',15000,'1995-01-08','18201587896',
-> 'zs@atguigu.com','2022-02-14','新部门');
#ERROR 1393 (HY000): Can not modify more than one base table through a join view
'atguigu_chapter9.emp_dept'

#方式1：使用CREATE OR REPLACE VIEW 子句修改视图
#create or replace VIEW相当于创建视图，如果视图存在则替换视图
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

#方式2：ALTER VIEW 修改视图的语法是：
alter view 视图名
as 查询语句

#删除视图只是删除视图的定义，并不会删除基表的数据。
#删除视图的语法是：
drop view if exists 视图名,....;

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
存储过程体
END

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

#示例：
DELIMITER $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
sql语句1;
sql语句2;
END $

delimiter $  #结束符为$
create procedure select_all_data()
begin
select * from emps;
end$  #结束
delimiter;  #重新定义结束符为;不影响后面的语句

#2. 存储过程的调用

CALL select_all_data();

delimiter $
create proudure avg_employee_salary()
begin 
select avg(salary) as avg_salary from emps;
end $
delimiter ;

delimiter $

create proudure show_max_salary()
language sql #用sql写的
not deterministic #结果不确定
contains sql #子程序包含SQL语句，但是并不包含读写数据的SQL语句
sql security definer#只有当前存储过程的创建者或者定义者才能执行当前存储过程
commint '查看最高薪资'
begin 
select max(salary) as max_salary from emps;
end $
delimiter;

delimiter $
create procedure show_min_salary(out ms double )
begin
       select min(salary) as min_salary from emps;
end $
delimiter $

#调用
CALL show_min_salary(@ms);
#查看变量值
SELECT @ms;

delimiter $
create procedure show_someone_salary(in empname varchar(20))
begin 
	select salary from emps where last_name = empname;
end $
delimiter ;
#调用方式1
call show_min_salary('Abel');
#调用方式2
set @empname := 'Abel';
call show_min_salary('@empname');

delimiter $
create procedure show_someone_salary2(in empname varchar(20) ,out empsalary double )
begin
	select salary into empsalary from emps where ename = empname;
end $
#调用
delimiter ;
set $empname = 'Abel';
call show_someone_salary2(@empname,@empsalary);

#类型5：带 INOUT
delimiter $
create procedure show_mgr_name(inout empname varchar(20))#这里有个点：设置的数据类型长度一定要比begin子句里面的长或者相等
begin
	select last_name  into empname 
	from emps 
	where e_id = (
				select m_id from emps where last_name = empname
	);
end $
delimiter ;
#调用
set @empname :='Abel';
call show_mgr_name(@empname);

CALL 存储过程名(实参列表)

CALL 存储过程名('值');

SET @name;
CALL 存储过程名(@name);
SELECT @name;

SET @name=值;
CALL sp1(@name);
SELECT @name;

create function 函数名(参数名 参数类型,...)
returns 返回值类型
[characteristics ...]
begin 
函数体 #函数体中肯定有 return 语句
end

select 函数名(实参列表)

delimiter $
create function email_by_name()
returns varchar(25)
begin
	return(select email from employees where last_name = 'Abel';)
end $

delimiter ;
#调用：
select email_by_name();

delimiter $
create function email_by_id(emp_id int)
returns varchar(20)
begin
return(
		select email from employees where employee_id = emp_id
);
end $
delimiter ;
#调用
set @emp_id = 102;
select email_by_id(102);

delimiter $

create function count_by_id(dept_id int )
returns int 
begin
return(
		select count(*) from departments where department_id = dept_id);
end $
delimiter ;
#调用
set @dept_id = 50;
select count_by_id(@dept_id);

show create {procedure|function}存储过程名或函数名;

show {procedure|function}status {like 'pattern'}

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

alter {procedure|function}存储过程或函数的名 [characteristic ...]

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string

drop {procedure |functon} [if exists]  存储过程或函数的名

#查看所有或部分系统变量
#查看所有全局变量
show global variables;
#查看所有会话变量
show session variables;
#或
show variables ;(默认session变量)

#查看满足条件的部分系统变量。
show global variables like '%标识符%';

#查看满足条件的部分会话变量
show session variables like '%标识符%';

#查看指定的系统变量的值
select @@global.变量名;

#查看指定的会话变量的值
select @@session.变量名;

#或
select @@变量名;

#为某个系统变量赋值
#方式1：
SET @@global.变量名=变量值;
#方式2：
SET GLOBAL 变量名=变量值;
#为某个会话变量赋值
#方式1：
SET @@session.变量名=变量值;
#方式2：
SET SESSION 变量名=变量值;

#变量的定义
#方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;

#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];

#查看用户变量的值 （查看、比较、运算等）
SELECT @用户变量

begin 
	#声明局部变量
	declare 变量名1 变量数据类型 [default 变量默认值];
	declare  变量名2,变量名3,... 变量数据类型 [default 变量默认值];
	#为局部变量赋值
	set 变量1 = 值;
	select 值 into 变量2 [from 子句];
	#查看局部变量的值
	select 变量1,....;
end 

#1.定义变量
declare 变量名 类型 [default 值]; # 如果没有DEFAULT子句，初始值为NULL

#2.变量赋值
#方式1：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
#方式2：一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;

#3.使用变量（查看、比较、运算等）
SELECT 局部变量名;

delimiter $
create procedure set_value()
begin
	declare emp_name varchar(25)
	declare sal double(10,2)
	
	select last_name, salary into emp_name ,sal
	from employees 
	where employees_id = 102;
	
	select emp_name, sal ;
end $

delimiter ;

delimiter $
create procedure print_var()
begin 
	set @m=1;
	set @n=2;
	set @mn = @m+@n;
end $

delimiter ;

#声明
DELIMITER //
CREATE PROCEDURE different_salary(IN emp_id INT,OUT dif_salary DOUBLE)
BEGIN

#声明局部变量
DECLARE emp_sal,mgr_sal DOUBLE DEFAULT 0.0;
DECLARE mgr_id INT;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT manager_id INTO mgr_id FROM employees WHERE employee_id = emp_id;
SELECT salary INTO mgr_sal FROM employees WHERE employee_id = mgr_id;
SET dif_salary = mgr_sal - emp_sal;
END //
DELIMITER ;

#调用
SET @emp_id = 102;
CALL different_salary(@emp_id,@diff_sal);
#查看
SELECT @diff_sal;

declare 错误名称 condition for  错误码（或错误条件）

#举例1：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。

#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

举例2：定义"ERROR 1148(42000)"错误，名称为command_not_allowed。
#使用MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;
#使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';

declare 处理方式 handler for 错误类型 处理语句

#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
#定义处理程序
DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;
SET @x = 1;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END //
DELIMITER ;

#调用过程：
mysql> CALL UpdateDataWithCondition();
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @x,@proc_value;
+------+-------------+
| @x | @proc_value |
+------+-------------+
| 3 | -1 |
+------+-------------+
1 row in set (0.00 sec)

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
#根据表达式的结果为TRUE或FALSE执行相应的语句。这里“[]”中的内容是可选的。
#特点：① 不同的表达式对应不同的操作 ② 使用在begin end中

#情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

#情况二：类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]

#使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程。
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id +1;
IF id >= 10 THEN LEAVE add_loop;
END IF;
END LOOP add_loop;

[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];

[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

LEAVE 标记名

ITERATE label

DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
DECLARE num INT DEFAULT 0;
my_loop:LOOP
SET num = num + 1;
IF num < 10
THEN ITERATE my_loop;
ELSEIF num > 15
THEN LEAVE my_loop;
END IF;
SELECT '尚硅谷：让天下没有难学的技术';
END LOOP my_loop;
END //
DELIMITER ;

#第一步，声明游标
#在MySQL中，使用DECLARE关键字来声明游标，其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement;

#这个语法适用于 MySQL，SQL Server，DB2 和 MariaDB。如果是用 Oracle 或者 #PostgreSQL，需要写成：

DECLARE cursor_name CURSOR IS select_statement;

/*要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，这里 select_statement 代表的是SELECT 语句，返回一个用于创建游标的结果集。*/
#比如：
DECLARE cur_emp CURSOR FOR
SELECT employee_id,salary FROM employees;
#第二步，打开游标
#打开游标的语法如下：
OPEN cursor_name；

#第三步，使用游标（从游标中取得数据）
FETCH cursor_name INTO var_name [, var_name] ...

/*这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游
标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。
注意：var_name必须在声明游标之前就定义好。*/
#注意：游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时
#候，MySQL 会提示错误。

#第四步，关闭游标
CLOSE cursor_nam
/*有 OPEN 就会有 CLOSE，也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。因为游标会
占用系统资源 ，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。而关闭游标
的操作，会释放游标占用的系统资源。
*/

DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT
total_count INT)
BEGIN
DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资
DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
DECLARE emp_count INT DEFAULT 0; #记录循环个数
#定义游标
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
#打开游标
OPEN emp_cursor;
REPEAT
#使用游标（从游标中获取数据）
FETCH emp_cursor INTO cursor_salary;
SET sum_salary = sum_salary + cursor_salary;
SET emp_count = emp_count + 1;
UNTIL sum_salary >= limit_total_salary
END REPEAT;
SET total_count = emp_count;
#关闭游标
CLOSE emp_cursor;
END //
DELIMITER ;

SET GLOBAL MAX_EXECUTION_TIME=2000;

SET PERSIST global max_connections = 1000;

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;

CREATE TABLE test_trigger (
id INT PRIMARY KEY AUTO_INCREMENT,
t_note VARCHAR(30)
);
CREATE TABLE test_trigger_log (
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(30)
);

delimiter //
create trigger before_insert
before insert on test_trigger
for each  row 
begin
	insert into test_trigger(t_log)
	values('before_insert');
end //

delimiter ;

insert into test_trigger (t_note )values ('测试 BEFORE INSERT 触发器')

mysql> SELECT * FROM test_trigger_log;
+----+---------------+
| id | t_log |
+----+---------------+
| 1 | before_insert |
+----+---------------+
1 row in set (0.00 sec)

DELIMITER //
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
DECLARE mgrsalary DOUBLE;
SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;
IF NEW.salary > mgrsalary THEN
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
END IF;
END //
DELIMITER ;
	#上面触发器声明过程中的NEW关键字代表INSERT添加语句的新记录。

#方式1：查看当前数据库的所有触发器的定义
show trigger\G
#方式2：查看当前数据库中某个触发器的定义
show create trigger 触发器名
/*方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。*/
select * from information_schema.triggers;

drop trigger if exists 触发器名称;

select * from where field in (value1,value2,value3,…)

select * from where field not in (value1,value2,value3,…)

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=0)

SELECT * FROM basic_zdjbxx WHERE suiji IN ( SELECT zdcode FROM ( SELECT zdcode FROM basic_h WHERE zdcode != "" ) AS h )

SELECT zd.* FROM ( SELECT DISTINCT zdcode FROM basic_h WHERE zdcode != "" ) AS h LEFT JOIN basic_zdjbxx zd ON zd.suiji = h.zdcode

SELECT * FROM user WHERE uid IN(1,2,'3','c')

SELECT * FROM user WHERE uid IN(1,2) OR username IN('admin','manong')

SELECT * FROM user WHERE uid IN (2,3,5)

SELECT * FROM user WHERE (uid=2 OR aid=3 OR aid=5)