走进mysql的世界

Mysql

Mysql登录方式

mysql的本地登录命令 mysql -uroot(root为用户名) -proot(root为密码)
或者mysql -root -p 就可以隐藏密码式的登录

Mysql数据模型

    DDL(数据定义语言):操作数据库、表，关键字(create、drop、alter)
    DML（数据操作语言）:增删改表中的数据，关键字(insert、delete、update)
    DQL(数据查询语言):查询表中的数据，关键字(select、where)
    DCL(数据控制语言):授权,关键字(GRANT、REVOKE)
    

where 和 having的区别

where是在分组之前进行限定条件，而having是在分组之后进行限定.
where后不能跟聚合函数，having后能跟聚合函数的判定

一、MyLSAM与InnoDB的区别

	MyLSAM	InnoDB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文搜索	支持	不支持
表空间大小	较小	较大，约为MyLSAM的两倍

各自有点：

• MySLAM：节约空间，速度较快
• InnoDB ：安全性高，支持事务的处理，多表多用户操作

二、联表查询

Join对比

七种连接查询

右连接：以右边的表为基准

左连接：以左边的表为基准

中间连接：无所有以哪边的表为基准

where 和 Join on 的区别

• Join 【表名】 on 【判断条件】 连接查询(固定语法)

• where 等值查询

名称	描述
Inner Join	查询并集（相同部分）如果表中至少有一个匹配，就返回行数据
Left Join	会从左表中返回所有值，即使在右表中没有匹配
Right Join	会从右表中返回所有的值，即使左表中没有匹配

自连接的使用

• 自己跟自己连接，把一张表拆分成两张表

/*
自连接
   数据表与自身进行连接

需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
    查询父栏目名称和其他子栏目名称
*/

-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 解题思路：找到相匹配的字段，既能显示出来

-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

三、分页(limit)和排序(order by)

• 排序 （升序与降序）：select * from 【表名】 where 【条件】 order by 字段名 【ASC/DESC】

• 分页 ： select * from 【表名】 where 【条件】limit 0,5

• 语法：limit 【数据库数据下标起始值】, 【每页显示几条数据】 *（n-1）pageSize，pageSize

• 公式

页数	数据	从第几条数据开始
第一页	limit 0,5	(1-1) * 5
第二页	limit 5,5	(2-1) * 5
第三页	limit 10,5	(3-1) * 5
第N页	limit (N-1)*5 ,5	(N - 1) * 5

如需查询前几条也可采取分页操作

栗子：查询学生表前五条数据?

• select * from student limit 0,5

四、mysql函数

Mysql的curd的执行顺序

SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

4.1、mysql常用函数

select ABS(x)  -- 取绝对值
select SQRT(x)  -- 返回非负数的x的二次方根
select MOD(x,y)  -- 返回x被y除后的余数
select CEIL(x)、CEILING(x)  -- 返回向上取整
select FLOOR(x)  -- 向下取整

-- 字符串函数
LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str) -- 前两者将str中的字母全部转换成小写，后两者将字符串中的字母全部转换成大写
INSERT(s1,x,len,s2) -- 返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符
LEFT(s,n)、RIGHT(s,n) -- 前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符
TRIM(s) -- 返回字符串s删除了两边空格之后的字符串
SPACE(n) -- 返回一个由n个空格组成的字符串
SUBSTRING(s,n,len)、MID(s,n,len) -- 两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串

-- 日期和时间函数
CURDATE()、CURRENT_DATE()   -- 将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定

CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE() -- 这四个函数作用相同，返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定

UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date) -- 前者返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数，后者返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数

FROM_UNIXTIME(date) --和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间

UTC_DATE()和UTC_TIME() -- 其格式为"YYYY-MM-DD"或"YYYYMMDD"，后者返回当前UTC时间值，其格式为"YYYY-MM-DD"或"YYYYMMDD"。具体使用哪种取决于函数用在字符串还是数字语境中

MONTH(date)和MONTHNAME(date) -- 前者返回指定日期中的月份，后者返回指定日期中的月份的名称

DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d) -- DAYNAME(d)返回d对应的工作日的英文名称，如Sunday、Monday等；DAYOFWEEK(d)返回的对应一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d对应的工作日索引，0表示周一，1表示周二

4.2、聚合函数

select count(列) from student -- 返回列值非空的行的数量，不包含null值的行

select count(*) from student -- count(*)：返回表中满足where条件的行的数量，包含null值的行

select count(1) from student -- count(1)：返回表中满足where条件的行的数量，包含null值的行

select max(列) from student -- max(列)选出这列最大的值

select min(列) from student -- min(列)选出这列最小的值

select sum(列) from student -- min(列)求和

select avg(列) from student -- min(列)求平均值

4.3、数据库级别的MD5加密

MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致，它可以对数据库嗦存储的密码进行加密，而且每次加密之后的MD5值都会不同 ，且不可逆。

栗子：设置密码进行加密

UPDATE testmd5 set `password` = MD5(`password`) where ...;

以下参考博客

五、事务的理解

什么是事务？

​ 事务是可以一次执行多个sql语句，并且它是作为一组操作进行执行，要么都执行，要么都不执行，它是一组不可分割的操作集合。

5.1、事务的四大特性（ACID）

原子性 （Atomicity）
事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

一致性 （Consistency）
事 务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。 （也就是本来两个相互转账的人共同只有1000块，怎么转最后加起来还是1000块，要保证数据最后的一致性。）

隔离性 （Isolation）
一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。 （两个事务之间，互不干扰）

持久性 （Durability）
也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。 （事务一旦commit，它就会写入到数据库中进行永久保存。）

开启事务自动提交功能

# 修改当前会话变量 autocommit ，可以使用 on/off 0/1 true/false
set autocommit=off;

事务的开启

start transaction;

事务的保存

savepoint 事务保存名

事务的提交

commit

事务的回滚

rollback to 事务保存名

​ 事务出现的一系列问题及解决方案

以下知识点参考博客

脏读

​ 脏读即B读取A的数据，A又进行修改操作，B又再次读取但是读了之后A又进行回滚操作，A又回到了最初的数据，那么B所读的数据就是错误数据，这种操作即为脏读。

不可重复读

​ 不可重复读发生在一个事务执行相同的查询两次或两次以上，但是每次都得到不同的数据时。这通常是因为另一个并发事务在两次查询期间进行了更新。

幻读

​ 幻读与不可重复读类似。它发生在一个事务并执行了操作（T1），接着另一个并发事务也执行插入了一些数据时（T2）。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录。

参考博客

参考狂神教学

六、索引

索引的作用

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化.

分类

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

主键索引

主键 : 某一个属性组能唯一标识一条记录

特点 :

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

• 主键索引只能有一个
• 唯一索引可能有多个

CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   -- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

常规索引

作用 : 快速定位特定数据

注意 :

• index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

CREATE TABLE `result`(
   -- 省略一些代码
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)
-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

全文索引

百度搜索：全文索引

作用 : 快速定位特定数据

注意 :

• 只能用于MyISAM类型的数据表
• 只能用于CHAR , VARCHAR , TEXT数据列类型
• 适合大型数据集

/*
#方法一：创建表时
  　　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 版本、存储引擎和数据类型是否支持全文索引。
*/

拓展：测试索引

CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

批量插入数据：100w

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

索引效率测试

无索引

SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ALL
possible_keys: NULL
        key: NULL
    key_len: NULL
        ref: NULL
        rows: 992759
    filtered: 10.00
      Extra: Using where
1 row in set, 1 warning (0.00 sec)

创建索引

CREATE INDEX idx_app_user_name ON app_user(name);

测试普通索引

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ref
possible_keys: idx_app_user_name
        key: idx_app_user_name
    key_len: 203
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

索引准则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

索引的数据结构

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

七、三大范式

​ 三大范式的作用

• 解决信息重复
• 解决更新异常
• 解决插入异常（无法显示正常信息）
• 解决删除异常（丢失有效的信息）

三大范式的设计

• 第一范式(1NF):（满足原子性,保证每一列不可再分）要求数据库表的每一列都是不可分割的原子数据项。
• 第二范式(2NF):（满足第一范式，每张表只描述一件事情）把一张表无相关的其他字段进行分表处理。
• 第三范式(3NF):（满足第一范式和第二范式）第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范性和性能问题

• 关联查询的表不得超过三张表 (以便查询速度快)

• 在设计表时，即便规范性比较重要，但是数据库的性能更加的重要

• 故意给某些表增加一些冗余字段以便把多表查询变成单标查询

• 故意增加一些计算列，从大数据量降低为小数据量的查询（如果建立索引比较耗内存，索引树比较消耗内存）

八、JDBC名词解释

JDBC加载驱动

static{
    // 将Mysql加载到JVM中
    Class.forName("com.mysql.jdbc.Driver");
}

获取连接对象:可以操作数据库

/* url：即连接数据库的方式
一般就是：jdbc:mysql://localhost:3306?数据库名&参数1&参数2

serverTimezone:设置时区
useUnicode: 字符集设置打开
characterEncoding : 设置为中文编码防止乱码

栗子：
jdbc:mysql://localhost:3306/test?useUnicode=true&serverTimezone=UTC&characterEncoding=utf-8

*/

Connection connection = DriverManager.getConnection(url,username,password)
    
    
connection.rollback();  // 对事务执行回滚
connection.commit();  // 对事务进行提交
connection.setAutoCommit(); //对事务设置是否自动提交

Statement对象

// Statement对象是执行SQL的对象 || PrepareStatement也是执行SQL的对象
Statement statement = connection.CreateStatement();

// Sql编写
String sql = "select * from user";

statement.executeQuery(sql)  //执行查询  返回ResultSet
statement.execute(sql);   //执行任何SQL
statement.executeUpdate(sql); // 执行更新、插入、删除，返回一个受影响的行数

Result 对象

statement.executeQuery(sql)  //执行查询  返回ResultSet
statement.execute(sql);   //执行任何SQL
statement.executeUpdate(sql); // 执行更新、插入、删除，返回一个受影响的行数

释放对象

public static void release(Connection connection,Statement statement,ResultSet resultSet){
    if(connection != null){
        try{
            connection.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
      if(statement != null){
            try{
                statement.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
    }
        if(resultSet != null){
            try{
                resultSet.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
    }
}

九、SQL注入问题

Statement 是不安全的存在SQL注入问题

PrepareStatement对象进行防止SQL注入问题

Statement 与PrepareStatement 区别

// 存在Sql注入问题	
Statement 是编写sql之后直接就执行sql
    栗子：statement.executeQuery(sql)
    
// 不存在Sql注入问题    
PrepareStatement的先编写sql并使用问号占位符进行占位
    再使用 preparedStatement.setString(1, userName); 对问号占位符进行赋值
    最后再执行SQL
    	int result = preparedStatement.executeUpdate(); //执行完Sql并返回受影响的行数

// 提前书写Sql  然后使用问号占位符进行占位

connection = JDBCUtils.getConnection(); 	// 连接数据库
        String sql = "update user set name = ?,username = ?,password = ? where id = ?";
		//预编译Sql，先不执行。
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
		// preparedStatement.setString()   问号填充值
        preparedStatement.setString(1, userName); 
        preparedStatement.setString(2, userAccount);
        preparedStatement.setString(3, userPassword);
        preparedStatement.setString(4, String.valueOf(id));
//        preparedStatement.setString(4, salary);
        int result = preparedStatement.executeUpdate(); // 执行替换得到一个受影响的行数

分析

// 替换更新
connection = JDBCUtils.getConnection(); 	// 连接数据库
        String sql = "update user set name = ?,username = ?,password = ? where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
		// preparedStatement.setString()   问号填充值
        preparedStatement.setString(1, userName); 
        preparedStatement.setString(2, userAccount);
        preparedStatement.setString(3, userPassword);
        preparedStatement.setString(4, String.valueOf(id));
//        preparedStatement.setString(4, salary);
        int result = preparedStatement.executeUpdate(); // 执行替换得到一个受影响的行数



// 查询
connection = JDBCUtils.getConnection();
        String sql = "SELECT * from user where username = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,queryUserByUserName);
        ResultSet resultSet = preparedStatement.executeQuery();
        User user = null;
		// 把查询得到的数据封装到resultSet这个结果集中，可以用while循环进行打印输出。
        while (resultSet.next()) {
            int id = resultSet.getInt(1);
            String s1 = resultSet.getString(2);
            String s2 = resultSet.getString(3);
            String s3 = resultSet.getString(4);
            String s4 = resultSet.getString(5);
            user = new User(id, s1, s2, s3, s4);
        }