约束
概述
1.概念:约束是作用域表中字段上的规则,用于限制存储在表中的数据。
2.目的:保证数据库中数据的正确,有效性和完整性。
3.分类:
约束演示
根据需求,完成表结构的创创建:
create table user(
id int primary key auto_increment ,
name varchar(10) not null unique ,
age int check ( age > 0 && age <=120 ) ,
status char(1) default '1',
gender char(1)
) comment '用户表';
insert into user(name,age,status,gender) values ('Tom',19,'1','男'),('Mike',22,'0','男');
外键约束:
概念:外键用来让两张表的数据建立链接,从而保证数据的一致性和完整性。
dept_id是外键, id是主键。
在数据库层面,并未建立外键关联,所以无法保证数据的一致性和完整性。
语法:
添加外键:
`create table 表名(`
• `字段名 数据类型,`
• `......`
• `[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)`
`);`
`alter table 表名 add constraint 外键名称 foreign key refernces 主表(主表列名);`
`删除外键:`
`alter table 表名 drop forergn key 外键名称`
;
删除/更新行为:
多表查询
多表关系
项目开发中,各种关系基本上分为三种:
一对多(多对一):
案例:部门与员工的关系。
关系:一个部门对应多个员工,一个员工对应多个部门。
实现:在多的一方建立外键,指向一的一方 的主键。
多对多:
案例:学生与课程的关系。
关系:一个学生可以选择多门课程,一门课程也可以供多个学生选择。
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键。
一对一:
案例:用户与用户详情的关系。
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情放在另一张表中,以提升效率。
实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的。
多表查询概述
概述:从多张表中查询数据。
xxx select * from emp , dept;(笛卡尔积)
在多表查询时,需要消除无效的笛卡尔积。
select * from emp ,dept where emp .dept_id = dept.id ;
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar (50) not null comment'部门名称'
)comment'部门表';
INSERT INTO dept (id, name) VALUES (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,
'销售部'),(5,'总经办'),(6,'人事部');
select * from dept;
-- 创建emp表,并插入数据
create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment'姓名', age int comment'年龄',
job varchar(20) comment'职位', salary int comment'薪资',
entrydate date comment'入职时间', managerid int comment'直属领导ID',
dept_id int comment'部门ID')comment'员工表';
-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) ;
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
(1,'金庸',66,'总裁',20000,'2000-01-01', null,5),
(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
(3,'杨逍',33,'开发',8400,'2000-11-03',2,1),
(4,'书一笑',48,'开发',11000,'2002-02-05',2,1),
(5,'过常遇春',43,'开发',10500,'2004-09-07',3,1),
(6,'见小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1),
(7,'灭绝',60,'财务总监',8500,'2002-09-12',1,3),
(8,'这周芷若',19,'会计',48000,'2006-06-02',7,3),
(9,'丁敏君',23,'出纳',5250,'2009-05-13',7,3),
(10,'赵敏',20,'市场部总监',12500,'2004-10-12',1,2),
(11,'鹿杖客',56,'职员',3750,'2006-10-03',10,2),
(12,'鹤笔翁',19,'职员',3750,'2007-05-09',10,2),
(13,'方东白',19,'职员',5500,'2009-02-12',10,2),
(14,'张三丰',88,'销售总监',14000,'2004-10-12',1,4),
(15,'俞莲舟',38,'销售',4600,'2004-10-12',14,4),
(16,'宋远桥',40,'销售',4600,'2004-10-12',14,4),
(17,'陈友谅',42,null,2000,'2011-10-12',1,null);
select * from emp,dept where emp.dept_id = dept.id;
连接查询
内连接:相当于查询A,B交集部分数据
外连接:左外连接:查询左表所有数据以及两张表交集部分数据
右外连接:查询右表所有数据以及两张表交集部分数据
自连接:当前表与自身连接查询,自连接必须使用表别名
内连接
隐式内连接:
select 字段列表 from 表1,表2 where 条件.......;
显式内连接:
select 字段列表 from 表1 join 表2 on 连接条件......;
/*查询每一个员工的姓名,以及关联部门的名称(隐式内连接实现)*/
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id;
/*查询每一个员工的姓名,以及关联部门的名称(显式内连接实现)*/
select emp.name ,dept.name from emp join dept on dept.id = emp.dept_id;
外连接
左外连接:
select 字段名 from 表1 left join 表2 on 条件......;
右外连接:
select 字段名 from 表1 right join 表2 on 条件......;
/*查询emp表的所有数据,和对应的部门信息*/
select emp.*,d.name from emp left join dept d on d.id = emp.dept_id;
/*查询dept表的所有数据,和对应的员工信息*/
select emp.*,dept.* from emp right join dept on dept.id = emp.dept_id;
自连接
select 字段名 from 表名A 别名A join 表A 别名B on 条件...;
/*查询员工及其所属领导的名字*/
select a.name , b.name from emp a , emp b where a.managerid = b.id;
/*查询所有员工及其领导的名字,如果员工没有领导也需要查询出来*/
select a.name,b.name from emp a left join emp b on a.managerid = b.id;
联合查询
union(all)
子查询
概念:SQL语句中嵌套SELECT语句,成为嵌套语句,又称子查询。
select * from t1 where column1 = (select column1 from t2);
子查询外部的语句可以是insert/update/delete/select
根据子查询结果不同分为:
标量子查询(子查询结果为一个值);
列子查询(子查询结果为一列);
行子查询(子查询结果为一行);
表子查询(子查询结果为多行多列)。
根据自查询位置分为:where之后,from之后,select之后。
标量子查询
子查询返回的结果是单个值(数字,字符串,日期等),最简单的形式,叫标量子查询。
/*查询销售部的所有员工信息*/
-- a.查询销售部的部门id
select id from dept where name = '销售部';
-- b.根据销售部的id,查询员工信息
select * from emp where dept_id = 4;
select * from emp where dept_id =(select id from dept where name = '销售部');
-- 查询方东白入职后的员工信息
-- a.查询方东白的入职日期
select entrydate from emp where name = '方东白';
-- b.查询日期之后的员工信息
select * from emp where entrydate > '2009-02-12';
select * from emp where entrydate > (select entrydate from emp where name = '方东白');
列子查询
子查询返回的结果是一列(可以是多行),叫列子查询。
常用操作符in ,not in, some ,all
-- 查询销售部和市场部的所有员工信息
select * from dept where name = '销售部' or name = '市场部' ;
select * from emp where dept_id in (select * from dept where name = '销售部' or name = '市场部') ;
-- 查询比财务部所有人员工资都高的员工信息
select id from dept where name = '财务部' ;
select salary from emp where dept_id = (select id from dept where name = '财务部') ;
select * from emp where salary > all( select salary from emp where dept_id = (select id from dept where name = '财务部'));
-- 查询比研发部任意一人工资高的员工信息
select id from dept where name = '研发部' ;
select salary from emp where dept_id = (select id from dept where name = '研发部');
select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name = '研发部'));
行子查询
-- 查询与张无忌的薪资和领导相同的员工信息
select salary , managerid from emp where name = '张无忌' ;
select * from emp where (salary,managerid) = (select salary , managerid from emp where name = '张无忌');
表子查询
子查询返回的结果是多行多列,叫表子查询。常用标识符:in
-- 查询与张无忌的薪资和领导相同的员工信息
select salary , managerid from emp where name = '张无忌' ;
select * from emp where (salary,managerid) = (select salary , managerid from emp where name = '张无忌');
-- 查询与鹿杖客,宋远桥的职位和薪资相同的员工信息
select job,salary from emp where name = '鹿杖客' or name = '宋远桥';
select * from emp where (job,salary) in (select job,salary from emp where name = '鹿杖客' or name = '宋远桥');
-- 查询日志日期是’2006-01-01‘之后的员工信息,及其部们信息
select * from emp where entrydate > '2006-01-01' ;
select e.*,dept.* from (select * from emp where entrydate > '2006-01-01') e left join dept on e.dept_id = dept.id;