一、数据库用户管理
DCL:数据控制语言,用于设置或者更改数据库用户或角色权限
1.1 新建用户
create user '用户名'@'源地址' identified by '密码';
'用户名'
指定将创建的用户名
'来源地址'
指定新创建的用户可在哪些主机上登录
可使用IP地址、网段、主机名的形式
本地用户可用localhost
允许任意主机登录可用通配符%
'密码'
若使用明文密码,直接输入'密码',插入到数据库时由Mysql自动加密;
若使用加密密码,需要先使用select password('密码'); 获取密文,再在语句中添加 password '密文';
若省略“identified by”部分,则用户的密码将为空(不建议使用)
create user 'user1'@'localhost' identified by '123456';
select password('abc123');
+-------------------------------------------+
| password('abc123') |
+-------------------------------------------+
| *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
create user 'user2'@'localhost' identified by PASSWORD '*6691484EA6B50DDDE1926A220DA01FA9E575C18A';
1.2 查看用户信息
#创建后的用户保存在 mysql 数据库的 user 表里
use mysql;
select user,authentication_string,Host from user;
#查看当前登录用户
select user();
1.3 重命名用户
rename user 'user1'@'localhost' to 'cxk'@'localhost';
1.4 删除用户
drop user 'cxk'@'localhost';
1.5 修改当前登录用户密码
#修改当前用户的密码
set password = password('abc123');
1.6 修改其他用户密码
#方式一
set password for 'user1'@'localhost' = PASSWORD('abc123');
#方式二
alter user '用户名'@'源地址' identified by '密码';
alter user 'root'@'%' indentified by 'abc123'
1.7 忘记 root 密码的解决办法
1)修改 mysql配置文件
/etc/my.cnf
,不使用密码直接登录到 mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables #添加,使登录mysql不使用授权表
systemctl restart mysqld
mysql #直接登录
2)使用
update
修改 root 密码,刷新数据库
use mysql;
update user set authentication_string=password('123123') where user='root';
FLUSH PRIVILEGES;
quit
mysql -u root -pabc123
#注意:最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除,并重启 mysql 服务。
1.8远程登录
mysql -utest -h192.168.91.100 -p'密码' -P端口号
例子:
vim /etc/my.cnf
[mysqld]
port = 9527
systemctl restart mysqld
客户机
mysql -utest -h192.168.91.100 -p'Admin@123' -P9527
二、数据库用户授权
2.1 授予权限
grant语句
:专门用来设置数据库用户的访问权限。 当指定的用户名不存在时,grant语句将会创建新的用户; 当指定的用户名存在时, grant 语句用于修改用户信息。
grant 权限列表 on 数据库名.表名 to '用户名'@'来源地址' [indentified by '密码'];
#权限列表:用于列出授权使用的各种数据库操作,以逗号进行分隔,如“select,insert,update”。使用“all”表示所有权限,可授权执行任何操作。
#数据库名.表名:用于指定授权操作的数据库和表的名称,其中可以使用通配符“*”。例如,使用“kgc.*”表示授权操作的对象为 kgc数据库中的所有表。
#'用户名@来源地址':用于指定用户名称和允许访问的客户机地址,即谁能连接、能从哪里连接。
#来源地址可以是域名、IP地址,还可以使用“%”通配符,表示某个区域或网段内的所有地址,如“%.byyb.com”、“192.168.80.%”等。
#IDENTIFIED BY:用于设置用户连接数据库时所使用的密码字符串。在新建用户时,若省略“IDENTIFIED BY”部分,则用户的密码将为空。
#允许用户 zhangsan 在本地查询 byy 数据库中 所有表的数据记录,但禁止查询其他数据库中的表的记录。
grant select ON byy.* TO 'zhangsan'@'localhost' IDENTIFIED BY '123456';
#允许用户 lisi 在所有终端远程连接 mysql ,并拥有所有权限。
GRANT ALL [PRIVILEGES] ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';
flush privileges;
quit
mysql -u zhangsan -p123456
use byy;
show tables;
select * from byy1;
2.2 查看权限
show grants for 用户名@来源地址;
#举个例子
show grants for 'root'@'localhost';
2.3 撤销权限
revoke 权限列表 on 数据库名.表名 from 用户名@来源地址;
#举个例子
#移除用户所有权限
revoke all on *.* from use1;
show grants for 'lisi'@'%';
#USAGE权限只能用于数据库登陆,不能执行任何操作;
#USAGE权限不能被回收,即 REVOKE 不能删除用户。
flush privileges;
三、MySQL 存储引擎
3.1 存储引擎的概念
MyISAM 表支持 3 种不同的存储格式:
1)静态(固定长度)表
静态表是默认的存储格式。静态表中的字段都是非可变字段,这样每个记录都是固定长度的,这种存储方式的优点是存储非常迅速,容易缓存,出现故障容易恢复;缺点是占用的空间通常比动态表多。
2)动态表
动态表包含可变字段,记录不是固定长度的,这样存储的优点是占用空间较少,但是频繁的更新、删除记录会产生碎片,需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能,并且出现故障的时候恢复相对比较困难。
3)压缩表
压缩表由 myisamchk 工具创建,占据非常小的空间,因为每条记录都是被单独压缩的,所以只有非常小的访问开支。
3.2 常用存储引擎(区别)
常用存储引擎:InnoDB、MyISAM MyISAM:不支持事务、外键约束,只支持表级锁定,适合单独的查询或插入的操作,读写并发能力较弱,支持全文索引,硬件资源占用较小,数据文件和索引文件是分开存储的。存储成三个文件:表结构文件.frm、数据文件.MYD、索引文件.MYI 使用场景:适用于不需要事务处理,单独的查询或插入数据的业务场景
InnoDB:支持事务、外键约束,支持行级锁定(在全表扫描时仍然会表级锁定),读写并发能力较好,支持全文索引(5.5版本之后),缓存能力较好可以减少磁盘IO的压力,数据文件也是索引文件。存储成两个文件:表结构文件.frm、数据文件.ibd 使用场景:适用于需要事务的支持,一致性要求高的,数据会频繁更新,读写并发高的业务场景
3.3 语句
3.3.1 查看
1)查看系统支持的存储引擎
2)查看表使用的存储引擎
方法一
show table status from 库名 where name='表名'\G
#举个例子
show table status from cxk where name='test'\G;
方法二
use 库名;
show create table 表名;
#举个例子
use cxk;
show create table test;
3.3.2 修改存储引擎
方法一:通过 alter table 修改
use 库名;
alter table 表名 engine=MyISAM;
方法二:修改配置文件
通过修改 /etc/my.cnf
配置文件,指定默认存储引擎并重启服务。
此方法只对修改了配置文件并重启mysql服务后新创建的表有效,已经存在的表不会更改。
vim /etc/my.cnf
......
[mysqld]
......
default-storage-engine=INNODB
systemctl restart mysqld
3.3.3 创建
通过 create table
创建表时指定存储引擎
use 库名;
create table 表名(字段1 数据类型,...) engine=MyISAM;
3.4 InnoDB行锁与索引的关系
InnoDB行锁是通过给索引项加锁来实现的,如果没有索引,InnoDB将通过隐藏的聚簇索引来对记录加锁。
delete from t1 where id=1;
如果id字段是主键,innodb对于主键使用了聚簇索引,会直接锁住整行记录。
如果id字段是主键,innodb对于主键使用了聚簇索引,会直接锁住整行记录。
delete from t1 where name='aaa';
如果name字段是普通索引,会先锁住索引的两行,接着会锁住相应主键对应的记录。
如果name字段是普通索引,会先锁住索引的两行,接着会锁住相应主键对应的记录。
delete from t1 where age=23;
如果age字段没有索引,会使用全表扫描过滤,这时表上的各个记录都将加上锁。
如果age字段没有索引,会使用全表扫描过滤,这时表上的各个记录都将加上锁。
四、MySQL事务
4.1 事务的概念
事务就是一组数据库操作序列(包含一个或者多个操作命令).
事务会把所有操作看作一个不可分割的整体向系统提交或撤销操作,所有操作要么都执行,要么都不执行.
4.2 事务的ACID特性
在可靠数据库管理系统(DBMS)中,事务(transaction)应该具有的四个特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。
这是可靠数据库所应具备的几个特性。
名称 | 描述 |
---|---|
原子性(Atomicity) | 原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生 |
一致性(Consistency) | 事务前后数据的完整性必须保持一致 |
隔离性(Isolation) | 事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离 |
持久性(Durability) | 事务一旦被提交则不可逆,被持久化到数据库中,接下来即使数据库发生故障也不应该对其有任何影响 |
在事务管理中,原子性是基础,隔离性是手段,一致性是目的,持久性是结果。
4.3 事务并发导致的问题
名称 | 描述 |
---|---|
脏读 | 指一个事务读取了另外一个事务未提交的数据 |
不可重复读 | 在一个事务内读取表中的某一行数据,多次读取结果不同 |
虚读(幻读) | 是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致 |
丢失更新 | 两个事务同时读取同一条记录,导致修改结果覆盖 |
4.4 事务的隔离级别
事务的隔离级别决定了事务之间可见的级别。
4.4.1 四种隔离级别
MySQL事务支持如下四种隔离,用以控制事务所做的修改,并将修改通告至其它并发的事务。
1)未提交读( Read Uncommitted(RU)) 允许脏读,即允许一个事务可以看到其他事务未提交的修改。
2)提交读(Read Committed(RC)) 允许一个事务只能看到其他事务已经提交的修改,未提交的修改是不可见的,防止脏读。
3)可重复读(Repeatable Read(RR))—mysql默认的隔离级别 确保如果在一个事务中执行两次相同的SELECT语句,都能得到相同的结果,不管其他事务是否提交这些修改;
可以防止脏读和不可重复读。
4)串行读(Serializable)—相当于锁表 完全串行化的读,将一个事务与其他事务完全地隔离;
每次读都需要获得表级共享锁,读写相互都会阻塞;
可以防止脏读,不可重复读取和幻读,(事务串行化)会降低数据库的效率。
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交(read-uncommitted) | 是 | 是 | 是 |
读已提交(read-committed) | 否 | 是 | 是 |
可重复读(repeatable-read) | 否 | 否 | 是 |
串行化(serializable) | 否 | 否 | 否 |
mysql默认的事务处理级别是 repeatable read ,而Oracle和SQL Server是 read committed 。
事务隔离级别的作用范围分为两种: ● 全局级:对所有的会话有效 ● 会话级:只对当前的会话有效
4.4.2 管理事务隔离级别
1)设置隔离级别
设置全局事务隔离级别:
#全局级的隔离级别,可在所有会话有效,需要重新登录才可生效
set global transaction isolation level 隔离级别名称;
set global transaction isolation level read committed;
set @@global.tx_isolation='read-committed'; #重启服务后失效
设置会话事务隔离级别:
#会话级的隔离级别,在当前会话中立即生效
set session transaction isolation level 隔离级别名称;
set session transaction isolation level repeatable read;
set @@session.tx_isolation='repeatable-read';
2)查询隔离级别
查询全局事务隔离级别:
show global variables like '%isolation%';
SELECT @@global.tx_isolation
查询会话事务隔离级别:
show session variables like '%isolation%';
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
4.5 事务控制语句
#显式地开启一个事务
begin;
#提交事务,并使已对数据库进行的所有修改变为永久性的。
commit;
#回滚
#回滚会结束用户的事务,并撤销正在进行的所有未提交的修改。
rollback;
#创建回滚点,相当于存档
#一个事务中可以有多个
#XX代表回滚点名称。
savepoint XX ;
#把事务回滚到标记点,相当于读档
rollback to XX;
#创建测试用表
use byyb;
create table account(
id int(10) primary key not null,
name varchar(40),
money double
);
insert into account values(1,'A',1000);
insert into account values(2,'B',1000);
#测试提交事务
begin;
update account set money= money - 100 where name='A';
commit;
select * from account;
#测试回滚事务
begin;
update account set money= money + 100 where name='A';
rollback;
select * from account;
4.6 使用 set 设置控制事务(自动提交)
如果没有开启自动提交:
当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback或者commit;
,当前事务才算结束。 当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
如果开起了自动提交:
mysql会把每个sql语句当成一个事务,然后自动的commit。
当然无论开启与否,begin;
commit|rollback;
都是独立的事务。
#禁止自动提交
SET AUTOCOMMIT=0;
#开启自动提交,Mysql默认为1
SET AUTOCOMMIT=1;
#查看Mysql中的AUTOCOMMIT值
SHOW VARIABLES LIKE 'AUTOCOMMIT';
use byyb;
select * from account;
SET AUTOCOMMIT=0;
update account set money= money + 100 where name='B';
select * from account;
quit
mysql -u root -p
use byyb;
select * from account;
命令总结
use 库名;
show tables; show tables from 库名;
desc [库名.]表名;
create database 库名;
use 库名;
create table [库名.]表名 (字段1 数据类型, 字段2 数据类型, ....[, primary key (字段)]);
drop table [库名.]表名;
drop database 库名;
修改表结构
改表名 alter table 旧表名 rename 新表名
增加字段 alter table 表名 add 新字段 数据类型 [字段属性];
修改字段 alter table 表名 change 旧字段 新字段 数据类型 [字段属性];
删除字段 alter table 表名 drop 字段;
DML:用于管理表数据
insert into 表名 (字段1, 字段2, ...) values (字段1的值, 字段2的值, ...);
insert into 表名 values (字段1的值, 字段2的值, ...); #要按照表结构的字段顺序设置值
update 表名 set 字段1=值, ... where 条件表达式;
delete from 表名 where 条件表达式;
DQL:用于根据条件查询表数据
select * from 表名 [where 条件表达式];
select 字段1,字段2,... from 表名 [where 条件表达式];
select * from 表名\G #纵向查看每行记录字段的值
select * from 表名 limit N; #查看表的前N行记录
select * from 表名 limit N,M; #查看表的前N行之后的连续M行记录(不包含第N行)
MySQL 的约束特性:
primary key 主键约束 字段的值不能重复,且不能为null,一个自建的表只能有一个主键
unique key 唯一性约束 字段的值不能重复,能为null,一个表可以有多个唯一键
not null 非空约束 字段的值不能为null
default 默认值约束 字段的值如果没有设置则使用默认值自动填充
auto_increment 自增约束 字段的值如果没有设置默认会从1开始每次自动递增1,要求自增字段必须设置主键
foreign key 外键约束 保证相关联的表数据的完整性和一致性
int(N) zerofill 零填充
克隆表
create table 新表 like 旧表; #克隆表结构
insert into 新表 (select * from 旧表); #克隆表数据 可实现表结构和表数据与旧表都一样
create table 新表 (select * from 旧表); 表数据与旧表一样,表结构与旧表可能不一样
清空表
delete from 表名; 一条一条的删除记录,效率较慢;自增字段仍然会按照清空前的最大记录继续自增
truncate table 表名; 直接重建表,清空表效率更快;自增字段会重新从1开始自增
临时表
create temporary table 表名 (....); 临时表可以跟普通的表一样增删改查表中的数据,但是show tables是查看不到的,
临时表只能在当前会话中有效,在其它会话中或者退出当前会话连接,临时都会失效
外键约束
主键表: alter table 表名 add primary key (主键字段);
外键表: alter table 表名 add foreign key (外键字段) references 主键表名 (主键字段);
插入新数据时,需要先在主键表插入数据再在外键表插入对应数据;删除数据时,需要先在外键表删除数据再在主键表删除对应数据
DCL:用于管理用户与权限
用户管理
select user(); 查看当前登录的用户
create user '用户名'@'源地址' identified by '密码';
localhost/IP/网段/主机名/%
rename user '旧用户名'@'源地址' to '新用户名'@'源地址';
drop user '用户名'@'源地址';
select user,host,authentication_string from mysql.user;
set password = password('密码');
set password for '用户名'@'源地址' = password('密码');
alter user '用户名'@'源地址' identified by '密码';
找回 root 密码?
1)修改mysql配置文件,在 [mysqld] 下面添加 skip-grant-tables 配置项
2)重启mysqld服务,使用 mysql 密码直接登录 mysql
3)执行 update user set authentication_string=password('密码') where user='root'; 命令修改 root 用户的密码
4)还原mysql配置文件,重启mysqld服务,使用 mysql -u 用户名 -p密码 [-h mysql地址 -P mysql端口] 命令来验证登录
权限管理
grant 权限1,权限2,.... on 库名.表名 to '用户名'@'源地址' [identified by '密码']; #5.7版本支持创建用户和权限授权,8.0版本只能用于权限授权
all *.*
show grants for '用户名'@'源地址';
revoke 权限1,权限2,.... on 库名.表名 from '用户名'@'源地址';
all