MySQL基础

安装

下载mysql免安装版

解压

命令行管理员模式，cd到D:\Program Files\mysql-8.0.22-winx64\bin

mysqld --initialize --console

mysqld -install mysql

net start mysql

mysql -u root -p

修改密码

alter user ‘root’@‘localhost’ identified with mysql_native_password by ‘123123’;

net stop mysql

登录： mysql -uroot -p123123

连接别人的数据库：mysql -h主机 -P端口号 -u用户 -p密码

mysql -h192.168… -P3306 -uroot -p123123

mysql -hlocalhost -P3306 -uroot -p123123

权限管理：

# 查看账户信息
USE mysql;
SELECT user FROM user;
# 创建账户
CREATE USER 用户名 identitied BY 'mypassword';
# 修改账户名
RENAME USER 用户名 TO 新用户名;
# 修改密码： 必须使用 Password() 函数进行加密。
set password = password('new_password');		# 当前用户
set password for 用户名 = password('new_password'); # 指定用户 
update mysql.user set password('new_password') when user='用户名'；
flush privileges；
# 删除账户
DROP USER 用户名;
# 查看权限
SHOW GRANTS FOR 用户名;
# 授予权限GRANT： 账户用 username@host 的形式定义，username@% 使用的是默认主机名。
  SELECT, INSERT ON mydatabase.* TO myuser;
# 删除权限REVOKE
REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;
# 访问权限层次：
1、整个服务器，使用 GRANT ALL 和 REVOKE ALL；
2、整个数据库，使用 ON database.*；
3、特定的表，使用 ON database.table；
4、特定的列；
5、特定的存储过程。

数据库操作

创建数据库：

• create database 库名 character set 字符集名；

• create database if not exists 库名；

删除库：

• drop database 库名；
• drop if not exist database 库名；

查询所有数据库：show databases;

显示当前所在数据库：select database();

选中数据库：use 库名

修改数据库名：rename database 库名 to 新库名；（已废弃）

修改数据库字符集：alter database 库名 character set gbk；

退出： exit；

数据表操作

创建数据表：

create table 表名(

​ 列名 数据类型(长度) 约束，

​ …

​ 列名 数据类型(长度) 约束

)；

删除数据表：

• drop table 表名;
• drop table if exists 表名;

修改数据表：

• 改列名：alter table 表名 change (column) 列名 新列名 数据类型； # 数据类型必须加

• 改类型：alter table 表名 modify (column) 列名 数据类型 (新约束)；

• 添加列：alter table 表名 add (column) 列名 数据类型 (first|after 字段名)；

• 删除列：alter table 表名 drop (column) 列名；

• 改表名：alter table 表名 rename (to) 新表名；

复制表：

• 复制结构：create table 新表名 like 表名
• 复制结构和数据：create table 新表名 select * from 表名；
• 复制部分：create table 新表名 select 列名, … from 表名 where 条件；（只复制结构，条件可以写 0 ）
• 跨库复制：creat table 新库名 select 列名 from 库名.表名；

查询当前库的数据表：show tables；

查询其他库的数据表：show tables from 其他数据库；

查看数据表结构：

• desc 表名；

• describe 表名;

• show columns from 表名;

修改字符集：set names gbk； utf8

修改数据表字符集：alter table 表名 convert to character set gbk；

插入数据：insert into tableName values ()

1、插入值类型要和列的类型一致或兼容
2、nullable的列可以插入NULL 或者 不写列名
3、列名顺序可以调换
4、列名不写就是默认所有列，且顺序必须和表中列顺序一致
5、 insert 后面可加上 ignore存在则忽略， replace存在则替换。
# 方式一:
   	支持插入多行
    insert into 表名
    values（值1，...），（值1，...）；

    insert into 表名（列名，...）
    values（值1，...）；
    
   	支持子查询
    insert into 表名
    select ...；
    
# 方式二: 只能插入一行，不支持子查询
    insert into 表名
    set 列名=值1，...；

删除数据：delete from tableName where name=‘**’；

1、单表删除
delete from 表名 
where 筛选条件
limit 条目数；

2、多表删除

删除表中所有
truncate table 表名； 相当于 delete from 表名； 

※ 区别：（重要）
- truncate 效率高一点，没有返回值（删除了多少行），不能添加筛选条件，不能回滚。
- delete   相反。
- delete   删除后，插入数据，自增长列从断点开始
- truncate 删除后，插入数据，自增长列从 1 开始

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

sql92：
delete 别名1，别名2   # 可以选择
from 表名1 别名1，表名2 别名2
where 连接条件
and 筛选条件；

修改数据：update tableName set name=‘**’ where owner=‘~~’；

1、修改单表
update 表名 
set 列=新值1，列=新值2...
where 筛选条件；
2、修改多表：
sql99：
update 表名1 别名
inner|left|right join 表名2 别名
on 连接条件
set 列=新值1，列=新值2...
where 筛选条件；
sql92：
update 表名1 别名，表名2 别名
set 列=新值1，列=新值2...
where 连接条件
and 筛选条件；

清空表：truncate table tableName ;

修改表

添加表： ALTER TABLE mytable ADD col CHAR(20);
删除列： ALTER TABLE mytable DROP COLUMN col;
删除表： DROP TABLE mytable;

插入

## 普通插入
INSERT INTO mytable(col1, col2)
VALUES(val1, val2);

## 插入检索数据
INSERT INTO mytable1(col1, col2)
SELECT col1, col2
FROM mytable2;

## 插入表到新表
CREATE TABLE newtable AS
SELECT * FROM mytable;

查询

## DISTINCT：相同值只会出现一次。所有列的值都相同才算相同。
SELECT DISTINCT col1, col2
FROM mytable;

## LIMIT(offest, size)：限制返回的行数。第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。
SELECT * FROM mytable
LIMIT 5;

SELECT * FROM mytable
LIMIT 2, 3;

过滤

=, >, >=大于等于(!<不小于), !=不等于 (<>), between， is null 为空
and or，优先处理and
not表否定
in 表包含，匹配一组值

通配符

## 通配符位于开头处匹配会非常慢
% 匹配 >=0 个任意字符
_ 匹配 ==1 个任意字符
[] 匹配集合内的字符，[ab]匹配字符 a 或者 b
^ 脱字符进行否定，[^AB] 不是 A 和 B 
Like 链接通配符

## eg：
SELECT * FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

# 转义字符：如找名字带有’_‘的。
SELECT * FROM nameTable
WHERE name LIKE '[%\_%]%';
或者
WHERE name LIKE '[%$_%]%' escape '$';  其中 $ 可以自定义。

计算字段

## 服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。 

## 用 AS 来取别名，否则输出的时候字段名为计算表达式。
SELECT col1 * col2 AS alias
FROM mytable;

## CONCAT() 用于连接两个字段
## 许多数据库会使用空格把一个值填充为列宽，用 TRIM() 可以去除首尾空格。
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;

函数

## 各个 DBMS 的函数都是不相同的，因此不可移植，以下主要是 MySQL 的函数。
## sql函数都有返回值

# 一、统计（聚合函数，分组函数）
AVG()	返回某列的平均值
COUNT()	返回某列的非空字段行数，参数放 * 或者 字符串/数字之类 查总行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和
# eg：
平均值： AVG() 会忽略 NULL 行。
SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;  ##  用 DISTINCT 可以汇总不同的值。

# 二、文本处理
LENGTH()	字节长度，中文在utf8占3个字节
CHAR_LENGTH() 字符长度
LOWER()	转换为小写字符
UPPER()	转换为大写字符
LEFT(string, len)	左边的字符
RIGHT(string, len)	右边的字符

TRIM()	去除前后空格
TRIM(str from string)	去除前后指定字符
LTRIM()	去除左边的空格
RTRIM()	去除右边的空格

Lpad(string, length, str)  在string左边填充str直到总长度为length
Rpad(string, length, str)  在string右边填充str直到总长度为length

substr(string, beg, length) 截取字串，起始索引从1开始。没有length就是后面所有
instr(string, substring)  字符第一次出现的索引，
strcmp(stringa, stringb)  比较两个字符大小，第一个开始，往后比。返回 正负1 和 0 

SOUNDEX()	转换为语音值。将一个字符串转换为描述其语音表示的字母数字模式。
# eg：
SELECT * FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')

# 三、日期处理
ADDDATE()	增加一个日期（天、周等）
ADDTIME()	增加一个时间（时、分等）
NOW()		返回当前日期和时间
CURDATE()	返回当前日期
CURTIME()	返回当前时间
DATE()		返回日期时间的日期部分
TIME()		返回日期时间的时间部分

DATEDIFF(date, date)	计算两个日期之差，返回天数
DATE_ADD()	高度灵活的日期运算函数
DATE_FORMAT(date, '%Y年%m月%d日 %H时%i分%s秒')	返回一个格式化的日期或时间串
str_to_date('3/15 1998', '%m/%d %Y')  返回字符串解析的日期

YEAR()		返回一个日期的年份部分
MONTH()		返回一个日期的月份部分
DAY()		返回一个日期的天数部分
DAYOFWEEK()	对于一个日期，返回对应的星期几
HOUR()		返回一个时间的小时部分
MINUTE()	返回一个时间的分钟部分
SECOND()	返回一个时间的秒部分

# eg：
SELECT NOW();

# 四、数值处理
ABS()	绝对值
SQRT()	平方根
MOD()	余数
EXP()	指数
ceil()  向上取整， >=的最大整数
floor() 向下取整，<=的最大整数
round(num，len) 四舍五入，len表示小数位数
truncate(num, len)  截断，len表示小数位数，后面的都不要。
SIN()	正弦
COS()	余弦
TAN()	正切
PI()	圆周率
RAND()	随机数

# 五、流程控制
if(100>9, 'good', 'bad')

# case 作为表达式（值），嵌套在其他语句中使用，也可以放到任何地方。 begin end 内或外。
# case 作为单独的语句（语句），只能在 begin end 内。
# 执行时，when满足，执行then语句，并直接跳出。
# else可以省略，若全部不满足，返回null
case 表达式|变量|字段
when 值1 then 结果1|语句1;  # 若表达式=值1，执行
when 值2 then 结果2|语句2;
else 结果n|语句n;
end as 别名

case
when 条件1 then 结果1|语句1;
when 条件2 then 结果2|语句2;
else 结果n|语句n;
end as 别名

窗口函数

# 定义
也叫OLAP函数（Online Anallytical Processing，联机分析处理）
常用于解决：排名问题，topN问题
# 功能：
具有分组（partition by）和排序（order by）的功能
不减少原表的行数，所以经常用来在每组内排名
原则上只能写在select子句中
# 语法：
<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)
# 分类：
专用窗口函数： rank, dense_rank, row_numbe等
聚合函数：sum, avg, count, max, min


# 专用窗口。分数为，100， 100， 90；
rank：分数相同并列，之后不顺延。1，1，3
dese_rank：分数相同并列，之后顺延。1，1，2
row_number：分数不并列。1，2，3

# 专用窗口函数案例
select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 成绩表

# partition by用来对表分组
select *,
   rank() over (partition by 班级
                order by 成绩 desc) as ranking
from 成绩表

# 聚合函数。分数为 20， 30， 25.
sum：当前总和。20， 50， 75.
count：当前个数。1， 2， 3.
max：当前最大。20， 30， 30.
# 聚合函数案例
select *,
   sum(成绩) over (order by 学号) as current_sum, 
   avg(成绩) over (order by 学号) as current_avg,
   count(成绩) over (order by 学号) as current_count,
   max(成绩) over (order by 学号) as current_max,
   min(成绩) over (order by 学号) as current_min
from 班级表

分组

• GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
• 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
• NULL 的行会单独分为一组；
• 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

group by和partiition by： 1，2，3，1，2

• group by分组汇总后改变了表的行数，一行只有一个类别。变成：1，2，3
• partiition by不会减少原表中的行数，会将信息相同的放到一起。变成：1，1，2，2，3

## 把具有相同的数据值的行放在同一组中，再用汇总函数进行处理。
SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col;   # GROUP BY 自动按分组字段进行排序

# ORDER BY 自动按汇总字段来进行排序
SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;

# WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。
# where 是分组前筛选，不能用到count产生的num
# having是分组后筛选
SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;

# 执行顺序，每个子句执行都会产生一个临时表
from
where
group by
having
select
order by

组合查询

# 使用 UNION 来组合两个查询.
1、如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。
2、每个查询的列数必须相同，类型不相同不报错。
3、默认会去除相同行，如果需要保留相同行，使用 UNION ALL。
4、只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

SELECT col FROM mytable
WHERE col = 1
UNION
SELECT col FROM mytable
WHERE col =2;

子查询/内查询（主查询/外查询）

# 分类
按子查询出现的位置
1、出现在select后面：子查询的结果为单行单列（标量子查询=单行 子查询）
2、出现在from后面：子查询的结果可以为多行多列，必须起别名
3、出现在where或者having后面：子查询结果为单列（单行子查询、多行子查询）
4、出现在exists后面：结果必须为单列（相关子查询）。exists返回有没有1或0。
# 特点：
1、子查询一般放到条件的右侧
2、子查询一般放到小括号里
3、子查询执行优先于主查询 
4、单行子查询对应单行操作符 >, <, >=, <> 等
	多行子查询对应多行操作符 in, any/some(>, >= 满足一个就可以), all(>, >= 满足所有)

# 子查询中只能返回一个字段的数据。
SELECT * FROM mytable1
WHERE col1 IN (SELECT col2 FROM mytable2);

# 检索客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次：
SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

连接查询、多表查询

• 求交集、并集等。不需要创建外键。
• 用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。
• 连接可以替换子查询，并且比子查询的效率一般会更快。
• 可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

内连接：inner join 或者 join。把两个表 通过某个字段相等 关联到一起。

select 查询列表
from 表名1 别名 
inner join 表名2 别名 on 连接条件
inner join 表名3 别名 on 连接条件
where 筛选条件 
group by 分组条件
having 分组后筛选
order by 排序列表；
将 连接条件 和 筛选条件 分开。

等值连接：连接条件为a.xx=b.xx;
非等值连接：连接条件类似 a.xx between b.xx and b.yy;

# 等效的普通查询
SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;
# 内连接
select * from tablea inner join tableb on tablea.id = tableb.id

自连接：inner join。可以看成内连接的一种，只是连接的表是自身而已。

# 一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。
# 子查询
SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");
      
# 自连接：查询员工部门和jim相同的员工名字
SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

自然连接：natural join。把同名列通过等值测试连接起来的，同名列可以有多个。

# 内连接提供连接的列，而自然连接自动连接所有同名列
SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

外连接：

• 左连接：left join 或者 left outer join。左边都会取出来，右边没有为NULL

• 左链接：right join 或者 right outer join。右边都会取出来，左边没有为NULL

• 完全外连接：full join 或者 full outer join。mysql不支持full join，可以把左右连接用union连接起来。

# 主表 和 从表。 
select * from 个人 left join 卡包 on 个人.卡id = 卡包.id
select * from 个人 right join 卡包 on 个人.卡id = 卡包.id

select * from 个人 left join 卡包 on 个人.卡id = 卡包.id
union
select * from 个人 right join 卡包 on 个人.卡id = 卡包.id

交叉连接：返回a和b表一一对应的所有数据

# 得到笛卡尔乘积
SELECT A.value, B.value
FROM tablea AS A cross JOIN tableb AS B;

字符集

基本术语：

• 字符集为字母和符号的集合；
• 编码为某个字符集成员的内部表示；
• 校对字符指定如何比较，主要用于排序和分组。

# 除了给表指定字符集和校对外，也可以给列指定：
CREATE TABLE mytable
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

# 可以在排序、分组时指定校对：
SELECT * FROM mytable
ORDER BY col COLLATE latin1_general_ci;

视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通表的操作一样。

视图具有如下好处：

• 简化复杂的 SQL 操作，比如复杂的连接；
• 只使用实际表的一部分数据；
• 通过只给用户访问视图的权限，保证数据的安全性；
• 更改数据格式和表示。

不能更新的视图：

• 包括：分组函数、group by、distinct、having、union
• 常量视图
• select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用from子句中的表

1、创建
CREATE VIEW 视图名 AS
SELECT 需要封装的虚拟表;

2、使用
select * from 视图名 where..

3、修改
create or replace view 视图名 as
select ...

alter view 视图名 as
select ...

4、删除
drop view 视图名1,视图名2...

5、查看
desc view 视图名1,视图名2...
show create view 视图名

变量

系统变量：系统提供的，服务器层面的。

• 全局变量：服务器每次启动会为所有全局变量赋初值，针对所有会话（连接）有效，不能跨重启。跨重启要修改配置文件。

• 会话变量：仅仅针对于会话（连接）有效。

• 全局变量语法：

  • 查看：show global variables
  • 部分查看：show global variables like ‘%char%’
  • 查看具体值：select @@global.系统变量名
  • 修改：set global 变量名 = 值;
  • 修改：set @@global.变量名 = 值;

• 会话变量语法：把global改成session或者删掉。

自定义变量：

• 用户变量：仅仅针对于会话（连接）有效。不限定类型。
• 局部变量：仅仅在begin end块中有效。必须放在第一句话。限定类型。
• 用户变量语法：
  • 声明：set @用户变量名 = 值;
  • 声明：set @变量名 := 值;
  • 声明：select @变量名 := 值; # 只能用:=，用于区分比较运算符
  • 赋值：select 字段 into @变量名 from 表; # 字段的值必须是一个值
  • 查看：select @变量名;
• 局部变量语法：
  • 声明：declare 局部变量名 类型;
  • 声明：declare 变量名 类型 default 值;
  • 赋值：set 变量名 = 值;
  • 赋值：set 变量名 := 值;
  • 赋值：select @变量名 := 值;
  • 赋值：select 字段 into 变量名 from 表 where…;
  • 查看：select 变量名;

存储过程

对一系列 SQL 操作的批处理（类似sql代码？）。作用：

• 代码封装，保证了一定的安全性；
• 代码复用；
• 由于是预先编译，因此具有很高的性能。减少了和数据库的连接次数。

/*
1、命令行中创建存储过程需要自定义分隔符。因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。
2、包含in、out 和 inout 三种参数。
3、给变量赋值都需要用 select into 语句。
4、每次只能给一个变量赋值，不支持集合的操作。
*/
1、定义：
create procedure 存储过程名( 参数模式 参数名 参数类型 )
    begin
    	存储过程体（sql语句）
    end
2、调用：
call 存储过程名（实参列表）
3、参数模式
	in：该参数需要传入值，默认，可以省略。
	out：该参数可以返回值
	inout：该参数需要传入值，也可以返回值
4、删除： drop procedure 存储过程名；  # 只能删一个。
5、查看： show create procedure 存储过程名； # 不能用 desc
6、如果存储过程体只有一句话， begin end可以省略
7、存储过程的结尾用 delimiter 设置结束标记。

## 定义：
delimiter //
create procedure myprocedure( out ret int )
    begin
        declare y int;
        select sum(col1)
        from mytable
        into y;
        select y*y into ret;
    end //
delimiter ; # 将结束符号换回 ；

## 调用：
set @ret； # 定义变量， 也可以不定义。如果是out或者inout，就要先定义。
call myprocedure(@ret);
select @ret;

函数

基本和存储过程一样。但是函数 有且仅有 一个返回。

• 存储过程：适合做批量插入、更新
• 函数：处理数据并返回一个结果

# 函数必须返回一个值，不然会报错。

1、定义：
create function 函数名（参数名 参数类型 ) returns 返回类型
    begin
    	（ declare 局部变量 int default 0；）
    	函数体（sql语句）；
    	return 值；
    end
2、调用：
select 函数名（参数列表）；  # 执行sql语句，并显示返回值
3、删除： drop function 函数名；  # 只能删一个。
4、查看： show create function 函数名； # 不能用 desc
6、如果存储过程体只有一句话， begin end可以省略

流程控制

• 顺序结构：程序冲上往下执行
• 分支结构：程序从多条汇总选择一条执行
  • if 函数：if（表达式1，表达式2，表达式3）。如果1成立，返回2，否则返回3。
  • case结构：类似switch，实现等值判断。多重if，实现区间判断。
  • if结构：实现多重分支。if 条件 then 语句1；。。。 elseif 条件 then 语句2；（else 语句 n；） end if； # 只能用于 begin end 中。
• 循环结构：程序满足一定条件基础上，重复执行。
  • iterate：类似continue，结束本次训练，继续下一次。
  • leave： 类似break，跳出循环。

循环：
1、 while循环：先判断再执行。
（标签：） while 循环条件 do
		循环体；
end while （标签）；
2、 loop循环：模拟简单死循环，搭配 leave跳出。
（标签：） loop
		循环体；
end loop （标签）；
3、 repeat循环：至少执行一次。 do while。
（标签：） repeat
		循环体；
until 结束循环条件
end repeat（标签）；

游标

在存储过程中，使用游标可以对一个结果集进行移动遍历。

游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

/*
使用游标的四个步骤：
1、声明游标，这个过程没有实际检索出数据；
2、打开游标；
3、取出数据；
4、关闭游标；
*/

delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;

        declare mycursor cursor for
        select col1 from mytable;
        # 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1
        declare continue handler for sqlstate '02000' set done = 1;

        open mycursor;

        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;

        close mycursor;
    end //
 delimiter ;

触发器

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

INSERT 触发器包含一个名为 NEW 的虚拟表。

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- 获取结果

数据类型

数值：

• 整型：tinyint 1、smallint 2、mediumint 3、int(integer) 4、bigint 8字节
  • 无符号，在后面加 unsigned。
  • int(5)，数字5表示显示位数，0填充，要加 zerofill。会用0填充，自动变无符号。
• 小数：
  • 定点数：dec(M, D) 相同 decimal(M, D)，M+2字节。最大值和double相同，取值在M、D之间
  • 浮点数：float(M, D) 4、double(M, D) 8。
    • D：小数位数，M：小数和整数位数和
    • dec的精度可以更好，默认精度为 (10, 0)。

字符型：

• 短文本：char(M) 0-255、varchar(M) 0-65535。
  • M表示最大字符数，汉字是1字符。char的M默认为1，varchar的M不可省略。
  • char固定M字符、varchar可变字符，但效率较低。对于固定字符数的用char
• 长文本：text、blob(较大的二进制)
• 二进制字符串：binary、varbinary。

日期型：

• date，4字节，1000-1-1 到 9999-12-31
• datetime，8字节，1000-1-1 00:00:00 到 9999-12-31 23:59:59
• timestamp，4字节，1970 01 01 08 00 01 到2038的某个时刻。用的多，会随着实际的时区改变。
  • 显示时区：show variables like ‘time_zone’;
  • 修改时区：set time_zone=’+9:00’; # 东九区
• time，3字节，-838:59:59 到 838:59:59
• year，1字节，1901 到 2155

位类型：bit(M)，1-8比特。

枚举类型：enum(成员1, …)。

• 插入值必须属于列表中给定值。但是只能插入其中一个。

• 成员是 1-255 需要1字节。255-65535 需要2字节。最多65535个成员。

• 对于字符，大小写不报错，会插入列表中的字符。

集合类型：set(成员1, …)。

• 插入值必须属于列表中给定值。一次可以选取多个成员。

• 可以存储0-64个成员。成员数：1-8需要1字节，9-16需要2字节，17-24需要3字节，25-32需要4字节，33-64需要8字节.

常见约束

六个约束：

• not null：非空，字段不能为空。
• defunct：默认，字段拥有默认值。
• primary key：主键，字段唯一且非空。
• unique：唯一，字段唯一，可为空，但只允许一个null。
• foreign key：外键，从表的关联列字段必须来自主表的关联列的值。在从表添加，引用主表的值。
• check：检查，mysql不支持，不报错。比如性别限定为男女，性别限定为18-60等。

外键问题：

• 从表关联列的类型和主表的关联列的类型要求一致或者兼容
• 主表关联列必须是key，一般是主键或者唯一键
• 级联删除，在后面添加 on delete cascade；
• 级联置空，在后面添加 on delete set null；
  • 插入数据时，先插入主表，后插入从表。
  • 删除数据时，先删除从表，后删除主表。

一般不用外键：

• 当数据比较多时，插入数据时，需要查询主表数据，效率低
• 数据表迁移时，如果很多表之前存在外键，表的先后顺序会很麻烦。、
• 删除数据麻烦。

分类：

• 列级约束：都可以写，外键约束没效果
• 表级约束：不支持非空、默认

create table 表名（
	字段名 字段类型 列级约束，
	...
	表级约束
）;inser
# 外键约束只能用表级约束，其他可用列级约束。
# 主键和唯一都可以组合。不建议组合主键。

1、列级约束
create table students(
	id int primary key, 			# 主键
	name varchar(20) not null,		# 非空
	seat int unique, 				# 唯一
	age int default 18,				# 默认
	majorId int references major(id),		# 外键
	gender cahr(1) check(gender in ('mail', 'femail'))		# 检查，mysql不支持
);

desc students;  				# 非空，主键，默认。
显示：列名，数据类型，为空，主键，默认，extra
show index from students;  		# 主键、外键、唯一键会自动生成索引
显示索引：索引所在表，不唯一，索引名，序列值，列名...

2、表级约束
create table students(
	id int,
	name varchar(20) not null,		# 非空
	seat int,
	age int default 18,				# 默认
	majorId int,
	gender cahr(1),
    constraint pk primary key(id),	# 主键
    constraint uk unique(seat),		# 唯一
    constraint fk_stu_major foreign key(majorId) references major(id),	# 外键
    constraint ck check(gender in ('mail', 'femail'))  # mysql不支持
);
# pk、uk等是键名，可以省略 constraint pk

3、修改约束：
添加约束：
	列级约束：alter table 表名 modify 列名 数据类型 (新约束)；
	表级约束：alter table 表名 add 约束(列名)；
	表级约束：alter table 表名 add constraint 键名 约束(列名)；
删除约束：
	列级约束：alter table 表名 modify 列名 数据类型；
	列级约束：alter table 表名 drop 主键|唯一键|外键 (唯一键名|外键名);
	
添加约束
	非空：alter table 表名 modify 字段名 数据类型 not null;
	默认：alter table 表名 modify 字段名 数据类型 default 值;
	主键：alter table 表名 add primary key(字段名);
	唯一：alter table 表名 add [constraint 键名] unique 字段名;
	外键：alter table 表名 add [constraint 键名] foreign key(字段名) references 主表 (被引用列);
删除约束
	非空：alter table 表名 modify 字段名 数据类型;
	默认：alter table 表名 modify 字段名 数据类型;
	主键：alter table 表名 drop primary key;
	唯一：alter table 表名 drop index 键名;
	外键：alter table 表名 drop foreign key 键名;
    
4、自增长列、标识列 auto_increment
# 标识列必须是一个key
# 标识列只能有一个
# 标识列只能是数值型
参数： show variables like '%auto_increment%';
	auto_increment_increment = 1 	# 步长
	auto_increment_offest = 1		# 起始值，mysql该变量没用
set auto_increment_increment = 3;   # 一般不改
创建：
create table students(
	id int primary key auto_increment,
	name varchar(20) not null
);
调用：
insert into students values(null, 'lucy');
insert into students() values(null, 'lucy');
修改：
alter table 表名 modify 字段名 类型 约束 auto_increment;
删除：
alter table 表名 modify 字段名 类型 约束;

• 主键约束：不重复且不为空NULL。能唯一确定一张表的一条记录。

  联合主键：加起来不重复就可以。但是不能为空。
  增加主键约束：alter table tableName add primary key(Name);
  删除主键约束：alter table tableName drop key;
  修改字段添加约束：alter table tableName modify Name int primary key;

• 自增约束：auto_increment，可以自动增长。

  插入数据：insert into tableName (Name) values(’**’)

  其中id自动赋值为1，在此插入，id赋值为2.

• 唯一约束：约束修饰的字段不可以重复，也可以直接写到字段后面。

  多个的话组合在一起不重复就行。

  唯一约束可以为空，可以同时加在多个字段之后

  增加唯一约束： alter table tableName add unique(Name);

  删除唯一约束：alter table tableName drop index Name;

  修改字段添加约束: alter table tableName modify Name int unique;

• 非空约束：修饰的字段不能为空。字段后面加 not null

• 默认约束：设置默认值。字段后面加 default Value

• 外键约束：主表/副表，父表/子表。

子表不能添加绑定父表的字段没有的值：比如班级的 id 只有123，那么学生表中不允许设置 class_id 为4。

父表的记录被子表引用，是不可以删除的。

练习

• 查询所有：select * from tableName;
• 查询部分：select Name1, Name2 from tableName;
• 查询合并：select concat（姓， ’ ‘，名） as 姓名 from tableName；
• 查询不重复：select distinct Name from tableName；-- Union也行？
• 查询是否为空：select 名字 from tableName is null； – is只判断null， =值判断非null， 安全等于符 <=>都可以
• 查询是否不为空：select 名字 from tableName is not null；
• 查询区间1：select * from 成绩单 where 分数 between 60 and 80；
• 查询区间2：select * from 成绩单 where 分数 > 60 and 分数 < 80；
• 查询或者关系：select * from 成绩单 where 分数 in (90, 91, 92);
• 查询不同列或者关系：select * from 学生表 where 班级=‘1’ or 性别=‘mail’；
• 查询降序：select * from 成绩单 order by 分数 desc；
• 查询升序：select * from 成绩单 order by 分数 asc；– ase可以不要，默认升序。
• 查询多个升降序：select * from 成绩单 order by 分数 asc, 学号 dese；
• 查询统计：select count(*) from 学生表 where 班级=‘1’；
• 查询最值：select max(分数) from 成绩单；
• 查询最值的列数据，子查询/复合 查询：select 学号, 课程号 from 成绩单 where 分数=(select max(分数) from 成绩单)；
• 查询最值的列数据，排序解：select 学号，课程 from 成绩单 order by 分数 desc limit 0, 1； – limit表示从0开始，共1个数据。有缺陷，比如最值有两个，有一个就没有取出来。
• 查询平均值单个：select avg(分数) from 成绩单 where 课程=‘英语’；
• 查询平均值总：select 课程, avg(分数) from 成绩单 group by 课程；– 分组 group by
• 查询人数大于2的课程：select 课程 from 成绩单 group by 课程 having count(课程)>2；– where过滤行，having过滤分组
• 查询人数大于2的课程：select 课程, count(*) from 成绩单 group by 课程 having count(课程)>2；– 显示count的人数
• 查询以3开头的课程：select 课程 from 成绩单 group by 课程 having count(课程)>2 and 课程 like ‘3%’； – like模糊查询
• 查询多表的列：select 名字, 学号, 分数 from 学生表, 成绩单 where 学生表.学号=成绩单.学号；
• 查询三表关联：select 名字，课程，分数 from 学生表，课程表，成绩表 where 成绩表.学号=学生表.学号 and 成绩表.课程号=课程表.课程号；
• 查询显示别名：select max(分数) as 最高分 from 成绩单；
• 查询1班每门课的平均分：select 课程号，avg（分数） from 成绩表 where 学号 in （select 学号 from 学生表 where 班级=‘1’） group by 课程号；
• 查询年份：select year（出生日期） from 学生表；
• 查询合并：select 。。。。union select。。。；
• 选择a课程的成绩至少大于b课程的学生的信息：select * from score where 课程=‘a’ and degree>any（select degree from score where 课程=‘b’） order by degree desc；
• 选择a课程的且成绩大于b课程的学生的信息： any改为all
• 查询成绩比平均成绩低的成绩表：select * from 成绩表 a where degree<（select avg（成绩） from 成绩表 b where a.课程号=b.课程号 ） order by 课程号； – 复制表数据作为条件查询
• 查询学生年龄：select 名字，year(now())-year(生日) as 年龄 from student；
• 查询学生的等级：select 名字，等级 from 成绩表，等级表 where 成绩 between 最低 and 最高；

leecode

# 查询第二高薪水： 注意如果表里面可能没有数据。
作为临时表：
SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary;
使用 “IFNULL” 函数
SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary


# 查询分数排名 
Scores表有 id 和 Score，返回 Score 和 Rank。同分下排名一样。
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
分数排序
select Score from Scores order by Score desc;
求排名：
select a.Score as Score, 
(select count(distinct b.Score) from Scores b where b.Score>=a.Score) as `Rank` 
from Scores a 
order by Score desc;
专用窗口函数：
select score, 
dense_rank() over(order by Score desc) as 'Rank'
from Scores;