回顾大纲

1. 管理逻辑库和数据表
   • 创建、删除、修改逻辑库和数据表
2. 了解常用的数据类型和约束
   • 字符串、整数、浮点数、精确数字、日期、枚举。
   • 住建约束、非空约束、唯一约束、外键约束等
3. 掌握索引运行机制和使用原则
   • 培训为什么可以提高数据检索速度？
   • 怎么创建和删除索引？
   • 什么条件下使用索引？

一、定义逻辑库和数据表

1.SQL语言分类

DML：添加、修改、删除、查询

DCL：用户、权限、事务

DDL：逻辑库、数据表、视图、索引

2.SQL语句注意事项

• SQL语句不区分大小写，但是字符串区分大小写。
• SQL语句必须以分好结尾。
• SQL语句中的空白和换行没有限制，但是不能破坏语法。

3.SQL语句注释：

• 用#号注释
• /**/ 多行注释

4.创建、删除、展示逻辑空间

• CREATE DATABASE test; # 创建
• DROP DATABASE test;# 删除
• SHOW DATABASES;# 展示

创建数据表：

CREATE TABLE 数据表名(
    列名1 数据类型 [约束] [COMMENT 注释]，
  列名2 数据类型 [约束] [COMMENT 注释]，
  ......
)[COMMENT = 注释];

USE test;
CREATE TABLE student(
    id INT UNSIGNED PRIMARY KEY,
    name VARCHAR(20)  NOT NULL,
    sex CHAR(1)      NOT NULL,
    birthday DATE     NOT NULL,
    tel CHAR(11)      NOT NULL,
    remark VARCHAR(200)
); 

向数据表中写入内容：

INSERT INTO student VALUES(1,"张三","男","1988-08-08","13885269625",NULL)

数据表的其他操作：

SHOW tables; # 查看当前逻辑空间里面有哪些数据表
DESC student; # 查看表的具体情况
SHOW CREATE TABLE student; # 查看创建表的sql语句
DROP TABLE student; # 删除这个表

二、数据类型

1.数字类型：数字

小整数、普通整数的区间是多少？1-100？还是0-255？后面核实

一般都是用int

对精度要求很高的数据需要用DECIMAL，例如保存钱。

十进制的浮点数转成二进制的浮点数会丢数据。

2.数据类型：字符串

一般常用的就是CHAR、VARCHAR。如果字符串很长，一般我们采用NoSQL来存储，这样效率更高。

3.数据类型：日期类型

三、数据定义语言：修改表结构

1.添加字段

ALTER TABLE 表名称
   ADD 列1 数据类型[约束] [COMMENT 注释],
 ADD 列2 数据类型[约束] [COMMENT 注释],
 ......; # 注意这里结尾是分号

USE test;
ALTER TABLE test # 注意这里是没有小括号的
ADD sex char(1) NOT NULL,
ADD address VARCHAR(50) NOT NULL; # 这里结尾是分号

2.对已有字段的修改

ALTER TABLE 表名称
MODIFY 列1 数据列席[约束] [COMMENT 注释],
MODIFY 列1 数据列席[约束] [COMMENT 注释];

ALTER TABLE test
MODIFY sex VARCHAR(300) NOT NULL; # 修改时 关键字是MODIFAY

修改字段名称

ALTER TABLE 表名称
CHANGE 列1 新列名1 数据列席[约束] [COMMENT 注释],
CHANGE 列 新列名2 数据列席[约束] [COMMENT 注释],
.....; # 最后一行还是需要用分号

ALTER TABLE test
CHANGE sex sexxx VARCHAR(100) not null;
DESC test;

3.删除字段

ALTER TABLE 表名称
DROP 列1，
DROP 列2，
...;

ALTER TABLE test
DROP sexxx;
DESC test;

四、字段约束

1.数据库的范式

第一范式：原子性：

原子性：数据表每一列都是不可分割的数据项

这条数据就不符合第一范式。因为班级数据还可以拆分为高三和1班

这样符合第一范式，原子性的要求。

第二范式：唯一性：

数据表中的每条九路必须是唯一的。为了实现区分，通常要为表加上一个列用来存储唯一标识。例如id，学号等，这个唯一属性被称作住建列。

例如这位学号为100的同学，一天之内参加了两次计算机考试，但是考试成绩相同，这样就无法区分成绩到底是否是重复的。

为了区分数据，我们可以为数据表加上一个id或者流水号

这样数据才具有唯一性。

第三范式：关联性：

（设计数据库时必须要满足的）每列都与主键有直接关系，不存在传递依赖

我们来分析这张表，儿子和女儿都可以和爸爸产生直接的关联。但是女儿的玩具以及女儿的衣服跟爸爸不能产生直接的关联，所以不满足第三范式。这种情况不允许的。

正确的设计应该是这样：

原来的表应该拆成两张表来设计，这样两张表都满足了第三范式。玩具、衣服都是跟女儿产生直接依赖。

依照第三范式，数据可以拆分保存到不同的数据表，彼此保持关联

例如：部门表和员工表就应该拆分成两个表，然后使两个表产生关联。

部门表：

员工表：

通过员工表中的部门字段，来关联两个表。每个员工都会有自己的部门。

2.四种字段约束

MySQL中的字段约束共有四种：

1.主键约束

• 主键约束要求字段的值在全表必须唯一，而且不能为NULL

• 主键一定要使用数字类型，这样检索速度非常快

• 主键为数字类型，还可以设置自动增长

  CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    ...
  );
  

2.非空约束

• 非空约束要求字段的值不能为NULL值

• NULL值是没有数据的意思，而不是空字符串

    CREATE TABLE teacher(
        id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(100) NOT NULL,
      married BOOLEAN NOT NULL DEFAULT FALSE # 要求非空，如果不添加数据，默认值是false
    );
  

3.唯一约束

• 唯一约束要求字段值如果不为NULL，那么在全表必须唯一

  CREATE TABLE teacher(
    ......
    tel CHAR(11) NOT NULL UNIQUE # 加上UNIQUE 必须全表唯一
  );
  

4.外键约束

• 外键约束用来保证关联数据的逻辑关系

  部门表：父表

  deptno	dname	tel
  10	财务部	1001
  20	技术部	1002
  30	销售部	1003

  # 创建父表的sql语句
  CREATE TABLE t_dept(
  deptno INT UNSIGNED PRIMARY KEY,
  dname VARCHAR(20) NOT NULL UNIQUE,
  tel CHAR(11) UNIQUE
  );
  

员工表：子表

员工表依赖部门表，所以外键约束是写在字表上的

# 创建子表的sql语句
CREATE TABLE t_emp(
  empno INT UNSIGNED PRIMARY KEY,
  ename VARCHAR(20) NOT NULL,
  sex ENUM("男","女") NOT NULL, # ENUM是枚举字段值，字段值只能是规定好的
  deptno INT UNSIGNED,
  hiredate DATE NOT NULL,
  FOREIGN KEY (deptno) REFERENCES t_dept(deptno) # 关联父表
);

我们一般不推荐外键约束。

外键约束的闭环问题

如果形成外键闭环，我们将无法删除任何一张表的记录。

外键约束闭环.png

分析一下，如果这种情况，我们怎么删除数据呢？所以一般不推荐外键约束。

五、数据定义语言：索引

数据排序的好处，例如查字典的时候，都是先看第一个字母。来提高查阅速度。

1.如何创建索引

CREATE TABLE 表名称(
    ......,
  INDEX [索引名称] (字段),
  ......
);

CREATE TABLE t_message(
    id INT UNSIGNED PRIMARY KEY,
    content VARCHAR(200) NOT NULL,
    type ENUM("公告","通报","个人通知") NOT NULL,
    create_time TIMESTAMP NOT NULL,
    INDEX idx_type(type) # 利用INDEX 给type添加索引
);

给type字段添加索引之后，要去查找速度就提升很多，因为是利用二叉树的二分查找。

2.如何添加与删除索引

# 给字段添加索引 给已经存在的表添加索引
CREATE INDEX 索引名称 ON 表名(字段);
# 例如：
CREATE INDEX idx_type ON t_message(type)
# 第二种方法 给已经存在的表添加索引
ALTER TABLE 表名称 ADD INDEX [索引名](字段);
# 例如：
ALTER TABLE t_message ADD INDEX idx_type(type)
# 查看数据表的所有索引
SHOW INDEX FROM 表名;
# 例如：
SHOW INDEX FROM t_message;
# 删除某列的索引，删除索引后，对应的二叉树也被删掉了
DROP INDEX 索引名称 ON 表名
# 例如：
DROP INDEX idx_type ON t_message;

3.索引的使用原则

1. 数据量很大，而且经常被查询的数据表可以设置索引
2. 数据写入大于查询，不适合设置索引。例如：日志表，记录都是用户操作，大量写入，基本不查询，就不适合设置索引。
3. 索引只添加在经常被用作检索条件的字段上面。例如：姓名，部门编号等。
4. 不要在大字段上创建索引。长度超过50个字符的字段不适合创建索引。

总结：

1.逻辑库和数据表的管理

2.MySQL常用数据类型

3.MySQL的字段约束

4.索引机制，懂得什么时候使用索引