MYSQL数据库基本知识点整理

MYSQL数据库

一、数据库的基本操作

1、开启MySQL数据库
以管理员的身份运行cmd
net start mysql 

2、关闭数据库
net stop mysql   | exit

3、链接数据库
mysql -u用户名 -p密码

4、查看数据库中已有的数据库
SHOW DATABASES

5、数据库一旦创建好之后，名字就不允许再修改

6、切换数据库
USE 数据库名

7、查看数据库的字符集
SHOW VARIABLES LIKE '%CHAR%';

8、查看当前数据库中有哪些表
SHOW TABLES

9、 查看表的结构
DESC 表名

10、查看数据库使用的存储引擎
SHOW ENGINES;

二、MySQL数据中的约束

PRIMARY KEY 主键约束
AUTO_INCREMENT 设置主键自动增长
NOT NULL 非空
UNIQUE 不重复
DEFAULT 默认值，当时在添加数据的时候没有给值的时候，使用设定的默认值
FOREIGN KEY 外键约束

添加外键约束的两种方式：
1、在创建表的时候：
CREATE TABLE 表名 （字段名 数据类型（长度）。。。
	CONSTRAINT 外键的名字 FOREIGN KEY(外键字段) REFERENCES 关联表（关联的字段）
）；
2、在表创建好之后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键的名字 FOREIGN KEY(外键字段) REFERENCES 关联的表（关联的字段）

三、MySQL数据库支持的SQL语句

1、DDL数据结构语言

1）创建数据库 
CREATE DATABASE 数据库名 【DEFAULT CHARACTER UTF8】
2）创建表
CREATE TABLE 表名（字段1 数据类型（长度） 约束，字段2 数据类型（长度）数据类型 约束。。。）

3）删除数据库
DROP DATABASE 数据库名
4）删除表名
DROP TABLE 表名

5）修改数据库的字符编码集
ALTER DATABASE 数据库名 CHARACTER set utf8
6)修改表的字符编码集
ALTER TABLE 表名 CHARACTER SET UTF8
7)设置查询结果集的编码格式
SET CHARACTER_SET_RESULTS = UTF8;
8) 修改表的名字
ALTER TABLE 表名 RENAME 新表名
9）修改字段的数据类型
ALTER TABLE 表名 MODIFY 字段名 列的新类型
9）修改字段
ALTER TABLE 表名 CHANGE 旧表名 新字段名 数据类型
9）添加字段
ALTER TABLE 表名 add 字段名 数据类型（长度）
10）删除字段
ALTER TABLE 表名 drop 字段名

11）清空表
TRUNCATE 表名    会将主键的记录也归1

2、DML 数据操作语言

1）插入数据
INSERT INTO 表名 （字段1，字段2，。。。） VALUES(值1，值2.。。。。)
插入一行的所有字段数据
INSERT INTO 表名  VALUES（值1，值2）

2）修改一条数据
UPDATE 表名 SET 字段 = 值 WHERE 条件

3）删除一条数据
DELETE FROM 表名 WHERE 条件
4)删除表内全部数据
DELETE FROM 表名

3、DQL数据查询语言

普通的简单查询：
1）查询一条数据的全部信息
SELECT * FROM 表名 WHERE 条件
2）分组查询
SELECT * FROM 表名 WHERE 条件 GROUP BY 分组的依据
3）在分组的基础上再次删选
SELECT * FROM 表名 WHERE 条件 GROUP BY 分组的依据 HAVING 条件
4）排序
SELECT * FROM 表名 WHERE 条件 DESC 字段
4）去除重复的值
SELECT DISTINCT（字段） FROM 表名 WHERE 条件

聚合查询：
AVG(字段) 求平均值
MIN(字段) 求最小值
MAX(字段) 求最大值
COUNT(字段) 求总结果数
SUM(字段) 求和
LIMIT m ,n 截取结果
#聚合函数只能在分组的条件下才能使用，默认把表中的数据分为一组

ifnull(m,n) 对null的值处理

子查询：
SELECT * FROM 表名 WHERE 条件（SELECT * FROM 表名 WHERE 条件）

级联查询 | 联合查询：
SELECT * FROM 表1，表2 WHERE 表1.字段 = 表2.字段

内来链接查询：
SELECT * FORM 表1 JOIN 表2 ON 条件
外连接查询：
SELECT * FROM 表1 LEFT JOIN 表2 ON 条件
SELECT * FROM 表1 RIGHT JOIN 表2 ON 条件

4、DCL数据控制语言

用来控制权限

授权
GRANT 权限名 ON 数据库名.表名 to 用户名@ip;
回收权限
REVOKE 权限名 ON 数据库名.表名 FROM 用户名@ip；
删除用户
DROP USER 用户名@ip；

5、DTL事务语言

COMMIT 确人对数据的操作
ROLLBACK 取消对数据的操作

开启事务
start TRANSACTION 

查看数据库的事务提交状态
SHOW VARIABLES LIKE '%AUTOCOMMIT'

设置手动提交事务
SET AUTOCOMMIT = 0;

取消自动提交事务
SET AUTOCOMMIT = 1;


事务的四大特性：
原子性、一致性、隔离性、持久性

MySQL数据库的事务隔离级别：

1、READ UNCOMMITTED 读取未提交的数据，造成的问题是脏读
2、RED COMMITTED 读取已提交的数据，造成的问题是不可重复的读取
3、REPEATEABLE READ 可重读读，造成的问题是幻读（MySQL数据库默认支持的隔离级别）
4、SERIALIZABLE 可串行化 强制事务排队

查看当前数据的事务隔离级别
SELECT @@TX_ISOLATION
设置事务的隔离级别：
SET GLOBAL | SESSION TRANSACTION ISOLATION LEVEL 隔离级别

四、数据库的三大范式

第一范式：字段不可分割，每个字段只能有一个字段

第二范式：非主键字段完全依赖主键字段

第三范式：非主键字段唯一完全依赖主键字段

五、数据库中的索引

建立索引
CREATE INDEX 索引名称 ON 表名（字段）
索引的结构：
BTREE、R_TREE\HASH\FULL_TEXT
建立索引的原则：
1、查询次数多，数据量大的数据表字段
2、查询条件中用到的字段
3、使用唯一约束添加索引
4、使用段索引
5、使用最左前缀法则


造成索引失效的几种情况：
1、字符不使用单引号
2、范围查询，最左边的条件不出发索引
3、违反最左最左前缀法则
4、在索引列上进行运算操作
5、用or链接的查询，后面的字段没有建立索引
6、模糊查询时%放在最前面
7、not in

MySQL数据操作时的一般优化
查询时：
1、适当的建立索引
2、避免进行索引失效的操作

插入数据时：
1、大批量插入数据时关闭唯一性校验、手动提交事务
2、使用load命令来导入数据，并严格按照对数据格式来导入
3、一条SQL语句插入多条数据
4、按照字段顺序和主键顺序插入数据

排序时：
1、通过索引字段排序
2、filesort 通过文件系统排序
3、适当的提高srot_buffer_size 和max_length_for_sort_data系统变量

MySQL数据操作时的一般优化
查询时：
1、适当的建立索引
2、避免进行索引失效的操作

插入数据时：
1、大批量插入数据时关闭唯一性校验、手动提交事务
2、使用load命令来导入数据，并严格按照对数据格式来导入
3、一条SQL语句插入多条数据
4、按照字段顺序和主键顺序插入数据

排序时：
1、通过索引字段排序
2、filesort 通过文件系统排序
3、适当的提高srot_buffer_size 和max_length_for_sort_data系统变量