MySQL实操知识点(执行顺序、符号说明、常用函数、连接知识点等）

是阿ciu

已于 2023-09-07 12:01:33 修改

阅读量153

点赞数

文章标签： mysql 数据库 sql

于 2023-08-28 15:50:42 首次发布

本文链接：https://blog.csdn.net/IDEA_guo/article/details/132539688

版权

MySQL实操知识点

sql语句执行顺序

https://blog.csdn.net/u014044812/article/details/51004754

sql语句的执行顺序：FROM—WHERE–GROUP BY—HAVING—SELECT—ORDER BY

sql语句符号说明

字段名和表名要用反引号`

格式(tab间隔/大写/字符串用单引号’')

SELECT
    vend_name
FROM 
    Vendors
WHERE
    vend_country='USA' 
    AND vend_state='CA'；

关于存储设置长度n

一文搞懂MySQL的数据类型中长度的含义_mysql 字符串字段类型长度_梅坞茶坊的博客-CSDN博客

varchar和char的n都是指字符数，不是字节数
整数类型，int/bigint/tinyint等，不影响存储环节，仅影响在检索时的输出格式，例如int，无论设置多少，永远是占用4个字节
FLOAT 类型固定占用4个字节， DOUBLE 类型固定占用8个字节，逻辑和上述的整型类似，不再赘述。
DECIMAL(m,n)的m指的是字节，1一个数字占一个字节

常用关键字

DISTINCT

关键词 DISTINCT 用于返回唯一不同的值。
- ```
SELECT DISTINCT 列名称 FROM 表名称
```

IN

in，允许我们在 WHERE 子句中规定多个值。

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

DESC

顺序排列是ORDER BY ASC/desc

GROUP BY

按照哪个字段进行分组，或者是哪几个字段进行分组，和聚合函数搭配使用
https://blog.csdn.net/u014717572/article/details/80687042?ops
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0kyVxRWH-1693206970423)(./SQL.assets/162343319172617.jpg)]

HAVING

它往往与 GROUP BY 配合使用，为聚合操作指定条件
因为WHERE后面不能有聚合函数

齐全且实用的MySQL函数使用大全_Sheenky的博客-CSDN博客

字符函数

LENGTH(str)

获取参数值的字节个数

对于utf-8字符集来说，一个英文占1个字节；一个中文占3个字节；

对于gbk字符集来说，一个英文占1个字节；一个中文占2个字节；

SELECT
	stu.s_name,LENGTH(stu.s_name)
FROM
	t_student stu 
GROUP BY
	stu.s_id

在这里插入图片描述

CONCAT(str1,str2,…)

字符串拼接，常用于mybatis进行like模糊查询

SELECT
	CONCAT(stu.s_name,"：",stu.s_birth) AS birthInfo
FROM
	t_student stu 
GROUP BY
	stu.s_id
	
-- birthInfo LIKE concat('%',#{birthInfo},'%')

在这里插入图片描述

UPPER(str)/LOWER(str)

把字母变成大/小写

SELECT 
	LOWER('ACIU'),UPPER('aciu')

在这里插入图片描述

SUBSTR(str,pos,len)

从pos开始截取len长度的str,pos从1开始

SELECT
	SUBSTR("0123456",1,2)

在这里插入图片描述

INSTR(str,substr)

返回子串第一次出现的位置索引，如果找不到，返回0；

SELECT
	INSTR("01234567","7")

在这里插入图片描述

TRIM(str)

去掉字符串前后的空格，但不能去掉前后的空格

SELECT
	TRIM("  aciu 好帅  啊 ") AS result

在这里插入图片描述

LPAD(str,len,padstr)

左填充，用指定的字符，实现对字符串左填充指定长度

SELECT
	LPAD( 10, 6, 0 ) AS res

在这里插入图片描述

RPAD(str,len,padstr)

右填充，用指定的字符，实现对字符串右填充指定长度

SELECT
	RPAD(101,7,0) AS res

在这里插入图片描述

REPLACE(str,from_str,to_str)

替换指定字符，只是显示替换并无修改到实际数据

SELECT
	REPLACE(stu.s_name,'赵雷','赵云')
FROM
	t_student stu

在这里插入图片描述

CAST(expr AS type)

https://www.yiibai.com/mysql/cast.html

CAST()函数将任何类型的值转换为具有指定类型的值。目标类型可以是以下类型之一：BINARY，CHAR，DATE，DATETIME，TIME，DECIMAL，SIGNED，UNSIGNED。

-- 主要是应对类型为decimal的字段，假如为0的话Mysql会自动为其在后面填充0，如decimal(3,2),检索出来就为0.00，下面例子是把后两位0.00去掉
SELECT
	0+CAST(0.00 AS CHAR) AS res

在这里插入图片描述

数学函数

ABS(X)

绝对值

SELECT 
		ABS(X)

在这里插入图片描述

ROUND(X,D)

四舍五入，D为保留位数

SELECT
	ROUND( 7.89, 1 ),
	ROUND( 7.81, 1 ),
	ROUND(- 7.11, 1 ),
	ROUND(- 7.16, 1 )

在这里插入图片描述

CEIL(X)

向上取整,找出大于等于x的最小整数

SELECT
	CEIL(-7.77),CEIL(1.1),CEIL(1.7)

在这里插入图片描述

FLOOR(X)

向下取整，找出小于等于x的最小整数

SELECT
	FLOOR( 7.11 ),
	FLOOR( 7.6 ),
	FLOOR(- 7.1 ),
	FLOOR(- 7.6)

在这里插入图片描述

TRUNCATE(X,D)

截断函数，x为小数

D若为正数，则截掉小数点右边第D位的数字
D若为0，则截掉小数点后所有数字
D若为负数，则往小数点左边D位截断

SELECT
	TRUNCATE(7.1234,2),
	TRUNCATE(7.1234,-1),
	TRUNCATE(7.1234,0)

在这里插入图片描述

MOD(N,M)

取余

SELECT
	MOD(9,4),MOD(-10,3)

在这里插入图片描述

POW(X,Y)

指数函数计算

SELECT
	POW(2,3)

在这里插入图片描述

时间与日期函数

格式表

时间是指时分秒，日期是指年月日

序号	格式符	含义
1	%Y	四位的年份
2	%y	2位的年份
3	%m	月份（01,02，…11,12）
4	%c	月份（1,2,3…11,12）
5	%d	日（01,02，…）
6	%H	小时（24小时）
7	%h	小时（12小时）
8	%i	分钟（00,01，…59）
9	%s	秒（00,01，…59）

NOW()

表示当下日期和时间

SELECT
	NOW()

在这里插入图片描述

CURDATE()

表示当下日期

SELECT
	CURDATE()

在这里插入图片描述

CURTIME()

表示当下时间

SELECT
	CURTIME()

在这里插入图片描述

获取日期和时间中的年、月、日、时、分、秒

-- 获取年份
year(DATE)

-- 获取月份
month(DATE)

-- 获取日
day(DATE)

-- 获取小时
hour(DATE)

-- 获取分钟
minute(DATE)

-- 获取秒数
second(DATE)

WEEKOFYEAR(date)

获取当前时刻下所属周数

SELECT
	WEEKOFYEAR(NOW())

在这里插入图片描述

QUARTER(date)

获取当前季度

SELECT
	QUARTER(NOW())

在这里插入图片描述

STR_TO_DATE(str,format)

将字符串转换为指定格式的日期

SELECT
	STR_TO_DATE('2023-8-26 16:56:03','%Y-%m-%d %H:%i:%s')

在这里插入图片描述

DATE_FORMAT(date,format)

将日期转换成想要的日期字符串

SELECT
	DATE_FORMAT('2023-08-28 09:09:00','%Y年%m月%d日')

在这里插入图片描述

DATE_ADD(date,INTERVAL expr unit)

向前、向后偏移日期和时间，正号为向后，负号为向前

SELECT
	DATE_ADD('2023-08-28',interval -1 year) AS afer_one_year,
	DATE_ADD('2023-08-28',interval 1 month) AS afer_one_month,
	DATE_ADD('2023-08-28',interval -1 day) AS afer_one_day,
	DATE_ADD('2023-08-28',interval 1 hour) AS afer_one_hour,
	DATE_ADD('2023-08-28',interval -1 minute) AS afer_one_minute,
	DATE_ADD('2023-08-28',interval 1 second) AS afer_one_second

在这里插入图片描述

LAST_DAY(date)

某个月的最后一天

SELECT
	LAST_DAY(NOW())

在这里插入图片描述

DATEDIFF(expr1,expr2)

计算两个日期相差的天数

SELECT
	DATEDIFF(DATE_ADD(NOW(),interval 1 month),NOW())

在这里插入图片描述

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

SELECT
	TIMESTAMPDIFF(day,NOW(),DATE_ADD(NOW(),interval 1 month))

在这里插入图片描述

流程控制函数

IF( expr1 , expr2 , expr3 )

expr1 的值为 TRUE，则返回值为 expr2
expr1 的值为FALSE，则返回值为 expr3

SELECT 
	IF('111'!='222','不相等','相等'),
	IF('111'='222','不相等','相等')

在这里插入图片描述

另外，mybatis有做相应的动态sql

<if test="">
    AND ...
</if>

IFNULL( expr1 , expr2 )

在 expr1 的值不为 NULL的情况下都返回 expr1，否则返回 expr2，如下：

SELECT 
	IFNULL(NULL,1),
	IFNULL(2,1)

在这里插入图片描述

CASE…WHEN…ELSE…END

实现多条件查询值筛选，主要为两种方式

在case后面加入某个表达式，在when后面进行等值判断
case后面不接任何表达式，直接在when后面接表达式判断并then输出结果

第一种

SELECT
	co.c_id,
	CASE co.c_id
	WHEN 1 THEN '语文'
	WHEN 2 THEN '数学'
	WHEN 3 THEN '英语'
	ELSE '新课程'
END AS course_name
FROM
	t_course co

在这里插入图片描述

第二种

SELECT
	sc.s_score AS score,
CASE
		WHEN sc.s_score >= 80 THEN
		'优秀' 
		WHEN sc.s_score >= 70 THEN
		'良好' 
		WHEN sc.s_score >= 60 THEN
		'及格' 
		ELSE  '不及格' 
	END AS result
FROM
	t_score sc

在这里插入图片描述

与聚合函数搭配使用

求出本班所有学生成绩的最高分、最低分、平均分、及格率、中等率、优良率、优秀率

SELECT	
	co.c_name AS course_name,
	MAX(sc.s_score) AS highest,
	MIN(sc.s_score) AS lowest,
	AVG(sc.s_score) AS average,
	SUM(CASE WHEN sc.s_score >= 60 THEN 1 ELSE 0 END)/COUNT(sc.s_score) AS pass_rate,
	SUM(CASE WHEN sc.s_score>=70 AND sc.s_score<80 THEN 1 ELSE 0 END)/COUNT(sc.s_score) AS med_rate,
	SUM(CASE WHEN sc.s_score>=80 AND sc.s_score<90 THEN 1 ELSE 0 END)/COUNT(sc.s_score) AS good_rate,
	SUM(CASE WHEN sc.s_score>=90 THEN 1 ELSE 0 END)/COUNT(sc.s_score) AS excell_rate
FROM
	t_score sc INNER JOIN t_course co ON sc.c_id=co.c_id
GROUP BY
	co.c_id

在这里插入图片描述

系统函数

VERSION()

查看MySQL系统版本信息号

SELECT
	VERSION()

CONNECTION_ID()

查看当前登入用户的连接id

SELECT
	CONNECTION_ID()

PROCESSLIST

查看用户的连接信息

SHOW
	PROCESSLIST

在这里插入图片描述

DATABASE()/SCHEMA()

查看当前使用的数据库

SELECT DATABASE(),SCHEMA()

在这里插入图片描述

USER()/CURRENT_USER()/SYSTEM_USER()

显示用户

SELECT 
	USER(),CURRENT_USER(),SYSTEM_USER()

在这里插入图片描述

CHARSET(str)

返回字符串使用字符集

SELECT 
	CHARSET(str)

在这里插入图片描述

COLLATION(str)

返回字符串排列方式

SELECT 
   COLLATION('abc')

在这里插入图片描述

聚合函数

最大值—max()
最小值—min()
平均值—avg()
总值 —sum()
总数 —count()

注意，空值会跳过，有时候得到的结果和AVG()函数不一定一样。当存在某计算列空值但其他列不是空值的时候就会出现不一样的结果，因为分母count(*)并没有跳过空值列。

连接

在这里插入图片描述

一分钟让你搞明白 left join、right join和join的区别_往事随风_h的博客-CSDN博客_join和leftjoin的区别

join，其实就是“inner join”，为了简写才写成join

不加条件的join就是笛卡尔积

Join 分为：

内连接(inner join)
外连接(outer join)

其中外连接分为：

左外连接(left outer join)
右外连接(right outer join)
全外连接(full outer join)

其中外连接的“OUTER”关键字可以省略不写。

题型

技巧

在审题前，遇到“查询没有…”或者“查询…和…”这种有特殊且多个条件的题目，记得使用not in去子查询反面的例子

子查询和联结表的效率比较

查询的结果是一致的，如果没有加去重的话，两个sql语句，连接查询效率相对高一些
两个查询语句都使用了去重，连接查询效率可能比子查询效率低得多
执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。
由于去重的原因，连接查询去重机制是将两张表中符合条件的数据先筛选出来，然后再去重;但是子查询相当于去重了两遍，副表中筛选了一次以后数据量小了很多，这时候再去跟主表中的数据匹配，再去重，工作量小了很多，所以查询效率高了很多

子查询

在SELECT子查询

https://www.nowcoder.com/practice/ce313253a81c4947b20e801cd4da7894

SELECT
    cust_id,
    (
        SELECT
            SUM(item_price*quantity)
        FROM
            OrderItems oi
        WHERE
            oi.order_num=o.order_num
    )AS total_ordered
FROM
    Orders o
Order By
    total_ordered DESC;

在WHERE子查询

https://www.nowcoder.com/practice/b692b0174c0444fa9452aee2d082fbbb

SELECT
    cust_id,order_date
FROM
    Orders
WHERE
    order_num 
    IN (SELECT
            order_num
        FROM
            OrderItems
        WHERE
            prod_id='BR01')
ORDER BY
    order_date;

联结表

在FROM处联结

选出来，然后再去重;但是子查询相当于去重了两遍，副表中筛选了一次以后数据量小了很多，这时候再去跟主表中的数据匹配，再去重，工作量小了很多，所以查询效率高了很多

子查询

在SELECT子查询

https://www.nowcoder.com/practice/ce313253a81c4947b20e801cd4da7894

SELECT
    cust_id,
    (
        SELECT
            SUM(item_price*quantity)
        FROM
            OrderItems oi
        WHERE
            oi.order_num=o.order_num
    )AS total_ordered
FROM
    Orders o
Order By
    total_ordered DESC;

在WHERE子查询

https://www.nowcoder.com/practice/b692b0174c0444fa9452aee2d082fbbb

SELECT
    cust_id,order_date
FROM
    Orders
WHERE
    order_num 
    IN (SELECT
            order_num
        FROM
            OrderItems
        WHERE
            prod_id='BR01')
ORDER BY
    order_date;