MYSQL数据库CURD中常用操作命令汇总及代码演示

1. CURD中常用操作

1.1插入

// mysql插入数据方法
insert yuangong(name,gender,salary,email,birthday)
values('张三','male',5000,'zhangsan@163.com','1986-02-01');
//mysql插入列方法
alter table yuangong add age int unsigned not null default 0 comment '年龄' after gender;

1.2删除

//删除某条数据
delete from yuangong where yid = 4;
//清空表
truncate yuangong;
//删除表
drop table yuangong;

1.3修改或者更新

//mysql更新某列数据(示例为根据生日更新年龄)
update yuangong set age = timestampdiff(year,birthday,now());
//根据条件更新某一个人的字段信息
update yuangong set gender = 'female' where name = '张三';
//修改自增起始序号
alter table yuangong auto_increment = 4;

1.4查询操作

//查看当前数据库
select database();
//查看当前数据库的版本
select version();
//查看当前时间
select now();
//条件查询-查询年龄大约35的员工相关信息
select name,gender,age,salary from yuangong where age > 35;
//条件查询-区间查询
select name,gender,age,salary from yuangong where age between 34 and 37;
//分组查询-如果使用中文别名必须复制到命令行，不支持手写中文别名（可以省略as关键字不写）
//聚合函数 max() min() sum() avg() count()
select gender as '性别',count(gender) as '数量' from yuangong group by gender;
//只查询男员工的数量，分组细分条件只能用having
select gender as '性别',count(gender) as '数量' from yuangong group by gender having gender = 'male';
//排序查询(asc为升序，desc为降序)
select name,gender,age,salary from yuangong order by age asc;
//分页查询 偏移量 =(page - 1)*10
//第1页
select name,gender,age,salary from yuangong limit 10 offset 0;
//第2页
select name,gender,age,salary from yuangong limit 10 offset 10;
//第3页
select name,gender,age,salary from yuangong limit 10 offset 20;
//子查询
select * from yuangong where age = (select max(age) from yuangong);
//集合查询 in
select * from yuangong where yid in(1,3);
//模糊查询 like
//名字以张开头的数据，%表示任何字符出现任意次数。
select * from yuangong where name like '张%';
//名字第二个字符为"五"的数据，下划线_表示匹配单个字符。
select * from yuangong where name like '_五%';
//is null(空值) is not null（非空值）
select * from yuangong where name is not null;
//通过多列数据过滤 and
select * from yuangong where age >35 and gender = 'female';
//匹配任意条件的数据 or
select * from yuangong where age = 35 or age = 37;
//关联查询
//内连接
select name,gender,age,salary 
from yuangong join gongsi
using(cid)
where cid = 1
//左外连接
select name,gender,age,salary 
from yuangong as y
left join gongsi as g
using(cid)
where y.yid is not null
//右外连接
select name,gender,age,salary 
from yuangong as y
right join gongsi as g
using(cid)
where g.gid is not null
//自然连接（前提是关联表中存在同名字段）
select name,gender,age,salary 
from yuangong yid join gongsi

2. 预处理原理

SQL语句中的数据，只有在执行阶段再与字段进行绑定。
作用：防止SQL注入攻击

//生成预处理的sql语句
prepare stmt from 'select * from yuangong where age >? and gender = ?';
//将真实的数据绑定到占位符上，要注意顺序
set @age = 35,@gender = 'female';
execute stmt using @age, @gender;