MySQL表操作进阶（查询操作重点）

已于 2024-07-17 23:36:38 修改
阅读量703 收藏 23
点赞数 18
分类专栏： MySQL数据库 文章标签： mysql 数据库 java
于 2024-07-10 17:30:36 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/2302_78814792/article/details/140278141
版权

文章目录

1. 数据库约束

*约束类型

  • NOT NULL：某列不能存储NULL值
  • UNIQUE：保证某列的每行必须有唯一的值
  • DEFAULT：规定没有给列赋值时的默认值
  • PRIMARY KEY：NOT NULL 和UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
  • FOREIGN KEY：保证一个表中的数据匹配另一个表中的值的参照完整性。
  • CHECK：保证列中的值符合指定的条件。
    注意：约束通常添加在字段类型的后面。

NULL约束

创建表时，指定某列不为空：

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT,
   name VARCHAR(20),
   qq_mail VARCHAR(20)
);

UNIQUE：唯一约束

被修饰的sn列为数值唯一，不重复的列：

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT UNIQUE,
   name VARCHAR(20),
   qq_mail VARCHAR(20)
);

DEFAULT：默认值约束

修饰name列时，当name列为空，默认值为unknown：

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
);

PRIMARY KEY：主键约束

指定id为主键：

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT PRIMARY KEY,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
);

对于整数类型的主键，常配搭自增长拓展auto_increment来使用。插入数据对应字段不给值时，使用最大值+1。

CREATE TABLE student (
   id INT PRIMARY KEY auto_increment,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
);

FOREIGN KEY：外键约束

外键用于关联其他表的主键或唯一键（unique），语法：

foreign key (字段名) references 主表(列)

案例：

  • 创建班级表classes，id为主键：
-- 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
 id INT PRIMARY KEY auto_increment,
 name VARCHAR(20),
 `desc` VARCHAR(100)
);
  • 创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键，classes_id为外键，关联班级表id
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT PRIMARY KEY auto_increment,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20),
   classes_id int,
   FOREIGN KEY (classes_id) REFERENCES classes(id)
);

CHECK约束

check保证sex为男或女：

drop table if exists test_user;
create table test_user (
   id int,
   name varchar(20),
   sex varchar(1),
   check (sex ='男' or sex='女')
);

2. 新增

将一张表中的数据插入到另一张表中，语法：

INSERT INTO 表名 [(column [, column] ...)] SELECT * | (column [, column] [, column] ...) ...

注意：[ ]中的内容为可选择项，插入数据的表可以不罗列出列名（不写默认为全选），from 后面的表不能不写列名，且写的列名要与前面插入数据的表罗列出的列名一一对应。当插入数据的表没有写明列名时，select 后面 只需加上 * 号（表示全选）。

案例：创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的学生数据复制进来，可以复制的字段为name、qq_mail

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
   id INT primary key auto_increment,
   name VARCHAR(20) comment '姓名',
   age INT comment '年龄',
   email VARCHAR(20) comment '邮箱',
   sex varchar(1) comment '性别',
   mobile varchar(20) comment '手机号'
);
-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;

3. 查询（重点）

3.1 聚合查询

* 聚合函数

在学习聚合查询之前，我们得先认识聚合函数，如果说表达式是操作列与列之间的数据，那么聚合函数就是操作行与行之间的数据。以下是几个常见的聚合函数：
[ ]中的内容为可选择项，expr为列名

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

案例：

  • count
    语法：count(列名)
-- 统计班级共有多少同学
SELECT COUNT(*) FROM student;
SELECT COUNT(0) FROM student;
-- 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果
SELECT COUNT(qq_mail) FROM student;

注意：具体来说，COUNT(0)、COUNT(*) 和 COUNT(列名) 在不同情况下有不同的用途和性能考虑：

  1. COUNT(0)：虽然参数是 0，但SQL服务器会忽略这个参数，并计算表中的行数。在大多数数据库系统中，COUNT(0) 的执行效率与 COUNT(*) 非常接近，因为两者都用于计算行数，而不关心列的具体值。

  2. COUNT(*)：计算包括 NULL 在内的所有行的总数。这是最直接的计数行数的方式，通常用于获取表中的总行数。

  3. COUNT(列名)：计算指定列中非 NULL 值的数量。如果列中包含 NULL 值，则这些 NULL 值不会被计入总数。

在实际应用中，COUNT() 是最常用的，因为它直接、简洁且高效。COUNT(0) 虽然在逻辑上与 COUNT() 等效，但在某些SQL方言中，其表现可能略有不同，但在大多数场景下可以视为等同。然而，为了提高代码的可读性和一致性，推荐使用 COUNT(*) 来表示计算行数。

总结来说，COUNT(0) 在 SELECT 语句中用于计算表中的行数，但更常见和推荐的做法是使用 COUNT(*)。

  • sum
    语法：sum（列名/表达式）
    SUM函数接受一个列名或表达式作为参数，并返回该列或表达式中所有非空数值的总和。
-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;
-- 不及格 < 60 的总分，没有结果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;
-- 计算所有学生成绩总分
SELECT SUM(chinese + math + english) FROM exam_result;

注意：
SUM函数仅计算非空值。如果列中包含NULL值，则这些值将被忽略。
SUM函数对于数值数据类型最有效。它不能用于文本或布尔数据类型。如果尝试对非数值列使用SUM函数，将会导致错误。

  • avg
    语法：avg(列名/表达式)
--统计数学平均分
SELECT AVG(math) FROM exam_result;
-- 统计平均总分
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;

注意：
AVG 函数在计算平均值时会忽略NULL值。
如果列中没有非NULL值，则 AVG 函数将返回NULL，而不是0。

  • max / min
    语法：max/min (列名/表达式)
    MAX(MIN) 函数用于从指定列中返回最大值（最小值）。它可以应用于任何数据类型的列，但通常用于数值列。当列中包含非数值数据时，MAX(MIN) 函数会根据字符串的字典顺序返回“ 最大（最小）”的字符串值。如果列中包含NULL值，MAX(MIN) 函数会忽略这些值。
-- 返回英语最高分
SELECT MAX(english) FROM exam_result;
-- 返回总分最高分
SELECT MAX(chinese + math + english) FROM exam_result;
-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;
-- 返回总分最低分
SELECT MIN(chinese + math + english) FROM exam_result;

注意：
当使用 MAX(MIN) 函数时，应确保查询的列包含数值或可排序的字符串类型的数据，否则可能会得到不符合预期的结果。

*GROUP BY子句

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

select column1, sum(column2), .. from table group by column1,column3;

案例：

create table emp(
 id int primary key auto_increment,
 name varchar(20) not null,
 role varchar(20) not null,
 salary numeric(11,2)
);
insert into emp(name, role, salary) values
('马云','服务员', 1000.20),
('马化腾','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);
select role,max(salary),min(salary),avg(salary) from emp group by role;

注意：
group by 也可以加表达式，但是通常建议使用列名以确保清晰和兼容性。

*HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING

案例：显示平均工资低于1500的角色和它的平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role 
having avg(salary)<1500;

3.2 联合查询

*笛卡尔积

在开始联合查询之前，我们要先通过一个图了解笛卡尔积：

select * from 表1，表2；

在这里插入图片描述
在不指明连接条件的情况下 图中 表1 和 表2 进行 笛卡尔积 得到“行乘行”的结果，笛卡尔积在多变连接中具有重要作用，此处仅为两表连接，还可以进行3及以上表的连接，最终得到的表的行数为各表行数的乘积，列数为各表列数的和。

然而，这种笛卡尔积运算连接表的方式却有着很大弊端：设想一下，在实际应用中，表中数据量有时可能达到几百万甚至几千万，这时如果用笛卡尔积进行多个表的连接将会占用数据库巨大的资源且产生大量冗余的数据，届时可能会导致整个数据库瘫痪！为了避免这种情况出现，在写多表连接的语句时应指明连接条件，因而衍生出了以下多种多表连接查询操作：

为了方便演示，我们先初始化测试数据（此前已有的表如student和classes不再新增）：

-- 创建课程表
DROP TABLE IF EXISTS course;
CREATE TABLE course (
   id INT PRIMARY KEY auto_increment,
   name VARCHAR(20)
);
-- 创建课程学生中间表：考试成绩表
DROP TABLE IF EXISTS score;
CREATE TABLE score (
   id INT PRIMARY KEY auto_increment,
   score DECIMAL(3, 1),
   student_id int,
   course_id int,
   FOREIGN KEY (student_id) REFERENCES student(id),
   FOREIGN KEY (course_id) REFERENCES course(id)
);
insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
insert into score(score, student_id, course_id) values (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
(60, 2, 1),(59.5, 2, 5),
(33, 3, 1),(68, 3, 3), (99, 3, 5),
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
(81, 5, 1),(37, 5, 5),
(56, 6, 2),(43, 6, 4),(79, 6, 6),
(80, 7, 2),(92, 7, 6);

*内连接

语法：

--写法一
select 字段 from 表1 [别名1] [inner] join 表2 别名2 on 连接条件 ... and 其他条件 ;
--写法二
select 字段 from 表1 [别名1],表2 [别名2], ... where 连接条件 and ...;

注意：在3及以上表的连接中，写法一中 join 新增表 on 连接条件 此连接条件要使新增表与前面某个表的相关联，例如：

select student.name, course.name, score.score from student join score on student.id = score.student_id join course on score.course_id = course.id;

在这里插入图片描述
使用 join on 语法不能随意调换连接条件的位置，而使用 where 语法则可以随意调换位置，如：

写法一
 select stu.name, cou.name, sco.score from student stu, course cou, score sco where sco.student_id = stu.id and sco.course_id = cou.id;
 写法二
 select stu.name, cou.name, sco.score from student stu, course cou, score sco where sco.course_id = cou.id and sco.student_id = stu.id;

在这里插入图片描述
两个写法得到的结果是一致的。
注意：
这里的sco是score的别名，这里又涉及到了别名的使用，在文章结尾为提到；
sco.score 是指 score 表中的 score 列，以此类推，stu 是 student 表的别名，用 . 符合来访问表中的列，在写列名时，最好加上列名所属的表名，以免因为多个表列名相同而造成混乱；

以下是一些综合查询的案例：

（1）查询 “许仙”同学的成绩

select sco.score from student stu inner join score sco on stu.id=sco.student_id
and stu.name='许仙';
-- 或者
select sco.score from student stu, score sco where stu.id=sco.student_id and
stu.name='许仙';

这里的 inner 可以省略，加上是为了与后面的其他连接方式作区分。

（2）查询所有同学的总成绩，及同学的个人信息：

-- 成绩表对学生表是多对1关系，查询总成绩是根据成绩表的同学id来进行分组的
SELECT
 stu.sn,
 stu.NAME,
 stu.qq_mail,
 sum( sco.score ) 
FROM
 student stu
JOIN score sco ON stu.id = sco.student_id
GROUP BY sco.student_id;

（3）查询所有同学的成绩，及同学的个人信息：

-- 查询出来的都是有成绩的同学，“老外学中文”同学 没有显示
select * from student stu join score sco on stu.id=sco.student_id;
-- 学生表、成绩表、课程表3张表关联查询
SELECT
 stu.id,
 stu.sn,
 stu.NAME,
 stu.qq_mail,
 sco.score,
 sco.course_id,
 cou.NAME
FROM
 student stu
JOIN score sco ON stu.id = sco.student_id
JOIN course cou ON sco.course_id = cou.id
ORDER BY stu.id;

*外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
语法：

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

案例：查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示

-- “老外学中文”同学 没有考试成绩，也显示出来了
select * from student stu left join score sco on stu.id=sco.student_id;
-- 对应的右外连接为：
select * from score sco right join student stu on stu.id=sco.student_id;
-- 学生表、成绩表、课程表3张表关联查询
SELECT
 stu.id,
 stu.sn,
 stu.NAME,
 stu.qq_mail,
 sco.score,
 sco.course_id,
 cou.NAME
FROM
 student stu
 LEFT JOIN score sco ON stu.id = sco.student_id
 LEFT JOIN course cou ON sco.course_id = cou.id
ORDER BY
 stu.id;

*自连接

自连接是内连接的一种特殊形式，指在同一张表连接自身进行查询，我们通过一个案例来理解：

显示所有“计算机原理”成绩比“Java”成绩高的学生信息

-- 先查询“计算机原理”和“Java”课程的id分别为1和3
select id,name from course where name='Java' or name='计算机原理';
-- 再查询成绩表中，“计算机原理”成绩比“Java”成绩 好的信息
SELECT
 s1.* 
FROM
 score s1,
 score s2 
WHERE
 s1.student_id = s2.student_id
 AND s1.score < s2.score
 AND s1.course_id = 1
 AND s2.course_id = 3;
-- 也可以使用join on 语句来进行自连接查询
SELECT
 s1.* 
FROM
 score s1
 JOIN score s2 ON s1.student_id = s2.student_id
 AND s1.score < s2.score
 AND s1.course_id = 1
 AND s2.course_id = 3;

上述代码中，表score通过两个别名 s1 和 s2 进行自连接，筛选出“计算机原理”成绩比“Java”成绩高的成绩，现在关联其他表，得出所有信息：

SELECT
 stu.*,
 s1.score Java,
 s2.score 计算机原理
FROM
 score s1
 JOIN score s2 ON s1.student_id = s2.student_id
 JOIN student stu ON s1.student_id = stu.id
 JOIN course c1 ON s1.course_id = c1.id
 JOIN course c2 ON s2.course_id = c2.id
 AND s1.score < s2.score
 AND c1.NAME = 'Java'
 AND c2.NAME = '计算机原理';

上述代码中，s1 和 s2 表的student_id 要一致，用其中一张表连接 student 表，s1 表与 ‘Java’ 课程表 c1 连接，s2 表与 ‘计算机原理’ 课程表 c2 连接，这样 s1 中就存有‘Java’ 课程成绩，而 s2 中就存有 ‘计算机原理’ 课程成绩，最后保证 s1 的分数 score 大于 s2 的，得出的结果就是所有“计算机原理”成绩比“Java”成绩高的学生信息了。

*子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

  • 单行子查询：返回一行记录的子查询

查询与“不想毕业” 同学的同班同学：

select * from student where classes_id=(select classes_id from student where
name='不想毕业');
  • 多行子查询：返回多行记录的子查询

案例：查询“语文”或“英文”课程的成绩信息

  1. [NOT] IN关键字：
-- 使用IN
select * from score where course_id in (select id from course where
name ='语文' or name ='英文');
-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!= '语文' and name!='英文');
  1. [NOT] EXISTS关键字：
-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou 
where (name='语文' or name='英文') and cou.id = sco.course_id);
-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou 
where (name!='语文' and name!='英文') and cou.id = sco.course_id);
  1. 多列包含
-- 插入重复的分数：score, student_id, course_id列重复
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),
-- 菩提老祖
(60, 2, 1);
-- 查询重复的分数
SELECT
 * 
FROM
 score 
WHERE
 ( score, student_id, course_id ) IN ( SELECT score, student_id, 
course_id FROM score GROUP BY score, student_id, course_id HAVING
count( * ) > 1 );

以上代码通过 group by 将 score, student_id, course_id 都相同的行分为一组，分好组后，再通过having 关键字 和 count( * ) 计算每个组中的行数，行数大于1的组则有重复的分数，输出结果。

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

查询所有比“中文系2019级3班”平均分高的成绩信息：

-- 获取“中文系2019级3班”的平均分，将其看作临时表
SELECT
 avg( sco.score ) score 
FROM
 score sco
 JOIN student stu ON sco.student_id = stu.id
 JOIN classes cls ON stu.classes_id = cls.id
WHERE
 cls.NAME = '中文系2019级3班';

查询成绩表中，比以上临时表平均分高的成绩：

SELECT
 * 
FROM
 score sco,
 (
	SELECT
		avg( sco.score ) score 
	FROM
		score sco
		JOIN student stu ON sco.student_id = stu.id
		JOIN classes cls ON stu.classes_id = cls.id
	WHERE cls.NAME = '中文系2019级3班'
 ) tmp 
WHERE sco.score > tmp.score;

在上述代码中（）中的内容为子查询建立的临时表，tmp 为该表的别名。

*合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION 和 UNION ALL 时，前后查询的结果集中，字段需要一致。

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

案例：查询id小于3，或者名字为“英文”的课程：

select * from course where id<3
union
select * from course where name='英文';
-- 或者使用or来实现
select * from course where id<3 or name='英文';
  • union all
    该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

案例：查询id小于3，或者名字为“Java”的课程:

-- 可以看到结果集中出现重复数据Java
select * from course where id<3
union all
select * from course where name='英文';

4. 总结

经过以上的学习，我们最终可以大致得到一个这样的sql语句：

select 列名/表达式/聚合函数 [as 别名] from (表名 join 表名 on 连接条件...) | (表名，表名... where 连接条件) | (子查询临时表) 
[where 条件] [group by 列名/表达式...] [having 条件][order by 列名...] [limit ...];

不那么严谨的说，在这条语句中，各部分的执行顺序应该为（已有做过实际检验）：from -> where -> group by -> having -> order by -> limit… 在这种执行顺序下，各部分别名的定义和使用就显得尤为严格，在上一篇文章中也有提到（MySQL数据库中表操作），不同的执行顺序导致别名的定义有了先后，别名在未定义前是不允许使用的，因此熟记这样的一条执行顺序在sql代码)时就有很大的帮助了。

以上就是数据库进阶表操作的内容了，虽然还是有很多欠缺的地方，但整理不易，有错误的地方请大家在评论区指出。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值