MYSQL数据库

MySQL的基本操作

主要目标：数据添加：INSERT语法、INSERT方言语法、INSERT子查询、IGNORE关键字；数据修改：UPDATE语法、UPDATE表连接；数据删除：DELETE语法、DELETE表连接
1、INSERT语句：可以向数据表写入记录，可以是一条记录，也可以是多条记录INSERT INTO 表名 （字段1，字段2，......) VALUES (值1，值2，.......);

INSERT INTO 表名 （字段1，字段2，......)
VALUES (值1，值2，.......),(值1，值2，....);

INSERT语句方言（MYSQL）INSERT INTO 表名 SET 字段1=值1，字段2=值2，.......;

（1）IGNORE关键字会让INSERT只插入数据库不存在的数据INSERT [IGNORE] INTO 表名......;

INSERT IGNORE INTO t_dept(deptno, dname, loc)
VALUES(40,"技术部", "北京")

（2）INSERT语句中可以包含子查询语句，代表把子查询的内容写入到数据表
示例：

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


# INSERT 方言
INSERT t_emp
SET empno=8002,ename="JACK",job="SALESMAN",mgr=8000,
hiredate="1995-2-12",sal=2400,comm=NULL,deptno=50;

# 把人数超过5人的部门记录拷贝到新的部门表
CREATE TABLE t_dept_new LIKE t_dept;
INSERT INTO t_dept_new (deptno,dname,loc)
(SELECT d.deptno,d.dname,d.loc FROM t_dept d JOIN
(SELECT deptno FROM t_emp GROUP BY deptno
HAVING COUNT(*)>=5) temp
ON d.deptno = temp.deptno);

2、UPDATE语句：用于修改表的记录UPDATE [IGNORE] 表名 SET 字段1=条件1，字段2=条件2,....[WHERE 条件1，条件2，.....] [ORDER BY....] [LIMIT....];
示例：

# 把ALLEN调往RESEARCH部门，职务调整为ANALYST
UPDATE t_emp e JOIN t_dept d
SET e.deptno = d.deptno,e.job="ANALYST",d.loc="北京"
WHERE e.ename = "ALLEN" AND d.dname = "RESEARCH";

因为WHERE子句中的子查询效率非常低，所以我们可以利用表连接的方式来改造UPDATE语句

UPDATE 表1 JOIN 表2 ON 条件
SET 字段1=值1，字段2=值2，.....;

UPDATE 表1,表2
SET 字段1=值1，字段2=值2，.....
WHERE 连接条件;

表连接的UPDATE语句可以修改多张表的记录；UPDATE语句的表连接既可以是内连接，又可以是外连接
示例：

# 把底薪低于公司平均底薪的员工，底薪增加150元
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;


# 把没有部门的员工，或者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 条件1，条件2，.....] [ORDER BY....] [LIMIT....];
表连接改造DELETE语句：DELETE 表1,.... FROM 表1 JOIN 表2 ON 条件 [WHERE 条件1，条件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;

# 删除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 sal FROM t_emp GROUP BY deptno) t
ON e.deptno = t.deptno AND e.sal<t.sal;



# 删除员工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;


# 删除SALES部门的员工，以及没有部门的员工
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;

DELETE语句的表连接既可以是内连接，又可以是外连接。

4、TRUNCATE语句在事务机制之外删除记录，速度远超过DELETE语句TRUNCATE TABLE 表名;

MySQL的基本函数的使用

主要内容：数字函数：FORMAT、ABS、MOD、CEIL、FLOOR、ROUND、EXP…；字符函数：UPPER、LOWER、CHAR、LENGTH、CONCAT、INSTR…；日期函数&条件函数：NOW、DATE_FORMAT、DATE_ADD、DATEDIFF、IF、IFNULL…
1、数字函数：


2、日期函数：





3、字符函数：


4、条件函数：


示例：

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

#
SELECT 
	e.ename,e.empno,
	CASE 
	WHEN d.dname="SALES" THEN "P1"
	WHEN d.dname="ACCOUNTNG" THEN "P2"
	WHEN d.dname="RESEARCH" THEN "P3"
END AS place
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;


# 调整基本工资
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=d.deptno
SET 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)/365<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 e.deptno IS NULL THEN e.sal+100
			ELSE e.sal
	END
);

MySQL的综合应用

主要内容：数据库事务机制：undo和redo日志、开启事务、提交事务、回滚事务；数据导出于导入：SQL文件的导出与导入、TXT文档的导入与导出
1、事务机制

事务机制：RDBMS = SQL语句 + 事务（ACID）；事务是一个或者多个SQL语句组成的整体，要么全部执行成功，要么全部执行失败

管理事务：默认情况下，MySQL执行每条SQL语句都会自动开启和提交事务；为了让多条SQL语句纳入到一个事务之下，可以手动管理事务START TRANSACTION; SQL语句 [COMMIT|ROLLBACK];
事务的ACID属性：原子性、一致性、隔离性、持久性
事务的四个隔离级别：

修改事务隔离级别：
（1）READ UNCOMMITTED代表可以读取其他事务未提交的数据

SET SESSION TRANSACTION ISOLATION  LEVEL READ UNCOMMITTED;

（2）READ COMMITTED代表只能读取其他事务提交的数据

SET SESSION TRANSACTION ISOLATION  LEVEL READ COMMITTED; 

（3）REPEATABLE READ代表事务在执行中反复读取数据，得到的结果是一致的，不会受其他事务影响

SET SESSION TRANSACTION ISOLATION  LEVEL REPEATABLE READ;

2、事务的导入导出
数据导出：导出的纯粹是业务数据
数据备份：备份的是数据文件、日志文件、索引文件等
3、AES加密函数：AES_ENCRYPT(原始数据，密钥字符串)

SELECT HEX(AES_ENCRYPT("你好世界","ABC123456"));

AES解密函数：AES_DECRYPT(加密结果，密钥字符串)

SELECT AES_DECRYPT(UNHEX("E85A104B6142A7375E53C0545CAD48EE"),
	"ABC123456");