mysql查询
(数据在mysql查询数据准备文章中:)
单表查询
select fruits.* from fruits;#查询所有字段
select * from fruits; #查询所有字段
select f_name from fruits;#查询单一字段
SELECT f_name , f_price FROM fruits WHERE f_price =10.2 #查询制定记录
select f_name,f_price from fruits where f_name = ‘apple’ ; #查找名为apple的水果
- 带in关键字的查询,并且加上升序和降序的使用
select f_name,f_price from fruits where s_id in(101,102) order by f_name asc #in多个指定查询,并且升序
select f_name,f_price from fruits where s_id not in(101,102) order by f_name desc # 并且降序 (s_id不等于101和102)
带between and 的范围查询
select f_name,f_price from fruits where f_price between 2.00 and 10.20 #查询价格在2.00元到10.20元之间的数据
select f_name,f_price from fruits where f_price>2.00 and f_price<10.20 #这样写也可以和上面一行所查询出来的效果一致带like的字符串匹配查询
select f_name,f_price from fruits where f_name like ‘%g%’ #查询f_name包含g的记录
select f_name,f_price from fruits where f_name like ‘b%’ #查询以b开头的记录,同理结尾的字符也是一样原理查询null值
select c_id ,c_name,c_email from customers where c_email is null #查询email为null的数据带and 的多条件查询
select f_name,f_price from fruits where s_id =’101’ and f_price>=5 #多个条件查询带or 的多条件查询
select f_name,f_price from fruits where s_id =’101’ or s_id = ‘102’ #or 条件查询查询结果不重复
select distinct f_name,f_price,s_id,f_id from fruits #过滤重复结果记录多列排序
select f_name,f_price from fruits order by f_name ,f_price #先按f_name排序,在按f_price排序指定排序方向
参考2分组查询
select s_id,count(*) as total from fruits group by s_id #根据s_id对fruits 进行分组
:这使用了group_concat(x)函数 将s_id对应的多个供应商的水果显示出来
select s_id , group_concat(f_name) as Names from fruits group by s_id #group_concat(x)函数
-
依据s_id对f_name进行分组,f_name数量必须在2以上才可以显示出来
select s_id , group_concat(f_name) as Names from fruits group by s_id having count(f_name)>2#
group by 可以和 having一起限定显示记录所需满足的条件,只有满足条件的分组才被显出出来
select * from fruits group by s_id,f_name # 多字段分组
由结果可以看到先按照s_id进行分组,然后对f_name字段不同的取值进行分组
group by 和order by一起使用
select o_num, sum(quantity * item_price) as orderTotal from orderitems group by o_num having sum(quantity * item_price) >= 100 #查询价格大于100的订单号和总订单价格,可以看出结果没有按照一定的顺序排列
select o_num, sum(quantity * item_price) as orderTotal from orderitems group by o_num having sum(quantity * item_price) >= 100 order by orderTotal #
- 分页查询
select * from fruits limit 15,5 #((4-1)*5),5 从15条记录开始获取5条记录
使用集合函数查询
select count(*) as cust_number from customers #count()函数统计数据表包含的记录数
select sum(quantity) as items_total from orderitems group by o_num #sum() 求和函数
select avg(f_price) as avg_price ,s_id from fruits group by s_id #avg() 求平均值
select s_id , max(f_price) as max_price from fruits group by s_id # max() 最大值
select s_id , min(f_price) as min_price from fruits group by s_id # min() 最小值
连接查询(多表)
1):内连接查询
普通连接查询
SELECT fruits.s_id AS fruits_id , fruits.f_name AS fruits_name, fruits.f_price AS fruits_name , suppliers.s_name
FROM fruits,suppliers WHERE fruits.s_id = suppliers.s_id #
内连接查询
SELECT fruits.s_id AS fruits_id , fruits.f_name AS fruits_name , fruits.f_price AS fruits_name , suppliers.s_name
FROM fruits inner join suppliers on fruits.s_id = suppliers.s_id #
2):外连接查询
普通连接查询
select customers.c_id as customers_id, orders.o_num as orders_num from customers , orders where customers.c_id=orders.c_id #
LEFT JOIN 左连接
select customers.c_id as customers_id, orders.o_num as orders_num
from customers left outer join orders on customers.c_id=orders.c_id #
right join 右连接
select customers.c_id as customers_id, orders.o_num as orders_num
from customers right outer join orders on customers.c_id=orders.c_id #
3):复合条件查询
select customers.c_id as customers_id, orders.o_num as orders_num
from customers inner join orders on customers.c_id=orders.c_id and customers.c_id=10001 #
查两表关联列相等的数据用内连接
Col_L是Col_R的子集时用右外连接。
Col_R是Col_L的子集时用左外连接。