MySQL

介绍

​ mysql是一个关系型数据库管理系统

一个数据库管理系统都包含哪些最基本成员

​ 1：若干个数据库组成

​ 2：管理软件

​ 3：使用人员（DBA，以及普通人员）

数据库

什么是数据库

​ 所谓的数据库，就是一个存储数据的仓库，数据存储在仓库里面，我们就可以通过管理软件提供的方式来对数据进行快速的CRUD

目前数据库主要分为两大类

关系型数据库

​ sql server，mysql，oracle…等

非关系型数据库

​ redis…等

如何区分一个数据库是否是关系型还是非关系型

​ 关系型数据库这个名字意味着它跟一些东西有关系，这个东西就是SQL规范，而所有的关系型数据库都必须要遵守SQL规范

SQL

​ 结构化查询语句，sql是一个为了实现特殊目的的编程语言。其主要目的就是针对关系型数据库的各种操作，以及数据库中数据的增删改查等，sql语言诞生于1974年，1980年美国的国家标准局数据委员会将sql定义成了关系型数据库语言的美国标准，当美国宣布不久之后，ISO（国际标准化组织）宣布将sql定为关系型数据库的国际标准

分为六类

1：DQL（数据查询语言，主要是查询数据）

​ 关键字：SELECT（查） WHERE（筛选过滤）

2：DML（数据操作语言，主要是增、删、改）

​ 关键字：INSERT（增加） UPDATE（修改） DELETE（删除）

3：TCL（事务控制语言）

4：DCL（数据控制语言，权限操作）

​ 关键字：GRANT（赋予用户权限） REVOKE（回收权限）

5：DDL（数据定义语言）

​ 关键字：CREATE（创建） DROP（删除） ALTER（修改）

6：CCL（指针控制语言）

MySQL>>>>数据库>>>>表>>>>列>>>>行

​ MySQL：是一个数据库管理系统，每个MySQL都能有很多数据库

​ 数据库：就是一个数据仓库，每个数据库中都有很多表

​ 表：里面存放的是我们的数据，每个表中都有任意个列

​ 列：用来指定了存放的数据类型，以及数据的长度等，每个列可能有任意行

​ 行：里面存放了我们需要存储的数据

如何用Java的目光看待数据库？

​ 将表看成Java的类，表中的列看成类中的属性，每一行数据都对应着Java的一个对象

SQL语法

SQL命名规则

​ sql不分大小写，多个单词之间使用_分割

权限设定

#登录本地mysql
mysql -uroot -p
#创建数据库(DDL)
CREATE DATABASE aaa;
#查看数据库
SHOW DATABASES;
#创建用户(DDL)
CREATE USER 'xyc' IDENTIFIED BY '123456';
#删除用户
DROP USER 'xyc';
#给xyc用户赋予针对aaa数据库下的创建，增加，查询权限(DCL)
GRANT CREATE,INSERT,SELECT ON aaa.*TO 'xyc';
#回收xyc用户针对aaa数据库下的创建，增加，查询权限（DCL）
REVOKE CREATE,INSERT,SELECT ON aaa.*FROM 'xyc';
#查看用户当前登录用户
SELECT USER();
#切换数据库
USE AAA;
#展示数据库中的表
SHOW TABLES;

建表

​ 1、CREATE：代表创建，在sql中无论是建库，还是建表，还是视图只要跟创建有关，第一个词都是CREATE
​ 2、TABLE：代表表
​ 3、USER：是表的名字，自己定义表名，尽量不要是Java与mysql的关键字
​ 4、（）：里面定义表中的具体信息
​ 5、id,name,sex等：这些都是我们自己定义的列名称，多个列之间使用分割，需要注意的是最后一个列不能使用
​ 6、int：是数据类型，表示整数，每一个列名后面都需要有一个数据类型，用来表示这一列存储的数据是什么类型
​ 7、varchar：用来表示字符串，字符串在数据库中需要指定长度，而varchar是可变字符串。

​ 例如：varchar（20）虽然指定了这一列能存储20个字符，但是其占用空间是根据实际占用情况来看的，如果数据只占用了两个字符的话，那实际占用空间就是2个字符，当前最高占用不能超过20个字符，varchar最高能存储65532字节的数据

​ 8、char：用来表示字符串，不可变（例如sex（20），这里指定了sex每一行数据都最多能存储20个字符，但是如果只存放了一个字符，实际占用空间还是20个字符空间）
​ 9、date：数据类型，用来表示日期
​ 10、timestamp：虽然会以年月日，时分秒来表示但是本质上是时间戳
​ 11、bit：只能存储数字0和1，对应Java布尔

CREATE TABLE USER(
id int,
name varchar(20),
sex char(1),
hiberdate date,
n timestamp,
is_admin bit 
)

删表

DROP TABLE USER;

查看表

#查看表的信息
DESC USER;
#查看表的创建语句
SHOW CREATE TABLE USER;		

修改表

#表已经存在了，但是创建错误了，此时需要修改表
#增加列
ALTER TABLE USER ADD number INT;
#修改列的类型
ALTER TABLE USER MODIFY number varchar(20);
#修改列的名称
ALTER TABLE USER CHANGE number num varchar(20);
#删除列
ALTER TABLE USER DROP num;
#修改表名
ALTER TABLE user RENAME TO u;
#删除主键自增长
ALTER TABLE U MODIFY id int; 
#添加主键自增长
ALTER TABLE u MODIFY id int AUTO_INCREMENT;
SELECT * FROM U;
#删除主键(需要先删除自增)，主键在删除之后会保留一个非空约束
ALTER TABLE u DROP PRIMARY KEY;
#增加主键
ALTER TABLE u ADD PRIMARY KEY(id);
#删除非空约束
ALTER TABLE u MODIFY sex varchar(20);
#增加非空约束
ALTER TABLE u MODIFY sex varchar(20) NOT NULL;
#删除唯一约束
ALTER TABLE u DROP KEY name;
#增加唯一约束
ALTER TABLE u ADD CONSTRAINT UNIQUE(name);
#删除外键约束(要根据约束的名称来删除)
ALTER TABLE stu DROP FOREIGN KEY stu_ibfk_1;
#添加外键
ALTER TABLE stu ADD CONSTRAINT stu_class_id FOREIGN KEY(class_id) REFERENCES class(id);
DESC stu;

插入数据

​ 1、INSERT INTO：表示插入
​ 2、USER：是需要插入数据的表名
​ 3、VALUES：表示需要插入多个数据
​ 4、（）：里面填写插入的值，需要注意的是，这个值的书写顺序必须与建表时候列的顺序一致
​ 5、插入的时候，需要插入所有列的全部值，如果有某一列不需要填写值，可以使用null代替

#插入的时候，需要插入所有列的全部值，如果有某一列不需要填写值，可以使用null代替
INSERT INTO USER VALUES(1,"张三",'男','1998-08-28','2008-08-08 12:08:08',1);
#指定列插入数据
INSERT INTO USER (id,name,n) VALUES(2,'李四','1999-10-25 12:00:00');
#数据翻倍（一般用于测试大量数据时候使用）
INSERT USER SELECT * FROM USER;	

查看数据

​ 我们查询语句查出来的内容统称为集合的投影，看起来是一个表结构，所以我们查询的结果集也能当表来使用

​ 查询语句主要分为三部分组成

​ SELECT：用来表示查询的关键字，SELECT后面需要写查询的列都有那些，多个列名之间使用。

​ FROM：用来表示从哪些表中进行查询，这个表可以是一个数据库真实存在的表，也可以是一个集合的投影

​ WHERE：用来条件过滤以及筛选

普通查询

#查询emp表中部门20的员工姓名（ename）以及工资（sal）
SELECT ENAME '员工姓名',SAL '工资' WHERE DEPTNO=20
#查询emp表中名字叫king的员工姓名，工资，部门编号
#可以给列或者表起一个别名，用来方便使用或者显示在结果集之中，别名的关键字是AS，默认可以不写
#一定要注意这个别名只存在于集合的投影中，并没有真实的影响数据库
SELECT ename AS '员工姓名',sal AS '工资',deptno AS '部门编号' FROM emp where ename='king';
SELECT ename '员工姓名',sal '工资',deptno '部门编号' FROM emp where ename='king';
#SELECT后面列的值可以进行运算然后显示在集合的投影中，查询所有员工的姓名以及员工的年薪（12薪）
SELECT ename '员工姓名',sal*12 '年薪' from emp; 
#显示每个员工月薪扣掉500后的工资
SELECT ename '员工姓名',sal-500 '年薪' from emp; 
#显示所有的职位(使用distinct可以对重复的数据进行去重)
SELECT DISTINCT job '职位' FROM emp;
#查询工资大于1600的员工所有信息
SELECT * FROM emp WHERE sal>1600;
#查询工资大于等于1600的员工所有信息
SELECT * FROM emp WHERE sal>1600 or sal=1600;
#查询工资小于1600的员工所有信息
SELECT * FROM emp WHERE sal<1600;
#查询工资小于等于1600的员工所有信息
SELECT * FROM emp WHERE sal<1600 or sal=1600;
SELECT * FROM emp WHERE sal<=1600;
#查询工资不等于1600的员工所有信息
SELECT * FROM emp WHERE NOT sal=1600;
SELECT * FROM emp WHERE sal!=1600;
SELECT * FROM emp WHERE sal<>1600;
#查询工资在3000~1000之间的员工所有信息
SELECT * FROM emp WHERE sal<=3000 and sal>=1000;
#使用between	and进行范围判断，相当于sal<=3000 and sal>=1000
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 3000;
#查询工资不在3000~1000之间的员工所有信息（使用not对between进行取反）
SELECT * FROM emp WHERE sal NOT BETWEEN 1000 AND 3000;
SELECT * FROM emp WHERE NOT (sal<3000 and sal>1000);
SELECT * FROM emp WHERE ! (sal<3000 and sal>1000);
SELECT * FROM emp WHERE sal>=3000 or sal<=1000;
#查询工资是800或5000的员工
SELECT * FROM emp WHERE sal=800 OR sal=5000;
#查询工资是800或1600或1250或2975或2450或2850的员工所有信息
SELECT * FROM emp WHERE sal=800 OR sal=1600 OR sal=1250 OR sal=2975 OR sal=2450 OR sal=2850
#使用in关键字进行条件匹配，在in的小括号之内可以写多个值，只要符合里面任意一个值都算匹配成功
SELECT * FROM emp WHERE sal IN(800,1600,1250,2975,2450,2850)
#查询工资不是5000,2450,1250或者部门不是20,10的员工所有信息
SELECT * FROM emp WHERE SAL NOT IN(5000,2450,1250) OR (DEPTNO NOT IN(10,20))
#查询没有奖金的员工,null进行判断的时候要使用is null，不为空使用is not NULL
SELECT * FROM EMP WHERE COMM IS NULL OR COMM=0
#查询奖金不为空的员工
SELECT * FROM EMP WHERE COMM!=0 AND COMM IS NOT NULL
#查询员工的年收入是多少(15薪)
#空值不能进行运算，所有的数值与NULL的运算结果还是NULL，此时ifnull出现了
#为null的值，第二个参数是如果第一个参数为null则取第二个值进行运算，如果第一个值不是NULL则使用本身运算
#ifnull（x，y）如果X是空则使用Y的值，否则使用X本身
SELECT ename '员工姓名',sal*15+IFNULL(COMM,0)*12 '年收入' FROM EMP
#公司年薪为15薪资，年底决定给奖金是null的员工每人奖金500元，求每个人年收入
SELECT ENAME '员工姓名',SAL*15+IFNULL(COMM*12,500) FROM EMP
#公司年薪为15薪资,年底时候决定给奖金小于300的员工每人每月奖励500元,求每个人年收入
#IF(表达式，X，Y)：类似于java中的三目运算，如果表达式为true则返回X值，否则返回Y值
SELECT ENAME '员工姓名',SAL*15+IF(IFNULL(COMM,0)<300,500,COMM)*12 FROM EMP
#按照年收入对员工进行排序升序
#在表名后面可以使用order by对查询出来的数据进行排序，默认排序是升序（asc），可以使用desc指定降序
SELECT ENAME '员工姓名',SAL*15+IF(IFNULL(COMM,0)<300,500,COMM)*12 '年薪' FROM EMP ORDER BY 年薪 DESC
#查询员工的所有信息，按照工资降序排序,如果工资相同按照姓名排序
#排序可以有多个标准，会首先按照第一个标准排序，如果第一个值出现了重复，则会按照接下来的规则继续排序
SELECT * FROM EMP ORDER BY SAL DESC,ENAME DESC
#查询部门20的员工所有信息,按照工资升序进行排序
SELECT * FROM EMP WHERE DEPTNO=20 ORDER BY SAL,ENAME

流程控制语句查询

​ 类似于Java中的switch case需要注意的是结束的时候最后要有end

#显示user表的用户名,以及性别
SELECT username,if(is_male=1,'男','女') FROM user
SELECT username,CASE is_male WHEN 1 THEN '男' WHEN 0 THEN '女' END FROM user

分组查询

​ group by分组，经常配合统计函数使用，如果sql中使用了group by，那么要求select后面的列必须在聚合函数之内或者写在group by的后面，否则数据肯定不对

SELECT ename,deptno,count(*) FROM emp GROUP BY deptno,ename
#求每个部门的平均工资，sal在聚合函数之中，deptno在group by后面，所有sql是正确的
SELECT deptno,avg(sal) FROM emp GROUP BY deptno

​ having是配合group by使用，主要目的是为了对分组之后的数据进行筛选

#找出平均工资大于2500的部门
SELECT deptno,avg(sal) s FROM emp GROUP BY deptno HAVING s>2500
#找出部门平均工资，大于所有员工平均工资的部门
SELECT deptno,avg(sal) s FROM emp GROUP BY deptno HAVING s>(SELECT avg(sal) FROM emp)

分页查询

​ limit分页，当数据过多时，一个页面无法完全显示，此时就需要将数据分批显示给用户，所以需要分页查询

​ limit后面如果是一个数字，就单纯的代表了显示前多少条数据

​ limit后面如果是两个数字，前面的数字代表从第几条数据之后开始显示，后面数字代表显示几条数据

SELECT *FROM emp LIMIT 0,5;#从第0条之后开始显示，显示5条
SELECT *FROM emp LIMIT 5,5;#从第5条之后开始显示，显示5条
SELECT *FROM emp LIMIT 10,5;#从第10条之后开始显示，显示5条

查询关键字的书写顺序

SELECT 
DISTINCT
deptno d,avg(sal) s
FROM emp
WHERE 1=1
GROUP BY d
HAVING 1=1
ORDER BY d
LIMIT 0,5;

sql的执行顺序

​ 1、from：确定表

​ 2、join on：确定表（多表联查）

​ 3、where：筛选行，将不符合要求的行数据筛选掉

​ 4、group by：对筛选之后的数据进行分组统计（列的别名，在这一步之后才使用）

​ 5、having：对分组之后的数据进行筛选

​ 6、select：确定列

​ 7、distinct：去处重复数据

​ 8、order by：对数据排序

​ 9、limit：分页显示数据

多表查询

​ 就是将数据库中的多个表关联起来进行查询，而想要将两张表关联起来最重要的就是寻找表与表之间的交集点

​ 多表查询主要分为以下几种

交叉连接

隐式交叉连接（产生笛卡尔积）

SELECT *FROM emp,dept

显示交叉连接

SELECT *FROM emp CROSS JOIN dept

自然连接

​ 将两张表中等值的列自动排在一起，并放在第一列显示，过滤掉笛卡尔积现实给我们

SELECT *FROM emp NATURAL JOIN dept

内连接

隐式内连接

​ 通过where指定条件，来让不同表中的列产生对比关系，只有关系成立的数据才会显示出来

SELECT *FROM emp,dept WHERE emp.deptno=dept.deptno

显示内连接

​ 使用inner join代替，然后使用on来做表关系的连接

SELECT *FROM emp INNER JOIN dept ON emp.deptno=dept.deptno

内连接的问题

​ 虽然避免了笛卡尔积并且常用，但是不符合条件的数据是无法显示，如果我们需要将不符合条件显示出来，就只能使用外连接

外连接

左外连接

​ 显示左侧表的全部数据，以及右侧表中符合要求的数据

SELECT *FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno

右外连接

​ 显示右侧表的全部数据，以及左侧表中符合要求的数据

SELECT *FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno

全连接

SELECT *FROM emp FULL JOIN dept

交并补差

交

​ mysql无法使用oracle验证

SELECT *FROM emp
INTERVAL
SELECT *FROM emp WHERE deptno=20

并

​ 将两条sql产生的结果集进行合并显示，会产生重复数据

SELECT *FROM emp
UNION ALL
SELECT *FROM emp WHERE deptno=20

补

​ 使用下面的结果集对上一句sql的结果集进行补充，不会产生重复数据

SELECT *FROM emp WHERE deptno=20
UNION
SELECT *FROM emp

差

​ mysql不存在，使用上面的结果集，减掉下面的结果集，然后对剩余的数据进行显示

SELECT *FROM emp
MINUS
SELECT *FROM emp WHERE deptno=20

（自连接）

​ 实际上不是一种语法，而是一种概念性东西，就是将一张表看成多个表，来进行查询，一般用于树形结构的数据中

SELECT e1.ename,e2.ename FROM emp e1 LEFT JOIN emp e2 ON e1.empno=e2.mgr

修改数据

​ 1、UPDATE：表示修改
​ 2、USER：我们的表名，需要修改那张表写那个表即可
​ 3、SET：开始设置值，在SET后面需要写列名=新的值，如果修改多个列之间使用，分割
​ 4、WHERE：用来过滤条件，只有符合WHERE条件的数据才会被修改，如果没有WHERE默认修改整张表
​ 5、AND：相当于Java中&&用于条件并行，只有当全部符合才算条件成立
​ 6、OR：相当于Java中||用于多条件选一，只要符合任何一个即可修改

UPDATE USER SET name='孙悟空';
UPDATE USER SET name='猪八戒' WHERE ID=2;
UPDATE USER SET NAME='沙和尚',HIBERDATE='2000-05-05' WHERE ID=1 AND SEX='男';
UPDATE USER SET NAME='唐僧' WHERE ID=1 OR NAME='沙和尚';

删除数据

#1：使用DELETE进行删除，需要注意当使用
DELETE FROM USER WHERE ID=2; 
#null在sql中不支持直接等于，判断为空要用is null，判断不为空用is not null
DELETE FROM USER WHERE ID=3 OR (ID=1 AND NAME='孙悟空' AND SEX is NULL) OR (ID=1 AND NAME='沙和尚' AND SEX = '男'); 
#2：直接清空表中的所有数据，不能加条件。
TRUNCATE TABLE USER;

DELETE与TRUNCATE的区别

​ 1：DELETE后面可以跟条件进行筛选，而TRUNCATE后面不能有任何条件
​ 2：DELETE操作在日志中有记录，可以使用回滚，而TRUNCATE在日志中没有记录，不能使用回滚
​ 3：DELETE是删除数据而已，而TRUNCATE是相当于删除了表，重新建立了一张表

查询表中所有数据

SELECT * FROM USER;

约束

​ 用来规定以及限制数据的内容

​ 1、非空约束（NOT NULL）：约束列的值不能为空

​ 2、唯一约束（UNIQUE）：列的值，在本列之内要唯一

​ 3、主键约束（PRIMARY KEY）：非空+唯一，表示这一列的值既不能是null也不能重复，一般情况下第一列都是主键

​ 4、外键约束：跟父子表有关（约束了子表的增加以及父表的删除）

​ 5、检查约束：在MySQL中存在，但是无效

​ 6、自增（AUTO_INCREMENT），一般情况下在mysql中我们的主键如果是int类型的话，可以使用自增长来让mysql自动维护主键的值

​ 7、我们可以使用default关键字来给列设置默认值，然后在插入数据的时候使用默认值进行插入

DROP TABLE USER;
CREATE TABLE USER(
#id是主键，自增长
id int PRIMARY KEY AUTO_INCREMENT,
#name唯一约束，要求值不重复
name varchar(20) UNIQUE,	
#sex要求非空约束，默认值是女
sex varchar(10) NOT NULL DEFAULT '女',	
#age要求检查约束，插入的值必须在18~25之间
age int,					
CHECK(age BETWEEN 18 AND 25)					
)
INSERT INTO USER VALUES(1,'张三','男',66);
#如果主键有自增长，插入的时候主键写null即可，mysql会自动填入主键
INSERT INTO USER VALUES(NULL,'李四','男',60);
#插入数据的时候使用DEFAULT来表示默认值
INSERT INTO USER VALUES(NULL,'王五',DEFAULT,60);
#唯一约束对null无效
INSERT INTO USER VALUES(NULL,NULL,DEFAULT,60)
#外键约束一般外键约束都在子表之内，由子表中的一列指向父类的主键（约束了子表的增加以及父表的删除）
CREATE TABLE CLASS(
id int PRIMARY KEY,
name varchar(20)
)
INSERT INTO CLASS VALUES(1,'终极一班');
INSERT INTO CLASS VALUES(2,'终极二班');
CREATE TABLE STU(
id int PRIMARY KEY,
name varchar(20),
class_id int,
#本表中的哪一列是外键绑定到哪张表的哪一列
FOREIGN KEY(class_id) REFERENCES class(id)
)
#增加的时候，由于class_id这一列绑定了class表的主键，所以class_id的值必须要在父表中存在，否则无法插入
INSERT INTO STU VALUES(1,'张三',1);
INSERT INTO STU VALUES(2,'李四',2);
#如果父表的主键值在子表中出现了，那么是不能直接删除的，需要先删除子表的数据，然后再删除父表
DELETE FROM STU WHERE ID=1;
DELETE FROM CLASS WHERE ID=1;

关于约束分类的一些争议

​ 观点1、约束分为列级约束与表级约束

​ 列级约束：自增，默认值，非空，检查

​ 表级约束：主键，外键，唯一

​ 观点2、约束分为行级与列级

​ 行级约束：自增，默认值，非空，检查

​ 列级约束：主键，外键，唯一

​ 观点3、约束分为行级与表级

​ 行级约束：自增，默认值，非空，检查

​ 表级约束：主键，外键，唯一

函数

数学函数

#求绝对值abs
SELECT ABS(-885)
#取余mod
SELECT 11%2
SELECT MOD(11,2)
#向上取整ceiling
SELECT CEILING(81.22)
#四舍五入round
SELECT ROUND(85.45,1)
SELECT ROUND(85.55)
#截取小数(后面指定保留多少位小数)truncate
SELECT TRUNCATE(2001.154,0)
SELECT TRUNCATE(2001.154,2)

字符串函数

#获取ASCII值ascii
SELECT ASCII('A')
#字符串的长度length
SELECT LENGTH('DJKHASKJDHKA')
#查询名字长度是5的员工emp表
SELECT *FROM EMP WHERE LENGTH(ENAME)=5
#字符串拼接concat
SELECT CONCAT('HELLO','JAVA','HELLO','张三')
SELECT CONCAT('HELLO ','JAVA')
#返回前面字符串在后面字符串中出现的位置，注意从1开始计算，如果没有返回0 locate
SELECT LOCATE('JA','HELLO JAVA')
#返回后面字符串在前面字符串中出现的位置，注意从1开始计算，如果没有返回0 instr
SELECT INSTR('HELLO JAVA','JA')
#从左侧进行字符串截取，截取多少位left
SELECT LEFT('HELLO',2)
#从右侧进行字符串截取，截取多少位(空格也是字符串)right
SELECT RIGHT('HELLO ',2)
#从第几位截取字符串，截取几位substr
SELECT SUBSTR('HELLO JAVA' FROM 2 FOR 5)
#从第x位截取字符串，截取全部
SELECT SUBSTR('HELLO JAVA',2)
#查询名字第三个字是A的员工
SELECT *FROM emp WHERE SUBSTR(ENAME FROM 3 FOR 1)='A'
#去取字符串的前后空格trim
SELECT TRIM('        打开了三轮      拉萨   大萨达撒多  ')
#去除左侧空格ltrim
SELECT LTRIM('        打开了三轮      拉萨   大萨达撒多  ')
#去除右侧空格rtrim
SELECT RTRIM('        打开了三轮      拉萨   大萨达撒多  ')
#字符串替换replace
SELECT REPLACE('        打开了三轮      拉萨   大萨达撒多  ',' ','')
#字符串翻倍，后面的数字是多少，就翻倍多少repeat
SELECT REPEAT('有人抄代码',50);
#字符串反转reverse
SELECT REVERSE('读好书')
#通过位置进行字符串替换insert(str,x,y,z)将str字符串中x位置的y个字符替换成z
SELECT INSERT('AEFGHI',2,0,'BCD')
#在字符串sdkjfhkjhfwfoiqewirfjioqweoqwr在第一个o后面插入小花
SELECT INSERT('sdkjfhkjhfwfoiqewirfjioqweoqwr',LOCATE('O','sdkjfhkjhfwfoiqewirfjioqweoqwr')+1,0,'小花')
#将字符串全部转换成小写
SELECT LOWER(ename) FROM emp
#将字符串全部转换成大写
SELECT UPPER(ename) FROM emp
#以首字母大写的方式显示员工姓名
SELECT INSERT(LOWER(ename),1,1,SUBSTR(UPPER(ename) FROM 1 FOR 1)) FROM emp

时间函数

#获取当前时间now
SELECT NOW()
#获得年月日curdate
SELECT CURDATE()
#获得时分秒current_time
SELECT CURRENT_TIME()
#返回星期的索引(国外)dayofweek
SELECT DAYOFWEEK(NOW())
#获得当前月过了多少天dayofmonth
SELECT DAYOFMONTH(NOW())
#获得当前年过了多少天dayofyear
SELECT DAYOFYEAR(NOW())
#求出年份year
SELECT YEAR(NOW())
#求出月份month
SELECT MONTH(NOW())
#求出日day
SELECT DAY(NOW())
#求出时hour
SELECT HOUR(NOW())
#求出分minute
SELECT MINUTE(now())
#求出秒second
SELECT SECOND(NOW())
#求出星期对应的英文名称dayname
SELECT DAYNAME(NOW())
#求出月份的名称monthname
SELECT MONTHNAME(NOW())
#求出季度quarter
SELECT QUARTER(NOW())
#根据参数决定求年月日时分秒extract
SELECT EXTRACT(DAY FROM NOW())
SELECT EXTRACT(HOUR FROM NOW())
SELECT EXTRACT(YEAR FROM NOW())
#在时间上加上对应的年月日时分秒date_add
SELECT DATE_ADD(NOW(),INTERVAL 30 MINUTE)
SELECT DATE_ADD(NOW(),INTERVAL 30 YEAR)
#在时间上减去对应的年月日时分秒date_sub
SELECT DATE_SUB(NOW(),INTERVAL 30 YEAR)
SELECT DATE_SUB(NOW(),INTERVAL 30 MINUTE)
#看如今距离200-08-28多久了(前面日期减掉后面日期)datediff
SELECT DATEDIFF(NOW(),'1999-11-26')
#将日期转换成天数to_days
SELECT TO_DAYS(NOW())-TO_DAYS('2000-08-28')
#返回特定（当前）日期的最后一天的日期
SELECT LAST_DAY(NOW()) FROM emp

系统函数

#查看系统版本version
SELECT VERSION()
#查看当前登录的数据库名称database
SELECT DATABASE()
#查看当前登录的用户user
SELECT USER()
#查看当前的字符集charset
SELECT CHARSET('啊')

加密函数

SELECT MD5(1123)
SELECT PASSWORD(123)

统计函数

#求平均值
SELECT AVG(sal) FROM emp
#求最大值
SELECT MAX(sal) FROM emp
#求最小值
SELECT MIN(sal) FROM emp
#求数据的总数
SELECT COUNT(*) FROM emp
#求和
SELECT SUM(sal) FROM emp

事务

概念

​ TCL（事务控制语言）事务实际上就是指我们要做的事情，那在sql语句中事务就代表了要执行的sql语句

​ 在关系型数据库中默认每条SQL语句就是一个单独的事务，当然我们也可以取消掉默认，让多条sql事务变成一个事务

​ 因为在mysql中默认每条SQL语句都是单独的事务，所以在mysql中事务默认是自动提交的，在事务开启的情况下只有提交事务才会对数据库执行更改操作

事务主要的两个关键字

commit

​ 提交事务，在开启事务的时候只有提交事务，数据库才会发生改变

rollback

​ 回滚事务，可以让数据恢复到执行SQL之前的状态

#开启事务
start transaction;
update u set money=money-500 where id=1;
dsajd
update u set money=money+500 where id=2;
#提交事务
commit;
#回滚事务
rollback;
select *from u;

事务的四大特性

原子性

​ 是过去的最小单位，代表了不可分割，表示一个事务是一个原子性的整体，不能对其进行分割，要么一起成功，要么一起失败

一致性

​ 数据从一个状态转变成另一个状态（数据安全性：举例，张三1000元，李四1000元，张三给李四转钱，无论怎么转最后数据库应该还是有2000元）

隔离性

​ 为了防止多个事务之间相互干扰

#查看当前级别
select @@tx_isolation

读未提交

​ read uncommitted，是指用户A没有提交的数据，被用户B读取到了（脏读，不可重复读，幻读）

create table u(
id int,
name varchar(20),
money int
);
insert into u values(1,'张三',1000);
insert into u values(2,'李四',1000);
#事务A
#设置数据库的隔离级别读未提交，观察脏读
set session transaction isolation level read uncommitted;
start transaction;
select *from u;
#设置数据库的隔离级别读已提交，解决脏读
set session transaction isolation level read committed;
start transaction;
select *from u;
commit;
#事务B
#设置数据库的隔离级别读未提交，观察脏读
set session transaction isolation level read uncommitted;
start transaction;
insert into u values(3,'王五',50000);
rollback;
#设置数据库的隔离级别读已提交，解决脏读
set session transaction isolation level read committed;
start transaction;
insert into u values(3,'王五',50000);
rollback;
select *from u;
commit;

读已提交

​ read committed，可以避免脏读，会引发不可重复读

不可重复读

​ 是指用户A在两次读取数据的中间，数据被用户B修改了，导致A两次读取的数据不一致

#事务A
#观察不可重复读
start transaction;
select *from u;
select *from u;
commit;
# 设置隔离级别可重复读,解决不可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM U;
SELECT * FROM U;
COMMIT;
#事务B
#观察不可重复读
start transaction;
update u set money=1 where id=1;
commit;
# 设置隔离级别可重复读,解决不可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
UPDATE U SET MONEY=1 WHERE ID=2;
COMMIT;

可重复读

​ repeatable read，可以避免脏读和不可重复读，会引发幻读（mysql默认的隔离级别）

幻读

​ 是指用户A在连续操作数据的过程中，B增加了一条新的数据，影响到了A

#事务A
# 观察幻读的出现,表面看起来可重复度,解决了幻读,但是真的解决了么？
START TRANSACTION;
SELECT * FROM U;
#SELECT * FROM U;
UPDATE U SET money=1000;
COMMIT;
# 解决幻读
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM U;
update u set money=2000;
COMMIT;
#事务B
# 观察幻读
START TRANSACTION;
INSERT INTO U VALUES(3,'王五',50000);
COMMIT;
# 解决幻读
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM U;
COMMIT;

串行化

​ serializable，相当于单线程，在操控表的过程中，其余任何人都不能操作，串行化不会引发任何问题

持久性

​ 将改变的数据持久化到可掉电设备（硬盘）中

锁

​ 锁的主要目的就是为了保护东西，数据库的锁主要目的就是为了保护数据，主要是为了防止多用户共同操作数据的时候出现数据混乱

​ 乐观锁（MVCC乐观锁），悲观锁，排它锁，共享锁，表锁，行锁

乐观锁

​ 非常乐观的一个人，总是认为在自己操控数据的时间之内，肯定不可能有其他人来操控数据，乐观锁需要我们通过数据库设计来实现，一般我们使用乐观锁的话，会在数据库之内加入一列叫版本号值的数字或时间戳，然后在每次去修改数据之前，先查看当前版本号是多少，在修改的时候需要去对比版本号是否一致，如果一致则修改成功，否则修改失败

create table s(
id int,
name varchar(20),
money int,
version int default 1
);
insert into s values(1,'张三',1000,default);
select*from s;
#乐观锁去修改数据时候的操作：将id是1的人加1000块钱
#1：先查出id1的人版本号多少
select version from s where id=1;
#2：将id是1的人加1000块钱
update s set money=money+1000,version=version+1 where id=1 and version=1;

悲观锁

​ 悲观锁在修改数据的时候，总是认为会有人跟我抢车位，如果对方跟我抢车位，我就要想办法先占住这个车位，共享锁和排它锁都是悲观锁的实现，数据库对悲观锁提供有关键字

排它锁（X锁，写锁）

​ 假设事务A对数据库对象O加了X锁之后，在此期间任何其他的事务都不能对O加锁

#事务A
set session transaction isolation level read uncommitted;
start transaction;
select *from u for update;
insert into u (money) values(1000);
commit;
#事务B
#演示悲观锁里面的排它锁
update u set money=1500 where id=3;
select *from u;

共享锁（S锁，读锁）

​ 假如事务A对数据库对象O加了S锁之后，就可以对其进行查询，但是不能更新数据，并且此时，其他事务可以继续对数据库对象加S锁但不能加X锁

#事务A
start transaction;
select *from u lock in share mode;
insert into u(money) values(2000);
commit;
#事务B
select *from u lock in share mode;
update u set money=2000 where id=1;

当前读

​ 任何加锁的查询都叫当前读，当前读由于锁的操作，在读取的过程中其他人无法更新数据，所以当前读肯定获取的是最新数据，但是当前读有可能造成线程的阻塞

快照读

​ 没加锁的读，就是直接写select查询语句，快照读有可能会读到历史版本的数据，因为快照读无法避免在读取数据的时候别人更新数据库