【MySQL】数据库中表的增删查改操作详解

前言

对于表，据我目前了解的情况而言，实际开发中常见的操作也是增删查改，也就是经常说的CURD。【create增、update改、retrive改、query查】但是可能会跟我们想象的有所出入，具体看下边的解释。

增加操作：也就是创建表、以及如何向表中增加记录

删除操作：也就是删除整张表，或者删除已经插入的记录

查询操作：也就是查询数据库中是不是有这张表，或者这张表中有没有我们想要的记录

更改操作：按照我们所想的，应该有对表名更改的操作、对表中字段名更改的操作、对表中已经插入的记录更改的操作，但是由于实际开发中“对表中已经插入的记录更改”这一操作使用的比较少，所以我们这里重点介绍前边两种更改操作。

【使用的mysql服务器为5.7版本】

为了方便理解，我们这里整篇文章均围绕下边这个例子来讲解。

SQL的通用语法

1. sql语句以分号为结尾

2. 可以通过过缩进和空格增强可读性

3. 不区分大小写

4. 注释：单行注释用–（减号）或者#（井号），多行注释用/**/

   --  第一种单行注释
   # 第二种单行注释
   /*
   多行注释
   */
   

一、表的创建与表的新增

语法

创建表

#创建表操作
create table [if not exists] tableName(字段名1 类型，字段名2 类型……)[comment '……'];

#新增记录操作
#第一种对所有列插入一条数据
insert into table_name values(值1，值2……);
#第二种，对指定列插入一条数据，
insert into table_name(属性1，属性2……)values(值1，值2……);
-- 补充未插入的列为默认值，不指定为Null【怎么设置默认值，在约束的地方会讲】

#第三种，一次插入多条数据
insert into table_name values(值1，值2……),(值1，值2……)……;

说明：

1. 创建表最简单最常用的书写方式为create table tableName(字段名1 类型，字段名2 类型……);
2. 插入记录使用比较多的是第一条和第三条。
3. comment+引号表示的意思是注释，我们可以为整张表添加注释，也可以为表中的单个字段添加注释。【字段也，简而言之就是列名】
4. if not exists跟它的中译的结果差不多，就是如果不存在的话创建这个表。但是由于这个子句主要起着检查的作用，我们很多时候在创建表的时候就已经知道它是否存在了，所以就很少用。
5. 另外，对于容易写错的sql语句，除了直接在客户端的命令行窗口写，我们还可以通过记事本比如windows自带的记事本或者sublimeText、idea等来写，之后再复制到命令行上边。

数据类型的介绍

整体语法我们知道的差不多了，我们具体看看表中的内容怎么写。可以看到我们要想成功创建出来一张表，就必须先知道它的数据类型。那么sql中到底有哪几种数据类型呢？主要有数值类型、字符串类型、日期类型，这些我们详细讲，当然除此以外很多类型比如复合类型、二进制类型，这里我们作为补充知识了解即可。

下边的了解即可

当然，对于这些数据类型，我们并不建议背，而是通过具体的实践来掌握。

说明

1. 可以看到对于字符串类型我们是需要指定字符的个数的。这里我们是采用尝试的方法尝试出来的，具体工作中会有产品经理进行约束。
2. 一般来讲，对于钱我们要求的比较高，不会像实际生活中用浮点数表示，而是通过整形存储到底有多少分【元–》分】

演示

看到Query OK……就算是创建成功了。

下边，我们借助查询语句select * from 表名来看看效果。

这里，我想提几个初学者容易犯的错误

1. 日期类型是date不是data
2. 建表语句中，最后一个字段后边没有逗号

二、表的删除

语法

删整张表的语法

drop table [if exists] table_name;

删记录的语法

delete from table_name [where 条件子句];

说明

1. 与建表语句是一样，虽然有if exists这个用法，但是实际应用应用中很少用到。
2. 删表语句这里涉及到where子句的用法，我们在表的查询那里会具体讲。

演示

因为表记录的删除的前提是首先这个表需要有记录，不是空表，所以这个我们会在讲完表记录的增加和修改之后，一起进行演示。

这里主要演示表的删除，把刚刚建好的那张表删除。

三、表的查询

查询使用的结构是select ……from。查询一般分为单表查询和多表查询，而单表查询和多表查询本身又分为很多种，相较于单表查询，多表查询会比较稍微复杂一点，这里我们先学习单表查询，先用起来，之后再进阶学习。

特别注意：查询的结果都是临时表！！！

查询整张表

语法:desc 表名;

这里的desc是英文describe描述的缩写

下边是对表中记录的查询。

（一）全列查询

语法：select * from 表名;

（二）指定列查询

语法：select 列名/列名序列 from 表名

（三）带表达式的查询

语法：select （列名,/列名序列）表达式/表达式序列 from 表名;

（四）带别名的查询

语法：select 表达式/列名 as 别名 from 表名;

除了可以给表达式/列名起别名，我们还可以给用聚合计算表达式起别名，还可以给表起别名，这些我们具体用到的时候再进行解释。

其中as关键字可以省略，但是考虑到语句的可读性，我们并不建议省略as关键字。

（五）去重查询

语法：select distinct 字段名 from 表名;

一般来讲，我们去重就是针对某列对查询出来的记录进行去重。

（六）排序查询

语法：select 列名/列名序列 form 表名 order by 字段名 asc/desc;

如果不指定排序规则，默认是升序。

这里的asc是英文ascend上升的缩写，desc是英文descend下降的缩写，注意与查询表的desc进行区别。

（七）条件查

条件查询使用的关键字是where，where放在表名的后边，where后边跟上条件子句。

语法：select 列名（序列）/表达式（序列） from 表名 where 子句（序列）;

在正式介绍条件查询之前，我们先介绍条件查询需要用到的几种运算符，分别是比较运算符、逻辑运算符。

比较运算符

运算符	说明
=	等于，判断字段之间的值是否相等，但是NULL不安全
<=>	等于，判断字段之间的值是否相等，NULL也安全
is null	是NULL
is not null	不是NULL
>=,<=,>,<	大于等于、小于等于、大于、小于
!=,<>	不等于
between a and b	范围匹配，[a,b]，在范围，结果为true
like	模糊匹配，%匹配任意个字符，_严格匹配一个字符

逻辑运算符

运算符	说明
and	同时为true才为真
or	任意为真则为真
not	值本身为false才为真

基本查询

查询年龄小于等于18岁的员工信息

查询年龄大于等于18岁的女员工信息

范围查询

查询年龄在18到25岁之间的员工的信息

模糊查询

语法：select 列名 from 表名 like '条件%条件';

select 列名 from 表名 like '……_……';

*使用like关键字，%可以模糊0或者多个字符，只能模糊并严格匹配一个字符。

这里为了方便演示，我再插入一条杨过的记录。

首先，我们来看_的例子【严格匹配一个字符】

其次，我们来看%的例子【匹配任意个字符，包括0个】

null查询

是null:is null、<=>null

不是null:is not null

注意：不能通过！=来判断不是null,这是有sql标准决定的。

查询名字不是null的员工信息

查询年龄是null的员工的信息

（八）分页查询

语法：select * /字段名/字段序列 from 表名 limit 页数 offset 个数;

页数从0开始。

查询年龄第二大的员工的信息

（九）聚合查询

前边的查询都是针对列跟列的，聚合查询时针对同一列中的不同行的元素。

一般聚合查询的时候，我们会用到聚合函数。

注意：

1. 不可以直接嵌套使用，比如： max（count（*））但是可以嵌套子查询使用（先分组取出count值， 再将count值倒序排列，取第一个值就是最大的）
2. 对于聚合函数而言，不是数字没有意义
3. 使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中
4. HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中显示的任意项

关于

四、表记录的修改

语法：update 表名 set 值1=,…… [where条件] ;

注意：

1. 如果不指定where条件，upadate会修改整张表中的列名。与之类似的是delete不指定条件，也会默认删除整张表中记录。
2. 这个关键字只能针对表记录中具体具体记录进行修改，不能对表名/列名进行修改。
3. 如果需要对表名/列名进行修改的话，需要使用alter关键字，但是因为实际开发过程中这个使用的频率并不是特别高，所以我们这里就不介绍了。

将杨过的id改成10，workno改成10，gender改成男，age改成16

一定要指定条件

为了做这个演示，我删表，重新建表了= _ =，下边是正确的更新操作。

除此之外，我们再演示一下，之前没有演示的删除记录的操作。

五、多表查询

我们已经知道mysql是一个关系型数据库，内部是一些有关联的表。那么这些表是怎么联系在一起的呢？

试想，我们如果想要把我们的专业的学生信息以及他们的成绩放到数据库中，我们并不是在任何时候都看到学生的成绩的，那就意味着我们要能够只拿到学生的信息而不包含成绩，而有时候我们又只看成绩，有的时候又只看班级。

此时，我们一般会建立三张表，一张是班级表，里边有班级id和属于这个班级的学生，一张是学生信息表，每个学生的信息又都至少包含一项班级id信息，一张是成绩表，这张表又至少包含了学生的id和对应班级的id。

那么如果不加限制的允许人们往里边输入数据，我们在进行多表查询的时候会不会出错？答案是肯定的。那么我们该怎样解决甚至避免这个问题呢？

这也就是我们接下来要谈的约束的问题，有了约束，表与表之间就会相互制约，过滤掉不符合要求的数据。所以，在正式讲解多表查询之前，我们先来看看什么是约束，约束的用法有哪些？

约束

约束是一种限制，是用来对表中的行或者列做出限制，来确保表中数据的完整性、唯一性。

not null约束

作用：使用not null指定列不为空。

效果：再向表中这一列进行插入记录时就不能为空了，否则就会语法错误。

unique约束【唯一键约束】

作用：指定列的值必须是唯一的，不重复的，可以是多个null值。

效果：再向表中这一列进行插入记录时不能和以前的这一列的值重复，否则就会语法错误。

primary key【主键约束】

作用：唯一标识这张表，等价于not null+unique的合体

注意：每个表最多只允许一个主键

除此以外，主键修饰的属性一般是id，我们还会用auto_increment自增约束进行修饰。当插入第一条记录时，自增字段没有给定一个具体值，可以写成DEFAULT/NULL，那么以后插入字段的时候，该自增字段就是从1开始，每插入一条记录，该自增字段的值增加1。当插入第一条记录时，给自增字段一个具体值，那么以后插入的记录在此自增字段上的值，就在第一条记录该自增字段的值的基础上每次增加1。

如果中间插入，断开了(id值），那么就跟枚举一样会随着这个值增长

foreign key约束【外键约束】

语法：foreign key (子表的关联字段) references 主表名(主键/唯一键)

这里的父/主表也就是驱动表，子表也就是被驱动表

效果：①父表对子表有约束作用。生成id不能那么随意②子表其实对父表也有约束作用的。父表不能轻易删除，必须先删子表，再删父表也就是说约束是双向的。③父表必须有唯一键/主键。

注意：这里的括号不可省略

讨论完主键的基本用法，我们来讨论另外一个问题，为什么父表中必须要有主键呢？

原因：每次给子表插入数据时，必然需要再父表查询id是否存在，默认情况下，查询是需要遍历表的，在数据量非常的情况下，遍历的效率非常的低，需要使用索引，这时外键和主键其实就相当于一个索引，加快了查找速度。

对于约束的个数问题，同一个表中主键只能有一个，其他的个数没有限制，同时大部分可以自由组合。

default约束【默认约束】

作用：指定列为空时的默认值，人为规定了就是我们规定的值，没有人为规定就是null

例如：例如：age int default 20

check约束【check约束】

作用：保证列中的值符合指定的条件

例如：sex char check (sex =‘男’ or sex=‘女’)

（一）内连接

语法：

​ 写法一：直接用from 将表名分开

​ 写法二：用表一 (inner) join表二 on条件（join 表三 on条件）

作用：求两个或者多个表的交集

效果：生成表的行数是被连接表行数的乘积，列数是被连接表列数的和。

方式一：

方式二：

（二）外连接

左外连接

语法：

​ 写法：用表一 left join表二 on条件（join 表三 on条件）

作用：左外连接【表一全集并上两个的交集】，

右外连接

语法：

​ 写法：用表一 right join表二 on条件（right join 表三 on条件）

作用：右外连接【表二全集并上两个的交集】

（三）自连接

作用：自连接是指在同一张表连接自身进行查询。

例如：select 表别名.属性 from 表名 as 别名1,表名 as 别名2;

（四）子查询

子查询就是将表的查询结果作为另一个表的来源进行查询。相当于是一个数据查询的小技巧——把一个子查询当做一个临时表使用。

单行子查询：返回一行记录的子查询，可以用=接收

多行子查询：返回多行记录的子查询，可以用（not）in接收。当然也可以用exists，但是这个只有内层查询有结果的时候外层才执行。

（五）合并查询

合并多个查询是合并多个select的执行结果。一般使用union和union all这两种集合操作符比较多。

前提：两个表查询得到的临时表字段是一致的

union：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉重复行。

union all：该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行