MySQL用户权限管理、DDL（数据定义语言）以及常见约束

查询用户

当我们才连接数据库的时候，直接查询是会报错的。原因是连接数据库的这个root用户多个数据库，没有指定使用哪个数据库。

查询所有信息。语法：SELECT * from user;

这个命令跳出一大堆看不懂的，是因为用户表的所有信息(列)都显示出来了。如图：

我们可以只查询User的信息，这样结果就好看多了(需要哪个列就用哪个列)。
语法：select user from user;

创建新用户、密码

语法：create user egg;

给创建的用户：zs设置密码为：123456。
语法：ALTER USER ‘zs’@’%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;

现在这个用户只有一个数据库。

给zs这个用户设置db_xxx表的所有权限。
语法：grant all on db_xxx.* to zs@’%’;

刷新一下，就能看到新的表的。

当zs这个用户不能删除db_xxx表，只能增、改、查，这时就需要回收数据库的权限，再重新授权数据库的查、改、增。
语法：revoke all on db_xxx.* from jf@'%';
语法：grant SELECT, UPDATE,INSERT on db_xxx.* to zs@’%’;

主要使用的InnoDB存储引擎和MYISAM存储引擎

功 能	MYISAM	InnoDB
存储限制	256TB	64TB
支持事物	No	Yes
支持全文索引	Yes	No
支持外键	No	Yes

增删改使用InnoDB性能更优；
查使用MYISAM性能更优。因为MYISAM在增删改的时候，会把原先的数据上锁，新数据操作完毕之后才会解锁，多了这个上锁解锁的过程，就慢了。

---

DDL（数据定义语言）

库和表的管理

创建： create
修改： alter
删除： drop

1.库的创建

原先是只有8个，新建一个之后，就有9个了。show是展示，database是数据库，s是复数。
语法：create database 库名;

2.库的修改（一般不用）

RENAME DATABASE 旧库名 TO 新库名;

3.库的删除

语法：DROP DATABASE IF EXISTS ;

1.表的创建

语法：create table 表名(列名 列的类型);

2.表的修改

语法:alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;

①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;

④删除列
ALTER TABLE book_author DROP COLUMN annual;

⑤修改表名
ALTER TABLE author RENAME TO book_author;

3.表的删除

语法：DROP TABLE 表名;

常用：
1.将列Last_name的长度增加到50
ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50);

2.根据表employees创建employees2
CREATE TABLE employees2 LIKE myemployees.employees;

3.删除表emp5
DROP TABLE IF EXISTS emp5;

4.将表employees2重命名为emp5
ALTER TABLE employees2 RENAME TO emp5;

5.在表dept和emp5中添加新列test_column，并检查所作的操作
ALTER TABLE emp5 ADD COLUMN test_column INT;

6.直接删除表emp5中的列 dept_id
ALTER TABLE emp5 DROP COLUMN test_column;

常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

分类：六大约束
	NOT NULL：非空，用于保证该字段的值不能为空。比如姓名、学号等
	DEFAULT:默认，用于保证该字段有默认值。比如性别
	PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空。比如学号、员工编号等
	UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空。比如座位号
	FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。比如学生表的专业编号，员工表的部门编号，员工表的工种编号
	CHECK:检查约束【mysql中不支持】。比如年龄、性别
	
添加约束的时机：
	1.创建表时
	2.修改表时
	
约束的添加分类：
	列级约束：
		六大约束语法上都支持，但外键约束没有效果
		
	表级约束：
		除了非空、默认，其他的都支持
		
		
主键和唯一的大对比：

        保证唯一性  是否允许为空    一个表中可以有多少个   是否允许组合
	主键   	√		×		          至多有1个         √，但不推荐
	唯一	√		√		          可以有多个          √，但不推荐
外键：
	1、要求在从表设置外键关系
	2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
	3、主表的关联列必须是一个key（一般是主键或唯一）
	4、插入数据时，先插入主表，再插入从表
	删除数据时，先删除从表，再删除主表

USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL UNIQUE,#非空
	gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
	seat INT UNIQUE,#唯一
	age INT DEFAULT  18,#默认约束
	majorId INT REFERENCES major(id)#外键
);

=========================================================添加
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;

#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);

#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 

==========================================================删除
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

SHOW INDEX FROM stuinfo;

常见约束练习

1.向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk）

ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);

2.	向表dept2的id列中添加PRIMARY KEY约束（my_dept_id_pk）

3.	向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);

约束类型	位置	支持的约束类型	是否可以起约束名
列级约束	列的后面	语法都支持，但外键没有效果	不可以
表级约束	所有列的下面	默认和非空不支持，其他支持	可以（主键没有效果）

先创建表emp2，create table emp2(id ind);
再在id列中添加PRIMARY KEY约束，约束名：my_emp_id_pk；
向表emp2中添加列dept_id，ALTER TABLE emp2 ADD COLUMN dept_id INT;
并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);