MySQL学习笔记

MySQL学习笔记

十月 19, 2020

登录和退出MySQL服务器

1
2
3
4
5
# 登录MySQL
$ mysql -u root -p12345612

# 退出MySQL数据库服务器
exit;

基本语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- 显示所有数据库
show databases;

-- 创建数据库
CREATE DATABASE test;

-- 切换数据库
use test;

-- 显示数据库中的所有表
show tables;

-- 创建数据表
CREATE TABLE pet (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);

-- 查看数据表结构
-- describe pet;
desc pet;

-- 查询表
SELECT * from pet;

-- 插入数据
INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);

-- 修改数据
UPDATE pet SET name = 'squirrel' where owner = 'Diane';

-- 删除数据
DELETE FROM pet where name = 'squirrel';

-- 删除表
DROP TABLE myorder;

建表约束

主键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 主键约束
-- 使某个字段不重复且不得为空,确保表内所有数据的唯一性。
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(20)
);

-- 联合主键
-- 联合主键中的每个字段都不能为空,并且加起来不能和已设置的联合主键重复。
CREATE TABLE user (
id INT,
name VARCHAR(20),
password VARCHAR(20),
PRIMARY KEY(id, name)
);

-- 自增约束
-- 自增约束的主键由系统自动递增分配。
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);

-- 添加主键约束
-- 如果忘记设置主键,还可以通过SQL语句设置(两种方式):
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;

-- 删除主键
ALTER TABLE user drop PRIMARY KEY;

唯一主键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 建表时创建唯一主键
CREATE TABLE user (
id INT,
name VARCHAR(20),
UNIQUE(name)
);

-- 添加唯一主键
-- 如果建表时没有设置唯一建,还可以通过SQL语句设置(两种方式):
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

-- 删除唯一主键
ALTER TABLE user DROP INDEX name;

非空约束

1
2
3
4
5
6
7
8
9
-- 建表时添加非空约束
-- 约束某个字段不能为空
CREATE TABLE user (
id INT,
name VARCHAR(20) NOT NULL
);

-- 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);

默认约束

1
2
3
4
5
6
7
8
9
10
-- 建表时添加默认约束
-- 约束某个字段的默认值
CREATE TABLE user2 (
id INT,
name VARCHAR(20),
age INT DEFAULT 10
);

-- 移除非空约束
ALTER TABLE user MODIFY age INT;

外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 班级
CREATE TABLE classes (
id INT PRIMARY KEY,
name VARCHAR(20)
);

-- 学生表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(20),
-- 这里的 class_id 要和 classes 中的 id 字段相关联
class_id INT,
-- 表示 class_id 的值必须来自于 classes 中的 id 字段值
FOREIGN KEY(class_id) REFERENCES classes(id)
);

-- 1. 主表(父表)classes 中没有的数据值,在副表(子表)students 中,是不可以使用的;
-- 2. 主表中的记录被副表引用时,主表不可以被删除。

数据库的三大设计范式

1NF

只要字段值还可以继续拆分,就不满足第一范式。

范式设计得越详细,对某些实际操作可能会更好,但并非都有好处,需要对项目的实际情况进行设定。

2NF

在满足第一范式的前提下,其他列都必须完全依赖于主键列。如果出现不完全依赖,只可能发生在联合主键的情况下:

1
2
3
4
5
6
7
8
-- 订单表
CREATE TABLE myorder (
product_id INT,
customer_id INT,
product_name VARCHAR(20),
customer_name VARCHAR(20),
PRIMARY KEY (product_id, customer_id)
);

实际上,在这张订单表中,product_name 只依赖于 product_idcustomer_name 只依赖于 customer_id 。也就是说,product_namecustomer_id 是没用关系的,customer_nameproduct_id 也是没有关系的。

这就不满足第二范式:其他列都必须完全依赖于主键列!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT
);

CREATE TABLE product (
id INT PRIMARY KEY,
name VARCHAR(20)
);

CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(20)
);

拆分之后,myorder 表中的 product_idcustomer_id 完全依赖于 order_id 主键,而 productcustomer 表中的其他字段又完全依赖于主键。满足了第二范式的设计!

3NF

在满足第二范式的前提下,除了主键列之外,其他列之间不能有传递依赖关系。

1
2
3
4
5
6
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT,
customer_phone VARCHAR(15)
);

表中的 customer_phone 有可能依赖于 order_idcustomer_id 两列,也就不满足了第三范式的设计:其他列之间不能有传递依赖关系。

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT
);

CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(20),
phone VARCHAR(15)
);

修改后就不存在其他列之间的传递依赖关系,其他列都只依赖于主键列,满足了第三范式的设计!