07_MySQL数据库_增删查改

此专栏所有章节快速导航

01_MySQL数据库_CentOS7安装MySQL
02_MySQL数据库_数据库基础知识
03_MySQL数据库_库的操作
04_MySQL数据库_表的操作
05_MySQL数据库_数据类型
06_MySQL数据库_表的约束
07_MySQL数据库_增删查改
08_MySQL数据库_复合查询
09_MySQL数据库_索引
10_MySQL数据库_事务管理
11_MySQL数据库_用户管理
12_MySQL数据库_CentOS7下C-C++链接MySQL

一. 增加（insert）

# 创建一个表
create table student 
(
    id int unsigned primary key auto_increment,  # id为主键且设置为自增
    name varchar(16) not null,  # 姓名不能为空
    email varchar(32) unique  # 邮箱不能重复
);

# 1. 全列插入
insert into student values(1, '李华', '10086@qq.com');  # into可以省略的，推荐写上，这样语义比较清晰

# 2. 指定列插入
insert into student(name) values ('诸葛星驰');

# 3. 多行插入
insert into student(name) values ('李莉莉'), ('上官小莉');  

# 4. 插入或更新（如果出现key重复了，就更新数据，更新的数据写在updata后面）
# on duplicate key 当发生重复key的时候
insert into student(id, name) values(3, '黎明') on duplicate key update name = '黎明';

# 5. 替换（如果key重复了就将原来行全部删去，然后插入这条数据，否则直接插入这条数据）
replace into student(id, name) values(4, '小雨淅淅');

二. 查询（select）

2.1 创建考试成绩表

# 创建表
create table exam
(
    id int unsigned primary key auto_increment,
    name varchar(16) not null,
    chinese float,
    math float,
    english float
);

# 向表中插入数据
insert into exam(name, chinese, math, english) values
('张益达', 67, 98, 56), 
('吕小布', 87, 78, 77),
('关谷小齐', 88, 98, 90), 
('诸葛奇迹', 82, 84, 67), 
('张大炮', 55, 85, 45), 
('小雨淅淅', 70, 73, 78), 
('夏小力', 75, 65, 30);

2.2 全列查询

# 1. 全列查询
select * from exam;  # 显示exam表的所有列

2.3 指定列查询

# 2. 指定列查询
select id, name, chinese from exam;

2.4 表达式和指定名查询

# 3. 查询字段为表达式
select id, name, chinese+math+english as 总分 from exam;  
# 把chinese+math+english运算出来，指定名为总分
# 注意这里只是拷贝一份出来计算，不会改动原来的表

2.5 查询结果去重

select distinct math from exam;  # 查询math这一列，然后去重

2.6 where条件

2.6.1 比较运算符

运算符	说明
>，>=，<， <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 true
!=，<>	不等于
between X and Y	范围[X，Y]，如果X <= values <= Y，返回true
in(option1，option2…)	如果是()中的任意一个，返回true
is null
is not null
like	模糊匹配,% 表示任意多个（包括 0 个）任意字符，_ 表示任意一个字符

2.6.2 逻辑运算符

运算符	说明
and	多个条件必须都为true，结果才是true
or	任意一个条件为true，结果就为true
not	条件为true，结果为false

2.6.3 练习案例

# 英语不及格的同学名字和英语成绩
select name, english from exam where english < 60;

# 语文在80到90之间的同学名字和语文成绩
select name, chinese from exam where chinese between 80 and 90;

# 数学成绩是58或者59或者98或者99分的同学名字和数学成绩
select name, math from exam where math in(58, 59, 98, 99);

# 姓张的同学
select name from exam where name like '张%';

# 语文成绩好于英语成绩的同学名字、语文成绩和英语成绩
select name, chinese, english from exam where chinese > english;

# 总分在200分以下的同学（这里where不能用总分这个别名，因为where语句比起别名先执行）
select name, chinese+math+english as 总分 from exam where chinese+math+english < 200;

# 英语成绩不及格，并且不姓张的同学名字和英语成绩
select name, english from exam where english < 60 and name not like '张%';

2.7 排序

2.7.1 解析

降序（descending）为desc，升序（ascending）asc，默认为升序。

2.7.2 练习案例

# 按数学降序的同学名字和数学成绩（null视为比任何值都小）
select name, math from exam order by math desc;

# 查询同学各门成绩和名字，依次按数学降序，英语升序，语文升序的方式显示
# 会先按数学成绩排好，如果有数学相同的就按照英语的排，如果有数学和英语都相等的就按照语文排
select name, chinese, math, english from exam order by math desc, english asc, chinese asc;

# 查询同学名字及总分，按总分由高到低（这里是先查出来再排序，所以'总分'这个别名可以被order by使用）
select name, chinese+math+english as 总分 from exam order by 总分 desc;

# 姓吕的同学或者姓张的同学名字和数学成绩，结果按数学成绩由高到低显示
select name, math from exam where name like '吕%' or name like '张%' order by math desc;

2.8 筛选分页结果

# 从0开始，筛选2条结果
select * from exam limit 0, 2;

# 从3开始，筛选2条结果
select * from exam limit 3, 2;  # 方法一
select * from exam limit 2 offset 3;  # 方法二

2.9 聚合函数

函数说明

函数	说明
count()	返回查询到的数据的数量
sum()	返回查询到的数据的总和，不是数字没有意义
avg()	返回查询到的数据的 平均值，不是数字没有意义
max()	返回查询到的数据的 最大值，不是数字没有意义
min()	返回查询到的数据的 最小值，不是数字没有意义

案例

# 统计班级共有多少同学
select count(*) from student;  # count(*)也可以用as起别名

# 统计班级收集的邮箱有多少个
select count(email) from student;

# 统计本次考试的数学成绩分数个数（不能有重复的数据）
select count(distinct math) from exam;

# 统计数学成绩总分
select sum(math) from exam;

# 统计所有同学总分的平均值
select avg(chinese+math+english) from exam;

# 返回英语最高分
select max(english) from exam;

# 返回70分以上的数学最低分
select min(math) from exam where math > 70;

2.10 分组查询

2.10.1创建测试数据库

# 创建一个数据库
create table employee 
(
  id int(6) unsigned zerofill primary key auto_increment comment '雇员编号',
  name varchar(10) not null comment '雇员姓名',
  job varchar(9) not null comment '雇员职位',
  salary decimal(7,2) not null comment '工资月薪',
  deptid int(2) unsigned zerofill not null comment '部门编号'
);

# 插入数据
insert into employee(name, job, salary, deptid) values
('张益达', '前端开发', 8400, 01), 
('吕小布', '平面设计', 8500, 02),
('诸葛奇迹', 'C++后端开发', 9100, 01), 
('上官婉儿', '摄影剪辑', 8300, 03),
('林轩', '3D设计', 8400, 02), 
('唐诗诗', '公众号运营', 8100, 03),
('黎明', 'Java后端开发', 8900, 01), 
('陈肖潇', 'C++后端开发', 9400, 01);

2.10.2 一层分组

# group by deptid表示以deptid这个字段作为分组依据，这个字段的值相同的就会被归为一组
# 显示每个部门的平均工资和最高工资
select deptid, avg(salary), max(salary) from employee group by deptid;

2.10.3 嵌套分组

# 显示每个部门的每种岗位的平均工资和最低工资
select deptid, job, avg(salary), min(salary) from employee group by deptid, job;  # 先根据部门分组，然后又根据职位分组

2.10.4 having

having和where功能类似都是在过滤数据，不同的是where过滤的是表中的数据，having过滤的是分组的数据。where一般运行都是比其他指令考前的，having一般是配合group by使用，并且执行的顺序也是在group by之后。

# 显示平均工资低于8500的部门和它的平均工资
select deptid, avg(salary) as 平均工资 from employee group by deptid having 平均工资 < 8500;

三. 更改（update）

# 将张益达同学的数学成绩变更为80分
update exam set math=80 where name='张益达';

# 将吕小布同学的数学成绩变更为86分，语文成绩变更为99分
update exam set math=86, chinese=99 where name='吕小布';

# 将总成绩前三的3位同学的数学成绩减去10分
update exam set math = math-30 order by chinese+math+english desc limit 3;

# 将所有同学的语文成绩更新为原来的2倍（没有where或其他条件限制就好更新全表的）
# 注意：update时记得加where或者其他条件筛选，否则就是全表了，非常危险
update exam set chinese = chinese*2;

四. 删除（delete）

4.1 创建for_delete表

# 创建表
create table for_delete
(
    id int unsigned primary key auto_increment,
    name varchar(16) not null
);

# 插入数据
insert into for_delete(name) values ('A'), ('B'), ('C');

4.2 联系案例

# 删除名字为A的信息
delete from for_delete where name='A';

# 删除整张表的数据
delete from for_delete;

4.3 截断表

4.3.1 SQL的日志

bin log： 几乎所有的sql操作，MySQL服务器都会记录下来，改log用来进行多主机同，增量备份等

redo log： MySQL数据持久化和crash-safe（我们操作数据库是在内存中的，数据不会立即刷新到磁盘上，会定期刷新，如果出现异常导致内存中的部分数据还没有刷新到磁盘，这时候就要用到redo log）

undo log： 在事务中承担回滚的日志，可以用来数据操作恢复

4.3.2 truncate和delete

• 只能对整表操作，不能像 delete 一样针对部分数据操作
• 实际上 MySQL 不对数据操作，所以比delete更快，但是truncate在删除数据的时候，并不经过真正的事物，所以无法回滚，也不能写入到bin log
• truncate会重置 auto_increment项，而delete不会

4.3.3 用例

truncate for_delete;