MySQL数据库DQL查询操作

DQL查询操作

概念：数据管理系统一个重要的功能就是数据查询，数据查询不应只是简单的返回数据库中存储的数据，还应该根据需要对税局进行筛选以及确定什么样的格式显示。

简单查询

方法：
select
[all | distinct]
<目标列的表达式1>[别名]，
<目标列的表达式2>[别名]……
from <表名或视图名>[别名]，<表名或视图名>[别名]……
[where <条件表达式>]
[group by <列名>]
[having <条件表达式>]
[order by <列名>[asc | desc]]
[limit <数字或者列表>];

注：“ [ ] ”中的条件均可省略

下面的例子涉及几种查询情况。查全表、指定字段查询、列别名查询、表别名查询、去重查询（重复行、重复列）

例如：

-- 简单查询
-- 1.查询所有的商品
SELECT pid,pname,price,category_id FROM product;
SELECT * FROM product;

-- 2.查询商品名
SELECT pname,price FROM product;
-- 3.别名查询 使用的关键字是as（as可省略）
-- 3.1表别名
 SELECT * from product as p;
 SELECT * from product  p;

-- 3.2列别名 SELECT * from product as p;
SELECT pname as '商品名',price '商品价格' FROM product;
-- 4.去掉重复值
SELECT DISTINCT price FROM product;
SELECT DISTINCT * FROM product;

-- 5.查询结果是表达式（运算查询）:将所有商品的加价10元进行显示
SELECT pname,price +10 new_price FROM product;

运算符

感念：数据库中的表结构确立后，表中的数据代表的意义就已经确定。通过mysql运算符进行运算，就可以获取到标结果以外的另一种数据。

MySQL支持四种运算符：

• 算术运算符
• 比较运算符
• 逻辑运算符
• 位运算符

算术运算符

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

例如：

算术运算符
SELECT 6+2;
SELECT 6-2;
SELECT 6*2;
SELECT 6/2;
SELECT 6%2;

-- 将所有商品的价格加十元
SELECT pname,price + 10 as new_price from product;
-- 将所有商品的价格上调10%
SELECT pname,price * 1.1 as new_price from product;

比较运算符

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

例如：

-- 查询商品名称为“海尔洗衣机”的商品所有信息：
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 >= 800;


-- 查询商品价格在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,5,20) as small_number;
SELECT LEAST(10,NULL,20) 
-- 如果求最小值时，有一个值为null，则不会进行比较，结果直接为null

-- 使用greatest求最大值
SELECT GREATEST(10,20,30);
SELECT GREATEST(10,NULL,30);
-- 如果求最大值时，有一个值为null，则不会进行比较，结果直接为null

逻辑运算符

逻辑运算符	说明
NOT 或 ！	逻辑非
AND 或&&	逻辑与
OR 或 丨丨	逻辑或
XOR	逻辑异或

位运算符

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

例如：

SELECT 3|5;
SELECT 3&5;
SELECT 3^5;
SELECT 3>>1;
SELECT 3<<1;
SELECT ~3;

排序查询

特点：
1.asc代表升序，desc代表降序，如果不写则默认升序
2.order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名
3.order by 子句，放在查询语句的最后面。LIMIT子句除外

方法：
select
字段名1，字段名2，……
from 表名
order by 字段名1[asc | desc ],字段名2 [asc | desc ]……

例如：

-- 使用价格升序（降序）
SELECT * FROM product ORDER BY price;
SELECT * FROM product ORDER BY price DESC;

-- 在价格排序（降序）的基础上，以分类排序（降序）
SELECT * FROM product ORDER BY price,category_id;
SELECT * FROM product ORDER BY price DESC,category_id DESC;

-- 显示商品的价格（去重复），并排序（降序）
SELECT DISTINCT price FROM product ORDER BY price DESC;

聚合查询

常见的聚合函数

聚合函数	作用
count()	统计指定列不为NULL的记录行数
sum())	计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
max()	计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
min()	计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
avg()	计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

例如：

-- 查询商品的总条数
SELECT COUNT(pid) FROM product;
SELECT COUNT(*) FROM product;


-- 查询价格大于200商品的总条数
SELECT COUNT(pid) FROM product WHERE price > 200;

-- 查询分类为'c001'的所有商品的总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';

-- 查询商品的最大价格
SELECT MAX(price) FROM product;

-- 查询商品的最小价格
SELECT MIN(price) FROM product;

SELECT MAX(price),MIN(price) FROM product;

-- 查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002'

聚合函数对NULL值的处理

1.count函数对NULL的处理
如果count函数的参数为*，则统计所有记录的个数。如果参数为某字段，不统计含null值的记录个数
2.sum和avg函数对null值的处理
这两个函数忽略了null值的存在，就好像该条记录不存在一样
3.max和min函数对null值的处理
max和min两个函数同样忽略null值的存在

例如：

CREATE TABLE test_null(
c1 VARCHAR(20),
c2 INT
);

INSERT INTO test_null VALUES('aaa',3);
INSERT INTO test_null VALUES('bbb',3);
INSERT INTO test_null VALUES('ccc',NULL);
INSERT INTO test_null VALUES('ddd',6);

SELECT COUNT(*),COUNT(1),COUNT(c2)FROM test_null;

SELECT sum(c2),MAX(c2),MIN(c2),AVG(c2) FROM test_null;

分组查询

概念：分组查询是指使用group by 字句对查询信息进行分组。

方法:
select 字段1，字段2……from 表名 group by 分组字段 having 分组条件;

例如：

SELECT COUNT(pid) FROM product GROUP BY category_id;

注：分组之后，select的后面只能写分组字段和聚合函数

分组后的条件筛选（having）

• 分组之后对统计结果进行筛选的话必须使用having，不能使用where
• where子句用来筛选from子句中指定的操作所产生的行
• group by子句用来分组where子句的输出
• having子句用来从分组的结果中筛选行

方法：
select 字段1，字段2……from 表名 group by 分组字段 having 分组条件;

例如：

SELECT category_id,COUNT(pid) cnt FROM product GROUP BY category_id HAVING cnt>4 ORDER BY cnt ;

分页查询

概念：分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。如淘宝商城里的商品，一页是显示不下的。

方法一：
select 字段1，字段2……from 表名 limit n
方法二：
select 字段1，字段2……from 表名 limit m,n
注：
m是从第几条索引看是，计算方式（当前页-1）*每页显示条数
n是表示查询多少条数据

例如：

-- 查询produce表的前五条记录
SELECT * FROM product LIMIT 5;
-- 从第四条开始显示，显示五条
SELECT * FROM product LIMIT 3,5;

insert into select语句

概念：将一张表的数据导入到另一张表中。
方法一：
insert into 表名2 select value1,value2,……from 表名1

例如：

CREATE TABLE product2(
pname VARCHAR(20),
price DOUBLE
);

INSERT INTO product2(pname,price) SELECT pname,price FROM product;

方法二：
insert into 表名2 select * from 表名1

例如：

CREATE TABLE product3(
category_id VARCHAR(20),
prouct_count INT
);

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

注：表名2必须存在

正则表达式

概念：正则表达式（regular expression）描述了一种字符串匹配的规则，正则表达式本身就是一个字符串，使用这个字符串来描述、用来定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常被用来检索、替换那些符合某个规则的文本。MySQL通过REGEXP关键字支持正则表达式进行字符串匹配。

格式：

模式	描述
^	匹配输入字符串的开始位置
$	匹配输入字符串的结束位置
.	匹配出"\n"之外的任何单个字符
[…]	字符集合。匹配所保护的任意一个字符。例如：'[abc]‘可以匹配’plain’中的’a’
[^…]	负值字符集合。匹配未包含的任意字符。例如：'[^abc]‘可以匹配’plain’中的’p’
p1丨p2丨p3	匹配p1或p2或p3。例如：'z丨food’能匹配‘z’或‘food’。‘（z丨f）food’则匹配‘zood’或‘food’
*	匹配前面的子表达式零次或多次。例如：zo*能匹配‘z’以及‘zoo’。等价于{0，}
+	匹配前面的子表达式一次或多次。例如：‘zo+’能匹配‘zo’以及‘zoo’，但不能匹配‘z’。+等价于{1，}
{n}	n是一个非负整数。匹配确定的n次。例如：‘o{2}’不能匹配‘Bob’中的‘o’，但是能匹配‘food’中的两个o
{n,m}	m和n均为非负整数，其中n<=m。最少匹配n次且最多匹配m次

例如：

-- ^在字符串开始出进行匹配
SELECT 'abc' REGEXP '^a';

SELECT * FROM product WHERE pname REGEXP '^海';


-- $在字符串末尾开始匹配
SELECT 'abc' REGEXP 'a$';
SELECT 'abc' REGEXP 'c$';

SELECT * FROM product WHERE pname REGEXP '水$';

-- 匹配任意字符,可以匹配除了换行符之外的任意字符
SELECT 'abc' REGEXP '.b';
SELECT 'abc' REGEXP '.c';
SELECT 'abc' REGEXP 'a.';

-- [...]匹配括号内的任意单个字符
SELECT 'abc' REGEXP '[xyz]';
SELECT 'abc' REGEXP '[xaz]';

-- [^...]注意^符合只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
SELECT 'a' REGEXP '[^abc]';
SELECT 'x' REGEXP '[^abc]';
SELECT 'abc' REGEXP '[^a]';

-- a*匹配0个活多个a，包括空字符串。 可以作为占位符使用，有没有指定字符都可以匹配到数据

SELECT 'stab' REGEXP '.ta*b';
SELECT 'stb' REGEXP '.ta*b';
SELECT '' REGEXP 'a*';

-- a+ 匹配1个或者多个a，但是不包括空字符

SELECT 'stab' REGEXP '.ta+b';
SELECT 'stb' REGEXP '.ta+b';

-- a? 匹配0个或者1个a
SELECT 'stb' REGEXP '.ta?b';
SELECT 'stab' REGEXP '.ta?b';
SELECT 'staab' REGEXP '.ta?b';

-- a1|a2，匹配a1或者a2

SELECT 'a' REGEXP 'a|b';
SELECT 'b' REGEXP 'a|b';
SELECT 'b' REGEXP '^(a|b)';
SELECT 'a' REGEXP '^(a|b)';
SELECT 'c' REGEXP '^(a|b)';

-- a{m}匹配m个a
SELECT 'auuuuc' REGEXP 'au{4}c';
SELECT 'auuuuc' REGEXP 'au{3}c';

-- a{m,},匹配m个或者更多个a
SELECT 'auuuuc' REGEXP 'au{3,}c';
SELECT 'auuuuc' REGEXP 'au{4,}c';
SELECT 'auuuuc' REGEXP 'au{5,}c';

-- a{m,n},匹配m到n个a，包含m和n
SELECT 'auuuuc' REGEXP 'au{3,5}c';
SELECT 'auuuuc' REGEXP 'au{4,5}c';
SELECT 'auuuuc' REGEXP 'au{5,10}c';

-- (a,b,c) 
-- abc作为一个序列匹配。不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去
-- 匹配就需要用到括号，所以括号适合上面的所有情况。

SELECT 'xababy' REGEXP 'x(abab)y';
SELECT 'xababy' REGEXP 'x(ab)*y';
SELECT 'xababy' REGEXP 'x(ab){1,2}y';

MySQL数据库DQL查询操作练习题

MySQL数据库DQL练习题