mysql基础8-运算符、函数、索引

1.mysql在运算符中使用

mysql> SELECT 3/0;
+------+
| 3/0  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> SELECT 3%8;
+------+
| 3%8  |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> SELECT 3 MOD 8;
+---------+
| 3 MOD 8 |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

# 对NULL做什么处理均为NULL
mysql> SELECT 1+NULL;
+--------+
| 1+NULL |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

-- 与NULL比较，用<=>
SELECT id,username,age,sex,sex<=>NULL FROM cms_user;
-- >  >=
SELECT id,username,score,score>=70 FROM student;
-- IS NULL   IS NOT NULL    判断是否等于NULL
SELECT id,username,age,age IS NOT NULL FROM cms_user;
-- BETWEEN AND    NOT BETWEEN  判断是否在范围内
SELECT id,username,age,age BETWEEN 10 AND 30 FROM cms_user;
-- IN  NOT IN    判断是否在某一个固定范围内
SELECT id,username,age,age IN(21,31,41,51) FROM cms_user;
-- LIKE     NOT LIKE  判断是否匹配
SELECT id,username,username LIKE '____' FROM cms_user;
-- REGEXP   判断是否正则匹配
SELECT id,username,username REGEXP '^t' FROM cms_user;

-- &&  ||  !  XOR
SELECT 2&&2,2&&0,2&&NULL,1||1,1||0,1||NULL,0||NULL;
-- NULL取反仍然是NULL
SELECT !1,!0,!NULL;

2.数学函数库

1）数学函数

2）字符串函数

3）日期时间函数

4）条件判断函数

SELECT id,username,score, CASE WHEN score>60 THEN '不错', WHEN score=60 THEN '刚及格'
ELSE '没及格' END FROM student;

5）系统信息函数

6）常用函数

INET_NTOA
GET_LOCK

3.索引的使用

-- 创建普通索引
CREATE TABLE test4(
id TINYINT UNSIGNED,
username VARCHAR(20),
INDEX in_id(id),
KEY in_username(username)
);

-- 创建唯一索引
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)
);

-- 创建全文索引
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)
);

-- 创建单列索引
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)
);

-- 唯一性的联合索引
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)
);

-- 创建空间索引
CREATE TABLE test10(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test GEOMETRY NOT NULL,
SPATIAL INDEX spa_test(test) 
);ENGINE=MyISAM;

-- 删除索引
DROP INDEX in_id ON test4;
DROP INDEX in_username ON test4;


-- 在已经存在的表上创建索引
-- 创建普通索引
CREATE INDEX in_id ON test4(id);
-- 创建唯一索引
CREATE UNIQUE INDEX uni_username ON test5(uername);
ALTER TABLE test5 ADD UNIQUE INDEX uni_card(card);
ALTER TABLE test4 ADD INDEX in_username(username);

-- 创建空间索引
DROP INDEX spa_test ON test10;
CREATE SPATIAL INDEX spa_test ON test10(test);