mysql基础

原创已于 2023-05-23 10:05:24 修改 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2018-04-28 17:31:19 首次发布

MySQL 专栏收录该内容

3 篇文章

订阅专栏

一、安装

安装MySQL

执行mysql语句报错：'mysql'不是内部或外部命令，也不是可运行的程序或批处理文件。
原因：
执行以上 mysql命令时，实际为系统调用了 mysql.exe 文件。该文件在mysql安装目录下的 /bin。
而系统是通过环境变量指定的目录或当前目录查找。
所以把bin目录添加到环境变量或直接进入bin目录执行语句即可。

通过命令 mysql -uroot -p 连接成功后的窗口与mysql服务器的关系为：
客户端 client 和服务器 server 的关系。
这里写图片描述

其他的mysql客户端：Navicate，phpMyAdmini，mysqlfront等

二、基本语句

操作MySQL基本语句

连接MySQL服务器：mysql -uusername -ppassword
操作库：
- 查看数据库：show databases
- 创建数据库：create database dbname charset utf8
- 选择数据库：use dbname
- 删除数据库：drop database 数据库名
- 没有修改数据库名的语句

操作表：

查看库中的表：show tables

创建表：

create table tbname(
	`id` int(11) UNSIGNED AUTO_INCREMENT,
	`name` varchar(10) NOT NULL DEFAULT '' COMMENT '用户名',
	PRIMARY KEY (`id`)
)engine innodb charset utf8;

删除表：drop table tbname
修改表名：rename talbe tbname
查看表结构详情：desc tbname

操作表数据：
- 插入数据：INSERT INTO tbname(field1,field2...) VALUES(val1,val2...),(val11,val22...)...
  如果不指定字段名，则默认根据字段顺序依次插入数据，注意列值要对应
- 删除数据：DELETE FROM tbname WHERE expr
- 清空数据（删除所有数据）：truncate tbname
- truncate - 删表重建；delete - 删表里的行；如果删除表里所有数据，truncate更快
- 更新数据：UPDATE tbname SET field1=new_val1,field2=new_val2... WHERE expr

编码：客户端使用的是GBK编码，服务器使用UTF-8。编码设置一致即可

这里写图片描述

三、字符类型

mysql三大列类型：

数值型：
- 整型：
  tinyint，smallint，mediumint，int，bigint
- 浮点型：
  float(M，D)，decimal(M，D)
字符串型
char(M)，varchar(M)，text 文本类型
日期时间类型
date 日期，time() 时间，datetime 日期时间类型，year 年类型

M - 表示最大显示宽度。最大有效显示宽度是255。
D - 适用于浮点和定点类型，并表示小数点后面的位数。最大可能的值是30，但不应大于M-2。

整型类的存储范围与所占字节

类型	存储（字节）	最小值签名	最小值无符号	最大值签署	最大值无符号
`TINYINT`	1	`-128`	`0`	`127`	`255`
`SMALLINT`	2	`-32768`	`0`	`32767`	`65535`
`MEDIUMINT`	3	`-8388608`	`0`	`8388607`	`16777215`
`INT`	4	`-2147483648`	`0`	`2147483647`	`4294967295`
`BIGINT`	8	`-2^63`	`0`	`2^63-1`	`2^64-1`

TINYINT 1字节 8位
无符号：0 ~ 2^8-1，0 ~ 255
有符号：-2^7 ~ 2^7-1，-128 ~ 127

SMALLINT 2字节 16位
无符号：0 ~ 2^16-1，0 ~ 65535
有符号：-2^15 = ~ 2^15-1，-32768 ~ 35767

INT 4字节 32位
无符号：0 ~ 2^32-1，0 ~ 4294967295
有符号：-2^31 ~ 2^31-1
一般而言，设某类型占N字节，则有8N位

无符号：0 ~ 2^8N-1

有符号：-2^（8N-1） ~ 2^（8N-1）-1

对于int型，所占字节越多，存储范围越大

整型默认为有符号型

整型的参数（M），unsigned，zerofill
zerofill表示0填充，M即为 tinyint( M )，M表示0填充的位数，二者配合使用才有意义
如果设置了ZEROFILL扩展属性，默认就有了无符号属性(UNSIGNED)

INT(M) ZEROFILL，加上ZEROFILL后M才表现出不同
比如 INT(3) ZEROFILL,插入到数据库里的是10,则实际插入为010,也就是在前面补充加了一个0.
如果INT(3)和INT(10)不加ZEROFILL,则它们没有什么区别．
Ｍ不是用来限制INT列内保存值的范围的．
int(M)的最大值和最小值与UNSIGNED有关。

mysql> alter table tbname add zfill tinyint(5) zerofill not null default 0;

浮点型

	float(M，D)
	decimal(M，D)

M - 精度，总位数（包含小数位）
D - 标度，小数位（小数点右边的位数）

float 存储范围：整数位10^{38，小数位10}-38
如何M <= 24 ,占4个字节，否者占8个字节。

用来表示数据中的小数，除了float 浮点
还有，decimal 定点，把整数部分和小数部分分开存储
decimal 比 float 精确，float 有时会损失精度

alter table tbname add isFloat float(5,2) unsigned not null default '0.00'
alter table tbname add isDecimal decimal(5,2) unsigned not null default '0.00'

字符型

char()，varchar()，text，blob

char(M)

定长
存储时：不论数据够不够宽度，实际都在占M个宽度。不够在末尾用空格补齐（浪费空间）
取出时：统一去掉数据末尾的空格（意味着右侧本身有空格会丢失）
查找行记录时，如果所有列的长度固定，定长比变长快
char(M)，M代表宽度，0 <= M <= 255。例：char(10)，则能输入10个字符

varchar(M)

变长
存储时：根据数据的实际长度给定空间（在数据内容前，有1-2个字符占据存储空间，用来标志该数据内容的长度）。
例：varchar(5)，输入的字符小于5格字符。一个字符也不存储时，实际占的空间不为0，因为还有数据头的前缀码所占空间。
取出时：会保留本身存储时的空格
varchar(M)，M代表宽度，0 <= M <= 6553（以ASCII字符为例，utf8为22000左右）

varchar() 标志数据内容长度的前缀码
这里写图片描述

char()和varchar()空间利用率对比
这里写图片描述

char()和varchar()存储空格，取出数据时比较

mysql> create table `char`(
    -> `id` int(11) unsigned not null auto_increment,
    -> `char` char(5) not null default '',
    -> `varchar` varchar(5) not null default '',
    -> PRIMARY KEY (`id`)
    -> )engine myisam charset utf8;
    
mysql> insert into `char`(`char`,`varchar`) values
    -> ('hello','hello'),('aa ','aa ');
    
mysql> select concat(`char`,'!'),concat(`varchar`,'!') from `char`;
+--------------------+-----------------------+
| concat(`char`,'!') | concat(`varchar`,'!') |
+--------------------+-----------------------+
| hello!             | hello!                |
| aa!                | aa !                  |
+--------------------+-----------------------+

char(M)和varchar(M)中的 M 代表字符数，不是字节数

mysql> insert into `char`(`char`,`varchar`) 
	 > values('五个字符数','三个字'),('六个字符数字','中华人民共和
国')，('123456','abcdef');
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from `char`;
+----+------------+------------+
| id | char       | varchar    |
+----+------------+------------+
|  3 | hello      | hello      |
|  4 | aa         | aa         |
|  5 | 五个字符数   | 三个字      |
|  6 | 六个字符数   | 中华人民共   |
|  7 | 12345      | abcde      |
+----+------------+------------+
4 rows in set (0.00 sec)

TEXT 和 BLOB

两者都不需要添加默认值
TEXT 用于存储新闻等信息
BLOB是一个二进制大对象，可以容纳可变数量的数据。用于存储图像音频等二进制信息。意义：防止字符集问题导致信息丢失。

日期时间
这里写图片描述

mysql> #日期 格式：YYYY-mm-dd
mysql>  create table test_date(
    ->  star varchar(20) not null default '',
    ->  birth date not null default '0000-00-00'
    ->  )engine myisam charset utf8;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from test_date;
+------+------------+
| star | birth      |
+------+------------+
| 日期     | 1994-12-07 |
+------+------------+
1 row in set (0.00 sec)

mysql> #时间 格式：HH:ii:ss
mysql> alter table test_date add sign time not null;
mysql> alter table test_date change sign sign time not null default '00:00:00';
mysql> insert into test_date(star,sign) values('时间','11:27:01');
mysql> select * from test_date;
+------+------------+----------+
| star | birth      | sign     |
+------+------------+----------+
| 日期     | 1994-12-07 | 00:00:00 |
| 时间    | 0000-00-00 | 11:27:01 |
+------+------------+----------+

mysql> #日期时间 格式：YYYY-mm-dd HH:ii:ss
mysql> create table date_time(
    -> sname varchar(20) not null default '',
    -> logintime datetime not null default '0000-00-00 00:00:00'
    -> )engine myisam charset utf8;
mysql> insert into date_time values('日期时间','1994-12-07 11:39:21');
mysql> select * from date_time;
+----------+---------------------+
| sname    | logintime           |
+----------+---------------------+
| 日期时间        | 1994-12-07 11:39:21 |
+----------+---------------------+

mysql>#timestamp 格式：与datetime一样。 能够自动存储日期时间
mysql> create table tsmp(
    -> ts timestamp default CURRENT_TIMESTAMP,
    -> id int
    -> )engine myisam charset utf8;
mysql> insert into tsmp(id) values(1),(2),(3);
mysql> select * from tsmp;
+---------------------+------+
| ts                  | id   |
+---------------------+------+
| 2018-05-04 11:47:17 |    1 |
| 2018-05-04 11:47:17 |    2 |
| 2018-05-04 11:47:17 |    3 |
+---------------------+------+

mysql>#yeay 格式：YYYY 范围：1901~2155
mysql> create table yea(
    -> yea yeat not null default '0000',
    -> id int
    -> )engine myisam charset utf8;
mysql> insert into tyear values
	 > ('最小年份',1901),('最大年份',2155),('小于1901',1900),('大于2155',2156),('存储为0000',0000);

mysql> select * from tyear;
+----------+-------+
| uname    | years |
+----------+-------+
| 最小年份        |  1901 |
| 最大年份         |  2155 |
| 小于1901    |  0000 |
| 大于2155     |  0000 |
| 存储为0000     |  0000 |
+----------+-------+
mysql> #year还可简写为两位
mysql> alter table tyear add yeartwo year(2) not null default '00';
mysql> insert into tyear(yeartwo) values(95),(18);
mysql> select *  from tyear;
+----------+-------+---------+
| uname    | years | yeartwo |
+----------+-------+---------+
| 最小年份        |  1901 |      00 |
| 最大年份         |  2155 |      00 |
| 小于1901    |  0000 |      00 |
| 大于2155     |  0000 |      00 |
| 存储为0000     |  0000 |  00 |
|          |  0000 |      95 |
|          |  0000 |      18 |
+----------+-------+---------+
#简写为两位时：
[00-69]+2000
[70-99]+1900
如上例：95表示1995，18表示2018

显示警告 show warnings

Query OK, 2 rows affected, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1264 | Out of range value for column 'years' at row 1 |
| Warning | 1264 | Out of range value for column 'years' at row 2 |
+---------+------+------------------------------------------------+
2 rows in set (0.00 sec)

tee 命令将cmd操作内容存储至指定位置，且可指定为任意文件

mysql> tee d:/180504.sql
Logging to file 'd:/180504.sql'

四、建表

建表
这里写图片描述

优化表

优化原理：时间换空间，空间换时间。

整张表都是定长能够提高查询效率
使用频率高的优先考虑效率，存储到主表；不常用的和比较占据空间的优先考虑空间占用，存储到辅表

	-- 主表
	CREATE TABLE `member` (
	  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	  `username` char(20) NOT NULL DEFAULT '' COMMENT '用户名',
	  `gender` char(1) NOT NULL DEFAULT '' COMMENT '性别',
	  `weight` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '体重',
	  `birth` date NOT NULL DEFAULT '0000-00-00' COMMENT '生日',
	  `salary` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '工资',
	  `lastlogin` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '上次登录时间',
	  PRIMARY KEY (`id`)
	) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='会员注册信息表';
	
	-- 辅表
	CREATE TABLE `member_other` (
	  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	  `intro` varchar(1500) NOT NULL DEFAULT '' COMMENT '个人简介',
	  `member_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '会员表id',
	  PRIMARY KEY (`id`)
	) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='会员注册信息附表';

--将dbname数据库的tbname 表的colname列的数据，导入到dbname1数据库的tbname1表的colname列中

insert into dbname.tbname select colname from dbname1.tbname1

五、select的查询模型

查询模型

列就是变量，在每一行上，列的值都在变化
where条件是表达式，在哪一行表达式为真，哪一行就取出来
查询结果集可以当成表看

1、熟练运算符in和not的用法
运算符 | 说明
--------|-------
in | 某个集合内
between and | 某个范围内
not 或！ | 逻辑非

like里的_ 匹配单一字符

2、where的注意事项

where 1; --all 
where 0; --empty

比如
select * from tbname where 1>2; -- empty

3、广义投影

mysql> #取出商品价比市场价省的钱
mysql> select goods_id,goods_name,(market_price-shop_price) as discount from goods;

#这一列即是运算结果，术语为“广义投影”
#即把列看成变量，可以运算

#取出商品价比市场价省的钱,并且大于200
mysql> select goods_id,goods_name,(market_price-shop_price) as discount from goods where (market_price-shop_price) >200;
而不能这样 where discount >200，因为 where 字句只针对原表中的数据，而discount是已经查询出的结果，用`having`

这里写图片描述

4、面试题

把数字[20,29]改为20，[30,39]改为30


mysql> select * from mst;
+------+
| num  |
+------+
|    1 |
|   13 |
|   25 |
|   27 |
|   29 |
|   32 |
|   35 |
|   35 |
|   45 |
|   46 |
+------+
10 rows in set (0.00 sec)

mysql> update mst set num=floor(num/10)*10 where num between 20 and 39;

把有前缀“诺基亚”的商品名改为“HTC”

mysql> select goods_id,goods_name,concat('HTC',substring(goods_name,4)) from goo
ds where goods_name like "诺基亚%";
+----------+---------------------+---------------------------------------+
| goods_id | goods_name          | concat('HTC',substring(goods_name,4)) |
+----------+---------------------+---------------------------------------+
|        4 | 诺基亚N85原装充电器              | HTCN85原装充电器
              |
|        9 | 诺基亚E66                | HTCE66                                |
|       14 | 诺基亚5800XM             | HTC5800XM                             |
|       32 | 诺基亚N85                | HTCN85                                |
+----------+---------------------+---------------------------------------+

COUNT(expr)函数：

COUNT(colname) 返回SELECT语句检索到的行中非NULL值的数目。若找不到匹配的行，则COUNT() 返回 0 。
COUNT(*) 返回检索行的包含 NULL值的数目


mysql> SELECT * from test_count;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 | NULL |
| NULL | NULL |
+------+------+

mysql> select count(id) from test_count;
+-----------+
| count(id) |
+-----------+
|         2 |
+-----------+

mysql> select count(num) from test_count;
+------------+
| count(num) |
+------------+
|          1 |
+------------+

mysql> select count(*) from test_count;
+----------+
| count(*) |
+----------+
|        3 |
+----------+

mysql> select count(1) from test_count;
+----------+
| count(1) |
+----------+
|        3 |
+----------+

count(*)和count(1)区别

在MyISAM中，无区别。因为引擎内有个计数器在维护着行数，行数可直接统计出来
在innoDB中，用count(*)直接读取行数，效率很低，因为引擎会一行一行数一遍

GROUP

取数据第一次出现的行

#分类统计商品数量
mysql> select cat_id,sum(goods_number) from goods group by cat_id;
+--------+-------------------+
| cat_id | sum(goods_number) |
+--------+-------------------+
|      3 |                 6 |
|      4 |                 2 |
|      6 |                 3 |
|      8 |                19 |
|     16 |                 6 |
|     18 |                 1 |
|     19 |                10 |
|     20 |                 1 |
|     22 |                 1 |
|     24 |                64 |
|     25 |               998 |
+--------+-------------------+

分析以下sql

mysql> select goods_id,sum(goods_number) from goods;
+----------+-------------------+
| goods_id | sum(goods_number) |
+----------+-------------------+
|        1 |              1111 |
+----------+-------------------+

该语句可以执行，并且把goods_id第一次出现的值取出来了。
对于sql标准来说，该语句是错误的，不能执行的，因为语意有问题。
但是mysql可以，因为这是它的一个特点，出于可移植性和规范性，不推荐这么写

严格来说，group by a,b,c 为列，则select的列只能在a，b，c 里选择，才没有语意矛盾

HAVING

表的数据
where
查询出满足条件的行
计算函数，group
结果集（广义投影）
having
最终结果集

where 针对表，having针对结果集

这里写图片描述

#查询本店价与市场价省的钱，并且省钱大于200
mysql> select goods_id,goods_name,(market_price-shop_price) as discount from goo
ds
    -> where 1
    -> having discount > 200;
+----------+--------------+----------+
| goods_id | goods_name   | discount |
+----------+--------------+----------+
|        1 | KD876        |   277.60 |
|        9 | 诺基亚E66         |   459.60 |
|       69 | 平衡车           |   399.79 |
|       14 | 诺基亚5800XM      |   525.00 |
|       32 | 诺基亚N85         |   602.00 |
+----------+--------------+----------+

#查询积压货款超过1w的栏目及其积压的货款
mysql> select cat_id,sum(goods_number*shop_price) as m from goods
    -> group by cat_id
    -> having m > 10000;
+--------+-----------+
| cat_id | m         |
+--------+-----------+
|      3 |  16636.00 |
|     25 | 996004.00 |
+--------+-----------+

练习题

//where-group-having
//查询挂科两门及两门以上的学员平均分
//(题目解释：学员全部科目的平均分，并且为挂机两门以上（包括两门）的科目)
 +------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三     | 数学       |    90 |
| 张三     | 语文        |    50 |
| 张三     | 地理        |    40 |
| 李四     | 语文        |    55 |
| 李四     | 政治        |    45 |
| 王五     | 政治        |    30 |
+------+---------+-------+


#求平均分=》筛选挂数
mysql> select name,avg(score),sum(score<60) as gks from result
    -> group by name having gks>=2;
+------+------------+------+
| name | avg(score) | gks  |
+------+------------+------+
| 张三     |    60.0000 |    2 |
| 李四     |    50.0000 |    2 |
+------+------------+------+


#筛挂科数=》求平均分
mysql> select name,avg(score) from result
    -> where name in (select name from (select name,count(1) as gks from result where score<60 group by name having gks>=2) as temp)
    -> GROUP BY name;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三     |    60.0000 |
| 李四     |    50.0000 |
+------+------------+
2 rows in set (0.00 sec)

ORDER

排序对象：结果集
书写语法：order colname1 asc/desc,colname asc/desc…
排序优先级：左高右低

这里写图片描述

LIMIT

书写语法：limit [offset,],N
offset：偏移量。及忽略前面的条目数
N：查询的条目数

mysql> select goods_id,goods_name,cat_id,shop_price from goods
    -> order by cat_id,shop_price desc
    -> limit 2,3;
+----------+--------------+--------+------------+
| goods_id | goods_name   | cat_id | shop_price |
+----------+--------------+--------+------------+
|        8 | 飞利浦9@9v         |      3 |     399.00 |
|       14 | 诺基亚5800XM      |      4 |    2625.00 |
|        1 | KD876        |      4 |    1388.00 |
+----------+--------------+--------+------------+
3 rows in set (0.00 sec)

子查询

where型子查询：把内层查询结果作为外层查询的比较条件
如果 where 列=（内层sql），则内层sql返回的必是单行单列单个值
如果 where 列 in （内层sql），则内层sql只返回单列，可多行
from型子查询：内层sql的查询结果，当成临时表，供外层sql再次查询
exists型子查询：把外层查询结果拿到内层sql测试，如果成立，则取出

子句书写顺序和执行优先级：where => group => having => order => limit

练习题

//取出每个cat_id下最大的goods_id

mysql> select cat_id,goods_id from goods
    -> order by cat_id,goods_id desc;
+--------+----------+
| cat_id | goods_id |
+--------+----------+
|      3 |       32 |
|      3 |        9 |
|      3 |        8 |
|      4 |       14 |
|      4 |        1 |
|      6 |       60 |
|      6 |       59 |
|      6 |       54 |
|      8 |       53 |
|      8 |       52 |
|      8 |        4 |
|     16 |       48 |
|     16 |       47 |
|     16 |       46 |
|     16 |       44 |
|     16 |       43 |
|     16 |       42 |
|     18 |       36 |
|     19 |       51 |
|     19 |       50 |
|     19 |       49 |
|     19 |       45 |
|     19 |       41 |
|     19 |       40 |
|     19 |       39 |
|     19 |       38 |
|     19 |       37 |
|     19 |       35 |
|     20 |       58 |
|     22 |       55 |
|     24 |       72 |
|     24 |       70 |
|     24 |       69 |
|     24 |       68 |
|     24 |       64 |
|     24 |       63 |
|     24 |       62 |
|     24 |       61 |
|     25 |       73 |
+--------+----------+
39 rows in set (0.00 sec)


1.WHERE型

SELECT
	*
FROM
	(
		SELECT
			cat_id,
			goods_id,
			goods_name
		FROM
			goods
		ORDER BY
			cat_id,
			goods_id DESC
	) AS temp
GROUP BY
	cat_id
ORDER BY
	cat_id;


2.FROM型

SELECT
	cat_id,
	goods_id,
	goods_name
FROM
	goods
WHERE
	goods_id IN (
		SELECT
			max(goods_id)
		FROM
			goods
		GROUP BY
			cat_id
	)
ORDER BY
	cat_id;

//exists型
//把goods栏目下有商品的取出来

category表
+--------+----------+
| cat_id | cat_name |
+--------+----------+
|      1 | 手机类型        |
|      3 | 小型手机       |
|      4 | 3G手机      |
|      6 | 手机        |
|      8 | 耳机         |
|      9 | 电池         |
|     12 | 充值卡        |
|     16 | 服装        |
|     18 | 智能硬件        |
|     19 | 配件         |
|     20 | 保护壳         |
|     22 | 移动电源       |
|     24 | 数码时尚        |
+--------+----------+

goods表
+--------+----------+-------------------------------------+
| cat_id | goods_id | goods_name                          |
+--------+----------+-------------------------------------+
|      3 |        8 | 飞利浦9@9v                                |
|      4 |        1 | KD876                               |
|      6 |       54 | 插线板                                   |
|      8 |        4 | 诺基亚N85原装充电器                              
|     16 |       42 | 短袖T恤 米兔大游行                                                    
|     18 |       36 | 路由器                                   |
|     19 |       35 | 体重秤                                   |
|     20 |       58 | 手机3高配版 超薄钢化玻璃膜(0.22mm)                       
|     22 |       55 | 移动电源10000mAh                          |
|     24 |       61 | 视频                                   |
+--------+----------+-------------------------------------+


mysql> select cat_id,cat_name from category
    -> where exists (select * from goods where goods.cat_id=category.cat_id);
+--------+----------+
| cat_id | cat_name |
+--------+----------+
|      3 | 小型手机       |
|      4 | 3G手机      |
|      6 | 手机        |
|      8 | 耳机         |
|     16 | 服装        |
|     18 | 智能硬件        |
|     19 | 配件         |
|     20 | 保护壳         |
|     25 | 数码时尚        |
|     22 | 移动电源       |
|     24 | 数码时尚        |
+--------+----------+
11 rows in set (0.02 sec)

NULL

这里写图片描述


mysql> select * from test2;
+-------+
| sname |
+-------+
| 张三      |
| 李四      |
| NULL  |
+-------+
3 rows in set (0.00 sec)

mysql> select * from test2 where sname is not null;
+-------+
| sname |
+-------+
| 张三      |
| 李四      |
+-------+
2 rows in set (0.02 sec)

mysql> select * from test2 where sname is null;
+-------+
| sname |
+-------+
| NULL  |
+-------+
1 row in set (0.00 sec)

mysql> select * from test2 where sname=null;
Empty set (0.00 sec)

mysql> select * from test2 where sname!=null;
Empty set (0.00 sec)

mysql> select 2>1;
+-----+
| 2>1 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

mysql> select 1>2;
+-----+
| 1>2 |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

mysql> select 2*3;
+-----+
| 2*3 |
+-----+
|   6 |
+-----+
1 row in set (0.00 sec)

mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

mysql> select null!=null;
+------------+
| null!=null |
+------------+
|       NULL |
+------------+

六、联合查询

问题：集合A(1,2,3),集合B(a,b)相乘：A*B=C？
答：C((1,a),(2,a),(3,a),(1,b),(2,b),(3,b))

集合元素的特点：确定性，互异性，无序性

表与集合的关系：一张表就是一个集合，每一行就是一个元素（不考虑列）

疑问：表数据完全相同？答：mysql表内部还有一个rowid（行id）

两表全相乘，笛卡尔积(交叉连接)

从行的角度：每行两两组合
从列的角度：所有列相加（名字可重复）

select * from tbname1,tbname2;

弊端：A表1000行,B表1000行，全相乘会在内存中临时生1000*1000行的大数据，并且未利用索引

LEFT [OUTER] JOIN（左连接）

以左表为基准联合查询右表，
如果右表有满足条件的行（包括多行）则取出，如果右表没有满足条件的行，则用 NULL 填充
（返回左表的行和匹配的右表的行。左表的行在右表没有匹配行时，则返回左表行不变，右表行的值用NULL填充）
返回的行数 >= 左表的行数
查询的结果集排列与全相乘一样（列相加，行两两组合）

select * from tbname1 a left join tbname2 b on a.col=b.col [后面还可使用left join on,where等];

这里写图片描述

mysql> select * from boy;
+-------+------+
| bname | pid  |
+-------+------+
| b1    | 1    |
| b2    | 2    |
| b3    | 3    |
+-------+------+


mysql> select * from girl;
+-------+------+
| gname | pid  |
+-------+------+
| g1    | 1    |
| g2    | 2    |
| g3    | 2    |
| g4    | 9    |
+-------+------+

mysql> select * from boy a left join girl b on a.pid=b.pid;
+-------+------+-------+------+
| bname | pid  | gname | pid  |
+-------+------+-------+------+
| b1    | 1    | g1    | 1    |
| b2    | 2    | g2    | 2    |
| b2    | 2    | g3    | 2    |
| b3    | 3    | NULL  | NULL |
+-------+------+-------+------+

RIGHT [OUTER] JOIN（右连接）

A left join B 等价于 B right join A

注意：左右连接可以互换，出于移植和兼容性的考虑，尽量用左连接

mysql> select * from boy a RIGHT JOIN girl b on a.pid=b.pid;
+-------+------+-------+------+
| bname | pid  | gname | pid  |
+-------+------+-------+------+
| b1    | 1    | g1    | 1    |
| b2    | 2    | g2    | 2    |
| b2    | 2    | g3    | 2    |
| NULL  | NULL | g4    | 9    |
+-------+------+-------+------+

[INNER] JOIN（内连接）

返回左右表匹配的行

mysql> select * from boy a inner join girl b on a.pid=b.pid;
+-------+------+-------+------+
| bname | pid  | gname | pid  |
+-------+------+-------+------+
| b1    | 1    | g1    | 1    |
| b2    | 2    | g2    | 2    |
| b2    | 2    | g3    | 2    |
+-------+------+-------+------+

从集合的角度：内连接是左右连接的交集

在sqlserver中还有外链接，返回左右连接的并集，但在mysql中不支持

练习题

用两次 left join 查询信息

match 表

 主键  | 主队ID |客队ID| 比分 | 比赛时间
+------+------+------+------+------------+
| mid  | hid  | gid  | mres | matime     |
+------+------+------+------+------------+
|    1 |    1 |    2 | 2:0  | 2006-05-21 |
|    2 |    2 |    3 | 1:2  | 2006-06-21 |
|    3 |    3 |    1 | 2:5  | 2006-06-25 |
|    4 |    2 |    1 | 3:2  | 2006-07-21 |
+------+------+------+------+------------+

team 表
 队伍id  队伍名 
+------+----------+
| tid  | tname    |
+------+----------+
|    1 | 国安      |
|    2 | 甲花      |
|    3 | 公益联队  |
+------+----------+

条件：match表的hid和gid都与team表关联

要求：用以下形式查询2006-01-01至2006-07-01的比赛信息
 SKT  3:0  RNG  2006-02-01

mysql> SELECT
    ->  b.tname,
    ->  a.mres,
    ->  c.tname,
    ->  a.matime
    -> FROM
    ->  match AS a
    -> LEFT JOIN team AS b ON a.hid = b.tid
    -> LEFT JOIN team AS c ON a.gid = c.tid
    -> WHERE
    ->  matime BETWEEN '2006-01-01' AND '2006-07-01';
    
+----------+------+----------+------------+
| tname    | mres | tname    | matime     |
+----------+------+----------+------------+
| 国安         | 2:0  | 甲花        | 2006-05-21 |
| 甲花        | 1:2  | 公益联队         | 2006-06-21 |
| 公益联队         | 2:5  | 国安         | 2006-06-25 |
| 甲花        | 3:2  | 国安         | 2006-07-21 |
+----------+------+----------+------------+

UNION（联合）

连接两个sql的结果集

sql1 UNION sql2

两个sql的列数必须一样
两个sql的列名可以不一样，但显示sql1列名，并且sql2的数据信息，排序在sql1的列名下
结果集中有完全一样的行时，将只留一条（union all不去重）

可对 union 后的结果集做排序等字句操作，此时如果内层有 limit 的排序将有效果，否则将无效（即内层没有limit，则order没用）

mysql> select * from boy;
+-----------+------+
| bname     | pid  |
+-----------+------+
| identical | 1    |
| b2        | 2    |
| b3        | 3    |
+-----------+------+

mysql> select * from girl;
+-----------+------+
| gname     | pid  |
+-----------+------+
| identical | 1    |
| g2        | 2    |
| g3        | 2    |
| g4        | 9    |
+-----------+------+

mysql> (
    ->  SELECT
    ->          gname,
    ->          pid
    ->  FROM
    ->          girl
    ->  ORDER BY
    ->          pid DESC
    ->  LIMIT 3
    -> )
    -> UNION
    ->  (
    ->          SELECT
    ->                  bname,
    ->                  pid
    ->          FROM
    ->                  boy
    ->          ORDER BY
    ->                  pid DESC
    ->  );
+-----------+------+
| gname     | pid  |
+-----------+------+
| g4        | 9    |
| g2        | 2    |
| g3        | 2    |
| identical | 1    |
| b2        | 2    |
| b3        | 3    |
+-----------+------+

面试题

mysql> select * from a;
+------+------+
| id   | num  |
+------+------+
| a    |    5 |
| b    |   10 |
| c    |   10 |
| d    |   10 |
+------+------+

mysql> select * from b;
+------+------+
| id   | num  |
+------+------+
| b    |    5 |
| c    |   15 |
| d    |   20 |
| e    |   99 |
+------+------+

要求得到以下结果
+------+------+
| id   | num  |
+------+------+
| a    |    5 |
| b    |   15 |
| c    |   25 |
| d    |   30 |
| e    |   99 |
+------+------+


答：

mysql> SELECT
    ->  id,
    ->  sum(num)
    -> FROM
    ->  (
    ->          SELECT
    ->                  *
    ->          FROM
    ->                  a
    ->          UNION ALL
    ->                  SELECT
    ->                          *
    ->                  FROM
    ->                          b
    ->  ) AS temp
    -> GROUP BY
    ->  id;
+------+----------+
| id   | sum(num) |
+------+----------+
| a    |        5 |
| b    |       15 |
| c    |       20 |
| d    |       25 |
| e    |       99 |
+------+----------+
5 rows in set (0.00 sec)

七、mysql 函数

一、数学函数

ABS(x) 返回绝对值
ACOS() 返回X的反余弦值，即余弦值为X的值。如果X不在-1到1的范围内，则返回NULL。

mysql> SELECT ACOS(1);
        -> 0
mysql> SELECT ACOS(1.0001);
        -> NULL
mysql> SELECT ACOS(0);
        -> 1.5707963267949

ASIN(X) 返回X的反正弦值，即正弦为X的值。如果X不在-1到1的范围内，则返回NULL。

mysql> SELECT ASIN(0.2);
        -> 0.20135792079033
mysql> SELECT ASIN('foo');

+-------------+
| ASIN('foo') |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
+---------+------+-----------------------------------------+

ATAN(X) 返回X的反正切值，即正切值为X的值。

mysql> SELECT ATAN(2);
        -> 1.1071487177941
mysql> SELECT ATAN(-2);
        -> -1.1071487177941

ATAN(Y,X), ATAN2(Y,X) 返回Y / X的反正切值，两个参数的符号用于确定结果的象限。

mysql> SELECT ATAN(-2,2);
        -> -0.78539816339745
mysql> SELECT ATAN2(PI(),0);
        -> 1.5707963267949

CEIL(X),CEILING(x) 返回大于x的最小整数

mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEILING(-1.23);
        -> -1

对于精确值数字参数，返回值具有精确值数字类型。
对于字符串或浮点参数，返回值具有浮点类型。

CONV(N,from_base,to_base)
在不同的数字基础之间转换数字。
返回数字N的字符串表示形式，从基本from_base转换为基本to_base。
如果任何参数为NULL，则返回NULL。
参数N被解释为整数，但可以指定为整数或字符串。
最小基数为2，最大基数为36.
如果from_base为负数，则N被视为有符号数。否则，N被视为未签名。
CONV（）以64位精度工作。

mysql> SELECT CONV('a',16,2);
        -> '1010'
mysql> SELECT CONV('6E',18,8);
        -> '172'
mysql> SELECT CONV(-17,10,-18);
        -> '-H'
mysql> SELECT CONV(10+'10'+'10'+X'0a',10,10);
        -> '40'

COS(x) 返回X的余弦，其中X以弧度给出

mysql> SELECT COS(PI());
        -> -1

COT(x)返回X的余切值。

mysql> SELECT COT(12);
        -> -1.5726734063977
mysql> SELECT COT(0);
        -> out-of-range error

CRC32(expr)
计算循环冗余校验值并返回一个32位无符号值。
如果参数为NULL，则结果为NULL。
该参数预计是一个字符串，并且（可能的话）如果它不是字符串将会被视为字符串

mysql> SELECT CRC32('MySQL');
        -> 1651825290
mysql> SELECT CRC32('mysql');
        -> 2501908538 
mysql> SELECT CRC32('null')
        -> NULL

DEGREES(X) 返回参数X，从弧度转换为度数。

mysql> SELECT DEGREES(PI());
        -> 180
mysql> SELECT DEGREES(PI() / 2);
        -> 90

EXP(X)
返回e的值（自然对数的底数），其值为X的幂
该函数的反函数是 LOG() （仅使用单个参数）或 LN()
（e，自然对数函数的底数 lnx，约2.718281828459045…）

mysql> SELECT EXP(1);
        -> 2.718281828459045
mysql> SELECT EXP(-1);
        ->  0.36787944117144233
mysql> SELECT EXP(0);
        -> 1

floor(x) 返回不大于X的最大整数值。

mysql> SELECT FLOOR(1.23), FLOOR(-1.23);
        -> 1, -2

对于精确值数字参数，返回值具有精确值数字类型
对于字符串或浮点参数，返回值具有浮点类型

FORMAT(X,D)
将数字X格式化为像’＃，###，###。##'这样的格式，四舍五入为D小数位，并将结果作为字符串返回。

mysql> select format(1.715,2);
+-----------------+
| format(1.715,2) |
+-----------------+
| 1.72            |
+-----------------+

mysql> select format(1.14,1);
+----------------+
| format(1.14,1) |
+----------------+
| 1.1            |
+----------------+

HEX(N_or_S)
该函数可用于获取十进制数或字符串的十六进制表示形式;
它的表现方式因论据的类型而异。

mysql> select hex(9);
+--------+
| hex(9) |
+--------+
| 9      |
+--------+

mysql> select hex(10);
+---------+
| hex(10) |
+---------+
| A       |
+---------+

mysql> select hex(99999);
+------------+
| hex(99999) |
+------------+
| 1869F      |
+------------+

LN(X)
返回X的自然对数; 即X的基数e对数。
如果X小于或等于0.0E0，则函数返回NULL，并报告“对于对数无效的参数”警告。

mysql> SELECT LN(1);
        -> 0
mysql> SELECT LN(2);
        -> 0.69314718055995
mysql> SELECT LN(-2);
        -> NULL

该功能与LOG（X）同义。这个函数的反函数是EXP（）函数。

LOG(X), LOG(B,X)
如果用一个参数调用，则此函数返回X的自然对数。
如果X小于或等于0.0E0，则函数返回NULL，并报告“对于对数无效的参数”警告。

这个函数的反函数（当用一个参数调用时）是EXP（）函数。

mysql> select log(1);
+--------+
| log(1) |
+--------+
|      0 |
+--------+

mysql> select log(-1);
+---------+
| log(-1) |
+---------+
|    NULL |
+---------+

如果使用两个参数调用，则此函数将X的对数返回给基数B.（以X为底，B的对数）
如果X小于或等于0，或者B小于或等于1，则返回NULL。

mysql> SELECT LOG(2,65536);
        -> 16
mysql> SELECT LOG(10,100);
        -> 2
mysql> SELECT LOG(1,100);
        -> NULL

LOG(B,X) 相当于 LOG(X) / LOG(B).

LOG2(X)
返回X的基数为2的对数。
如果X小于或等于0.0E0，则该函数返回NULL，并报告“对于对数无效的参数”警告。

mysql> SELECT LOG2(8);
        -> 3
mysql> SELECT LOG2(-100);
        -> NULL

LOG10(X)
同 log2()
LOG10(X) 相当于 LOG(10,X)
MOD(N,M), N % M, N MOD M 模数操作。返回N除以M的余数

mysql> SELECT MOD(2, 4);
        -> 2
mysql> SELECT 4 % 2;
        -> 0
mysql> SELECT 21 MOD 4;
        -> 1

该功能可以安全地用于BIGINT值。
MOD（）也适用于具有小数部分的值，并返回除法后的精确余数：

mysql> SELECT MOD(34.5,3);
        -> 1.5

MOD(N,0) returns NULL.

POW(X,Y)，POWER(X,Y) 返回X的值增加到Y的幂。

mysql> SELECT POW(2,2);
        -> 4
mysql> SELECT POW(2,-2);
        -> 0.25
mysql> SELECT POWER(3,3);
        -> 9

RADIANS(X)
返回从度数转换为弧度的参数X. （注意π弧度等于180度。）

mysql> SELECT RADIANS(180);
        -> 3.141592653589793

RAND([N]) 返回0 <= v <1.0范围内的随机浮点值v。

要获得范围为i <= R <j的随机整数R，请使用表达式FLOOR（i + RAND（）*（j-i））。

//获取1 <= R < 10范围内的随机整数：
mysql> SELECT FLOOR(1 + (RAND() * 9));
+-------------------------+
| FLOOR(1 + (RAND() * 9)) |
+-------------------------+
|                       4 |
+-------------------------+

如果指定了整数参数N，则将其用作种子值：
在初始化参数不变的情况下，当语句准备好时，种子会在执行前初始化一次。
使用非常量初始化参数（如列名称），种子将使用每次调用RAND（）的值进行初始化。

在ORDER BY或GROUP BY子句中使用带有RAND（）值的列可能会产生意外结果，因为对于任一子句，RAND（）表达式可以针对同一行进行多次评估，每次都返回不同的结果。

如果目标是以随机顺序检索行，则可以使用如下所示的语句：

SELECT * FROM tbl_name ORDER BY RAND();

要从一组行中选择一个随机样本，请将ORDER BY RAND（）与LIMIT结合使用：

SELECT * FROM table1, table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000;

ROUND(X), ROUND(X,D)
将参数X舍入到D小数位。
舍入算法取决于X的数据类型。
如果未指定，则D默认为0。
D可能是负数，导致值X的小数点左边的D数字变为零。

mysql> SELECT ROUND(-1.23);
        -> -1
mysql> SELECT ROUND(-1.58);
        -> -2
mysql> SELECT ROUND(1.58);
        -> 2
mysql> SELECT ROUND(1.298, 1);
        -> 1.3
mysql> SELECT ROUND(1.298, 0);
        -> 1
mysql> SELECT ROUND(23.298, -1);
        -> 20
mysql> select round(233.298,-2);
        -> 200

返回值与第一个参数具有相同的类型（假设它是整数，双精度或十进制）。
这意味着对于整数参数，结果是一个整数（无小数位）：

mysql> SELECT ROUND(150.000,2), ROUND(150,2);
+------------------+--------------+
| ROUND(150.000,2) | ROUND(150,2) |
+------------------+--------------+
|           150.00 |          150 |
+------------------+--------------+

根据第一个参数的类型，ROUND（）使用以下规则：

对于精确值数字，ROUND（）使用“从零开始的半个圆”或“向最近”旋转规则：
小数部分为.5或更大的值向上取整为下一个整数，如果为正或向下下一个整数如果为负数（换句话说，它是从零开始舍去的。）如果小数部分小于.5，则数值向下取整为下一个整数，如果是负数，则向下取整。

对于近似值数字，结果取决于C库。在许多系统上，这意味着ROUND（）使用“round to nearest even”规则：将任何小数部分的值四舍五入到最接近的偶数整数。

以下示例显示了精确值和近似值的舍入方式不同：

mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3          |            2 |
+------------+--------------+

//25E-1 = 2.5

SIGN(X) 根据X是负数，零还是正数，将参数的符号返回为-1,0或1。

mysql> SELECT SIGN(-32);
        -> -1
mysql> SELECT SIGN(0);
        -> 0
mysql> SELECT SIGN(234);
        -> 1
mysql> SELECT SIGN('a');
        -> 0

SIN(X) 返回X的正弦值，其中X以弧度给出。

mysql> SELECT SIN(PI());
        -> 1.2246063538224e-16
mysql> SELECT ROUND(SIN(PI()));
        -> 0

SQRT(X) 返回非负数X的平方根。

mysql> SELECT SQRT(4);
        -> 2
mysql> SELECT SQRT(20);
        -> 4.4721359549996
mysql> SELECT SQRT(-16);
        -> NULL

TAN(X) 返回X的正切值，其中X以弧度给出

mysql> SELECT TAN(PI());
        -> -1.2246063538224e-16
mysql> SELECT TAN(PI()+1);
        -> 1.5574077246549

TRUNCATE(X,D)
返回数字X，截断到D小数位
如果D为0，结果没有小数点或小数部分
D可能是负数，导致值X的小数点左边的D数字变为零

mysql> SELECT TRUNCATE(1.223,1);
        -> 1.2
mysql> SELECT TRUNCATE(1.999,1);
        -> 1.9
mysql> SELECT TRUNCATE(1.999,0);
        -> 1
mysql> SELECT TRUNCATE(-1.999,1);
        -> -1.9
mysql> SELECT TRUNCATE(122,-2);
       -> 100
mysql> SELECT TRUNCATE(10.28*100,0);
       -> 1028

bin(x) 返回x二进制（oct 八进制，hex 十六进制）

mysql> select bin(7);
+--------+
| bin(7) |
+--------+
| 111    |
+--------+

LOCATE(substr,str) , LOCATE(substr,str,pos)
第一个语法返回字符串 str中子字符串substr的第一个出现位置。
第二个语法返回字符串 str中子字符串substr的第一个出现位置, 起始位置在pos。
如若substr 不在str中，则返回值为0。

mysql> select locate('世','你好 世界');
+--------------------------+
| locate('世','你好 世界')           |
+--------------------------+
|                        4 |
+--------------------------+

mysql> select locate('a','hello world');
+---------------------------+
| locate('a','hello world') |
+---------------------------+
|                         0 |
+---------------------------+

POSITION(substr IN str) 返回字符串第一次出现的位置

mysql> select position('@' in 'ywc@2018.cmo');
+---------------------------------+
| position('@' in 'ywc@2018.cmo') |
+---------------------------------+
|                               4 |
+---------------------------------+

right(str,len) 从字符串 str 右边开始，返回 len 个字符。

mysql> select right('hello world',3);
+------------------------+
| right('hello world',3) |
+------------------------+
| rld                    |
+------------------------+

//取出@后缀
mysql> select id,email,right(email,length(email)-position('@' in `email`)) as hz
 from email;
+------+-----------------+-----------+
| id   | email           | hz        |
+------+-----------------+-----------+
|    1 | ywc@qq.com      | qq.com    |
|    2 | moon@sina.cn    | sina.cn   |
|    3 | ymoon@gmail.com | gmail.com |
+------+-----------------+-----------+

所以存储的时候可以把email以@为界，分别存储在两个列中，在想查询后缀的时候可省去以上，优化了数据库查询

二、聚合函数

group_concat(col) 返回属于一组的列值连接组合而成的结果

mysql> select goods_id,cat_id from goods where cat_id=24;
+----------+--------+
| goods_id | cat_id |
+----------+--------+
|       69 |     24 |
|       70 |     24 |
|       72 |     24 |
|       64 |     24 |
|       63 |     24 |
|       61 |     24 |
|       62 |     24 |
|       68 |     24 |
+----------+--------+

mysql> select group_concat(goods_id,'') from goods where cat_id=24;
+---------------------------+
| group_concat(goods_id,'') |
+---------------------------+
| 69,70,72,64,63,61,62,68   |
+---------------------------+

三、字符串函数

ascii(char) 返回字符串的ASCII码值

mysql> select ascii('a');
+------------+
| ascii('a') |
+------------+
|         97 |
+------------+

length(str)返回字符串str的字节数

mysql> select length('hello world');
+-----------------------+
| length('hello world') |
+-----------------------+
|                    11 |
+-----------------------+

mysql> select length('你好 世界'); //gbk编码的字符,空格占两个字节
+---------------------+
| length('你好 世界')  |
+---------------------+
|                   9 |
+---------------------+

mysql> select tid,tname,length(tname) from t; //utf8编码的字符占三个字节
+------+----------+---------------+
| tid  | tname    | length(tname) |
+------+----------+---------------+
|    1 | 国安      |             6 |
|    2 | 甲花      |             6 |
|    3 | 公益联队   |            12 |
+------+----------+---------------+

char_length(str)返回字符串str的字符数

mysql> select tid,tname,length(tname),char_length(tname) from t;
+------+----------+---------------+--------------------+
| tid  | tname    | length(tname) | char_length(tname) |
+------+----------+---------------+--------------------+
|    1 | 国安         |             6 |                  2 |
|    2 | 甲花        |             6 |                  2 |
|    3 | 公益联队         |            12 |                  4 |
+------+----------+---------------+--------------------+

reverse(str) 反转字符串str

mysql> select tid,tname,reverse(tname) from t;
+------+----------+----------------+
| tid  | tname    | reverse(tname) |
+------+----------+----------------+
|    1 | 国安         | 安国               |
|    2 | 甲花        | 花甲               |
|    3 | 公益联队         | 队联益公             |
+------+----------+----------------+

四、日期函数

now()获取当前日期和时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-05-16 13:47:51 |
+---------------------+

curdate(),current_date()返回当前日期

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2018-05-16 |
+------------+

curdate()，current_date()返回当前时间

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 13:50:14  |
+-----------+

dayofweek(date) 返回 date 在那周的第几天

mysql> select dayofweek('2018-05-16');
+----------------------+
| dayofweek(curdate()) |
+----------------------+
|                    4 |
+----------------------+

//注意：周日是第一天

week(date) 返回日期 date 在当年的第几周

mysql> select week(curdate());
+-----------------+
| week(curdate()) |
+-----------------+
|              19 |
+-----------------+

问：按周统计加班时间

如下表，1~5 的 num 相加，6~12 的 num 相加

mysql> select * from overtime;
+------+------------+
|num   |  date      |
+------+------------+
|    5 | 2018-05-01 |
|    6 | 2018-05-02 |
|    7 | 2018-05-03 |
|    8 | 2018-05-04 |
|    9 | 2018-05-05 |
|   10 | 2018-05-06 |
|   11 | 2018-05-07 |
|   12 | 2018-05-08 |
|   13 | 2018-05-09 |
|   14 | 2018-05-10 |
|   15 | 2018-05-11 |
|   16 | 2018-05-12 |
+------+------------+

答：
mysql> select sum(num) as overtime,week(dt) as wk from overtime group by wk;
+----------+------+
| overtime | wk   |
+----------+------+
|       35 |   17 |
|       91 |   18 |
+----------+------+

五、加密函数

md5(str)计算字符串str的md5效验和

mysql> select md5('123');
+----------------------------------+
| md5('123')                       |
+----------------------------------+
| 202cb962ac59075b964b07152d234b70 |
+----------------------------------+
//该加密算法不可逆

良好的加密：
1.不可逆。 即不可以根据加密后的值逆推原值
2.碰撞性底。 即两个加密后的值重复的可能性底

六、流程控制函数

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

在第一个方案的返回结果中， value=compare-value。
第二个方案的返回结果是第一种情况的真实结果。
如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL。

mysql> select * from b;
+------+------+
| id   | num  |
+------+------+
| b    |    5 |
| c    |   10 |
| d    |   15 |
| e    | NULL |
+------+------+
4 rows in set (0.00 sec)

mysql> SELECT
    ->  id,
    ->  CASE `num`
    -> WHEN 5 THEN
    ->  '值5'
    -> WHEN 10 THEN
    ->  10
    -> WHEN 15 THEN
    ->  '值15'
    -> ELSE
    ->  '其他值'
    -> END AS nums
    -> FROM
    ->  b;
+------+--------+
| id   | nums   |
+------+--------+
| b    | 值5     |
| c    | 10     |
| d    | 值15    |
| e    | 其他值      |
+------+--------+
4 rows in set, 1 warning (0.01 sec)


mysql> SELECT
    ->  id,
    ->  CASE
    -> WHEN num THEN
    ->  1
    -> ELSE
    ->  0
    -> END AS nums
    -> FROM
    ->  b;
+------+------+
| id   | nums |
+------+------+
| b    |    1 |
| c    |    1 |
| d    |    1 |
| e    |    0 |
+------+------+
4 rows in set (0.00 sec)

IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。

mysql> SELECT
    ->  id,
    -> IF (num = 5, '值5', '其他') AS num
    -> FROM
    ->  b;
+------+------+
| id   | num  |
+------+------+
| b    | 值5   |
| c    | 其他     |
| d    | 其他     |
| e    | 其他     |
+------+------+
4 rows in set, 1 warning (0.00 sec)

IFNULL(expr1,expr2)
假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。

mysql> SELECT
    ->  id,
    -> IFNULL (num,'expr1为NULL') AS num
    -> FROM
    ->  b;
+------+-------------+
| id   | num         |
+------+-------------+
| b    | 5           |
| c    | 10          |
| d    | 15          |
| e    | expr1为NULL  |
+------+-------------+
4 rows in set (0.00 sec)

NULLIF(expr1,expr2)
如果expr1 = expr2 成立，那么返回值为NULL，否则返回值为 expr1。

mysql> SELECT
    ->  id,
    -> NULLIF (num,5) as num
    -> FROM
    ->  b;
+------+------+
| id   | num  |
+------+------+
| b    | NULL |
| c    |   10 |
| d    |   15 |
| e    | NULL |
+------+------+
4 rows in set (0.00 sec)

七、系统函数

user()
返回用户及所在主机，判断自己身份

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

database()
返回当前正在操作的数据库

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

version()
返回当前数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.53    |
+-----------+
1 row in set (0.03 sec)

使用mysql函数会影响查询速度，应通过表结构等减少函数的使用

八、mysql视图

视图view

视图的特点：

视图是表的投影，可以像表一样查询
表改变，视图改变
视图改变，当视图与表是一一对应关系时（否者视图不能改变），可以增删改，表也对应改变。
视图不占用空间，只是与表的一种关系，不存在真实的数据
创建的视图只有*.frm文件（frm表结构，MYD表数据，MYI表索引）
用 limit 查询创建的视图没有一一对应关系

视图的作用：

简化查询
精细权限控制
数据多时，用于分表

示例：

mysql> create view avg_price
    -> as
    -> select cat_id,avg(shop_price) as avg_price from goods group by cat_id;

mysql> select * from avg_price;
+--------+-------------+
| cat_id | avg_price   |
+--------+-------------+
|      3 | 1902.333333 |
|      4 | 2006.500000 |
|      6 |   26.833333 |
|      8 |   52.000000 |
|     16 |   64.000000 |
|     18 |  149.000000 |
|     19 |   60.180000 |
|     20 |   29.000000 |
|     22 |   69.000000 |
|     24 |  336.762500 |
|     25 |  998.000000 |
+--------+-------------+

这里写图片描述

视频截图

视图的概念：

创建视图的语法：

视图的作用：

视图algorithm

merge：合并查询语句。简单的查询 (如 x< col < y)
temptable：建立临时表。复杂查询（如分组统计）
undefined ：未定义，由系统判断

注意：合并语句比建临时表快

	create algorithm = merge view v1
	as 
	select语句

//algorithm=merge

mysql> create algorithm=merge view v1
    -> as
    -> select goods_id,goods_name,shop_price from goods where shop_price>100;

mysql> select * from v1
    -> where shop_price<500;
+----------+--------------------------+------------+
| goods_id | goods_name               | shop_price |
+----------+--------------------------+------------+
|        8 | 飞利浦9@9v                     |     399.00 |
|       72 | 智能相机                         |     149.00 |
|       64 | 运动相机                        |     399.00 |
|       36 | 路由器                        |     149.00 |
|       39 | 移动电源 10000mAh 高配版             |     149.00 |
|       47 | 极简都市双肩包                        |     149.00 |
|       50 | 移动电源16000mAh               |     129.00 |
+----------+--------------------------+------------+

//algorithm=temptable

mysql> create algorithm=temptable view v1
    -> as
    -> select goods_id,goods_name,cat_id,shop_price from goods
    -> order by cat_id asc,shop_price desc limit 10;

这里写图片描述

九、字符编码

字符集编码

ASCII
1字节 8位
0~127 表示，组合有256种变化，例如 65 66，分别表示为 a b
0xxx xxxx，最高位始终是0
用来表示英文
GB2312字符集
2字节 16位
不占用 0~127
组合来自[129_255][129255]，例如 144 155 29 136 122，分别表示为中英中
只能组合10000+种变化，事实上 gb2312 只能容纳6000多字
GBK
2字节 16位
0~127的也能用
组合来自 [129~255] [0~127]，例如 144 29 28 136 26，分别表示为中英中
收录汉字 21003，符号 883 个，并提供 1894 个造字码位
区分gb2312，GBK，ASCII：
如果第一个值<127，则为ascii，
如果第一个值>127，且第二个值 <128，则为GBK
如果第一个值>127，且第二个值 >127，则为GB2312
ANSI 代表本地字符集。即在哪个国家就按哪个国家的字符翻译规则。

这里写图片描述

UNICODE
国际字符集，世界通用的码表，全世界的字符，统一分配一个标号
4个字节编码，2^32，40多亿
unicode的实现方式为 unicode 转换格式（Unicode Transformation Format，简称UTF），其中最出名的转换方式为UTF-8
UTF-8
负责转码Unicode的编码
变长编码，占1~6个字节

确定字符编码的边界：
最高位为0占一个字节，最高位为110则占两个字节，如下表：

截取utf8字符（各国），无乱码：
从头开始取一个字节，通过位运算计算连续的1的个数，
如为0，则截取1个字节；如为N，则取N个字节
从容量上看： GB2312 < GBK < UTF-8

GBK转换UTF-8：GBK转换为unicode编码，再转换为utf-8

形成乱码的原因：
1.解码时与实际编码不一致（可修复）
如解码为utf-8的编码，却在浏览器中指定charset为gbk
编码与解码一致即可

2.在传输的过程中，编码不一致，导致字节丢失（不可修复）
UTF-8 -> gbk2312
容量大容量小
gbk不识别utf-8的一些编码，则丢弃字节

mysql字符集参数

客户端（client） -> 连接器（connection） -> 数据库返回内容（results）

三个参数：
客户端发送的编码
连接器使用的编码
获取数据库返回数据的编码

这里写图片描述

#假如，客户端使用的编码为gbk，服务器使用的编码为utf8
则应该如下设置

mysql> set character_set_client=gbk; //设置客户端的编码为gbk
mysql> set character_set_connection=utf8;	//设置连接器的编码为utf8
mysql> set character_set_results=gbk;  //设置数据库返回数据的编码为gbk (非数据库的编码, 这个过程发生：utf8转换为gbk)
或
mysql> set character_set_client=gbk; 
mysql> set character_set_connection=gbk;	
mysql> set character_set_results=gbk;  
或
mysql> set character_set_client=utf8; 
mysql> set character_set_connection=utf8;	
mysql> set character_set_results=utf8;

可得三者编码关系：client <= connection <= results

如果三者都设置为gbk，则可简写为：set names gbk

如果，网页utf8，则client和返回值是utf8；服务器是utf8，则connect是utf8。因此，set names utf8

牵涉到数据库，不乱码原则：
1.正确指定客户端编码
2.合理选择连接器编码
3.正确指定返回内容编码

网页meta的charset编码和client / connection / results都保持一致，将不会出现乱码问题，否则为其他错误。

utf8的BOM问题

在txt文件里写入“联通”两个字保存，再打开时出现乱码

原因：
默认存储为ansi，即gbk编码
记事本打开时，也分不出写入的字符是哪种编码，在字节比较少时容易搞错
在xp下，用记事本创建utf8文件，前面多了三个字节，这三个字节用来辨识编码，不显示

EF BB BF 标识为utf8

utf8+ 就是utf8有bom头的意思，使用session时会报错，只要再次更改存储格式为utf8，或另存为使用utf8即可

php header("content-type:text/html;charset=utf-8")
mysql set names utf8
html <meta charset=utf-8>
都是UTF-8编码的意思

截取字符编码
这里写图片描述

这里写图片描述

<?php
$str = "中华人aaaa民共b和,国万c岁";

/*
* 截取字符串（通过高位判断,可判断ASCII，GBK，UTF-8 编码）
* $str 待截取字符串
* $len 截取的字符数
*/

function utf8sub($str,$len){
	if($len <= 0){
		return '';
	}
	
	$length = strlen($str); //待截取的字符串字节数
	$offset = 0; //截取高位字节时的偏移量
	$chars = 0;	//截取到的字符数
	$res = ''; //截取的字符串

	while( $chars < $len && $offset < $length){ //没有截取到长度继续进行
		//substr 截取字节
		//ord 返回字符的ASCII码值
		//decbin 十进制转二进制
		$high = decbin(ord(substr($str,$offset,1))); //截取字符串的第一个字节，判断高位字节
		
		//通过截取字节位判断
		if(strlen($high)< 8 ){// 因为decbin()函数会舍弃最高位的0，导致截取的1字节变为了7位
			//截取1个字节
			$count = 1;
		}else if(substr($high,0,3) == '110'){ 
			//截取2个字节
			$count = 2;
		}else if(substr($high,0,4) == '1110'){
			//截取3个字节
			$count = 3;
		}else if(substr($high,0,5) == '11110'){
			//截取4个字节
			$count = 4;
		}else if(substr($high,0,6) == '111110'){
			//截取5个字节
			$count = 5;
		}
		
		// var_dump(($high & '11000000'));
		// //通过位运算判断(未成功)
		// if(strlen($high)< 8 ){
		// 	$count = 1;
		// }else if( ($high & '11000000') == '11000000'){ 
		// 	$count = 2;
		// }else if( ($high & '11100000') == '11100000'){
		// 	$count = 3;
		// }else if( ($high & '11110000') == '11110000'){
		// 	$count = 4;
		// }else if( ($high & '11111000') == '11111000'){
		// 	$count = 5;
		// }

		$res .= substr($str,$offset,$count);
		$chars += 1;
		$offset += $count;

	}
	return $res;
	
}

echo utf8sub($str,5);

十、触发器

触发器概念
触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行。

即执行一条sql语句，自动触发执行其他的sql语句

触发器四要素：

监视地点：table
监视事件：insert / update / delete
触发时间：after / before
触发事件：insert / update / delete

查看已有trigger：show triggers
删除已有trigger：drop trigger triggerName

如果删除表格，则表格的任何触发器也会被删除。

实例

要创建的表和数据

//商品表
CREATE TABLE g (
	`gid` INT,
	`name` VARCHAR (20),
	`num` SMALLINT
);

//订单表
CREATE TABLE `ord` (
	`oid` INT,
	`gid` INT,
	`much` SMALLINT
);

//插入商品
INSERT INTO g
VALUES
	(1, 'cat', 34),
	(2, 'dog', 34),
	(3, 'pig', 34);

触发器：trigger

需求：
商品表：g 
订单表：ord 

当下订单时对应的商品表要相应减少（买几个商品少几个库存）

分析：
监视对象：ord 
监视动作：insert 
触发时间：after 
触发事件：update

新增订单（insert用new）

//创建触发器

//以 $ 符替换 ; 结束语句（方便trigger用;分割书写多条sql语句）
mysql> delimiter $ 			

mysql>  create trigger t2   //触发器名字
    ->  after      //触发时间
    ->  insert     //监视动作
    ->  on ord     //监视对象
    ->  for each row     //行级触发器
    ->  begin   //触发事件开始 
    ->  update g set num=num-new.much where gid=new.gid;     //触发事件
    ->  end$   //触发事件结束 
    
//监视的ord表的insert，可以用new表示ord表新插入的行（delete用old） 
//begin和end之间可以写多条sql语句，用;号隔开（前提;不是结束符号）

//查看原始数据
mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   32 |
|    2 | dog  |   34 |
|    3 | pig  |   34 |
+------+------+------+

mysql> select * from ord$
+------+------+------+
| oid  | gid  | much |
+------+------+------+
|  134 |    1 |    2 |
+------+------+------+

//执行trigger监视insert后的语句
mysql> insert into ord values(135,2,4)$

//trigger后的数据
mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   32 |
|    2 | dog  |   30 |
|    3 | pig  |   34 |
+------+------+------+

删除订单（delete用old）

//创建trigger
mysql> create trigger t3
    -> after
    -> delete
    -> on ord
    -> for each row
    -> begin
    -> update g set num=num+old.much where gid=old.gid;
    -> end$

//原始数据
mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   32 |
|    2 | dog  |   30 |
|    3 | pig  |   34 |
+------+------+------+ 

mysql> select * from ord$
+------+------+------+
| oid  | gid  | much |
+------+------+------+
|  134 |    1 |    2 |
|  135 |    2 |    4 |
+------+------+------+

//执行trigger监视ord表的delete语句
mysql> delete from ord where oid=135$
mysql> delete from ord where oid=134$

//trigger后的数据
mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   34 |
|    2 | dog  |   34 | 
|    3 | pig  |   34 |
+------+------+------+

mysql> select * from ord$
Empty set (0.00 sec)

修改订单（用old和new）


mysql> create trigger t4
    -> before
    -> update
    -> on ord
    -> for each row
    -> begin
    -> update g set num = num + old.much - new.much where gid=new.gid;
    -> end$

mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   34 |
|    2 | dog  |   30 |
|    3 | pig  |   34 |
+------+------+------+

mysql> select * from ord$
+------+------+------+
| oid  | gid  | much |
+------+------+------+
|  124 |    2 |    4 |
+------+------+------+

//执行update
mysql> update ord set much = much - 2 where oid=124$

mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   34 |
|    2 | dog  |   32 |
|    3 | pig  |   34 |
+------+------+------+

mysql> select * from ord$
+------+------+------+
| oid  | gid  | much |
+------+------+------+
|  124 |    2 |    2 |
+------+------+------+

before 与 after 的区别

商品爆仓问题


mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   34 |
|    2 | dog  |   32 |
|    3 | pig  |   34 |
+------+------+------+

mysql> select * from ord$
+------+------+------+
| oid  | gid  | much |
+------+------+------+
|  124 |    2 |    2 |
+------+------+------+

//购买35只pig，商品只有34只
mysql> insert into ord values
    -> (125,3,35)$

mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   34 |
|    2 | dog  |   32 |
|    3 | pig  |   -1 |
+------+------+------+

mysql> select * from  ord$
+------+------+------+
| oid  | gid  | much |
+------+------+------+
|  124 |    2 |    2 |
|  125 |    3 |   35 |
+------+------+------+

预防商品爆仓

//用after预防

mysql> create trigger t6
    -> after
    -> insert
    -> on ord
    ->
    -> for each row
    -> begin
    ->
    -> declare
    -> rnum int;    //声明变量。declare必须写在begin里面，oracle可以写在外面
    ->
    -> select num into rnum from g where gid=new.gid;    //把原商品数量存如变量rnum中
    ->
    -> if new.much > rnum then       //比较购买的（insert）的商品数量和原商品数量
    ->     set new.much = rnum;     //如果购买的商品数量大于原商品数量，则把原商品数量赋给购买的商品数量。注意写分号
    -> end if;    //注意写分号
    ->
    -> update g set num=num - new.much where gid=new.gid;
    -> end$
//报如下错误：
ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger

//原因：insert 之后，new行已经插入表中，不能再修改


//用before

mysql> create trigger t6
    -> before
    -> insert
    -> on ord
    ->
    -> for each row
    -> begin
    ->
    -> declare
    -> rnum int;
    ->
    -> select num into rnum from g where gid=new.gid;
    ->
    -> if new.much > rnum then
    ->     set new.much = rnum;
    -> end if;
    ->
    -> update g set num=num - new.much where gid=new.gid;
    -> end$

//原数据
mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   34 |
|    2 | dog  |   32 |
|    3 | pig  |   34 |
+------+------+------+

mysql> select * from ord$
+------+------+------+
| oid  | gid  | much |
+------+------+------+
|  124 |    2 |    2 |
+------+------+------+

//购买35个商品数量故意超过
mysql> insert into ord values(125,3,35)$

//预防成功
mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   34 |
|    2 | dog  |   32 |
|    3 | pig  |    0 |
+------+------+------+

mysql> select * from ord$
+------+------+------+
| oid  | gid  | much |
+------+------+------+
|  124 |    2 |    2 |
|  125 |    3 |   34 |
+------+------+------+

for each row 行级触发器

每一行受影响，触发器都执行，叫做行级触发器

在oracle中，触发器分为语句级触发器和行级触发器
在oracle中，不写for each row，无论语句影响多少行，都只执行一次
在mysql中，不写for each row报错，不支持语句级触发器

mysql>  create trigger t1
    ->  before
    ->  update
    ->  on g
    ->  for each row
    ->  begin
    ->  insert into tem values(1);
    ->  end$

//原表数据
mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   34 |
|    2 | dog  |   32 |
|    3 | pig  |    0 |
+------+------+------+

mysql> select * from tem$
Empty set (0.00 sec)

//注意条件，两条数据受影响
mysql>  update g set num=255 where gid > 1$
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   34 |
|    2 | dog  |  255 |
|    3 | pig  |  255 |
+------+------+------+

//由for each row 循环执行了两遍insert语句
mysql> select * from tem$
+------+
| id   |
+------+
|    1 |
|    1 |
+------+

以下为mysql手册笔记

CREATE TRIGGER语法

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

触发器是一个与表关联的命名数据库对象，当表发生特定事件时会激活该对象。
触发器与名为tbl_name的表相关联，该表必须引用永久表。您无法将触发器与TEMPORARY表或视图相关联。

触发器名称存在于模式名称空间中，这意味着所有触发器都必须在模式中具有唯一的名称。不同模式中的触发器可以具有相同的名称。

CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name
需要与触发器关联的表的TRIGGER权限。
该语句可能还需要SET_USER_ID或SUPER权限，具体取决于DEFINER值。
如果启用二进制日志记录，则CREATE TRIGGER可能需要SUPER权限

DEFINER子句确定在触发器激活时检查访问特权时要使用的安全上下文

trigger_time: { BEFORE | AFTER }
是触发器操作时间。
可以在之前或之后指示触发器在要修改的每行之前或之后激活。

基本列值检查发生在触发器激活之前，因此您不能使用BEFORE触发器将不适合列类型的值转换为有效值。

trigger_event: { INSERT | UPDATE | DELETE }
表示激活触发器的操作类型。
这些trigger_event值是允许的：
INSERT：只要将新行插入表中，触发器就会激活;
例如，通过INSERT，LOAD DATA和REPLACE语句。
UPDATE：只要行被修改，触发器就会激活;
例如，通过UPDATE语句。
DELETE：只要从表中删除一行，触发器就会激活;
例如，通过DELETE和REPLACE语句。
表中的DROP TABLE和TRUNCATE TABLE语句不会激活此触发器，因为它们不使用DELETE。
删除分区也不会激活DELETE触发器。

trigger_event不代表激活触发器的字面类型的SQL语句，因为它表示一种表操作。例如，INSERT触发器不仅激活INSERT语句，还激活LOAD DATA语句，因为这两个语句都将行插入表中。

一个潜在的令人困惑的例子是 INSERT INTO … ON DUPLICATE KEY UPDATE …语法：BEFORE INSERT触发器激活每一行，然后是AFTER INSERT触发器或BEFORE UPDATE和AFTER UPDATE触发器，具体取决于是否存在该行的重复键。

NOTE
级联外键操作不会激活触发器。

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

可以为具有相同触发事件和操作时间的给定表定义多个触发器。

例如，您可以为表创建两个BEFORE UPDATE触发器。默认情况下，具有相同触发事件和操作时间的触发器按照它们创建的顺序激活。

要影响触发器顺序，请指定一个trigger_order子句，该子句指示FOLLOWS或PRECEDES以及同样具有相同触发器事件和操作时间的现有触发器的名称。

使用FOLLOWS，新触发器将在现有触发器后激活。

使用PRECEDES，新触发器会在现有触发器之前激活。

trigger_body
触发器激活时要执行的语句。
要执行多个语句，请使用 BEGIN … END 复合语句结构。
这也使您能够使用存储例程中允许的相同语句。
触发器中不允许使用某些语句;参见对存储程序的限制

在触发器正文中，可以使用别名 OLD 和 NEW 来引用主题表中的列（与触发器关联的表）。
OLD.col_name在更新或删除之前引用现有行的列。
NEW.col_name引用要插入的新行的列或更新后的现有行。

触发器不能使用 NEW.col_name 或使用 OLD.col_name 来引用生成的列。

当创建触发器时，MySQL存储 sql_mode 系统变量设置，并始终使用此设置执行触发器主体，而不管触发器开始执行时的当前服务器 SQL 模式如何。

DEFINER 子句指定在触发器激活时检查访问权限时要使用的 MySQL 帐户。
如果给出用户值，它应该是指定为 ‘user_name’@‘host_name’，CURRENT_USER 或 CURRENT_USER（）的 MySQL 帐户。
DEFINER 的默认值是执行 CREATE TRIGGER 语句的用户。这与明确指定 DEFINER = CURRENT_USER 相同。

如果您指定DEFINER子句，则这些规则将确定有效的DEFINER用户值：

如果您没有SET_USER_ID或SUPER权限，则唯一允许的用户值是您自己的帐户，可以按字面或使用 CURRENT_USER 指定。您无法将定义者设置为某个其他帐户。
如果您具有 SET_USER_ID 或S UPER 权限，则可以指定任何语法上有效的帐户名称。如果该帐户不存在，则会生成警告。
虽然可以创建一个不存在 DEFINER 帐户的触发器，但直到帐户实际存在之前激活这些触发器并不是一个好主意。否则，有关特权检查的行为是未定义的。

在检查触发特权时，MySQL会将 DEFINER 用户考虑在内，如下所示：

在 CREATE TRIGGER 时间，发布语句的用户必须具有 TRIGGER 权限。
在触发器激活时，将针对DEFINER用户检查权限。该用户必须具有以下权限：
- 主题表的TRIGGER特权。
- 如果在触发器主体中使用 OLD.col_name 或 NEW.col_name 进行对表列的引用，则主题表的 SELECT 权限会发生。
- 如果表列是 SET NEW.col_name 的目标，则主题表的 UPDATE 特权 = 触发器主体中的值赋值。
- 无论触发器执行的语句通常需要什么其他权限。

在触发器体内，CURRENT_USER（）函数返回用于在触发器激活时检查权限的帐户。这是 DEFINER 用户，而不是其操作导致触发器被激活的用户。

如果您使用 LOCK TABLES 锁定具有触发器的表格，则触发器中使用的表格也将被锁定。

触发器语法和示例

创建一个名为 ins_sum 的触发器与 account 表关联。

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
       FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.01 sec)

要使用触发器，请将累加器变量设置为零，执行INSERT语句，然后查看该变量后面的值：

mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+

如果触发器不在默认模式中，销毁触发器必须指定模式（数据库？）名称：

mysql> DROP TRIGGER test.ins_sum;

如果删除表格，则表格的任何触发器也会被删除。

触发器名称存在于模式名称空间中，这意味着所有触发器都必须在模式中具有唯一的名称。不同模式中的触发器可以具有相同的名称。

为具有相同触发事件和操作时间的给定表定义多个触发器
FOLLOWS，在指定触发器之后激活
PRECEDES，在指定触发器之前激活

mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
       FOR EACH ROW PRECEDES ins_sum
       SET
       @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
       @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
Query OK, 0 rows affected (0.01 sec)

获取触发器的元数据：查询INFORMATION_SCHEMA 数据库的 TRIGGERS 表

十一、存储过程

在一个语言中，如pascal，有一个概念叫 “过程” procedure 和 "函数" function
在php中，没有过程，只有函数

过程：封装了若干条语句，调用时执行这些封装体。过程类似函数，但是没有返回值

存储过程：把过程存储在数据库中

创建存储过程语法：

create procedure procedureName()
begin
	-- sql语句
end$

mysql> delimiter $
mysql> create procedure p1()
    -> begin
    -> select 1+2;
    -> end$
Query OK, 0 rows affected (0.03 sec)

查看存储过程语法：show procedure status

mysql> show procedure status \G
*************************** 1. row ***************************
                  Db: test2
                Name: p1
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2018-05-24 15:46:12
             Created: 2018-05-24 15:46:12
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

调用存储过程语法：call procedureName

mysql> call p1()$
+-----+
| 1+2 |
+-----+
|   3 |
+-----+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.03 sec)

在存储过程中，用 declare 声明变量，语法类似声明列：declare 变量名变量类型 [default 默认值]

mysql> create procedure p2()
    -> begin
    -> declare age int default 18;
    -> declare height int default 180;
    ->
    -> select concat('年龄是',age,'身高是',height);
    -> end$
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> call p2()$
+--------------------------------------+
| concat('年龄是',age,'身高是',height)             |
+--------------------------------------+
| 年龄是18身高是180                                |
+--------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.03 sec)

在存储过程中，变量可以在sql中合法运算，如±*/，并且运算的结果可以赋值给变量，语法为：set 变量名 := expression

mysql> create procedure p3()
    -> begin
    -> declare age int default 18;
    -> set age := age + 20;
    -> select concat('20年后的年龄是',age);
    -> end$
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> call p3()$
+------------------------------+
| concat('20年后的年龄是',age)             |
+------------------------------+
| 20年后的年龄是38                         |
+------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

存储过程可以用控制结构

if / else 控制结构

mysql> CREATE PROCEDURE p4 ()
    -> BEGIN
    ->  DECLARE age INT DEFAULT 18;
    ->  IF age >= 18 THEN
    ->          SELECT'已成年';
    ->  ELSE
    ->          SELECT '未成年';
    ->  END IF;
    -> end$
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> call p4()$
+--------+
| 已成年      |
+--------+
| 已成年      |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.02 sec)

while 控制结构

//求1~100的和

mysql> CREATE PROCEDURE p6 ()
    -> BEGIN
    ->  DECLARE total INT DEFAULT 0;
    ->  DECLARE num INT DEFAULT 0;
    ->
    ->  WHILE num < 100 DO
    ->          SET num := num + 1;
    ->          SET total := total + num;
    ->  END WHILE;
    ->  select total;
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p6$
+-------+
| total |
+-------+
|  5050 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

case 控制结构


create procedure p9()
begin
	declare pos int default 0;
	set  pos := floor(1+rand()*4);
	case pos 
	when 1 then select '数字1';
	when 2 then select '数字2';
	when 3 then select '数字3';
	else select '其他数字';
	end case;
end$

mysql> call p9()$
+-------+
| 数字1     |
+-------+
| 数字1     |
+-------+
1 row in set (0.00 sec)

mysql> call p9()$
+-------+
| 数字3     |
+-------+
| 数字3     |
+-------+
1 row in set (0.00 sec)

repeat 循环控制结构

mysql> create procedure p10()
    -> begin
    ->  declare sum int default 0;
    ->  declare n int default 0;
    ->  repeat
    ->          set n := n + 1;
    ->          set sum := sum + n;
    ->  until n >= 100 end repeat;
    ->  select sum;
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p10()$
+------+
| sum  |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

存储过程的括号里可以声明参数，语法为： [ in / out / inout] 参数名参数类型，其中
in ，往过程里输入值
out ，往外输出值
inout，即可往里输入值，也可往外输出值

//普通传参（默认in）

mysql> CREATE PROCEDURE p5 (width int,height int)
    -> begin
    ->          select concat('你的面积是',width*height) as area;
    ->
    ->          if width>height then
    ->                  select '胖';
    ->          elseif width<height then
    ->                  select '瘦';
    ->          else
    ->                  select '方';
    ->          end if;
    ->
    -> end$
Query OK, 0 rows affected, 4 warnings (0.00 sec)


mysql> call p5(3,4)$
+--------------+
| area         |
+--------------+
| 你的面积是12           |
+--------------+
1 row in set (0.00 sec)

+----+
| 瘦   |
+----+
| 瘦   |
+----+

Query OK, 0 rows affected, 4 warnings (0.04 sec)

//in和out

mysql> CREATE PROCEDURE p7 (in n int,out m int)
    -> BEGIN
    ->  declare num int DEFAULT 0;
    ->  set m = 0;
    ->  while num < 10 do
    ->                  set num := n + 1;
    ->                  set m := m + num;
    ->  end while;
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p7(100,@m)$
Query OK, 0 rows affected (0.00 sec)

mysql> select @m$
+------+
| @m   |
+------+
|  101 |
+------+
1 row in set (0.00 sec)

//inout 

mysql> CREATE PROCEDURE p8 (inout age int)
    -> BEGIN
    ->  set age := age + 10;
    -> end$
Query OK, 0 rows affected (0.00 sec)

//注意：直接传参会报错
mysql> call p8(24)$
ERROR 1414 (42000): OUT or INOUT argument 1 for routine test2.p8 is not a variale or NEW pseudo-variable in BEFORE trigger

//应该先在过程外设置变量并赋值，然后把变量传进去
mysql> set @curage = 11$
Query OK, 0 rows affected (0.00 sec)

mysql> call p9(@curage)$
Query OK, 0 rows affected (0.00 sec)

mysql> select @curage$
+---------+
| @curage |
+---------+
|      21 |
+---------+
1 row in set (0.00 sec)

cursor 游标
游标是，1条sql对应N条结果集的资源，取出资源的接口 / 句柄
沿着游标可以一次取出一行 (类似指针，取出一行后指向下一行)

声明： declare 游标名 cursor for select_statement;
打开：open 游标名
取值：fetch 游标名 into var1，var2[,...]
关闭：close 游标名

注意：游标的意义在于，可以对每取出一行的数据做复杂的判断处理，不只是简单的取出数据而已

//当游标指向的行没有数据时会报错

mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   34 |
|    2 | dog  |  255 |
|    3 | pig  |  255 |
+------+------+------+
3 rows in set (0.10 sec)

mysql> create procedure p12()
    -> begin
    ->
    ->  declare row_gid int;
    ->  declare row_name varchar(20);
    ->  declare row_num int;
    ->
    ->  declare getg cursor for select gid,`name`,num from g;
    ->
    ->  open getg;
    ->
    ->  fetch getg into row_gid,row_name,row_num;
    ->  select row_gid,row_name,row_num;
    ->
    ->  fetch getg into row_gid,row_name,row_num;
    ->  select row_gid,row_name,row_num;
    ->
    ->  fetch getg into row_gid,row_name,row_num;
    ->  select row_gid,row_name,row_num;
    ->
    ->  fetch getg into row_gid,row_name,row_num;
    ->  select row_gid,row_name,row_num;
    ->
    ->  close getg;
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p12()$
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       1 | cat      |      34 |
+---------+----------+---------+
1 row in set (0.01 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       2 | dog      |     255 |
+---------+----------+---------+
1 row in set (0.03 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.05 sec)

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

//取出表中每一行数据

mysql> create procedure p14()
    -> begin
    ->
    ->  declare row_gid int;
    ->  declare row_name varchar(20);
    ->  declare row_num int;
    ->
    ->  declare cnt int default 0;
    ->  declare i int default 0;
    ->
    ->  declare getg cursor for select gid,`name`,num from g;
    ->
    ->  select  count(*) into cnt from g;
    ->
    ->  open getg;
    ->
    ->  repeat
    ->          set i := i+1;
    ->          fetch getg into row_gid,row_name,row_num;
    ->          select row_gid,row_name,row_num;
    ->  until i >= cnt end repeat;
    ->
    ->  close getg;
    ->
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p14()$
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       1 | cat      |      34 |
+---------+----------+---------+
1 row in set (0.01 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       2 | dog      |     255 |
+---------+----------+---------+
1 row in set (0.03 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.07 sec)

handler 越界标识

游标取值越界时可以用标识来结束

语法为：declare continue/exit/undo handler for NOT FOUND statement;
continue 触发后，后面的语句继续执行
exit 触发后，后面的语句不再执行
undo触发后，前面的语句撤销（mysql不支持）

//用 declare continue handler 来操作一个越界标识
//这里是个逻辑有问题的查询语句

mysql> create procedure p15()
    -> begin
    ->
    ->  declare row_gid int;
    ->  declare row_name varchar(20);
    ->  declare row_num int;
    ->
    ->  declare mark int default 1; -- 定义标识为1
    ->  declare getg cursor for select gid,`name`,num from g;
    ->  declare continue handler for NOT FOUND set mark := 0; -- 当repeat查
到数据时,mark将赋值为0
    ->
    ->  open getg;
    ->
    ->  repeat
    ->          fetch getg into row_gid,row_name,row_num;
    ->          select row_gid,row_name,row_num;
    ->  until mark=0 end repeat; -- 当标识为0结束循环
    ->
    ->  close getg;
    ->
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p15()$
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       1 | cat      |      34 |
+---------+----------+---------+
1 row in set (0.02 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       2 | dog      |     255 |
+---------+----------+---------+
1 row in set (0.04 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.06 sec)

//最后一行重复取出
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.08 sec)

Query OK, 0 rows affected, 1 warning (0.09 sec)

//报错变为警告
mysql> show warnings$
+-------+------+-----------------------------------------------------+
| Level | Code | Message                                             |
+-------+------+-----------------------------------------------------+
| Error | 1329 | No data - zero rows fetched, selected, or processed |
+-------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

上例问题出现在continue
当游标从第三行指向第四行时，发现没有数据，触发 handler 的 NOT FOUND
于是 mark 赋值为0，但是 handler 为 continue，语句还要往这句 handler 之后执行，于是还要查询一次最后一行
（游标已经在第四行，在NOT FOUND时，时光倒流，游标倒退一行？）

把continue改为exit即可，即不再执行后面的语句

//用 declare exit handler 来操作一个越界标识

mysql> create procedure p16()
    -> begin
    ->
    ->  declare row_gid int;
    ->  declare row_name varchar(20);
    ->  declare row_num int;
    ->
    ->  declare mark int default 1; -- 定义标识为1
    ->  declare getg cursor for select gid,`name`,num from g;
    ->  declare exit handler for NOT FOUND set mark := 0; -- 当repeat查询不到数据时,mark将赋值为0
    ->
    ->  open getg;
    ->
    ->  repeat
    ->          fetch getg into row_gid,row_name,row_num;
    ->          select row_gid,row_name,row_num;
    ->  until mark=0 end repeat; -- 当标识为0结束循环
    ->
    ->  close getg;
    ->
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p16()$
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       1 | cat      |      34 |
+---------+----------+---------+
1 row in set (0.00 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       2 | dog      |     255 |
+---------+----------+---------+
1 row in set (0.01 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.02 sec)

//警告仍然存在（可忽略？）
mysql> show warnings$
+-------+------+-----------------------------------------------------+
| Level | Code | Message                                             |
+-------+------+-----------------------------------------------------+
| Error | 1329 | No data - zero rows fetched, selected, or processed |
+-------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

在循环外fetch一次也可避免最后一行查询两次的问题


mysql> create procedure p17()
    -> begin
    ->
    ->  declare row_gid int;
    ->  declare row_name varchar(20);
    ->  declare row_num int;
    ->
    ->  declare mark int default 1; 
    ->  declare getg cursor for select gid,`name`,num from g;
    ->  declare continue handler for NOT FOUND set mark := 0; 
    ->
    ->  open getg;
    ->  fetch getg into row_gid,row_name,row_num; //先在repeat外fetch一次，判断是否为NULL（如果为null则跑到not found去）
    ->
    ->  repeat
    ->          select row_gid,row_name,row_num;  //先输出列表再查询
    ->          fetch getg into row_gid,row_name,row_num;
    ->  until mark=0 end repeat; 
    ->
    ->  close getg;
    ->
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p17()$
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       1 | cat      |      34 |
+---------+----------+---------+
1 row in set (0.00 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       2 | dog      |     255 |
+---------+----------+---------+
1 row in set (0.02 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.04 sec)

Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql>

用 while 也行

mysql> create procedure p18()
    -> begin
    ->
    ->  declare row_gid int;
    ->  declare row_name varchar(20);
    ->  declare row_num int;
    ->
    ->  declare mark int default 1; 
    ->  declare getg cursor for select gid,`name`,num from g;
    ->  declare continue handler for NOT FOUND set mark := 0; 
    ->
    ->  open getg;
    ->  fetch getg into row_gid,row_name,row_num;
    ->
    ->  while mark = 1 do   //repeat改为while
    ->          select row_gid,row_name,row_num;
    ->          fetch getg into row_gid,row_name,row_num;
    ->  end while; 
    ->
    ->  close getg;
    ->
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p18()$
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       1 | cat      |      34 |
+---------+----------+---------+
1 row in set (0.00 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       2 | dog      |     255 |
+---------+----------+---------+
1 row in set (0.02 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.04 sec)

十一、权限

权限检查原理

用户登录mysql服务器分为两个阶段：
1.是否有权限连接服务器
2.是否有权限执行某些操作（如select，update等）

对于1：
服务器判断用户是否有权限连接服务器，依据三个参数
主机host，用户user，密码password

这三个参数的信息，存储在mysql.user表中

mysql> use mysql
Database changed
mysql> select host,user,password from user;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  |                                           |
| 127.0.0.1 | root  |                                           |
| ::1       | root  |                                           |
| %         | user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| %         | user  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+-------+-------------------------------------------+
5 rows in set (0.04 sec)

修改主机域名和用户密码

mysql>update user set host='192.168.1.118' where user='root';  //修改主机域名
mysql>update user set password=password('123') where user='root';  //修改用户密码
mysql>flush privileges;  //必须冲刷权限才能生效，因为权限表载入内存

全局授权与收回

用户登录mysql服务器时，依次会经过如下表的权限检查：
mysql库下的 user表 -> db表 -> tables_pri表

user表，管理用户是否能连接服务器的权限
db表，管理用户是否有操作某库的权限
tables_priv表，管理用户是否有操作某表的权限

新增用户：grant [权限1，权限2，权限3...] on *.* to user@'host' identified by 'password'

常用的表权限有 all，create，drop，insert，delete，update，select

//创建并授权一个mysql新用户

//root用户登录
C:\Users\Administrator>mysql -hlocalhost -uroot -p

有库下的，所有表的，所有权限
mysql> grant all on *.* to ywc@'192.168.1.%' identified by '123456';
Query OK, 0 rows affected (0.08 sec)
//创建用户，主机为 192.168.1.% ,用户名为 ywc ,密码为 123456 ，并授权用户 所
//	all：所有权限
//	on *.*：所有库及其下所有表
//	to ywc@'192.168.1.%': 用户ywc和主机192.168.1.%（%表示所有）
//	identified by '123456'：密码123456


mysql> select host,user,password from user where user='ywc';
+-------------+-------+-------------------------------------------+
| host        | user  | password                                  |
+-------------+-------+-------------------------------------------+
| 192.168.1.% | ywc   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------+-------+-------------------------------------------+
6 rows in set (0.00 sec)

//登录新用户ywc
C:\Users\Administrator>mysql -h192.168.1.118 -uywc -p123456

//查看当前用户
mysql> select user();
+----------------+
| user()         |
+----------------+
| ywc@ywcmoon |
+----------------+
1 row in set (0.00 sec)


mysql> use mysql
Database changed

//查看用户ywc的权限
mysql> select * from user where user='ywc' \G
*************************** 1. row ***************************
                  Host: 192.168.1.%
                  User: ywc
              Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin:
 authentication_string: NULL
1 row in set (0.00 sec)

//测试用户ywc的权限
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql> use test1;
Database changed

mysql> create table t1(id int);
Query OK, 0 rows affected (0.45 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.12 sec)

撤消权限：revoke all on *.* from ywc@'192.168.1.%';

//root用户登录
C:\Users\Administrator>mysql -hlocalhost -uroot -p

//撤消用户ywc的权限
mysql> revoke all on *.* from ywc@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.13 sec)

mysql> select * from user where user='ywc' \G
*************************** 1. row ***************************
                  Host: 192.168.1.%
                  User: ywc
              Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin:
 authentication_string: NULL
1 row in set (0.00 sec)

//ywc用户登录
C:\Users\Administrator>mysql -h192.168.1.118 -uywc -p123456

//Access denied 拒绝访问
mysql> use mysql;
ERROR 1044 (42000): Access denied for user 'ywc'@'192.168.1.%' to database 'mysq
l'

非全局授权与收回

针对某个库做授权

上面的授权和撤销授权都是全局性的

下面将在指定库及其下的表做授权

//root用户登录
C:\Users\Administrator>mysql -hlocalhost -uroot -p

//创建用户ywc,只授权test库
mysql> grant all on test.* to ywc@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)

//用户ywc在全局上没有权限
mysql> select * from user where user='ywc' \G
*************************** 1. row ***************************
                  Host: 192.168.1.%
                  User: ywc
              Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin:
 authentication_string: NULL
1 row in set (0.00 sec)

//用户ywc在test库上有所有权
mysql> select * from db \G
*************************** 1. row ***************************
                 Host: 192.168.1.%
                   Db: test
                 User: ywc
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y

//重新登录
C:\Users\Administrator>mysql -h192.168.1.118 -uywc -p123456

//只能访问test库
mysql> use mysql
ERROR 1044 (42000): Access denied for user 'ywc'@'192.168.1.%' to database 'mysql'

mysql> use test;
Database changed

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

//具有test库下所有表的操作权
mysql> create table t1(id int);
Query OK, 0 rows affected (0.10 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.04 sec)

撤销部分授权

//root用户登录
C:\Users\Administrator>mysql -hlocalhost -uroot -p

mysql> revoke all on test.* from ywc@'192.168.1.%';
Query OK, 0 rows affected (0.01 sec)

//数据库级别的授权信息没有了
mysql> select * from db \G
empty set(0.00 sec)

针对表做授权

//root账户

//授权用户ywc对test2库的account表有select,insert,update权限
mysql> grant select,insert,update on test2.account to ywc@'192.168.1.%';
Query OK, 0 rows affected (0.02 sec)

//ywc用户


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| test2              |
+--------------------+
3 rows in set (0.00 sec)

mysql> use test2;
Database changed

//test2库下有多张表，但只能操作account表
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| account         |
+-----------------+
1 row in set (0.00 sec)

//具有account表的的增,改,查权限，没有删除权限
mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |   14.98 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+
3 rows in set (0.10 sec)

mysql> delete from account;
ERROR 1142 (42000): DELETE command denied to user 'ywc'@'qicheng012' for table 'account'

//root用户

mysql> use mysql

//db表中没有用户ywc的信息
mysql> select * from db \G
empty set

//table_priv 表中有用户ywc对test2库的account表有Select,Insert,Update权限
mysql> select * from table_priv \G
*************************** 3. row ***************************
       Host: 192.168.1.%
         Db: test2
       User: ywc
 Table_name: account
    Grantor: root@localhost
  Timestamp: 2018-05-26 11:24:27
 Table_priv: Select,Insert,Update
Column_priv:

MySQL 8.0 Reference Manual：GRANT Syntax