MySQL基础

目录

级联操作CASCADE

级联删除和级联修改

多表关系（设计数据库的三大范式）

表和表的关系问题

数据库的三大范式：（理论）

多表查询

内链接

外链接：

子查询

数据库的事务

数据库的事物：

传统事务特点（重点）ACID:

传统事物的隔离级别：

通过sql指令控制事物

JDBC

JDBC七大步骤

JDBC针对DQL语句，遍历集合结果集

junit单元测试的使用步骤

视图View

sql注入：原因是由于sql语句的实际参数存在字符串拼接，导致语句出现漏洞！

Statement和PrepardStatement的区别

数据库连接池

德鲁伊Druid配置文件的参数说明

数据库连接池的好处：

Druid连接池的使用步骤：

加入连接池后自定义工具类的步骤：

JDBA控制事务

工具类库:commons-dbutils

---

级联操作CASCADE

级联操作：CASCADE（修改以及删除主表数据，从表数据随之改变）

级联删除和级联修改

1、创建表时添加外键的同时后面添加级联删除以及级联修改

CREATE TABLE employee (
  字段名称1 字段类型1,
  字段名称2 字段类型2,
  ....,
  
  dept_id INT,  -- 外键要作用的字段名称
  -- 所在的部门编号
  -- 声明 
  CONSTRAINT 
  外键名称     -- 外键名称:主表名_从表名_fk
  FOREIGN KEY (外键作用的从表的字段名称)  -- 外键作用在哪个字段上
  
  REFERENCES -- 关联
  主表名(主键字段)-- 主表的主键字段(就是id)
  on update cascade 
  on delete cascade 
) ;

CREATE TABLE employee (
  id INT PRIMARY KEY AUTO_INCREMENT,
  -- 员工编号
  NAME VARCHAR (10),
  -- 员工姓名
  gender VARCHAR (3),
  -- 员工性别
  salary DOUBLE,
  -- 工资
  dept_id INT,
  -- 所在的部门编号
  -- 声明 
  CONSTRAINT -- 外键名称:主表名_从表名_fk
  dept_emp_fk -- 外键作用在哪个字段上
  FOREIGN KEY (dept_id) -- 关联
  REFERENCES -- 主表的主键字段(就是id)
  dept(id)
  
  ON UPDATE CASCADE 	-- 级联修改
  ON DELETE CASCADE 	-- 级联删除
) ;

2、通过sql添加外键的同时，设置级联操作（级联修改和级联删除）

alter table 表名 add
        constraint 外键名称
         foreign key  (外键作用从表的字段名称)
         references 主表名(主键字段)
         on update cascade
         on delete cascade ;

多表关系（设计数据库的三大范式）

表和表的关系问题

    一般情况:两张表一个外键
    两张表以上以上,可以设置多个外键
    
    一对多
            部门和员工的关系
            从部门维度--->一个部门中包含多个员工
            从员工维度---> 一个员工从属于某个部门
            
            用户和订单
                一个用户(用户维度):一个用户可以下多个订单
                订单(订单维度):一个订单在某个时刻属于某个用户的
                        一个订单可以可以从属于多个用户的
                
    多对多
            学生表和选课表
            一个学生可以选择多个课程
            一个课程被多个学生选择
            
            用户表和角色表   (用户表和角色表中间表)
            
                从用户维度--看角色:一个用户有多个角色
                从角色维度--看用户:一个角色赋予多个用户
            角色表和权限表     (角色表和权限表中间表)
                从角色表维度--看权限:一个角色可以多个权限
                                CEO:crud(添加,查询,修改和删除)
                从权限维度---看角色:一个权限可以被多个角色赋予
                                查询权限:
                                        CEO
                                        hr
                                        root管理员
                                        普通管理员

数据库的三大范式：（理论）

1NF：保证数据库表中的每一列是不能再拆分的原子数据项（单独某一列）

2NF：在1NF的基础上，数据库表中每一个非主键字段必须完全以来主键字段

        1）一张表描述一件事情

        2)非主键字段必须完全依赖主键字段

3NF：在2NF的基础上，数据库中每一个非主键字段不能产生传递依赖（必须在2NF上满足非主键字段必须完全依赖主键字段）

多表查询

基本语法：

select 指定字段名称 from 表名1，表名2；

SELECT
   emp.`id`	'员工编号',
   emp.`name`	'员工姓名',
   emp.`gender`	'员工性别',
   emp.`salary`	'员工工资',
   emp.`join_date` '入职日期',
   dept.`name`  '部门名称'   
FROM
   emp,
   dept;

内链接

隐式内链接：就是利用where语句完成条件拼接（&&或者and关键字）

select 字段列表 from 表名1，表名2 where 连接条件1 and... 连接条件2...

-- 查询员工表中的id,姓名,性别,工资,入职日志以及部门表的部门名称
SELECT 
  e.`id` '员工编号',
  e.`name` '员工姓名',
  e.`gender` '性别',
  e.`salary` '工资',
  e.`join_date` '入职日期',
  d.`name` '部门名称' 
FROM

  emp e,
  -- 给表起一个别名
  dept d -- d是部门表的别名
WHERE e.`dept_id` = d.`id` ;

显示内连接：inner join

select 字段列表 from 表名1（inner）join 表名2 on 连接条件1 and 连接条件2...

-- 查询员工表名的姓名,工资,入职日期以及部门表的部门名称
SELECT 
  t1.`name` '员工姓名',
  t1.`salary` '工资',
  t1.`join_date` '入职日期',
  t2.`name` '部门名称' 
FROM
  emp t1 
   INNER JOIN dept t2 
    ON t1.`dept_id` = t2.`id` ;

外链接：

左外连接/右外连接：

一般情况常用左外连接：将左边的数据全部查询以及两张表之间的交集数据(连接条件的数据)全部查询!

select
       指定字段列表
    from 表名1 
    left outer join 表名2     / right outer join  右外连接
    on 连接条件

SELECT
   t1.*,  -- 查询员工的所有数据
   t2.`id` '部门编号',
   t2.`name` '部门名称' -- 部门名称
FROM
   emp t1
LEFT OUTER JOIN 
   dept t2
ON 
   t1.`dept_id` = t2.`id` ;

子查询

1、利用聚合函数查询出来的单列的数据和select语句进行嵌套

-- 查询最高工资的员工信息以及员工所在的部门信息
-- 1)查询出最高工资是多少
-- select max(salary) from emp ; -- 9000
-- 2)查询员工工资是9000的员工信息以及部门信息
/*
select 
    t1.* , -- 员工所有信息
    t2.*   -- 部门所有信息
from 
   emp t1,
   dept t2
where 
     t1.`salary` = 9000  and t1.`dept_id` = t2.`id`;
     */
     
-- 一步走:
SELECT 
  t1.*,
  -- 员工所有信息
  t2.* -- 部门所有信息
FROM
  emp t1,
  dept t2 
WHERE t1.`dept_id` = t2.`id` 
  AND t1.`salary` = 
  (SELECT 
    MAX(salary) 
  FROM
    emp) ;

2、利用子查询 in（值1，值2，值3集合数据）；

SELECT 
  e.*,
  d.`id`   '部门编号',
  d.`name` '部门名称' 
FROM
  emp e,
  dept d 
WHERE e.`dept_id` = d.`id` 
  AND e.`dept_id` IN (2, 3) ;
  
-- 一步走
SELECT 
  e.*,
  d.`id` '部门编号',
  d.`name` '部门名称' 
FROM
  emp e,
  dept d 
WHERE e.`dept_id` = d.`id` 
  AND e.`dept_id` IN 
  (SELECT 
    id 
  FROM
    dept 
  WHERE NAME IN('市场部','财务部') ) ;

3、将某张表select的结果集---当做一张虚表和其他表建立连接查询

SELECT 
  t1.*,
  -- 所有的部门信息
  t2.* -- 所有员工信息
FROM
  dept t1 
  INNER JOIN 
    (SELECT 
      * 
    FROM
      emp 
    WHERE emp.`join_date` > '2021-10-07') t2 
    ON t1.`id` = t2.`dept_id` ;
    
    
 -- 优化隐式内连接
SELECT 
  e.*,
  d.* 
FROM
  emp e,
  dept d 
WHERE e.`dept_id` = d.`id` 
  AND e.`join_date` > '2021-10-07' ;
SELECT * FROM dept ;
SELECT * FROM emp;

数据库的事务

数据库的事物：

事务就是DBA（数据库管理员）操作多个sql的时候，可能有一条执行语句可能异常了，其他sql都不执行，就会导致出现紊乱，将整个sql的执行看做一个“整体”，要么同时执行，成功，要么同时执行失败！

始终保证数据的一致性（高并发中，读/写）

-- 创建一个账户表account
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 账户id
	NAME VARCHAR(10)		 , -- 账户名称
	balance INT                        -- 账户余额
);


-- 插入两个账户信息
INSERT INTO account(NAME,balance) VALUES('zhangsan',1000),('lisi',1000) ;


-- zhangsan 给 lisi转账500
UPDATE account SET balance = balance - 500 WHERE id = 1 ;
-- 出问题了,这块不执行了
UPDATE account SET balance = balance + 500 WHERE id = 2 ;


UPDATE account SET balance = 1000 ;

-- 将 "转账业务"看成一个整体,
-- 开启事务
START TRANSACTION ; -- 将自动提交切换为手动提交
UPDATE account SET balance = balance - 500 WHERE id = 1 ;
 -- 出问题了,这块不执行了
UPDATE account SET balance = balance + 500 WHERE id = 2 ;

-- 有问题回滚;事务回滚:撤销之前的所有操作,默认回滚到执行更新之前的数据
ROLLBACK ;

-- 没有问题,正常提交事务

COMMIT ; -- 不提交,不会永久性的更改数据---

传统事务特点（重点）ACID:

原子性：执行的某业务操作，同时操作多个sql（增删改），要么同时执行成功，要么同时执行失败!

一致性：在实际开始发中，多个线程同时去写和读时，数据必须一致！

隔离性：事务与事物之间，互相独立，互不影响！

持续性：就是对增删改zhexiesql语句，一旦事务提交了，对数据库中表的操作是永久性的，即使关机了，数据永久保存！

传统事物的隔离级别：

查询数据库的隔离基本：

select  @@tx_isolation;    -- mysql8.0服务器版---SELECT @@transaction_isolation

设置隔离级别：

set global transaction isolstion level (级别的名称) ;

执行效率从大到小，安全性：从低到高

读未提交：read uncommited；

一个事务读取到另一个没有提交的事务（最不安全），出现“脏读”（最严重的问题！）

读已提交：read committed；

有效防止脏读！会出现另一个问题“不可重复读”，

读取到自己本身没有提交的事务的数据前后两次不一致，本身这个事物没有提交，多次读取到这个事务必须一致！

可重复读：mysql的默认隔离级别（repetable-read）

有效防止脏读，不可重复读，可能出现“幻读”

串行话：sertializable 

通过sql指令控制事物

-- 开启事务:将mysql自动提交,切换手动提交事务
start transaction ;
-- 设置回滚命令
rollback;
-- 永久更新数据(保证数据的持久性)
commit ;

-- 设置隔离级别
set global transaction isolation level 级别名称;
-- 查看隔离级别
select @@tx_isolation ;

JDBC

JDBC：Java连接数据库的规范，可以使用Java语言连接数据库完成CRUD操作

JDBC的本质:是一个普通的java类，是数据库厂商提供的驱动jar包，可以实现sun提供一套接口规范！

JDBC七大步骤

1.导包
    mysql5.1的jar包---com.mysql.jdbc.Driver 驱动类
    mysql8.0的jar包---com.mysql.cj.jdbc.Driver 驱动类

2.注册驱动

     Class.forName("com.mysql.jdbc.Driver") ;  获取正在运行的这个类的字节码文件对象:
        1)加载这个类会执行com.mysql.jdbc.Driver这个类的staitc静态代码块,立即注册驱动!
        2)为了向下兼容!

  3.准备sql语句---定义String类型,里面书写插入/修改/删除
  静态sql语句,里面的参数要么键盘录入,要么直接给值,存在字符串拼接!
  String sql = "insert into account(name,balance) values('文章',1000);" ;

  4.获取数据库的连接对象 java.sql.Connnection
  java.sql.DriverManager类:
       提供静态方法:
       public static Connection getConnection(String url,
                                              String user,
                                              String password)
                                       throws SQLException
        url:统一资源定位符
            jdbc:mysql://本地默认localhost或者127.0.0.1:端口号/库名    (mysql驱动包5.1jar包这个地址不需要带参数)
                                                                    mysql驱动包8.0jar包后面带参数
            jdbc:mysql://本地默认localhost或者127.0.0.1:端口号/库名?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
                                                                       编码格式/是否启用证书(不启用,都是默认密码登录)/时区/是否允许数据库公钥验证(默认true):验证登录的密码
            端口号:3306 (mysql)
        user:用户名:
        password:登录mysql的密码

  java.sql.Connection:接口:代表的与特定数据库的连接会话

5.获取执行对象执行静态sql语句
        Statement createStatement()

 6.执行对象sql

java.sql.Statement:在数据库中执行sql语句的接口

            针对DML语句(insert into/delete/update) ,直接影响了的行数,返回值int
            针对ddl语句(创建表/修改表/删除表):返回值是0
            int executeUpdate(String sql)throws SQLException

            执行DQL语句,数据库查询语句 select语句

            ResultSet executeQuery(String sql)throws SQLException

  java.sql.ResultSet:获取数据表的结果集  (接口)
        获取查询的多条件记录或者单条记录或者查询单行单列数据....
7.释放资源

 xxx.close（）；

// 7大步骤 后期--->开源框架:就是对原生Jdbc代码的封装+一些配置文件
//1)导入驱动jar包 --mysql-connnecto-java驱动jar包  
//2)注册驱动
Class.forName("com.mysql.jdbc.Driver") ; 
//3)获取数据库的连接对象
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/库名",
"root",
"123456") ;
//4)准备sql语句:DDL语句或者DML语句
String sql = "insert into employee(name,age,gender,birthday,address) values(xxx,xx,xx,xx,xx,xx)" ;
//5)通过连接对象获取执行对象Statement 
Statement stmt  = conn.createStatement() ;
//6)执行sql,通用的更新
int count = stme.executeUpdate(sql) ;
//7)释放资源
stmt.close() ;
conn.close() ;

JDBC针对DQL语句，遍历集合结果集

// 7大步骤 后期--->开源框架:就是对原生Jdbc代码的封装+一些配置文件
//1)导入驱动jar包 --mysql-connnecto-java驱动jar包  
//2)注册驱动
Class.forName("com.mysql.jdbc.Driver") ; 
//3)获取数据库的连接对象
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/库名",
"root",
"123456") ;
//4)准备sql语句:DDL语句或者DML语句
String sql = "select 指定的字段列表  from 表名" ;
//5)通过连接对象获取执行对象Statement 
Statement stmt  = conn.createStatement() ;
//6)执行sql,通用的查询操作
ResultSet rs = stmt.executeQuery(sql) ;

//遍历结果集
while(rs.next()){
    //获取数据
    //通用的写,列的名称获取/列的索引值获取
    XXX 变量= rs.getXXX("字段名称") ;
    //自己封装数据...
}

//7)释放资源
stmt.close() ;
conn.close() ;

自定义工具类jdbcUtils的步骤

//先定义一个jdbc.properties文件
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/库名
user=root
password=123456
//创建一个类
class JdbcUtils{
    private static String user = null ;
    private static String password = null ;
    private static String driverClass = null ;
    private static String url = null ;
    
    //定义static代码块
    static{
        //读取配置文件
        Properties prop = new Properties() ;
        //获取key对应的value,赋值给上面四个成员变量
        InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties") ;
        user = prop.getProperty("user") ;
        password = prop.getProperty("password") ;
        url = prop.getPrpperty("url") ;
        driverClass = prop.getProperty("driverClass") ;
        //注册驱动
        Class.forName(driverClass) ;
    }
    
    
 
    private JdbcUtils(){}
    public static Connnection getConnection(){
        	Connection conn = DriverManager.getConnection(url,user,password) ;
        return conn ;
    }
    //释放资源
    //带三个参数:ResultSet ,Statment,Connection---针对DQL语句操作
    //带两个参数:Satement,Connection--->针对DML语句
}

junit单元测试的使用步骤

1)导入junit的核心包: 4.13.1以及依赖包
2)建立测试类---XXXTest
3)定义单元测试方法,在方法上加入@Test 
4)方法中要么Assert断言,要么直接测试指定功能(XXXDao--->添加,删除,修改,查询的功能)

视图View

视图：是一个虚拟表，只是定义视图，表的数据来源于基础表中的（数据库中真实存在的）

作用:保证数据的安全性/简单性(快速查看到真实表的结构,类型....)

视图的基础语法:如何创建视图,修改实体,更新视图等等

1、创建视图
        CREATE VIEW <视图名> AS <SELECT语句>

2、查看视图的结构---查询表的结构语法相同
查询基础表(employee) 它的结构
        DESC employee ;
 查询指定视图的结构
        DESCRIBE view_employee ; -- 简写成功desc 视图名称;
查询当前库中的所有表以及视图的名称
        SHOW TABLES ；
查询当前库中的所有表以及视图的名称以及后面的table_type:表类型
        SHOW FULL TABLES ;

3、修改视图的内容
        ALTER VIEW <视图名> AS <SELECT语句>

更新视图(视图添加数据/修改数据/删除数据,会直接影响基础表的内容,
一般很少去使用视图去更新基础表的内容)

4、删除视图:就是drop  view 视图名称,多个视图名称;
        只是的删除视图的定义,不会影响基础表的任何内容

5、重命名视图跟表的重命名语法一样
        renname  table 视图名 to 新视图名;

sql注入：原因是由于sql语句的实际参数存在字符串拼接，导致语句出现漏洞！

Statement和PrepardStatement的区别

1、共同点

都是可以发送sql到数据库的，都是执行对象，后者继承前者（java.sql.包下的接口）

2、不同点

1）是否会造成sql注入

Statement：永远执行的是静态sql语句:语句存在"硬编码",存在sql的字符串拼接,就造成sql注入,不安全

ProparedStatement：永远指定的是参数化的sql语句，全部参数是“？”，占位符号，有效防止sql的拼接，预防sql注入，提高sql的安全性！

2）是否提高sql执行效率

Statement:不会提高sql执行效率,获取执行对象,然后将sql语句发送数据库(没写一个sql,发一次!),频繁的操作访问数据库
PreparedStatement:大大提高SQL执行效率参数化sql是在获取PreparedStatement,就已经发送给数据库了,然后在PreparedStatement赋值不同的值;

数据库连接池

德鲁伊Druid配置文件的参数说明

#这些名称都是DruidDataSoure提供的参数
#连接数据库的驱动类
driverClassName=com.mysql.jdbc.Driver   
#连接数据库的url地址:统一资源定位符
url=jdbc:mysql://localhost:3306/myee2302_db_2
#用户名
username=root
#密码
password=123456
#连接池一旦创建,初始化5个连接数量
initialSize=5
#最大连接数量值:默认值8个,自己设定值,和maxIdel:最大空闲数量相等
maxActive=10
#最大等待时间:为毫秒值,一旦连接池中创建连接对象超过了最大连接数量,等待3秒中,如果还连接不上,连接池会产生错误日志,提示"连接超时"
maxWait=1000

数据库连接池的好处：

节省资源:当前连接池(DataSource接口)创建之后,就会初始化一些连接对象,节省连接对象的创建;
重复利用:当某个线程持有的连接对象被使用完之后,归还连接池等待下次利用;
提高性能:连接池中:只需要配置好这些连接参数(最基本参数:数据库的相关信息)/连接池中的优化参数,DataSource里的实现类---DruidDataSource自己封装这里面的所有连接参数;(还可以不断监控连接池中连接数量的变化) 

Druid连接池的使用步骤：

    1)导包druid-1.1.10.jar /mysql的驱动jar包
    2)准备配置文件 
    url=jdbc:mysql://localhost:3306库名
    driverClassName=com.mysql.jdbc.Driver
    username=root
    password=123456
    
    initialSize=初始化连接数量
    maxActive=最大激活数量
    minIdel=最小空闲数量
    maxidel=最大空闲数量
    maxWait=最大等待时间毫秒值
    
    3)创建数据源javax.sql.DataSoure接口对象,
    //属性集合列表
    Properties prop = new Properties() ;
    //读取src下面的druid.propertites---->当前类的字节码文件对象获取类加载器--->资源文件的输入流对象
    prop.load(字节输入流) ;
    
    DataSource ds = DruidDataSourceFatory.createDataSource(prop) ;
    Connection conn = ds.getConnection() ;

加入连接池后自定义工具类的步骤：

class JdbcUtils{
    private static ThreadLocal<Connection> tl = new ThreadLocal<>() ;//每一个线程都有自己的连接对象
    private static DataSource ds ;
    private JdbcUtils(){}
    
    static{
     //1)读取德鲁伊的配置文件,让德鲁伊自己封装配置文件参数
            InputStream inputStream = DruidJdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            //创建属性集合列表
            Properties prop = new Properties() ;
            //加载属性集合列表
            prop.load(inputStream) ;
            //2)创建DruidDataSource(连接池)
            ds =  DruidDataSourceFactory.createDataSource(prop);
    }
    
    public static Connection getConnection(){
        Connection conn =  tl.get() ;
        if(conn==null){
       		  //从数据源中(连接池)获取连接对象
            conn = ds.getConnection() ;
            //将当前连接对象绑定到自己的线程上
            t1.set(conn) ;
        }
        return conn ;
    }
}

JDBA控制事务

jdbc控制事务： ---->javax.sql.Connection

public void setAutoCommit(boolean auto) ;参数为true,表示自动提交,如果是false,手动提交
  void rollback():事务回滚,撤销之前所有更改,必须释放连接对象,连接对象需要从线程中解绑
  void commit():提交事务,将更改数据永久保存,提交完毕,释放连接对象,需要从线程中解绑

工具类库:commons-dbutils

commons-dbutils的使用步骤:
    1)导包 commons-dbutils-1.6.jar
    2)创建执行器 QueryRunner---> 底层PreparedStatement
     public QueryRunner(DataSource ds) 参数就是数据源--->自定义工具获取到了数据源 (自动提交)
     public QueryRunner():创建执行器,手动提交

   3)准备好sql语句
        DML语句---添加/修改/删除
                  insert into
                  update
                  delete from...

        QueryRunner提供通用的更新操作:
        public int update(Connection conn, String sql, Object... params) throws SQLException  :手动提交
        public int update(String sql, Object... params):自动提交

        DQL语句--- 查询语句

        QueryRunner提供的通用查询操作
        public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException
        第一个参数:查询的sql语句
        第二个参数:ResultSetHandler结果集的处理
                    它的子实现类:BeanHandler---->将查询的某一条记录封装到实体了中(JavaBean:是具体类,属性私有,对外提供setXXX()/getXXX)
                    public BeanHandler(Class<T> type) --->  参数针对查询的记录--封装到类名.class中

                    子实现类:BeanListHandler<T>    ---将查询的多条记录封装到List集合中,List集合都是当前类对象

                     public BeanListHandler(Class<T> type)
                    子实现类:

                        ScalarHandler:通过聚合函数(count(列名称),其他max(xx),avg(xxx))查询单行单的列的数据
                        的结果封装到Object类中
        第三个参数:就是赋值的实际参数params,没有参数可以不写