mysql查询

最新推荐文章于 2023-03-31 09:57:04 发布

ldd_unique

最新推荐文章于 2023-03-31 09:57:04 发布

阅读量668

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43206928/article/details/104977835

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、准备工作

创建数据库select_database
创建数据表product
并插入数据

#创建数据库
CREATE DATABASE select_database;
#切换数据库
USE select_database;
#创建商品表
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,'JACK JONES',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');

二、mysql查询语句

2.1 简单查询

#查询所有的商品
SELECT * FROM product;
#查询商品名和商品价格
SELECT pname,price FROM product;
#使用别名
SELECT pname AS '商品名称',price AS '商品价格' FROM product;
#as可以省略
SELECT pname '商品名称',price '商品价格' FROM product;
#''可以省略
SELECT pname 商品名称,price 商品价格 FROM product;
#表名也是可以起别名的，但是不能写''
SELECT pname 商品名称,price 商品价格 FROM product AS p;
#查询所有的价格(重复的只显示一个)--使用关键字：distinct
SELECT DISTINCT price FROM product;
#查询结果是表达式(运算查询):将所有商品的价格+10元进行显示
SELECT pname,price,price+10 FROM product;
SELECT pname AS '商品名称',price AS '涨价前',price+10 AS '涨价后' FROM product;

2.2 条件查询

   格式：
 	select 列名1,列名2... from 表名 where 条件
   条件：
      between ... and ...:显示某一区间的值(含头含尾)
      in(100,200):等于100或者等于200

练习：
1.查询商品名称为"花花公子"的商品所有信息
2.查询价格为800的商品
3.查询价格不是800的所有商品
4.查询商品价格大于60元的所有商品
5.查询商品价格在200到1000之间的所有商品
6.查询商品价格是200或800的所有商品

#1.查询商品名称为"花花公子"的商品所有信息
SELECT * FROM product WHERE pname='花花公子';
#2.查询价格为800的商品
SELECT * FROM product WHERE price=800;
#3.查询价格不是800的所有商品
SELECT * FROM product WHERE price!=800;
SELECT * FROM product WHERE price<>800;
SELECT * FROM product WHERE NOT(price=800);
#4.查询商品价格大于60元的所有商品
SELECT * FROM product WHERE price>60;
#5.查询商品价格在200到1000之间的所有商品
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
SELECT * FROM product WHERE price>=200 AND price<=1000;
#6.查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price IN(200,800);
SELECT * FROM product WHERE price=200 OR price=800;

2.3 模糊查询

 格式:
  使用关键字 like
 通配符：
  1.%:代表任意多个字符(0个,1个,2个...)
  2._:有且仅有1个字符
  select 列名1,列名2 ... from 表名 where ... like ...
  is null:判断是否为空
  is not null:判断是否不为空

练习：
1.查询含有’霸’字的所有商品
2.查询以’香’开头的所有商品
3.查询第二个字为’想’的所有商品
4.商品没有分类的商品
5.查询有分类的商品

#查询含有'霸'字的所有商品
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;
SELECT * FROM product WHERE NOT(category_id IS NULL);

2.4 排序查询

格式：
  select ... from 表名 where 条件 order by 排序字段1 asc|desc,排序字段2 asc|desc;
注意：
  1.asc：升序，不写默认就是asc
  2.desc：降
  3.order by：一般写在sql语句的最后

练习：
1.查询id为1,3,5,7的商品，并按价格降序排序
2.使用价格排序(降序)
3.在价格排序(降序)的基础上，以分类排序(降序)
4.显示商品的价格(去重复),并排序(降序)

#1.查询id为1,3,5,7的商品，并按价格降序排序
SELECT * FROM product WHERE pid IN(1,3,5,7) ORDER BY price DESC;
#2.使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;
#3.在价格排序(降序)的基础上，以分类排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC; #价格相同时，按照分类id排序
#4.显示商品的价格(去重复),并排序(降序)
SELECT DISTINCT price FROM product ORDER BY price DESC;

三、聚合函数

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

练习：
1.查询商品的总条数
2.查询商品的价格和
3.查询价格大于200商品的总条数
4.查询分类为’c001’的所有商品价格的总和
5.查询分类为’c002’所有商品的平均价格
6.查询商品的最大价格和最小价格
7.查询pid为1 3 7 商品价格的平均值
8.查询pid为1 3 7 14 商品价格的平均值
9.统计指定列不为null的记录行数
10.删除price为null的记录

#1.查询商品的总条数
SELECT COUNT(*) FROM product;
SELECT COUNT(pid) FROM product;
#2.查询商品的价格和
SELECT SUM(price) FROM product;
#3.查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price>200;
SELECT COUNT(pid) FROM product WHERE price>200;
SELECT COUNT(price) FROM product WHERE price>200;
#4.查询分类为'c001'的所有商品价格的总和
SELECT SUM(price) FROM product WHERE category_id='c001';
#5.查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id='c002';
#6.查询商品的最大价格和最小价格
SELECT MAX(price) AS '最大值',MIN(price) AS '最小值' FROM product;
#7.查询pid为1 3 7 商品价格的平均值
SELECT AVG(price) FROM product WHERE pid IN(1,3,7);#4000
#插入pid为14的商品
INSERT INTO product(pid,pname,price,category_id) VALUES(14,'卤煮',NULL,NULL);
#8.查询pid为1 3 7 14 商品价格的平均值
SELECT AVG(price) FROM product WHERE pid IN(1,3,7,14);#4000,price是null,不参与计算
#9.统计指定列不为null的记录行数
SELECT COUNT(category_id) FROM product WHERE category_id IS NOT NULL;
#10.删除price为null的记录
DELETE FROM product WHERE pid=14;

练习:
#查看所有商品的平均价格
#查看所有商品的平均价格，所有商品的平均价格>800的话，就显示出所有商品的平均价格
#查询商品名称为"花花公子"的商品所有信息

注意：

1.where后面不能写聚合函数
2.having后面可以使用聚合函数

 SQL语句书写顺序：select... from... where... group by... having... order by...
 SQL语句执行顺序：from... where... group by... having... select... order by...

#查看所有商品的平均价格
SELECT AVG(price) FROM product;
#查看所有商品的平均价格，所有商品的平均价格>800的话，就显示出所有商品的平均价格
SELECT AVG(price) FROM product HAVING AVG(price)>800; #having后面可以使用聚合函数
#查询商品名称为"花花公子"的商品所有信息
SELECT * FROM product WHERE pname="花花公子";

四、分组查询

统计各个分类商品的个数
1.分组：按照商品分类id,category_id
2. 聚合函数：个数count

SELECT category_id,COUNT(*) FROM product GROUP BY category_id;

统计各个分类商品的个数,且只显示个数大于1的信息
1.分组：按照商品分类id,category_id
2.聚合函数：个数count
3.条件：个数大于1 用到聚合函数，必须使用having

SELECT category_id,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*)>1;
SELECT category_id,COUNT(pid) FROM product GROUP BY category_id HAVING COUNT(pid)>1;

统计各个分类商品的平均价格
1.分组：按照商品分类id,category_id
2.聚合函数：平均价格avg

SELECT category_id,AVG(price) FROM product GROUP BY category_id;

统计各个分类商品的平均价格,且只显示平均价格>800的信息
1.分组：按照商品分类id,category_id
2.聚合函数：平均价格avg
3.条件：平均价格>800 用到聚合函数，必须使用having

SELECT category_id,AVG(price) FROM product GROUP BY category_id HAVING AVG(price)>800;

五、sql语句的书写顺序和执行顺序

书写顺序：
SELECT→FROM → JOIN → ON → WHERE → GROUP BY → HAVING → ORDER BY→ LIMIT
执行顺序：
FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → ORDER BY→ LIMIT

六、外键约束

完成商品分类和商品信息表创建
步骤：
1.创建商品分类表
2.向商品分类表中添加数据
3.创建商品信息表
4.给商品信息表添加外键约束
5.给商品信息表中添加数据

以下是具体的sql实现：
1.创建商品分类表

CREATE TABLE category(
 cid INT PRIMARY KEY,
 cname VARCHAR(100)
);

2.向商品分类表中添加数据

INSERT INTO category(cid,cname) VALUES(1,'家电');
INSERT INTO category(cid,cname) VALUES(2,'饮品');
INSERT INTO category(cid,cname) VALUES(3,'服装');
INSERT INTO category(cid,cname) VALUES(4,'水果');

3.创建商品信息表

CREATE TABLE products(
 pid INT PRIMARY KEY,
 pname VARCHAR(100),
 price INT,
 cid INT
);

4.给商品信息表添加外键约束
主表：商品分类表
主表中的主键：商品分类表中的cid
从表：商品信息表
从表中的外键：商品信息表中的cid

用商品分类表中的cid限制商品信息表中的cid
用主表的主键限制从表的外键

添加外键约束的格式：通过修改从表表结构的方式
alter table 从表名 add [constraint 外键约束名称] foreign key 从表名(从表中作为外键的字段) references 主表名(主表的主键)

ALTER TABLE products ADD CONSTRAINT fk_p_cid FOREIGN KEY products(cid) REFERENCES category(cid);

5.给商品信息表中添加数据

#目前已经添加外键约束，所以cid的值不可以随便写
#insert into products(pid,pname,price,cid) values(1,'可乐',3,1000);
INSERT INTO products(pid,pname,price,cid) VALUES(1,'冰箱',6000,1);
INSERT INTO products(pid,pname,price,cid) VALUES(2,'彩电',5000,1);
INSERT INTO products(pid,pname,price,cid) VALUES(3,'洗衣机',3000,1);
INSERT INTO products(pid,pname,price,cid) VALUES(4,'娃哈哈',6,2);
INSERT INTO products(pid,pname,price,cid) VALUES(5,'乳娃娃',6,2);
SELECT * FROM products