1. 约束:唯一,非空,默认值
唯一约束
指定某一列不能出现相同的值
语法
在建表的时候创建
create table 表名 (
字段名 字段类型 unique
)
非空约束
设置某列数据不能为空,必须要输入
语法
create table 表名 (
字段名 字段类型 not null
)
默认值
如果一个字段没有设置它的值,将使用默认值
语法
create table 表名 (
字段名 字段类型 default 默认值
)
2. 外键约束
外键的概念:在一张表建立一个字段关联另一张表的主键,这个字段称为外键。
-
什么是外键:一定是出现在从表中。被主表中主键约束的。
-
创建表的时候,先创建主键,再创建从表。删除表的时候先删除从表,再删除主表。
创建外键约束
语法
新建表时增加外键:
create table 表名 (
外键字段名 字段类型,
foreign key(外键字段名) references 主表(主键)
)
已有表增加外键:
alter table 表名 add constraint 约束名 foreign key(外键字段名) references 主表(主键)
-- 约束名用于后期删除外键约束
删除外键
-- 删除employee表的employee_ibfk_1外键
alter table employee drop foreign key employee_ibfk_1;-- 在employee表情存在的情况下添加外键
alter table employee add foreign key (dept_id) references department(id);
3. 外键约束:级联更新和删除
什么是级联操作
修改主表中主键,或者删除主表中记录的时候,从表中外键也被同时修改,或者删除。
语法
写在外键约束的后面,在创建外键约束的时候创建级联操作
级联操作 | 语法 |
---|---|
级联更新 | on update cascade |
级联删除 | on delete cascade |
-- 删除外键约束
alter table employee drop foreign key employee_ibfk_1;-- 添加外键约束,级联更新和级联删除
alter table employee add constraint fk_emp_dept
foreign key (dept_id) references department(id) on update cascade on delete cascade;-- delete from employee where id > 6;
select * from employee;-- 把部门表中id等于1的部门改成id等于9
select * from department;
update department set id=9 where id=1;-- 删除部门号是2的部门
delete from department where id=2;
4. 数据库范式:第一范式
什么是范式
一种规则,指导程序员创建表的规则。数据库的范式有6种范式,程序员在设计表的时候,只需要符合三大范式就可以了。
三大范式
分成几个等级,一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的。
满足最低要求的范式是第一范式(1NF),在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式以次类推。一般说来,数据库只需满足第三范式(3NF)就行了。
第一范式
概念
原子性:表中每一列都不可以再分割成更小的列。如果一列中包含的数据可以再拆分,不符合第一范式。
第一范式:满足列不可再分。
案例
班级表
学号 | 姓名 | 班级 |
---|---|---|
1000 | 张三 | 一年级3班 |
1001 | 李四 | 一年级2班 |
2001 | 王五 | 二年级1班 |
解决方案
学号 | 姓名 | 年级 | 班号 |
---|---|---|---|
1000 | 张三 | 一年级 | 3班 |
5.数据库范式:第二范式
概念
在满足第一范式的基础上,有更多的要求,才是第二范式。表中每一列都必须完全依赖于主键,
示例
借书证表
主键 | 学生证号 | 学生证名称 | 学生证办理时间 | 借书证号 | 借书证名称 | 借书证办理时间 |
---|---|---|---|---|---|---|
1 | A2349U | 张三学生证 | 2018-01-23 | 29374 | 张三借书证 | 2019-03-10 |
6. 数据库范式:第三范式
概念
在满足第二范式的基础上,有更多的要求。 表中每列都直接依赖于主键,而不是通过其它列间接依赖于主键。
依赖关系
所谓传递依赖,指的是如果存在"A → B → C"的决定关系,则C传递依赖于A。
满足第三范式的数据库表应该不存在如下依赖关系:主键列 → 非主键列x → 非主键列y
示例
学生信息表
学号 | 姓名 | 年龄 | 所在学院 | 学院地点 |
---|---|---|---|---|
1000 | 张三 | 20 | 传智专修学院 | 江苏 |
2000 | 李四 | 19 | 广州美术学院 | 广州 |
存在传递的决定关系:学号 -> 学院 -> 地点
解决方案
从表:有外键 (多方)
主键 | 姓名 | 年龄 | 所在学院ID |
---|---|---|---|
从表 | 外键 |
学院ID | 学院名字 | 学院地点 |
---|---|---|
主表(1方) |
三范式:降低数据的冗余。提高表的科学性设计!指导思想!!
反三范式:为了提高性能。有时候会反三范式设计!
7. 表与表之间的关系概述
一对一
-- 主表
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键
NAME VARCHAR(20)
);
-- 从表
CREATE TABLE info(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键
address VARCHAR(20),
use_name VARCHAR(10),
weight DOUBLE,
-- 直接把主键约束成外键。 主键也可以是外键。
FOREIGN KEY(id) REFERENCES stu(id)
);
一对多
/*
一对多,一个分类对应多条线路
因为sql中不区分大小写:如果有多个单词,中间使用_分隔
*/
/*
创建旅游线路分类表tab_category
cid旅游线路分类主键,自动增长
cname旅游线路分类名称非空,唯一,字符串100
*/
create table tab_category(
cid int primary key auto_increment,
cname varchar(100) not null unique
);/*
创建旅游线路表tab_route
rid旅游线路主键,自动增长
rname旅游线路名称非空,唯一,字符串100
price价格
rdate 上架时间,日期类型
cid 外键,所属分类
*/
create table tab_route(
rid int primary key auto_increment,
rname varchar(100) not null unique,
price double,
rdate date,
cid int, -- 外键
foreign key(cid) references tab_category(cid)
);
多对多
-- 多对多的关系
/*
创建用户表tab_user
uid用户主键,自增长
username用户名长度100,唯一,非空
password密码长度30,非空
name真实姓名长度100
birthday生日
sex性别,定长字符串1
telephone手机号,字符串11
email邮箱,字符串长度100
*/
create table tab_user(
uid int primary key auto_increment,
username varchar(100) unique not null,
password varchar(30) not null,
name varchar(100),
birthday date,
sex char(1),
telephone varchar(11),
email varchar(100)
);
/*
创建收藏表tab_favorite
rid 旅游线路id,外键
date 收藏时间
uid用户id,外键
rid和uid不能重复,设置复合主键,同一个用户不能收藏同一个线路两次
*/
create table tab_favorite (
rid int, -- 线路id的外键
`date` datetime,
uid int , -- 用户id的外键
-- 创建复合主键
primary key(rid, uid),
foreign key(rid) references tab_route(rid), -- 关联了线路的主键
foreign key(uid) references tab_user(uid) -- 关联了用户的主键
);
8.表连接:笛卡尔积和内连接
-- 需求:查询所有的员工和所有的部门
-- 查询孙悟空在哪个部门名字
select * from emp;select * from dept;
-- 需求:查询所有的员工和所有的部门
-- 查询2张表结果是,是2张表记录的乘积,称为笛卡尔积
select * from emp,dept;-- 如何消除笛卡尔积:条件是从表.外键=主表.主键
select * from emp,dept where emp.dept_id = dept.id;
-- 这就是隐式内连接,使用where,没有用到join...on-- 给表起别名
select * from emp e ,dept d where e.dept_id = d.id;
-- 查询孙悟空在哪个部门名字
select * from emp e ,dept d where e.dept_id = d.id and e.id=1;
-- 只显示2列
select e.name 员工名,d.name 部门名 from emp e ,dept d where e.dept_id = d.id and e.id=1;
隐式内连接语法
select 列名 from 左表,右表 where 从表.外键=主表.主键
显式内连接语法
-- 显示内连接, on后面就是表连接的条件
select 列名 from 左表 inner join 右表 on 从表.外键=主表.主键
9. 外连接:左连接和右连接
左连接
语法
select 列名 from 左表 left join 右表 on 从表.外键=主表.主键
概念
左连接:保证左表中所有的数据都出现,如果右表没有对应的记录,使用NULL填充
右连接
语法
select 列名 from 左表 right join 右表 on 从表.外键=主表.主键
概念
右连接:保证右表中所有的数据都出现,如果左表没有对应的记录,使用NULL填充
10.子查询:子查询引入
子查询的概念
-
将一个查询的结果做为另一个查询的条件
-
这是一种查询语句的嵌套,嵌套的SQL查询称为子查询。
-
如果使用子查询必须要使用括号
-- 需求:查询开发部中有哪些员工
select * from emp;select id from dept where name='开发部';
select * from emp where dept_id=1;
-- 写成一句:使用子查询
select * from emp where dept_id=(select id from dept where name='开发部');select * from emp where dept_id=(select id from dept where name='市场部');
子查询: 单行单列的情况
- 如果子查询是单行单列,父查询使用比较运算符:> < =
-- 案例:查询工资最高的员工是谁?
-- 1. 查询最高工资是多少
select max(salary) from emp;
-- 2. 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary=(select max(salary) from emp);-- 查询工资大于"蜘蛛精"的员工
-- 1. 查询蜘蛛精的工资是多少
select salary from emp where name='蜘蛛精';
-- 2. 查询大于这个工资的员工
select * from emp where salary > (select salary from emp where name='蜘蛛精');
子查询: 多行单列的情况
- 多行单列认为是一个数组,父查询使用in /any /all
-- 查询工资大于5000的员工,来自于哪些部门,得到部门的名字
-- 1. 先查询大于5000的员工所在的部门id
select dept_id from emp where salary > 5000;-- 2. 再查询在这些部门id中部门的名字
-- Subquery returns more than 1 row
select * from dept where id=(select dept_id from emp where salary > 5000);
select * from dept where id in(select dept_id from emp where salary > 5000);-- 列出工资高于在1号部门工作的所有员工,显示员工姓名和工资、部门名称。
-- 1. 查询1号部门所有员工的工资,得到多行单列
select salary from emp where dept_id=1;-- 2. 使用大于号不能计算,怎么办
select * from emp where salary > all (select salary from emp where dept_id=1);
-- any表示任何一个,all所有
select * from emp where salary > any (select salary from emp where dept_id=1);
子查询: 多行多列的情况
- 认为它是一张虚拟表,可以使用表连接再次进行多表查询
-- 查询出2011年以后入职的员工信息,包括部门名称
-- 1. 在员工表中查询2011-1-1以后入职的员工
select * from emp where join_date > '2011-1-1';-- 2. 查询所有的部门信息,与上面的虚拟表中的信息组合,找出所有部门id等于的dept_id
select * from dept d inner join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;select * from dept d right join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;