数据库笔记——day04

G-Y99

已于 2022-04-12 21:57:57 修改

阅读量594

点赞数

分类专栏： MySQL 文章标签：后端 mysql

于 2022-04-11 21:34:17 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_65047139/article/details/124105612

版权

MySQL 专栏收录该内容

6 篇文章

订阅专栏

这篇博客回顾了数据库字段约束，包括默认约束、检查约束和外键约束，并详细介绍了多表联查的三种方式：笛卡尔积、连接查询（内连接、左连接、右连接）和子查询。提供了多个实战练习，如查询特定部门员工信息、部门详情、总监部门等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、巩固练习

#练习1：按照多个条件排序,ASC升序，desc降序
SELECT * FROM emp ORDER BY sal DESC,ename DESC;
SELECT * FROM emp ORDER BY deptno DESC,ename ASC;

#练习2：查询每个岗位的员工人数
SELECT COUNT(1),job FROM emp 
GROUP BY job 
#什么时候需要分组？查询是出现了混合列
#按照啥分组？按照非聚合列分组

#练习3：统计每个部门包含re的部门编号
SELECT deptno FROM dept 
WHERE dname LIKE 're%' #以re开始的
GROUP BY loc; #合理的分组

#练习4：统计每年入职的员工的最高薪资，只要>5000的
SELECT YEAR(hiredate),MAX(sal) a from emp 
GROUP BY YEAR(hiredate)  #分组
HAVING a>5000; #分组后过滤
#having里可用聚合函数where不能

#练习5：查询所有员工的最高薪-用分页
SELECT MAX(sal) FROM emp;
SELECT sal FROM emp ORDER BY sal DESC LIMIT 1

#创建user表
#创建user表(id,name,pwd)，并插入数据
CREATE TABLE USER(
id INT PRIMARY KEY auto_increment, #主键自增
name VARCHAR(20),
pwd VARCHAR(10) 
);

INSERT into USER VALUES(null,'jack','123'),
INSERT into USER VALUES(null,'rose','456');
#对上面SQL中使用的事务进行优化，因为上面的SQL需要两个事务的开启和关闭
#下面这种SQL也叫做批量插入的功能，高效，多次插入使用同一个事务
INSERT into USER VALUES
(null,'tony','123'),
(null,'jerry','456');

二、字段约束

1.默认约束 default

哪个字段添加了默认约束，从此字段值的就有了默认值

#字段约束
#默认约束，使用default
CREATE table a(
id INT PRIMARY KEY auto_increment,
sex VARCHAR(10) DEFAULT'男' #默认约束
)

2.检查约束 check

哪个字段添加了检查约束，从此，字段值必须符合检查的条件才可以

#检查约束：使用check
CREATE TABLE b(
id INT PRIMARY KEY auto_increment,
age int,
CHECK(age<200 AND age>0) #检查约束
#了解，数据要满足检查条件才可以
)

3.外键约束 foreign key

使用明确的一段代码表示，两个表之间的关系

#外键约束：
#主表
CREATE TABLE tb_user(
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
age INT,
sex CHAR(10) DEFAULT'男' #默认约束
);
#子表
CREATE TABLE tb_user_address(
user_id INT PRIMARY KEY,
address VARCHAR(200),
#创建外键
#foreign key(当前表的主键名) references 对方表(对方的主键)
FOREIGN KEY(user_id) REFERENCES tb_user(id)
);

#2.使用外键
#约束的情况1：子表的主键值 必须 取自主表的主键值
#约束的情况2：主表的记录，没有被子表使用，才可以删

其他三个字段约束：主键约束、非空约束、唯一约束

三、多表联查

1.准备数据

#多表联查
#1.课程表
CREATE TABLE courses( 
cno VARCHAR(5) NOT NULL, 
cname VARCHAR(10) NOT NULL,
tno VARCHAR(3) NOT NULL, 
PRIMARY KEY (cno) #单独设置主键
);

#2.得分表
CREATE TABLE scores(
sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL, 
degree NUMERIC(10,1) NOT NULL,
PRIMARY KEY (sno, cno) #联合主键,了解
);

#3.学生表
CREATE TABLE students( 
sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME, 
class VARCHAR(5), 
PRIMARY KEY (sno)#主键
);

#4.老师表
CREATE TABLE teachers( 
tno VARCHAR(3) NOT NULL,
tname VARCHAR(4),
tsex VARCHAR(2),
tbirthday DATETIME,
prof VARCHAR(6),
depart VARCHAR(10),
PRIMARY KEY (tno)
);

#5.插入数据
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES 
(108 ,'曾华' ,'男' ,'1977-09-01',95033),
(105 ,'匡明' ,'男' ,'1975-10-02',95031),
(107 ,'王丽' ,'女' ,'1976-01-23',95033),
(101 ,'李军' ,'男' ,'1976-02-20',95033),
(109 ,'王芳' ,'女' ,'1975-02-10',95031),
(103 ,'陆君' ,'男' ,'1974-06-03',95031);
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES 
(804,'易天','男','1958-12-02','副教授','计算机系'),
(856,'王旭','男','1969-03-12','讲师','电子工程系'),
(825,'李萍','女','1972-05-05','助教','计算机系'),
(831,'陈冰','女','1977-08-14','助教','电子工程系');


INSERT INTO COURSES(CNO,CNAME,TNO)VALUES 
('3-105' ,'计算机导论',825),
('3-245' ,'操作系统' ,804),
('6-166' ,'模拟电路' ,856),
('6-106' ,'概率论' ,831),
('9-888' ,'高等数学' ,831);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES 
(103,'3-245',86),
(105,'3-245',75),
(109,'3-245',68),
(103,'3-105',92),
(105,'3-105',88),
(109,'3-105',76),
(101,'3-105',64),
(107,'3-105',91),
(108,'3-105',78),
(101,'6-166',85),
(107,'6-106',79),
(108,'6-166',81);

2.三种方式

方式一：笛卡尔积

#多表联查，三种方式：
#方式1：笛卡尔积，用逗号隔开多张表
SELECT * FROM dept,emp; #产生了大量的结果集
SELECT * FROM dept,emp #逗号隔开表
#描述两张表的关系   表名.字段名
WHERE dept.deptno = emp.deptno 
#练习1：查询部门名称和员工表的所有数据
SELECT emp.*,dept.dname FROM dept,emp
WHERE dept.deptno = emp.deptno; #表关系

#练习2：查询部门的所有和员工的名字，但是只要部门名称叫accounting的
SELECT dept.*,emp.ename FROM dept,emp #逗号隔开表名
WHERE dept.deptno = emp.deptno #表关系
AND dept.dname='accounting'; #业务条件

#练习3：查询所有部门和员工的数据，条件是部门编号>1的
SELECT * FROM dept,emp 
WHERE dept.deptno = emp.deptno #表关系
AND dept.deptno>1; #业务条件

#练习4：查询易天老师能讲的课程名称
SELECT courses.cname FROM teachers,courses
WHERE teachers.tno = courses.tno #表关系
AND teachers.tname='易天'; #业务条件

#练习5：查询计算机导论课程的总分
SELECT SUM(degree) FROM courses,scores
WHERE courses.cno = scores.cno #表关系
AND courses.cname = '计算机导论' #业务关系

方式二：连接查询，用join连接多张表

#方式2：连接查询，用join连接多张表
SELECT * FROM dept JOIN emp  #产生了大量的结果集

SELECT * FROM dept JOIN emp
ON dept.deptno = emp.deptno #表关系

#练习1：查询部门名称和员工表的所有数据
SELECT emp.*,dept.dname FROM dept JOIN emp
ON dept.deptno = emp.deptno; #表关系

#练习2：查询部门的所有和员工的名字，但是只要部门名称叫accounting的
SELECT dept.*,emp.ename FROM dept JOIN emp 
ON dept.deptno = emp.deptno #表关系
WHERE dept.dname='accounting'; #业务条件

#练习3：查询所有部门和员工的数据，条件是部门编号>1的
SELECT * FROM dept JOIN emp 
ON dept.deptno = emp.deptno #表关系
WHERE dept.deptno>1; #业务条件

#练习4：查询易天老师能讲的课程名称
SELECT courses.cname FROM teachers JOIN courses
ON teachers.tno = courses.tno #表关系
WHERE teachers.tname='易天'; #业务条件

#练习5：查询计算机导论课程的总分
SELECT SUM(scores.degree) FROM courses JOIN scores
ON courses.cno = scores.cno #表关系
WHERE courses.cname = '计算机导论' #业务关系

join的三种连接

内连接 inner join
左（外）连接 left join
右（外）连接 right join

#测试：三种连接的区别？？
#方式2：连接查询，用join连接多张表
#inner join：查两张表的交集
#left join：左边表的所有和右边满足条件的
#right join：右边表的所有和左边满足条件的

#内连接：取交集
SELECT * FROM dept INNER JOIN emp 
ON dept.deptno=emp.deptno

#左连接：取左表的所有和右表满足条件的不满足的都是null
SELECT * FROM dept LEFT JOIN emp
ON dept.deptno=emp.deptno

#右连接：取右表的所有和左表满足条件的不满足的都是null
SELECT * FROM emp RIGHT JOIN dept 
ON dept.deptno=emp.deptno

#连接查询的效率：小表驱动大表，
#把结构简单或者数据量少的表放在前面作为左表
#因为左表会查所有数据,右表只查满足了条件的那些数据

inner join、left join、right join的区别？

在这里插入图片描述

方式三：子查询 subquery

概念

也叫嵌套查询,把上次的查询结果,当这次查询的条件来用

到底用=还是in来引导着子查询，要看子查询查到几条数据
如果只查到一条结果用=，如果查到多个结果只能用in

单行子查询 =

返回结果为一个

#方式三：子查询
#子查询：要分析第一次查啥，第二次查啥
#练习1：查询计算机导论课程的总分
#第一次查：1.根据cname查询cno- 3-105
#SELECT cno FROM courses WHERE cname='计算机导论'
#第二次查：2.根据cno查询degree
#SELECT SUM(degree) FROM scores WHERE cno='3-105'
#改成子查询：嵌套查询
SELECT SUM(degree) FROM scores WHERE cno=(
SELECT cno FROM courses WHERE cname='计算机导论'
)

#练习2：查询易天老师能讲的课程名称
#1.根据tname查tno --804
#SELECT tno FROM teachers WHERE tname='易天'
#2.根据tno查cname
#SELECT cname FROM courses WHERE tno='804'
#改为子查询
SELECT cname FROM courses WHERE tno=(
SELECT tno FROM teachers WHERE tname='易天'
)


#练习3：查询accounting部门的员工的名字
#根据dname查deptno --1
#SELECT deptno FROM dept WHERE dname='accounting'
#根据deptno查ename
#SELECT ename FROM emp WHERE deptno='1'
#变成子查询
SELECT ename FROM emp WHERE deptno=(
SELECT deptno FROM dept WHERE dname='accounting'
)

#练习5：查询高于平均工资的员工信息
SELECT * FROM emp WHERE sal>(
SELECT AVG(sal) FROM emp #平均工资
)

多行子查询 in

in子查询

#练习4：查询在二区办公的员工名字
SELECT ename FROM emp WHERE deptno IN (
SELECT deptno FROM dept WHERE loc='二区'
)

3. 三种方式练习

练习1：查询research部门的所有员工姓名和工资

#用三种多表联查的方式完成

#练习1：查询research部门的所有员工姓名和工资
#方式1：笛卡尔积
SELECT emp.ename,emp.sal FROM dept,emp
WHERE dept.deptno = emp.deptno
AND dept.dname='research'

#方式2：连接查询
SELECT emp.ename,emp.sal FROM dept JOIN emp
ON dept.deptno = emp.deptno
WHERE dept.dname='research'

#方式3：子查询
SELECT emp.ename,emp.sal FROM emp WHERE deptno=(
SELECT deptno FROM dept WHERE dname='research'
)

练习2:查询jack所在的部门信息

#练习2:查询jack所在的部门信息
#方式1：笛卡尔积
SELECT dept.* FROM dept,emp
WHERE dept.deptno=emp.deptno
AND emp.ename='jack'
#方式2：连接查询
SELECT dept.* FROM dept JOIN emp
ON dept.deptno=emp.deptno
WHERE emp.ename='jack'
#方式3：子查询
SELECT * FROM dept WHERE deptno=(
SELECT deptno FROM emp WHERE ename='jack'
)

练习3:查询总监的部门信息

#练习3:查询总监的部门信息
#方式1：笛卡尔积
SELECT dept.* FROM dept,emp
WHERE dept.deptno=emp.deptno
AND emp.job='总监'
#方式2：连接查询
SELECT dept.* FROM dept JOIN emp
ON dept.deptno=emp.deptno
WHERE emp.job='总监'
#方式3：子查询
SELECT dept.* FROM dept WHERE deptno=(
SELECT deptno FROM emp WHERE job='总监'
)

练习4:查询李军的平均分

#练习4:查询李军的平均分
#方式1：笛卡尔积
SELECT AVG(scores.degree) FROM students,scores
WHERE students.sno=scores.sno
AND students.sname='李军'
#方式2：连接查询
SELECT AVG(scores.degree) FROM students JOIN scores
ON students.sno=scores.sno
WHERE students.sname='李军'
#方式3：子查询
SELECT AVG(scores.degree) FROM scores WHERE sno=(
SELECT sno FROM students WHERE sname='李军'
)

练习5:查询陈冰能讲的课程名

#练习5:查询陈冰能讲的课程名
#方式1：笛卡尔积
SELECT courses.cname FROM courses,teachers
WHERE courses.tno=teachers.tno
AND teachers.tname='陈冰'
#方式2：连接查询
#SELECT courses.cname FROM courses JOIN teachers
ON courses.tno=teachers.tno
WHERE teachers.tname='陈冰'
#方式3：子查询
SELECT courses.cname FROM courses WHERE tno=(
SELECT tno FROM teachers WHERE tname='陈冰'
)