1、约束
概念: 对表中的数据进行限定,保证数据的正确性、有效性、完整性。
分类:
1、主键约束:primary key
2、非空约束:not null
3、唯一约束:unique
4、外键约束:foreign key
1、非空约束: not null 值不能为空
-- 创建表时添加非空约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name 为非空
);
-- 删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
-- 创建表后添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
2、 唯一约束:unique,值不能重复
注意:唯一约束可以有null值,但是只能有一条记录为null。
-- 1、创建表时,添加唯一约束
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE -- 添加唯一约束
);
-- 注意在MySQL中,唯一约束限定的列的值可以有多个null,即null不算是唯一值
-- 2、 删除唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20); -- 这样不能删除唯一约束
ALTER TABLE stu DROP INDEX phone_number; -- 正确的删除语句
-- 3、在创建表后,添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
3、主键约束:primary key
1、注意:
1、含义:非空且唯一
2、一张表只能有一个字段为主键
3、主键就是表中记录的唯一标识
-- 创建表时,添加主键约束
CREATE TABLE stu(
id INT PRIMARY KEY, -- 添加主键约束
NAME VARCHAR(20)
);
SELECT *FROM stu
-- 删除主键
-- ALTER TABLE stu MODIFY id INT; -- 错误
ALTER TABLE stu DROP PRIMARY KEY;
-- 创建完表之后添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
自动增长--------(一般都同主键一起使用):
概念:如果某一列是数值类型的,使用auto_increment可以来完成值的自动增长。
-- 在创建表时, 添加主键约束,并设置自动增长
CREATE TABLE stu(
id INT PRIMARY KEY auto_increment, -- 给id添加主键约束,并且设置自动增长
name VARCHAR(20)
);
INSERT INTO stu VALUES(null, "ccc"); -- 可以自动增长,也可以赋值给主键
INSERT INTO stu VALUES(10, "ccc"); -- 自动增长只和上一条记录有关。
-- 删除自动增长
ALTER TABLE stu MODIFY id INT;
-- 添加自动增长
ALTER TABLE stu MODIFY id INT auto_increment;
4、外键约束:foreign key,让表与表之间产生关系,从而保证数据的正确性。
-- 为什么需要外键约束?
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
SELECT *FROM emp;
-- 数据之间存在冗余,部门名称和部门地址是对应的
-- 数据表的拆分
-- 解决方案:分成 2 张表
-- 创建部门表(id,dep_name,dep_location)
-- 一方,主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方,从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int -- 外键对应主表的主键
)
-- 添加 2 个部门
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
select * from employee;
SELECT * from department;
1、在创建表时,可以添加外键
语法:
create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
2. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
3. 创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
4. 级联操作
1. 添加级联操作
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;
2. 分类:
1. 级联更新:ON UPDATE CASCADE
2. 级联删除:ON DELETE CASCADE
DROP TABLE emp;
DROP TABLE department;
DROP TABLE employee;
-- 创建部门表(id,dep_name,dep_location)
-- 一方,主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方,从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
-- 添加 2 个部门
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
select * from employee;
SELECT * from department;
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
-- 创建表之后添加外键
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);
-- 将部门的id修改
UPDATE employee SET dep_id = NULL WHERE dep_id = 1;
-- 外键可以为null,但是不能为外键所在表中不存在的值
UPDATE employee SET dep_id = 5 WHERE dep_id IS NULL;
-- 级联
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
-- 创建表之后添加外键,同时设置级联更新
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY
(dep_id) REFERENCES department(id) ON UPDATE CASCADE;
-- 级联删除
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
-- 创建表之后添加外键,同时设置级联更新,设置级联删除
-- 级联更新和级联删除可以设置一个,也可以都设置。
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY
(dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;
2、数据库的设计
1、多表之间的关系
1、分类:
1、一对一的关系:(了解,在工程中用的少)
人和身份证的关系:一个人只有一个身份证,一个身份证只能对应一个人
2、一对多(多对一):
部门和员工的关系:一个部门有多个员工,一个员工只能对应一个部门
3、多对多:
学生和课程的关系:一个学生可以选择多门课程,一个课程也可以被很多个学生选择
2、实现关系:
1、一对多(多对一) :
员工和部门:多个员工对一个部门
实现方式:在多的一方建立外键,指向一的一方的主键
2、多对多:
学生和课程:多个学生对多个部门
实现方式:需要借助中间表,中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键----联合主键
3、一对一关系
学生和身份证
实现方式:可以在任意一方设置外键指向另一方的主键,注意外键要加上唯一约束----直接合成一张表不香嘛
-- 创建旅游线路
分类表对线路表是一对多的关系,在线路表中设置外键指向分类表的主键
分类表 tab_category
-- cid 旅游线路分类主键,自动增长
-- cname 旅游线路分类名称非空,唯一,字符串 100
CREATE TABLE tab_category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100) NOT NULL UNIQUE
);
-- 创建旅游线路表 tab_route
/*
rid 旅游线路主键,自动增长
rname 旅游线路名称非空,唯一,字符串 100
price 价格
rdate 上架时间,日期类型
cid 外键,所属分类
*/
CREATE TABLE tab_route(
rid INT PRIMARY KEY AUTO_INCREMENT,
rname VARCHAR(100) NOT NULL UNIQUE,
price DOUBLE,
rdate DATE,
cid INT,
FOREIGN KEY (cid) REFERENCES tab_category(cid)
);
线路表与用户表是多对多的关系,需要借助中间表,至少包含两张表的主键,设置了联合主键
/*创建用户表 tab_user
uid 用户主键,自增长
username 用户名长度 100,唯一,非空
password 密码长度 30,非空
name 真实姓名长度 100
birthday 生日
sex 性别,定长字符串 1
telephone 手机号,字符串 11
email 邮箱,字符串长度 100
*/
CREATE TABLE tab_user (
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) UNIQUE NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
NAME VARCHAR(100),
birthday DATE,
sex CHAR(1) DEFAULT '男',
telephone VARCHAR(11),
email VARCHAR(100)
);
/*
创建收藏表 tab_favorite
rid 旅游线路 id,外键
date 收藏时间
uid 用户 id,外键
rid 和 uid 不能重复,设置复合主键,同一个用户不能收藏同一个线路两次
*/
CREATE TABLE tab_favorite (
rid INT, -- 线路id
DATE DATETIME,
uid INT, -- 用户id
-- 创建复合主键
PRIMARY KEY(rid,uid), -- 联合主键
FOREIGN KEY (rid) REFERENCES tab_route(rid),
FOREIGN KEY(uid) REFERENCES tab_user(uid)
);
2、数据库设计的范式
概念: 设计数据库时,需要遵循的一些规范。
范式越高,要求越多,如要遵循后面的范式,必须遵循前面的所有范式要求。
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
* 分类:
1. 第一范式(1NF):每一列都是不可分割的原子数据项------所有的列都是单独不可分的
--- 如果全是第一范式的表格,存在的问题:
1、存在严重的数据冗余问题。姓名、系名、系主任重复过多
2、数据添加存在问题,只添加新开设的系和系主任的时候,数据不合法
3、数据删除存在问题,如果只有一个学生,学生毕业删除数据时,会将系的数据一起删除
------ 第一范式是一张表--学生表
2. 第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码
(在1NF基础上消除非主属性对主码的部分函数依赖)----------第二范式就是消除部分依赖。
* 几个概念:
1. 函数依赖:A-->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称B依赖于A
例如:学号-->姓名。 (学号,课程名称) --> 分数
学号可以唯一确定姓名的值,因此姓名依赖于学号。
学号和课程名称可以唯一确定分数,分数依赖于属性组(学号,课程名称)
2. 完全函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定需要依赖于A属性组中所有的属性值。
例如:(学号,课程名称) --> 分数
分数依赖于学号和课程名称两个,即属性组中所有属性值。
3. 部分函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值即可。
例如:(学号,课程名称) -- > 姓名
属性组中某个属性就可确定该属性,学号就可以确定姓名
4. 传递函数依赖:A-->B, B -- >C . 如果通过A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值可以确定唯一C属性的值,则称 C 传递函数依赖于A
例如:学号-->系名,系名-->系主任
学号唯一确定系名,系名唯一确定系主任,则称传递函数依赖
5. 码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
例如:该表中码为:(学号,课程名称)
通过学号和课程名称,可以唯一确定其他的所有属性值
* 主属性:码属性组中的所有属性 ---- 学号、课程名称
* 非主属性:除过码属性组的属性 --- 其他的属性
--- 第二范式分成了学生表和选课表
3. 第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性
(在2NF基础上消除传递依赖)
---- 第三范式分成了学生表、选课表、系表
---- 此时存在的问题都被解决。
3、数据库的备份和还原
1. 命令行:
* 语法:
* 备份: mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
* 备份的是一些数据库的表、数据等
* 还原:
1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 执行文件。source 文件路径------------把原有的生成的SQL文件source一下
2. 图形化工具:
右键就可以进行转储SQL文件
也可以直接导入SQL备份文件
4、事务
1、事务的基本介绍
1、概念:指满足ACID特性的一组操作,可以通过Commit提交一个事务,也可以使用Rollback进行回滚。
---如果一个包含多个步骤的业务操作,被事务管理,这些操作要么同时成功,要么同时失败。
例子:张三给李四转账500元 --- 被事务管理 --- 要开启事务、回滚、提交事务
1、查询张三余额是否大于500
2、张三账户金额 - 500
3、李四账户金额 + 500
2、操作:
1、开启事务:start transaction
2、回滚:rollback
3、提交:commit
4、MySQL数据库中事务默认自动提交
* 事务提交的两种方式:
* 自动提交:
* mysql就是自动提交的
* 一条DML(增删改)语句会自动提交一次事务。
* 手动提交:
* Oracle 数据库默认是手动提交事务 -- 在Oracle中执行完必须要commit一下
* 需要先开启事务,再提交
* 修改事务的默认提交方式:
* 查看事务的默认提交方式:SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
* 修改默认提交方式: set @@autocommit = 0;
use db3
-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
DROP TABLE account;
SELECT * FROM account;
UPDATE account SET balance = 1000;
-- 0、开启事务
START TRANSACTION
-- 1、张三账户减去500
UPDATE account SET balance = balance - 500 WHERE name = 'zhangsan';
-- 2;李四账户加上500
UPDATE account SET balance = balance + 500 WHERE name = 'lisi';
-- 如果第二步出现了异常,可能会出现张三钱变少了,李四钱没变多的情况 ---因此要开启事务
-- 发现出现问题了,回滚事务
ROLLBACK
-- 发现执行没有问题,提交事务
COMMIT
SELECT @@autocommit;
SET @@autocommit = 0;
SET @@autocommit = 1;
2、事务的四大特征ACID
1、原子性(Atomicity):是不可分割的最小操作单位,事务的所有操作要么同时提交成功,要么同时失败回滚。
2、一致性(Consistency):数据库在事务执行前后都保持一致性状态,数据总量不变。
---一致性状态下,所有事务对同一个数据的读取结果是相同的。
3、隔离性(Isolation):多个事务之间,相互独立。--一个事务所做的修改在最终提交之前,对其它事务不可见。
4、持久性(Durability):当事务提交或回滚之后,数据会持久化地保存数据,保存在硬盘上。
ACID特性不是一种平级关系:
1、只有满足一致性,事务的执行结果才是正确的。
2、在无并发的情况下,事务串行执行,隔离性一定能够满足。此时只要能满足原子性,就一定能满足一致性。
3、在并发的情况下,多个事务并行执行,事务不仅要满足原子性,还需要满足隔离性,才能满足一致性。
4、事务满足持久化是为了能应对系统崩溃的情况。
3、事务的隔离级别
1、概念:多个事务之间具有隔离性,是相互独立的。
但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
2、存在的问题--并发一致性问题:
1、脏读:一个事务,读取到另一个事务中没有提交的数据。----别的事务撤销了这次修改。
2、不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。----可能别的事务作了修改
3、幻读:一个事务操作(DML)数据表中所有的记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。 --- 在MySQL数据库中见不到
4、丢失修改:一个事务的更新操作被另外一个事务的更新操作替换。
产生并发不一致性问题的主要原因是破坏了事务的隔离性,解决方法是通过并发控制保证隔离性。
数据库管理系统提供了事务的隔离级别,用户可以更轻松地处理并发一致性问题。
3、隔离级别:
1、read uncommitted:读未提交
---事务中的修改,即使没有提交,对其它事务也是可见的。
产生的问题:脏读、不可重复读、幻读
2、read committed : 读已提交(Oracle数据库默认级别)
---一个事务只能读取已经提交的事务所做的修改。==一个事务所做的修改在提交之前对其它事务不可见。
产生的问题:不可重复读、幻读
3、repeatable read:可重复读 (MySQL数据库默认级别)
---保证在同一个事务中多次读取同一数据的结果是一样的。
产生的问题: 幻读
4、serializable:串行化
---强制事务串行执行,使多个事务互不干扰,不会出现并发一致性问题。
---需要加锁实现,使用加锁机制保证同一时间只有一个事务执行,保证事务串行执行。
可以解决所有问题
注意: 隔离级别从小到大,从1到4,安全性越来越高,但是效率越来越低。
数据库查询隔离级别:* select @@tx_isolation;
数据库设置隔离级别:* set global transaction isolation level 级别字符串;
select @@tx_isolation;
SELECT * FROM account;
UPDATE account SET balance = 1000;
set global transaction isolation level read uncommitted; -- 出现了脏读、不可重复读
start transaction;
-- 转账操作
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;
-- 可能会出现脏读现象--同时两次读取的数据不一样,出现了不可重复读问题
read committed : 读已提交
解决了脏读问题,仍存在不可重复读问题
如果还要解决不可重复读 -- 要设置成repeatable read
-- 左右两个事务都要提交。。
串行化serializable;类似java中锁的机制
只有一个事务提交或者回滚之后,另一个事务才能操作。
相当于给这张表上了个锁。。
4、封锁
并发控制还可以通过封锁实现,封锁操作需要用户自己控制。
1、封锁粒度
MySQL中提供了两种封锁粒度:行级锁、表级锁
应该尽量只锁定需要修改的那部分数据。锁定的数据量越少,发生锁争用的可能就越小,系统的并发程度就越高。
加锁需要消耗资源,锁的各种操作都会增加系统开销。因此封锁粒度越小,系统开销就越大。
2、封锁类型
1、读写锁
1、互斥锁(Exclusive),简写为 X 锁,又称写锁。
2、共享锁(Shared),简写为 S 锁,又称读锁。
规定:-----写锁和读锁阻塞(读写互斥),读与读共享,写和写阻塞(写写互斥)
1、一个事务对数据对象A加了写锁,该事务可以对A进行读取和写入。加锁期间其它事务不能对A加任何锁。
2、一个事务对数据对象A加了读锁,可以对A进行读取操作,但是不能更新写入。
加锁期间其它事务能对 A 加读锁,但是不能加 写 锁。
2、意向锁
使用意向锁(Intention Locks)可以更容易地支持多粒度封锁。
在存在行级锁和表级锁的情况下,事务 T 想要对表 A 加 X 锁,就需要先检测是否有其它事务对表 A 或者
表 A 中的任意一行加了锁,那么就需要对表 A 的每一行都检测一次,这是非常耗时的。
意向锁在原来读写锁之上引入了IX/IS,IX/IS都是表锁,用来表示一个事务想在表中的某数据行上加X锁或S锁。
规定:---任意 IS/IX 锁之间都是兼容的--表级的 IX 锁和行级的 X 锁兼容,两个事务可以对两个数据行加 X 锁。
1、一个事务在获得某个数据行对象的S锁之前,必须先获得表的IS锁或者更强的锁。
2、一个事务在获得某个数据行对象的X锁之前,必须先获得表的IX锁。
通过引入意向锁,事务想对表A加X锁,只需要先检测是否有其他事务对表A加了X/IX/S/IS 锁,如果加了就表示
有其它事务正在使用这个表或者表中某一行的锁,因此事务 T 加 X 锁失败。
3、封锁协议
1、三级封锁协议
1、一级封锁协议
事务T要修改数据A时必须加X写锁,直到事务结束才释放锁。
可以解决丢失修改问题。因为不能同时有两个事务对同一个数据进行修改,那么事务的修改就不会被覆盖。
2、二级封锁协议
在一级的基础上,要求读取数据时必须加S读锁,读取完马上释放锁。
可以解决数据脏读问题。因为如果一个事务在对数据 A 进行修改,根据 1 级封锁协议,会加 X 锁,那么就不能再加 S 锁了,也就是不会读入数据。
3、三级封锁协议
在二级的基础上,要求读取数据 A 时必须加 S 锁,直到事务结束了才能释放 S 锁。
可以解决不可重复读的问题,因为读 A 时,其它事务不能对 A 加 X 锁,从而避免了在读的期间数据发生改变。
2、两段锁协议
--指加锁和解锁分为两个阶段进行。
可串行化调度是指,通过并发控制,使得并发执行的事务结果与某个串行执行的事务结果相同。
串行执行的事务互不干扰,不会出现并发一致性问题。
事务遵循两段锁协议是保证可串行化调度的充分条件,但不是必要条件。
满足两段锁协议,它是可串行化调度:lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)
不满足两段锁协议,但它还是可串行化调度:lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)
4、MySQL隐式与显示锁定
MySQL 的 InnoDB 存储引擎采用两段锁协议,会根据隔离级别在需要的时候自动加锁,
并且所有的锁都是在同一时刻被释放,这被称为隐式锁定。
InnoDB 也可以使用特定的语句进行显示锁定:
SELECT ... LOCK In SHARE MODE;
SELECT ... FOR UPDATE;
5、DCL
SQL分类:
1、DDL:操作数据库和表 Definition
2、DML:增删改表中数据 Manipulation
3、DQL:查询表中数据 Query
4、DCL:管理用户,授权 Control
DBA:数据库管理员
DCL:管理用户,授权 Control
1、管理用户
1、添加用户:
语法:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
CREATE USER 'wang'@'localhost' IDENTIFIED By '123';
CREATE USER 'chen'@'%' IDENTIFIED By '123';
2、删除用户:
语法:DROP USER '用户名'@'主机名';
DROP USER 'wang'@'localhost';
DROP USER 'chen'@'%';
3、修改用户密码: -- MySQL数据库中password函数加密函数
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
another方法:DCL特有的方式。
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');-- 修改当前登录的用户密码
mysql中忘记了root用户的密码?
1. cmd -- > net stop mysql 停止mysql服务
* 需要管理员运行该cmd -- 右键管理员运行cmd,停止MySQL服务
2. 使用无验证方式启动mysql服务: mysqld --skip-grant-tables
3. 打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功
4. use mysql;
5. update user set password = password('你的新密码') where user = 'root';
6. 关闭两个窗口
7. 打开任务管理器,手动结束mysqld.exe 的进程
8. 启动mysql服务
9. 使用新密码登录。
4、查询用户:
-- 1. 切换到mysql数据库
USE myql;
-- 2. 查询user表
SELECT * FROM USER;
* 通配符: % 表示可以在任意主机使用用户登录数据库
2、 权限管理:
1.、查询权限:
-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';
2.、授予权限:
-- 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-- 给张三用户授予所有权限,在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
3、 撤销权限:
-- 撤销权限:
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';