mysql 左链接删除_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);