MySQL笔记二

最新推荐文章于 2023-05-10 14:39:25 发布

勿在浮砂築高台

最新推荐文章于 2023-05-10 14:39:25 发布

阅读量712

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/m0_60974765/article/details/123782286

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

DQL：

select语句的文档：

MySQL :: MySQL 8.0 Reference Manual :: 13.2.10 SELECT Statement

select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

简单查询：

对查询结果的字段做限制：

语法：

select_expr [, select_expr] ...
[into_option]
[FROM table_references
[PARTITION partition_list]] //选择需要的字段

例：只查询person表的name和id_number字段的记录。

mysql> select name,id_number from person ;
+------+--------------------+
| name | id_number          |
+------+--------------------+
| 张三 | 360428134567063530 |
| 李四 | 360428123456789012 |
+------+--------------------+

对查询结果的记录做限制：

例：只查询person表的name和id_number字段的内容，并且名字是张三的记录。

select_expr [, select_expr] ...
[into_option]
[FROM table_references
[PARTITION partition_list]]
[WHERE where_condition] //选择需要的记录

mysql> select name,id_number from person where name='张三';
+------+--------------------+
| name | id_number          |
+------+--------------------+
| 张三 | 360428134567063530 |
+------+--------------------+

where子句：

单条件查询：

在SQL中，insert、update、delete和select后面都能带where子句，用于插入、修改、删除或查询指定条件的记录。

#SQL语句中使用

where子句语法 SELECT column_name FROM table_name WHERE column_name 运算符 value

例：查询employee中男性的记录：

mysql> select * from employee where sex='男';
+----+--------+------+--------+
| id | name   | sex  | salary |
+----+--------+------+--------+
|  1 | 张三   | 男   |   5500 |
|  4 | 欧阳辉 | 男   |   7500 |
|  6 | 张江   | 男   |   6800 |
|  7 | 李四   | 男   |  12000 |
|  8 | 王五   | 男   |   3500 |
|  9 | 马小龙 | 男   |   6000 |
| 10 | 龙五   | 男   |   8000 |
+----+--------+------+--------+
7 rows in set (0.00 sec)

mysql> select * from employee where sex<>'女';
+----+--------+------+--------+
| id | name   | sex  | salary |
+----+--------+------+--------+
|  1 | 张三   | 男   |   5500 |
|  4 | 欧阳辉 | 男   |   7500 |
|  6 | 张江   | 男   |   6800 |
|  7 | 李四   | 男   |  12000 |
|  8 | 王五   | 男   |   3500 |
|  9 | 马小龙 | 男   |   6000 |
| 10 | 龙五   | 男   |   8000 |
+----+--------+------+--------+

多条件查询：

在where子句中，使用and、or可以把两个或多个过滤条件结合起来。

#and、or运算符语法：

SELECT column_name FROM table_name WHERE condition1 AND condition2 OR condition3

运算符	描述
and	表示左右两边的条件同时成立
or	表示左右两边只要有一个条件成立

（和c里面的 && 和 || 类似）

例：查询年龄是男性中薪资大于10000或薪资小于4000的记录。

mysql> select * from employee where salary>=10000 and sex='男' or salary<=4000 and sex='男';
+----+------+------+--------+
| id | name | sex  | salary |
+----+------+------+--------+
|  7 | 李四 | 男   |  12000 |
|  8 | 王五 | 男   |   3500 |
+----+------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from employee where sex='男' and (salary>10000 or salary<=4000);
+----+------+------+--------+
| id | name | sex  | salary |
+----+------+------+--------+
|  7 | 李四 | 男   |  12000 |
|  8 | 王五 | 男   |   3500 |
+----+------+------+--------+

运算符in的使用：

运算符 IN 允许我们在 WHERE 子句中过滤某个字段的多个值。

#where子句使用in语法：

SELECT column_name FROM table_name WHERE column_name IN(value1, value2, …)

和between and还有and类似，但是这个可以做到不连续的条件的查询

例：查询id为1，4，9的记录

mysql> select * from employee where id in(1,4,9);
+----+--------+------+--------+
| id | name   | sex  | salary |
+----+--------+------+--------+
|  1 | 张三   | 男   |   5500 |
|  4 | 欧阳辉 | 男   |   7500 |
|  9 | 马小龙 | 男   |   6000 |
+----+--------+------+--------+

运算符like的使用：

在where子句中，有时候我们需要查询包含xxx 字符串的所有记录，这时就需要用到运算符like。 #where子句使用like语法

SELECT column_name FROM table_name WHERE column_name LIKE ‘%value%’

说明：

1、LIKE子句中的%类似于正则表达式中的*，匹配任意0个或多个字符

2、LIKE子句中的_匹配任意单个字符

3、LIKE子句中如果没有%和_，就相当于运算符=的效果

例：查询姓张的记录：

mysql> select * from employee where name like '张%';
+----+------+------+--------+
| id | name | sex  | salary |
+----+------+------+--------+
|  1 | 张三 | 男   |   5500 |
|  6 | 张江 | 男   |   6800 |
+----+------+------+--------+

查询姓张且名字是两个子的的记录：

mysql> select * from employee where name like '张_';
+----+------+------+--------+
| id | name | sex  | salary |
+----+------+------+--------+
|  1 | 张三 | 男   |   5500 |
|  6 | 张江 | 男   |   6800 |
+----+------+------+--------+

MySQL内置函数:

我们通常说的MySQL函数指的是MySQL数据库提供的内置函数，包括数学函数、字符串函数、日期和时间函数、聚合函数、条件判断函数等，这些内置函数可以帮助用户更方便地处理表中的数据，简化用户的操作。

函数now()：

函数now()用于返回当前的日期和时间。

例：查询当前的时间。

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-03-28 14:00:54 |
+---------------------+

应用场景：

在实际应用中，大多数业务表都会带一个创建时间create_time字段，用于记录每一条数据的产生时间。在向表中插入数据时，就可以在insert语句中使用now()函数。

示例如下： insert into user(id, name, create_time) values(1, 'zhangsan', now());

函数date_format()：

函数date_format()用于以指定的格式显示日期/时间。

例：分别以-和/做分隔符显示当前的年月日（y m d的大小写有区分）

mysql> select date_format(now(),'%Y-%m-%d');
+-------------------------------+
| date_format(now(),'%Y-%m-%d') |
+-------------------------------+
| 2022-03-28                    |
+-------------------------------+
1 row in set (0.01 sec)

mysql> select date_format(now(),'%Y/%m/%d');
+-------------------------------+
| date_format(now(),'%Y/%m/%d') |
+-------------------------------+
| 2022/03/28                    |
+-------------------------------+

应用场景：在实际应用中，一般会按照标准格式存储日期/时间，如 2019-12-13 14:15:16 。在查询使用数据时，往往又会有不同的格式要求，这时就需要使用date_format()函数进行格式转换。示例如下： select name, date_format(birthday, '%Y/%m/%d') from user

聚合函数：

聚合函数是对一组值进行计算，并返回单个值。 MySQL常用的聚合函数有5个，分别是count、sum、avg、min和max。（忽略空值就是忽略这条记录，看avg()函数就知道了）

例：计算employee表中的工资最少员工的记录

mysql> select id,name,sex,max(salary) from employee;
+------+------+------+-------------+
| id   | name | sex  | max(salary) |
+------+------+------+-------------+
|    1 | 张三 | 男   |       12000 |
+------+------+------+-------------+

函数ifnull()：

函数ifnull()用于处理NULL值。

ifnull(v1,v2)，如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。（类似于三目运算符）

mysql> select ifnull(1/0,1);
+---------------+
| ifnull(1/0,1) |
+---------------+
|        1.0000 |
+---------------+

case when：

case when是流程控制语句，可以在SQL语句中使用case when来获取更加准确和直接的结果。 SQL中的case when类似于编程语言中的if else或者switch。

#case when的语法有2种

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END //推荐

CASE WHEN [expr] THEN [result1]…ELSE [default] END

例：在employ表中输出，全部数据，性别以'F'、'M'代表

select id,name,case sex when '男' then 'F' when '女' then 'M' else
    -> ' 'end as sex ,salary from employee;
+----+--------+-----+--------+
| id | name   | sex | salary |
+----+--------+-----+--------+
|  1 | 张三   | F   |   5500 |
|  2 | 李洁   | M   |   4500 |
|  3 | 李小梅 | M   |   4200 |
+----+--------+-----+--------+

最好在end后面使用as换名字，不然显示的太长了，观看不方便。

查询结果排序与分页：

排序的应用场景我们已经掌握使用select语句结合where查询条件获取需要的数据，但在实际的应用中，还会遇到下面这类需求，又该如何解决？

一、学生按身高从高到低进行排列。

二、双十一，某商城的商品交易量排行榜

order by的使用：

在SQL中，使用order by对查询结果集进行排序，可以按照一列或多列进行排序。

#order by语法

SELECT column_name1, column_name2 FROM table_name1, table_name2 ORDER BY column_name, column_name [ASC|DESC]

说明： 1. ASC表示按升序排列，DESC表示按降序排列。 2. 默认情况下，对列按升序排列

例：对employee表，第一排序标准是性别，第二排序标准是薪资，进行排序。

mysql> select * from employee order by sex,salary;
+----+--------+------+--------+
| id | name   | sex  | salary |
+----+--------+------+--------+
| 12 | 马小花 | 女   |   4000 |
|  3 | 李小梅 | 女   |   4200 |
|  2 | 李洁   | 女   |   4500 |
|  5 | 李芳   | 女   |   8500 |
| 11 | 冯小芳 | 女   |  10000 |
| 13 | jean   | 男   |   NULL |
|  8 | 王五   | 男   |   3500 |
|  1 | 张三   | 男   |   5500 |
+----+--------+------+--------+

limit的使用

在SELECT语句中使用LIMIT子句来约束要返回的记录数，通常使用LIMIT实现分页。

#limit语法

SELECT column_name1, column_name2 FROM table_name1, table_name2 LIMIT [offset,] row_count

说明： 1. offset指定要返回的第一行的偏移量。第一行的偏移量是0，而不是1。 2. row_count指定要返回的最大行数（和操作系统的偏移量类似）

例：以每页5行进行查看数据

mysql> select * from employee limit 0,3;
+----+--------+------+--------+
| id | name   | sex  | salary |
+----+--------+------+--------+
|  1 | 张三   | 男   |   5500 |
|  2 | 李洁   | 女   |   4500 |
|  3 | 李小梅 | 女   |   4200 |
+----+--------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from employee limit 3,3;
+----+--------+------+--------+
| id | name   | sex  | salary |
+----+--------+------+--------+
|  4 | 欧阳辉 | 男   |   7500 |
|  5 | 李芳   | 女   |   8500 |
|  6 | 张江   | 男   |   6800 |
+----+--------+------+--------+

group by：

但在实际的应用中，还会遇到下面这类需求，又该如何解决？（需要分组的问题）

一、公司想知道每个部门有多少名员工。二、班主任想统计各科第一名的成绩

group by的使用

从字面上理解，group by表示根据某种规则对数据进行分组，它必须配合聚合函数进行使用，对数据进行分组后可以进行count、sum、avg、max和min等运算。（因为分组之后每一组只显示一条数据，如果不搭配，聚合函数使用意义不大。所以一般是按照分组的字段和聚合函数，作为筛选之后的结果显示）

#group by语法

SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name

说明： 1. aggregate_function表示聚合函数。 2. group by可以对一列或多列进行分组

例：查看employee组中每一组有多少名员工

mysql> select dept,count(*) from employee group by dept;
+-------+----------+
| dept  | count(*) |
+-------+----------+
| 部门A |        6 |
| 部门C |        3 |
| 部门B |        4 |
+-------+----------+

having的使用：

WHERE是对分组之前的记录进行筛选，HAVING是对分组之后的字段进行筛选

#having语法

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value

例：查看人数小于5的部门，薪资最高时多少

mysql> select dept,max(salary) from employee group by dept having count(*)<5;
+-------+-------------+
| dept  | max(salary) |
+-------+-------------+
| 部门C |       10000 |
| 部门B |       12000 |
+-------+-------------+

group_concat:

是一个函数，配合group_by 使用，因为前面提到，group_by 一个分组只能显示一条数据，所以就提出了group_concat。用于将某一列的值按指定的字符进行分割中间可以进行排序。（MySQL默认的分割符是，）

#group_concat语法

group_concat([distinct] column_name [order by column_name asc/desc] [separator '分隔符'])

例：将employee表中每个部门里面的名字显示出来，按照名字的逆序，分隔符使用 ';'

mysql> select dept,group_concat(name order by name separator ';') from employee group by dept;
+-------+------------------------------------------------+
| dept  | group_concat(name order by name separator ';') |
+-------+------------------------------------------------+
| 部门A | jean;张三;张江;李小梅;李芳;马小龙              |
| 部门B | 李四;王五;马小花;龙五                          |
| 部门C | 冯小芳;李洁;欧阳辉                             |
+-------+------------------------------------------------+

distinct:

用于查询中返回列的去重，可以对单列、双列进行操作。

#distinct语法

SELECT DISTINCT column_name,column_name

FROM table_name;

例：

对单列进行去重，查看有多少个用户

mysql> select distinct username from footprint;
+----------+
| username |
+----------+
| liufeng  |
| zhangsan |
| lisi     |
+----------+

对双列进行去重，查看有用户都去过哪些地方（distinct是同时作用于两个字段的）

mysql> select distinct username,city from footprint;
+----------+-------+
| username | city  |
+----------+-------+
| liufeng  |  贵阳 |
| liufeng  |  北京 |
| zhangsan |  上海 |
| lisi     |  拉萨 |
+----------+-------+

表连接（内连接、外连接、自连接）：

note:两个表交换一下，左连接就可以转换成右连接，MySQL里面没有全连接。

内连接：

例：查询有分数同学的成绩

mysql> select A.stu_no,A.name,B.course,B.score
    -> from
    ->student A inner join score B on(A.stu_no=B.stu_no);
+---------+------+----------+-------+
| stu_no  | name | course   | score |
+---------+------+----------+-------+
| 2016001 | 张三 | 计算机   |    99 |
| 2016001 | 张三 | 离散数学 |    85 |
| 2016002 | 李芳 | 计算机   |    78 |
+---------+------+----------+-------+

思考：交换A B的顺序可以吗？可以，这里是内连接，没有什么影响

左连接：

例：查询所有同学的成绩（包括，没考的）

mysql> select A.stu_no,A.name,B.course,B.score
    -> from
    -> student A left join score B on(A.stu_no=B.stu_no);
+---------+--------+----------+-------+
| stu_no  | name   | course   | score |
+---------+--------+----------+-------+
| 2016001 | 张三   | 计算机   |    99 |
| 2016001 | 张三   | 离散数学 |    85 |
| 2016002 | 李芳   | 计算机   |    78 |
| 2016003 | 张晓燕 | NULL     |  NULL |
+---------+--------+----------+-------+

这个时候必须学生表，加入到成绩表中，必然就是变成右连接了

笛卡尔连接：

没用，要避免

mysql> select A.stu_no,A.name,B.course,B.score
    -> from student A ,score B;
+---------+--------+----------+-------+
| stu_no  | name   | course   | score |
+---------+--------+----------+-------+
| 2016003 | 张晓燕 | 计算机   |    99 |
| 2016002 | 李芳   | 计算机   |    99 |
| 2016001 | 张三   | 计算机   |    99 |
| 2016003 | 张晓燕 | 离散数学 |    85 |
| 2016002 | 李芳   | 离散数学 |    85 |
| 2016001 | 张三   | 离散数学 |    85 |
| 2016003 | 张晓燕 | 计算机   |    78 |
| 2016002 | 李芳   | 计算机   |    78 |
| 2016001 | 张三   | 计算机   |    78 |
+---------+--------+----------+-------+

自连接：

自连接是一种特殊的表连接,它是指相互连接的表在物理上同为一张表,但是逻辑上是多张表。自连接通常用于表中的数据有层次结构，如区域表、菜单表、商品分类表等。

#自连接语法

SELECT A.column, B.columnFROM table A, table B
WHERE A.column = B.column;

有层次结构，如果是省份pid是0，如果是城市，pid就是省份的id。

mysql> select *from area;
+----+-----+--------+
| id | pid | name   |
+----+-----+--------+
|  1 |   0 | 贵州省 |
|  2 |   1 | 贵阳   |
|  3 |   1 | 遵义   |
|  4 |   0 | 广东省 |
|  5 |   4 | 广州   |
|  6 |   4 | 深圳   |
+----+-----+--------+

例：查看哪个城市是属于哪个省份的。

select A.id,A.name,B.name as provinceName
    -> from area A,area B
    -> where A.pid = B.id and A.pid<>0;
+----+------+--------------+
| id | name | provinceName |
+----+------+--------------+
|  2 | 贵阳 | 贵州省       |
|  3 | 遵义 | 贵州省       |
|  5 | 广州 | 广东省       |
|  6 | 深圳 | 广东省       |
+----+------+--------------+

 select A.id,A.name,B.name as provinceName
    -> from area A join area B on(A.pid=B.id);
+----+------+--------------+
| id | name | provinceName |
+----+------+--------------+
|  2 | 贵阳 | 贵州省       |
|  3 | 遵义 | 贵州省       |
|  5 | 广州 | 广东省       |
|  6 | 深圳 | 广东省       |
+----+------+--------------+

和内连接，十分相似，自连接就是，假想自己有两张表，其他都一样。

子查询EXISTS和IN的使用：

子查询：

子查询IN：

如果运算符in后面的值是来源于某个查询结果,并非是指定的几个值,这时就需要用到子查询。子查询又称为内部查询或嵌套查询,即在SQL查询的WHERE子句中嵌入查询语句。（假想in是等号）

#子查询in语法

SELECT column name FROM table_ name
WHERE column_ name IN(
SELECT column_ name FROM table_ name [WHERE]
);

EXISTS是子查询中用于测试内部查询是否返回任何行的布尔运算符。将主查询的数据放到子查询中做条件验证,根据验证结果( TRUE或FALSE )来决定主查询的数据结果是否保留。

#where子句使用exists语法

SELECT column_ name1
FROM table_ name1
WHERE EXISTS (SELECT * FROM table_ name2 WHERE condition);

#查询所有选修了课程的学生信息
#select A.* from student A where A.stu_no in (select
#B.stu_no from score B);

#查询选了离散数学的学生
# select A.* from student A where A.stu_no in(select B.stu_no from score B where 
# course='离散数学'); 

#查询所有选修了课程的学生信息
#select A.* from student A where exists(select * from score B where A.stu_no=B.stu_no);
#查询所有没有选修了课程的学生信息
#select A.* from student A where not exists(select * from score B where A.stu_no=B.stu_no);