mysql学习录（三）多表查询、子查询、分页查询、case语句、单行函数

最新推荐文章于 2024-06-12 15:51:36 发布
最新推荐文章于 2024-06-12 15:51:36 发布
阅读量313 收藏
点赞数
分类专栏： # MySql的学习 数据库 文章标签： mysql sql
by顾辞嘤嘤怪
本文链接：https://blog.csdn.net/qq_43636709/article/details/119857274
版权

文章目录

小技巧：输入了好几条语句，但是又不想执行，又删不掉，这时候就需要使用 \c

mysql> SELECT
    ->
    -> \c
mysql>
多表查询

把连接条件写在 where 之后的这种方式叫做隐性连接，他的功能和内连接的功能是一样的，而带 on 的连接方式叫做显性连接。现在随着数据库的规范和发展，大多用显性连接，很少用隐性连接。

1.笛卡尔积现象

在多表连接查询时，如果定义了无效连接或者漏写连接条件，就会产生笛卡尔积现象。所谓笛卡尔积就是每个表的每一行都和其它表的每一行组合，假设两个表的总行数一个 x，一个 y，则返回笛卡尔积 x*y。

2.等值连接查询

寻找俩表中字段名称相等，数据类型相同的字段进行连接，会自动去重重复列（如果有多个字段符合要求，那么他们会被作为自然连接的条件）又叫自然连接

最常见的，通常是在存在主键外键关联关系之间的表之间进行，并将连接条件设置为有关系的列【主键-外键】，使用 ”=“ 连接相关表。避免笛卡尔积现象，n个表等值连接时至少需要 n-1 个等值连接条件。

mysql>-- emp员工表的 dept_id 字段是 dept部门表的主键 id
mysql> desc dept;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| DEPTNO   | int         | NO   | PRI | NULL    |       |
| DNAME    | varchar(14) | YES  |     | NULL    |       |
| LOCATION | varchar(13) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int         | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MANAGER  | int         | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  |     | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> -- 隐性连接的写法
mysql> select d.dname, e.empno, e.ename from dept as d, emp e where d.deptno = e.deptno;
+------------+-------+--------+
| dname      | empno | ename  |
+------------+-------+--------+
| RESEARCH   |  7369 | SMITH  |
| SALES      |  7499 | ALLEN  |
| SALES      |  7521 | WARD   |
| RESEARCH   |  7566 | JONES  |
| SALES      |  7654 | MARTIN |
| SALES      |  7698 | BLAKE  |
| ACCOUNTING |  7782 | CLARK  |
| RESEARCH   |  7788 | SCOTT  |
| ACCOUNTING |  7839 | KING   |
| SALES      |  7844 | TURNER |
| RESEARCH   |  7876 | ADAMS  |
| SALES      |  7900 | JAMES  |
| RESEARCH   |  7902 | FORD   |
| ACCOUNTING |  7934 | MILLER |
+------------+-------+--------+
14 rows in set (0.00 sec)

as 给别名 ，一般表的别名直接用空格隔开 省略as

3.自连接查询

一个表中进行多表查询,。

员工和员工上级都在员工表中，用manager_id 来标识员工上级

select e1.empno as 员工编号, e1.ename as 员工姓名, e2.empno as 领导编号, e2.ename as 领导姓名 from emp e1, emp e2 where e1.manager_id = e2.empno;
4.内连接查询

关键字：表1 inner join 表2 on 连接条件 (inner可以省略)

语句：select * from a_table a inner join b_table b on a.a_id = b.b_id;

说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集部分。
自连接查询也可以用内连接来实现：

SELECT e1.empno AS 员工编号, e1.ename AS 员工姓名, e2.empno AS 领导编号, e2.ename AS 领导姓名 FROM emp e1 JOIN emp e2 ON e1.manager_id = e2.empno;

条件不一定是两个表是主外键关系，返回的是符合条件的记录

5.外连接查询

外连接分为 left outer join（左外连接） 和 right outer join （右外连接），outer可以省略。

关键字join左边的叫左表，右边的叫右表

a. 左连接表示显示左表的全部数据，右表满足连接条件的数据，不满足的用null值填充；右连接表示显示右表的全部数据，左表满足连接条件的数据，不满足null值填充。

b. 结果集要包含右表中所有匹配的行，若右表中有的项在左表中没有对应的项，以null值填充。
在这里，join指定的表为主表，即右表为主表。（哪个表中所有行都显示了，就是主表）

mysql> -- 左外连接 ( OUTER 关键字是可以省略的 )
mysql> SELECT * FROM t_class c LEFT OUTER JOIN t_major m ON c.major_id = m.id ;
+----+----------+----------+------+------------------+
| id | name     | major_id | id   | name             |
+----+----------+----------+------+------------------+
|  1 | 软件一班 |        2 |    2 | 软件工程         |
|  2 | 软件二班 |        2 |    2 | 软件工程         |
|  3 | 计科一班 |        4 |    4 | 计算机科学与技术 |
|  4 | 计科二班 |        4 |    4 | 计算机科学与技术 |
|  5 | 卓越班   |     NULL | NULL | NULL             |
|  6 | 东坡班   |     NULL | NULL | NULL             |
+----+----------+----------+------+------------------+
6 rows in set (0.00 sec)

mysql> -- 右外连接 ( OUTER 关键字是可以省略的 )
mysql> SELECT * FROM t_class c RIGHT OUTER JOIN t_major m ON c.major_id = m.id ;
+------+----------+----------+----+------------------+
| id   | name     | major_id | id | name             |
+------+----------+----------+----+------------------+
| NULL | NULL     |     NULL |  5 | 信息对抗         |
| NULL | NULL     |     NULL |  3 | 动物医学         |
| NULL | NULL     |     NULL |  1 | 土木工程         |
|    3 | 计科一班 |        4 |  4 | 计算机科学与技术 |
|    4 | 计科二班 |        4 |  4 | 计算机科学与技术 |
|    1 | 软件一班 |        2 |  2 | 软件工程         |
|    2 | 软件二班 |        2 |  2 | 软件工程         |
+------+----------+----------+----+------------------+
7 rows in set (0.00 sec)

全外连接( MySQL不支持 )
相当于左外和右外连接的并集
SELECT * FROM t_class c FULL OUTER JOIN t_major m ON c.major_id = m.id ;

子查询
1.单行子查询

返回单行单列

2.多行子查询

返回单行多列 (使用了成对比较))

3.多列子查询

返回多行单列

4.相关子查询

子查询使用了外部SQL的某些表或列 (但是外部SQL不可使用子查询中的表和列)

5.嵌套子查询:

在子查询内部可以继续嵌套子查询

分页查询

使用 lilmit 实现分页查询
语法：limit a , b 第一个参数表示从第几条开始查（用页数索引（0开始）* 每页记录数）， 第二个表示一页查几条

-- 显示第一条到第五条记录
select * from emp limit 0,5;
CASE语句
1.语法：
CASE 
	WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	...
	WHEN 条件n THEN 结果n
	ELSE 其余情况
END
2.例子：
mysql> SELECT s.name 学生姓名 ,
    ->        c.name 课程名称 ,
    ->        CASE
    ->            WHEN x.score BETWEEN 90 AND 100 THEN 'A'
    ->            WHEN x.score > 80 THEN 'B'
    ->            WHEN x.score > 70 THEN 'C'
    ->            WHEN x.score > 60 THEN 'D'
    ->            ELSE 'E'
    ->        END AS 成绩等级
    -> FROM t_students s
    ->      JOIN t_scores x ON x.sid = s.id
    ->      JOIN t_courses c ON x.cid = c.id ;
+--------------+--------------+--------------+
| 学生姓名     | 课程名称     | 成绩等级     |
+--------------+--------------+--------------+
| 张无忌       | C++          | A            |
| 张无忌       | C++          | A            |
| 周芷若       | C++          | A            |
| 张三丰       | C++          | A            |
| 刘书妹       | C++          | E            |
| 刘书妹       | C++          | E            |
| 张无忌       | Java         | A            |
| 小昭         | Java         | A            |
| 吴莫愁       | Java         | D            |
| 赵敏         | Oracle       | B            |
| 张三丰       | Oracle       | E            |
| 周芷若       | HTML/CSS     | A            |
| 吴莫愁       | HTML/CSS     | C            |
| 刘书妹       | HTML/CSS     | E            |
+--------------+--------------+--------------+
14 rows in set (0.00 sec)
单行函数

SQL函数即数据库的内置函数，可以应用在SQL语句中实现特定功能，单行函数对于每一行的数据进行计算后得到一行输出结果；多行函数是多行数据参与运算得到一行输出结果。

1.字符函数
length()、 concat()、upper() 、lower()、substr()或substring()、 instr()、trim()、lpad()和rpad()、replace() ;
大小写转换函数：upper，lower
mysql> SELECT upper('abc'), lower('ABC');
+--------------+--------------+
| upper('abc') | lower('ABC') |
+--------------+--------------+
| ABC          | abc          |
+--------------+--------------+
1 row in set (0.00 sec)
字符串连接函数：concat

连接字符串

mysql> SELECT concat('tx', 'sql');
+---------------------+
| concat('tx', 'sql') |
+---------------------+
| txsql               |
+---------------------+
1 row in set (0.00 sec)
获取子字符串函数：substr

用于字符串截取 第一个参数是被截取的字符串 第二个参数是从第几位开始截取 空格也算一个字符

mysql> SELECT substr('java sql js', 5);
+--------------------------+
| substr('java sql js', 5) |
+--------------------------+
|  sql js                  |
+--------------------------+
1 row in set (0.00 sec)
其它的字符串函数：
  1. length(字符串); ：获取字符串的长度
  2. instr('父字符串', '子字符串'); ：获取子字符串在父字符串中的索引 从1开始 空格也算
  3. ltrim(' java web'); ：去掉字符串左侧空格
  4. rtrim('java web '); ：去掉右侧的空格
  5. trim(' java web '); ：去掉两侧的空格
  6. replace('字符串', '要替换的子字符串', '替换字符串'); ：第一个是父字符串 第二个要替换的子字符串 替换的字符串
2.数学函数
round()、ceil()和floor()、truncate()、mod();
  • round(x, [y]) ：对指定的 X 进行四舍五入操作 可以指定保留位数 y 可以是负值
  • truncate(x, y) ：对指定的 X 进行截取操作，可以指定保留的数值位数
  • ceil(x) ：返回不小于指定值x的最小整数 比如 x是4.5返回5
  • floor(x) ：返回不大于x的最大整数 比如x是8.1返回8
  • abs(x) ：取绝对值
  • mod(x, y) ：取x除以y的余数
  • sign(x) ：取x的符号函数 负数（-1），0，正数 （1）
  • power(x, y) ：取x的y次幂 x=2 ,y=3,则 2的3次方等于8
  • sqrt(x) ：取x的二次方根
3.日期函数
now()、curdate()、curtime()、年、月、日、小时、分钟、秒、str_to_date()和date_format();
当前日期函数
日期截取函数
日期增加函数
特例：
日期格式符
1.概念：

格式符是描述日期的格式符号，用字母描述日期中特定的部分，以%开头，例如 %m表示日期中的月份。

2.格式符表：
格式描述
%a缩写星期名
%b缩写月名
%c月，数值
%D带有英文前缀的月中的天
%d月的天，数值（00-31）
%e月的天，数值（0-31）
%f微秒
%H小时（00-23）
%h小时（01-12）
%I小时（01-12）
%i分钟，数值（00-59）
%j年的天（001-366）
%k小时（0-23）
%l小时（1-12）
%M月名
%m月，数值（00-12）
%pAM 或 PM
%r时间，12-小时（hh:mm:ss AM 或 PM）
%S秒（00-59）
%s秒（00-59）
%T时间, 24-小时（hh:mm:ss）
%U周（00-53）星期日是一周的第一天
%u周（00-53）星期一是一周的第一天
%V周（01-53）星期日是一周的第一天，与 %X 使用
%v周（01-53）星期一是一周的第一天，与 %x 使用
%W星期名
%w周的天（0=星期日, 6=星期六）
%X年，其中的星期日是周的第一天，4 位，与 %V 使用
%x年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y年，4 位
%y年，2 位
查询今天
/*
  NOW() 函数返回当前的日期和时间。
  TO_DAYS()函数返回日期和年份0（日期"0000-00-00"）之间的天数。
*/
SELECT * FROM cpidata WHERE TO_DAYS( 时间字段名 ) = TO_DAYS(NOW());
查询昨天
/*
  NOW() 函数返回当前的日期和时间。
  TO_DAYS()函数返回日期和年份0（日期"0000-00-00"）之间的天数。
*/
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) = 1
查询本周
/*
  YEARWEEK()返回年份和星期
  DATE_FORMAT(被格式化的日期,格式符)日期格式化函数
*/
SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT(日期字段名,'%Y-%m-%d')) = YEARWEEK(NOW());
查询上周
/*
  YEARWEEK()返回年份和星期
  DATE_FORMAT(被格式化的日期,格式符)日期格式化函数
*/
SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT(日期字段名,'%Y-%m-%d')) = YEARWEEK(NOW())-1;
查询近7天
/*
  DATE_SUB() 函数从日期减去指定的时间间隔
  DATE_SUB(合法的日期表达式,INTERVAL 希望添加的时间间隔 时间类型)
  CURDATE() 函数返回当前的日期。
*/
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
查询近30天
/*
  DATE_SUB() 函数从日期减去指定的时间间隔
  DATE_SUB(合法的日期表达式,INTERVAL 希望添加的时间间隔 时间类型)
  CURDATE() 函数返回当前的日期。
*/
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
查询本月
/*
  DATE_FORMAT(被格式化的日期,格式符)日期格式化函数
*/
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );
查询上一月
/*
  PERIOD_DIFF(第一个日期,第二个日期)函数返回两日期之间的差异。结果以月份计算
*/
SELECT * FROM 表名 WHERE PERIOD_DIFF( DATE_FORMAT( NOW( ) , '%Y%m' ) , DATE_FORMAT( 时间字段名, '%Y%m' ) ) =1
查询本季度
/*
 QUARTER(日期)返回日期的季度：
*/
SELECT * FROM 表名 WHERE QUARTER(日期字段名)=QUARTER(NOW());
查询上季度
/*
 QUARTER(日期)返回日期的季度：
 DATE_SUB() 函数从日期减去指定的时间间隔
 DATE_SUB(合法的日期表达式,INTERVAL 希望添加的时间间隔 时间类型)
*/
SELECT * FROM 表名 WHERE QUARTER(日期字段名)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER));
查询本年度
/*
 year(日期字段名)返回日期中的年份
*/
SELECT * FROM 表名 WHERE YEAR(日期字段名)=YEAR(NOW());
查询上年度
/*
 year(日期字段名)返回日期中的年份
 DATE_SUB() 函数从日期减去指定的时间间隔
 DATE_SUB(合法的日期表达式,INTERVAL 希望添加的时间间隔 时间类型)
*/
SELECT * FROM 表名 WHERE YEAR(日期字段名)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));
4.其它内置系统函数
version()、database()、user();
bigdata7
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL分组查询、连表查询、子查询，分页查询
JiangHu_Java的博客
06-17 364
MySQL分组查询、连表查询、子查询，分页查询前言分组查询案例连表查询概念：分类连接sql92标准连接查询案例非等值连接自连接join链接sql99语法 join案例自连接外连接常见函数常见函数案例子查询总结 前言 之前讲过了MySQL的一些基本知识点，今天讲一点复杂的SQL语句！ MySQL相关知识点： MySQL安装卸载以及使用 MySQL之账号管理，四大引擎以及DDL（数据定义语言） MySQL常见查询、表达式、排序、分组查询基础 分组查询案例 语法： select 查询列表 from 表 【wher
mysql子查询的用法
04-05
NULL 博文链接：https://thetopofqingshan.iteye.com/blog/1518502
使用 MySQL 子查询和 CASE 语句判断关联状态
最新发布
李长渊的博客
06-12 967
使用 MySQL 子查询和 CASE 语句判断关联状态
case when 子查询_MySQL之多表查询
weixin_39772352的博客
11-25 893
一、表的加法（union、union all ）union : 会删除两张表里重复的部分 ；union all : 会保留两张表里重复的部分SELECT cid, ccourse FROM course UNION ALL SELECT cid, ccourse FROM course1;二、表的联结(join)studentscorecourse交叉联结(语法：cross join)：交叉联...
MySQL_子查询
weixin_51923997的博客
01-30 741
文章目录一、需求分析与问题解决1、 实际问题2、 子查询的基本使用3、子查询的分类二、单行子查询1、单行比较操作符2、示例3、HAVING 中的子查询4、CASE中的子查询三、多行子查询1、多行比较操作符2、示例3、 空值问题四、相关子查询1、相关子查询执行流程2、示例3、EXISTS 与 NOT EXISTS关键字 一、需求分析与问题解决 1、 实际问题 #查询谁的工资比Abel高 # 方式一： SELECT salary FROM employees WHERE last_name='Abel
case when 子查询_MySQL基础知识—多表查询
weixin_39627144的博客
11-27 269
一、表的加法（union）将两个表的数据按照行合并在一起，并且重复值只保留一个SELECT 如果想保留两个表中的重复值，那么使用union all，那么重复值语文课程就被保留下来了SELECT 需要注意的是：这里的重复指的是课程号和课程名称完全一致，教师号是否重复不影响查询结果，因为我没有查询教师号这一列。二、表的联结下面四个表通过学号，课程号，教师号这三列进行表与表之间的联结交叉联结，也叫笛卡尔...
Oracle、MySQL和SqlServe三种数据库分页查询语句的区别介绍
09-10
Oracle、MySQL和SQL Server是三大主流的关系型数据库管理系统，它们各自提供了不同的分页查询语句来实现这一功能。接下来，我们将详细探讨这三种数据库在分页查询上的差异。 一、SQL Server的分页查询 在SQL ...
mysql一对多关联查询分页错误问题的解决方法
09-09
为了解决这个问题，我们需要调整SQL查询语句，确保分页逻辑同时应用于主表和子表。在上述示例中，我们看到XML配置文件中定义了一个resultMap，用于处理一对多关联查询，其中`orderProductList`是订单表（youpin_...
MySQL中使用case when 语句实现多条件查询的方法
09-11
在MySQL数据库中，`CASE WHEN` 语句是一种强大的条件表达式，用于在查询中进行复杂的逻辑判断。本文将深入探讨如何使用`CASE WHEN`语句实现多条件查询，特别是针对给定的示例场景。 首先，让我们了解`CASE WHEN`的...
mysql分页原理和高效率的mysql分页查询语句
09-10
MySQL分页原理与高效查询是数据库管理中的重要概念，尤其对于大数据量的Web应用，合理的分页策略可以显著提升用户体验并降低服务器负载。本篇文章将深入探讨这两种分页方法。 首先，基本的分页原理是利用`LIMIT`...
数据库case，分页
sunshine940326的博客
06-19 280
–case使用case expr when 值 then 值 when .. then .. endcase when 条件 then 值 when 条件 then 值 else 值 end –表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。 select * from T8
MySQL--子查询
sdau_20171819的博客
01-28 670
一、基本使用 1.1 基本语法 SELECT select_list FROM table WHERE expr operator ( SELECT select_list FROM TABLE); 子查询（内查询）在主查询之前一次执行完成。 子查询的结果被主查询（外查询）使用 。 子查询要包含在括号内 将子查询放在比较条件的右侧 单行操作符对应单行子查询，多行操作符对应多行子查询 1.2 子查询分类 我们按
Mysql之子查询
qq_52150810的博客
03-24 126
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入 目录 一. 分类 二. 单行子查询 2.1单行比较操作符 2.3 HAVING 中的子查询 2.4 CASE中的子查询 三. 多行子查询 3.1 多行比较操作符 3.2 代码示例 四. 相关子查询： 五.EXISTS 与 NOT EXISTS 六. 练习。。。 一. 分类 分类方式1： 我们按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询 、 多行子查询 。 ...
[mysql]子查询
easycome easygoing、
08-13 345
sql
mysql权限模型子查询 casewhen子查询
Lauuii_的博客
02-08 548
MySQL 记录
Mysql系列课程--第五章 高级查询 表连接 子查询 case when
极客—杨振的博客
11-25 2070
一.表连接1.内连接1.1等值内连接 /李文辉是哪个班的/select s.s_name,c.c_name from student s inner join class c on s.c_no = c.c_no where s.s_name='李文辉';（第一种写法） select s.s_name,c.c_name from student s,class c where s.c_no = c
mysql连接与嵌套查询_case 嵌套查询与连接查询你需要懂得
weixin_42127937的博客
02-08 347
Case 子查询连接查询在sql开发中很常用的一个查询功能,感兴趣的你可不要错过本文，接下来为你详细介绍下代码如下:select * from scorecreate database demouse democreate table [user]([uId] int identity( 1 ,1 ) primary key,[name] varchar ( 50),[level] int --1...
MySQL基础-子查询
热门推荐
~
03-24 1万+
子查询概念：子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较子查询的基本语法结构：说明：子查询（内查询）在主查询之前一次执行完成子查询的结果被主查询（外查询）使用子查询要包含在括号内建议将子查询放在比较条件的右侧，便于阅读。
MySQL多表查询与笛卡尔积解决
本文档主要关注如何有效地进行多表查询，解决笛卡尔积问题，以及如何结合统计函数、分组统计、子查询和其他高级查询技术来实现复杂的业务需求。 首先，多表查询的基本概念在于合并来自两个或更多表的数据。在SQL...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值