单表查询
- 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）
