第12章 0224 - 数据库操作基础2，学习心得、笔记（mySql的，CURD操作， 预处理）

1. CURD操作

-- 增加数据
insert staffs(name,gender,salary,email,birthday) values 
 ('test','male',99999,'test@php.cn','2021-1-1'); 
-- 删除数据
delete from staffs where salary = 99999;
-- 修改数据
update staffs set salary = salary + 1000 where salary < 5000;
-- 条件查询
select sid,name,salary from staffs where salary>9000;
-- 条件查询，并按年龄从大到小排序
select sid,name,birthday,salary from staffs where salary>9000 
order by timestampdiff(year, birthday, now()) desc;
-- 模糊条件查询
select sid,name from staffs where name like 'a%';
select sid,name from staffs where name like '__m%';
-- 分组查询
select gender, count(*) num from staffs group by gender;
-- 关联查询
select aid,title,name from articles a, categories c where a.cid = c.cid;
-- 所有国内新闻，改为c.cid=2, 为查所有国外新闻
select aid,title,name from articles a, categories c where a.cid = c.cid and c.cid=1;
-- 简化，哪果关联表中有同名字段，可以简化为如下
select aid,title,name from articles natural join categories;

---

2. 预处理

-- 生成预处理sql语句, 用 ?号作占位符；
prepare sqlStr from 'select sid,name,salary from staffs where salary > ? and salary < ? order by salary desc';
-- 将真实的数据绑定到预处理语句的占位符上；
set @sal1 = 6000, @sal2 = 10000;
-- 执预处理sql语句
execute sqlStr using @sal1, @sal2;