CRUD工程师必会：MySQL 表 的操作（全）

目录

一.表的基本操作

1.创建表

2.删除表

3.约束

3.1 默认值约束

3.2 NULL约束

3.3 唯一约束

3.4 主键约束

3.5 外键约束

3.6 check约束

二.新增

1.新增单行数据

2.新增多行数据

3.复制表中内容

三.查询

1.全列查询

2.指定列查询

3.表达式查询

4.别名查询

5.去重查询

6.排序查询

7.条件查询

8.分页查询

四.聚合查询

1.聚合函数

2. 分组查询

五.联合查询

1. 原理

2. 内连接

3. 外连接

4. 自连接

5.子查询（嵌套查询）

5.1单行子查询

5.2 多行子查询

5.3 exists

6.合并查询

六.修改

七.删除

八.结语

---

表的基础操作包括增删查改四个操作。这里对表的操作是建立在我们选中了对应数据库的前提下。

一.表的基本操作

操作表时要先选择数据库。

1.创建表

create table 表名(
	名1 类型,
    名2 类型,
    名3 类型
);
//可用comment增加说明

//查看数据库中有哪些表
show tables;

//查看某一个表的结构
desc 表名;

2.删除表

drop table 表名;
drop table if exists 表名;

注：一个drop可以同时删多个表。

3.约束

在创建表的时候我们可以加入一些约束来对表进行限制。

3.1 默认值约束

可以规定没有给列赋值时的默认值，用户指定的优先级高于默认约束，关键词是：default

举个例子：

create table student(
    id int,
    name varchar(25) default '未知名'
);

3.2 NULL约束

可以指定某一列不可为空，关键词：not null

举个例子：

create table student(
    id int not null,
    name varchar(25)
);

3.3 唯一约束

指定列的每一行都是独一无二的，不能有重复的，关键词：unique

举个例子：

create table student(
    id int unique,
    name varchar(25)
);

3.4 主键约束

指定列是非空的也是唯一的，主键约束是NULL约束和唯一约束的融合，关键词：primary key

举个例子：

create table student(
    id int primary key,
//  id int primary key auto_increment, 
    name varchar(25)
);

补充：auto_increment 是自增键，在上面的例子中，id可以实现自增。自增的规则：在最大值的基础上加一。比如一开始什么都没有加，那就是0；如果当前id最大值是50，那么下一次插入时id就是51。但是要注意，自增是在我们没有规定id变量的情况下，如果我们自己在插入时写上了id是几的话就不会自增，当我们不写id时才会自增。

一个表中只允许有一个主键。但一个主键不是只能包含一个列，可以包含多个列，这种主键叫复合主键。在复合主键中，只有这个复合主键中的所有列都相同的情况下才是重复。

3.5 外键约束

用于关联其他表中的主键或唯一键，起连接作用，关键词：foreign key（列名）reference（列名）

举个例子：

create table student(
    id int,
    class_id int,
    name varchar(25),
    foreign key(class_id) references class(id)
);
//class_id是当前表中的列，class我们要建立联系的主表，id是主表中我们要建立联系的列

3.6 check约束

插入规定列时要满足一定条件，关键词：check

举个例子：

create table student(
    id int,
    name varchar(25),
    check(name = '张三' or name = '李四')
);
//name只能为张三或者李四，其他不行

二.新增

增也可以说是插入，在表中插入行。

1.新增单行数据

列名处可以写一个，也可以是多个，但是要是表中有的列名。后面插入的内容要与前面的列名内容一一对应。

insert into 表名(列名) values(要插入的内容);
//例子
insert into book(book_name,price) values ('西游记',45);
insert into book(book_name) values ('西游记');

还可以不写列名，但是后面values中的内容要写全，并且要与我们定义的列名顺序相同。

insert into book values(要插入的内容);
//例子
insert into book values('水浒传','施耐庵',45,'古典小说');

2.新增多行数据

跟新增一行数据类似，比较简单。后面插入的内容与前面的列名要一一对应。

insert into book(列名) values (要插入的内容),(要插入的内容);
//例子
insert into book(book_name,price) values ('三国演义',50),('红楼梦',50);

3.复制表中内容

insert into 目标表(要复制的列名) select (与前面对应) from 原表;
//例子
insert into student1(id,name) select (id,name) from student2;

三.查询

1.全列查询

不建议这样使用，如果表中的数据过多，那么查询的量会巨大，影响服务器的效率。

select * from 表名;
//例子
select * from book;

查询结果：

2.指定列查询

无需按照我们定义的列名顺序来写，列名可是一个或多个

select 列名1,列名2 from 表名;
//例子
select book_name,price from book;

查询结果：

3.表达式查询

可以在查询的结果中加入一个新的列，查询结果的表只是临时的，不会影响我们原来的表

//例子
select name,chinese+10 from standings;

select name,chinese+math from standings;

查询结果：

4.别名查询

查询结果可另起新名，下面的例子比较形象：

//表示方法一
select name,chinese+math as 总分 from standings;
//表示方法二
select name,chinese+math '总分' from standings;
//表示方法三
select name,chinese+math 总分 from standings;

注意：如果新起的名字中还有空格的只能使用第二种。

查询结果：

5.去重查询

查询给定列名的列，如果某一行与另一行的每一列的内容都相同，这才说是重复的。

select distinct 列名 from 表名;
//例子
select distinct chinese from standings;

查询结果：

6.排序查询

有升序（ASC）和降序（DESC），在默认的情况下是升序

如果我们排序多列且没有规定排序规则，那么就会按照我们定义列的顺序依次排序

注意：NULL在任何情况下都是最小的，可以理解其数值是负无穷。例如当我们算总数的时候，一但某一列有NULL，那我们得到的总数就是NULL，因为负无穷加任何数还是负无穷。

下面直接举几个形象的例子：

//根据某一列升序
select * from standings order by chinese DESC;

//根据某一列降序
select * from standings order by chinese ASC;

//对表达式或别名排序
select name,chinese+math from standings order by chinese+math desc;
select name,chinese+math as total from standings order by total desc;

//根据优先级排序
select name,chinese,math from standings order by chinese DESC,math ASC;

7.条件查询

我们可以使用一些比较运算符或者逻辑运算符来设立一些条件去筛选的查找。

select 列名 from 表名 where 条件;
//例子
select name from standings where chinese > math;

还可以进行范围查询：

//方法一
select name from standings where chinese between 80 and 90;
//方法二
select name from standings where chinese >=80 and chinese <=90;

//从这里可以看到取的范围左右都是闭的

从某个集合内查询：

//方法一
select name from standings where math in(10,20,30);
//方法二
select name from standings where math=10 or math=20 or math=30 ;

模糊匹配查询，% 表示后面可以匹配任意多个（包括 0 个）任意字符，_ 表示后面可以匹配任意一个字符：

select name from standings where name like '张_';
select name from standings where name like '张%';

NULL的查询：

//判断是不是 是NULL

//方法一
select name from standings where chinese is null;
//方法二
select name from standings where chinese <=> null;

//判断是不是 不是NULL
select name from standings where chinese is not null;

8.分页查询

分页查询有一个作用是可以查询某一个具体的名次，比如说我要查询某一个成绩单的第三名是谁，这个时候就可以使用分页查询了。

可以规定从哪一行开始查询，可加入排序规则

//n是指从下标0开始查询n条数据
select 列名 表名 limit n;
//例子
select * from standings limit 2;
select * from standings order by chinese ASC limit 2;

我们还可以规定从哪一行还是查询往后查多少行

//s是从下标为s的行开始读，n是从s往后读多少条

select 列名 表名 limit s,n;
//例子
select * from standings limit 1,2;
select * from standings order by chinese ASC limit 1,2;

//严谨写法

select 列名 表名 limit n offset s;
//例子
select * from standings limit 2 offset 1;

四.聚合查询

1.聚合函数

函数	说明
count	统计行的个数
sum	统计查询数据的总和
avg	统计查询数据的平均值
max	统计查询数据的最大值
min	统计查询数据的最小值

具体操作如下：

select 函数(列名) from 表名;
//例子
select sum(math) from student;

注意：

1.统计列的数量时，如果列中有null，不计入数量；

2.查询全部列的数量用 count(*)；

3.使用聚合函数时，null都不参与统计；

4.多个聚合函数可以同时使用。

2. 分组查询

可以根据某些组别进行查询，如统计成绩时可以按照同学的班级进行分组，然后进行查询。关键词是group by。

select 分组的列名,聚合函数 from 表名 group by 分组的列名;
//例子
select class_id,avg(chinese) from student group by class_id;

如果我们想要对分组的结果进行条件过滤，这个时候不能使用where，要使用新的关键词：having。

select 分组的列名,聚合函数 from 表名 group by 分组的列名 having 条件;
//例子
select class_id,avg(chinese) from student group by class_id having avg(chinese)>60;

注意：having是在分完组之后才过滤的。

五.联合查询

联合多个表进行查询，这里就用到了我们上面说的主外键约束了。

1. 原理

MySQL在进行联合查询的时候进行了下面几步的操作：

1.取多张表的笛卡儿积得到一个全排列的结果集；

2.通过连接条件过滤数据（主外键的关系）；

3.通过指定列查询来精简结果集。

我们在写联合查询时的思路也是依靠上面这三条的。

2. 内连接

直接看语法：

select 某一个表的列名 from 表名 inner join 表名 on 主外键条件;
//例子
select student.name from student inner join class on student.calss_id=class.class_id;
//inner 可以省略

内连接其实不用其关键词使用where来过滤也是可以的。

3. 外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。这个很好理解。这里的左和右指的时join的左面和右面。关键词：left和right。

select student.name from student left join class on student.calss_id=class.class_id;

上面的例子用的就是left为基准，student的全部都显示，不管其有没有班级。

4. 自连接

指在同一张表内进行操作，比较简单，要注意的是对于同一个量要取不一样的名字。

5.子查询（嵌套查询）

5.1单行子查询

用查询好的元素作为判断条件继续查找。可以多层嵌套。

//例子
select * from student where class_id=(select class_id from student where name= '张三');

5.2 多行子查询

这里嵌套体返回的是一个集合。

//例子
select * from student where class_id in (select class_id from student where name= '张三');
select * from student where class_id not in (select class_id from student where name= '张三');

5.3 exists

exists就是SQL中的if，如果其中的有结果，那么就执行前面的查询，如果不存在，则反之。

//例子
select * from student where exists (select class_id from student where name='张三');
select * from student where not exists (select class_id from student where name='张三');

这里如果存在张三这个人的话就可以查询到表内的全部内容了。

6.合并查询

将多个查询结果进行合并。关键词：union，union all。两个关键词的区别是：union可以去除结果集中的重复行，union all 不去除结果集中的重复行。

//例子
select * from student where class_id=1 union select * from student where class_id=2;

六.修改

我们可以通过设置条件，设置排序规则，限制查询条数来限制修改数据的范围，如果不加条件，不小心修改了全部数据是很危险的。

update 表名 set 列名=内容 where 条件 order by 排序规则 limit 数量;
//例子
update standings set math=50 where name='李四';

这里的条件、排序规则、数量都是可选项，不加也可以。

七.删除

要注意，这里的删除是删除表里的数据，不是把表给删除了。

删除操作和修改操作一样都可以设置条件、排序规则、数量，这三个都是可选项。

不管是上面什么删除操作，都是危险的行为，在实际应用是要多加小心。

delete from 表名 where 条件 order by 排序规则 limit 数量;
//例子
delete from standings where name = '李四';

两个表存在主外键关系，如果要删除主表中的内容，要先删除子表中的内容。

八.结语

有人可能会问，这个查询可不可以写多个列名，这个查询可不可以加个排序，这个条件可不可以......其实这些大家可以自行动手判断可不可以，这些东西就过多赘述了。表的增删查改差不多就这些，如果还有的话我还会补充的。