MySQL高级SQL语言常用查询与连接查询

已于 2024-04-09 09:29:35 修改
阅读量1.4k 收藏 26
点赞数 19
分类专栏： 关系型数据库MySQL 文章标签： sql 子查询 mysql视图 连接查询
于 2024-03-27 18:20:00 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_64612585/article/details/137043692
版权

前言

对 MySQL 数据库，除了使用基本语言处理一些简单的事务外，还可以使用高级SQL语言用于复杂的数据库操作。包括多表联合查询、子查询、触发器、存储过程和视图等功能。

目录

一、数据库函数

1. 数学函数

2. 聚合函数

3. 字符串函数

4. 日期时间函数

二、常用查询

1. 按关键字排序

1.1 语法格式

1.2 示例

2. 区间判断及查询去重

2.1 and/or（且/或）

2.2 查询不重复记录

3. 分组查询

3.1 语法格式

3.2 示例

4. 限制结果条目

4.1 语法格式

4.2 示例

5. 设置别名

5.1 语法格式

5.2 示例

6. 通配符

6.1 一般格式 

6.2 示例

7. 子查询

7.1 格式

7.2 查询 

7.3 删除

7.4 写入

7.5 更新

7.6 exists 关键字查询

7.7 子查询别名

7.8 返回值取反

三、mysql 视图

1. 概述

2. 视图与表的区别

3. 视图操作

3.1 视图创建

3.2 查看表状态

3.3 视图查询

3.4 视图修改

3.5 视图删除

4. null 值 

四、连接查询

1. 概述

2. 内连接

3. 左连接

4. 右连接

一、数据库函数

1. 数学函数

数据库内存储的记录，经常要进行一系列的算术操作，所以 MySQL 支持很多数学函数。

常用的数学函数如下：

数学函数描述
abs(x)返回 x 的绝对值
rand()返回 0 到 1 的随机数
mod(x,y)返回 x 除以 y 以后的余数
power(x,y)返回 x 的 y 次方
round(x)返回离 x 最近的整数
round(x,y)保留 x 的 y 位小数四舍五入后的值
sqrt(x)返回 x 的平方根
truncate(x,y)返回数字 x 截断为 y 位小数的值
ceil(x)返回大于或等于 x 的最小整数
floor(x)返回小于或等于 x 的最大整数
greatest(x1,x2…)返回集合中最大的值
least(x1,x2…)返回集合中最小的值

示例：

mysql> select abs(-2),rand(),mod(3,2),power(2,2),round(5),round(1.295,2),sqrt(4),truncate(1.295,2),ceil(5),floor(55),greatest(1,3,5),least(1,3,5);
+---------+--------------------+----------+------------+----------+----------------+---------+-------------------+---------+----------+-----------------+--------------+
| abs(-2) | rand()             | mod(3,2) | power(2,2) | round(5) | round(1.295,2) | sqrt(4) | truncate(1.295,2) | ceil(5) | floor(5) | greatest(1,3,5) | least(1,3,5) |
+---------+--------------------+----------+------------+----------+----------------+---------+-------------------+---------+----------+-----------------+--------------+
|       2 | 0.5765201352318005 |        1 |          4 |        5 |           1.30 |       2 |              1.29 |       5 |        5 |               5 |            1 |
+---------+--------------------+----------+------------+----------+----------------+---------+-------------------+---------+----------+-----------------+--------------+

2. 聚合函数

MySQL 中的聚合函数用于对一组值执行计算，并返回单个值作为结果。这些函数可以对数据进行汇总、统计或计算，常见的聚合函数包括 sum、count、avg、max 和 min。

  • avg()：返回指定列的平均值
  • count()：返回指定列中非 NULL 值的个数
  • min()：返回指定列的最小值
  • max()：返回指定列的最大值
  • sum()：返回指定列的所有值之和

示例：

mysql> select * from student;
+----+----------+----------+------+-------+
| id | name     | address  | age  | score |
+----+----------+----------+------+-------+
|  1 | zhangsan | nanjin   | 10   |  90.5 |
|  2 | lisi     | nanjin   | 50   |  65.0 |
|  3 | wangwu   | beijin   | 30   |  70.0 |
|  4 | zhaoliu  | shanghai | 30   |  50.0 |
|  5 | zhouqi   | hangzhou | 40   |  65.0 |
+----+----------+----------+------+-------+

mysql> select sum(score) from student;
+------------+
| sum(score) |
+------------+
|      340.5 |
+------------+
mysql> select min(score) from student;
+------------+
| min(score) |
+------------+
|       50.0 |
+------------+

3. 字符串函数

在 MySQL 中，字符串函数用于处理和操作文本数据。这些函数允许你执行各种操作，如字符串连接、截取、查找、替换等。

常见的字符函数如下：

字符串函数描述
length(x)返回字符串 x 的长度
trim()从字符串的开头和结尾去除空格或其他指定字符
concat(x,y)将提供的参数 x 和 y 拼接成一个字符串
upper(x)将字符串 x 的所有字母变成大写字母
lower(x)将字符串 x 的所有字母变成小写字母
left(x,y)返回字符串 x 的前 y 个字符
right(x,y)返回字符串 x 的后 y 个字符
repeat(x,y)将字符串 x 重复 y 次
space(x)返回 x 个空格
replace(x,y,z)将字符串 z 替代字符串 x 中的字符串 y
strcmp(x,y)比较 x 和 y，返回的值可以为-1,0,1
substring(x,y,z)获取从字符串 x 中的第 y 个位置开始长 度为 z 的字符串
reverse(x)将字符串 x 反转

示例：

mysql> select length('ab c'),trim(' abc'),concat('a','b'),upper('abc'),lower('ABC'),left('abc',2),right('abc',2),repeat('ab',2),space(2),replace('heaao','aa','ll'),strcmp('1','2'),substring('abcd',2,2),reverse('abc');
+----------------+--------------+-----------------+--------------+--------------+---------------+----------------+----------------+----------+----------------------------+-----------------+-----------------------+----------------+
| length('ab c') | trim(' abc') | concat('a','b') | upper('abc') | lower('ABC') | left('abc',2) | right('abc',2) | repeat('ab',2) | space(2) | replace('heaao','aa','ll') | strcmp('1','2') | substring('abcd',2,2) | reverse('abc') |
+----------------+--------------+-----------------+--------------+--------------+---------------+----------------+----------------+----------+----------------------------+-----------------+-----------------------+----------------+
|              4 | abc          | ab              | ABC          | abc          | ab            | bc             | abab           |          | hello                      |              -1 | bc                    | cba            |
+----------------+--------------+-----------------+--------------+--------------+---------------+----------------+----------------+----------+----------------------------+-----------------+-----------------------+----------------+

4. 日期时间函数

在 MySQL 中，时间日期函数用于处理日期、时间和时间戳数据，执行各种操作，如日期加减、格式化、提取部分日期等。

常见的日期时间函数如下：

日期时间函数描述
curdate()返回当前时间的年月日
curtime()返回当前时间的时分秒
now()返回当前时间的日期和时间
month(x)返回日期 x 中的月份值
week(x)返回日期 x 是年度第几个星期
hour(x)返回 x 中的小时值
minute(x)返回 x 中的分钟值
second(x)返回 x 中的秒钟值
dayofweek(x)返回 x 是星期几，1 星期日，2 星期一
dayofmonth(x)计算日期 x 是本月的第几天
dayofyear(x)计算日期 x 是本年的第几天

示例：

mysql> select curdate(),curtime(),now(),month('2024-03-26'),week(curdate()),hour('18:34:08'),minute(curtime()),second(curtime()),dayofweek(curdate()),dayofmonth(curdate()),dayofyear(curdate());
+------------+-----------+---------------------+---------------------+-----------------+------------------+-------------------+-------------------+----------------------+-----------------------+----------------------+
| curdate()  | curtime() | now()               | month('2024-03-26') | week(curdate()) | hour('18:34:08') | minute(curtime()) | second(curtime()) | dayofweek(curdate()) | dayofmonth(curdate()) | dayofyear(curdate()) |
+------------+-----------+---------------------+---------------------+-----------------+------------------+-------------------+-------------------+----------------------+-----------------------+----------------------+
| 2024-03-26 | 18:37:45  | 2024-03-26 18:37:45 |                   3 |              12 |               18 |                37 |                45 |                    3 |                    26 |                   86 |
+------------+-----------+---------------------+---------------------+-----------------+------------------+-------------------+-------------------+----------------------+-----------------------+----------------------+

二、常用查询

1. 按关键字排序

使用 select 语句可以将需要的数据从 mysql 数据库中查询出来，可以使用 order by 语句来对语句实现排序，可针对多个字段。

1.1 语法格式

select 字段1,字段2,... from 表名 order by 字段a,字段b,... [desc];
# ASC：升序，默认，可以省略
# DESC：降序
# order by 前面也可以使用 where 进一步筛选
注意：
# order by 语句使用多个字段来进行排序，当第一个字段相同的记录存在多条，重复记录再按照第二个字段进行排序
# order by 后面跟多个字段时，字段之间英文逗号隔开，优先级是按先后顺序而定；但 order by 之后的第一个参数只有在出现相同值时，第二个字段才有意义（生效）

1.2 示例

准备环境：

mysql> create table student (id int(5) primary key,name char(15),address varchar(20),age char(5),score decimal(3,1));
mysql> insert into student values (1,'zhangsan','nanjin',10,90.5);
mysql> insert into student values (2,'lisi','nanjin',65,80);
mysql> insert into student values (3,'wangwu','beijin',30,70);
mysql> insert into student values (4,'zhaoliu','shanghai',30,50);
mysql> insert into student values (5,'zhouqi','hangzhou',40,65);
mysql> select * from student;
+----+----------+----------+------+-------+
| id | name     | address  | age  | score |
+----+----------+----------+------+-------+
|  1 | zhangsan | nanjin   | 10   |  90.5 |
|  2 | lisi     | nanjin   | 50   |  65.0 |
|  3 | wangwu   | beijin   | 30   |  70.0 |
|  4 | zhaoliu  | shanghai | 30   |  50.0 |
|  5 | zhouqi   | hangzhou | 40   |  65.0 |
+----+----------+----------+------+-------+
5 rows in set (0.00 sec)

示例1：按分数分别升序降序排序，只显示 name 和 score 字段

升序：
mysql> select name,score from student order by score asc;
+----------+-------+
| name     | score |
+----------+-------+
| zhaoliu  |  50.0 |
| lisi     |  65.0 |
| zhouqi   |  65.0 |
| wangwu   |  70.0 |
| zhangsan |  90.5 |
+----------+-------+

降序：
mysql> select name,score from student order by score desc;
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan |  90.5 |
| wangwu   |  70.0 |
| lisi     |  65.0 |
| zhouqi   |  65.0 |
| zhaoliu  |  50.0 |
+----------+-------+

示例2：筛选地址是 nanjin 的学生按分数降序排列，只显示 name、address、score 字段

mysql> select name,address,score from student where address='nanjin' order by score desc;
+----------+---------+-------+
| name     | address | score |
+----------+---------+-------+
| zhangsan | nanjin  |  90.5 |
| lisi     | nanjin  |  65.0 |
+----------+---------+-------+

示例3：查询信息先按 score 降序排列，相同的按年龄升序排列，只显示 name、score、age 字段

mysql> select name,score,age from student order by score desc,age;
+----------+-------+------+
| name     | score | age  |
+----------+-------+------+
| zhangsan |  90.5 | 10   |
| wangwu   |  70.0 | 30   |
| zhouqi   |  65.0 | 40   |
| lisi     |  65.0 | 50   |
| zhaoliu  |  50.0 | 30   |
+----------+-------+------+

2. 区间判断及查询去重

2.1 and/or（且/或）

语法格式：

select *(或者字段) from 表名 where 条件1 and/or 条件2;

示例1：查找成绩大于80，或小于60分的信息

mysql> select * from student where score>80 or score<60;
+----+----------+----------+------+-------+
| id | name     | address  | age  | score |
+----+----------+----------+------+-------+
|  1 | zhangsan | nanjin   | 10   |  90.5 |
|  4 | zhaoliu  | shanghai | 30   |  50.0 |
+----+----------+----------+------+-------+

示例2：查找成绩大于等于60，且小于80分的信息

mysql> select * from student where score>=60 or score<80;
+----+----------+----------+------+-------+
| id | name     | address  | age  | score |
+----+----------+----------+------+-------+
|  1 | zhangsan | nanjin   | 10   |  90.5 |
|  2 | lisi     | nanjin   | 50   |  65.0 |
|  3 | wangwu   | beijin   | 30   |  70.0 |
|  4 | zhaoliu  | shanghai | 30   |  50.0 |
|  5 | zhouqi   | hangzhou | 40   |  65.0 |
+----+----------+----------+------+-------+

嵌套多条件：

语法格式：

select *(或者字段) from 表名 where 条件1 and/or (条件2 and/or 条件3);

示例3：查找成绩大于90，或满足大于50且小于70分的信息

mysql> select * from student where score>90 or (score>50 and score<70);
+----+----------+----------+------+-------+
| id | name     | address  | age  | score |
+----+----------+----------+------+-------+
|  1 | zhangsan | nanjin   | 10   |  90.5 |
|  2 | lisi     | nanjin   | 50   |  65.0 |
|  5 | zhouqi   | hangzhou | 40   |  65.0 |
+----+----------+----------+------+-------+

2.2 查询不重复记录

语法格式：

select distinct 字段 from 表名;

示例：去掉 address 字段重复信息

mysql> select distinct address from student;
+----------+
| address  |
+----------+
| nanjin   |
| beijin   |
| shanghai |
| hangzhou |
+----------+

3. 分组查询

通过 SQL 查询出来的结果，还可以对其进行分组，使用 group by 语句来实现 ，group by 通常都是结合聚合函数（对列进行操作）一起使用的。分组的时候可以按一个或多个字段对结果进行分组处理。

常用的聚合函数：

计数：count
求和：sum
平均数：avg
最大值：max
最小值：min

3.1 语法格式

select 函数(字段1)，字段2 from 表名 where 表达式 group by 字段2 order by 函数(字段1)(或其他字段) [desc];

group by 格式原则：
# 凡是在 group by 后面出现的字段，必须在 select 后面出现；
# 凡是在 select 后面出现的、且未在聚合函数中出现的字段，必须出现在 group by 后面

3.2 示例

示例1：按 addres 相同信息的分组，计算相同分数的学生个数（基于 name 个数进行计数）

mysql> select count(name),address from student group by address;
+-------------+----------+
| count(name) | address  |
+-------------+----------+
|           1 | beijin   |
|           1 | hangzhou |
|           2 | nanjin   |
|           1 | shanghai |
+-------------+----------+

示例2：结合 where 语句，筛选分数大于等于60的分组，计算学生个数

mysql> select count(name),address from student where score>=60 group by address;
+-------------+----------+
| count(name) | address  |
+-------------+----------+
|           1 | beijin   |
|           1 | hangzhou |
|           2 | nanjin   |
+-------------+----------+

示例3：结合 order by 把计算出的学生个数按降序排列

mysql> select count(name),score,address from student where score>=60 group by address order by score desc;
+-------------+-------+----------+
| count(name) | score | address  |
+-------------+-------+----------+
|           2 |  90.5 | nanjin   |
|           1 |  70.0 | beijin   |
|           1 |  65.0 | hangzhou |
+-------------+-------+----------+

示例4：查看字段长度

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        5 |
+----------+

4. 限制结果条目

在使用 MySQL select 语句进行查询时，结果集返回的是所有匹配的记录（行）。使用 limit 命令可以限制指定的行，注意第一条信息位置偏移量是0。

4.1 语法格式

select 字段1,字段2,... from 表名 limit [offset,] 行数  # offset：偏移量

4.2 示例

示例1：显示前两行

mysql> select * from student limit 1;
+----+----------+---------+------+-------+
| id | name     | address | age  | score |    # 字段名也算一行
+----+----------+---------+------+-------+
|  1 | zhangsan | nanjin  | 10   |  90.5 |
+----+----------+---------+------+-------+

示例2：从第二行开始，往后显示两行内容

mysql> select * from student limit 1,2;
+----+--------+---------+------+-------+
| id | name   | address | age  | score |
+----+--------+---------+------+-------+
|  2 | lisi   | nanjin  | 50   |  65.0 |
|  3 | wangwu | beijin  | 30   |  70.0 |
+----+--------+---------+------+-------+

示例3：结合 order by 语句，按 score 的大小降序排列显示前三名

mysql> select * from student order by score desc limit 3;
+----+----------+---------+------+-------+
| id | name     | address | age  | score |
+----+----------+---------+------+-------+
|  1 | zhangsan | nanjin  | 10   |  90.5 |
|  3 | wangwu   | beijin  | 30   |  70.0 |
|  2 | lisi     | nanjin  | 50   |  65.0 |
+----+----------+---------+------+-------+

5. 设置别名

当表的名字比较长或者表内某些字段比较长时，可以设置别名方便书写。

5.1 语法格式

对于列的别名：
select 旧字段名 [as] 新字段名 from 表名;
对于表的别名：
select 字段(s) from 旧表名 as 新表名;

5.2 示例

示例1：设置别名

列别名设置：
mysql> select name as 姓名,score as 成绩 from student;
+----------+--------+
| 姓名     | 成绩   |
+----------+--------+
| zhangsan |   90.5 |
| lisi     |   65.0 |
| wangwu   |   70.0 |
| zhaoliu  |   50.0 |
| zhouqi   |   65.0 |
+----------+--------+

表别名设置：
mysql> select s.name as 姓名,s.score as 成绩 from student as s;
+----------+--------+
| 姓名     | 成绩   |
+----------+--------+
| zhangsan |   90.5 |
| lisi     |   65.0 |
| wangwu   |   70.0 |
| zhaoliu  |   50.0 |
| zhouqi   |   65.0 |
+----------+--------+

示例2：以别名方式查询

mysql> select s.name as 姓名,s.age as 年龄 from student as s;
+----------+--------+
| 姓名     | 年龄   |
+----------+--------+
| zhangsan | 10     |
| lisi     | 50     |
| wangwu   | 30     |
| zhaoliu  | 30     |
| zhouqi   | 40     |
+----------+--------+

示例3：as 作为连接语句操作符——创建新表 student1，将 表查询的记录全部插入 student1 表中

mysql> create table student1 as select * from student;
mysql> select * from student1;
+----+----------+----------+------+-------+
| id | name     | address  | age  | score |
+----+----------+----------+------+-------+
|  1 | zhangsan | nanjin   | 10   |  90.5 |
|  2 | lisi     | nanjin   | 50   |  65.0 |
|  3 | wangwu   | beijin   | 30   |  70.0 |
|  4 | zhaoliu  | shanghai | 30   |  50.0 |
|  5 | zhouqi   | hangzhou | 40   |  65.0 |
+----+----------+----------+------+-------+
mysql> desc student1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(5)       | NO   |     | NULL    |       |
| name    | char(15)     | YES  |     | NULL    |       |
| address | varchar(20)  | YES  |     | NULL    |       |
| age     | char(5)      | YES  |     | NULL    |       |
| score   | decimal(3,1) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(5)       | NO   | PRI | NULL    |       |
| name    | char(15)     | YES  |     | NULL    |       |
| address | varchar(20)  | YES  |     | NULL    |       |
| age     | char(5)      | YES  |     | NULL    |       |
| score   | decimal(3,1) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

由此可见使用 as 作为连接符创建的表克隆了数据和结构，但是没有“约束”；同时需要留意的是：如果原表有主键，附表 default 字段会默认设置一个0.

示例4：as 也可以接条件语句

mysql> create table student2 as select * from student where score>80;
mysql> select * from student2;
+----+----------+---------+------+-------+
| id | name     | address | age  | score |
+----+----------+---------+------+-------+
|  1 | zhangsan | nanjin  | 10   |  90.5 |
+----+----------+---------+------+-------+

6. 通配符

用于替换字符串中的部分字符，通过部分字符的匹配结果；通配符都是跟 like 一起使用的，并协同 where 子句共同来完成查询任务。

常用的通配符有两个：

  • %：百分号表示零个、一个或多个字符，类似*
  • _：下划线表示单个字符，类似 . 

6.1 一般格式 

select 字段1,字段2,... from 表名 where 字段 like '通配符表达式';

6.2 示例

示例1：筛选 address 以 n 开头的记录，只显示 id 和 address

mysql> select id,address from student where address like 'n%';
+----+---------+
| id | address |
+----+---------+
|  1 | nanjin  |
|  2 | nanjin  |
+----+---------+

示例2：查询名字里是 l 和 i 中间有两个字符的记录

mysql> select * from student where name like 'l__i';
+----+------+---------+------+-------+
| id | name | address | age  | score |
+----+------+---------+------+-------+
|  2 | lisi | nanjin  | 50   |  65.0 |
+----+------+---------+------+-------+

示例3：查询名字中间有 g 的记录

mysql> select * from student where name like '%g%';
+----+----------+---------+------+-------+
| id | name     | address | age  | score |
+----+----------+---------+------+-------+
|  1 | zhangsan | nanjin  | 10   |  90.5 |
|  3 | wangwu   | beijin  | 30   |  70.0 |
+----+----------+---------+------+-------+

示例4：组合使用

mysql> select * from student where address like '%zho_';
+----+--------+----------+------+-------+
| id | name   | address  | age  | score |
+----+--------+----------+------+-------+
|  5 | zhouqi | hangzhou | 40   |  65.0 |
+----+--------+----------+------+-------+

7. 子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。执行顺序由内到外。子语句和主语句查询表可以相同，也可以不同。

7.1 格式

查询：
select 字段1,字段2,... from 表1 where 条件字段 in (select 条件字段 from 表2 where 表达式);
插入：
insert into 表1 select * from 表2 where 条件字段 in (select 条件字段 from 表3);
更新：
update 表1 set 新表达式 where 条件字段 in (select 条件字段 from 表2 where 表达式); 
删除：
delete from 表1 where 条件字段 in (select 条件字段 where 表达式);
关键字查询：
select 聚合函数(*) from 表1 where exists (select 字段 from 表2 where 表达式);
子查询别名：（假设定义表别名为a）
select a.字段1,a.字段2,... from (select 字段1,字段2,... from 表) a;

环境准备：

mysql> select * from student;
+----+----------+----------+------+-------+
| id | name     | address  | age  | score |
+----+----------+----------+------+-------+
|  1 | zhangsan | nanjin   | 10   |  90.5 |
|  2 | lisi     | nanjin   | 50   |  65.0 |
|  3 | wangwu   | beijin   | 30   |  70.0 |
|  4 | zhaoliu  | shanghai | 30   |  50.0 |
|  5 | zhouqi   | hangzhou | 40   |  65.0 |
+----+----------+----------+------+-------+
mysql> select * from student1;
+----+----------+----------+------+-------+
| id | name     | address  | age  | score |
+----+----------+----------+------+-------+
|  1 | zhangsan | nanjin   | 10   |  90.5 |
|  2 | lisi     | nanjin   | 50   |  65.0 |
|  3 | wangwu   | beijin   | 30   |  70.0 |
|  4 | zhaoliu  | shanghai | 30   |  50.0 |
|  5 | zhouqi   | hangzhou | 40   |  65.0 |
+----+----------+----------+------+-------+

7.2 查询 

mysql> select name,score from student where id in (select id from student1 where address='nanjin');
# 查询地区是nanjind的信息
mysql> select * from student;
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan |  90.5 |
| lisi     |  65.0 |
+----------+-------+

7.3 删除

mysql> delete from student where id in (select id where score<70);
# 删除小于70分
mysql> select * from student;
+----+----------+---------+------+-------+
| id | name     | address | age  | score |
+----+----------+---------+------+-------+
|  1 | zhangsan | nanjin  | 10   |  90.5 |
|  3 | wangwu   | beijin  | 30   |  70.0 |
+----+----------+---------+------+-------+

7.4 写入

mysql> insert into student select * from student1 where id in (select id from student1 where score<70);
# 插入来自student1表大于70分的数据到student表
mysql> select * from student;
+----+----------+----------+------+-------+
| id | name     | address  | age  | score |
+----+----------+----------+------+-------+
|  1 | zhangsan | nanjin   | 10   |  90.5 |
|  2 | lisi     | nanjin   | 50   |  65.0 |
|  3 | wangwu   | beijin   | 30   |  70.0 |
|  4 | zhaoliu  | shanghai | 30   |  50.0 |
|  5 | zhouqi   | hangzhou | 40   |  65.0 |
+----+----------+----------+------+-------+

7.5 更新

mysql> update student set score=55 where id in (select id from student1 where id=5);
Query OK, 1 row affected (0.01 sec)
# 更新id为5的成绩为55
mysql> select * from student;
+----+----------+----------+------+-------+
| id | name     | address  | age  | score |
+----+----------+----------+------+-------+
|  1 | zhangsan | nanjin   | 10   |  90.5 |
|  2 | lisi     | nanjin   | 50   |  65.0 |
|  3 | wangwu   | beijin   | 30   |  70.0 |
|  4 | zhaoliu  | shanghai | 30   |  50.0 |
|  5 | zhouqi   | hangzhou | 40   |  55.0 |
+----+----------+----------+------+-------+

7.6 exists 关键字查询

exists 主要功能是判断子查询结果集是否为空。

mysql> select count(*) from student where exists (select id from student1 where name like 'zhang%');
+----------+
| count(*) |
+----------+
|        5 |
+----------+
# 判断student1表是否有一个叫zhang开头名字的，存在返回表的行计数

7.7 子查询别名

将结果集做为一张表进行查询。

mysql> select a.id,a.name from (select id,name from student) a;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | zhaoliu  |
|  5 | zhouqi   |
+----+----------+

7.8 返回值取反

mysql> select name,score from student where id not in (select id from student1 where score<90);
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan |  90.5 |
+----------+-------+

三、mysql 视图

1. 概述

数据库中的虚拟表，不包含真实数据，只是映射；简化SQL语句，简化查询结果集、灵活查询，针对不同的用户呈现不同的结果集；只适合查询，不适合增删改。视图有表之后才能存在，它的内容都来自基本表，一个视图可对应一个或多个基本表。

2. 视图与表的区别

① 视图是已经编译好的sql语句，表不是

② 视图没有实际的物理记录，表有

③ 表占用物理空间，而视图不占用，视图是逻辑概念存在；表可即时修改，但视图只能由创建的语句来修改

④ 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，从安全角度来说，用户接触不到表结构

⑤ 表属于全局模式中的实表；视图属于局部模式的虚表

⑥ 视图的建立和删除只影响视图本身，不影响对应的基本表。当更新视图数据，会影响到基本表

3. 视图操作

环境增加：

mysql> select * from newstudent;
+------+----------+--------+
| id   | name     | cardid |
+------+----------+--------+
|    1 | zhangsan | 111    |
|    2 | lisi     | 222    |
|    3 | wang     | 333    |
+------+----------+--------+

3.1 视图创建

格式：

单表创建视图：
create view v_名称 as select * from 表 where 表达式；  # 没有“约束”
多表创建视图：
create view v_名称 (字段1,字段2,字段3,...) as select a.字段1,a.字段2,b.字段3,... from 表1 a,表2 b where a.同名字段=b.同名字段;

示例：

单表创建视图：
mysql> create view v_1 as select * from student where score>=60;
多表创建视图:
mysql> create view v_2 (id,name,cardid) as select a.id,a.name,b.cardid from student a,newstudent b where a.name=b.name;

3.2 查看表状态

mysql> show table status\G;
*************************** 5. row ***************************
           Name: v_1
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW

3.3 视图查询

mysql> select * from v_1;
+----+----------+---------+------+-------+
| id | name     | address | age  | score |
+----+----------+---------+------+-------+
|  1 | zhangsan | nanjin  | 10   |  90.5 |
|  2 | lisi     | nanjin  | 50   |  65.0 |
|  3 | wangwu   | beijin  | 30   |  70.0 |
+----+----------+---------+------+-------+
mysql> select * from v_2;
+----+----------+--------+
| id | name     | cardid |
+----+----------+--------+
|  1 | zhangsan | 111    |
|  2 | lisi     | 222    |
+----+----------+--------+

3.4 视图修改

mysql> update v_1 set score=50 where id=3;
mysql> select * from v_1;
+----+----------+---------+------+-------+
| id | name     | address | age  | score |
+----+----------+---------+------+-------+
|  1 | zhangsan | nanjin  | 10   |  90.5 |
|  2 | lisi     | nanjin  | 50   |  65.0 |
+----+----------+---------+------+-------+
# 由于创建视图有硬性条件“score>=60”，所以修改后记录会丢失
mysql> update v_1 set age=15 where id=2;
mysql> select * from v_1;
+----+----------+---------+------+-------+
| id | name     | address | age  | score |
+----+----------+---------+------+-------+
|  1 | zhangsan | nanjin  | 10   |  90.5 |
|  2 | lisi     | nanjin  | 15   |  65.0 |
+----+----------+---------+------+-------+
mysql> select * from student where id=2;
+----+------+---------+------+-------+
| id | name | address | age  | score |
+----+------+---------+------+-------+
|  2 | lisi | nanjin  | 15   |  65.0 |
+----+------+---------+------+-------+
# 由此可见，修改视图会影响基础表
mysql> update student set name='zhangs' where id=1;
mysql> select * from v_1;
+----+--------+---------+------+-------+
| id | name   | address | age  | score |
+----+--------+---------+------+-------+
|  1 | zhangs | nanjin  | 10   |  90.5 |
|  2 | lisi   | nanjin  | 15   |  65.0 |
+----+--------+---------+------+-------+
# 由此可见，修改基础表会影响视图，注意：这里表不能修改以函数、符合函数方式计算出来的字段

3.5 视图删除

格式：
drop view [if exists] 视图名;
示例：
mysql> drop view v_1;
mysql> select * from v_1;
ERROR 1146 (42S02): Table 'school.v_1' doesn't exist
mysql> select * from student;
+----+---------+----------+------+-------+
| id | name    | address  | age  | score |
+----+---------+----------+------+-------+
|  1 | zhangs  | nanjin   | 10   |  90.5 |
|  2 | lisi    | nanjin   | 15   |  65.0 |
|  3 | wangwu  | beijin   | 30   |  50.0 |
|  4 | zhaoliu | shanghai | 30   |  50.0 |
|  5 | zhouqi  | hangzhou | 40   |  55.0 |
+----+---------+----------+------+-------+
# 删除视图，不影响基础表

4. null 值 

表示缺失的值，即该字段没有值；与“0”或者“空白”的字段不同。null 与空值的区别：空值的长度为0，不占空间，null 值长度为1，占用空间。

mysql> select length(null),length(''),length('abc');
+--------------+------------+---------------+
| length(null) | length('') | length('abc') |
+--------------+------------+---------------+
|         NULL |          0 |             3 |
+--------------+------------+---------------+

统计数量：检测null是否会加入统计中

mysql> update newstudent set cardid='' where id=3;
mysql> select * from newstudent;
+------+----------+--------+
| id   | name     | cardid |
+------+----------+--------+
|    1 | zhangsan | 111    |
|    2 | lisi     | 222    |
|    3 | wang     |        |
+------+----------+--------+
mysql> select count(cardid) from newstudent;
+---------------+
| count(cardid) |
+---------------+
|             3 |
+---------------+

查询空值：

mysql> insert into newstudent (id,name) values(4,'zhou');
# 插入一条数据，cardid输入null
mysql> select * from newstudent;
+------+----------+--------+
| id   | name     | cardid |
+------+----------+--------+
|    1 | zhangsan | 111    |
|    2 | lisi     | 222    |
|    3 | wang     |        |
|    4 | zhou     | NULL   |
+------+----------+--------+
mysql> select * from newstudent where cardid is NULL;
+------+------+--------+
| id   | name | cardid |
+------+------+--------+
|    4 | zhou | NULL   |
+------+------+--------+
# 查询null值
mysql> select count(cardid) from newstudent;
+---------------+
| count(cardid) |
+---------------+
|             3 |
+---------------+
# 再次计数

四、连接查询

1. 概述

来自两个或多个表的记录结合起来，基于表之间的共同字段进行拼接。使用较多的连接查询包括：内连接、左连接和右连接。

环境准备：

mysql> select * from student;
+----+---------+----------+------+-------+
| id | name    | address  | age  | score |
+----+---------+----------+------+-------+
|  1 | zhangs  | nanjin   | 10   |  90.5 |
|  2 | lisi    | nanjin   | 15   |  65.0 |
|  3 | wangwu  | beijin   | 30   |  50.0 |
|  4 | zhaoliu | shanghai | 30   |  50.0 |
|  5 | zhouqi  | hangzhou | 40   |  55.0 |
+----+---------+----------+------+-------+
mysql> select * from newstudent;
+------+----------+--------+
| id   | name     | cardid |
+------+----------+--------+
|    1 | zhangsan | 111    |
|    2 | lisi     | 222    |
|    3 | wang     |        |
|    4 | zhou     | NULL   |
+------+----------+--------+

2. 内连接

在 from 子句中使用关键字 [inner] join 来连接多张表，并使用 on 子句设置连接条件。输出匹配字段共同的数据。

格式：

select 表1[2].字段1,表1[2].字段2,... from 表1 inner join 表2 on 表1.同名字段=表2.同名字段;

 查询：

mysql> select student.id,student.name from student inner join newstudent on student.name=newstudent.name;
+----+------+
| id | name |
+----+------+
|  2 | lisi |
+----+------+

3. 左连接

在 from 子句中使用 left join 或者 left outer join 关键字来表示。左连接中左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，这些记录在左表中以 null 补足。

 格式：

select * from 表1 left join 表2 on 表1.同名字段=表2.同名字段;

查询：

mysql> select * from student left join newstudent on student.name=newstudent.name;
+----+---------+----------+------+-------+------+------+--------+
| id | name    | address  | age  | score | id   | name | cardid |
+----+---------+----------+------+-------+------+------+--------+
|  2 | lisi    | nanjin   | 15   |  65.0 |    2 | lisi | 222    |
|  1 | zhangs  | nanjin   | 10   |  90.5 | NULL | NULL | NULL   |
|  3 | wangwu  | beijin   | 30   |  50.0 | NULL | NULL | NULL   |
|  4 | zhaoliu | shanghai | 30   |  50.0 | NULL | NULL | NULL   |
|  5 | zhouqi  | hangzhou | 40   |  55.0 | NULL | NULL | NULL   |
+----+---------+----------+------+-------+------+------+--------+

4. 右连接

在 from 子句中使用 right join 或者 right outer join 关键字来表示。右连接跟左连接正好相反，在右连接的查询结果集中，除了符合匹配规则的行外，还包括右表中有但是左表中不匹 配的行,这些记录在左表中以 null 补足。

格式：

select * from 表1 right join 表2 on 表1.同名字段=表2.同名字段;

查询：

mysql> select * from student right join newstudent on student.name=newstudent.name;
+------+------+---------+------+-------+------+----------+--------+
| id   | name | address | age  | score | id   | name     | cardid |
+------+------+---------+------+-------+------+----------+--------+
|    2 | lisi | nanjin  | 15   |  65.0 |    2 | lisi     | 222    |
| NULL | NULL | NULL    | NULL |  NULL |    1 | zhangsan | 111    |
| NULL | NULL | NULL    | NULL |  NULL |    3 | wang     |        |
| NULL | NULL | NULL    | NULL |  NULL |    4 | zhou     | NULL   |
+------+------+---------+------+-------+------+----------+--------+
Fang_Qianling
关注
  • 19
    点赞
  • 踩
  • 26
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL入门教程（九）：高级查询之连接查询
专业小白的博客
08-16 1642
SQL入门教程（九）前言二级目录三级目录 前言 文中涉及到操作范例，表创建和数据插入的SQL代码，可以打开我的主页，在资源中免费下载。 关系型数据库管理系统：MySQL8.0 数据库管理工具：Navicat Premium 12 二级目录 三级目录 ...
mysql查询--高级查询
CSDN
03-21 783
distinct 不重复前提 从teacher表中查找不重复的Depart selectdistinctDepartfromteacher between ...and... 在。。。之间 从Score表中查找degree在60-80之间的所有信息 select * from Score where Degree between 60 and 80 in+元组 ...
MySQL -- 连接查询
Afison的博客
06-03 1508
MySQL使用连接查询（JOIN）是为了从多个相关表中获取数据。
MySQL连接查询
weixin_45392969的博客
03-17 2025
内连接（INNER JOIN）查询、外连接查询 、LEFT JOIN（左连接）、RIGHT JOIN（右连接）：
10个高级的 SQL 查询技巧，你掌握了几个？
最新发布
MarkerHub的博客
06-26 72
前后端微服务商城项目，手把手教学！前后端微服务商城项目，手把手教学！前后端微服务商城项目，手把手教学！1.常见表表达式（CTEs）2.递归CTEs.3.临时函数4.使用CASE WHEN枢转数据5.EXCEPT vs NOT IN6.自联结7.Rank vs Dense Rank vs Row Number8.计算Delta值9.计算运行总数10.日期时间操纵谢谢阅读！随着数据量持续增长，对合格数...
MySQL基础篇 | 连接查询、子查询（嵌套）
热门推荐
m0_61933976的博客
08-30 1万+
MySQL基础篇【第四篇】| 连接查询（内连接和外连接）、子查询（嵌套）
Mysql中的七种常用查询连接详解
qq_41946216的博客
04-22 1万+
一、概述 在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。一个业务都会对应多张表，比如：学生和班级，起码两张表。(避免产生数据的冗余)。二、连接查询的分类 1、根据语法出现的年代来划分： sql97--仅仅支持内连接(一些老的DBA可能还在使用这种语法。) sql99--推荐使用,支持左外+右外(左外+右外)+交叉 2、根据功能划分分类: 交叉连接：笛卡尔积。 内连接：等值连接、非等值连接、自连接。 外连接：左外连接(左连接)、右外连接...
MySQL DQL 连接查询
Dablelv 的博客专栏。
09-09 955
在关系型数据库管理系统（RDBMS）中，连接查询是一项重要的数据库操作，它允许我们从多个表中检索和组合数据，以便进行更复杂的查询和分析。比如员工的个人信息存储在 employee 表中，部门相关的信息存储在 department 表中，同时 employee 表中存在一个外键字段（dept_id），引用了 department 表的主键字段。因此，当我们想要查看员工的个人信息以及他/她所在的部门信息，就需要同时查询 employee 和 department 表中的信息。此时，我们需要使用连接查询。
Mysql：连接查询
不怕猫的耗子A
11-23 1万+
连接查询 1、连接是关系型数据库的主要特点。连接查询是关系型数据库中最主要的查询，主要包括内连接、外连接等 ⑴连接查询：也可以叫跨表查询，需要关联多个表进行查询 2、通过连接运算符可以实现多个表查询 3、在关系数据库管理系统中，表建立时各数据之间的关系不必确定，通常把一个实体的所有信息存放在一个表中 ⑴当查询数据时，通过连接操作查询出存放在多个表中的不同实体信息 ⑵当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询 注： 1、在实际开发中，如果把所.........
MySQL查询语句汇总与使用指南.docx
04-24
其核心优势在于支持SQL（Structured Query Language）语言进行数据库管理，这使得MySQL能够高效地处理复杂的数据查询与操作需求。 ##### 1.1 主要特点 - **数据安全**：MySQL提供了强大的数据加密和安全认证机制，...
最常用的SQL查询语句.docx
06-03
本篇文章将详细讲解最常用的SQL查询语句，涵盖简单查询、联合查询和连接查询。 一、简单查询 1. 查询列和表：`SELECT`语句用于从数据库中提取数据，基本格式为`SELECT column_name FROM table_name`。例如，`...
SQL查询语言技术
11-04
SQL查询语言技术是数据库管理、数据检索、更新和删除的核心工具，广泛应用于各种数据库系统，如MySQL、Oracle、SQL Server、PostgreSQL等。本节将深入探讨SQL的基本概念、语法和常见操作。 一、SQL基本概念 1. ...
SQL结构化查询语言
01-09
### SQL结构化查询语言知识点详解 #### 一、SQL简介 **SQL**(Structured Query Language)是一种专门用于管理和处理关系数据库的标准计算机语言。它被广泛应用于各种数据库系统中，包括MySQL、Oracle、SQL Server等...
mysql 查询语句大全及用法10条.docx
04-22
除了上述基础查询语句之外，MySQL还提供了许多高级查询功能，例如子查询、连接查询、聚合函数等，这些都可以进一步增强SQL语言的能力，满足更加复杂的数据处理需求。建议读者参考MySQL官方文档或其他相关资源来深入...
SQL数据查询——连接查询
z135733的博客
02-13 8920
等值连接、内连接、外连接、自然连接、SQL JOINS等主要操作以及SQL92与SQL99语法比较、Oracle与MySQL语法比较。
SQL之连接查询
m0_62172219的博客
11-01 5440
因为不同表之间的数据具有不同的用途和字段，连接查询可以将我们需要用到的两个表的不同字段进行关联，从而找到我们有用的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，然后通过连接进行查询。连接操作除了可以是两表连接，一个表于其自身连接外，还可以是两个以上的表进行连接，后者通常 称为多表连接。对于不同的DBMS,支持的集合运算不同，例如MySQL只支持并运算，但是集合运算大都可以使用关系运算符进行替换或者使用嵌套查询，因此不必担心集合运算连接查询。
MySql中的连接查询
欢迎阅读我的博客
02-13 2494
外连接: 在外连接当中，两张表连接产生了主次关系 , 主要查询的是主表的数据 , 捎带着关联查询次表 , 即如果对方没有记录和我主表匹配 , 那么默认对方是null , outer关键字可以省略。内连接：A表和B表连接，A和B两张表没有主次关系是平等的 , 查询时只是将能够匹配上连接条件的数据查询出来 , 即如果没有匹配的就查询不出来 , inner关键字可以省略。当两张表进行连接查询，没有指定连接条件的时候，最终查询结果条数是两张表条数的乘积，这种现象被称为：笛卡尔积现象（笛卡尔发现的，这是。
Mysql连接查询详解
弹指天下
04-22 6678
记得刚工作几年的时候，就写了一篇关于Mysql连接查询的博客文章，里面简单的认为先对关联表的记录进行笛卡尔积，然后再根据where条件过滤，现在看来有点贻笑大方了。正好最近看了《mysql是怎样运行的》这本书，所以对Mysql连接查询的原理和过程重新做下总结。
MySQL连接查询语句
青梅煮酒
08-12 4677
介绍MySQL的连接查询
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值