MySQL数据库之常用函数、视图、索引、触发器

常用函数及视图

常用函数

#### 常用函数
-- FORMAT(X,N) 以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，
-- 最后一位四舍五入
SELECT FORMAT(1234.5678,2)
-- LTRIM(s)去掉字符串 s 开始处的空格 
SELECT LTRIM('           NI HAO A ')
-- RTRIM(s)去掉字符串 s 结尾处的空格
SELECT RTRIM('# NI HAO A ')
-- REPEAT(s,n)将字符串 s 重复 n 次
SELECT REPEAT('HELLO ',3)
-- 获取月份
SELECT MONTH('2019-02-12')
SELECT MONTH(NOW())
SELECT MONTHNAME(NOW())
-- 获取天  DAYOFYEAR(d)
-- 从参数年01.01号到参数日期是第几天 
SELECT DAYOFYEAR(NOW())
-- 计算当前月的第几天
SELECT DAYOFMONTH(NOW())
-- 获取日期格式中的天
SELECT DAY(NOW())
-- 将日期转化为指定的日期格式
SELECT DATE_FORMAT(NOW(),'%Y/%m/%d')
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')
SELECT DATE_FORMAT(NOW(),'%Y/%m/%d %T')
-- DATE()从日期或日期时间表达式中提取日期值
SELECT DATE(NOW())
-- DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数
SELECT DATEDIFF(NOW(),'2020-01-16')
-- 在字符串“I love ”和字符串“beijing”合并为同一个字符串
SELECT CONCAT('I love','beijing');
-- 返回字符串“me”在字符串“You love me. He love me.”中第一次出现的位置
SELECT LOCATE('me','You love me.He love me')
-- 查看当前数据库的版本号，当前数据库名和当前用户
SELECT DATABASE();
SELECT USER();
-- 使用字符串“college”来加密字符串“university”
SELECT ENCODE('college','university');
-- 把字符串“i love you”转换成大写
SELECT UPPER('i love you');
--把字符串“i love you”转换成小写
SELECT LOWER('I LOVE YOU');
-- 获取顾客表每位邮箱的长度
SELECT LENGTH(cemail) AS 长度FROM customer;
-- 提取顾客邮件信息,起始位置为 7，长度为 5
SELECT SUBSTRING(cemail,7,5) as 内容 FROM customer
-- 获取今年是哪一年，这个月是这一年的那个月，这一周是这一年的那一周，今天是这一年那一天
SELECT  YEAR(CURDATE()) as yr,
MONTH(CURDATE()) as mon, WEEK(CURDATE()) as wk, DAYOFYEAR(CURTIME()) AS yd;
-- 去掉字符串“	abc	”左边的空格，去掉字符串“	bcd	”右边的空格，去掉字符串“	bnm	”两边的空格
SELECT LTRIM('	abc	'),RTRIM('	bcd	'),TRIM('	bnm	')

exists关键字的用法：

-- 使用Exist查询所有参加考试的学生信息
SELECT * FROM student WHERE EXISTS
(SELECT 1 FROM score WHERE student.stuid = score.stuid)
-- 2.查询部门所有人工资都大于等于3000的部门信息
SELECT * FROM dept WHERE did IN
(SELECT did FROM employee 
GROUP BY did
HAVING MIN(salary) < 3000) 
-- 3.使用EXISTS查询所有“2016-02-02”有员工入职的部门信息
SELECT * FROM dept WHERE EXISTS 
(SELECT 1 FROM employee WHERE dept.did = employee.did AND hiredate = '2016-02-02')
-- 4.查询工资比财务部的所有员工工资都高的员工信息
SELECT * FROM employee WHERE salary >ALL
(SELECT salary FROM employee,dept 
WHERE employee.did = dept.did AND dname = '财务部')

SELECT * FROM employee WHERE salary >
(SELECT MAX(salary) FROM employee INNER JOIN dept 
ON employee.did = dept.did WHERE dname = '财务部')

IN和NOT IN
先执行子查询，子查询返回一个集合，然后再将子查询的结果作为外层查询的条件。
EXISTS和NOT EXISTS
先执行外层查询的第一条记录，再查内层符合的记录（查询完整张表）是否存在，存在则外层查询该记录满足；一次查询外层的第二条、第三条是否满足。

三大范式

1）第一范式：不允许表中有表。
2）第二范式：满足第一范式，并且不存在非主属性对候选码的部分依赖。
3）第三范式：满足第二范式，并且不存在非主属性对候选码的传递依赖。

**视图**

概念

是一种虚拟表，逻辑表，只存表结构，不存数据。

优点

保证数据安全，提升查询效率。
1.简单，用户不用关心对应的表结构、关联条件和筛选条件。
2.安全，用户只能访问被允许查询的结果集。
3.数据独立，一旦视图确定表结构，可以屏蔽表结构变化对用户的影响。

CREATE VIEW view_em
AS
SELECT  id,`name`,IF(sex=1,'男','女') sex,
				CASE(dept)
					WHEN 1001 THEN '行政部'
					WHEN 1002 THEN '销售部'
					WHEN 1003 THEN '技术部'
					ELSE '无部门'
				END deptname,
				IF(ispost,'在职','离职') post
FROM tb_em

SELECT `name`,post,deptname FROM view_em
-- 更新视图，删除视图数据，对应的基本表也改变了
UPDATE view_em SET name='张飞' WHERE id=1001
DELETE FROM view_em WHERE id=1006
-- 删除表
DROP TABLE IF EXISTS tb_em;
-- 修改视图 ALTER
ALTER VIEW view_mysql
AS
SELECT `name`,stu.stuid,score
FROM `subject` sub,student stu,score sco
WHERE sub.subid = sco.subid 
AND stu.stuid = sco.stuid AND subname = 'mysql'
-- 查看视图的表结构
DESC view_mysql

触发器、存储引擎和索引

存储引擎

概念

使用不同的存储机制来存储文件。

常见的存储引擎

存储引擎	特点
Innodb	数据库的首选引擎，支持事务操作，支持并发、外键。
myisam	基于ISAM的存储引擎，拥有较高的插入、查询速度，不支持事务。
memory	将表中的数据放在内存中，数据量不大、没有较高安全性，主要针对查询、访问数据。

索引

概念

是一个排序的列表，列表中存储这索引值及该值数据所在的物理地址。

分类

类型	特点
普通索引	用表的普通列建立的索引。
主键索引	根据主键创建的索引，不允许重复、空行、空值。
唯一索引	用来建立索引的列是空值，只允许最多一个为空值。
组合索引	用多个列组合构建的索引，不允许有空值。
全文索引	用文本对象对应的列构建的索引，可以对文章内容作索引。

优点

1.可以提升检索的效率，减少IO次数。

缺点

1.索引会占存储空间，是普通表的1.5倍。
2.降低数据表的修改操作，在修改数据表的同时还需要修改索引表。

适合创建索引的列

1.经常搜索的某些列，加快搜索的速度。
2.作为主键的列，加快搜索的速度。
3.经常用于连接表的列，主要指的是外键，可以加快连接的速度。
4.需要排序的列，利用索引的排序加快排序查询时间。
5.WHERE子句的列，加快条件判断速度。

索引的实现原理

1.不同的搜索引擎对索引的实现方式是不同的。
2.Myisam采用的是B+树
3.InnoDB默认支持B+树，也支持哈希

-- 查看存储引擎
SHOW ENGINES;
-- 创建索引 
-- 1.普通索引
-- 1.1直接创建索引 
USE studentmanager;

CREATE INDEX myindex1
ON student(name(20));

SHOW INDEX FROM student;
-- 1.2修改表的时候添加索引
ALTER TABLE student ADD INDEX myindex2 (phone(11))
-- 1.3创建表的时候同时创建索引
CREATE TABLE mytable(
	id INT NOT NULL PRIMARY KEY,
	username VARCHAR(32) NOT NULL,
	INDEX myindex3(username(32))
)
-- 2.唯一索引------------------------------------------------
USE studentmanager;
CREATE UNIQUE INDEX myindex4 ON `subject`(subname(32));
SHOW INDEX FROM `subject`;
-- 3.主键索引
-- 测试索引 
CREATE DATABASE bigdata;
USE bigdata;
CREATE TABLE test(
	id BIGINT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT'主键id',
	username VARCHAR(50) DEFAULT NULL COMMENT'用户名',
	nickname VARCHAR(50) DEFAULT NULL COMMENT'昵称',
	`password` VARCHAR(50) DEFAULT NULL COMMENT'密码'
)ENGINE=myisam AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8;

-- 模拟插入1000万条数据
CREATE PROCEDURE testproc()
BEGIN
	DECLARE num INT;
	SET num = 1;
	WHILE num<10000000 DO
		INSERT INTO test(username,nickname,`password`) VALUES(num,CONCAT('昵称',num),PASSWORD(num));
		-- 10000001,1,昵称1,密码
		SET num = num+1;
	END WHILE;
END

-- 调用存储过程
CALL testproc()

SELECT COUNT(1) FROM test
-- 在不使用索引的情况下查询
SELECT * FROM test WHERE nickname='昵称'
-- 建立索引
ALTER TABLE test ADD INDEX nickname_index(nickname)

SELECT * FROM test WHERE nickname='昵称'
-- 索引的删除
DROP INDEX nickname_index ON test
-- 修改表的方式删除索引
USE studentmanager;
SHOW INDEX FROM student;
ALTER TABLE student DROP INDEX myindex2;

触发器

定义

就是某个表发生一个事件，然后自动化执行预先编写好的SQL语句，执行相关操作。

触发条件

增删改

触发的频率

对每一行的操作

创建的四个要素

1.监视地点(table)
2.监视事件(增删改)
3.触发时间
– after：先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作。
– before：先完成触发，再增删改，触发的语句先于监视的增删改。
4.触发事件(增删改)

应用场景

比如：下订单

NEW和OLD

1.引用触发器发生变化的记录内容。
2.insert类型的触发器中，new表示将要或已经插入的数据。
3.Update类型的触发器中，Old表示将要或已经被修改的源数据；New表示修改后的新数据。
4.Delete类型的触发器，OLD表示将要或者已经删除的源数据。
5.使用方法：New.列名；Old.列名。
6.尽量不使用触发器，比较耗时。

-- 创建触发器的语法
-- 触发行的操作
CREATE TRIGGER tr_student_count
AFTER 
INSERT 
ON student
FOR EACH ROW
BEGIN 
	-- 修改班级为1的班级实际人数
	UPDATE classinfo SET ActualNum = ActualNum+1 WHERE cid = 1;
	UPDATE classinfo SET RemainderNum = RemainderNum-1 WHERE cid = 1;	
END
-- 测试 当插入一条数据后，触发器自动执行
INSERT INTO student(`name`,sex,phone,birthday,cid)
VALUES('蔡徐坤','男','12345678912','2019-01-01',1)

-- 对于新增而言，新插入的行使用new表示，行中新增的数据（每一列的值），

-- 删除触发器
DROP TRIGGER tr_student_count;
-- 创建新的触发器
CREATE TRIGGER tr_student_count
AFTER INSERT ON student
FOR EACH ROW
BEGIN
-- 修改班级实际人数
	UPDATE classinfo SET ActualNum = ActualNum+1 WHERE cid=new.cid;
	UPDATE classinfo SET RemainderNum = RemainderNum-1 WHERE cid=new.cid;	
END

INSERT INTO student(`name`,sex,phone,birthday,cid)
VALUES('吴亦凡','男','12345678912','2019-01-01',3)

-- 创建一个删除的触发器
-- 对于删除信息而言，想引用要删除的一行数据，用old来表示，old.列名
CREATE TRIGGER tr_student_delete
AFTER DELETE ON student
FOR EACH ROW 
BEGIN
	-- 修改班级实际人数和实际名额
	UPDATE classinfo SET ActualNum = ActualNum-1 WHERE cid = old.cid;
	UPDATE classinfo SET RemainderNum = RemainderNum+1 WHERE cid = old.cid;
END

-- 测试删除学生信息
DELETE FROM student WHERE stuid = 20;

-- 查看触发器
SHOW TRIGGERS;