增加
insert into table (value1,...)
insert into table (column1,column2,...) values (value1,value1,...)
-- 多行插入
insert into table (column1,column2,...) values (value1,value1,...),(value2,value2,...)
insert into table set column1=value1,column2=value2,...
删除
delete from table where ....
修改
update table set column1=value1,column2=value2,... where ...
查询
条件查询
select * from table -- 查找所有
--范围 = != > < between
select * from table where id = 1
-- != 可能不支持时,使用<>,<>兼容性高于!=
select * from table where id != 1
select * from table where id > 1
select * from table where id < 1
select * from table where id between 1 and 10
-- in/not in
select * from table where id in (1,2)
select * from table where id in (select id form table2)
select * from table where id not in (1,2)
where 条件之间可以通过or and组合起来,如:
select * from table where id = 1 and name='xh'
select * from table where id < 10 or id > 20
--等价于 select * from table where id between 1 and 10
select * from table where id > 0 and id < 11
通配符(_ %)查询
_表示一个字符
%表示多个字符
如a%可以匹配到
ab
abc
abcd....等等
只要有一个a后面紧跟大于等于0个字符就可以匹配到
a_只可以匹配到
ab
a(不能匹配)
abc(不能匹配)
只有a后面有且只有紧跟一个字符时才可以匹配到
_a 与 a_ 一个向前匹配一个向后匹配,%一样
SQL如下
select * from table where id like 'a%'
select * from table where id like '%a'
select * from table where id like '%a%'
限制偏移
-- select * from table limit 0,10;
select * from table limit 10; --默认偏移0,取10条
select * from table limit 5,10 --偏移5即重6开始取,取10条
-- limit 10 offset 5 兼容性较强,limit 5,10兼容弱
select * from table limit 10 offset 5 --偏移5即重6开始取,取10条,limit要在前,offset在后
分页:第2页,取10条 page = 2 size = 10
-- 虚码,这里只是写逻辑,语句是错误的
select * from table limit (page-1)*size,size
select * from table limit size offset (page-1)*size
排序
-- 升序
select * from table order by id asc
-- 降序
select * from table order by id desc
-- 先按id降序,如果id相同,则按id1继续降序
select * from table order by id desc, id1 desc
助记提示:asc字母a小,所有从小到大,desc字母d比a大从大到小
分组
id | num |
---|---|
1 | 1 |
2 | 2 |
3 | 2 |
4 | 4 |
5 | 3 |
select num,count(*),min(id),max(id),sum(id),avg(id) from tb1 group by num;
上面结果为
num | count(*) | min(id) | max(id) | sum(id) | avg(id) |
---|---|---|---|---|---|
1 | 1 | 1 | 1 | 1 | 1.0000 |
2 | 2 | 2 | 3 | 5 | 2.5000 |
4 | 1 | 4 | 4 | 4 | 4.0000 |
3 | 1 | 5 | 5 | 5 | 5.0000 |
其中
num | count(*) | min(id) | max(id) | sum(id) | avg(id) |
---|---|---|---|---|---|
2 | 2 | 2 | 3 | 5 | 2.5000 |
这一行 表示num=2的记录有2条(count(*)),这两天数据中id最新的是2(min(id)), id最大是3(max(id)),id之和为5(sum(id)),id之平均数为2.5(avg(id))
分组通过 having 帅选
select num,count(*),min(id),max(id),sum(id),avg(id) from tb1 group by num
having count(*) > 1
结果为:
num | count(*) | min(id) | max(id) | sum(id) | avg(id) |
---|---|---|---|---|---|
2 | 2 | 2 | 3 | 5 | 2.5000 |
count(*) 小于1的都被筛选掉
还可以进行多条件分组,如
select id,num,... form tb1 where .. group by id,num,... order by id asc
联合查询
select * from table left join table1 on table.column = table1.column;
select * from table right join table1 on table.column = table1.column;
select * from table inner join table1 on table.column = table1.column;
left join,right join,的区别,
left join 保留左表的所有字段,右表若没数据,以null填充
right join 保留右表的所有字段,左表若没数据,以null填充
inner join 只保留,两表都有的数据