MySQL3 DQL数据查询语言

SQL-DQL

DQL(Data Query Language 数据查询语言)。用途是查询数据库数据，如SELECT语句。是SQL语句中最核心、最重要的语句，也是使用频率最高的语句。其中，可以根据表的结构和关系分为单表查询和多表联查。

单表查询：针对数据库中的一张数据表进行查询，可以通过各 种查询条件和方式去做相关的优化。
多表联查：针对数据库中两张或者两张以上的表同时进行查询， 依赖的手段有复杂查询和嵌套查询。

SELECT   [DISTINCT]
 {*|表1.*|[ 表1.字段1 [as  字段别名1]
 [, 表1.字段2[as  字段别名2]][, …]]}
 FROM  表1 [as  表别名 ]
 [ left|right|inner join  表2   on  表之间
的关系 ]
 [ WHERE]
 [ GROUP BY ] 
[ HAVING]
 [ ORDER BY]
 [ LIMIT  {[ 位置偏移量,]行数}]; 

其中：
“[ ]”包含的内容可以省略；
“{ }”包含的内容必须存在；

必须按照该顺序使用

关键字：

DISTINCT：设定DISTINCT可以去掉重复记录。
AS：表名或者字段名过长时，可以用AS关键字起别名，方便操作。
GROUP BY：按组分类显示查询出的数据。
HAVING：GROUP BY分组时依赖的分组条件。
ORDER BY：将查询出来的结果集按照一定顺序排序完成。
LIMIT：限制显示查询结果的条数。

重要地位

数据库管理系统⼀个重要功能就是数据查询，SQL语句中最核⼼、最重要的语句，也是使⽤频率最 ⾼的语句.数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进⾏筛选以及确定数 据以什么样的格式显示。 MySQL提供了功能强⼤、灵活的语句来实现这些操作。 MySQL数据库使⽤select语句来查询数据。 应⽤

简单查询

通过简单查询，可以查询表中的所有列或者指定列，通过算术运算符、列别名以及消除重复⾏可以 改变查询结果的显示⽅式。

select简单查询

⭐查全表

select * from 表名

⭐查询指定的字段

select 字段名12....n   from 表名

语法：

select  [all|distinct]
  <目标列的表达式1> AS [别名],
  <目标列的表达式2> AS [别名]...
from <表名1或视图名1> [别名],<表名2或视图名2> [别名]...
[where <条件表达式>]
[group by <列名>]
[having <条件表达式>]
[order by <列名> [asc(从小到大排序)|desc(从大到小排序)]]
[limit <数字或列表>];

简化版查询所有列语法
select * | 列名 from 表 where 条件

说明：

1、 "*" 号代表指定表中的所有列名；  
2、 "|" 代表"或者" 的含义，所以SELECT⼦句后⾯可以既可以写"*" ，也可以写所有的列名，不能两种⽅式都写； 
3、如果使⽤第⼆种⽅式，每个列名之间必须要⽤逗号分隔，最后⼀个列名后⾯不写逗号； 
4、FROM⼦句后⾯写要查询的表名；  
5、SQL语句的最后需要加分号； 

数据准备

创建数据库和表：

CREATE DATABASE db3;
USE db3;
-- 简单查询
-- 创建商品表：
create table product(
 pid int primary key auto_increment, -- 商品编号
 pname varchar(20) not null , -- 商品名字
 price double,  -- 商品价格
 category_id varchar(20) -- 商品所属分类
);

添加数据：

--c001家用电器
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,'九阳电饭煲',200,'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);
insert into product values(NULL,'老北京方便面',66,'c004');
INSERT INTO product VALUES(NULL,'老北京方便面',66,'c004');

简单的查询

-- 1.查询所有的商品
select pid,pname,price,category_id from product;
-- 推荐使⽤,可读性⾼
select *  from product;
-- 2.查询商铺名和商品价格
select pname,price from product

⭐别名(AS)

select  empno  as  '员工号'，sal  工资  from emp

select  p.empno,p.sal    from emp p

列别名⽤来给查询语句中的列或者表达式重新命名，使语句的可读性更强。

语法：

SELECT   
	列名1 | 表达式1 [as]  [列别名1],
	列名2 | 表达式2  [as]   [列别名2],  
	...,
	列名n | 表达式n  [as]   [列别名n]  
FROM    table;

1.列别名可以直接写在列名或者表达式的后面，也可以添加as关键字，加不加as没有区别，通常不加。

2.如果select子句中有计算表达式，通常使用列别名。

-- 别名 字段名/表名 as(可省略) 别名
SELECT pname AS '商品名',price '价格' FROM product;

注意：

1.列别名中包含有空格

2.列别名中包含有特殊字符，如%,-等。

⭐消除重复行(DISTINCT去重)

重复⾏是查询结果中有完全相同的数据⾏,消除重复⾏是消除相同的查询结果，只保留⼀⾏重复数据，使⽤关键字 DISTINCT 来完成。

语法:

SELECT DISTINCT 列名1,列名2,......列名n FROM 表名;

例如：

-- 去重
-- 去重一列的数据
SELECT DISTINCT category_id FROM product;
-- 去重多列的数据
SELECT DISTINCT price,pname FROM product WHERE pname='老北京方便面' AND price=66;

也可以使⽤DISTINCT关键字消除多列重复数据（同时考虑所有列的数据重复）

算数运算符

简介

数据库中的表结构确⽴后，表中的数据代表的意义就已经确定。通过MySQL运算符进⾏运算，就可 以获取到表结构以外的另⼀种数据。 例如，学⽣表中存在⼀个birth字段，这个字段表示学⽣的出⽣年份。⽽运⽤MySQL的算术运算符⽤ 当前的年份减学⽣出⽣的年份，那么得到的就是这个学⽣的实际年龄数据。

MySQL支持四种运算符：

算数运算符、⽐较运算符、逻辑运算符、位运算符。

运算符优先级

优先级	运算分类	运算符举例
1	算术运算符	+，-，*，/，%
2	比较运算符	=，<>，<，>，<，<=，>=
3	特殊比较运算符	BETWEEN…AND…,IN,LIKE,IS NULL
4	逻辑非	NOT
5	逻辑与	AND
6	逻辑或	OR

0.优先级

乘除优先于加减

相同优先级按照从左至右的顺序依次计算

可以使用括号提升优先级

⭐1.算数运算符

可以在SELECT⼦句中使⽤算术运算符（只对数值型数据起作⽤），来改变查询结果的显示⽅式。其中⽀持的5种运算符包括：

运算符	作用
+	加
-	减
*	乘
/ 或 DIV	除法运算，返回商
% 或 MOD	求余运算，返回余数

-- 基本运算
-- 将所有的商品加价10元,只是在显示的时候进行操作,表中的数据不进行修改
SELECT pname,price,5*(price+10) AS new FROM product;

SELECT 3+5;
SELECT 3-5;
SELECT 3*5;
SELECT 3/5;
SELECT 3%5;

⭐2.比较运算符

比较运算符	说明
=	等于
< 和 <=	小于和小于等于
> 和 >=	大于和大于等于
<=>	安全的等于，两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
<> 或 !=	不等于
IS NULL 或 ISNULL	判断一个只是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN…AND…	判断一个值是否落在两值之间，包含边界值
IN	判断一个值是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

⭐3.逻辑运算符

（1）按位与运算&：如果左右两边是数字，进行位运算，同为1，则结果为1。(应用：a&1==0可判断是否为偶数，a&1==1可判断是否为奇数)
（2）按位或运算|：有一个为1，则结果为1.
（3）按位异或^:不同则为1，相同则为0.（应用：异或可进行两数快速交换）
（4）左移<<:每移动一次，该数值扩大为之前的两倍。
（5）右移>>：每移动一次，该数值缩小为之前的一半。

逻辑运算符	说明
NOT 或者 !	逻辑非
AND 或者 &&	逻辑与
OR 或者 ||	逻辑或
XOR	逻辑异或

⭐4.位运算符

位运算符是在⼆进制数上进⾏计算的运算符。位运算会先将操作数变成⼆进制数，进⾏位运算。然后再 将计算结果从⼆进制数变回⼗进制数。

位运算符	说明
|	按位或
&	按位与
^	按位异或
<<	按位左移
>>	按位右移
~	按位取反，反转所有比特

SELECT 3&5;--与
SELECT 3|5;
SELECT 3^5;-- 异或 不同为1,相同为0
SELECT 3>>5;
SELECT 3<<5;

空值

空值⽤NULL表示，表示⼀种⽆效的、未知的值，空值不是零，也不是空格。

SELECT *  FROM product WHERE pid>15;

查询结果如下：

空值参与运算

空值参与算术运算，运算后的结果仍为NULL —上述为字符串类型，可以单独修改查看

-- 如果某个值为NULL,NULL不参与运算,结果仍旧为NULL
INSERT INTO product VALUES(NULL,'三只松鼠坚果礼盒',NULL,'c004');
SELECT pname,price,price+10 AS '爪爪' FROM product;

insert into product values(NULL,' 三只松⿏坚果礼盒 ',NULL,'c004'); 
SELECT pid,pname,price+10,category_id FROM product WHERE pname = ' 三只松⿏坚果礼盒 ';

查询结果如下，可以看到空值参与算术运算后，结果仍为空值

条件查询

通过简单查询，可以查询表中的所有数据⾏。但⼤多数情况下，我们只需要查询符合某些特定条件的数据⾏，这就需要⽤到SQL语⾔中的条件查询来过滤。

普通条件查询

语法：

SELECT [DISTINCT] * | 列名 | 表达式 [别名] [,....]
FROM 表名
[WHERE 条件表达式1  逻辑运算符 条件表达式2];

说明:

1.条件查询使用WHERE子句来完成，WHERE子句必须紧跟在FROM子句后面。

2.条件表达式的格式为：列名 比较运算符 要比较的值，例如：deptno=10

3.常见的比较运算符包括:=、>、>=、<、<=、<>

含义为等于, 大于，大于等于，小于，小于等于，不等于

4.数值型、字符型、日期型数据都可以使用比较运算符进行比较。

5.字符型和日期型数据作为查询条件时，条件表达式右边的值，必须添加单引号

-- 条件查询
-- 1.查询商品价格为200的商品(数值型条件)
SELECT * FROM product WHERE price = 200;

-- 2.查询商品名为老北京方便面(字符型条件)
SELECT * FROM product WHERE pname='老北京方便面';

-- 3.查询商品价格不为200的商品信息(其他条件运算符)
SELECT * FROM product WHERE price != 200;

SELECT * FROM product WHERE price <> 200;

SELECT * FROM product WHERE NOT (price = 200);
-- 4.查询商品价格大于500的商品
SELECT * FROM product WHERE price > 500;

-- 5.查询商品价格在200~3000之间的商品
SELECT * FROM product WHERE price >=200 AND price<= 3000;
SELECT * FROM product WHERE price >=200 && price<= 3000;

特殊比较运算符

BETWEEN…AND…

使⽤BETWEEN … AND… 可以查询出某列的值在某个范围内(包括边界值)的数据⾏。

语法：WHERE 列名 BETWEEN 值1 AND 值2;

-- BETWEEN 200 AND 3000;包含边界值
SELECT * FROM product WHERE price BETWEEN 200 AND 3000;

IN

使⽤IN运算符可以查询出某列的值是否和给定集合内的任意⼀个值相等。

语法：WHERE 列名 IN （值1, 值2, …值n)

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

LIKE

使⽤LIKE运算符可以查询出某列的值是否满⾜关键字匹配，也叫模糊查询。

先介绍两个通配符：

% :⽤来匹配任何字符,进⾏模糊匹配

_ : 下划线_⽤来匹配单个字符

-- 7.like %占位符 占多位 _占位符 占一位
-- 以老字开头的
SELECT * FROM product WHERE pname LIKE '老%';
-- 以水字结尾的
SELECT * FROM product WHERE pname LIKE '%水';
-- 商品中第三个字为京字的商品
SELECT * FROM product WHERE pname LIKE '__京%';
-- 商品中包含老字的商品
SELECT * FROM product WHERE pname LIKE '%老%';

IS NULL

如果要查询某列的值是否为NULL，

使⽤ “ 列名 = NULL” 是查询不出来的。只能使⽤ “ 列名 IS NULL”

-- 8.is null 运算符
-- null和null不等,不能拿来判断
SELECT * FROM product WHERE category_id = NULL;
SELECT * FROM product WHERE category_id IS NULL;
SELECT * FROM product WHERE category_id IS NOT NULL;

least，greatest运算符

-- 9.最大值,最小值
SELECT LEAST(10,20,30);
-- 如果求最小值时有null,则直接返回null
SELECT LEAST(10,20,NULL) MIN;

SELECT GREATEST(10,20,30);
-- 如果求最大值时有null,则直接返回null
SELECT GREATEST(10,20,NULL);

SELECT LEAST(pid,price),pname FROM product;

⭐对查询结果排序【排序查询】

简介：

前⾯讲解的简单查询和条件查询SQL，结果集的显示顺序都不是我们指定的。在⼤多数情况下，我 们希望查询出来的结果集按照⼀定的顺序显示，⽐如按价格降序显示、在价格基础上分类排序等等。这就需要⽤到SQL语⾔中的对结果集排序的⼦句ORDER BY。

语法：

 SELECT   字段名 1 ，字段名 2 ， …… 
 FROM     表名 
 [WHERE   条件 ] 
 [ORDER BY  字段名 1 [ASC|DESC], 字段名 2[ASC|DESC]];

排序规则说明

1、ORDER BY⽤于⼦句中可以⽀持单个字段，多个字段，表达式，函数，别名等

2、可以按照3种⽅式进⾏排序：分别是按列名排序、按列别名排序、按列序号排序。

3、ASC表示按升序排序(默认值), DESC表示按降序排序。

-- OrderBy子句
USE db3;
-- ASC升序
SELECT * 
FROM product
ORDER BY price ASC;
-- DESC降序
SELECT * 
FROM product
ORDER BY price DESC;

4、可以同时按照多个列名进⾏排序

5、ORDER BY ⼦句必须写在SELECT语句的最后，LIMIT⼦句除外

6、数字升序排列⼩值在前，⼤值在后。即按照数字⼤⼩顺序由⼩到⼤排列。

7、⽇期升序排列相对较早的⽇期在前，较晚的⽇期在后。

8、字符升序排列按照字⺟由⼩到⼤的顺序排列，即由A-Z排列。

9、空值在升序排列中排在最前⾯，在降序排列中排在最后。

按列名排序

-- 进行排序,按照商品的价格来排序
-- 降序
SELECT *
FROM product
WHERE category_id IN('c001','c002')
ORDER BY price DESC;

-- 默认升序ASC 可写可不写
SELECT *,price*100 AS `猫猫`
FROM product
ORDER BY 5 ASC;

按别名排序

-- 按照别名排序
SELECT *,price*100 AS 猫猫
FROM product
ORDER BY 猫猫 DESC;

按列序号排序

-- 按照列的序号来进行排序,表的第几列
SELECT *
FROM product
ORDER BY 4;

按多列排序

-- 先按照价格降序排序,价格相同按照pid排序
SELECT * 
FROM product
ORDER BY price DESC,pid ASC;

⭐聚合函数

MIN，MAX，SUM，AVG，COUNT

MIN和MAX函数会忽略掉NULL值后,再进行计算

-- 字符型最大值
SELECT MAX(pname) FROM product; 

-- 数值型最小值
SELECT MIN(price) FROM product;

-- 数值型最大值+10
SELECT MAX(price+10) FROM product;

SUM求和，AVG求平均值，它俩也会忽略掉NULL值，再进行计算

-- SUM 求和
SELECT SUM(price) FROM product WHERE category_id='c001';
-- AVG 平均值
SELECT AVG(price) FROM product WHERE category_id='c001'; 

COUNT 求符合条件的记录[元组]数

-- COUNT(*)包含null值
SELECT COUNT(*) FROM product;-- 21
-- COUNT(category_id)不包含null值
SELECT COUNT(category_id) FROM product;-- 20

-- 去重后有多少行
SELECT COUNT(DISTINCT category_id) sumID FROM product;

聚合函数处理NULL值的小测试

CREATE TABLE test_null(
 c1 VARCHAR(20),
 c2 int
)

INSERT INTO test_null VALUES('a',3);
INSERT INTO test_null VALUES('b',3);
INSERT INTO test_null VALUES('c',NULL);
INSERT INTO test_null VALUES('d',6);

-- 12 3.0 6 3
SELECT SUM(c2),AVG(IFNULL(c2,0)),MAX(c2),MIN(c2)
FROM test_null;


-- 4 3
SELECT COUNT(*),COUNT(c2)
FROM test_null;

⭐Group By 分组

- group by 分组,select后面只能跟分组的列以及聚合函数
SELECT category_id,COUNT(*) 总和
FROM product
WHERE category_id IN('c001','c002')
GROUP BY category_id
ORDER BY 总和 DESC;
-- 多列分组:根据商品的分类和商品的价格进行分组
SELECT category_id,price,COUNT(*)
FROM product
WHERE category_id IN('c001','c003')
GROUP BY category_id,price

Having是分组后进行筛选

-- 分组后进行筛选
-- 展示商品数>=2的商品分类和价格
SELECT category_id,price,COUNT(*) SUM
FROM product
WHERE category_id IN('c001','c003')
GROUP BY category_id,price
HAVING SUM>=2;

⭐分页查询LIMIT

语法：

SELECT * FROM product LIMIT page;

page 展示多少条数据，索引从0开始

-- 分页查询
-- 展示5条数据,索引从0开始
SELECT * FROM product LIMIT 5;

SELECT * FROM product LIMIT start,page

start 表示开始的下标：

计算公式：(第几页-1)*每页最多显示几条

page:每页最多显示几条

-- 第一个参数表示开始的元组数,第二个表示最多显示多少条
SELECT * FROM product LIMIT 5,5

⭐SQL语法顺序最终版

select  [all|distinct]
<目标列的表达式1> AS [别名],
<目标列的表达式2> AS [别名]...
from <表名1或视图名1> [别名],<表名2或视图名2> [别名]...
[where <条件表达式>]
[group by <列名>]
[having <条件表达式>]
[order by <列名> [asc(从小到大排序)|desc(从大到小排序)]]
[limit <数字或列表>];

⭐执行顺序

通过FROM子句中找到需要查询的表，
通过WHERE子句进行非聚合函数筛选判断:
通过GROUP BY子句完成分组操作;
通过聚合函数完成计算操作
通过HAVING子句完成组函数筛选判断，
通过SELECT子句选择显示的列或表达式及组函数，
通过ORDER BY子句进行排序操作。
通过LIMIT子句进行分页操作