MySQL 初学笔记

柔光的暖阳◎ 2023-10-03 15:36 36阅读 0赞

### MD5加密 ###

update test set passward=MD5(passward);
    insert into test values('4','name',MD5('passward'));

检验：将用户传入的密码也进行MD5加密，再进行对比即可检验

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5rGk5rGk5rGk5rGk_size_20_color_FFFFFF_t_70_g_se_x_16]

create database if not exists `school`;
    -- 创建一个school数据库
    use `school`;-- 创建学生表
    drop table if exists `student`;
    create table `student`(
    	`studentno` int(4) not null comment 学号,
        `loginpwd` varchar(20) default null,
        `studentname` varchar(20) default null comment 学生姓名,
        `sex` tinyint(1) default null comment 性别，0或1,
        `gradeid` int(11) default null comment 年级编号,
        `phone` varchar(50) not null comment 联系电话，允许为空,
        `address` varchar(255) not null comment 地址，允许为空,
        `borndate` datetime default null comment 出生时间,
        `email` varchar (50) not null comment 邮箱账号允许为空,
        `identitycard` varchar(18) default null comment 身份证号,
        primary key (`studentno`),
        unique key `identitycard`(`identitycard`),
        key `email` (`email`)
    )engine=myisam default charset=utf8;
    
    -- 创建年级表
    drop table if exists `grade`;
    create table `grade`(
    	`gradeid` int(11) not null auto_increment comment 年级编号,
      `gradename` varchar(50) not null comment 年级名称,
        primary key (`gradeid`)
    ) engine=innodb auto_increment = 6 default charset = utf8;
    
    -- 创建科目表
    drop table if exists `subject`;
    create table `subject`(
    	`subjectno`int(11) not null auto_increment comment 课程编号,
        `subjectname` varchar(50) default null comment 课程名称,
        `classhour` int(4) default null comment 学时,
        `gradeid` int(4) default null comment 年级编号,
        primary key (`subjectno`)
    )engine = innodb auto_increment = 19 default charset = utf8;
    
    -- 创建成绩表
    drop table if exists `result`;
    create table `result`(
    	`studentno` int(4) not null comment 学号,
        `subjectno` int(4) not null comment 课程编号,
        `examdate` datetime not null comment 考试日期,
        `studentresult` int (4) not null comment 考试成绩,
        key `subjectno` (`subjectno`)
    )engine = innodb default charset = utf8;

### 事务管理（ACID） ###

原子性（Atomicity）  
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。  
一致性（Consistency）  
事务前后数据的完整性必须保持一致。  
隔离性（Isolation）  
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。  
持久性（Durability）  
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

### 事务手动开启与关闭 ###

**MySQL是默认开启事务自动提交的**

set autocommit = 0; /*事务关闭*/
    set autocommit = 1; /*事务关闭*/

**手动开启事务**  
事务开启

start transaction --标记一个事务的开始，从这之后的事务都在同一个事务内
    insert xx
    insert xx

commit --提交：持久化（成功）

rollback --回滚：回到原来的样子（失败）

savepoint name --设置一个事务的保存点
    rollback to savepoint name --回滚到保存点
    release savepoint name --撤销保存点

例子：

/*
    课堂测试题目
    
    A在线买一款价格为500元商品,网上银行转账.
    A的银行卡余额为2000,然后给商家B支付500.
    商家B一开始的银行卡余额为10000
    
    创建数据库shop和创建表account并插入2条数据
    */
    
    CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
    USE `shop`;
    
    CREATE TABLE `account` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL,
    `cash` DECIMAL(9,2) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8
    
    INSERT INTO account (`name`,`cash`)
    VALUES('A',2000.00),('B',10000.00)
    
    -- 转账实现
    SET autocommit = 0; -- 关闭自动提交
    START TRANSACTION;  -- 开始一个事务,标记事务的起始点
    UPDATE account SET cash=cash-500 WHERE `name`='A';
    UPDATE account SET cash=cash+500 WHERE `name`='B';
    COMMIT; -- 提交事务
    # rollback;
    SET autocommit = 1; -- 恢复自动提交

### 索引 ###

**分类**  
主键索引 (Primary Key)  
唯一索引 (Unique)  
常规索引 (Index)  
全文索引 (FullText)

/*
    #方法一：创建表时
      　　CREATE TABLE 表名 (
                   字段名1 数据类型 [完整性约束条件…],
                   字段名2 数据类型 [完整性约束条件…],
                   [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                   [索引名] (字段名[(长度)] [ASC |DESC])
                   );
    
    
    #方法二：CREATE在已存在的表上创建索引
           CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                        ON 表名 (字段名[(长度)] [ASC |DESC]) ;
    
    
    #方法三：ALTER TABLE在已存在的表上创建索引
           ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                                索引名 (字段名[(长度)] [ASC |DESC]) ;
                               
                               
    #删除索引：DROP INDEX 索引名 ON 表名字;
    #删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
    
    
    #显示索引信息: SHOW INDEX FROM student;
    */
    
    /*增加全文索引*/
    ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
    
    /*EXPLAIN : 分析SQL语句执行性能*/
    EXPLAIN SELECT * FROM student WHERE studentno='1000';
    
    /*使用全文索引*/
    -- 全文搜索通过 MATCH() 函数完成。
    -- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
    EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
    
    /*
    开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况
    
    MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
    MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
    只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
    测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
    */

**索引准则**  
索引不是越多越好  
不要对经常变动的数据加索引  
小数据量的表建议不要加索引  
索引一般应加在查找条件的字段

### 用户权限 ###

/* 用户和权限管理 */ ------------------
    用户信息表：mysql.user
    
    -- 刷新权限
    FLUSH PRIVILEGES
    
    -- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
    CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
      -- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
      -- 只能创建用户，不能赋予权限。
      -- 用户名，注意引号：如 'user_name'@'192.168.1.1'
      -- 密码也需引号，纯数字密码也要加引号
      -- 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
    
    -- 重命名用户 RENAME USER kuangshen TO kuangshen2
    RENAME USER old_user TO new_user
    
    -- 设置密码
    SET PASSWORD = PASSWORD('密码')    -- 为当前用户设置密码
    SET PASSWORD FOR 用户名 = PASSWORD('密码')    -- 为指定用户设置密码
    
    -- 删除用户 DROP USER kuangshen2
    DROP USER 用户名
    
    -- 分配权限/添加用户
    GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
      - all privileges 表示所有权限
      - *.* 表示所有库的所有表
      - 库名.表名 表示某库下面的某表
    
    -- 查看权限   SHOW GRANTS FOR root@localhost;
    SHOW GRANTS FOR 用户名
       -- 查看当前用户权限
      SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
    
    -- 撤消权限
    REVOKE 权限列表 ON 表名 FROM 用户名
    REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    -- 撤销所有权限

### MySQL备份 ###

导出

-- 导出
    1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
    　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
    2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
    　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
    3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
    　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
    4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
    　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
    
    可以-w携带备份条件
    
    -- 导入
    1. 在登录mysql的情况下：-- source D:/a.sql
    　　source 备份文件
    2. 在不登录的情况下
    　　mysql -u用户名 -p密码 库名 < 备份文件

### 规范化数据库设计 ###

**糟糕的数据库设计 :**  
数据冗余,存储空间浪费  
数据更新和插入的异常  
程序性能差

**良好的数据库设计 :**  
节省数据的存储空间  
能够保证数据的完整性  
方便进行数据库应用系统的开发

**软件项目开发周期中数据库设计 :**  
需求分析阶段: 分析客户的业务和数据处理需求  
概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

### JDBC ###

记得要jdbc添加到lib

package com.company;
    
    import java.sql.*;
    
    public class Main {
        
    
        public static void main(String[] args) throws ClassNotFoundException, SQLException {
        
            //1.加载驱动
    	    Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法，加载驱动
    
            //2.用户信息和url
            //useUnicode=true&characterEncoding=utf8&useSSL=true
            String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
            String username = "root";
            String password = "123456";
    
            //3.连接成功，数据库对象 connection代表数据库
            Connection connection = DriverManager.getConnection(url,username,password);
    
            //4.执行数据库对象 statement执行sql的对象
            Statement statement = connection.createStatement();
    
            //5.去执行SQl
            String sql = "SELECT * FROM student";
    
            ResultSet resultSet = statement.executeQuery(sql);//返回相应的结果集
    
            while (resultSet.next()){
        
                System.out.println("id:" + resultSet.getObject("id"));
                System.out.println("name:" + resultSet.getObject("name"));
                System.out.println("age:" + resultSet.getObject("age"));
                System.out.println("-----------------");
            }
    
            //6.释放连接
            resultSet.close();
            statement.close();
            connection.close();
        }
    }

### SQL注入 ###

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

### PreparedSatement ###

**PreparedSatement 的好处**

1.  prepareStatement()会先将 SQL 语句发送给数据库预编译。PreparedStatement 会引用着预编译后的结果。  
    可以多次传入不同的参数给 PreparedStatement 对象并执行。减少 SQL 编译次数，提高效率。
2.  安全性更高，没有 SQL 注入的隐患。
3.  提高了程序的可读性

**使用 PreparedStatement 的步骤：**

1.编写 SQL 语句，未知内容使用?占位：“SELECT \* FROM user WHERE name=? AND password=?”;  
2. 获得 PreparedStatement 对象  
3.设置实际参数：setXxx(占位符的位置, 真实的值)  
4.执行参数化 SQL 语句  
5.关闭资源

package com.lqg;
    import com.itheima.utils.JdbcUtils;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.Scanner;
    /**
    * 使用 PreparedStatement
    */
    public class Demo8Login {
        
        //从控制台上输入的用户名和密码
        public static void main(String[] args) throws SQLException {
        
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String name = sc.nextLine();
        System.out.println("请输入密码：");
        String password = sc.nextLine();
        login(name, password);
    }
    /**
    * 登录的方法
    * @param name
    16 / 21
    * @param password
    */
    private static void login(String name, String password) throws SQLException {
        
        Connection connection = JdbcUtils.getConnection();
        //写成登录 SQL 语句，没有单引号 ‘?’是占位符，代替参数
        String sql = "select * from user where name=? and password=?";
        //得到语句对象
        PreparedStatement ps = connection.prepareStatement(sql);
        //设置参数
        ps.setString(1, name);
        ps.setString(2,password);
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next()) {
        
        System.out.println("登录成功：" + name);
        }
        else {
        
        System.out.println("登录失败");
        }
        //释放资源,子接口直接给父接口
        JdbcUtils.close(connection,ps,resultSet);
        } 
    }

### JDBC操作事务 ###

package le4;
    
    import le2.utils.JdbcUtils;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class TestTransaction {
        
        public static void main(String[] args) {
        
            Connection conn = null;
            PreparedStatement st = null;
            ResultSet rs = null;
            try {
        
                conn = JdbcUtils.getConnection();
                //关闭自动提交功能，开始事务
                conn.setAutoCommit(false);
                String sql1 = "UPDATE account set money=money-100 WHERE name='A'";
                st = conn.prepareStatement(sql1);
                st.executeUpdate();
    
                //会导致操作失败，事务回滚。
                int x=1/0;
    
                String sql2 = "UPDATE account set money=money+100 WHERE name='B'";
                st = conn.prepareStatement(sql2);
                st.executeUpdate();
    
                //业务完毕提交事务
                conn.commit();
                System.out.println("Success");
    
            } catch (SQLException throwables) {
        
                //如果失败则回滚
                try {
        
                    conn.rollback();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
                throwables.printStackTrace();
            } finally {
        
                JdbcUtils.release(conn,st,rs);
            }
        }
    }

### 数据库连接池-DBCP ###

connection= JdbcUtils_DBCP.getConnection();

package com.company;
    
    import org.apache.commons.dbcp.BasicDataSourceFactory;
    
    import javax.sql.DataSource;
    import java.io.InputStream;
    import java.sql.*;
    import java.util.Properties;
    
    public class JdbcUtils_DBCP {
        
        private static DataSource dataSource=null;
    
        static {
        
            try {
        
                InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
                Properties properties=new Properties();
                assert in != null;
                properties.load(in);
    
                //创建数据源 工厂模式→创建
                dataSource=BasicDataSourceFactory.createDataSource(properties);
    
    
    
            }catch (Exception e){
        
                e.printStackTrace();
            }
        }
    
        //获取连接
        public static Connection getConnection() throws Exception {
        
            return dataSource.getConnection();
        }
    
        //施放资源
        public  static void release(Connection connection, Statement statement, ResultSet resultSet) throws Exception {
        
            if (resultSet != null) {
        
                resultSet.close();
            }
            if (statement != null) {
        
                statement.close();
            }
            if (connection != null) {
        
                connection.close();
            }
    
        }
    
    }

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5rGk5rGk5rGk5rGk_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/28e4a23a57cd4548a1327a66b9443a49.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5bCP5rGk5rGk5rGk5rGk,size_20,color_FFFFFF,t_70,g_se,x_16