3-1-1 MySQL基础-数据库创建与修改,约束与索引

SQL语言分类

DML

添加 修改 删除 查询

DCL

用户 权限 事务

DDL

逻辑表 数据表 视图 索引

SQL语言注意事项

SQL语句不区分大小写,但是字符串区分大小写

SQL语句必须以分号结尾

SQL语句中的空白和换行没有限制,但是不能破坏语法

SQL语句的注释

# 这是单行注释

/* 这是多行
*   注释
*/

逻辑库操作

# 展示逻辑库
SHOW DATABASES;
# 新建逻辑库
CREATE DATABASE mingm;
# 删除逻辑库
DROP DATABASE mingm;
# 切换逻辑库
USE mingm;

数据库操作

CREATE TABLE 数据表(

　　列名1 数据类型 [约束] [COMMENT 注释],

　　列名2 数据类型 [约束] [COMMENT 注释]

) [COMMENT = 注释];

DROP 数据表;

# 创建数据表
CREATE TABLE t_student(
    id INT UNSIGNED PRIMARY KEY COMMENT 'ID',
    name VARCHAR(20) NOT NULL COMMENT '名字',
    sex CHAR(1) NOT NULL COMMENT '性别',
    birthday DATE NOT NULL COMMENT '生日',
    tel CHAR(11) NOT NULL COMMENT '号码',
    remark VARCHAR(200) COMMENT '备注'
) COMMENT = '学生表';
# 删除数据表
DROP TABLE t_student;
# 展示逻辑库下的所有表
SHOW TABLES;
# 描述表
DESC t_student;
# 展示建表语句
SHOW CREATE TABLE t_student;

数据类型-数字

类型	大小	说明
TINYINT	1字节	小整数
SMALLINT	2字节	普通整数
MEDIUMINT	3字节	普通整数
INT	4字节	较大整数
BIGINT	8字节	大整数
FLOAT	4字节	单精度浮点数
DOUBLE	8字节	双精度浮点数
DECIMAL	----

不精确的浮点数

十进制的浮点数无法在二进制计算机精确表达.

比如0.2用二进制表示是1/8+1/16+1/128+...

计算精确数字用DECIMAL

数据类型-字符串

类型	大小	说明
CHAR	1-255字符	固定长度字符串
VARCHAR	1-65535字符	不固定长度字符串
TEXT	1-65535字符	不确定长度字符串
MEDIUMTEXT	1-1千6百万字符	不确定长度字符串
LONGTEXT	1-42亿字符	不确定长度字符串

数据类型-日期类型

类型	大小	说明	格式
DATE	3字节	日期	YYYY-MM-DD
TIME	3字节	时间	HH:MM:SS
YEAR	1字节	年份	YYYY
DATETIME	8字节	日期时间	YYYY-MM-DD HH:MM:SS
TIMESTAMP	4字节	时间戳	YYYYMMDD HHMMSS

修改表结构

添加字段

ALTER TABLE 表名称

ADD 列1 数据类型 [约束] [COMMENT 约束],

ADD 列2 数据类型 [约束] [COMMENT 约束],

....;

ALTER TABLE t_student
ADD age TINYINT NOT NULL COMMENT '年龄';

修改字段类型和约束

ALTER TABLE 表名称

MODIFY 列1 数据类型 [约束] [COMMENT 约束],

MODIFY 列2 数据类型 [约束] [COMMENT 约束],

....;

ALTER TABLE t_student
MODIFY age SMALLINT NOT NULL COMMENT '年龄';

修改字段名称

ALTER TABLE 表名称

CHANGE 列1 新列名1 数据类型 [约束] [COMMENT 注释],

CHANGE 列2 新列名2 数据类型 [约束] [COMMENT 注释],

.....;

ALTER TABLE t_student
CHANGE name stu_name VARCHAR(20) NOT NULL COMMENT '学生名字';

删除字段

ALTER TABLE 表名称

DROP 列1,

DROP 列2,

....;

ALTER TABLE t_student
DROP age;

修改表名

ALTER TABLE 表名称 RENAME 新表名;

ALTER TABLE t_student RENAME t_stu;

字段约束

数据库的范式

构造数据库必须遵循一定的规则,这种规则就是范式

目前关系数据库有6种范式,一般情况下,只满足第三范式即可

第一范式:原子性

1.第一范式是数据库的基本要求,不满足这一点就不是关系数据库

2.数据表的每一列都是不可分割的基本数据项,同一列中不能有多个值,不能存在重复的属性

不符合第一范式:

学号	姓名	班级
1000	小明	高三1班

符合第一范式:

学号	姓名	年级	班级
1000	小明	高三	1班

第二范式:唯一性

1.数据表中的每条记录必须是唯一的.为了实现区分,通常要为表加上一个列用来存储这个标识,这个唯一属性被称为主键列

无法区分的数据:

学号	成绩	日期
230	68	2020-09-17
230	68	2020-09-17

数据具有唯一性:

流水号	学号	成绩	日期
20200917001	230	68	2020-09-17
20200917002	230	68	2020-09-17

第三范式:关联性

1.每列都与主键有直接关系,不能存在传递依赖

2.依照第三范式,数据可以保存到不同的数据表,彼此保持联系

不符合第三范式:

爸爸	儿子	女儿	女儿的玩具	女儿的衣服
陈华	陈浩	陈婷婷	海绵宝宝	校服

符合第三范式:

爸爸	儿子	女儿
陈华	陈浩	陈婷婷

 

女儿	女儿的玩具	女儿的校服
陈婷婷	海绵宝宝	校服

四种字段约束

约束字段	关键字	描述
主键约束	PRIMARY KEY	字段唯一,且不能为NULL
非空约束	NOT NULL	字段值不能为空
唯一约束	UNIQUE	字段值唯一,且可以为空
外键约束	FOREIGN KEY	保证关联数据的逻辑性

主键约束

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

建议主键一定要使用数字类型,因为数字的检索速度非常快

如果主键是数字类型,还可以设置自动增长

CREATE TABLE t_student(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
 ...
) COMMENT = '学生表';

非空约束

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

NULL值以为没有值,而不是""字符串

CREATE TABLE t_student(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(20) NOT NULL COMMENT '名字',
    sex CHAR(1) NOT NULL DEFAULT '男' COMMENT'性别',
    ...
) COMMENT = '学生表';

唯一约束

唯一约束要求字段如果不为NULL,那么全表必须唯一

CREATE TABLE t_student(
    ...
    tel CHAR(11) NOT NULL UNIQUE COMMENT '号码',
    ...
) COMMENT = '学生表';

外键约束

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

外键约束定义在子表上

CREATE TABLE t_emp(
    ...
    deptno INT UNSIGNED,
    FOREIGN KEY (deptno) REFERENCES t_dept(deptno)
);

外键约束的的闭环问题:

如果 表之间形成外键闭环,我们将无法删除任何一张表

所以不建议使用外键约束

索引

一旦数据排序之后,查找的速度就会翻倍

MYSQL利用二叉树结构,对数据表的记录排序,从而加速数据的检索速度

如何创建索引

CREATE TABLE 表名称(

　　...,

　　INDEX [索引名称] (字段)

);

CREATE TABLE t_student(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(20) NOT NULL COMMENT '名字',
    sex CHAR(1) NOT NULL DEFAULT '男' COMMENT'性别',
    birthday DATE NOT NULL COMMENT '生日',
    tel CHAR(11) NOT NULL UNIQUE COMMENT '号码',
    remark VARCHAR(200) COMMENT '备注',
    INDEX idx_name (name)
) COMMENT = '学生表';

如何添加和创建索引

CREATE INDEX 索引名称 ON 表名(字段);

ALTER TABLE 表名称 ADD INDEX [索引名] (字段);

SHOW INDEX FROM 表名;

DROP INDEX 索引名称 ON 表名;

# 添加索引
ALTER TABLE t_student ADD INDEX idx_name(name);
CREATE INDEX idx_name ON t_student(name);
# 展示索引
SHOW INDEX FROM t_student;
# 删除索引
DROP INDEX idx_name ON t_student;

索引使用规则

数据量很大,而且经常被查询的数据表可以设置索引

索引只添加在经常被用作检索条件的字段上面

要在大字段上创建索引