【3.0】数据库知识点小结数据库升级
【3.0】数据库知识点小结数据库升级
【一】约束条件
【1】default(默认值)
（1）补充
在插入数据的时候可以指定字段
- 创建数据库
create database day02;
# Database changed
- 创建表
create table t1(
id int,
name char(16)
);
# Query OK, 0 rows affected (0.77 sec)
- 插入数据
insert into t1(name,id) values(
"dream",18
);
# Query OK, 1 row affected (0.31 sec)
可以根据指定字段进行插入数据，如果不写则默认按位置进行插入数据
- 查看数据
select * from t1;
+------+-------+
| id | name |
+------+-------+
| 18 | dream |
+------+-------+
1 row in set (0.00 sec)
（2）设置默认值
- 创建表
create table t2(
id int,
name char(16) not null,
gender enum('male','female','others') default 'male'
);
# Query OK, 0 rows affected (0.61 sec)
- 查看表
desc t2;
+--------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(16) | NO | | NULL | |
| gender | enum('male','female','others') | YES | | male | |
+--------+--------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
- 插入数据1
insert into t2 (id,name) values(1,"dream");
# Query OK, 1 row affected (0.21 sec)
- 插入数据2
insert into t2 (id,name,gender) values(2,"mengmeng",'female');
# Query OK, 1 row affected (0.21 sec)
- 查看数据
select * from t2;
+------+----------+--------+
| id | name | gender |
+------+----------+--------+
| 1 | dream | male |
| 2 | mengmeng | female |
+------+----------+--------+
2 rows in set (0.00 sec)
第一条数据没有插入gender，使用默认的gender
第二条使用了自己插入的数据
【2】unique(唯一)
（1）单列唯一
- 创建表
create table t3(
id int unique,
name char(16)
);
# Query OK, 0 rows affected (0.74 sec)
- 查看表
desc t3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
- 插入多条数据
insert into t3 values(1,"dream")(1,"mengmeng");
# ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1,"mengmeng")' at line 1
- 插入数据2
insert into t3 values(1,"dream")(2,"zuimeng");
# Query OK, 1 row affected (0.17 sec)
- 查看数据
select * from t3;
+------+---------+
| id | name |
+------+---------+
| 2 | zuimeng |
| 1 | dream |
+------+---------+
2 rows in set (0.00 sec)
插入数据正常
（2）联合唯一
比如我们的IP和端口
单个可以重复，联合到一起不能重复
- 创建表
create table t4(
id int,
ip char(16),
port int,
unique(ip,port)
);
# Query OK, 0 rows affected (0.97 sec)
- 插入数据
insert into t4 values(1,'127.0.0.1',8080);
# Query OK, 1 row affected (0.20 sec)
insert into t4 values(2,'127.0.0.1',8081);
# Query OK, 1 row affected (0.20 sec)
insert into t4 values(3,'127.0.0.2',8080);
# Query OK, 1 row affected (0.20 sec)
insert into t4 values(4,'127.0.0.1',8080);
# ERROR 1062 (23000): Duplicate entry '127.0.0.1-8080' for key 'ip'
- 查看数据
select * from t4;
+------+-----------+------+
| id | ip | port |
+------+-----------+------+
| 1 | 127.0.0.1 | 8080 |
| 2 | 127.0.0.1 | 8081 |
| 3 | 127.0.0.2 | 8080 |
+------+-----------+------+
3 rows in set (0.00 sec)
【3】primary key(主键)
（1）约束条件效果：非空且唯一
单从约束条件效果上看
primary key = not null + unique
非空且唯一
- 创建表
create table t5(
id int primary key
);
# Query OK, 0 rows affected (0.87 sec)
- 查看表
desc t5;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
- 插入数据
insert into t5 values(null);
# ERROR 1048 (23000): Column 'id' cannot be null
insert into t5 values(1);
# Query OK, 1 row affected (0.17 sec)
insert into t5 values(1);
# ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
（2）是Innodb引擎组织数据的依据
它除了有约束效果外，还是Innodb存储引擎组织数据的依据
Innodb存储在创建表的时候肯定有 primary key
因为他类似于书的目录，能够帮助提示查询效率并且也是建表的依据
(1) 一张表中有且只有一个主键，如果没有设置主键，那么会从上往下搜索直到遇到一个非空且唯一的字段，将它自动升级为主键
- 创建表
create table t6(
id int,
name char(16),
age int not null unique,
addr char(32) not null unique
);
# Query OK, 0 rows affected (0.86 sec)
- 查看表
desc t6;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(16) | YES | | NULL | |
| age | int(11) | NO | PRI | NULL | |
| addr | char(32) | NO | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
age 字段自动提升为 primary key 字段
(2) 如果表中没有主键，也没有其他任何非空且唯一字段，那么Innodb会采用自己内部提供的一个隐藏字段作为主键。隐藏意味着无法使用，且无法提升查询速度。
(3) 一张表中都应该具有一个主键字段，并且通常将 id/uid/sid 字段作为主键。
单个字段主键
create table t7( id int primary key, name char(36) );联合主键(多个字段联合起来作为表的主键，本质还是一个主键)
create table t7( id int , ip char(16), port int, primary key(ip,port) ); # Query OK, 0 rows affected (0.83 sec)desc t7;+-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | ip | char(16) | NO | PRI | NULL | | | port | int(11) | NO | PRI | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
总结：id字段在创建时，一定要加 主键
【4】auto_increment(自增)
在创建数据时，会自动增加
- 创建表
create table t8(
id int primary key auto_increment,
name char(16)
);
# Query OK, 0 rows affected (0.96 sec)
- 查看表
desc t8;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
- 插入数据
insert into t8(name) values("dream"),("hope"),("meng");
# Query OK, 3 rows affected (0.18 sec)
# Records: 3 Duplicates: 0 Warnings: 0
- 查看数据
select * from t8;
+----+-------+
| id | name |
+----+-------+
| 1 | dream |
| 2 | hope |
| 3 | meng |
+----+-------+
3 rows in set (0.00 sec)
auto_increment：只能加载 key 键上 ，不能在普通字段上使用
通常是加在主键上
【5】结论
（1）主键的标准语法
- 在创建表的唯一标识(id/sid/uid)时的标准语法
create table t1(
id int primary key auto_increment
);
（2）主键自增
- 在使用以下命令在删除表的时候，已经自增过的主键不会被删除
- 新创建的数据会延续上一次删除之前主键自增的位置进行自增
delete from 表名;
- 可以使用以下命令删除表，达到删除主键的目的
- 清空表数据并且重置表的主键
truncate 表名;
【二】外键
【1】表与表之间建立关系
- 表与表之间只有三种关系
- 一对一
- 多对弈
- 一对多
- 在MySQL的关系中没有多对一的说法
- 一对多 多对多 都叫 一对多
（1）定义一张表
id name gender dep_name dep_desc
- 该表的组织结构不清晰
- 浪费硬盘空间
- 数据的扩展性极差，修改起来的难度极差
（2）如何优化？
-
将员工表拆分建立联系
- 拆成员工表 + 部门表
- 建立联系
-
员工表
id dep_name dep_desc
- 部门表
id name gender dep_id
【2】什么是外键(Foreign Key)
外键（Foreign Key）是关系数据库中的一个概念，用于建立两个关系表之间的关联关系。
它是一个列或一组列，用来指向另一个表的主键。
外键在建立数据表与数据表之间的关系时起到了重要的作用。
【3】一对多关系
（1）一对多关系，外键建在字段多的地方
（2）在创建表的时候一定要先创建被关联表
（3）在录入数据的时候必须先录入被关联表
在确定表与表关系之前，首先要换位思考
例如
- 在员工表
- 要考虑到员工表里面的一个员工是否能对应部门表里面的多个部门
- 在部门表
- 要考虑到一个部门能否对应员工表里面的多个员工
- 总结
- 员工表与部门表只是单向的一对多成立，那么员工表与部门表就是一对多的表关系
- 创建部门表
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
#Query OK, 0 rows affected (0.82 sec)
- 查看表(部门表)
desc dep;
+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| dep_name | char(16) | YES | | NULL | |
| dep_desc | char(32) | YES | | NULL | |
+----------+----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
- 创建员工表(员工表)
create table emp(
id int primary key auto_increment,
emp_name char(16),
emp_gender enum("male","female","others") default "male",
dep_id int,
foreign key (dep_id) references dep(id)
);
# Query OK, 0 rows affected (0.92 sec)
- 查看表(员工表)
desc emp;
+------------+--------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| emp_name | char(16) | YES | | NULL | |
| emp_gender | enum('male','female','others') | YES | | male | |
| dep_id | int(11) | YES | MUL | NULL | |
+------------+--------------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
- 插入数据(部门表)
insert into dep(dep_name,dep_desc) values("sm运动社","日常活动"),("游戏社","休闲娱乐"),("技术部","能力提升"),("cp外交部","社交沟通");
# Query OK, 4 rows affected (0.14 sec)
# Records: 4 Duplicates: 0 Warnings: 0
- 插入数据(员工表)
insert into emp(emp_name,emp_gender,dep_id) values("dream","male",1),("chimeng","female",4),("mengmeng","female",2),("drunkmeng","male",3);
# Query OK, 4 rows affected (0.13 sec)
# Records: 4 Duplicates: 0 Warnings: 0
- 查看数据(部门表)
select * from dep;
+----+-------------+--------------+
| id | dep_name | dep_desc |
+----+-------------+--------------+
| 1 | sm运动社 | 日常活动 |
| 2 | 游戏社 | 休闲娱乐 |
| 3 | 技术部 | 能力提升 |
| 4 | cp外交部 | 社交沟通 |
+----+-------------+--------------+
4 rows in set (0.00 sec)
- 查看数据(员工表)
select * from emp;
+----+-----------+------------+--------+
| id | emp_name | emp_gender | dep_id |
+----+-----------+------------+--------+
| 1 | dream | male | 1 |
| 2 | chimeng | female | 4 |
| 3 | mengmeng | female | 2 |
| 4 | drunkmeng | male | 3 |
+----+-----------+------------+--------+
4 rows in set (0.00 sec)
（1）正常命令修改被关联表中的某个字段
-
修改 emp 表中的 dep_id 字段或者 dep 表里面的 id 字段
-
修改/删除命令
# 修改某张表中的关联值
update dep set id = 200 where id =2;
#ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`day02`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
# 删除表命令
delete from dep;
# ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`day02`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
无法通过正常修改命令对其进行修改
（2）解决办法
- (1) 先删除教学部对应的员工数据 ，之后再删除部门
- (2) 真正做到数据之间有关系
- 更新就同步更新
- 删除就同步删除
级联更新
级联删除
create table emp( id int primary key auto_increment, emp_name char(16), emp_gender enum("male","female","others") default "male", dep_id int, foreign key (dep_id) references dep(id) on uodate cascade on delete cascade ); # Query OK, 0 rows affected (0.92 sec)
- 删除表
drop table emp;
# Query OK, 0 rows affected (0.53 sec)
drop table dep;
Query OK, 0 rows affected (0.25 sec)
- 创建表
create table emp(
id int primary key auto_increment,
emp_name char(16),
emp_gender enum("male","female","others") default "male",
dep_id int,
foreign key (dep_id) references dep(id)
on update cascade
on delete cascade
);
# Query OK, 0 rows affected (0.92 sec)
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
#Query OK, 0 rows affected (0.82 sec)
- 插入数据
insert into dep(dep_name,dep_desc) values("sm运动社","日常活动"),("游戏社","休闲娱乐"),("技术部","能力提升"),("cp外交部","社交沟通");
# Query OK, 4 rows affected (0.14 sec)
# Records: 4 Duplicates: 0 Warnings: 0
insert into emp(emp_name,emp_gender,dep_id) values("dream","male",1),("chimeng","female",4),("mengmeng","female",2),("drunkmeng","male",3);
# Query OK, 4 rows affected (0.13 sec)
# Records: 4 Duplicates: 0 Warnings: 0
- 修改数据
update dep set id = 200 where id=2;
- 查看数据
select * from dep;
+-----+-------------+--------------+
| id | dep_name | dep_desc |
+-----+-------------+--------------+
| 1 | sm运动社 | 日常活动 |
| 3 | 技术部 | 能力提升 |
| 4 | cp外交部 | 社交沟通 |
| 200 | 游戏社 | 休闲娱乐 |
+-----+-------------+--------------+
4 rows in set (0.00 sec)
select * from emp;
+----+-----------+------------+--------+
| id | emp_name | emp_gender | dep_id |
+----+-----------+------------+--------+
| 1 | dream | male | 1 |
| 2 | chimeng | female | 4 |
| 3 | mengmeng | female | 200 |
| 4 | drunkmeng | male | 3 |
+----+-----------+------------+--------+
4 rows in set (0.00 sec)
【4】多对多关系
- 书籍表的角度
- 一本书可以有多个作者
- 作者表的角度
- 一个作者可以写多本书
- 总结
- 书籍表和作者表是双向的一对多
- 那么关系就是 多对多 关系
（1）建表问题
- 创建图书表
id title price author_id
create table book(
id int primary key auto_increment,
title varchar(32),
price int,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
)
- 创建作者表
id name age book_id
create table author (
id int primary key auto_increment,
name varchar(32),
age int,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
)
这种方式建表，由于外键具有 在创建表的时候一定要先创建被关联表 的特性
导致都无法创建彼此的关联表
针对多对多字段关系，不能在原有两张表的基础上创建外键
需要创建一张新表来建立两表的关系
（2）解决循环建表问题
- 建表
book
id title price
author
id name age
book_connect
id boo_id author_id
- 创建表
# 图书表
create table book(
id int primary key auto_increment,
title varchar(32),
price int
);
# 作者表
create table author(
id int primary key auto_increment,
name varchar(32),
age int
);
# 中转联系表
create table book_connect(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
- 查看表
desc book;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(32) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
desc author;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
desc book_connect;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| author_id | int(11) | YES | MUL | NULL | |
| book_id | int(11) | YES | MUL | NULL | |
+-----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
- 插入数据
insert into book(title,price) values("西游记",18),("水浒传",29),("三国演义",99),("如何让富婆爱上你",999);
insert into author(name,age) values("dream",18),("chimeng",28),("mengmeng",38);
insert into book_connect(author_id,book_id) values(1,3),(2,1),(2,3),(1,1);
- 查看表数据
select * from book;
+----+--------------------------+-------+
| id | title | price |
+----+--------------------------+-------+
| 1 | 西游记 | 18 |
| 2 | 水浒传 | 29 |
| 3 | 三国演义 | 99 |
| 4 | 如何让富婆爱上你 | 999 |
+----+--------------------------+-------+
4 rows in set (0.00 sec)
select * from author;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | dream | 18 |
| 2 | chimeng | 28 |
| 3 | mengmeng | 38 |
+----+----------+------+
3 rows in set (0.00 sec)
select * from book_connect;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 1 | 1 | 3 |
| 2 | 2 | 1 |
| 3 | 2 | 3 |
| 4 | 1 | 1 |
+----+-----------+---------+
4 rows in set (0.00 sec)
【5】一对一关系
在MySQL的关系中没有多对一的说法
一对多 多对多 都叫 一对多
- 如果一个表的字段特别多，每次查询又不是所有字段的数据都需要
示例一
-
可以将表一分为二
- 用户表
- 用户详情表
-
用户表
- 一个用户能否对应多个用户详情
- 否
- 一个用户能否对应多个用户详情
-
详情表
- 一个详情表能否属于多个用户
- 否
- 一个详情表能否属于多个用户
-
结论
- 单向的一对多都不能成立，那么这个时候两者之间的表关系要么是一对一，或没有关系
示例二
- 客户表
- 学生表
建表
authors
id name age author_detail_id
author_detail
id phone addr
一对一，外键建在任意一方都可以，但是建议建立在查询频率较高的表内
- 创建表
create table author_detail(
id int primary key auto_increment,
phone int,
addr varchar(64)
);
create table authors(
id int primary key auto_increment,
name varchar(16),
age int,
author_detail_id int unique,
foreign key(author_detail_id) references author_detail(id)
on update cascade
on delete cascade
);
- 查看表
desc author_detail;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| phone | int(11) | YES | | NULL | |
| addr | varchar(64) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
desc authors;
+------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(16) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| author_detail_id | int(11) | YES | UNI | NULL | |
+------------------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
【6】小结
- 表关系的建立需要用到 foreign key
- 一对多
- 外键字段在多的一方
- 多对多
- 开设第三张表作为媒介
- 一对一
- 建在任意一方均可
- 推荐建在查询频率较高的表中
- 判断表与表之间的关系要站在双方的角度考虑
【三】修改表
MySQL 的 SQL 语句是不区分大小写的
【1】修改表明
alter table 表名 rename 新表明;
【2】增加字段
# 默认添加到尾部
alter table 表名 add 字段名 字段类型(宽度) 约束条件;
# 添加到首位
alter table 表名 add 字段名 字段类型(宽度) 约束条件 first;
# 添加到指定字段名的后面
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名;
【3】删除字段
# 修改指定字段名
alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
# 替换指定字段名
alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
【四】复制表
SQL 语句其实查询到的表都其实是虚拟表(在内存中)
【1】全复制表
# 只能复制表中的原始数据，但是无法复制每个字段的约束条件(主键、外键、索引...)
create table 表名 select * from 旧表名;
【2】根据条件复制表
# 根据查询条件复制旧表中的数据，如果有数据就会被复制过来，如果没有数据就会创建一张没有数据的空表
create table 表名 select * from 旧表名 where 条件;
本文来自博客园，作者：Chimengmeng，转载请注明原文链接：https://www.cnblogs.com/dream-ze/p/17520096.html
