MySQL常用指令

CAMELLIA2024-07-222024-07-23

[!TIP]
记住：如果别名中有空格的话，可以将这个别名使用双引号或者单引号将其括起来。
1
2
3
SELECT ename, sal * 12 "year sal" FROM EMP;

SELECT ename, sal*12 as "year sal" FROM EMP;
select * from dept; 在执行的时候会被解析为 select DEPTNO, DNAME, LOC from dept; 再执行，所以这种效率方面弱一些。
别名是中文是可以的，但是对于低版本的MySQL来说会报错，需要添加双引号或单引号。
1
SELECT ename, sal*12 年薪 FROM EMP;
在MySQL当中，字符串可以使用单引号，也可以使用双引号
查询的数据是字符串类型，要用 ‘ ‘或者” “。
1
2
3
4
# 查询工作岗位不是MANAGER的员工姓名和岗位
SELECT ENAME , JOB FROM EMP WHERE JOB<> 'MANAGER';

SELECT ENAME , JOB FROM EMP WHERE JOB<> "MANAGER";
and和or同时出现时，and优先级较高，会先执行，如果希望or先执行，这个时候需要给or条件添加小括号。
1
2
# 找出薪资小于1500，并且部门编号是20或30的员工姓名、薪资、部门编号。
SELECT ENAME , SAL , DEPTNO FROM EMP WHERE SAL < 1500 AND (DEPTNO = 20 OR DEPTNO = 30);
between...and...在使用时一定是左小右大。左大右小时无法查询到数据。
1
2
# 找出薪资在1600到3000的员工姓名、薪资。
SELECT ENAME , SAL FROM EMP WHERE SAL BETWEEN 1600 AND 3000;
between...and... 和 >= and <=只是在写法结构上有区别，执行原理和效率方面没有区别。
SQL语句中日期需要加上单引号。
1
2
# 查询在1982-01-23到1987-04-19之间入职的员工
SELECT * FROM EMP WHERE HIREDATE BETWEEN '1982-01-23' AND '1987-04-19';
判断某个数据是否为null，不能使用等号，只能使用 is null。
1
2
# 找出津贴为空的员工姓名、薪资、津贴。
SELECT ENAME , SAL , COMM FROM EMP WHERE COMM IS NULL;
判断某个数据是否不为null，不能使用不等号，只能使用 is not null。
1
2
# 找出津贴不为空的员工姓名、薪资、津贴。
SELECT ENAME , SAL , COMM FROM EMP WHERE COMM IS NOT NULL;
IN 关键字就是在 SQL 查询中，用来检查某个值是不是在你列出的那些值里。
1
2
# 找出工作岗位是MANAGER和SALESMAN的员工姓名、薪资、工作岗位
SELECT ENAME , SAL , JOB FROM EMP WHERE JOB IN ('MANAGER' ,'SALESMAN');
NOT IN 关键字在 SQL 查询中，用来排除掉不想要的一堆值。
1
2
# 找出工作岗位不是MANAGER和SALESMAN的员工姓名、工作岗位。
SELECT ENAME , SAL , JOB FROM EMP WHERE JOB NOT IN ('MANAGER' ,'SALESMAN');
in和not in对于NULL的处理方式
1
2
3
4
5
6
7
8
9
select * from emp where comm in(NULL, 300);
# in 执行的实质，NULL不能用=判断，所以comm = NULL恒为false。
select * from emp where comm = NULL or comm = 300;
-- 查出来的都是comm = 300为true的。

select * from emp where comm not in(NULL, 300);
# not in 执行的实质，NULL不能用<>判断，所以comm <> NULL恒为false。
select * from emp where comm <> NULL and comm <> 300;
-- where 后面恒为false，返回空。
如果in和or所在的列有索引或主键，二者性能几乎相同，执行计划和时间相差无几。但在没有索引的情况下，性能差异显著。随着数据量增加，in的效率变化不大，而or的性能会急剧下降，呈指数级增长。
在模糊查询中，通配符主要包括两个：一个是%，一个是下划线_。其中%代表任意多个字符，下划线_代表任意一个字符。
1
2
3
4
5
6
# 查询员工名字以'S'开始的员工姓名
SELECT ENAME FROM EMP WHERE ENAME LIKE 'S%';
#查询员工名字中第二个字母是'A'的员工姓名
SELECT ENAME FROM EMP WHERE ENAME LIKE '_A%';
# 查询学员名字中含有下划线的。
SELECT * FROM EMP WHERE ENAME LIKE '%\_%';
排序操作：单一字段升序、单一字段降序、默认采用升序、多个字段排序。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 单一字段升序
-- 查询员工的编号、姓名、薪资，按照薪资升序排列。
SELECT EMPNO , ENAME , SAL FROM EMP ORDER BY SAL ASC;

# 单一字段降序
-- 查询员工的编号、姓名、薪资，按照薪资降序排列。
SELECT EMPNO , ENAME , SAL FROM EMP ORDER BY SAL DESC;

# 默认采用升序
-- 查询员工的编号、姓名、薪资，按照薪资升序排列。
SELECT EMPNO , ENAME , SAL FROM EMP ORDER BY SAL;

-- 查询员工的编号、姓名，按照姓名升序排列。
SELECT EMPNO , ENAME FROM EMP ORDER BY ENAME;

# 多个字段排序
-- 查询员工的编号、姓名、薪资，按照薪资升序排列，如果薪资相同的，再按照姓名升序排列。
SELECT EMPNO , ENAME , SAL FROM EMP ORDER BY SAL ASC , ENAME ASC;

# where和order by的位置,where先执行，order by语句是最后执行的。
-- 找出岗位是MANAGER的员工姓名和薪资，按照薪资升序排列。
SELECT ENAME , SAL FROM EMP WHERE JOB = 'MANAGER' ORDER BY SAL ASC;
distinct去重，distinct只能出现在所有字段的最前面。当distinct出现后，后面多个字段一定是联合去重的。
1
2
3
4
5
-- 查询去重工作岗位
SELECT DISTINCT JOB FROM EMP;

-- 找出公司中不同部门的不同工作岗位。
SELECT DISTINCT DEPTNO , JOB FROM EMP;
If语句：如果条件为TRUE则返回“YES”，如果条件为FALSE则返回“NO”。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 如果工资高于3000，则输出1，反之则输出0
SELECT ENAME , IF(SAL > 3000, 1 , 0) FROM EMP;

-- 如果名字是SMITH的，工资上调10%，其他员工工资正常显示。
SELECT ENAME , IF(ENAME = 'SMITH' , SAL*1.1 ,SAL) AS SAL FROM EMP;

-- 工作岗位是MANAGER的工资上调10%，是SALESMAN的工资上调20%，其他岗位工资正常。
SELECT
	ENAME, JOB,
IF
	(
		JOB = 'MANAGER',
		SAL * 1.1,
	IF
	( JOB = 'SALESMAN', SAL * 1.2, SAL )) AS SAL 
FROM
	EMP;
cast函数用于将值从一种数据类型转换为表达式中指定的另一种数据类型。
1
2
3
4
5
6
SELECT CAST('2022-7-8' AS DATE);

SELECT CAST('2022-7-8 09:12;34' AS TIME);

SELECT CAST('2022-7-8 09:12;34' AS datetime);
加密函数:可以将给定的字符串经过md5算法进行加密处理，字符串经过加密之后会生成一个固定长度32位的字符串。
1
SELECT md5('camellia·XIAOHUA');
获取当前日期和时间
1
2
# 获取当前日期和时间
SELECT NOW() , SLEEP(2) , SYSDATE();
[!CAUTION]

now()：获取的是执行select语句的时刻。sysdate()：获取的是执行sysdate()函数的时刻。
获取当前日期
1
2
# 获取当前日期
SELECT CURDATE() AS way1 , CURRENT_DATE AS way2 , CURRENT_DATE() AS way3;
获取当前时间
1
2
# 获取当前时间
SELECT CURTIME() AS way1 , CURRENT_TIME() AS way2 , CURRENT_TIME AS way3 ;
获取单独的年、月、日、时、分、秒
1
SELECT NOW() AS 日期 , YEAR(now()) AS 年 , MONTH(now()) AS 月 , DAY(now()) AS 日 , HOUR(NOW()) AS 小时 ,MINUTE(NOW()) AS 分钟, SECOND(NOW()) AS 秒, DATE(NOW()) AS  期日, TIME(NOW()) AS 时间;
date_add函数
1
2
给指定的日期添加间隔的时间，从而得到一个新的日期。
SELECT DATE_ADD(DATE(NOW()), INTERVAL 3 DAY);
date_format日期格式化函数，将日期转换成具有某种格式的日期字符串，通常用在查询操作当中。
1
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') AS 北京时间;
[!CAUTION]

在MySQL当中，默认的日期格式就是：%Y-%m-%d %H:%i:%s，所以当你直接输出日期数据的时候，会自动转换成该格式的字符串。
str_to_date函数，将char类型的日期字符串转换成日期类型date，通常使用在插入和修改操作当中。
1
2
INSERT INTO events (event_name, event_date)
VALUES ('Conference', STR_TO_DATE('2024-07-23', '%Y-%m-%d'));
[!CAUTION]

如果提供的日期字符串格式能够被MySQL解析，str_to_date函数是可以省略的，底层会自动调用该函数进行类型转换。（百度）
dayofweek、dayofmonth、dayofyear函数
1
2
3
4
5
6
7
8
-- dayofweek：一周中的第几天（1~ 7），周日是1，周六是7。
SELECT DAYOFWEEK(NOW());

-- dayofmonth：一个月中的第几天（1~31）
SELECT DAYOFMONTH(NOW());

-- dayofyear：一年中的第几天（1~366）
SELECT DAYOFYEAR(NOW());
last_day、datediff、timediff函数
1
2
3
4
5
6
7
8
-- last_day函数,获取给定日期所在月的最后一天的日期。
SELECT LAST_DAY(NOW());

-- datediff函数,计算两个日期之间所差天数。
SELECT DATEDIFF(NOW() , DATE_ADD(NOW(), INTERVAL -1 YEAR));

-- timediff函数,计算两个日期所差时间。
SELECT TIMEDIFF(NOW() , DATE_ADD(NOW(), INTERVAL -1 HOUR));
rand()和rand(x)
1
2
3
4
5
-- rand()生成0到1的随机浮点数。
SELECT RAND();

-- rand(x)生成0到1的随机浮点数，通过指定整数x来确定每次获取到相同的浮点值。
SELECT RAND(6);
round(x)和round(x,y)四舍五入
1
2
3
4
5
-- round(x)四舍五入，保留整数位，舍去所有小数。
SELECT ROUND(9.77856);

-- round(x,y)四舍五入，保留y位小数。
SELECT  ROUND(9.77856,3);
truncate(x, y)，保留y位小数，其他的全舍去。
1
SELECT TRUNCATE(9.3478,2);
ceil与floor
1
2
3
4
5
-- ceil函数：返回大于或等于数值x的最小整数
SELECT CEIL(5.1);

-- floor函数：返回小于或等于数值x的最大整数
SELECT FLOOR(5.9);
ifnull(x, y)，空处理函数，当x为NULL时，将x当做y处理。
1
2
-- ifnull(comm, 0)，表示如果员工的津贴是NULL时当做0处理。
SELECT (SAL + IFNULL(COMM,0))*12 AS 年薪 FROM EMP;
[!CAUTION]

在SQL语句中，凡是有NULL参与的数学运算，最终的计算结果都是NULL，所以可以用ifnull处理后再计算。
转大写upper和ucase
1
SELECT UPPER(ENAME) AS WAY1 , UCASE(ENAME) AS WAY2 FROM EMP;
转小写lower和lcase
1
SELECT LOWER(ENAME) AS WAY1 , LCASE(ENAME) AS WAY2 FROM EMP;
截取字符串substr。语法：substr( ‘被截取的字符串’ , 起始下标 , 截取长度)，当第三个参数“截取长度”缺失时，截取到字符串末尾。
1
2
3
-- 找出员工名字中第二个字母是A的
SELECT ENAME FROM EMP WHERE 'A' = SUBSTR(ENAME, 2 , 1);
SELECT ENAME FROM EMP WHERE ENAME LIKE '_A%';
length、char_length。在SQL中，LENGTH 返回字符串的字节长度，适用于需要知道字符串占用多少字节空间的情况；而 CHAR_LENGTH 返回字符串的字符长度，计算实际的字符数量，适用于需要知道字符串包含多少个字符的情况。
1
2
--  获取字符串长度length，获取字符的个数char_length。
SELECT LENGTH("你好MySQL") , CHAR_LENGTH("你好MySQL");
字符串拼接concat ( ‘字符串1’ , ‘字符串2’ , ‘字符串3’ …. )
1
SELECT CONCAT('Hello World ','Hello MySQL');
trim，去除字符串前后空白。
1
SELECT TRIM("   Hello World  ");
去除指定的前缀后缀
1
2
3
4
5
-- 去除前置0&去除后置0&前置0和后置0全部去除
SELECT
	TRIM( LEADING '0' FROM '0001110000' ),
	TRIM( TRAILING '0' FROM '0001110000' ),
	TRIM( BOTH '0' FROM '0001110000' );