PHP数据库增删改查（CURD）常用操作|常用的select查询|预处理

1. 熟练操作CURD中常用操作;
2. 对于常用的select查询，必须全部达到背诵级别，特别是关联操作，一定要理解原理并上机实战;
3. 明白预处理原理，并举例

一、数据库CURD中常用操作

-- 新增 张波和李文集
insert staffs (name, gender, salary, email, birthday) values
  ('张波', 'male', 9500, 'zb@sinaw.com', '1999-12-30'),
  ('李文集', 'female', 8600, 'lwj@sinaw.com', '2000-01-01');
-- 删除 张波
delete from staffs where name = '张波';
-- 修改 李文集为刘天好
update staffs set name = '刘天好' where name = '李文集';
-- 查询 刘天好
select name, gender, salary, email, birthday from staffs where name = '刘天好';

二、select查询

-- 条件查询：查询salary大于9000的用户
select sid,name,salary from users where salary>9000;

图示：

-- 区间查询：salary > 9000 salary <= 12000;
select sid,name,salary from staffs where salary>=9000 and salary <= 12000;

图示：

-- 集合查询: in
select sid,name from staffs where sid in (1,3,5);

图示：

-- like查询
select sid,name from staffs where name like 'b%';
select sid,name from staffs where name like '_a%';

-- 分页查询
-- limit 显示数量 offset 偏移量(跳过的记录数量)
-- limit 偏移量, 显示数量
-- limit 子句一定要放在select的最后
-- 第一页： offset = ( 1 - 1 ) * 15 = 0
select sid,name,email from staffs limit 15 offset 0;
-- 第二页: offset = ( 2 - 1 ) * 15 = 15
select sid,name,email from staffs limit 15 offset 15;
-- 第三页: offset = ( 3 - 1 ) * 15 = 30
select sid,name,email from staffs limit 15 offset 30;
-- 第四页: offset = ( 4 - 1 ) * 5 = 15
select sid,name,email from staffs limit 15 offset 45;

图示：

-- 排序
select sid,name,age from staffs order by age asc;
select sid,name,age from staffs order by age desc;
select sid,name,age,salary from staffs order by age desc,salary asc;

混合排列

-- 分组条件: having,不能用where
select gender, count(*) num from staffs group by gender having gender = 'female';

分组条件having图示：

-- 聚合函数: max(), min(), sum(), avg(), count()
select gender, count(*) num from staffs group by gender;
select gender, round(avg(age),2) avg_age from staffs group by gender;
-- 分组条件: having,不能用where

2.3关联查询：

-- 内连接使用join
select a.aid,title,name
from articles a join categories c
using(cid);
-- 关联查询
select a.aid,title,name from articles a, categories c where a.cid = c.cid;
-- 左外连接：左主表，右从表
select *
from articles a
left join categories c
on a.cid = c.cid;
-- 右外连接：右主表，左从表
select *
from articles a
right join categories c
on a.cid = c.cid;

2.4-1创建视图：

-- 创建视图
create view v_staffs as select * from staffs;
create view cw_staffs as select sid,name,salary from staffs;
create view my_email as select '498668472@qq.com' as email;
select * from staffs where email = (select email from my_email);

2.4-2更新视图：

-- 更新视图,会同步更新基本表
update cw_staffs set salary = 8900  where sid = 2;
drop view v_staffs;

更新视图图示：

2.5

-- 索引应该创建在经常被查询的字段,或者常出现在结果集中的字段上
-- 索引分类: 普通索引，主键索引, 唯一索引, 全文索引
create index 索引名称 on 表名(表字段)
create index i_email on staffs (email);
show index from staffs;
create unique index unique_email on staffs (email);
alter table test add primary key i_id (id);
drop index i_email

show index from staffs图示：

三、预处理原理

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

-- 生成预处理的sql语句
prepare stmt from 'select sid,name,salary from staffs where salary > ? limit ?';
-- 将真实的数据绑定到预处理语句中的占位符上 ?
-- set @salary = 5000;
-- set @num = 5;
set @salary = 5000, @num = 5;
execute stmt using @salary, @num;

图示：