二、Mysql从入门到跑路

一、初识mysql数据库

DBMS(数据库管理系统)
数据库的管理软件,科学有效的管理我们的数据。维护和获取数据;
基础命令
命令行登入Mysql

mysql -uroot -p

展示所有表

show tables;

查看表的信息

describe 表名;

退出mysql数据库

 exit

SQLyog是一个Mysql的图形化工具
数据库语言
数据库定义语言DDL(Data Definition Language)
数据库操纵语言DML（Data Manipulation Language）
*数据库查询语言DQL（Data Query Language）
数据库控制语言DCL（Data Control Language）

二、操作数据库（了解）

创建数据库

 create database [if not exists] 数据库名;

删除数据库

drop database [if exists] 数据库名;

查看数据库

show databases;

使用数据库

use 数据库名;

三、数据库的列类型

数值

名称	描述	大小	备注
tinyint	十分小的数据	1个字节
smallint	较小的数据	2个字节
int	标准的整数	4个字节	最常用
bigint	较大的数据	8个字节
float	浮点数	4个字节
double	浮点数	8个字节
decimal	字符串形式的浮点数		用于金融计算，解决精度问题

字符串

名称	描述	大小	备注
char	字符串固定大小	0~255
varchar	可变字符串	0~65535	常用的变量
tinytext	微型文本	$$2^8-1$$
text	文本串	$$2^{16}-1$$	保存大文本

• char字符串固定大小的0~255
• varchar可变字符串0~65535常用的变量String- tinytext
  微型文本2^8- 1
  text
  文本串
  2^16-1保存大文本

时间日期(对应java.util.Date)

• date YYYY-MM-DD，日期格式. time HH: mm: ss 时间格式
• datetime YYYY-MM-DD HH: mm: ss 最常用的时间格式
• timestamp时间戳，1970.1.1到现在的毫秒数!也较为常用!
• year年份表示

null
没有值，未知。不要进行运算

四、数据库的字段属性(重点)

Unsigned ;

无符号的整数，声明了该列不能声明为负数

zerofill:

不足的位数，使用0来填充
自增:
自动在上—条记录的基础上+1〔默认)·通常用来设计唯一的主键index，必须是整数类型。可以自定义设计主键自培的起始值和步长
非空NUll not null
假设设置为not null 。如果不给它赋值，就会报错
NuII ，如果不填写值，默认就是null
默认:
·设置默认的值

五、创建数据库表

模板

CREATE TABLE IF NOT EXISTS `school3`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(30) 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

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型[属性][索引][注释]，
......
`字段名` 列类型[属性][索引][注释]
)[表类型][字符集设置][注释]

常用命令

SHOW CREATE DATABASE school --查看创建数据库的语句
SHOW CREATE TABLE student --查看student数据表的定义语句
DESC student --显示表的结构

六、数据库引擎（科普）

INNODB:默认使用。安全性高，支持事务的处理，多表多用户操作
MYISAM：早些年使用。节约空间，速度较快。

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为2倍
在物理空间存在的位置
所有的数据库文件都存在data目录下本质还是文件的存储。
MySQL引擎在物理文件上的区别：
INNODB 在数据库表中只有一个*.frm文件，以及上级目录下的 ibdata1文件.
MYISAM对应文件

• .frm表结构的定义文件
• .MYD数据文件(data)
• .MYI 索引文件(index)
  设置数据库表的字符集编码
  方法一（推荐）：

CHARSET=utf8

不设置的话，会是mysql默认的字符集编码Latin1，不支持中文
方法二（不推荐）：
在my.ini中配置默认的编码

character-set-server=utf8

七、修改删除表

修改表名

ALTER TABLE 旧表名 RENAME AS 新表名

增加表的字段

ALTER TABLE 表名 ADD 字段名 列属性 

修改表字段约束

 ALTER TABLE 表名 MODIFY 字段名 列属性[]

字段重命名

ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]

刷除表的字段

ALTER TABLE 表名 DROP 字段名

删除表

DROP TABLE [IF EXISTS] 表名

八、外键(了解即可）

外键（foreign key，键不在自己表中),如果一张表中有一个字段(非主键)指向另外一张表中的主键，那么该字段称之为外键。MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。

模板

ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(字段名) REFERENCES 被引用表名(被引用字段名)

例子

ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`grade_id`) REFERENCES `grade`(`grade_id`)

1）引用出错主要有下面4个原因：
1、外键的引用类型不一样，主键是int外键是char
2、找不到主表中 引用的列
3、主键和外键的字符编码不一致
4、还有要建立外键的话，要先建立索引。没有建立索引也会出错
2）删除有外键关系的表的时候，必须要先删除引用别人的表（从表)，再删除被引用的表(主表)
3）以上操作是物理外键，数据库级别的外键，不建议使用!（避免数据库过多造成困扰)

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据)和列(字段)
• 我们想使用多张表的数据，想使用外键（程序去实现)

九、DML语言（背诵）

插入数据

insert into 表名([字段名1,字段2,字段3] values (‘值1'),('值2'),('值3')....

更新数据

update 表名 set 修改列1='修改值',修改列2='修改值'  [where 条件]

where条件操作符

=  、!= 、>、  <、  <=、  >=、  BETWEEN ... and....、  AND 、 OR

删除数据

DELETE FROM 表名 [WHERE 条件]

清空数据库内容

TRUNCATE  表名

delete的 TRUNCATE 区别

• TRUNCATE 重新设置自增列计数器会归零
• TRUNcATE不会影响事务

了解即可:DELETE删除,重启数据库现象：

• InnoDB自增列会重1开始(存在内存当中的,断电即失)
• MyISAM继续从上一个自增量开始(存在文件中的,不会丢失)

十、DQL（最重点）

查询指定字段

SELECT 字段 FROM 表 

给结果起一个名字AS可以给字段起别名，也可以给表起别名

SELECT  'studentNo' As 学号  FROM student As s

函数concat (a,b）拼接字符串

SELECT CONCAT('姓名: ' ,studentName)  FROM  student

查询某个字段时去除重复数据

SELECT DISTINCT 字段 FROM 表名 

数据库的列(表达式)

SELECT VERSION() #查询版本
SELECT 100*3-1 AS 计算结果 #用来计算
SELECT @@auto_increment_increment #查询自增的步长︰(变量)
SELECT `studentNo` , `studentResult` +1 AS 提分后 FROM result #学员成绩加1

数据库中的表达式：文本值,列，Null,函数，计算表达式，系统变量…

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为NULL，结果为真
IS NOT NULL	a is not null	如果操作符不为NULL，结果为真
BETWEEN	a between b and c	若a在b和c之间，结果为真
LIKE	a like b	SQL匹配，若a匹配到b，结果为真
IN	a in (a1,a2,a3)	若a在a1,a2,a3某一个中，结果为真

查询姓刘的问学(like结合%代表0-任意个字符，_代表一个字符)

SELECT  `studentName` FROM `student` WHERE StudentName LIKE '刘%'
SELECT `studentName` FROM `student` WHERE `studentName` LIKE '刘_'

查询名字中间有伟字的同学

SELECT `studentName` FROM `student` WHERE studentName LIKE '%伟%'

查询1001,1002,1003号学员

SELECT `studentName` FROM `student` WHERE `StudentNo` IN(1001,1002,1003);

查询地址为空的学生null

SELECT  `StudentName` FROM `student` WHERE address='' OR address IS NULL

联表查询 join on（难点）

查询参加了考试的同学(学号，姓名，科目编号，分数）

1）交叉连接

SELECT s.studentNO,studentName, SubjectNo,StudentResult 
FROM student AS s
INNER JOIN result AS r
ON s.studentNO = r.studentNO

2）右连接

SELECT s.studentNO,studentName, SubjectNo,StudentResult 
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNO = r.studentNO

3）左连接

SELECT s.studentNO,studentName, SubjectNo,StudentResult 
FROM student AS s
LEFT JOIN result AS r
ON s.studentNO = r.studentNO

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	从左表中返回值，即使右表中没有匹配
right join	从右表中返回值，即使左表中没有匹配

三张及以上表的联表查询样式：

SELECT s.studentNo , studentName , subjectName , studentResult FROM student s
RIGHT JOIN result AS r
ON r.studentNo = s.studentNo
INNER JOIN `subject` AS sub
ON r.subjectNo = sub.subjectNo

自连接（了解)
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可

SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目' 
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`

排序order by
desc降序，asc升序

SELECT * FROM grade
ORDER BY gradeid DESC

分页limit
语法：limit 起始下标，页的大小

SELECT * FROM grade
LIMIT 0,2

子查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句where (select * from )
例子：查询高等数学的所有考试结果（学号，科目编号，成绩）
（用联表查询也可以实现）

SELECT `studentNo`, `subjectNo` , `studentResult` FROM `result`
WHERE subjectNo =(
SELECT subjectNo FROM `subject`
WHERE subjectName ='高等数学'
)

十一、MySQL函数

数学运算

SELECT ABS(-8) #绝对值
SELECT CEILING(9.4)#向上取整
...

字符串函数

SELECT CHAR_LENGTH ('计算长度')
SELECT CONCAT('拼','接')
...

聚合函数
如：count() 、max() 、min()、avg()、sum()

查询一个表中有多少个记录，就使用这个count ()

SELECT COUNT(`phone`) FROM student  #会忽略所有null值
SELECT COUNT(*) FROM student #不会忽略null值
SELECT COUNT(1) FROM student #不会忽略null值

查询不同课程的平均分，平均分大于70。根据不同的课程分组

SELECT subjectname, AVG (studentresult) AS 平均分
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.subjectNo 
HAVING 平均分>=70

ps:在group by后需要过滤时，要使用having不能用where,条件和where一样
MD5加密（扩展了解）
主要增强算法复杂度和不可逆性

INSERT INTO grade VALUES (100,MD5('123456'))

十二、事务

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

隔离所导致的一些问题：
1）脏读
指一个事务读取了另外一个事务未提交的数据
2）不可重复读
在一个事务内读取表中的某一行数据，多次读取结果不同。(这个不一定是错误，只是某些场合不对)
3）虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致
经典参考博客

mysql是默认开启事务自动提交的

SET autocommit=0 #关闭
SET autocommit=1 #开启(默认的）

手动处理事务

SET autocomnit=0 #关闭自动提交
#事务开启
START TRANSACTION #标记一个事务的开始，从这个之后的 sql都在同一个事务内
INSERT XX
INSERT XX
#提交:持久化(成功!)
COMMIT
#回滚:回到的原来的样子(失败）
ROLLBACK
#事务结束
SET autocomnit=l #开启自动提交

了解即可

SAVEPOINT 保存点名 #设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 #回滚到保存点
RELEASE SAVEPOINT 保存点名 #撤销保存点

十三、索引

MySQL官方对索引的定义为:索引(Index）是帮助MySQL高效获取数据的数据结构。
索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引(PRIMARY KEY )：唯一的标识,主键不可重复,只能有一个列作为主键
• 唯一索引(UNIQUE KEY) ：避免重复的列出现,唯一索引可以重复,多个列都可以标识位唯一索引
• 常规索引(KEY/INDEX)：默认的,index。 key关键字来设置
• 全文索引(FullText)：在特定的数据库引擎下才有,MylSAM。快速定位数据

索引的使用
1、显示所有的索引信息

SHOW INDEX FROM student

2、增加一个全文索引(索引名)列名

ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`)

3、EXPLAIN分析sql执行的状况

EXPLAIN SELECT * FROM student #非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘') #全文索引

索引在大数据量时用处明显，在小数据时用处不大

MySQL索引背后的数据结构及算法原理，精读博客

索引原则

• 索引不是越多越好
• 不要对经常变动数据加索引·
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上!

索引的数据结构

• Hash类型的索引
• Btree：InnoDB的默认数据结构

十四、用户管理与备份

创建用户

CREATE USER  用户名 IDENTIFIED  BY  密码

修改当前用户密码

SET PASSWORD = PASSWORD(密码)

修改指定用户密码

SET PASSWORD FOR 用户名=PASSWORD(密码)

重命名

RENAME USER 旧名 TO 新名

用户授权

GRANT ALL PRIVILEGES ON *.* TO 用户名  #获得除授权权限外的所有权限

查看权限

SHOW GRANTS FOR 用户名

移除权限

REVOKE ALL PRIVILEGES ON *.* FROM 用户名

删除用户

DROP USER 用户名

备份文件

• 直接拷贝data物理文件
• 在sqlyog可视化工具中手动导出：在想要导出的表或库中右键选择备份
• 使用命令行导出:mysqldump

十五、三大范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。而通常我们用的最多的就是第一范式（1NF）、第二范式（2NF）、第三范式（3NF）

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

三大范式参考博客

范式是为了增大数据库的规范性，但有时候会为了性能会舍弃一些规范性，

十六、JDBC(重点）

SUN公司为了简化开发人员的(对数据库的统一）操作，提供了一个(Java操作数据库的)规范，俗称JDBC这些规范的实现由具体的厂商去做。对于开发人员来说只需要掌握JDBC接口的操作即可!

驱动：mysql-connector-java-5.1.47
导入步骤
1、创建lib文件夹
2、把驱动复制到lib文件夹下
3、设置Add as Library

第一个jdbc程序

import java.sql.*;

public class test01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2、url与用户信息
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding-utf8&useSSL=true";
        String username="root";
        String password="root";
        //3、Connection代表数据库
        Connection connection= DriverManager.getConnection(url,username,password);
        //4、执行SQL的对象
        Statement statement=connection.createStatement();
        //5、编写SQL
        String sql="select * from users";
        ResultSet resultSet=statement.executeQuery(sql);
        while (resultSet.next()){
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("name"));
            System.out.println(resultSet.getObject("password"));
        }
        //6、释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }

}

PreparedStatement可以防止SQL注入。效率更好!

IDEA连接数据库
可以像SQLYOG实现可视化，注意

• 导入驱动
• 调整mysql时区：在mysql中输入show variables like’%time_zone’;

数据库连接池
频繁连接，关闭十分浪费资源，所以数据库连接池可以保持开放状态以供程序可以重复的连接数据库，高对数据库操作的性能。
常见开源数据库连接池

• DBCP
• C3PO
• Druid:阿里巴巴

---

参考视频：【狂神说Java】MySQL最新教程通俗易懂