mysql学习（二）

一、常用函数

-- =============常用函数==================
-- 数学运算
SELECT ABS(-8)         -- 绝对值
SELECT CEILING(9.4)    -- 向上取整
SELECT FLOOR(9.4)      -- 向下取整
SELECT RAND()          -- 0到1之间随机数
SELECT SIGN(-10)       -- 返回符号，0返回0，负数返回-1，正数返回1
-- 字符串
SELECT CHAR_LENGTH('我是说')          -- 字符串长度
SELECT CONCAT('我','认识','你')       -- 拼接字符串
SELECT INSERT('我认识你',2,2,'见过')  -- 替换x位置长度为y的字符串‘我见过你’
SELECT INSERT('我认识你',2,0,'不')    -- 插入x位置的字符串‘我不认识你’
SELECT LOWER(Ssr)  -- 小写
SELECT UPPER(Ssr)  -- 大写
SELECT INSTR('shanghai','h')          -- 返回第一次出现子串的位置
SELECT REPLACE('那年十八','那','今')  -- 替换出现的指定字符串
SELECT SUBSTR('12345678',3,3)         -- 截取字符串，起始位置，截取数量
SELECT REVERSE('123')                 -- 反转
 
 
-- 查询姓周的同学，改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE student LIKE '周%'
 
-- 时间和日期函数（记住）
SELECT CURRENT_DATE()    -- 获取当前日期
SELECT NOW()             -- 时间
 
-- 系统
SELECT USER()         -- 当前用户
SELECT VERSION()         -- 当前版本

分页和排序

排序：

-- =============================分页limit 和排序order by===========================================
-- 排序：升序asc 降序 desc
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询结果根据成绩升序，降序
SELECT s.studentno AS '学号',studentname AS '姓名',subjectname AS '科目名称',studentresult AS '分数'
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno=r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno=sub.subjectno
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC

分页

-- 分页，每页只显示两条数据
-- 语法：limit 起始值，页面大小
-- 网页应用 ：当前页，总的页数，页面的大小
-- LIMIT 0,2   第一条数据后的两条数据
-- LIMIT 2,2   第三条数据后的两条数据
SELECT s.studentno AS '学号',studentname AS '姓名',subjectname AS '科目名称',studentresult AS '分数'
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno=r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno=sub.subjectno
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC
LIMIT 4,2
 
-- 网页应用 ：当前页，总的页数(数据总是除以页面大小向上取整)，页面的大小
-- 第一页 LIMIT 0,2   (1-1)*2
-- 第二页 LIMIT 2,2   (2-1)*2
-- 第三页 LIMIT 4,2   (3-1)*2
-- 第N 页 LIMIT (n-1)*pagesize,pagesize

子查询

-- ========================where============================
 
-- 1.查询高等数学1所有考试结果（学号，科目编号，成绩），降序
-- 方式1：使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY studentresult DESC
 
-- 方式2：使用子查询（由内到外）
SELECT `studentno`,`subjectno`,`studentresult`
FROM result
WHERE subjectno=(
    SELECT subjectno FROM `subject`
    WHERE subjectname = '高等数学-1'
)
ORDER BY studentresult DESC
 
-- 查询科目为高等数学1，且成绩大于80分的学生（学号，姓名） 
SELECT studentno,studentname FROM student WHERE studentno IN(
    SELECT studentno FROM result WHERE studentresult>=80 AND subjectno=(
        SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-1'
    )
)

二、事务

事务原则：ACID原则  原子性，一致性，隔离性，持久性

原子性（要么都成功，要么都失败）

一致性（事务前后的数据完整性保持一致）

隔离性（多个并发事务之间要隔离）

持久性（事务一旦提交就不可逆，被持久化到数据库中）

隔离所导致的一些问题

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

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

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

 

-- mysql是默认开启事务自动提交的
SET autocommit = 0  /*关闭*/
SET autocommit = 1 /*开启*/
-- 手动处理事务
SET autocommit = 0  -- 关闭自动提交

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

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

-- 事务结束
SET autocoment = 1 -- 开启自动提交

-- 了解
SAVEPOINT 保存点名 -- 开启一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名  -- 撤销保存点

 三、索引

索引的分类：

• 主键索引（PRIMARY KEY）：唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引（UNIQUE KEY）：避免重复的行出现，多个列都可以标识为唯一索引
• 常规索引（KEY/INDEX）：默认的：
• 全文索引（FullText）：在特定数据库引擎下才有，MyISAM，快速定位数据

-- 显示所有的索引信息
SHOW INDEX FROM student
 
-- 增加一个全文索引（索引名，列名）
ALTER TABLE SCHOOL.student ADD FULLTEXT INDEX `studentname`(`studentname`);
 
-- EXPLAIN 分析sql执行的状况
 
EXPLAIN SELECT * FROM student;  -- 非全文索引
 
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) against('存');

 为了更加直观的看到索引的用处

先创建一个表

CREATE TABLE `app_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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

在表中插入一百万个数据

DROP FUNCTION IF EXISTS mock_data;
-- 写函数之前必须要写，标志:$$
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
-- 注意returns，否则报错。
BEGIN
DECLARE num INT DEFAULT 1000000;
-- num 作为截止数字，定义为百万，
DECLARE i INT DEFAULT 0;
WHILE i < num DO
   INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), CONCAT('100',i,'@qq.com'), CONCAT('13', 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'; --  0.779 sec
 SELECT *FROM app_user WHERE `name` = '用户9999';  -- 0.001 sec 创建索引后
 SELECT *FROM student
 
 -- id_表名_字段名
 -- create index 索引名 on 表（字段）
 CREATE INDEX id_app_user_name ON app_user(`name`);

从查询时间来看，索引在数据量小的时候，用处不大，但在大数据的时候区别十分明显

索引原则

• 索引不是越多越好
• 不要对进程变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加载常用查询的字段上

http://blog.codinglabs.org/articles/theory-of-mysql-index.html   这篇文章是对索引以及计算机底层原理的解释

数据用户管理

 -- 创建用户 create user 用户名 identified by '密码'
 CREATE USER kkk IDENTIFIED BY '123456'
 
 -- 修改密码（修改当前用户密码）
 SET PASSWORD = PASSWORD('123177')
 
 -- 修改指定用户的密码 
 SET PASSWORD FOR kkk = PASSWORD('121345')
 
 -- 重命名  RENAME USER 原来的名字 To 新的名字
 RENAME USER kkk TO kkk2
 
 -- 用户授权 ALL PRIVILEGES 全部的权限
 -- ALL PRIVILEGES除了不能给别人授权外，其他的都可以
 GRANT ALL PRIVILEGES ON *.* TO kkk2 
 
 -- 查询权限
 SHOW GRANTS FOR kkk2 -- 查看指定用户的权限
 
 SHOW GRANTS FOR root@localhost
 
 -- 撤销权限  .代表全局的权限
 REVOKE ALL PRIVILEGES ON *.* FROM kkk2
 
 -- 删除用户
 DROP USER kkk2