MySQL
01 数据库分类
关系型数据库：通过表和表之间，行和列之间的关系进行数据的存储，SQL
- MySQL
- Oracle
- SqlServer
- DB2
- SQLlite
非关系型数据库：对象存储，通过对象的自身的属性来决定，NoSQL(not only)
- Redis
- MongDB
02 安装MySQL
1.建议不要用exe安装，卸载不干净。用zip
2.放到一个software文件夹或者environment
3.配置环境变量
4.配置my.ini
[mysqld]
#目录一定要换成自己的
basedir=D：\Environment\mysql
datadir=D：\Environment\mysql\data\
port=3306
skip-grant-tables
5、启动管理员模式下的CMD，运行所有的命令
6、安装mysql服务
7、初始化数据库文件
可视化工具：Sqlyog
03 MySQL的常用命令行
mysql -u root -p123456； --连接数据库
update mysql·user set authentication_string=password('123456'）where user='root' and Host= 'localhost'; --修改用户密码
flush privileges; --刷新权限
-- 所有的语句都使用；结尾
Showdatabases; --看所有的数据库
mysql>use school -- 切换数据库 use 数据库名
Database changed
show tables; -- 看数据库中所有的表
describe student; --显示数据库中表结构信息
create database westos；-- 创建一个数撫库
exit; -·退出连接
-- : 单行注释(SQL的本来的注释）
SHOW CREATE DATABASE school 一一查看创建数据库的语句
SHOW CREATE TABLE student 一一查看student数据表的定义语句
DESC student ——显示表的结构
-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
-- 修改表的字段（重命名，修改约束！）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) --修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1) --字段重名
-删除表的字段
ALTER TABLE teacher1 DROP age1
所有的创建和删除操作尽量加上判断，以免报错
SELECT VERSION() 查询系统版木（函数）
SELECT 100*3-1 AS 计算结果 用来计算（表达式）
select @@auto_increment_increment 查询自增的步长（变量）
数据总数/页面大小
| 第一页 | limit0，5 | （1-1）*5,5 |
|---|---|---|
| 第二页 | limit5，5 | （2-1）*5,5 |
| 第三页 | limit10，5 | （3-1）*5,5 |
| 第n页 | limit0，5 | (n-1)*pageSize,pageSize |
查询语句的语法：
select 去重 要查询的字段 from表（注意：表和字段可以取别名）
xxx join 要连接的表 on 等值判断
where（具体的值，子查询语句）
Group By（通过哪个字段来分组）
having（过滤分组后的倍息，条件和where是一样的，位置不同）
Order By（通过哪个字段排序）[升序/降序]
Limit startlndex,pagesize
一mysql是默认开启事务自动提交的
SET autocommit = 0 /*关闭*/
SET autocommit = 1 /*开启（默认的）*/
一手动处理事务
SET autocommit = 0 一关闭自动提交
—事务开启
START TRANSACTION ——标记一个事务的开始，从这个之后的sql都在同一个事务内
INSERT XX
INSERT XX
一提交：持久化（成功！）
COMMIT
回滚：回到的原来的样子（失败！）
ROLLBACK
一事务结束
SET autocommit = 1 —开启自动提交
了解
SAVEPOINT 保存点名 ——设置一个事务的保存点
ROLLBACKTOSAVEPOINT 保存点名 一一回滚到保存点
RELEASESAVEPOINT 保存点名，一一撤销保存点
——模拟转账：事务
SET autocommit = 0; 一关闭自动提交
START TRANSACTION; 一开启一个事务
UPDATE account SET money=money - 500 WHERE name ='A' ——A减500
UPDATE account SET money=money+500 WHERE name ='B' ——B加500
COMMIT; 一提交事务
ROLLBACK; —回滚
SETautocommit = 1; 一恢复默认值
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引（索引名）列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`（`studentName`）
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student;
一非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName）AGAINST（'刘'）
一插入100万数据
DELIMITER$$ -- 写函数之前必须要写
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000
DECLARE i INT DEFAULT 0；
WHILE i < num DO
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT（'用户',i）,'24736743@qq.com',CONCAT('18',FLOOR(RAND()*（(99999)));
SET i = i+1
END WHILE;
END;
SELECT mock_data();
-- 创建索引
-- CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user('name'）；
-创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER kuangshenIDENTIFIEDBY'123456'
-修改密码〈修改当前用户密码）
SET PASSWORD =PASSWORD（'123456'）
-修改密码〈修改指定用户密码）
SET PASSWORD FOR kuangshen=PASSWORD（'123456'）
_重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER kuangshen TO kuangshen2
-用户授杈 ALL PRIVILEGES 全部的杈限，库．表
GRANT ALL PRIVILEGES ON *.* TO kuangshen2
_查询杈限
-查看指定用户的权限
SHOW GRANTS FOR kuangshen2
SHOW GRANTS FOR root@iocaihost
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2
-- 删除用户
DROP USER kuangshen2
04 数据库定义语言
DDL定义
DML操作
DQL查询
DCL控制
1、创建数据库
（CREATE DATABASE [IF NOT EXISTS] westos
2、删除数据库
DROP DATABASE [IF EXISTS] westos
3、使用数据库
-tab键的上面，如果你的表名或者字段是一个特殊字符，就需要带
USE school
4、查看数据库
SHOW DATABASES – 查看所有的数据库
5、SQL操作符
05 MySQL的列数据类型
数值
- tinyint 十分小的数据——1个字节
- smallint 较小的数据——2个字节
- mediumint 中等大小的数据——3个字节
- int 标准的整数——4个字节（常用的）
- bigint 较大的数据——8个字节
- float 浮点数——4个字节
- double 浮点数——8个字节（精度问题！）
- decimal 字符串形式的浮点数 金融计算的时候，一般是使用decimal
字符串
- char 字符串 固定大小的0~255
- varchar 可变字符串 0~65535 常用的变量String
- tinytext 微型文本 2^8-1
- text 文本串 2^16·1 保存大文本
时间日期
java.utiI.Date
- date YYYY-MM-DD，日期格式
- time HH:mm:ss时间格式
- datetime YYYY-MM-DDHH：mm：ss 最常用的时间格式
- timestamp 时间戳，1970.1.1 到现在的毫秒数！也较为常用！
- year 年份表示
NULL
- 没有值，未知
- 注意，不要使用NULL进行运算，结果为NULL
06 数据库的字段属性
Unsigned：
- 无符号的整数
- 明了该列不能声明为负数
zerofill：
- 0填充的
- 不足的位数，使用0来填充，int（3）5 005
自增：
- 通常理解为自增，自动在上一条记录的基础上+1（默认）
- 通常用来设计唯一的主键、index,必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空 NULL not null
- 假设设置为not null，如果不给它赋值，就会报错
- null ,如果不填写值，默认为null
07 数据库引擎
INNODB：默认使用
MYISAM：早些年使用的
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，约为2倍 |
常规使用操作：
MYISAM节约空间，速度较快
INNODB安全性高，事务的处理，多表多用户操作
在物理空间存在的位置
MySQL引擎在物理文件上的区别：
- lnnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
- MYISAM对应文件
- *.frm——表结构的定义文件
- *.MYD——数据文件(data)
- *.MYI——索引文件(index)
设置数据库表的字符集编码：CHARSET=utf8
不设置的话，会是mysql默认的字符集编码、（不支持中文！）
MySQL的默认编码是Latin1，不支持中文
08 MySQL常用函数
数学运算
SELECT ABS（一8） 绝对值
SELECT CEILING（9．4） 向上取整
SELECT FLOOR（9·4） 向下取整
SELECT RAND() 返回一个0～1之间的随机数
SELECT SIGN（10） 判断一个数的符号0一0，负数返回一1，正数返回1
字符串函数
SELECT CHARLENGTH（‘即使再小的帆也能远航’） 字符串长度
SELECT CONCÄT(‘我’，‘爱’，‘你们’） 拼接字符串
SELECT INSERT(‘我爱编程helloworld’,1，2，‘超级热爱’） 查询，从某个位置开始替换某个长度
SELECT LOWER（‘KuangShen’） 小写字母
SELECT UPPER(‘Kuangshen’） 大写字母
SELECT INSTR(‘kuangshen’，‘h’） 返回第一次出现的子串的索引
SELECT REPLACE（‘狂神说坚持就能成功’) 替换出现的指定字符串
SELECT SUBSTR（‘狂神说坚持就能成功’，4，6） 返回指定的子字符串（源字符串，截取的位置，截取的长度）
SELECT REVERSE（‘清晨我上马’） 反转
时间和日期函数
SELECT CURRENTDATE() 获取当前日期
SELECT CURDATE() 获取当前日期
SELECT NOW() 获取当前的时间
SELECT LOCALTIME() 本地时间
SELECT SYSDATE() 系统时间
SELECT YEAR(NOW() ）
SELECT MONTH(NOW() ）
SELECT DAY（NOW() ）
SELECT HOUR（NOW() ）
SELECT MINUTE(NOW() ）
SELECT SECOND（NOW() ）
聚合函数
统计表中的数据想查询一个表中有多少个记录，COUNT(*)
SELECT COUNT（BornDate）FROM student; count(字段），会忽略所有的null值
SELECT COUNT（）FROM student; count（），不会忽略null值，本质计算行数
SELECT COUNT（1）FROM result; count（1），不会忽略忽略所有的null值本质计算行数
MD5加密函数
insert into user(id,pwd) values(1,MD5(‘123456’));
SELECT * FROM USER WHERE PWD=MD5(‘123456’);
09 事务 ACID原则及隔离级别
数据库事务特性ACID
- 原子性(Atomicity)：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- 要么都成功，要么都失败
- 一致性(Correspondence)：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
- 事务前后的数据完整性要保证一致
- 隔离性(Isolation)：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
- 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离。
- 持久性(Durability)：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
- 事务一旦提交则不可逆，被持久化到数据库中
数据库事务隔离级别
READ UNCOMMITTED（读未提交数据）：允许事务读取未被其他事务提交的变更数据，会出现脏读、不可重复读和幻读问题。
READ COMMITTED（读已提交数据）：只允许事务读取已经被其他事务提交的变更数据，可避免脏读，仍会出现不可重复读和幻读问题。
REPEATABLE READ（可重复读）：确保事务可以多次从一个字段中读取相同的值，在此事务持续期间，禁止其他事务对此字段的更新，可以避免脏读和不可重复读，仍会出现幻读问题。
SERIALIZABLE（序列化）：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，可避免所有并发问题，但性能非常低。
REPEATABLE READ为默认事务隔离级别。
ISOLATION_REPEATABLE_READ隔离级别下，对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，这种隔离级别可以阻止脏读和不可重复读，但幻读仍有可能发生。
隔离级别
- ISOLATIONDEFAULT：使用后端数据库默认的隔离级别
- ISOLATIONREADUNCOMMITTED：允许读取未提交的数据（对应未提交读）。可能致读、不可重复读、幻读
- ISOLATIONREADCOMMITTED：允许在一个事务中读取另一个已经提交的事务中的数据（对应已提交读）。可以避免读，但是无法避免不可重复读和幻读
- ISOLATIONREPEATABLEREAD：一个事务不可能更新由另一个事务修改但尚未提交（回滚）的数据（对应可重复读）。可以避免读和不可重读，但无法避免幻读
- ISOLATIONSERIA凵ZABLE：这种隔离级别是所有的事务都在一个执行队列中，依次顺序执行，而不是并行（对应可序列化）。可以避免读、不可重复读、幻读。但是这种隔斋级别效率很低，因此，除非必须，否则不建议使用。
脏读，指一个事务读取了另外一个事务未提交的数据。
不可重复读，在一个事务内读取表中的某一行数据，多次读取结果不同。
虚读（幻读）（这个不一定是错误，只是某些场合不对），是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
10 索引
在一个表中，主键索引只能有一个，唯一索引可以有多个
主键索引(PRIMARY KEY）
唯一的标识，主键不可重复，只能有一个列作为主腱
唯一索引(UNIQUE KEY)
避免重复的列出现，唯一索引可以重复，多个列都可以标识位唯一索引
常规索引(KEY/INDEX)
默认的，index。key关键字来设置
全文索引(FullText)
- 在特定的数据库引擎下才有，MylSAM
- 快速定位数据
索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
Hash类型的索引
Btree：lnnoDB的默认数据结构
阅读：htt[p://blog.codinglabs.org/articles/theory](file:///P://blog.codinglabs.org/articles/theory)-of-mysql-index.html
11 MySQL备份
为什么要备份：
- 保证重要的数据不丢失
- 数据转移
MySQL数据库备份的方式
- 直接拷贝物理文件
- 在Sqlyog这种可视化工具中手动导出
- 使用命令行导出mysqldump命令行使用
#mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p1234S6 school student>D:/a.sql
#mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3>物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p1234S6 school student>D:/b.sq1
mysqldump-h主机 -u用户名叩密码 数据库 数据库>物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p1234S6 school >D:/c.sq1
#导入
#登录的情况下，切换到指定的数据库
#source 备份文件
source d:/a.sql
mysql -u用户名 -p密码 库名<备份文件
12 数据库设计
设计数据库的步骤：（个人博客）
一、.收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 评论表
- 友链表（友链信息）
- 自定义表（系统信息，某个关键的字，或者一些主字段）key : value
- 说说表（发表心情··id…content…create-time)
二、标识实体（把需求落地到每个字段）
三、标识实体之间的关系
- 写博客：user–>blog
- 创建分类：user一>category
- 关注：user—>user
- 评论：user-user-blog
- 友链：links
为什么需要数据规范化？
1、信息重复
2、更新异常
3、插入异常：无法正常显示信息
4、删除异常：丢失有效的信息
第一范式(INF)
原子性：保证每一列不可再分
第二范式(2NF)
前提：满足第一范式
每张表只描述一件事情
第三范式(3NF)
前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
（规范数据库的设计）
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标，（成本，用户体验），数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下规范性！
- 故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
13 JDBC
数据库驱动
驱动：声卡、显卡、数据库
JDBC
SIJN公司为了简化开发人员的（对数据库的统一）操作，提供了一个(Java操作数据库的）规范，俗称 JDBC。这些规范的实现由具体的厂商去做
对于开发人员来说，我们只需要掌握JDBC接口的操作即可
java.sql
javax.sql
还需要导入 一个数据库库驱动包mysql-connector-java-5.1.47.jar
第一个JDBC程序
public class JdbcDemo{
public static void main(String[] args) throws ClassNotFoundException ,SQLException{
//1. 加载驱动（固定写法）
Class.forName("com.mysql.jdbc.Driver");
//2。用户信息和url
//useUnicode=true&characterEncoding=utf8&useSSL=true
String url = "jdbc：mysql：//localhost：33Ø6/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true"
String username = "root";
String password = "123456";
//3，连接成劝，数据库对象 connection 代表数据库
Connection connection = DriverManager.getConnection(url,username,password);
//4，执行SQL的对象 Statement 执行sql的对象
Statement statement = connection.createStatement();
//5. 执行SQL的对象 去 执行SQL，可能存在结果，查看返回结果
String sql = "SELECT * FROM users ";
ResultSet resultSet =statement.executeQuery(sql);//返回的结果集，结果集中封装了我们全部的查询出来的结果
while(resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("NAME"));
System.out.println("pwd="+resultSet.getObject("PASSWORD"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birth="+resultSet.getObject("birthday"));
}
// 6. 释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结
1、加载驱动
2、连接数据库DriverManager
3、获得执行sq|的对象Statement
4、获得返回的结果集
5、释放连接
ResultSet查询的结果集：封装了所有的查询结果
获得指定的数据类型
resultset.getObject()；//在不知道列类型的情况下使用
//如果知道列的类型就使用指定的类型
resultSet.getlnt();
resultSet.getDate()；
resultSet.getString();
resultSet.getFloat();
resultSet.getObject()；
遍历，指针
resultSet.beforeFirst()； //移动到最前面
resultSet.afterLast(); //移动到最后而
resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一行
resultSet.absolute(row)； //移动到指定行
防SQL注入：PreparedStatement对象
//PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符
//假设其中存在转义字符，比如说’会被直接转义
//区别
//使用？占位符代替参数
PreparedStatement st = null;
String sqlPre = "insert into users(id,`NAME`,'PASSWORD' 'email','birthday'）values (?,?,?,?,?)";
st=connection.prepareStatement(sqlPre); //预编译SQL，先写SQL，然后不执行
//手动给参数赋值
st.setInt(1,4);// id
st.setString(2,"qinjiang");
st.setString(3,"1232112");
st.setString(4,"24734673@qq．com");
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功！");
}
JDBC操作事务
//关闭数据库的自动提交
conn.setAutoCommit(false)；//开启事务
String sql1= "update account set money = money-100 where name = 'A' ";
st = conn.prepareStatement(sql1);
st.executeUpdate();
String sql2 = "update account set money = money+100 where name = 'B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
//业务完毕，提交事务
conn·commit(）；
System.out.print1n("成功！);
数据库连接池
数据库连接—执行完毕—释放
连接–释放十分浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
最小连接数：10
最大连接数：15
等待超时：100ms
编写连接池，实现一个接口DataSource
开源数据源实现（拿来即用）
DBCP
C3P0
Druid /ˈdruːɪd/ :阿里巴巴
使用了这些数据库连接池之后，在项目开发中就不需要编写连接数据库的代码了
DBCP 需要用到的jar包：commons-dbcp-1.4、commons·pool-1.6
C3P0 需要用到的jar包 ：c3p0·0.9.5.5、mchange-commons-java-0.2.19
结论
无论使用什么数据源，本质还是一样的，DataSource接口不会变，方法就不会变
1438
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
