SQL

数据完整性

实体完整性

列约束：MySQL可以对插入的数据进行特定的验证，只有满足条件才可以插入到数据表中，否则认为是非法插入。

主键（primary key）：

• 一个表只能有一个主键
• 主键具有唯一性，主键不能重复
• 主键字段的值不能为null
• 声明字段时，用 primary key 标识
• 主键可以由多个字段共同组成。此时需要在字段列表后声明的方法

eg：

create table test_primary(
	id int primary key,
	name varchar(255),
	address varchar(255)
);


insert into test_primary values(1,"zhangsan","beijing");

insert into test_primary values(1,"lisi","liaoning");

select * from test_primary;

出现error：
> 1062 - Duplicate entry '1' for key 'PRIMARY'

• auto_increment（自动增长约束）

• 一些序号，没有必须手动生成，想让mysql自动生成。

  • 自动增长必须为索引(主键或unique)

  • 只能存在一个字段为自动增长

  • 默认为1开始自动增长

eg：

create table test_autoincrement(
	id int primary key auto_increment,
	name varchar(255),
	address varchar(255)
);


insert into test_autoincrement(name, address) values ("zhangsan", "beijing");

insert into test_autoincrement(name, address) values ("lisi", "nanjing");

insert into test_autoincrement(name, address) values ("wangwu", "jilin");

-->
+----+----------+---------+
| id | name     | address |
+----+----------+---------+
|  1 | zhangsan | beijing |
|  2 | lisi     | nanjing |
|  3 | wangwu   | jilin   |
+----+----------+---------+
默认从1开始自动增长

自动增长是不是一定会连续？

• 不能
• 比如说自己手动插入了一个id = 100的数据，则下次auto_increment从id = 101开始
• 再比如表里有unique字段，插入了一个重复的值，导致插入失败，会导致插入id不连续

域完整性

• null/not null
  • not null：代表不允许为空，如果插入了null，则会报错
• unique
  • 不能重复
  • 允许插入null
  • null可以重复

unique与primary key的区别和联系：

• 都不能存储重复的值
• primary key不能存储null，unique能存储null

参照完整性

• 外键

default（默认值）

• 如果不指定值，则使用默认值
• 如果指定了值，则就用指定的值

eg：

create table tab 
( create_time timestamp default current_timestamp );

-- 表示将当前时间的时间戳设为默认值。
current_date, current_time

comment（注释）

• 给自己看的

eg：

create table test_comment(
  id int primary key auto_increment,
  name varchar(255) comment "名字",
  status int comment "0表示未付款，1表示已付款，2"
);

---

多表设计

一对一

• 一对一是指两个表中的数据是一一对应的
  • 比如：人和身份证号、用户和用户详情

存储关系：

• 一对一的情况，关系可以存储在任意一张表上，只要新增一个字段。
• 所有的一一对应的表，在逻辑上，都可以合并为一个表，但是出于效率的考虑没有合并

一对多

• 存在表A和表B，表A中的一条数据，对应表B中的多条数据；而表B中的一条数据，对应表A中的一条数据
  • 比如：学生和班主任

存储关系：

• 关系存储在多的一方

多对多

• 指存在表A和表B，表A中的一条数据，对应表B中的多条数据；而表B中的一条数据，对应表A中的多条数据。
  • 比如：学生和课程、订单和商品

存储关系：

• 需要额外一张表来存储之间的关系

数据库三大范式

第一范式：原子性

• 我们存储在数据库的列，应该保持原子性。
  • 比如：地址

第二范式：唯一性

• 每一张表，需要有一个主键

第三范式：数据的冗余

• 数据不要冗余
• 右边这里即存储了班主任id又存储了班主任姓名，造成了冗余
• 冗余的好处
  • 查询速度快
• 冗余的坏处
  • 耗费磁盘
  • 修改的时候，要修改多张表

反范式化设计：

• 如果你的需求频繁的要根据学生找老师名
• 查询的需求远远大于修改的需求

---

多表查询

-- 如果user这个表存在，就去删除
drop table if exists user;

create table user(
	id int primary key auto_increment,
	name varchar(255),
	password varchar(255)
);

连接查询

交叉连接

• 就是求多个表的笛卡尔积
• 交叉连接的结果没有实际的意义
  • 但是内连接和外连接都是基于交叉连接的结果去筛选的

eg：

select * from student_test cross join test_primary;

-->
+------+----------+------+---------+--------+----+----------+---------+
| id   | name     | age  | address | remark | id | name     | address |
+------+----------+------+---------+--------+----+----------+---------+
|    1 | lihua    |   20 | china   | None   |  1 | zhangsan | beijing |
|    2 | zhangsan |   18 | Asia    | None   |  1 | zhangsan | beijing |
|    3 | mike     |   21 | china   | None   |  1 | zhangsan | beijing |
|    4 | Jack     |   18 | china   | None   |  1 | zhangsan | beijing |
+------+----------+------+---------+--------+----+----------+---------+

inner join（内连接）

• 是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息

eg：

-- 内连接只返回符合连接条件的数据
select * from student_test inner join test_primary on student_test.id = test_primary.id;

-->
+------+-------+------+---------+--------+----+----------+---------+
| id   | name  | age  | address | remark | id | name     | address |
+------+-------+------+---------+--------+----+----------+---------+
|    1 | lihua |   20 | china   | None   |  1 | zhangsan | beijing |
+------+-------+------+---------+--------+----+----------+---------+


-- 1. 一般会先取别名，给表取名
-- 2. 先用*来占位，最后需要什么数据，再通过 别名.属性名拿取
select st.name,tp.address
from student_test st inner join test_primary tp on st.id = tp.id;

-->
+-------+---------+
| name  | address |
+-------+---------+
| lihua | beijing |
+-------+---------+

outer join（外连接）

• 左外连接：会在内连接的结果的基础之上，去和左表做并集，会保留左表的全部数据

  • left outer join / left join

• 右外连接：会在内连接的结果的基础之上，去和右表做并集，会保留右表的全部数据

  • right outer join / right join

• outer是可以省略的，可以写成left join或者right join

eg：

-- 左外连接
-- 左外连接：会保存左表的全部数据
select * from student_test st left outer join test_primary tp on st.id = tp.id;

-->
+------+----------+------+---------+--------+------+----------+---------+
| id   | name     | age  | address | remark | id   | name     | address |
+------+----------+------+---------+--------+------+----------+---------+
|    1 | lihua    |   20 | china   | None   |    1 | zhangsan | beijing |
|    2 | zhangsan |   18 | Asia    | None   | NULL | NULL     | NULL    |
|    3 | mike     |   21 | china   | None   | NULL | NULL     | NULL    |
| NULL | Jack     |   18 | china   | None   | NULL | NULL     | NULL    |
+------+----------+------+---------+--------+------+----------+---------+


-- 右外连接
-- 右外连接：会保存右表的全部数据
select * from student_test st right outer join test_primary tp on st.id = tp.id;

-->
+------+-------+------+---------+--------+----+----------+---------+
| id   | name  | age  | address | remark | id | name     | address |
+------+-------+------+---------+--------+----+----------+---------+
|    1 | lihua |   20 | china   | None   |  1 | zhangsan | beijing |
+------+-------+------+---------+--------+----+----------+---------+

每次写数据库的时候，在代码前面要加上：drop table if exists 表名;，但是修改表的时候不要运行，否则写好的数据就会清除。

inner join，left (outer) join，right (outer) join的区别：

• inner join只会保留on后面条件符合的（也可以说交集）
• left join除了保留交集，还会保留左表的所有数据
• right join除了保留交集，还会保留右表的所有数据

写关联查询最重要的两件事：

1. 使用什么连接
2. 关联条件

子查询

• 又称为嵌套查询。

• 一个SQL语句的结果可以作为另外一个SQL语句的条件

• 子查询很符合直觉，但是速度太慢了，能不用就尽量不要用

  • 子查询会生成临时表

eg：

-- 首先先拿到Java的id
select id from tec_cource where name='Java';
-- 然后再把这个id=1放给第二个
select * from tec_sele_cource where cource_id=1;
select * from tec_stu where id in (1,3);

---->
-- 看学生信息
select * from tec_stu where id in (
    -- 看哪些学生选了 Java
	select student_id from tec_sele_cource where cource_id=(
        -- 获取Java的id
		select id from tec_cource where name='Java'
	)
)

联合查询

• SQL支持把多个SQL语句的结果拼装起来
• union将两个SQL的结果，拼接起来返回
• 两条SQL返回的列应当一致
  eg：

select * from students where class = '一班'
union
select * from students where class = '二班';

等价于
select * from students where class = ('一班', '二班');

数据库的备份与恢复

cmd命令行操作

# 备份
# 1. 打开命令行
mysqldump -uroot -p dbName（数据库的名称） > c:/path/dbName.sql(就是要存储的路径名)
-- > 表示把这个数据库输出到哪里

# 恢复
# 1. 打开命令行
# 2. 连接MySQL服务器
mysql -uroot -p

# 3. 选中数据库（假如没有合适的数据库，可以新建一个）
use dbName;
# 4. 执行文件中的SQL语句，恢复数据
source c:/path/dbName.sql

通过Navicat操作

• 备份
  
• 恢复