MySql学习-4.查询
1.数据查询语言DQL(Data Query Language)：
** 代码教程来自于：黑马程序员课程**：https://www.bilibili.com/video/BV1NK411W7Gz?p=71
SQL语句执行顺序：from > where > group by > 聚合函数 > having > select > order by > limit
分组之后：select 的后边只能跟分组字段和聚合函数
1.1 创建表：
#创建商品表： create table product( pid int primary key, 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,'杰克琼斯',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,'海澜之家',1,'c002');
1.2 语法：
select [distinct] *| 列名,列名 from 表 where 条件 **注意** 1.select 后写列名，*代表是所有列； 2.列名可以用as起别名，其出现在结果集中； 3.查询多个列，之间用逗号隔开；
1.3 简单查询：
#1.查询所有的商品. select * from product; #2.查询商品名和商品价格. select pname,price from product; #3.别名查询.使用的关键字是as（as可以省略的）. #3.1表别名: select * from product as p; #3.2列别名： select pname as pn from product; #4.去掉重复值. select distinct price from product; #5.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示. select pname,price+10 from product; # 消除重复的行：select distinct 列名 from 表名；
1.4 条件查询：
#查询商品名称为“花花公子”的商品所有信息： 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 '_想%'; #查询没有分类的商品 SELECT * FROM product WHERE category_id IS NULL; #查询有分类的商品 SELECT * FROM product WHERE category_id IS NOT NULL;
1.5 排序查询
通过order by语句，可以将查询出的结果进行排序。暂时放置在select语句的最后。 格式: SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC; ASC 升序 (默认) DESC 降序 #1.使用价格排序(降序) SELECT * FROM product ORDER BY price DESC; #2.在价格排序(降序)的基础上，以分类排序(降序) SELECT * FROM product ORDER BY price DESC,category_id DESC; #3.显示商品的价格(去重复)，并排序(降序) SELECT DISTINCT price FROM product ORDER BY price DESC;
1.6 聚合查询
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。 今天我们学习如下五个聚合函数： 聚合函数 作用 count() 统计指定列不为NULL的记录行数； sum() 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0 max() 计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算； min() 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算； avg() 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0 #1 查询商品的总条数 SELECT COUNT(*) FROM product; #2 查询价格大于200商品的总条数 SELECT COUNT(*) FROM product WHERE price > 200; #3 查询分类为'c001'的所有商品的总和 SELECT SUM(price) FROM product WHERE category_id = 'c001'; #4 查询分类为'c002'所有商品的平均价格 SELECT AVG(price) FROM product WHERE category_id = 'c002'; #5 查询商品的最大价格和最小价格 SELECT MAX(price),MIN(price) FROM product;
1.7 分组查询
分组查询是指使用group by字句对查询信息进行分组。 格式： SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件; 分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。 having与where的区别: 1).having是在分组后对数据进行过滤.,where是在分组前对数据进行过滤 2).having后面可以使用分组函数(统计函数),where后面不可以使用分组函数。 实例: #1 统计各个分类商品的个数 SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ; #2 统计各个分类商品的个数,且只显示个数大于1的信息 SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;
1.8 分页查询
分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。 SELECT 字段1，字段2... FROM 表明 LIMIT M,N M: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数 N: 整数，表示查询多少条数据 SELECT 字段1，字段2... FROM 表明 LIMIT 0,5 SELECT 字段1，字段2... FROM 表明 LIMIT 5,5 #查询product表的前5条记录 SELECT * FROM product LIMIT 0,5 # 第0页，显示5条
1.9 insert into select语句
# 基本语法： INSERT INTO table2 SELECT column_name(s) FROM table1; create table product2( pid int primary key, pname varchar(20), price double ); insert into product2 select pid,pname,price from product where category_id = 'c001';
2. 多表操作与多表查询
2.1 多表操作
“分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。 # 外键特点： 从表外键的值是对主表主键的引用。 从表外键类型，必须与主表主键类型一致。 #使用外键目的： 保证数据完整性 # 语法： alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键); [外键名称] 用于删除外键约束的，一般建议“_fk”结尾 alter table 从表 drop foreign key 外键名称
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 , 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.2 多表查询
2.2.1 交叉链接查询：
# 交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解],得到笛卡尔集 # 语法和代码 select * from A,B; select * from category,products
2.2.2 内连接查询：
# 隐式内连接：select * from A,B where 条件; # 查询那个分类下面有那些商品： select * from category as a, products as b where a.cid = b.categorary_id; # as 可以省略 select a.cname,b.cname from category as a, products as b where a.cid = b.categorary_id;
# 显示内连接：select * from A inner join B on 条件; select * from category as a inner join products as b on a.cid =b.categorary_id; # ,改为 inner join，where 改为on
2.2.3 外连接查询：
# 外连接查询(使用的关键字 outer join -- outer可以省略)
# 左外连接：left outer join # select * from A left outer join B on 条件; # 查询那个分类下面有那些商品： select * from category as a left outer join products as b on a.cid = b.categorary_id;
# 外连接：right outer join select * from A right outer join B on 条件;
select * from category a right outer join products b on a.cid = b.categorary_id;
#1.查询哪些分类的商品已经上架 #隐式内连接 SELECT DISTINCT c.cname FROM category c , products p WHERE c.cid = p.category_id AND p.flag = '1'; #内连接 SELECT DISTINCT c.cname FROM category c INNER JOIN products p ON c.cid = p.category_id WHERE p.flag = '1'; #2.查询所有分类商品的个数 #左外连接 INSERT INTO category(cid,cname) VALUES('c004','奢侈品'); SELECT cname,COUNT(category_id) FROM category c LEFT OUTER JOIN products p ON c.cid = p.category_id GROUP BY cname;
2.3 子查询
#子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。 # select ....查询字段 ... from ... 表.. where ... 查询条件 # ################查询“化妆品”分类上架商品详情#########################
# ----------------将子查询结果当作一个值------------------------------ # 多表查询 select * from products a, category b where a.cid = b.category_id and a.cname = '化妆品'
#子查询 select * from products p where p.category_id = (select cid from category where cname='化妆品') ; # c003，化妆品的cid
# 商品表中查询价格最高的商品:查询最高价格，让商品价格等于最高价格； select * from products p where p.price = (select max(price) from products ) ; #price = (最高)
# 将select的查询结果当作一张表
# ------------------将子查询的结果当作一个表-------------------------- select * from category where cname='化妆品'; # 先查分类表中是化妆品的 select * from products a, (select * from category where cname='化妆品') b where a.categorary_id = b.cid; # 再查到商品表中那些和我刚查到的匹配
# 查询化妆品和家电的详情 #-------------------将子查询结果当作多个值------------------------- select * from products p where p.category_id in (select cid from category where cname='化妆品' or cname = '家电') # 由于查到的cid是两个值，因此要用in select * from products a, (select * from category where cname='化妆品' or cname='家电') b where a.category_id=b.cid;
