回顾 mysql 的一些知识点，以及记录之后遇见有趣的代码。
1、 安装
- win10 + mysql8.0 ：一定不要手动创建my.ini，mysql5.0版本才要手动创建
- Linux 安装 mysql
- 顺手把 navicat 给装了
2、 建表以及crud的建议
- 建表时一定要加上字符设置
charset=utf8,因为默认 mysql 的字符集 latin1 不支持中文编码。 - mysql 默认的数据库引擎为 innodb(5.5以后成为默认的)，相比于之前的 MyISAM 二者的区别为：
| MYISAM | INNODB | |
|---|---|---|
| 事物支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持，只支持表锁定 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大，约两倍 |
MYISAM：节约空间，速度较快。 INNODB：安全性高，事物的处理，多表多用户操作。
- MYSQL 所有的数据库文件都存在data目录，一个文件夹就对应一个数据库
- 建表时不建议使用物理外键，也就是在表上建外键：因为每次delete/update表时都必须考虑外键约束，表越多外键约束越多开发难度越大。最佳方案为：
- 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
- 外键通过应用层（程序代码）实现。
- 清空表时不建议使用
delete from 表名，而是用truncate 表名。好处在于：- TRUNCATE 重新设置自增列，计数器会归 0（而 delete 以后并且数据库引擎为 innodb，需要重启数据库计数器才会归 0，这是因为 innodb 会将计数器存在内存中，断点即失，而 MyISAM 是存在文件中）
- TRUNCATE 不会影响事物
3、 索引
强烈推荐：MySQL索引背后的数据结构及算法原理
4、 备份
#导出；
#mysqldump -h 主机 -u 用户名 -p 密码 数据库 [表1] [表2]... > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p***** school student > D:/b.sql
#导入：
#登录情况下，切换到指定数据库
source D:/b.sql
#无登录情况下
mysql -uroot -p**** 库名 < 备份文件
5、 JDBC
5.1 Demo
准备工作：
- 环境为：IDEA 2020 + jdk 8.0 + mysql 8.0 + mysql-connector-java-8.0.21.jar
- 将 mysql 驱动导入 idea 中
- 将mysql 驱动所在的目录设为 Liberary
public class Test01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1. 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai";
String username = "root";
String pas = "redhat";
//2. 连接数据库
Connection con = DriverManager.getConnection(url,username,pas);
String sql = "select * from student";
//3. 获得执行的sql对象
Statement statement = con.createStatement();
//4. 执行sql 获得结果集
ResultSet rs = statement.executeQuery(sql);
while(rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
System.out.println(id+" "+name+" "+age);
}
//5. 关闭连接
statement.close();
con.close();
}
}
5.2 JDBC 工具类
工具类：
//JDBC工具类
public class Utils {
static String driver = null;
static String url= null;
static String user= null;
static String pwd= null;
static{
InputStream in = Utils.class.getClassLoader().getResourceAsStream("com/lrr/jdbc/db.properties");
Properties properties = new Properties();
try {
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
pwd = properties.getProperty("pwd");
//驱动只用加载一次：
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//得到连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,pwd);
}
//关闭连接
public static void close(ResultSet rs, Statement statement,Connection con){
if(rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(con != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
使用：
public class Test02 {
public static void main(String[] args) throws SQLException {
Connection connection = Utils.getConnection();
Statement statement = connection.createStatement();
String sql = "select * from student";
ResultSet rs = statement.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getInt("id")+" "+rs.getString("name"));
}
Utils.close(rs,statement,connection);
}
}
5.3 PreparedStatement
PreparedStatement 可以防止 sql 注入。防止的原理是把传递进来的参数当作字符，即使参数存在转义字符，比如说 ‘ ，也会当成字符串。 相当于外面又包了一层。
Demo：
public class Test03 {
public static void main(String[] args) throws SQLException {
Connection con = Utils.getConnection();
String sql = "select * from student where id =?"; //占位符
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1,1); //将具体的值替换占位符
ResultSet rs = ps.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("id")+" "+rs.getString("name"));
}
Utils.close(rs,ps,con);
}
}
使用 Statement 时，sql 注入语句为：
String id = "2323 or 1 = 1";
String sql = "select * from student where id ="+ id;
拼接后为：select * from student where id = 2323 or 1 = 1。而这句话会将表中所有数据都得到
而使用 PreparedStatement 时，sql 注入语句为：
String sql = "select * from student where name =?"; //占位符
ps.setString(1,"2323 or 1 = 1");
拼接后为：select * from student where name = "2323 or 1 = 1"。也就是说在 2323 or 1 = 1 外面又加上"" ，从而将整个输入当作字符串。
6、 数据库连接池
编写连接池，需要实现一个接口：DataSource
有三个流行的 具体实现方案：DBCP，C3P0 ，Druid。三者之间的比较
使用了这些数据库连接池之后，在项目开发中就不需要编写连接数据库的代码了。
需要的 jar 包： DBCP ：commons-dbcp 、 commons-pool C3P0 ： c3p0 、 mchange-commons-java
6.1、 DBCP
- 下载 commons-dbcp 、 commons-pool 还有可能需要 commons-logging-1.2 、tomcat-juli-10.0.0-M7
- 新建 *.properties 文件 （dbcp.properties），内容为： ```properties driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/school username=root password=redhat
#初始化时连接池中connection数量 initialSize=10
#最大连接数量 maxActive=50
#最大的空闲连接数量 maxIdle=20
#最小的空闲链接数量 minIdle=5
#最大的等待时间，单位是毫秒 maxWait=60000 #建立连接时的附加参数，如果指定的编码不一致数据库中会出现乱码 connectionProperties=useUnicode=true;characterEncoding=utf8;serverTimezone=Asia/Shanghai
#是否开启自动提交，跟事务的控制有关 defaultAutoCommit=true
#指定由连接池所创建的连接的事务隔离级别（TransactionIsolation）。 defaultTransactionIsolation=REPEATABLE_READ
+ Utils_dbcp：
```java
public class Utils_dbcp {
static BasicDataSource basicDataSource = null;
static InputStream in = null;
static Properties properties = null;
static{
in = Utils_dbcp.class.getClassLoader().getResourceAsStream("com/lrr/jdbc/dbcp.properties");
properties = new Properties();
try {
properties.load(in);
try {
basicDataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
//得到连接
public static Connection getConnection() throws SQLException {
return basicDataSource.getConnection();
}
//关闭连接
public static void close(ResultSet rs, Statement statement, Connection con){
if(rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(con != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
文档信息
- 本文作者：MikasaLee
- 本文链接：/2020/08/15/mysql/
- 版权声明：自由转载-非商用-非衍生-保持署名（创意共享3.0许可证）