MySQL 基础笔记 & JDBC

MySql

数据库优点：1.持久化存储数据。 2.方便管理数据
连接本机数据库，可以简写：jdbc:mysql:///test
备份数据库：mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
数据库类型:
int:整数类型
double:小数类型
date:日期，只包含年月日，yyyy-MM-dd
datetime:日期，只包含年月日时分秒 yyyy-MM-dd HH:mm:ss
timestamp:时间戳类型，包含年月日时分秒 yyyy-MM-dd HH:mm:ss – 如果不给这个字段赋值或赋值null，那么会用当前系统时间，自动赋值

SQL分类：

1）DDL（Data Definition Laguage）数据定义语言
    用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

2）DML(Data Manipulation Language)数据操作语言
    用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

3）DQL(Data Query Language)数据查询语言
    用来查询数据库中表的记录(数据)。关键字：select, where 等

4）DCL(Data Control Language)数据控制语言(了解)
    用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

---

DDL：操作数据库、表

C（Create:创建
    1. 操作数据库 
     #  CREATE DATABASE IF NOT EXISTS 数据库名称 SET UTF8; -- 创建数据库，判断是否存在，并设    置字符集为UFT-8
    2. 操作表
     #  CREATE TABLE 表名 LIKE 被复制的表名;
R（Retrieve）：查询
    1. 操作数据库
        SHOW DATABASES; -- 查询所有数据库
     #  SHOW CREATE DATABASE 数据库名称; -- 查询某个数据库的字符集
    2. 操作表
        SHOW TABLES; -- 查询某个数据库中所有的表名称
     #  SHOW CREATE TABLE 表名;
     #  DESC 表名; -- 查询表结构
U（Update）：修改
    1. 操作数据库
        ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称; -- 修改数据库的字符集
    2. 操作表
        ALTER TABLE 表名 RENAME TO 新的表名; -- 修改表名
        ALTER TABLE 表名 CHARACTER SET UTF8; -- 修改表的字符集
        ALTER TABLE 表名 ADD 列名 数据类型; -- 添加一列
        ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型; -- 修改列名称，类型
        ALTER TABLE 表名 MODIFY 列名 新数据类型; -- 修改列类型
        ALTER TABLE 表名 DROP 列名; -- 删除列
D（Delete）：删除
    1. 操作数据库
        DROP DATABASE IF EXISTS 数据库名称; -- 判断数据库是否存在，若存在则删除
使用数据库：
  # SELECT DATABASE(); -- 查询当前正在使用的数据库名称
    USE 数据库名称; -- 使用数据库

DML：增删改表中数据

C:
    INSERT INTO 表名(列名1，列名2..) VALUES(值1,值2..) ,(值3,值4..),(值5,值6..)....
U:  
 #  UPDATE 表名 SET 列名1=值1，列名2=值2 WHERE 条件...
D:
    DELETE FROM 表名 WHERE 条件; -- 删除表，如果不加条件，则删除全表
 #  TRUNCATE TABLE 表名; -- 先删除表，然后再创建一张一样的表（推荐，效率高）

DQL：查询表中的记录

    SELECT              SELECT
       字段列表             sex,AVG(math),AVG(english)
    FROM                FROM
       表名列表             student
    WHERE               WHERE
       条件列表             math >= 60
    GROUP BY            GROUP BY
       分组字段             sex
    HAVING              HAVING
       分组之后的条件          AVG(math) >= 60
    ORDER BY            ORDER BY
       排序                   AVG(math) DESC,AVG(english) DESC
    LIMIT               LIMIT
       分页限定             (0,5)

1. 基础查询 
    SELECT DISTINCT * FROM 表名 WHERE 条件语句; -- 去重查询（distinct）
    SELECT IFNULL(score,0) FROM 表名; -- 对null值做处理
    SELECT * FROM 表名 WHERE score IS NULL; -- 查询Null值
    SELECT * FROM 表名 WHERE score BETWEEN 60 AND 100; -- 查询成绩在60-100之间
    模糊查询：
    _：单个任意字符
    %：多个任意字符

2. 排序查询（ORDER BY 排序字段1 排序方式1，排序字段2 排序方式2...）
    SELECT * FROM student ORDER BY math ASC , english ASC; -- 先按照数学成绩升序，若成绩相同，则按照英语成绩升序
3. 聚合函数（聚合函数的计算，会排除null值）将一列数据作为一个整体，进行纵向的计算。
    SELECT 聚合函数 FROM 表名;
    1. count：计算个数
        1）一般会选择非空的列：主键
        2）count(*)  只要这一行，有一列不为null，则计数
    2. max：计算最大值
    3. min：计算最小值
    4. sum：计算和
SELECT SUM(math) FROM student
    5. avg：计算平均值
4. 分组查询（GROUP BY 分组字段） 分组后再进行条件筛选 --> having关键字
    SELECT sex, AVG(math) FROM student GROUP BY sex; -- 查询男，女同学的数学平均分
    SELECT sex, AVG(math) FROM student WHERE math > 70 GROUP BY sex;
    SELECT sex, AVG(math),COUNT(id) FROM student GROUP BY sex HAVING COUNT(id) >2; -- 查询男女的平均分和人数，并且只需平均分人数大于2人的。

    -- 查询男女各自的数学平均分，低于60分的不参与统计，对平均分降序排列
    SELECT sex AS 性别,AVG(math) AS 平均分 FROM student WHERE math >= 60 GROUP BY sex ORDER BY 平均分 DESC;
5. 分页查询（LIMIT index,length）

DCL：管理用户（账户、密码、权限）

1. 管理用户
    1. 添加用户
        语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
    2. 删除用户
        语法：DROP USER '用户名'@'主机名';
    3. 修改用户密码
        语法：UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名'
             SET PASSWORD FOR '用户名'@'新密码' = PASSWORD('新密码')
    4. 查询用户
    5. 忘记root密码？
        1. net stop mysql
        2. 使用无验证方式启动mysql服务：mysqld --skip-grant-tables
        3. 新开一个cmd，输入mysql直接登陆，修改root密码
        4. 打开任务管理器，关闭mysql服务
        5. net start mysql

---

约束

1. 主键约束 PRIMARY KEY   非空且唯一
    添加主键自增：ALTER TABLE 表名 MODIFY 列名 类名 PRIMARY KEY AUTO_INCREMENT;
    删除主键约束：ALTER TABLE 表名 DROP PRIMARY KEY
    删除自动增长：ALTER TABLE 表名 MODIFY 主键列名 类型; -- 主键不会被删除
2. 非空约束 NOT NULL    删除非空约束：ALTER TABLE 表名 MODIFY 列名 类型;
3. 唯一约束 UNIQUE -- 空值只能有一个
    删除唯一约束：ALTER TABLE 表名 DROP INDEX 列名;
4. 外键约束
    1. 创建表时添加外键：
        CREATE TALBE 表名(
            ...
            外键字段
            CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 表名(主键)
        简化写法：FOREIGN KEY (外键列名称) REFERENCES 表名(主键)  -- 系统会自动分配一个唯一的外键名称
        );
    2. 删除外键
     #  ALTER TABLE 表名 DROP FOREIGN KEY 外键列
    3. 创建表后，添加外键    
     #  ALTER TABLE 表名 ADD CONSTRAINT 外键列 FORIEGN KEY (外键字段) REFERENCES 主表名(主   键)
    4. 级联操作
        1. 添加级联操作
            语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称
                    FOREIGN KEY(外键字段名) REFERENCES 主表名称 ON UPDATE CASCADE
                    ON DELETE CASCADE;
        2. 分类：
         #  1. 级联更新：ON UPDATE CASCADE
         #  2. 级联删除：ON DELETE CASCADE

---

范式

概括： 第一范式：每一列都是不可分割的原子数据项
       第二范式：去除冗余
       第三范式：解耦和

---

多表联查

1. 内连接： （查询左表和右表共有的）
   
   1. 隐式内连接：使用where条件消除无用数据
      SELECT t1.NAME,t1.gender,t2.NAME FROM emp t1,depa t2 WHERE t1.id = t2.id
   2. 现式内连接：（INNDER字段可以省略）
      SELECT 查询字段 FROM 表1 INNER JOIN 表2 ON 条件
2. 外连接（OUTER可以省略）
   
   1. 左外连接：（查询左表全部 和 右表共有的）
      SELECT 查询字段 FROM 表1 LEFT OUTER JOIN 表2 ON 条件
   2. 右外连接：（查询右表全部 和 左表共有的）
      SELECT 查询字段 FROM 表2 RIGHT OUTER JOIN 表2 ON 条件
3. 子查询
   
   1. 单行单列：SELECT * FROM emp WHERE salary = (SELECT MIN(salary) FROM emp);
   2. 单行多列：SELECT * FROM emp WHERE id IN (SELECT id FROM emp WHERE age >= 20);
   3. 多行多列：SELECT * FROM emp,(SELECT * FROM dept)

---

事务

START TRANSACTION;开启事务
ROLLBACK;事务回滚
COMMIT;事务提交

事务的四大特性：
1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据
3. 隔离性：多个事务之间，相互独立
4. 一致性：事务操作器前后，数据总量不变

事务的隔离级别
* 存在问题：
1. 脏读：事务A 读取到了 事务B 未提交的数据
2. 不可重复度：事务A 读取到了 事务B 已经提交的修改（update）后的数据
3. 幻读(虚读)：事务A 读取到了 事务B 已经提交的插入（insert）后的数据
* 隔离级别：（安全性越高，性能越低）
1. read uncommitted：读未提交
* 产生的问题：脏读、不可重复读、幻读
2. read committed：读已提交（Oracle默认隔离级别）
* 产生的问题：不可重复读、幻读
3. repeatable read：可重复度 （MySQL默认隔离级别）
* 产生的问题：幻读
* 在当前事务中，是读取不到数据的改变的，提交事务之后，再查询，才能看到数据变化
4. serializable：串行化
* 可以解决所有的问题

    * 数据库查询隔离级别：
     #  * select @@tx_isolation;
    * 数据库设置隔离级别：
     #  * set global transaction isolation level  级别字符串;
     #  * set global transaction isolation level read uncommitted;

---

JDBC连接数据库步骤 （需要导入mysql-connector-java）

概念：Java DataBase Connectivity Java 数据库连接， Java语言操作数据库
* JDBC本质：其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。

1. 注册驱动
    Class.forName("com.mysql.jdbc.Driver");
2. 通过DriverManager获取数据库连接对象
    Connection conn = DriverManager.getConnection(url,username,password)
3. 获取语句执行平台对象 Statment：声明
    Statement stat = conn.createStatement();
    // PreparedStatement ps = conn.prepareStatement(String sql);
4. 执行sql语句，获取受影响的行数
    int row = stat.executeUpdate(String sql);
5. 执行sql语句，获取结果集（类似游标，通过next()将游标向下移动一行）
    ResultSet resultSet = stat.executeQuery(sql);
6. 打印结果集
    while(resultSet.next()){
        System.out.println(resultSet.getInt("id")+resultSet.getString("name"));
    }
7. 释放资源
    stat.close();
    conn.close();

8. 注册驱动的第二种方式:DriverManager.deregisterDriver(new Driver)
    由于Driver类里面有一个静态代码块
    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }

所以第二种注册驱动的方式，会注册两次，不推荐使用

---

JDBC事务

try{
    conn.setAutoCommit(false); // 关闭自动事务提交
    ...
    conn.commit(); // 事务提交
} catch{
    conn.rollback(); // 事务回滚
}

---

JDBC连接池（c3p0、Druid）

1. c3p0数据库连接池
   
   • 步骤
     
     1. 导入jar包（三个）一个数据库驱动包（mysql-connector-java）
        c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar
     2. 定义配置文件：（名称固定，c3p0会自动去扫描该名称的文件）
        名称：c3p0.properties 或者 c3p0-config.xml
        路径：直接将文件放在src目录下即可
     3. 创建核心对象，数据库连接池对象 ComboPooledDataSource
     4. 获取连接：getConnection

        // 1.通过ComboPooledDataSource获取【默认】数据库连接池
        DataSource ds = new ComboPooledDataSource();
        // 1.1可以获取在c3p0-config.xml中获取【指定名称】数据库连接池
        DataSource ds = new ComboPooledDataSource("otherc3p0");

        // 2.从连接池中获取连接
        Connection conn = ds.getConnection();

        // 3.归还连接
        conn.close();

1. Druid：数据库连接池
   
   • 步骤
     
     1. 导入jar包 druid-1.0.9.jar
     2. 定义配置文件
        
        • 是properties形式的
        • 可以叫任意名称，可以放在任意目录下
     3. 加载配置文件
     4. 获取数据库连接池对象：通过工厂类来静态方法获取 DruidDataSourceFactory.createDataSource()
     5. 获取连接：getConnection

        public static void main(String[] args) throws Exception {
        // 1.定义配置文件
        Properties pro = new Properties();

        // 2.加载配置文件
        pro.load(DruidDemo01.class.getClassLoader().getResourceAsStream("druid.properties"));

        // 3.通过DruidDataSourceFactory工厂类获取数据库连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);

        // 4.通过数据库连接池获取连接对象
        Connection connection = dataSource.getConnection();

        // 5.归还连接
        connection.close();

* 定义工具类
    1. 定义一个类 JDBCUtils
    2. 提供静态代码块，加载配置文件，初始化连接对象
    2. 提供方法
        1. 获取连接方法：通过数据库连接池获取连接
        2. 释放资源
        3. 获取连接池方法

Spring JDBCTemplate

        Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发
    #   执行完成后，该框架会自动将资源关闭，将连接对象返回连接池
1. 导入jar包
2. 创建JdbcTemplate对象。依赖于数据库连接池DataSource
    * JdbcTemplate template = new JdbcTemplate(DataSource dataSource)
3. 调用JdbcTemplate的方法来完成CURD的操作

    1. 执行DML语句。增、删、改
        update(String sql, Object...params)  

    2. 查询结果，将结果集封装为【Map】集合
        queryForMap(String sql,Object...params) 

    3. 查询结果，将结果集封装为【List】集合
        queryForList(String sql,Object...params)

    4. 查询结果，将结果【封装】为JavaBean对象
        query(String sql,new RowMapper<T>()) // 重写方法，封装对象
        query(String sql,new BeanPropertyRowMapper<T>(Class cls)) // 自动封装对象

    5. 查询结果，将结果封装为对象，【一般用于聚合函数】
        queryForObject(String sql,Class cls)：

JDBC工具类

    public class JDBCUtils{
        // 定义成员变量
        private Connection conn = null;

        /*
         * 定义静态代码块，类加载时执行
         */
        static{
            // 1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 2.通过DriverManager获取连接对象
            conn = DriverManager.getConnection();
        }

        /*
         * 获取连接对象
         */
        public static Connection getConnection(){
            return conn;
        }

        /*
         * 关闭资源 ResultSet、Statement、Connection
         */
        public static void close(ResultSet rs, Statement stat, Connection conn){
            if(rs != null){
                rs.close();
            }
            if(stat != null){
                stat.close();
            }
            if(conn != null){
                conn.close();
            }
        }
        /*
         * 关闭资源 Statement、Connection
         */
        public static void close(Statement stat, Connection conn){
            close(null,stat,conn);
        }
    }