一、DQL操作
1、认识DQL
数据查询操作,根据需求对数据进行筛选已确定数据以什么样的格式显示。
语法格式
select
[all|distinct]
<列1> [别名],
<列2> [别名],
from <表名和视图名> [别名],<表名和视图名> [别名] ...
[where<条件表达式>]
[group by <列名>
[having <条件表达式>]]
[order by <列名> [asc|descc]]
[limit <数字或者列表>];
select *|列名 from 表 where 条件;
示例:
-- 查询所有商品
select * from product;
-- 查询商品名和商品价格
select pname,price from product;
-- 别名查询,使用的关键字是as(as 可以省略的)
-- 表别名
select * from product as p;
-- 列别名
select pname as pn from product;
-- 去掉重复值
select distinct price from product;
-- 查询结果是表达式(运算查询),将所有商品的价格+10缘进行显示
select pname,price+10 as '新价格' from product;
2、运算符
数据库中的表结构确立后,表中的数据代表的意义就已经确定。通过MySQL运算符进行运算,就可以获取到表结构以外的另一种数据。
1)算术运算符
示例:
-- 将每件商品的价格加10
select pname,price + 10 as new_price from product;
-- 将所有商品的价格上调10%
select pname,price * 1.1 as new_price from product;
运行结果
2)比较运算符
示例:
-- 查询商品名称为“海尔洗衣机”的商品所有信息
select * from product where pname = '海尔洗衣机';
-- 查询介个为800的商品
select * from product where price = 800;
-- 查询价格不足800的所有商品
select * from product where 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 <=1000;
select * from product where price >=200 && price <= 1000;
-- 查询价格在800或200的商品
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;
%用来匹配任意字符,相当于空着的,x裤x,(裤在名字中间出现)
x裤,(裤子在最后)
裤x,(裤子在前面出现)
'_蔻%'是指第二个字为蔻,且后面还有其他文字
-- 查询category_id为null的商品
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,50);
如果求最小值时,有值为null,则不会进行比较,结果直接为null。
-- 使用greatest求最大值
select greatest(10,5,50) as '最大值';
3)逻辑运算符
与运算
-- 与运算
select 3 & 5;
0011
0101
------
0001
或运算
-- 或运算
select 3 | 5;
0011
0101
------
0110
4)位运算符
是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数,进行位运算。然后再将计算结果从二进制数变回十进制数。
位异或(相同为0,不同为1)
-- 位异或
select 3^5;
0011
0101
------
0110
位右移
-- 位右移
select 3>>1;
0011
00011
-------->向右移动一位
0001
位左移
select 3<<1;
0011
00110
<------
0110
位取反
select ~3;
位数在4个一下弄
0011
取反----
1100
3、排序查询(order by)
需要对读取的数据进行排序,使用order by子句即可进行排序,再返回搜索结果
语法
select
字段名1,字段名2,...
from 表名
order by 字段民1 [asc|desc],字段名2[asc|desc]......
特点
asc默认升序,desc代表降序,默认升序
order by 用于子句中可以支持单个字段,多个字段,标傲世,函数,别名
order by子句,放在查询语句的最后面。limit子句除外
示例:
-- 使用价格排序(降序)
select * from product order by price desc;
--在价格排序(降序)的基础上,已分类排序(降序)
select * from product order by price,category_id desc;
-- 显示商品的价格(去重复),并排序(降序)
select distinct price from product order by price desc;
4、聚合查询
纵向查询,是对一列的值进行计算,然后返回一个单一的值; 会忽略空值
1)count()
-- 查询商品的总条数
select count(pid) from product;
select count(*) from product;
>>18
>>18
2)sum()
-- 查询分类为’c001‘的所有商品价格的总和
select sum(price) from product where category_id = 'c001';
3)max()、min()
-- 查询商品的最大价格
select max(price) from product;
-- 查询商品的最小价格
select min(price) from product;
-- 也可以写成
select max(price),min(price) from product;
4)avg()
-- 查询分类为‘c002’所有商品的平均价格。
select avg(price) from product where category_id = 'c002';
5)NULL值的处理
示例:
-- 测试对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;
5、分组查询
1)group by
分组查询是指使用group by 子句对查询信息进行分组
格式:
select 字段1,字段2。。。 from 表名 group by 分组字段 having 分组条件
如果要进行分组的话,则使用select子句之后,只能出现分组的字段和统计函数,其他的字段不能出现
操作:
-- 统计各个分类商品的个数
select category_id,count(*) from product group by category_id;
流程:from product----group by------count(pid)--------select --------c00x----数量
2)分组查询-having
分组之后的条件筛选-having
分组之后对统计结果进行筛选的话必须使用having,不能使用where
where子句用来筛选from子句中指定的操作所产生的行
group by 字句用来分组where子句的输出
having子句用来从分组的结果中筛选行
格式
select 字段1,字段2... from 表名 group by 分组字段 having 分组条件;
操作
-- 统计各个分类商品的个数,且只显示个数大于4的信息
select category_id,count(*) from product group by category_id having count(*) >1;
流程: from product----group by------count(pid)--------select --------c00x----数量---having
3)分页查询-limit
用于数据量巨大,显示屏长度有限,对数据需要采取分页显示。(从0开始)
格式
-- 方法1 显示前n条
select 字段1,字段2... from 表名 limit n;
-- 方式2 分页显示
select 字段1,字段2... from 表名 limit m,n;
m:整数,表示从第几条索引开始,计算方式(当前页-1)*每页显示条数
n:整数,表示查询多少条数据
操作
-- 查询product 表的前5条记录
select * from product limit 5;
-- 从第4条开始显示,显示5条
select * from product limit 3,5;
对于分页的理解:
4)insert into select语句
将一张表的数据导入到另一张表中,可以使用insert into select 语句(表2必须存在)
格式
insert into table2() select value1,value2,。。。。from table1;
或者:
insert into table 2 select * from table1;
示例1:
-- insert into table select 。。。
create table product2(
pname varchar(20),
price double
);
insert into product2(pname,price) select pname,price from product;
select * from product2;
示例2:
create table product3(
category_id varchar(20),
product_count int
);
insert into product3 select category_id,count(*) from product group by category_id;
select * from product3;