MySQL中的运算符
1、算数运算符
MySQL 支持的算术运算符包括加、减、乘、除和模运算。## 算数运算符
mysql> SELECT 1+1;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in set (0.00 sec)
mysql> SELECT 1+1,1-1,2*4,3/8,4 DIV 2,3%8,3 MOD 8;
+-----+-----+-----+--------+---------+------+---------+
| 1+1 | 1-1 | 2*4 | 3/8 | 4 DIV 2 | 3%8 | 3 MOD 8 |
+-----+-----+-----+--------+---------+------+---------+
| 2 | 0 | 8 | 0.3750 | 2 | 3 | 3 |
+-----+-----+-----+--------+---------+------+---------+
1 row in set (0.00 sec)
NULL参与的运算结果均为NULL
mysql> SELECT 1+NULL,NULL+NULL;
+--------+-----------+
| 1+NULL | NULL+NULL |
+--------+-----------+
| NULL | NULL |
+--------+-----------+
1 row in set (0.03 sec)
2、比较运算符
当使用 select 语句进行查询时, MySQL 允许用户对表达式的左边操作数和右边操作数进行比较,比较结果为真,则返回 1,为假则返回 0,比较结果不确定则返回 null。比较运算符可以用于比较数字、字符串和表达式。数字作为浮点数比较,而字符串以不区分大小写的方式进行比较。
mysql> SELECT id,username,age,sex,sex<=>NULL,age>40,id BETWEEN 3 AND 7,age IN(21,31,41,51) FROM cms_user;
+----+--------------+------+------+------------+--------+--------------------+---------------------+
| id | username | age | sex | sex<=>NULL | age>40 | id BETWEEN 3 AND 7 | age IN(21,31,41,51) |
+----+--------------+------+------+------------+--------+--------------------+---------------------+
| 1 | zhangsan | 11 | 男 | 0 | 0 | 0 | 0 |
| 2 | zhangsanfeng | 21 | 女 | 0 | 0 | 0 | 1 |
| 3 | zhangziyi | 33 | 男 | 0 | 0 | 1 | 0 |
| 4 | long | 44 | 女 | 0 | 1 | 1 | 0 |
| 5 | ring | 25 | 男 | 0 | 0 | 1 | 0 |
| 6 | queen | 77 | 女 | 0 | 1 | 1 | 0 |
| 7 | king | 56 | 男 | 0 | 1 | 1 | 0 |
| 8 | blek | 88 | 女 | 0 | 1 | 0 | 0 |
| 9 | rose | 12 | 男 | 0 | 0 | 0 | 0 |
| 10 | lily | 32 | 女 | 0 | 0 | 0 | 0 |
| 11 | john | 65 | 保密 | 0 | 1 | 0 | 0 |
| 12 | TEST | 18 | NULL | 1 | 0 | 0 | 0 |
+----+--------------+------+------+------------+--------+--------------------+---------------------+
12 rows in set (0.15 sec)
3、逻辑运算符
逻辑运算符又称为布尔运算符,用来确认表达式的真和假。
mysql> SELECT 2&&2,2&&0,2&&NULL,1||1,1||0,1||NULL,0||NULL,!1,!0,!NULL,1 XOR 0,1 XOR 1,0 XOR 0;
+------+------+---------+------+------+---------+---------+----+----+-------+---------+---------+---------+
| 2&&2 | 2&&0 | 2&&NULL | 1||1 | 1||0 | 1||NULL | 0||NULL | !1 | !0 | !NULL | 1 XOR 0 | 1 XOR 1 | 0 XOR 0 |
+------+------+---------+------+------+---------+---------+----+----+-------+---------+---------+---------+
| 1 | 0 | NULL | 1 | 1 | 1 | NULL | 0 | 1 | NULL | 1 | 0 | 0 |
+------+------+---------+------+------+---------+---------+----+----+-------+---------+---------+---------+
1 row in set (0.00 sec)
4、运算符的优先级
优先级顺序
可用()改变优先级。
MySQL中的函数
数学函数
mysql> SELECT CEIL(1.2),FLOOR(3.14),MOD(3,8),POW(2,3),ROUND(3.14567,2),TRUNCATE(3.14567,2),ABS(-12),PI(),RAND();
+-----------+-------------+----------+----------+------------------+---------------------+----------+----------+---------------------+
| CEIL(1.2) | FLOOR(3.14) | MOD(3,8) | POW(2,3) | ROUND(3.14567,2) | TRUNCATE(3.14567,2) | ABS(-12) | PI() | RAND() |
+-----------+-------------+----------+----------+------------------+---------------------+----------+----------+---------------------+
| 2 | 3 | 3 | 8 | 3.15 | 3.14 | 12 | 3.141593 | 0.11569049220130825 |
+-----------+-------------+----------+----------+------------------+---------------------+----------+----------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT SIGN(12),SIGN(0),SIGN(-12),EXP(3);
+----------+---------+-----------+--------------------+
| SIGN(12) | SIGN(0) | SIGN(-12) | EXP(3) |
+----------+---------+-----------+--------------------+
| 1 | 0 | -1 | 20.085536923187668 |
+----------+---------+-----------+--------------------+
1 row in set (0.28 sec)
字符串函数
mysql> SELECT CHAR_LENGTH('abcde'),LENGTH('abcde'),CHAR_LENGTH('啊'),LENGTH('啊');
+----------------------+-----------------+-------------------+--------------+
| CHAR_LENGTH('abcde') | LENGTH('abcde') | CHAR_LENGTH('啊') | LENGTH('啊') |
+----------------------+-----------------+-------------------+--------------+
| 5 | 5 | 2 | 2 |
+----------------------+-----------------+-------------------+--------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT('HELLO','WORLD'),CONCAT('a','b',null);
+-------------------------+----------------------+
| CONCAT('HELLO','WORLD') | CONCAT('a','b',null) |
+-------------------------+----------------------+
| HELLOWORLD | NULL |
+-------------------------+----------------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT_WS('^_^','a','b','c'),CONCAT_WS(NULL,'a','b','c'),CONCAT_WS('^_^','a','b','c',NULL);
+------------------------------+-----------------------------+-----------------------------------+
| CONCAT_WS('^_^','a','b','c') | CONCAT_WS(NULL,'a','b','c') | CONCAT_WS('^_^','a','b','c',NULL) |
+------------------------------+-----------------------------+-----------------------------------+
| a^_^b^_^c | NULL | a^_^b^_^c |
+------------------------------+-----------------------------+-----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT UPPER('this is a test'),UCASE('this is a test'),LOWER('HELLO WORLD'),LCASE('HELLO WORLD');
+-------------------------+-------------------------+----------------------+----------------------+
| UPPER('this is a test') | UCASE('this is a test') | LOWER('HELLO WORLD') | LCASE('HELLO WORLD') |
+-------------------------+-------------------------+----------------------+----------------------+
| THIS IS A TEST | THIS IS A TEST | hello world | hello world |
+-------------------------+-------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
mysql> SELECT LEFT('ABCDEF',2),RIGHT('ABCDEF',2);
+------------------+-------------------+
| LEFT('ABCDEF',2) | RIGHT('ABCDEF',2) |
+------------------+-------------------+
| AB | EF |
+------------------+-------------------+
1 row in set (0.00 sec)
mysql> SELECT LPAD('A',5,'?'),RPAD('A',5,'!');
+-----------------+-----------------+
| LPAD('A',5,'?') | RPAD('A',5,'!') |
+-----------------+-----------------+
| ????A | A!!!! |
+-----------------+-----------------+
1 row in set (0.29 sec)
mysql> SELECT TRIM('A' FROM 'ABCBCA');
+-------------------------+
| TRIM('A' FROM 'ABCBCA') |
+-------------------------+
| BCBC |
+-------------------------+
1 row in set (0.28 sec)
mysql> SELECT REPEAT('H',5);
+---------------+
| REPEAT('H',5) |
+---------------+
| HHHHH |
+---------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT('_',SPACE(5),'_');
+--------------------------+
| CONCAT('_',SPACE(5),'_') |
+--------------------------+
| _ _ |
+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT REPLACE('ABCBCA','A','_');
+---------------------------+
| REPLACE('ABCBCA','A','_') |
+---------------------------+
| _BCBC_ |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT STRCMP('A','A'),STRCMP('A','a'),STRCMP('B','A'),STRCMP('A','B');
+-----------------+-----------------+-----------------+-----------------+
| STRCMP('A','A') | STRCMP('A','a') | STRCMP('B','A') | STRCMP('A','B') |
+-----------------+-----------------+-----------------+-----------------+
| 0 | 0 | 1 | -1 |
+-----------------+-----------------+-----------------+-----------------+
1 row in set (0.04 sec)
mysql> SELECT SUBSTRING('ABCDEF',2,2);
+-------------------------+
| SUBSTRING('ABCDEF',2,2) |
+-------------------------+
| BC |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT REVERSE('ABC');
+----------------+
| REVERSE('ABC') |
+----------------+
| CBA |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT ELT(2,'A','B','C');
+--------------------+
| ELT(2,'A','B','C') |
+--------------------+
| B |
+--------------------+
1 row in set (0.29 sec)
日期时间函数
mysql> SELECT CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME(),NOW();
+------------+----------------+-----------+----------------+---------------------+
| CURDATE() | CURRENT_DATE() | CURTIME() | CURRENT_TIME() | NOW() |
+------------+----------------+-----------+----------------+---------------------+
| 2020-03-08 | 2020-03-08 | 17:22:10 | 17:22:10 | 2020-03-08 17:22:10 |
+------------+----------------+-----------+----------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT MONTH(NOW()),MONTHNAME(NOW()),DAYNAME(NOW()),DAYOFWEEK(NOW()),WEEKDAY(NOW()),WEEK(NOW());
+--------------+------------------+----------------+------------------+----------------+-------------+
| MONTH(NOW()) | MONTHNAME(NOW()) | DAYNAME(NOW()) | DAYOFWEEK(NOW()) | WEEKDAY(NOW()) | WEEK(NOW()) |
+--------------+------------------+----------------+------------------+----------------+-------------+
| 3 | March | Sunday | 1 | 6 | 10 |
+--------------+------------------+----------------+------------------+----------------+-------------+
1 row in set (0.10 sec)
mysql> SELECT YEAR(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()),DATEDIFF(CURRENT_DATE(),'1990-1-1');
+-------------+-------------+---------------+---------------+-------------------------------------+
| YEAR(NOW()) | HOUR(NOW()) | MINUTE(NOW()) | SECOND(NOW()) | DATEDIFF(CURRENT_DATE(),'1990-1-1') |
+-------------+-------------+---------------+---------------+-------------------------------------+
| 2020 | 17 | 27 | 4 | 11024 |
+-------------+-------------+---------------+---------------+-------------------------------------+
1 row in set (0.04 sec)
条件判断函数
mysql> SELECT id,username,score,IF(score>=60,'及格','不及格')FROM student;
+----+----------+-------+------------------------------------+
| id | username | score | IF(score>=60,'及格','不及格') |
+----+----------+-------+------------------------------------+
| 1 | king | 95 | 及格 |
| 2 | king1 | 35 | 不及格 |
| 3 | king2 | 45 | 不及格 |
| 4 | king3 | 55 | 不及格 |
| 5 | king4 | 65 | 及格 |
| 6 | king5 | 75 | 及格 |
| 7 | king6 | 80 | 及格 |
| 8 | king7 | 90 | 及格 |
| 9 | king8 | 25 | 不及格 |
+----+----------+-------+------------------------------------+
9 rows in set (0.03 sec)
mysql> SELECT id,username,score,
-> CASE WHEN score>60 THEN '不错'
-> WHEN score=60 THEN '刚及格'
-> ELSE '没及格' END as performance
-> FROM student;
+----+----------+-------+-------------+
| id | username | score | performance |
+----+----------+-------+-------------+
| 1 | Tom | 95 | 不错 |
| 2 | king | 35 | 没及格 |
| 3 | quen | 45 | 没及格 |
| 4 | zhangsan | 55 | 没及格 |
| 5 | lisi | 65 | 不错 |
| 6 | wangwu | 75 | 不错 |
| 7 | xiaoming | 80 | 不错 |
| 8 | xiaohong | 90 | 不错 |
| 9 | xiaogang | 25 | 没及格 |
+----+----------+-------+-------------+
9 rows in set (0.00 sec)
系统信息函数
加密函数
mysql> SELECT MD5('ADMIN');
+----------------------------------+
| MD5('ADMIN') |
+----------------------------------+
| 73acd9a5972130b75066c82595a1fae3 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT PASSWORD('root'),PASSWORD('king');
+-------------------------------------------+-------------------------------------------+
| PASSWORD('root') | PASSWORD('king') |
+-------------------------------------------+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | *0C6F8A2CE8ABFD18609CCE4CDFAB3C15DAD20718 |
+-------------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)
其他常用函数
mysql> SELECT FORMAT(3.14567,2);
+-------------------+
| FORMAT(3.14567,2) |
+-------------------+
| 3.15 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT ASCII('abc');
+--------------+
| ASCII('abc') |
+--------------+
| 97 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT BIN(5),HEX(5),OCT(5);
+--------+--------+--------+
| BIN(5) | HEX(5) | OCT(5) |
+--------+--------+--------+
| 101 | 5 | 5 |
+--------+--------+--------+
1 row in set (0.00 sec)
mysql> SELECT CONV(5,10,2);
+--------------+
| CONV(5,10,2) |
+--------------+
| 101 |
+--------------+
1 row in set (0.00 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)
MySQL的索引
索引由数据库中一列或多列组合而成,其作用是提高对表中数据的查询速度。索引的优点是可以提高检索数据的速度,缺点是创建和维护索引需要耗费时间。索引可以提高查询速度,但会减慢写入速度。
索引的分类:
1、普通索引;
2、唯一索引;
3、全文索引;
4、单列索引;
5、多列索引;
6、空间索引。
创建索引
1、建表时创建索引
CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件]
…,
[UNIQUE I FULLTEXT I SPATIAL] INDEX I KEY [索引名称](字段名称[(长度)]
[ASC I DESC])
);
创建普通索引:
mysql> CREATE TABLE test4(
-> id TINYINT UNSIGNED,
-> username VARCHAR(20),
-> INDEX in_id(id),
-> KEY in_username(username)
-> );
Query OK, 0 rows affected (0.01 sec)
创建唯一索引:
mysql> 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)
-> );
Query OK, 0 rows affected (0.01 sec)
2、在已存在的表上创建索引
CREATE [UNIQUE I FULLTEXT I SPATIAL] INDEX 索引名称 ON 表名 {字段名称[(长度)] [ASC I DESC]}
mysql> CREATE INDEX in_id ON test4(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE tbl_name ADD [UNIQUE I FULLTEXT I SPATIAL] INDEX 索引名称(字段名称[(长度)] [ASCIDESC]);
mysql> ALTER TABLE test4 ADD INDEX in_username(username);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除索引
DROP INDEX 索引名称 ON tbl_name
mysql> DROP INDEX in_id ON test4;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP INDEX in_username ON test4;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0