MySQL知识汇总（一）

数据库的账号管理

1.操作人员对数据库权限操作的细化分：

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；

2.使用grant命令来分配权限

格式：grant 权限 on 数据库对象 to 用户 identified by 密码

1.给root@%这个用户对应的test数据库分配所有操作权限，identified by是需要进行的一个密码认证。把all换成USAGE就表示这个用户除了登陆啥都干不了。

GRANT ALL ON my243.* TO 'root' @'%' IDENTIFIED BY 'best' WITH GRANT OPTION;

结果：

2 .给root@%这个用户对应的my243数据库分配了增删改查的操作权限。

GRANT SELECT
	,
	INSERT,
	UPDATE,
	DELETE ON my243.* TO 'root' @'%' IDENTIFIED BY 'best' WITH GRANT OPTION;

3.查询root用户的权限以及my243和test库：

SHOW GRANTS FOR 'root' @'%'; 

结果：

4.创建myroot账号，密码best，只拥有TUTORIALS的权限

GRANT SELECT
,
INSERT,
UPDATE,
DELETE,
CREATE,
DROP ON TUTORIALS.* TO 'myroot' @'localhost' IDENTIFIED BY 'best';

结果：

5.修改用户名的密码
语法：

USE mysql;

UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’;

FLUSH PRIVILEGES;

四大引擎

1.常用的两大引擎，InnoDB存储引擎，MyISAM存储引擎

1.1 InnoDB存储引擎

我们在创建表的时候会发现它首选的是InnoDB存储引擎 ，因为我们要对表进行增删改查

1.2 MyISAM存储引擎：

MyISAM拥有较高的插入、查询速度，但不支持事物。

存储引擎的选择：

不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：

功 能	MYISAM	Memory	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事物	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持数索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率

总结：InnoDB用的多，支持事务，增删改效率高，MYISAM查询效率高

库和表的管理

1.库的管理 ：
1.1 库的创建，
语法：

CREATE DATABASE [IF NOT EXISTS ]库名；

1.2 创建一个名为books的库

CREATE DATABASE
IF
	NOT EXISTS books；

结果：

2.删除库：(注意库名带单引号)

DROP DATABASE
IF
	EXISTS `books；`

结果：

3.表的管理，
创建表
语法：

create table 表名（
列名 列的类型【（长度） 约束】，
列名 列的类型【（长度） 约束】
）

CREATE TABLE book ( 
  id INT, #编号
	bName VARCHAR ( 20 ), #书名
	price DOUBLE #价格
	);

查看表结构：DESC book;

4.修改表的列名：
修改book表的列名为price改为bprice(注意后面接类型)

	ALTER TABLE book CHANGE COLUMN price bprice DOUBLE;	

结果：

4.1 修改列段的类型price的double，改为int

ALTER TABLE book MODIFY COLUMN bprice INT;	

结果：

5.新增列字段:

ALTER TABLE book ADD COLUMN annual DOUBLE;	

结果：

6.删除列字段

	ALTER TABLE book DROP COLUMN annual ;	

查询所有的表：SHOW TABLES

7.删除表

DROP TABLE IF EXISTS book;

8.只复制表的结构

CREATE TABLE cody LIKE book;

9.1 复制表结构带上数据 (只复制部分数据的话带上where条件)

CREATE TABLE cody1 	SELECT * FROM book;

常见的约束

六大约束:

约束	释义
NOT NULL	非空
DEFAULT	默认
PRIMARY KEY	主键
UNIQUE	唯一
CHECK	检查约束（mysql不支持）
FOREIGN KEY	外键

1.主键(Primary Key) 必须包含唯一值,主键列不能包含NULL值。 这意味着必须使用NOT NULL，如果没有设置就会强转为NOT NULL.

2.主键列通常具有自动生成键的唯一序列的AUTO_INCREMENT属性

3.创建一个user表为它的id设置主键

CREATE TABLE users(
   uid INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(40),
   password VARCHAR(255)
);

3.1 还可以在CREATE TABLE语句的末尾指定PRIMARY KEY，如下所示：

CREATE TABLE roles(
   rid INT AUTO_INCREMENT,
   name VARCHAR(50),
   PRIMARY KEY(rid)
);

3.2 索引关系

查看索引关系：SHOW INDEX FROM stuinfo;

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

3.3 外键表

CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
)

添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

列级约束
添加主键

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

表级约束
添加主键

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

添加唯一约束
列级约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

表级约束

ALTER TABLE stuinfo ADD UNIQUE(seat);

添加外键

ALTER TABLE stuinfo ADD CONSTRAINT stuinfo_marjor FOREIGN KEY(majorid) REFERENCES major(id);

删除约束
删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN age INT;

删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY stuinfo_major;