MySQL的学习笔记

数据库操作常用命令：

SHOW DATABASES; --查看所有数据库

USE 数据库名；--切换数据库

SHOW TABLES；--查看所有表

DESCRIBE 表名；--显示表的信息

CREATE DATABASE 名字； --创建数据库

SHOW CREATE 表名； --显示该表被创建时的查询语句

ALTER TABLE 表名 旧名 RENAME AS 新名，

                                 ADD 新字段 类型，

                                 MODIFY 字段 新类型，

                                 CHANGE 旧字段名 新字段名

                                 DROP 字段

建表eg：

CREATE TABLE `NewTable`(
`name` VARCHAR(10) NOT NULL COMMENT 'Student',                                                              `id` INT(10) DEFAULT 0 NOT NULL COMMENT 'ID',
`birthday` DATETIME NULL COMMENT 'Birthday',
`address` VARCHAR(100) NULL COMMENT 'Location',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4

---

数据操作：

FOREIGN KEY (`本表字段`) REFERENCES `外表`(`字段`) --表级约束，也是物理外键，由于强壮的级联性不建议使用，因为数据库体现为单纯的存储数据，应避免繁杂的其他程序功能；

插入：

INSERT INTO `表名`(要添加的字段 --可以为空，即为默认全部属性) 

VALUES (元组1), (元组2), ... --每个元组对象需一一对应上行的字段，自增字段可忽略；

更新：

UPDATE `表名` SET `字段1` = xxx, `字段2`= xxx,  `字段3` = xxx... 

WHERE `字段` =、<>、>、<、BETWEEN a AND b --闭区间；未指定则SET所有行

删除：

DELETE FROM `表名` WHERE 行级条件；-- 逐条删除各元组，表结构保存，自增保存

TRUNCATE `表名` -- 摧毁表结构然后重建，自增归零

---

数据查询：

单表查询：

SELECT [DISTINCT（去重）] 字段1，字段2，...  AS 别名 FROM 表名 

模糊查询：

WHERE 字段 LIKE ‘_%’、BETWEEN x AND y、IN （值1，值2，...）、IS NULL（= '' 应该也行）

联表查询：

一般仅为WHERE条件下的满足：SELECT 别名1.字段1， 别名2.字段2，...

                                                      FROM 表1 别名1， 表2 别名2，...

                                                      WHERE 连接谓词 AND 其他限定条件

若想以基本表所有元组作为查询结果：SELECT ...

                                                              FROM 主体表 LEFT OUTER JOIN 查询表 

                                                              ON 连接条件；

自连接：一般是几层递归查询采用：单标两别名后自身连接；

！多表查询多以双表查询开始逐一累加查询

分页和排序：  ORDER BY  字段 默认ASC（升序）/DESC（降序）

                        LIMIT 起始页（以0开始），每页尺寸（每页数据条数）

                        eg：LIMIT 0,5 --最开始的五条数据

子查询与嵌套查询：连接运算优化比子查询完善

相关子查询（父子查询间存在元组变量的传递）过程：按序生成元组变量由子查询WHERE条件确定子查询元组后执行子查询返回结果给父查询后生成当下元组变量的最终结果；

！别名也作为元组变量，即代表某一元组来匹配where条件；

！EXISTS子查询SELECT一般是*，而且多为相关子查询（子查询WHERE需存在与子表连接的条件，接受传递下来的元组变量），并且多数时候可以替换IN，比较运算以及ANY、ALL等子查询，且更加高效，由于不用返回结果表；

---

常用函数：SELECT CEILING();   SELECT CHAR_LENGTH();返回字符串长度

                  SELECT FLOOR();  SELECT CONCAT();拼接字符串

                  SELECT ABS(); SELECT LOWER();SELECT UPPER();

                  SELECT RAND();0~1随机数  SELECT REVERSE();反转

                  SELECT CURRENT_DATE();

聚集函数：COUNT(*); COUNT(1); 由于内部SQL优化两者差异不大；

                  AVG(); SUM(); MAX(); MIN();   ...

                  GROUP BY 字段; HAVING 条件（筛选GROUP BY后的符合条件元组）

！WHERE不能加聚集函数因为其先于GROUP BY，无法作用于整个基本表；

！MD5('密码');返回加密后的暗文，校验，传递皆使用此暗文；                

---

事务并发导致的问题：

脏读：某端读到了另一端回滚前的数据；

幻读：一个事务内读到了新插入的元组结果；

不可重复读：由于他端修改操作导致当前端多次读结果不一致；

事务执行：（InnoDB支持事务）

开始前要SET AUTOCOMMIT = OFF, 结尾再开启回来；

START TRANSACTION 

... ...

COMMIT; -- 手动提交

ROLLBAKE; -- 手动撤销

---

索引

分类：

B+树

散列索引

顺序文件索引

... ...

CREATE [UNIQUE] [CLUSTER] INDEX 索引名

ON 表名(列名) 次序;

-- UNIQUE  表明数据值唯一，不可重复

SHOW INDEX -- 显示索引

EXPLAIN 分析查询效率

---

用户管理 

      （实际就是在对存在于本地数据库的用户表CRUD）：

        创建用户：CREATE USER 用户名 IDENTIFIED BY 密码;

        修改密码：...

        赋予权限：GRANT ALL PRIVILEGES(权限名) ON 库.表 TO 用户名

        显示权限：SHOW GRANTS FOR 用户名        

        撤销权限：REVOKE (权限名) ON 库.表 FROM 用户名

---

数据库备份

命令行（mysql登录状态下）

导出： mysqldump -h 主机地址 -u 用户名 -p 密码 库名 表名 > D:/... ... -- 备份位置

导入：source 直接拖曳文件过来？/ 备份文件地址

---

数据库设计

注意事项：

1 - 一般的表项都有id这一字段，作为主键；

2 - 评论表可加入上级id产生自连接；

3 - 订阅数为中间表（关系一个用户和另一个用户的表）的统计；

4 - 标识实体关系图（ER图）

数据库CRUD异常：

插入异常：主码未标定无法插入；

删除异常：删个别主码字段导致整个元组数据的丢失；

修改复杂：修改一个需要接连修改从属字段；

规范化：逐步分离数据依赖中不合适的部分，即概念的逐步概括单一化；

2NF：一个表只能完全围绕主码（主码组），即只能完全形容一种实体；

3NF：在2NF基础上，不可以存在传递依赖，即非主码组不能被依赖，或称为不可再拆出一个主键表，ps：还是保证每张表只能完全形容一种主体；

与性能的关系：范式越高，表的分解度就越高，导致查询性能变差，在考虑用户性能体验时，结合商业化需求往往会存在反范式设计；

---

JDBC

First jdbc procedure

//note that there are several exception needed to be thrown;

//1. load the driver

Class.forName("com.mysql.jdbc.driver");//it's static code inside the driver class

//2. connection with relevant info

String url = "jdbc:mysql://localhost:3360/nameOfDatabase?useUnicode = true&characterEncoding=utf8&useSSL=true";

String username = "root";
String password = "666666";


Connection connection = DriverManager.getConnection(url,username,password);


//2.return statement object



Statement statement = connection.createStatement;


//3.CRUD using statement

ResultSet resultSet = statement.executeQuery("SELECT * FROM TABLE");
//this result set has all result from select statement and it's a form of linked list; 
//only for select statement, and it's a cursor actually(.next(); .previous();)...

int num = statement.executeUpdate("DELETE ID FROM TABLE WHERE AGE < 10");
//update statement will return a int which means the rows affected;



while(resultSet.next())
{
    resultSet.getObject(Column index: 1);
    resultSet.getObject(Column label: "id");
    ...

}

//4.shut down connection

resultSet.close();
statement.close();
connection.close();

.properties文件为一些相关变量和配置参数，目的为从程序中的变量改变独立出来；

两种配置文件读取方式，！注意Properties的建立接收应在static块里；

1.反射取得类加载器


Properties properties = new Properties();

// 使用ClassLoader加载properties配置文件生成对应的输入流
InputStream in = jdbc.class.getClassLoader().getResourceAsStream("config/config.properties"); //相对路径

// 使用properties对象加载输入流
properties.load(in);

//获取key对应的value值，properties变量格式为键值对
properties.getProperty(String key);



2.输入流

Properties properties = new Properties();

// 使用InPutStream流读取properties文件
BufferedReader bufferedReader = new BufferedReader(new FileReader("E:/config.properties"));
//绝对路径

properties.load(bufferedReader);

// 获取key对应的value值
properties.getProperty(String key);

工具类的概念：

相对于首个JDBC程序，一个jdbc工具类的建立可以简化连接建立释放等固定操作；

public class utils
{
    private static String driver;
    private static String username;
    private static String password;
    private static String url;


   static {
       //1. loading the properties file
       Properties config = new Properties();
       InputStream in = FirstJDBC.class.getClassLoader().getResourceAsStream("jdbc.properties");

       try {
           config.load(in);

           url = config.getProperty("url");
           username = config.getProperty("username");
           password = config.getProperty("password");
           driver = config.getProperty("driver");

           //1. load the Driver
           Class.forName(driver);



       } catch (Exception e) {
           e.printStackTrace();
       }
   }

   public static Connection connect() throws SQLException {
       //2. Connect the database

       return DriverManager.getConnection(url,username,password);
   }


   public static void close(Statement statement,ResultSet resultSet, Connection connection)

       //4. 释放连接
   {
       if(resultSet != null)
       {
           try {
               resultSet.close();
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           }
       }

       if(statement != null)
       {
           try {
               statement.close();
           } catch (Exception throwables) {
               throwables.printStackTrace();
           }
       }

       if(connection != null)
       {
           try {
               connection.close();
           } catch (Exception throwables) {
               throwables.printStackTrace();
           }
       }

   }
}

SQl注入问题

可通过字符串输入破坏SQL查询逻辑结构致使数据泄露，例如拼接or true条件；

PreparedStatement对象：对参数全部实行字符化处理并且忽略转义字符；

public static void login(String username,String password)//模拟登录注入
    {
        try {
            ResultSet result = null;
            Connection connect = utils.connect(); //常规调用

            PreparedStatement statement = connect.prepareStatement("select * from users where name = ? and password = ?");//变量设置占位符‘？’
    

            statement.setString(1,username); //对每个占位符按顺序设置字符或者其他类型
            statement.setString(2,password);

            if(!statement.executeQuery().next())
                System.out.println("Failed");
            else
            {
                result = statement.executeQuery();
                while(result.next())
                    System.out.println(result.getString(1));
            }
            
            utils.close(statement,result,connect);



        } catch (SQLException e) {
            e.printStackTrace();
        }
        

    }

---

数据库连接池：

DBCP(DataBase connection pool)数据库连接池是 apache 上的一个Java连接池项目，通过连接池预先同数据库建立一些连接放在内存中，应用程序需要建立数据库连接时直接到从接池中申请一个连接使用，用完后由连接池回收该连接，从而达到连接复用，减少资源消耗的目的。

//DBCP2数据源的工具类实现

public class DBCP_utils
{
       static BasicDataSource dataSource = null;


    static {
        //1. loading the properties file
        Properties config = new Properties();
//配置文档为DBCP专用的内置参数设置
        InputStream in = DBCP_utils.class.getClassLoader().getResourceAsStream("DBCP/DBCP_config.properties");

        try {
            
            //连接配置文件到properties对象
            config.load(in);

            //通过配置文件读取信息源初始参数并返回数据源对象；
            dataSource = BasicDataSourceFactory.createDataSource(config);

        } catch (Exception e) {
            e.printStackTrace();
        }
        
    }

    public static Connection connect() throws Exception {
        //2. Connect the database
        return dataSource.getConnection();
    }


    public static void close(PreparedStatement statement,Connection connection)
    {


        if(statement != null)
        {
            try {
                statement.close();
            } catch (Exception throwables) {
                throwables.printStackTrace();
            }
        }

        if(connection != null)
        {
            try {
                connection.close();
            } catch (Exception throwables) {
                throwables.printStackTrace();
            }
        }

    }
}

！DataSource为函数式接口唯一getConnection方法，其他所以上层数据源皆延展自此接口。