mysql CURD常用操作及预处理机制

数据更新 update

update user set age = timestampdiff(year, brithdy, now());

查询数据 select

• 查询全部

select * from user;

• 条件查询 where

select * from user where id = 1;

• 区间查询 between … and

select * from user where id between 1 and 5;

• 集合查询 in

select * from user where id in(1,2,4,5);

• 分组查询 count() max() min() avg() sum()

//count()
select gender, count(*) from user group by gender;
//sum()
select sum(age) from user;
//avg()
select avg(age) from user;

• 分组条件查询 having,不能用where

select gender, count(*) from user group by gender having gender = 'male';

• 排序 order by

select * from user order by age desc;

• 分页查询 limit

select * from user order by age desc limit 5;

关联查询

• 内连接

select u.id,u.name,t.id,t.name from user as u, test as t where u.id = t.id;
//简化

• inner join … on

select * from user u join test t on u.id = t.id;
//简化
select * from user u join test t  using(id);
//条件
select * from user u join test t  using(id) where u.id = 1;

• 左外连接 left join

select * from user u left join test t  using(id);

• 右外连接 right join

select * from user u right join test t  using(id);

索引

索引应该创建在经常被查询的字段,或者常出现在结果集中的字段上

• 创建索引

//普通索引
create index i_name on user (name);
//唯一索引
create unique index unique_gender on user(gender);

预处理

• 防止 SQL 注入攻击
• SQL 语句中的数据,只有在执行阶段再与字段进行绑定

prepare stmt from 'select id,name from user where age > ? limit ?';
set @age= 20, @num = 5;
execute stmt using @age, @num;