- 更新 update
增加一个年龄字段alter table user add age int unsigned not null default 0 comment '年龄' after gender;
更新年龄数据update user set age= timestampdiff(year,borthday,now());
条件更新update user set name='xiaowang' where name='wang';
- 删除
delete from user where id=1;
修改起始主键
alter table user auto_increment =1;
清空表
truncate 表名; - 查询
SELECT [DISTRINCT] 表达式1|字段…(*表示所有列)
FROM 表名 别名 [,数据源2 别名2]
WHERE 查询条件
GROUP BY 分组字段1…
HAVING 分组筛选条件1,…
ORDER BY 排序字段1 ASC|DESC, 排序字段2 ASC|DESC
LIMIT 偏移量 数量;
查询数据库
select database()
查询版本
select vsersion()
查询当前时间
select now();
select * from user where id>950;
select name as 姓名,count(1) as数量 from user group by name;
select name 姓名,count(1) 数量 from user group by name;
- 排序查询
select * from user where name='li' order by id desc;
- 分页查询
limit 显示数量 offset 偏移量(跳过的记录数量)
limit 偏移量,显示数量
offset=(page-1)num;在查询语句的最后写
前俩条 等价于limit 0,2;
` select from user order by age desc limit 2;![](https://img.php.cn/upload/image/669/941/590/1616663725280251.png) 从2到3
select from user order by age desc limit 2 offset 1;![](https://img.php.cn/upload/image/163/911/787/1616663736497138.png) 年龄最大
select from user where age=(select max(age) from user);` - 区间查询
select * from user where id>=10 and id <=20;
select * from user where id between 10 and 20;
- 集合查询
select * from user where id in (1,3,5);
- like
查询name以z开始的select * from user where name like 'z%';
查询name包含a的select * from user where name like '%a%';
查询name以g结尾的select * from user where name like '%g';
- 聚合函数
max()最大值,min()最小值,sum(最大值),avg()平均数,count()数量,四舍五入round(x,d) ,x指要处理的数,d是指保留几位小数 - 分组查询
查询男女人数select gender ,count(*) from user group by gender;
查询男女平均年龄select gender ,avg(age) 平均年龄 from user group by gender;
分组带条件查询,having,不能用where查询select gender ,count(*) from user group by gender having gender='male';
- 关联查询
- 内链接
select a.id,a.title,b.name from articles a, cates b where a.cid=b.cid;
使用inner jion on简化select id,title,name from articles a inner join cates b on a.cid=b.cid;
//简化
select id,title,name from articles a join cates b using(cid);
//过滤
select id,title,name from articles a inner join cates b on a.cid=b.cid where a.cid=1;
- 外连接
左外连接,左表文章表为主表,查询所有主表的信息,关联表只列出匹配的数据,没有匹配到的为nullselect * from articles a left join cates b on a.cid=b.cid;
右外连接select * from articles a right join cates b on a.cid=b.cid;
外连接转内连接,将从表为null的字段过滤掉select * from articles a left join cates b on a.cid=b.cid where a.cid is not null;
- 自然连接
自然连接是内连接的一个特例,前提是关联表中存在同名字段。natural joinselect id,title,name from articles natural join cates;
视图
创建视图create view v_user as select * from user; create view vc_user as select name,age from user;
查询视图select * from vc_user;
更新视图,会同步更新基本表update vc_user set age =40 where name='wang';
删除视图
drop view vc_user;索引
索引应该创建在经常被查询的字段,或者常出现在结果集的字段上
索引分类:普通索引,主键索引,唯一索引,全文索引。
create index 索引名称 on 表名(表字段);
创建索引
查询索引//普通索引
create index age on user(age);
//唯一索引
create unique index unique_age on user(age);
//主键索引
alter table user add primary key id(id);
show index from user;
删除索引
drop index age on user;预处理
防止sql注入攻击,sql语句中的数据只有在执行阶段才会和字段进行绑定。也可以看做是某一条sql语句的模板,可以多次重复调用提高效率
prepare生成预处理sql语句
- 内链接