MySQL详解

MySQL

查询

Sql语句规定顺序

关键字顺序要求必须从上到下，位置不能乱

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查询的记录从哪条至哪条

查询拼接

CONCAT

SELECT CONCAT('名字:',`sname`) 名字
FROM student

会在查询出来的表中对sname字段的每一条数据加 名字：

去重查询

DISTINCT

SELECT DISTINCT `sgrade`,`sname`
FROM student

会去除这两个字段都相同的其他数据，只保留一组数据

模糊查询

like in(…)

like

SELECT `sid`,`sname`,`smajor`
FROM `student` 
WHERE `sname` LIKE '%王%' //字符前后任意个字符满足即可
********************************************
WHERE `sname` LIKE '_王_' //字符前后必须有且只有一个字符，否则查不到

in

SELECT `sid`,`sname`,`smajor`
FROM `student` 
WHERE `sname` IN ('王'，'李') //符合in中条件就可以查到

外连接-联表查询

联表查询的语句

select + 查询字段

from + ==主表(这里主的含义是指如果select* 的话，行是主表的全部字段及on条件引入的次表条件字段)

** join + 次表

on + 条件

内连接 inner join(等值连接) 只返回两个表中联结字段相等的行

以ON条件为核心判断

ON s.sid = sd.sid

只有满足ON条件的信息才会被查询

SELECT s.`sid`,`sname`,`smajor`,`shobit`
FROM `student` s
INNER JOIN `student_detail` sd
ON s.sid = sd.sid

左连接 left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录

以上述主表为核心查询

核心指前边的表

返回左表的全部记录和ON条件内符合条件的右表记录

SELECT s.`sid`,`sname`,`smajor`,`shobit`
FROM `student` s
LEFT JOIN `student_detail` sd
ON s.sid = sd.sid

右连接 right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

以上述次表为核心查询

核心指后边的表

返回右表的全部记录和ON条件内符合条件的左表记录

RIGHT JOIN student_detail sd

联查多个表的思路

先两两联查，再接着联查

SELECT s.`sid`,`sname`,`smajor`,`shobit`
FROM `student` s
LEFT JOIN `student_detail` sd
ON s.sid = sd.sid
INNER JOIN `student_class` sc
ON s.sid = sc.sid

自连接查询

自连接是指根据一张表中的可联系信息把一张表拆分为多个表

sid指自己的学科id，mid指他们所属的学科id，mid为1指父学科

SELECT a.`name` AS '父学科', b.`name` AS '子学科'
FROM `test` AS a, `test`AS  b
WHERE a.`sid` = b.`mid`

子查询-嵌套查询

查询套查询

原理和实现与外连接相同

分页查询

limit

limit(5,7)

指从第5+1 6个数开始，因为下标从0开始，以每一页7个元素分页

查询排序

order by

order by 字段 asc 升序排序

order by 字段 desc 降序排序

聚合函数

COUNT(计数) SUM(求和) AVG(平均值) MAX(最大值) MIN(最小值)

SELECT COUNT(字段) from student

其他同理

分组查询和过滤

group by having

SELECT sid,sname,smajor,sgrade
FROM student
GROUP BY sgrade
HAVING sid BETWEEN 2 AND 11

事务

事务的概念

一组操作要么全部完成，要么全都不做，不允许单独完成

事务的ACID原则

A:原子性 事务内的操作要么全部成功，要么全部失败

假如A转给B 100元，A-100 B+100 不能A少了100.但是B没有收到

C:一致性 事务执行前后结果不变

假如A转给B 100元，A-100 B+100 事务完成不能影响他们的结果，总和不可以变

I: 隔离性 对同一内容进行操作的事务，用户之间的操作不可相互影响

假如A转给B 100元，C转给B 50元， A-100 B+100 B+50 C-50 这两个事务之间不能互相干扰，影响操作结果

D: 持久性 事务一旦提交，就永久保存到数据库，不会断电即失，因为存到了硬盘

并发事务问题

脏读

一个事务修改了数据，但是未提交到数据库，没有保存，其他事务读到的当前数据可能不准确。

不可重复读

一个事务连续读数据，可能有一时刻其他事务修改了数据，导致该事务读取数据异常。

幻读，虚读

一个事务连续读数据，可能有一时刻其他事务增加或者删除了数据，导致该事务读取数据异常。

幻读和和不可重复读的区别是，幻读

基本语法

-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交

-- 开始一个事务,标记事务的起始点
START TRANSACTION  

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 还原MySQL数据库的自动提交
SET autocommit =1;

-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

索引

索引的作用

加快数据多的数据库上的查询，数据少索引的效果不明显

索引分类

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

索引创建

创建表时

CREATE TABLE 表名 (
               字段名1 数据类型 [完整性约束条件…],
               字段名2 数据类型 [完整性约束条件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (字段名[(长度)] [ASC |DESC])
               );

在已存在的表上创建索引,

CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                    ON 表名 (字段名[(长度)] [ASC |DESC]) ;

在已存在的表上创建索引

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            索引名 (字段名[(长度)] [ASC |DESC]) ;

查看表结构

SHOW INDEX FROM 表名查看所有的索引信息

EXPLAIN SELECT FROM 表名查看表的结构

数据库设计规范

三大范式

第二范式前提满足第一范式，第三范式前提满足第二范式

第一范式

数据库表中字段的内容不可再分

比如学生的家庭信息，可以分为几口人，还有家庭住址，这都属于家庭信息，这样不满足第一范式规范

第二范式

数据库表中每一条字段必须要与主键字段有联系，没有联系的字段不能存在这个表中

比如用户表中的商品信息，不可存在这样没有关联的字段

在有联合主键的表中尤其要注意符合第二范式，只与一条主键产生联系也不可以，必须同时产生联系

补充：联合主键

主键只有一个！！！！

一张数据库表只有一个主键这是规定

联合主键是指多条字段联合起来作为主键，也是一个主键，这是一种思想，且单独一条主键可以重复——便于查看相同元素

联合主键让一张表的内容更加清晰，加快检索速度

第三范式

数据库表中每一条字段必须要与主键字段有联系，没有直接联系的字段不能存在这个表中，不可间接联系

第三范式是第二范式的增强，字段必须和主键有直接联系才可以

规范与实际开发中性能的选择

阿里巴巴规定关联查询的表不能超过三张

而三大范式的规范使得一些字段必须在其他表中定义，分开冗余字段，关联查询就必须联合多张表

在实际开发中，会因用户体验和开发成本考虑选择以性能为主的开发思路而尽可能的遵守三大范式

适当的存在冗余字段或加入计算列是一种正常的以性能为考量的开发手段

JDBC

首先要有数据库连接驱动

导入mysql-connector-java.jar

在java中的代码实现为下

public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2进入用户
        String url = "jdbc:mysql://localhost:3306/guojinhao?useSSL=true&characterEncoding=utf-8&timeZone=CST";
        String username = "root";
        String password = "12345";
        //3创建数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);
        //4创建操作sql的对象
        Statement statement = connection.createStatement();
        //5操作数据库
        String sql = "SELECT `sname`,`sid`,`smajor`,`num`\n" +
                "FROM `student`\n" +
                "WHERE `sid` BETWEEN 2 AND 10\n" +
                "ORDER BY `num` ASC\n";
    	//返回查询结果集
        ResultSet resultSet = statement.executeQuery(sql);  //更改，删除，添加 都有一个executeUpdate方法返回操作影响行数
        //调用结果集的方法来打印结果
   	    while (resultSet.next()){
            System.out.println("sid="+resultSet.getObject("sid"));
            System.out.println("sname="+resultSet.getObject("sname"));
            System.out.println("smajor="+resultSet.getObject("smajor"));
            System.out.println("num="+resultSet.getObject("num"));
        }
    }

SQL注入

什么是SQL注入

SQL 注入就是在用户输入的字符串中加入 SQL 语句，如果在设计不良的程序中忽略了检查，那么这些注入进去的 SQL 语句就会被数据库服务器误认为是正常的 SQL 语句而运行，攻击者就可以执行计划外的命令或访问未被授权的数据

比如输入用户名“ ‘wang’ or 1=1”这种字段，普通的操作SQL的对象statement是无法进行鉴别的

preparedStatement

会在传值之前加一个预编译SQL的执行流程，先写参数为？的SQL，再通过preparedStatement的set方法把参数传到SQL中进行数据库操作

原理是什么

preparedStatement会把传入的参数当做字符串，默认添加一个双引号进行多重判断，如果传入参数有转义字符如 ‘ ，传入参数会被忽略

回顾MyBatis中 #{} 和 &{} 的区别

#{}是预编译处理，${}是字符串替换。

Mybatis在处理#{}时，会将sql中的#{}替换为?号，调用PreparedStatement的set方法来赋值

Mybatis在处理$时，就是把${}替换成变量的值

使用#{}可以有效的防止SQL注入，提高系统安全性