插入数据
1、普通插入
insert into user values('01','fei','table-ball','23');
2、指定插入
insert into user(id,name,hobby) values('02','fei','football');
3、多条插入
insert into user(id,name,hobby) values('03','fei','speak'),('08','fei','papa')；
4、set插入
insert into user set id='9',name='sheng',age='24';
5、批量插入
insert into user(id,name) select id,name from Fuser where age>24
更新数据
update user set name='gege',age='99' where id=1;
删除数据
1、删除单条
delete from user where id=9;
2、删除全部
truncate table user;
查询数据
1、简单查询
select * from user;
2、NULL查询
select * from user where hobby is NULL and age is not NULL;
3、in查询
select * from user where age in('22','23','24');
4、between and查询
select * from user where age between 21 and 55;
5、or查询
select * from user where age=22 or age=23
6、order by查询
select * from user order by age asc; 升序
select * from user order by age desc; 降序
7、group by查询
select name,sum(age) from user group by name
8、内连接查询
select u.id,f.name from user u inner join user f on u.id=f.id; 推荐
select u.id,f.name from user u,user f where u.id=f.id;
9、外连接查询
select u.id,f.name from user u left join user f on u.id=f.id;
10、exists子查询
select * from user u where exists (select 1 from user f where u.name=f.name and u.age>30);
11、in子查询
select * from user where id in(select id from user where age>30);
12、distinct子查询
select distinct * from user where id in(select id from user where age>30);
13、any子查询：满足任意一个就行
select * from user where age>=any(select age from user where age>30);
14、all子查询：满足所用的即可
select * from user where age>=all(select age from user where age>30);
15、union查询：去除重复的行
select name from user union select name from user;
16、union all查询：保留重复的行
select name from user union all select name from user;
17、字段别名查询
select name myname from user;
18、正则表达式查询：查询以ang结尾的记录
select name from user where name REGEXP 'ang$';
19、模糊查询
1、_:任意一个字母
2、%:任意0~n个字母
select * from user where name like '_强胜'
20、分页查询
select * from user order by id limit 100,3