MySQL高级SQL语句
- 一:运算符
- 1.1:比较运算符
- 1.1.1:常用的比较运算符
- 1.1.2:等于运算符
- 1.1.3:不等于运算符
- 1.1.4:大于、大于等于、小于、小于等于
- 1.1.5: IS NULL、IS NOT NULL
- 1.1.6:BETWEEN AND
- 1.1.7:LEAST、GREATEST
- 1.1.8:IN、NOT IN
- 1.1.9:LIKE、NOT LIKE
- 1.2:逻辑运算符
- 1.2.1:常用的逻辑运算符号
- 1.2.2:逻辑非
- 1.2.3:逻辑与
- 1.2.4:逻辑或
- 1.2.5:逻辑异或
- 1.3:位运算符
- 1.3.1:MYSQL支持的运算符
- 1.3.2:运算符的优先级
- 1.4:连接查询
- 1.4.1:内连接
- 1.4.2:左连接
- 1.4.3:右连接
- 二:数据库函数
- 2.1:常用的函数分类
- 2.2:数学函数
- 2.3:聚合函数
- 2.4:字符串函数
- 2.5:日期时间函数
一:运算符
1.1:比较运算符
- 字符串的比较迷人不区分大小写,可使用binary来区分
1.1.1:常用的比较运算符
1.1.2:等于运算符
等号(=)是用来判断数字、字符串和表达式是否相等的,如果相等则返回 1,如果不相等则返回 0。如果比较的两者有一个值是 NULL,则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的,如果 ASCII 码相等,则表示两个字符相同;如果 ASCII 码不相等,则表示两个字符不相同。例如,等于运算符在数字、字符串和表达式上的使用,具 体操作如下所示。
mysql> select 2=3,3='3','abc'='abc',(3+4)=(2+5),'a'=NULL;
+-----+-------+-----------------+-------------+----------+
| 2=3 | 3='3' | 'abc'='abc' | (3+4)=(2+5) | 'a'=NULL |
+-----+-------+-----------------+-------------+----------+
| 0 | 1 | 1 | 1 | NULL |
+-----+-------+-----------------+-------------+----------+
1 row in set (0.00 sec)
从以上查询可以看出来:
- 如果两者都是整数,则按照整数值进行比较。
- 如果一个整数一个字符串,则会自动将字符串转换为数字,再进行比较。
- 如果两者都是字符串,则按照字符串进行比较。
- 如果两者中至少有一个值是 NULL,则比较的结果是 NULL
1.1.3:不等于运算符
- 不等于号有两种写法,分别是<>或者!=,用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1,如果相等则返回 0,这点正好跟等于的返回值相反。(不等于运算符不能用来判断null)
例如:
mysql> mysqlt 'abc'<>'def',13!=17,NULL=NULL;
+-----------------+--------+-----------+
| 'abc'<>'def' | 13!=17 | NULL=NULL |
+-----------------+--------+-----------+
| 1 | 1 | NULL |
+-----------------+--------+-----------+
1 row in set (0.00 sec)
#不等于运算符不能判断NULL
数值与字符串是否能比较?
mysql> select 'hhh'!=45;
+-----------+
| 'hhh'!=45 |
+-----------+
| 1 |
+-----------+
1 row in set, 1 warning (0.00 sec)
mysql> select 'hhh'=45;
+----------+
| 'hhh'=45 |
+----------+
| 0 |
+----------+
1 row in set, 1 warning (0.00 sec)
1.1.4:大于、大于等于、小于、小于等于
- 大于(>)运算符用来判断左侧的操作数是否大于右侧的操作数,若大于返回 1,否则返回 0,同样不能用于判断 NULL。
- 小于(<)运算符用来判断左侧的操作数是否小于右侧的操作数,若小于返回 1,否则返回 0,同样不能用于判断 NULL。
- 大于等于(>=)判断左侧的操作数是否大于等于右侧的操作数,若大于等于返回 1,否则返回 0,不能用于判断 NULL。
- 小于等于(<=)判断左侧的操作数是否小于等于右侧的操作数,若小于等于返回 1,否则返回 0,不能用于判断 NULL。
数值比较会自动转换ASCII表的数值
常见的几个字符的ASCLL值:
0为48(其他以此类推)
A为65(其他以此类推)
a为97(其他以此类推)
mysql> mysql> select 5>3,'a'>'b',3>=4,(5+6)>=(3+2),4.4<3,1<2,'x'<='y',5<=5.5,'u'>=NULL;
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
| 5>3 | 'a'>'b' | 3>=4 | (5+6)>=(3+2) | 4.4<3 | 1<2 | 'x'<='y' | 5<=5.5 | 'u'>=NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
| 1 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
1 row in set (0.00 sec)
1.1.5: IS NULL、IS NOT NULL
- ISNULL 判断一个值是否为 NULL,如果为 NULL 返回 1,否则返回 0。
- IS NOT NULL 判断一个值是否不为 NULL,如果不为 NULL 返回 1,否则返回 0
例如:
mysql> select 2 is NULL,'d' is not NULL,NULL IS NULL;
+-----------+-----------------+--------------+
| 2 is NULL | 'd' is not NULL | NULL IS NULL |
+-----------+-----------------+--------------+
| 0 | 1 | 1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)
1.1.6:BETWEEN AND
- BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。例如,判断某数字是否在另外两个数字之间,也可以判断某英文字母是否在另外两个字母之间
例如:
mysql> select 5 between 2 and 8,7 between 5 and 10,'d' between 'a' and 'z';
+-------------------+--------------------+-------------------------+
| 5 between 2 and 8 | 7 between 5 and 10 | 'd' between 'a' and 'z' |
+-------------------+--------------------+-------------------------+
| 1 | 1 | 1 |
+-------------------+--------------------+-------------------------+
1 row in set (0.00 sec)
数字能否与字符比较?
mysql> select 5 between 2 and 8,7 between 5 and 10,8 between 'a' and 'z';
+-------------------+--------------------+-----------------------+
| 5 between 2 and 8 | 7 between 5 and 10 | 8 between 'a' and 'z' |
+-------------------+--------------------+-----------------------+
| 1 | 1 | 0 |
+-------------------+--------------------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
#数字不能与字符比较的
1.1.7:LEAST、GREATEST
- LEAST:当有两个或者多个参数时,返回其中的最小值。如果其中一个值为 NULL,则返回结果就为 NULL。
- GREATEST:当有两个或者多个参数时,返回其中的最大值。如果其中一个值为 NULL, 则返回结果就为 NULL。
例如:
mysql> SELECT least(1,2,3),least('a','d','f'),greatest(1,2,3),greatest('a','d','f');
+--------------+--------------------+-----------------+-----------------------+
| least(1,2,3) | least('a','d','f') | greatest(1,2,3) | greatest('a','d','f') |
+--------------+--------------------+-----------------+-----------------------+
| 1 | a | 3 | f |
+--------------+--------------------+-----------------+-----------------------+
1 row in set (0.00 sec)
min是否能求最小值呢
mysql> select min(2,3,4),min('d','e','f');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3,4),min('d','e','f')' at line 1
#对了是不能的 min后面只能跟字段名称
1.1.8:IN、NOT IN
- IN 判断一个值是否在对应的列表中,如果是返回 1,否则返回 0。
- NOT IN 判断一个值是否不在对应的列表中,如果不是返回 1,否则返回 0。
mysql> select 2 in (1,2,3,4,5),'c' not in ('a','b','c');
+------------------+--------------------------+
| 2 in (1,2,3,4,5) | 'c' not in ('a','b','c') |
+------------------+--------------------------+
| 1 | 0 |
+------------------+--------------------------+
1 row in set (0.00 sec)
1.1.9:LIKE、NOT LIKE
- LIKE 用来匹配字符串,如果匹配成功则返回 1,反之返回 0。LIKE 支持两种通配符:’%’ 用于匹配任意数目的字符,而’_’只能匹配一个字符。NOT LIKE 正好跟 LIKE 相反,如果没有匹配成功则返回 1,反之返回 0。
mysql> select 'shuai' like 'shu_','xiao' like '%k','lisi' not like '%a';
+---------------------+------------------+----------------------+
| 'shuai' like 'shu_' | 'xiao' like '%k' | 'lisi' not like '%a' |
+---------------------+------------------+----------------------+
| 0 | 0 | 1 |
+---------------------+------------------+----------------------+
1 row in set (0.00 sec)
1.2:逻辑运算符
- 逻辑运算符又被称为布尔运算符,通常用来判断表达式的真假,如果为真返回 1,否则返回 0,真和假也可以用 TRUE 和 FALSE 表示。
- 又被称布尔值运算符
- 用来判断表达式的真假
1.2.1:常用的逻辑运算符号
1.2.2:逻辑非
- 逻辑运算符中最简单的运算符就是逻辑非,逻辑非使用 NOT 或!表示。逻辑非将跟在它后面的逻辑测试取反,把真变为假,把假变为真。如果 NOT 后面的操作数为 0 时,所得值为 1;如果操作数为非 0 时,所得值为 0;如果操作数为 NULL 时,所得值为 NULL。例如,对非 0 值和 0 值分别作逻辑非运算。
示例:
mysql> select not 0,not 2,!4,!(2-2);
+-------+-------+----+--------+
| not 0 | not 2 | !4 | !(2-2) |
+-------+-------+----+--------+
| 1 | 0 | 0 | 1 |
+-------+-------+----+--------+
1 row in set (0.00 sec)
1.2.3:逻辑与
- 逻辑与通常用于判断两个值或多个值的有效性,如果所有值都是真返回 1,否则返回 0。逻辑与使用 AND 或者&&表示。例如,对非 0 值、0 值和 NULL 值分别作逻辑与运算。
示例:
#两个都是非0值 出来结果就是1
区分是按照非0值与0区分的
mysql> select 3 and 5,0 and 8,0 and NULL,1 and NULL;
+---------+---------+------------+------------+
| 3 and 5 | 0 and 8 | 0 and NULL | 1 and NULL |
+---------+---------+------------+------------+
| 1 | 0 | 0 | NULL |
+---------+---------+------------+------------+
1 row in set (0.00 sec)
1.2.4:逻辑或
- 逻辑与通常用于判断两个值或多个值的有效性,如果所有值都是真返回 1,否则返回 0。逻辑与使用 AND 或者&&表示。例如,对非 0 值、0 值和 NULL 值分别作逻辑与运算。
一旦有个非0值,出来就是非0值
示例:
mysql> select 3 OR 5,0 OR 8,0 OR NULL,1 OR NULL;
+--------+--------+-----------+-----------+
| 3 OR 5 | 0 OR 8 | 0 OR NULL | 1 OR NULL |
+--------+--------+-----------+-----------+
| 1 | 1 | NULL | 1 |
+--------+--------+-----------+-----------+
1 row in set (0.00 sec)
1.2.5:逻辑异或
- 两个非 NULL 值的操作数,如果两者都是 0 或者都是非 0,则返回 0;如果一个为 0, 另一个为非 0,则返回结果为 1;当任意一个值为 NULL 时,返回值为 NULL。例如,对非0 值、0 值和 NULL 值分别作逻辑异或运算。
示例:
mysql> SELECT 2 XOR 3,0 XOR 0,0 XOR 5,1 XOR NULL,NULL XOR NULL;
+---------+---------+---------+------------+---------------+
| 2 XOR 3 | 0 XOR 0 | 0 XOR 5 | 1 XOR NULL | NULL XOR NULL |
+---------+---------+---------+------------+---------------+
| 0 | 0 | 1 | NULL | NULL |
+---------+---------+---------+------------+---------------+
1 row in set (0.00 sec)
1.3:位运算符
- 位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式,然后进行位运算,最后在将计算结果从二进制变回到十进制格式,方便用户查 看。
1.3.1:MYSQL支持的运算符
示例:
mysql> select 10&15,10|15,10^15,5 &~1;
+-------+-------+-------+-------+
| 10&15 | 10|15 | 10^15 | 5 &~1 |
+-------+-------+-------+-------+
| 10 | 15 | 5 | 4 |
+-------+-------+-------+-------+
1 row in set (0.00 sec)
- 按位与运算(&),是对应的二进制位都是 1 的,它们的运算结果为 1,否则为 0,所以 10 & 15 的结果为 10。
- 按位或运算(|),是对应的二进制位有一个或两个为 1 的,运算结果为 1,否则为 0, 所以 10 | 15 的结果为 15。
- 按位异或运算(^),是对应的二进制位不相同时,运算结果 1,否则为 0,所以 10 ^ 15的结果为 5。
- 按位取反(~),是对应的二进制数逐位反转,即 1 取反后变为 0, 0 取反后变为 1。数字 1 的二进制是 0001,取反后变为 1110, 数字 5 的二进制是 0101,将 1110 和 0101进行求与操作,其结果是二进制的 0100,转换为十进制就是 4。
列如对数字进行左移动或右移的运算:
mysql> select 1<<2;
+------+
| 1<<2 |
+------+
| 4 |
+------+
1 row in set (0.01 sec)
mysql> select 10<<2;
+-------+
| 10<<2 |
+-------+
| 40 |
+-------+
1 row in set (0.00 sec)
<<左移位运算 向左移动两位,空缺出补0
1<<2
0001
0100 左移后
10<<2
1010
101000 左移后
1.3.2:运算符的优先级
- 以上不管哪种运算符,在使用过程中都有优先级问题。运算符的优先级决定了不同的运 算符在计算过程中的先后顺序。级别高的运算符会先进行计算,如果运算符的级别相同, MySQL 会按照顺序从左到右依次进行计算。如果不确定所使用的运算符的优先级,可以使用()改变优先级。
1.4:连接查询
- MySQL 的连接查询,通常都是将来自两个或多个表的行结合起来,基于这些表之间的共同字段,进行数据的拼接。首先,要确定一个主表作为结果集,然后将其他表的行有选择 性的连接到选定的主表结果集上。使用较多的连接查询包括:内连接、左连接和右连接
示例:
mysql> select * from tt;
+----+-----------+-------+----------+-------+
| id | name | score | address | hobby |
+----+-----------+-------+----------+-------+
| 1 | wangwu | 70.00 | beijing | 2 |
| 2 | lisi | 80.00 | nanjing | 1 |
| 3 | zhangsan | 80.00 | beijing | 1 |
| 4 | zhaoliu | 60.00 | nanjing | 2 |
| 5 | tianqi | 80.00 | beijing | 1 |
| 6 | shuaige | 70.00 | suzhou | 2 |
| 7 | tom | 87.00 | shanghai | 1 |
| 8 | shuaige | 76.00 | hangzhou | 2 |
| 13 | 0000 | 37.00 | tianjing | 2 |
| 14 | 000000000 | 88.00 | shanghai | 3 |
| 15 | aaa | 66.00 | wuchang | 0 |
| 16 | bbb | 45.00 | sichuan | 0 |
+----+-----------+-------+----------+-------+
12 rows in set (0.00 sec)
mysql> select * from gg;
+----+-----------+
| id | gg_name |
+----+-----------+
| 1 | 小当家 |
| 2 | 小神龙 |
| 3 | 小福贵 |
| 4 | 小可爱 |
+----+-----------+
4 rows in set (0.00 sec)
1.4.1:内连接
- 内连接是系统默认的表连接,所以在 FROM 子句后可以省略 INNER 关键字,只使用关键字 JOIN。同时有多个表时,也可以连续使用 INNER JOIN 来实现多表的内连接,不过为了更好的性能,建议最好不要超过三个表。
示例:
mysql> select T.name,G.gg_name from tt T inner join gg G on T.hobby=G.id;
+-----------+-----------+
| name | gg_name |
+-----------+-----------+
| wangwu | 小神龙 |
| lisi | 小当家 |
| zhangsan | 小当家 |
| zhaoliu | 小神龙 |
| tianqi | 小当家 |
| shuaige | 小神龙 |
| tom | 小当家 |
| shuaige | 小神龙 |
| 0000 | 小神龙 |
| 000000000 | 小福贵 |
+-----------+-----------+
10 rows in set (0.00 sec)
上述操作是将 tt表的每一行与 gg表的每一行进行比较,以检查它们是否都满足条件。当满足条件时,INNER JOIN 将返回由 tt表和 gg表中的列组成的新行。如果没有匹配项,查询将返回一个空的结果集。
- 具体的INNER JOIN工作原理
- 如果表中至少有一个匹配项,INNER JOIN 关键字将返回一行。如果没有匹配项,则不会有输出结果。
1.4.2:左连接
- MySQL 除了内连接,还可以使用外连接。区别于 MySQL 外连接是将表分为基础表和参考表,再依据基础表返回满足条件或不满足条件的记录。外连接按照连接时表的顺序来分, 有左连接和右连接之分。
- 左连接也可以被称为左外连接,在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表,接收左表的所有行,并用这些行与右侧参考表中的记录进行匹配,也就是说匹配左表中的所有行以及右表中符合条件的行。
示例:
mysql> select T.name,G.gg_name from tt T left join gg G on T.hobby=G.id;
+-----------+-----------+
| name | gg_name |
+-----------+-----------+
| lisi | 小当家 |
| zhangsan | 小当家 |
| tianqi | 小当家 |
| tom | 小当家 |
| wangwu | 小神龙 |
| zhaoliu | 小神龙 |
| shuaige | 小神龙 |
| shuaige | 小神龙 |
| 0000 | 小神龙 |
| 000000000 | 小福贵 |
| aaa | NULL |
| bbb | NULL |
+-----------+-----------+
12 rows in set (0.01 sec)
主表全部显示
从以上左连接查询结果可以看出,除了匹配的行,还包括了左表中有但右表中没有的行,如果有表没有对应值,则使用 NULL 代替。也就是说在左连接查询中,使用 NULL 值表示右表中没有找到与左表中匹配的记录。
1.4.3:右连接
- 右连接也被称为右外连接,在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反,它是以右表为基础表,用于接收右表中的所有行,并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录
mysql> mysql> select T.name,G.gg_name from tt T right join gg G on T.hobby=G.id;
+-----------+-----------+
| name | gg_name |
+-----------+-----------+
| wangwu | 小神龙 |
| lisi | 小当家 |
| zhangsan | 小当家 |
| zhaoliu | 小神龙 |
| tianqi | 小当家 |
| shuaige | 小神龙 |
| tom | 小当家 |
| shuaige | 小神龙 |
| 0000 | 小神龙 |
| 000000000 | 小福贵 |
| NULL | 小可爱 |
+-----------+-----------+
11 rows in set (0.00 sec)
#右边所有信息都显示(tt) 左表是辅助
在右连接的查询结果集中,除了符合匹配规则的行外,还包括右表中有但是左表中不匹 配的行,这些记录在左表中以 NULL 补足。
MYSQL提供了实现各种功能的函数
2.1:常用的函数分类
- 数学函数
- 聚合函数
- 字符串函数
- 日期时间函数
2.2:数学函数
数学函数 | 描述 |
abs(x) | 返回x的绝对值 |
rand() | 返回0到1的随机数 |
mod(x,y) | 返回x除以y以后的余数 |
poewr(x,y) | 返回x的y次方 |
round(x) | 返回离x最近的整数 |
round(x,y) | 保留x的y位小数四舍五入后的值 |
sqrt(x) | 返回x的平方根 |
truncate(x,y) | 返回数字x截断为y位小数的值 |
ceil(x) | 返回大于或等于x的最小整数 |
floor(x) | 返回小于或等于x的最大整数 |
greatest(x1,x2…) | 返回集合中的最大值 |
least(x1,x2…) | 返回集合中的最小值 |
2.3:聚合函数
- 对表中数据记录进行集中概括而设计的一类函数
- 常用的聚合函数
聚合函数 | 描述 |
avg() | 返回指定列的平均值 |
count() | 返回指定列中非NULL值的个数 |
min() | 返回指定列的最小值 |
max() | 返回指定列的最大值 |
sum() | 返回指定列的所有值的和 |
2.4:字符串函数
- 常用的字符串函数
2.5:日期时间函数
- 常用的日期时间函数
字符串函数 | 描述 |
curdate() | 返回当前的年月日 |
curtime() | 返回当前的时分秒 |
now() | 返回当前的日期和时间 |
month(x) | 返回日期X中的月份值 |
week(x) | 返回日期X是年度第几个星期 |
hour(x) | 返回X中的最小值 |
minute(x) | 返回X中的分钟值 |
second(x) | 返回X中的秒钟值 |
dayofweek(x) | 返回X是星期几,1是星期日,2是星期一 |
dayofmonth(x) | 计算日期X是本月的第几天 |
dayofyear(x) | 计算日期X是本年的第几天 |