mysql查询

2018-06-17  本文已影响0人  海边的蜗牛ng

数据准备

单表查询

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的水果
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)
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 #这样写也可以和上面一行所查询出来的效果一致
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开头的记录,同理结尾的字符也是一样原理
select c_id ,c_name,c_email from customers where c_email is null #查询email为null的数据
select f_name,f_price from fruits where s_id =’101’ and f_price>=5 #多个条件查询
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排序
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 
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() 最小值

连接查询(多表)

#普通连接查询 
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 #
# 普通连接查询 
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 #
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的子集时用左外连接。

索引

create table myUser(
  id VARCHAR(255) NOT NULL PRIMARY KEY ,
  name VARCHAR(255),
  createTime TIMESTAMP,
  INDEX indexName (name(244))
)ENGINE=InnoDB DEFAULT CHARSET=utf8
DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test(
 id int AUTO_INCREMENT PRIMARY KEY,
 user_name varchar(30) NOT NULL,
 sex bit(1) NOT NULL DEFAULT b'1',
 city varchar(50) NOT NULL,
 age int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
上一篇 下一篇

猜你喜欢

热点阅读