MySQL数据库基础第四天

MySQL中的运算符

MySQL中常用的运算符：
算数运算符

符号	表达式	作用
+	X1+X2	加法
-	X1-X2	减法
*	X1*X2	乘法
/	X1/X2	除法
DIV	X1 DIV X2	除法
%	X1%X2	取余
MOD	MOD(X1,X2)	取余

mysql> SELECT 1+1,1-1,2*4,3/8;
+-----+-----+-----+--------+
| 1+1 | 1-1 | 2*4 | 3/8    |
+-----+-----+-----+--------+
|   2 |   0 |   8 | 0.3750 |
+-----+-----+-----+--------+
1 row in set (0.06 sec)

也支持数据类型转换

mysql> SELECT 1 +'3MAZI';
+------------+
| 1 +'3MAZI' |
+------------+
|          4 |
+------------+
1 row in set, 1 warning (0.05 sec)

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

mysql> SELECT 1+null;
+--------+
| 1+null |
+--------+
|   NULL |
+--------+
1 row in set (0.05 sec)

比较运算符

符号	形式	作用
=	X1=X2	判断是否相等
<>或!=	X1<>X2 或X1 != X2	判断是否不相等
<=>	X1<=>X2	判断是否不相等，可以判断是否等于NULL
>、>=	X1>X2、X1>=X2	判断是否大于等于
<、<=	X1<X2、X1<=X2	判断是否小于等于
IS NULL或IS NOT NULL	X1 IS NULL X1 IS NOT NULL	判断是否等于NULL
BETWEEN AND或NOT BETWEEN	X1 BETWEEN m AND n	判断是否在范围内
IN 或NOT IN	X1 IN (值1,…)	判断是否在某个固定范围内
LIKE 或 NOT LIKE	X1 LIKE 表达式	判断是否匹配
REGEXP	X1 REGEXP 正则	判断是否正则匹配

mysql> SELECT 1=1,1='1',1=2;
+-----+-------+-----+
| 1=1 | 1='1' | 1=2 |
+-----+-------+-----+
|   1 |     1 |   0 |
+-----+-------+-----+
1 row in set (0.00 sec)

SELECT id,username,age,age BETWEEN 10 AND 30 FROM cms_user;

mysql> SELECT 1 IN (1,2,3);
+--------------+
| 1 IN (1,2,3) |
+--------------+
|            1 |
+--------------+
1 row in set (0.05 sec)

mysql> SELECT 'S' LIKE '_';
+--------------+
| 'S' LIKE '_' |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

逻辑运算符

符号	形式	作用
&& 或者 AND	与	并且
|| 或者 OR	或	或者
! 或者 NOT	非	取反
XOR	异或	不同为真

与null运算都是null

mysql> SELECT 2&&2,2&&0,2&&NULL,1||1,1||0,1||NULL,0||NULL;
+------+------+---------+------+------+---------+---------+
| 2&&2 | 2&&0 | 2&&NULL | 1||1 | 1||0 | 1||NULL | 0||NULL |
+------+------+---------+------+------+---------+---------+
|    1 |    0 |    NULL |    1 |    1 |       1 |    NULL |
+------+------+---------+------+------+---------+---------+
1 row in set, 7 warnings (0.05 sec)

mysql> SELECT !1,!0,!NULL;
+----+----+-------+
| !1 | !0 | !NULL |
+----+----+-------+
|  0 |  1 |  NULL |
+----+----+-------+
1 row in set, 3 warnings (0.03 sec)

运算符的优先级：

优先级	运算符	优先级	运算符
1	!	8	|
2	~	9	=,<=>,<,<=,>,>=,!=,<>,IN,IN NULL,LIKE,REGEXP
3	^	10	BETWEEN AND,CASE,WHEN,THEN,ELSE
4	*,/,DIV,%,MOD	11	NOT
5	+,-	12	&&,AND
6	>>,<<	13	||,OR,XOR
7	&	14	;=

括号可以改变运算符优先级

数据函数库

数据函数：

名称	描述
CEIL()	进一取整
FLOOR()	舍一取整
MOD()	取余数（取模）
POWER()	幂运算
ROUND()	四舍五入，可以加如第二个参数，表式保留小数点后几位
TRUNCATE()	数字截取，可以加如第二个参数，表示保留小数点后几位
ABS()	取绝对值
PI()	圆周率
RAND()和RAND(X)	返回0~1之间的随机数，RAND(X)返回固定随机数
SIGN()	返回X的符号，X为负数：-1，0返回0，整数：1
EXP()	计算e的几次方

SELECT * FROM cms.cms_user ORDER BY RAND();

字符串函数库

函数名称	描述
CHAR_LENGTH(S)	返回字符串的字符数
LENGTH	返回字符串的长度
CONCAT(S1,S2…)	将字符串合并为一个字符串
CONCAT_WS(X,S1,S2…)	以指定分隔符连接字符串
UPPER(S)/UCASE(S)	将字符串转换为大写
LOWER(S)/LCASE(S)	将字符串转换为小写
LEFT(S,N)/RIGHT(S,N)	返回字符串的前/后n个字符
LPAD(S1,LEN,S2)/RPAD(S1,LEN,S2)	将字符串S1用S2填充给到指定的LEN
LTRIM(S)/RTRIM(S)/TRIM(S)	去掉字符串中的空格
TRIM(S1 FROM S)	去掉字符串s中开始处和结尾处的字符串s1
REPEAT(S,N)	重复字符串指定次数
SPACE(N)	返回N个空格
REPLACE(S,S1,S2)	将字符串s中搜索s1，替换成s2
STRCMP(S1,S2)	比较字符串，>=<分别返回1，0，-1
SUBSTRING(S,N,LEN)	截取字符串
REVERSE(S)	反转字符串
ELT(N,S1,S2…)	返回指定位置的字符串

mysql> SELECT CHAR_LENGTH('MAIZI'),LENGTH('MAIZI');
+----------------------+-----------------+
| CHAR_LENGTH('MAIZI') | LENGTH('MAIZI') |
+----------------------+-----------------+
|                    5 |               5 |
+----------------------+-----------------+
1 row in set (0.05 sec)

mysql> SELECT CHAR_LENGTH('啊'),LENGTH('啊');
+--------------------+---------------+
| CHAR_LENGTH('啊')  | LENGTH('啊')  |
+--------------------+---------------+
|                  1 |             3 |
+--------------------+---------------+
1 row in set (0.00 sec)

以null来连接就是null

mysql> SELECT CONCAT('A','B',NULL);
+----------------------+
| CONCAT('A','B',NULL) |
+----------------------+
| NULL                 |
+----------------------+
1 row in set (0.05 sec)

mysql> SELECT CONCAT_WS(NULL,'A','B');
+-------------------------+
| CONCAT_WS(NULL,'A','B') |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set (0.00 sec)

NULL也相当于一个空字符串

mysql> SELECT CONCAT_WS('-','A','B',NULL);
+-----------------------------+
| CONCAT_WS('-','A','B',NULL) |
+-----------------------------+
| A-B                         |
+-----------------------------+
1 row in set (0.00 sec)

REPALCE如果没找到的话，会返回原字符串

mysql> SELECT REPLACE('ABCBCA','A','-');
+---------------------------+
| REPLACE('ABCBCA','A','-') |
+---------------------------+
| -BCBC-                    |
+---------------------------+
1 row in set (0.05 sec)

mysql> SELECT REPLACE('ABCBCA','a','-');
+---------------------------+
| REPLACE('ABCBCA','a','-') |
+---------------------------+
| ABCBCA                    |
+---------------------------+
1 row in set (0.00 sec)

STRCMP比较的是比较ANSCII码
字符串的下标是从1开始的

--从2的位置截取2个字符
mysql> SELECT SUBSTRING('ABCDEF',2,2);
+-------------------------+
| SUBSTRING('ABCDEF',2,2) |
+-------------------------+
| BC                      |
+-------------------------+
1 row in set (0.05 sec)

返回指定位置的字符或者字符串

mysql> SELECT ELT(2,'A','B','C');
+--------------------+
| ELT(2,'A','B','C') |
+--------------------+
| B                  |
+--------------------+
1 row in set (0.05 sec)

日期和时间函数

名称	描述
CURDATE(),CURRENT_DATE()	返回当前日期
CURTIME,CURRENT_TIME()	但会当前时间
NOW()	返回当前日期和时间
MONTH(D)	返回日期中月份的值
MONTHNAME(D)	返回日期中月份名称，返回January
DAYNAME(D)	返回日期是几，Monday
DAYOFWEEK(D)	返回一周内的第几天，1代表周日
WEEKDAY(D)	返回日期是星期几，0代表星期一
WEEK(D)	一年中的第多少个星期
YEAR(D)	返回年分值
HOUR(D)	返回小时值
MINUTE(D)	返回分钟值
SECOND(D)	返回秒数
DATEDIFF(D1,D2)	计算两个日期之间相隔的天数

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2020-02-07 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 18:45:01  |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
|            2 |
+--------------+
1 row in set (0.05 sec)

mysql> SELECT MONTHNAME(NOW());
+------------------+
| MONTHNAME(NOW()) |
+------------------+
| February         |
+------------------+
1 row in set (0.05 sec)

mysql> SELECT DAYNAME(NOW());
+----------------+
| DAYNAME(NOW()) |
+----------------+
| Friday         |
+----------------+
1 row in set (0.05 sec)

mysql> SELECT DAYOFWEEK(NOW());
+------------------+
| DAYOFWEEK(NOW()) |
+------------------+
|                6 |
+------------------+
1 row in set (0.05 sec)

mysql> SELECT WEEKDAY(NOW());
+----------------+
| WEEKDAY(NOW()) |
+----------------+
|              4 |
+----------------+
1 row in set (0.05 sec)

条件判断函数和系统函数

判断函数如下：

名称	描述
IF(EXPR,V1,V2)	如果表达式EXPR成立，返回结果V1，否则V2
IFNULL(V1,V2)	如果V1的不为空，就显示V1的值；否则V2
CASE WHEN exp1 THEN v1[WHEN exp2 THEN v2][ELSE vn] END	CASE表示函数开始，END表示函数结束。如果表达式exp1成立时，返回v1。如果表达还是exp2成立时，返回v2的值。依此类推，最后遇到ELSE时，返回vn的值

系统函数：

名称	描述
VERSION()	返回数据库版本号
CONNECTION_ID()	返回服务器连接数
DATABASE(),SCHEMA()	返回当前数据库名
USER(),SYSTEM_USER()	返回当前用户
CURRENT_USER(),CURRENT_USER	返回当前用户,第二种时常量形式
CHARSET(STR)	返回字符串STR的字符集
COLLATION(STR)	返回字符串STR的校验字符集
LAST_INSERT_ID()	返回最近生成的AUTO_INCREMENT

其他函数

加密函数：

名称	描述
MD5(str)	信息摘要算法，返回32长度字符串
PASSWORD(str)	密码算法，最新版已经移除这个函数
ENCODE(str,pwd_str)	加密结果时以二进制数，必须使用BLOB类型字段保存
DECODE(crypt_STR,pwd_str)	对通过ENCODE加密之后的内容解密

mysql> SELECT MD5('ADMIN');
+----------------------------------+
| MD5('ADMIN')                     |
+----------------------------------+
| 73acd9a5972130b75066c82595a1fae3 |
+----------------------------------+
1 row in set (0.05 sec)

mysql> SELECT LENGTH(MD5('ADMIN'));
+----------------------+
| LENGTH(MD5('ADMIN')) |
+----------------------+
|                   32 |
+----------------------+
1 row in set (0.00 sec)

MySQL的密码都会通过PASSWORD加密。

名称	描述
FORMAT(X,N)	将数字x进行格式话，将x保留到小数点后n位
ASCII(S)	返回字符串s的第一个字符的ASCII码
BIN(X)	返回x的二进制编码
HEX(X)	返回x的十六进制编码
OCY(X)	返回x的八进制编码
CONV(X,F1,F2)	将x从f1进制变成f2进制
INET_AION(IP)	将IP地址转换位数字
INET_NOTA(N)	将数字转换成IP地址
GET_LOCT(NAME,TIME)	定义锁
RELEASE_LOCK(NAME）	解锁

mysql> SELECT FORMAT(3.14567,2);
+-------------------+
| FORMAT(3.14567,2) |
+-------------------+
| 3.15              |
+-------------------+
1 row in set (0.05 sec)

mysql> SELECT ASCII('ABC');
+--------------+
| ASCII('ABC') |
+--------------+
|           65 |
+--------------+
1 row in set (0.05 sec)

mysql> SELECT CONV(5,10,2);
+--------------+
| CONV(5,10,2) |
+--------------+
| 101          |
+--------------+
1 row in set (0.05 sec)

mysql> SELECT INET_ATON('127.0.0.1');
+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT INET_NTOA(2130706433);
+-----------------------+
| INET_NTOA(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+
1 row in set (0.00 sec)

索引的使用

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

索引的分类：

• 普通索引：相当于建立一个书签
• 唯一索引：primary key和unique key都是，唯一索引不允许重复 使用UNIQUE
• 全文索引：只能创建在字符型字段上，内容是英文的才能用到，使用FULLTEXT
• 单列索引：创建在一个字段上
• 多列索引：多个字段上创建索引
• 空间索引：空间索引利用SPATAL

创建索引

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

什么都不加，只有KEY或者INDEX就是普通索引

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

id、username 、card都是索引，主键索引也是特殊的唯一索引。

CREATE TABLE test5(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
--UNIQUE后面可以省略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)
);

可以通过以下语句查看表内容。

SHOW CREATE TABLE tbl_name;

创建单列索引：

CREATE TABLE test7(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
INDEX in_test1(test1)
);

创建多列索引：

CREATE TABLE test7(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
INDEX in_test1_test2(test1,test2)
);

多列索引只能验证第一个字段才能查看是否多列索引。
创建空间索引,空间索引必须是MyISAM：

CREATE TABLE test10(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test GEOMETRY NOT NULL,
SPATIAL INDEX spa_test(test)
)ENGINE=MyISAM;

在已经存在表上创建索引

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

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

创建一个普通索引：

--第一种方法
CREATE INDEX in_id ON test4(id);
--第二种方法
ALTER TABLE test4 ADD INDEX in_username(username);

其他索引于此类似。

可以通过下列语句删除索引：

DROP INDEX 索引名称 ON tbl_name;
ALTER TABLE tbl_name DROP INDEX 索引名称

管理工具简单介绍：

phpMyAdmin是一个用PHP编写的软件工具，可以通过web方式控制和操作MySQL数据库
SQLyog是业界著名的Webyog公司出品的一款简洁高效、功能强大的图形化MySQL数据库管理工具。使用SQLyog可以快速直观地让您从世界地任何角落通过网络来远程维护数据库。
其特点是基于C++和MySQLAPI编程、方便快捷地数据库同步与数据库结构同步工具、易用地数据库、数据表备份与还原功能、支持导入与导出XML、HTML、CSV等多种格式地数据、直接运行批量SQL脚本文件，速度极快、新版本支持数据迁移。