SQL语句增、删、改、查、左连接、右连接操作
插入一条数据
insert into user(id,username) values(null,'zhangsan');
insert into user(id,username) values(null,'lisi'),(null,'wangwu');
修改数据
update user set username='wangwu' where id=3;
注意：必须加where条件（避免灾难性操作，数据全部更改）
删除数据
delete from user where id=4;
注意：必须加where条件（避免灾难性操作，数据全部更改）
alter table user auto_increment=4;
truncate user; //数据被清空 id从1开始
查询所有
select * from user;
起别名 as或空格
select id,username as user,password as pass from user;
select id,username user,password pass from user;
查询id为2并且名字等于zhangsan的这条记录
select * fromuser where id=2 and username='zhangsan';
查询id为2和id为3的两条记录
select * from user where id=2 or id=3;
查询id为2、3、6的记录
select * from bbs_user where id in(2,3,6);
查询id不为2，3，6的记录
select * from user where id!=2 and id!=3 and id!=6;
查询id不为2，3，6的记录
select * from user where id not in(2,3,6);
查询2-5区间的记录
select * from user where id>=2 and id<=5;
select * from user where id between 2 and 5;
查询id不在2-5区间的值
select * from user where id not between 2 and 5;
查询字段值是否为null要使用 is null | is not null
查询username字段为null的记录
select * from user where username is null;
查询username字段不为null的记录
select * from user where username is not null;
like 模糊查询
select * from user where username like 'li%';
%代表任意字符 _代表一个任意字符,如果%加在了前面 '%xiao%', 如果有索引则索引会失效。
not like
查询username字段中值不存在li任意字符
select * from user where username not like 'li%';
排序 order by asc(升序) desc（降序）
select * from user order by id;//默认按升序
select * from user order by id desc; //按id降序排列
group by 按字段分组
查询每个用户发了几条贴子
select uid,count(uid) from post group by uid;
查询每个用户发了几条贴子并按结果进行倒序排序
select uid,count(uid) as cuid from post group by uid order by cuid desc;
select子句顺序
select * from user where......group by......order by.....limit.
limit m,n;跳过m条，显示n条，如果m为0可以省略不写。
select * from user limit 2,3;
联合查询(关联查询)
查询用户所有信息 user userdetail (推荐使用)
select u.id,u.username,d.age,d.sex,d.phone from user u,userdetail d where u.id=d.uid;
left join on 、right join on 、 inner join on
left join on 左关联：先输出左表的内容。再根据on后面的条件输出右表的内容
select * from user left join userdetail on user.id=userdetail.uid;
right join on 右关联：输出右表内容 再根据on的条件输出左表内容
select * from user right join userdetail on user.id=userdetail.uid;
只输出符合条件的内容
select * from user inner join userdetail on user.id=bbs_userdetail.uid;
嵌套查询：使用一个查询语句的结果作为另一个查询语句的条件。
查询userdetail表里uid在user表里的数据
select * from userdetail where uid in(select id from user);
