MySql语法基础(二)视图、索引、系统函数

视图

● 一条SELECT语句执行后返回的结果集
● 是一张虚拟表
● 简化用户操作，减少复杂的SQL语句，增强可读性
● 权限控制：隐藏不希望用户访问到的列，对机密数据提供安全保护
创建视图-CREATE VIEW视图名(列1，列2…) AS SELECT (列1，列2…) FROM …[with… / grant…];
使用视图-SELECT * FROM view_name; 使用视图和使用表完全一样，是一张虚拟表；
修改视图-CREATE OR REPLACE VIEW 视图名 AS SELECT […] FROM […]; 存在则修改视图，不存在则创建视图
查看视图-SHOW TABLES [LIKE …]; 可以使用模糊查找。视图和查找表的方式一样。
查看视图详情-DESC 视图名; /-SHOW FIELDS FROM视图名;
删除视图-DROP VIEW view_name;

索引

查询

● 如果表中有大量的数据，现在要进行查询
○ 全表搜索，将所有记录一一取出，和查询条件一一对比，返回满足的记录
○ 将消耗大量的数据库系统时间
○ 大量的磁盘I/O操作，如果数据库的数据量不大，那么也不能带来很大的提升。

索引的概念

● 在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构
● 相当于书籍的目录，通过目录中的页码可以快速找到对应的内容
● 通过保存在索引中的ROWID（相当于页码）快速访问数据库中的特定信息，这样SQL语句执行得更快

语法

创建索引
普通创建-CREATE INDEX idx_s_phone ON table_s(phone);

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,…)
index_col_name : column_name[(length)][ASC | DESC]
分析
[UNIQUE|FULLTEXT|SPATIAL] 指定索引的类型
index_name 索引的名称
[USING index_type] 指定索引使用哪个类型，不指定默认使用的是B+树
tbl_name(index_col_name,…) 对哪张表的哪些字段建立索引，tbl_name为表名，index_col_name为字段名
在MySQL数据库中，如果字段为主键，就会默认有一个主键索引。

修改表结构创建-ALTER s ADD INDEX idx_s_phone ON table_s(phone);
创建表结构创建-
CREATE TABLE s(
id INT NOT NULL,
s_name VARCHAR(255) NOT NULL,
phone CHAR(11) NOT NULL;
INDEX idx_phone (phone)
);
查看索引-SHOW INDEX FROM table_name;
删除索引-DROP INDEX [index_name] ON table_name;

索引的使用
● 建立索引可以大大提高查询速度
● 滥用索引会降低更新表的速度
● 建立索引会占用磁盘空间，因为会生成索引文件

建立索引的原则
● 应该创建索引
○ 主键列
○ 外键列
○ 频繁查询的数据列
○ 频繁用在WHERE子句中的列
● 不应该创建索引
○ 表修改操作远比查询操作频繁时
○ 数据很少的列，如一个type字段只有0和1两种值
○ 很少被查询用到的列

索引的分类
● 单列索引
○ 只包含单个列
● 组合索引
○ 也可以使用创建索引的三种方式创建组合索引
○ 一个索引包含多个列
■ 遵循组合索引“最左前缀”规则，即创建组合索引（c1，c2，c3），实际包含三个索引（c1），（c1,c2），（c1,c2,c3）
■ 查询时，必须要包含最左字段，否则不会应用索引
CREATE INDEX idx_c1_c2_c3 ON 表名(c1,c2,c3);

系统函数

聚合函数

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

比较运算符与函数

● [不]在范围之内：[NOT] BETWEEN … AND …
● [不]在列出值范围内：[NOT] IN()
● [不]为空：IS [NOT] NULL
● 返回最小值：LEAST(x,y,…)
● 返回最大值：GREATEST(x,y,…)

日期函数

● 当前日期和时间值：NOW()
● 系统时间：SYSDATE()
● 当前日期：CURDATE()
● 当前时间：CURTIME()
● 日期变化 -DATE_ADD(NOW(),INTERVAL 1 DAY);
○ DATE_ADD(date,INTERVAL expr unit)
○ ADDDATE(date,INTERVAL expr unit)
○ DATE_SUB(date,INTERVAL expr unit)
○ SUBDATE(date,INTERVAL expr unit)
● 日期差值：DATEDIFF(expr1,expr2)
● 日期格式化：DATE_FORMAT(date,format)
● 等待：SLEEP(S)
-SELECT SYSDATE(), SLEEP(2), SYSDATE();

信息函数

● 当前数据库：DATEBASE()
● 连接ID：CONNECTION_ID()
● 最后插入记录的ID号：LAST_INSERT_ID()
● 当前用户：USER()
● 版本信息：VERSION()

加密函数

● 信息摘要算法：MD5(str)
○ 若参数为NULL，则会返回NULL
● 密码算法：PASSWORD(str)
○ 原文密码str，计算并返回密码字符串，用于用户授权表的Password列中的加密，用于MySQL密码存储

算术操作符与数学函数

● 返回X的绝对值：ABS(X)
● 进一取整，返回不小于X的最小整数值：CEIL(X)
● 整数除法，类似于FLOOR()：num1 DIV num2
SELECT 10 DIV 5;
舍一取整，返回不大于X的最大整数值：FLOOR()
取余数（取模）：MOD(num1,num2)
幂运算，返回X的Y次方的结果值：POW(X,Y)，POWER(X,Y)
四舍五入：ROUND(X)，ROUND(X,D)
– 1
ROUND(1.2);
– 1.2
ROUND(1.212);
数字截取：TRUNCATE(X,D)
– 1.2
TRUNCATE(1.267,1);
返回一个随机浮点值v，范围在0到1之间（即，其范围为0<=v<=1.0）。若已指定一个整数参数N，则它被用作种子值，用来产生重复序列值：RAND()

字符函数

● 字符连接：CONCAT(str1,str2,…)
○ 如果拼接的字符中有NULL，则返回NULL -CONCAT(‘HELLO’,NULL,‘MYSQL’)
● 指定的分隔符进行字符连接：CONCAT_WS(separator,str1,str2,…)
○ separator为分隔符
○ 拼接的字符中间如果有NULL，则忽略NULL继续进行拼接
● 转换成小写字母：LOWER(str)
● 转换成大写字母：UPPER(str)
● 获取左侧字符：LEFT(str,len)
● 获取右侧字符：RIGHT(str,len)
● 获取指定位置的字符：SUBSTR()
○ SUBSTR(str,pos)：从第pos个字符处开始截取（包含pos的位置）
○ SUBSTR(str FROM pos)：从第pos个字符处开始截取（包含pos的位置）
○ SUBSTR(str,pos,len)：从第pos个字符处开始截取len个长度（包含pos的位置）
○ SUBSTR(str FROM pos FOR len) ：从第pos个字符处开始截取len个长度（包含pos的位置）
■ pos可以为负数
● 数字格式化：FORMAT(X,D)
○ X表示整数位每3位进行分割，分隔符为英文逗号（,）
○ D表示小数位的位数，小数位大于D则四舍五入为D位小数，小数位小于D则使用0补充至D位小数

控制流程函数
IF分支表达式：IF(expr1,expr2,expr3)
expr1是TRUE(expr1 <> 0 and expr1 <> NULL)，则IF()的返回值为expr2；否则返回值为expr3
SELECT regName,IF(leaveMount>=40000,‘是土豪’,‘不是’) FROM member;
CASE分支表达式：CASE … WHEN … THEN …
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result …] [ELSE result] END
– 当1等于1返回‘one’，等于2返回‘two’，否则返回‘more’
SELECT CASE 1 WHEN 1 THEN ‘one’ WHEN 2 THEN ‘two’ ELSE ‘more’ END;
– 实战
SELECT regName,type
CASE type WHEN 1 THEN ‘VIP用户’ WHEN 2 THEN ‘普通用户’ ELSE ‘其他用户’ END
FROM member;

CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END
– 当第一个条件（1>0）为真时，则返回‘true’，否则返回‘false’
SELECT CASE WHEN 1>0 THEN ‘true’ ELSE ‘false’ END;
– 实战
SELECT regName,leaveMount,
CASE WHEN leaveMount >=40000 THEN ‘土豪’
WHEN leaveMount < 40000 AND leaveMount >= 20000 THEN ‘小康’
ELSE ‘贫民’ END
FROM member;
● 判空表达式：IFNULL(expr1,expr2)
○ expr1不为NULL，则IFNULL()的返回值为expr1；否则返回值为expr2
SELECT title,amount,IFNULL(fullTime,‘未满标’) FROM loan;