day5：函数与索引

1 索引

经常进行排序、分类的字段添加索引。
创建表时添加索引

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
…,
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名称](字段名称[(长度)] [ASC|DESC])
);

唯一性索引可以在字段时，在末尾通过关键字添加，其他索引不行

（1）普通索引

创建普通索引

CREATE TABLE test4(
id TINYINT UNSIGNED,
username VARCHAR(20),
INDEX in_id(id),
KEY in_username(username)
);

查看表格的详细定义

可以看到两个普通索引

再查看表的结构，普通索引会显示MUL

（2）唯一性索引

CREATE TABLE test5(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) NOT NULL,
UNIQUE KEY uni_card(card)
);

创建了两个唯一性索引。

另外，主键也可以认为是特殊的唯一性索引，只有唯一性索引可以在定义字段的时候，在末尾说明，其他索引都需要另起一行说明哪个字段为索引。

（3）全文索引

创建全文索引

CREATE TABLE test6(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
userDesc VARCHAR(20) NOT NULL,
FULLTEXT INDEX full_userDesc(userDesc)
);

（4）添加空间索引（用的少，不用掌握）

-- 创建空间索引，字段必须是空间数据类型
-- 存储引擎一定要为MyISAM
CREATE TABLE test10(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test GEOMETRY NOT NULL,
SPATIAL INDEX spa_test(test)
)ENGINE=MyISAM;

（5）单列索引与多列索引

单列索引就是在一个字段上添加索引，多列索引就是联合索引

-- 创建单列索引
CREATE TABLE test7(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
INDEX in_test1(test1)
);

-- 创建多列索引
CREATE TABLE test8(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
INDEX mul_t1_t2_t3(test1,test2,test3)
);
DESC test8;
ALTER TABLE test8 DROP INDEX mul_t1_t2_t3;
ALTER TABLE test8 ADD INDEX mul_ti_t2_t3(test1,test2,test3);

-- 创建多列唯一索引
CREATE TABLE test9(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
UNIQUE KEY mul_t1_t2_t3(test1,test2,test3)
);

创建之后可以查看表结构

（6）删除索引

DROP INDEX 索引名称 ON tbl_name

也可以

ALTER TABLE tbl_name DROP INDEX 索引名称;

（7）为已经存在的表添加索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON 表名 {字段名称[(长度)] [ASC|DESC]}

或

-- 没有指定索引名字时，系统会默认字段名就是索引名字
ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX [索引名称](字段名称[(长度)] [ASC|DESC]);

2 运算符

（1）算术运算符

MySQL也支持数据类型转换


取余

NULL参与的运算，结果仍为NULL

（2）比较运算符

比较运算符只能为真或假


结果为1代表真，0代表假

是否为空，不能用=，要用<=>

正确的应该是0或1才对


也可以单独使用

查询用户名长度是4的用户

（3） 逻辑运算符

XOR表异或

（4）运算符的优先级

2 MySQL的函数

之前像得到MySQL的版本号，就是一个系统函数

（1）数学函数库

TRUNCATE是截断，不是四舍五入

（2）字符串函数库

中文算一个字符，但在utf8中长度为3


字符分割

如果分隔符为NULL，整个结果为NULL

连接的内容有NULL，结果不会NULL

填充到长度为5的位置



替换的时候区分大小写，比较的时候不区分

返回第二个字符串

（3）时间日期函数

（4）条件判断函数

（5）系统信息函数

SELECT LAST_INSERT_ID()返回的一定是最近插入的那张表的自增长值

（6）加密函数

MD5

返回一个32位长度的字符串

PASSWORD(str)
给用户信息加密用PASSWORD(str)

（7）其他常用函数