MYSQL
说明：mysql是数据库的一种，sql是专门用来与数据库通信的语言。
SQLyog是MySQL图形化管理工具的一种
启动：net start mysql80
关闭：net stop mysql80
在功能上主要分为如下3大类：
DDL（Data Definition Languages、数据定义语言）
DML（Data Manipulation Language、数据操作语言）
DCL（Data Control Language、数据控制语言）
SQL语言
导入数据：
source 数据目录
SQL语言的规则和规范
SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
每条命令以 ; 或 \g 或 \G 结束
关键字不能被缩写也不能分行
关于标点符号
必须保证所有的()、单引号、双引号是成对结束的
必须使用英文状态下的半角输入方式
字符串型和日期时间类型的数据可以使用单引号（’ '）表示
列的别名，尽量使用双引号（" "），而且不建议省略as
SQL大小写规范 （建议遵守）
MySQL 在 Windows 环境下是大小写不敏感的，MySQL 在 Linux 环境下是大小写敏感的
数据库名、表名、表的别名、变量名是严格区分大小写的
关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
推荐采用统一的书写规范：
数据库名、表名、表别名、字段名、字段别名等都小写
SQL 关键字、函数名、绑定在·变量等都大写
注 释
可以使用如下格式的注释结构
单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(–后面必须包含一个空格。)
多行注释：/* 注释文字 */
这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
#约束
#约束的分类：
/*
角度一：约束字段的个数
单列约束 vs 多列约束
角度二：约束的作用范围
列级约束：将此约束声明在对应字段的后面
表级约束：将表中所有字段都声明完，在所有字段的后面声明的约束
角度三：约束的作用（或功能）
not null（非空约束）
unique（唯一约束）
primary key（主键约束）
foregin key（外键约束）
check（检查约束）
default（默认值约束）
通常在CREATE TABLE时添加约束
也可以在ALTER TABLE时增加或删除约束
*/
#查看某个表已有的约束
/*SELECT *
FROM information_schema.table_constraints
WHERE table_name = '表名称';
*/
#列如：
SELECT *
FROM information_schema.table_constraints
WHERE TABLE_NAME = 'employees';
/*
非空约束:只有列级约束，无表级约束
作用
限定某个字段/某列的值不允许为空
关键字
NOT NULL
特点
默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
一个表可以有很多列都分别限定了非空
空字符串''不等于NULL，0也不等于NULL
*/
#建表时直接在类型后面加NOT NULL
CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL,
sex CHAR NULL
);
DESC emp;
#建表后
/*
ALTER TABLE 表名称
MODIFY 字段名 数据类型 NOT NULL;
*/
ALTER TABLE students
MODIFY sname VARCHAR(20) NOT NULL;
#删除非空约束
#在ALTER TABLE时去掉NOT
ALTER TABLE emp
MODIFY id INT(10) NULL;
#唯一性约束UNIQUE：既有表级也有列级
#创建表是添加
CREATE TABLE test2(
id INT UNIQUE,#列级约束
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2),
#表级约束
CONSTRAINT te2 UNIQUE(email)
);
DESC test2;
SELECT *
FROM information_schema.table_constraints
WHERE TABLE_NAME = 'test2';
#在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同
#可以向声明为unique字段上添加NUL值，而却可以多次添加
#建表后添加约束
#方式1
ALTER TABLE test2
ADD CONSTRAINT sal UNIQUE(salary);
#方式2
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;
#注：如果列中有数据不满足约束，则会修改失败
#符合的唯一型约束
#即：被约束的多个字段中，只要有一个不一样就行
CREATE TABLE test3(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);
INSERT INTO test3
VALUES(1,'张三','137');
INSERT INTO test3
VALUES(1,'李四','137');
#删除唯一索引
/*
添加唯一性约束的列上也会自动创建唯一索引。
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()
中排在第一个的列名相同。也可以自定义唯一性约束名。
*/
SELECT *
FROM information_schema.table_constraints
WHERE TABLE_NAME = 'test2';
ALTER TABLE test2
DROP INDEX last_name;
#primary key（主键约束）：非空且唯一
#在CREATE TABLE时添加
#一个表中最多有一个唯一约束，用于唯一标识表中的一条记录
CREATE TABLE test4(
id INT PRIMARY KEY,#列级约束
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2)
);
CREATE TABLE test5(
id INT ,
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2),
#表级约束
PRIMARY KEY(id)
);
SELECT *
FROM information_schema.table_constraints
WHERE TABLE_NAME = 'test6';
/*
一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
主键约束对应着表中的一列或者多列（复合主键）
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用!!!!!!!!!!!!!!!!!!!
当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询
的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的
值，就有可能会破坏数据的完整性。
*/
#复合主键约束，有一个不一样且均非空
#建表后添加主键约束
CREATE TABLE test6(
id INT ,
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2)
);
ALTER TABLE test6
ADD PRIMARY KEY(id);
#删除主键约束
ALTER TABLE test6
DROP PRIMARY KEY;
#自动增长列AUTO_INCREMENT
/*
（1）一个表最多只能有一个自增长列
（2）当需要产生唯一标识符或顺序值时，可设置自增长
（3）自增长列约束的列必须是键列（主键列，唯一键列）!!!!!!!!!!!!!!!!!!!
（4）自增约束的列的数据类型必须是整数类型!!!!!!!!!!!!!!!!!!!!!!!
（5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接
赋值为具体值。
*/
CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15)
);
SELECT *
FROM test7;
INSERT INTO test7(last_name)
VALUES('tom');
INSERT INTO test7(last_name)
VALUES('tom');
INSERT INTO test7
VALUES(NULL,'tom');
#如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接
#赋值为具体值。
SELECT *
FROM test7;
#建表后添加
CREATE TABLE test8(
id INT PRIMARY KEY,
last_name VARCHAR(15)
);
ALTER TABLE test8
MODIFY id INT AUTO_INCREMENT;
/*在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重
置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发
现的问题。
MySQL 8.0将自增主键的计数器持久化到 重做日志 中。每次计数器发生改变，都会将其写入重做日志
中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。
*/
#外键约束 FOREIGN KEY
/*
主表和从表/父表和子表
主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表
*/
/*
（1）从表的外键列，必须引用/参考主表的主键或唯一约束的列
为什么？因为被依赖/被参考的值必须是唯一的
（2）在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如
student_ibfk_1;），也可以指定外键约束名。
（3）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
（4）删表时，先删从表（或先删除外键约束），再删除主表
（5）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖
该记录的数据，然后才可以删除主表的数据
（6）在“从表”中指定外键约束，并且一个表可以建立多个外键约束
（7）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类
型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”。
（8）当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束
名。（根据外键查询效率很高）
（9）删除外键约束后，必须 手动 删除对应的索引
*/
#外键约束中，主表中关联约束的字段必须拥有主键约束或唯一性约束
#先创建主表
CREATE TABLE dept1(
id INT PRIMARY KEY,#必须拥有主键约束或唯一性约束
dept_name VARCHAR(15)
);
#在创建从表
CREATE TABLE emp6(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
#表级约束
CONSTRAINT fedi FOREIGN KEY (department_id) REFERENCES dept1(id)
);
#主表从表建好后添加外键约束：
/*
ALTER TABLE 从表名
ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名 (被引用 字段)[on update xx][on delete xx];约束等级
*/
/*Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子
表的外键列不能为not null No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
Restrict方式 ：同no action， 都是立即检查外键约束
Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置
成一个默认的值，但Innodb不能识别
如果没有指定等级，就相当于Restrict方式。
对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。
*/
/*
一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定义好。不
过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那
么，就要用修改表的方式来补充定义。
*/
#删除外键
#ALTER TABLE 从表名 DROP INDEX 索引名;
#CHECK 约束
#作用:检查某个字段的值是否符号xx要求，一般指的是值的范围
-- 关键字 CHECK 3、说明：MySQL 5.7 不支持 MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告
-- 但是MySQL 8.0中可以使用check约束了
CREATE TABLE emp3(
id INT,
salary INT CHECK(salary>2000),
last_name VARCHAR(15)
);
#添加成功
INSERT INTO emp3(salary)
VALUES (2500);
#添加失败
INSERT INTO emp3(salary)
VALUES (2000);
#DEFAULT约束
#在create table是添加
CREATE TABLE emp4(
id INT,
salary INT DEFAULT 2000
);
INSERT INTO emp4
VALUE(1,6000);
INSERT INTO emp4(id)
VALUE(1);
SELECT *
FROM emp4;
#在ALTER TABLE时删除default
ALTER TABLE emp4
MODIFY salary INT;
DESC emp4;
/*面试1、为什么建表时，加 not null default '' 或 default 0
答：不想让表中出现null值。
面试2、为什么不想要 null 的值
答:（1）不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通
常返回null。 （2）效率不高。影响提高索引效果。因此，我们往往在建表时 not null default '' 或 default 0
面试3、带AUTO_INCREMENT约束的字段值是从1开始的吗？ 在MySQL中，默认AUTO_INCREMENT的初始
值是1，每新增一条记录，字段值自动加1。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第
一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一
条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要
设置字段自动增加属性。
面试4、并不是每个表都可以任意选择存储引擎？ 外键约束（FOREIGN KEY）不能跨引擎使用。
MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来
保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不
能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。
*/
用于添加、删除、更新和查询数据库记
录，并检查数据完整性。SELECT是SQL语言的基础，最为重要。
伪表DUAL
SELECT 1+1
FROM DUAL;
SELECT … FROM …
语法：
SELECT 标识选择那些列
FROM 标识从哪个表中选择;
选择全部列
SELECT *
FROM 表名;
选择特定的列
SELECT 列名, 列名,......
FROM 表名;
列的别名
重命名一个列
便于计算
紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特
殊的字符并区分大小写。
AS 可以省略
建议别名简短，见名知意
SELECT last_name AS ln
FROM 表名
去除重复行DISTINCT
SELECT DISTINCT 列名
空值参与运算
所有运算符或列值遇到null值，运算的结果都为null
着重号
列名或表名等与关键字或保留字重名，加上着重号以表示此名称不代表关键字或保留字。
SELECT `ORDER`
FROM 表名
查询常数
SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。
为什么我们还要对常数进行查询呢？
SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个
固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。
比如：
SELECT '呱呱' AS '哈哈'
FROM 表名;
显示表结构DESCRIBE或DESC
DESCRIBE 列名;
#或
DESC 列名;
各个字段的含义分别解释如下：
Field：表示字段名称。
Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的。
Null：表示该列是否可以存储NULL值。
Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
Default：表示该列是否有默认值，如果有，那么值是多少。
Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。
过滤数据WHERE
过滤条件声明在FROM结构后面
列如：
SELECT *
FRMO 表名
WHERE 判断条件
算术运算符+，-，*，/，%
一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）
一个数乘以整数1和除以整数1后仍得原数；
一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。
如下：
SELECT 100+'1'
FROM DUAL;
#在SQL语句中 + ，仅代表加法运算，会将字符串转化为数值(隐式转换)
SELECT 100+'a'
FROM DUAL;#此时将a看作0
SELECT 100+NULL#NULL参与运算为NULL
FROM DUAL;
# % 运算符的结果的正负号与被除数一致
比较运算符=,<=>,<>,!=,<,<=,>,>=;
比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。
比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。
等号运算符 =
等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。
在使用等号运算符时，遵循如下规则：
如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等
如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
安全等与运算符 <=>
两边的操作数的值都为NULL时，返回的结果为1而不是NULL，其他
返回结果与等于运算符相同。
不等于运算符（<>和!=）
用于判断两边的数字、字符串或者表达式的值是否不相等
如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL
非符号类型的运算符
空运算符（IS NULL或者ISNULL）和 非空运算符（IS NOT NULL）
空：判断一个值是否为NULL，如果为NULL则返回1，否则返回
0。
非空：判断一个值是否不为NULL，如果不为NULL则返回1，否则返
回0。
#IS NULL ，IS NOT NULL，ISNULL(字段)
SELECT last_name ,commission_pct
FROM employees
WHERE commission_pct IS NULL;
SELECT commission_pct
FROM employees
WHERE NOT commission_pct <=>NULL;
最小值运算符LEAST和最大值运算符GREATEST
最小值运算符 语法格式为：LEAST(值1，值2，…，值n)。其中，“值n”表示参数列表中有n个值。在有两个或多个参数的情况下，返回最小值。当参数为字符串时，返回字母表中顺序最靠前的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。
最大值运算符 语法格式为：GREATEST(值1，值2，…，值n)。其中，n表示参数列表中有n个值。当有两个或多个参数时，返回值为最大值。当参数为字符串时，返回字母表中顺序最靠后的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。
#最大，最小，least(字段1，字段3...),greatest(字段1，字段2...)
SELECT LEAST(first_name,last_name),GREATEST(2,3)
FROM employees;
BETWEEN AND运算符
BETWEEN运算符使用的格式通常为SELECT D FROM TABLE WHERE C BETWEEN A AND B，此时，当C大于或等于A，并且C小于或等于B时，结果为1，否则结果为0。
# 字段+BETWEEN(下界) ... AND(上界)...(如果是不在此范围内，则在字段后加NOT)
#查询某字段从...到...的数据
SELECT first_name ,salary
FROM employees
WHERE salary NOT BETWEEN 4000 AND 8000
IN运算符和NOT IN运算符
IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL，则结果为NULL。
NOT IN运算符用于判断给定的值是否不是IN列表中的一个值，如果不是IN列表中的一个值，则返回1，否则返回0。
#IN(set) / NOT IN(set)
SELECT first_name ,department_id
FROM employees
#错误的写法:where department_id =10 or 20 or 30;
**#where department_id=10 or department_id=20 or department_id=30;**
WHERE department_id IN(10,20,30);
LIKE运算符
LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。
#5.like 模糊查询
# % 代表不确定的字符
SELECT last_name
FROM employees
#where last_name like '%a%';#查询last_name中带有a的数据
#wHERE last_name LIKE 'a%';#查询以a开头的
#WHERE last_name LIKE '%a';#查询以a结尾的
WHERE last_name LIKE '%a%e%';#查询a在前e在后的
_ 代表一个不确定的字符
# _ :代表一个不确定的字符
SELECT last_name
FROM employees
#where last_name like '_a%';#第二个字母是a的数据
#WHERE last_name LIKE '__a%';#第三个字母是a的数据
转义字符: \
#where last_name like '_\_a%';#第二个字母是_,第三个字母是a
如果使用\表示转义，要省略ESCAPE。如果不是\，则要加上ESCAPE。
WHERE last_name LIKE '_$_a%' ESCAPE '$';
REGEXP运算符
（1）‘^’匹配以该字符后面的字符开头的字符串。
（2）‘$’匹配以该字符前面的字符结尾的字符串。
（3）‘.’匹配任何一个单字符。
（4）“[…]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一 个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
（5）‘’匹配零个或多个在它前面的字符。例如，“x”匹配任何数量的‘x’字符，“[0-9]”匹配任何数量的数字， 而“”匹配任何数量的任何字符。
SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk';
SELECT 'abaabsf' REGEXP 'ba.bs', 'atguigu' REGEXP '[ab]';# . 代表取任一值
逻辑运算符(&& ,II, !, XOR)
#6.逻辑运算符
# NOT ！，AND && ，OR || ，XOR（异同，一个满足一个不满足）
SELECT last_name,salary,department_id
FROM employees
#WHERE department_id = 10 or department_id = 20;
#WHERE department_id = 10 and department_id = 20;
WHERE department_id = 10 OR salary > 6000;
#not
SELECT last_name,salary,department_id
FROM employees
#where commission_pct is not null;
WHERE NOT commission_pct <=> NULL;
#XOR:一真一假
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 50 XOR salary > 6000;
位运算符
& | ^ ~ >> <<
多表查询
#多表查询
#多表查询正确方式：需要有链接条件
SELECT employee_id,department_name
FROM employees,departments
#两个表的链接条件
WHERE employees.`department_id`=departments.`department_id`
#如果查询语句中出现了多个表中存在的字段，则必须指明此字段所在的表
SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`
#建议：从SQL优化角度，建议多表查询时，每个字段前都指明所在表
#可以给表起别名,在SELECT和WHERE中是使用
SELECT ep.employee_id,dp.department_name,ep.department_id
FROM employees ep,departments dp
WHERE ep.`department_id`=dp.`department_id`
#如果给表起了别名，如果在SELECT和WHERE中使用，则必须使用别名，不能用原名
#如果有n个表实现多表查询，则至少需要n-1个链接条件
#等值连接 vs 非等值连接
SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
#自连接 vs 非自连接
#自连接：在同一张表中查询
#查询员工id，姓名及其管理者id，姓名
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
#内连接 vs 外连接
#内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
SELECT ep.employee_id,dp.department_name,ep.department_id
FROM employees ep,departments dp
WHERE ep.`department_id`=dp.`department_id`
#外连接：合并具有同一列的两个以上的表的行, 除结果集中包含一个表与另一个表匹配的行外
#还包含左表或右表中不匹配的行
#外连接的分类：左外连接，右外连接，满外连接
#左外连接： 两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的
#行 ，这种连接称为左外连接。
#右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的
#行 ，这种连接称为右外连接。
#查询所有员工的last_name,department-name
#SQL92语法使用 + ------ MySQL不支持！！！！！！
SELECT ep.employee_id,dp.department_name,ep.department_id
FROM employees ep,departments dp
#WHERE ep.`department_id`=dp.`department_id`(+)#需要使用左外连接
#SQL99语法使用JOIN...ON...的方式实现多表查询，MySQL支持
#SQL99语法实现内连接 JOIN前加INNER,可省略
SELECT ep.`last_name`,dp.`department_name`,l.`city`
FROM employees ep INNER JOIN departments dp
ON ep.`department_id`=dp.`department_id`
JOIN locations l
ON dp.`location_id`=l.`location_id`;
#SQL99语法实现外连接
#左外连接：JOIN前加LEFT OUTER,OUTER可省略
SELECT ep.`last_name`,dp.`department_name`
FROM employees ep LEFT JOIN departments dp
ON ep.`department_id`=dp.`department_id`
#右外连接：JOIN前加RIGHT OUTER,OUTER可省略
SELECT ep.`last_name`,dp.`department_name`
FROM employees ep RIGHT JOIN departments dp
ON ep.`department_id`=dp.`department_id`
#UNION和UNION ALL的使用
#UNION:会执行去重操作
#UNION ALL：不会执行去重操作
#执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，
#或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率
#满外连接：在两条SQL语句中间加上UNION ALL(推荐)或UNION
#SQL99语法新特性：1.自然连接
SELECT ep.`employee_id`,ep.`last_name`,dp.`department_name`
FROM employees ep JOIN departments dp
ON ep.`department_id`=dp.`department_id`
AND ep.`manager_id`=dp.`manager_id`;
#NATURAL JOIN:你自动查询两张连接表中 所有相同的字段 ，然后进行 等值 连接
SELECT ep.`employee_id`,ep.`last_name`,dp.`department_name`
FROM employees ep NATURAL JOIN departments dp;
#USING:SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配
#合JOIN一起使用。
#JION...USING
SELECT ep.`employee_id`,ep.`last_name`,dp.`department_name`
FROM employees ep JOIN departments dp
USING(department_id);
聚合函数
#AVG(...)#求平均值，只计算非NULL值的平均值
SELECT AVG(salary)
FROM employees;
#SUM(...)#求和，只计算非NULL的和
SELECT SUM(salary)
FROM employees;
#COUNT(字段)#求字段数量，只计算非NULL的字段
SELECT COUNT(salary)
FROM employees;
#公式：AVG=SUM/COUNT
SELECT AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct)
FROM employees;
#计算表中有多少条记录
#方式1：COUNT(*)
#方式2：COUNT(1)
#方式3：COUNT(字段）#不一定准确#因为不计算NULL值
#GROUP BY
#求各个部门的平均工资
SELECT department_id , AVG(salary)
FROM employees
GROUP BY department_id
#查询各个部门department_id,job_id
SELECT department_id , job_id ,AVG(salary)
FROM employees
GROUP BY department_id , job_id;#顺序可颠倒
#结论（重点）：SELECT中出现的非组函数的字段必须声明在GROUP BY中
#反之，GROUP BY中声明的字段可以不出现在SELECT中
#GROUP BY声明位置如下：
SELECT job_id,department_id,salary
FROM employees
WHERE salary>6000
GROUP BY job_id,department_id
ORDER BY salary DESC
LIMIT 0,10
#WITH ROLLUP使用
#说明：使用 WITH ROLLUP 关键字之后，
#在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所
#有记录的总和，即统计记录数量。
#当使用WITH ROLLUP时，不能使用ORDER BY进行排序，两者相互排斥
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;
#ORDER BY salary DESC;
#HAVING
#如果过滤条件中使用了聚合函数，则必须使用HAVING来代替WHERE,否则报错
#HAVING必须放在GROUP BY的后面
#结论：HAVING中可以放聚合函数，WHERE不能，HAVING中也可以放非聚合函数，但
#建议将非聚合函数的判断条件放在WHERE中，执行效率更高
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000
####HAVING
#1. 行已经被分组。
#2. 使用了聚合函数。
#3. 满足HAVING 子句中条件的分组将被显示。
#4. HAVING 建议不要单独使用，最好与GROUP BY一起使用。
#方式1：推荐，执行效率高于方式2
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN(10,20,30,40)
GROUP BY department_id
HAVING MAX(salary)>10000;
#方式2：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 AND department_id IN(10,20,30,40);
/*
SQL92语法:
SELECT (DISTINCT)...,...,...(存在聚合函数)
FROM ...,...,...
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,...
HAVING 包含聚合函数的过滤条件
ORDER BY ...,...(ASC/DESC)
LIMIT ...,...
SQL99语法：
SQL92语法:
SELECT (DISTINCT)...,...,...(存在聚合函数)
FROM ...,(LEFT/RIGHT)JOIN...ON...
(LEFT/RIGHT)JOIN...ON...
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,...
HAVING 包含聚合函数的过滤条件
ORDER BY ...,...(ASC/DESC)
LIMIT ...,...
*/
#SQL语句的执行过程
#FROM ...,...->ON->(LEFT/RIGHT)JOIN->WHERE->GROUP BY->HAVING->SELECT->DISTINCT->
#ORDER BY->LIMIT
子查询
SELECT
last_name,
salary
FROM
employees
WHERE salary >
(SELECT
salary
FROM
employees
WHERE last_name = 'Abel');
/*子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用 。
注意事项
子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行操作符对应多行子查询
*/
/*
子查询的分类
角度1：从内查询返回的结果的条目数
单行子查询 vs 多行子查询
角度2：内查询是否被执行多次
相关子查询 vs 不相关子查询
*/
#多行子查询操作符IN ANY ALL SOME
创建和管理表
#创建和管理数据库
#方式1：`atguigudb`
CREATE DATABASE mytest1;#使用默认字符集
#
SHOW CREATE DATABASE mytest1;
#方式2：
CREATE DATABASE mytest2 CHARACTER SET 'gbk';#指明创建数据库的字符集
#查看数据库的字符集
SHOW VARIABLES LIKE 'CHARACTER_%';
#方式3（推荐）：
#如果创建数据库已存在，则创建不成功，但不会报错
CREATE DATABASE IF NOT EXISTS mytest2 CHARACTER SET 'utf8';
#如果创建数据库不存在，则创建成功
CREATE DATABASE IF NOT EXISTS mytest3 CHARACTER SET 'utf8';
#管理数据库
#查看当前连接中的数据库有哪些
SHOW DATABASES;
#切换数据库
USE atguigudb;
#查看当前数据库中保存到的数据表
SHOW TABLES;
#查看当前使用的数据库
SELECT DATABASE() FROM DUAL;
#查看指定数据库下保存的数据表
SHOW TABLES FROM atguigudb;
#修改数据库
#更改数据库字符集
ALTER DATABASE mytest2 CHARACTER SET 'utf8';
SHOW CREATE DATABASE mytest2;
#删除数据库
#方式1：
DROP DATABASE mytest1;
#方式2：推荐，如果要删除的数据库存在，则删除成功，如果不存在，则默默结束，不会报错
DROP DATABASE IF EXISTS mytest1;
#如何创建数据表
USE atguigudb;
SHOW CREATE DATABASE atguigudb;
#方式1：
CREATE TABLE IF NOT EXISTS myemp1(#需要用户具有创建表的权限
id INT,
emp_name VARCHAR(15),#可变长字符数据，根据字符串实际长度保存，必须指定长度
hire_date DATE
);
#查看表结构
DESC myemp1;
#查看创建表的语句结构
SHOW CREATE TABLE myemp1;#如果创建表示没有指明使用的字符集，则默认使用表所在的数据库的字符集，如果数据库也没指明，则与相应配置文件一致
#方式2：
#基于现有的表创建表,同时导入数据！
CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;
DESC myemp2;
SELECT *
FROM myemp2;
#说明1：查询语句中字段的别名，可以作为新创建的表的字段的名称
#说明2：此时的查询语句可以结构比较丰富，包括各种select语句
#列如：
CREATE TABLE myemp3
AS
SELECT e.employee_id 'em',e.last_name 'ln',d.department_name
FROM employees e JOIN departments d
ON e.department_id=d.department_id;
DESC myemp3;
SELECT *
FROM myemp3;
#创建一个表，实现对数据库中表的复制，但不包含原表的数据
CREATE TABLE employees_blank
AS
SELECT *
FROM employees
WHERE 1=2;
DESC employees_blank;
SELECT *
FROM employees_blank;
#修改表:ALTER TABLE
DESC myemp1;
#添加一个字段
ALTER TABLE myemp1#默认添加到表中的最后一个字段的位置
ADD salary DOUBLE(10,2);
ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;#添加到表头
ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;
DESC myemp1;
SELECT *
FROM myemp1;
#修改一个字段：数据类型、长度、默认值（略）
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';
DESC myemp1;
#重命名一个字段
ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50)
#删除一个字段
ALTER TABLE myemp1
DROP COLUMN my_emil;
#重命名表
#方式1：（推荐）
RENAME TABLE myemp1
TO myemp11;
DESC myemp11;
#方式2：
ALTER TABLE myemp2
RENAME TO myemp12;
DESC myemp12;
#删除表
#不仅删除表结构，同时删除表中的数据，释放空间
DROP TABLE IF EXISTS myemp12;
#清空表
#清空表中所有数据，但表结构保留
SELECT * FROM employees_copy;
TRUNCATE TABLE employees_copy;
SELECT * FROM employees_copy;
数据处理之增删改
#数据处理之增删改
USE atguigudb
SELECT DATABASE() FROM DUAL;
CREATE TABLE IF NOT EXISTS emp1(
id INT,
`name` VARCHAR(15),
hire_date DATE,
salary DOUBLE(10,2)
);
DESC emp1;
SELECT *
FROM emp1;
#添加数据
#方式1：一条一条的添加数据
#①没有指明添加的字段
INSERT INTO emp1
VALUES(1,'Tom','2000-12-21',3400);#一定要按照声明的字段的先后顺序添加
#②指明要添加的字段（推荐）
INSERT INTO emp1(id,hire_date,salary,`name`)
VALUES(2,'1999-09-09',4000,'Jerry');
#③同时插入多条数据（更推荐）
INSERT INTO emp1(id ,`name`,salary)
VALUES
(4,'Jim',5000),
(5,'张俊杰',5500);
#方式2：将查询的结果插入到表中
SELECT *
FROM emp1;
INSERT INTO emp1(id,`name`,salary,hire_date)
#查询语句
SELECT employee_id,last_name,salary,hire_date
FROM employees
WHERE department_id IN(60,70)#查询字段一定要与添加到表的字段一一对应
#说明：！！被添加表中要添加数据的字段长度不能低于 添加表 中查询字段的长度，否则有添加不成功的风险
#VALUES也可以使用VALUE，但VALUES是标准写法，推荐使用
#更新数据（或修改数据）
#UPDATE ... SET ... WHERE ...
#可以实现批量修改数据**加粗样式**
UPDATE emp1
SET hire_date=CURDATE()
WHERE id=5;
#同时修改一条数据的多个字段
UPDATE emp1
SET hire_date=CURDATE(),salary=6000
WHERE id=5;
#修改数据时是可能存在不成功的情况的，可能是由于约束的影响造成的
#删除数据 DELETE FROM ... WHERE ...
DELETE FROM emp1
WHERE id=1;
#删除数据时也可能因为约束影响，导致删除失败。
#小结：DML操作默认情况下，执行完成以后都会自动提交数据，如果希望执行完以后不自动提交数据则需要使用 SET autocommit=FALSE
#MySQL新特性：计算列
USE atguigudb
CREATE TABLE test1(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a+b) VIRTUAL#字段c即为计算列
);
INSERT INTO test1(a,b)
VALUES(10,20);
SELECT *
FROM test1;
排序与分页
#如果没有使用排序操作，默认情况下查询返回的数据是按照添加数据的顺序显示的
#使用ORDER BY 对查询到的数据进行排序操作
#升序ASC(ascend)
#降序DESC(descend)
#列如对薪水降序
SELECT employee_id,last_name,salary
FROM employees
#order by salary DESC;#如果ORDER BY后面没有指明排序方式的话，则默认按照升序排列
ORDER BY salary;
#可以使用类的别名进行排序
SELECT employee_id,last_name,salary*12 AS "ss"
FROM employees
ORDER BY ss ASC;
#类的别名只能在ORDER BY 中使用，不能在ORDER中使用
#原因：SQL语句先执行FROM后的语句，再执行WHERE后的语句，再执行SELECT后的语句，最后执行ORDER BY后的语句
#强调格式：WHERE需要声明在FROM后，ORDER BY 之前
SELECT employee_id,last_name,salary,salary*12 AS "ss"
FROM employees
WHERE salary>6000
ORDER BY ss ASC;
#二级排序
#使是department_id降排，salary升排
SELECT department_id,salary
FROM employees
ORDER BY department_id DESC , salary ASC;#多列排序以此类推
#分页
#MySQL使用LIMIT实现数据的分页显示
#每一页记录20条数据，显示第一页
SELECT employee_id , last_name
FROM employees
LIMIT 0,20;
#每一页记录20条数据，显示第2页
SELECT employee_id , last_name
FROM employees
LIMIT 20,20;
#每一页记录20条数据，显示第3页
SELECT employee_id , last_name
FROM employees
LIMIT 40,20;
#公式：(pageNo-1)*pageSize,pageSize
#结构：LIMIT 0,条目数 等价于 LIMIT 条目数
#WHERE...ORDER BY...LIMIT声明顺序如下：
SELECT employee_id , last_name,salary
FROM employees
WHERE salary>6000
ORDER BY salary DESC
LIMIT 20;
#LIMIT语句在ORDER BY语句之后执行
#MySQL8.0新特性LIMIT 条目数 OFFSET 偏移量 与 LIMIT ...,...相反
SELECT employee_id , last_name
FROM employees
LIMIT 2 OFFSET 20;
约束返回结果的数量可以 减少数据表的网络传输量 ，也可以 提升查询效率 。如果我们知道返回结果只有
1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需
要扫描完整的表，只需要检索到一条符合条件的记录即可返回。
MySQL数据类型
#CHARACTER SET NAME
#创建数据库是指明字符集
CREATE DATABASE IF NOT EXISTS dbtest12 CHARACTER SET 'utf8';
SHOW CREATE DATABASE dbtest12;
#创建表是指明表的字符集
CREATE TABLE temp(
id INT
) CHARACTER SET 'utf8';
SHOW CREATE TABLE temp;
#创建表，指明表中字段时，可以指定字段的字符集
CREATE TABLE temp1(
id INT,
`name` VARCHAR(15) CHARACTER SET 'gbk'
);
SHOW CREATE TABLE temp1;
DESC temp1;
#宽度
#可选属性
#MySQL5.7中显式宽度，从MySQL 8.0.17开始整数数据类型不推荐使用显示宽度属性
#无符号类型 UNSIGNED
#ZEROFILL:当时用此修饰时，该字段也会同时被UNSIGNED修饰！！！
CREATE TABLE temp2(
f1 INT UNSIGNED,
f2 INT,
f3 INT(5) ZEROFILL
)
#int(M)和int是一样的
#int(M)，必须和UNSIGNED ZEROFILL一起使用才有意义
#不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。
INSERT INTO temp2(f3)
VALUES (123),(123456789);
SELECT *
FROM temp2;
#浮点类型
#FLOAT(a,b)表示小数占b位，整数占a-b位，存储的数的小数位超出b位则四色五入到b位
#整数位不得超过a-b位
#因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。
#定点数类型 DECIMAL
/*
DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。
DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。也就是
说，在一些对精度要求不高的场景下，比起占用同样字节长度的定点数，浮点数表达的数值范围可
以更大一些。
定点数在MySQL内部是以 字符串 的形式进行存储，这就决定了它一定是精准的。
当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)!!!!!!。当数据的精度超出了定点数类型的
精度范围时，则MySQL同样会进行四舍五入处理!!!!!!
*/
#位类型：BIT，BIT类型中存储的是二进制值，类似010110。可以用BIN()和HEX()表示不同进制
#加0以后，可以以十进制显示数据
#日期与时间类型
/*
YEAR 类型通常用来表示年(1901~2155)：
DATE 类型通常用来表示年、月、日
TIME 类型通常用来表示时、分、秒
DATETIME 类型通常用来表示年、月、日、时、分、秒
TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒
*/
123 123 123)123
CREATE TABLE test_year(
f1 YEAR,
f2 YEAR(4)
);
DESC test_year;
INSERT INTO test_year(f1)
VALUES('2021'),(2020);#推荐第一个带引号的
SELECT *
FROM test_year;
#year两位表示时(不推荐，建议用四位的)
/*
当取值为01到69时，表示2001到2069；
当取值为70到99时，表示1970到1999；
当取值整数的0或00添加的话，那么是0000年；
当取值是日期/字符串的'0'添加的话，是2000年。
*/
#DATE类型
#DATE类型表示日期，没有时间部分，格式为 YYYY-MM-DD ，其中，YYYY表示年份，MM表示月份，DD表示
#日期。需要 3个字节 的存储空间。
#使用 CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期！！！
/*
插入数据
INSERT INTO test_date1
VALUES ('2020-10-01'), ('20201001'),(20201001);
*/
#TIME类型
/*
TIME类型用来表示时间，不包含日期部分。在MySQL中，需要 3个字节 的存储空间来存储TIME类型的数
据，可以使用“HH:MM:SS”格式来表示TIME类型，其中，HH表示小时，MM表示分钟，SS表示秒。
在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式。 （1）可以使用带有冒号的
字符串，比如' D HH:MM:SS' 、' HH:MM:SS '、' HH:MM '、' D HH:MM '、' D HH '或' SS '格式，都能被正
确地插入TIME类型的字段中。其中D表示天，其最小值为0，最大值为34。如果使用带有D格式的字符串
插入TIME类型的字段时，D会被转化为小时，计算格式为D*24+HH。当使用带有冒号并且不带D的字符串
表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10。 （2）可以使用不带有冒号的
字符串或者数字，格式为' HHMMSS '或者 HHMMSS 。如果插入一个不合法的字符串或者数字，MySQL在存
储数据时，会将其自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒，表示
00:12:10，而不是12:10:00。 （3）使用 CURRENT_TIME() 或者 NOW() ，会插入当前系统的时间。
*/
CREATE TABLE test_time1(
f1 TIME
);
INSERT INTO test_time1
VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');
INSERT INTO test_time1
VALUES ('123520'), (124011),(1210);
INSERT INTO test_time1
VALUES (NOW()), (CURRENT_TIME());
SELECT * FROM test_time1;
#DATETIME类型
/*
DATETIME类型在所有的日期时间类型中占用的存储空间最大，总共需要 8 个字节的存储空间。在格式上
为DATE类型和TIME类型的组合，可以表示为 YYYY-MM-DD HH:MM:SS ，其中YYYY表示年份，MM表示月
份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。
在向DATETIME类型的字段插入数据时，同样需要满足一定的格式条件。
以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，
最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。
以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DD HH:MM:SS格式。
*/
CREATE TABLE test_datetime1(
dt DATETIME
);
INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'), ('20210101065030');
INSERT INTO test_datetime1
VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'), ('200101000000');
INSERT INTO test_datetime1
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);
INSERT INTO test_datetime1
VALUES (CURRENT_TIMESTAMP()), (NOW());
SELECT *
FROM test_datetime1;
/*
TIMESTAMP类型 TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是 YYYY-MM-DD HH:MM:SS ，需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储
“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫
作世界标准时间。
存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时
区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。
向TIMESTAMP类型的字段插入数据时，当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS
时，两位数值的年份同样符合YEAR类型的规则条件，只不过表示的时间范围要小很多。
如果向TIMESTAMP类型的字段插入的时间超出了TIMESTAMP类型的范围，则MySQL会抛出错误信息。
*/
/*
TIMESTAMP和DATETIME的区别：
TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能
反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。
*/
/*
用得最多的日期时间类型，就是 DATETIME 。虽然 MySQL 也支持 YEAR（年）、 TIME（时间）、
DATE（日期），以及 TIMESTAMP 类型，但是在实际项目中，尽量用 DATETIME 类型。因为这个数据类型
包括了完整的日期和时间信息，取值范围也最大，使用起来比较方便。毕竟，如果日期时间信息分散在
好几个字段，很不容易记，而且查询的时候，SQL 语句也会更加复杂。
此外，一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用 时间戳 ，因为
DATETIME虽然直观，但不便于计算。
*/
#CHAR
/*CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在 右侧填充 空格以达到指定的长
度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
*/
#VARCHAR
/*
VARCHAR(M) 定义时， 必须指定 长度M，否则报错。
MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字
节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。
检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间
为字符串实际长度加1个字节。
*/
#TEXT
/*
在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、 MEDIUMTEXT 和 LONGTEXT 类型。
在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。这一点和
VARCHAR类型相同。
由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用
CHAR(M)，或者 VARCHAR(M)。
TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR， VARCHAR来代替。还有TEXT类型不用加默认值，加了也没用。而且text和blob类型的数据删除后容易导致
“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用
一个表。
*/
#枚举类型ENUM
CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);
#忽略大小写
INSERT INTO test_enum
VALUES('UNKNOW');
# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum
VALUES('1'),(3);
# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');
# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);
SELECT *
FROM test_enum;
#SET类型
/*SET表示一个字符串对象，可以包含0个或多个成员，但成员个数的上限为 64 。设置字段值时，可以取
取值范围内的 0 个或多个值。
SET类型在存储数据时成员个数越多，其占用的存储空间越大。注意：SET类型在选取成员时，可以一次
选择多个成员，这一点与ENUM类型不同。
*/
CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);
INSERT INTO test_set (s)
VALUES ('A'), ('A,B');
#插入重复的SET类型成员时，MySQL会自动删除重复的成员
INSERT INTO test_set (s)
VALUES ('A,B,C,A');
#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。
INSERT INTO test_set (s)
VALUES ('A,B,C,D');
SELECT *
FROM test_set;
#二进制字符串类型
/*
二进制字符串类型
MySQL中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数
据。
MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB类型。
BINARY与VARBINARY类型 BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。
BINARY (M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。如果未
指定(M)，表示只能存储 1个字节 。例如BINARY (8)，表示最多能存储8个字节，如果字段值不足(M)个字
节，将在右边填充'\0'以补齐指定长度。
VARBINARY (M)为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长
度限制65535，另外还要考虑额外字节开销，VARBINARY类型的数据除了存储数据本身外，还需要1或2个
字节来存储数据的字节数。VARBINARY类型 必须指定(M) ，否则报错。
*/
CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3), # f3 VARBINARY,
f4 VARBINARY(10)
);
#BLOB类型
/*
BLOB是一个 二进制大对象 ，可以容纳可变数量的数据。
MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型，它们可容纳值的最大
长度不同。可以存储一个二进制的大对象，比如 图片 、 音频 和 视频 等。
需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图
片、音频和视频文件存储到 服务器的磁盘上 ，并将图片、音频和视频的访问路径存储到MySQL中
*/
CREATE TABLE test_blob1(
id INT, img MEDIUMBLOB
);
/*
TEXT和BLOB的使用注意事项：
在使用text和blob字段类型时要注意以下几点，以便更好的发挥数据库的性能。
① BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值
会在数据表中留下很大的" 空洞 "，以后填入这些"空洞"的记录可能长度不同。为了提高性能，建议定期
使用 OPTIMIZE TABLE 功能对这类表进行 碎片整理 。 ② 如果需要对大文本字段进行模糊查询，MySQL 提供了 前缀索引 。但是仍然要在不必要的时候避免检
索大型的BLOB或TEXT值。例如，SELECT * 查询就不是很好的想法，除非你能够确定作为约束条件的
WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。
③ 把BLOB或TEXT列 分离到单独的表 中。在某些环境中，如果把这些数据列移动到第二张数据表中，可
以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会 减少主表中的 碎片 ，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过
网络传输大量的BLOB或TEXT值。
*/
#JSON 类型
/*
JSON（JavaScript Object Notation）是一种轻量级的 数据交换格式 。简洁和清晰的层次结构使得 JSON 成
为理想的数据交换语言。它易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效
率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻
松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。
在MySQL 5.7中，就已经支持JSON数据类型。在MySQL 8.x版本中，JSON类型提供了可以进行自动验证的
JSON文档和优化的存储结构，使得在MySQL中存储和读取JSON类型的数据更加方便和高效。 创建数据
表，表中包含一个JSON类型的字段 js 。
*/
CREATE TABLE test_json(
js JSON
);
INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}');
SELECT *
FROM test_json;
#空间类型......................？？？？！！！！！???!!!
视图
#视图
#视图的理解
/*
视图是一种 虚拟表 ，本身是 不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念。
视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和
修改操作时，数据表中的数据会相应地发生变化，反之亦然。
向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句
在数据库中，视图不会保存数据，数据真正保存在数据表中。当对视图中的数据进行增加、删
除和修改操作时，数据表中的数据会相应地发生变化；反之亦然。
视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视
图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我
们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便
*/
CREATE DATABASE dbtest14;
USE dbtest14;
CREATE TABLE emps
AS
SELECT *
FROM `atguigudb`.employees;
CREATE TABLE depts
AS
SELECT *
FROM `atguigudb`.departments;
SELECT *
FROM emps;
SELECT *
FROM depts;
#针对于单表
CREATE VIEW vu_emp1
AS
SELECT employee_id,last_name,salary
FROM emps;
SELECT *
FROM vu_emp1;
#确定视图中字段的名称：
#方式1：查询语句中字段的别名会作为视图中字段的名称
CREATE VIEW ve2
AS
SELECT employee_id ei, last_name lan,salary
FROM emps
WHERE salary > 8000;
SELECT *
FROM ve2;
#方式2：
#小括号内字段个数与select中字段个数相同
CREATE VIEW ve3(ei,lan,sa)
AS
SELECT employee_id ei, last_name lan,salary
FROM emps
WHERE salary > 8000;
SELECT *
FROM ve3;
#利用视图对数据进行格式化
CREATE VIEW ved
AS
SELECT CONCAT(e.last_name,'(',d.department_name,')') ed
FROM emps e JOIN depts d
ON e.department_id=d.department_id;
SELECT *
FROM ved;
#基于视图创建视图
CREATE VIEW ved2
AS
SELECT employee_id,last_name
FROM vu_emp1
SELECT *
FROM ved2;
#查看视图
#语法1：查看数据库的表对象、视图对象
SHOW TABLES;
#语法2：查看视图的结构
DESCRIBE vu_emp1;
#语法3：查看视图的属性信息
SHOW TABLE STATUS LIKE 'vu_emp1';
#语法4：查看属图的的详细定义信息
SHOW CREATE VIEW vu_emp1;
#更新视图中的数据，会导致基表中的数据被修改，反之亦然
#要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。
/*
在定义视图的时候指定了“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；
视图定义基于一个 不可更新视图 ；
常量视图。
*/
#对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。
#修改视图
#方式1：
#创建或替换视图create or replace
CREATE OR REPLACE VIEW vu_emp1
AS
SELECT employee_id,last_name,salary,email
FROM emps
WHERE salary>7000;
DESC vu_emp1;
/*
方式2：
ALTER VIEW 视图名称
AS
查询语句
*/
#删除视图
/*
DROP VIEW IF EXISTS 视图名称;
DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;
*/
存储过程与存储函数
#存储过程与存储函数
#准备工作
CREATE DATABASE dbtest15;
USE dbtest15;
CREATE TABLE employees
AS
SELECT *
FROM atguigudb.`employees`;
CREATE TABLE departments
AS
SELECT *
FROM atguigudb.`departments`;
SELECT *
FROM employees;
SELECT *
FROM departments;
#创建存储过程
#类型1：无参数无返回值
#举例1：创建存储过程select_all_data()，查看 emps 表的所有数据
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT *
FROM employees;
END $
DELIMITER ;
#存储过程的调用
CALL select_all_data();
#举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资
DELIMITER //
CREATE PROCEDURE avg_employee_salary()
BEGIN
SELECT AVG(salary)
FROM employees;
END//
DELIMITER ;
#调用
CALL avg_employee_salary()
#举例3：创建存储过程show_max_salary()，用来查看“emps”表的最高薪资值
DELIMITER //
CREATE PROCEDURE show_max_salary()
BEGIN
SELECT MAX(salary)
FROM employees;
END//
DELIMITER//
#调用
CALL show_max_salary()
#类型2：带 out：当前参数为输出参数，也就是表示出参
#举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。
#并将最低薪资通过OUT参数“ms”输出
DESC employees;
DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
SELECT MIN(salary) INTO ms
FROM employees;
END //
DELIMITER ;
#调用
CALL show_min_salary(@ms);
#查看变量值
SELECT @ms;
#IN ：当前参数为输入参数，也就是表示入参；
#举例5：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，
#并用IN参数empname输入员工姓名。
DELIMITER //
CREATE PROCEDURE show_someone_salary (IN empname VARCHAR (20))
BEGIN
SELECT
salary
FROM
employees
WHERE last_name = empname;
END //
DELIMITER ;
#调用方式1：
CALL show_someone_salary('Abel');
#调用方式2：
SET @empname := 'Abel';
CALL show_someone_salary(@empname);
#类型4：带IN和OUT
#举例6：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname
#输入员工姓名，用OUT参数empsalary输出员工薪资。
DELIMITER //
CREATE PROCEDURE
#举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，
#并用INOUT参数“empname”输入员工姓名，输出领导的姓名
DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
SELECT last_name INTO empname
FROM employees
WHERE employee_id=(
SELECT manager_id
FROM employees
WHERE last_name=empname
);
END//
DELIMITER ;
#调用
SET @empname='Abel';
CALL show_mgr_name(@empname);
SELECT @empname;
#存储函数的使用
#创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，
#数据类型为字符串型。
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
BEGIN
RETURN (SELECT email FROM employees WHERE last_name='Abel');
END //
DELIMITER ;
#调用
SELECT email_by_name();
#创建存储函数，名称为email_by_id()，参数传入emp_id，
#该函数查询emp_id的email，并返回，数据类型为字符串型。
#创建函数前，执行此语句，保证函数创建会成功
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
CREATE FUNCTION mail_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGIN
RETURN (SELECT email FROM employees WHERE employee_id=emp_id);
END//
DELIMITER ;
#调用
SELECT mail_by_id(100);
#创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，
#并返回，数据类型为整型。
DELIMITER //
CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
BEGIN
RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END//
DELIMITER ;
#调用
SELECT count_by_id(30);
#或
SET @abc=50;
SELECT count_by_id(@abc);
#存储过程，存储函数的查看
#使用show create 查看存储过程，存储函数的创建信息
SHOW CREATE PROCEDURE show_mgr_name;
SHOW CREATE FUNCTION count_by_id;
#使用SHOW STATUS语句查看存储过程和函数的状态信息
#这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;
#模糊查询
#SHOW PROCEDURE STATUS like '...';
#SHOW FUNCTION STATUS LIKE '...';
#从information_schema.Routines表中查看存储过程和函数的信息
/*
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
*/
#修改
#修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现
/*
ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名
[characteristic ...]
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER } |
COMMENT 'string'
*/
/*其中，characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有
不同。
CONTAINS SQL ，表示子程序包含SQL语句，但不包含读或写数据的语句。
NO SQL ，表示子程序中不包含SQL语句。
READS SQL DATA ，表示子程序中包含读数据的语句。
MODIFIES SQL DATA ，表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER | INVOKER } ，指明谁有权限来执行。
DEFINER ，表示只有定义者自己才能够执行。
INVOKER ，表示调用者可以执行。
COMMENT 'string' ，表示注释信息。
*/
#删除
#删除存储过程和函数，可以使用DROP语句，其语法结构如下：
DROP FUNCTION count_by_id;
DROP PROCEDURE show_min_salary;
变量、流程控制与游标
#变量、流程控制与游标
#变量
#系统变量：全局系统变量、会话系统变量 、（还有一些既是全局也是会话）
#vs
#用户自定义变量
#查看全局系统变量
SHOW GLOBAL VARIABLES;#全局617
#查看会话系统变量
SHOW SESSION VARIABLES;#会话640
#默认查询的是会话系统变量
SHOW VARIABLES;
#查询部分系统变量
SHOW GLOBAL VARIABLES LIKE 'admin_%';
SHOW VARIABLES LIKE 'character_%';
#查看指定系统变量
/*
作为 MySQL 编码规范，MySQL 中的系统变量以 两个“@” 开头，其中“@@global”仅用于标记全局系统变
量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，
则标记全局系统变量。
*/
#查看指定的系统变量的值 SELECT @@global.变量名;
#查看指定的会话变量的值 SELECT @@session.变量名;
#或者 SELECT @@变量名;
SELECT @@global.max_connections;
SELECT @@global.character_set_client;
SELECT @@session.character_set_client;
SELECT @@character_set_client;#先查询会话
#修改系统变量的值
#为某个系统变量赋值
#方式1： SET @@global.变量名=变量值;
#方式2： SET GLOBAL 变量名=变量值;
#为某个会话变量赋值 #方式1： SET @@session.变量名=变量值;
#方式2： SET SESSION 变量名=变量值;
#方式1：
SET @@global.max_connections=161;
#方式2：
SET GLOBAL max_connections = 171;
#针对当前数据库的实例是有效的，一旦重启mysql服务，就失效了
#会话系统变量
#方式1：
SET @@session.character_set_client='gbk';
#方式2：
SET SESSION character_set_client='gbk';
#仅针对当前会话有效，结束会话，建立新链接则失效
#用户变量
#会话用户变量 vs 局部变量
#会话用户变量：作用域和会话变量一样，只对 当前连接 会话有效。
#局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在 存储过程和函数 中使用。
#会话用户变量
#变量的定义
#方式1：“=”或“:=” SET @用户变量 = 值;
#SET @用户变量 := 值;
#方式2：“:=” 或 INTO关键字 SELECT @用户变量 := 表达式 [FROM 等子句];
#SELECT 表达式 INTO @用户变量 [FROM 等子句];
#准备工作
CREATE DATABASE dbtest16;
USE dbtest16;
CREATE TABLE employees
AS
SELECT * FROM atguigudb.`employees`;
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
#方式1：
SET @m1=1;
SET @m2=2;
SET @m3=@m1+@m2;
SELECT @m3;
#方式2：
SELECT @count :=COUNT(*) FROM employees;
SELECT @count;
SELECT AVG(salary) INTO @sa FROM employees;
SELECT @sa;
#局部变量
#定义：可以使用 DECLARE 语句定义一个局部变量
#作用域：仅仅在定义它的 BEGIN ... END 中有效
#位置：只能放在 BEGIN ... END 中，而且只能放在第一句
/*
#声明局部变量
DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
#为局部变量赋值
SET 变量名1 = 值;
SELECT 值 INTO 变量名2 [FROM 子句];
#查看局部变量的值
SELECT 变量1,变量2,变量3;
使用变量（查看、比较、运算等）
SELECT 局部变量名;
*/
DELIMITER //
CREATE PROCEDURE hhhh()
BEGIN
DECLARE a INT DEFAULT 0;
DECLARE b INT ;
#declare a,b int default 0;
DECLARE emp_name VARCHAR(25);
#赋值
SET a=1;
SET b:=2;
SELECT last_name INTO emp_name FROM employees WHERE employee_id=101;
#使用
SELECT a,b,emp_name;
END//
DELIMITER ;
#调用存储过程
CALL hhhh();
# 定义条件与处理程序
DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
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 ;
DROP PROCEDURE UpdateDataNoCondition;
CALL UpdateDataNoCondition();
#定义条件
/*
定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。
它将一个 错误名字 和 指定的 错误条件 关联起来。
这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中。
DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）
*/
#举例1：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。
#使用MySQL_error_code
#方式1：使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
#方式2：使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';
/*
#方法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 UpdateDataNoConditio()
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 ;
#调用
CALL UpdateDataNoConditio();
SELECT @x;
#流程控制
#分支结构IF
DELIMITER //
CREATE PROCEDURE aif()
BEGIN
DECLARE aif VARCHAR(15);
IF aif IS NULL
THEN SELECT 'a is null';
END IF;
END //
DELIMITER ;
CALL aif();
/*
表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
*/
#分支结构CASE
#CASE 语句的语法结构1：
#类似于switch
/*
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号) ...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
*/
#CASE 语句的语法结构2：
#情况二：类似于多重if
/*
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号) ...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
*/
#循环结构之LOOP
/*
LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子
句），跳出循环过程。
[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]
*/
DELIMITER //
CREATE PROCEDURE haa()
BEGIN
DECLARE a INT DEFAULT 1;
loop_bl :LOOP
SET a = a + 1;
IF a>=10 THEN LEAVE loop_bl;
END IF;
END LOOP loop_bl;
SELECT a;
END //
DELIMITER ;
CALL ha();
#循环结构WHILE
/*
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];
*/
DELIMITER //
CREATE PROCEDURE aa()
BEGIN
DECLARE a INT DEFAULT 1;
WHILE a<=10 DO
SET a=a+1;
END WHILE;
SELECT a;
END//
CALL aa();
#循环结构REPEAT
/*
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]
*/
DELIMITER //
CREATE PROCEDURE test_repeat ()
BEGIN
DECLARE i INT DEFAULT 0;
REPEAT
SET i = i + 1;
UNTIL i >= 10
END REPEAT;
SELECT i;
END //
DELIMITER ;
CALL test_repeat();
#跳转语句之LEAVE语句
/*
LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出
程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break。
基本格式如下：
LEAVE 标记名
其中，label参数表示循环的标志。LEAVE和BEGIN ... END或循环一起被使用。
这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名
称。 2、 LOOP：一般用于实现简单的"死"循环 WHILE：先判断后执行 REPEAT：先执行后判断，无条件
至少执行一次
*/
#ITERATE语句
/*
只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序
转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意
思为“再次循环”。
语句基本格式如下：
ITERATE label
label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。
*/
#注loop_label表示LOOP语句的标注名称，该参数可以省略。
#当其不可省略时，标注名称为 循环体名称+任意字符！！！！！！
#游标
/*
第一步，声明游标
DECLARE cursor_name CURSOR FOR select_statement;
要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，这里 select_statement 代表的是
SELECT 语句，返回一个用于创建游标的结果集。
第二步，打开游标
OPEN cursor_name
当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候 SELECT 语句的查询结
果集就会送到游标工作区，为后面游标的 逐条读取 结果集中的记录做准备。
第三步，使用游标（从游标中取得数据）
FETCH cursor_name INTO var_name [, var_name] ...
这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游
标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。
var_name必须在声明游标之前就定义好。
注意：游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时
候，MySQL 会提示错误。
第四步，关闭游标
CLOSE cursor_name
有 OPEN 就会有 CLOSE，也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。因为游标会
占用系统资源 ，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。而关闭游标
的操作，会释放游标占用的系统资源。
DECLARE cursor_name CURSOR FOR select_statement; DECLARE cursor_name CURSOR IS select_statement; DECLARE cur_emp CURSOR FOR SELECT employee_id,salary FROM employees; DECLARE cursor_fruit CURSOR FOR SELECT f_name, f_price FROM fruits ; OPEN cursor_name OPEN cur_emp ; FETCH cursor_name INTO var_name [, var_name] ... FETCH cur_emp INTO emp_id, emp_sal ; CLOSE cursor_name
关闭游标之后，我们就不能再检索查询结果中的数据行，如果需要检索只能再次打开游标。
游标是 MySQL 的一个重要的功能，为 逐条读取 结果集中的数据，提供了完美的解决方案。跟在应用层
面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。
但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行 加锁 ，这样在业务并发量大
的时候，不仅会影响业务之间的效率，还会 消耗系统资源 ，造成内存不足，这是因为游标是在内存中进
行的处理。
建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。
*/
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 ;
CALL get_count_by_limit_total_salary(200000,@t);
SELECT @t;
#MySQL 8.0的新特性—全局变量的持久化
/*
使用SET GLOBAL语句设置的变量值只会 临时生效 。 数据库重启 后，服务器又会从MySQL配置文件中读取
变量的默认值。 MySQL 8.0版本新增了 SET PERSIST 命令。
MySQL会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用
其中的配置来覆盖默认的配置文件。
*/
SET GLOBAL MAX_EXECUTION_TIME=2000;
触发器
#触发器
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)
);
#触发器执行的语句块 ：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块
DELIMITER //
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log (t_log)
VALUES('aaa');
END//
DELIMITER ;
INSERT INTO test_trigger_log (t_log)
VALUES ('bbb');
SELECT *
FROM test_trigger;
SELECT *
FROM test_trigger_log;
#自定义报错显示
#SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
#方式1：查看当前数据库的所有触发器的定义
SHOW TRIGGERS;
方式2：查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名
#方式3：从系统库information_schema的TRIGGERS表中查询所有触发器的信息。
SELECT * FROM information_schema.TRIGGERS;
#删除触发器
DROP TRIGGER IF EXISTS 触发器名称;