MySQL用户管理及高级SQL语句

用户管理

权限表

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

账户管理

登录和退出MySQL服务器

使用root用户登录到本地mysql服务器的test库中

mysql> mysql -u root -p -h localhost test

使用root用户登录到本地mysql服务器的test库中，执行一条查询语句

mysql> mysql -u root -p -h localhost test -e "DESC person;"

新建普通用户

使用CREATE USER或GRANT语句。

mysql> CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'pwd123';
mysql> GRANT SELECT,UPDATE  ON *.* TO 'lisi'@'localhost' IDENTIFIED BY '123456';    //创建账户并赋予权限

直接操作MySQL授权表。

mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','wangwu',PASSWORD('wangwu'));

删除普通用户

使用DROP USER语句删除用户

DROP USER 'zhangsan'@'localhost';

使用DELETE语句删除用户

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

root用户修改自己的密码

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

mysql> mysqladmin -u root -p password "123456"

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

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

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

mysql> SET PASSWORD=password("rootpwd3");

root用户修改普通用户密码

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

mysql> set password for 'zhangsan'@'localhost'=password("123456");

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

mysql> update mysql.user set authentication_string=password('pwd123') where user='zhangsan' and host='localhost';
mysql> flush privileges;        //重载权限表

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

mysql> grant select  on *.* to 'zhangsan'@'localhost' identified by 'zhang123';

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

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

（2）使用root用户登录和重新设置密码
[root@localhost ~]# mysql -u root
mysql> update mysql.user set password=password('mypass') where user='root' and host='localhost';

（3）加载权限表
flush privileges;

权限管理

授权

使用grant创建一个用户，用户名为lisi密码为pwd123、主机名为localhost，权限为select和update权限
mysql> grant select,update on *.* to 'lisi'@'localhost' identified by 'pwd123';


查询用户的权限
mysql> SELECT Host,User,Select_priv,Insert_priv, Grant_priv FROM mysql.user where user='grantUser';

收回权限

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

mysql> revoke update on *.* from 'lisi'@'localhost';
Query OK, 0 rows affected (0.00 sec)
#执行成功可用show grant语句显示用户权限
mysql> show grants for 'lisi'@'localhost';
+-------------------------------------------+
| Grants for lisi@localhost                 |
+-------------------------------------------+
| GRANT SELECT ON *.* TO 'lisi'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)

查看权限

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

mysql> show grants for 'lisi'@'localhost';
+-------------------------------------------+
| Grants for lisi@localhost                 |
+-------------------------------------------+
| GRANT SELECT ON *.* TO 'lisi'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)

高级SQL语句

1：创建测试数据库和表

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);

2：常用查询介绍

（1）按关键字排序

句中如果没有指定具体的排序方式，则默认按 ASC 升序方式进行排序。DESC 是按降序方式进行排列。当然 ORDER BY 前面也可以使用 WHERE 子句对查询结果进一步过滤。
mysql> select id,name,level from t1 where level>=45 order by level desc;

ORDER BY 语句也可以使用多个字段来进行排序，当排序的第一个字段相同的记录有多条的情况下，这些多条的记录再按照第二个字段进行排序。
mysql> select id,name,level from t1 where level>=45 order by level desc, id desc;

（2）对结果进行分组

通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现。

通常都是结合聚合函数一起使用的

常用的聚合函数包括：

计数（COUNT）

求和（SUM）

求平均数（AVG）

最大值（MAX）

最小值（MIN）

 GROUP BY 除了配合聚合函数一起使用外，还可以引入 WHERE 子句。

统计等级在 45 级及以上，以等级为分组，每个等级有多少人
mysql> select count(name),level from t1 where level>=45 group by level;

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 子句减少了数据结果的返回时间，提高了执行效率，也解决了由于数据量过大从而导致的性能问题。

查询表的前 3 个用户的信息
mysql> select id,name,level from t1 limit 3; 

IMIT 子句的使用也可以结合 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）设置别名

在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，可以给字段列或表设置别名

在统计表内所有记录共有多少条时，使用 count（*），这么写不便于识别，可以将其别名设置为 number
mysql> select count(*) as number from t1;
mysql> select count(*) number from t1;

执行以下操作即可将 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;

执行以下操作即可实现用一条 SQL语句完成在创建表 t3 的时候将 t1 表内的数据写入 t3表。

mysql> create table t3 as select * from t1; 
mysql>select count(*) from t3; 

（5）通配符

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

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

_：下划线表示单个字符

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

利用下划线替换表内 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';

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

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

（6）子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。

先查出等级大于等于 45级的 ID，然后在判断 t1 表内的 ID 是不是在这个结果集内，如果在就打印此行的名字和等级
mysql> select name,level from t1 where id in (select id from t1 where level>=45);

 子查询还可以用在 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> select * from t2;

通常使用 NULL 来表示缺失的值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以用 NOT NULL 关键字，不使用则默认可以为空。

运算符

算术运算符

运算符	描述
+	加法
-	减法
*	乘法
/	除法
%	取余数

比较运算符

运算符	作用
=	等于
<=>	安全的等于
<>或者!=	不等于
<=	小于等于
>=	大于等于
>	大于
IS NULL或者ISNULL	判断一个值是否为空
IS NOT NULL	判断一个值是否不为空
BETWEEN AND	判断一个值是否落在两个值之间

逻辑运算符

运算符	描述
NOT或！	逻辑非
AND 或&&	逻辑与
R或||	逻辑或
XOR	逻辑异或

位运算符

运算符	作用
&	按位与
|	按位或
^	按位异或
!	取反
<<	左移
>>	右移

 连接查询

创建测试用表：

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);

内连接

在刚才创建的 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;

左连接

从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;

右连接

从 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;

数据库函数

数学函数

函数	描述
abs(x)	返回x的绝对值
rand()	返回0到1 的随机数
mod(x,y)	返回x除以y以后的余数
power(x,y)	返回x的y次方
round()	返回离x最近的整数

聚合函数 

特意为库内记录求和或者对表中的数据进行集中概括而设计的

函数	描述
avg()	返回指定列的平均值
count()	返回指定列中非 NULL 值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum(x)	返回指定列的所有值之和

字符串函数 

函数	描述
length(x)	返回字符串 x 的长度
upper(x)	将字符串 x 的所有字母变成大写字母
lower(x)	将字符串 x 的所有字母变成小写字母
right(x,y)	返回字符串 x 的后 y 个字符

 日期时间函数

函数	描述
curdate()	返回当前时间的年月日
now()	返回当前时间的日期和时间
curtime()	返回当前时间的时分秒

 存储过程

存储过程的优点

存储过程执行一次后，生成的二进制代码就驻留在缓冲区，之后如果再次调用的话，将直接调用二进制代码，使得存储过程的执行效率和性能得到大幅提升。

存储过程是 SQL 语句加上控制语句的集合，有很强的灵活性，可以完成复杂的运算。

存储过程存储在服务器端，客户端调用时，直接在服务器端执行，客户端只是传输的调用语句，从而可以降低网络负载。

存储过程被创建后，可以多次重复调用，它将多条 SQL 封装到了一起，可随时针对 SQL语句进行修改，不影响调用它的客户端。

存储过程可以完成所有的数据库操作，也可以通过编程的方式控制数据库的信息访问权限。

 创建存储过程

mysql> DELIMITER $$     //用于定义SQL语句的结束符,默认结束符是分号“;”

mysql> CREATE PROCEDURE t1Role() 
    -> BEGIN 
    -> SELECT id,name,level from t1 limit 3; 
    -> END $$

mysql> DELIMITER ;            //将结束符调整回分号“;”


#通过call调用存储过程
mysql> call t1Role;