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
非空 -
UQ
unique
唯一- 该项数据==不能重复==
- 允许一条可以为==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_phon
e 放到 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