一. DQL查询表中数据
数据查询语言(Data Query Language,DQL):DQL主要用于数据的查询,其基本结构是使用SELECT子句,FROM子句和WHERE子句的组合来查询一条或多条数据
DQL是实际开发过程中使用最多的
1.1 数据准备
# 创建员工表 表名 emp 表中字段: eid 员工id,int ename 姓名,varchar sex 性别,char salary 薪资, double hire_date 入职时间,date dept_name 部门名称,varchar |
---|
# 创建bjsxt02数据库 create database bjsxt02; # 选择bjsxt02 use bjsxt02; # 创建员工表 create table emp( eid int, ename varchar(20), sex char(1), salary double, hire_date date, dept_name varchar(20) ); |
---|
插入数据
# 添加测试数据 insert into emp VALUES(1,’孙悟空’,’男’,7200,’2013-02-04’,’教学部’); insert into emp VALUES(2,’猪八戒’,’男’,3600,’2010-12-02’,’教学部’); insert into emp VALUES(3,’唐僧’,’男’,9000,’2008-08-08’,’教学部’); insert into emp VALUES(4,’白骨精’,’女’,5000,’2015-10-07’,’市场部’); insert into emp VALUES(5,’蜘蛛精’,’女’,5000,’2011-03-14’,’市场部’); insert into emp VALUES(6,’玉兔精’,’女’,200,’2000-03-14’,’市场部’); insert into emp VALUES(7,’林黛玉’,’女’,10000,’2019-10-07’,’财务部’); insert into emp VALUES(8,’黄蓉’,’女’,3500,’2011-09-14’,’财务部’); insert into emp VALUES(9,’吴承恩’,’男’,20000,’2000-03-14’,null); insert into emp VALUES(10,’孙悟饭’,’男’, 10,’2020-03-14’,’财务部’); insert into emp VALUES(11,’兔八哥’,’女’, 300,’2010-03-14’,’财务部’); insert into emp VALUES(12,’Tom’,’男’, null,’2010-03-14’,’财务部’); |
---|
1.2 简单查询
·查询不会对数据库中的数据进行修改, 只是一种显示数据的方式
语法格式
select 字段名 from 表名 |
---|
需求1: 查询emp中所有的数据
— 将要查询的字段全部列出 select eid ,ename, sex, salary, hire_date, dept_name from emp; —查询所有的字段可以使用 , 代表所有的字段 select * from emp; |
---|
需求2: 查询emp表中所有的记录, 只显示eid和ename字段
select eid, ename from emp; |
---|
需求3: 查询所有的员工信息, 将字段名显示为中文
select eid as ‘编号’, — as可以省略 ename as ‘姓名’, sex as ‘性别’, salary as ‘薪资’, hire_date as ‘入职日期’, dept_name as ‘部门名称’ from emp; |
---|
需求4: 查询有多少个部门
— 查询所有的部门(出现重复部门) select dept_name from emp; |
---|
— 使用去重查询 关键字 distinct select distinct dept_name from emp; |
需求5: 将所有的员工薪资加1000显示
— 支持算术运算符 + - * / % 的运算 select eid, ename, salary, salary+1000 from emp; |
---|
1.3 条件查询
·如果查询语句中没有设置条件, 就会查询所有的行信息
·在实际应用中, 一定要指定查询的条件, 对记录进行过滤
语法格式
select 列名 from 表名 where 条件 |
---|
1) 比较运算符
2) 逻辑运算符
需求1: 精确查询
#1 查询员工姓名为黄蓉的员工信息 #2 查询薪水价格为5000的员工信息 #3 查询薪水价格不是5000的所有员工信息 #4 查询薪水价格大于6000元的所有员工信息 #5 查询薪水价格在5000到10000之间所有员工信息 #6 查询薪水价格是3600或7200或者20000的所有员工信息 |
---|
SQL实现
#1 查询员工姓名为黄蓉的员工信息 select from emp where ename = ‘黄蓉’; #2 查询薪水价格为5000的员工信息 select from emp where salary = 5000; #3 查询薪水价格不是5000的所有员工信息 select from emp where salary != 5000; select from emp where salary <> 5000; #4 查询薪水价格大于6000元的所有员工信息 select from emp where salary > 6000; #5 查询薪水价格在5000到10000之间所有员工信息 select from emp where salary >= 5000 and salary <= 10000; select from emp where salary between 5000 and 10000; #6 查询薪水价格是3600或7200或者20000的所有员工信息 select from emp where salary = 3600 or salary = 7200 or salary = 20000; select * from emp where salary in (3600, 7200, 20000); |
---|
需求2: 模糊查询
#1 查询含有’八’字的所有员工信息 #2 查询以’孙’字开头的所有员工信息 #3 查询第二个字为’兔’的所有员工信息 #4 查询没有部门的员工信息 #5 查询有部门的员工信息 |
---|
模糊查询 通配符
SQL实现
#1 查询含有’八’字的所有员工信息 select from emp where ename like ‘%八%’; #2 查询以’孙’字开头的所有员工信息 select from emp where ename like ‘孙%’; #3 查询第二个字为’兔’的所有员工信息 select from emp where ename like ‘_兔%’; #4 查询没有部门的员工信息 select from emp where dept_name is null; #5 查询有部门的员工信息 select * from emp where dept_name is not null; |
---|
1.4排序
· 通过order by子语句, 可以将查询出的结果进行排序(排序只是显示效果, 并不会影 响真实的数据
语法结构
select 字段名 from 表名 [where 条件] order by 字段名[asc | desc]; — asc 升序(默认) — desc 降序 |
---|
1)单列排序
· 只按照某一个字段进行排序
需求1: 查询所有的员工信息, 使用saraly进行排序
SQL实现
— 升序排序(默认 asc) select from emp order by salary; — 降序排序(desc) select from emp order by salary desc; |
---|
2) 组合排序
·同时对多个字段进行排序, 如果第一个字段相同, 就按照第二个字段排序, 以此类推
需求2: 查询所有的员工信息, 使用salary升序排列,salary相同按照入职日期降序排序
SQL实现
— 组合排序 select * from emp order by salary, hire_date desc; |
---|
1.5函数
MySQL中提供了大量函数来简化用户对数据库的操作,比如字符串的处理、日期的运算、数值的运算等等。使用函数可以大大提高SELECT语句操作数据库的能力,同时也给数据的转换和处理提供了方便。
函数只是对查询结果中的数据进行处理,不会改变数据库中数据表的值。MySQL中的函数主要分为单行函数和多行函数两大类
单行函数
单行函数是指对每一条记录的值进行计算,并得到相应的计算结果,然后返回给用户,也就是说,每条记录作为一个输入参数,经过函数计算得到每条记录的计算结果。常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数
使用单行函数, 是对行中字段的操作, 操作多少行, 返回多少行数据
多行函数
我们之前做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用多行函数查询是纵向查询,它是对某一列的值进行计算,然后返回一个单一的值
·多行函数会忽略null空值
·多行函数也称为分组函数, 聚合函数
1.4.1 单行函数
1. 字符串函数 (String StringBuilder)
函数 | 描述 |
---|---|
CONCAT(str1, str2, ···, strn) | 将str1、str2···strn拼接成一个新的字符串 |
INSERT(str, index, n, newstr) | 将字符串str从第index位置(从1开始计算)开始 n个字符替换成字符串newstr |
LENGTH(str) | 获取字符串str的长度(字节) |
LOWER(str) | 将字符串str中的每个字符转换为小写 |
UPPER(str) | 将字符串str中的每个字符转换为大写 |
LEFT(str, n) | 获取字符串str最左边的n个字符 |
RIGHT(str, n) | 获取字符串str最右边的n个字符 |
LPAD(str, n, pad) | 使用字符串pad在str的最左边进行填充,直到长度为n个字符为止 |
RPAD(str, n, pad) | 使用字符串pad在str的最右边进行填充,直到长度为n个字符为止 |
LTRIM(str) | 去除字符串str左侧的空格 |
RTRIM(str) | 去除字符串str右侧的空格 |
TRIM(str) | 去除字符串str左右两侧的空格 |
REPLACE(str,oldstr,newstr) | 用字符串newstr替换字符串str中所有的子字符串oldstr |
REVERSE(str) | 将字符串str中的字符逆序 |
STRCMP(str1, str2) | 比较字符串str1和str2的大小 |
SUBSTRING(str,index,n) | 获取从字符串str的index(从1开始)位置开始的n个字符 |
2. 数值函数 (Math)
函数 | 描述 |
---|---|
ABS(num) | 返回num的绝对值 |
CEIL(num) | 返回大于num的最小整数(向上取整) |
FLOOR(num) | 返回小于num的最大整数(向下取整) |
MOD(num1, num2) | 返回num1/num2的余数(取模) |
PI() | 返回圆周率的值 |
POW(num,n)/POWER(num, n) | 返回num的n次方 |
RAND(num) | 返回0~1之间的随机数 |
ROUND(num, n) | 返回x四舍五入后的值,该值保留到小数点后n位 |
TRUNCATE(num, n) | 返回num被舍去至小数点后n位的值 |
- 日期与时间函数
| 函数 | 描述 |
| —- | —- |
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| SYSDATE() | 返回该函数执行时的日期和时间 |
| DAYOFYEAR(date) | 返回日期date为一年中的第几天 |
| WEEK(date)/WEEKOFYEAR(date) | 返回日期date为一年中的第几周 |
| DATE_FORMAT(date, format) | 返回按字符串format格式化后的日期date |
| DATE_ADD(date, INTERVAL expr unit)
/ADDDATE(date, INTERVAL expr unit) | 返回date加上一个时间间隔后的新时间值 | | DATE_SUB(date, INTERVAL expr unit)
/SUBDATE(date, INTERVAL expr unit) | 返回date减去一个时间间隔后的新时间值 | | DATEDIFF(date1, date2) | 返回起始日期date1与结束日期date2之间的间隔天数 |
4. 流程函数( IF SWITCH)
间隔类型 | 描述 |
---|---|
IF(condition, t, f) | 如果条件condition为真,则返回t,否则返回f |
IFNULL(value1, value2) | 如果value1不为null,则返回value1,否则返回value2 |
NULLIF(value1, value2) | 如果value1等于value2,则返回null,否则返回value1 |
CASE value WHEN [value1] THEN result1 [WHEN [value2] THEN result2 …] [ELSE result] END | 如果value等于value1,则返回result1,···,否则返回result |
CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 …] [ELSE result] END | 如果条件condition1为真,则返回result1,···,否则返回result |
5. JSON函数
函数 | 描述 |
---|---|
JSON_APPEND() | 在JSON文档中追加数据 |
JSON_INSERT () | 在JSON文档中插入数据 |
JSON_REPLACE () | 替换JSON文档中的数据 |
JSON_REMOVE () | 从JSON文档的指定位置移除数据 |
JSON_CONTAINS() | 判断JSON文档中是否包含某个数据 |
JSON_SEARCH() | 查找JSON文档中给定字符串的路径 |
6.其他函数
函数 | 描述 |
---|---|
DATABASE() | 返回当前数据库名 |
VERSION() | 返回当前MySQL的版本号 |
USER() | 返回当前登录的用户名 |
INET_ATON(IP) | 返回IP地址的数字表示 |
INET_NTOA | 返回数字代表的IP地址 |
PASSWORD(str) | 实现对字符串str的加密操作 |
FORMAT(num, n) | 实现对数字num的格式化操作,保留n位小数 |
CONVERT(data, type) | 实现将数据data转换成type类型的操作 |
— 字符串函数的操作 #1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x #2 查询emp表所有数据, 将ename第二个字符都换为 某 #3 查询emp表所有数据, 显示ename的长度 #4 查询emp表所有数据, 将 ename有英文的改为都是大写 #5 查询emp表所有数据, 将 ename有英文的改为都是小写 #6 查询emp表所有数据, ename只显示姓 |
---|
SQL实现
#1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x select concat(‘编号:’, eid), concat(‘姓名:’, ename), concat(‘性别:’, sex) from emp; #2 查询emp表所有数据, 将ename第二个字符都换为 某 select eid, insert(ename, 2, 1, 某’), sex from emp; #3 查询emp表所有数据, 显示ename的长度 select eid, ename, length(ename), sex from emp; #4 查询emp表所有数据, 将 ename有英文的改为都是大写 select eid, ename, upper(ename), sex from emp; #5 查询emp表所有数据, 将 ename有英文的改为都是小写 select eid, ename, lower(ename), sex from emp; #6 查询emp表所有数据, ename只显示姓 select eid, ename, substring(ename, 1, 1), sex from emp; |
---|
MySQL提供的一张虚拟表中进行演示,该表名为“dual”,是MySQL为了满足用“SELECT ••• from•••”的习惯而增设的一张虚拟表。 在使用dual表时,如果没有where子句,则可以省略“from dual”, 没有实际意义, 查询的字段不属于任何表的时候, 就可以使用dual这张虚拟表
— 数值函数的操作 select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25) from dual; select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25); |
---|
— 日期与时间函数的操作 select curdate(), curtime(), now(), sysdate(); select curdate(), curtime(), now(), sleep(2), sysdate(); |
---|
— 流程函数的操作 #1 查询emp表所有数据, 薪资 >= 10000 高工资 <10000 低工资 #2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k) #3 查询emp表所有数据, 薪资 >= 15000 优秀 >=9000 坚持住 >=5000 加油哦 >= 3000 加把劲 其他 努力奋斗吧骚年 |
---|
SQL实现
#1 查询emp表所有数据, 薪资 >= 10000 高工资 其他 低工资 select eid, ename, salary, if(salary >= 10000, ‘高工资’, ‘低工资’) from emp; #2 查询emp表所有数据, 计算出员工的年薪 薪资12 加年终奖(每人30k) select eid, ename, salary, salary 12+30000 ‘年薪’ from emp; — 需要考虑null select eid, ename, salary, ifnull(salary, 0) 12+30000 ‘年薪’ from emp; #3 查询emp表所有数据, 薪资 >=3000 加把劲 >=5000 加油哦 >=9000 坚持住 >= 15000 优秀 其他 不及格 select eid, ename, salary, case * when salary >= 15000 then ‘优秀’ when salary >= 9000 then ‘坚持住’ when salary >= 5000 then ‘加油哦’ when salary >= 3000 then ‘加把劲’ else ‘努力奋斗吧骚年’ end from emp; |
---|
— 其它函数 select database(),user(),version(),inet_aton(“192.168.10.1”),inet_ntoa(3232238081); |
---|
1.4.2 多行函数
·多行函数会忽略null空值
·多行函数也称为分组函数, 聚合函数
需求:
#1 查询员工的总数 #2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值, 显示为总薪水, … #3 查询薪水大于4000员工的个数 #4 查询部门为’教学部’的所有员工的个数 #5 查询部门为’市场部’所有员工的平均薪水 #6 查询部门的个数 |
---|
SQL实现
#1 查询员工的总数 — 使用某个字段查询,聚合函数会忽略null, 需要注意为null的字段 select count(eid) from emp; — 所有字段匹配查询 select count(*) form emp; — 增加一列 select 1 from emp; — 效率更高推荐使用 select count(1) from emp; #2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值, 显示为总薪水, … select sum(salary) ‘总薪水’, max(salary) ‘最高薪水’, min(salary) ‘最小薪水’, avg(salary) ‘平均薪水’ from emp; #3 查询薪水大于4000员工的个数 select count(1) from emp where salary > 4000; #4 查询部门为’教学部’的所有员工的个数 select count(1) from emp where dept_name = ‘教学部’; #5 查询部门为’市场部’所有员工的平均薪水 select avg(salary) from emp where dept_name = ‘市场部’; #6 查询部门的个数 select dept_name from emp; — 9个 select count(dept_name) from emp; — 8个 — 部门去重之后, 统计个数 select count(distinct dept_name) from emp; — 3个 |
---|
1.6分组
·分组查询指的是使用group by语句, 对查询的信息进行分组, 相同数据作为一组
语法格式
select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件] |
---|
需求1: 通过性别分组
SQL实现
# 按照性别分组查询 select * from emp group by sex; — 能查到结果, 但是没有意义 select sex from emp group by sex; — 正确操作 |
---|
分析: group by 分组过程
注意事项:
·分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作
·查询其他字段没有意义
需求1:
#1 查询每个部门的名称 #2 查询每个部门的平均薪资 #3 查询每个部门的平均薪资, 部门名称不能为空 |
---|
SQL实现
#1 查询每个部门的名称 select dept_name from emp group by dept_name; #2 查询每个部门的平均薪资 select dept_name, avg(salary) from emp group by dept_name; #3 查询每个部门的平均薪资, 部门名称不能为空 select dept_name from emp where dept_name is not null group by dept_name; |
---|
需求2:
#4 查询每个部门的平均薪资, 只显示平均工资在4000以上的 |
---|
SQL实现
select dept_name, avg(salary) from emp where avg(salary) > 4000 group by dept_name; — Invalid use of group function 报错 |
---|
分析:
1) 需要在分组后, 对数据进行过滤, where的作用是在分组前过滤
2) select语句的执行顺序
from — where — group by — having –- select — order by
3)分组操作中的having子语句, 适用于对分组后的数据进行过滤的, 作用类似于 where
SQL实现:
#4 查询每个部门的平均薪资, 只显示平均工资在4000以上的 select dept_name, avg(salary) from emp group by dept_name having avg(salary) > 4000; |
---|
where 和 having的区别
过滤方式 | 特点 |
---|---|
where | 分组之前的过滤 后边不能写多行函数 |
having | 分组之后的过滤 后边可以写多行函数 |
group by 和 having练习 #1 统计每个部门中的最小工资, 列出最小工资小于2000的部门名称 #2 统计平均工资大于3000的部门名称 #3 统计人数小于4的部门的平均工资 #4 统计每个部门最高工资, 排除最高工资小于3000的部门 |
---|
#1 统计每个部门中的最小工资, 列出最小工资小于4000的部门名称 — 每个部门的最小工资 select dept_name, min(salary) from emp group by dept_name; select dept_name, min(salary) from emp group by dept_name having min(salary) < 4000; #2 统计平均工资大于6000的部门名称 — 每个部门的平均工资 select dept_name, max(salary) from emp group by dept_name; select dept_name, max(salary) from emp group by dept_name having max(salary) > 6000; #3 统计人数小于4个人部门的平均工资 — 每个部门的平均工资和人数 select dept_name, avg(salary), count(1) from emp group by dept_name; select dept_name, avg(salary), count(1) from emp group by dept_name having count(1) < 4; #4 统计每个部门最高工资, 排除最高工资小于10000的部门 — 每个部门的最高工资 select dept_name, max(salary) from emp group by dept_name select dept_name, max(salary) from emp group by dept_name having max(salary) >= 10000; |
---|
1.7 limit关键字
作用:
·limit是限制的意思, 限制返回的查询结果的函数(通过limit之sing查询多少行数据)
·limit 语法是 MySql的方言, 用来完成分页
语法结构
select 字段1, 字段2 … from 表名 limit offset, length; |
---|
参数说明
1) offset 起始行数, 从0开始, 如果省略则默认从0开始, 0代表MySQL中第一条数据
2) length 返回的行数
需求1:
#1 查询emp表中的前5条数据 #2 查询emp表中 从第4条开始, 查询6条 |
---|
SQL实现
#1 查询emp表中的前5条数据 select from emp limit 5; — 不指定从哪行还是, 默认从0开始 select from emp limit 0, 5; #2 查询emp表中 从第4条开始, 查询6条 select * from emp limit 3, 6; — 从0开始, 所以第四条数据为3 |
---|
二. 多表
2.1 多表的概述
实际开发中, 一个项目通常需要很多张表才能完成
例如一个商城项目的数据库, 需要很多张表: 如 用户表, 分类表, 商品表. 订单表…
2.2单表的缺点
2.2.1数据准备
- 创建一张员工表 emp
字段: eid, ename, age, dep_name, dep_location
eid为主键并且自增
添加五条测试数据
create table emp( id int primary key auto_increment, ename varchar(10), age int, dep_name varchar(10), dep_location varchar(10) ); |
---|
— 添加数据 insert into emp (ename, age, dep_name, dep_location) values(‘张百万’, 20, ‘研发部’, ‘广州’); insert into emp (ename, age, dep_name, dep_location) values(‘赵四’, 21, ‘研发部’, ‘广州’); insert into emp (ename, age, dep_name, dep_location) values(‘广坤’, 20, ‘研发部’, ‘广州’); insert into emp (ename, age, dep_name, dep_location) values(‘小斌’, 20, ‘销售部’, ‘深圳’); insert into emp (ename, age, dep_name, dep_location) values(‘艳秋’, 22, ‘销售部’, ‘深圳’); insert into emp (ename, age, dep_name, dep_location) values(‘大玲子’, 18, ‘销售部’, ‘深圳’); |
---|
2.2.2单表存在的问题
2.3 解决方案
设计为两张表
- 多表方式设计
employee 员工表: eid ename age
department 部门表: depid, dep_name, dep_location
- 创建员工和部门表
| — 创建员工表
create table employee(
eid int primary key auto_increment,
ename varchar(10),
age int,
dep_id int
); | | —- | | — 创建部门表
create table department(
dep_id int primary key auto_increment,
dep_name varchar(10),
dep_location varchar(10)
); |
3) 插入数据
— 添加2个部门 insert into department values(default, ‘研发部’,’广州’),(default, ‘销售部’, ‘深圳’); select * from department; |
---|
— 添加5个人员工 insert into employee values(default, ‘张百万’, 20, 1), (default, ‘赵四’, 21, 1), (default, ‘广坤’, 20, 1), (default, ‘小斌’, 20, 2), (default, ‘艳秋’, 22, 2), (default, ‘大玲子’, 18, 2); select * from employee; |
2.3.1 表关系分析
1) 员工表中有一个字段dep_id与部门表中的主键对应吗员工表的这个字段就叫做 外键
2) 拥有外键的员工表被称为 从表, 与外键对应的主键所在的表叫做 主表
2.3.2多表设计上的问题
当我们在员工表的dep_id里面输入了不存在的部门编号, 数据依然可以添加, 显然这是不合理的
例如:
— 插入一条 不存在部门的数据 insert into employee values(default, ‘张亿万’, 20, 100), |
---|
应该保证员工表所添加的dep_id, 必须在部门表dep_id中存在
解决方案:
使用外键约束, 约束员工表中的dep_id必须在部门表dep_id中存在
2.3.3 外键约束
1) 外键
外键指的是在主表中与从表的主键对应的的那个字段, 如员工表的dep_id, 就是外键
2) 外键约束
使用外键约束可以让两张表之间产生一个对应关系, 从而保证主从表的引用的完整性
创建外键约束
语法格式:
1. 新建表时添加外键约束
constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段); |
---|
- 为已创建好的表添加外键约束
alter table 从表名 add constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段名); |
---|
需求: 为employee表的 dep_id字段添加外键约束
1. 为已经创建好的employee表添加外键约束
alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(dep_id); |
---|
- 新创建employee表时, 为dep_id添加外键约束
create table employee( eid int primary key auto_increment, ename varchar(10), age int, dep_id int, — 添加外键约束 constraint emp_dep_fk foreign key(dep_id) references department(dep_id) ); |
---|
3. 测试数据
— 插入正常数据(从表的外键对应着主表的主键) insert into employee values(default, ‘张百万’, 20, 1), (default, ‘艳秋’, 22, 2); — 插入不存在的dep_id insert into employee values(default, ‘张亿万’, 20, 100); — Cannot add or update a child row: a foreign key constraint fails |
---|
删除外键约束
语法格式:
alter table 从表名 drop foreign key 外键约束的名称 |
---|
需求: 删除employee表中的外键约束
alter table employee drop foreign key emp_dep_fk; |
---|
2.3.4 外键约束的注意事项
1) 从表的外键类型必须和主表的主键类型保持一致
2) 添加从表数据时
从表中添加的外键值, 必须在主表的主键中存在
- 删除和变更数据主表数据时
先删除从表中的数据或将外键设置为null, 再删除主表中的数据
— 删除部门表中主键为1的部门信息 delete from department where dep_id = 1; — 报错信息如下 — Cannot delete or update a parent row: a foreign key constraint fails |
---|
4) 通过navicat设置外键约的束变更和删除的级联操作
使用说明:
选项 | 作用 |
---|---|
RESTRICT(默认) | 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 |
No action | 同RESTRICT, 都是立即检查外键约束 |
CASCADE | 在主表上update/delete记录时,同步update/delete掉从表匹配的记录 |
SET NULL | 在父表上update/delete记录时,将子表上匹配记录的列设为null 注意: 子表的外键列不能为not null |
三. 多表关系设计
实际的开发过程中, 一个项目通常需要很多张表才能完成. 例如: 一个商城项目就需要分类表(category), 商品表(products), 订单表(orders)等多张表. 而且这些表的数据之间存在一定的关系, 接下来我们一起学习以下多表设计方面的知识
表与表之间的3种关系 | 例如 |
---|---|
一对多关系(最常见) | 员工表和部门表 学生表和班级表 |
多对多关系 | 学生表和课程表 用户表和角色表 |
一对一关系(使用较少) | 一对一的关系可以设计成一张表 |
3.1 一对多关系(常见)
·一对多关系(1 : n)
例如: 班级和学生, 部门和员工, 客户和订单, 类别和商品
·一对多建表原则
3.2 多对多关系
·多对多关系(m : n)
例如: 老师和学生, 学生和课程, 用户和角色
·多对多关系建表原则
多对多的关系不能直接处理, 需要创建第三张表, 也称为中间表, 中间表至少两个字段,这两个字段分别作为外键指向各自一方的主键, 实际就是将多对多拆分为两个一对多
3.3 一对一关系(了解)
·多对多关系(1 : 1)
在实际的开发过程中应用不多, 因为一对一的关系可以设计为一张表
四. 多表查询
4.1 什么是多表查询
·DQL: 查询多张表, 获取到需要的数据
·比如: 我们要查询家电分类下都有哪些商品, 那么我们就需要查询分类与商品两张表
4.2 数据准备
- 创建分类表与商品表
| #分类表 (一方 主表)
create table category (
cid varchar(32) primary key,
cname varchar(50)
);
#商品表 (多方 从表)
create table products(
pid varchar(32) primary key,
pname varchar(50),
price int,
flag varchar(2), # 是否上架标记为:1表示上架、0表示下架
cid varchar(32),
— 添加外键约束
foreign key (cid) references category (cid)
); | | —- |
2) 插入数据
#分类数据 insert into category(cid,cname) values(‘c001’,’家电’); insert into category(cid,cname) values(‘c002’,’鞋服’); insert into category(cid,cname) values(‘c003’,’化妆品’); insert into category(cid,cname) values(‘c004’,’汽车’); #商品数据 insert into products values(‘p001’,’小米电视机’,5000,’1’,’c001’); insert into products values(‘p002’,’格力空调’,3000,’1’,’c001’); insert into products values(‘p003’,’美的冰箱’,4500,’1’,’c001’); insert into products values(‘p004’,’篮球鞋’,800,’1’,’c002’); insert into products values(‘p005’,’运动裤’,200,’1’,’c002’); insert into products values(‘p006’,’T恤’,300,’1’,’c002’); insert into products values(‘p007’,’冲锋衣’,2000,’1’,’c002’); insert into products values(‘p008’,’神仙水’,800,’1’,’c003’); insert into products values(‘p009’,’大宝’,200,’1’,’c003’); |
---|
4.2 笛卡尔积
交叉连接(CROSS JOIN)是对两个或者多个表进行笛卡儿积操作,所谓笛卡儿积就是关系代数里的一个概念,表示两个表中的每一行数据任意组合的结果
笛卡尔积不管是否匹配,都连接。没有实际意义,有理论意义
笛卡尔积便于理解连接查询的原理
使用语法
select 字段名 from 表1 cross join 表2; |
---|
使用交叉连接, 查询分类表与商品表
select * from products cross join category; |
---|
4.3 多表查询的分类
4.3.1 内连接查询
内连接的特点
·通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上就不显示
·如: 按照从表的外键 = 主表的主键进行匹配 | inner join on, inner可以省略
4.3.1.1隐式内连接
from子句后面直接写多个表名 使用where指定连接条件的 这种连接方式是隐式内连接. 使用where条件过滤无用的数据
语法格式
select 字段名 from 表1, 表2 where 连接条件; |
---|
需求1: 查询分类信息以及对应的商品信息
select * from category, products where category.cid = products.cid; |
---|
需求2: 查询分类信息以及对应的商品信息, 只显示类别名称, 商品名称, 价格
— 可以为表定义别名, 方便查询 select c.cname, p.pname, p.price from category c, products p where c.cid = p.cid; |
---|
4.3.1.2 显示内连接
使用 inner join …on 这种方式, 就是显式内连接
语法格式
select 字段名 from 表1 [inner] join 右表 on 条件 |
---|
需求1: 查询分类信息以及对应的商品信息
select * from category c inner join products p on c.cid = p.cid; |
---|
需求2: 查询鞋服分类下, 价格大于500的商品名称和价格以及对应的分类名称
# 思路分析 — 1.查询哪几张表 category products — 2.表之间的连接条件 主表主键 = 从表外键 — 3.查询条件 p.price > 500 and c.cname = ‘鞋服’ — 4.查询的字段 cname pname price select c.cname, p.pname, p.price from category c inner join products p on c.cid = p.cid where p.price > 500 and c.cname = ‘鞋服’; |
---|
注意事项:
·内连接只会显示匹配的数据
·内连接不能实现不匹配数据的显示
4.3.2 外连接查询
外连接的特点
通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上也可以显示
4.3.2.1 左外连接
·使用 left outer join , outer 可以省略
语法格式
select 字段名 from 表1 left join 表2 on 条件; |
---|
需求1: 查询分类信息以及对应的商品信息
select * from category c left join products p on c.cid = p.cid; |
---|
左外连接的特点:
1) 以左表为主, 左表中的数据全部显示
2) 右表匹配到数据就显示匹配到的数据
3) 右表没有匹配的数据显示为null
需求2: 使用左外连接查询每个分类下的商品名称, 商品个数
# 思路分析 — 1.查询哪几张表 category products — 2.表之间的连接条件 主表主键 = 从表外键 — 3.查询条件 每个类别 需要分组 — 4.查询的字段 类别名称 商品数量 select c.cname, count(p.cid) from category c left join products p on c.cid = p.cid group by c.cname; |
---|
4.3.2.2 右外连接
·使用 right outer join, outer可以省略
语法格式
select 字段名 from 表1 right join 表2 on 条件; |
---|
需求1: 查询商品信息以及对应的分类信息
select * from products c right join category p on c.cid = p.cid; |
---|
左外连接的特点:
1) 以右表为主, 右表中的数据全部显示
2) 左表匹配到数据就显示匹配到的数据
3) 左表没有匹配的数据显示为null
4.3.2.3 内连接和外连接的总结
内连接: inner join: 只获取两张表中 交集部分的数据.
左外连接: left join: 以左表为基准, 查询左表的所有数据, 以及与右表有交集的部分
右外连接: right join: 以右表为基准, 查询右表的所有的数据, 以及与左表有交集的部分