MySQL基础_第4节_运算符、函数、索引、图形化管理工具

MySQL中的运算符

算数运算符

• MySQL接受数据类型转换

  SELECT 1 + '3MAIZI'
  

  • WARNING信息中显示，把3MAIZI转化为了3。
    
• 3/0，3 DIV 0 结果都是 NULL
• 对于 NULL 的所有操作都为 NULL

比较运算符

• 结果之能为真（1）或者假（0）
• <>、IS NOT NULL 不等于 NULL
• <=>、IS NULL 等于NULL。
• 判断表格中的值

  -- 判断表格中的用户名是否为king
  SELECT username, username != 'king' FROM studnet;
  

  

逻辑运算符

• &&、AND
  
• ||、OR
  • 1为真，所以1||NULL结果为真
  • 0为假，所以0||NULL结果为假
    
• !、NOT
  
• XOR，二者不同则为真
  

运算符的优先级

• 可以通过括号()改变优先级

MySQL中的函数

数学函数

• CEIL() 进一取整
  

• FLOOR() 舍一取整
  

• MOD(N,M) 返回N除以M余值

  • 如果 N < M 则返回N
    
    

• POWER() 幂运算
  

• ROUND() 四舍五入
  

• TRUNCATE() 数字截取
  

• ABS() 取绝对值

• PI() 圆周率
  

• RAND() 随机数 和RAND(X) 固定随机数
  

  • 随机排序
    
  • 固定随机数
    

• SIGN(X) 得到数字的符号：负数返回-1，0返回0，正数返回1。
  

• EXP(X) e的X次方。
  

字符串函数

• 返回字符串长度：CHAR_LENGTH(S)、LENGTH(S)
  
  • 中文长度两种方式计算不同
    
• CONCAT(S1, S2, …) 将字符串合并为一个字符串
  
  • 如果字符串中有 NULL ，则结果为 NULL
    
• CONCAT_WS(X, S1, S2)：以X来分割链接字符串
  

  • 如果分割符为 NULL ，则结果为 NULL
    

  • 如果字符串中有 NULL，则结果部位 NULL
    

• LEFT(S, N)，RIGHT(S, N) 返回前几个字符串
  
• LPAD(S1, LEN, S2)，RPAD(S1, LEN, S2) 将字符填充至几位长度
  

• TRIM

  • 去掉空格
    
  • 去掉开头和结尾的字符
    

• REPEAT(S, N)：重复字符串S，N次
  

• SPACE(N)：返回N个空格
  

• REPLACE(S, S1, S2) 在S中搜索S1，替换成S2。如果没找到的话，返回原来的字符串。

  • 搜索的时候是区分大小写的
    

• STRCMP(S1, S2) 比较字符串。

  • 如果S1、S2相等返回0，如果S1根据当前的排序顺序比S2小则返回-1，否则返回1。
  • 不区分大小写
    

• SUBSTRING(S, N, LEN)：在S字符串中，从N位置开始，截取LEN长度的字符串。

  • 字符串下标从1开始，包含起始字符。
    

• REVERSE(S)：反转字符串
  

• ELT(N, S1, S2, …)：返回指定位置N的字符串
  

日期时间函数

• CURDATE() \ CURRENT_DATE()
  

• CURTIME() \ CURRENT_TIME()
  

• NOW()
  

• MONTH(D)
  
  

• MONTHNAME(D)
  

• DAYNAME(D)：返回得到一周的周几。
  

• DAYOFWEEK(D)：返回得到一周的第几天（1~7）。1代表周日，7代表周六。
  

• WEEKDAY(D)：0代表星期一，5代表周六。
  

• WEEK(D)：返回得到一年内的第几周。
  

• DATEDIFF(D1, D2)：计算D1、D2两个日期中间相隔的天数。
  

条件判断函数

• IF(EXPR, V1, V2)：如果表达式EXPR成立，则返回结果V1，否则返回V2。
  
  

• IFNULL(V1, V2)：如果V1的不为NULL，则返回结果V1，否则返回V2。

  -- 如果年龄为NULL的话，则返回100。
  SELECT id, username, age, IFNULL(age, '100') FROM cms_user;
  

  

• CASE WHEN exp1
  THEN v1 [WHEN exp2 
  THEN v2] [ELSE vn]
  END
  

  • CASE表示函数开始，END表示函数结束。如果表达式exp1成立，则返回v1。如果表达式exp2成立，则返回v2。依此类推，最后遇到ELSE时，返回vn。

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

    

系统信息函数

• CHARSET(STR)
  

加密函数

其它常用函数

MySQL中的索引

什么是索引？

• 索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度。
• 优点：提高检索数据的速度
• 缺点：创建和维护索引需要耗费时间
• 提高查询速度，会减慢写入速度

索引的分类？

• 普通索引：相当于书签
• 唯一索引：字段的值不可以重复，例如UNIQUE KEY。主键也是一个特殊的唯一索引。
• 全文索引：只能创建在英文字符类的字段上。
• 单列索引：在一个字段上创建索引。
• 多列索引：在多个字段上创建索引。
• 空间索引：要求数据类型也是空间。

如何创建索引？

• 创建表的时候创建索引

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

• 在已经存在的表上创建索引

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

ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称(字段名称[(长度)] [ASC|DESC]);

如何删除索引？

• DROP INDEX 索引名称 ON tbl_name

索引相关测试

• 创建普通索引

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

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

-- 添加普通索引
CREATE INDEX in_id ON test4(id) COMMENT 'in_id为索引名称，如果不指定，则会自动设置';
ALTER TABLE test4 ADD INDEX in_username(username);

• 创建唯一索引：可以有多个唯一性索引，但是只可以有一个主键索引。

-- 创建表时，创建唯一索引
CREATE TABLE test5(
id TINYINT UNSIGNED AUTO_INCREMENT KEY COMMENT '主键，主键也是一个特殊的唯一索引',
username VARCHAR(20) NOT NULL UNIQUE COMMENT '唯一性约束',
card CHAR(18) NOT NULL,
UNIQUE KEY uni_card(card) COMMENT '建立唯一性索引'
);

-- 删除索引
ALTER TABLE test5 DROP INDEX uni_card;
DROP INDEX username ON test5;

-- 添加唯一索引
CREATE UNIQUE INDEX uni_username ON test5(username);
ALTER TABLE test5 ADD UNIQUE INDEX 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) COMMENT '创建全文索引'
);

-- 删除索引
DROP INDEX full_userDesc ON test6;

-- 添加全文索引
CREATE FULLTEXT INDEX full_userDesc ON test6(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) COMMENT '创建单列索引'
);

• 创建多列索引

-- 创建表时，创建多列索引
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)
) COMMENT '创建多列索引';

-- 删除索引
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)
);

• 创建空间索引：存储引擎必须为MyISAM

-- 创建表时，创建空间索引
CREATE TABLE test10(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test GEOMETRY NOT NULL,
SPATIAL INDEX spa_test(test) COMMENT '创建空间索引，禁止为空'
)ENGINE=MyISAM;

-- 删除索引
DROP INDEX spa_test ON test10;

-- 添加空间索引
CREATE SPATIAL INDEX spa_test ON test10(test);

图形化工具管理MySQL

PHPMyAdmin的安装及使用

• phpMyAdmin 是一个用PHP编写的软件工具，可以通过web方式控制和操作MySQL数据库
  

SQLyog的安装及使用

• SQLyog是业界著名的Webyog公司出品的一款简洁高效、功能强大的图形化MySQL数据库管理工具。
• 特点
  • 基于C++和MySQLAPI编程
  • 方便快捷的数据库同步与数据库结构同步工具
  • 易用的数据库、数据表备份与还原功能
  • 支持导入与导出XML、HTML、CSV等多种格式的数据
  • 直接运行批量SQL脚本文件，速度极快
  • 新版本更是增加了强大的数据迁