SQL 常用语句集（收藏）

创建表

creat table stu(
id int unsigned primary key auto_increment,
name varchar(10),
age int unsigned
)

增加字段

alter table stu add isdelete int

插入

insert into stu values(1,'kai',12)
insert into stu (name,age) values('kai',12)

修改

update stu set age=11 where id=1

删除

delete from stu where id=2

逻辑删除

1、设计表-增加isdelete字段，1代表删除，0代表没有删除
2、把所有数据的isdelete都改为0
3、要删除某一条数据，更新他的isdelete为1
4、当要查询数据时，只查询isdelete为0的数据
update stu set isdelete=0
update stu set isdelete=1 where id=1

条件查询

select * from stu where isdelete=0

查询

select * from students
select name,age,hometown from students
给列起别名
select name as 性别,age as 年龄,hometown 家乡 form students as可有可无，最好用
给表起表名
select s.name,s.age,c.hometown from students as s,class as c
查询去重后的数据
select distinct age from students
select dinstinct age,class from students 两者一起不重复
select dinstinct * from students 由于主键不重复，所以查出所有数据

比较运算符

等于 =
大于 >
大于或等于 >=
小于 <
小于等于 <=
不等于 != 或 <>

逻辑运算

and
or
not
select * from students where not hometown='天津'

模糊查询

like
% 表示任意多个任意字符
_ 表示一个任意字符
select * from students where name like '孙%'
select * from students where name like '孙_' 姓孙，名字是一个的名字

范围查询

in
between...and...
select * from students where hometown in('北京','上海','广州')
select * from students where age between 18 and 20

空判断Null

select * from students where card in null

排序

select * from students order by age asc 升序 asc可以省略

select * from students order by age desc 降序
select * from students order by age , studentNo desc
中文名排不了 UTF-8编码，需要转化
select * from students order by convert(name using gbk)

聚合函数

count(*)
select count(*) from students 统计行数
max(列)
select max(age) from students where sex='女'
min(列)
select min(age) from students where sex='女'
sum(列)
select sum(age) from students where hometwon='北京'
avg(列)
select avg(age) from students where sex='女'
select max(age) as 最大年龄,min(age) as最小年龄,avg(age) as ,平均年龄 from students where sex='女'