MySql查询DQL知识点

1.基本查询语法格式

--语法格式
select
    [all|distinct\
    <目标列的表达式1>[别名],
    <目标列的表达式2>[别名]...
from <表名或视图名>[别名],<表名或视图名>[别名]...
[where<条件表达式>]
[group by<列名>]
[having <条件表达式>]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];

--简化版
select *|列名 from 表 where 条件

练习题：

--创建plan计划表
create table plan(
	time date,
	plan_name varchar(30),
	plan_id int(15),
	consumption decimal(10,2),
	imp int,
	cilck int,
	ctr varchar(5),
	cpc decimal(5,2),
	cpm decimal(5,2),
	givelike int,
	comments int,
	collection int,
	focus int,
	share int,
	interactive int,
	cpe decimal(5,2),
	private_number smallint,
    messages_number int,
	private_cost decimal(5,2),
	open_number smallint,
	open_cost decimal(5,2)
);

--导入cvs数据源文件
--简单查询
--1.查询所有的商品
select * from plan;
--2.查询计划名称、私信人数、私信成本
select plan_name,private_number,private_cost from plan;
--3.别名查询,关键词：as(as可以省略)
select * from plan p;
--列别名
select plan_name as '计划名称' ,private_number '私信人数' from plan;
--4.去掉列重复值
select distinct plan_name from plan;
--5.查询结果是表达式（运算查询），将所有的私信人数加5进行显示
select plan_name,private_number+5 as new_private_number from plan;

2.运算符

概念：通过mysql运算符进行运算，可以获取到表结构以外的另一种数据

mysql支持4中运算符：算术运算符、比较运算符、逻辑运算符、位运算符

算术运算符

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

比较运算符

比较运算符	说明
=	等于
< 和 <=	小于和小于等于
> 和 >=	大于和大于等于
<=>	安全的等于，两个操作码均为NULL时，期所得值为1;而当一个操作码为NULL时，其所得值为0
<> 或 !=	不等于
is null 或 isnull	判断一个值是否为NULL
is not null	判断一个值是否不为NULL
least	当有两个或多个参数时，返回最小值
greatest	当有两个或多个参数时，返回最大值
between and	判断一个值是否落在两个值之间
in	判断一个值是in列表中的任意一个值
not in	判断一个值不是in列表中的任意一个值
like	通配符匹配
regexp	正则表达式匹配

逻辑运算符

逻辑运算符	说明
not 或者 ！	逻辑非
and 或者 &&	逻辑与
or 或者 ||	逻辑或
xor	逻辑异或

运算符练习：

--比较运算符练习
--1.查询100-200之间的字段信息
select * from 表名 where 字段名 between 100 and 200;
select * from 表名 where 字段名 >= 100 and 字段名 <= 200;
select * from 表名 where 字段名 >= 100 && 字段名 <= 200;

--2.查询在200或者800的字段
select * from 表名 where 字段名 in(200,800);

--3.查询含有'瓦力'字的所有数据
select * from 表名 where 字段名 like '%瓦力%'; --%用来匹配任意字符

--4.查询以'瓦'子开头的所有数据
select * from 表名 where 字段名 like '瓦%';

--5.查询第二个字为'力'的所有数据
select * from 表名 where 字段名 like '_力'; --下滑线匹配单个字符

--6.查询计划名称为null的数据
select * from plan where plan_name is null;

--7.查询计划名称不为null的数据
select * from plan where plan_name is not null;

--8.使用least求最小值
select least(10,5,20) as small_number;
select least(10,null,30) as small_number; --如果求最小值时，有值为null，则不会比较，结果直接返回null

--9.使用greatest求最大值
select greatest(10,null,30) as big_number;

3.排序查询

--排序查询
select
    字段名1，字段名2,...
from 表名
order by 字段名1 [asc|desc],字段名2 [asc|desc]

特点：

1）asc代表升序，desc代表降序，不写默认是升序

2）order by用于子句中可以支持单个字段、多个字段、表达式、函数、别名

3）order by 子句，放在查询语句的最后面，limit子句除外

排序查询练习题：

--1.使用价格排序(降序)
select * from product order by price desc;

--2.多个字段排序（降序）
select * from product order by price desc,category_id asc;

--3.显示商品的价格（去重复），并降序
select distinct price from product order by price desc;

4.聚合查询

聚合查询

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

--示例
--1.查询商品的总条数
select count(pid) from product;
select count(*) from product;

--2.查询价格大于200商品的总条数
select count(pid) 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;

--6.查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id = 'c002';

5.聚合查询-NULL值的处理

1）count函数对null值的处理

如果count函数的参数为count(*),则统计所有记录的个数，而如果参数为某字段，不统计含null值的记录个数

2）sum、avg、max、min函数对null值的处理

忽略null值，直接计算

6.分组查询-group by

--分组查询
select 字段1，字段2... from 表名 group by 分组字段;
--示例：
--1.统计各个分类商品的个数
--如果要进行分组，则select子句之后，只能出现分组的字段和统计函数，其他字段不能出现
select category_id,count(pid) from product group by category_id; 

分组之后的条件查询-having

1)分组之后对统计结果进行筛选的话必须使用having，不能使用where

2)where子句用来筛选from子句中指定的操作所产生的行

3）group by子句用来分组where子句的输出

4）having子句用来从分组的结果中筛选行  

--分组之后的条件查询-having
select 字段1，字段2... from 表名 group by 分组字段 having 分组条件;

--1.统计各个分类商品的个数，且只显示个数大于4的信息
--sql执行顺序：from -> group by -> count(pid) -> select -> having -> order by
select
 category_id,count(pid) as cnt 
from 
    product 
group by 
    category_id 
having
    cnt > 4
order by
    cnt;

7.分页查询-limit

--分页查询
--方式1：显示前面n条
select 字段1，字段2... from 表名 limit n;
--方式2：分页显示
--m:整数，表示从第几条索引开始，计算方式（当前页-1）*每页显示条数
--n:整数，表示查询多少条数据
select 字段1，字段2... from 表名 limit m,n;
--示例：
--1.查询product表前5条数据
select * from product limit 5;
--2.从第4条开始显示，显示5条
select * from product limit 3,5;

8.insert into select语句

概念：将一张表的数据导入到另一张表中

--数据表转移
insert into table2(field1,field2,...) select value1,value2,.. from table1

--方式2：
--表2必须存在
insert into table2 select * from table1;

9.正则表达式

概念：匹配符合规则的文本

模式	描述
^	匹配输入字符串的开始位置
$	匹配输入字符串的结束位置
.	匹配除“\n”之外的任何单个字符
[...]	字符集合，匹配所包含的任意一个字符，例如'[abc]'可以匹配“plain”中的'a'
[^...]	负值字符集合，匹配未包含的任意字符例如'[^abc]可以匹配“plain”中的'p'
p1|p2|p3	匹配p1或p2或p3，例如'z|food'能匹配‘z’或'food','(z|f)ood'能匹配‘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次