MySQL索引、事务与存储引擎

一、权限表

1.user表

User表是MySQL中最重要的一个权限表，记录允许连接到服务器的帐号信息，里面的权限是全局级的。

2.db表和host表

db表和host表是MySQL数据中非常重要的权限表。db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。host表中存储了某个主机对数据库的操作权限，配合db权限表对给定主机上数据库级操作权限做更细致地控制。

3.tables_priv表和columns_priv表

tables_priv表用来对表设置操作权限。

columns_priv表用来对表的某一列设置权限。

4.procs_priv表

procs_priv表可以对存储过程和存储函数设置操作权限。

二、账户管理

1.登录和退出MySQL服务器

mysql命令的常用参数以及登录和退出mysql服务器的方法。

【例13.1】使用root用户登录到本地mysql服务器的test库中
 
mysql -u root -p -h localhost test

【例13.2】使用root用户登录到本地mysql服务器的test库中，执行一条查询语句
mysql -u root -p -h localhost test -e "DESC person;"

2.新建普通用户

（1）使用CREATE USER或GRANT语句。

【例13.3】使用CREATE USER创建一个用户，用户名是jeffrey，密码是mypass，主机名是localhost
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

【例13.4】使用GRANT语句创建一个新的用户testUser，密码为testpwd。用户 testUser对所有的数据有查询和更新权限，并授于对所有数据表的SELECT和UPDATE权限
GRANT SELECT,UPDATE  ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'testpwd';    /*创建账户并授予权限*/
SELECT Host,User,Select_priv,Update_priv, FROM mysql.user where user='testUser';  /*查看账户权限信息*/

（2）直接操作MySQL授权表。

【例13.5】使用INSERT创建一个新账户，其用户名称为customer1，主机名称为localhost，密码为customer1：
INSERT INTO user (Host,User,Password) VALUES('localhost','customer1',PASSWORD('customer1'));

3.删除普通用户

（1）使用DROP USER语句删除用户

【例13.6】使用DROP USER删除用户'jeffrey'@'localhost'
DROP USER 'jeffrey'@'localhost';

（2）使用DELETE语句删除用户

【例13.7】使用DELETE删除用户'customer1'@'localhost'
DELETE FROM mysql.user WHERE host='localhost' and user='customer1';

4.root用户修改自己的密码

（1）使用mysqladmin命令在命令行指定新密码

【例13.8】使用mysqladmin将root用户的密码修改为“rootpwd”
mysqladmin -u root -p password "123456"

（2）修改mysql数据库的user表

【例13.9】使用UPDATE语句将root用户的密码修改为“rootpwd2”：
UPDATE mysql.user set Password=password("rootpwd2")
WHERE User="root" and Host="localhost";

（3）使用SET语句修改root用户的密码

【例13.10】使用SET语句将root用户的密码修改为“rootpwd3”：
SET PASSWORD=password("rootpwd3");

5.root用户修改普通用户密码

（1）使用SET语句修改普通用户的密码

【例13.11】使用SET语句将testUser用户的密码修改为“newpwd”：
SET PASSWORD FOR 'testUser'@'localhost'=password("newpwd");

（2）使用UPDATE语句修改普通用户的密码

【例13.12】使用UPDATE语句将testUser用户的密码修改为“newpwd2”：
UPDATE   mysql.user set Password=PASSWORD("newpwd2")
WHERE User="testUser" and Host="localhost";

（3）使用GRANT语句修改普通用户密码

【例13.13】使用GRANT语句将testUser用户的密码修改为“newpwd3”：
GRANT USAGE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'newpwd3';

6.普通用户修改密码

普通用户登录MySQL服务器后，通过SET语句设置自己的密码。
SET PASSWORD = PASSWORD(‘newpassword’);

【例13.14】testUser用户使用SET语句将自身的密码修改为“newpwd4”：
SET PASSWORD = PASSWORD("newpwd4");

7.root用户密码丢失的解决办法

（1）使用--skip-grant-tables选项启动MySQL服务

mysql start-mysqld --skip-grant-tables

（2）使用root用户登录和重新设置密码

mysql -u root
update mysql.user set password=password('mypass') where user='root' and host='localhost';

（3）加载权限表

flush privileges;

三、权限管理

1.MySQL的各种权限

账户权限信息被存储在mysql数据库的user、db、host、tables_priv、columns_priv和procs_priv表中。在MySQL启动时，服务器将这些数据库表内容读入内存。

2.授权

授权就是为某个用户授于权限。合理的授权可以保证数据库的安全。MySQL中可以使用GRANT语句为用户授于权限。

【例13.15】使用GRANT语句创建一个新的用户grantUser，密码为“grantpwd”。用户grantUser对所有的数据有查询、插入权限，并授于GRANT权限。GRANT语句及其执行结果如下：
MySQL> GRANT SELECT,INSERT ON *.* TO 'grantUser'@'localhost'  IDENTIFIED BY 'grantpwd' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)
结果显示执行成功，使用SELECT语句查询用户testUser2的权限：
MySQL> SELECT Host,User,Select_priv,Insert_priv, Grant_priv FROM mysql.user where user='grantUser';
+-----------+------------+-------------+--------------+-------------+
| Host    | User    | Select_priv | Insert_priv | Grant_priv |
+-----------+------------+-------------+--------------+--------------+
| localhost | testUser2 | Y        | Y        | Y        |
+-----------+------------+-------------+--------------+--------------+
1 row in set (0.00 sec)

3.收回权限

收回权限就是取消已经赋于用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用REVOKE语句取消用户的某些权限。

【例13.16】使用REVOKE语句取消用户testUser的更新权限。REVOKE语句及其执行结果如下：
MySQL> REVOKE UPDATE ON *.* FROM 'testUser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
执行结果显示执行成功，使用SELECT语句查询用户test的权限：
MySQL> SELECT Host,User,Select_priv,Update_priv,Grant_priv FROM MySQL.user where user='testUser';
+-----------+------+--------------+---------------+--------------+
| Host    | User | Select_priv | Update_priv | Grant_priv |
+-----------+------+--------------+---------------+--------------+
| localhost | test  | Y       | N         | Y        |
+-----------+------+-------------+---------------+--------------+
1 row in set (0.00 sec)

4.查看权限

SHOW GRANTS语句可以显示指定用户的权限信息，使用SHOW GRANT语句查看账户信息。
SHOW GRANTS FOR ‘user’@’host’ ;

【例13.17】使用SHOW GRANTS语句查询用户testUser的权限信息。SHOW GRANTS语句及其执行结果如下：
MySQL> SHOW GRANTS FOR 'testUser'@'localhost';
+------------------------------------------------------------------------------------------------------------------------------+
| Grants for testUser@localhost                                                            |
+------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'testUser'@'localhost' IDENTIFIED BY PASSWORD 
'*53835E70E1FC57BE1A455169C761A8778D307C81' WITH GRANT OPTION                   |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. 案例操作过程
  打开MySQL客户端工具，输入登录命令，登录MySQL。
C:\>mysql -u root -p
Enter password: **
输入正确密码，按回车，出现欢迎信息表示登录成功。
  选择mysql数据库为当前数据库。
MySQL> use mysql;
Database changed
出现Database changed信息表明切换数据库成功。
创建新账户，用户名称为newAdmin，密码为pw1，允许其从本地主机访问MySQL。
使用GRANT语句创建新账户，创建过程如下：
MySQL> GRANT SELECT, UPDATE(id, name, age)
    -> ON test_db.person_old
    -> TO 'newAdmin'@'localhost' IDENTIFIED BY 'pw1'
    -> WITH MAX_CONNECTIONS_PER_HOUR 30;
Query OK, 0 rows affected (0.06 sec)
提示消息可以看到，语句执行成功。
  分别从user表中查看新账户的账户信息，从tables_priv和columns_priv表中查看权限信息。
用户账户创建完成之后，账户信息已经保存在user表，权限信息则分别保存在tables_priv和columns_priv中，查询user名称为newAdmin的账户信息，执行过程如下：
SELECT host, user, select_priv, update_priv FROM user WHERE user='newAdmin';
 
SELECT host, db, user, table_name, table_priv, column_priv 
FROM tables_priv WHERE user='newAdmin';
 
SELECT host, db, user, table_name, column_name, column_priv 
FROM columns_priv WHERE user='newAdmin';
3条SQL语句的查询结果分别如下：
MySQL> SELECT host, user, select_priv, update_priv FROM user WHERE user='newAdmin';
+-----------+--------------+-------------+--------------+
| host    | user      | select_priv | update_priv |
+-----------+--------------+-------------+--------------+
| localhost | newAdmin | N       | N         |
+-----------+--------------+-------------+--------------+
1 row in set (0.00 sec)
 
MySQL> SELECT host, db, user, table_name, table_priv, column_priv
    -> FROM tables_priv WHERE user='newAdmin';
+-----------+-----+-------------+-------------+-------------+---------------+
| host    | db  | user     | table_name | table_priv | column_priv |
+-----------+-----+-------------+-------------+-------------+---------------+
| localhost | test | newAdmin | person   | Select    | Update     |
+-----------+-----+-------------+-------------+-------------+---------------+
1 row in set (0.00 sec)
 
MySQL> SELECT host, db, user, table_name, column_name, column_priv
    -> FROM columns_priv WHERE user='newAdmin';
+-----------+-----+-------------+-------------+-----------------+----------------+
| host    | db  | user     | table_name | column_name | column_priv |
+-----------+-----+-------------+-------------+-----------------+----------------+
| localhost | test | newAdmin | person   | id          | Update      |
| localhost | test | newAdmin | person   | name        | Update     |
| localhost | test | newAdmin | person   | age         | Update      |
+-----------+-----+-------------+-------------+-----------------+----------------+
3 rows in set (0.00 sec)

使用SHOW GRANTS语句查看newAdmin的权限信息。
查看newAdmin账户的权限信息，输入语句如下：
SHOW GRANTS FOR 'newAdmin'@'localhost';
执行结果如下：
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for newAdmin@localhost                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'newAdmin'@'localhost' IDENTIFIED BY PASSWORD '*2B602296
A79E0A8784ACC5C88D92E46588CCA3C3' WITH MAX_CONNECTIONS_PER_HOUR 30    |
| GRANT SELECT, UPDATE (age, id, name) ON `test`.`person` TO 'newAdmin'@'localhost'      |
+----------------------------------------------------------------------------------
-------------------------------------+
2 rows in set (0.00 sec)

使用newAdmin用户登录MySQL。
退出当前登录，使用EXIT命令，语句如下：
MySQL> exit
Bye
使用newAdmin账户登录MySQL，语句如下：
MySQL -u newAdmin -p
Enter password: ***
输入密码正确后，出现“mysql>”提示符，登录成功。
  使用newAdmin用户查看test_db数据库中person_dd表中的数据。
newAdmin用户被授予test数据库中person表中3个字段上的查询权限，因此可以执行SELECT语句查看这几个字段的值，执行过程如下：
MySQL> SELECT * FROM test_db.person_dd LIMIT 5;
+----+----------+-----+--------------+
| id  | name  | age | info       |
+----+----------+-----+--------------+
|  1 | Green  |  21 | Lawyer   |
|  2 | Suse   |  22 | dancer    |
|  3 | Mary  |  24 | Musician  |
|  4 | Willam |  20 | sports man |
|  5 | Laura  |  25 | NULL    |
+----+----------+-----+--------------+
5 rows in set (0.00 sec)
可以看到，查询结果显示了表中的前5条记录。

使用newAdmin用户向person_dd表中插入一条新记录，查看语句执行结果。
插入新记录，输入语句如下：
INSERT INTO test_db.person_old(name, age,info) VALUES('gaga', 30);
执行结果如下：
ERROR 1142 (42000): INSERT command denied to user 'newAdmin'@'localhost' for table 
'person'
可以看到，语句不能执行，错误信息表明newAdmin用户不能对person表进行插入操作。因此，用户不可以执行没有被授权的操作语句。
  退出当前登录，使用root用户重新登录，收回newAdmin账户的权限。
输入退出命令：
exit
重新以root用户登录MySQL，并选择mysql数据库为当前数据库。
输入语句收回newAdmin账户的权限，执行过程如下：
REVOKE SELECT, UPDATE ON test.person FROM 'newAdmin'@'localhost';
执行结果如下：
MySQL> REVOKE SELECT, UPDATE ON test.person FROM 'newAdmin'@'localhost';
Query OK, 0 rows affected (0.00 sec)
  删除newAdmin的账户信息。
删除指定账户，可以使用DROP USER语句，输入如下：
DROP USER 'newAdmin'@'localhost';
语句执行成功之后，tables_priv和columns_priv中相关的记录将被删除。

四、访问控制

1.连接核实阶段

当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码验证身份来接受或拒绝连接。

user表

3个字段（Host，User和Password）

2.请求核实阶段

建立了连接之后，对在此连接上进来的每个请求，服务器检查用户要执行的操作，然后检查是否有足够的权限来执行它。

user、db、host、tables_priv或columns_priv表

访问控制分为两个阶段：

五、综合案例——综合管理用户权限

步骤1.打开MySQL客户端工具，输入登录命令，登录MySQL：

/*使用root用户登录mysql*/

mysql -u root -p

步骤2.将选择mysql数据库为当前数据库。

use mysql;

步骤3.创建新账户，用户名称为newAdmin，允许其从本地主机访问MySQL。

GRANT SELECT, UPDATE(id, name, age)

  ON test.person

  TO 'newAdmin'@'localhost' IDENTIFIED BY 'pw1'

  WITH MAX_CONNECTIONS_PER_HOUR 30;

步骤4.分别从user表中查看新账户的账户信息，从tables_priv和columns_priv表中查看权限信息。

/* 查看user表中账户信息*/

SELECT host, user, select_priv, update_priv FROM user WHERE user='newAdmin';

/*查看tables_priv表中权限信息*/

SELECT host, db, user, table_name, table_priv, column_priv

FROM tables_priv WHERE user='newAdmin';

/*查看columns_priv表中权限信息*/

SELECT host, db, user, table_name, column_name, column_priv

FROM columns_priv WHERE user='newAdmin';

步骤5.使用SHOW GRANTS语句查看newAdmin的权限信息

SHOW GRANTS FOR 'newAdmin'@'localhost';

步骤6.使用newAdmin用户登录MySQL

/*退出命令*/

exit

/*使用newAdmin 用户登录mysql*/

mysql -u newAdmin -p

步骤7.使用newAdmin用户查看test数据库中person表中的数据

SELECT * FROM test.person LIMIT 5;

步骤8.使用newAdmin用户向person表中插入一条新记录，查看语句执行结果。

INSERT INTO test.person(name, age,info) VALUES('gaga', 30);

步骤9.退出当前登录，使用root用户重新登录，收回newAdmin账户的权限。

/*退出命令*/

exit

/*使用root用户登录mysql*/

mysql -u root -p

REVOKE SELECT, UPDATE ON test.person FROM 'newAdmin'@'localhost';

步骤10.删除newAdmin的账户信息。

删除指定账户，可以使用DROP USER语句，输入如下：

DROP USER 'newAdmin'@'localhost';

MySQL 高级 SQL 语句

一、MySQL 进阶查询

创建测试数据库和表：

create database auth;
use auth
create table t1(id int(10), name char(20),level int(10));
insert into t1 value(10,'sagou',42);
insert into t1 value(8,'senoku',45);
insert into t1 value(15,'useless',47);
insert into t1 value(27,'guess',52);
insert into t1 value(199,'useless',48);
insert into t1 value(272,'Theshy',36);
insert into t1 value(298,'leslieF',40);
insert into t1 value(30,'shirley',58);
insert into t1 value(190,'zhangsan',48);
insert into t1 value(271,'lisi',52);
insert into t1 value(299,'wangwu',52);
insert into t1 value(31,'zhaoliu',58);
 
create table t2(id int(10), name char(20),level int(10));
insert into t2 value(10,'sagou',42);
insert into t2 value(8,'senoku',45);
insert into t2 value(15,'useless',47);
insert into t2 value(27,'guess',52);
insert into t2 value(199,'useless',48);
insert into t2 value(272,'Theshy',36);
insert into t2 value(298,'leslieF',40);
insert into t2 value(30,'shirley',58);
insert into t2 value(190,'zhangsan',48);
insert into t2 value(271,'lisi',52);
insert into t2 value(299,'wangwu',52);
insert into t2 value(31,'zhaoliu',58);

1.常用查询介绍

（1）按关键字排序

使用 SELECT 语句可以将需要的数据从 MySQL 数据库中查询出来，如果对查询的结果进行排序，该如何去实现呢？可以使用 ORDER BY 语句来完成排序，并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段，也可以针对多个字段。

【语法】
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ...  ASC|DESC;
 
【例1】SELECT 语 
句中如果没有指定具体的排序方式，则默认按 ASC 方式进行排序。DESC 是按降序方式进行排列。当然 ORDER BY 前面也可以使用 WHERE 子句对查询结果进一步过滤。
执行以下操作可查询等级大于等于 45 级的用户，并按降序进行排序
mysql> select id,name,level from t1 where level>=45 order by level desc;

【例2】ORDER BY 语句也可以使用多个字段来进行排序，当排序的第一个字段相同的记录有多条的情况下，这些多条的记录再按照第二个字段进行排序。
执行以下操作可查询等级在 45 级及以上的用户，并以 level降序排列和 id 降序排列
mysql> select id,name,level from t1 where level>=45 order by level desc, id desc;
 
 
备注：
 ORDER BY A,B desc 指 A 用升序，B 用降序； 
 ORDER BY A asc,B desc 指 A 用升序，B 用降序； 
 ORDER BY A desc,B desc 指 A 用降序，B 用降序；

（2）对结果进行分组

通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现。GROUP BY 从字面上看，是以 BY 后面的内容对查询出的数据进行分组，就是将一个“数据集”划分成若干个“小区域”，然后针对这些个“小区域”进行数据处理。GROUP BY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数（COUNT）、求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），这些聚合函数的用法在后面函数小节会有更详细的讲解。GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理

【语法】
SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name;
 
【例1】GROUP BY 除了配合聚合函数一起使用外，还可以引入 WHERE 子句。首先通过WHERE 过滤掉一部分不符合需求的查询结果，然后再对结果进行分组。如果有排序的需求，也可以引入 ORDER BY 语句。
执行以下操作即可统计等级在 45 级及以上，以等级为分组，每个等级有多少人
mysql> select count(name),level from t1 where level>=45 group by level;
 
【例2】而GROUP BY 结合 ORDER BY 即可实现分组并排序的查询。
查询等级在 45 级及以上，按等级进行分组，并将每个等级的人数按降序排序，具体操作如下所示
mysql> select count(name),level from t1 where level>=45 group by level order by count(name) desc;

（3）限制结果条目

在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录。有时候仅需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句

【语法】
SELECT column1, column2, ... FROM table_name LIMIT [offset,] number
 
【例1】LIMIT 子句是一种简单的分页方法，它的使用减少了数据结果的返回时间，提高了执行效率，也解决了由于数据量过大从而导致的性能问题。
执行以下操作即可查询表的前 3 个用户的信息
mysql> select id,name,level from t1 limit 3; 
 
【例2】LIMIT 子句的使用也可以结合 ORDER BY：先进行排序，然后再 LIMIT 限制固定的记录。也就是说 LIMIT 是放在最后的，将处理好的结果集按要求选出几行来。
将查询记录按等级 level 降序排列，只取前三条记录
mysql> select id,name,level from t1 order by level desc limit 3;
 
【例3】在显示结果的时候也可以不从第一行开始，引入 offset 参数。
执行以下操作即可从第 3 条记录开始显示之后的 3 条数据
mysql> select id,name,level from t1 limit 2,3;

（4）设置别名

在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了，增强可读性。

【列的别名语法】
SELECT column_name AS alias_nameFROM table_name;
 
【表的别名语法】
SELECT column_name(s)FROM table_name AS alias_name;

【例1】在统计表内所有记录共有多少条时，使用 count（*），这么写不便于识别，可以将其别名设置为 number
mysql> select count(*) as number from t1;
mysql>select count(*) number from t1;
 
【例2】执行以下操作即可将 t1 表的别名设置成 p。
mysql> select p.id,p.name from t1 as p limit 3;
mysql>select p.id,p.name from t1 p limit 3;
 
【例3】执行以下操作即可实现用一条 SQL语句完成在创建表 t1 的时候将 t1 表内的数据写入 t1表。
mysql> create table t1 as select * from t1; 
mysql>select count(*) from t1; 

（5）通配符

通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务

Ø %：百分号表示零个、一个或多个字符

Ø _：下划线表示单个字符

【例1】查询 t1 表内 name 字段分别以 s 开头的名字、以 s 结尾的名字和名字中间包含 es 的字段，具体操作如下所示
 
name 字段以 s 开头的记录
mysql> select id,name,level from t1 where name like 's%';
 
name 字段以 s 结尾的记录
mysql> select id,name,level from t1 where name like '%s';
 
name 字段中间含 es 的记录
mysql> select id,name,level from t1 where name like '%es%';

【例2】利用下划线替换表内 name 字段开头的字符、结尾的字符或者中间的字符
 
替换开头的一个字符
 
mysql> select id,name,level from t1 where name like '_uess';
 
 
替换结尾的四个字符
 
mysql> select id,name,level from t1 where name like 'use____';
 
 
替换中间的一个字符
 
mysql> select id,name,level from t1 where name like 'shi_ley';

【例3】name 字段中，开头有一个字符，接着是 es 两个字符，后面再跟着零个、一个或多个字符，从 t1 表中查询这样的数据
mysql> select id,name,level from t1 where name like '_es%';

（6）子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。

【例1】先查出等级大于等于 45级的 ID，然后在判断 t1 表内的 ID 是不是在这个结果集内，如果在就打印此行的名字和等级
mysql> select name,level from t1 where id in (select id from t1 where level>=45);
 
【例2】子查询还可以用在 INSERT 语句中。子查询的结果集可以通过 INSERT 语句插入到其他的表中。
例如，先清空之前使用的 t2 表，然后通过子查询的方式将 t1 的内容插入到 t2 表中。
 
mysql> truncate table t2;
mysql> select * from t2; 
mysql> insert into t2 select * from t1 where id in (select id from t1); 
mysql> insert into t2 select * from t1;
 
【例3】UPDATE 语句也可以使用子查询。UPDATE 内的子查询，在 set 更新内容时，可以是单独的一列，也可以是多列。
例如，执行以下操作即可通过子查询实现将等级大于等于 47的用户减去 7
mysql>select id,name,level from t2 where id=30;
 
mysql> update t2 set level = level - 7 where id in (select id from (select id from t2 where level >= 47) a); 
mysql> select id,name,level from t2 where id=30;
 
【例4】DELETE 也适用于子查询。
例如，先将原来 47 级的用户等级恢复，然后通过子查询的方式将 47 级用户删除。实现方式类似 UPDATE 的子查询
mysql> update t2 set level=47 where id=30; 
mysql> delete from t2 where id in (select id from (select id from t2 where level=47) a); 
mysql>select id,name,level from t2 where id=30;
 
【例5】除了以上所列举的 IN 操作符，在 IN 前面还可以添加 NOT，其作用与 IN 相反，表示否 
定（即不在子查询的结果集里面）。例如，执行以下操作即可查询用户等级不小于 45 级的 
用户
mysql> select name,level from t2 where id not in (select id from t2 where level < 45);
 
【例6】子查询上也可以使用比较运算符（=、<、>、>=、<=），这些运算符主要是对运算符 
前面的表达式和后面的子查询进行比较运算。例如，查询出名字是 shirley 的记录，并输出 
其 ID、名字和等级信息
mysql> select id,name,level from t2 where id = (select id from t2 where name='shirley');
 
【例7】EXIST 这个关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空， 
则返回 TRUE；反之，则返回 FALSE。例如，先通过子查询判断返回是否为 TRUE，如果 
用户 shirley 存在，则计算整个 t1 表的总记录数量，具体操作如下所示。
mysql> select count(*) as number from t1 where EXISTS (select id from t1 where name='shirley');

2.NULL值

在 SQL 语句使用过程中，经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失

的值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以使

用 NOT NULL 关键字，不使用则默认可以为空。

【例1】创建一个表 test，该表包含不设置 NOT NULL 的字段，然后向表中插入不同的 
记录值，其中包括 NULL 值和实际有值的记录，最后通过 SELECT 查询字段中包括 NULL 
和不包括 NULL 的记录值
 
CREATE TABLE test ( 
id int(10) NOT NULL AUTO_INCREMENT, 
NAME varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
level int(10) NOT NULL, 
coin int(32), 
PRIMARY KEY (id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
插入的记录中不包括 coin 字段
mysql> insert into test(name,level) values('aa',10);
 
插入的记录中包括 coin 字段
mysql>insert into test(name,level,coin) values('ab',20,100);
 
查询 coin 字段为空值的记录
mysql>select * from test where coin is null;
 
查询 coin 字段不为空的记录
mysql> select * from test where coin is not null;

备注：

NULL值和空值有什么区别：

• 空值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。
• IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是空值的。
• 空值的判断使用=’’或者<>’’来处理。
• 在通过 count()计算有多少记录数时，如果遇到 NULL 值会自动忽略掉，遇到空值会加入到记录中进行计算。

3.正则表达式

（1）以特定字符串开头的记录

【例】在 t1 表中查询以 zhang 开头的 name 字段并打印对应的 id、name 和 level 记录
mysql>select id,name,level from t1 where name REGEXP '^zhang';

（2）以特定字符串结尾的记录

【例】在 t1 表中查询以 ss 结尾的 name 字段并打印对应的 id、name 和 level 记录
mysql> select id,name,level from t1 where name REGEXP 'ss$';

（3）包含指定字符串的记录

【例】在 t1 表中查询包含 an 字符串的 name 字段并打印对应的 id、name 和 level记录
 
mysql>select id,name,level from t1 where name REGEXP 'an';

（4）以“.”代替字符串中的任意一个字符的记录

【例】在 t1 表中查询包含字符串 shir 与 ey，且两个字符串之间只有一个字符的name 字段并打印对应的 id、name 和 level 记录
mysql>select id,name,level from t1 where name REGEXP 'shir.ey';

（5）匹配包含或者关系的记录

【例】在 t1 表中查询包含字符串 on 或者 in 的 name 字段并打印对应的 id、name 
和 level 记录
mysql> select id,name,level from t1 where name REGEXP 'on|in';

（6）“*”匹配前面字符的任意多次

【例】在 t1 表中查询包含一个或者更多个连续的 o 的 name 字段并打印对应的 id、 
name 和 level 记录
mysql>select id,name,level from t1 where name REGEXP 'oo*';

（7）“+”匹配前面字符至少一次

【例】在 t1 表中查询包含一个或者更多个 o 的 name 字段并打印对应的 id、name 
和 level 记录
mysql>select id,name,level from t1 where name REGEXP 'oo+';

（8）匹配指定字符集中的任意一个

【例】在 t1 表中查询包含以 a-n 开头的 name 字段并打印对应的 id、name 和 
level 记录
mysql>select id,name,level from t1 where name REGEXP '^[a-n]';

4.运算符

（1）算术运算符

 

【例】以 SELECT 命令来实现最基础的加减乘除运算
mysql>select 1+2 as addition, 2-1 as subtraction, 2*3 as multiplication, 4/2 as division, 7%2 as remainder;

（2）比较运算符

【例1】等于运算符在数字、字符串和表达式上的使用
mysql> select 2=4,2='2','e'='e',(2+2)=(3+1),'r'=NULL;
 
【例2】关于数字、字符串和表达式的不等于运算符的使用
mysql>SELECT 'kgc'<>'bdqn', 1<>2, 3!=3, 2.5!=2, NULL<>NULL ;
 
【例3】关于大于、大于等于、小于、小于等于运算符的使用
mysql> select 5>4,'a'>'b',2>=3,(2+3)>=(1+2),4.4<3,1<2,'x'<='y',5<=5.5,'u'>=NULL;
 
【例4】关于数字、字符和 NULL 值的运用
mysql>select 2 IS NULL,'f' IS NOT NULL,NULL IS NULL;
 
【例5】判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间
mysql>select 4 BETWEEN 2 AND 6,5 BETWEEN 6 AND 8,'c' BETWEEN 'a' AND 'f';
 
【例6】若要判断一组数字或字母中哪个最小、哪个最大，可以通过使用 LEAST 和GREATEST 来实现
mysql>SELECT least(1,2,3),least('a','b','c'),greatest(1,2,3),greatest('a','b','c');
 
备注：
 LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL。 
 GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为 NULL，则返回结果就为 NULL。
 
 
【例7】判断某数字是否在一组数字中，也可判断某字符是否在一组字符中
mysql>SELECT 2 in (1,2,3,4,5),'c' not in ('a','b','c');
 
【例8】若要判断某字符串能否匹配成功，分单字符匹配和多字符匹配，也可以判断不匹 
配
mysql>SELECT 'bdqn' LIKE 'bdq_','kgc' LIKE '%c','etc' NOT LIKE '%th';

备注：

Ø IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0。

Ø NOT IN 判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0。

备注：

LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回 0。LIKE 支持两种通配符：’%’

用于匹配任意数目的字符，而’_’只能匹配一个字符。NOT LIKE 正好跟 LIKE 相反，如果没

有匹配成功则返回 1，反之返回 0。

（3）逻辑运算符

逻辑非：NOT 或 ！

逻辑与：AND 或 &&

逻辑或 ：R 或 ||

逻辑异或：XOR

【例1】逻辑非：对 非0值 和0值 分别作逻辑非运算
mysql>SELECT not 2,!3,not 0,!(4-4);
 
【例2】逻辑与：对非 0 值、0 值和 NULL 值分别作逻辑与运算
mysql>SELECT 2 AND 3,4 && 0,0 && NULL,1 AND NULL;
 
【例3】逻辑或：对非 0 值、0 值和 NULL 值分别作逻辑或运算
mysql>SELECT 2 OR 3,4 || 0,0 OR NULL,1 || NULL;
 
【例4】逻辑异或：对非0 值、0 值和 NULL 值分别作逻辑异或运算
mysql>SELECT 2 XOR 3,0 XOR 0,0 XOR 5,1 XOR NULL,NULL XOR NULL;

（4）位运算符

【例1】对数字进行按位与、或和取反运算，具体操作如下所示
mysql> SELECT 10 & 15, 10 | 15, 10 ^ 15, 5 &~1;

与运算规则：任何一个值为0，则结果就为0    1 & 1=1   1 & 0=0   0 & 0=0

或运算规则：任何一个值为1，则结果就为1    1 | 1 =1   1| 0 =1   0 | 0=0

参与^运算的两个二进制位不同时，结果为 1，相同时，结果为 0。

10 ^ 15：10 的补码为 1010，15 的补码为 1111，按位异或运算之后，结果为 0101

5 &~1：由于位取反运算符‘~’的级别高于位与运算符‘&’，因此先对 1 进行取反操作，结果为 63 个‘1’加一个‘0’，然后再与整数 5 进行与运算，结果为 0100，即整数 4。

备注：

常量 1 的补码为 63 个‘0‘加 1 个’1‘，位取反后就是 63 个’1‘加一个’0‘

备注：

补码为64位的二进制，比如十进制5，换算成二进制为101，在左侧补出61个0，就成了5的补码

【例2】对数字进行左移或右移的运算
mysql>SELECT 1<<2, 2<<2,10>>2,15>>2;

5.连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。使用较多的连接查询包括：内连接、左连接和右连接。

创建测试用表：
CREATE TABLE `a_t1` ( 
`a_id` int(11) DEFAULT NULL, 
`a_name` varchar(32) DEFAULT NULL, 
`a_level` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
CREATE TABLE `b_t1` ( 
`b_id` int(11) DEFAULT NULL, 
`b_name` varchar(32) DEFAULT NULL, 
`b_level` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
insert into a_t1(a_id, a_name, a_level) values(1, 'aaaa', 10); 
insert into a_t1(a_id, a_name, a_level) values(2, 'bbbb', 20); 
insert into a_t1(a_id, a_name, a_level) values(3, 'cccc', 30); 
insert into a_t1(a_id, a_name, a_level) values(4, 'dddd', 40); 
insert into b_t1(b_id, b_name, b_level) values(2, 'bbbb', 20); 
insert into b_t1(b_id, b_name, b_level) values(3, 'cccc', 30); 
insert into b_t1(b_id, b_name, b_level) values(5, 'eeee', 50); 
insert into b_t1(b_id, b_name, b_level) values(6, 'ffff', 60);

（1）内连接

【例】在刚才创建的 a_t1 和 b_t1 表中使用内连接查询出通过判断 a_id 和 b_id 相等，包含在两个表内的部分
mysql>select a_id,a_name,a_level from a_t1 inner join b_t1 on a_id=b_id;

（2）左连接

【例】从a_t1 和 b_t1 表中，查询出 a_t1 表中所有内容，并且查询出通过 a_id 和 b_id 
相等判断出的 b_t1 中的部分
mysql>select * from a_t1 a left join b_t1 b on a.a_id=b.b_id;

（3）右连接

【例】从 a_t1 和 b_t1 表中，查询出在 b_t1 表内的所有记录，并且通过判断 a_id 和 b_id 相等，在 a_t1 表内的部分
mysql>select * from a_t1 a right join b_t1 b on a.a_id=b.b_id;

二、数据库函数

MySQL 数据库函数提供了能够实现各种功能的方法，使我们在查询记录时能够更高效的输出。MySQL 内建了很多函数，常用的包括数学函数、聚合函数、字符串函数和日期时间函数。

1.数学函数

abs(x) ：返回 x 的绝对值

rand() ：返回 0 到 1 的随机数

mod(x,y) ：返回 x 除以 y 以后的余数

power(x,y) ：返回 x 的 y 次方

round(x) ：返回离 x 最近的整数

round(x,y) ：保留 x 的 y 位小数四舍五入后的值

truncate(x,y) ：返回数字 x 截断为 y 位小数的值

ceil(x) ：返回大于或等于 x 的最小整数

floor(x) ：返回小于或等于 x 的最大整数

least(x1,x2...) ：返回集合中最小的值

sqrt(x) ：返回 x 的平方根

greatest(x1,x2...) ：返回集合中最大的值

【例】
mysql>select abs(-1), rand(), mod(5,3), power(2,3), round(1.89);
mysql>select round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);

2.聚合函数

MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集

中概括而设计的，这些函数被称作聚合函数。

avg() ：返回指定列的平均值

count() ：返回指定列中非 NULL 值的个数

min() ：返回指定列的最小值

max() ：返回指定列的最大值

sum(x) ：返回指定列的所有值之和

【例】
mysql>select sum(level) as sum_level from t1; 
mysql>select max(level) as max_level from t1; 
mysql>select min(level) as min_level from t1;

3.字符串函数

length(x)   返回字符串 x 的长度第

trim()   移除字符串两侧的空白字符或其他预定义字符

concat(x,y)   将提供的参数 x 和 y 拼接成一个字符串

upper(x)   将字符串 x 的所有字母变成大写字母

lower(x)   将字符串 x 的所有字母变成小写字母

left(x,y)   返回字符串 x 的前 y 个字符

right(x,y)   返回字符串 x 的后 y 个字符

repeat(x,y)   将字符串 x 重复 y 次

space(x)   返回 x 个空格

replace(x,y,z)   将字符串 z 替代字符串 x 中的字符串 y

strcmp(x,y)   比较 x 和 y，返回的值可以为-1,0,1

substring(x,y,z)   获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串

reverse(x)   将字符串 x 反转

【例】
mysql>select length('bdqn'), trim(' yellow '), concat('bd', 'qn'), upper('abc'), right('hello', 3);
mysql>select repeat('kgc', 2), replace('hello', 'll', 'kgc'), strcmp(4, 5), substring('bjbdqn', 4, 2), reverse('hello');

4.日期时间函数

curdate()  返回当前时间的年月日

curtime()  返回当前时间的时分秒

now()   返回当前时间的日期和时间

month(x)  返回日期 x 中的月份值

week(x)   返回日期 x 是年度第几个星期

hour(x)   返回 x 中的小时值

minute(x)   返回 x 中的分钟值

second(x)   返回 x 中的秒钟值

dayofweek(x)   返回 x 是星期几，1 星期日，2 星期一

dayofmonth(x)   计算日期 x 是本月的第几天

dayofyear(x)   计算日期 x 是本年的第几天

【例】
mysql>select curdate(), curtime(), now(), month('2020-02-09'), week('2020-02-09'), hour('21:13:53'); 
mysql>select minute('21:13:53'), second('21:13:53'), dayofweek('2020-02-09'), dayofmonth('2020-02-09'), dayofyear('2020-02-09');

三、存储过程

1.存储过程简介

MySQL 数据库存储过程是一组为了完成特定功能的 SQL 语句的集合，存储过程在数据库中创建并保存，它不仅仅是 SQL 语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。

备注：存储过程的优点

• 存储过程执行一次后，生成的二进制代码就驻留在缓冲区，之后如果再次调用的话，将直接调用二进制代码，使得存储过程的执行效率和性能得到大幅提升。
• 存储过程是 SQL 语句加上控制语句的集合，有很强的灵活性，可以完成复杂的运算。
• 存储过程存储在服务器端，客户端调用时，直接在服务器端执行，客户端只是传输的调用语句，从而可以降低网络负载。
• 存储过程被创建后，可以多次重复调用，它将多条 SQL 封装到了一起，可随时针对 SQL语句进行修改，不影响调用它的客户端。
• 存储过程可以完成所有的数据库操作，也可以通过编程的方式控制数据库的信息访问权限。

2.创建存储过程

【例】通过存储过程查询 t1 表的三条数据，存储过程是不带参数的
mysql> DELIMITER $$ 
mysql> CREATE PROCEDURE t1Role() 
BEGIN 
SELECT id,name,level from t1 limit 3; 
END $$
 
备注：
DELIMITER：用于定义SQL语句的结束符，默认的结束符是分号。
 
mysql> DELIMITER ; 
mysql> call t1Role();

【例】通过存储过程查询 t1 表中某一条记录，存储过程是带参数的
mysql>DELIMITER $$ 
mysql>CREATE PROCEDURE GetRole(IN inname VARCHAR(16)) 
BEGIN 
SELECT id,name,level from t1 where name=inname; 
END $$
 
 
mysql>DELIMITER ; 
mysql>call GetRole('shirley');
 
备注：
DELIMITER ;   分号前有空格

3.修改存储过程

存储过程在创建之后，随着开发业务的不断推进，业务需求难免有所调整，相应的存储

过程也会发生变动，这个时候就需要修改存储过程。存储过程的修改分为特征的修改和业务

内容的修改。

【例】特征的修改：修改存储过程，将其权限修改为modifies sql data，并且指明调用者可以执行
ALTER PROCEDURE t1Role
modifies sql data
sql security invoker;

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储

过程。