MySQL表的增删改查（进阶）

本文主要是介绍MySQL表的增删改查（进阶），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

文章目录

• • 1.数据库的约束
  • • 1.1约束类型
    • 1.2null约束
    • 1.3unique：唯一约束
    • 1.4default：默认值约束
    • 1.5primary key:主键约束
    • 1.6foreign key:外键约束
  • 2.表的设计
  • • 2.1三大范式
  • 3.新增
  • 4.查询
  • • 4.1聚合查询
    • • 4.1.1聚合函数
      • 4.1.2 group by子句
      • 4.1.3 having
    • 4.2联合查询（面试重点）
    • • 4.2.1内连接
  • 4.2.2外连接
  • • 4.2.3自连接
    • 4.2.4子查询
    • • 4.2.5合并查询

1.数据库的约束

说明：数据库在使用的时候，对于里面能够存的数据提出要求和限制

1.1约束类型

✅1.NOT NULL - 指示某列不能存储 NULL 值。
✅2.UNIQUE - 保证某列的每行必须有唯一的值。（没有重复的值）
✅3.DEFAULT - 规定没有给列赋值时的默认值。
✅4.PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
✅5.FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。（表的数据必须在另一个表中存在）
✅6.CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

1.2null约束

说明：创建表示，可以指定某一列不为空

//用法
CREATE TABLE 表名 (列名 类型 NOT NULL,列名 类型);

1.3unique：唯一约束

说明：指定列是唯一的，不重复的

//用法
create table 表名（列名 类型 unique,列名 类型）;

1.4default：默认值约束

说明：指定插入数据时，该列为空，默认值unkown

//用法
create table 表名（列名 类名 default 'unkown',列名 类型）;

1.5primary key:主键约束

说明：指定列名1为主键

//用法
create table 表名（列名1 类型 primary key,列名2 类型）;
//对于整型类型的主键，常搭配自增长auto_increment来使用。插入数据对应的字段不给定值时，使用最大值+1
// 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL
列名 类型 PRIMARY KEY auto_increment;

1.6foreign key:外键约束

说明：外键用于关联其它表的主键或唯一键
语法：

foreign key 表1的主键（被设为主键的列名） references 主表（列名）;

例子：创建班级表classes，id为主键：

-- 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识
CREATE TABLE classes (
 id INT PRIMARY KEY auto_increment,
 name VARCHAR(20),
 `desc` VARCHAR(100)
);
//创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键，
//classes_id为外键，关联班级表id
CREATE TABLE student (id INT PRIMARY KEY auto_increment,classes_id int,
 FOREIGN KEY (classes_id) REFERENCES classes(id));

2.表的设计

2.1三大范式

1.一对一：就是一对一的关系，一个学生对应一个学号，一个学号也对应一个学生
2.一对多：就是一对多的关系，一个学生只能对应一个班级，一个班级可以对应多个学生
3.多对多：就是多对多的关系，一个学生可以选择多门课程，一门课程也能对应多名学生。

3.新增

插入结果查询
语法：

insert into 表名1(列名，列名....) select 列名1，列名2 from 表名2；
//先执行查找表名2的列名1，列名2，针对查到的每一个结果都执行插入操作，把列名查找的结果插入到表名1中。

4.查询

4.1聚合查询

4.1.1聚合函数

常见的聚合函数：

说明：exper可以是列名或者表达式；count函数查询的有多少行，如果有null不会计算进去，可以搭配where使用，先筛选在聚合，distinct是去重可写可不写，没有满足条件的就会返回null，sum函数只对数字进行操作。
示例：
count操作

SELECT COUNT(*) FROM 表名;//*表示全列查询
SELECT COUNT(列名或表达式) FROM 表名;

sum操作

select sum(列名或表达式) from 表名;

avg操作

select avg(列名或表达式) from 表名；

max操作

select max(列名或表达式) from 表名；

min操作

select min（列名或表达式） from 表名；

4.1.2 group by子句

说明：SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。
需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。
语法：

select 列名1，sum(列名2,...) from 表名 group by 列名1，列名2；

4.1.3 having

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING。
如果我们使用where是可以的，不过是where的操作是在执行group by 分组操作之前执行。
语法：

select 列名1，sum(列名2,...) from 表名 group by 列名1 having 表达式；

4.2联合查询（面试重点）

多表查询就对多张表的数据进行笛卡尔积。
注意事项：
1.类似于排列组合，查询的列等于所有查询的表的列之和，查询的行等于所有查询的表的行之积。
2.这也是一个危险操作，数据量过大会导致事故。
3.关联查询可以对关联表使用别名。
4.如果查询表的列名有相同的列名，可以使用表名.列名来查询。

语法：

select * from 表名1，表名2......；
select * from 表名1.列名，表名2.列名.....；//表名可以省略
select * from 表名1，表名2......where 表达式；

4.2.1内连接

语法：

select 列名 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其它条件;
select 列名 from 表1 别名1，表2 别名2 where 连接条件 and 其它条件;
//连接条件是两个表的相同的字段，其它条件就是限制条件

4.2.2外连接

说明：外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。没有数据的用null来填补。
语法：

// 左外连接，表1完全显示
select 列名  from 表名1 left join 表名2 on 连接条件;
//右外连接，表2完全显示
select 列名 from 表名1 right join 表名2 on 连接条件;

4.2.3自连接

说明：就是自己连接自己（把行转成列）
语法：

select * from 表名1 as 别名1，表名1 as 别名2 where 连接条件 and 其它条件;

4.2.4子查询

子查询就指嵌入在其它sql语句中的select语句，也叫嵌套查询
1.单行子查询：返回一行记录的子查询
语法：

select * from 表名 where 列名1=（select 列名2 from 表名 where 条件；

2.多行子查询：返回多行记录的子查询
2.1 [not] in关键字：

-- 使用IN
select * from 表名1 where 列名1 in (select 列名2 from 表名2 where 条件);
-- 使用 NOT IN
select * from 表名1 where 列名1 not in (select 列名2 from 表名2 where 条件);

2.2 [not] exists关键字：

//使用 EXISTS
select * from 表名1 where exists (select 列名2 from 表名2 where (条件) and 条件);
//使用 NOT EXISTS
select * from 表名1 where not exists (select 列名2 from 表名2 
where (条件) and 条件);

说明：在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当作一个临时表使用。

4.2.5合并查询

说明：就是把多个查询语句放在一起
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。
1.union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

select * from 表名1 where 条件表达式
union
select * from 表名1 where 条件表达式;
// 或者使用or来实现
select * from 表名1 where 条件表达式 or 条件表达式;

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

select * from 表名1 where 条件表达式
union all
select * from 表名1 where 条件表达式;

这篇关于MySQL表的增删改查（进阶）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！