MySQL学习笔记(Windows)
安装
参考此blog:在Windows上安装 MySQL 8.0 教程(默认选项 Developer Default 安装)
随后把mysql加入系统环境变量中:
打开 控制面板 > 系统和安全 > 系统 > 高级系统设置 > 环境变量,在系统变量的Path中加入C:\Program Files\MySQL\MySQL Server 8.0\bin
开始学习
一、Mysql的数据类型
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。(这边大致浏览一下就好)
1.数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度浮点数值 |
DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
2.日期和时间类型
类型 | 大小( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
3.字符串类型
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
二、使用终端操作数据库
1. 登陆数据库
管理员模式下打开cmd,输入
mysql -uroot -ppassword
root处填用户名,password处填密码
也可以只输入mysql -uroot -p
,屏幕上会出现 Enter password:
让你输入密码
C:\windows\system32>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.
然后cmd的小箭头会变成下面这样,说明已经成功进入mysql
mysql>
2.查询数据库服务器中所有数据库
输入 show databases;
注意加 ; 以结束输入
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
6 rows in set (0.02 sec)
3.选中一个数据库进行操作
从上面得到的数据库中随便选一个选中, 语句为
use databaseName
,这里我选了 sakila
mysql> use sakila
Database changed
先使用 show tables;
查看该数据库中的表
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.00 sec)
接下来查询其中一个表
语句为 select * from tableName
,这里我选择 store
mysql> select * from store;
+----------+------------------+------------+---------------------+
| store_id | manager_staff_id | address_id | last_update |
+----------+------------------+------------+---------------------+
| 1 | 1 | 1 | 2006-02-15 04:57:12 |
| 2 | 2 | 2 | 2006-02-15 04:57:12 |
+----------+------------------+------------+---------------------+
2 rows in set (0.00 sec)
只选中 store_id
为1的表项
mysql> select * from store where store_id=1;
+----------+------------------+------------+---------------------+
| store_id | manager_staff_id | address_id | last_update |
+----------+------------------+------------+---------------------+
| 1 | 1 | 1 | 2006-02-15 04:57:12 |
+----------+------------------+------------+---------------------+
1 row in set (0.00 sec)
4.退出数据库
输入 exit
mysql> exit
Bye
5.创建数据库
重新登陆mysql后,使用 create database databaseName;
创建数据库
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
再次使用 show databases;
查看现有数据库,可以看到多了一个 test
数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| test |
| world |
+--------------------+
7 rows in set (0.00 sec)
选中 test
数据库,并查看该库下的表,可以看到当前库下没有数据表
mysql> use test
Database changed
mysql> show tables;
Empty set (0.00 sec)
6.创建数据表
使用 create table tableName
进行创建,依次输入 表项名 表项类型
,用逗号隔开各项,高亮处大小写均可
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.06 sec)
同样使用 show tables;
检查
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet |
+----------------+
1 row in set (0.00 sec)
使用 describe tableName;
查看数据表的结构,可用简写 desc tableName;
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> desc 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.00 sec)
7.向数据表中添加数据记录
刚才创建的数据表中是空的,没有记录
mysql> select * from pet;
Empty set (0.00 sec)
接下来向表中添加记录,使用 insert into tableName
添加,依次输入对应变量
mysql> insert into pet
-> values('Nimo', 'Elmo','poodle','f','2015-01-01',NULL);
Query OK, 1 row affected (0.01 sec)
注意:这里为字符类型,必须加上单引号或双引号
再次查询
mysql> select * from pet;
+------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+------+-------+---------+------+------------+-------+
| Nimo | Elmo | poodle | f | 2015-01-01 | NULL |
+------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
8.数据记录的删除
先多加几条进去
insert into pet values('Fluffy','Harold','cat','f','1993-02-04',NUll);
insert into pet values('CLaws','Gwen','cat','m','1994-03-04',NUll);
insert into pet values('Baobao','Rabbit','cat','f','2015-04-27',NUll);
删除记录使用语句
delete from tableName where value=XXX;
mysql> delete from pet where name='Fluffy';
Query OK, 1 row affected (0.01 sec)
9.数据记录的修改
update tableName set value1=XXX where value2=YYY
把 value2
为 YYY
的 value1
修改为 XXX
mysql> update pet set owner='Mia' where name='Baobao';//把name为Baobao的owner修改为Mia
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
三、Mysql建表约束
1.主键约束
(1) 主键约束
主键约束能够唯一确定一张表中的一条记录,通过给某个字段添加约束,使该字段不重复且不为空
在 表项名 表项类型
后加上 primary key
关键字
mysql> create table user(
-> id int primary key,
-> name varchar(20));
Query OK, 0 rows affected (0.07 sec)
查看该表结构可以看见在 id
的 Key
处有 PRI
mysql> describe user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
插入一条 id
为1的记录后,再插入一条 id
为1的记录就会报错,说明主键元素必须唯一且不重复,把id修改为不重复的值插入
mysql> insert into user values(1,'Nimo');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user values(1,'Elmo');
ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'
mysql> insert into user values(2,'Elmo');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | Nimo |
| 2 | Elmo |
+----+------+
2 rows in set (0.00 sec)
同时主键元素值不能为空
mysql> insert into user values(NULL, 'Kaws');
ERROR 1048 (23000): Column 'id' cannot be null
(2) 联合主键
联合主键:用2个或2个以上的字段组成的主键,这些字段值不完全相同,且联合主键的值都不能为空
在创建表时用语句 primary key(value1, value2, ……);
mysql> create table user2(
-> id int,
-> name varchar(20),
-> password varchar(20),
-> primary key(id, name) //id与name作为联合主键
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> describe user2;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
| password | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into user2 values(1,'Nimo','123');
Query OK, 1 row affected (0.02 sec)
mysql> insert into user2 values(1,'Nimo','345');
ERROR 1062 (23000): Duplicate entry '1-Nimo' for key 'user2.PRIMARY'
mysql> insert into user2 values(1,'Elmo','345');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user2 values(2,'Nimo','345');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user2;
+----+------+----------+
| id | name | password |
+----+------+----------+
| 1 | Elmo | 345 |
| 1 | Nimo | 123 |
| 2 | Nimo | 345 |
+----+------+----------+
3 rows in set (0.00 sec)
mysql> insert into user2 values(NULL,'Nimo','345');
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into user2 values(2,NULL,'345');
ERROR 1048 (23000): Column 'name' cannot be null
(3) 自增约束
自增字段值在输入时可以为空,该字段值会自动+1,每次在最后一条记录的基础上+1
在要自增的变量后加上 auto_increment
关键字
mysql> create table user3(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> describe user3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into user3 (name) values('Elmo');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user3 (name) values('Elmo');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user3;
+----+------+
| id | name |
+----+------+
| 1 | Elmo |
| 2 | Elmo |
+----+------+
2 rows in set (0.00 sec)
mysql> insert into user3 values(10,'Elmo');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user3 (name) values('Elmo');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user3;
+----+------+
| id | name |
+----+------+
| 1 | Elmo |
| 2 | Elmo |
| 10 | Elmo |
| 11 | Elmo |
+----+------+
4 rows in set (0.00 sec)
mysql> insert into user3 values(NULL,'Elmo');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user3;
+----+------+
| id | name |
+----+------+
| 1 | Elmo |
| 2 | Elmo |
| 10 | Elmo |
| 11 | Elmo |
| 12 | Elmo |
+----+------+
5 rows in set (0.00 sec)
(4) 建表后添加与删除主键
添加 alter table tableName add primary key(value1, value2, ……);
删除 alter table tableName drop primary key;
可以看到在删除主键后字段值仍不允许为空
mysql> create table user4(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> alter table user4 add primary key(id, name);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
添加也可以使用 modify
修改命令进行,但只能对一个字段进行操作,删除主键还是必须使用 drop
命令
alter table tableName modify XXX valueType primary key
mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
2.唯一约束
唯一约束修饰的字段值不可重复,但可以为空,使用关键字 unique
(1)添加唯一约束
使用 add
添加
mysql> create table user5 (
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into user5 values(1,'Nimo');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user5 values(2,'Nimo');
ERROR 1062 (23000): Duplicate entry 'Nimo' for key 'user5.name'
或是在创建表时添加
mysql> create table user6(
-> id int,
-> name varchar(20),
-> unique(name)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> create table user7(
-> id int,
-> name varchar(20) unique
-> );
Query OK, 0 rows affected (0.05 sec)
或是使用 modify
进行添加
mysql> alter table user6 modify id int unique;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
并且唯一约束可以允许多个字段为唯一约束,但主键约束只允许一个字段或是多个字段组合起来为主键约束。
新建两个表 user8
和 user9
,其中 user8
为联合唯一约束,只要输入值不完全相同即可, user9
各字段值都为唯一约束,输入记录必须都不重复。
mysql> create table user8(
-> id int,
-> name varchar(20),
-> unique(id, name)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> insert into user8 values(1,'Nimo');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user8 values(1,'Nimo');
ERROR 1062 (23000): Duplicate entry '1-Nimo' for key 'user8.id'
mysql> insert into user8 values(2,'Nimo');
Query OK, 1 row affected (0.01 sec)
mysql> create table user9(
-> id int unique,
-> name varchar(20) unique
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into user9 values(1,'Nimo');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user9 values(1,'Nimo');
ERROR 1062 (23000): Duplicate entry '1' for key 'user9.id'
mysql> insert into user9 values(2,'Nimo');
ERROR 1062 (23000): Duplicate entry 'Nimo' for key 'user9.name'
mysql> insert into user9 values(2,'Elmo');
Query OK, 1 row affected (0.01 sec)
(2)删除唯一约束
alter table tableName drop index xxx;
mysql> alter table user7 drop index name;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
3.非空约束
非空约束:修饰的字段不能为空,关键字为 not null
mysql> create table user10(
-> id int unique,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into user10 (id) values(1);
ERROR 1364 (HY000): Field 'name' does not have a default value
mysql> insert into user10 (name) values('Elmo');
Query OK, 1 row affected (0.01 sec)
4.默认约束
默认约束:当插入字段值时若没有传入值,会使用默认值,关键字 default
mysql> create table user11(
-> id int,
-> name varchar(20),
-> age int default 10
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc user11;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | 10 | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into user11 (id, name) values(1,'Elmo');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user11 values(2, 'Nimo', 23);
Query OK, 1 row affected (0.02 sec)
mysql> select * from user11;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | Elmo | 10 |
| 2 | Nimo | 23 |
+------+------+------+
2 rows in set (0.00 sec)
5.外键约束
外键约束涉及到两个表,主表(父表)和从表(子表),外键用来建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性和完整性。外键所在的表是从表。
关键字 foreign key(xxx) references tableName(xxx)
如下,建立一个 classes
表和一个 student
表,其中 student
表为从表
当主表中不存在某数据值时,向从表中插入记录会报错
例如我只建立了四个班级1~4,但是向从表中插入记录时写了班级5,此时就会报错
mysql> create table classes(
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> create table student(
-> id int primary key,
-> name varchar(20),
-> class_id int,
-> foreign key(class_id) references classes(id)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| class_id | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into classes values(1, 'class 1');
Query OK, 1 row affected (0.01 sec)
mysql> insert into classes values(2, 'class 2');
Query OK, 1 row affected (0.01 sec)
mysql> insert into classes values(3, 'class 3');
Query OK, 1 row affected (0.01 sec)
mysql> insert into classes values(4, 'class 4');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(1001, 'Nimo', 1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(1002, 'Elmo', 2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(1003, 'Elsa', 3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(1004, 'Yisa', 4);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(1005, 'Linda', 5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
且若主表中的记录被从表引用,该条记录是不可被删除的
mysql> delete from classes where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
四、数据库的设计范式
1.第一范式 1NF
数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。数据表中的所有字段都是不可分割的原子值。
像下述这样 address
字段值还可以继续拆分的就不满足第一范式
mysql> create table student2(
-> id int primary key,
-> name varchar(20),
-> address varchar(30));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into student2 values(1, 'Nimo', 'Beijing, China');
Query OK, 1 row affected (0.03 sec)
mysql> insert into student2 values(2, 'Elmo', 'Hainan, China');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student2 values(3, 'Yusa', 'Sichuan, China');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student2;
+----+------+----------------+
| id | name | address |
+----+------+----------------+
| 1 | Nimo | Beijing, China |
| 2 | Elmo | Hainan, China |
| 3 | Yusa | Sichuan, China |
+----+------+----------------+
3 rows in set (0.00 sec)
范式设计得越详细,对于某些实际操作可能更好
下面的例子把国家省份城市详细住址进行拆分(假设除details外均满足第一范式)
mysql> create table student3(
-> id int primary key,
-> name varchar(20),
-> country varchar(30),
-> province varchar(30),
-> city varchar(30),
-> details varchar(30));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into student3 values(1, 'Nimo', 'China', 'Beijing', 'Beijing', 'Chaoyang');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student3 values(2, 'Elmo', 'China', 'Hainan', 'Haikou', 'Qilou');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student3 values(3, 'Yusa', 'China', 'Sichuan', 'Chengdu', 'Dujiangyan');
Query OK, 1 row affected (0.01 sec)
2.第二范式 2NF
在满足第一范式的前提下,第二范式除主键外的每一列都必须完全依赖于主键
所谓完全依赖是指不能存在仅依赖主键一部分的属性即如果有不完全依赖,只可能发生在联合主键的情况下。
例如下列所建的 myorder
表, product_name
只依赖于 product_id
, customer_name
只依赖于 customer_id
,也就是说它们只依赖于主键的一部分属性。因此该表不满足第二范式。
mysql> create table myorder(
-> product_id int,
-> customer_id int,
-> product_name varchar(20),
-> customer_name varchar(20),
-> primary key(product_id, customer_id)
-> );
Query OK, 0 rows affected (0.07 sec)
若想要满足第二范式,必须对表进行拆分
将该表拆分成三个表,myorder2
product
和 customer
,各表中的各项都完全依赖于各自的主键
mysql> create table myorder2(
-> order_id int primary key,
-> product_id int,
-> customer_id int
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> create table product(
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> create table customer(
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.03 sec)
3.第三范式 3NF
在满足第二范式的前提下,要求一个数据库表中不包含已在其它表中已包含的非主关键字信息
也就是说每个数据项除了和主键有关系之外,不允许和其他数据项有关系
例如下面的 myorder3
,其中的 customer_id
和 customer_phone
有依赖关系,会产生冗余,应将 customer_phone
项放在 customer
表中
mysql> create table myorder3(
-> order_id int primary key,
-> product_id int,
-> customer_id int,
-> customer_phone varchar(15)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> create table customer2(
-> id int primary key,
-> name varchar(20),
-> customer_phone varchar(15)
-> );
Query OK, 0 rows affected (0.03 sec)