目录
数据准备
-- 创建数据库
CREATE DATABASE IF NOT EXISTS mydb2; USE mydb2;
-- 创建商品表
CREATE TABLE product( pid INT PRIMARY KEY AUTO_INCREMENT, -- 商品编号 pname VARCHAR(20) NOT NULL, -- 商品名字 price DOUBLE, -- 商品价格 category_id VARCHAR(20) -- 商品所属分类 );
-- 添加数据
INSERT INTO product VALUES(NULL,'海尔洗衣机',5000,'c001'); INSERT INTO product VALUES(NULL,'美的冰箱',3000,'c001'); INSERT INTO product VALUES(NULL,'格力空调',5000,'c001'); INSERT INTO product VALUES(NULL,'九阳电饭煲',5000,'c001'); INSERT INTO product VALUES(NULL,'啄木鸟衬衣',300,'c002'); INSERT INTO product VALUES(NULL,'恒源祥西裤',800,'c002'); INSERT INTO product VALUES(NULL,'花花公子夹克',440,'c002'); INSERT INTO product VALUES(NULL,'劲霸休闲裤',266,'c002'); INSERT INTO product VALUES(NULL,'海澜之家卫衣',180,'c002'); INSERT INTO product VALUES(NULL,'杰克琼斯运动裤',430,'c002'); INSERT INTO product VALUES(NULL,'兰蔻面霜',300,'c003'); INSERT INTO product VALUES(NULL,'雅诗兰黛精华水',200,'c003'); INSERT INTO product VALUES(NULL,'香奈儿香水',350,'c003'); INSERT INTO product VALUES(NULL,'SK-II神仙水',350,'c003'); INSERT INTO product VALUES(NULL,'资生堂粉底液',180,'c003'); INSERT INTO product VALUES(NULL,'老北京方便面',56,'c004'); INSERT INTO product VALUES(NULL,'良品铺子海带丝',17,'c004'); INSERT INTO product VALUES(NULL,'三只松鼠坚果',88,NULL);
简化版查询语法:
select *| from 表 where 条件;
USE mydb2;
简单查询
1.查询所有的商品
SELECT * FROM product;
2.查询商品名和商品价格
SELECT pname,price FROM product;
3.别名查询(关键字as且可省略)
-
-- (1)表别名
SELECT * FROM product AS P; -- as 可省略
-
-- (2)列别名
SELECT pname AS '商品名',price AS '商品价格' FROM product;
4.去掉重复值(distinct)
SELECT DISTINCT price FROM product; -- 去掉价格这一列重复的值(某一列重复的值) SELECT DISTINCT * FROM product; -- 去掉某一行和另外一行数据全部重复的值
5.查询结果是表达式(运算查询):
-
-- (1)将所有商品加价十元进行显示
SELECT pname,price+10 new_price FROM product;
-
-- (2)将所有商品价格上调10
SELECT pname,price*1.1 AS new_price FROM product;
运算符操作
1.算数运算符
SELECT 6 + 2; SELECT 6 - 2; SELECT 6 * 2; SELECT 6 / 2;
2.比较运算符、逻辑运算符
-
-- 查询商品名为“海尔洗衣机”的商品所有信息
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 BETWEEN 200 AND 1000; SELECT * FROM product WHERE price >= 200 AND price <= 1000; SELECT * FROM product WHERE price >=200 && price <=1000;
-
-- 查询商品价格是200和800的所有商品
SELECT * FROM product WHERE price IN(200,800); SELECT * FROM product WHERE price =200 OR price =800; SELECT * FROM product WHERE price =200 || price =800;
-
-- 查询含有“裤”字的所有商品
SELECT * FROM product WHERE pname LIKE '%裤%'; -- %用来匹配任意字符
-
-- 查询以“海”字开头的所有商品
SELECT * FROM product WHERE pname LIKE '海%';
-
-- 查询第二个字为“蔻”的所有商品
SELECT * FROM product WHERE pname LIKE '_蔻%'; -- 下划线用来匹配任意单个字符串
-
-- 查询category_id为null的所有商品
SELECT * FROM product WHERE category_id IS NULL;
-
-- 查询category_id不为null分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL;
-
-- 使用least求最小值
SELECT LEAST(10,20,30,5) AS small_number; SELECT LEAST(10,20,30,NULL) AS small_number; -- 如果求最小值有一个值为null时,则不会进行比较,结果直接为null。
-
-- 使用greatest求最大值
SELECT GREATEST(10,20,30) AS big_number; SELECT GREATEST(10,NULL,30) AS bid_number; -- 如果求最大值有一个值为null时,则不会进行比较,结果直接为null。
-
-- 求价格最便宜的商品
SELECT MIN(price) FROM product;
-
-- 求价格最贵的商品
SELECT MAX(price) FROM product;
3.位运算符
SELECT 3 & 5; -- 位与 相同为1,不同为0 0011 0101 ----- 0001 SELECT 3 | 5; -- 位或 有一个为1,结果为1 0011 0101 ----- 0111 SELECT 3 ^ 5; -- 位异或 相同为0,不同为1 0011 0101 ---- 0110 SELECT 3 >> 1; -- 位右移 向右移一位 0011 ---> 0001 SELECT 3 << 1; -- 位左移 向左移一位 0011 ---> 0110 SELECT ~3; -- 位取反 0变1,1变0 00000000...0011 ---> 11111111...1100
排序查询 -> order by
1.使用价格排序(降序:desc)
SELECT * FROM product ORDER BY price DESC;
2.在价格排序(降序)的基础上,以分类排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;
3.显示商品的价格(去重复),并排序(降序)
聚合查询
count() sum() max() min() avg() 聚合查询的列中有null,则null不被统计
1.查询商品的总条目
SELECT COUNT(pid) FROM product; SELECT COUNT(*) FROM product;
2.查询价格大于200商品的总条目
SELECT COUNT(pid) FROM product WHERE price >200; SELECT COUNT(*) FROM product WHERE price > 200;
3.查询分类为‘c001’的所有商品的总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';
4.查询商品的最大价格
SELECT MAX(price) FROM product;
5.查询商品的最小价格
SELECT MIN(price) FROM product; SELECT MAX(price) max_price,MIN(price) min_price FROM product; -- 同时查询最大价格和最小价格
6.查询分类为‘c002’所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002';
分组查询 -> group by
-- 格式:select 字段1,字段2... from 表名 group by 分组字段 having 分组条件 -- 注意:分组之后select后面只能写分组字段和聚合函数
1.统计各个分类商品的个数
SELECT category_id,COUNT(pid) FROM product GROUP BY category_id;
2.统计各个分类商品的个数,且只显示个数大于四的商品信息
-- SQL执行顺序:from -> group by -> count(pid) -> select -> having -> order by SELECT category_id,COUNT(pid) COUNT FROM product GROUP BY category_id HAVING COUNT > 4 ORDER BY COUNT;
分页查询 -> limit
-- 格式1:select 字段1,字段2... from 表名 limit n 显示前n条 -- 格式2:select 字段1,字段2... from 表名 limit n,m 从第n(索引)+1条开始显示,显示m条
1.查询product表的前5条记录
SELECT * FROM product LIMIT 5;
2.从第4条开始显示,显示5条
SELECT * FROM product LIMIT 3,5;
insert_into_select语句
将一张表的数据导入到另一张表中
-- 创建一个新表product2
CREATE TABLE product2( pname VARCHAR(20), price DOUBLE );
-- 将product表中数据导入Product2表中
INSERT INTO product2(pname,price) SELECT pname,price FROM product; SELECT * FROM product2;
-- 创建一个新表product3
CREATE TABLE product3( categroy_id VARCHAR(20), product_count INT );
-- 将product表中数据导入Product3表中
INSERT INTO product3 SELECT category_id,COUNT(*) FROM product GROUP BY category_id; SELECT * FROM product3;