Mysql基础学习
MYSQL
INNODB 和 MYISAM的区别
常规使用操作：
- MYISAM 节约空间，速度较快
- INNODB 安全性高，事务的处理，多表多用户操作
在物理空间存在的位置
所有的数据库文件都在data目录下，本质还是文件的存储！
MySQL 引擎在物理文件上的区别
- INNODB 在数据库表中只有一个*.frm文件，以及上级目录下的 ibdata1文件
- MYISAM 对应文件
- *.frm -表结构的定义文件
- *.MYD 数据文件 (data)
- *.MYI 索引文件 (index）
设置数据库的字符集编码
CHARSET=utf8
不设置的话，会是mysql默认的字符集编码（不支持中文！）
一、事务
事务的四大特性
原子性
要么都成功，要么都失败
一致性
事务前后的数据完整性要保证一致
持久性
事务一旦提交则不可逆，被持久化到数据库中！
隔离性
事务的隔离性事多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离
隔离所导致的一些问题
1、脏读：
指一个事务读取了另外一个事务未提交的数据
2、不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
3、虚读（幻读）
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
-- mysql 是默认开启事务自动提交的
set autocommit = 0 /* 关闭 */
set autocommit = 1 /* 开启 */
-- 手动处理事务
start transaction --标记一个事务的开始，从这个之后的sql都在一个事务内
insert xxx
insert xxx
-- 提交 ： 持久化（成功！）
commit
-- 回滚 ： 回到原来的样子（失败！）
rollback
-- 事务结束
set autocommint = 1 -- 开启自动提交
-- 了解
savepoint 保存点名 -- 设置一个事务的保存点
rollback to savepoint 保存点名 -- 回滚到保存点
release savepoint 保存点名 -- 撤销保存点
模拟场景
-- 模拟转账：事务
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; -- 回滚
SET autocommit = 1; -- 恢复默认值
二、索引
在一个表中主键索引只能有一个，唯一索引可以有多个
1、索引分类
- 主键索引 （PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一个列作为主键
- 唯一索引 （UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复多个列标识为唯一索引
- 常规索引 （KEY/INDEX）
- 默认的，index，key 关键字来设置
- 全文索引 （FULLTEXT）
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据
-- 索引的使用
-- 显示所有的索引信息
SHOW INDEX FROM teacher
-- 增加一个全文索引 （索引名） 列名
ALTER TABLE school.`teacher` ADD FULLTEXT INDEX `email` (`email`) ;
-- id_表名_字段名
-- create INDEX 索引名 on 表(字段)
CREATE INDEX id_app_user ON app_user(`name`);
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM teacher; -- 非全文索引
EXPLAIN SELECT * FROM teacher WHERE MATCH(email) AGAINST('@qq.com')
2、测试索引
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
DROP TABLE app_user;
SET GLOBAL log_bin_trust_function_creators=1; -- 开启创建函数功能
/*
第一个语句 delimiter 将 mysql 解释器命令行的结束符由”;” 改成了”$$”，
让存储过程内的命令遇到”;” 不执行
*/
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
DETERMINISTIC
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()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
RETURN i;
END $$
SELECT mock_data();
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
-- id_表名_字段名
-- create INDEX 索引名 on 表(字段)
CREATE INDEX id_app_user ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '用户9999';
添加索引后大大提升了
索引在小数据量的时候差别不大，但在大数据的时候，区别十分明细明显
3、索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上！
索引的数据结构
Hash类型的索引
Btree：InnoDB的默认数据结构！
阅读相关资料：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
三、JDBC
1、第一个JDBC程序
创建测试数据库
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,'zhangsan','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')
1、创建一个普通项目
2、导入数据库驱动
3、编写测试代码
1、sql建表语句
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`NAME` varchar(40) DEFAULT NULL,
`PASSWORD` varchar(40) DEFAULT NULL,
`email` varchar(60) DEFAULT NULL,
`birthday` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `users` */
insert into `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values (1,'zhangsan','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');
2、IDEA连接数据库
package com.kuang.lesson01;
//我的第一个jdbc程序
import java.sql.*;
public class JdbcFirstDemo {
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=false";
String username = "root";
String password = "123456";
//3.连接成功，数据库对象 去执行SQL
Connection connection = DriverManager.getConnection(url,username,password);
//connection 代表数据库 数据库设置自动提交 事务自动提交 事务回滚
//4.执行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、获得执行sql的对象 Statement
4、获得返回的结果集
5、释放连接
Statement 执行sql的对象 PrepareStatement 执行SQL对象
statement.executeQuery();//查询操作返回 ResultSet
statement.execute(); //执行任何SQL
statement.executeUpdate()//更新、插入、删除，都是用这个，返回一个受影响的行数
ResultSet 查询的结果集；封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知道的情况下使用
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
....
遍历 指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.next(); //移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定hang
释放资源
//6.释放连接
resultSet.close();
statement.close();
connection.close();
2、statement对象
jdbc中的statement对象用于向数据库SQL语句，想完成数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法，用于向数据库发送增、删、改、的sql语句，executeUpdate执行完后，将会返回一个整数(即增删改语句告知了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象
代码实现
1、编写工具类
package com.kuang.lesson01;
import java.io.IOException;
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("key");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//1、驱动之用加载一次
} catch (IOException 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) {
e.printStackTrace();
}
}
if (st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn !=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2、编写增删改的方法，executeUpdate
package com.kuang.lesson02.utils;
import com.kuang.lesson01.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 conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获取SQL执行对象
String sql ="DELETE FROM users WHERE id = 4" ;
int i = st.executeUpdate(sql);
if (i>0) {
System.out.println("删除成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3、查询 executeQuery
package com.kuang.lesson02.utils;
import com.kuang.lesson01.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) throws SQLException {
Connection conn = null ;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
//SQL
String sql = "SELECT * FROM users where id = 4";
rs = st.executeQuery(sql);//查询完毕会返回结果集
if (rs.next()) {
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("email"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3、PrepareStament对象
1、插入
package com.kuang.lesson02.utils;
import com.kuang.lesson01.JdbcUtils;
import java.util.Date;
import java.sql.*;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
ResultSet rs = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
//区别
//使用 占位符替代参数
String sql = "insert into users(`id`,`name`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?) ";
st = conn.prepareStatement(sql); //预编译SQL,先写sql，然后不执行
//手动给参数赋值
st.setInt(1,5);
st.setString(2,"liuxing");
st.setString(3,"123456");
st.setString(4,"123456789@qq.com");
//注意点： sql.Date 数据库 java.sql.Date()
// util.Date Java new Date().getTime() 获得时间戳
st.setDate(5, new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if (i>0) {
System.out.println("插入成功");
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
2、查询
package com.kuang.lesson03;
import com.kuang.lesson01.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql ="select * from users where id =?";//编写SQL
st = conn.prepareStatement(sql); //预编译
//传递参数
st.setInt(1,4);
rs = st.executeQuery();//执行
if (rs.next()) {
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3、删除
package com.kuang.lesson03;
import com.kuang.lesson01.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
ResultSet rs = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
//区别
//使用 占位符替代参数
String sql = "DELETE from users where id=?";
st = conn.prepareStatement(sql); //预编译SQL,先写sql，然后不执行
//手动给参数赋值
st.setInt(1,5);
//执行
int i = st.executeUpdate();
if (i>0) {
System.out.println("删除成功");
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
4、修改
package com.kuang.lesson03;
import com.kuang.lesson01.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
ResultSet rs = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
//区别
//使用 占位符替代参数
String sql = "update users set `name` = ? where id =?";
st = conn.prepareStatement(sql); //预编译SQL,先写sql，然后不执行
//手动给参数赋值
st.setString(1,"hahaha");
st.setInt(2,5);
//执行
int i = st.executeUpdate();
if (i>0) {
System.out.println("更新成功");
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
sql注入问题
sql存在漏洞，会被攻击存在漏洞
package com.kuang.lesson02.utils;
import com.kuang.lesson01.JdbcUtils;
import java.sql.*;
public class SQL注入 {
public static void main(String[] args) {
//正常情况下
// login("xiaoxiao","123456");
login("' or '1=2","'or '1=2");
}
public static void login(String username,String password){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//SQL
String sql = "SELECT * FROM users where `NAME`='"+username+"'AND `password` ='"+password+"'";
rs = st.executeQuery(sql);//查询完毕会返回结果集
st = conn.prepareStatement(sql);//预编译
if (rs.next()) {
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("password"));
System.out.println("==================================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SQL注入方式大致分为两类：数字型注入、字符型注入。
login("' or '1=2","'or '1=2");
解决sql注入问题
package com.kuang.lesson02.utils;
import com.kuang.lesson01.JdbcUtils;
import java.sql.*;
public class SQL注入 {
public static void main(String[] args) {
//正常情况下
// login("hahaha","123456");
login("'' or '1=1","123456");
}
public static void login(String username,String password){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//SQL
//PrepareStatement 防止SQL注入的本质，把传递进来的参数当作字符
//假设其中存在转义字符，比如说 ' ，会被直接转义
String sql = "select * from users where `NAME` =? and `PASSWORD` =?";
st = conn.prepareStatement(sql);//预编译
st.setString(1,username);
st.setString(2,password);
rs = st.executeQuery();//查询完毕会返回结果集
if (rs.next()) {
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("password"));
System.out.println("==================================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
四、使用IDEA连接数据库
第一步：打开右边的Database
第二步：输入数据库信息
第三步： 连接成功后，打开对应的数据库
第四步：若在数据库中修改信息，需要提交
第五步 : 若要输入sql语句，需要打开sql控制台
第六步 : 若要切换数据库
事务
要么都成功，要么都失败
ACID 原则
1、原子性 :要么全部完成，要不都不完成
2、一致性 :总数不变
3、隔离性 :多个进程互不干扰
4、持久性 :一旦提交不可逆，持久化到数据库
隔离性的问题：
脏读： 一个事务读取了另一个没有提交的事务
不可重复读：在同一事务内，重复读取表中的数据，表数据发生了改变
虚读(幻读)：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致
代码实现
1、开启事务 conn.setAutoCommit(false);
2、一组业务执行完毕，提交事务
3、可以在catch语句中定义回滚语句，但默认失败就会回滚
CREATE TABLE account(
id int PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(40),
money FLOAT
);
/* 插入测试数据 */
insert into account(name,money) values('A',1000);
insert into account(name,money) values('B',1000);
insert into account(name,money) values('C',1000);
package com.kuang.lesson04;
import com.kuang.lesson01.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransation1 {
//事务的执行
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//关闭数据库的自动提交，自动会开启事务
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.println("成功！");
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
事务失败案例
可以在事务中间加上 int x= 1/0;事务失败后，默认会回滚
//关闭数据库的自动提交，自动会开启事务
conn.setAutoCommit(false); //开启事务
String sql1 = "update account set money =money - 100 where name = 'A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
int x= 1/0;
String sql2 = "update account set money =money + 100 where name = 'B' ";
st = conn.prepareStatement(sql2);
st.executeUpdate();
五、数据库连接池
数据库连接---执行完毕---释放
在 连接---释放这个过程中十分浪费系统资源
针对于这种情况，可以通过池化技术解决资源浪费的问题
池化技术 ： 准备一些预先的资源，过来就连接预先准备好的
最小连接数、最大连接数，等待超时
编写连接池，实现一个接口DataSource
开源数据源实现
DBCP
C3P0
Druid: 阿里巴巴
使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了！
六、MySQL数据库语言
DQL(查询)
对于连表查询的一些注意点
inner join:查询两个表共有+左边单独有+右边单独有的数据 - left join：查询两个表共有+左边单独有的数据 - right join：查询两个表共有+右边表单独有的数据
浙公网安备 33010602011771号