mysql查询DQL&多表关系

最新推荐文章于 2023-11-14 14:10:37 发布

金钰～

最新推荐文章于 2023-11-14 14:10:37 发布

阅读量1.6k

点赞数 1

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_42105648/article/details/104010050

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

mysql查询DQL&多表关系

能够使用SQL语句查询数据
能够使用SQL语句进行条件查询
能够使用SQL语句进行排序
能够使用聚合函数
能够使用SQL语句进行分组查询
能够完成数据的备份和恢复
能够使用可视化工具连接数据库,操作数据库
能够说出多表之间的关系及其建表原则
能够理解外键约束

第一章SQL语句(DQL)

1.1 DQL准备工作和语法

准备工作

#创建商品表：
create table product(
  pid int primarykey,
  pname varchar(20),
  price double,
  category_id varchar(32)
);
INSERT INTO product(pid,pname,price,category_id)VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id)VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id)VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id)VALUES(4,'JACKJONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id)VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id)VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id)VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id)VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id)VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id)VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id)VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id)VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id)VALUES(13,'果9',1,NULL);

1.2简单查询

练习

#注意:工作很少用,查询的数据多,效率慢
-- 查询所有的商品
select * from product;

-- 查询商品名和商品价格.
select pname,price from product;

#注意:别名只是对查询的结果临时起一个名字,不会改变表中原有的名字
#注意:别名中如果有特殊符号或者空格,那么必须使用引号包裹起来 
	  
-- 别名查询.使用的关键字是as（as可以省略的）.表别名:
select * from product as p;
-- 别名查询.使用的关键字是as（as可以省略的）.列别名:
select pname as pn from product;

#注意:distinct关键字之后只有一个字段,那么就会过滤掉这个字段中重复的数据
#注意:distinct关键字之后如果有多个字段,那么多个字段必须同时满足才会过滤掉

-- 查询商品价格,过滤掉重复的价格
SELECT DISTINCT price FROM product;
-- 查询商品名称和价格,过滤调用名称和价格同时重复的数据
SELECT DISTINCT pname,price FROM product;

-- 查询语句中,可以直接进行数据计算
select pname,price+10 from product;

1.3 条件查询

比较运算符	< <= = = <>	大于、小于、大于(小于)等于、不等于
	`BETWEEN ...AND...`	显示在某一区间的值(含头含尾)
	`IN(set)`	显示在in列表中的值，例：in(100,200)
	`LIKE ‘张 pattern’`	模糊查询，Like语句中，% 代表零个或多个任意字符，_ 代表一个字符，例如： `first_name like '_a%';`
	`IS NULL`	判断是否为空
	`and`	多个条件同时成立
	`or`	多个条件任一成立
	`not`	不成立，例： `where not(salary>100);`

练习

-- 查询商品名称为“花花公子”的商品所有信息：
SELECT * FROM product WHERE pname = '花花公子'

-- 查询价格为800商品
SELECT * FROM product WHERE price = 800

-- 查询价格不是800的所有商品
SELECT * FROM product WHERE price != 800
SELECT * FROM product WHERE price <> 800
SELECT * FROM product WHERE NOT(price = 800)

-- 查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;

-- 查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price >= 200 AND price <=1000;
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

-- 查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price = 200 OR price = 800;
SELECT * FROM product WHERE price IN (200,800);

-- 查询含有'霸'字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%';
-- 查询以'香'开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';
-- 查询第二个字为'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';
-- 查询商品名称是5个字符的所有商品信息
SELECT * FROM product WHERE pname LIKE '_____';

-- 商品没有分类的商品
SELECT * FROM product WHERE category_id IS NULL
-- 查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL

1.4 排序查询

通过order by语句，可以将查询出的结果进行排序。暂时放置在select语句的最后。

格式

SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
-- ASC 升序 (默认)
-- DESC 降序

练习

-- 使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;

-- 在价格排序(降序)的基础上，以分类排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;

-- 显示商品的价格(去重复)，并排序(降序)
SELECT DISTINCT price FROM product ORDER BY price DESC;

注意:无论sql语句多么复杂,排序语句必须写在sql语句的末尾

1.5 聚合查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

今天我们学习如下五个聚合函数：

count：统计指定列不为NULL的记录行数；
sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

练习：

-- 查询商品的总条数
SELECT COUNT(*) FROM product; -- 13

-- 查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price > 200;

-- 查询分类为'c001'的所有商品的总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';

-- 查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002';

-- 查询商品的最大价格和最小价格
SELECT MAX(price),MIN(price) FROM product;

1.6分组查询

分组查询是指使用group by字句对查询信息进行分组。

格式

SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件;

分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

having与where的区别:
- having是在分组后对数据进行过滤.
  
  where是在分组前对数据进行过滤
- having后面可以使用分组函数(统计函数)
  
  where后面不可以使用分组函数
练习:

-- 统计各个分类商品的个数
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;

-- 按照商品类别分组,统计各个分组商品的个数,只查询每组商品价格大于200的商品
-- 先where过滤,在分组,在统计
SELECT category_id,COUNT(*) FROM product WHERE price>200 GROUP BY category_id;

-- 按照商品类别分组,统计各个分组商品的个数,且只显示统计个数等于3的信息
-- 先where过滤,在分组,在统计,在having过滤
SELECT category_id,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*)=3;

在这里插入图片描述

1.7分页查询

使用关键字:limit

格式:
- 只要前n条数据
  
  select *|字段 from 表名 limit n;
- 分页查询
  
  select *|字段 from 表名 limit m,n;
  
  m:每页开始行的索引
  
  n:每页显示的数量
注意:

数据库行的索引从0开始,列的索引从1开始
练习:

  -- 只要前6条数据
SELECT * FROM product LIMIT 6;
  
  -- 分页查询,每页显示5条,查询第1页
SELECT * FROM product LIMIT 0,5;
  
  -- 分页查询,每页显示5条,查询第2页
SELECT * FROM product LIMIT 5,5;
  
  -- 分页查询,每页显示5条,查询第3页
  SELECT * FROM product LIMIT 10,5;

在这里插入图片描述

第二章 SQLyog（MySQL图形化开发工具）

安装：

提供的SQLyog软件为免安装版，可直接使用

使用:

输入用户名、密码，点击连接按钮，进行访问MySQL数据库进行操作

在这里插入图片描述

在Query窗口中，输入SQL代码，选中要执行的SQL代码，按F8键运行，或按执行按钮运行。

第三章 SQL备份与恢复

3.1 SQL备份

数据库的备份是指将数据库转换成对应的sql文件

MySQL命令备份

数据库导出sql脚本的格式：

mysqldump -u用户名 -p密码 数据库名>生成的脚本文件路径

例如:

mysqldump -uroot -proot day04>d:\day03.sql

以上备份数据库的命令中需要用户名和密码，即表明该命令要在用户没有登录的情况下使用

可视化工具备份

选中数据库，右键 ”备份/导出” ，指定导出路径，保存成.sql文件即可。

在这里插入图片描述

3.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文件，执行即可。

在这里插入图片描述

第四章多表操作

实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、商品表 (products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。

4.1表与表之间的关系

一对多关系:
- 常见实例：客户和订单，分类和商品，部门和员工
- 一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键
多对多关系：
- 常见实例：学生和课程、用户和角色
- 多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键

在这里插入图片描述

一对一关系：(了解)
在实际的开发中应用不多.因为一对一可以创建成一张表.
- 两种建表原则：
- 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一unique。
- 外键是主键：主表的主键和从表的主键，形成主外键关系。

4.2 外键约束

现在我们有两张表“分类表”和“商品表”，为了表明商品属于哪个分类，通常情况下，我们将在商品表上添加一列，用于存放分类cid的信息，此列称为：外键

此时“分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现在这里插入图片描述

就是一对多关系。

外键特点：
- 从表外键的值是对主表主键的引用。
- 从表外键类型，必须与主表主键类型一致
声明外键约束

-- 语法：
alter table 从表 add [constraint][外键名称] foreign key (从表外键字段名) references 主表 (主表的主键字段名);

-- 创建表的时候添加外键约束
FOREIGN KEY(本表外键字段名) REFERENCES 主表(主表的主键字段名)

-- [外键名称]用于删除外键约束的，一般建议“_fk”结尾
alter table 从表 drop foreign key 外键名称

使用外键的目的
- 保证数据完整性

4.3 一对多操作

分析

在这里插入图片描述

category分类表，为一方，也就是主表，必须提供主键cid
products商品表，为多方，也就是从表，必须提供外键category_id

实现:分类和商品

/*
	1对多关系
	建表原则:从表使用主表的主键作为外键
	主表:分类表category
	从表:商品表products
*/
-- 创建主表:分类表category 字段:分类主键,分类名称
CREATE TABLE category(
	-- 分类主键
	cid INT PRIMARY KEY AUTO_INCREMENT,
	-- 分类名称
	cname VARCHAR(20)
);

-- 分类表添加数据
INSERT INTO category(cname) VALUES('服装'),('家电'),('化妆品'),('零食');

-- 创建从表:商品表products 字段:商品主键,商品名称,商品价格,商品分类
CREATE TABLE products(
	-- 商品主键
	pid INT PRIMARY KEY AUTO_INCREMENT,
	-- 商品名称
	pname VARCHAR(20),
	-- 商品价格
	price DOUBLE,
	-- 商品分类
	category_cid INT
);

-- 商品表添加数据
INSERT INTO products(pname,price,category_cid) VALUES('羽绒服',300,1),('秋裤',20,1);
INSERT INTO products(pname,price,category_cid) VALUES('空调',998,2),('手电筒',10,2);
INSERT INTO products VALUES(NULL,'香奈儿香水',800,3),(NULL,'大宝',8,3);

操作

/*
	添加外键约束:
	格式:使用修改表结构,添加列
		alter table 从表名 add [constraint 外键名称] foreign key(从表中的外键字段) references 主表名(主键);
		foreign:外部的
		key:关键字
	作用:保证数据的准确性和完整性
		主表中有的数据,从表可以有,可以没有
		主表中没有的数据,从表中也不能有
		删除主表中的数据,必须保证从表没有使用
			
*/

-- 给从表商品表中的字段category_cid添加外键约束,使用主表分类表的主键作为外键
ALTER TABLE products ADD CONSTRAINT products_fk FOREIGN KEY(category_cid) REFERENCES category(cid);

-- 主表中没有的数据,从表中也不能有
-- Cannot add or update a child row: a foreign key constraint fails
INSERT INTO products(pname,price,category_cid) VALUES('足球',200,5);


-- 删除主表中的数据,必须保证从表没有使用
-- 删除主表中cid为3的数据,从表正在使用,不能删除
-- Cannot delete or update a parent row: a foreign key constraint fails
DELETE FROM category WHERE cid =3;

-- 先删除从表中使用了cid为3的数据
DELETE FROM products WHERE category_cid=3;
-- 在删除主表中cid为3的数据,从表没有使用就可以删除
DELETE FROM category WHERE cid =3;

/*
	删除外键约束:使用修改表结构,删除列
		alter table 从表名 drop foreign key 外键名称;
*/
ALTER TABLE products DROP FOREIGN KEY products_fk;

多对多

分析

在这里插入图片描述

商品和订单多对多关系，将拆分成两个一对多。
products商品表，为其中一个一对多的主表，需要提供主键pid
orders 订单表，为另一个一对多的主表，需要提供主键oid
orderitem中间表，为另外添加的第三张表，需要提供两个外键oid和pid

实现：订单和商品

/*
	商品表和订单表的多对多关系
	建表原则:创建一张中间表,使用两个主表的主键作为外键
	主表: 商品表products 订单表orders
	中间表:products_orders
*/
-- 创建主表订单表orders 字段:订单主键,订单总金额
CREATE TABLE orders(
	-- 订单主键
	oid INT PRIMARY KEY AUTO_INCREMENT,
	-- 订单总金额
	totalPrice DOUBLE
);
-- 订单表添加数据
INSERT INTO orders(totalPrice) VALUES(2098),(38),(328);

-- 创建中间表:products_orders
CREATE TABLE products_orders(
	-- 商品主键(外键)
	products_pid INT,
	-- 订单主键(外键)
	orders_oid INT,
	-- 添加products_pid字段的外键约束,使用products表的pid作为外键
	FOREIGN KEY(products_pid) REFERENCES products(pid),
	-- 添加orders_oid字段的外键约束,使用orders表中的oid作为外键
	FOREIGN KEY(orders_oid) REFERENCES orders(oid)	
);

-- 中间表添加两个主表都有的数据
INSERT INTO products_orders VALUES(1,1),(3,1),(5,1);
INSERT INTO products_orders VALUES(2,2),(4,2),(6,2);
INSERT INTO products_orders VALUES(1,3),(2,3),(6,3);

操作

-- 中间表添加主表products不存在的数据
-- Cannot add or update a child row: a foreign key constraint fails 
INSERT INTO products_orders VALUES(8,1);

-- 中间表添加主表orders不存在的数据
-- Cannot add or update a child row: a foreign key constraint fails 
INSERT INTO products_orders VALUES(1,10);

-- 删除主表订单表中oid为3的数,中间表在使用,不能删除
-- Cannot delete or update a parent row: a foreign key constraint fails 
DELETE FROM orders WHERE oid=3;

-- 先删除中间表中使用了oid为3的数据
DELETE FROM products_orders WHERE orders_oid=3;
-- 再删除主表订单表中oid为3的数,中间表没有使用,可以删除
DELETE FROM orders WHERE oid=3;

金钰～

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql查询DQL&多表关系

day03-mysql查询DQL&多表关系能够使用SQL语句查询数据能够使用SQL语句进行条件查询能够使用SQL语句进行排序能够使用聚合函数能够使用SQL语句进行分组查询能够完成数据的备份和恢复能够使用可视化工具连接数据库,操作数据库能够说出多表之间的关系及其建表原则能够理解外键约束目录day03-mysql查询...
复制链接

扫一扫