MySQL数据库基础操作(四)

MySQL查询

一、简单查询

语法格式

select 
	[all|distinct]
	<目标列的表达式1> [别名]
	<目标列的表达式2> [别名]...
from <表名或视图名> [别名],<表名或视图名> [别名]...
[where <条件表达式>]#查询条件
[group by <列名>]#分组
[having <条件表达式>]#对分组之后的结果进行筛选
[order by <列名> [asc|desc]]#降序或升序
[limit <数字或者列表>];

简化语法格式

select *| 列名 from 表 where 条件

查询所有商品

select * from product;

查询商品名和商品价格

select panme,price from product;

别名查询(使用关键字as–as可省略)

表别名

select * from product as p; 
#或者可以省略as
select * from product p;
#当出现多张表时别名可以起到一个区分属性值的作用
select p.id,u.id from product p,user u;

列别名

select pname as '商品名',price '商品价格' from product;

去掉重复值

#关键字distinct
select distinct price from product;

查询结果是表达式(运算查询)：将所有结果加价10元显示

select pname,price +10 new_price from product;

二、运算符

算数运算符

将每件商品价格上调10

select pname,price + 10 as neew_price from product;

将每件商品价格上调10%

select pname,price * 1.1 as new_price from product;

比较运算符

逻辑运算符

查询商品名为“亚瑟士跑鞋”的商品所有信息

select * from product where pname = '亚瑟士跑鞋';

查询价格为500的商品

select * from product where price = 500;

查询价格不是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 >= 60;

查询商品价格是50-1000之间的所有商品

select * from product where price between 50 and 1000;
select * from product where price >= 50 and price <= 1000;
select * from product where price >= 50 && price <= 1000;

查询商品价格是200或者800所有商品

select * from product where price between 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 8 from product where pname like '_瑟%';

查询category_id 为null的商品

select * from product where category_id is null;
#查询不为null的商品
select * from product where category_id is not null;

使用least求最小值

select least(10,5,30) as small_number;
#如果使用least求最小值或最大值有一个值为null，那结果直接为null。
#使用min和max求最值那门会忽略值为null的那行数据，avg同样会忽略
aaa   6
bbb   3
ccc   NULL
ddd   3
max=5 min=3 avg=4 least=null
#要想null不被忽略可以在建表时添加default 0

使用greatest求最大值

select greatest(10,5,30) as small_number;

位运算符

排序查询

语法格式

select
	字段名1,字段名2,.....
from 表名
order by 字段名1 [asc|desc], 字段名2[asc|desc]...

特点

1.asc代表升序，desc代表降序，如果不写默认升序

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

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

使用价格排序（降序）

select * from product order by price desc;

在价格降序的基础上，以分类排序（降序）

select * from product order by price desc,category_id desc;

去重显示商品的价格，并降序排序

select distinct price from product order by price desc;

三、聚合查询

查询商品的总条数

select count(*) from product;

查询价格大于200商品的总条数

select count(pid) from product where price > 200;

查询分类为‘c001’的商品总和

select sum(price) from product where category_id = 'c001';

查询商品的最大、最小价格

select min(price) from product;
select max(price) from product;
select min(price) min_price,max(price) max_price from product;

查询分类为‘c002’所有商品的平均价格

select avg(price) from product where category_id = 'c002';

聚合查询–NULL值的处理

#如果使用least求最小值或最大值有一个值为null，那结果直接为null。
#使用min和max求最值那门会忽略值为null的那行数据，avg同样会忽略
aaa   6
bbb   3
ccc   NULL
ddd   3
max=5 min=3 avg=4 least=null
#要想null不被忽略可以在建表时添加default 0

四、分组查询

分组查询–group by

语法格式

select   字段1，字段2... from 表名 group by 分组字段;

统计各个分类商品的个数

#注意   分组之后   select的后面只可以写分组字段和聚合函数
select category_id,count(pid) from product group by category_id;

#学号    年龄   名字    省    市     县
#select  from 表名 group by 省，市，县；
#多列分组查询，结果只有全部相同才进行统计

分组之后的条件筛选–having

语法格式

select   字段1，字段2... from 表名 group by 分组字段   having   分组条件;

统计各个分类商品的个数，且只显示个数大于4的信息

select category_id,count(*) from product group by category_id having count(*) >4;

#SQL执行顺序：from--->group by--->count(*)--->select--->having--->order by

分页查询-limit

语法格式

#显示前n条
select 字段1，字段2... from 表名 limit n;
#从第4条开始显示，显示5条
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;
#这里的3是下标，下标从0开始

分页显示

select * from product limit 0,60;   #第1页
select * from product limit 60,60;   #第2页
select * from product limit 120,60;   #第3页
select * from product limit (n-1)*60,60;   #第n页--（当前页-1）*每页显示条数

INSERT INTO SELECT语句(表导入语句)

简介

将一张表的数据导入另一张表中，可以使用insert into select语句。

语法格式

insert into Table2(field1,filed2...) select value1,value2... from TAble1;
或者
insert into Table2 select * from Table1
#要求目标表Table2必须存在，Table1查询的字段在Table2中一定要有相对应的字段

查询表product中catepory_id字段分类数量导入数据到表product2

create table product2{
	catepory_id varchar(20),
	product_count int
};

insert into prduct2 select category_id , count(*) from product group by category_id;

select * from product2;