MySQL 初学笔记

MD5加密

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

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

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();
        }

    }

}