一、DDL操作
1. 操作库
-
创建数据库
#建库 create database mydb1; #建库并设置字符集 create database mydb2 character set utf8; #设置字符集并进行类型检查 create database mydb3 character set utf8 collate utf8_general_ci; -
查看数据库
#查看数据库 show databases; #查看创库语句 show create database mydb1; -
修改数据库
#修改库字符集 alter database mydb1 character set utf8; -
删除数据库
drop database mydb3;
2. 表结构操作
2.1 创建表
（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）多表关系
-
一对一关系
两张表的主键对应主键
-
一对多关系
在多的那一方创建字段，作为外键，关联一的那一方主键
-
多对多关系
创建第三张表，在第三张表至少有两个字段作为外键，关联两个表的主键
以一对多为例
# 一对多
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 #报错，因为有关联
由于声明外键后，另一张表的主键与当前表的外键联系起来，所以会导致增删不灵活(如有关联时就无法删除主键)，所以一般都是逻辑上关联即可，不需要真实的在语法上关联。
（3）约束
-
主键约束
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") #报错 -
唯一约束
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开发') #报错 -
非空约束
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) #报错 -
缺省值
default# 缺省：字段默认值 CREATE TABLE person ( id INT, pname VARCHAR(100) DEFAULT 'lucy' ) INSERT INTO person VALUES(1, 'jack') INSERT INTO person (id) VALUES(1) #lucy -
综合演示
## 综合演示 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 ) -
索引
索引是一个单独的、物理的数据库结构
（1）不过度索引
（2）索引条件列(where后面最频繁的条件比较适宜索引)
（3）索引散列值,过于集中的值不要索引，例如:给性别"男","女"加索引,意义不大
create index index_name on dept (id) -
自动增长
## 自动增长和索引 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.2 修改表
#修改表名
rename table t1 to t2;
#添加字段
alter table t2 add column height double;
#修改字段类型
alter table t2 modify column height float;
#修改字段名称
alter table t2 change column name age int;
#删除字段
alter table t2 drop column height;
2.3 查删表
#查看当前库中的表
show tables;
#显示创表语句
show create table t1;
#查看表结构
desc t1;
二、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 #删除整张表再重建整张表
查询操作
-
简单查询
#查询操作 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要求分组的字段需要出现在查询结果中 -
聚合函数
函数实际上是对查询的结果进行处理
# 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 -
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 -
子查询
# 查询比平均的年龄大 #查询平均年龄 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 隔离性问题
如果不考虑事务隔离性，并发操作中，会产生三个读问题
- 脏读：一个未提交事务，读取到另外一个未提交事务操作数据
- 幻读：一个未提交事务，读取到另外一个已提交事务添加操作
- 不可重复读：一个未提交事务，读取到另外一个已提交事务修改操作
可以通过设置隔离级别来避免这些问题
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0g43ZjFS-1633826875199)(C:\Users\10139\AppData\Roaming\Typora\typora-user-images\image-20211009114047223.png)]
2. 操作事务
-
开启事务
START TRANSACTION; -
执行事务操作
update emp set ename = 'abc' where eid = 2; -
提交/回滚事务
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 使用步骤
- 加载数据库驱动
- 将数据库实现jar包导入到项目中
- 通过反射代码加载驱动对象
- 创建数据库连接
- 编写sql语句，执行sql语句
- 若执行查询操作，会得到结果集，遍历结果集得到结果
- 关闭资源
2. JDBC的使用
2.1 增删改查操作
导入jar包
- 在当前项目下创建lib文件夹下的包，将jar包复制到文件夹下
- 右击已经在加入到文件夹下的包，添加到库中
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方式执行语句存在不足
- SQL语句拼接有问题
- SQL注入问题(因sql语句拼接引起的缺陷)
- 无法处理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();
}
五、德鲁伊连接池
- 预先创建好一些连接到数据库连接池中，当使用时直接从连接池里调用，用完归还连接。
- 可以通过实现DataSource自己创建连接池，但一般都是用开源已有连接池
1. 使用步骤
<!--db.properties配置文件-->
<!--采用key=value格式，德鲁伊连接池的key值是固定的-->
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<ThreadLocal, Object>，其key就是一个ThreadLocal，而Object即为该线程的共享变量。而这个map是通过ThreadLocal的set和get方法操作的。对于同一个static ThreadLocal，不同线程只能从中get，set，remove自己的变量，而不会影响其他线程的变量。
-
ThreadLocal.get: 获取ThreadLocal中当前线程共享变量的值。
-
ThreadLocal.set: 设置ThreadLocal中当前线程共享变量的值。
-
ThreadLocal.remove: 移除ThreadLocal中当前线程共享变量的值。
2.2 连接池工具类实现
public class JdbcUtils {
private static DataSource dateSource;
private static ThreadLocal<Connection> 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 查
-
创建数据对应的类
class Emp { private int eid; private String ename; private int age; ... } -
操作
/********获取一条数据*********/ //创建对象 QueryRunner queryRunner = new QueryRunner(); //执行操作 Emp emp = queryRunner.query(connection, sql2, new BeanHandler<Emp>(Emp.class), 1); //参数：数据集合，获取数据的数量 sout(emp); //打印输出 /********获取多条数据*********/ List<Emp> list = queryRunner.query(connection, sql2, new BeanListHandler<Emp>(Emp.class)); //参数：数据集合，获取数据的数量 sout(list); //打印输出
4. Dao层封装
-
定义接口
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<Dept> findAll(); } -
泛型封装：内部调用DBUtils的接口，调用封装好的德鲁伊连接池工具类
public class BasicSqlDaoImpl { //封装查询返回一条记录 public <T> T getBean(Connection conn,String sql,Class<T> type,Object...params) { QueryRunner runner = new QueryRunner(); try { T t = runner.query(conn, sql, new BeanHandler<T>(type), params); return t; } catch (SQLException e) { e.printStackTrace(); } return null; } //封装查询返回多条记录 public <T> List<T> getListBean(Connection conn,String sql,Class<T> type,Object...params) { QueryRunner runner = new QueryRunner(); try { List<T> list = runner.query(conn, sql, new BeanListHandler<T>(type), params); return list; } catch (SQLException e) { e.printStackTrace(); } return null; } } -
具体类型的操作：调用
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<Dept> findAll() { String sql = "select * from dept"; List<Dept> listBean = getListBean(JdbcUtils.getConnection(), sql, Dept.class); return listBean; } } -
具体类型的操作：调用
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<Dept> findAll() { String sql = "select * from dept"; List<Dept> listBean = getListBean(JdbcUtils.getConnection(), sql, Dept.class); return listBean; } }