MYSQL基础篇（1）｜ 青训营笔记

这是我参与「第四届青训营 」笔记创作活动的的第14天

1.2 常见函数

1.2.1 字符函数

LOWER('MYSQL') ##mysql
UPPER('mysql') ##MYSQL

CONCATC('MY','SOL') ##MYSQL                  ##此处CONCAT这个函数可以理解为拼接字符串的作用 
SUBSTR('Good Morning',1,4)                    ##Good ##此处的“1.4”分别代表“截取”这一动作的头部和尾部;注意,空格也会被计算在内
LENGTH('Birthday') ##8                           ##此处的LENGTH函数即是统计字符串长度;注意,空格也会被计算在内 
INSTR('china',i) ##3                                ##此外的TNSTR多数可以理解为“选中”，根据给出的字符出后面的字符来确定是在该字符出的第几个位置
LPAD('MORNING',3,'*') ##***NING            ##此外的IPAD函数可以理解为月最后一个字符去“遮盖”相对应的字符位数(从第一个字符开始计数)，具体数目见字符串后面一个数字，其余数字保持不变，“拼接"起来
RPAD('MORNING',3,'*') ##MORN***           ##此处的RPAD函数可以理解为用最后一个字符去“遮盖”相对应的字符位数(从最后一个字符开始计数)，具体数字见字符串后面的一个数字，其余数字保持不变，“拼接"起来
TRIM('G' FROM 'GoodMorning') ##oodmorning   ##此处的TRIM函数可以理解为用第一个字符串去“抹除”该字符串在后面一串函数中的位置，其余字符串保持不变，“拼接"起来
REPLACE('Hello', 'e' 'o') ##Hollo                 ##顾名思义，此处的REPLACE函数可以理解为用最后一个字符串去替换原文中相对应的字符串的位置，其余位置保持不变，“拼接"起来

1.2.1 数字函数

ROUND:四舍五入
ROUND(45.926,2)  ##45.93    ##此处需要考虑小数点四舍五入的问题
TRUNCATE:截断
TRUNC(45.993,1)  ##45.9    ##此处起到截断作用，不需要考虑四舍五入问题，根据函数TRUNC第二个字符串来截取相对应小数点的位数
MOD:求余
MOD(1600,300)    ##100     ##此处的MOD函数理解如下，MOD(x,y)即可用x % y,得到的数字即为答案

1.2.2 日期函数

NOW:获取当前日期
str_to_date:将日期格式的字符转换成指定格式的日期
STR_TO_DATE('9-30-2002','%m-%d-%Y')   ##2002-09-30     ##此处是格式转换函数，注意年份的Y是大写字母，其余均为小写，同时要注意月份和日都是两位数，不足自动补齐
date_format:将日期转换成字符
DATE_FORMAT('2002/9/30','%Y年%m月%d日')    ##2002年09月30日    ##此处是格式转换函数，注意年份的Y是大写字母，其余均为小写，同时要注意月份和日都是两位数，不足自动补齐

序号	格式符	功能
1	%Y	四位的年份
2	%y	二位的年份
3	%m	月份（双数字）
4	%c	月份（单数字）
5	%d	日（双数字）
6	%H	小时（24小时制）
7	%h	小时（12小时制）
8	%i	分钟（双数字）
9	%s	秒（双数字）

1.2.3 流程控制函数

（1)在SQL语句中使用IF-THEN-ELSE逻辑

CASE expr WHEN comparison_expr1 THEN return_expr1
[
    WHEN comparison_expr2 THEN return_expr2
    WHEN comparison_exprn THEN return_exprn
    ELSE else_expr
]
END

1.3 数据处理之增删改

1.3.1 INSERT

（1）使用INSERT语句向表中插入数据

INSERT INTO table [(column[,column...])]
VALUES (value[,value...])

使用这种语法一次只能向表中插入一条数据

1.3.2 插入数据

（1）为每一列添加一个新值

（2）按列的默认顺序列出每个列的值

（3）在INSERT子句中随意列出列名和它们的值

（4）字符和日期型数据应包含在单引号中

INSERT INTO departments(
department_id , department_name , manager_id , location_id)
VALUES(70,'Public Relations',70,1700);
)

上方代码详解

该SQL语句的即向departments这个表中插入department_id , department_name , manager_id , location_id这四个字段所对应的数值，即70,'Public Relations',70,1700

注意：使用INSERT语句插入相关数据时要确保字段位置。不可随意更改，否则可能会报Error

INSERT INTO employees（employee_id , last_name , email , hire_date , job_id）
VALUES(
300 , 'Tom' , 'tom@126.com' , to_date('2012-3-21','yyyy-mm-dd'),'SA_RAP');
)

上方代码详解

该SQL语句即向employees这个表插入employee_id , last_name , email , hire_date , job_id这五个字段所对应的数值，即300 , 'Tom' , 'tom@126.com'，2012-3-21，SA_RAP

注意：使用INSERT语句插入相关数据时要确保字段位置。不可随意更改，否则可能会报Error

1.3.3 向表中插入空值

（1）隐性方式：在列名表中省略该列的值

INSERT INTO departments(
department_id , department_name   )
VALUES(30,'Purchasing')
);

（2）显示方式：在VALUES子句中指定空值

INSERT INTO departments
VALUES(100,'Finance',NULL,NULL);

1.3.4 插入指定的值

NOW（）函数：记录当前系统的日期和时间

INSERT INTO employees(
employee_id , first_name , last_name , email , phone_number , hire_date , job_id , salary , commission_pct , manager_id , department_id
)
VALUES(
113,'Louis','Popp','LPOPP','515.124.4567',NOW(),'AC_ACCOUNT' , 6900 , NULL , 205 , 100
);

该SQL语句即向employees这个表插入employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id这些字段所对应的数值，即113,'Louis','Popp','LPOPP','515.12' 4.4567'，NOW()，'AC_ACCOUNT' ，6900 ，NULL ，205，100

注意：使用INSERT语句插入相关数据时要确保字段位置。不可随意更改，否则可能会报Error

1.3.5 从其它表中拷贝数据

在 INSERT 语句中加入子查询

ISNERT INTO emp2
SELECT * FROM employees
WHERE department id = 90 ;

INSERT INTO sales_reps(id , name , salary , commission_pct)
SELECT employee_id , last_name , salary , commission_pct
FROM employees
WHERE job_id LIKE '%REP%'

（１）不必书写VALUES子句

（２）子查询中的值列表应与INSERT子句中的列名对应

（３）上述语句不必了解，后续会讲

1.3.6 更新数据

1.3.6.1 UPDATE 语句语法

使用 UPDATE 语句更新数据

UPDATE table
SET column = value [, column = value , ...]
[WHERE condition]

（１）可以一次更新多条数据

（２）如果需要回滚数据,需要保证在DML之前，进行设置 SET AUTOCOMMIT = FALSE ;

1.3.6.2 更新数据

（１）使用WHERE子句指定需要更新的数据

UPDATE employees 
SET department_id = 70
WHERE employee_id = 113 ;

（２）如果省略WHERE子句,则表中的所有数据都将被更新

UPDATE copy_emp
SET department_id = 110 ;

1.3.6.3 更新数据的完整性错误

UPDATE employees
SET department_id = 55 
WHERE department_id = 110 ;

错误原因：不存在55号部门

1.3.7 删除数据

1.3.7.1 DELETE 语句

使用DELETE语句从表中删除数据

DELETE FROM table
[WHERE     condition];

1.3.7.2 删除数据

（１）使用WHERE子句删除指定的记录

DELETE FROM departments
WHERE department_name = 'Finance'

如果省略 WHERE 子句，则表中的全部数据将会被清除

DELETE FROM copy_emp;

1.3.7.3 删除中的数据完整性错误

DELETE FROM departments
WHERE department_id = 60 ;

存在primary key问题

1.4 数据处理之查询

SELECT *|{[DISTINCT]}  column | expression [alias], ...}
FROM table;

SELECT 表示选择哪些列

FROM 标识从哪个表中选择

1.4.1 选择全部列

SELECT * 
FROM departments;

此处的*即代表 "选择全部列"

1.4.2 选择特定的列

SELECT department_id , location_id
FROM departments;

department_id , location_id这两列都是指定的列，故上方代码查询的是指定这两列的内容

1.4.3 注意事项

（１）SQL语言大小写不敏感

（２）SQL可以写在一行或者多行

（３）关键字不能被缩写也不能被分行

（４）各子句一般要分行写

（５）使用缩进提高语句的可读性