运算符
算术运算符
简单示例
SELECT 5+6 加法操作,12-6 减法操作,9/3 除法操作,8 DIV 4 除法操作,15%6 求模操作,13 MOD 5 求模操作;
示例，在表中
建立表
CREATE TABLE t_student(
id INT(11) PRIMARY KEY,
name VARCHAR(20),
age INT(4),
gender INT(8)
);
CREATE TABLE t_score(
stuid INT(11),
Chinese INT(4),
English INT(4),
Math INT(4),
Chemistry INT(4),
Physics INT(4),
CONSTRAINT fk_stuid FOREIGN KEY(stuid) REFERENCES t_student(id)
);
DESC T_STUDENT;
DESC T_SCORE;
ALTER TABLE T_STUDENT MODIFY gender VARCHAR(8);
DESC T_STUDENT;
插入数据
INSERT INTO T_STUDENT VALUES(1,'Rebecca',16,'Female'),
(2,'Justin',17,'Male'),
(3,'Jim',16,'Male');
INSERT INTO T_SCORE VALUES(1,87,94,99,89,91),
(2,76,78,89,80,90),
(3,92,98,99,93,80);
查询表格
SELECT stu.name,sco.Chinese,sco.English,
sco.Math,sco.Chemistry,sco.Physics,
sco.Chinese+sco.English+sco.Math+sco.Chemistry+sco.Physics total_score
FROM t_student stu,t_score sco
WHERE stu.id=sco.stuid;
非法运算操作，返回NULL
比较运算符
SELECT 3=3 数值比较,'sky'='heaven' 字符串比,3*4=2*6 表达式比,1<=>1 数值比,
'dragon'<=>'dragon' 字符串比较,2+7<=>6+3 表达式比;
“=”和“<=>”在比较字符串是否相等的时候，依据字符的ASCII 码来进行判断。“=”不能操作空值（NULL），而“<=>”可以操作空值（NULL）
“=”不能操作NULL所以结果为NULL，“<=>”可以操作空值所以，结果为1或0
SELECT NULL<=>NULL '<=>符号效果',NULL=NULL '=符号效果';
SELECT 2<>2 数值比较,'mouse'<>'keyboard' 字符串比较, 2+5<>3+4 数值比较,
6!=6 数值比较,'year'!='year' 字符串比较,7+8!=2+9 数值比较;
“<>”和“!=”都不能操作空值（NULL）所以结果都是NULL
SELECT NULL!=NULL '!=符号效果', NULL<>NULL '<>符号效果';
SELECT 4>=4 数值比较,'abcde'>='abcde' 字符串比较,5+8>4+5 数值比较,
3>3 数值比较,'abc'<='bcd' as '<=符号使用',2+7<5+9 as '<符号使用';
特殊运算符
IS NULL && IS NOT NULL && LIKE
like运算符，判断某个字符串中是否含有另一个字符串，如果含有，返回1，否则返回0
%ice% ：ice前后都有别的字符
ice% ： ice后面有别的字符，ice开头，ice前面没有别的字符
%ice ： ice前面有别的字符，ice结尾，ice后面没有别得字符
SELECT 8 IS NULL, NULL IS NULL;
SELECT 8 IS NOT NULL, NULL IS NOT NULL;
SELECT 'songofice&fire' like 'ice%',
'songofice&fire' like '%ice%',
'songofice&fire' like '%eci%';
BETWEEN AND && IN && REGEXP
BETWEEN AND : 判断是否在在某个范围内
IN ： 判断是否在某个列表中
REGEXP : 正则表达式去模式匹配一类字符串
SELECT 27 BETWEEN 18 AND 30,9 BETWEEN 5 AND 10;
SELECT 4 in (3,4,5),
'a' in('a','b','c','d'),
10 in (7,8,9);
SELECT 'onelittlefinger' REGEXP '^o',
'onelittlefinger' REGEXP '^one';
逻辑运算符
SELECT 5 AND 6,0 AND 7,0 AND NULL,3 AND NULL,
9 && 2,0 && 12,0 && NULL,14 && NULL;
SELECT 5 OR 6, 0 OR 7,0 OR 0,3 OR NULL,
9 || 2,0 || 12,0 || NULL,14 || NULL;
SELECT NOT 5,NOT 0,NOT NULL,!3,!0,!NULL;
SELECT 5 XOR 6,0 XOR 0,NULL XOR NULL,
0 XOR 7,0 XOR NULL,3 XOR NULL;
位运算符
SELECT 3&6, BIN(3&6) 二进制数,3&6&7, BIN(3&6&7) 二进制数;
SELECT 3|6, BIN(3|6) 二进制数,3|6|7, BIN(3|6|7) 二进制数;
SELECT ~6, BIN(~6) 二进制数;
SELECT 6^7, BIN(6^7) 二进制数;
SELECT BIN(7) 二进制数,7<<4,BIN(7<<4) 二进制数,7>>2,BIN(7>>2) 二进制数;
运算符优先级
综合示例
CREATE table test(NUM INT(4),INFO VARCHAR(100));
INSERT INTO test VALUES(50,"YouthIsNotATimeOfLife");
SELECT num,num+10,num-12,num*2,num DIV 5,num%3 FROM test;
SELECT num,num=20,num<>45,num>35,num>=30,num<10,num<=40,num<=>60 FROM test;
SELECT num,num BETWEEN 34 AND 49,num IN(2,4,50,65,78) FROM test;
SELECT info,info is NULL,info LIKE "Yo%",info REGEXP "^X",info REGEXP 'e$' FROM test;
SELECT 3&&0,4&&NULL,0 AND NULL,4||0,5||NULL,0 OR NULL;
SELECT !3,!0,NOT NULL,4 XOR 0,2 XOR NULL,0 XOR NULL;
SELECT 8&12,8|12,~13;
SELECT 14<<3,155>>2;
mysql> CREATE table test(NUM INT(4),INFO VARCHAR(100));INSERT INTO test VALUES(50,"YouthIsNotATimeOfLife");SELECT num,num+10,num-12,num*2,num DIV 5,num%3 FROM test;SELECT num,num=20,num<>45,num>35,num>=30,num<10,num<=40,num<=>60 FROM test;SELECT num,num BETWEEN 34 AND 49,num IN(2,4,50,65,78) FROM test;SELECT info,info is NULL,info LIKE "Yo%",info REGEXP "^X",info REGEXP 'e$' FROM test;SELECT 3&&0,4&&NULL,0 AND NULL,4||0,5||NULL,0 OR NULL;SELECT !3,!0,NOT NULL,4 XOR 0,2 XOR NULL,0 XOR NULL;SELECT 8&12,8|12,~13;SELECT 14<<3,155>>2;
Query OK, 0 rows affected, 1 warning (0.05 sec)
Query OK, 1 row affected (0.01 sec)
+------+--------+--------+-------+-----------+-------+
| num | num+10 | num-12 | num*2 | num DIV 5 | num%3 |
+------+--------+--------+-------+-----------+-------+
| 50 | 60 | 38 | 100 | 10 | 2 |
+------+--------+--------+-------+-----------+-------+
1 row in set (0.00 sec)
+------+--------+---------+--------+---------+--------+---------+----------+
| num | num=20 | num<>45 | num>35 | num>=30 | num<10 | num<=40 | num<=>60 |
+------+--------+---------+--------+---------+--------+---------+----------+
| 50 | 0 | 1 | 1 | 1 | 0 | 0 | 0 |
+------+--------+---------+--------+---------+--------+---------+----------+
1 row in set (0.00 sec)
+------+-----------------------+----------------------+
| num | num BETWEEN 34 AND 49 | num IN(2,4,50,65,78) |
+------+-----------------------+----------------------+
| 50 | 0 | 1 |
+------+-----------------------+----------------------+
1 row in set (0.00 sec)
+-----------------------+--------------+-----------------+------------------+------------------+
| info | info is NULL | info LIKE "Yo%" | info REGEXP "^X" | info REGEXP 'e$' |
+-----------------------+--------------+-----------------+------------------+------------------+
| YouthIsNotATimeOfLife | 0 | 1 | 0 | 1 |
+-----------------------+--------------+-----------------+------------------+------------------+
1 row in set (0.00 sec)
+------+---------+------------+------+---------+-----------+
| 3&&0 | 4&&NULL | 0 AND NULL | 4||0 | 5||NULL | 0 OR NULL |
+------+---------+------------+------+---------+-----------+
| 0 | NULL | 0 | 1 | 1 | NULL |
+------+---------+------------+------+---------+-----------+
1 row in set, 4 warnings (0.00 sec)
+----+----+----------+---------+------------+------------+
| !3 | !0 | NOT NULL | 4 XOR 0 | 2 XOR NULL | 0 XOR NULL |
+----+----+----------+---------+------------+------------+
| 0 | 1 | NULL | 1 | NULL | NULL |
+----+----+----------+---------+------------+------------+
1 row in set, 2 warnings (0.00 sec)
+------+------+----------------------+
| 8&12 | 8|12 | ~13 |
+------+------+----------------------+
| 8 | 12 | 18446744073709551602 |
+------+------+----------------------+
1 row in set (0.00 sec)
+-------+--------+
| 14<<3 | 155>>2 |
+-------+--------+
| 112 | 38 |
+-------+--------+
1 row in set (0.00 sec)
mysql>
822
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
