数据库CURD常用操作-select查询-预处理

数据库CURD常用操作-select查询-预处理

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

1. 熟练操作CURD中常用操作;

-- 增 王二和麻子
insert staffs (name, gender, salary, email, birthday) values
  ('王二', 'male', 4000, 'aa@bb.cc', '1999-12-30'),
  ('麻子', 'female', 6000, 'bb@aa.cc', '2000-01-01');
-- 删 麻子
delete from staffs where name = '麻子';
-- 改 王二为王五
update staffs set name = '王五' where name = '王二';
-- 查 王五
select name, gender, salary, email, birthday from staffs where name = '王五';

2. 对于常用的select查询，必须全部达到背诵级别，特别是关联操作，一定要理解原理并上机实战;

-- 1. 条件查询
-- 查询工资 salary > 4000 且 salary < 10000 且性别为男的记录
select id, name, salary from staffs where salary > 4000 and salary < 10000 and gender = 'male';
-- 2. 分组查询
-- 按性别分组查询，别名 num 统计数量
select gender, count(*) as num from staffs group by gender;

-- 3. 排序查询
-- 按工资降序排列
select id, name, salary from staffs order by salary desc;
-- 4. 分页查询
-- 每页显示2条记录，查询第3页，偏移量是 offset = 2 * (3 - 1) = 4
select id, name, salary from staffs limit 2 offset 4;
select id, name, salary from staffs limit 4, 2;
-- 5. 子查询
-- 查询工资最大值的记录
select id, name, salary from staffs where salary = (select max(salary) from staffs);
-- 6. 区间查询
-- 查询工资 salary 在 4000 ~ 10000 的记录，包含边界
select id, name, salary from staffs where salary between 4000 and 10000;
-- 7. 集合查询
-- 查询工资在 4000 5000 中的记录
select id, name, salary from staffs where salary in (4000, 5000);
-- 8. 模糊查询
-- 搜索工资包含三个零 "000" 的记录
select id, name, salary from staffs where salary like '%000%';
-- 查询名字以"五"结尾的记录
select id, name, salary from staffs where name like '%五';
-- 查询名字第2字符以"五"开始的记录
select id, name, salary from staffs where name like '_五%';

-- 9. 空值查询
-- 工资非空查询
select id, name, salary from staffs where salary is not null;
-- 10. 分组过滤查询
-- 按性别分组查询，别名 num 统计数量，过滤条件为男性的记录
select gender, count(*) as num from staffs group by gender having gender = 'male';

-- 11. 关联查询
-- 创建一个 test 表存储用户 id 字段为 uid
create table test (
    id int unsigned auto_increment not null primary key,
    uid int unsigned not null
);
-- 将 staffs 表中的 id 导入到 test 表中的 uid 字段中
insert test (uid) select id from staffs;
-- 关联查询
select name, salary, uid from staffs, test where staffs.id = test.uid;
-- 使用别名
select name, salary, uid from staffs as s, test as t where s.id = t.uid;
select s.name, s.salary, t.uid from staffs as s, test as t where s.id = t.uid;
-- 11.1 内连接
select name, salary, uid from staffs inner join test on staffs.id = test.uid where staffs.id = 1;
-- 内连接省略 inner 使用别名
select name, salary, uid from staffs as s join test as t on s.id = t.uid where s.id = 1;

-- 11.2 外连接
-- 11.2.1 左外连接：左主表，右从表
select name, salary from staffs as s left join test as t on s.id = t.uid where s.id = 1;
-- 11.2.2 右外连接：右主表，左从表
select name, salary from staffs as s right join test as t on s.id = t.uid where s.id = 1;

3. 明白预处理原理，并举例

• 防止 sql 注入
• sql 语句中数据，在执行阶段再与字段绑定

-- 准备预处理sql语句
prepare stmt from 'select name, salary from staffs where salary > ? limit ?';
-- 数据绑定到预处理语句中的占位符 ? 上
set @salary = 4000, @num = 2;
-- 执行sql语句
execute stmt using @salary, @num;