CURD常用操作、select常用查询、预处理原理

1. CURD常用操作

-- 增
insert users (name,gender,salary,email,birthday)
values ('php','male',888,'php@php.cn','1993-08-03'),
('cn','male',888,'php@php.cn','1993-08-03');
-- 删
delete from users where name = 'cn';
-- 改 将users库中name值为php的改为helloworld
update users set name = 'helloworld' where name = 'php';
-- 查
select name,gender,email,salary from users where name = 'helloworld';

---

2. 常用select查询、关联查询

• 2.1 常用select查询

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

-- 区间查询：salary > 6000 salary <= 8000;
select sid,name,salary from users where salary > 6000 and salary <= 8000;

-- 分组查询：按性别分组查询，统计数量
select gender 性别, count(*) 数量 from users group by gender;

-- 排序查询：按年龄降序排序
select sid,name,age,salary from users order by age desc limit 5;

-- 分页查询：每页显示五条记录，查询第二页，偏移量公式：offset = (2-1) * 5 = 5
select sid,name,email from users limit 5 offset 5;

-- 集合查询：查询id为35，38，40
select sid,name,salary from users where sid in (35,38,40);

-- 模糊查询：查询名称第二个带o的用户
select sid,name,salary from users where name like '_o%';

-- 分组过滤查询：按性别分组查询，num统计数量，过滤条件为男性
select gender, count(*) num from users group by gender having gender = 'female';

• 2.2 关联查询

关联查询

左外连接

-- 内连接使用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;

---

3. mysql预处理原理

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