特殊形式的查询

最新推荐文章于 2020-11-07 21:46:32 发布
最新推荐文章于 2020-11-07 21:46:32 发布
阅读量187 收藏
点赞数
分类专栏： 学习数据库笔记 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/HanghangzZ/article/details/109552887
版权

子查询

指把一个查询语句嵌套在另一个查询语句中

SELECT 字段名称,... FROM tbl_name1 WHERE col_name1=(SELECT col_name2 FROM tbl_name2)

我们可以看到有两层查询结果，内层查询语句的结果可以作为外层查询语句的条件。

由[NOT] IN引发的子查询

SELECT 字段名称,... FROM tbl_name WHERE col_name [NOT] IN (条件)

由比较运算符引出的子查询

SELECT 字段名称,... FROM tbl_name WHERE col_name 比较运算符(条件)

由EXISTS引发的子查询

如果内层查询查到了结果存在，那么返回的是真值，也就执行外层查询；不然的话返回的是假值，不执行外层查询。

SELECT 字段名称,... FROM tbl_name WHERE col_name EXISTS(内层查询)

由ANY SOME ALL引发的子查询

在这里插入图片描述

我们来稍微解释几个。

>=ANY表示大于等于内层查询的最小值。也就是大于等于内层查询的任意一个。

>=SOME表示大于等于最小值。也就是大于等于内层查询的有一些。

>=ALL代表大于等于内层查询的最大值。也就是大于等于内层查询的每一个

SELECT 字段名称,... FROM tbl_name WHERE col_name 关系运算符 ANY|SOME|ALL(内层查询);

由子查询带来的一些语句

INSERT … SELECT

将查询出来的结果写入到表中。包括INSERT … SET和UPDATE … SET 的形式都可以使用到我们的子查询。实现数据的导入。

CREATE … SELECT

在创建表时将另外一个表中的数据写入到表中。实现数据的导入。

CREATE TABLE tbl_name(
	...
)SELECT 字段名称,... FROM emp;

-- 创建表结构一样的表
CREATE TABLE tbl_name1 LIKE tbl_name2;

测试

测试子查询

测试由IN引发的子查询

下面是测试用表的内容

SELECT * FROM emp;
+----+----------+-----+------+------+-------+
| id | username | age | sex  | addr | depId |
+----+----------+-----+------+------+-------+
|  1 | king     |  25 | 保密 | 北京 |     1 |
|  2 | queen    |  26 | 保密 | 北京 |     2 |
|  3 | imooc    |  27 | 保密 | 北京 |     1 |
|  4 | lily     |  28 | 保密 | 北京 |     1 |
|  5 | rose     |  29 | 保密 | 北京 |     3 |
|  6 | john     |  30 | 保密 | 北京 |     3 |
|  7 | 测试用户 |  39 | 保密 | 北京 |     6 |
+----+----------+-----+------+------+-------+

SELECT * FROM dep;
+----+---------------+-----------------+
| id | depName       | depDesc         |
+----+---------------+-----------------+
|  1 | PHP教学部     | 研发PHP课件     |
|  2 | JAVA教学部    | 研发JAVA课件    |
|  3 | WEB前端教学部 | 研发WEB前端课件 |
|  4 | IOS教学部     | 研发IOS课件     |
+----+---------------+-----------------+

查询出emp表中的depId在dep表中有对应的记录

SELECT * FROM emp WHERE depID IN(1,2,3,4);

+----+----------+-----+------+------+-------+
| id | username | age | sex  | addr | depId |
+----+----------+-----+------+------+-------+
|  1 | king     |  25 | 保密 | 北京 |     1 |
|  2 | queen    |  26 | 保密 | 北京 |     2 |
|  3 | imooc    |  27 | 保密 | 北京 |     1 |
|  4 | lily     |  28 | 保密 | 北京 |     1 |
|  5 | rose     |  29 | 保密 | 北京 |     3 |
|  6 | john     |  30 | 保密 | 北京 |     3 |
+----+----------+-----+------+------+-------+

我们可以发现在上面IN的括号中就是dep表中id的值，我们可以替换一下里面已有的内容，改成查询语句

SELECT * FROM emp WHERE depId IN(SELECT id FROM dep);

+----+----------+-----+------+------+-------+
| id | username | age | sex  | addr | depId |
+----+----------+-----+------+------+-------+
|  1 | king     |  25 | 保密 | 北京 |     1 |
|  2 | queen    |  26 | 保密 | 北京 |     2 |
|  3 | imooc    |  27 | 保密 | 北京 |     1 |
|  4 | lily     |  28 | 保密 | 北京 |     1 |
|  5 | rose     |  29 | 保密 | 北京 |     3 |
|  6 | john     |  30 | 保密 | 北京 |     3 |
+----+----------+-----+------+------+-------+

还可以使用NOT IN

SELECT * FROM emp WHERE depId NOT IN(SELECT id FROM dep);

+----+----------+-----+------+------+-------+
| id | username | age | sex  | addr | depId |
+----+----------+-----+------+------+-------+
|  7 | 测试用户 |  39 | 保密 | 北京 |     6 |
+----+----------+-----+------+------+-------+
测试由比较运算符引出的子查询
-- 创建学院表
CREATE TABLE IF NOT EXISTS stu(
	id TINYINT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '学院名称',
    score TINYINT UNSIGNED NOT NULL COMMENT '学院考试分数'
);
INSERT stu(username,score) VALUES('king',95),
('queen',75),
('zhangsan',69),
('lisi',78),
('wangwu',87),
('zhaoliu',88),
('tianqi',98),
('ceshi',99),
('tiancai',50);

-- 创建分数基本表
CREATE TABLE IF NOT EXISTS level(
	id TINYINT UNSIGNED KEY AUTO_INCREMENT COMMENT '编号',
    score TINYINT UNSIGNED COMMENT '分数'
);
INSERT level(score) VALUES(90),
(80),
(70);

-- 查询出成绩优秀的学员
SELECT id,username,score FROM stu
WHERE score>=(SELECT score FROM level WHERE id=1);

+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  1 | king     |    95 |
|  7 | tianqi   |    98 |
|  8 | ceshi    |    99 |
+----+----------+-------+

-- 查询出没有得到评级的学员
SELECT id,username,score FROM stu
WHERE score<=(SELECT score FROM level WHERE id=3);

+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  3 | zhangsan |    69 |
|  9 | tiancai  |    50 |
+----+----------+-------+
测试由EXISTS引发的子查询

下面是测试表的内容

SELECT * FROM emp;
+----+----------+-----+------+------+-------+
| id | username | age | sex  | addr | depId |
+----+----------+-----+------+------+-------+
|  1 | king     |  25 | 保密 | 北京 |     1 |
|  2 | queen    |  26 | 保密 | 北京 |     2 |
|  3 | imooc    |  27 | 保密 | 北京 |     1 |
|  4 | lily     |  28 | 保密 | 北京 |     1 |
|  5 | rose     |  29 | 保密 | 北京 |     3 |
|  6 | john     |  30 | 保密 | 北京 |     3 |
|  7 | 测试用户 |  39 | 保密 | 北京 |     6 |
+----+----------+-----+------+------+-------+

SELECT * FROM dep;
+----+---------------+-----------------+
| id | depName       | depDesc         |
+----+---------------+-----------------+
|  1 | PHP教学部     | 研发PHP课件     |
|  2 | JAVA教学部    | 研发JAVA课件    |
|  3 | WEB前端教学部 | 研发WEB前端课件 |
|  4 | IOS教学部     | 研发IOS课件     |
+----+---------------+-----------------+

对于下面语句的内查询，显然是不存在的。

SELECT * FROM emp WHERE EXISTS(SELECT depName FROM dep WHERE id=10);

Empty set

对于下面语句的内查询，显然是存在的，外查询也就执行了。

SELECT * FROM emp WHERE EXISTS(SELECT depName FROM dep WHERE id=1);

+----+----------+-----+------+------+-------+
| id | username | age | sex  | addr | depId |
+----+----------+-----+------+------+-------+
|  1 | king     |  25 | 保密 | 北京 |     1 |
|  2 | queen    |  26 | 保密 | 北京 |     2 |
|  3 | imooc    |  27 | 保密 | 北京 |     1 |
|  4 | lily     |  28 | 保密 | 北京 |     1 |
|  5 | rose     |  29 | 保密 | 北京 |     3 |
|  6 | john     |  30 | 保密 | 北京 |     3 |
|  7 | 测试用户 |  39 | 保密 | 北京 |     6 |
+----+----------+-----+------+------+-------+
测试由ANY|SOME|ALL引发的子查询

下面是测试表的内容

SELECT * FROM stu;
+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  1 | king     |    95 |
|  2 | queen    |    75 |
|  3 | zhangsan |    69 |
|  4 | lisi     |    78 |
|  5 | wangwu   |    87 |
|  6 | zhaoliu  |    88 |
|  7 | tianqi   |    98 |
|  8 | ceshi    |    99 |
|  9 | tiancai  |    50 |
+----+----------+-------+

SELECT * FROM level;
+----+-------+
| id | score |
+----+-------+
|  1 |    90 |
|  2 |    80 |
|  3 |    70 |
+----+-------+
ANY
SELECT * FROM stu
WHERE score>=ANY(SELECT score FROM LEVEL);

+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  1 | king     |    95 |
|  2 | queen    |    75 |
|  4 | lisi     |    78 |
|  5 | wangwu   |    87 |
|  6 | zhaoliu  |    88 |
|  7 | tianqi   |    98 |
|  8 | ceshi    |    99 |
+----+----------+-------+

SELECT * FROM stu
WHERE score=ANY(SELECT score FROM level);

Empty set
SOME
SELECT * FROM stu
WHERE score>=SOME(SELECT score FROM LEVEL);

+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  1 | king     |    95 |
|  2 | queen    |    75 |
|  4 | lisi     |    78 |
|  5 | wangwu   |    87 |
|  6 | zhaoliu  |    88 |
|  7 | tianqi   |    98 |
|  8 | ceshi    |    99 |
+----+----------+-------+
ALL
SELECT * FROM stu
WHERE score>=ALL(SELECT score FROM LEVEL);

+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  1 | king     |    95 |
|  7 | tianqi   |    98 |
|  8 | ceshi    |    99 |
+----+----------+-------+

SELECT * FROM stu
WHERE score<ALL(SELECT score FROM level);

+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  3 | zhangsan |    69 |
|  9 | tiancai  |    50 |
+----+----------+-------+

SELECT * FROM stu
WHERE score!=ALL(SELECT score FROM level);

+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  1 | king     |    95 |
|  2 | queen    |    75 |
|  3 | zhangsan |    69 |
|  4 | lisi     |    78 |
|  5 | wangwu   |    87 |
|  6 | zhaoliu  |    88 |
|  7 | tianqi   |    98 |
|  8 | ceshi    |    99 |
|  9 | tiancai  |    50 |
+----+----------+-------+

测试CREATE … INSERT 和 INSERT … SELECT

-- 创建一个user4表，将emp表中的id username字段插入到新建的user4表中
CREATE TABLE user4(
	id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20)
)SELECT id,username FROM emp;

mysql> SELECT * FROM user4;
+----+----------+
| id | username |
+----+----------+
|  1 | king     |
|  2 | queen    |
|  3 | imooc    |
|  4 | lily     |
|  5 | rose     |
|  6 | john     |
|  7 | 测试用户 |
+----+----------+

-- 将user3表中的用户名表写入到user4表中
INSERT user4(username) SELECT username FROM user3;

SELECT * FROM user3;
+----+----------+------------+-------+
| id | username | email      | proId |
+----+----------+------------+-------+
|  1 | a        | 123@qq.com |     1 |
|  2 | b        | 123@qq.com |     1 |
|  3 | c        | 123@qq.com |     1 |
|  4 | d        | 123@qq.com |     2 |
|  5 | e        | 123@qq.com |     3 |
|  6 | f        | 123@qq.com |     1 |
|  7 | g        | 123@qq.com |     1 |
+----+----------+------------+-------+

SELECT * FROM user4;
+----+----------+
| id | username |
+----+----------+
|  1 | king     |
|  2 | queen    |
|  3 | imooc    |
|  4 | lily     |
|  5 | rose     |
|  6 | john     |
|  7 | 测试用户 |
|  8 | a        |
|  9 | b        |
| 10 | c        |
| 11 | d        |
| 12 | e        |
| 13 | f        |
| 14 | g        |
+----+----------+
由这两条语句实现数据的导入
-- 创建表结构与user4一样的表user5
-- 注意刚创建完的表是一个空表
CREATE TABLE user5 LIKE user4;

-- 将user4中的数据导入user5
INSERT user5 SELECT id,username FROM user4;

SELECT * FROM user5;
+----+----------+
| id | username |
+----+----------+
|  1 | king     |
|  2 | queen    |
|  3 | imooc    |
|  4 | lily     |
|  5 | rose     |
|  6 | john     |
|  7 | 测试用户 |
|  8 | a        |
|  9 | b        |
| 10 | c        |
| 11 | d        |
| 12 | e        |
| 13 | f        |
| 14 | g        |
+----+----------+

接下来用 INSERT … SET 加上子查询 来实现一下导入数据

INSERT user5 SET username=(SELECT username FROM stu WHERE id=9);

SELECT * FROM user5;
+----+----------+
| id | username |
+----+----------+
|  1 | king     |
|  2 | queen    |
|  3 | imooc    |
|  4 | lily     |
|  5 | rose     |
|  6 | john     |
|  7 | 测试用户 |
|  8 | a        |
|  9 | b        |
| 10 | c        |
| 11 | d        |
| 12 | e        |
| 13 | f        |
| 14 | g        |
| 15 | tiancai  |
+----+----------+

并且因为user5的username字段并不是唯一的，所以我们可以插入重复的值，我们再次执行上面那条插入语句，来介绍一下另一个东东

| 15 | tiancai  |
| 16 | tiancai  |
+----+----------+

-- 去除重复的记录
SELECT DISTINCT(username) FROM user5;

+----------+
| username |
+----------+
| king     |
| queen    |
| imooc    |
| lily     |
| rose     |
| john     |
| 测试用户 |
| a        |
| b        |
| c        |
| d        |
| e        |
| f        |
| g        |
| tiancai  |
+----------+

但是实际上它并没有真的去除掉重复值，只是在这一次查询中去除掉了。

HanghangzZ
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql修改语文成绩为100_mysql-select语句小结
weixin_30240765的博客
02-08 964
首先先创建一个班学生的信息，并插入一些数据来解释用法：create table student(id int not null,name varchar(20) not null,chinese float(5,2) not null default 0.0,math float(5,2) not null default 0.0,english float(5,2) not null defau...
[作业答案]MySQL数据库课后作业-‘查询student表的所有记录‘-‘查询student表的第2条到4条记录‘-‘从student表中查询计算机系和英语系的学生的信息‘
热门推荐
qq_43055855的博客
11-02 2万+
文章目录前言一、题目二、解题步骤1.创建数据库、表。并为之添加数据。2.新建查询总结 前言        今天老师讲完了MySQL数据库基础，发了一份文档下来，有一些简单的题目布置给我们完成。再次记录一下自己的课后习题，加深印象。 一、题目 二、解题步骤 1.创建数据库、表。并为之添加数据。 咱先依次创建数据库数据表： 然后为之添加数据： 2.新建查询 查询student表的所有记录语句： select * from s
【数据库复习】数据库操作 ，数据库中表的操作 ，表中的增删查改CRUD
仙女猴的博客
08-27 283
数据库的操作 查看有哪些数据库show databases; 用哪个数据库use db_name; 创建数据库create database [if not exists] db_name; 删除数据库drop database [if exists] db_name; 查看当前用的数据库是哪个 select database(); 查看当前数据库下创建了哪些表show tables 表操作 1. 建表 drop table if exists stu_test; create table stu_te
oracle中的各种命令（关于表的增删改查）
qq_43463977的博客
11-20 9174
–创建用户 create user xinke identified by orcl; –为创建的用户赋予权限 grant resource,connect to xinke; –删除用户同时删除其下的对象（table等） drop user tea cascade; –查询当前日期 select sysdate from dual; –虚拟表，辅助查询 select * from dual; –...
Mysql语法教程
qq_44932835的博客
05-01 621
很久没用了,今天把知识系统的梳理一遍 文章目录一.数据库概念二.Mysql安装与使用三.SQL基本概念与通用语法四. 数据库的CRUD操作五.数据库表的CRUD操作六. 数据库表中记录的基本操作七.数据库的查询操作八.表的约束九.多表操作十.三大范式十一.数据库的备份和还原十二.多表查询十三.子查询十四.多表查询练习十五.事务十六.用户管理和权限管理 一.数据库概念 二.Mysql安装与使用 三....
XYCMS代理商查询认证系统
04-06
一开始也想用证书形式表现，发现证书其实没有必要，查询验证为真代理商一般就OK了，后续发现不足，慢慢完善。感谢大家一致支持XYCMS。如喜欢XYCMS系统，多多支持我们。需要定做程序也可以联系我们定做的。声明：本...
SQLServer2008查询性能优化 2/2
03-02
内容涵盖了数据库应用系统中各种性能瓶颈的表现形式及其发生的根源和解决方法，从硬件瓶颈到查询、索引设计以及数据库管理等，贯穿了数据库系统知识的各个方面。最后以一个实际的工作负载将所有技巧联系起来，并且...
SQLServer2008查询性能优化 1/2
03-02
内容涵盖了数据库应用系统中各种性能瓶颈的表现形式及其发生的根源和解决方法，从硬件瓶颈到查询、索引设计以及数据库管理等，贯穿了数据库系统知识的各个方面。最后以一个实际的工作负载将所有技巧联系起来，并且...
三菱plc特殊继电器和寄存器功用说明.xlsx
04-01
三菱plc特殊继电器和寄存器功用说明，自己制作，excel表格形式，方便在手机上查询
cq-prolyfill：针对CSS容器查询的Prolyfill
02-04
容器查询Prolyfill 这是特殊版本的（aka元素查询）的。 您可以在阅读有关该想法及其内部工作方式的更多信息。演示版可以在这里找到正在使用的容器查询的快速演示： : 用法借助此prolyfill，您可以按以下形式在CSS中...
Java学习进程——MySQL基础学习
qq_40523277的博客
10-13 167
MySQL多表查询： select name,password,ppd from where a.id=b.id; 等价于 select name,password,ppd from a join b on a.id=b.id;
sqlite3创建学生管理数据库
qq_33437675的博客
09-09 2181
1、创建student表单，id主键，name非空，score默认为0 create table student( id int primary key not null, name text not null, score real default 0.0 check(score>=0)); 2、任意插入10组数据，姓名成绩自定义 第一种：insert into student(id,name,score)values(1,'tianbo',90); 第二种：inser
SQL特殊语句查询
我的笔记
12-18 402
top语句：用于规定要返回的记录数目 select top 5 name from Students /*查询学生表中，前5名学生的姓名*/ select top 20 percent name from Students /*查询学生表中，前20%的学生姓名*/ /*查询员工工资前十员工*/ select top 10 * from emp order by sal /*查询员工...
外键约束
HanghangzZ的博客
11-07 2572
外键约束 只有InnoDB存储引擎支持外键，并且在创建外键的时候，我们子表的外键必须关联主表的主键。 外键字段依赖于已经存在的表的一个主键，所以一定要先有主表。 我们的外键字段必须和我们主表的主键字段的数据类型要相似：如果是数值型，两者必须类型一致，并且同时为有无符号；如果是字符串型，两者的长度可以不同，但是类型必须相同。 如果子表的外键字段没有创建索引，但是当我们创建完外键之后，它会自动的添加索引。 如果子表中有记录外键关联着主表中的记录，那么主表中的这条记录以及主表是无法删除的，子表中的这些记录可以删
自身连接查询
HanghangzZ的博客
11-07 694
自身连接查询 无限级分类的实现形式 测试 这里的父id是什么东西呢？ 例如，在一个电商网站上会有侧边的导航栏，导航栏里面可能会有很多次级栏。 比如一个服装栏，它可以作为顶级分类，它的父id就是0； 长袖和裙子隶属于服装，父id为1； 8分袖隶属于长袖，父id为2 -- 创建数据库分类表 CREATE TABLE cate1( id SMALLINT UNSIGNED KEY AUTO_INCREMENT COMMENT '编号', cateName VARCHAR(100) NOT NULL U
数据表相关操作
HanghangzZ的博客
10-01 648
数据表是由行和列组成的二维网络，为什么说MySQL是典型的关系型数据库，就是因为表和表之间存在关系 数据表相关操作 数据表 是数据库最重要的成分之一，数据是保存在数据表中的 数据表由行(row)和列(column)组成 每个数据表中至少有一列，行可以有零行、一行或者多行组成 表明要求唯一，不要含有特殊字符，最好含义明确 创建表 CREATE TABLE [IF NOT EXISTS] tbl_name{ ​ 字段名称 字段类型 [完整性约束条件], ​ 字段名称 字段类型 [完整性约束条
MySQL多表查询
HanghangzZ的博客
11-07 180
MySQL多表查询 笛卡尔积的形式 将几个表的每一个记录都组合，详情见下面的测试。 这个形式无法满足我们的需求。 内连接的形式 查询两个表中符合连接条件的记录。内连接的使用比外连接要多的多。 SELECT 字段名称,... FROM tbl_name1 [INNER] JOIN tbl_name2 ON 连接条件 外连接的形式 左外连接 SELECT 字段名称,... FROM tbl_name1 LEFT [OUTER] JOIN tbl_name2 ON 连接条件 先显示左表中的全部记录，再去右表
MySQL常用函数
HanghangzZ的博客
11-07 180
MySQL常用函数 数学函数 CEIL() 进一取整 SELECT CEIL(1.2); +-----------+ | CEIL(1.2) | +-----------+ | 2 | +-----------+ FLOOR() 舍掉小数部分 SELECT FLOOR(1.9); +------------+ | FLOOR(1.9) | +------------+ | 1 | +------------+ ROUND() 四舍五入 -- 最后的一个参数表示四舍五入到
oracle 并行查询
最新发布
06-13
在 Oracle 数据库中，通过并行查询可以将一个大查询拆分成多个小查询并行执行，以提高查询速度，...同时，对于一些特殊的查询问题，如锁竞争、I/O 瓶颈等，使用并行查询可能会产生负面影响，需要进行特别的优化处理。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值