mysql查询

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的水果

1. 带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)

1. 带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 #这样写也可以和上面一行所查询出来的效果一致

2. 带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开头的记录,同理结尾的字符也是一样原理

3. 查询null值
   select c_id ,c_name,c_email from customers where c_email is null #查询email为null的数据

4. 带and 的多条件查询
   select f_name,f_price from fruits where s_id =’101’ and f_price>=5 #多个条件查询

5. 带or 的多条件查询
   select f_name,f_price from fruits where s_id =’101’ or s_id = ‘102’ #or 条件查询

6. 查询结果不重复
   select distinct f_name,f_price,s_id,f_id from fruits #过滤重复结果记录

7. 多列排序
   select f_name,f_price from fruits order by f_name ,f_price #先按f_name排序,在按f_price排序

8. 指定排序方向
   参考2

9. 分组查询
   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)函数

group by 可以和 having一起限定显示记录所需满足的条件,只有满足条件的分组才被显出出来

依据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#

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 #

1. 分页查询
   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的子集时用左外连接。