SQL语句之简单查询、条件查询、排序查询、聚合查询、分组查询

查询的数据准备

创建商品表：

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');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

SQL语句_简单查询语句

查询语句:使用关键字select (选择,查询) from (从) where(哪里)

格式一:查询指定的字段
        select 字段1,字段2… from 表名;

– 查询pname字段和price字段的所有数据

SELECT pname,price FROM product;
SELECT price FROM product;

格式二:查询所有的字段
        select 列出所有的字段 from 表名;
        select * from 表名;

SELECT pid,pname,price,category_id FROM product;
SELECT * FROM product;

格式三:去掉重复的字段
        select [distinct] 字段名 from 表名;
        select [distinct] 字段名,字段名… from 表名;
        注意:
        distinct关键字后边只有一个字段,那么它会去除这个字段中重复的数据
        distinct关键字后边有多个字段,那么只能去除多个字段同时重复的数据

– 查询价格字段

SELECT price FROM product;

– 查询价格字段,去掉重复的数据

SELECT DISTINCT price FROM product;

– 查询pname和price,去掉pname和price同时重复的数据

SELECT DISTINCT pname,price FROM product;

格式四:别名查询
        1.给表起一个别名:在多表查询的时候会使用（在后续调用的时候方便调用）
                格式:
                        select *|字段 from 表名 [as] 别名;
        2.给表中的字段起别名
                格式:
                        select 字段[as] 别名,…字段[as] 别名 from表名;
        注意:
                1.给表/字段起别名,给他们起了一个其他的名字,本来的名字不会改变
                2.起别名,名字中有空格等特殊符号,别名必须使用引号包裹起来
                3.as关键字可以省略不写

– 1.给表起一个别名:在多表查询的时候会使用

SELECT * FROM product AS 商品表;
SELECT * FROM product p;

– 2.给表中的字段起别名

SELECT pname AS 商品名称,price 商品价格 FROM product;
SELECT pname AS 商品名称,price '商品 价格' FROM product;--（起别名,名字中有空格等特殊符号,别名必须使用引号包裹起来）

引申
数据库中是可以直接进行计算的

SELECT (5+10); – 15

SELECT(5/2); – 2.5

SELECT price+10000 FROM product; – 给product表中的price所有数据都加10000

SQL语句_条件查询语句

条件查询语句:查询出符合条件的数据
        格式:
                select *|字段 from 表名 [where 条件]（|表示或者的意思）

比较运算符:
        1.基本的比较运算符:< <= = > >= <>(!=)不等于
        2.between…and:查询某一个区间 相当于 >= && <=
        3.in(…):查询多个值 相当于 字段=80 or 字段=90 or 字段=100
        4.模糊查询:使用关键字like
                                                %:多个任意字符
                                                _:一个任意字符
        5.is null:查询为null的数据
                                逻辑运算符:
                                                与: and &&
                                                或: or ||
                                                非: not !

– 查询商品名称为“花花公子”的商品所有信息

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>=200 && price<=1000;

(重点！！！)

使用between…and优化(字段>=数值 and 字段<=数值)语句:查询一个区间,包含头,包含尾
使用between…and优化(字段>=数值 && 字段<=数值)语句:查询一个区间,包含头,包含尾

        注意:
                能对一个时间的区间进行查询 ‘2018-2-20’ ‘2019-5-30’
                小的值要写在前边

使用in(…)优化以上(字段=数值 or 字段=数值)语句
使用in(…)优化以上(字段=数值 || 字段=数值)语句

SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
SELECT * FROM product WHERE price BETWEEN 2000 AND 1000;

– 查询商品价格是200或800的所有商品

SELECT * FROM product WHERE price=200 OR price=800;
SELECT * FROM product WHERE price=200 || price=800;

使用in(…)优化以上sql语句

SELECT * FROM product WHERE price IN(200,800);
SELECT * FROM product WHERE price IN(200,800,1000,2000,3000,4000,5000);

模糊查询:使用关键字like
                                %:多个任意字符
                                _:一个任意字符

– 查询商品名称以"想"结尾的数据

SELECT * FROM product WHERE pname='想';
SELECT * FROM product WHERE pname LIKE '%想';

– 查询商品名称以"香"开头的数据

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;-- 是不空
SELECT * FROM product WHERE NOT(category_id IS NULL);
SELECT * FROM product WHERE !(category_id IS NULL);

SQL语句_排序查询语句

排序查询语句:对查询的结果进行排序(升序,降序)
        格式:
                select *|字段 from 表名 [where 条件] order by 排序的字段 [asc]|desc,…排序的字段 [asc]|desc;
                asc:升序,不写默认
                desc:降序
        注意:
                无论sql语句多么复杂,排序必须写在末尾
                注意排序的字段可以写多个,如果第一个字段值一样,按照第二个字段排序

– 查询商品的价格,按照价格升序排序

SELECT price FROM product ORDER BY price ASC;
SELECT price FROM product ORDER BY price;

– 查询商品的价格,按照价格降序排序

SELECT price FROM product ORDER BY price DESC;

– 查询商品价格大于1000的数据,按照价格降序排序

SELECT price FROM product WHERE price>1000 ORDER BY price DESC; 

– 在价格排序(降序)的基础上，以分类排序(降序)

SELECT * FROM product ORDER BY price DESC,category_id DESC;

– 显示商品的价格(去重复)，并排序(降序)

SELECT DISTINCT price FROM product ORDER BY price DESC;

SQL语句_聚合查询语句

聚合查询语句:对一列数据进行查询,结果返回一个单一的值,会忽略null值
        - count：统计指定列不为NULL的记录行数；
        - sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
         - max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
        - min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
        - avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

select count(字段|*),sum(字段),avg(字段),max(字段),min(字段) from 表名;

– 查询商品的总条数

SELECT COUNT(pid) FROM product; -- 13
SELECT COUNT(pname) FROM product;-- 13
SELECT COUNT(category_id) FROM product;-- 12
SELECT COUNT(*) FROM product;-- 13

– 查询价格大于200商品的总条数

SELECT COUNT(*) FROM product WHERE price>200;

– 查询所有商品的价格总和

SELECT SUM(price) FROM product; -- 17503

– 查询所有商品的价格平均值

SELECT AVG(price) FROM product; -- 1346.3846153846155

– 如果指定列类型不是数值类型，那么计算结果为0；

SELECT SUM(pname) FROM product; -- 0
SELECT AVG(pname) FROM product; -- 0

– 查询分类为’c001’的所有商品的总和

SELECT SUM(price) FROM product WHERE category_id='c001';-- 13000

– 查询分类为’c002’所有商品的平均价格

SELECT AVG(price) FROM product WHERE category_id='c002';-- 860

– 查询商品的最大价格和最小价格

SELECT MAX(price),MIN(price)FROM product;
SELECT MAX(price) 最大值,MIN(price) 最小值 FROM product;

SQL语句_分组查询语句

分组查询语句:可以把数据按照指定的字段进行分组
        格式:
                select 被分组的字段,count,max,min,avg,sum from 表名 [where 条件] group by 被分组的字段 [having 条件];
        注意:
                一般都会把分组的字段写在select的后边作为查询条件,方便我们查看结果

        (重点！！！)

        sql语句的执行流程
                先where条件过滤,在进行分组,在统计每组个数,在having条件过滤

– 统计各个分类商品的个数

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

– 统计各个分类商品的总和

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

– 统计各个分类商品的个数,且只显示个数大于1的信息

where和having的区别: （面试）

where过滤条件:在分组语句执行前过滤
having过滤条件:对分组的数据进行过滤(在分组之后过滤)

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