多表关系&多表查询
第一章 SQL备份、恢复、数据库密码重置
1.1 SQL备份
数据库的备份是指将数据库转换成对应的sql文件
MySQL命令备份
数据库导出sql脚本的格式:
mysqldump -u用户名 -p密码 数据库名>生成的脚本文件路径
例如:
mysqldump -uroot -proot day04>d:\day03.sql
以上备份数据库的命令中需要用户名和密码,即表明该命令要在用户没有登录的情况下使用
可视化工具备份
选中数据库,右键 ”备份/导出” , 指定导出路径,保存成.sql文件即可。
1.2 SQL恢复
数据库的恢复指的是使用备份产生的sql文件恢复数据库,即将sql文件中的sql语句执行就可以恢复数据库内容。
MySQL命令恢复
使用数据库命令备份的时候只是备份了数据库内容,产生的sql文件中没有创建数据库的sql语句,在恢复数据库之前需要自己动手创建数据库。
- 在数据库外恢复
- 格式:
mysql -uroot -p密码 数据库名 < 文件路径
- 例如:
mysql -uroot -proot day03<d:\day03.sql
- 格式:
- 在数据库内恢复
- 格式:
source SQL脚本路径
- 例如:
source d:\day03.sql
- 注意:使用这种方式恢复数据,首先要登录数据库.
- 格式:
可视化工具恢复
数据库列表区域右键“从SQL转储文件导入数据库”, 指定要执行的SQL文件,执行即可。
1.3 MySQL数据库密码重置
- 停止mysql服务器运行输入services.msc 停止mysql服务
- 在cmd下,输入mysqld --console --skip-grant-tables 启动服务器,出现一下页面,不要关闭该窗口
- 新打开cmd,输入mysql -uroot 不需要密码
use mysql;
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
- 关闭两个cmd窗口 重启服务 使用新密码即可
第二章 多表操作
实际开发中,一个项目通常需要很多张表才能完成。例如:一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系,接下来我们将在单表的基础上,一起学习多表方面的知识。
2.1 表与表之间的关系
- 一对多关系:
- 常见实例:客户和订单,分类和商品,部门和员工.
- 一对多建表原则:在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键.
- 多对多关系:
- 常见实例:学生和课程、用户和角色
- 多对多关系建表原则:需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键.
- 一对一关系:(了解)
- 在实际的开发中应用不多.因为一对一可以创建成一张表.
- 两种建表原则:
- 外键唯一:主表的主键和从表的外键(唯一),形成主外键关系,外键唯一unique。
- 外键是主键:主表的主键和从表的主键,形成主外键关系。
2.2 外键约束
现在我们有两张表“分类表”和“商品表”,为了表明商品属于哪个分类,通常情况下,我们将在商品表上添加一列,用于存放分类cid的信息,此列称为:外键
此时“分类表category”称为:主表,“cid”我们称为主键。“商品表products”称为:从表,category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系,呈现就是一对多关系。
-
外键特点:
- 从表外键的值是对主表主键的引用。
- 从表外键类型,必须与主表主键类型一致。
-
声明外键约束
语法:
alter table 从表 add [constraint][外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
[外键名称]用于删除外键约束的,一般建议“_fk”结尾
alter table 从表 drop foreign key 外键名称
- 使用外键目的:
- 保证数据完整性
2.3 一对多操作
分析
- category分类表,为一方,也就是主表,必须提供主键cid
- products商品表,为多方,也就是从表,必须提供外键category_id
实现:分类和商品
#创建分类表
create table category(
cid varchar(32) PRIMARY KEY ,
cname varchar(100) -- 分类名称
);
# 商品表
CREATE TABLE `products` (
`pid` varchar(32) PRIMARY KEY ,
`name` VARCHAR(40) ,
`price` DOUBLE
);
#添加外键字段
alter table products add column category_id varchar(32);
#添加约束
alter table products add constraint product_fk foreign key (category_id) references category (cid);
操作
#1 向分类表中添加数据
INSERT INTO category (cid ,cname) VALUES('c001','服装');
#2 向商品表添加普通数据,没有外键数据,默认为null
INSERT INTO products (pid,pname) VALUES('p001','商品名称');
#3 向商品表添加普通数据,含有外键信息(category表中存在这条数据)
INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');
#4 向商品表添加普通数据,含有外键信息(category表中不存在这条数据) -- 失败,异常
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999');
#5 删除指定分类(分类被商品使用) -- 执行异常
DELETE FROM category WHERE cid = 'c001';
2.4 多对多
分析
- 商品和订单多对多关系,将拆分成两个一对多。
- products商品表,为其中一个一对多的主表,需要提供主键pid
- orders 订单表,为另一个一对多的主表,需要提供主键oid
- orderitem中间表,为另外添加的第三张表,需要提供两个外键oid和pid
实现:订单和商品
#商品表[已存在]
#订单表
create table `orders`(
`oid` varchar(32) PRIMARY KEY ,
`totalprice` double #总计
);
#订单项表
create table orderitem(
oid varchar(50),-- 订单id
pid varchar(50)-- 商品id
);
#订单表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references orders(oid);
#商品表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_product_fk foreign key (pid) references products(pid);
#联合主键(可省略)
alter table `orderitem` add primary key (oid,pid);
操作
#1 向商品表中添加数据
INSERT INTO products (pid,pname) VALUES('p003','商品名称');
#2 向订单表中添加数据
INSERT INTO orders (oid ,totalprice) VALUES('x001','998');
INSERT INTO orders (oid ,totalprice) VALUES('x002','100');
#3向中间表添加数据(数据存在)
INSERT INTO orderitem(pid,oid) VALUES('p001','x001');
INSERT INTO orderitem(pid,oid) VALUES('p001','x002');
INSERT INTO orderitem(pid,oid) VALUES('p002','x002');
#4删除中间表的数据
DELETE FROM orderitem WHERE pid='p002' AND oid = 'x002';
#5向中间表添加数据(数据不存在) -- 执行异常
INSERT INTO orderitem(pid,oid) VALUES('p002','x003');
#6删除商品表的数据 -- 执行异常
DELETE FROM products WHERE pid = 'p001';
第三章 多表关系练习
3.1 练习1:省和市
- 方案1:多张表,一对多
3.2 练习2:用户和角色权限
- 多对多关系
第四章 多表查询
提供表结构如下:
CREATE DATABASE day02;
USE day02;
# 分类表
CREATE TABLE category (
cid VARCHAR(32) PRIMARY KEY ,
cname VARCHAR(50)
);
#商品表
CREATE TABLE products(
pid INT PRIMARY KEY AUTO_INCREMENT ,
pname VARCHAR(50),
price DOUBLE,
flag VARCHAR(2), #是否上架标记为:1表示上架、0表示下架
cid VARCHAR(32),
CONSTRAINT products_fk FOREIGN KEY (cid) REFERENCES category (cid)
);
4.1 初始化数据
#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
INSERT INTO products(pid, pname,price,flag,cid) VALUES(1,'联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,cid) VALUES(2,'海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,cid) VALUES(3,'雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,cid) VALUES(4,'JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,cid) VALUES(5,'真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,cid) VALUES(6,'花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,cid) VALUES(7,'劲霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,cid) VALUES(8,'香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,cid) VALUES(9,'相宜本草',200,'1','c003');
4.2 多表查询
交叉查询
--交叉查询取的是两张表的笛卡尔积
select * from category,products;
select * from category cross join products;
内连接
--内连接查询的是两张表的交集
-- 隐式内连接
select p.*,c.cname from category c,products p where c.cid = p.cid;
-- 显示内连接
select p.*,c.cname from category c inner join products p on c.cid = p.cid;
select p.*,c.cname from category c join products p on c.cid = p.cid;
外连接
-- 左外连接 以左表为基准 左表有的都查询出来 右表没有 显示null值
select * from category c left join products p on c.cid = p.cid;
-- 左外连接 查询左表有 右表没有的记录
select * from category c left join products p on c.cid = p.cid where p.cid is null;
-- 右外连接 以右表为基准 右表有的内容都查询出来 左表没有 显示null值
select * from category c right join products p on c.cid = p.cid ;
-- 右外连接 查询右表有 但是左表没有的数据
select * from category c right join products p on c.cid = p.cid where c.cid is null;
全外连接(mysql不支持)
-- 查询两张表的并集 mysql不支持
select * from category c full join products p on c.cid = p.cid;
-- 可以通过union实现
SELECT * FROM category c LEFT JOIN products p ON c.cid = p.cid
UNION
SELECT * FROM category c RIGHT JOIN products p ON c.cid = p.cid;
-- 查询张表不相交的 A表有B表没有 B表有A表没有
select * from t_category c full outer join t_product p on c.cid = p.cid where c.cid is null
or p.cid is null;
-- 可以通过union实现
SELECT * FROM category c LEFT JOIN products p ON c.cid = p.cid WHERE p.cid IS NULL
UNION
SELECT * FROM category c RIGHT JOIN products p ON c.cid = p.cid WHERE c.cid IS NULL;
union
#union 将多条语句的查询结果 合并成一个结果
CREATE TABLE us(
id INT PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(100),
gender VARCHAR(100)
);
INSERT INTO us VALUES
(NULL,'john','male'),
(NULL,'lucy','female'),
(NULL,'jack','male'),
(NULL,'rose','female');
CREATE TABLE ch(
id INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100),
sex VARCHAR(100)
);
INSERT INTO ch VALUES
(NULL,'张三','男'),
(NULL,'李四','女'),
(NULL,'王五','男');
#查询所有性别为男的信息
SELECT id,cname FROM ch WHERE sex ='男'
UNION ALL
SELECT id,uname FROM us WHERE gender = 'male';
4.3 子查询
子查询:一条select语句结果作为另一条select语法一部分(查询条件,查询结果,表等)。
语法:select ....查询字段 ... from ... 表.. where ... 查询条件
#3 子查询, 查询“化妆品”分类上架商品详情
#隐式内连接
SELECT p.*
FROM products p , category c
WHERE p.category_id=c.cid AND c.cname = '化妆品';
#子查询
##作为查询条件
SELECT *
FROM products p
WHERE p.category_id =
(
SELECT c.cid FROM category c
WHERE c.cname='化妆品'
);
##作为另一张表
SELECT *
FROM products p ,
(SELECT * FROM category WHERE cname='化妆品') c
WHERE p.category_id = c.cid;
子查询练习:
#查询“化妆品”和“家电”两个分类上架商品详情
SELECT *
FROM products p
WHERE p.category_id in
(SELECT c.cid
FROM category c
WHERE c.cname='化妆品' or c.name='家电'
);
相关子查询(了解)
-- 查询 分类id 分类名称 以及每个商品的个数
-- 连接查询
select c.cid,c.cname,count(p.cid) num
from category c left join products p
on c.cid = p.cid group by p.cid;
-- 连接查询
select c.cid,c.cname,if(p.num is null,0,p.num) from
(select cid ,count(*) num from products group by cid ) p
right join category c on c.cid = p.cid;
-- 相关子查询
-- 先执行主查询,再针对主查询返回的每一行数据执行子查询。
select cid,cname,(select count(*) from products p where p.cid = category.cid) num from category;
-- 非相关子查询 非相关子查询执行顺序是先执行子查询,再执行主查询。
select * from products where cid in (select cid from category where cname = '化妆品' );
--相关子查询
-- 相关子查询:先执行主查询,再针对主查询返回的每一行数据执行子查询,如果子查询能够返回行,则这条记录就保留,否则就不保留。
/*
(1)从外层查询中取出一个元组,将元组相关列的值传给内层查询。
(2)执行内层查询,得到子查询操作的值。
(3)外查询根据子查询返回的结果或结果集得到满足条件的行。
(4)然后外层查询取出下一个元组重复做步骤1-3,直到外层的元组全部处理完毕。
*/
select * from products p where exists (select * from category c where c.cid = p.cid and c.cname ='化妆品' );
-- 如果查询数量比较小时:非相关子查询比相关子查询效率高。
-- 如果查询数量比较大时:一般不建议使用in,因为in的效率比较低,我们可以使用相关子查询。
4.4 case when语法
CREATE TABLE persons(
pid INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(100),
age INT ,
sex VARCHAR(100)
);
INSERT INTO persons(NAME,age,sex) VALUES
('liuyan',38,'女'),
('tangyan',18,'女'),
('jinlian',28,'女'),
('dalang',8,'男'),
('ximenqing',80,'男');
SELECT *,IF(sex = '男', 'man', 'woman') gender FROM persons;
-- 第一种格式
SELECT *,
CASE sex
WHEN '男' THEN 'man'
WHEN '女' THEN 'woman'
ELSE 'other' END AS english_sex
FROM persons;
-- 第二种格式
SELECT *,
CASE
WHEN sex = '男' THEN 'man'
WHEN sex = '女' THEN 'woman'
ELSE 'other'
END AS english_sex
FROM persons;
-- 对年龄进行分段
/*
如果年龄在 [10,20)之间 则 显示 10~20
如果年龄在 [20,30)之间 则 显示 20~30
如果年龄在 [30,40)之间 则 显示 30~40
如果不在这些范围 则显示 other
*/
SELECT *,
IF(age >= 10 AND age < 20, '10~20',
IF(age >= 20 AND age < 30, '20~30', IF(age > 30 AND age < 40, '30~40', 'other'))) AS age_stage
FROM persons;
select *,
case
when age >= 10 and age < 20 then '10~20'
when age >= 20 and age < 30 then '20~30'
when age >= 30 and age < 40 then '30~40'
else 'other' end as age_stage
from t_user;
第五章 函数(了解)
1.字符串函数
SELECT CONCAT("liu", "yan")
-- 带分隔符字符串连接函数:concat_ws(separator,[string|array(string)]+)
SELECT CONCAT_WS('.', 'www','51doit', 'com');
-- 字符串截取函数: substr(str, pos[, len])或者substring(str, pos[,len])
SELECT SUBSTR("angelababy", -2); -- pos是从1开始的索引,如果为负数则倒着数
SELECT SUBSTR("angelababy", 2, 2);
-- 字符串长度函数: length(str / binary)
SELECT LENGTH("I Love Hive");
-- 字符串反转函数: reverse
SELECT REVERSE("I Love Hive");
-- 字符串连接函数:concat(stri, str2, ... strN)
-- 字符串转大写函数: upper , ucase
SELECT UPPER("I Love Mysql");
SELECT UCASE("I Love Mysql");
-- 字符串转小写函数:lower , Lcase
SELECT LOWER("I Love Mysql");
SELECT LCASE("I Love Mysql");
-- 去空格函数: trim去除左右两边的空格
SELECT TRIM(" I Love Mysql ");
-- 左边去空格函数: ltrim
SELECT LTRIM(" I Love Mysql ");
-- 右边去空格函数:rtrim
SELECT RTRIM(" I Love Mysql ");
-- 空格字符串函数: space(n)返回指定个数空格
SELECT SPACE(4);
-- 重复字符串函数:repeat(str, n)重复str字符串n
SELECT REPEAT("I Love Mysql", 2);
-- 首字符ascii函数: ascii
SELECT ASCII("angela");
-- a对应ASCII 97
-- 左补足函数:lpad
SELECT LPAD('hi', 5, '*');
-- 右补足函数: rpad
SELECT RPAD('hi', 5, '*');
-- 集合查找函数:find_in_set(str ,str_array) 返回str的位置 从1开始
SELECT FIND_IN_SET('b', 'abc,b,ab,c,def');
2.日期函数
-- 当前日期
SELECT CURRENT_DATE();
-- 获取当前时问歌:current_timestamp
-- 同一查询中对current_timestamp 的所有调用均返回相同的值。
SELECT CURRENT_TIMESTAMP();
-- 获取当前UNIX时间戳函数:unix_timestamp
SELECT UNIX_TIMESTAMP();
-- 日期转UNIX时问戳函数: unix_timestamp
SELECT UNIX_TIMESTAMP("2022-08-13 19:08:03");
-- UNIX时间戳转日期函数:from_unixtime
SELECT FROM_UNIXTIME(1660417683);
SELECT FROM_UNIXTIME(0);
-- 日期比较函数; datediff日期格式要求'yyyy-MN-dd HH:mm:ss ' or 'yyyy-MM-dd '
SELECT DATEDIFF('2022-08-08', '2022-08-10');
-- 日期增加函数: date_add
SELECT DATE_ADD('2022-08-08', INTERVAL 10 DAY);
-- 日期减少函数: date_sub
SELECT DATE_SUB('2012-08-08', INTERVAL 10 DAY);
-- 获取年:year
SELECT YEAR('2022-08-13 19:08:03');
-- 获取月:month
SELECT MONTH('2022-08-13 19:08:03');
-- 获取日:day
SELECT DAY('2022-08-13 19:08:03');
-- 获取时:hour
SELECT HOUR('2022-08-13 19:08:03');
-- 获取分:minute
SELECT MINUTE('2022-08-13 19:08:03');
-- 获取秒:second
SELECT SECOND('2022-08-13 19:08:03');
-- 获取周:weekofyear 指定年份的第多少周
SELECT WEEKOFYEAR('2022-08-13 19:08:03');
3.数学函数
-- 四舍五入: round返回double类型的整数值部分(四舍五入)
SELECT ROUND(3.1415926);
SELECT ROUND(3.1415926, 2);
-- 向下取整函数:floor
SELECT FLOOR(3.1415926);
SELECT FLOOR(-3.1415926);
-- 向上取整函数: ceil
SELECT CEIL(3.1415926);
SELECT CEIL(-3.1415926);
-- 取随机数函数: rand每次执行都不一样返回一个0到1范围内的随机数
SELECT RAND();
-- 在同一个查询中保证同一个随机数
SELECT RAND(10);
-- 二进制
SELECT BIN(100);
-- 转换进制 conv(num,现在进制,要转换的进制)
SELECT CONV(11, 10, 2);
-- 绝对值
SELECT ABS(-100);
4.条件判断函数
-- if判断 类似java三元表达式 if(boolean testCondion,T valueTrue,T valueFalse)
SELECT IF(1 = 2, 'a', 'b');
-- 判断是否为空: isnull 是null 返回1 不是null 返回0
SELECT ISNULL(NULL);
SELECT ISNULL(10);
-- coalesce(v1,v2,v3,v4...)返回第一个非空的值 如果都为null返回null
SELECT COALESCE(NULL, NULL, NULL, NULL);
SELECT COALESCE(NULL, NULL, 1, 2, 3, 4, NULL, 5, 6);
SELECT COALESCE(NULL, 3, 1, 2, 3, 4, NULL, 5, 6);
第六章 查询练习
1.练习1
CREATE TABLE tb_login(
uid VARCHAR(100),
uname VARCHAR(100),
login_date DATE
);
INSERT INTO tb_login(uid,uname,login_date) VALUES
('001','liuyan','2022-10-26'),
('002','tangyan','2022-10-26'),
('003','jinlian','2022-10-26'),
('004','dalang','2022-10-26'),
('005','ximenqing','2022-10-26'),
('001','liuyan','2022-10-27'),
('002','tangyan','2022-10-27'),
('004','dalang','2022-10-27'),
('006','wusong','2022-10-27');
-- 查询26号和27号都登陆人的信息
SELECT t1.uid, t1.uname
FROM (SELECT * FROM tb_login WHERE login_date = '2022-10-26') t1
JOIN
(SELECT * FROM tb_login WHERE login_date = '2022-10-27') t2
ON t1.uid = t2.uid;
-- 查询26号登陆 27号没登录人的信息
SELECT *
FROM (SELECT * FROM tb_login WHERE login_date = '2022-10-26') t1
LEFT JOIN
(SELECT * FROM tb_login WHERE login_date= '2022-10-27') t2
ON t1.uid = t2.uid
WHERE t2.uid IS NULL;
-- 查询27号登陆 26号没登录人的信息
SELECT t2.*
FROM (SELECT * FROM tb_login WHERE login_date = '2022-10-26') t1
RIGHT JOIN
(SELECT * FROM tb_login WHERE login_date = '2022-10-27') t2
ON t1.uid = t2.uid
WHERE t1.uid IS NULL;
-- 查询26号登陆 27号没登录人的信息 和 查询27号登陆 26号没登录人的信息
SELECT *
FROM (SELECT * FROM tb_login WHERE login_date = '2022-10-26') t1
LEFT JOIN
(SELECT * FROM tb_login WHERE login_date= '2022-10-27') t2
ON t1.uid = t2.uid
WHERE t2.uid IS NULL
UNION
SELECT *
FROM (SELECT * FROM tb_login WHERE login_date = '2022-10-26') t1
RIGHT JOIN
(SELECT * FROM tb_login WHERE login_date = '2022-10-27') t2
ON t1.uid = t2.uid
WHERE t1.uid IS NULL;
2.练习2
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HjpGKFvC-1682506057298)(img/17_hangzhuanlie.png)]
CREATE TABLE stu_mark(
sname VARCHAR(100),
SUBJECT VARCHAR(100),
score DOUBLE
);
INSERT INTO stu_mark VALUES
('liuyan','语文',100),
('liuyan','数学',99),
('liuyan','英语',100),
('tangyan','语文',80),
('tangyan','数学',98),
('tangyan','英语',60);
-- 使用case when 查询每个人的语文成绩
SELECT sname, CASE SUBJECT WHEN '语文' THEN score ELSE 0 END AS `语文` FROM stu_mark ;
-- 查询每个人的每科成绩
SELECT sname,
CASE SUBJECT WHEN '语文' THEN score ELSE 0 END AS `语文`,
CASE SUBJECT WHEN '数学' THEN score ELSE 0 END AS `数学`,
CASE SUBJECT WHEN '英语' THEN score ELSE 0 END AS `英语`
FROM stu_mark ;
-- 得到最终结果
SELECT sname,
MAX(CASE SUBJECT WHEN '语文' THEN score ELSE 0 END) AS `语文`,
MAX(CASE SUBJECT WHEN '数学' THEN score ELSE 0 END) AS `数学`,
MAX(CASE SUBJECT WHEN '英语' THEN score ELSE 0 END) AS `英语`
FROM stu_mark GROUP BY sname;