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')

注意事项：

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

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条件子句

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

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

逻辑运算符

运算符	语法	描述
and &&	a and b a && b	逻辑与
or \|\|	a or b a \|\| b	逻辑或
Not !	not a !a	逻辑非

-- 查询成绩在区间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、聚合函数（重要）

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
....	.....

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

步骤总结：

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

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学习笔记的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

MySql教程