MySQL快速入门（JDBC）
MySQL简版笔记
简版，省略某些内容，例如函数、运算符、7种JOIN的实现等。
利于快速学习进入JDBC之后开始学习JavaWeb和MyBatis
【目录见右侧大纲】
基本概念
数据库就是存储数据的“仓库”
字段和记录
一张表格，例如学生信息表，表头有学生姓名、学号、班级、出生日期、性别、籍贯等相关信息，这些表头就是字段
而里面每一个学生的数据内容就是记录
我们把多个记录组织在一起，就形成了一个“表”（Table），我们称之为数据表。
目前市场上常用的数 据库包括 SQL Server，MySQL、Oracle、DB2、Sybase 等。
在图形化数据库软件中运行SQL语句，运行什么就鼠标选中什么，如果直接点击运行则默认运行全部SQL语句。
数据类型
创建数据库、表的时候，什么类型适合就选用什么类型。大致分为以下四种：
bit（比特、位）是表示信息的最小单位
Byte（字节）1字节 = 8位
1Byte = 8 bit
1KB = 1024Byte
数值
| 类型 | 描述 | 字节大小 |
|---|---|---|
| tinyint | 最小的数据 | 1Byte |
| smallint | 较小的数据 | 2Byte |
| mediumint | 中等大小 | 3Byte |
| int | 标准整数 | 4Byte |
| bigint | 较大的数据 | 8Byte |
| float | 浮点数 | 4Byte |
| double | 浮点数 | 8Byte |
| decimal | 字符串形式浮点数（金融计算时必用） |
字符串
| 类型 | 描述 | 范围大小 |
|---|---|---|
| char | 固定大小的字符串 | 0~255 |
| varchar | 可变字符串 | 0~65535 |
| tinytext | 微型文本 | 2^8-1 |
| text | 文本串 | 2^16-1 |
日期时间
YYYY-MM-DD 年月日
HH:mm:ss 时分秒
| 类型 | 格式 | 描述 |
|---|---|---|
| date | YYYY-MM-DD | 日期格式 |
| time | HH:mm:ss | 时间格式 |
| datetime | YYYY-MM-DD HH:mm:ss | 最常用 |
| timestamp | 1970.1.1到现在的毫秒数 | 时间戳 |
| year | 表示年份 |
NULL
空，未知。
NULL 不等于 NULL
不要直接用NULL进行运算，跟谁运算都为NULL
如果需要跟NULL进行运算请使用 IS NULL 和 IS NOT NULL
字段属性
Unsigned
无符号的整数
声明了该列不能声明为负数
zerofill
0填充
不足的位数，使用0来填充。例如：
int(3) , 5=005
自增
默认在上一条记录+1
通常设计唯一主键
可以自定义自增的步长
非空
NOT NULL
不能为空
NULL 不填写默认为空
默认
设置默认值
例如设置性别sex不设置默认为男
必有字段
在标准项目中，以下字段必须包含。表示每一条记录存在的意义。
| 字段名称 | 描述 |
|---|---|
| id | 主键 |
| version | 乐观锁 |
| is_delete | 伪删除 |
| gmt_create | 创建时间 |
| gmt_update | 修改时间 |
约束
对表的一个或多个列的限制，以限制可以存储在该列中的值的类型。
SQL中有几种不同类型的约束，包括：
- NOT NULL
- PRIMARY KEY
- UNIQUE
- DEFAULT
- FOREIGN KEY
- CHECK
创建
创建数据库、数据表
省略命令连接和基本命令等内容。
创建数据库
设置字符编码&排序规则
CREATE DATABASE database_name;
CREATE DATABASE IF EXISTS database_name;
DROP DATABASE IF EXISTS database_name;
SELECT `user` FROM student;
# 创建一个名为 mydatabase 的数据库，并将字符集设置为 utf8mb4，排序规则设置为 utf8mb4_general_ci
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;# 创建一个名为 mytable 的表，并将字符集设置为 utf8mb4，排序规则设置为 utf8mb4_general_ci
CREATE TABLE mytable ( id INT NOT NULL, name VARCHAR(50) NOT NULL ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;# 将数据库 mydatabase 的字符集修改为 utf8mb4，排序规则修改为 utf8mb4_general_ci
ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;# 将表 mytable 的字符集修改为 utf8mb4，排序规则修改为 utf8mb4_general_ci
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE 创建数据库
IF EXISTS 即如果存在，一般用为 如果存在 则 执行 xxx。这里用于可以重复创建数据库。更多用法
DROP删除表。drop、truncate和delete的用法
CHARACTER SET指定字符集
COLLATE指定排序规则
着重号 规避关键字
ALTER DATABASE修改字符集
ALTER TABLE修改排序规则
创建表
CREATE TABLE 语句用于创建数据库中的表。
CREATE TABLE 表名称 (
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)
| 数据类型 | 描述 |
|---|---|
| integer(size) int(size) smallint(size) tinyint(size) | 仅容纳整数。在括号内规定数字的最大位数。 |
| decimal(size,d) numeric(size,d) | 容纳带有小数的数字。“size” 规定数字的最大位数。“d” 规定小数点右侧的最大位数。 |
| char(size) | 容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。 |
| varchar(size) | 容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。在括号中规定字符串的最大长度。 |
| date(yyyymmdd) | 容纳日期。 |
举例：
创建表：学号int、登陆密码varchar(20)、姓名、性别varchar(2)、出生日期datetime、家庭住址、email
CREATE TABLE IF NOT EXISTS `student` (`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', -- int类型最大位数为4，不能为空，自增，注释内容“学号”`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', -- 不能为空，默认值“匿名”，注释内容“姓名”`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',PRIMARY KEY(`id`) -- 主键
)ENGINE=INNODB DEFAULT CHARSET=UTF8
NOT NULL
- 不能为空
AUTO_INCREMENT
- 自增（默认自增步长为1）
COMMENT
- 注释，知道这一字段是干什么的，为养成好习惯必写
PRIMARY KEY
- 主键。一般一张表只有一个主键。可以写在第一行 id 的后面，但为了明显看到主键是什么，一般写在最下面。
ENGINE
- 引擎InnoDB和MyISAM的区别
- MyISAM
- InnoDB（默认使用）
引擎区别
上面引入了InnoDB和MyISAM的区别，这里不再供述。可以知道的是
- MyISAM 节约空间，速度较快
- InnoDB 安全性高，支持事务处理、外键约束、多用户操作。
物理文件的区别:
所有数据库文件都存储在data目录下，一个文件夹对应一个数据库。
MySQL引擎物理文件上的区别：
- InnoDB
- *.frm
- 上级目录的ibdate1
- MyISAM
- *.frm 表结结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
查看
SHOW CREATE DATABASE 数据库名; -- 查看创建数据库的语句
SHOW CREATE TABLE 表名; -- 查看数据表的定义语句
DESC 表名; -- 显示表结构
修改
ALTER
在已有的表中添加、修改或删除列
-- 修改表名 RENAME
ALTER TABLE 旧表名 RENAME AS 新表名
-- 添加字段（列）ADD
ALTER TABLE 要修改的表
ADD 字段名 字段数据类型ALTER TABLE teachers
ADD age INT(11)
-- 删除列 DROP
ALTER TABLE 表名
DROP COLUMN 字段名
-- 修改字段数据类型 COLUMN
ALTER TABLE 表名
ALTER COLUMN 字段名 字段数据类型-- 修改约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;
-- 重命名字段
ALTER TABLE 表名 CHANGE age age1 INT(11)
-- 删除表的字段
ALTER TABLE 表名 DROP 字段名
-- 删除表（如果存在删除）
DROP TABLE IF EXISTS 表名
CHANGE修改字段类型和约束
MODIFY用来重命名
IF EXISTS如果存在，后面的才执行，否则不执行（上面也有讲到）
两者使用时的区别：
MODIFY COLUMN用于修改表中现有列的类型；
CHANGE COLUMN用于修改列名、类型和顺序；
CHANGE COLUMN可以添加默认值和约束。
外键
数据库级别的外键，数据库过多会造成困扰（例如一些数据添加不上、不能删除等）
此处外键内容仅作了解，因为每次删除或者修改都要考虑外键约束会导致开发的时候很痛苦。
并且阿里的Java规范中有一条规定
【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
-
外键是一种索引，是通过一张表中的一列指向另一张表的主键，使得这两张表产生关联
-
减少单张表的冗余信息
-
一张表中可以有一个外键也可以有多个外键
使用外键后想要删除/更新主表的相关列会报错，因为从表正在使用。
使用外键后若想向从表中插入新列，但是外键值主表中没有，会报错，更新从表的外键值，但是值不在从表中会报错。
创建外键：
-- 方法一：对已有的表进行修改
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
REFERENCES 外表表名(主键字段名)-- 方法二：创建表时添加外键
CONSTRAINT 约束名 FOREIGN KEY(字段名) REFERENCES 主表（被引用列）
INSERT
在数据库表中插入记录
INSERT INTO table_name (column1,column2,...) VALUES (value1,value2,...);
-- column1，column2，...等表示表列的名称，而value1，value2，...等表示这些列的对应值
对于自增的字段可以省略。
插入的值要用逗号隔开，字符串要用单引号包含。
一定要确定字段和数据一一对应。
UPDATE
更新数据库表中的记录
UPDATE table_name SET column1_name = value1, column2_name = value2,...WHERE condition;
-- column1_name，column2_name，...是要更新其值的数据库表的列或字段的名称# 更新单列
UPDATE employees SET emp_name = 'Sarah Ann Connor'
WHERE emp_id = 3;-- 如果不筛选条件则会修改所有
# 更新多列
UPDATE employees
SET salary = 6000, dept_id = 2
WHERE emp_id = 5;# 条件可以是一个范围
UPDATE `student` SET `name`='未定义' WHERE id BETWEEN 2 AND 5# 可以通过多个条件定位数据
UPDATE `student` SET `name`='未定义' WHERE name='xxx' AND sex='1' age=12
WHERE条件选择（在修改、查询等操作时使用WHERE进行筛选要操作那些数据）
==BETWEEN … AND …==闭合区间选择（两边都取得到）
删除
前面有引用过删除的几种方式
-- delete
DELETE FROM `table_name`;
DELETE FROM `table_name` WHERE id=1;
-- TRUNCATE
TRUNCATE `table_name`
两者区别：
- 共同点：都能删除数据，都不会删除表结构
- 不同点：
- TRUNCATE 会重置自增列的计数，不会影响事务
- DELETE 不会影响自增（但是↓）
使用DELETE删除并重启数据库后：
使用 InnoDB 引擎，自增列计数会被清除，因为存储在内存中，断电即失
使用 MyISAM 引擎，不会清除自增列计数，因为存储在文件中。
查询
SELECT和别名
SELECT employee_id emp_id,last_name AS lname FROM employees;
-- 查询常数
SELECT '文字',123,employee_id,last_name FROM employees;
AS：别名。在表后空格加上别名，也可以用AS和双引号""不要使用单引号（MySQL也可以用，但是不严谨，不推荐）
如果别名中有空格，就必须使用双引号，不然识别别名为空格前面的。
DISTINCT
去除重复行
例如查询员工部门，有很多员工是从属相同部门的。
SELECT DISTINCT department_id;
多表去重（保留不重复的，工资重复项比部门少，可以看到不同部门的工资区间情况）
SELECT DISTINCT department_id,salary FROM employees;
DESC
显示表结构
DESCRIBE employees;
DESC employees;
例如查询一张表中的数值类型，是否能为空，有无默认值，有无约束等。
这两个关键字都是一个意思。
模糊查询LIKE
%
代表不确定个数的字符（前面和后面又0个或多个不确定的字符）
练习：查询last_name中包含字符’a’的员工信息
SELECTF last_name
FROM employees
WHERE last_name LIKE '%a%';-- % 代表不确定个数的字符（前面和后面又0个或多个不确定的字符）
WHERE last_name LIKE 'a%'; -- 查询以a开头的
练习：查询last_name中包含字符’a’ 且 包含字符’e’ 的员工信息
SELECTF last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
-- 写法2
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';
_
一个下划线代表一个不确定的字符
查询第二个字符是’a’的员工信息：
SELECTF last_name
FROM employees
WHERE last_name LIKE '__a%';-- 下划线不要有空格，不然就是以第x个字符后面有空格的条件进行查询
查询第二个字符是_下划线且第三个字符是a的员工信息
需要使用转义字符 \
SELECTF last_name
FROM employees
WHERE last_name LIKE '_\_a%';
-- 或者
WHERE last_name LIKE '_$_a%' ESCAPE '$';
REGEXP
(正则表达式)REGEXP运算符用来匹配字符串，常用通配符：
‘^’ 匹配以某字符为开头的字符串
‘$’ 匹配以某字符为结尾的字符串
‘.’ 匹配任何一个单字符
‘[ ]’ 匹配在方括号内的任何字符。例如匹配a或b或c写作[abc]，规定范围用-，例如匹配任何字母写作[a-z]，匹配任何数字写作[0-9]
‘*’ 匹配零个或多个在它前面的字符。例如 x* 匹配任何数量的x字符，[0-9]* 匹配任何数量的数字，而*匹配任何数量的任何字符
多表查询
将不同表的列组合成一张新的表给我们看，那么这表跟表之间需要有关联，不然内容对不上。
如果我们要查询员工表的的员工id和部门表的部门名称：
SELECT employee_id,department_name
FROM employees,departments
# 两个表的连接条件
WHERE employees.`department_id` = departments.department_id;
如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表：
SELECT employee_id,department_name,departments.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;
建议：从SQL优化的角度，建议多表查询时，每个字段前面都指明所在的表：
SELECT employees.employee_id,departments.department_name,departments.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;
可以观察到，重复的表明太多太长，可读性变差，可以给SELECT和WHERE中使用的表别名：
# 如果给表起了别名，一旦在SELECT和WHERE中使用了表明的话，则必须使用表的别名，而不能再使用表的原名。
# 按SQL的执行顺序即可理解。从FROM开始程序使用别名了。
SELECT emp.employee_id,dept.department_name,dept.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;
如果有n个表实现多表查询，则至少需要n-1个连接条件
练习：查询员工的employees_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;
非等值连接
查询员工姓名、工资和工资对应的等级
SELECT e.lst_name,e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= `j.highest_sal`;
-- 因为工资跟工资等级没有对应的连接，不能像别的查询有一一对应的连接条件，但是可以寻找等级对应的工资区间进项查找。
#WHERE e.`salary` BETWEEN j.`lowest_sal` AND `j.highest_sal`;
自连接
查询员工id、员工姓名以及其管理者的id和姓名
SELECT emp.employee_id,emp.last_name,emp.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.`manger_id` = mgr.`employee_id`;
员工有员工id和他的上级也就是他的领导的id，而他的领导也是员工，所以也有员工id。而员工领导的id跟员工id就是一一对应的连接条件。虽然是一张表，但是把它想象成两张表，一张是员工表，一张是管理者表，管理者表的管理者id跟员工表的员工id就是连接条件。像这种在一张表内进行查询有连接条件的就称之为自连接。
内连接
SELECT e.employee_id,d.department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;
查询员工所在的部门，其中员工表的部门id和部门表的id成连接关系，这样的查询就是内连接。
只把有连接条件的查出来了，没有连接条件的被舍去（交集）。这里没有连接条件的就是老板，他没有部门编号，还有可能有些部门刚成立还没有人，但是结果没有体现出来。
内连接：合并具有同一列或者两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。
而想查到全部数据，就需要用到
外连接
外连接分为两个，左外连接 和 右外连接。
想象两个重合的圆组成的交集图案，中间重合的部分就是两张表共有的部分为内连接，而两边，左边就是左表特有的跟右表没有共同关系的部分，这就是左外连接，右外连接同理。
而这三个部分全都要就叫 满外连接。
左外连接：两个表在连接的过程中除了返回满足连接条件的行以外还返回左表中不满足的行。（右外连接同理）。
练习：查询 所有 的员工的last_name,department_name信息。
SQL92语法实现外连接
（SQL92就是92年提出的标准规范，主要就是92和99，也分别叫做SQL-2 和 SQL-3标准）
SELECT e.employee_id,d.department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id(+);
这个加号+的意思就是哪个表数据没查出来少了就加回来。但是MySQL不支持这个语法。
JOIN … ON
SQL99语法使用 JOIN … ON 的方式实现多表查询。这种方式也能解决外连接的问题。MySQL支持。
ON ：只能和JOIN一起使用，只写关联条件。
SQL99语法实现内连接
SELECT last_name,department_name
FROM employees e INNER JOIN departments d -- 已经是内连接了，inner可以省略
ON e.department_id = d.departments_id;SELECT last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.departments_id
JOIN locations l
ON d.location_id = l.location_id;
SQL99语法实现外连接
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d -- 已经是外连接了，outer可以省略
ON e.department_id = d.departments_idSELECT last_name,department_name
FROM employees e RIGHT JOIN departments d -- 右外连接
ON e.department_id = d.departments_idSELECT last_name,department_name
FROM employees e FULL JOIN departments d -- 满外连接(MySQL不支持)
ON e.department_id = d.departments_id
运算符
算术运算符
加减乘除取余（模运算）+ - * / div % mod
除法可以用斜杠/和div
取余可以用百分号%和mod
先乘除后加减，有小数（浮点数）参与运算结果保留精度。
在SQL中没有连接符，就是表示加法运算，字符串会转为数值（隐式转换）
比较运算符
假的false为0，真的ture为1
| 比较运算符 | |
|---|---|
| 等于 | = |
| 安全等于 | <=> |
| 不等于 | <> != |
| 小于 | < |
| 小于等于 | <= |
| 大于 | > |
| 大于等于 | >= |
NULL运算
安全等于<=>
为NULL而生
可以用于对NULL进行判断，两数都为NULL时返回结果1，而其中有一个数不为NULL时 返回值为0
SELECT 1 <=> NULL,NULL <=> NULL FROM DUAL;
-- 0 1
练习：查询commission_pct中为null的数据：
SELECT last_name,salary
FROM employees
WHERE commission_pct <=> null;
IS NULL 、 IS NOT NULL 、ISNULL
SELECT last_name,salary
FROM employees
WHERE commission_pct <=> NULL;-- 等同于：
WHERE commission_pct IS NULL;
WHERE ISNULL(commission_pct);
练习：查询commission_pct中 不为 null的数据：
SELECT last_name,salary
FROM employees
WHERE commission_pct IS NOT NULL; -- 或者
WHERE NOT commission_pct <=> null;
排序
ORDER-BY
对查询的数据进行排序操作
升序：ASC (ascend)
降序：DESC (descend)
例：按照 salary 从高到低的顺序显示员工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;
如果在ORDER BY后面没有指定排序方式，则默认按照升序排序
可以使用列的别名进行排序
SELECT employee_id,salary,salary * 12 AS annula_sal
FROM employees
ORDER BY annula_sal;
列的别名只能在ORDER BY中使用，不能再WHERE中使用因为：
SQL执行的顺序跟中文语序一样，先找你要查询的FROM的表中符合WHERE条件的数据，然后看你要查什么SELECT这个时候才有别名，最后才去排序。
WHERE需要声明在FROM后，ORDER BY之前
二级排序
例：显示员工信息，按照department_id的降序排列，salary的升序排列
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;
多列排序：
- 可以使用不在SELECT列表中的字段进行排序
- 进行多列排序时，首先排序第一列必须有相同值才会对第二列进行排序。
- 优先级就是先排序第一列，再排序第二列。
- 如果前面的列的值都是唯一的，那么后面的列将不会进行排序。
分页
LIMIT
返回结果太多，为方便查看的同时减轻服务器负载，而实现分页操作
例：每页显示20条记录，此时显示第一页
SELECT employee_id,last_name
FROM employees
LIMIT 0,20; -- 第0条数据，每页20条
数据是从结果集的第一行开始的，第0行是表头
例：每页显示20条记录，此时显示第二页
SELECT employee_id,last_name
FROM employees
LIMIT 20,20; -- 第20条数据，每页20条
偏移量：一页20条数据，第二页就是从第21条数据开始，所以偏移量为20
例：每页显示pageSize条记录，此时显示第pageNo页
公式：LIMIT (pageNo - 1) * pageSize,pageSize;
声明顺序：WHERE ... ORDER BY ... LIMIT
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
LIMIT 0,10;
#LIMIT 10;
LIMIT 0,10;等价于 LIMIT 10;从头开始就不用写0
严格来说是 LIMIT 位置偏移量，条目数；
练习：查询表中第32、33条数据
SELECT employee_id,last_name
FROM employees
LIMIT 31,2;
OFFSET
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;
跟上面反过来。LIMIT 显示条数 OFFSET 偏移量；
事务
将一组SQL放在一个批次中去执行，要么都成功，要么都失败
事务原则：ACID原则：原子性、一致性、隔离性、持久性。
https://blog.csdn.net/dengjili/article/details/82468576
原子性（Atomicity）：要么都成功，要么都失败
一致性（Consistency）：事务前后数据的完整性必须保持一致。
隔离性（Isolation）：互不干扰，多个并发事务之间相互隔离。
持久性（Durability）：事务一旦提交则不可逆，被持久化到数据库中。
隔离性所导致的问题：
脏读：指一个事务读取了另外一个事务未提交的数据。
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。
虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
-- MySQL 默认开启事务自动提交
SET AUTOCOMMIT = 0 /* 关闭 */
SET AUTOCOMMIT = 1 /* 开启 */-- 手动处理事务
SET AUTOCOMMIT = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开启，从这开始的SQL都在同一个事物内
INSERT XXX
INSERT XXX
-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK
-- 事务结束
SET AUTOCOMMIT = 1 -- 开启自动提交-- 保存点（类似存档）
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
转账Demo：
CREATE DATABASE shop CHARACTER SET UTF8 COLLATE utf8_general_ci
USE shopCREATE TABLE `account`(`id` INT(3) NOT NULL AUTO_INCREMENT,`name` VARCHAR(30) NOT NULL,`money` DECIMAL(9,2) NOT NULL,PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET=UTF8INSERT INTO account(`name`,`money`)
VALUES ('A',200.00),('B',10000.00)-- 模拟转账：事务
SET AUTOCOMMIT = 0;
START TRANSACTION UPDATE account SET money = monye-500 WHERE `name` = 'A' -- A减500
UPDATE account SET money = monye+500 WHERE `name` = 'B' -- B加500COMMIT; -- 提交事务
ROLLBACK; -- 回滚SET AUTOCOMMIT = 1;
执行完START TRANSACTION后的SQL语句都为一个事务，如果不执行提交，而是执行回滚，那么中间无论执行了什么SQL语句都会回滚到执行开始事务时的样子。
索引
提高MySQL的检索速度
https://blog.csdn.net/hsuehgw/article/details/130170218
- 主键索引（PRIMARY KEY）
- 唯一索引（UNIQUE KEY）
- 常规索引（KEY\INDEX）
- 全文索引（FULLTEXT）
用户管理
-- 创建用户
CREATE USER 用户名 IDENTIFIED BY '密码'
-- 修改当前用户密码
SET PASSWORD = PASSWORD('密码')
-- 修改指定用户密码
SET PASSWORD FOR 用户名 = PASSWORD('密码')
-- 修改用户名
RENAME USER 原名 TO 新名-- 用户授权
GRANT ALL PRIVILEGES ON *.* 用户名
-- ALL PRIVILEGES 除了给别人授权的权限都有
-- 可以给指定数据库的权限，防止删库跑路-- 查询权限
SHOW GRANTS FOR 用户名@主机名
SHOW GRANTS FOR root@localhost-- 撤销权限（哪些权限，在哪个库撤销，给谁撤销）
PEVOKE ALL PRIVILEGES ON *.* 用户名 -- 撤销全部权限全部库表-- 删除用户
DROP USER 用户名
备份
可以直接拷贝物理文件，也可以在图形工具中操作。
命令备份：mysqldump
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student > E:/BACKUP/a.sql# 命令行没有逗号，都是空格。上面数表名可以空格隔开写多个，也可以不写直接备份整个数据库。
导入：source
# 在登录情况下
source E:/BACKUP/a.sql
# 未登录情况下
mysql -u -p 库名 < 备份文件
数据库设计
略。
三大范式
1NF：原子性：保证每一列不可再分
2NF：每张表只描述一件事
3NF：每列数据都与主键直接相关
JDBC
Java操作数据库的规范。使用JDBC接口操纵数据库。
package com.clover.demo;
// 使用代码执行查询操作
import java.sql.*;public class TestJdbc01 {public static void main(String[] args) throws ClassNotFoundException, SQLException {Class.forName("com.mysql.jdbc.Driver");//1String url = "jdbc:mysql://localhost:3306/myemployees?useUnicode=turecharacterEncoding=utf8&useSSL=yes";String username = "root";String password = "123456";Connection connection = DriverManager.getConnection(url, username, password);//2Statement statement = connection.createStatement();//3String sql = "SELECT * FROM employees";ResultSet resultSet = statement.executeQuery(sql);//4while (resultSet.next()){System.out.println("employee_id="+resultSet.getObject("employee_id"));System.out.println("first_name="+resultSet.getObject("first_name"));System.out.println("last_name="+resultSet.getObject("last_name"));}//5resultSet.close();statement.close();connection.close();}
}
就是将图形化界面的操作 代码化，跟用鼠标点击的步骤一样 ：
- 加载驱动（驱动就是让两者之间建立联系）
- 连接数据库 DriverManager（通过驱动登陆上你的数据库）
- 获得执行SQL的对象 Statement （用这个对象来执行SQL语句）
- 获得返回结果集（跟在图形化界面看到查询过后出现的表过一样，所以只有查询才会返回结果集）
- 释放连接（不占用系统资源，并遵守先开后关原则：就像连续打开三道门，退出来时关上全部的门需要先关闭最后打开的一扇门）
DriverManager:
加载驱动：Class.forName(“com.mysql.jdbc.Driver”);
自动提交：connection.setAutoCommit();
事务提交：connection.commit();
事务回滚：commection.rollback();
URL:
jdbc:mysql://localhost:3306/myemployees?useUnicode=turecharacterEncoding=utf8&useSSL=yes
jdbc:mysql://数据库地址:端口号/数据库名?使用Unicode编码=turecharacterEncoding=utf8&使用安全连接SSL=yes
oralce数据库 – 1521
jdbc:oracle:thin:@localhost:1521:sid
Statement：执行SQL的对象
String sql = “SELECT * FROM employees”;编写SQL语句
statement.executeQuery();//查询，返回结果集 ResultSet
statement.execute();//执行任何SQL
statement.executeUpdate();//更新、插入、删除。返回受影响的行。
ResultSet 查询结果集：封装了所有的查询结果
resultSet.getObject();//在不知道什么类型的时候用//知道什么类型就用什么类型：resultSet.getString();resultSet.getInt();resultSet.getFloat();resultSet.getDate();resultSet.getBoolean();
遍历，指针
resultSet.next();//移动到下一个数据resultSet.previous();//移动到前一行resultSet.absolute(row);//移动到指定行resultSet.beforeFirst();//移动到最前面resultSet.afterLast();//移动到最后面
释放资源
(消耗资源，用完需要关闭)
resultSet.close();statement.close();connection.close();
Statment对象
从上面可以发现，执行SQL语句用的都是statment对象，如果我们有很多要执行的SQL，那每次对数据库从头到尾的连接关闭是相当繁琐的，所以将连接数据库、获得statment对象这两个步骤封装进工具类中，使用时进行调用即可（驱动只需要加载一次，在工具类中有加载）。
将数据库信息保存在单独的配置文件中：
配置文件：db.properties
driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/myemployees?useUnicode=truecharacterEncoding=utf8&useSSL=yesusername=rootpassword=123456
工具类：
package com.clover.utils;import java.io.InputStream;
import java.sql.*;
import java.util.Properties;public class JdbcUtils {private static String driver = null;private static String url = null;private static String username = null;private static String password = null;static {try {//读取数据库配置信息InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");Properties properties = new Properties();properties.load(in);driver = properties.getProperty("driver");url = properties.getProperty("url");username = properties.getProperty("username");password = properties.getProperty("password");//1、驱动只用加载一次Class.forName(driver);} catch (Exception e) {e.printStackTrace();}}// 获取连接public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url,username,password);}// 释放资源public static void release(Connection conn, Statement st, ResultSet rs){if (rs != null){try{rs.close();} catch (SQLException e) {throw new RuntimeException(e);}}if (st != null){try {st.close();} catch (SQLException e) {throw new RuntimeException(e);}}if (conn != null){try {conn.close();} catch (SQLException e) {throw new RuntimeException(e);}}}
}
工具类已经写好，需要时调用即可：
进行增删改：(都是修改数据库，用到的都是Update)
package com.clover.demo;import com.clover.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class TestInsert {public static void main(String[] args) {Connection connection = null;Statement statement = null;ResultSet resultSet = null;try {connection = JdbcUtils.getConnection();statement = connection.createStatement();String sql = "INSERT INTO employees(first_name,last_name,email,phone_number,job_id,salary,manager_id,department_id) VALUES('Clover','White','OUTLOOK','131.313.313','IT_PROG','9999','100','90')";int i = statement.executeUpdate(sql);if (i>0){System.out.println("插入成功");}} catch (SQLException e) {throw new RuntimeException(e);} finally {JdbcUtils.release(connection,statement,resultSet);}}
}
查询：
（查询在前面提到过，resultSet是用来接收结果集的，循环每一行结果在控制台打印出来）
package com.clover.demo;import com.clover.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class TestDelete {public static void main(String[] args) {Connection connection = null;Statement statement = null;ResultSet resultSet = null;try {connection = JdbcUtils.getConnection();statement = connection.createStatement();String sql = "SELECT DISTINCT department_id,salary FROM employees";resultSet = statement.executeQuery(sql);while (resultSet.next()){System.out.println("department_id:"+resultSet.getInt("department_id"));System.out.println("salary:"+resultSet.getInt("salary"));}} catch (SQLException e) {throw new RuntimeException(e);} finally {JdbcUtils.release(connection,statement,resultSet);}}
}
SQL注入
用一种很巧妙的方式将传递的参数与SQL语句进行拼接。最好的例子就是登录账户：
package com.clover.demo;import com.clover.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class SQLcark {public static void main(String[] args) {login(" 'or '1=1"," 'or'1=1");}public static void login(String name,String password){Connection connection = null;Statement statement = null;ResultSet resultSet = null;try {connection = JdbcUtils.getConnection();statement = connection.createStatement();String sql = "SELECT name,password FROM user WHERE name='"+name+"' AND password='"+password+"'";resultSet = statement.executeQuery(sql);while (resultSet.next()){System.out.println("name:"+resultSet.getString("name"));System.out.println("password:"+resultSet.getString("password"));}} catch (SQLException e) {throw new RuntimeException(e);} finally {JdbcUtils.release(connection,statement,resultSet);}}
}
login传递进来的两个参数与SQL语句拼接进行查询，看看拼接了什么好东西：
SELECT name,password FROM user WHERE name=''or '1=1' AND password=''or'1=1'
从结果可以看到，这个语句完全变了意思。变成了为空或者1=1，那结果肯定为true，where语句相当于不存在了。
不仅有这总方式，还有别的，只要保证最终条件为true即可完成注入。
所以这种方式很不安全，将采取一种相对安全的方式：
PreparedStatment对象
防止SQL注入，效率更高（本身为statment子类）。
使用PreparedStatment进行增删改：
package com.clover.demo;import com.clover.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;public class TestPre {public static void main(String[] args) {Connection connection = null;PreparedStatement preparedStatement = null;try {connection = JdbcUtils.getConnection();//先写SQL后执行。问号占位符先不赋值。String sql = "insert into user(name,password,sex) values(?,?,?)";//预编译SQL，先写SQL，然后不执行preparedStatement = connection.prepareStatement(sql);//手动给参数赋值preparedStatement.setString(1,"OneMoreThink");//1代表第一个问号的位置preparedStatement.setString(2,"123123");preparedStatement.setInt(3,1);//执行int i = preparedStatement.executeUpdate();if (i>0){System.out.println("插入成功");}} catch (SQLException e) {throw new RuntimeException(e);}finally {JdbcUtils.release(connection,preparedStatement,null);}}
}
查询：
package com.clover.demo;import com.clover.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class TestPreSelect {public static void main(String[] args) {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;//要用到它来接收结果集了try {connection = JdbcUtils.getConnection();//先写SQL后执行。问号占位符先不赋值。String sql = "select * from user WHERE id = ?";//预编译SQL，先写SQL，然后不执行preparedStatement = connection.prepareStatement(sql);//手动给参数赋值preparedStatement.setInt(1,1);//执行resultSet = preparedStatement.executeQuery();if (resultSet.next()){System.out.println(resultSet.getString("name"));System.out.println(resultSet.getString("password"));System.out.println(resultSet.getInt("sex"));}} catch (SQLException e) {throw new RuntimeException(e);}finally {JdbcUtils.release(connection,preparedStatement,null);}}
}
防止SQL注入的原理很简单：把传递进来的参数当作字符处理，并且存在转义字符和引号的都会被忽略。
JDBC事务
事务遵循ACID原则，回顾
package com.clover.demo;import com.clover.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class TestTransaction {public static void main(String[] args) {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;try {connection = JdbcUtils.getConnection();// 关闭数据库自动提交,关闭之后会自动开启事务，这一点跟写SQL不一样connection.setAutoCommit(false);String sql1 = "update account set money = money-100 where name = 'A'";preparedStatement = connection.prepareStatement(sql1);preparedStatement.executeUpdate();String sql2 = "update account set money = money=+100 where name = 'B'";preparedStatement = connection.prepareStatement(sql2);preparedStatement.executeUpdate();// 业务完毕，提交事务connection.commit();System.out.println("成功");} catch (SQLException e) {try {connection.rollback();// 如果失败则回滚事务。其实不用写，失败会自动回滚。} catch (SQLException ex) {throw new RuntimeException(ex);}throw new RuntimeException(e);}}
}
数据库连接池
数据库在进行连接到执行再到释放，整个过程是十分浪费系统资源的。
池化技术：准备一些预先的资源，过来就连接预先准备好的
假设我们准备了：
最小连接数：10
最大连接数15
等待超时：5000ms
使用连接池只需要实现一个接口：Data Source
现有开源数据源实现：
DBCP
C3P0
Druid（阿里巴巴）
使用这些开源的数据库连接池后，我们在项目中就不需要编写连接数据库的代码了。
DBCP
使用jar包：
commons-dbcp-1.4.jar
commons-pool-1.6.jar
编辑配置文件：dbcpconfig.properties
#
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myemployees?useUnicode=truecharacterEncoding=utf8&useSSL=yes
username=root
password=123456#
initialSize=10#
maxActive=50#
maxIdle=20#
minIdle=5#
maxWait=50000#
connectionProperties=useUnicode=true;characterEncoding=utf8##
defaultAutoCommit=true#
defaultTransactionIsolation=REPEATABLE_READ稍微修改以下我们之前编写的工具类：
package com.clover.utils;import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;public class JdbcUtils_DBCP {private static DataSource dataSource = null;static {try {InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");Properties properties = new Properties();properties.load(in);// 创建数据源(工程模式->创建对象）dataSource = BasicDataSourceFactory.createDataSource(properties);} catch (Exception e) {e.printStackTrace();}}// 获取连接public static Connection getConnection() throws SQLException {return dataSource.getConnection(); // 从数据源中获取连接}// 释放资源public static void release(Connection conn, Statement st, ResultSet rs){if (rs != null){try{rs.close();} catch (SQLException e) {throw new RuntimeException(e);}}if (st != null){try {st.close();} catch (SQLException e) {throw new RuntimeException(e);}}if (conn != null){try {conn.close();} catch (SQLException e) {throw new RuntimeException(e);}}}
}
然后就可以使用这种池化技术了：
package com.clover.demo;import com.clover.utils.JdbcUtils;
import com.clover.utils.JdbcUtils_DBCP;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class TestDBCP {public static void main(String[] args) {Connection connection = null;Statement statement = null;ResultSet resultSet = null;try {connection = JdbcUtils_DBCP.getConnection();statement = connection.createStatement();String sql = "SELECT DISTINCT department_id,salary FROM employees";resultSet = statement.executeQuery(sql);while (resultSet.next()){System.out.println("department_id:"+resultSet.getInt("department_id"));System.out.println("salary:"+resultSet.getInt("salary"));}} catch (SQLException e) {throw new RuntimeException(e);} finally {JdbcUtils_DBCP.release(connection,statement,resultSet);}}
}
另外的两种也是类似的方式，这种都是开源的，我们需要的时候直接使用即可。
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！