MySQL学习笔记
MySQL学习笔记
一、MySQL基础
1. MySQL常见数据类型
| 类型 | 描述 |
|---|---|
| int | 整型 |
| double | 浮点型 |
| varchar | 字符串型 |
| date | 日期类型，yyyy-MM-dd ,只有年月日，没有时分秒 |
2. char和varchar的区别
- char类型是固定长度的： 根据定义的字符串长度分配足够的空间。
- varchar类型是可变长度的： 只使用字符串长度所需的空间。
比如：保存字符串 "abc"
x char(10) 占用10个字节
y varchar(10) 占用3个字节
3. 使用count的注意事项
不要对有空值的列使用count统计
select count(dept_name) from emp;
-- 假如dept_name这个字段的有null值，那么使用count统计的时候会过滤null值。
二、MySQL约束&事务
1. SQL常见约束
| 约束名 | 约束关键字 |
|---|---|
| 主键约束 | primary key |
| 唯一约束 | unique |
| 非空约束 | not null |
| 外键约束 | foreign key |
1.1 主键约束
不可重复、唯一、非空
1.1.1 三种添加主键约束的方法
-- 方式1
CREATE TABLE emp2(
eid INT PRIMARY KEY,
ename VARCHAR(20),
sex CHAR(1)
);
-- 方式2
CREATE TABLE emp2(
eid INT ,
ename VARCHAR(20),
sex CHAR(1),
-- 指定主键为 eid字段
PRIMARY KEY(eid)
);
-- 方式3
CREATE TABLE emp2(
eid INT ,
ename VARCHAR(20),
sex CHAR(1)
)
-- 创建的时候不指定主键,然后通过 DDL语句进行设置
ALTER TABLE emp2 ADD PRIMARY KEY(eid);
1.1.2 删除主键约束
alter table emp2 drop primary key;
1.1.3 主键自增
- 插入数据的方式
-- eid是主键自增字段
-- 插入数据的方式1
insert into emp(ename, sex) values('张三', '男');
-- 插入数据的方式2
insert into emp values(null, '张三', '男');
- 修改自增起始值
-- 修改自增起始值
create table emp(
eid int primary key auto_increment,
ename varchar(20),
sex char(1)
)auto_increment=100; -- 表示从100开始自增
-
delete 和 truncate 对自增长的影响
-
delete是将表中的数据逐条删除，删除之后对自增没有影响。
-
truncate是先将整个表删除，然后再创建一张结构相同的表。删除之后自增从1开始。
-
1.2 外键约束
1.2.1 添加外键约束
添加原则：在一对多的情况下，在多的一方建立外键，指向一的一方的主键。
-
在新建表的时候添加外键约束
create table employee( eid int primary key auto_increment, ename varchar(20), age int, dept_id int, -- 外键可以为空 -- 添加外键约束 -- 语法格式：[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名) constraint emp_dept_fk foreign key(dept_id) references department(id) ); -
在建表之后添加外键约束
-- 语法格式 alter table employee ADD constraint emp_dept_fk foreign key(dept_id) references department(id);
1.2.2 删除外键约束
-- 语法格式：alter table 从表 drop foreign key 外键约束名称
alter table employee drop foreign key emp_dept_fk;
1.2.3 外键约束的注意事项
- 从表外键数据类型必须与主表主键类型一致，否则创建失败。
- 添加数据时，应当先添加主表数据。
- 删除数据时，应当先删除从表数据。
2. 事务
2.1 什么是事务
事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败。
比如: 银行的转账业务,张三给李四转账500元 , 至少要操作两次数据库, 张三 -500, 李四 + 500,这中间任何一步出现问题,整个操作就必须全部回滚, 这样才能保证用户和银行都没有损失。
2.2 事务的四大特性
- 原子性：事务中的所有SQL要么都执行成功，要么都执行失败。
- 一致性：事务执行前后，整体的状态要保持一致。比如转账，转账之前两人的总金额是2000，转账之后两人的总金额也还是2000.
- 隔离性：事务与事务之间不能产生干扰。
- 持久性：事务提交之后，数据将持久化。就算关机，数据也是要保存下来的。
三、MySQL数据库设计
1. 数据库设计
1.1 数据库三范式
- 原子性：列不可再分
- 一张表只描述一件事。比如：学生表中值描述学生的信息，不应该包含课程的信息。
- 表的信息如果能被推导出来，就不应该单独设计一个字段来存放。比如：通过number和price就可以计算出总金额，那么这个时候就不应该再设计一个总金额的字段。
1.2 反三范式
- 在某些场景下，允许一个字段出现在多张表中。
1.3 总结
- 尽量遵守三范式原则
- 合理的加入冗余字段，减少join，让数据库的执行性能更高。
四、 MySQL索引
1. 主键索引(primary key)
特点
-
主键是一种唯一性索引，每个表只能有一个主键，用于标识数据表中的某一条记录。
-
一个表可以没有主键。但是最多只能有一个主键，且主键的值不能是null。
-
表中至少应该有一个主键索引
以上两点是不是矛盾的？
2. 唯一索引(unique)
特点：索引列中的所有值只能出现一次，必须唯一。
语法格式
-
创建表的时候添加唯一索引
create table emp( id int, class int, unique class_unique (class) ); -
使用create语句创建：在已有的表上创建索引
-- create unique index 索引名 on 表名(列名(长度)) create unique index class_unique on emp(class);
3. 普通索引
唯一的目的就是为了提升查询速度，应该只为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDER BY column）中的数据列创建索引。
语法格式
create index 索引名 on 表名(列名[长度])
ALTER TABLE 表名 ADD INDEX 索引名 (列名)
6.4 索引总结
-
添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。
-
索引的优点
- 大大提升查询速度
- 可以显著地减少查询中分组和排序的时间
-
索引的缺点
- 创建索引和维护索引需要时间，且数据量越大时间越长。
- 当对表中的数据进行增、删、改的操作的时候，索引也要同时维护，降低了数据的维护速度。
- 索引文件需要占据磁盘空间
五、 MySQL视图
1. 语法格式
create view 视图名 [column_list] as select语句;
view: 表示视图
column_list: 可选参数，表示属性清单，指定视图中各个属性的名称，默认情况下，与SELECT语句中查询
的属性相同
as : 表示视图要执行的操作
select语句: 向视图提供数据内容
2. 创建一张视图
CREATE VIEW products_category_view
AS SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;
六、DCL(数据控制语言)
1. 创建用户
-- 创建 admin1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123456
CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';
-- 创建 admin2 用户可以在任何电脑上登录 mysql 服务器，密码为 123456
CREATE USER 'admin2'@'%' IDENTIFIED BY '123456'; -- % 表示 用户可以在任意电脑登录 mysql服务器
2. 用户授权
语法格式
GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
| 参数 | 说明 |
|---|---|
| 权限 | 授予用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。 如果要授 予所有的权限则使用 ALL |
| on | 用来指定权限针对哪些库和表。 |
| to | 表示将权限赋予某个用户。 |
示例代码
-- 给 admin1 用户分配对 db4 数据库中 products 表的 操作权限：查询
grant select on db4.products to 'admin1'@'localhost';
-- 给 admin2 用户分配所有权限，对所有数据库的所有表
grant all on *.* to 'admin2'@'localhost';
3. 查看权限
示例代码
-- 查看root用户的权限
SHOW GRANTS FOR 'root'@'localhost';
4. 删除用户
示例代码
-- 删除 admin1 用户
DROP USER 'admin1'@'localhost';
七、DDL&DML
1. 修改表和修改表数据对比
-
修改表(DDL)：除了修改表名所使用的关键字是rename table之外，其他修改操作都是alter table关键字.
-- 修改表名 RENAME TABLE category TO category1; -- 添加字段 ALTER TABLE category ADD cdesc VARCHAR(20); -- 修改字段数据类型 ALTER TABLE category MODIFY cdesc VARCHAR(50); -- 修改字段名 ALTER TABLE category CHANGE cdesc description VARCHAR(30); -- 删除字段 ALTER TABLE category DROP description; -
修改表数据(DML)：update 表名 set
UPDATE student SET age = 20,address = '北京' WHERE sid = 2;
八、JDBC
1. 什么是JDBC
总结：JDBC就是Java提供的一套操作关系型数据库的标准(接口)，然后数据库厂商来实现这一套接口，并且提供数据库驱动jar包。我们去使用这套接口，真正执行的是对应的驱动包中的实现类。
2. JDBC开发步骤
- 注册驱动(可省略)
- 获取连接对象
- 获取语句执行平台
- 处理结果集(只在查询的时候处理)
- 释放资源
示例代码
public class JDBCDemo01 {
public static void main(String[] args) {
Connection con = null;
Statement statement = null;
try {
//1. 注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2. 获取连接对象
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
//3. 获取操作sql语句执行平台
statement = con.createStatement();
//4. 执行sql语句
String sql = "create table test01(id int, name varchar(20),age int);";
//返回的是影响的行数
long l = statement.executeLargeUpdate(sql);
//5. 处理结果
System.out.println(i)
} catch (Exception e) {
e.printStackTrace();
} finally {
//6. 释放资源
if (null != statement) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (null != con) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
3. 自定义JDBC工具类
public class JDBCUtils {
// 定义一个驱动类名字的常量
public static final String DIRVER_NAME = "com.mysql.jdbc.Driver";
// 定义URL常量
public static final String URL = "jdbc:mysql://localhost:3306/db3?characterEncoding=UTF-8";
// 定义一个登录mysql的用户名的常量
public static final String USER = "root";
// 定义用户密码常量
public static final String PASSWORD = "root";
// 静态代码块，用户获取驱动
static {
try {
Class.forName(DIRVER_NAME);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 用户获取连接的静态方法
public static Connection getConnection() {
Connection con = null;
try {
con = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
return null;
}
return con;
}
// 用于关闭资源的静态方法,适用于增、删、改的操作
public static void close(Connection con, Statement statement) {
if (con != null && statement != null) {
try {
statement.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 用户关闭资源的静态方法，适用于查找操作
public static void close(Connection con, Statement statement, ResultSet rs) {
if (con != null && statement != null && rs != null) {
try {
rs.close();
statement.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
4. SQL注入问题
4.1 代码演示什么是SQL注入
# SQL注入演示
-- 填写一个错误的密码
SELECT * FROM jdbc_user WHERE username = 'tom' AND PASSWORD = '123' OR '1' = '1';
4.2 PrepareStatement预处理对象
4.2.1 PrepareStatement接口介绍
- PreparedStatement 是 Statement 接口的子接口，继承于父接口中所有的方法。它是一个预编译的 SQL 语句对象。
- 预编译: 是指SQL 语句被预编译,并存储在 PreparedStatement 对象中。然后可以使用此对象多次高效地执行该语句。
4.2.2 与Statement对比
- Statement处理的是静态SQL；而PrepareStatement是预编译的SQL语句对象，语句中可包含动态参数"?"。
- Statement每执行一条语句，数据库就要编译一次SQL语句；而PrepareStatement只需要编译一次SQL语句，大大提高数据库性能。
- Statement不能防止SQL注入问题；而PrepareStatement可以有效防止SQL注入问题。
4.3 解决SQL注入问题
使用PrepareStatement预处理对象
示例代码
public class TestLogin02 {
/**
* 使用预编译对象 PrepareStatement 完成登录案例
* @param args
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
//1.获取连接
Connection connection = JDBCUtils.getConnection();
//2.获取Statement
Statement statement = connection.createStatement();
//3.获取用户输入的用户名和密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名: ");
String name = sc.nextLine();
System.out.println("请输入密码: ");
String pass = sc.nextLine();
System.out.println(pass);
//4.获取 PrepareStatement 预编译对象
//4.1 编写SQL 使用 ? 占位符方式
String sql = "select * from jdbc_user where username = ? and password = ?";
PreparedStatement ps = connection.prepareStatement(sql);
//4.2 设置占位符参数
ps.setString(1,name);
ps.setString(2,pass);
//5. 执行查询 处理结果集
ResultSet resultSet = ps.executeQuery();
if(resultSet.next()){
System.out.println("登录成功! 欢迎您: " + name);
}else{
System.out.println("登录失败!");
}
//6.释放资源
JDBCUtils.close(connection,statement,resultSet);
}
}
5. JDBC控制事务
5.1 常用方法
| 方法 | 说明 |
|---|---|
| void setAutoCommit(boolean autoCommit) | 参数是 true 或 false 如果设置为 false，表示关闭自动提交，相当于开启事务。 |
| void commit() | 提交事务 |
| void rollback() | 回滚事务 |
5.2 示例代码
public class JDBCTransaction {
//JDBC 操作事务
public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null;
try {
//1. 获取连接
con = JDBCUtils.getConnection();
//2. 开启事务
con.setAutoCommit(false);
//3. 获取到 PreparedStatement 执行两次更新操作
//3.1 tom 账户 -500
ps = con.prepareStatement("update account set money = money - ? where name = ? ");
ps.setDouble(1,500.0);
ps.setString(2,"tom");
ps.executeUpdate();
//模拟tom转账后 出现异常
System.out.println(1 / 0);
//3.2 jack 账户 +500
ps = con.prepareStatement("update account set money = money + ? where name = ? ");
ps.setDouble(1,500.0);
ps.setString(2,"jack");
ps.executeUpdate();
//4. 正常情况下提交事务
con.commit();
System.out.println("转账成功!");
} catch (SQLException e) {
e.printStackTrace();
try {
//5. 出现异常回滚事务
con.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
//6. 最后关闭资源
JDBCUtils.close(con,ps);
}
}
}
九、数据库连接池
推荐使用Druid连接池
1. Druid连接池
1.1 使用方法
需要导入jar包以及配置文件。配置文件是Properties格式的。
1.2 示例代码
- 工具类代码
public class DruidUtils {
//1.定义成员变量
public static DataSource dataSource;
//2.静态代码块
static{
try {
//3.创建属性集对象
Properties p = new Properties();
//4.加载配置文件 Druid 连接池不能够主动加载配置文件 ,需要指定文件
InputStream inputStream =
DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
//5. 使用Properties对象的 load方法 从字节流中读取配置信息
p.load(inputStream);
//6. 通过工厂类获取连接池对象
dataSource = DruidDataSourceFactory.createDataSource(p);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接的方法
public static Connection getConnection(){
try {
return dataSource.getConnection();
}
catch (SQLException e) {
e.printStackTrace();
return null;
}
}
//释放资源，代码省略
}
- 测试类代码
// 需求 查询 薪资在3000 到 5000之间的员工的姓名
public static void main(String[] args) throws SQLException {
//1.获取连接
Connection con = DruidUtils.getConnection();
//2.获取Statement对象
Statement statement = con.createStatement();
//3.执行查询
ResultSet resultSet = statement.executeQuery("select ename from employee where salary
between 3000 and 5000");
//4.处理结果集
while(resultSet.next()){
String ename = resultSet.getString("ename");
System.out.println(ename);
}
//5.释放资源
DruidUtils.close(con,statement,resultSet);
}
十、DBUtils工具类
1. 作用
简化JDBC开发代码
2. 核心功能介绍
- QueryRunner类：对SQL语句进行操作
- ResultSetHandler接口：用于在定义select操作之后，怎样封装结果集。
- DbUtils类：定义了关闭资源和与事务相关的操作
3. DBUtils完成CRUD操作
3.1 QueryRunner核心类
3.1.1 构造方法
QueryRunner qr = new QueryRunner()
QueryRunner qr = new QueryRunner(DataSource ds) // 提供数据源(连接池)，DBUtils底层自动维护connection
3.1.2 重要方法
// 用来完成增删改操作
update(Connection con, String sql, Object... params)
// 用来完成查询操作
query(Connection con, String sql, ResultHandler<T> rsh, Object... params)
3.1.3 实现增删改操作
@Test
public void testInsert() throws SQLException {
//1.创建 QueryRunner 手动模式创建
QueryRunner qr = new QueryRunner();
//2.编写 占位符方式 SQL
String sql = "insert into employee values(?,?,?,?,?,?)";
//3.设置占位符的参数
Object[] param = {null,"张百万",20,"女",10000,"1990-12-26"};
//4.执行 update方法
Connection con = DruidUtils.getConnection();
int i = qr.update(con, sql, param);
//5.释放资源
DbUtils.closeQuietly(con);
}
//修改操作 修改姓名为张百万的员工工资
@Test
public void testUpdate() throws SQLException {
//1.创建QueryRunner对象 自动模式,传入数据库连接池
QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
//2.编写SQL
String sql = "update employee set salary = ? where ename = ?";
//3.设置占位符参数
Object[] param = {0,"张百万"};
//4.执行update, 不需要传入连接对象
qr.update(sql,param);
}
//删除操作 删除id为1 的数据
@Test
public void testDelete() throws SQLException {
QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
String sql = "delete from employee where eid = ?";
//只有一个参数,不需要创建数组
qr.update(sql,1);
}
3.1.4 实现查询操作
需要使用到ResultSetHandler接口，用于对结果集进行处理。
- ResultSetHandler常见实现类如下：
| ResultSetHandler实现类 | 说明 |
|---|---|
| ArrayHandler | 将结果集中的第一条记录封装到一个Object[]数组中，数组中的每一个元素就是这条记录中的每一个字段的值 |
| ArrayListHandler | 将结果集中的每一条记录都封装到一个Object[]数组中，将这些数组再封装到List集合中。 |
| BeanHandler | 将结果集中第一条记录封装到一个指定的javaBean中. |
| BeanListHandler | 将结果集中每一条记录封装到指定的javaBean中，再将这些javaBean在封装到集合中 |
| MapHandler | 将结果集中第一条记录封装到了Map<String,Object>集合中，key就是字段名，value就是字段值 |
| ScalarHandler | 它是用于封装单个数据。例如 select count(*) from 表操作。 |
- 对上述实现类的测试
查询id为5的记录,封装到数组中
public static void testQuery() throws SQLException {
// 1.获取连接对象
Connection con = DruidUtils.getConnection();
// 2.手动创建QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 3.编写SQL语句
String sql = "select * from employee where eid = ?";
// 4.执行select方法
Object[] queryResult = queryRunner.query(con, sql, new ArrayHandler(), 5);
for (Object o: queryResult) {
System.out.println(o);
}
// 5.释放资源
DbUtils.closeQuietly(con);
}
查询所有数据,封装到List集合中
public static void queryAll() throws SQLException {
// 1.获取连接对象
Connection con = DruidUtils.getConnection();
// 2.手动创建QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 3.编写SQL语句
String sql = "select * from employee";
// 4.执行select方法
List<Object[]> queryList = queryRunner.query(con, sql, new ArrayListHandler());
for (Object[] o: queryList) {
System.out.println(Arrays.toString(o));
}
// 5.释放资源
DbUtils.closeQuietly(con);
}
查询id为5的记录,封装到指定JavaBean中
public static void queryToJavaBean() throws SQLException {
// 1.获取连接对象
Connection con = DruidUtils.getConnection();
// 2.手动创建QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 3.编写SQL语句
String sql = "select * from employee where eid = ?";
// 4.执行select方法
Employee query = queryRunner.query(con, sql, new BeanHandler<Employee>(Employee.class), 5);
System.out.println(query.toString());
// 5.释放资源
DbUtils.closeQuietly(con);
}
查询姓名是张百万的员工信息,将结果封装到Map集合中
public static void queryToMap() throws SQLException {
// 1.获取连接对象
Connection con = DruidUtils.getConnection();
// 2.手动创建QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 3.编写SQL语句
String sql = "select * from employee where eid = ?";
// 4.执行select方法
Map<String, Object> query = queryRunner.query(con, sql, new MapHandler(), 5);
for (String s:query.keySet()) {
System.out.println(s + ": " + query.get(s));
}
// 5.释放资源
DbUtils.closeQuietly(con);
}
十一、批处理
1. 什么是批处理
-
批处理(batch) 操作数据库
- 批处理指的是一次操作中执行多条SQL语句，批处理相比于一次一次执行效率会提高很多。
- 当向数据库中添加大量的数据时，需要用到批处理。
-
举例: 送货员的工作：
- 未使用批处理的时候，送货员每次只能运送 一件货物给商家；
- 使用批处理，则是送货员将所有要运送的货物, 都用车带到发放处派给客户。
2. 实现批处理
Statement和PrepareStatement都支持批处理操作
- 需要使用到的方法
| 方法 | 说明 |
|---|---|
| void addBatch() | 将给定的 SQL 命令添加到此 Statement 对象的当前命令列表中。通过调用方法 executeBatch 可以批量执行此列表中的命令。 |
| executeBatch() | 每次提交一批命令到数据库中执行，如果所有的命令都成功执行了，那么返回一个数组，这个数组是说明每条命令所影响的行数 |
- mysql 批处理是默认关闭的，所以需要加一个参数才打开mysql 数据库批处理，在url中添加
rewriteBatchedStatements=true
例如: url=jdbc:mysql://127.0.0.1:3306/db5?characterEncoding=UTF-8&rewriteBatchedStatements=true
- 测试代码
public class TestBatch {
//使用批处理,向表中添加 1万条数据
public static void main(String[] args) {
try {
//1.获取连接
Connection con = DruidUtils.getConnection();
//2.获取预处理对象
String sql ="insert into testBatch(uname) values(?)";
PreparedStatement ps = con.prepareStatement(sql);
//3.创建 for循环 来设置占位符参数
for (int i = 0; i < 10000 ; i++) {
ps.setString(1,"小强"+i);
//将SQL添加到批处理 列表
ps.addBatch();
}
//添加时间戳 测试执行效率
long start = System.currentTimeMillis();
//统一 批量执行
ps.executeBatch();
long end = System.currentTimeMillis();
System.out.println("插入10000条数据使用: " +(end - start) +" 毫秒!");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
