数据准备

单表查询

• a 普通查询

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

• b 带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)

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

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

• e 查询null值

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

• f 带and 的多条件查询

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

• g 带or 的多条件查询

select f_name,f_price from fruits where s_id =’101’ or s_id = ‘102’ #or 条件查询

• h 查询结果不重复

select distinct f_name,f_price,s_id,f_id from fruits #过滤重复结果记录

• i 多列排序

select f_name,f_price from fruits order by f_name ,f_price #先按f_name排序,在按f_price排序

• j 分组查询

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 

• k 分页查询

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() 最小值

连接查询(多表)

• a 内连接查询

#普通连接查询 
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 #

• b 外连接查询

# 普通连接查询 
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 #

• c 复合条件查询

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

• a (show INDEX from myUser)查看所在表的索引

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

• b 创建一个组合索引： ALTER TABLE user_test ADD INDEX idx_user(user_name , city , age)
  匹配最左前缀是指优先匹配最左索引列，如：上面创建的索引可用于查询条件为：（user_name ）、（user_name, city）、（user_name , city , age）

• c 注：满足最左前缀查询条件的顺序与索引列的顺序无关，如：（city, user_name）、（age, city, user_name）