用户管理

权限表

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;

点赞(0) 打赏

评论列表 共有 0 条评论

暂无评论

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部