SQL语法总结
增加数据
sql
-- users表中新增数据 now()为当前时间
insert into users(username,create_time) values ('张三', now());
-- 批量插入
insert into users(username,create_time) values ('张三', now()),('李四', now());修改数据
sql
-- 将users表中id=1数据的name和update_time字段,没有加where则修改整张表
update users set name="王五",update_time=now() where id=2;删除数据
sql
-- 将users表中id=5的数据删除
delete from users where id=5;查询数据
1. 基本查询
sql
-- 查询users表中的所有数据(性能低)
select * from 'users';
-- 查询users表中的username和password两个字段的数据
select username,password from 'users';
-- 查询users表中的username和password两个字段的数据，并起别名
select username as 用户名,password as 密码 from 'users';
-- 使用distinct查询users表中gander字段，并去掉重复的
select distinct gander from 'users';2. 条件查询
sql
-- 查询name为张三的数据
select * from users where name='张三';
-- 查询count<=5的数据
select * from users where count<=5;
-- 查询username不为null的数据，相反则使用 is null
select * from users where username is not null;
-- 查询update_time在 '2000-01-01'到 '2010-01-01'之间的gander=2的数据
select * from users where update_time between '2000-01-01' and '2010-01-01' and gander=2;
-- 查询count=2或者count=3的数据
select * from users where count=2 or count=3;
-- 查询count=2或者3或者4的数据
select * from users where count in (2,3,4);
-- 查询name是2个字的数据
-- like为模糊匹配 _代表匹配单个字符 %代表匹配任意字符
select * from users where name like '__';
-- 查询name姓张的数据
select * from users where name like '张%';3.聚合函数
将一列数据作为一个整体进行纵向计算,不对null运算。
sql
-- 计算总数
select count(*) from users;
-- 计算age最小值
select min(age) from users;
-- 计算age最大值
select max(age) from users;
-- 计算age平均值
select avg(age) from users;
-- 计算age之和
select sum(age) from users;4.分组查询
执行顺序：where>聚合函数>having
sql
-- 根据gender字段进行分组
select gender,count(*) from users group by gender;
-- 查询年龄>=20并且根据gender字段分组，gender数量>=2的数据
select gender,count(*) from users where age>=20 group by gender having count(*)>=2;5. 排序查询
sql
-- 查询users表中所有数据并且以status字段排序，默认为升序（ASC可省略不写）
select * from users order by status;
-- 查询users表中所有数据并且以id字段排序，desc为降序
select * from users order by id desc;
-- 查询users表中所有数据并且以status字段升序再以id降序
select * from users order by status,id desc;6.分页查询
sql
-- 查询第1页数据，每页展示20条
select * from users limit 0,20;
-- 查询第2页数据，每页展示20条
select * from users limit 20,20;外键
不推荐使用外键，建议使用代码逻辑实现表与表之间的关系
sql
alter table users
add constraint jobs
foreign key (job_id) references users (id);多表查询
user和dept通过user表中的depId关联
sql
select * from user,dept where user.depId=dept.id;
# 给表起别名
select * from user u,dept d where u.depId=d.id;内连接
内连接查询的是两个表中的交集数据，可能会出现查询数据不全的情况。语法: inner join ... on ...
sql
select * from user u inner join dept d on u.depId=d.id;外连接
外连接分为左外链接和右外连接,左外连接会完全包含左表的数据，右左外连接会完全包含右表的数据，两者可以互转。
sql
select * from user u left out join dept d on u.depId=d.id;
# 相当于
select * from dept d right out join user u on u.depId=d.id;自连接
自连接是指将一张表连接到自身，以便在查询中引用同一张表中的多个字段或行。
sql
select a.name,b.name from user a,user b where a.parentId=b.id;联合查询
union all会查询出合并后的数据可能会有重复数据，一般直接使用union
sql
select * from user where age>50
union
select * from user where sex=0;子查询
标量子查询
sql# 查询销售部门所有员工的数据 select * from emp where dept_id=(select id from dept where name = '销售部');列子查询
操作符 描述 in 在指定范围内 not in 不在指定范围内 any 有任意一条满足条件 some 同any all 所有条件必须满足 sql# 查询销售部和市场部所有员工的数据 select * from emp where dept_id in (select id from dept where name = '销售部' or name= '市场部');行子查询
sqlselect * from emp where (salary,parent_id)=(select salary,parent_id from emp where name ='张三');表子查询
子查询查出来的是多行多列
sqlselect * from emp where (salary,job)=(select salary,job from emp where name='张三' or name='李四');
事务
用户一组操作要么同时成功要么同时失败
sql
-- 开启事务
start transaction;
delete from users where id=1;
delete from jobs where job_id=1;
-- 提交事务
commit;
-- 回滚事务
rollback;索引
sql
-- 创建索引
create index userage_index on users(name);
-- 查看索引
show index from users;
-- 删除索引
drop index userage_index on users;