MySQL 数据库表常用的增删改查/去重/联表/分页sql语句

增删改查/模糊查/去重sql语句如下

-- 创建表
create table testtable (
		`id` INT(3) not null auto_increment,
		`name` VARCHAR(20) not null,
		`money` DECIMAL(9,2) not null,
		PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 查看表的创建的语句
SHOW CREATE TABLE testtable;

-- 显示表结构
DESC testtable;

-- 修改表名
ALTER TABLE testtable RENAME as testtable1;

-- 增加表字段
ALTER TABLE testtable ADD num int(11);

-- 修改表字段 约束
ALTER TABLE testtable MODIFIES num VARCHAR(11);

-- 修改表字段名字,重命名
ALTER TABLE testtable CHANGE num num1 int(11);

-- 删除表字段
ALTER TABLE testtable DROP num;

-- 新增一条数据
insert into 表名 values(全部列的值，用”,“分割);
insert into 表名 (字段1, 字段2) values (值1, 值2);

-- 新增多行数据
insert into 表名 values(全部列的值)，(全部列的值)....;
insert into 表名 (字段1, 字段2) values (值1, 值2),(值1, 值2),(值1, 值2)....;

-- delete 自增id不会重置   TRUNCATE 自增id重置从0开始
delete from 表名 where 条件;-- 注意：在修改或者删除数据的时候一定要指定条件，否则可能造成所有数据被污染或者清空。

truncate testtable;-- 清空数据(会把全表都给清洗掉，并且自增主键从1开始)

-- 修改表数据
update 表名 set 列1=值1，列2=值2，... where 条件;

-- 查询表数据
select * from 表名;

-- 根据条件查询
select * from 表名 where 条件;
select 字段 (as 别名) from 表名 where 条件;

-- like模糊查询
select * from 表名 where 字段 like '%值%';

-- 查询人员表姓王的人员，名字后面只有一个字的
select * from sys_user where nick_name like '王_';

-- 查询人员表姓王的人员，名字后面只有两个字的
select * from sys_user where nick_name like '王__';

-- 去重查询
select distinct * from 表名; 

-- 查询部门有研发部门/财务部门/市场部门
select * from sys_dept where dept_name in ('研发部门','财务部门','市场部门') and dept_name is not null; 

-- 查询部门编号在100-200之间
select * from sys_dept where dept_id >=100 and dept_id <=200;
select * from sys_dept where dept_id >=100 && dept_id <=200;
select * from sys_dept where dept_id >=100 or dept_id <=200;
select * from sys_dept where dept_id BETWEEN 100 and 200; -- BETWEEN and

-- 查询除编号100之外的部门
select * from sys_dept where dept_id !=100;
select * from sys_dept where not dept_id = 100;

联表查询sql语句如下(left join/right join/inner join/full join)

-- 左右连接查询（left outer join 的简写 left join）
select a.c from a left/right outer join b on a.d = b.d;
select a.c from a left/right  join b on a.d = b.d;

-- 内连接（inner join 简写 join）
select * from a inner join b on a.d = b.d;
select * from a join b on a.d = b.d;

-- 全连接（full outer join 简写 full join）
select * from a full outer jion b on a.d=b.d;
select * from a full  jion b on a.d=b.d;

-- inner join查询两表之间的并集
select a.nick_name,b.dept_name from sys_user a
inner join sys_dept b
on a.dept_id=b.dept_id;

-- left join 从左表中返回所有值,即使右表没有匹配
select a.nick_name,b.dept_name from sys_user a
left join sys_dept b
on a.dept_id=b.dept_id;

-- right join 从右表中返回所有值,即使左表没有匹配
select a.nick_name,b.dept_name from sys_user a
right join sys_dept b
on a.dept_id=b.dept_id;

-- 查询部门中无人员的部门有哪些
select a.nick_name,b.dept_name from sys_user a
right join sys_dept b
on a.dept_id=b.dept_id
where nick_name is null;

-- 查询人员所在的部门及角色
select a.nick_name,b.dept_name,d.role_name
from sys_user a
inner join sys_dept b
on a.dept_id=b.dept_id
inner join sys_user_role c
on a.user_id=c.user_id
inner join sys_role d
on c.role_id=d.role_id

分页查询sql语句如下

分页数据查询 limit 当前页，页面大小
第一页 limit 0,2   (1-1)*2
第二页 limit 2,2   (2-1)*2
第三页 limit 4,2   (3-1)*2
第N页  limit 0,2   (n-1)*pageSize,pageSize
pageSize:页面大小  n:当前页数  数据总数/页面大小=总页数

select a.nick_name,b.dept_name,d.role_name
from sys_user a
inner join sys_dept b
on a.dept_id=b.dept_id
inner join sys_user_role c
on a.user_id=c.user_id
inner join sys_role d
on c.role_id=d.role_id
order by b.dept_id asc 
limit 0,2