mysql基础知识

一、数据类型

1、数字

类型	大小	说明
TINYINT	1字节	小整数(8个二进制位，负的128到正的127)
SMALLINT	2字节	普通整数(16个二进制位，负的2的16次幂到正的2的16次幂)
MEDIUMINT	3字节	普通整数
INT	4字节	较大整数(最常用)
BIGINT	8字节	大整数
FLOAT	4字节	单精度浮点数
DOUBLE	8字节	双精度浮点整数
DECIMAL	------	DECIMAL(10,2)，（不会丢失精度，涉及到钱等对精度要求高或者很重要的数据时用）

• 为什么浮点数不精确，因为十进制的浮点数转成二进制的浮点数会丢失精度
• decimal精确是因为不管保存整数还是浮点数，都是采用字符串的方式来保存的。 DECIMAL(10,2)其中的10表示整数位和小数位加起来一共10个字符，2表示精确到小数点后两位

2、字符串

类型	大小	说明
CHAR	1-255字符	固定长度字符串
VARCHAR	1-65535字符	不固定长度字符串
TEXT	1-65535字符	不确定长度字符串
MEDIUMTEXT	1-1千6百万字符	不确定长度字符串
LONGTEXT	1-42亿字符	不确定长度字符串

3、日期类型

类型	大小	说明
DATE	3字节	日期
TIME	3字节	时间
YEAR	1字节	年份
DATETIME	8字节	日期时间
TIMESTAMP	4字节	时间戳

二、数据库表的相关操作

逻辑空间对应的是文件夹，数据表对应的是文件夹中的.ibd文件

1、定义逻辑空间

# 展示所有的逻辑空间
SHOW DATABASES;
# 创建逻辑空间
CREATE DATABASE demo;
# 删除逻辑空间
DROP DATABASE demo;
# 使用（或者说切换到） demo 逻辑空间
USE demo;

2、定义数据表

# 创建 student 数据表
CREATE TABLE student (
    id INT UNSIGNED PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex CHAR(1) NOT NULL,
    birthday DATE NOT NULL,
    tel CHAR(11) NOT NULL,
    remark VARCHAR(200)
);

# 在数据表 student 中插入数据
INSERT INTO student VALUES(1, "李强", "男", "1990-05-06", "13355556666", NULL);

# 查看当前逻辑空间下所有数据表名
SHOW TABLES;
# 查看数据表 student 的表结构
DESC student;
# 查看数据表 student 创建时候的sql语句
SHOW CREATE TABLE student;
# 删除数据表 student
DROP TABLE student;

3、修改数据表结构

# 在数据表中添加新字段
ALTER TABLE student
ADD address VARCHAR(200) NOT NULL,
ADD home_tel CHAR(11) NOT NULL;

# 修改数据表中已经存在的字段的数据类型和约束
ALTER TABLE student
MODIFY home_tel VARCHAR(20) NOT NULL;

# 修改数据表中的字段名
ALTER TABLE student 
CHANGE address home_address VARCHAR(200) NOT NULL;

# 删除数据表中已经存在的字段
ALTER TABLE student
DROP home_address,
DROP home_tel;

4、字段约束

• MySQL中的字段约束共有四种

约束名称	关键字	描述
主键约束	PRIMARY KEY	字段值唯一，且不能为NULL
非空约束	NOT NULL	字段值不能为NULL
唯一约束	UNIQUE	字段值唯一，且可以为NULL
外键约束	FOREIGN KEY	保持关联数据的逻辑性

# 创建部门表
CREATE TABLE t_dept(
    deptno INT UNSIGNED PRIMARY KEY,
    deptname VARCHAR(10) NOT NULL UNIQUE,
    tel CHAR(11) UNIQUE
);

# 创建员工表，设置外键关联部门表中的 deptno
CREATE TABLE t_emp (
    empno INT UNSIGNED PRIMARY KEY,
    empname VARCHAR(20) NOT NULL,
    sex ENUM("男", "女") NOT NULL,
    deptno INT UNSIGNED NOT NULL,
    hiredate DATE NOT NULL,
    FOREIGN KEY (deptno) REFERENCES t_dept(deptno)
);

5、数据表索引

• 数据量大，而且经常被查询的数据表可以设置索引
• 索引只添加在经常被用作检索条件的字段的上面
• 不要在大字段上创建索引（字符长度超过50的字符串）

# 创建数据表 t_message 并给 type字段 添加索引 idx_type
CREATE TABLE t_message (
    id INT UNSIGNED PRIMARY KEY,
    content VARCHAR(200) NOT NULL,
    type ENUM("公告", "个人通知", "通报") NOT NULL,
    create_time TIMESTAMP NOT NULL,
    INDEX idx_type(type)
);

# 展示数据表 t_message 中的索引
SHOW INDEX FROM t_message;
# 删除数据表中的索引
DROP INDEX idx_type ON t_message;
# 给数据表中的字段 type 添加索引（下面是两种方法）
CREATE INDEX idx_type ON t_message(type);
ALTER TABLE t_message ADD INDEX idx_type(type);

三、数据库表的基本查询

1、普通查询

# 查询数据表中所有字段信息
SELECT * FROM t_emp;
# 查询数据表中 empno, ename, sal 三个字段的信息
SELECT empno, ename, sal FROM t_emp;
# 使用列别名
# 通常情况下， SELECT子句中使用了表达式，那么这列的名字就默认为表达式，因此可以通过 AS 来对结果集中列名进行重命名
SELECT
    empno,
    sal * 12 AS "income"
FROM t_emp;

2、数据分页

# 使用LIMIT关键字进行数据分页，
SELECT ... FROM ... LIMIT 起始位置, 偏移量;
# 例子
SELECT empno, ename FROM t_emp LIMIT 0, 5;

3、排序

# 在数据表t_emp中按字段 sal 进行升序排序，默认就是升序（ASC写不写都可）
SELECT empno,ename,sal,deptno 
FROM t_emp
ORDER BY sal ASC;

# 在数据表t_emp中按字段 sal 进行降序排序
SELECT empno,ename,sal,deptno 
FROM t_emp
ORDER BY sal DESC;

# 单个字段排序，如果有两条数据，排序字段内容相等，那么会根据这两条数据的主键升序进行排序
# 可以设置多个排序字段，第一个字段升序排列，第二个字段降序排列
SELECT deptno,ename,sal
FROM t_emp
ORDER BY deptno ASC, sal DESC;

# ORDER BY 和 LIMIT 混合使用，可以实现排序加分页的效果
# 获取员工工资排在前五位的员工数据
SELECT empno,ename,sal
FROM t_emp
ORDER BY sal DESC LIMIT 0, 5;

4、去除重复记录

• 使用 DISTINCT 的 SELECT 子句中只能查询一列数据，如果查询多列，去除重复记录就会失效
• DISTINCT 关键字只能在 SELECT 子句中使用一次

# 获取员工表中所有职位
SELECT DISTINCT job FROM t_emp;

5、条件查询

比较运算符

序号	表达式	意义	例子
1	>	大于	age > 18
2	>=	大于等于	age >= 18
3	<	小于	age < 18
4	<=	小于等于	age <= 18
5	=	等于	age = 18
6	!=	不等于	age != 18
7	IN	包含	age IN(10,20,30)
8	IS NULL	为空	dept IS NULL
9	IS NOT NULL	不为空	dept IS NOT NULL
10	BETWEEN AND	范围	sal BETWEEN 2000 AND 3000
11	LIKE	模糊查询	ename LIKE "A%"
12	REGEXP	正则表达式	ename REGEXP "[a-zA-Z]{4}"

1、LIKE "A%"中的%表示0到多个字符的意思，哪怕没有字符也会匹配
2、LIKE "A%" 表示以字符A开头；LIKE "%A" 表示以字符A结尾；LIKE "%A%" 表示含有A这个字符，开头结尾还是在中间都可以匹配，

/*
   NULL 和其它数字做加减乘除运算，结果都为 NULL，为了做计算的时候规避这种情况
   引入IFNULL(expr1,expr2)，expr1为要参与计算的参数，exp2为当expr1为NULL时候用什么值来参与运算
*/
/*
   函数DATEDIFF(expr1,expr2)，可计算两个时间之间的差值，expr1 减去 expr2，得到的结果为天数
   函数NOW()，可以获取当前时间
*/

# 获取员工表中部门编号为10，并且年收入超过15000，并且工龄超过20年的员工信息
SELECT empno,ename,sal,hiredate
FROM t_emp
WHERE deptno=10 AND ((sal+IFNULL(comm,0))*12 )>=15000
AND DATEDIFF(NOW(),hiredate)/365>=20;

# 获取部门编号在10，20，30之间，并且入职日期在1985年之前，并且job不是SALESMAN的员工信息
SELECT empno,ename,deptno,hiredate,job
FROM t_emp
WHERE deptno IN(10,20,30) AND hiredate<"1985-01-01" AND job!="SALESMAN";

逻辑运算符

序号	表达式	意义	例子
1	AND	与关系	age > 18 AND sex = "男"
2	OR	或关系	age > 18 OR sex="男"
3	NOT	非关系	NOT deptno = 20
4	XOR	异或关系	age > 18 XOR sex="男"

SELECT ename,deptno,sal
FROM t_emp
WHERE NOT deptno IN(10, 20) XOR sal>=5000;

1、where子句中，条件执行的顺序是从左到右的。所以我们应该把索引条件，或者筛选掉记录最多的条件写在最左侧，这样会提高查询速度
2、条件查询中各种子句执行顺序是： FROM > WHERE > GROUP BY > SELECT > ORDER BY > LIMIT

四、数据库表的高级查询

1、聚合函数

序号	函数名	说明
1	SUM	求和，只能用于数字类型，日期类型统计结果是毫秒数相加，字符类型为0
2	MAX	获得非空值的最大值
3	MIN	获得非空值的最小值
4	AVG	获得非空值的平均值，非数字数据统计结果为0
5	COUNT	COUNT(*)用于获得包含空值的记录数，COUNT(列名)用于获得包含非空值的记录数

2、分组查询

默认情况下汇总函数是对全表范围内的数据做统计。GROUP BY子句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理。

# 查询每个部门里，每种职位的人员数量和平均底薪，并按部门排序
SELECT deptno,job,COUNT(*),AVG(sal + IFNULL(comm,0))
FROM t_emp
GROUP BY deptno, job 
ORDER BY deptno;

# GROUP_CONCAT()函数可以把分组查询中的某个字段拼接成一个字符串
# 查询每个部门内底薪超过2000元的人数和员工姓名
SELECT deptno,GROUP_CONCAT(ename),COUNT(*)
FROM t_emp
WHERE sal>=2000
GROUP BY deptno;

1、查询语句中如果含有GROUP BY子句，那么SELECT子句中的内容就必须要遵守规定：SELECT子句中可以包括聚合函数，或者GROUP BY子句的分组列，其余内容均不可以出现在SELECT子句中
2、GROUP_CONCAT函数可以把分组查询中的某个字段拼接成一个字符串，中间用逗号分隔

# sal不应该出现在SELECT子句中，以下语句执行会直接报错
SELECT deptno, COUNT(*), AVG(sal), sal
FROM t_emp GROUP BY deptno;

# 直接写ename会报语法错误，使用GROUP_CONCAT可以避免语法错误，上面的sal也一样
SELECT deptno, COUNT(*), GROUP_CONCAT(ename)
FROM t_emp
WHERE sal>=2000
GROUP BY deptno;

WITH ROLLUP对分组结果集再次做汇总计算

# 对分组结果集再次做汇总计算
SELECT deptno, COUNT(*), AVG(sal), SUM(sal), MAX(sal), MIN(sal)
FROM t_emp
GROUP BY deptno WITH ROLLUP;

聚合函数作为查询条件，需要引入 HAVING 子句， HAVING 子句不能独立存在，必须依赖于 GROUP BY 子句

# 查询每个部门中，1982年以后入职的员工超过2个人的部门编号
SELECT deptno
FROM t_emp
WHERE hiredate>"1982-01-01"
GROUP BY deptno HAVING COUNT(*)>=2
ORDER BY deptno ASC;

3、表连接查询

表连接分为两种：内连接和外连接
内连接是结果集中只保留符合条件的记录
外连接是不管符不符合连接条件，记录都要保留在结果集中

1.内连接

内连接的多种语法形式

# JOIN 表连接关键字，ON 表连接条件
# 查询每名员工的部门信息，下面三种写法一样
SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;

SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d WHERE e.deptno=d.deptno; 

SELECT e.empno,e.ename,d.dname
FROM t_emp e,t_dept d WHERE e.deptno=d.deptno;

内连接的数据表不一定必须有同名字段，只要字段之间符合逻辑关系就可以

# 查询每个员工的编号，姓名，部门名称，底薪，职位，工资等级
SELECT e.empno,e.ename,d.dname,sal,job,s.grade
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno 
JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

同一张数据表也可以做表连接

# 查询与SCOTT相同部门的员工都有谁
#（1）符合逻辑习惯，但执行效率低
SELECT ename
FROM t_emp
WHERE deptno=(SELECT deptno FROM t_emp WHERE ename="SCOTT") AND ename!="SCOTT";
#（2）利用表连接，执行效率远高于（1）
# 相同数据表也可以做表连接
SELECT e2.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno
WHERE e1.ename="SCOTT" AND e2.ename!="SCOTT";

查询结果集也可以当做一张表和其它表进行连接

# 查询底薪超过公司平均底薪的员工信息
# (1) 这种方法存在语法错误，ON相当于WHERE筛选条件，不可用没有明确值的聚合函数
SELECT e1.empno,e1.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.sal>AVG(e2.sal);
#（2）将查询结果集也当做一张表和其它表进行连接
SELECT e.ename,e.sal
FROM t_emp e JOIN (SELECT AVG(sal) AS avg FROM t_emp) t ON e.sal>t.avg;

# 查询 RESEARCH 部门的人数、最高底薪、最低底薪、平均底薪、平均工龄
SELECT COUNT(*),MAX(e.sal),MIN(e.sal),AVG(e.sal),AVG(DATEDIFF(NOW(),e.hiredate))
FROM t_emp e JOIN t_dept d 
WHERE d.dname="RESEARCH";

#查询每种职业的最高工资、最低工资、平均工资、最高工资等级和最低工资等级
SELECT 
e.job,
MAX(e.sal+IFNULL(e.comm,0)),
MIN(e.sal+IFNULL(e.comm,0)),
AVG(e.sal+IFNULL(e.comm,0)),
MAX(s.grade),
MIN(s.grade)
FROM t_emp e JOIN t_salgrade s 
ON (e.sal+IFNULL(e.comm,0)) BETWEEN s.losal AND s.hisal
GROUP BY e.job;

# 查询每个底薪超过部门平均底薪的员工信息
SELECT e1.deptno,e1.empno,e1.ename,e1.sal,e1.comm
FROM t_emp e1 JOIN (SELECT deptno, AVG(sal) AS avg FROM t_emp GROUP BY deptno) e2 
ON e1.deptno=e2.deptno AND e1.sal>e2.avg;

2.外连接

1、外连接和内连接的区别在于，除了符合条件的记录之外，结果集中还会保留不符合条件的记录。
2、左外连接就是保留左表的所有记录，与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录，就用NULL与左表连接。右外连接也是如此。

SELECT e.ename, e.deptno
FROM t_emp e
LEFT JOIN t_dept d 
ON e.deptno=d.deptno;

UNION关键字可以将多个查询语句的结果集进行合并

# 查询每个部门的名称和部门的人数
SELECT d.dname, COUNT(e.deptno)
FROM t_emp e RIGHT JOIN t_dept d ON e.deptno=d.deptno
GROUP BY d.deptno;

# 查询每个部门的名称和部门的人数，如果没有部门的员工，部门名称用NULL代替
(
SELECT d.dname, COUNT(e.deptno)
FROM t_emp e RIGHT JOIN t_dept d ON e.deptno=d.deptno
GROUP BY d.deptno
) 
UNION
(
SELECT d.dname, COUNT(*)
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
GROUP BY d.deptno
);

# 查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司姓名、上司部门
SELECT e.empno, e.ename, d.dname, 
e.sal+IFNULL(e.comm,0), 
s.grade, FLOOR(DATEDIFF(NOW(),e.hiredate)/365),
t.empno as mempno, t.ename as mename, t.dname as mdname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
LEFT JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN
(SELECT e1.empno, e1.ename, d1.dname
FROM t_emp e1 JOIN t_dept d1 ON e1.deptno=d1.deptno) t
ON e.mgr=t.empno;

内连接只保留符合条件的记录，所以查询条件写在ON 子句和WHERE子句中的效果是相同的。但是外连接里，条件写在WHERE子句里，不符合条件的记录是会被过滤掉的，而不是保留下来。

# ON 不过滤
SELECT e.empno, e.ename, d.dname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno = d.deptno
AND e.deptno = 10;

# WHERE 过滤
SELECT e.empno, e.ename, d.dname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno = d.deptno
WHERE e.deptno = 10;

五、MySQL对数据的基本操作

1、INSERT语句

1、插入一条数据
INSERT INTO 表名(字段1, 字段2, ...) VALUES(值1, 值2, ...);
2、插入两条数据，多条就继续添加
INSERT INTO 表名(字段1, 字段2, ...) VALUES(值1, 值2, ...), (值1, 值2, ...);

注：表名后面字段声明不写也可以执行，但是执行速度会受影响，因为MySQL在执行sql语句的时候会做词法分析，如果发现表名后面没有写字段声明，会去查询表结构，然后自动补上字段声明，这样的话，MySQL就不能立即执行，需要做额外的工作，然后才会去执行sql语句，往数据库写入数据。
所以，请务必在表名后写字段声明

# 向技术部添加一条员工记录
INSERT INTO t_emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) 
VALUES(8001, "lily", "SALESMAN", 8000, "1990-01-02", 2000, NULL, (SELECT deptno FROM t_dept WHERE dname="技术部"));

IGNORE关键字会让INSERT只插入数据库不存在冲突的记录
INSERT IGNORE INTO 表名....;

2、UPDATE语句

UPDATE 语句用于修改表的记录，[]中可写可不写
UPDATE [IGNORE] 表名
SET 字段1=值1, 字段2=值2, ...
[WHERE 条件1...]
[ORDER BY ...]
[LIMIT ...]

# 把每个员工的编号和上司的编号+1，用 ORDER BY 子句完成
UPDATE t_emp SET empno=empno+1, mgr=mgr+1 ORDER BY empno DESC;

# 把月收入前三名的员工底薪减100元，用 LIMIT 子句完成
UPDATE t_emp SET sal=sal-100 ORDER BY sal DESC LIMIT 3;

# 把10部门中，工龄超过20年的员工，底薪增加200
UPDATE t_emp SET sal=sal+200 WHERE DATEDIFF(NOW(),hiredate)/365>20 AND deptno=10;

因为相关子查询的效率非常低，所以我们可以利用表连接的方式来改造UPDATE语句
UPDATE 表1 JOIN 表2 ON 条件 SET 字段1=值1, 字段2=值2, ...
UPDATE 表1, 表2 SET 字段1=值1, 字段2=值2, ... WHERE 条件

# 把ALLEN调往RESEARCH部门，职务调整为ANALYST

# 方法1：利用子查询，这种在SET中的子查询是相关子查询，改变一条数据还可以使用，但是同时改变多条数据的时候，效率会很低
UPDATE t_emp SET job="ANALYST", deptno=(SELECT deptno FROM t_dept WHERE dname="RESEARCH") WHERE ename="ALLEN"; 

# 方法2：利用表连接，JOIN可以写成, 推荐
UPDATE t_emp e JOIN t_dept d SET e.job="ANALYST", e.deptno=d.deptno WHERE e.ename="ALLEN" AND d.dname="RESEARCH";
UPDATE t_emp e, t_dept d SET e.job="ANALYST", e.deptno=d.deptno WHERE e.ename="ALLEN" AND d.dname="RESEARCH";

# 把底薪低于公司平均底薪的员工，底薪增加150元，下面两种写法一样
UPDATE t_emp e JOIN (SELECT AVG(sal) AS avg FROM t_emp) t SET e.sal=e.sal+150 WHERE e.sal<t.avg;
UPDATE t_emp e JOIN (SELECT AVG(sal) AS avg FROM t_emp) t ON e.sal<t.avg SET e.sal=e.sal+150;

UPDATE语句的表连接可以是内连接，又可以是外连接
UPDATE 表1 [LEFT | RIGHT] JOIN 表2 ON 条件 SET 字段1=值1, 字段2=值2, ...

# 把没有部门的员工，或者SALES部门低于2000元底薪的员工，都调往20部门
UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
SET e.deptno=20 
WHERE e.deptno IS NULL OR (d.dname="SALES" AND e.sal<2000);

3、DELETE语句

DELETE 语句用于删除记录，[]中可写可不写
DELETE [IGNORE] FROM 表名
[WHERE 条件, 条件2, ...]
[ORDER BY ...]
[LIMIT ...]

# 删除10部门中，工龄超过20年的员工记录
DELETE FROM t_emp WHERE deptno=10 AND DATEDIFF(NOW(),hiredate)/365>20; 

# 删除20部门中工资最高的员工记录
DELETE FROM t_emp WHERE deptno=20 ORDER BY sal+IFNULL(comm,0) DESC LIMIT 1;

因为相关子查询效率非常低，所以我们可以用表连接的方式来改造DELETE语句
DELETE 表1, ... FROM 表1 JOIN 表2 ON 条件
WHERE 条件1, 条件2, ...
ORDER BY ...
LIMIT ...

# 删除SALES部门和该部门的全部员工记录
DELETE e, d
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="SALES";

# 删除每个低于部门平均底薪的员工记录
DELETE e
FROM t_emp e JOIN (SELECT deptno, AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal<t.avg;

# 删除员工KING和他的直接下属的员工记录，用表连接实现
DELETE e
FROM t_emp e JOIN
(SELECT empno FROM t_emp WHERE ename="KING") t
ON e.mgr=t.empno OR e.empno=t.empno;

DELETE语句的表连接既可以是内连接，又可以是外连接
DELETE 表1, ... FROM 表1 [LEFT | RIGHT] JOIN 表2 ON 条件...

# 删除SALES部门的员工，以及没有部门的员工，下面两种方法都可，推荐第二种
DELETE e
FROM t_emp e JOIN (SELECT deptno FROM t_dept WHERE dname="SALES") t
ON e.deptno=t.deptno OR e.deptno IS NULL;

DELETE e
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="SALES" OR e.deptno IS NULL;

快速删除数据表全部记录
1、DELETE语句是在事务机制下删除记录，删除记录之前，先把将要删除的记录保存到日志文件里，然后再删除记录
2、TRUNCATE语句在事务机制之外删除记录，速度远超过DELETE语句
3、语法如下：
TRUNCATE TABLE 表名;

# 删除员工表中的所有数据
TRUNCATE TABLE t_emp;

六、MySQL基本函数的使用

1、数字函数

函数	功能	用例
ABS	绝对值	ABS(-100)
ROUND	四舍五入	ROUND(4.62)
FLOOR	强制舍位到最近的整数	FLOOR(9.9)
CEIL	强制进位到最近的整数	CEIL(3.2)
POWER	幂函数	POWER(2,3)
LOG	对数函数	LOG(7,3)
LN	对数函数	LN(10)

ROUND()：四舍五入
CEIL()：进位
FLOOR()：舍位

# 四舍五入
SELECT ROUND(1.4); // 1
SELECT ROUND(1.5); // 2

# 进位
SELECT CEIL(1.1); // 2

# 舍位
SELECT FLOOR(1.9); // 1

函数	功能	用例
SQRT	开平方	SQRT(9)
PI	圆周率	PI()
SIN	三角函数	SIN(1)
COS	三角函数	COS(1)
TAN	三角函数	TAN(1)
COT	三角函数	COT(1)
PADIANS	角度转换弧度	PADIANS(30)
DEGREES	弧度转换角度	DEGREES(1)

2、字符函数

函数	功能	用例
LOWER	转换小写字符	LOWER(ename)
UPPER	转换大写字符	UPPER(ename)
LENGTH	字符数量	LENGTH(ename)
CONCAT	连接字符串	CONCAT(sal, "$")
INSTR	字符出现的位置	INSTR(ename, "A")
INSERT	插入/替换字符	INSERT("你好", 1, 0, "先生")
REPLACE	替换字符	REPLACE("你好先生", "先生", "女士")

函数	功能	用例
SUBSTR	截取字符串	SUBSTR("你好世界", 3, 4)
SUBSTRING	截取字符串	SUBSTRING("你好世界", 3, 2)
LPAD	左侧填充字符	LPAD("Hello", 10, "*")
RPAD	右侧填充字符	RPAD("Hello", 10, "*")
TRIM	去除首尾空格	TRIM(" 你好世界 ")

3、日期函数

函数	功能	格式
NOW()	获得系统日期和时间	yyyy-MM-dd hh:mm:ss
CURDATE()	获取当前系统日期	yyyy-MM-dd
CURTIME()	获取当前系统时间	hh:mm:ss

DATE_FORMAT()函数用于格式化日期，返回用户想要的日期格式
DATE_FORMAT(date,format)
date：日期，format：表达式

# 获取员工姓名和入职年份
SELECT ename, DATE_FORMAT(hiredate,"%Y") FROM t_emp;

占位符	作用	占位符	作用
%Y	年份	%m	月份
%d	日期	%w	星期（数字）
%W	星期（名称）	%j	本年第几天
%U	本年第几周	%H	小时（24）
%h	小时（12）	%i	分钟
%s	秒	%r	时间（12）
%T	时间（24）

# 利用日期函数，查询明年你的生日是星期几
SELECT DATE_FORMAT("2023-11-13","%w"); // 0代表周天
SELECT DATE_FORMAT("2023-11-13","%W");

# 利用日期函数，查询1981年上半年入职的员工有多少人
SELECT ename, hiredate FROM t_emp 
WHERE (DATE_FORMAT(hiredate,"%Y") = 1981) AND DATE_FORMAT(hiredate,"%m") < 7;

MySQL数据库里面，两个日期不能直接加减，日期也不能与数字加减
DATE_ADD()函数可以实现日期的偏移计算，而且时间单位很灵活
DATE_ADD(日期,INTERVAL 偏移量 时间单位)

# 15天之后的时间
SELECT DATE_ADD(NOW(),INTERVAL 15 DAY);

# 300分钟之前的时间
SELECT DATE_ADD(NOW(),INTERVAL -300 MINUTE);

# 6个月零3天之前的时间
SELECT DATE_ADD(DATE_ADD(NOW(),INTERVAL -6 M  ONTH),INTERVAL -3 DAY);

计算日期之间相隔的天数
DATEDIFF()函数用来计算两个日期之间相差的天数
DATEDIFF(日期,日期)

4、条件函数

SQL语句中可以利用条件函数来实现编程语言里的条件判断
IFNULL(表达式, 值)
IF(表达式, 值1, 值2)

# 中秋节公司发放礼品，SALES部门发放礼品A，其余部门发放礼品B，打印每名员工获得的礼品
SELECT e.ename, d.deptno, d.dname, IF(d.dname="SALES","A","B")
FROM t_emp e JOIN t_dept d
WHERE e.deptno=d.deptno;

复杂的条件判断可以用条件语句来实现，比IF语句更强大
CASE
WHERE 表达式 THEN 值1
WHERE 表达式 THEN 值2
......
ELSE 值N
END

# 公司年庆决定组织员工集体旅游，每个部门旅游目的地是不同的。SALES部门去P1地点，ACCOUNTING部门去P2地点，RESEARCH部门去P3地点，查询每名员工的旅行地点。
SELECT
    e.ename,
    CASE 
    WHEN d.dname="SALES" THEN "P1"
    WHEN d.dname="ACCOUNTING" THEN "P2"
    WHEN d.dname="RESEARCH" THEN "P3"
    END AS PLACE
FROM t_emp e JOIN t_dept d
WHERE e.deptno=d.deptno;

# 公司决定为员工调整基本工资，具体调整方案如下
# 1、SALES部门中工龄超过20年，涨幅10%
# 2、SALES部门中工龄不满20年，涨幅5%
# 3、ACCOUNTING部门， +300元
# 4、RESEARCH部门里低于部门平均底薪， +200元
# 5、没有部门的员工， +100元
UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno 
LEFT JOIN (SELECT deptno, AVG(sal) AS avg FROM t_emp GROUP BY deptno) t ON e.deptno=t.deptno
SET e.sal=(
    CASE
    WHEN d.dname="SALES" AND DATEDIFF(NOW(),e.hiredate)/365>20 THEN e.sal*1.1
    WHEN d.dname="SALES" AND DATEDIFF(NOW(),e.hiredate)<20 THEN e.sal*1.05
    WHEN d.dname="ACCOUNTING" THEN e.sal+300
    WHEN d.dname="RESEARCH" AND e.sal<t.avg THEN e.sal+200
    WHEN d.dname IS NULL THEN e.sal+100
    ELSE e.sal
    END
);