sql语句

1.创建表

create table user(
  userId varchar(3) not null,
  name varchar(5),
  ename varchar(10),
  primary key(userId)
);
1
2
3
4
5
6

2.删除表

drop table if exists user;
1

3.插入数据

insert into user(userId,name,ename)values('101','晓晓','tom');
1

4修改数据

update user set name='小',ename='xiao' where userId='101';
1

5删除数据

delete from user where name='晓晓';
1

6查询数据

• 查询所有记录

select * from user 
1

• 查询表中某些字段对应数据

select name from user
1

• 别名

select name 姓名 from user
1

• 查询表中某些字段不重复的数据

select distinct name from user
1

• 等于=

select * from user where age=20
1

• 不等于!=

select * from user where city !='北京'
1

• < > >= <=

select * from user where age>20
1

select * from user where age>=20
1

• in:包含 not in:不包含

select * from user where city in('北京','上海')
1

select * from user where city not in('北京','上海')
1

• between:之间 包含两边的值 not between:不包含 不包含两边的值

select * from user where age between 10 and 20  
1

select * from user where age not between 10 and 20  
1

• like:完全匹配。 not like 不区分大小的 。
  %代表任意长度的字符串,_下划线代表一个任意的字符

select * from user where name like '晓%'
1

select * from user where name like '晓_'
1

select * from user where name not like '晓%'
1

• like binary:区分大小写

select * from user where ename like binary 'T%'
1

• rlike:不完全匹配。支持正则表达式匹配 not rlike

select * from user where ename rlike '[0-9]'
1

select * from user where ename not  rlike '[0-9]'
1

• is null:为空 is not null

select * from user where ename is null
1

select * from user where ename is not null
1

• and:与

select * from user where age=20 and city='北京'
1

• or:或

select * from user where city='北京'or city='上海'
1

• not:非 相当于!=

select * from user where not  city='北京'
1

• order by:排序 asc:升序（默认），desc:降序

select * from user order by age
1

• group by:分组查询

select * from user group by city;
1

• having。having语句只能配合group by语句使用，没有group by时不能使用having。

select city,count(userId) from user group by city having count(userId)>1
1

• 综合应用

select city,min(age),max(age),sum(age),avg(age),count(age),group_concat(name) from user where sex='男'group by city having avg(age)>20 order by min(age)
1

• +(加)，-(减)，*(乘)，/(除)

select age,age/2 from user
1

• limit:分页查询

查询3条数据
1

select * from user limit 3
1

（从第几条开始，查询几条）查询4-6三条数据
1

select  * from user limit 3,3
1