Mysql 笔记整理

!!! 虽然Mysql 不区分大小写但是为了规范建议大写

## 1. 数据库操作

1.1 登录数据库

mysql -u root -p

C:\Users\LuZhenFang>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

==登录== 到数据库系统

1.2 查询数据库

SHOW DATABASES;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| baidu_db           |
| information_schema |
| music              |
| mygamedb           |
| mysql              |
| performance_schema |
| runoob             |
| sys                |
| table_db           |
+--------------------+
9 rows in set (0.09 sec)

查看当前数据库系统下所有的==数据库==

1.3 选择数据库

USE xxx;

mysql> use music;
Database changed
mysql>

选择在当前数据库系统中==选择==一个数据库

1.4 查询数据库

1. 查询全部

SELECT * FROM xxx;

mysql> select * from users;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | aaa      | bbb      |
|  2 | ccc      | ddd      |
|  3 | lzf      | 123      |
+----+----------+----------+

查询 xxx 表中的==全部==数据

2. 查询指定

SELECT * FROM xxx WHERE ....;

mysql> select * from users where id = 1;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | aaa      | bbb      |
+----+----------+----------+
1 row in set (0.00 sec)

mysql>

查询 xxx 表中 ==id = 1== 的数据

mysql> select * from users where username = "lzf";
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  3 | lzf      | 123      |
+----+----------+----------+
1 row in set (0.00 sec)

mysql>

1.5 创建数据库

CREATE DATABASE xxx;

mysql> create database test;
Query OK, 1 row affected (0.77 sec)

mysql>

在数据库系统中==创建==一个数据库

1.6 退出数据库

exit;

mysql> exit;
Bye
C:\Users\LuZhenFang>

==退出==当前数据库系统

2. 数据表操作

2.1 创建数据表

CREATE TABLE XXX;

CREATE TABLE pet (
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE);

其中, name ... 是 ==字段名== 后面的是 ==字段类型==

mysql> CREATE TABLE pet (
    ->     name VARCHAR(20),
    ->     owner VARCHAR(20),
    ->     species VARCHAR(20),
    ->     sex CHAR(1),
    ->     birth DATE,
    ->     death DATE);
Query OK, 0 rows affected (0.36 sec)


mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet            |
+----------------+
1 row in set (0.00 sec)

mysql>

2.2 查看数据表

SHOW TABLES;

mysql> use table_db;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_table_db |
+--------------------+
| classinfo          |
+--------------------+
1 row in set (0.00 sec)

查看当前数据库中所有的==数据表==

2.3 查看表结构

DESCRIBE xxx; |DESC xxx;

mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.09 sec)

查看指定数据表的 ==结构类型==

2.4 添加表数据

INSERT INTO xxx;

INSERT INTO pet
VALUES("puffball","Diane","hamster","f","1999-03-30",NULL);

插入语句使用 ==INSERT== 命令

mysql> INSERT INTO pet
    -> VALUES("puffball","Diane","hamster","f","1999-03-30",NULL);
Query OK, 1 row affected (0.15 sec)


mysql> select * from pet;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

向 pet 表中插入一段数据

VALUES 和参数表结构要==一致==

mysql> INSERT INTO pet
    -> VALUES("旺财","周星驰","狗","公","1990-01-01",NULL);
Query OK, 1 row affected (0.15 sec)

向 pet 表中 ==插入==一条数据

name:旺财

owner:周星驰

species: 狗

sex:公

birth:1990-01-01

death:NULL

mysql> select * from pet;
+----------+-----------+---------+------+------------+-------+
| name     | owner     | species | sex  | birth      | death |
+----------+-----------+---------+------+------------+-------+
| puffball | Diane     | hamster | f    | 1999-03-30 | NULL  |
| 旺财     | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
+----------+-----------+---------+------+------------+-------+
3 rows in set (0.00 sec)

2.5 插多行数据

INSERT INTO xxx

插入多行数据本质也是 ==逐行==

执行 SQL 语句

在编程中可用循环语句等

INSERT INTO pet VALUES("Fluffy","Harold","Cat","f","1993-02-04",NULL);
INSERT INTO Pet VALUES("Claws","Gwen","Cat","m","1994-05-09",NULL);

mysql> INSERT INTO pet VALUES("Fluffy","Harold","Cat","f","1993-02-04",NULL);
Query OK, 1 row affected (0.47 sec)
mysql> INSERT INTO Pet VALUES("Claws","Gwen","Cat","m","1994-05-09",NULL);
Query OK, 1 row affected (0.14 sec)


mysql> select * from pet;
+----------+-----------+---------+------+------------+-------+
| name     | owner     | species | sex  | birth      | death |
+----------+-----------+---------+------+------------+-------+
| puffball | Diane     | hamster | f    | 1999-03-30 | NULL  |
| puffball | Diane     | hamster | f    | 1999-03-30 | NULL  |
| 旺财     | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
| Fluffy   | Harold    | Cat     | f    | 1993-02-04 | NULL  |
| Claws    | Gwen      | Cat     | m    | 1994-05-09 | NULL  |
+----------+-----------+---------+------+------------+-------+
5 rows in set (0.00 sec)

2.6 删除表数据

DELETE FROM xxx WHERE key =“ xxx”

这里 DELETE 用于==删除==表数据

mysql> select * from pet;
+----------+-----------+---------+------+------------+-------+
| name     | owner     | species | sex  | birth      | death |
+----------+-----------+---------+------+------------+-------+
| puffball | Diane     | hamster | f    | 1999-03-30 | NULL  |
| puffball | Diane     | hamster | f    | 1999-03-30 | NULL  |
| 旺财     | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
| Fluffy   | Harold    | Cat     | f    | 1993-02-04 | NULL  |
| Claws    | Gwen      | Cat     | m    | 1994-05-09 | NULL  |
+----------+-----------+---------+------+------------+-------+
5 rows in set (0.00 sec)

mysql> delete from pet where name = "puffball";
Query OK, 2 rows affected (0.09 sec)

mysql> select * from pet;
+--------+-----------+---------+------+------------+-------+
| name   | owner     | species | sex  | birth      | death |
+--------+-----------+---------+------+------------+-------+
| 旺财   | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
| Fluffy | Harold    | Cat     | f    | 1993-02-04 | NULL  |
| Claws  | Gwen      | Cat     | m    | 1994-05-09 | NULL  |
+--------+-----------+---------+------+------------+-------+
3 rows in set (0.00 sec)

2.7 修改表数据

UPDATE pet SET xxx =“ ”

这里用 update 来==修改==表数据

需要注意的是 update 后不要忘记要操作的==表名==

update pet set name ="旺旺财" where owner ="周星驰";

mysql> update pet set name ="旺旺财" where owner ="周星驰";
Query OK, 1 row affected (0.18 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from pet;
+-----------+-----------+---------+------+------------+-------+
| name      | owner     | species | sex  | birth      | death |
+-----------+-----------+---------+------+------------+-------+
| 旺旺财    | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
| Fluffy    | Harold    | Cat     | f    | 1993-02-04 | NULL  |
| Claws     | Gwen      | Cat     | m    | 1994-05-09 | NULL  |
+-----------+-----------+---------+------+------------+-------+
3 rows in set (0.00 sec)

mysql>

3. 条件约束

PK primer key 主键
- 每行元素==独一无二==标识
- 不能有空值
- 每个表只能有==一个==主键
- 由一列或者多列组成
NN not null 非空
UQunique唯一
- 该项数据==不能重复==
- 允许一条可以为==NUL==L
B binary二进制数据
UN unsigned无符号
ZF zero fill 填充0
AI auto increment 自增
G generated生成列

3.1 主键约束

Primary key ==唯一==确定一张表中的一条记录==不重复且不为空==

创建约束

CREATE TABLE user(
	id int primary key,
    name varchar(20)
);

mysql> CREATE TABLE user(
    ->  id int primary key,
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (0.52 sec)

mysql>

在这里我们创建了一个 user 表,它有两个字段,其中 id 是主键.

因此 id 不可重复且 不为空 ==唯一性==

添加数据

INSERT INTO user VALUES(1,"张三");
INSERT INTO user VALUES(2,"李四");
INSERT INTO user VALUES(3,"王五");
INSERT INTO user VALUES(4,"赵六");
INSERT INTO user VALUES(5,"宋七");

重复添加

INSERT INTO user VALUES(1,"旺财")

mysql> INSERT INTO user VALUES(1,"旺财");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>

这里可以看到插入失败了

因为主键约束 ==只能==有一个 id 为 1 的记录

INSERT INTO user VALUES(NULL,"旺财");

mysql> INSERT INTO user VALUES(NULL,"旺财");
ERROR 1048 (23000): Column 'id' cannot be null
mysql>

这里 id 为NULL 也添加失败了

由此可见主键的唯一和非空约束特性

联合主键

PRIMARY KEY(Key1,key2)

联合主键约束的主键加起来不重复 (==或==)

CREATE TABLE user2(
    id int,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY KEY(id,name)
);

mysql> CREATE TABLE user2(
    ->     id int,
    ->     name VARCHAR(20),
    ->     password VARCHAR(20),
    ->     PRIMARY KEY(id,name)
    -> );
Query OK, 0 rows affected (0.36 sec)

INSERT INTO user2 VALUES(1,"张三","123");
INSERT INTO user2 VALUES(2,"张三","123");
INSERT INTO user2 VALUES(1,"李四","123");

INSERT INTO user2 VALUES(1,"李四","' at line 2
mysql> INSERT INTO user2 VALUES(1,"张三","123");
Query OK, 1 row affected (0.13 sec)

mysql> INSERT INTO user2 VALUES(2,"张三","123");
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO user2 VALUES(1,"李四","123");
Query OK, 1 row affected (0.17 sec)

mysql> SELECT * FROM user2;
+----+--------+----------+
| id | name   | password |
+----+--------+----------+
|  1 | 张三   | 123      |
|  1 | 李四   | 123      |
|  2 | 张三   | 123      |
+----+--------+----------+
3 rows in set (0.00 sec)

mysql>

这里插入全部成功了

第一条正常插入所以没问题

第二条虽然名字也是张三但是 id 和第一条不同所以也插入成功了

第三条虽然 id 和第一条一样但是 username 不同所以也插入成功了

3.2 自增约束

PRIMARY KEY AUTO_INCREMENT

CREATE TABLE user3(
	id int PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

mysql> CREATE TABLE user3(
    ->  id int PRIMARY KEY AUTO_INCREMENT,
    ->     name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.36 sec)

mysql>

INSERT INTO user3 (name) VALUES("张三");

这里插入一条记录这里 (name) 代表 指定字段,因为主键是 id 并且自增所以无需填写

mysql> INSERT INTO user3 VALUES("李四");
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql>

==指定字段必须填写==

当自增约束和主键约束在一起时会==自动== 管控值

添加主键

ALTER TABLE xxx

当我们创建一个表的时候忘记添加了主键我们就需要 修改表结构 来进行添加主键

ALTER 用来修改

CREATE TABLE user4(
    id int,
    name VARCHAR(20)
);

ALTER TABLE user4 ADD PRIMARY KEY(id);

mysql> CREATE TABLE user4(
    ->     id int,
    ->     name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.36 sec)

mysql> DESCRIBE user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE user4 ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

删除主键

ALTER TABLE xxx DROP PRIMARY KEY:

ALTER TABLE user4 DROP PRIMARY KEY;

mysql> DESCRIBE user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE user4 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

修改主键

ALTER TABLE user4 MODIFY id int PRIMARY KEY;

mysql> ALTER TABLE user4 MODIFY id int PRIMARY KEY;
Query OK, 0 rows affected (0.71 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

3.3 唯一约束

UNIQUE

unique 约束修饰的字段的==值不可==以==重复==

这个值相对于一条记录是 ==唯一==的

应用场景: 用户名唯一

添加约束

CREATE TABLE user5(
	id int,
    name VARCHAR(20)
);

ALTER TABLE user5 ADD UNIQUE(name);

mysql> CREATE TABLE user5(id int,name VARCHAR(20));
Query OK, 0 rows affected (0.41 sec)

mysql> DESCRIBE user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE user5 ADD UNIQUE(name);
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

尝试添加数据

mysql> INSERT INTO user5 VALUES(1,"张三");
Query OK, 1 row affected (0.15 sec)

mysql> INSERT INTO user5 VALUES(1,"张三");
ERROR 1062 (23000): Duplicate entry '张三' for key 'name'
mysql>

这里可以看到添加失败了,因为我们的 name 是唯一约束, 所以不可以出现==重复值==!

当然我们也可以在创建表的时候添加唯一约束

CREATE TABLE user6(
	id int,
    name VARCHAR(20),
    UNIQUE(name)
);

CREATE TABLE user6(
	id int,
    name VARCHAR(20) UNIQUE
);

mysql> CREATE TABLE user6(
    ->  id int,
    ->     name VARCHAR(20),
    ->     UNIQUE(name)
    -> );
Query OK, 0 rows affected (0.45 sec)

mysql> DESCRIBE user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

联合约束

当然唯一约束也可以联合

CREATE TABLE user7(
	id int,
    name VARCHAR(20),
    UNIQUE(id,name)
);

mysql> CREATE TABLE user7(
    ->  id int,
    ->     name VARCHAR(20),
    ->     UNIQUE(id,name)
    -> );
Query OK, 0 rows affected (0.50 sec)

mysql> DESCRIBE user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

尝试插入数据

INSERT INTO user7 VALUES(1,"张三");
INSERT INTO user7 VALUES(2,"张三");
INSERT INTO user7 VALUES(1,"李四");




mysql> INSERT INTO user7 VALUES(1,"张三");
Query OK, 1 row affected (0.12 sec)

mysql> INSERT INTO user7 VALUES(2,"张三");
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO user7 VALUES(1,"李四");
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM user7;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    1 | 李四   |
|    2 | 张三   |
+------+--------+
3 rows in set (0.00 sec)

mysql>

由此可见我们也是插入成功了

说白还是 id 和 name 联合在一起不重复即可

删除约束

ALTER TABLE user7 DROP INDEX name,id;

修改约束

MODIFY

ALTER TABLE user7 MODIFY name VARCHAR(20) UNIQUE;

创建:建表的时候添加约束
添加:使用ALTER ...ADD
修改:ALTER ... MODIFY
删除: ALTER .... DROP

与主键不同的是唯一约束可以为空

而主键约束则是 唯一约束 且 不为空

3.4 非空约束

NOT NULL

not null顾名思义 ,就是修饰的字段==不能为空==

CREATE TABLE user8(
	id int,
    name VARCHAR(20) NOT NULL
);


mysql> CREATE TABLE user8(
    ->  id int,
    ->     name VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.37 sec)

mysql> DESCRIBE user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

INSERT INTO user8 (id) VALUES(1);

mysql> INSERT INTO user8 (id) VALUES(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql>

INSERT INTO user8 VALUES(1,"张三");



mysql> INSERT INTO user8 VALUES(1,"张三");
Query OK, 1 row affected (0.15 sec)

mysql> SELECT * FROM user8;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
+------+--------+
1 row in set (0.00 sec)

mysql>

当然我们也可以只写 name 不写 ID

INSERT INTO user8 (name) VALUES("李四");

mysql> INSERT INTO user8 (name) VALUES("李四");
Query OK, 1 row affected (0.13 sec)

mysql> SELECT * FROM user8;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
| NULL | 李四   |
+------+--------+
2 rows in set (0.00 sec)

mysql>

3.5 默认约束

DEFAULT

当插入字段值的时候,如果没有传值,就会使用==默认值==

当有值传递则不会使用默认值

CREATE TABLE user9(
	id int,
    name VARCHAR(20),
    age int DEFAULT 10
);


mysql> CREATE TABLE user9(
    ->  id int,
    ->     name VARCHAR(20),
    ->     age int DEFAULT 10
    -> );
Query OK, 0 rows affected (0.48 sec)

mysql> DESCRIBE user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | 10      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

INSERT INTO user9 (id,name) VALUES(1,"张三");

mysql> SELECT * FROM user9;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | 张三   |   10 |
+------+--------+------+
1 row in set (0.00 sec)

3.6 外键约束

FOREIGN KEY

涉及到两个表, 父表,和子表

主表
副表

-- 班级表
CREATE TABLE classes(
	id int PRIMARY KEY,
    name VARCHAR(20)
);
-- 学生表
CREATE TABLE students(
    id int PRIMARY KEY,
    name VARCHAR(20),
    class_id int,
    FOREIGN KEY(class_id) REFERENCES classes(id)
);

FOREIGN KEY (==字段名==) REFERENCES ==表名==(==字段==)

INSERT INTO classes VALUES(1,"一班");
INSERT INTO classes VALUES(2,"二班");
INSERT INTO classes VALUES(3,"三班");
INSERT INTO classes VALUES(4,"四班");

INSERT INTO students VALUES(1001,"张三",1);
INSERT INTO students VALUES(1002,"李四",2);
INSERT INTO students VALUES(1003,"王五",3);
INSERT INTO students VALUES(1004,"赵六",4);

可以看到正常插入

INSERT INTO students VALUES(1005,"宋七",5);


mysql> INSERT INTO students VALUES(1005,"宋七",5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
mysql>

这里插入失败了因为 5 不存在主表中 ,

主表(父表) classes 没有过数据值, 在子表中是不可以使用的

主表中的记录被子表引用,是不可以被删除的

4.设计范式

数据库表字段信息不能有冗余
每个表都要有主键
表与表之间的关于要使用主键进行关联

4.1 1NF

数据表中所有字段都是不可分割的原子值

CREATE TABLE student2(
	id INT,
    name VARCHAR(20),
    address VARCHAR(30)
);

INSERT INTO student2 VALUES(1,"张三","山西省太原市小店区榆古路东一号山西应应用科技学院");
INSERT INTO student2 VALUES(2,"李四","山西省晋中市太谷县白塔寺山西应用科技学院南校区");
INSERT INTO student2 VALUES(3,"王五","山西省临汾市xxx");



mysql> SELECT * FROM student2;
+------+--------+--------------------------------------------------------------------------+
| id   | name   | address                                                                  |
+------+--------+--------------------------------------------------------------------------+
|    1 | 张三   | 山西省太原市小店区榆古路东一号山西应应用科技学院                         |
|    2 | 李四   | 山西省晋中市太谷县白塔寺山西应用科技学院南校区                           |
|    3 | 王五   | 山西省临汾市xxx                                                          |
+------+--------+--------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

字段值还可以==继续拆分==的就不满足第一范式

因此上表不满足 1NF

继续拆分字段

CREATE TABLE student3(
	id INT,
    name VARCHAR(20),
    country VARCHAR(30),
    province VARCHAR(30),
    city VARCHAR(30),
    details VARCHAR(30)
);

INSERT INTO student3 VALUES(1,"张三","中国","山西省","太原市","小店区");
INSERT INTO student3 VALUES(2,"李四","中国","山西省","晋中市","太谷县");
INSERT INTO student3 VALUES(3,"王五","中国","山西省","临汾市","xxx");




mysql> SELECT * FROM student3;
+------+--------+---------+-----------+-----------+-----------+
| id   | name   | country | province  | city      | details   |
+------+--------+---------+-----------+-----------+-----------+
|    1 | 张三   | 中国    | 山西省    | 太原市    | 小店区    |
|    2 | 李四   | 中国    | 山西省    | 晋中市    | 太谷县    |
|    3 | 王五   | 中国    | 山西省    | 临汾市    | xxx       |
+------+--------+---------+-----------+-----------+-----------+
3 rows in set (0.00 sec)

mysql>

范式设计越详细对于某些实际操作可能更好,但不一定都是好处!

4.2 2NF

第二范式不许是满足第一范式前提下

要求:==除主键==外每一列都必须==完全依赖==主键

如果出现不完全依赖,只可能在发生在联合主键的情况下

下面来设计一个订单表

CREATE TABLE myorder(
	product_id INT,
    customer_id INT,
    product_name VARCHAR(20),
    customer_name VARCHAR(20),
    PRIMARY KEY(product_id,customer_id)
);

虽然设计了一个这样的表但是有一个十分明显的问题

除主键外其他列只依赖于主键部分字段

解决方案:==拆表==

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)
);

分成了三个表后就满足了 2NF!

4.3 3NF

必须先满足 2NF

除了主键其他列不能有传递依赖

CREATE TABLE myorder(
    order_id INT PRIMARY KEY,
	product_id INT,
    customer_id INT,
    customer_phone VARCHAR(20)
);

因为这里 customer_phone 可以通过 customer_id 确定

但是 customer_phone 可能和 id 有关系

customer_id 又可以通过id 找到

因此产生了冗余

所以

将 customer_phone 放到 customer 表中更合理

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

5. 表关系

OneToOne一对一
OneToMany 一对多
ManyToMany多对多

6. 查询练习

学生表Student

学号
姓名
性别
出生日期
班级

CREATE TABLE student (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) NOT NULL,
    birthday DATE, -- 生日
    class VARCHAR(20) -- 所在班级
);

课程表Course

课程号
课程名称
教师编号

CREATE TABLE course (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    t_no VARCHAR(20) NOT NULL, -- 教师编号
    -- 表示该 tno 来自于 teacher 表中的 no 字段值
    FOREIGN KEY(t_no) REFERENCES teacher(no) 
);

成绩表Score

学号
课程号
成绩

CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL, -- 学生编号
    c_no VARCHAR(20) NOT NULL, -- 课程号
    degree DECIMAL,	-- 成绩
    -- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值
    FOREIGN KEY(s_no) REFERENCES student(no),	
    FOREIGN KEY(c_no) REFERENCES course(no),
    -- 设置 s_no, c_no 为联合主键
    PRIMARY KEY(s_no, c_no)
);

教师表Teacher

教师编号
教师名字
教师性别
出生年月日

CREATE TABLE teacher (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) NOT NULL,
    birthday DATE,
    profession VARCHAR(20) NOT NULL, -- 职称
    department VARCHAR(20) NOT NULL -- 部门
);

添加数据

-- 添加学生表数据
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');

-- 添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');

-- 添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');

-- 添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

Task

查询student 表的所有记录。
查询s tudent表中的所有记录的sname、ssex和class列。
查询教师所有的单位即不重复的depart列。
查询score表中成绩在60到80之间的所有记录。
查询score表中成绩为85, 86或88的记录。
查询student表中“95031 ”班或性别为“女”的同学记录。
以clas s降序查询student表的所有记录。
以cno升序、 degree降序查询score表的所有记录。
查询“95031"班的学生人数。
查询score表中的最高分的学生学号和课程号。(子查询或者排序)

3. CURD

3.1 增

INSERT

3.2 删

DELETE

3.3 改

UPDATE

3.4 查

SELECT

文章作者: 凉拌西蓝花🥦

文章连结: https://lzfblog.cn/?p=4

微信扫一扫