目录
1.表的约束(数据库完整性)
表的约束即保证数据库的完整性-->转 数据库的完整性
1.1.表约束概念
- 对表中的数据进行限定约束，保证数据的正确性，有效性和完整性。
1.2.约束四大类
- 1.主键约束:primary key
- 2.非空约束:not null
- 3.唯一约束:unique
- 4.外键约束:foreign key
1.2.1.非空约束
- 非空约束:not null，被约束的字段值不能为null。
1.创建表时添加非空约束
create table student3(
id int,
name varchar(20) not null -- name字段要求非空
);
-- 创建表后删除name的非空约束(即修改表字段信息modify)
alter table student3 modify name varchar(20);
2.-- 创建表后给name字段添加非空约束
alter table student3 modify name varchar(20) not null;1.2.2.唯一约束
- 唯一约束:unique,被约束的字段值不能重复。(唯一约束通常又称唯一索引)
1.创建表时添加number字段的唯一约束
create table student3(
id int,
number varchar(20) unique; -- number字段要求唯一
);
-- 创建表后删除number字段的唯一约束(唯一索引)
alter table student3 drop index number;-- 修改表student3，删除number索引。
-- 创建表后，添加唯一约束
alter table student3 modify number varchar(20) unique;- 注意:被约束唯一的字段数据可以是null,可以有多个null，mysql不认为null与null是重复的。即mysql中，唯一约束限定的列的值可以有多个null.
1.2.3.主键约束:primary key
- 1.注意:
- 1.含义:非空且唯一。
- 2.一张表只能有一个字段为主键。
- 3.主键就是表中记录的唯一标识。
- 2.在创建表时添加主键约束:
create table student3(
id int primary key, -- 给id添加主键约束
name varchar(20)
);- 删除主键
alter table student3 drop primary key;- 3.创建完表后再添加主键
alter table student3 modify id int primary key;1.2.4.主键约束_自动增长
- 1.概念:
- 如果某一列是数值类型的，使用auto_increment可以来完成值的自动增长。
- 2.在创建表时，添加主键约束，并且完成主键自增长
create table student3(
id int primary key auto_increment,-- 给id添加主键约束以及自增长
name varchar(20)
);
insert into student3 values(1,'a');- 添加主键自增长约束后的字段值可以null,id值根据上一条记录的值加一，即2
insert into student3 values(null,'b');
insert into student3 values(5,'c');
insert into student3 values(null,'d');-- id的值为6- 删除自动增长
alter table student3 drop id int; -- 这样只删除自动增长，不会删除主键的。- 3.创建表后添加自动增长
alter table student3 modify id int auto_increment;1.2.5.外键约束
1.创建如下的部门信息表:
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30), -- 部门的名称
dep_location VARCHAR(30)-- 部门的地址
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
SELECT * FROM emp;2.上述表存在的问题(缺点):
- 发现添加的数据中有很多冗余的数据，而且如果要修改研发部这个部门的名称的话就需要修改所有在该部门的员工中的dep_location字段的数据，不符合数据库设计的准则。
3.解决方案:表的拆分
- 一张表记录员工的基本信息，另一张表记录部门信息，然后将员工表和部分表通过外键进行关联即可。
4.表的拆分及添加外键和级联操作:员工表+部门表
-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
-- 添加 2 个部门
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
select * from department;
-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 外键对应主表的主键(employee表的dep_id字段关联department表的id字段)
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
-- 下面给外键添加级联操作，需要先删除外键，然后重新添加外键，同时设置级联的更新和删除操作
alter table employee drop foreign key emp_dept_fk;
alter table employee add constrainst (emp_dept_fk) foreign key (dep_ip) references
department (id) on update cascade on delete cascade;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
select * from employee;5.外键约束:foreign key，
- 让表与表关联，从而保证数据的正确性。(这就是产生外键的原因)。
6.在创建表时，可以添加外键
create table 表名(
....
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称);
);7外键关联两张表效果.
- employee表的dep_id字段通过外键关联department表的id字段，这样当你要删除department部门表中的id为1的研发部时，系统就会报错，告诉你有一张表的外键引用(约束)着这个id，不允许删除。
- 另外当前部门表中的id字段只有1和2时，若在员工表添加员工信息时的部门编号若是设置5则也会报错的，由于外键的关联在部门表中找不到id编号为5的部门信息;
- 另外部门表的id也不能随便更改，因为有一张表外键约束着，这样就避免数据错误。
8.删除外键
alter table 表名 drop foreign key (外键名称)
alter table employee drop foreign key emp_dept_fk;9.创建表后添加外键:
alert table 表名 add constraint外键名称 foreign key(外键字段名称) references主表(主表列名)
alter table employee add constraint emp_dept_fk foreign key (dep_id) references
department(id);10.注意:
- 外键的值可以null,但不可以为被外键约束的字段中不存在的值。
- 设置好外键后，可以点击数据库导航栏最后一项的new schema designer(架构设计器)来查看两表关系:
1.2.6.外键约束_级联操作
0.什么是级联操作
- 在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作。
1.产生的原因:
- 在上述的员工表和部门表中，员工表的dep_id字段设置为外键关联着部门表的id字段，虽然将两表关联，保证数据的正确性，但是也造成了由于员工表的外键约束，导致部门表中被约束的id字段不能随意更改，不然可能会造成第一表无法关联到第二张表(即找不到对应的信息)，因此为了实现修改部门表的部门编号id数据同时，使员工表中的部门编号dept_id随之修改，就需要级联操作。
2.级联操作_更新:
- 先删除表的外键，然后重新添加外键同时，设置级联更新。
alter table employee add constrainst (emp_dept_fk) foreign key (dep_ip) references
department (id) on update cascade;3.级联操作_删除:
- 实现当把部门表的id=1的部门记录删除后，员工表的dep_id字段中对应的相关数据也会被删除掉。
- 先删除表的外键，然后给表重新添加外键的同时，设置级联删除。
alter table employee add constrainst (emp_dept_fk) foreign key (dep_ip) references
department (id) on delete cascade;4.注意:
- 级联更新和级联删除需要删除表中的外键后，给表重新设置外键同时设置级联操作。
- 级联更新和删除可以同时设置。
5.级联操作缺点:
- 级联操作虽然方便，但是有很大的弊端，特别是级联删除操作，将来删除部门表id=1的部门信息时，它会把和这条记录关联的所有记录都删除掉，如果将来有很多表，它们都是关联的，当删除某张表的记录后，其它表中只要和这个记录(的字段)有关联都会被自动删除掉，因为设置了级联删除操作。这样操作很危险，而且影响性能。所以在实际开发中对这种级联的操作用的时候非常的谨慎。
2.数据库设计
2.1.多表设计(建表原则)
1.多表之间的关系:
- 1.一对一:
- 如:人和身份证，一个人只有一个身份证，一个身份证只能对应一个人。
- 2.一对多(多对一)
- 如:部门和员工,一个部门有多个员工，一个员工只能对应一个部门。
- 3.多对多
- 如:学生和课程，一个学生可以选择很多门课程，一个课程也可以本很多学生选择。
2.一对多关系的实现(建表原则):
- 如:部门表和员工表：
- 在多的一方(员工表)建立外键，指向一的一方(部门表)的主键。
3.多对多关系的实现(建表原则):
- 如:学生表和课程表，
- 多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表外键，分别指向两表主键。
4.一对一关系的实现(建表原则)
- 如:学生表和身份证表
- 一对一关系的实现，可以在任意一方添加唯一外键指向另一方的主键。添加外键可以实现一对多，再加上唯一约束即可)当然，一对一关系的话，通常都是合成一张表来完成的。
2.2.数据库设计的范式
1.范式概念:
- 设计数据库时，需要遵循的一些规范。不同范式即对数据库设计的要求不同。
2.官方解释:
- 设计数据库时，遵循不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，
- 各种规范成递次规范，越高的范式，数据库冗余越小。
- 目前关系型数据库有6种规范:第一范式(1NF),第二范式(2NF),第三范式(3NF),巴斯-科德范式(BCNF),第四范式(4NF)和第五范式(5NF又称完美范式).
3.范式分类:
第一范式(1NF):
- 每一列都是不可分割的原子数据项。
- (学号，姓名，系(系名，系主任),课程名称，分数)，该表不符合第一范式，存在系这列可以分割。
第二范式(2NF):
- 在1NF基础上，非码属性须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)
- 1.函数依赖:A-->B,如果通过A属性(组)的值，可以确定唯一的B属性的值，称B依赖于A。
- 例如:学号-->姓名，(学号，课程名称)-->分数
- 2.完全函数依赖:A-->B,如果A是一个属性组，则B属性值的确定需要依赖A属性组中所有的属性值。
- 例如:(学号,课程名称)-->分数，分数需要依赖(学号,课程名称)这个属性组中所有的属性才能唯一确定。
- 3.部分函数依赖:A-->B,如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某一些值即可(非全部)
- 例如:(学号,课程名称)-->姓名，姓名只需要依赖学号就可以确定，不需要这个属性组来确定。
- 4.传递函数依赖:A-->B,B-->C,如果通过A属性(组)的值，可以确定唯一B属性的值，再通过B属性值可唯一确定C属性的值，则称C传递函数依赖于A。
- 例如:学号-->系名，系名-->系主任。学号被系名所依赖，系名被系主任所依赖。
- 5.码:如果在一张表中，一个属性或属性组被其它所有属性所完全依赖，则称该属性(组)为该表的码。
- 下表中(学号,姓名,系名,系主任,课程名称,分数)，学号属性并没有被其它所有属性完全依赖，即学号不能唯一的确定所有其它的属性，比如通过学号不能唯一确定课程名称和分数，而通过(学号，课程名称)是可以确定其它属性的唯一值，则称(学号,课程名称)为该表的码。
- 6.主属性:指码属性组中的所有属性。
- 比如:若(学号，课程名称)为主码的话(即可以唯一确定其它所有属性)，那么学号，课程名称都是主属性。
- 7.非主属性:除码属性组的属性外，其它就是非主属性。
第二范式解释:
- 第二范式的定义是:在第一范式的基础上，消除非主属性对主码的部分函数依赖。
- 而对于学生表(学号,姓名,系名，系主任，课程名称，分数)来说，(学号，课程名称)属性组可以唯一确定表中其它属性，
- 即该属性组为码属性组，该组中的属性称主属性，则表中其它属性称非主属性，
- 第二范式要求表消除非主属性对码属性的部分函数依赖，即非码属性必须完全依赖于码属性组，非码属性不能部分依赖码属性组，显然该学生表中姓名、系名、系主任这三个非主属性是部分函数依赖于码属性组中的学号属性，
- 即学号-->姓名，系名，系主任；存在部分函数依赖则就需要进行表的拆分。
- 即:选课表(学号，课程名称，分数)；学生表(学号，姓名，系名，系主任)；
- 这两张表都符合第二范式，即消除非主属性对码属性组的部分函数依赖。
- 但是第二范式只解决了数据冗余的问题，当删除学生表中某个同学的记录，仍然会删除系名和系主任的记录；添加新开设的系和系主任时，这条记录也是不全的。下面就需要使用第三范式来解决。
3.第三范式(3NF):
- 在2NF基础上，任何非主属性不依赖与其它非主属性(在2NF基础上，消除传递依赖)。
- 第三范式定义是:在2NF基础上，消除传递依赖，即任何非主属性不依赖于其它非主属性。
- 在第二范式规范下的学生表(学号，姓名，系名，系主任)中，学号-->系名，系名-->系主任。即不符合第三范式。则再拆表。
- 经学生表(学号，姓名，系名，系主任)拆成学生表(学号，姓名，系名)和系表(系名，系主任)；
4.分析下图表格:
- 首先系这个列可以分割为系名和系主任，即可以分割，所以不满足第一范式。将系列分割后就满足了第一范式。
- 满足第一范式后分析这个表存在的问题:
- 第一个问题是:存在非常严重的数据冗余(重复):姓名、系名、系主任这三列数据。
- 第二个问题是:数据添加存在问题，添加新开设的系和系主任时，那条记录的数据不合法。(即其它字段没有数据)。
- 第三个问题是:张无忌毕业，删除他的数据，会将系的数据一起删除。
5.总结:
- 原始表(学号，姓名，系(系名，系主任)，课程名称，分数)
- 经第一范式，每一个列都是不可分割的原子数据项后的表:
- (学号，姓名，系名，系主任，课程名称，分数)
- 经第二范式，在第一范式基础上消除非主属性对码属性组的部分函数依赖，码属性组是(学号，课程名称)
- 学生表(学号，姓名，系名，系主任)，选课表(学号，课程名称，分数)
- 经第三范式，在第二范式基础上，消除非主属性对其它非主属性的传递依赖，学号-->系名，系名-->系主任
- 学生表(学号，姓名，系名)，系表(系名，系主任)，选课表(学号，课程名称，分数)
- 至此，该表就符合第三范式。
3.多表关系设计案例
1.问题描述:
- 旅游网站有收藏功能:即用户根据旅游线路分类，选择旅游线路，收藏进收藏夹。设计数据库表:
2.数据库设计:
- 三个实体分别是旅游线路分类，旅游线路，用户，然后为这三个实体设计三张表，
- 旅游线路分类表和旅游线路表是一对多的关系，即一种旅游线路分类下有很多旅游线路可以选择，但一个旅游线路只能属于一种旅游线路分类中。
- 分析出是一对多关系后，就可以设计这两张表，即线路表的外键指向线路分类表的主键。
- 线路表和用户表是多对多的关系，即一个线路可以被多个用户收藏，一个用户也可以收藏多个线路。
- 分析出是多对多关系后，设计表，需要设计中间表关联同时关联线路表和用户表，该表中至少有两个字段都是外键，分别指向这两张表的主键。
-- 1.创建旅游线路分类表 tab_category
/*cid 旅游线路分类主键，自动增长
-- cname 旅游线路分类名称非空，唯一，字符串 100
*/
create table tab_category (
cid int primary key auto_increment,
cname varchar(100) not null unique
);
-- 添加旅游线路分类数据：
insert into tab_category (cname) values ('周边游'), ('出境游'), ('国内游'), ('港澳游');
select * from tab_category;
-- 2.创建旅游线路表 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)
);
-- 添加旅游线路数据
INSERT INTO tab_route VALUES
(NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁 3 天 惠贵团】尝味友鸭面线 住 1 晚鼓浪屿', 1499,
'2018-01-27', 1),
(NULL, '【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-22', 3),
(NULL, '【爆款￥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州往返 特价团】', 1699, '2018-01-27', 2),
(NULL, '【经典•狮航 ￥2399 秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23',2),
(NULL, '香港迪士尼乐园自由行 2 天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店暨会议中心标准房 1 晚住宿】', 799, '2018-04-10', 4);
select * from tab_route;
-- 3.创建用户表 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) default '男',
telephone varchar(11),
email varchar(100)
);
-- 添加用户数据
INSERT INTO tab_user VALUES
(NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'),
(NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com');
select * from tab_user;
-- 4.创建收藏表 tab_favorite
/*
rid 旅游线路 id，外键
date 收藏时间
uid 用户 id，外键
rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
create table tab_favorite (
rid int,
date datetime,
uid int,
-- 创建复合主键
primary key(rid,uid),
foreign key (rid) references tab_route(rid),-- 复合主键中每一个主键作为外键指向主表主键
foreign key(uid) references tab_user(uid)
);
-- 增加收藏表数据
INSERT INTO tab_favorite VALUES
(1, '2018-01-01', 1), -- 老王选择厦门
(2, '2018-02-11', 1), -- 老王选择桂林
(3, '2018-03-21', 1), -- 老王选择泰国
(2, '2018-04-21', 2), -- 小王选择桂林
(3, '2018-05-08', 2), -- 小王选择泰国
(5, '2018-06-02', 2); -- 小王选择迪士尼
select * from tab_favorite;4.表与表之间关系及维护
- 一对多:在多方(从表)设置外键指向(约束)一方(主表)的主键，比如在成员表中设外键dept_id指向部门表主键id
- 多对多:使用中间表，设置联合主键，分别作为外键指向两表的主键，形成两个一对多关系。
- 一对一:一对一关系的实现，可以在任意一方添加唯一外键指向另一方的主键。添加外键可以实现一对多，再加上唯一约束即可)当然，一对一关系的话，通常都是合成一张表来完成的。
5.多表查询
多表查询时几张表需要主外键进行关联，消除无用的数据
5.1.准备数据-创建数据表
# 创建部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept (name) values ('开发部'),('市场部'),('财务部');
# 创建员工表
create table emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), -- 性别
salary double, -- 工资
join_date date, -- 入职日期
dept_id int,
foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
);
insert into emp(name,gender,salary,join_date,dept_id) values
('孙悟空','男',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values
('猪八戒','男',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values
('唐僧','男',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values
('白骨精','女',500s0,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values
('蜘蛛精','女',4500,'2011-03-14',1);
5.2.笛卡尔积
1.笛卡尔积
- 当同时查询多张表emp和dept表时产生的结果集称为笛卡尔积，
- 即对于A,B两个集合，将这两个集合的所有组合情况就是笛卡尔积，
- 比如A集合有3条数据，B集合有2两条数据，那么它们的所有组合情况就是3*2=6种。
- 但在所有的结果集中有一些数据是不正确的，比如在员工表中孙悟空的部门编号对应的是1，但在结果集中有两条关于孙悟空的数据在部门编号属性值上是错误的。所以多表查询要消除无用的数据，也叫消除无用的笛卡尔积数据。
2.总结:
- 笛卡尔积:有两个集合A,B,取这两个集合的所有组成情况。
- 要完成多表查询，需要使用主外键关系做条件消除无用的数据。
5.3.多表查询分类
多表查询要消除无用的数据，也叫消除无用的笛卡尔积。
5.3.1.合并结果集:UNION 、 UNION ALL
- 作用：合并结果集就是把两个select语句的查询结果合并到一起！
- 合并结果集有两种方式：
- UNION：去除重复记录，
例如：SELECT* FROM t1 UNION SELECT * FROM t2；- UNION ALL：不去除重复记录，
例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。
- 注意：被合并的两个结果：列数、列类型必须相同。
5.3.2.连接查询
- 连接查询就是求出多个表的乘积(笛卡尔积)，例如t1连接t2，那么查询出的结果就是t1*t2。
- 连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1), (a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。 那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过 条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。
- 使用主外键关系做为条件来去除无用信息
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
emp.deptno是从表(多方)外键 dept.deptno是主表(一方)主键1.内连接查询:
- 什么时候使用内连接:
- 多表查询时出现无用的数据(笛卡尔积)，使用内连接通过主外键关系做条件消除无用数据
- 1.隐式内连接:使用where条件来消除无用的数据。
内连接语法:
select 列名
from 表1
inner join 表2
on 表1.列名=表2.列名 //外键列的关系
where.....
或者
select 列名
from 表1,表2
where 表1.列名=表2.列名 and ...(其他条件)
-- 查询所有员工信息和对应的部门信息
select * from emp,dept where emp.dept_id=dept.id;
-- 查询员工表的姓名，性别以及对应的部门表的名字
select emp.name,emp.genger,dept.name from emp,dept where emp.dept_id=dept.id;
-- 多表查询的简化，给表名起别名,下面是多表查询的标准格式，每个关键字占一行，方便加注释
select
t1.name, -- 员工表姓名
t1.genger,
t2.name
from
emp t1, -- 员工表
dept t2 -- 部门表
where
t1.dept_id=t2.id;
- 2.显示内连接:select 字段列表 from 表名1 inner join 表名2 on 条件
-- 查询所有员工信息和对应的部门信息
select * from emp inner join dept on emp.dept_id=dept.id;(inner可以省略)- 3.内连接查询:
- 1.从哪些表中查询数据
- 2.查询条件是什么
- 3.查询哪些字段
2.外连接查询
- 什么时候使用外连接:
- 比如下面这两张表emp员工表和dept部门表，员工1007分配的部分编号50，但部门表没有这个编号，如果通过内连接e.deptno=d.deptno1是查不出1007员工这条记录的，因为d.deptno1找不到值为50；这就需要使用外连接，select * from emp e left join dept d on e.deptno=d.deptno1; (左边emp是主表显示所有记录，右边是dept从表，能匹配到的就显示，匹配不到显示null)
- 1.左外连接:
1.语法:
select 字段列表 from 表1 left [outer] join 表2 on 条件 (outer可以省略不写)
左外连接查询的是左表所有数据以及其交集部分(两表交集)
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则也查询出来。
/*这里新添加一个实习生还没有分配部门编号(部门编号虽然是外键，但可以设置Null值，没有非空约束)
若以内连接(比如隐式内连接)查询，则实习生这个记录不会被查询出来，因为没有部门编号。
这种情况下就需要使用左外连接(因为左外连接可以显示左表的所有数据及其交集，这样左表中的实习生
记录虽然没有部门，但也可查询出来)：
*/
select t1.*,t2.name -- 左外连接
from emp t1
left join dept t2
on t1.dept_id=t2.id;
- 2.右外连接:
1.语法:
select 字段列表 from 表1 right [outer] join 表2 on 条件
右外连接查询的是右表所有数据以及其交集部分(两表交集)
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不查询该员工信息。
/*没有部门则不查询员工信息，使用右外连接可以实现，因为右外连接可以显示右表所有的数据及其交集
由于左右表交集的部分就是dept_id,由于左表实习生这条记录没有dept_id则无法显示该条记录
*/
select t1.*,t2.name from emp t1 right join dept t2 on t1.dept_id=t2.id;
一般只使用左外连接，因为左外连接中把两张表兑换位置就成右连接了。-
5.3.3.子查询
1.概念:查询中嵌套查询，称为子查询。
-- 查询工资最高的员工信息(分部)
select max(salary) from emp; -- 1.查询到最高工资为9000
select * from emp where emp.salary=9000; -- 2.查询到最高工资为9000的员工信息
-- 查询工资最高的员工信息(嵌套查询)
select * from emp where emp.salary=(select max(salary) from emp);
2.子查询不同情况:
1.子查询的结果是单行单列的:
子查询可以直接作为条件，使用运算符去判断。
-- 查询工资最高的员工信息(嵌套查询)
select * from emp where emp.salary=(select max(salary) from emp);
-- 查询员工工资小于平均工资的人
select * from emp where emp.aslary<(select avg(salary) from emp);
2.子查询的结果是多行单列的:
子查询可以直接作为条件，使用IN集合运算符判断:
-- 查询'财务部'和'市场部'所有员工的信息
select * from emp where dept_id in (
select id from dept where name='财务部' or name='市场部'
);
3.子查询的结果是多行多列的:
子查询可以作为一张虚拟表，来参与多表的查询。
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息，(嵌套查询实现)
-- 可以先查询出员工信息表，然后通过部门编号对员工信息表和部门信息表进行多表查询。
select * from dept t1,(select * from emp where emp.join_date>2011-11-11) t2,
where t1.id=t2.dept_id;
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息，(内连接实现不用嵌套查询更好理解)
select * from emp t1,dept t2,where t1.dept_id=t2.id and t1.join_date>201-11-11;
5.4.多表查询几种方式总结
1.多表查询的几种方式:
1.1.内连接:
- 隐式内连接:使用where条件来消除无用的数据。
- 显示内连接:from 表1 join 表2 on 条件
1.2.外连接:
- 左外连接:from 表1 left join 表2 on 条件; 左外连接查询的是左表所有数据以及其交集部分(两表交集)
- 右外连接:from 表1 left join 表2 on 条件; 右外连接查询的是右表所有数据以及其交集部分(两表交集)
1.3.子查询:
- 子查询的结果是单行单列的，子查询可以直接作为条件，使用运算符去判断。
- 子查询的结果是多行单列的，子查询可以直接作为条件，使用IN集合运算符判断。
- 子查询的结果是多行多列的，子查询可以作为一张虚拟表，来参与多表的查询。
2.SQL标准化，规范化写法:
- 1.建议给表起别名，使用比较方便。
- 2.每一个关键字以及每一个字段都要独占一行，并加入相应的注释。
3.内联接和外连接查询区别:
- 内连接查询，若有数据为Null时的记录是无法查询出来的，而这个时候就可以使用外连接，
- 哪个表有Null，就把这个表设置左表，另外一个表设为右表，进行左外连接查询，查询左表所有数据和交集数据。
- 这里所说的左表所有数据不是整个表数据，而是要查询的某些字段的所有数据。交集数据是指两个表交集的字段的数据
6.多表查询练习
练习1
-- 创建数据库db4
-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 职务表，职务名称，职务描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT , -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
需求：
- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位
- 3.查询员工姓名，工资，工资等级
- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
- 5.查询出部门编号、部门名称、部门位置、部门人数
- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
分析：
1.先分析这几张表的关系:
- emp员工表中编号id,员工名字ename,job_id是职位编号是外键对应着职位表job的主键，mgr是它的管理者编号，joindate是入职日期，salary是薪资，bonus是奖金，dept_id部门编号，也是一个外键对应着部门表的主键，
- 因为员工表和职位表之间是一对多的关系，即一个员工对应一个职位，一个职位对应多个员工；
- 同理员工表和部门表也是一对多的关系，即一个员工对应一个部门，一个部门对应多个员工。
- 因为这两对表都是一对多的关系，所以对一对多关系的多表实现只要通过外键分别关联着部门表和职位表的主键，这样当部门表中某部门被砍掉后，那么员工表中对应的员工也会自动被删除掉。另外还有一个薪资等级表。
2.关系模式(逻辑结构设计)
- emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id);
- dept(id,dname,loc);
- job(id,jname,descript);
- salarygrade(grade,losalary,hisalary);
3.答案
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/*
分析: 1.需要哪些表:emp表，job表
2.查询条件(找外键):emp.job_id=job.id
3.查询方式:最简单的可以直接使用内连接(隐式的)
下面SQL是最规范的写法。
*/
select
t1.id, -- 查询员工编号
t1.ename, -- 员工姓名
t1.salary,-- 工资
t2.jname, -- 职务名称
t2.description -- 职务描述
from
emp t1,dept t2
where
t1.job_id=t2.id;
-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
/*
分析:1.需要查询哪些表:emp表，job表，dept表。
2.查询条件(找外键):emp.job_id=job.id and emp.dept_id=dept.id;
3.查询方式:还是内连接方便。
*/
select
t1.id, -- 查询员工编号
t1.ename, -- 员工姓名
t1.salary,-- 工资
t2.jname, -- 职务名称
t2.description, -- 职务描述
t3.dname, -- 部门名称
t3.loc -- 部门位置
from
emp t1,job t2，dept t3
where
t1.job_id=t2.id and ti.dept_id=t3.id;
-- 3.查询员工姓名，工资，工资等级,以及工资所在的范围
/*
分析:1.需要查询哪些表:emp表，salarygrade表
2.查询条件:注意，这两张表并没有外键关联。不能使用等式，使用大于小于或者between..and..
emp.salary>=salarygrade.losalary and emp.salary<=salarygrade.hisalarygrade
或者emp.salary between salarygrade.losalary and salarygrade.hisalarygrade
*/
select
t1.ename,
t1.salary,
t2.*
from emp t1, salarygrade t2
where t1.salary between t2.losalary and t2.hisalary;
-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
分析:1.需要查询哪些表:emp表，job表，dept表，salarygrade表
2.查询条件;emp.job_id=job.id and emp.dept_id=dept.id
and emp.salary between salarygrade.losalary and salarygrade.hisalarygrade
*/
select
t1.ename, -- 员工姓名
t1.salary, -- 员工薪资
t2.jname, -- 职务名称
t2.description, -- 职务描述
t3.dname, -- 部门名称
t3.loc, -- 部门位置
t3.grade -- 工资等级
from
emp t1, job t2, dept t3, salarygrade t4
where
t1.job_id=t2.id -- 员工表和职位表关联
and t1.dept_id=t3.id -- 员工表和部门表关联
AND t1.salary BETWEEN t4.losalary AND t4.hisalary;-- 员工表和薪资等级表关联
-- 5.查询出部门编号、部门名称、部门位置、部门人数
/*
分析:1.需要查询的表:dept表，部门人数使用emp表，
2.使用分组查询。按照emp.dept_id完成分组，查询count(id).
(部门人数是通过emp表查询的，即单表查询各组人数，必然使用分组查询出各个分组人数)
3.使用子查询将第二步的查询结果集作为虚表和dept表进行关联查询。
使用了子查询的第三种情况。
*/
select
t1.id, -- 部门编号
t1.dname,-- 部门名称
t1.loc, -- 部门位置
t2.total -- 部门人数
from
dept t1,
(select
dept_id,count(id) total -- 给部门个数起别名
from
emp
group by dept_id
) t2 -- 子查询的结果集(每个部门编号以及对应的部门人数)作为虚表，和dept表关联查询
where
t1.id=t2.dept_id;
-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
/*
分析:首先解释下直接上级:在emp员工表中有id以及管理者编号mgr,那么mgr的编号就是本人的直接上
级，比如孙悟空id是1001，它mgr管理者编号是1004，那么孙悟空直接上级就是id为1004的唐僧。
1.需要查询的表:emp表，emp表的id和mgr是自关联的。可以给emp分别起别名t1,t2.
2.查询条件:emp.id=emp.mgr
3.为实现 没有领导的员工也需要查询，由于罗贯中最大，所以他没有mgr编号。
查询左表t1(ename,mgr)所有数据和交集数据。右表t2(id,ename),右表并没有要求id,方便看。
使用左连接查询。from 左表 left join 右表 on 条件
*/
-- 若直接内连接查询，则mgr管理者编号为Null的员工就找不到对应的id，则查询数据不全。
select
t1.ename,
t1.mgr,
t2.id,
t2.ename
from emp t1,emp t2
where t1.mgr=t2.id;
-- 需要使用左外连接，将含有Null数据的表作左表，进行左外连接查询左表所有数据和交集字段的数据。
select
t1.ename,
t1.mgr,
t2.id,
t2.ename
from emp t1
left join emp t2
on t1.mgr=t2.id;
练习2
某网上商城数据库如下图所示
综合练习-【多表查询】
- 1>查询用户的订单,没有订单的用户不显示
- 2>查询所有用户的订单详情
- 3>查询所有订单的用户详情
2 综合练习2-【子查询】
- 1>查看用户为张三的订单详情
- 2>查询出订单的价格大于300的所有用户信息。
- 3>查询订单价格大于300的订单信息及相关用户的信息。
综合练习3-【分页查询】
- 1>查询所有订单信息，每页显示5条数据
7.扩展
14.1.多行新增
insert into 表名(列名) values (列值),(列值),(列值)；14.2.多表更新
(1)update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件
(2)update 表1
inner join 表2 on 表1.列名=表2.列名
set 列名=列值
where 限定条件
示例：
update employee e,salary s
set title='助工',salary=1000
where e.empid=s.empid and name='李四'14.3.多表删除
语法：
delete 被删除数据的表 from 删除操作中使用的表
where 限定条件
注:多张表之间使用逗号间隔
示例：
//删除人事部的信息
delete d,e,s from department d,employee e,salary s
where d.depid=e.depid and s.empid=e.empid and depname='人事部'14.4.日期运算函数
now() 获得当前系统时间
year(日期值) 获得日期值中的年份
date_add(日期,interval 计算值 计算的字段);
注：计算值大于0表示往后推日期，小于0表示往前推日期
示例：
date_add(now(),interval -40 year);//40年前的日期
MySQL基本使用及单表的增删改查语句
https://blog.csdn.net/qq_40454863/article/details/113809607
常用SQL语句练习整理
https://blog.csdn.net/qq_40454863/article/details/113925975
MySQL高级使用--事务详解
https://blog.csdn.net/qq_40454863/article/details/113899837
Java操作数据库详解--JDBC
扫一扫
3144
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
