4.简单查询

# select 字段名,字段名 from 表名
select shopName , brandName from goods;
# 起别名 select filed as xxx from xxx;

简单筛选

select * from class where id > 1;

select * from class where name = 'zhangsan';

# description中包含p字符的记录，但是like 有性能问题，一般可以用es之类的来替代这个功能
select * from class where description like '%p%';

# not like 这里表示不包含 '另外' and 关联两个条件
SELECT  * FROM goods  WHERE  shopName = 'ax5400' and  description not LIKE '%另外%';

简单关联查询

SELECT * FROM  customer WHERE link_id =2  OR  cname LIKE  '%2%'

-- %2 :以2开头  2%:以2结尾 %2%:包含2
SELECT link_id from customer  
-- distinct 去重
select DISTINCT link_id from customer 

-- 查询age>=30 <=40
select cname,age,address  from customer WHERE age >= 30 AND  age <= 40
-- 查询30到50之间 包括两端
SELECT * from customer WHERE age  between 30 and 50
-- 查询不在30到50之间到数据
SELECT * from customer WHERE age NOT between 30 and 50
-- 查询link_id 是2或者1的数据
SELECT * FROM  customer WHERE link_id =2 OR link_id =1
-- 查询link_id 在（1，2）集合中的数据
SELECT * FROM  customer WHERE link_id in (2,1)
-- 查询link_id 不在（1）集合中的数据
SELECT * FROM  customer WHERE link_id NOT in (1)
-- 查询link_id 是null的数据
SELECT * FROM  customer WHERE link_id IS  NULL  
-- 查询link_id 不是null的数据
SELECT * FROM  customer WHERE link_id IS NOT  NULL

处理null 默认值

-- 如果link_id 没有值就给一个2333 默认值
SELECT cname , if (link_id,link_id,2333) from customer 

-- 使用ifnull 没有值就用123456 
select cname , IFNULL(link_id,123456) from customer

升序、倒叙

-- 按age 倒叙
select cname, link_id from customer order by age DESC
-- 按age 升序
select cname, link_id from customer order by age ASC 
-- 按age , link_id 升序
select * from customer order by  age  ASC , link_id ASC

限制个数

-- 取最后一个
SELECT * from customer order by id DESC limit 1 

-- 取前面两个
select *from customer order by id ASC  LIMIT 2

-- 从第1条数据开始往后取两条
select * from customer order by id asc limit 1,2