MySql

一、DDL操作 1. 操作库 1.1 CRUD

1. 创建数据库

   #建库
   create database mydb1;
   #建库并设置字符集
   create database mydb2 character set utf8;
   #设置字符集并进行类型检查
   create database mydb3 character set utf8 collate utf8_general_ci;
   

2. 查看数据库

   #查看数据库
   show databases;
   #查看创库语句
   show create database mydb1;
   

3. 修改数据库

   #修改库字符集
   alter database mydb1 character set utf8;
   

4. 删除数据库

   drop database mydb3;
   

1.2 创建多表

1. 一对一关系

   两张表的主键对应主键

2. 一对多关系

   在多的那一方创建字段，作为外键，关联一的那一方主键

3. 多对多关系

   创建第三张表，在第三张表至少有两个字段作为外键，关联两个表的主键

以一对多为例

# 一对多
CREATE TABLE dept (
	did INT PRIMARY KEY,
	dname VARCHAR(100)
)
CREATE TABLE emp (
	eid INT PRIMARY KEY,
	ename VARCHAR(100),
	age INT,
	edid INT,
	FOREIGN KEY(edid) REFERENCES dept(did)	#声明外键`dept`
)
DELETE FROM dept WHERe did = 1	#报错，因为有关联

由于声明外键后，另一张表的主键与当前表的外键联系起来，所以会导致增删不灵活(如有关联时就无法删除主键)，所以一般都是逻辑上关联即可，不需要真实的在语法上关联。

2. 表操作 2.1 CURD

1. 创建表

   #选择数据库
   use mydb1;
   #创建表
   create table t1(id int, name varchar(20));
   create table newT1 like t1;	#拷贝表结构
   create table newT1 as (select * from t1);	#拷贝表结构+数据(不包括约束和索引)
   create table newT1 like db2.t1;	#跨库拷贝表结构
   

2. 查看表

   #查看当前库中的表
   show tables;
   #显示创表语句
   show create table t1;
   #查看表结构
   desc t1;
   

3. 修改表

   #修改表名
   rename table t1 to t2;
   #添加字段
   alter table t2 add column height double;
   #修改字段
   alter table t2 modify column height float;
   #删除字段
   alter table t2 drop column height;
   

4. 删除表

   drop table t2;
   

2.2 约束

1. 主键约束primary key

   # 主键：非空且唯一
   CREATE TABLE users (
   	id INT PRIMARY KEY,
   	NAME VARCHAR(20)
   )
   
   CREATE TABLE person (
   	id INT,
   	NAME VARCHAR(20),
   	PRIMARY KEY(id)
   )
   
   CREATE TABLE book (
   	id INT,
   	bname VARCHAR(100),
   	bno INT
   	PRIMARY KEY(id, bno)
   )
   INSERT INTO users VALUES (1, "lucy")
   INSERT INTO users VALUES (1, "mary")	#报错
   

2. 唯一约束unique

   ## 唯一：字段值不能重复------unique
   CREATE TABLE book (
   	id INT PRIMARY KEY,
   	bname VARCHAR(100) UNIQUE
   )
   INSERT INTO book VALUES(1, 'java开发')
   INSERT INTO book VALUES(2, 'java开发')	#报错
   

3. 非空约束not null

   # 非空：字段不能是NULL-------NOT NULL
   CREATE TABLE orders (
   	id INT PRIMARY KEY,
   	ordersname VARCHAR(100) NOT NULL
   )
   INSERT INTO orders VALUES(1, 'abc')
   INSERT INTO book (id) VALUES(1)		#报错
   

4. 缺省值default

   # 缺省：字段默认值
   CREATE TABLE person (
   	id INT,
   	pname VARCHAR(100) DEFAULT 'lucy'
   )
   INSERT INTO person VALUES(1, 'jack') 
   INSERT INTO person (id) VALUES(1) 	#lucy
   

5. 综合演示

   ## 综合演示
   CREATE TABLE emp (
   	eld INT PRIMARY KEY,`dept`
   	ename VARCHAR(100) NOT NULL,
   	gender VARCHAR(10) NOT NULL, 
   	ecode VARCHAR(100) UNIQUE,
   	salary DECIMAL(10, 2) DEFAULT 2000
   )
   

2.3 自动增长与索引

1. 自动增长

   ## 自动增长和索引
   CREATE TABLE dept (
   	id INT PRIMARY KEY AUTO_INCREMENT,	#不能是varchar
   	dname VARCHAR(100)	
   )
   INSERT INTO dept VALUES(NULL, '安保部')	#id为1
   INSERT INTO dept VALUES(NULL, '财务部')	#id为2
   

2. 索引

   索引是一个单独的、物理的数据库结构

   （1）不过度索引

   （2）索引条件列(where后面最频繁的条件比较适宜索引)

   （3）索引散列值,过于集中的值不要索引，例如:给性别"男","女"加索引,意义不大

   create index index_name on dept (id)
   

二、DML操作 1. 单表操作 增加记录

insert into stu(id, name) values (1, '宋青书')
insert into stu values (2, 'def')
insert into stu values (3, 'ghi'), (4, 'jkl')
insert into stu select * from t1	#拷贝t1数据到stu

修改记录

UPDATe stu SET address='峨眉' WHERe sname = '宋青书'
UPDATE stu SET age = 28, address = '峨眉' WHERe sname = '宋青书'
UPDATE stu SET age = age + 10

删除操作

DELETE FROM stu WHERe sid = 2
DELETe FROM stu	#删除表中所有数据
TRUNCATE stu	#删除整张表再重建整张表

查询操作

1. 简单查询

   #查询操作
   SELECt * FROM stu
   SELECt sid, sname FROM stu
   
   SELECt sid AS id, sname AS NAME FROM stu AS s	#别名
   
   CREATE TABLE t1 (
   	id INT, 
   	`name` VARCHAR(20)	#着重号
   )
   
   SELECt * FROM stu AS s WHERe s.sname = '宋青书'
   
   #去重
   SELECt DISTINCT ename FROM emp
   
   #区间范围
   SELECt * FROM stu AS s WHERe s.sid > 2 AND s.age > 20
   SELECt * FROM stu AS s WHERe s.sid between 2 and 20
   SELECt * FROM emp WHERe age IN(20, 40, 60)	
   
   #模糊查询
   SELECt * FROM stu AS s WHERe sname like '%宋%'	#通配符不限字符
   SELECt * FROM stu AS s WHERe sname like '宋____'	#通配符指定字符
   
   #排序
   select * from emp order by age desc	#降序，asc升序
   
   #分页
   select * from emp limit 4, 3	#3代表每页的记录数，2代表当前页开始显示位置(从第一页的0开始)
   
   #分组：对统计的结果进行分组
   select count(*), deid from emp group by deid	#标准sql要求分组的字段需要出现在查询结果中
   

2. 聚合函数

   函数实际上是对查询的结果进行处理

   # count()对查询结果进行统计
   SELECt COUNT(*) num FROM emp 
   SELECt COUNT(*) FROM emp WHERe age > 40
   
   # sum()对查询结果进行求和
   SELECt SUM(age) FROM emp
   
   # avg()求平均
   SELECt AVG(age) FROM emp
   SELECt CAST(AVG(age) AS DECIMAL(10, 2)) FROM emp	#转换类型
   
   # max()求最大
   SELECt MAX(age) FROM emp
   
   # min()求最小
   SELECt MAX(age) FROM emp
   

3. having查询

   where的条件内部不可以是函数，having相当于对where进行补充

   # 查询年龄大于12的部门
   select edid, dname, max(age) from emp e, dept d
   	where emp.edid = dept.did
   	group by edid			
   	having max(age) > 12	#对上面的结果再进行处理
   	order by desc
   	limit 5, 4
   

   查询的顺序是where->group by->having->order by->select->limit

4. 子查询

   # 查询比平均的年龄大
   	#查询平均年龄
   	select avg(age) from emp
   	#查询比平均年龄大
   	select * from emp where age > (select avg(age) from emp)
   

2. 多表查询 笛卡尔积

select * from dept d, emp e

内连接

# 表1 inner join 表2 on 连接条件
select * from dept d inner join emp e on d.did = e.edid
select * from dept d, emp e where d.did = e.edid

外连接

• 左外连接

  # 表1 left outer join 表2 on 连接条件
  select * from dept d left outer join emp e on d.did = e.edid
  

• 右外连接

  # 表1 right outer join 表2 on 连接条件
  select * from dept d right outer join emp e on d.did = e.edid
  

独有与全有

就是在外连接基础上加上条件

#A表独有
SELECt * FROM dept d LEFT OUTER JOIN emp e ON d.did = e.edid WHERe eid IS NULL
#B表独有
SELECt * FROM dept d RIGHT OUTER JOIN emp e ON d.did = e.edid WHERe did IS NULL
#AB全有
SELECt * FROM dept d LEFT OUTER JOIN emp e ON d.did = e.edid
UNIOn
SELECt * FROM dept d RIGHT OUTER JOIN emp e ON d.did = e.edid
#AB独有
SELECt * FROM dept d LEFT OUTER JOIN emp e ON d.did = e.edid WHERe eid IS NULL
UNIOn
SELECt * FROM dept d RIGHT OUTER JOIN emp e ON d.did = e.edid WHERe did IS NULL

三、事务

事务是数据库操作最基本单元，在逻辑上一组操作，要么都成功，如果有一失败，所有都失败。如银行转账

1. 概述 1.1 基本特性

1. 原子性：有多个操作，要么都成功，如果有一个失败，则所有都失败
2. 一致性：操作之前和之后总量不变的
3. 隔离性：并发操作中，多事务操作，多事务之间不互相影响
4. 持久性：当事务操作完成之后，进行事务提交，数据库数据真正发生变化

1.2 隔离性问题

如果不考虑事务隔离性，并发操作中，会产生三个读问题

1. 脏读：一个未提交事务，读取到另外一个未提交事务操作数据
2. 幻读：一个未提交事务，读取到另外一个已提交事务添加操作
3. 不可重复读：一个未提交事务，读取到另外一个已提交事务修改操作

可以通过设置隔离级别来避免这些问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0g43ZjFS-1633826875199)(C:Users10139AppDataRoamingTyporatypora-user-imagesimage-20211009114047223.png)]

2. 操作事务

1. 开启事务

   START TRANSACTION;
   

2. 执行事务操作

   update emp set ename = 'abc' where eid = 2;
   

3. 提交/回滚事务

   rollback;
   commit;
   

四、JDBC 1. 概述 1.1 作用

JDBC(Java Database Connectivity)是java操作数据库一套规范（接口），由具体数据库提供这些规范实现，以jar包形式提供

Java中数据库存取技术分：JDBC直接访问数据库、JDO(java data object)、第三方O/R工具(Mybitis等)，JDBC是java访问数据库的基石，其他的方式是对JDBC进行了封装

1.2 使用步骤

1. 加载数据库驱动
   • 将数据库实现jar包导入到项目中
   • 通过反射代码加载驱动对象
2. 创建数据库连接
3. 编写sql语句，执行sql语句
4. 若执行查询操作，会得到结果集，遍历结果集得到结果
5. 关闭资源

2. JDBC的使用 2.1 增删改查操作 导入jar包

1. 在当前项目下创建lib文件夹下的包，将jar包复制到文件夹下
2. 右击已经在加入到文件夹下的包，添加到库中

statement方式

public static void testSelect() throws Exception {
        // 1.加载驱动(高版本可以省略)
        Class.forName("com.mysql.jdbc.Driver");
        
    	// 2.连接数据库
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1",
                "root", "123456");
        // 3.执行操作
    	Statement statement = connection.createStatement();
    		//增
        String sql = "insert into emp values (10, 'aaa', 1, 1)";
    	int ret = statement.executeUpdate(sql);	//返回影响的行数
    		//删
		sql = "delete from emp where eid = 10";
    	ret = statement.executeUpdate(sql);
    		//改
        sql = "update emp set ename = 'bbb' where eid = 10";
    	ret = statement.executeUpdate(sql);	
    		//查
        sql = "select * from emp";
        ResultSet resultSet = statement.executeQuery(sql);
        
    	// 4.得到结果
        while (resultSet.next()) {
            //可以全部用String类型，推荐使用各自字段的类型
            System.out.println(resultSet.getInt("eid"));
            System.out.println(resultSet.getString("ename"));
            System.out.println(resultSet.getInt("age"));
        }
        // 5.关闭资源
        resultSet.close();
        statement.close();
        connection.close();
}

PreparedStatement方式

Statement方式执行语句存在不足

1. SQL语句拼接有问题
2. SQL注入问题(因sql语句拼接引起的缺陷)
3. 无法处理Blob类型数据(文件类型)

为了解决该问题，建议使用PreparedStatement对象，其先确定要执行的语句，在单独执行

// 执行操作
String sql = "select * from emp where ename = ? and dname = ?";	//设置执行语句，？代表通配符
PreparedStatement prestate = connection.prepareStatement(sql);	//获取psd对象
prestate.setString(1, "abc");	//补充通配符
prestate.setString(2, "研发部");
int ret = pst.executeUpdate();	//执行语句

2.3 其他操作 获取自动增长的值

String ename = "abc";
int age = 10;
String sql = "insert into emp values(null, ?, ?)";
PreparedStatement pst = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);	//在sql语句参数后加一个常量，实值为1
pst.setString(1, ename);	
pst.setInt(2, age);
int ret = pst.executeUpdate();	//执行语句

//获取自动增长
ResultSet resSet = pst.getGeneratedKeys();
while(resSet.next()) {
    //遍历自动正增长得值
}

批处理操作

批处理操作就是循环调用执行语句，但每次执行一次就调用一次execute函数效率太低，先addBatch()添加到缓存，在使用executeBatch()统一执行

//执行命令
String sql = "insert into emp values (null, ?, ?)";
PreparedStatement pst = conn.prepareStatement(sql);
for (int i = 0; i < 10; i++) {
      pst.setString(1, "abc"+i);
      pst.setInt(2, 1+i);
    
      pst.addBatch();	//添加到缓存
}
pst.executeBatch();	//批处理执行操作

事务操作

JDBC默认开启事务自动提交，若想要手动提交/回滚需要在执行前关闭自动提交，当没抛异常时提交，抛出异常时回滚

Connection conn = null;
try {
    //加载驱动
    Class.forName("com.mysql.jdbc.Driver");
    //创建连接
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3066/db1");
    //开启事务
    conn.setAutoCommit(false);
    //执行命令
    String sql = "insert into emp values (null, ?, ?)";
    PreparedStatement pst = conn.prepareStatement(sql);
    pst.setString(1, "abc");
    pst.setInt(2, 1);
    pst.executeUpdate();
    //提交结果
    conn.commit();
} catch (Exception e) {
    //回滚
    try {
        conn.rollback();
    } catch (SQLException e1) {
        e1.printStackTrace();
    }
    e.printStackTrace();
}

五、德鲁伊连接池

1. 预先创建好一些连接到数据库连接池中，当使用时直接从连接池里调用，用完归还连接。
2. 可以通过实现DataSource自己创建连接池，但一般都是用开源已有连接池

1. 使用步骤

url=jdbc:mysql://localhost:3306/db1
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver
initialSize=10	//有默认，可以省略
maxActive=20	//有默认，可以省略
maxWait=1000	//有默认，可以省略

//创建preproties对象获取配置文件信息
Properties properties = new Properties();
//通过类加载器获取配置文件位置
properties.load(JdbcTest.class.getClassLoader().getResourceAsStream("db.properties"));
//创建德鲁伊数据源
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//获取连接
Connection connection = dataSource.getConnection();
System.out.println(connection);

2. 连接池工具类 2.1 ThreadLocal

ThreadLocal用于保存某个线程共享变量，原因是在Java中，每一个线程对象中都有一个ThreadLocalMap，其key就是一个ThreadLocal，而Object即为该线程的共享变量。而这个map是通过ThreadLocal的set和get方法操作的。对于同一个static ThreadLocal，不同线程只能从中get，set，remove自己的变量，而不会影响其他线程的变量。

1. ThreadLocal.get: 获取ThreadLocal中当前线程共享变量的值。

2. ThreadLocal.set: 设置ThreadLocal中当前线程共享变量的值。

3. ThreadLocal.remove: 移除ThreadLocal中当前线程共享变量的值。

2.2 连接池工具类实现

public class JdbcUtils {
    private static DataSource dateSource;
    private static ThreadLocal threadLocal;
    //获取连接池
    static {
        try {
            //加载配置文件
            Properties properties = new Properties();
            properties.load(JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"));
            //创建连接池
            dateSource = DruidDataSourceFactory.createDataSource(properties);

            //初始化ThreadLocal
            threadLocal = new ThreadLocal<>();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    //获取连接
    public Connection getJdbcConn() {
        Connection connection = threadLocal.get();
        if (connection == null) {
            try {
                threadLocal.set(dateSource.getConnection());
                connection = threadLocal.get();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return connection;
    }

    //关闭(归还)连接
    public void removeJdbcConn() {
        Connection connection = threadLocal.get();
        if (connection != null) {
            try {
                connection.close();
                threadLocal.remove();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

工具类使用

public class JdbcTest {
    public static void main(String[] args) {
        JdbcUtils jdbcUtils = new JdbcUtils();
        Connection jdbcConn = jdbcUtils.getJdbcConn();
        String sql = "select * from emp";
        try {
            PreparedStatement pst = jdbcConn.prepareStatement(sql);
            ResultSet resultSet = pst.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("eid"));
                System.out.println(resultSet.getString("enmae"));
                System.out.println(resultSet.getString("age"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.removeJdbcConn();
        }
    }
}

3. DBUtils

DBUtils是对数据的操作进行封装，并不能提高效率

3.1 增删改

//创建对象
QueryRunner queryRunner = new QueryRunner();
//执行操作
queryRunner.update(connection, sql1, 1);	//参数：连接、sql语句、通配符参数

3.2 查

1. 创建数据对应的类

   class Emp {
       private int eid;
       private String ename;
       private int age;
       
       ...
   }
   

2. 操作

   //创建对象
   QueryRunner queryRunner = new QueryRunner();
   //执行操作
   Emp emp = queryRunner.query(connection, sql2, new BeanHandler(Emp.class), 1);	//参数：数据集合，获取数据的数量
   sout(emp);	//打印输出
   
   
   
   List list = queryRunner.query(connection, sql2, new BeanListHandler(Emp.class));	//参数：数据集合，获取数据的数量
   sout(list);	//打印输出
   
   

4. Dao层封装

1. 定义接口

   public interface SqlDao {
       //添加
       public void insertDept(Dept dept);
   
       //修改
       public void updateDept(Dept dept);
   
       //删除
       public void removeDept(int did);
   
       //根据id查询
       public Dept findDeptById(int did);
   
       //查询所有记录
       public List findAll();
   }
   

2. 泛型封装：内部调用DBUtils的接口，调用封装好的德鲁伊连接池工具类

   public class BasicSqlDaoImpl {
       //封装查询返回一条记录
       public  T getBean(Connection conn,String sql,Class type,Object...params) {
           QueryRunner runner = new QueryRunner();
           try {
               T t = runner.query(conn, sql, new BeanHandler(type), params);
               return t;
           } catch (SQLException e) {
               e.printStackTrace();
           }
           return null;
       }
   
       //封装查询返回多条记录
       public  List getListBean(Connection conn,String sql,Class type,Object...params) {
           QueryRunner runner = new QueryRunner();
           try {
               List list = runner.query(conn, sql, new BeanListHandler(type), params);
               return list;
           } catch (SQLException e) {
               e.printStackTrace();
           }
           return null;
       }
   }
   

3. 具体类型的操作：调用

   public class DeptDaoImpl extends BasicDeptDaoImpl implements DeptDao {
       @Override
       public void insertDept(Dept dept) {
   
       }
   
       @Override
       public void updateDept(Dept dept) {
   
       }
   
       @Override
       public void removeDept(int did) {
   
       }
   	
       // 单条查询
       @Override
       public Dept findDeptById(int did) {
           String sql = "select * from dept where did=?";
           Dept dept = getBean(JdbcUtils.getConnection(), sql, Dept.class, did);
           return dept;
       }
   	//多条查询
       @Override
       public List findAll() {
           String sql = "select * from dept";
           List listBean = getListBean(JdbcUtils.getConnection(), sql, Dept.class);
           return listBean;
       }
   }
   

4. 具体类型的操作：调用

   public class DeptDaoImpl extends BasicDeptDaoImpl implements DeptDao {
       @Override
       public void insertDept(Dept dept) {
   
       }
   
       @Override
       public void updateDept(Dept dept) {
   
       }
   
       @Override
       public void removeDept(int did) {
   
       }
   	
       // 单条查询
       @Override
       public Dept findDeptById(int did) {
           String sql = "select * from dept where did=?";
           Dept dept = getBean(JdbcUtils.getConnection(), sql, Dept.class, did);
           return dept;
       }
   	//多条查询
       @Override
       public List findAll() {
           String sql = "select * from dept";
           List listBean = getListBean(JdbcUtils.getConnection(), sql, Dept.class);
           return listBean;
       }
   }