用户管理
权限表
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;
![[每周一更]-(第103期)：GIT初始化子模块](https://img-blog.csdnimg.cn/direct/9c71a0fe979140c29433620914d491af.jpeg#pic_center)
