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 关键字，不使用则默认可以为空。

运算符

算术运算符

比较运算符

逻辑运算符

位运算符

 连接查询

创建测试用表：

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;

数据库函数

数学函数

聚合函数 

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

字符串函数 

 日期时间函数

 存储过程

存储过程的优点

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

存储过程是 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;