【狂神说】MySQL笔记

最新推荐文章于 2023-12-08 23:04:22 发布

cencen666

最新推荐文章于 2023-12-08 23:04:22 发布

阅读量817

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.youkuaiyun.com/cencen666/article/details/122348560

版权

公众号笔记

一、初识MySQL

表名/字段等名称用 ` tab上边的键
内容用 ’元素‘

二、数据库操作

三、DML语言

外键：相当于引入一个约束（CONSTRAINT）

-- 创建外键的方式一 : 创建子表同时创建外键

-- 年级表 (id\年级名称)
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student` (
`id` INT(4) NOT NULL COMMENT '学号',
`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
`gradeid` INT(10) DEFAULT NULL COMMENT '年级',
`phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
`borndate` DATETIME DEFAULT NULL COMMENT '生日',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`id`), 

--设置约束名“FK_需要引用的名字”
KEY `FK_gradeid` (`gradeid`),

--给外键添加约束 
--外键   FOREIGN KEY (`gradeid`)
--外键引用 REFERENCES `grade` 
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

在这里插入图片描述

改：
在这里插入图片描述
删：

四、使用DQL查询数据

语句顺序从上到下不能换
在这里插入图片描述

concat函数
在这里插入图片描述
模糊查询

例题：

联表查询join

-- 因为student和result表里都有studentno，因此加入一个s和r代表是使用哪一个表里的
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

在这里插入图片描述

-- From a left join b -- 以a表为基准
-- From a right join b -- 以b表为基准

自连接
在这里插入图片描述
分页

五、MySQL函数

select小结
在这里插入图片描述

六、事务和索引

ACID理解
在这里插入图片描述
例子


-- 创建数据库
CREATE DATABASE bank CHARACTER SET utf8 COLLATE utf8_general_ci
USE bank

-- 创建用户表单
CREATE TABLE `account`(
	`id` INT(5) NOT NULL AUTO_INCREMENT ,
	`name` VARCHAR(50) NOT NULL,
	`money` DECIMAL(9,2),
	PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- 插入数据
INSERT INTO account(`name`,`money`)
VALUE ('A',10000),('B',8000),('C',30000)


-- 模拟转账：事务
SET autocommit = 0 -- 关闭自动提交（事务默认一开始为自动提交）
START TRANSACTION -- 开启一个事务（一组事务）

UPDATE account SET money = money - 500 WHERE `name` = 'A' -- A减500
UPDATE account SET money = money + 500 WHERE `name` = 'B' -- B加500

COMMIT; -- 提交事务。完成后就被持久化了！即使执行回滚也不会改变
ROLLBACK; -- 回滚。恢复到初始状态

SET autocommit = 1; -- 恢复默认值

索引
索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
必看文章讲索引

七、权限及如何设计数据库

八、JDBC(重点)

1.数据库驱动

在这里插入图片描述
程序通过数据库驱动和数据库打交道

2.jdbc

在这里插入图片描述
要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar

3.第一个jdbc程序

import java.sql.*;
/**
 * @Classname jdbctest1
 * @Description TODO
 * @Date 2022/1/4 12:07
 * @Created by cencen
 */
public class jdbctest1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载数据
        //DriverManager.registerDriver(new com.mysql.jdbc.Driver());//不建议使用，因为下一句执行的Driver的源码会执行该语句
        Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动

        //2.用户信息和url
        //useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?characterEncoding=UTF8&useSSL=true";
        String username = "root";
        String password = "20020809l";

        //3.连接成功，数据库对象 Connection代表数据库
        Connection connection = DriverManager.getConnection(url,username,password);

        //4.执行sql的对象 Satement 执行sql的对象
        Statement statement = connection.createStatement();

        //5.执行sql的对象去执行sql，可能存在结果，查看返回结果
        String sql = "SELECT * FROM users";

        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集，结果集中封装了我们全部的查询出来的结果

        while(resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("id=" + resultSet.getObject("name"));
            System.out.println("id=" + resultSet.getObject("password"));
            System.out.println("id=" + resultSet.getObject("email"));
            System.out.println("id=" + resultSet.getObject("birthday"));
            System.out.println("--------------------------------------------");
        }

        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤
在这里插入图片描述
各个对象

4.Statement对象

在这里插入图片描述

增

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JDBCutils.getConnection();//直接通过所写的工具类获取连接
            st = conn.createStatement();//获取SQL的执行对象
            String sql = "INSERT INTO users(id,`name`,`password`,`email`,`birthday`)"+
                    "VALUES(4,'ff','123456','123123@QQ.COM','1920-09-08')";

            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCutils.release(conn,st,rs);
        }
    }
}

删

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JDBCutils.getConnection();//直接通过所写的工具类获取连接
            st = conn.createStatement();//获取SQL的执行对象
            String sql = "DELETE FROM users WHERE id = 4";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCutils.release(conn,st,rs);
        }
    }
}

改

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JDBCutils.getConnection();//直接通过所写的工具类获取连接
            st = conn.createStatement();//获取SQL的执行对象
            String sql = "UPDATE users SET `name` = 'pp' WHERE `id` = 1";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("更新成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCutils.release(conn,st,rs);
        }
    }
}

查

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JDBCutils.getConnection();//直接通过所写的工具类获取连接
            st = conn.createStatement();//获取SQL的执行对象
            String sql = "SELECT * FROM users WHERE id = 1";
            while (rs.next()){
                System.out.println(rs.getString("name"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCutils.release(conn,st,rs);//全部释放
        }
    }
}

执行操作时
增删改使用

	//开头定义可以不定义，最后传参设为null即可
	//ResultSet rs = null;
	。。。。。。
	int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }

查使用：

//开头要定义，结果传参要传出rs
    ResultSet rs = null;
    。。。。。。
    while (rs.next()){
                System.out.println(rs.getString("name"));
            }

5.PreparedStatement

和Statement比：
可以防止SQL注入，且效率更高
PreparedStatement是继承了Statement类

区别
使用？占位符代替参数

增

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JDBCutils.getConnection();

            //区别
            //使用？占位符代替参数
            String sql = "insert into users(id,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";

            st = conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"kk");
            st.setString(3,"12312311");
            st.setString(4,"653535@qq.com");
            //注意点： sql.Data  是数据库的    java.sql.Date()
            //       util.Date 是java自带的  new Date().getTime() 获得时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行，返回更新影响的行数,就是你这个SQL查询更新了多少行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCutils.release(conn,st,null);
        }

    }

}

删

public class TestDelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JDBCutils.getConnection();

            //区别
            //使用？占位符代替参数
            String sql = "DELETE FROM users WHERE id = ?";

            st = conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,4);

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCutils.release(conn,st,null);
        }

    }
}

改

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JDBCutils.getConnection();

            //区别
            //使用？占位符代替参数
            String sql = "UPDATE users SET `name` =? WHERE id = ?";

            st = conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行

            //手动给参数赋值
            st.setString(1,"cencen");
            st.setInt(2,3);


            //执行,用于执行INSERT、UPDATE或DELETE语句以及SQLDDL
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("更新成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCutils.release(conn,st,null);
        }

    }
}

查

public class TestSelect {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {

            JDBCutils.getConnection();

            String sql = "select * from users where id = ?";

            conn.prepareStatement(sql);

            st.setInt(1,1);

            //执行，从数据库里读出的内容，返回一个结果集！
            rs = st.executeQuery();//执行

            if (rs.next()){
                System.out.println(rs.getString("name"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCutils.release(conn,st,rs);
        }
    }
}

6.SQL注入

sql存在漏洞，会被攻击导致数据泄露
sql被拼接
只要执行的结果是true，则会成功进入数据库

Statement的sql注入成功

public class sql {
    public static void main(String[] args) {
        //正常登录
//        login("pp","12344");
        //sql注入:只要满足结果为true
        login("'or'1=1","'or'1=1");
    }

    //登录业务
    public static void login(String username, String password) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JDBCutils.getConnection();
            st = conn.createStatement();

            //SELECT * FROM users WHERE `name` = 'pp' AND password` = '12344'
            String sql = "select * from users where `name` = '"+username+"'AND`password` = '"+password+"'";

            rs = st.executeQuery(sql);//查询完毕会返回一个结果集
            while(rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCutils.release(conn,st,rs);
        }
    }
}

PreparedStatement的SQL注入失败，该对象可防止SQL注入

public class sql {
    public static void main(String[] args) {
        //正常登录
//        login("pp","12344");
        //sql注入:只要满足结果为true
        login("'or'1=1","'or'1=1");
    }

    //登录业务
    public static void login(String username, String password) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JDBCutils.getConnection();

            //SELECT * FROM users WHERE `name` = 'pp' AND password` = '12344'
            String sql = "select * from users where `name` = ? and `password` = ?";

            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);

            rs = st.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("=====================");

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCutils.release(conn,st,rs);
        }

    }
}

7.事务

在这里插入图片描述

public class TestTransaction1 {
    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 cash = cash - 100 WHERE name = 'A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 = "UPDATE account SET cash = cash + 100 WHERE name = 'B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            //业务完毕，提交事务
            conn.commit();
            System.out.println("ok!");


        } catch (SQLException throwables) {
            //若失败会默认回滚
//            try {
//                conn.rollback();//如果失败回滚
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//            throwables.printStackTrace();
        }finally {
            JDBCutils.release(conn,st,rs);
        }

    }
}

8.数据库连接池

都是为了提升效率使用

在这里插入图片描述
编写连接池，实现一个接口 DataSource

在这里插入图片描述
（1）DBCP

创建一个propertices文件（格式固定）

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=20020809l

# 初始化连接
initialSize=10

#最大连接数量
maxActive=50

# 最大空闲连接
maxIdle=20

# 最小空闲连接
minIdle=5

# 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意：user 与 password 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

创建一个工具类，即在原来基础上可将手动连接数据库改为通过工厂模式创建

package 连接池;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @Classname JDBCutils
 * @Description TODO
 * @Date 2022/1/6 13:57
 * @Created by cencen
 */
public class Utils_DBCP {

    private static DataSource dataSource = null;

    static {
        try{
            InputStream in = utils.JDBCutils.class.getClassLoader().getResourceAsStream("连接池/dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //创建数据源 工厂模式 --->创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);

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

    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源中获取连接
    }

    //获取连接资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

操作增删改查，只需要在之前代码上把工具类引用改成第二步的工具类即可

(2)c3p0
创建一个.xml文件,要放在最外边根目录下（格式固定）

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    c3p0的缺省（默认）配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省（默认）-->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&amp;characterEncoding=utf8&amp;uesSSL=false&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">20020809l</property>

        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <!--
    c3p0的命名配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource(MySQL);这样写就表示使用的是mysql的缺省（默认）-->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&amp;characterEncoding=utf8&amp;uesSSL=false&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">20020809l</property>

        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config>

工具类

package 连接池;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @Classname Utils_c3p0
 * @Description TODO
 * @Date 2022/1/7 14:49
 * @Created by cencen
 */
public class Utils_c3p0 {
    private static ComboPooledDataSource dataSource = null;

    static {
        try{
          //代码配置版 不建议使用
//            dataSource = new ComboPooledDataSource();
//            dataSource.setDriverClass();
//            dataSource.setUser();
//            dataSource.getPassword();
//            dataSource.setJdbcUrl();
//
//            dataSource.setMinPoolSize();

            //创建数据源 工厂模式 --->创建
            dataSource = new ComboPooledDataSource("MySQL");//配置文件写法
            //""里不写内容就会默认使用无名参数配置

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

    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源中获取连接
    }

    //获取连接资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试

package 连接池;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @Classname TestC3p0
 * @Description TODO
 * @Date 2022/1/7 14:53
 * @Created by cencen
 */
public class TestC3p0 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = Utils_c3p0.getConnection();

            //区别
            //使用？占位符代替参数
            String sql = "DELETE FROM users WHERE id = ?";

            st = conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,2);

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Utils_c3p0.release(conn,st,null);
        }

    }
}