3.MySQL基础——DQL
准备数据
在进行数据查询操作前,先来准备一些数据并存入到数据表中。
例:
(1)先来创建一个数据库goods,设定编码为utf8;
(2)在库中创建一个product表,字段有pid商品编号、pname商品名称、price商品价格、category_id商品分类;
(3)当成功添加数据内容后,查看数据结果。
##########################准备数据######################################
# 创建数据库
create database if not exists goods charset utf8;
# 使用数据库
use goods;
# 创建表
create table product(
pid int primary key auto_increment,
pname varchar(20),
price double,
category_id varchar(32)
) engine = InnoDB default charset utf8;
# 插入数据
insert into product values (1,'联想',8000,'c001');
insert into product values (2,'海尔',3000,'c001');
insert into product values (3,'雷神',5000,'c001');
insert into product values (4,'杰克琼斯',800,'c002');
insert into product values (5,'真维斯',200,'c002');
insert into product values (6,'花花公子',440,'c002');
insert into product values (7,'劲霸',2000,'c002');
insert into product values (8,'香奈儿',800,'c003');
insert into product values (9,'相宜本草',200,'c003');
insert into product values (10,'面霸',52,'c003');
insert into product values (11,'好想你枣',56,'c004');
insert into product values (12,'香飘飘奶茶',6,'c005');
insert into product values (13,'海澜之家',190,'c002');
insert into product values (14,'三只松鼠核桃',120,'c005');
insert into product values (15,'洞庭湖鱼',69,'c005');
insert into product values (16,'三文鱼',220,'c005');
insert into product values (17,'蓝月亮',54,'c002');
insert into product values (18,'越南青芒',78,'c002');
简单查询
简单查询有两种方式:
(1)查询所有数据
(2)按不同字段名来查询数据
# 查询表内所有数据
select * from 表名;
# 查询表内数据,以指定的列来显示结果
select [distinct] 字段名1,字段名2,... from 表名;
例:
(1)在数据库goods,查询商品表的所有数据信息;
(2)查询商品表的所有名称信息;
(3)查询商品名称、价格、分类的所有结果;
(4)将所有的商品名称、价格+20进行显示出结果;
(5)思考:如何去除重复的价格值并显示所有价格信息?
#########################简单查询#################################
# 使用库
use goods;
desc product;
# 1
select * from product;
# 2
select pname from product;
# 3
select pname,price,category_id from product;
# 4
select pname,price from product;
select pname,price+20 from product;
# 取外号/别名
# ① 字段名 as 别名
select pname,price+20 as price2 from product;
# ② 字段名 别名
select pname,price+20 price2 from product;
# 5
select distinct price from product; # 推荐
比较查询
select [*|字段名1, 字段名2, ...] from 表名 where 条件;
比较运算符有:
比较运算符 | 含义 |
---|---|
= | 等于 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
!= [或<>] | 不等于,忽略<> |
例:
(1)在操作商品表时,查询商品表中的所有商品信息;
(2)查询商品名称为"花花公子"的商品所有信息;
(3)查询价格为800的商品信息;
(4)查询价格不是800的所有商品信息;
(5)查询商品价格大于60元的所有商品信息;
(6)查询商品价格小于等于800元的所有商品信息。
##########################比较查询############################
# 使用库
use goods;
# 1
select * from product;
# 2
select * from product where pname = '花花公子';
# 3
select * from product where price = 800;
# 4
select * from product where price != 800; # 推荐
select * from product where price <> 800;
# 5
select * from product where price > 60;
# 6
select * from product where price <= 800; # 推荐
select * from product where 800 >= price;
范围查询
范围查询有两种方法:
# 1.in是用于非连续值的范围查询,语法:
select * from 表名 where 字段名 in (范围值1,范围值2,...);
# 2.between and 是用于值在连续范围的查询,语法:
select * from 表名 where 字段名 between 范围值1 and 范围值2;
例:
(1)在操作商品表时,查询商品价格是200或800的所有商品信息;
(2)查询商品价格在200-1000之间所有商品信息;
(3)思考:有其他方法来完成查询商品价格在200-1000之间所有商品信息
吗?
#########################范围查询###############################
# 使用库
use goods;
# 1
select * from product where price in (200,800);
# 2
select * from product where price between 200 and 1000;
select * from product where price between 200 and 800; # 200 <= x <= 1000
逻辑查询
select [*|字段名1, 字段名2, ...] from 表名 where 条件;
逻辑运算符有:
逻辑查询运算符 | 含义 |
---|---|
and | 与、和、且。 1.逻辑运算符and的前后一般是True或False的结果; 2.当两个条件都为True(真),整个操作结果才是True真。 |
or | 或、或者 1.逻辑运算符or的前后一般是True或False的结果; 2.当两个条件都为False(假),整个操作结果才是False假。 |
not | 一般情况下,要设定not取反,对整个操作结果做not处理。 |
例:
(1)查询商品价格在200到1000之间所有商品信息;
(2)查询商品价格是200或800的所有商品信息;
(3)查询价格不是800的所有商品;
(4)思考:如果要查询不是200或800的所有商品信息,该怎么做呢?
####################逻辑查询##########################
# 使用库
use goods;
# 1
select * from product where price between 200 and 1000;
# 逻辑运算+比较运算
select * from product where price >= 200 and price <= 1000;
select * from product where 200 <= price and price <= 1000; # 连贯/推荐
# 2
select * from product where price in (200,800);
select * from product where price = 200 or price = 800;
# 3
select * from product where price != 800; # 正向
select * from product where price = 800;
select * from product where not (price = 800); # 英文 + shift+9
# 4
select * from product where not (price = 200 or price = 800); # 理解
select * from product where price != 200 and price != 800;
select * from product where not (price in (200,800));
模糊匹配
select * from 表名 where 字段名 like '%某个字%';
或
select * from 表名 where 字段名 like '某个字_';
说明:%表示任意多个任意字符,_表示一个任意字符。
例:
(1)查询商品名称含有"香"字的所有商品信息;
(2)查询商品名称为三个字的商品信息;
(3)查询商品名称以"斯"结尾,并且是三个字的商品信息;
(4)思考1:查询以"香"开头,且是三个字的商品信息;
(5)思考2:查询以"香"开头的所有商品信息。
######################模糊查询##################################
# 使用库
use goods;
# 1
select * from product where pname like '%香%';
# 2
select * from product where pname like '___';
# 3
select * from product where pname like '__斯';
# 4
select * from product where pname like '香__';
# 5
select * from product where pname like '香%';
非空查询
非空运算符有:
非空运算符 | 含义 |
---|---|
is null | 判断为空。 |
is not null | 判断为非空。 |
例:
(1)将商品名称为"香奈儿"的分类category_id修改为null;
(2)查询分类为空的商品信息;
(3)查询分类不为空的所有商品信息。
########################非空查询##############################
# 使用库
use goods;
# 1
update product set category_id=null where pname = '香奈儿';
select * from product;
# 2
# select * from product where category_id = null; # 错误
select * from product where category_id is null;
# 3
select * from product where category_id is not null;
排序查询
# 查询
select [*|字段名1,字段名2,...|函数(...)] from 表名 where 条件 另外的要求;
# 排序查询指的是对某字段进行升序或降序的形式来查询结果
select * from 表名 where 条件 order by 字段名 [asc|desc];
说明:
(1)asc从小到大排列,即升序;
(2)desc从大到小排序,即降序;
(3)默认按照列值从小到大进行排序(即asc升序)。
例:
(1)按价格进行升序排序查询所有的商品信息;
(2)按价格进行降序排序查询所有的商品信息;
(3)按照价格升序排序查询名称中有"想"字的所有商品信息。
#########################排序查询################################
# 使用库
use goods;
# 升序
select * from product order by price asc; # 建议
select * from product order by price;
# 降序
select * from product order by price desc;
# 升序
select * from product where pname like '%想%' order by price asc;
聚合查询
select 函数(...) from 表名 [where 条件];
聚合函数有:
聚合函数 | 作用 |
---|---|
count(col) | 用于统计指定列不为null的总数据条数。 |
sum(col) | 用于计算指定列的数值和。 |
max(col) | 用于计算指定列的最大值。 |
min(col) | 用于计算指定列的最小值。 |
avg(col) | 用于计算指定列的平均值。 |
例:
(1)查询商品的总条数;
(2)查询商品价格的最大值;
(3)查询商品价格的最小值;
(4)加入where条件后,查询价格大于200的商品总条数;
(5)查询分类c001中所有商品的总和;
(6)查询分类为c002所有商品的平均价格。
############################聚合函数################################
# 使用库
use goods;
# 1
select count(*) from product;
# 2
select max(price) from product;
# 3
select min(price) from product;
# 4
select count(*) from product;
select * from product where price > 200;
select count(*) from product where price > 200;
# 5
select sum(price) from product where category_id='c001';
# 6
select avg(price) from product where category_id='c002';