此专栏所有章节快速导航

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）二. 查询（select）2.1 创建考试成绩表2.2 全列查询2.3 指定列查询2.4 表达式和指定名查询2.5 查询结果去重2.6 where条件2.6.1 比较运算符2.6.2 逻辑运算符2.6.3 练习案例2.7 排序2.7.1 解析2.7.2 练习案例2.8 筛选分页结果2.9 聚合函数2.10 分组查询2.10.1创建测试数据库2.10.2 一层分组2.10.3 嵌套分组2.10.4 having三. 更改（update）四. 删除（delete）4.1 创建for_delete表4.2 联系案例4.3 截断表4.3.1 SQL的日志4.3.2 truncate和delete4.3.3 用例

一. 增加（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@'); # 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 比较运算符

2.6.2 逻辑运算符

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 聚合函数

函数说明

案例

# 统计班级共有多少同学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 logtruncate会重置auto_increment项，而delete不会

4.3.3 用例

truncate for_delete;