1, 约束
为了使实验变得轻松，这里将db1数据库里的stu表数据进行一定的修改重新保存为stu2
效果如下：
通过观察，我们可以看出有几个问题：
1.id不应该有重复，而且还存在空值。
2.第一行的数值中math为-51，不合理
3.第四行数据中age为9000，不合理
对此产生问题的原因是因为我们在设计表的时候没有对表进行限制，也就是说约束，所以会产生这样的情况。
1.1 约束的概念
- 约束就是作用于表中的列上，限制列里的数据的语法。
例如上图中，我们应该约束id列的值不能为null且不能重复
- 约束的意义就是要保证数据的合理性，有效性。
添加约束可以使我们在添入数据的时候就把无效数据限制走，无法添加，例如上面的9000的age，-51的math还有空值的id。
1.2 约束的分类
1.2.1 非空约束
- 非空约束： 关键字是NOT NULL
这个约束是保证我们添加的数据不能为空的
例如如果上方表中id添加了这个约束，那么第二行数据我们是无法添加的，是无效的。
一般作用于必须要添加的数据
代码如下：
-- 创建表时添加非空约束CREATE TABLE 表名(列名 数据类型 NOT NULL,…);
然后对约束的修改也要遵循以下语法:
-- 建完表后添加非空约束ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;-- 删除约束ALTER TABLE 表名 MODIFY 字段名 数据类型;
1.2.2 唯一约束
- 唯一约束：关键字是UNIQUE
这个约束是保证我们添加的数据是唯一的，不能相同的
例如上方表格中我们在id列我们添加这个约束，那么之后的第三行和第四行的数据就无法添加，因为他们的id和第一行的id是相同的。
一般这个约束作用于号码一类的
代码如下：
-- 创建表时添加唯一约束CREATE TABLE 表名(列名 数据类型 UNIQUE [AUTO_INCREMENT],-- AUTO_INCREMENT: 当不指定值时自动增长,默认从1开始增长…);CREATE TABLE 表名(列名 数据类型,…[CONSTRAINT] [约束名称] UNIQUE(列名));
建完表之后修改约束:
-- 建完表后添加唯一约束ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;-- 删除约束ALTER TABLE 表名 DROP INDEX 字段名;
1.2.3 主键约束
- 主键约束：关键字是PRIMARY KEY
这个约束是让这一列的数据成为唯一标识，就是指此列数据必须非空且唯一
例如上表中id就可以只用一个这个主键约束，将id设置为主键，可以来识别每一条数据，因此不能为空，而且不能相同
一般我们在表中都会寻找一列来作为主键列，用它来当唯一标识。
代码如下:
-- 创建表时添加主键约束CREATE TABLE 表名(列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],…);CREATE TABLE 表名(列名 数据类型,[CONSTRAINT] [约束名称] PRIMARY KEY(列名));
建完表后修改约束的代码如下:
-- 建完表后添加主键约束ALTER TABLE 表名 ADD PRIMARY KEY(字段名);-- 删除约束ALTER TABLE 表名 DROP PRIMARY KEY;
1.2.4 默认约束
- 默认约束：关键字是DEFAULT
这个约束是让我们在保存一条数据时，若那一列没有添加数值，那么会默认其为0，当然默认值也可以自己设置，若没有设置，则为0
例如上方柳青的english数据为null，若添加了这个约束，那么我们在保存数据的时候，此条数据会被保存为0
代码如下:
-- 创建表时添加默认约束CREATE TABLE 表名(列名 数据类型 DEFAULT 默认值,…);
建完表后修改约束的代码如下:
-- 建完表后添加默认约束ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;-- 删除约束ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
1.2.5 检查约束
- 检查约束：关键字是CHECK
这个约束是让我们保证这条数据中的值是在某个条件中
若我们设置了此约束，我们加入的那条数据中不满足条件时，则会失效
检查约束在MySQL中不支持，但是可以写出来，不会报错。如果想要限制，之后可以在java代码中实现限制，性值是相同的。
因为用不到,所以就一笔带过
1.2.6 外键约束
- 概述：
外键：用来连接两个表，使之产生联系。
- 关键字:FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
代码如下:
-- 创建表时添加外键约束CREATE TABLE 表名(列名 数据类型,…[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名));
建完表之后对约束进行修改：
-- 建完表后添加外键约束ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);-- 删除外键约束ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
1.3 约束的练习
上文都介绍了各个约束的区别,在这里我们将一个个测试各个约束的作用.
为了使实验简单,这里我们先建立一个emp表,代码如下:
-- 员工表create table emp (id int, -- 员工id，主键且自增长ename varchar(50), -- 员工姓名，非空且唯一joindate date, -- 入职日期，非空salary double(7,2), -- 工资，非空bonus double(7,2) -- 奖金，如果没有将近默认为0);
我们初始建立的表格是没有添加约束.意味着可以任意的插入数据,可以自己随意尝试添加任意数据.
接下来我们开始测试约束,开始修改表的结构.代码如下:
drop table emp if exists;-- 员工表create table emp (id int primary key, -- 员工id，主键且自增长ename varchar(50) not null unique, -- 员工姓名，非空且唯一joindate date not null, -- 入职日期，非空salary double(7,2) not null, -- 工资，非空bonus double(7,2) default 0-- 奖金，如果没有将近默认为0);
因为之前建立了一个无约束的emp表，所以此处要先删除。
表创立好了之后，现在开始来测试表的约束是否发生了作用。
首先我们添加一个没有问题的数据，代码如下:
insert into emp values(1,'郭嘉俊','2022-04-07',10000,5000);
效果图：
1.3.1 验证非空约束
代码如下：
insert into emp values(2,'甘祖传','2021-03-22',null,0);
效果图：
由此可见，非空约束验证成功，在执行代码的时候报错，提示salary列的数值不能为null
1.3.2 验证唯一约束
代码如下：
insert into emp values(3,'郭嘉俊','2022-11-14',8000,5000);
效果图：
由此可见，唯一约束也验证成功，在执行代码的时候会报错，因为之前出现过郭嘉俊的数据，但是ename列的数据是唯一约束，不能有相同的。
1.3.3 验证主键约束（非空且唯一）
验证非空代码如下：
insert into emp values(null,'甘祖传','2021-03-14',12000,500);
效果图：
由此可见，主键约束列的数据不能为空
验证唯一的代码如下:
insert into emp values(1,'甘祖传','2022-11-14',8000,5000);
效果图：
通过对比，我们添加一个正确的代码即可看出来，代码如下:
insert into emp values(2,'甘祖传','2022-11-14',8000,5000);
效果图：
添加成功，此时总共添加了两条正确的数据，所以表中应该有两条数据：
1.3.4 验证默认约束
代码如下:
insert into emp(id,ename,joindate,salary) values(3,'黄志鹏','2019-11-24',11000);
效果图：
tips：虽然这里bonus的默认值是0，但是如果不写他的值的话，使用默认值，在前边的表后要写详细的列表名称，同时不写bonus的列名称，否则会报错：
还有一种情况，当我们给了默认约束的列数据为null时，此时数值为null不是为默认值,代码如下:
insert into emp values(3,'黄志鹏','2019-11-24',11000,null);
效果图：
1.3.5 验证自动增长：auto_increment 当列是数字类型 并且有唯一约束
代码如下：
这里为了实验方便需要建立一个emp2表格，代码如下：
CREATE TABLE emp2 (id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一joindate DATE NOT NULL , -- 入职日期，非空salary DOUBLE(7,2) NOT NULL , -- 工资，非空bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0);
添加数据的代码:
insert into emp2(ename,joindate,salary,bonus) values('郭一','2000-10-10',1000,200);insert into emp2(id,ename,joindate,salary,bonus) values(null,'郭二','2005-03-19',1000,200);insert into emp2(id,ename,joindate,salary,bonus) values(null,'郭三','2020-08-24',1000,200);
效果图:
不给id添加数值：（会自动增加）
给id列添加数值：（也会自动增长）
1.3.6 验证外键约束
建立一个dept部门表格,代码如下:
-- 部门表的建立create table if not exists dept(id int primary key auto_increment, -- 部门id，用主键约束，且自增dep_name varchar(25), -- 部门名称addr varchar(25) -- 部门所在地);
为了实验方便，在这时候我们先将之前的emp表都删除，重新建立一个emp表格，建立一个外键约束,把emp表外键约束dept表的id列.代码如下：
-- 删除之前的员工表drop table if exists emp;-- 员工表的建立create table if not exists emp(id int primary key auto_increment, -- 员工id,自动增加主键约束name varchar(20), -- 员工名称age int, -- 员工年龄dept_id int, -- 员工部门号id-- 建立一个外键约束,把emp表外键约束dept表的id列.constraint fk_emp_dept foreign key(dept_id) references dept(id));
效果图:
emp中的dep_id是外键
dept是主表(被引用的是主表,相当于你抄别人的东西,这个东西是别人的)
接下来添加数据进行测试,代码如下:
-- 添加两个部门insert into dept(dep_name,addr) values('宣传部','上海'),('销售部','杭州');-- 添加几个员工insert into emp(name,age,dept_id) values('郭嘉俊',19,1),('黄志鹏',22,1),('甘祖传',31,1),('甘昀弘',19,2),('肖富成',20,2),('熊凯龙',15,2);
效果图:
emp表:
dept表(主表):
现在开始测试外键约束是否生效,
这里我们删除主表中的1 宣传部 上海 这条数据,代码如下:
delete from dept where id=1;
效果图:
由图可以看出,dept被外键约束,此时删除不了数据,因为id这一列被外键约束约束了,emp表中有dept id=1 这条数据的数据
与之对比,我们添加一个数据是id=3的随便一个部门,然后删除,代码如下:
insert into dept(dep_name,addr) values('研发部','北京');
效果图:
然后我们对此进行删除,代码如下:
delete from dept where id=3;
效果图:
没有报错,说明关联的表中如果没有使用到被外键约束的表中的数据,那么是可以删除的
拓展内容:
我们默认的外键约束策略是:RESTRICT:当删除父表(主表)中的某条记录时,如果该记录在从表中有关联的记录,则删除会失败,要正确删除的话,得先去从表中删除关联的记录,然后才能删除掉主表的记录.
还有其他的删除策略:
on DELETE CASCADE:级联删除,即删除主表数据时,会把对应的从表数据一同删除掉.
on DELETE SET NULL:当删除主表数据的时候,不会删除掉从表中的数据,但是会把对应的外键列值设为null.
测试一下:
级联删除策略测试代码如下:
-- 删除之前的表格drop table if exists emp;drop table if exists dept;-- 部门表的建立create table if not exists dept(id int primary key auto_increment, -- 部门id，用主键约束，且自增dep_name varchar(25), -- 部门名称addr varchar(25) -- 部门所在地);-- 员工表的建立create table if not exists emp(id int primary key auto_increment, -- 员工id,自动增加主键约束name varchar(20), -- 员工名称age int, -- 员工年龄dept_id int, -- 员工部门号id-- 建立一个外键约束,把emp表外键约束dept表的id列.constraint fk_emp_dept foreign key(dept_id) references dept(id) on delete cascade);-- 添加两个部门insert into dept(dep_name,addr) values('宣传部','上海'),('销售部','杭州');-- 添加几个员工insert into emp(name,age,dept_id) values('郭嘉俊',19,1),('黄志鹏',22,1),('甘祖传',31,1),('甘昀弘',19,2),('肖富成',20,2),('熊凯龙',15,2);-- 在有外键约束的情况下删除主表的数据delete from dept where id=1;
效果图:
删除成功,没有了id为1的数据
而且对应的从表中的数据也被删除了
on DELETE SET NULL策略测试代码如下:
-- 删除之前的表格drop table if exists emp;drop table if exists dept;-- 部门表的建立create table if not exists dept(id int primary key auto_increment, -- 部门id，用主键约束，且自增dep_name varchar(25), -- 部门名称addr varchar(25) -- 部门所在地);-- 员工表的建立create table if not exists emp(id int primary key auto_increment, -- 员工id,自动增加主键约束name varchar(20), -- 员工名称age int, -- 员工年龄dept_id int, -- 员工部门号id-- 建立一个外键约束,把emp表外键约束dept表的id列.constraint fk_emp_dept foreign key(dept_id) references dept(id) on delete set null);-- 添加两个部门insert into dept(dep_name,addr) values('宣传部','上海'),('销售部','杭州');-- 添加几个员工insert into emp(name,age,dept_id) values('郭嘉俊',19,1),('黄志鹏',22,1),('甘祖传',31,1),('甘昀弘',19,2),('肖富成',20,2),('熊凯龙',15,2);-- 在有外键约束的情况下删除主表的数据delete from dept where id=1;
效果图:
主表中的数据删除成功
由图可以看出,当主表中id为1的数据删除的时候,从表中外键约束的列中与之对应的值变为null,整条数据并没有被删除.
此处再练习上删除和重新添加外键约束:
删除约束代码如下:
alter table emp drop foreign key fk_emp_dept;
效果图:
重新添加外键约束的代码如下:
alter table emp add constraint fk_emp_dept foreign key(dept_id) references dept(id);
效果图:
2, 数据库设计
- 数据库设计概念
- 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
- 建立数据库中的表结构以及表与表之间的关联关系的过程。
- 有哪些表？表里有哪些字段？表和表之间有什么关系？
数据库设计的步骤
2.1.1 表关系（一对一）
- 一对一：
例子：用户 和 用户详情
一对一的关系经常用来进行表的拆分，把经常使用的字段放在一个表中，剩下的放在另一个表中
- 如何实现：
在两表的任意一表中加入外键，关联另一个表的主键，并且还要将外键设置为唯一（unique）。
2.1.2 表关系（一对多）
- 一对多：
例子：手机牌子 和 手机
一个手机只能属于一个手机牌子，但是一个手机牌子却可以有很多手机
- 如何实现：
我们在多的那一方加入外键，少的为主表，并且要将外键指向主表的主键
- 例子：
以之前的员工表和部门表举例：
这里边一个部门有多个员工，但是一个员工只能属于一个部门，由此多的一方是员工表，要在员工表中建立外键，这里建议添加一列（dept_id），指向部门表的主键（id）。
tips：建立外键的时候，两个列的数据属性一定要相同
代码如下：
-- 删除之前的表delete table if exists emp;delete table if exists dept;-- 建立新的表格，并加入外键create table emp(id int primary key auto_increment,name varchar(25),age int,-- 添加外键需要的列,不能设置为唯一(unique)，这样子会变成一对一的关系dept_id int,constraint fk_emp_dept foreign key(dept_id) references tmp(id));create table dept(id int primary key auto_increment,name varchar(25),addr varchar(25));
效果图：
2.1.3 表对表（多对多）
- 多对多
例子: 商品 和 订单
一种商品可以有多个订单，一个订单也可以包含多种商品
- 实现方法：
在建立一个表，作为中间表，这个表至少包含两个外键，分别关联其他两个表。
- 案例：
- 我们以
订单表和商品表举例：
经过分析发现，订单表和商品表都属于多的一方，此时需要创建一个中间表，在中间表中添加订单表的外键和商品表的外键，分别指向两张表的主键：
代码如下：
-- 删除之前的表格drop table if exists `order`;drop table if exists goods;-- 创建原表格create table `order`(id int primary key auto_increment,payment double(7,2),type varchar(10),status varchar(10));create table goods(id int primary key auto_increment,title varchar(10),price double(7,2));-- 创建中间表create table order_goods(id int,order_id int,goods_id int);-- 使用第二种方法，在外添加外键-- ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);alter table order_goods add constraint fk1 foreign key (order_id) references `order`(id);alter table order_goods add constraint fk2 foreign key (goods_id) references goods(id);
效果图:
2.2 数据库设计案例
题目：根据下图设计表和表之间的关系
：
经过分析，我们分为 专辑表 曲目表 短评表 用户表 4张表。
一个专辑可以有多个曲目，一个曲目只能属于某一张专辑，所以专辑表和曲目表的关系是一对多。
一个专辑可以被多个用户评论，一个用户可以对多个专辑进行评论，所以专辑表和用户表的关系是 多对多。
一个用户可以发多个短评，一个短评只能是某一个人发的，所以用户表和短评表的关系是 一对多。
create table if not exists Music(title varchar(32),alias varchar(32),image varchar(64),style varchar(8),type varchar(4),medium varchar(4),publish_time date,publisher varchar(16),number tinyint,barcode bigint,summary varchar(1024),artist varchar(64),id int unique);
2)创建曲目表Song,其中有外键关联到Music上
代码如下:
create table if not exists Song(name varchar(32),serial_number tinyint,id int unique,Music_id int,constraint fk_Song_Music foreign key(Music_id) references Music(id));
3) 创建用户表User:
代码如下:
create table if not exists User(username varchar(16) unique,image varchar(64),singature varchar(64),nickname varchar(16),id int primary key);
4)创建评论表,包含两个一对多的外键,评论表皆是外键.
代码如下:
create table if not exists Review(content varchar(256),rating tinyint,review_time datetime,Music_id int,User_id int,-- 两个外键，一个专辑，一个用户constraint fk_Review_Music foreign key(Music_id) references Music(id),constraint fk_Review_User foreign key(User_id) references User(id));
5) 创建用户和专辑的中间表Music_User,包含两个外键,分别关联Music表和User表.
代码如下:
create table if not exists Music_User(id int primary key,Music_id int,User_id int,constraint fk_music foreign key(Music_id) references Music(id),constraint fk_user foreign key(User_id) references User(id));
效果图:
3, 多表查询
多表查询指的就是我们要查询的数据是2个表上的,我们通过一次查询就可以得到.
为了使实验变得方便,把之前的emp和dept表格删除重做,代码如下:
DROP TABLE IF EXISTS emp;DROP TABLE IF EXISTS dept;# 创建部门表CREATE TABLE dept(did INT PRIMARY KEY AUTO_INCREMENT,dname VARCHAR(20));# 创建员工表CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),gender CHAR(1), -- 性别salary DOUBLE, -- 工资join_date DATE, -- 入职日期dep_id INT,FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键));-- 添加部门数据INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');-- 添加员工数据INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES('孙悟空','男',7200,'2013-02-24',1),('猪八戒','男',3600,'2010-12-02',2),('唐僧','男',9000,'2008-08-08',2),('白骨精','女',5000,'2015-10-07',3),('蜘蛛精','女',4500,'2011-03-14',1),('小白龙','男',2500,'2011-02-14',null);
多表查询的代码很简单,就是想查询哪些表格就在后边加 , 然后再见加表格名字就好,当然这个地方可以在后边给表格起别名,在前边直接用别名.
tips: 一般使用别名的情况: 1): 子查询的结果是多行多列的时候,要把子查询当作一个新的表,然后起别名 2): 当连接两个表中有相同的字段的时候,而且要查询这个同名的字段,一定要取别名
查询代码如下:
select * from emp,dept;select * from dept,emp;
两表对比可以看出来,哪个表在前边,哪个表的数据就先查询.
而且我们也看出了一些问题,这样子查询出来的数目是两表的笛卡尔积
tips: dept表的数据条数*emp表的数据条数,是这样的.
如果我们要只查询员工的信息,比如孙悟空就是只属于1研发部的,那么234部门就不会出现,我们只需要在后边加上限定就可以了,代码如下:
select * from dept,emp where dep_id = did;
效果图:
因为小白龙没有部门号,所以没相等的,被排除了.
像这种我们之前学习的叫做连接查询,多表查询是指以下几种:
连接查询
- 内连接查询 ：相当于查询AB交集数据
- 外连接查询
- 左外连接查询 ：相当于查询A表所有数据和交集部分数据
- 右外连接查询 ：相当于查询B表所有数据和交集部分数据
- 子查询
3.1 内连接查询
内连接查询 ：相当于查询AB交集数据
语法
-- 隐式内连接select 字段名称 from 表1,表2... where 条件;-- 显式内连接select 字段名称 from 表1[inner] join 表2 on 条件;
案例
隐式内连接之前已经测试过了,就不用测试了,
显示内连接:
代码如下:
select * from emp inner join dept on dep_id = did;
效果图:
和之前是一样的查询结果.
3.2 外连接查询
语法
-- 左外连接:相当于查询A表所有数据和交集部分数据select 字段名称 from 表1 left [outer] join 表2 on 条件;-- 右外连接:相当于查询B表所有数据和交集部分数据select 字段名称 from 表1 right [outer] join 表2 on 条件;
案例
查询emp表所有数据和对应的部门信息
代码如下:
-- 左外连接select * from emp left join dept on dep_id = did;-- 右外连接select * from dept right join emp on dep_id = did;
效果图:
左外连接:
左外连接此处是emp的全部信息和交集,因为小白龙的dept_id为null 所以没有相同的,因此只有emp的数据.
右外连接:
右外连接此处是dept的全部信息和交集,因为小白龙的dept_id为null 所以没有相同的,因此只有dept的数据.
当然把代码中的表换位置 也行,不过查询结果是按写的顺序来的,比如第一个是emp,则先显示emp的列,再显示dept的列,反过来也是一个道理.
3.3 子查询
查询中有时候 条件不是具体的值,比如说要查询大于猪八戒的薪水的这个条件,如果不知道具体值的时候,可以嵌套一个查询(一个查询猪八戒的薪水的查询),这个查询就叫做子查询,子查询通常嵌套在WHERE和FROM语句中.
根据返回值的结果,我们将子查询分为三种,分别是以下三种:
3.3.1 单行单列子查询
当返回值是当行单列的时候,一定要在where子句中,且主查询(外层查询)只能用单行操作符: > < = >= <= <>
语法:
select 字段列表 from 表 where 字段 单行操作符(子查询);
例子:
- 查询工资高于八戒的员工信息
第一步:查询八戒的工资(3600),代码如下:
select salary from emp where name='猪八戒';
第二步:查询工资大于3600的员工信息
select * from emp where salary>3600;
然后用单行单列查询来写,就可以直接一步到位,代码如下:
select * from emp where salary>(select salary from emp where name='猪八戒');
效果图:
3.3.2 多行单列子查询
查询结果只有一列,但是有多行的时候,结果就是一组值,所以主查询(外查询)只能用多行操作符: in all any .
all和any要跟在”>”和”<”的后面.
in的案例:
- 查询’财务部’和’市场部’的所有员工信息
代码如下:
-- 第一步,查询出财务部和市场部的部门编号(2,3)select did from dept where dname='财务部' or dname='市场部';-- 第二部,查询出部门编号为2,3的所有员工信息select * from emp where dep_id in(2,3);
效果图:
使用多行单列来查询,代码如下:
select * from emp where dep_id in(select did from dept where dname='财务部' or dname='市场部');
效果图:
all的案例:
- 查询比2号部门所有员工工资都高的员工信息(说明要比最高的高)
代码如下:(之后为了方便,就省略一开始的,直接写最后的代码)
select * from emp where salary >all(select salary from emp where dept_id=2);
效果图:
因为最高工资是9000,没有比9000高的,因此都为null,如果查询比1号部门所有工资都高的员工信息,那么会有,因为1号工资最高的只有7200
代码如下:
select * from emp where salary >all(select salary from emp where dep_id=1);
效果图:
any的案例: 查找比1号部门任何员工工资低的员工信息(这里说明小于1号部门最高的工资)
代码如下:
select * from emp where salary <any(select salary from emp where dep_id=1);
效果图:
因为部门1的最高工资是7200,部门中有个4500,所以这个4500也被查询了出来
3.3.3 多行多列子查询
当子查询的结果是多行多列 的时候,就是一个新的表的时候,子查询作为一个虚拟表,在from之后,并且必须起别名
案例:
- 查询入职日期是’2011-11-11’之后的员工编号,姓名,入职日期和部门名称
代码如下:
-- 查询入职日期是'2011-11-11'之后的员工编号,姓名,入职日期和部门名称-- 第一步，在emp表中查询入职日期大于2011-11-11的编号，姓名，入职日期，部门编号select id,name,join_date,dep_id from emp where join_date>'2011-11-11';-- 第二步，把上方查询的结果变成一个新表，命名为t1，在其中查询dep_id的全部值select t1.dep_id from (select id,name,join_date,dep_id from emp where join_date>'2011-11-11')t1-- 第三步，查询部门号为1，3的员工编号,姓名,入职日期和部门名称select t1.id,t1.name,t1.join_date,dept.dname from (select id,name,join_date,dep_id from emp where join_date>'2011-11-11')t1,dept where dept.did = t1.dep_id;
效果图:
3.4 练习题
练习题的环境配置代码如下:
DROP TABLE IF EXISTS emp;DROP TABLE IF EXISTS dept;DROP TABLE IF EXISTS job;DROP TABLE IF EXISTS salarygrade;-- 部门表CREATE TABLE dept (did INT PRIMARY KEY, -- 部门iddname VARCHAR(50), -- 部门名称loc VARCHAR(50) -- 部门所在地);-- 职务表，职务名称，职务描述CREATE TABLE job (id INT PRIMARY KEY,jname VARCHAR(20),description VARCHAR(50));-- 员工表CREATE TABLE emp (id INT PRIMARY KEY, -- 员工idename VARCHAR(50), -- 员工姓名job_id INT, -- 职务idmgr 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 (did));-- 工资等级表CREATE TABLE salarygrade (grade INT PRIMARY KEY, -- 级别losalary INT, -- 最低工资hisalary INT -- 最高工资);-- 添加4个部门INSERT INTO dept(did,dname,loc) VALUES(10,'教研部','北京'),(20,'学工部','上海'),(30,'销售部','广州'),(40,'财务部','深圳');-- 添加4个职务INSERT INTO job (id, jname, description) VALUES(1, '董事长', '管理整个公司，接单'),(2, '经理', '管理部门员工'),(3, '销售员', '向客人推销产品'),(4, '文员', '使用办公软件');-- 添加员工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);-- 添加5个工资等级INSERT INTO salarygrade(grade,losalary,hisalary) VALUES(1,7000,12000),(2,12010,14000),(3,14010,20000),(4,20010,30000),(5,30010,99990);
1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
代码如下:
-- 分清楚每个数据在什么表中,-- 1.员工编号,员工姓名,工资 在emp员工表中-- 2.职务名称,职务描述 在job职务表中-- 职务表和员工表是多对一的关系,因此直接用连接查询即可-- 隐式内连接查询select emp.id,emp.ename,emp.salary,job.jname,job.description from emp,job where job.id = emp.job_id;--显式内连接查询select emp.id,emp.ename,emp.salary,job.jname,job.description from emp join job on job.id = emp.job_id;
效果图:
2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
代码如下:
-- 员工编号,员工姓名,工资 在emp员工表中-- 职务名称,职务描述 在job职务表中-- 部门名称，部门位置 在dept部门表中select emp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.locfrom emp,job,deptwhere emp.dept_id = dept.did and emp.job_id = job.id;
效果图:
3.查询员工姓名，工资，工资等级
代码如下:
-- 员工姓名,工资在emp员工表-- 工资等级在salarygrade工资等级表中select emp.ename,emp.salary,salarygrade.gradefrom emp,salarygradewhere emp.salary between salarygrade.losalary and salarygrade.hisalary;
效果图:
4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
代码如下:
-- 员工姓名,工资 在emp员工表中-- 职务名称,职务描述 在job职务表中-- 部门名称，部门位置 在dept部门表中-- 工资等级 在salarygrade工资等级表中select emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc,salarygrade.gradefrom empjoin job on emp.job_id=job.idjoin job on emp.dept_id=dept.idjoin job on salarygrade.job_id=salarygrade.id;
效果图:
第五题:查询出部门编号、部门名称、部门位置、部门人数
代码如下:
-- 部门编号,部门名称,部门位置在部门表-- 部门人数得统计,使用count计数-- 第一步，查出每个部门有多少人select dept_id 部门号,count(*) 人数 from emp group by dept_id;-- 第二步，汇总select dept.did,dept.dname,dept.loc,t1.countfrom dept,(select dept_id,count(*) count from emp group by dept_id)t1where dept.did = t1.dept_id;
效果图:
若有收获，就点个赞吧
0 人点赞
