mysql学习笔记

本文主要是介绍mysql学习笔记，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1、创建数据表

CREATE TABLE IF NOT EXISTS `student2`(
   `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(10) NOT NULL DEFAULT '男' COMMENT '性别',
   PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

• 注意点 ，使用英文（），表的名称和字段要用 `` 括起来

• AUTO_INCREMENT 表示自增

• 字符串使用 单括号 括起来

• 所有的语句后面加 , 最后一个语句不用加

• PRIMARY KEY 主键，一般一个表只有一个唯一的主键

2、insert语句详解

-- 一般写插入语句，我们一定要数字和字段一一对应
-- 插入单个字段
INSERT INTO `student2`(`name`,`pwd`,`sex`) VALUES ('1','233','女')

-- 插入多个字段
INSERT INTO `student2`(`name`,`pwd`,`sex`) VALUES ('张三','3667','男'),('李四','999','男'),('王五','0088','女')

语法 : insert into 表名（[][][字段一，字段二，字段三]）values('值1')，('值2')，('值3')

注意事项：

1. 字段和字段之间使用英文逗号隔开
2. 字段可以省略，但后面的值必须一一对应，不能少
3. 可以同时插入多条数据，但需要用逗号隔开

3、Updata语句详解

-- 修改学生的名字
UPDATE `student2` SET `name`='旺仔' WHERE id=1

-- 不指定条件的情况下，会改变所有的表!
UPDATE `student2` SET `name`='旺仔' 

-- 修改多个属性
UPDATE `student2` SET `name`='旺仔',`sex`='男' WHERE id=1

-- 语法：
-- UPDATE 表名 set colnum_name=value,[colnum_name=value] where 条件

4、Delete和Truncate详解

delete命令

语法：delete from 表名 [where 条件]

-- 删除数据（尽量避免这种写法,会删除所有的数据）
DELETE FROM `student2`

-- 删除数据
DELETE FROM `student2` WHERE id=1

Truncate命令

作用：完全清空一个数据库表，表的结构和索引约束不会变

-- 清空所有的数据
TRUNCATE `student2`

delete与truncate 的区别

• 相同点：都能删除数据，都不会删除表的结构
• 不同点：
  • truncate 重新设置 自增列，计数器会归零
    • truncate 不会影响事务

-- 测试delete与truncate 的区别

CREATE TABLE IF NOT EXISTS `test`(
   `id` INT(4) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(10) NOT NULL,
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`name`) VALUES ('张三'),('李四'),('王五')

DELETE FROM `test` -- 不会影响自增

TRUNCATE FROM `test` -- 自增归零

5、DQL查询数据（最重点）

5.1、DQL

​ (Data Query LANGUAGE:数据查询语言)

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询它都能用
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

-- 查询全部的学生   SELECT 字段 FROM 表
SELECT * FROM `student2`

-- 查询指定字段
SELECT `name`,`sex` FROM `student2`

-- 别名  给结果起一个名字  AS 可以给字段起别名，也可以给表起别名
SELECT `name` AS 姓名,`sex` AS 性别 FROM `student2`

-- 函数  Concat（a,b）
SELECT CONCAT('姓名：',`name`) AS 新名字 FROM `student2`

语法：SELECT 字段........ FROM 表

5.2、去重 distinct

作用：去除select查询出来的结果中重复的数据，重复数字只显示一条

-- 发现重复数据，去重
SELECT DISTINCT `name` FROM `student2`

5.3、where条件子句

作用：检索数据中 符合条件 的值

搜索条件由一个或多个条件组成，结果为布尔值

逻辑运算符

-- 查询成绩在区间80到100之间
SELECT `name`,`score` FROM `student3`
WHERE `score`>=80 AND `score`<=100

-- 模糊查询
SELECT `name`,`score` FROM `student3`
WHERE `score` BETWEEN 70 AND 100

-- not
SELECT `name`,`score` FROM `student3`
WHERE  NOT `score`=100

5.4、分页和排序

排序 ORDER

-- 排序  ：升序ASC ，降序 DESC
-- ORDER BY 通过那个字段排序
SELECT `name`,`score` FROM `student3`
WHERE `score` BETWEEN 70 AND 100
ORDER BY `score` ASC

分页 LIMIT

-- 分页  每页只显示五条数据
-- 语法  LIMIT 当前页，页面大小
-- LIMIT 0，5
SELECT `name`,`score` FROM `student3`
WHERE `score` BETWEEN 70 AND 100
ORDER BY `score` ASC
LIMIT 0,5

6、Mysql函数

官网：https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

6.1、常用函数

-- 数学运算
SELECT ABS(-9)  -- 绝对值
SELECT CEILING(7.4)
SELECT CEIL(7.3) -- 返回不低于参数的最小整数值
SELECT FLOOR(6.4)-- 返回不超过参数的最大整数值

-- 字符串函数
SELECT CHAR_LENGTH('孤帆远影碧空尽') -- 参数中的字符返回数
......
......

-- 时间和日期（记住）

SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE()      -- 获取当前日期
SELECT NOW()          -- 获取当前时间
SELECT LOCALTIME()    -- 本地时间
SELECT SYSDATE()      -- 系统时间

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

6.2、聚合函数（重要）

6.3、数据库级别的MD5加密（扩展）

什么是MD5

主要增强算法复杂度和不可逆性。

MD5不可逆，具体的值的MD5是一样的。

-- =================================测试MD5加密原理=============================

CREATE TABLE IF NOT EXISTS `test1`(
    `id` INT(4) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    `pwd` VARCHAR(50) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO `test1` VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')

-- 加密
UPDATE `test1` SET pwd=MD5(pwd) WHERE id=1   -- 加密后：e10adc3949ba59abbe56e057f20f883e

UPDATE `test1` SET pwd=MD5(pwd)  -- 加密全部的密码

-- 插入的时候加密
INSERT INTO `test1` VALUES(4,'小明',MD5('123456'))

7、事务

7.1、什么是事务

要么都成功，要么都失败

1、SQL 执行 A给B 转账 A 1000 ---->200 B 200

2、SQL执行 B收到A的钱 A 800 -----> B 400

将一组SQL放在同一批次去执行

事务原则：ACID原则 原子性（Atomicity），一致性（Consistency），隔离性（Isolation），持久性（Durability）

原子性（Atomicity）

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

隔离导致的一些问题

脏读

指一个事务读取了另外一个事务未提交的数据。

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

页面统计查询值）

虚读(幻读)

是指在一个事 务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

7.2、测试事务：转账实例

执行事务 ：转账

-- 转账
CREATE TABLE `account`(
   `id` INT(4) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(10) NOT NULL,
   `money` VARCHAR(10) NOT NULL,
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `account`(`name`,`money`) VALUES('A','2000.0'),('B','1000.0')

-- 模拟转账：事务
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;  -- 恢复默认值

8、JDBC

8.1、第一个JDBC程序

sun公司为了简化开发人员的（对数据库的同一）操作，提供了一个（java操作数据库）规范，俗称JDBC，这些规范的实现具体由具体的厂商去做

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `jdbcstudy`;

CREATE TABLE users(
  `id` INT PRIMARY KEY,
  `name` VARCHAR(10),
  `password` VARCHAR(50),
  `email` VARCHAR(50),
  `birthday` DATE
)

INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)
VALUES(1,'张三','123456','kih@167367.com','2000-12-03'),(2,'李四','123456','nih@167337.com','1999-12-04'),(3,'王五','123456','lin@167367.com','2000-12-03')

编写测试代码

package com.hygv.lessom01;

import java.sql.*;

//我的第一个JDBC程序
public class jdbcFirst {
    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 ur1 = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";

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

        //4、执行sql对象    Statement 执行的对象
        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("name="+resultSet.getObject("name"));
            System.out.println("password="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
            System.out.println("====================================================");
        }
        //6、释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

1. 加载驱动
2. 连接数据库 DriverManager
3. 获得执行sql的对象 Statement
4. 获得返回的结果集
5. 释放连接

8.2、statement对象（重要）

数据库配置文件

driver=com.mysql.cj.jdbc.Driver
ur1=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

提取工具类

public class jdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
     try {
         InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properities");
         Properties properties = new Properties();
         properties.load(in);


         driver = properties.getProperty("driver");
         url = properties.getProperty("ur1");
         username = properties.getProperty("username");
         password = properties.getProperty("password");

         //驱动值加载一次
         Class.forName(driver);
     }catch (Exception e){
         e.printStackTrace();
     }
    }

    //获取连接
    public static Connection  getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet res){
        if (res!=null){
            try {
                res.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (st!=null){
            try {
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}

测试增加数据

public class testInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement sta = null;
        ResultSet res = null;
        try {
            conn = jdbcUtils.getConnection();
            sta = conn.createStatement();
            String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)\n" +
                    "VALUES(6,'wangzai2','2636245744','hug@7654272.com','2010-05-23')";
            int i = sta.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(conn,sta,res);
        }
    }
}

测试删除数据

public class testDelete {
    public static void main(String[] args) {
        Connection conn = null;
        Statement sta = null;
        ResultSet res = null;
        try {
            conn = jdbcUtils.getConnection();
            sta = conn.createStatement();
            String sql = "DELETE FROM `users` WHERE id = 4";
            int i = sta.executeUpdate(sql);
            if (i>0){
                System.out.println("删除成功！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(conn,sta,res);
        }
    }
}

测试更新数据

public class testUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        Statement sta = null;
        ResultSet res = null;
        try {
            conn = jdbcUtils.getConnection();
            sta = conn.createStatement();
            String sql = "UPDATE `users` SET `name`='wushang',`email`='786888' WHERE id=1";
            int i = sta.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(conn,sta,res);
        }
    }
}

8.3、SQL注入问题

• SQL注入
  根据我们提交的用户名和密码被合成到SQL查询语句当中之后是这样的：
  select * from app01_test where name='用户名' and pwd='密码';
  很明显，用户名和密码都和我们之前给出的一样，肯定能够成功登陆。但是，如果我们输入一个错误的用户名或密码呢?很明显，肯定登入不了吧。恩，正常情况下是如此，但是对于有SQL注入漏洞的网站来说，只要构造个特殊的“字符串”，照样能够成功登录。
  比如：在用户名输入框中输入:’or 1=1#,密码随便输入，这时候的合成后的SQL查询语句为：
  select * from app01_test where name='' or 1=1#' and pwd='';
  语义分析：“#”在mysql中是注释符，这样井号后面的内容将被mysql视为注释内容，这样就不会去执行了，换句话说，以下的两句sql语句等价：
  select * from app01_test where name='' or 1=1#' and pwd='';
  等价于
  select * from app01_test where name='' or 1=1
  因为1=1永远都是成立的，即where子句总是为真，将该sql进一步简化之后，等价如下select语句：
  select * from app01_test
  没错，该sql语句的作用是检索users表中的所有字段
  果不其然，我们利用万能语句(’or 1=1#)能够登录！看到了吧，一个经构造后的sql语句竟有如此可怕的破坏力，相信你看到这后，开始对sql注入有了一个理性的认识了吧~

• 三、攻击原理

  正常登陆name框中填写root，pwd为123 上面sql_sel = " select * from app01_test where name='{0}' and pwd='{1}' 就相当于select * from app01_test where name=root and pwd=123 进行查询

  但
  如果name框中填写root or 1=1 #，pwd 随便输入
  上面sql_sel = " select * from app01_test where name='{0}' and pwd='{1}'
  就相当于select * from app01_test where name=root or 1=1 # and pwd=123 进行查询
  这里的#相当于把后面的所有查询包括password查询给注释，并且 or 1 = 1的查询永远是正确的，所以sql攻击注入就完成了

  通过这样的方式就绕过了密码检查
  所以设计SQL语句时不建议这样设计，或者对特殊字符 #、or、and 等做特殊处理

8.4、PreparedStatement

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`)\n" +
                    "VALUES(?,?,?,?,?)";
            st = conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行

            //手动给参数配值
            st.setInt(1,7);
            st.setString(2,"五六七");
            st.setString(3,"2648245744");
            st.setString(4,"2648245744@qq.com");
            //注意点：sql.Date   数据库   java.sql.Date()
            //new Date().getTime() 获得时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,null);
        }
    }
}

这篇关于mysql学习笔记的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！