Mysql学习笔记
一、如何使用终端操作数据库
1. 登录和退出服务器
# 登录MySQL
$ mysql -u root -p12345612
# 退出MySQL数据库服务器
exit;
2. 如何查询数据库服务器中所有的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
databases的末尾的分号;是关键
3.如何选中某一个数据库进行操作
在做数据库进行操作时,首先要先选中,如果不选中直接操作,就会出现下面的情况:
mysql> select * from admin;
ERROR 1046 (3D000): No database selected
选中数据库:
mysql> use sys
Database changed
use之后,开始查询数据表中的记录
SQL语句中的查询
mysql> select * from admin;
ERROR 1146 (42S02): Table 'sys.admin' doesn't exist
但是此时,数据库中没有数据。
4. 如何在数据库服务器中创建数据库
#create database 语句创建数据库
mysql> create database test;
Query OK, 1 row affected (0.03 sec)
此时再看数据库里面的内容:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
可以发现里面多了test数据库,现在查看test数据库中有什么东西:
#操作数据库之前先要use一下;
mysql> use test;
Database changed
#使用show tables语句去查看数据库里面有什么数据表;
mysql> show tables;
Empty set (0.04 sec)#发现里面是空的。
查看后发现数据库test中的数据表是空的。
5. 如何创建一个数据表
这个数据表的创建是在上述test数据库的基础上。
#创建一个宠物信息的数据表:
CREATE TABLE pet (
name VARCHAR(20),
owner VARCHAR(20).
species VARCHAR(20),
sex CHAR(1),
birth DATA,
death DATA);#注意最后要有分号。
结果图:
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.05 sec)
再次查看数据表是否创建成功:show tables;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet |
+----------------+
1 row in set (0.00 sec)
就创建好了数据。
6.查看创建的数据表的结构
describe 语句:
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.01 sec)
查询数据表中的记录:
mysql> select * from pet;
Empty set (0.01 sec)
数据表中没有记录。
7. 如何往数据表中添加数据记录
insert into +数据表名字+values(按照创建的时候的格式)
mysql> insert into pet
-> values('巴扎黑','张艺龄','bage','m','2018-12-27',NULL);
Query OK, 1 row affected (0.01 sec)
查看数据表的记录:
mysql> select * from pet;
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| 巴扎黑 | 张艺龄 | bage | m | 2018-12-27 | NULL |
+--------+--------+---------+------+------------+-------+
1 row in set (0.00 sec)
8. Mysql常用数据类型
数值类型
与数据类型对应的数值范围,现在测试TINYINT的数据范围。
#在数据库test中创建一个表:testtype,创建的时候指定表里面的内容名称和类型
mysql> create table testtype(
-> number TINYINT);
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;#这个语句应该换成:describe testtype,可以看到表中的类型。
+----------------+
| Tables_in_test |
+----------------+
| pet |
| testtype |
+----------------+
2 rows in set (0.00 sec)
#describe 语句可以看到表中的类型
mysql> describe testtype;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| number | tinyint | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
#select语句可以看到表中具体的记录内容。
mysql> select * from testtype
-> ;
Empty set (0.00 sec)
mysql> insert into testtype
-> values(127);
Query OK, 1 row affected (0.00 sec)
mysql> insert into testtype
-> values(137);
#137超出了TYNYINT类型的数据范围,所有报错。
ERROR 1264 (22003): Out of range value for column 'number' at row 1
#使用查询语句可以看到表中的记录。
mysql> select * from testtype;
+--------+
| number |
+--------+
| 127 |
| 127 |
+--------+
2 rows in set (0.01 sec)
日期时间类型
字符串类型
数据类型如何选择
日期选择按照格式,数值和字符串按照大小。
9. 如何删除数据?
当前的数据表:
mysql> select * from pet
-> ;
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| 巴扎黑 | 张艺龄 | bage | m | 2018-12-27 | NULL |
| 巴扎黑 | 张艺龄 | bage | m | 2019-12-27 | NULL |
| 牛牛 | 张艺龄 | 斗牛犬 | m | 2019-12-27 | NULL |
| 花花 | 张艺龄 | 串串 | f | 2019-12-27 | NULL |
+--------+--------+---------+------+------------+-------+
4 rows in set (0.00 sec)
- 删除数据表中的某一条:现在想删除巴扎黑那一条:
mysql> delete from pet where name='巴扎黑';
Query OK, 2 rows affected (0.01 sec)
mysql> select * from pet
-> ;
+------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+------+--------+---------+------+------------+-------+
| 牛牛 | 张艺龄 | 斗牛犬 | m | 2019-12-27 | NULL |
| 花花 | 张艺龄 | 串串 | f | 2019-12-27 | NULL |
+------+--------+---------+------+------------+-------+
2 rows in set (0.01 sec)
- 删除整个数据表:drop table +数据表名称
mysql> drop table students;
Query OK, 0 rows affected (0.03 sec)
mysql> desc students;
ERROR 1146 (42S02): Table 'test.students' doesn't exist
10. 如何修改数据?
使用update +表名称+set+修改的项+where + 位置
mysql> update pet set name='牛花花' where species='串串';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 查询记录
mysql> select * from pet
-> ;
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| 牛牛 | 张艺龄 | 斗牛犬 | m | 2019-12-27 | NULL |
| 牛花花 | 张艺龄 | 串串 | f | 2019-12-27 | NULL |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.01 sec)
总结:数据记录常见操作
增加:insert
删除:delete
修改:update
查询:select * from
11. MySQL建表约束
(1)主键约束
他能够唯一确定一张表中的一条记录,我们通过给某个字段添加约束,就可以使得该字段不重复且不为空。
- 如何创建主键约束?
在创建表的时候,primary key
#在创建表指定完类型之后,添加主键约束primary key
mysql> create table users(
-> ID int primary key,
-> name varchar (20));
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet |
| testtype |
| users |
+----------------+
3 rows in set (0.00 sec)
mysql> describe users; #简写 desc users
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into users values(1,'张艺龄');
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+--------+
| ID | name |
+----+--------+
| 1 | 张艺龄 |
+----+--------+
1 row in set (0.00 sec)
mysql> insert into users values(1,'张');
ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY'
- 联合主键约束
mysql> create table users3(
-> id int,
-> name varchar(20),
-> psssword varchar(20),
-> primary key(id,name));#这句话为联合主键约束。
Query OK, 0 rows affected (0.04 sec)
mysql> insert into users3 values(1,'张三','123456');
Query OK, 1 row affected (0.01 sec)
mysql>
#当输入的id和name同时重复时会触发约束报错。
mysql> insert into users3 values(1,'张三','123456');
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'users3.PRIMARY'
当只有一个条件重复,ID或者name时,不报错,是允许的。
mysql> insert into users3 values(1,'张','123456');
Query OK, 1 row affected (0.01 sec)
mysql> select * from users3;
+----+------+----------+
| id | name | psssword |
+----+------+----------+
| 1 | 张 | 123456 |
| 1 | 张三 | 123456 |
+----+------+----------+
2 rows in set (0.00 sec)
当有输入为空NULL时,报错。
mysql> insert into users3 values(NULL,'张','123456');
ERROR 1048 (23000): Column 'id' cannot be null
-总结:联合主键约束,只要加起来不重复就可以,但是任何一个字段都不应该为空。联合主键约束,可以针对字段使用,也可以联合两个字段进行使用。
(2)自增约束
自增约束经常和主键约束联合使用 auto_increment。
#创建一个表4
mysql> create table user4(
-> id int primary key auto_increment,#设置为主键约束,自增约束
-> name varchar(20));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into user4 (name) values('zhangsan')#在user4之后指定字段name进行添加
-> ;
Query OK, 1 row affected (0.01 sec)
mysql> select * from user4;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
mysql> insert into user4 (name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user4;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | zhangsan |#ID序号是自动生成的。
+----+----------+
2 rows in set (0.00 sec)
- 如果前面创建表的时候忘记添加主键约束,可以使用alter table +add 语句进行添加。
#首先是users3数据表没有主键约束。
mysql> desc users3
-> ;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| psssword | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#添加主键约束,可以指定字段进行约束。name
mysql> alter table users3 add primary key (name);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc users3;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
| psssword | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#不可以通过alter add 的方式对主键进行约束,只能在create的时候进行联合约束。
mysql> alter table users3 add primary key (id);
ERROR 1068 (42000): Multiple primary key defined
#删除主键约束
mysql> alter table users3 drop primary key;
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc users3;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| psssword | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
(3)唯一约束
约束修饰的字段的值不可以重复。
#使用数据库
mysql> use test;
Database changed
#创建数据表
mysql> create table user5(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)
#设置唯一约束的第一种方式:alter + 表+ 表名称+ add + unique +字段
mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#desc表后发现,name字段变成了唯一约束。
mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
#现在插入值 insert into + 表名字 + values(...)
mysql> insert into user5 values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)
#再次插入一样的values时发现报错,出现了zhangsan触发了约束。
mysql> insert into user5 values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'user5.name'
#name字段不是zhangsan时,就不会出错。
mysql> insert into user5 values(1,'zhann');
Query OK, 1 row affected (0.01 sec)
#显示表记录。
mysql> select * from user5;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 1 | zhann |
+------+----------+
2 rows in set (0.00 sec)
#添加唯一约束的第2种方式
mysql> create table user6(
-> id int,
-> name varchar(20) unique
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
#添加唯一约束的第三种方式:
mysql> create table user7(
-> id int,
-> name varchar(20),
-> unique(name)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
- 联合唯一约束unique(id,name),只要两个值不重复就ok
mysql> create table user8(
-> id int,
-> name varchar(20),
-> unique(name,id)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into user8 values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user8 values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan-1' for key 'use
mysql> insert into user8 values(2,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user8 values(2,'zhann');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user8;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | zhangsan |
| 2 | zhann |
+------+----------+
3 rows in set (0.00 sec)
唯一约束添加可以为空,主键约束不可以。联合主键与联合唯一约束的
作用机理一样,输入的两个字段不可以全部一样,但是其中某一个字段可以一样。
- 删除唯一约束alter … +drop +index+字段
mysql> alter table user8 drop index name;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
- 总结:
添加约束的方式有三种:
- 建表的时候添加。create table user();
- 可以使用alter table user add …;
- alter … modify
删除: - alter…drop…
(4)非空约束
修饰的字段不能为空, not null
#首先是创建一个数据表user10,将name的属性命名为非空 not null
mysql> create table user10(
-> id int,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (0.03 sec)
#查看表user10,发现name在null栏下是No,也就是不可以是非空。
mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
测试非空约束:
#当只单纯的插入id字段时,在insert语句中,要加入字段说明,在user10后面。
mysql> insert into user10 (id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
#错误结果显示,name字段没有默认值。
#当只输入name字段,id字段为空null时,程序是允许的。
mysql> insert into user10 (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user10;
+------+----------+
| id | name |
+------+----------+
| NULL | zhangsan |
+------+----------+
1 row in set (0.00 sec)
mysql> insert into user10 (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user10;
+------+----------+
| id | name |
+------+----------+
| NULL | zhangsan |
+------+----------+
1 row in set (0.00 sec)
- 添加not null的约束不仅可以通过create 的方式,还可以使用之前说过的,alter和modify方式。
- 删除非空约束,使用alter table [表名] modify [列名] varchar(20) null;的形式。
mysql> alter table user10 modify name varchar(20) null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
(5)默认约束
当插入字段值的时候,如果没有传入值,那么就会使用默认值。如果传入了值,那么就会使用传入值。
- 添加默认约束的方法1:create table 中。
mysql> create table user12(
-> id int,
-> name varchar(20),
-> age int default 20);
Query OK, 0 rows affected (0.03 sec)
mysql> desc user12;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | 20 | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into user12 (id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)
#没有传入age值,则为默认值。
mysql> select * from user12;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 20 |
+------+----------+------+
1 row in set (0.00 sec)
- 添加默认约束方法2:alter table +表名+modify+字段+类型+default +默认值
mysql> alter table user12 modify name varchar(20) default 'lisi';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from user12;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 20 |
+------+----------+------+
- 删除默认约束方法1:alter table + 名+modify +字段+类型;
mysql> alter table user11 modify id int default 1;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user11;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | 1 | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> alter table user11 modify id int;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user11;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
- 删除默认约束方法2:alter table +名称+alter column +字段+drop default;
mysql> alter table user11 alter column id drop default;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user11;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
(6)外键约束
外键约束涉及到两个表:父表-子表(主表-副表)
#创建主表:classes
mysql> create table classes(
-> id int primary key,
-> class_name varchar(20)
-> );
Query OK, 0 rows affected (0.02 sec)
#创建副表students,在字段class_id处,添加外键约束,和主表中的id相关联。
mysql> create table students(
-> id int primary key,
-> name varchar(20),
-> class_id int, #关联的是id为int型,则class_id也为int型。
-> foreign key(class_id) references classes(id)#添加外键约束,关联。
-> );
Query OK, 0 rows affected (0.04 sec)
#测试了一下,不可以关联为name的varcahr类型。
#现插入数据:
mysql> insert into classes values(1,'一班');
Query OK, 1 row affected (0.00 sec)
mysql> insert into classes values(2,'二班');
Query OK, 1 row affected (0.01 sec)
mysql> insert into classes values(3,'三班');
Query OK, 1 row affected (0.01 sec)
mysql> select * from classes;
+----+------------+
| id | class_name |
+----+------------+
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
+----+------------+
3 rows in set (0.01 sec)
#插入学生信息数据
mysql> insert into students values(1001,'zhangsan',2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into students values(1002,'zhangsan',3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into students values(1003,'zhangsan',1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+------+----------+----------+
| id | name | class_id |
+------+----------+----------+
| 1001 | zhangsan | 2 |
| 1002 | zhangsan | 3 |
| 1003 | zhangsan | 1 |
+------+----------+----------+
3 rows in set (0.00 sec)
#如果在外键约束的字段中,插入主表没有的数据4,那么就会报错。
mysql> insert into students values(1004,'zhangsan',4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) R
EFERENCES `classes` (`id`))
#主表中被副表引用的值是不可以删除的。
mysql> delete from classes where id=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constra
nt fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_i
`) REFERENCES `classes` (`id`))
- 总结
- 主表classes中没有的数据,在副表中是不可以使用的。
- 主表中的记录被副表引用,是不可以被删除的。
12. 数据表设计
(1)第一范式1NF
数据表中的所有字段都是不可分割的原子值。
mysql> create table student(
-> id int primary key,
-> name varchar(20),
-> address varchar(30));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into student values(1,'zhangsan','中国北京大运村');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(2,'zhangsan','中国北村');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;#像这种地址还可以继续拆分,那么就不满足第一范式。
+----+----------+----------------+
| id | name | address |
+----+----------+----------------+
| 1 | zhangsan | 中国北京大运村 |
| 2 | zhangsan | 中国北村 |
+----+----------+----------------+
2 rows in set (0.00 sec)
address->country-province-city-details
- 总结:
- 范式,设计的越详细,对于某些实际操作可能会更好,但是不一定都是好处。最终要根据项目的实际需求去设计。
- 如果拆分到每个字段都已经不再可拆分,那么就满足了第一范式。
(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)
);
实际上,在这张订单表中,product_name 只依赖于 product_id ,customer_name 只依赖于 customer_id 。也就是说,product_name 和 customer_id 是没用关系的,customer_name 和 product_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)
);
拆分之后,myorder 表中的 product_id 和 customer_id 完全依赖于 order_id 主键,而 product 和 customer 表中的其他字段又完全依赖于主键。满足了第二范式的设计!
(3)第三范式3NF
在满足第二范式的前提下,除了主键列之外,其他列之间不能有传递依赖关系。
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT,
customer_phone VARCHAR(15)
);
表中的 customer_phone 有可能依赖于 order_id 、 customer_id 两列,也就不满足了第三范式的设计:其他列之间不能有传递依赖关系。
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)
);
修改后就不存在其他列之间的传递依赖关系,其他列都只依赖于主键列,满足了第三范式的设计!