MySQL表的增删改查(进阶)

2022/3/19 19:28:40

本文主要是介绍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聚合函数

常见的聚合函数:

函数说明
COUNT([DISTINCT] expr)返回查询到的数据的 数量
SUM([DISTINCT] expr)返回查询到的数据的 总和,不是数字没有意义
AVG([DISTINCT] expr)返回查询到的数据的 平均值,不是数字没有意义
MAX([DISTINCT] expr)返回查询到的数据的 最大值,不是数字没有意义
MIN([DISTINCT] expr)返回查询到的数据的 最小值,不是数字没有意义

说明: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表的增删改查(进阶)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程