mysql

一.安装

mysql：关系型数据库管理系统(DBMS)
1.安装windows版本mysql5.7：
2.配置环境变量：Path
C:\Program Files\MySQL\MySQL Server 5.7\bin;
3.修改mysql配置文件
basedir = C:\Program Files\MySQL\MySQL Server 5.7\
datadir = C:\Program Files\MySQL\MySQL Server 5.7\data\
port = 3306
skip-grant-tables
4.启动mysql
mysqld --install
mysqld --initialize-insecure # 初始化数据文件
net start mysql

安装sqlyong 或 navicat

二.操作数据库

2.1 操作数据库

-- 创建数据库
CREATE DATABASE IF NOT EXISTS test
-- 删除数据库
DROP DATABASE IF EXISTS test
-- 使用数据库
USE `test`
-- 查看数据库
SHOW DATABASE

2.2 数据库列类型

数值

tinyint 1个字节
smallint 2个字节
mediumint 3个字节
int 4个字节（常用）
bigint 8个字节

float 4个字节
double 8个字节
decimal 字符串形式浮点数（金融计算）

字符串

char 0-255
varchar 0-65535(常用)
tinytext 2^8-1
text 2^16-1(保存大文本)

时间日期

date YYYY-MM-DD
time HH:mm:ss
datetime YYYY-MM-DD HH:mm:ss(常用)
timestamp 时间戳，1970.01.01到现在的毫秒数
year 年份

null

2.3 数据库的字段属性

unsigned 无符号
zerofill 0填充
auto increment 用来设置唯一主键，int类型
not null 必须有值
默认 设置默认值

2.4 数据库引擎

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	不支持	支持
表空间大小	较小	较大，约为2倍

INNODB：安全性高，支持事务，支持多表多用户操作

2.5 表操作

-- 创建表
CREATE TABLE IF NOT EXISTS `student`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
		`pwd` VARCHAR(20) NOT NULL DEFAULT '123' 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
-- 修改表名
ALTER TABLE `student` RENAME AS `student1`
-- 增加表字段
ALTER TABLE `student1` ADD age INT(11)
-- 修改表字段(重命名，修改约束)
ALTER TABLE `student1` MODIFY age VARCHAR(11)
ALTER TABLE `student1` CHANGE age age1 INT(1)
-- 删除表字段
ALTER TABLE `student1` DROP age1
-- 删除表
DROP TABLE IF EXISTS `student1`

-- 查看创建数据库的语句
SHOW CREATE DATABASE `school`
-- 查看创建表的语句
SHOW CREATE TABLE `student`
-- 查看表字段
DESC `student`

三. 数据管理

3.1 DML语言

-- 插入数据
INSERT INTO `student`(`name`, `pwd`, `sex`)
VALUES('张三','aaaaaaa','男'),
('李四','bbbbbbb','男'),
('王五','ccccccc','男')
-- 修改数据
UPDATE `student`
SET `name`='剑皇',sex='女'
WHERE `id`=1  
-- 删除数据
DELETE FROM <表名>
WHERE <条件>
-- 清空表
TRUNCATE <表名> 
/*
与DELETE FORM <表名>区别
1.TRUNCATE自增归零
2.TRUNCATE不影响事务
*/

3.2 DQL语言（重要）

select完整语法:
SELECT [ALL|DISTINCT]
字段
FROM
JOIN
ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

-- 查询所有表数据
SELECT * FROM `student`
-- 指定字段
SELECT `name`,`pwd` FROM `student`
-- 起别名
SELECT `name` AS '名字',`pwd` AS '密码' FROM `student`
-- 字段值拼接 CONCAT()函数
SELECT CONCAT('姓名',`name`) AS '新姓名' FROM `student`
-- 去重 DISTINCT
SELECT DISTINCT `name` FROM `student`

SELECT VERSION() -- 查询版本（函数）
SELECT 100*3-1 AS 计算结果  --做计算（表达式）
SELECT @@auto_increment_increment  --查询自增步长（变量）

-- 学员考试成绩 +1分查看
SELECT `name`, `scores`+1 AS '提分后' FROM `student`

where子句
筛选条件：
= <> > <　>= <=
模糊查询
AND OR

模糊查询
IS NULL
IS NOT NULL
BETWEEN AND
LIKE
IN

-- 查询姓刘的学生
SELECT `name` FROM `student`
WHERE `name` LIKE `刘%`
-- 查询刘后面有一个字的学生
SELECT `name` FROM `student`
WHERE `name` LIKE `刘_`
-- 查询刘后面有两个字的学生
SELECT `name` FROM `student`
WHERE `name` LIKE `刘__`
-- 查询名字中有嘉的人
SELECT `name` FROM `student`
WHERE `name` LIKE `%嘉%`
-- IN的使用
SELECT `name` FROM `student`
WHERE `id` IN (1,2,3);

多表联查
inner join：交集
left join：差集 左减右
right join：差集 右减左

SELECT s.`name` 
FROM `student` AS s
INNER JOIN `result` AS r
ON s.id = r.id
-- 自连接:表自己的两个字段连接
SELECT a.`name` AS '父', b.`name` AS '子'
FROM `studnent` AS a, `student` AS b
WHERE a.`id` = b.`pid`

排序

ORDER BY <字段> AEC	 -- DESC降序

分页

LIMIT 起始,页面大小  -- (n-1)*s, s

子查询：等价于多表联查
where <子查询语句>

分组和过滤
GROUP BY
HAVING

3.3 mysql函数

参考手册

常用函数

ABS()	-- 绝对值
CEILING() -- 向上取整
FLOOR()  -- 向下取整
RAND()   -- 0~1随机数
SIGN()   --	判断一个数的符号 0返回0  正数返回1  负数返回-1

CHAR_LENGTH()   -- 字符串长度
CONCAT(,)		-- 字符串拼接
INSERT(,,,)		-- 从某个位置开始替换某个长度
LOWER()			-- 转小写
UPPER()			-- 转大写
INSTR(,)		-- 返回第一次出现字串的索引
REPLACE(,,)		-- 字符串替换
SUBSTR(,)		-- 截取字符串
REVERSE()		-- 反转

CURRENT_DATE()	-- 当前日期
CURDATE()		-- 当前日期
NOW()			-- 当前日期时间
LOCALTIME()		-- 本地时间
SYSDATE			-- 系统时间
YEAR(NOW())		-- 年

SYSTEM_USER()	-- 当前用户
USER()			-- 当前用户
VERSION()		-- 版本

MD5()			-- MD5加密

聚合函数

COUNT()
SUM()
AVG()
MAX()
MIN()

四.事务

事务：一组sql要么都成功，要么都失败
事务原则：ACID原则：原子性、一致性、隔离性、持久性
脏读：一个事务读取了另一个事务未提交数据
不可重复读：多次读取结果不同
幻读：多次读取多一行

SET AUTOCOMMIT = 0 -- 关闭自动提交
START TRANSACTION -- 开启一个事务
-- sql1
-- sql2
COMMIT;  -- 提交事务，提交就持久化
ROLLBACK; -- 回滚
SET AUTOCOMMIT = 1; -- 恢复默认值

五.索引

索引：帮助高效获取数据的数据结构
查询的时候有索引的列会更快
底层：B+树（BBST
1.二分查找
2.维护一个索引需要使用大容量的磁盘，内存与硬盘读写速度不同，使用B树减少磁盘IO。
3.添加顺序访问指针提高区间访问性能
）

1.索引分类

• 主键索引（PRIMARY KEY） 唯一标示
• 唯一索引（UNIQUE KEY） 避免重复列
• 常规索引（KEY）
• 全文索引（FULLKey）

2.索引的使用

创建表时候给字段增加索引
创建完毕后增加索引

-- 显示所有的索引信息
SHOW INDEX FROM `student`
-- 增加一个索引
ALTER TABLE `student` ADD FULLTEXT INDEX `indexName`(`name`);
-- 分析sql执行状况
EXPLAIN <sql>

3.索引原则

大数据量、
查询频繁字段。

六.权限管理与备份

6.1. 用户管理

-- 创建用户
CREATE USER zyt IDENTIFIED BY '123456'
-- 修改当前用户密码
SET PASSWORD = password('111111')
-- 修改指定用户密码
SET PASSWORD FOR zyt = password('111111')
-- 重命名
RENAME USER zyt TO zyt02
-- 授权(库.表)授予所有权限给所有库表
GRANT ALL PRIVILEGES ON *.* TO zyt
-- 查询权限
SHOW GRANT FOR zyt
SHOW GRANT FOR root@localhost
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM zyt 

6.2 mysql备份

1.直接拷贝物理文件（物理备份）
2.可视化工具中手动导出（逻辑备份）
3.mysqldump（逻辑备份）

-- 在终端执行mysqldump操作
-- 备份
mysqldump -hlocalhost -uroot -p123456 school student >D:a.sql
mysqldump -hlocalhost -uroot -p123456 school student teacher >D:a.sql
mysqldump -hlocalhost -uroot -p123456 school >D:a.sql
-- 恢复
source xxx.sql

七.三大范式

设计一个数据库的流程
1.需求分析
2.标识实体
3.标识实体之间的关系

1.第一范式（1NF）
每一列不可再分
2.第二范式（2NF）
每张表只能有一个属性（可由主键推出）
3.第三范式（3NF）
每一列都与主键直接相关（消除传递依赖）

规范性与性能是一对矛盾体：性能相对更重要，有时会故意增加冗余字段，从多表查询变为单表查询。

八.JDBC

JDBC下载

8.1 jdbc

package com.test02;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;

/*
 *-- 测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
*/

public class TestJdbc {
	// mysql-connector-java-5.1.47
	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 user = "root";
		String password = "";
		//3. 数据库对象，连接数据库
		Connection connection = DriverManager.getConnection(url, user, password);
		//4. 执行SQL对象，Statement
		Statement statement =connection.createStatement();
		//5. 执行SQL，返回结果集
		String sql = "SELECT * FROM users";
		ResultSet resultSet = statement.executeQuery(sql);
		//statement.executeUpdate(sql); 增 改 删
		//statement.execute(sql); 任何
		
		while(resultSet.next()){
			System.out.println("id=" + resultSet.getObject("id"));
			System.out.println("id=" + resultSet.getObject("NAME"));
			System.out.println("id=" + resultSet.getObject("PASSWORD"));
			System.out.println("id=" + resultSet.getObject("email"));
			System.out.println("id=" + resultSet.getObject("birthday"));
			System.out.println("===============================");

			//getObject();
			//getString();
			//getInt();
			//getDate();
			//getFloat();
		}
		//resultSet.beforeFirst(); 移动到最前面
		//resultSet.afterLast(); 移动到最后面
		//resultSet.next(); 移动到下一个数据
		//resultSet.previous(); 移动到前一行
		//resultSet.absolude(row); 移动到指定行
		
		//释放连接
		resultSet.close();
		statement.close();
		connection.close();
	}
}

8.2 sql注入

非法字符串拼接攻击数据库
例如：username = "'or '1=1"; //会暴露所有用户名
使用PrepareStatement预编译对象防止SQL注入

//预编译
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday` values(?,?,?,?,?))";
PrepareStatement st = coon.prepareStatement(sql);
st.setInt(1,4);
st.setString(2,"qinjiang");
st.setString(3,"qinjiang");
st.setString(4,"qinjiang");
st.setDate(5,new Date(new Date.getTime));

//执行
st.executeUpdate();

8.3 操作事务

try{
//获取链接对象
//1.开始事务
conn.setAutoCommit(false);
//2.处理业务
//sql1,execute()
//sql2,execute()
//3.提交事务
conn.commit();
}catch(){
//执行失败默认回滚
}finally{
	//释放连接
}

8.4 数据库连接池

开启关闭数据库连接太耗费资源
池化技术：开启一次连接，提供服务
常用数据库连接池：DBCP、C3P0、Druid

DBCP连接池
commons-dbcp-1.4.jar
commons-pool-1.6.jar

添加配置文件dbcpconfig.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

//读取数据源dbcpconfig.properties
//创建数据源
DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
//从数据源中获取连接
java.sql.Connection connection = dataSource.getConnection();