MySQL

常用

MySql模糊搜索结果按匹配度排序

SELECT *
FROM mov_video_info
WHERE title LIKE '%魔兽%'
ORDER BY REPLACE(title,'魔兽','')
LIMIT 10

通过替换关键字为空字符串来改变排序规则

基础

SQL语句分类

1. DDL(Data Definition Language) 数据定义语言
   用来定义数据库对象：数据库，表，列等。关键字：create，drop，alter等

2. DML(Data Manipulation Language) 数据操作语言
   用来对数据库中表的数据进行增删改。关键字：insert，delete，update等

3. DQL(Data Query Language) 数据查询语言
   对数据库进行数据查询，关键字select

4. DCL(Data Control Language)数据控制语言(了解)

   是用来设置或更改数据库用户或角色权限的语句，这个比较少用到

SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾。

2. 可使用空格和缩进来增强语句的可读性。

3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

   SELECT
      * 
         FROM 
             abc;
   

4. 3种注释
   单行注释: – 注释
   多行注释: /*注释*/
   MySQL特有的单行注释：# 注释

5. SQL的作用

   通过SQL语句可以很方便的操作数据库,表,记录
   

6. SQL的分类

   DDL: 数据定义语言  (操作数据库,表)
   DML: 数据操作语句  (操作数据的增删改)
   DQL: 数据查询语句  (查询数据)
   

创建数据库

1. 直接创建数据库

   CREATE DATABASE 数据库名;
   直接创建数据库db1
   CREATE DATABASE db1;
   

2. 判断是否存在并创建数据库

   CREATE DATABASE IF NOT EXISTS 数据库名;
   判断是否存在并创建数据库db2
   CREATE DATABASE IF NOT EXISTS db2;
   

3. 创建数据库并指定字符集(编码表)

   CREATE DATABASE 数据名 DEFAULT CHARACTER SET 字符集;
   创建数据库db3并指定字符集为gbk
   CREATE DATABASE db3 CHARACTER SET gbk;
   

查看数据库

1. 查看所有的数据库

SHOW DATABASES;

2. 查看某个数据库的定义信息

SHOW CREATE DATABASE 数据名;

修改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集;
ALTER: 表示修改
DATABASE: 数据库

删除数据库

DROP DATABASE 数据库名;
DROP: 删除

查看正在使用的数据库

SELECT DATABASE();

使用/切换数据库

USE 数据库名;

DDL语句操作数据库	关键字
创建	CREATE DATABASE 数据库名;
修改	ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
查看	SHOW DATABASES;
删除	DROP DATABASE 数据库名;

创建表

CREATE TABLE 表名 (字段名1 字段类型1, 字段名2 字段类型2);

建议写成如下格式：

CREATE TABLE 表名 (
    字段名1 字段类型1,
    字段名2 字段类型2
);

MySQL数据类型

常用数据类型

int: 整数
double: 小数
date: 日期
varchar(length) 可变长度字符串

查看某个数据库中的所有表

SHOW TABLES;

查看表结构

DESC 表名;

查看创建表的SQL语句

SHOW CREATE TABLE 表名;

复制表

CREATE TABLE 表名 LIKE 其他表;

删除表

DROP TABLE 表名;

判断表是否存在并删除表

DROP TABLE IF EXISTS 表名;

添加表一列

ALTER TABLE 表名 ADD 字段名 字段类型;
为学生表添加一个新的字段remark,类型为varchar(20)
ALTER TABLE student ADD remark VARCHAR(20);

添加表多列

ALTER TABLE 表名 ADD 字段名 字段类型 COMMENT 备注,ADD 字段名 字段类型 COMMENT 备注...;

修改字段类型

ALTER TABLE 表名 MODIFY 字段名 新类型;
将student表中的remark字段的改成varchar(100)
ALTER TABLE student MODIFY remark VARCHAR(100);

修改字段中的某段字段

UPDATE 表名 SET 字段名= REPLACE (字段名,  '需要修改的字段', '修改的结果' )

修改字段名

ALTER TABLE 表名 CHANGE 老字段名 新字段名 类型;
将student表中的remark字段名改成intro，类型varchar(30)
ALTER TABLE student CHANGE remark intro varchar(30);

删除字段

ALTER TABLE 表名 DROP 字段名;
删除student表中的字段intro
ALTER TABLE student DROP intro;

修改表名

RENAME TABLE 表名 TO 新表名;
将学生表student改名成student2，再删除student2表
 RENAME TABLE student TO student2;
 DROP TABLE student2;

修改表的字符集

ALTER TABLE 表名 DEFAULT CHARACTER SET 新字符集;
将sutden2表的编码修改成gbk
ALTER TABLE student2 character set gbk;

创建student表包含id,name,birthday,sex,address字段。

CREATE TABLE student (
      id INT,
      name VARCHAR(20),
      birthday DATE,
      sex char(2),
      address varchar(50)
);

添加数据

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

添加数据不写字段名

INSERT INTO 表名 VALUES (值1, 值2, 值3, ...);

两张表（导出表和目标表）的字段一致，并且插入全部数据

INSERT INTO 目标表 SELECT * FROM 来源表 WHERE 条件;

只导入指定字段

INSERT INTO 目标表(字段1, 字段2, ...)SELECT 字段1, 字段2, ... FROM 来源表 WHERE 条件;

插入部分数据

只需要指定要插入数据的字段
INSERT INTO 表名 (字段名1, 字段名2...) VALUES (字段值1, 字段值2);
插入部分数据，往学生表中添加 id, name, age, sex数据
INSERT INTO student (id, NAME, age, sex) VALUES (1, '张三', 20, '男');

DOS命令窗口操作数据乱码问题

快捷设置

在DOS命令行输入：
set names gbk;

不带条件修改数据

UPDATE 表名 SET 字段名=字段值;

带条件修改数据

UPDATE 表名 SET 字段名=字段值,字段名=字段值 WHERE 条件;

带条件删除数据

DELETE FROM 表名 WHERE 条件;

不带条件删除数据

DELETE FROM 表名;

查询表中所有列数据

SELECT 字段名1, 字段名2, 字段名3 FROM 表名;
使用*表示所有列
SELECT * FROM 表名;

别名查询

SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名;
查询sudent表中name 和 age 列，name列的别名为”姓名”，age列的别名为”年龄”
SELECT NAME AS 姓名, age AS 年龄 FROM student;

清除重复值

SELECT DISTINCT 字段名 FROM 表名;

查询结果参与运算

某列数据和固定值运算

SELECT 字段名 + 值 FROM 表名;

某列数据和其他列数据参与运算

SELECT 字段1 + 字段2 FROM 表名;

修改student表结构,添加数学和英语成绩列

ALTER TABLE student ADD math INT;
ALTER TABLE student ADD english INT;

查询math + english的和

SELECT math + english FROM student;
查询math + english的和使用别名”总成绩”
SELECT math + english 总成绩 FROM student;
查询所有列与math + english的和并使用别名”总成绩”
SELECT *, math + english 总成绩 FROM student;
查询姓名、年龄，将每个人的数学增加10分
SELECT name, math + 10 FROM student;

DML_DQL小结

DML语句操作	关键字
添加	INSERT INTO 表名 (字段名) VALUES (字段值);
修改	UPDATE 表名 SET 字段名=值;
删除	DELETE FROM 表名;
查询	SELECT * FROM 表名;

DQL查询语句

语法格式：

SELECT * FROM 表名 WHERE 条件;
流程：取出表中满足条件的记录

比较运算符

>大于
<小于
<=小于等于
>=大于等于
=等于
<>、!=不等于

查询math分数大于80分的学生

SELECT * FROM student3 WHERE math>80;

逻辑运算符

and(&&) 多个条件同时满足
or(||) 多个条件其中一个满足
not(!) 不满足

查询age大于35且性别为男的学生(两个条件同时满足)

SELECT * FROM student3 WHERE  age>35 AND sex='男';

– 查询英语成绩不是null的
– 注意对NULL的处理不能写=或!=, 写 IS NULL 或 IS NOT NULL

SELECT * FROM student3 WHERE english IS NOT NULL;

in关键字
语法格式：

SELECT * FROM 表名 WHERE 字段名 IN (值1, 值2);
`in`里面的每个数据都会作为一次条件，只要满足条件的就会显示

范围

SELECT * FROM 表名 WHERE 字段名 BETWEEN 值1 AND 值2;
比如：`age BETWEEN 80 AND 100`
相当于： `age>=80 && age<=100` 

模糊查询like

LIKE 像什么什么一样

SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
满足`通配符字符串`规则的数据就会显示出来
MySQL通配符有两个：
`%`: 表示任意多个字符
`_`: 表示一个字符

排序

通过ORDER BY子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)

SELECT * FROM 表名 ORDER BY 字段名 ASC|DESC;

ASC: 升序
DESC: 降序

总结

1. 能够使用SQL语句建库、建表
	建库: CREATE DATABASE 数据库名;
   
	删库: DROP DATABASE 数据库名;

	建表: CREATE TABLE 表名 (字段名 字段类型);

	删表: DROP TABLE 表名;

	复制表: CREATE TABLE 表名 LIKE 其他表;

2. 能够使用SQL语句进行数据的添删改查操作

   添加数据: INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
   修改数据: UPDATE 表名 SET 字段名=值;
   删除数据: DELETE FROM 表名;
   查询数据: SELECT * FROM 表名;

3. 能够使用SQL语句进行排序

   SELECT * FROM 表名 ORDER BY 字段名 [ASC|DESC];

   默认是升序

   ASC: 表示升序

   DESC: 表示降序

拓展

-- 蠕虫复制
-- 自己在本机建立一个数据和表,和项目中的是一样的,操作你本机的
-- 创建一张表结构相同的表
CREATE TABLE student666 LIKE student3;

-- 将student3表中的数据复制到student666表中
-- 1.查询student3表的数据
-- 2.将查询的数据添加到student666中
INSERT INTO student666 SELECT * FROM student3;

-- 想蠕虫复制一部分,将student3表中的name和age数据,复制到student666中
INSERT INTO student666 (NAME, age) SELECT NAME, age FROM student3;

聚合函数

聚合函数的使用：写在 SQL语句SELECT后 字段名的地方

SELECT 字段名 FROM 表名;

SELECT 聚合函数(字段名) FROM 表名;
-- 让小数显示指定的位数 FORMAT(值, 小数位数)
SELECT FORMAT(值, 小数位数) FROM 表名;

聚合函数是以列为单位操作

函数	作用
SUM(列名)	求总和
COUNT(列名)	求总数量
MAX(列名)	求最大值
MIN(列名)	求最小值
AVG(列名)	求平均值

分组查询是指使用 GROUP BY语句对查询信息进行分组

SELECT * FROM 表名 GROUP BY 字段名;

将分组字段中,相同的数据作为一组,返回每组的第一条数据
一般来说,使用哪个字段分组,就查询哪个字段

   - having是在分组后对数据进行过滤
   - where是在分组前对数据进行过滤
   - having后面可以使用聚合函数
   - where后面不可以使用聚合函数

LIMIT语句格式

SELECT * FROM 表名 LIMIT 跳过的条数, 显示的条数;

SELECT 字段名 FROM 表名 WHERE 条件 GROUP BY 分组列名 HAVING 条件 ORDER BY 排序列名 LIMIT 跳过行数, 返回行数;

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
关键字说明：
1.`用户名`：将创建的用户名
2.`主机名`：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
3.`密码`：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

授权格式

GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';
关键字说明:
1.`GRANT` 授权关键字
2.授予用户的权限，如`SELECT`，`INSERT`，`UPDATE`等。如果要授予所有的权限则使用`ALL`
3.`数据库名.表名`：该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如`*.*`
4.`'用户名'@'主机名'`: 给哪个用户授权

撤销授权

REVOKE 权限1, 权限2... ON 数据库名.表名 FROM '用户名'@'主机名';

查看权限

SHOW GRANTS FOR '用户名'@'主机名';

删除用户

DROP USER '用户名'@'主机名';

修改管理员密码

注意：需要在未登陆MySQL的情况下操作。
mysqladmin -uroot -p密码 password 新密码  -- 新密码不需要加上引号

数据库备份

备份格式

注意：这个操作不用登录

在DOS命令行:mysqldump -u用户名 -p密码 数据库 > 文件的路径

还原格式

注意：还原的时候需要先登录MySQL，并选中对应的数据库

SOURCE 导入文件的路径

约束

数据库约束的作用

对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。

约束种类

• PRIMARY KEY: 主键约束
• UNIQUE: 唯一约束
• NOT NULL: 非空约束
• DEFAULT: 默认值约束
• FOREIGN KEY: 外键约束

创建主键方式

在创建表的时候给字段添加主键

CREATE TABLE 表名 (
	字段名 字段类型 PRIMARY KEY,
	字段名 字段类型
);

在已有表中添加主键(不常用)

ALTER TABLE 表名 ADD PRIMARY KEY (字段名);

删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

主键的特点：1.唯一，2.非空

主键自增

主键让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值

字段名 字段类型 PRIMARY KEY AUTO_INCREMENT

AUTO_INCREMENT 表示自动增长(字段类型必须是数值类型)
默认地AUTO_INCREMENT 的开始值是1，如果希望修改起始值，请使用下列SQL语法
ALTER TABLE st6 AUTO_INCREMENT = 10000;

-- 扩展delete和truncate的区别
-- DELETE只是单纯的删除表中的数据,表中的自动增长的值还在, 把所有的租客赶走,房子还在
DELETE FROM 表名;

-- TRUNCATE 表名;
-- TRUNCATE 摧毁表并重新创建一张一样的表,表中的自动增长的值没啦, 把房子拆了,再建一样的房子
TRUNCATE 表名;

唯一约束

这个字段的值不能重复

CREATE TABLE 表名 (
	字段名 字段类型 UNIQUE,
	字段名 字段类型
);

非空约束

CREATE TABLE 表名 (
	字段名 字段类型 NOT NULL,
	字段名 字段类型
);

主键唯一和非空，普通的字段我们也可以添加唯一和非空,有区别吗?

1.一张表中只能有一个主键
2.一张表中可以有多个唯一和非空普通字段
3.主键才能自动增长，普通的字段不能自动增长

默认值

CREATE TABLE 表名 (
	字段名 字段类型 DEFAULT 值,
	字段名 字段类型
);

外键约束

新建表时增加外键约束：

CREATE TABLE 表名 (
	字段名 字段类型,
    字段名 字段类型,
    -- 添加外键约束
    CONSTRAINT 外键约束名 FOREIGN KEY(外键字段名) REFERENCES 主表(主键字段名) [ON UPDATE CASCADE  ON DELETE CASCADE]
);

关键字解释:
CONSTRAINT:  表示约束
外键约束名： 给外键约束取个名字,将来通过约束名可以删除这个约束
FOREIGN KEY(外键字段名):  指定某个字段作为外键
REFERENCES 主表(主键字段名) :  引用主表的主键的值
级联更新：ON UPDATE CASCADE 当主表的主键修改时,从表的数据也跟着修改
级联删除：ON DELETE CASCADE 当主表的主键删除时,从表的数据也跟着删除

已有表增加外键约束：

ALTER TABLE 从表 ADD CONSTRAINT 外键约束名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

总结

能够使用SQL语句添加主键、外键、唯一、非空约束

1.主键约束: PRIMARY KEY
2.唯一约束: UNIQUE
3.非空约束: NOT NULL
4.默认值约束: DEFAULT 值
5.外键约束: [CONSTRAINT 外键约束名] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名)

能够使用聚合函数

COUNT: 统计这列的数量
SUM: 统计这列的总和
MAX: 统计这列的最大值
MIN: 统计这列的最小值
AVG: 统计这列的平均值

SELECT 聚合函数(字段) FROM 表名;

能够使用SQL语句进行分组查询

SELECT 字段名, 统计操作(字段名) FROM 表名 GROUP BY 字段名;

相同数据作为一组,返回每组的第一条,分组会使用聚合函数做统计

能够理解三大范式

1NF: 表中的字段不能再拆分,直接可以使用
2NF: 1.一张表描述一件事情/实体  2.每张表添加主键
3NF: 表中的字段引用其他表的主键(建立外键)

多表查询

隐式内连接

SELECT * FROM 表1, 表2 WHERE 条件;

显式内连接

显式内连接：使用INNER JOIN ... ON语句, 可以省略INNER

SELECT * FROM 表1 INNER JOIN 表2 ON 表连接条件 WHERE 查询条件;

左外连接

使用LEFT OUTER JOIN ... ON，OUTER可以省略

SELECT * FROM 左表 LEFT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件;

左外连接可以理解为：将满足要求的数据显示,左表不满足要求的数据也显示(保证左表的数据全部显示)

右外连接

使用RIGHT OUTER JOIN ... ON，OUTER可以省略

SELECT * FROM 左表 RIGHT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件;

右外连接可以理解为：满足要求的数据显示,并且右表不满足要求的数据也显示(在内连接的基础上保证右边的数据全部显示)

子查询

一个查询语句的结果作为另一个查询语句的一部分

SELECT 查询字段 FROM 表 WHERE 条件;

子查询需要放在（）中
先执行子查询，将子查询的结果作为父查询的一部分

子查询结果有三种情况：单行单列 (一个值)，多行单列 (多个值)，多行多列 (虚拟表)

子查询结果是多行单列

子查询结果是多行单列，结果集类似于一个数组，在WHERE后面作为条件，父查询使用IN运算符

SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);

子查询的结果是多行多列

子查询结果是多列，在FROM后面作为虚拟表

SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

子查询作为表需要取别名，否则这张表没有名称无法访问表中的字段

包含查询

-- find_in_set(字段名，字符串)  该方法相当于java中字符串的contains方法一样，查询字段名中匹配字符串的数据

SELECT * FROM co_contract WHERE FIND_IN_SET(create_dept,'100,100101,11111');

MySQL的索引

创建索引

单列索引是基于单个列所建立的索引

CREATE INDEX 索引名 ON 表名(字段名);

查看表中的索引

SHOW INDEX FROM 表名;

删除索引

DROP INDEX 索引名 ON 表名;

复合索引

复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引

CREATE INDEX 索引名 ON 表名(字段名1, 字段名2);

事务

事务特性	含义
原子性（Atomicity）	事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）	事务前后数据的完整性必须保持一致。
隔离性（Isolation）	是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离，不能相互影响。
持久性（Durability）	指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

什么是事务

由多条SQL语句组成一个工能,这多条SQL语句就形成一个事务

事务四个特性

原子性
一致性
隔离性
持久性

事务有关的SQL语句

SQL语句	描述
START TRANSACTION;	开启事务
COMMIT;	提交事务
ROLLBACK;	回滚事务

查询事务自动提交的提交状态

SHOW VARIABLES LIKE '%commit%';

SELECT @@AUTOCOMMIT; -- 推荐

关闭事务自动提交

SET AUTOCOMMIT = 0;

事务原理

1. 用户登录数据库,会创建一个临时事务日志文件
2. 普通的SQL执行,直接作用到数据库上
3. 开启事务,操作机制发生变化
4. 执行的SQL语句会保存到临时事务日志文件中
5. 提交事务,临时日志文件中的SQL语句就会作用到数据库上
6. 回滚事务,直接清空临时日志文件的内容

事务的操作	MySQL操作事务的语句
开启事务	START TRANSACTION;
提交事务	COMMIT;
回滚事务	ROLLBACK;
查询事务的自动提交情况	SELECT @@AUTOCOMMIT;
设置事务的自动提交方式	SET AUTOCOMMIT = 0;

设置回滚点语法

savepoint 回滚点名字;

回到回滚点语法

rollback to 回滚点名字;

事务在操作时的理想状态：多个事务之间互不影响，如果隔离级别设置不当就可能引发并发访问问题。

事务并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致
幻读	一个事务中多次读取，数据的数量不一样

MySQL数据库有四种隔离级别：上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

能够理解事务并发访问的三个问题

脏读：一个事务读取到另一个事务未提交的数据

不可重复读：一个事务读取多次,读取的数据内容不一致

幻读：一个事务读取多次,读取的数量不一致

能够说出MySQL的四种隔离级别

1.读未提交  read uncommitted
2.读已提交  read committed
3.可重复读 repeatable read 
3.串行化  serializable

查询全局事务隔离级别？

SHOW VARIABLES LIKE '%isolation%';

SELECT @@TX_ISOLATION;

设置全局事务隔离级别？

SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

如何解决赃读?

将隔离级别提升到 read committed

如何解决不可重复读？

将数据库的隔离级别设置为：REPEATABLE READ;

如何解决幻读？

将隔离级别设置成：SERIALIZABLE

总结

1. 能够使用内连接进行多表查询

   隐式内连接: select * from 表1, 表2 where 条件;

   (推荐)显示内连接: select * from 左表 inner join 右表 on 表连接条件 where 查询条件;

2. 能够使用左外和右外连接进行多表查询

   左外: select * from 左表 left outer join 右表 on 表连接条件 where 查询条件; 满足条件的数据显示,并且左表不满足条件的数据也显示

   右外: select * from 左表 right outer join 右表 on 表连接条件 where 查询条件; 满足条件的数据显示,并且右表不满足条件的数据也显示

3. 能够使用子查询

   一个查询语句的结果作为另一个查询语句的一部分

   SELECT * FROM 表名 WHERE 字段名=(SELECT MAX(字段名) FROM 表名);

   先执行()里面的子查询

4. 能够理解多表查询的规律

   多表查询3步骤:

   1. 明确查询哪些表
   2. 明确表连接条件
   3. 后续查询

5. 能够理解事务的概念

   由多条SQL语句组成一个功能,这多条SQL语句形成一个事务

   事务中的多条SQL语句要么都执行,要么都不执行,保证原子性

6. 能够说出事务的原理

   1. 用户登录数据库,会创建一个临时事务日志文件

   2. 普通的SQL执行,直接作用到数据库上

   3. 开启事务,操作机制发生变化

   4. 执行的SQL语句会保存到临时事务日志文件中

   5. 提交事务,临时日志文件中的SQL语句就会作用到数据库上

   6. 回滚事务,直接清空临时日志文件的内容

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5dYG4K5U-1618794757360)(F:/内网通/张平老师/day17(MySQL第三天)]/资料/img/事务19.png)

7. 理解MySQL索引的作用

   提高查询效率

视图

创建视图、 删除视图、 查看视图

创建视图: CREATE OR REPLACE VIEW 视图名称 AS 查询语句;
删除视图: DROP VIEW IF EXISTS 视图名称;
查看视图: SHOW TABLES;
使用视图: SELECT * FROM 视图名;
修改视图：ALTER VIEW 视图名称 AS 查询语句;

内置函数

MySQL中字符串函数如下:
SELECT CHAR_LENGTH(s) AS 长度;-- 返回字符串 s 的字符数
SELECT CONCAT(s1,s2...sn);-- 字符串 s1,s2 等多个字符串合并为一个字符串
SELECT LOWER(s);-- 将字符串 s 的所有字母变成小写字母
SELECT UPPER(s);-- 将字符串转换为大写
SELECT SUBSTR(s, start, length)-- 从字符串 s 的 start 位置截取长度为 length 的子字符串，从1开始计数
SELECT TRIM(s)-- 去掉字符串 s 开始和结尾处的空格
SELECT REPLACE(字符串, 源字符串,新字符串)-- 将字符串中的源字符串换成新的字符串

MySQL中数学函数:
SELECT RAND();-- 返回 0 到 1 的随机数
SELECT ROUND(小数, 保留几位)-- 四舍五入保留几位小数

MySQL日期函数:
SELECT ADDDATE(date, n);-- 计算起始日期 date 加上 n 天的日期
SELECT CURDATE();-- 返回当前日期
SELECT dateDiff(d1,d2);-- 计算日期 d1->d2 之间相隔的天数
SELECT NOW()-- 返回当前日期和时间
SELECT YEAR(日期)-- 获取指定日期的年份

高级函数CASE和IF

CASE

-- case表达式语法1
select 字段名1, 字段名2, case 字段名
	when 值1 then 返回的值
	when 值2 then 返回的值
	...
	else
                 上面都不符合返回的值
    end  列名
  from 表名;
  
-- case表达式语法2
select 字段名1, 字段名2, case 
	when 判断条件1 then 返回的值
	when 判断条件1 then 返回的值
	...
	else
                 上面条件都不成立返回的值
    end
  from 表名;
  
-- case表达式功能
实现分支条件判断，与java的switch结构类似， 
当字段的值与when的值匹配时返回 then 后面值， 都不符合返回else的值

IF高级函数

IF函数也是用于条件判断, 语法如下

IF(条件, '条件成立返回的值', '条件不成立返回的值')
IFNULL(xxx, 0)-- xxx为null时，使xxx变为0

存储过程

-- 注意： mysql中默认结束语句使用; 但是创建一个存储过程出现了多次; 时候mysql就不知道你的函数的范围在哪里
-- 所以我们在定义函数或者是存储过程的时候一定要修改默认的结束符号。 delimiter 关键字就可以修改结束符号
-- 这个语句则代表了mysql的结束符号以及变成$
DELIMITER $  
CREATE PROCEDURE 存储过程名()
BEGIN
  -- 多条SQL语句
END $
DELIMITER ;  -- 把MySQL的结束符号修改回;

-- DELIMITER

-- 该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，MySQL是否可以执行了。

-- 默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，MySQL将会执行该命令。

CALL 存储过程名称();-- 调用存储过程
DROP PROCEDURE 存储过程名称；-- 删除存储过程
SHOW PROCEDURE STATUS;-- 查询存储过程的状态信息
SHOW CREATE PROCEDURE 存储过程名;-- 查询某个存储过程的定义

存储过程编程-变量:
DECLARE 变量名 类型 [默认值];-- DECLARE定义变量,通过DECLARE可以定义一个局部变量，该变量的作用范围只能在BEGIN…END块中
SET 变量名 = 值;-- SET给变量赋值

-- IF关键条件判断
IF 条件 THEN 代码
ELSEIF 条件 THEN 代码
ELSE 代码
END IF;

存储过程编程-参数和返回值:
CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT] 参数名 参数类型)
...
IN :  该参数可以作为输入，也就是需要调用方传入值 , 默认
OUT:  该参数作为输出，也就是该参数可以作为返回值
INOUT: 既可以作为输入参数，也可以作为输出参数
-- 调用的时候输出参数需要使用一个类似于变量的内容去接收即可。
-- @xxx: 在变量名称前面加上“@”符号，叫做用户会话变量，表示本次会话只要不关闭连接,这个变量都可以使用。

存储过程编程-循环:
-- WHILE循环
WHILE 条件 DO
	循环体;
END WHILE;

-- REPEAT循环
-- 有条件的循环控制语句，当满足条件的时候退出循环 。WHILE是满足条件才执行，REPEAT是满足条件就退出循环。
REPEAT
	代码;
UNTIL 条件
END REPEAT;

REPEAT当UNTIL的条件为true结束了循环

-- LOOP语句
-- LOOP无限循环，通常可以使用 LEAVE 结束LOOP循环
[begin_label:] LOOP
  if 结束循环的条件 then 
    leave 标签名
  end if; 
  循环体;
END LOOP [end_label];
-- [begin_label:]为循环前缀名，当使用leave关键字退出循环时要设置这个名字
-- LEAVE语句:用来从标注的流程构造中退出，通常和 BEGIN ... END 或者循环一起使用。

游标/光标

声明游标：

DECLARE 游标名字 CURSOR FOR 查询语句;

OPEN 游标

OPEN 游标名字;

FETCH 游标：

FETCH 游标名字 INTO 变量名1 ,变量2...

CLOSE 游标：

CLOSE 游标名字;

函数

函数与存储存储过程的区别在于，存储过程不能有返回值，函数可以返回值。

语法结构

CREATE FUNCTION function_name([param type ... ])
RETURNS 返回值类型
BEGIN
...
END;

函数和存储过程的区别:

关键字不一样,函数使用FUNCTION,存储过程使用procedure

函数有返回值类型: returns 类型

调用方式不同,存储过程使用call 调用, 函数使用 select

总结

1. 会使用mysql字符串函数

   char_length: 获取字符串的长度

   concat: 拼接字符串

   substr: 截取子字符串

   trim: 去掉字符串前后空格

2. 会使用mysql日期函数

   adddate(date, n): 在date基础上增加n天

   datediff(date1, date2): date1-date2的天数

   curdate(): 获取当前日期(yyyy-MM-dd)

   curtime(): 获取当前时间(HH:mm:ss)

   now(): 获取当前日期时间 (yyyy-MM-dd HH:mm:ss)

   year(date): 获取日期中的年份

   month(date): 获取日期中的月份

   SELECT 后面写什么就会打印什么

   SELECT 函数(); 打印函数的调用结果

JDBC

JDBC会用到的包

1. java.sql：JDBC访问数据库的基础包
2. javax.sql： JDBC访问数据库的扩展包
3. 数据库的驱动，各大数据库厂商来实现

JDBC四个核心对象

这几个类都是在java.sql包中

1. DriverManager: 用于注册驱动
2. Connection: 表示数据库的连接
3. Statement: 执行SQL语句的对象
4. ResultSet: 结果集或一张虚拟表

注册数据库驱动

Class.forName("com.mysql.jdbc.Driver");

获取数据库连接

//调用方法：DriverManager.getConnection(url, user, password); 传入对应参数即可
static Connection getConnection(String url, String user, String password) 
连接到给定数据库 URL ，并返回连接。
String url：连接数据库的URL，用于说明连接数据库的位置
String user：数据库的账号
String password：数据库的密码

通过JDBC连接mysql的URL写法

jdbc:mysql://localhost:3306/day20
如果连接的是本机端口号是3306,可以省略为:
jdbc:mysql:///day20

获取Statement对象

//在java.sql.Connection接口中有如下方法获取到Statement对象
Statement createStatement() 
创建一个 Statement 对象来将 SQL 语句发送到数据库

JDBC实现对单表数据增、删、改

使用Statement对象来执行SQL语句对数据库进行增、删、改、查。

Statement的API介绍

boolean execute(String sql)
用执行任何SQL语句,如果是查询返回true,如果不是查询语句返回false; 通常不用

int executeUpdate(String sql)
用于执行增删改等语句; 返回影响的行数

ResultSet executeQuery(String sql)
用于执行查询语句; 返回查询到的结果集

executeQuery：用于执行查询SQL

executeUpdate：用于执行除查询外的SQL

ResultSet的原理

1. ResultSet内部有一个指针，记录获取到哪行数据
2. 调用next方法, ResultSet内部指针会移动到下一行数据
3. 我们可以通过ResultSet得到一行数据 getXxx得到某列数据

通过ResultSet取数据

while (rs.next()) {
    Xxx 值 = rs.getXxx("字段名");
}

JDBC事务

Connection接口中与事务有关的方法

void setAutoCommit(boolean autoCommit) throws SQLException;
false：开启事务， ture：关闭事务

void commit() throws SQLException;
提交事务

void rollback() throws SQLException;
回滚事务

JDBC中与事务相关的API

在Connection接口中
setAutoCommit(false) 开启事务
commit() 提交事务
rollback() 回滚事务

编写JDBC工具类步骤

1. 将固定字符串定义为常量
2. 在静态代码块中注册驱动
3. 提供一个获取连接的方法static Connection getConneciton();
4. 定义关闭资源的方法close(Connection conn, Statement stmt, ResultSet rs)
5. 重载关闭方法close(Connection conn, Statement stmt)

登录案例核心思路

1.数据库保存一些账号密码
2.让用户输入账号密码
3.根据用户输入的账号密码去数据库查询
4.如果查到数据,登录成功
5.如果查询不到数据,登录失败

SQL注入

用户输入的内容和SQL语句进行拼接,改变的SQL语句的原本的含义

PreparedStatement预编译对象

1. prepareStatement()会先将SQL语句发送给数据库预编译。PreparedStatement会引用着预编译后的结果。可以多次传入不同的参数给PreparedStatement对象并执行。减少SQL编译次数。
2. 安全性更高，没有SQL注入的隐患。
3. 提高了程序的可读性。

获取PreparedStatement

在java.sql.Connection有获取PreparedSatement对象的方法

PreparedStatement prepareStatement(String sql)
会先将SQL语句发送给数据库预编译。PreparedStatement对象会引用着预编译后的结果。

"SELECT * FROM user WHERE name=? AND password=?;"; 参数化的SQL ?表示占位符,将来会传递具体值

PreparedStatement的API介绍

在java.sql.PreparedStatement中有设置SQL语句参数，和执行参数化的SQL语句的方法

1. void setDouble(int parameterIndex, double x) 将指定参数设置为给定 Java double 值。
   

2. void setFloat(int parameterIndex, float x) 将指定参数设置为给定 Java REAL 值。 
   

3. void setInt(int parameterIndex, int x) 将指定参数设置为给定 Java int 值。
   

4. void setLong(int parameterIndex, long x) 将指定参数设置为给定 Java long 值。 
   

5. void setObject(int parameterIndex, Object x) 使用给定对象设置指定参数的值。  
   

6. void setString(int parameterIndex, String x) 将指定参数设置为给定 Java String 值。 
   

7. ResultSet executeQuery()
   在此 PreparedStatement 对象中执行 SQL 查询，并返回该查询生成的ResultSet对象。 
   

8. int executeUpdate() 
   在此 PreparedStatement 对象中执行 SQL 语句，该语句必须是一个 SQL 数据操作语言DML语句，比如 INSERT、UPDATE 或 DELETE 语句；或者是无返回内容的 SQL 语句，比如 DDL 语句。
   

PreparedSatement使用步骤

1. 获取连接
2. 编写SQL语句，未知内容使用?占位："SELECT * FROM user WHERE name=? AND password=?;"; 参数化的SQL
3. 获得PreparedStatement对象
4. 设置实际参数
5. 执行参数化SQL语句
6. 关闭资源

PreparedSatement如何设置？的参数

setXxx(第几个问号, 具体值);

PreparedSatement如何执行SQL

ResultSet executeQuery(); 执行查询SQL
int executeUpdate(); 执行除查询外的SQL

PreparedSatement实现增删改方法

int executeUpdate();

不是任何地方都可以写?,字段值不确定的时候才可以写?

PreparedSatement使用步骤

1. 编写SQL语句，未知内容使用?占位
2. 获得PreparedStatement对象
3. 设置实际参数
4. 执行参数化SQL语句
5. 关闭资源

将SQL中的账号密码使用?占位

PreparedSatement查询数据

实现步骤

1. 定义Employee类
2. 执行SQL语句，得到ResultSet
3. 创建一个集合用于保存所有的员工对象
4. 每次循环将一条记录存放到一个员工对象中
5. 把员工对象放到集合中
6. 关闭资源

连接池

连接池的原理

1.连接池启动时,准备一批连接
2.当用户需要使用连接时,从连接池中取出一个连接
3.当用户使用完毕,将连接重新放回连接池中

连接池的好处

连接池中的连接可以重复使用,减少连接的创建,降低资源的消耗

C3P0连接池

C3P0常用的配置参数解释

参数	说明
initialPoolSize	连接池刚启动时，连接池内包含的连接数量
maxPoolSize	连接池中最多可以放多少个连接
checkoutTimeout	连接池中没有连接时最长等待时间
maxIdleTime	连接池中的空闲连接多久没有使用就会回收。默认是0，0表示不回收

API介绍

com.mchange.v2.c3p0.ComboPooledDataSource类表示C3P0的连接池对象，常用2种创建连接池的方式：
1.无参构造，使用默认配置
2.有参构造，使用命名配置

ComboPooledDataSource类API

public ComboPooledDataSource()
无参构造使用默认配置（使用xml中default-config标签中对应的参数）

public ComboPooledDataSource(String configName)
有参构造使用命名配置（configName：xml中配置的名称，使用xml中named-config标签中对应的参数）

public Connection getConnection() throws SQLException
从连接池中取出一个连接

使用步骤

1. 导入jar包c3p0-0.9.5.2.jar和mchange-commons-java-0.2.12.jar
2. 复制配置文件c3p0-config.xml放在src目录下，配置对应参数
3. 创建连接池对象ComboPooledDataSource
4. 从连接池中获取连接对象
5. 使用连接对象操作数据库
6. 关闭资源，将连接还回连接池中

注意事项

C3P0配置文件名称必须为c3p0-config.xml
C3P0命名配置可以有多个

使用配置文件方式好处

只需要单独修改配置文件，不用修改代码

多个配置的好处

1. 可以连接不同的数据库：db1,db2
2. 可以使用不同的连接池参数：maxPoolSize
3. 可以连接不同厂商的数据库：Oracle或MySQL

Druid连接池

Druid常用的配置参数

参数	说明
initialSize	刚启动连接池时,连接池中包含连接的数量
maxActive	连接池中最多可以放多少个连接
maxWait	获取连接时最大等待时间，单位毫秒

API介绍

com.alibaba.druid.pool.DruidDataSourceFactory类有创建连接池的方法

public static DataSource createDataSource(Properties properties)
创建一个连接池，连接池的参数使用properties中的数据

使用步骤

1. 导入druid-1.0.0.jar的jar包

2. 复制druid.properties文件到src下，并设置对应参数

3. 加载properties文件的内容到Properties对象中

4. 创建Druid连接池，使用配置文件中的参数

5. 从Druid连接池中取出连接

6. 执行SQL语句

7. 关闭资源

编写连接池工具类

编写连接池工具类步骤

1. 声明静态连接池成员变量
2. 创建连接池对象
3. 返回连接池中的连接
4. 定义close关闭资源