MySQL入门教程-函数,索引

4MySQL函数

常用函数

 -- 数学运算
 SELECT ABS(-8); -- 绝对值
 SELECT CEIL(5.1); -- 向上取整
 SELECT CEILING(5.1); -- 向上取整
 SELECT RAND(); -- 返回0~1之间的一个随机数
 SELECT SIGN(-10); -- 返回一个数的符号;0返回0;正数返回1;负数返回-1
 ​
 -- 字符串函数
 SELECT CHAR_LENGTH('我喜欢你'); -- 字符串长度
 SELECT CONCAT('我','喜欢','你'); -- 拼接字符串
 SELECT INSERT('我喜欢',1,1,'超级') -- INSERT(str,pos,len,newstr) 从str的pos位置开始替换为长度为len的newstr
 SELECT UPPER('zsr'); -- 转大写
 SELECT LOWER('ZSR'); -- 转小写
 SELECT INSTR('zsrs','s'); -- 返回第一次出现字串索引的位置
 SELECT REPLACE('加油就能胜利','加油','坚持'); -- 替换出现的指定字符串
 SELECT SUBSTR('坚持就是胜利',3,6); -- 返回指定的字符串(源字符串,截取位置,截取长度)
 SELECT REVERSE('rsz'); -- 反转字符串
 ​
 -- 时间日期函数
 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());
 ​
 -- 系统信息
 SELECT SYSTEM_USER();
 SELECT USER();
 SELECT VERSION();

聚合函数

 SELECT COUNT(StudentName) FROM student; 
 SELECT COUNT(*) FROM student;
 SELECT COUNT(1) FROM student;
 ​
 SELECT SUM(`StudentResult`) FROM result;
 SELECT AVG(`StudentResult`) FROM result;
 SELECT MAX(`StudentResult`) FROM result;
 SELECT MIN(`StudentResult`) FROM result;

数据库级别的MD5加密

MD5信息摘要算法（MD5 Message-Digest Algorithm）

• MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性

• MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值

 CREATE TABLE `testMD5`(
     `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 `testMD5` VALUES(1,'zsr','200024'),
 (2,'gcc','000421'),(3,'bareth','123456');
 ​
 -- 加密
 UPDATE `testMD5` SET `pwd`=MD5(pwd) WHE RE id=1;
 UPDATE `testMD5` SET `pwd`=MD5(pwd); -- 加密全部的密码
 ​
 -- 插入的时候加密
 INSERT INTO `testMD5` VALUES(4,'barry',MD5('654321'));
 ​
 -- 如何校验:将用户传递进来的密码，进行MD5加密，然后对比加密后的值
 SELECT * FROM `testMD5` WHERE `name`='barry' AND `pwd`=MD5('654321');

5.事务

要么都成功，要么都失败

 SQL执行：A转账给B
 SQL执行：B收到A的钱

将一组SQL放在一个批次中去执行

• 例如银行转账：只有A转账成功且B成功到账，该事件才算结束，如果一方不成功，则该事务不成功

事务原则：ACID

事务并发导致的问题

隔离级别

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别

读未提交：一个事务读取到其他事务未提交的数据；这种隔离级别下，查询不会加锁，一致性最差，会产生脏读、不可重复读、幻读的问题

读已提交：一个事务只能读取到其他事务已经提交的数据；该隔离级别避免了脏读问题的产生，但是不可重复读和幻读的问题仍然存在；

读提交事务隔离级别是大多数流行数据库的默认事务隔离级别，比如 Oracle，但是不是 MySQL 的默认隔离界别

可重复读：事务在执行过程中可以读取到其他事务已提交的新插入的数据，但是不能读取其他事务对数据的修改，也就是说多次读取同一记录的结果相同；该个里级别避免了脏读、不可重复度的问题，但是仍然无法避免幻读的问题

可重复读是MySQL默认的隔离级别

串行化：事务串行化执行，事务只能一个接着一个地执行,、，并且在执行过程中完全看不到其他事务对数据所做的更新；缺点是并发能力差，最严格的事务隔离，完全符合ACID原则，但是对性能影响比较大

执行事务的过程

1️⃣ 关闭自动提交

 SET autocommit=0;

2️⃣ 事务开启

 START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

3️⃣ 成功则提交，失败则回滚

 -- 提交：持久化（成功）
 COMMIT
 -- 回滚：回到原来的样子（失败）
 ROLLBACK

4️⃣ 事务结束

 SET autocommit=1; -- 开启自动提交

5️⃣ 其他操作

 SAVEPOINT 保存点名; -- 设置一个事务的保存点
 ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点
 RELEASE SAVEPOINT 保存点名; -- 撤销保存点

6、索引

索引（`Index`）是帮助MySQL高效获取数据的**数据结构**。

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

索引的分类

 -- 创建学生表student
 CREATE TABLE `student`( 
     `StudentNo` INT(4) NOT NULL COMMENT '学号',
     `LoginPwd` VARCHAR(20) DEFAULT NULL,
     `StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
     `Sex` TINYINT(1) DEFAULT NULL COMMENT '性别,取值0或1',
     `GradeID` INT(11) DEFAULT NULL COMMENT '年级编号',
     `Phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空,即可选输入',
     `Adress` VARCHAR(255) NOT NULL COMMENT '地址,允许为空,即可选输入',
     `BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',
     `Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号,允许为空,即可选输入',
     `IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
     PRIMARY KEY (`StudentNo`),
     UNIQUE KEY `IdentityCard` (`IdentityCard`),
     KEY `Email` (`Email`)
 )ENGINE=MYISAM DEFAULT CHARSET=utf8;

主键索引（PRIMARY KEY）

唯一的标识，主键不可重复，只有一个列作为主键

• 最常见的索引类型，不允许为空值

• 确保数据记录的唯一性

• 确定特定数据记录在数据库中的位置

 -- 创建表的时候指定主键索引
 CREATE TABLE tableName(
   ......
   PRIMARY INDEX (columeName)
 )
 ​
 -- 修改表结构添加主键索引
 ALTER TABLE tableName ADD PRIMARY INDEX (columnName)

普通索引（KEY / INDEX）

默认的，快速定位特定数据

• index 和 key 关键字都可以设置常规索引

• 应加在查询找条件的字段

• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

 -- 直接创建普通索引
 CREATE INDEX indexName ON tableName (columnName)
 ​
 -- 创建表的时候指定普通索引
 CREATE TABLE tableName(
   ......
   INDEX [indexName] (columeName)
 )
 ​
 -- 修改表结构添加普通索引
 ALTER TABLE tableName ADD INDEX indexName(columnName)

唯一索引（UNIQUE KEY）

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值

与主键索引的区别：主键索引只能有一个、唯一索引可以有多个

 -- 直接创建唯一索引
 CREATE UNIQUE INDEX indexName ON tableName(columnName)
 ​
 -- 创建表的时候指定唯一索引
 CREATE TABLE tableName(  
     ......
     UNIQUE INDEX [indexName] (columeName)  
 );  
 ​
 -- 修改表结构添加唯一索引
 ALTER TABLE tableName ADD UNIQUE INDEX [indexName] (columnName)

全文索引（FULLText）

快速定位特定数据（百度搜索就是全文索引）

• 在特定的数据库引擎下才有：MyISAM

• 只能用于CHAR , VARCHAR , TEXT数据列类型

• 适合大型数据集

 -- 增加一个全文索引
 ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);
 ​
 -- EXPLAIN 分析sql执行的情况
 EXPLAIN SELECT * FROM student; -- 非全文索引
 EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d'); -- 全文索引

索引的使用

索引的创建

• 在创建表的时候给字段增加索引

 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 表名;