用户管理与高级SQL语句

一、用户管理

1.权限表

(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表可以对存储过程和存储函数设置操作权限。

2.账户管理

(1)新建普通用户

        使用CREATE USER创建一个用户，用户名是jeffrey，密码是mypass，主机名是localhost

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

        使用GRANT语句创建一个新的用户testUser，密码为testpwd。用户 testUser对所有的数据有查询和更新权限，并授于对所有数据表的SELECT和UPDATE权限。

GRANT SELECT,UPDATE  ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'testpwd';

         使用INSERT创建一个新账户，其用户名称为customer1，主机名称为localhost，密码为customer1

INSERT INTO user (Host,User,Password) VALUES ('localhost','customer1',PASSWORD('customer1'));

(2)删除普通用户

        使用DROP USER删除用户'jeffrey'@'localhost'

DROP USER 'jeffrey'@'localhost';

         使用DELETE删除用户'customer1'@'localhost'

DELETE FROM mysql.user WHERE host='localhost' and user='customer1';

 (3)root用户修改自己的密码

        在命令行指定新密码，使用mysqladmin命令将root用户的密码修改为“123456”。

mysqladmin -u root -p password "123456"

         使用UPDATE语句修改mysql数据库中的user表，将root用户的密码修改为“rootpwd2”

UPDATE mysql.user set Password=password("rootpwd2") WHERE User="root" and Host="localhost";

         使用SET语句将root用户的密码修改为“rootpwd3”

SET PASSWORD=password("rootpwd3");

(4)root用户密码丢失的解决方法

         首先，使用--skip-grant-tables选项启动MySQL服务，其次，使用root用户登录和重新设置密码。最后，加载权限表。

mysql start-mysqld --skip-grant-tables
mysql -u root
update mysql.user set password=password('mypass') where user='root' and host='localhost';
flush privileges;

 3.授权管理

(1)MySQL的各种权限

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

(2)授权

        授权就是为某个用户授于权限。合理的授权可以保证数据库的安全。MySQL中可以使用GRANT语句为用户授于权限。
        使用GRANT语句创建一个新的用户grantUser，密码为“grantpwd”。用户grantUser对所有的数据有查询、插入权限，并授于GRANT权限。

GRANT SELECT,INSERT ON *.* TO 'grantUser'@'localhost'  IDENTIFIED BY 'grantpwd' ;

(3)收回权限

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

REVOKE UPDATE ON *.* FROM 'testUser'@'localhost';

(4)查看权限

        SHOW GRANTS语句可以显示指定用户的权限信息，使用SHOW GRANT语句查看账户信息。

SHOW GRANTS FOR ‘user’@’host’ ;

4.访问控制

(1)连接核实阶段 

        当连接MySQL服务器时，服务器基于user表3个字段（Host，User和Password）来判断用户的身份以及用户是否能通过正确的密码验证身份来接受或拒绝连接。

(2)请求核实阶段

        建立了连接之后，对在此连接上进来的每个请求，服务器检查用户要执行的操作，然后检查ser、db、host、tables_priv或columns_priv表，判断该用户是否有足够的权限来执行它。

二、高级SQL语句 

1.MySQL 进阶查询 

(1)按关键字排序

        使用 SELECT 语句可以将需要的数据从 MySQL 数据库中查询出来，如果对查询的结果进行排序，该如何去实现呢？可以使用 ORDER BY 语句来完成排序，并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段，也可以针对多个字段。
        句中如果没有指定具体的排序方式，则默认按 ASC 方式进行排序。DESC 是按降序方式进行排列。ORDER BY 语句也可以使用多个字段来进行排序，当排序的第一个字段相同的记录有多条的情况下，这些多条的记录再按照第二个字段进行排序。

(2)对结果进行分组 

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

【例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 子句。
        LIMIT 子句是一种简单的分页方法，它的使用减少了数据结果的返回时间，提高了执行效率，也解决了由于数据量过大从而导致的性能问题。LIMIT 子句的使用也可以结合 ORDER BY：先进行排序，然后再 LIMIT 限制固定的记录。也就是说 LIMIT 是放在最后的，将处理好的结果集按要求选出几行来。

        将查询记录按等级 level 降序排列，只取前三条记录

mysql> select id,name,level from t1 order by level desc limit 3;

        在显示结果的时候也可以不从第一行开始，引入 offset 参数。执行以下操作即可从第 3 条记录开始显示之后的 3 条数据。

mysql> select id,name,level from t1 limit 2,3;

(4)通配符

        通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务        
        %：百分号表示零个、一个或多个字符
        _：下划线表示单个字符
        查询 t1 表内 name 字段分别以 s 开头的名字、以 s 结尾的名字和名字中间包含 es 的字段，具体操作如下所示

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%';

        利用下划线替换表内 name 字段开头的字符、结尾的字符或者中间的字符

mysql> select id,name,level from t1 where name like 'shi_ley';

        name 字段中，开头有一个字符，接着是 es 两个字符，后面再跟着零个、一个或多个字符，从 t1 表中查询这样的数据

mysql> select id,name,level from t1 where name like '_es%';

(5)子查询

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

        先查出等级大于等于 45级的 ID，然后在判断 t1 表内的 ID 是不是在这个结果集内，如果在就打印此行的名字和等级。

mysql> select name,level from t1 where id in (select id from t1 where level>=45);

2.运算符

(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)逻辑运算符

逻辑非：NOT 或 ！

逻辑与：AND 或 &&

逻辑或 ：R 或 ||

逻辑异或：XOR

3.连接查询

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

(1)内连接

        在刚才创建的表中使用内连接查询出通过判断 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)左连接

将上述语句中的inner更换成left即可。

(3)右连接

将上述语句中的inner更换成right即可。

3.数据库函数

        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...) ：返回集合中最大的值

(2)聚合函数

        MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的，这些函数被称作聚合函数。

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

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

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

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

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

(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 反转

(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 是本年的第几天

4.存储过程

(1)存储过程简介

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

(2)存储过程的优点

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