文章目录
MySQL进阶查询
1.1: 按关键字排序
-
使用ORDER BY语句来实现排序
排序可针对一个或多个字段
ASC:升序,默认排序方式
DESC:降序
ORDERBY的语法结构SELECT column1,column2,...FROM table_name ORDER BY column1,column2,... ASC|DESC;
- 按单字段排序
mysql> select * from Hob; +----+--------------+ | id | hob_name | +----+--------------+ | 1 | 云计算 | | 2 | 大数据 | | 3 | 人工智能 | +----+--------------+ 3 rows in set (0.00 sec) mysql> select * from accp; +----+----------+-----+-------+-------+ | id | name | age | hobby | score | +----+----------+-----+-------+-------+ | 1 | wangwu | 18 | 2 | 88.00 | | 2 | lisi | 28 | 1 | 90.00 | | 4 | wangwu | 30 | 3 | 88.00 | | 3 | zhangsan | 20 | 2 | 80.00 | +----+----------+-----+-------+-------+ 4 rows in set (0.00 sec) mysql> select id,name,score from accp where score>80 order by score desc; +----+--------+-------+ | id | name | score | +----+--------+-------+ | 2 | lisi | 90.00 | | 1 | wangwu | 88.00 | | 4 | wangwu | 88.00 | +----+--------+-------+ 3 rows in set (0.00 sec)
1.2: 按多字段排序
mysql> select id,name,score from accp where score>80 order by score desc,id desc;
+----+--------+-------+
| id | name | score |
+----+--------+-------+
| 2 | lisi | 90.00 |
| 4 | wangwu | 88.00 |
| 1 | wangwu | 88.00 |
+----+--------+-------+
3 rows in set (0.00 sec)
在前一个字段score相同的情况下,再按id降序排列
主参考放前面,辅助参考字段放后面
主参考相同的参考第二个字段排序
1.3: 对结果进行分组
-
使用GROUP BY语句来实现分组
-
通常结合聚合函数一起使用
-
可以按一个或多个字段对结果进行分组
-
GROUP BY的语法结构
SELECT column_name,aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
-
聚合函数
聚合函数 聚合函数多个值传进去却只出来一个值
count (字段名称) 计数
sum (字段名称) 求和
avg (字段名称) 平均值
max (字段名称) 最大值
min (字段名称) 最小值
1.3.1: GROUP BY分组
- GROUP BY分组
GROUP BY分组
mysq> select count(name),level from player where level>=45 group by level;
mysql> select count(name),score from kgc where score>=88 group by score;
+-------------+-------+
| count(name) | score |
+-------------+-------+
| 2 | 88.00 |
| 1 | 90.00 |
+-------------+-------+
2 rows in set (0.00 sec)
//88分的两人,90分的1人
mysql> select count(name),score from kgc where score>=80 group by score;
+-------------+-------+
| count(name) | score |
+-------------+-------+
| 1 | 80.00 |
| 2 | 88.00 |
| 1 | 90.00 |
+-------------+-------+
3 rows in set (0.00 sec)
mysql> select count(name),score,age from kgc where score>=80 group by score;
+-------------+-------+-----+
| count(name) | score | age |
+-------------+-------+-----+
| 1 | 80.00 | 20 |
| 2 | 88.00 | 18 |
| 1 | 90.00 | 28 |
+-------------+-------+-----+
3 rows in set (0.00 sec)
1.3.2: GROUP BY结合ORDER BY
mysql> select count(name),level from player where level>=45 group by level
order by count(name) desc;
mysql> select count(name),score,age from kgc where score>=80 group by score desc;
+-------------+-------+-----+
| count(name) | score | age |
+-------------+-------+-----+
| 1 | 90.00 | 28 |
| 2 | 88.00 | 18 |
| 1 | 80.00 | 20 |
+-------------+-------+-----+
3 rows in set (0.00 sec)
mysql> select count(name),score,age from kgc where score>=80 group by score order by count(name) desc;
+-------------+-------+-----+
| count(name) | score | age |
+-------------+-------+-----+
| 2 | 88.00 | 18 |
| 1 | 90.00 | 28 |
| 1 | 80.00 | 20 |
+-------------+-------+-----+
3 rows in set (0.00 sec)
1.4: 限制结果条目
-
只返回SELECT查询结果的第一行或前几行
-
使用LIMIT语句限制条目
-
LIMIT语法结构
SELECT column1,column2,....FROM table_name LIMIT[offset,] number; number 返回记录行的最大数目 [offset,] 位置偏移量,从0开始 [a,b] 索引起始值 b代表包括a的行开始往下b行
mysql> select id,name from kgc limit 3; +----+--------+ | id | name | +----+--------+ | 1 | wangwu | | 2 | lisi | | 4 | wangwu | +----+--------+ 3 rows in set (0.00 sec) //显示前三条数据
mysql> select id,name from kgc limit 2,3; +----+----------+ | id | name | +----+----------+ | 4 | wangwu | | 3 | zhangsan | +----+----------+ 2 rows in set (0.00 sec) //从第二条开始,显示之后的三条数据
1.5: 设置别名
-
使用AS语句设置别名,关键字AS可省略
-
设置别名时,保证不能与库中其他表或字段名称冲突
-
别名的语法结构列的别名
SELECT column_name AS alias_name FROM table_name; SELECT column_name(s)FROM table_name AS alias_name;
-
AS的用法
字段的别名 mysql> select count(*) as number from player; +--------+ | number | +--------+ |3218 | +--------+ 1 row in set (o.o1 sec) 表的别名 mysql> select p.id,p.name from player as p limit 1; +----+--------------+ lid | namel +----+--------------+ |1 │修欧拉卡 | 3 rows in set (O.00 sec) A as B 把A的别名定义成B
mysql> select a.name,h.hob_name from accp a inner join Hob h on a.hobby=h.id; +----------+--------------+ | name | hob_name | +----------+--------------+ | wangwu | 大数据 | | lisi | 云计算 | | wangwu | 人工智能 | | zhangsan | 大数据 | +----------+--------------+ 4 rows in set (0.00 sec) 把aaccp as a Hob as h 定义别名,as可以省略
-
此外,AS 还可以作为连接语句的操作符。例如,执行以下操作即可实现用一条 SQL
语句完成在创建表 tmp 的时候将 player 表内的数据写入 tmp 表。
mysql> create table tmp as select * from accp; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from tmp; +----+----------+-----+-------+-------+ | id | name | age | hobby | score | +----+----------+-----+-------+-------+ | 1 | wangwu | 18 | 2 | 88.00 | | 2 | lisi | 28 | 1 | 90.00 | | 4 | wangwu | 30 | 3 | 88.00 | | 3 | zhangsan | 20 | 2 | 80.00 | +----+----------+-----+-------+-------+ 4 rows in set (0.00 sec)
1.6: 通配符
-
用于替换字符串中的部分字符
-
通常配合LIKE一起使用,并协同WHERE完成查询
-
常用通配符
%表示零个、一个或多个
_表示单个字符 -
mysql> select * from accp; +----+----------+-----+-------+-------+ | id | name | age | hobby | score | +----+----------+-----+-------+-------+ | 1 | wangwu | 18 | 2 | 88.00 | | 2 | lisi | 28 | 1 | 90.00 | | 4 | wangwu | 30 | 3 | 88.00 | | 3 | zhangsan | 20 | 2 | 80.00 | +----+----------+-----+-------+-------+ mysql> select id,name,score from accp where name like '%i'; +----+------+-------+ | id | name | score | +----+------+-------+ | 2 | lisi | 90.00 | +----+------+-------+ 1 row in set (0.00 sec) mysql> select id,name,score from accp where name like 'ss%'; Empty set (0.00 sec) mysql> select id,name,score from accp where name like '%s%'; +----+----------+-------+ | id | name | score | +----+----------+-------+ | 2 | lisi | 90.00 | | 3 | zhangsan | 80.00 | +----+----------+-------+ 2 rows in set (0.00 sec) //%i 是i前面是任意的字符也就是以i结尾的字符串 //%s% 是匹配s左右两边都是任意的字符,中间包含s的字符串
mysql> select id,name,score from accp where name like '_i__'; +----+------+-------+ | id | name | score | +----+------+-------+ | 2 | lisi | 90.00 | +----+------+-------+ 1 row in set (0.00 sec)
1.7: 子查询
-
也称作内查询或者嵌套查询
-
先于主查询被执行,其结果将作为外层主查询的条件
-
在增删改查中都可以使用子查询
-
支持多层嵌套
-
IN语句是用来判断某个值是否在给定的结果集中
-
IN 的语法结构如下
<表达式> [NOT] IN <子查询>
-
当表达式与子查询返回的结果集中的某个值相等时,返回 TRUE,否则返回 FALSE。若启用了 NOT 关键字,则返回值相反。需要注意的是,子查询只能返回一列数据,如果需求比较复杂,一列解决不了问题,可以使用多层嵌套的方式来应对。
-
多数情况下,子查询都是与 SELECT 语句一起使用的。
-
示例
mysql> select name,level from player where id in (select id from player where level>=45); //先查出等级大于等于 45 级的 ID,然后在判断 player 表内的 ID 是不是在这个结果集内,如果在就打印此行的名字和等级
mysql> truncate table tmp;//清空 tmp 表 Query OK, 0 rows affected (0.01 sec) mysql> select * from tmp; Empty set (0.00 sec) mysql> insert into tmp select * from player where id in (select id from player); // 将 player 表 的 内 容 插 入 tmp 表 mysql> insert into tmp select * from player; //可跟上面子查询 SQL 达到同样的效果
-
实操
mysql> select name,hobby from accp where hobby in (select id from Hob where hob_name='云计算'); +------+-------+ | name | hobby | +------+-------+ | lisi | 1 | +------+-------+ 1 row in set (0.00 sec)
mysql> select name,hobby from accp where hobby in (select id from Hob); +----------+-------+ | name | hobby | +----------+-------+ | wangwu | 2 | | lisi | 1 | | wangwu | 3 | | zhangsan | 2 | +----------+-------+ 4 rows in set (0.00 sec)
mysql> select name,hobby from accp where hobby!=(select id from Hob where hob_name='云计算'); +----------+-------+ | name | hobby | +----------+-------+ | wangwu | 2 | | wangwu | 3 | | zhangsan | 2 | +----------+-------+ 3 rows in set (0.00 sec) !=和<>等价
mysql> insert into tmp select * from accp where id in (select id from accp); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from tmp; +----+----------+-----+-------+-------+ | id | name | age | hobby | score | +----+----------+-----+-------+-------+ | 1 | wangwu | 18 | 2 | 88.00 | | 2 | lisi | 28 | 1 | 90.00 | | 4 | wangwu | 30 | 3 | 88.00 | | 3 | zhangsan | 20 | 2 | 80.00 | | 1 | wangwu | 18 | 2 | 88.00 | | 2 | lisi | 28 | 1 | 90.00 | | 4 | wangwu | 30 | 3 | 88.00 | | 3 | zhangsan | 20 | 2 | 80.00 | +----+----------+-----+-------+-------+ 8 rows in set (0.00 sec) //查询accp表的id 集合的值遍历给acccp 当accp的id等于集合里的值得时候,把查询accp的记录插入到tmp内
mysql> select * from accp; +----+----------+-----+-------+-------+ | id | name | age | hobby | score | +----+----------+-----+-------+-------+ | 1 | wangwu | 18 | 2 | 88.00 | | 2 | lisi | 28 | 1 | 90.00 | | 4 | wangwu | 30 | 3 | 88.00 | | 3 | zhangsan | 20 | 2 | 80.00 | +----+----------+-----+-------+-------+ mysql> update accp set score=score+5 where score<88; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from accp; +----+----------+-----+-------+-------+ | id | name | age | hobby | score | +----+----------+-----+-------+-------+ | 1 | wangwu | 18 | 2 | 88.00 | | 2 | lisi | 28 | 1 | 90.00 | | 4 | wangwu | 30 | 3 | 88.00 | | 3 | zhangsan | 20 | 2 | 85.00 | +----+----------+-----+-------+-------+ 4 rows in set (0.00 sec) //把小于88分得分数加5分
mysql> delete from tmp where id in (select a.id from (select id from tmp where age=20) a); Query OK, 2 rows affected (0.01 sec) mysql> select * from tmp; +----+--------+-----+-------+-------+ | id | name | age | hobby | score | +----+--------+-----+-------+-------+ | 1 | wangwu | 18 | 2 | 88.00 | | 2 | lisi | 28 | 1 | 90.00 | | 4 | wangwu | 30 | 3 | 88.00 | | 1 | wangwu | 18 | 2 | 88.00 | | 2 | lisi | 28 | 1 | 90.00 | | 4 | wangwu | 30 | 3 | 88.00 | +----+--------+-----+-------+-------+ //把(select id from tmp where age=20)的结果定义别名给a
mysql> select name,score from accp where exists (select id from Hob where hob_name='大数据'); +----------+-------+ | name | score | +----------+-------+ | wangwu | 88.00 | | lisi | 90.00 | | wangwu | 88.00 | | zhangsan | 85.00 | +----------+-------+ 4 rows in set (0.00 sec) //查询Hob表把兴趣为大数据的id 如果存在 ,执行前面的select语句
1.8: NULL值
-
表示缺失的值
-
与数字0或者空白(spaces))是不同的
-
使用IS NULL或IS NOT NULL进行判断
-
NULL值和空值的区别
空值长度为0,不占空间;NULL值的长度为NULL,占用空间
lS NULL无法判断空值
空值使用“="或者“<>"来处理
COUNT()计算时,NULL会忽略,空值会加入计算 -
NULL 空就是没有 真空,没有任何介质
''空字符串是一个值 如空气
创建时not null 如int类型 则默认值为0
空值长度为零,不占用空间 NULL长度为NULL,占用空间
-
mysql> insert into num (id,name) values (1,'tom'); Query OK, 1 row affected (0.01 sec) mysql> insert into num (id) values (2); Query OK, 1 row affected (0.00 sec) mysql> select * from num; +----+------+ | id | name | +----+------+ | 1 | tom | | 2 | NULL | +----+------+ 2 rows in set (0.00 sec) //如果定义的列是not null,插数据的时候 不能不插入值,要不insert插入报错
mysql> select count(id) from num; +-----------+ | count(id) | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec) mysql> select count(name) from num; +-------------+ | count(name) | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) //COUNT计数时NULL会被忽略
mysql> select * from num; +----+------+ | id | name | +----+------+ | 1 | tom | | 2 | NULL | | 3 | | +----+------+ 3 rows in set (0.00 sec) mysql> select count(name) from num; +-------------+ | count(name) | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec) //Conut计数空值会计入统计
-
查询name 字段为NULL值得记录
mysql> select * from num where name is null; +----+------+ | id | name | +----+------+ | 2 | NULL | +----+------+ 1 row in set (0.01 sec)
-
查询name字段不为空的记录
mysql> select * from num where name is not null; +----+------+ | id | name | +----+------+ | 1 | tom | | 3 | | +----+------+ 2 rows in set (0.00 sec)
1.9: 正则表达式
-
根据指定的匹配模式匹配记录中符合要求的特殊字符
-
使用REGEXP关键字指定匹配模式
-
常用匹配模式
^ 匹配文本的开始字符 ‘^bd’ 匹配以 bd 开头的字符串 $ 匹配文本的结束字符 ‘qn$’ 匹配以 qn 结尾的字符串 . 匹配任何单个字符 ‘s.t’ 匹配任何s 和t 之间有一个字符的字符串 * 匹配零个或多个在它前面的字符 ‘fo*t’ 匹配 t 前面有任意个 o + 匹配前面的字符 1 次或多次 ‘hom+’ 匹配以 ho 开头,后面至少一个m 的字符串 字符串 匹配包含指定的字符串 ‘clo’ 匹配含有 clo 的字符串 p1|p2 匹配 p1 或 p2 ‘bg|fg’ 匹配 bg 或者 fg […] 匹配字符集合中的任意一个字符 ‘[abc]’ 匹配 a 或者 b 或者 c [^…] 匹配不在括号中的任何字符 ‘[^ab]’ 匹配不包含 a 或者 b 的字符串 {n} 匹配前面的字符串 n 次 ‘g{2}’ 匹配含有 2 个 g 的字符串 {n,m} 匹配前面的字符串至少 n 次,至多m 次 ‘f{1,3}’ 匹配 f 最少 1 次,最多 3 次
1.9.1: 以特定字符串开头的记录
-
mysql> select id,name,score from accp where name regexp '^li'; +----+------+-------+ | id | name | score | +----+------+-------+ | 2 | lisi | 90.00 | +----+------+-------+ 1 row in set (0.00 sec)
1.9.2: 以特定字符串结尾的记录
-
mysql> select id,name,score from accp where name regexp 'wu$'; +----+--------+-------+ | id | name | score | +----+--------+-------+ | 1 | wangwu | 88.00 | | 4 | wangwu | 88.00 | +----+--------+-------+ 2 rows in set (0.00 sec)
1.9.3: 包含指定字符串的记录
-
mysql> select id,name,score from accp where name regexp 'an'; +----+----------+-------+ | id | name | score | +----+----------+-------+ | 1 | wangwu | 88.00 | | 4 | wangwu | 88.00 | | 3 | zhangsan | 85.00 | +----+----------+-------+ 3 rows in set (0.00 sec)
1.9.4: 以" ." 代替字符串的任意一个字符记录
-
mysql> select id,name,score from accp where name regexp 'zhang.an'; +----+----------+-------+ | id | name | score | +----+----------+-------+ | 3 | zhangsan | 85.00 | +----+----------+-------+ 1 row in set (0.00 sec)
1.9.5: 匹配包含或者关系的记录
-
mysql> select id,name,score from accp where name regexp 'an|si'; +----+----------+-------+ | id | name | score | +----+----------+-------+ | 1 | wangwu | 88.00 | | 2 | lisi | 90.00 | | 4 | wangwu | 88.00 | | 3 | zhangsan | 85.00 | +----+----------+-------+ 4 rows in set (0.00 sec)
1.9.6: 匹配前面字符的任意多次
-
mysql> insert into num (id,name) values (4,'oooo'),(5,'ooooo'); mysql> select * from num; +----+-------+ | id | name | +----+-------+ | 1 | tom | | 2 | NULL | | 3 | | | 4 | oooo | | 5 | ooooo | +----+-------+ mysql> select id,name from num where name regexp 'ooo*'; +----+-------+ | id | name | +----+-------+ | 4 | oooo | | 5 | ooooo | +----+-------+ 2 rows in set (0.00 sec)
1.9.7: ‘+’ 匹配前面字符至少一次
-
mysql> select id,name from num where name regexp 'ooooo+'; +----+-------+ | id | name | +----+-------+ | 5 | ooooo | +----+-------+ 1 row in set (0.00 sec)
1.9.8: 匹配指定字符集中的任意一个
-
mysql> select id,name from num where name regexp '^[a-z]'; +----+-------+ | id | name | +----+-------+ | 1 | tom | | 4 | oooo | | 5 | ooooo | +----+-------+
1.10: 运算符
1.10.1: 算数运算符
-
MySQL 支持使用的算术运算符,如下表
运算符 描述 + 加法 - 减法 * 乘法 / 除法 % 取余数 mysql> select 1+2,2*3,5-4,7%2,7/2; +-----+-----+-----+------+--------+ | 1+2 | 2*3 | 5-4 | 7%2 | 7/2 | +-----+-----+-----+------+--------+ | 3 | 6 | 1 | 1 | 3.5000 | +-----+-----+-----+------+--------+ 1 row in set (0.00 sec)
1.10.2: 比较运算符
-
通过使用比较运算符可以判断出 表中有哪些记录是符合条件的,如果比较的结果为真则返回 1,如果为假则返回 0,比较的结果如果不确定则返回 NULL。其中字符串在进行比较的时候默认是不区分大小写的,如果要区分大小写可以通过 binary 关键字来实现。
运算符 描述 运算符 描述 = 等于 IS NOT NULL 判断一个值是否不为 NULL > 大于 BETWEEN AND 两者之间 < 小于 IN 在集合中 >= 大于等于 LIKE 通配符匹配 <= 小于等于 GREATEST 两个或多个参数时返回最大值 !=或<> 不等于 LEAST 两个或多个参数时返回最小值 IS NULL 判断一个值是否为 NULL REGEXP 正则表达式 -
等于运算符
等号(=)是用来判断数字、字符串和表达式是否相等的,如果相等则返回 1,如果不相等则返回 0。如果比较的两者有一个值是 NULL,则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的,如果 ASCII 码相等,则表示两个字符相同;如果 ASCII 码不相等,则表示两个字符不相同。
mysql> select 2=4,2='2','e'='e',(2+2)=(3+1),'r'=NULL; +-----+-------+---------+-------------+----------+ | 2=4 | 2='2' | 'e'='e' | (2+2)=(3+1) | 'r'=NULL | +-----+-------+---------+-------------+----------+ | 0 | 1 | 1 | 1 | NULL | +-----+-------+---------+-------------+----------+ 1 row in set (0.01 sec)
从以上查询可以看出来:
如果两者都是整数,则按照整数值进行比较。
如果一个整数一个字符串,则会自动将字符串转换为数字,再进行比较。
如果两者都是字符串,则按照字符串进行比较。
如果两者中至少有一个值是 NULL,则比较的结果是 NULL。
-
不等于运算符
不等于号有两种写法,分别是<>或者!=,用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1,如果相等则返回 0,这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL。
mysql> select 'kgc'!='bdqn',12<>13,NULL<>NULL; +---------------+--------+------------+ | 'kgc'!='bdqn' | 12<>13 | NULL<>NULL | +---------------+--------+------------+ | 1 | 1 | NULL | +---------------+--------+------------+ 1 row in set (0.00 sec)
-
大于、大于等于、小于、小于等于运算符
大于(>)运算符用来判断左侧的操作数是否大于右侧的操作数,若大于返回 1,否则返回 0,同样不能用于判断 NULL。
小于(<)运算符用来判断左侧的操作数是否小于右侧的操作数,若小于返回 1,否则返回 0,同样不能用于判断 NULL。
大于等于(>=)判断左侧的操作数是否大于等于右侧的操作数,若大于等于返回 1,否则返回 0,不能用于判断 NULL。
小于等于(<=)判断左侧的操作数是否小于等于右侧的操作数,若小于等于返回 1,否则返回 0,不能用于判断 NULL。
mysql> mysql> select 5>4,'a'>'b',2>=3,(2+3)>=(1+2),4.4<3,1<2,'x'<='y',5<=5.5,'u'>=NULL; +-----+---------+------+--------------+-------+-----+----------+--------+-----------+ | 5>4 | 'a'>'b' | 2>=3 | (2+3)>=(1+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)
//注意ASCII码的比较 0对应的48 A对应的65, a 对应的97
-
IS NULL、IS NOT NULL
IS NULL 判断一个值是否为 NULL,如果为 NULL 返回 1,否则返回 0。
IS NOT NULL 判断一个值是否不为 NULL,如果不为 NULL 返回 1,否则返回 0。
mysql> select 2 is NULL,'f' is not NULL,NULL is NULL;
+-----------+-----------------+--------------+
| 2 is NULL | 'f' is not NULL | NULL is NULL |
+-----------+-----------------+--------------+
| 0 | 1 | 1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)
-
BETWEEN AND
BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。例如,判断某数字是否在另外两个数字之间,也可以判断某英文字母是否在另外两个字母之间,具体操作 如下所示。
mysql> select 4 between 2 and 6,5 between 5 and 10,'f' between 'a' and 'z'; +-------------------+--------------------+-------------------------+ | 4 between 2 and 6 | 5 between 5 and 10 | 'f' between 'a' and 'z' | +-------------------+--------------------+-------------------------+ | 1 | 1 | 1 | +-------------------+--------------------+-------------------------+ 1 row in set (0.00 sec) mysql> select 'adf' between 'a' and 'c'; +---------------------------+ | 'adf' between 'a' and 'c' | +---------------------------+ | 1 | +---------------------------+ 1 row in set (0.00 sec) 匹配abc的第一个字符,第一个匹配不上接着匹配第二个字符
-
LEAST、GREATEST
LEAST:当有两个或者多个参数时,返回其中的最小值。如果其中一个值为 NULL,则返回结果就为 NULL。
GREATEST:当有两个或者多个参数时,返回其中的最大值。如果其中一个值为 NULL, 则返回结果就为 NULL。
mysql> select least(10,20,30),least('a','b','c'); +-----------------+--------------------+ | least(10,20,30) | least('a','b','c') | +-----------------+--------------------+ | 10 | a | +-----------------+--------------------+ 1 row in set (0.00 sec)
-
IN、NOT IN
IN 判断一个值是否在对应的列表中,如果是返回 1,否则返回 0。
NOT IN 判断一个值是否不在对应的列表中,如果不是返回 1,否则返回 0。
mysql> select 10 in (10,20,30),'c' not in ('a','b','c');
+------------------+--------------------------+
| 10 in (10,20,30) | 'c' not in ('a','b','c') |
+------------------+--------------------------+
| 1 | 0 |
+------------------+--------------------------+
1 row in set (0.00 sec)
-
LIKE、NOT LIKE
LIKE 用来匹配字符串,如果匹配成功则返回 1,反之返回 0。LIKE 支持两种通配符:’%’ 用于匹配任意数目的字符,而’_’只能匹配一个字符。NOT LIKE 正好跟 LIKE 相反,如果没有匹配成功则返回 1,反之返回 0。
A like B
左边参考,右边模糊匹配 ;_ 单个字符 % 任意字符
mysql> SELECT 'bdqn' like 'bdq_','kgc' like '%c','tom' not like '%j'; +--------------------+-----------------+---------------------+ | 'bdqn' like 'bdq_' | 'kgc' like '%c' | 'tom' not like '%j' | +--------------------+-----------------+---------------------+ | 1 | 1 | 1 | +--------------------+-----------------+---------------------+ 1 row in set (0.00 sec)
1.11: 逻辑运算符
-
逻辑运算符又被称为布尔运算符,通常用来判断表达式的真假,如果为真返回 1,否则返回 0,真和假也可以用 TRUE 和 FALSE 表示。MySQL 中支持使用的逻辑运算符有四种, 具体如下表所示。
运算符 描述 NOT 或 ! 逻辑非 AND 或 && 逻辑与 OR 或 || 逻辑或 XOR 逻辑异或 -
逻辑非
逻辑运算符中最简单的运算符就是逻辑非,逻辑非使用 NOT 或!表示。逻辑非将跟在它后面的逻辑测试取反,把真变为假,把假变为真。如果 NOT 后面的操作数为 0 时,所得值为 1;如果操作数为非 0 时,所得值为 0;如果操作数为 NULL 时,所得值为 NULL。
mysql> select not 2,!3,not 0,!(4-4);
+-------+----+-------+--------+
| not 2 | !3 | not 0 | !(4-4) |
+-------+----+-------+--------+
| 0 | 0 | 1 | 1 |
+-------+----+-------+--------+
-
逻辑与 && AND 且
逻辑与通常用于判断两个值或多个值的有效性,如果所有值都是真返回 1,否则返回 0。逻辑与使用 AND 或者&&表示。
mysql> select 2 and 3,4 && 0,0 && NULL,1 AND NULL; +---------+--------+-----------+------------+ | 2 and 3 | 4 && 0 | 0 && NULL | 1 AND NULL | +---------+--------+-----------+------------+ | 1 | 0 | 0 | NULL | +---------+--------+-----------+------------+ 见0则0,全1(非零)则1
-
逻辑或 || OR
逻辑或表示包含的操作数,任意一个为非零值并且不是 NULL 值时,返回 1,否则返回0。逻辑或通常使用 OR 或者||来表示。
mysql> select 2 or 3,4 || 0,0 or null,1 || null;
+--------+--------+-----------+-----------+
| 2 or 3 | 4 || 0 | 0 or null | 1 || null |
+--------+--------+-----------+-----------+
| 1 | 40 | NULL | NULL |
+--------+--------+-----------+-----------+
1 row in set (0.06 sec)
//上述表示40,有问题,所以尽量使用or
mysql> select 2 or 3,4 or 0,0 or null,1 or null;
+--------+--------+-----------+-----------+
| 2 or 3 | 4 or 0 | 0 or null | 1 or null |
+--------+--------+-----------+-----------+
| 1 | 1 | NULL | 1 |
+--------+--------+-----------+-----------+
见1(非零)则1,全0则0
-
逻辑异或 xor
两个非 NULL 值的操作数,如果两者都是 0 或者都是非 0,则返回 0;如果一个为 0, 另一个为非 0,则返回结果为 1;当任意一个值为 NULL 时,返回值为 NULL
mysql> select 2 xor 3,0 xor 1,0 xor null,1 xor null; +---------+---------+------------+------------+ | 2 xor 3 | 0 xor 1 | 0 xor null | 1 xor null | +---------+---------+------------+------------+ | 0 | 1 | NULL | NULL | +---------+---------+------------+------------+ 1 row in set (0.00 sec) //全零则零,全1(非零)则零,一零一非零则为1
-
位运算符
位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式,然后进行位运算,最后在将计算结果从二进制变回到十进制格式,方便用户查 看。MySQL 支持 6 种位运算符,具体如表
运算符 描述 & 按位与 | 按位或 ~ 按位取反 ^ 按位异或 << 按位左移 >> 按位右移 关于与运算和或运算 //与运算 全1则1 1010 10 1111 15 结果 1010=10 //或运算 见1则1 1010 1111 结果 1010=15 //异或运算 1010 1111 结果 0101=5 // 5&~1 意思是5 与 1取反 运算 0001 取反 1110 1110 与运算 全1则1 0101 结果 0100=4
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)
左移或右移运算符,都是将数转换为二进制后,然后在左移或右移指定的位数,超出的位数将被移除并丢弃,空出来的位置用 0 补齐。 例如,“2<<2”将数字 2 的二进制数 0010, 向左移动两位后变成 10,右侧用 00 补齐,最终变为二进制的 1000,转换为十进制是 8。“15>>2”将数字 15 转换为二进制是 1111,向右移动两位,右侧的两位 11 被丢弃,变为 11, 左侧用 00 补齐,最终变为二进制的 0011,转换为十进制就是 3。 1111 右移两位 11 前面补零 0011 0010 左移两位 10 后面空缺补零 1000
mysql> SELECT 1<<2, 2<<2,10>>2,15>>2; +------+------+-------+-------+ | 1<<2 | 2<<2 | 10>>2 | 15>>2 | +------+------+-------+-------+ | 4 | 8 | 2 | 3 | +------+------+-------+-------+ 1 row in set (0.00 sec)
-
运算符的优先级
级别高的运算符会先进行计算,如果运算符的级别相同, MySQL 会按照顺序从左到右依次进行计算。如果不确定所使用的运算符的优先级,可以使用()改变优先级。常用的运算符优先级情况如下表 。“!”的优先级最高,而“:=”的优先级最低。
优先级 运算符 优先级 运算符 1 ! 8 | 2 ~ 9 =,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN 3 ^ 10 BETWEEN,CASE,WHEN,THEN,ELSE 4 * , /(DIV), %(MOD) 11 NOT 5 +,- 12 &&,AND 6 >>,<< 13 ||,OR,XOR 7 & 14 :=
1.12: 连接查询
- MySQL 的连接查询,通常都是将来自两个或多个表的行结合起来,基于这些表之间的共同字段,进行数据的拼接。首先,要确定一个主表作为结果集,然后将其他表的行有选择 性的连接到选定的主表结果集上。使用较多的连接查询包括:内连接、左连接和右连接。
1.12.1: 内连接
-
MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在FROM 子句中使用关键字 INNER JOIN 来连接多张表,并使用 ON 子句设置连接条件。内连接的语法格式如下。
SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
-
内连接是系统默认的表连接,所以在 FROM 子句后可以省略 INNER 关键字,只使用关键字 JOIN。同时有多个表时,也可以连续使用 INNER JOIN 来实现多表的内连接,不过为了更好的性能,建议最好不要超过三个表。
-
mysql> select a.name,h.hob_name from accp a inner join Hob h on a.hobby=h.id; +----------+--------------+ | name | hob_name | +----------+--------------+ | wangwu | 大数据 | | lisi | 云计算 | | wangwu | 人工智能 | | zhangsan | 大数据 | +----------+--------------+
1.12.2: 左连接
-
MySQL 除了内连接,还可以使用外连接。区别于 MySQL 外连接是将表分为基础表和参考表,再依据基础表返回满足条件或不满足条件的记录。外连接按照连接时表的顺序来分, 有左连接和右连接之分。
-
左连接也可以被称为左外连接,在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表,接收左表的所有行,并用这些行与右侧参考表中的记录进行匹配,也就是说匹配左表中的所有行以及右表中符合条件的行
-
mysql> select * from accp; +----+----------+-----+-------+-------+ | id | name | age | hobby | score | +----+----------+-----+-------+-------+ | 1 | wangwu | 18 | 2 | 88.00 | | 2 | lisi | 28 | 1 | 90.00 | | 4 | wangwu | 30 | 3 | 88.00 | | 3 | zhangsan | 20 | 2 | 85.00 | | 5 | zhaoliu | 98 | 0 | 98.00 | | 6 | tianqi | 100 | 10 | 77.00 | +----+----------+-----+-------+-------+ 6 rows in set (0.00 sec) mysql> select * from Hob; +----+--------------+ | id | hob_name | +----+--------------+ | 1 | 云计算 | | 2 | 大数据 | | 3 | 人工智能 | +----+--------------+ 3 rows in set (0.00 sec) mysql> select a.name,h.hob_name from accp a left join Hob h on a.hobby=h.id; +----------+--------------+ | name | hob_name | +----------+--------------+ | lisi | 云计算 | | wangwu | 大数据 | | zhangsan | 大数据 | | wangwu | 人工智能 | | zhaoliu | NULL | | tianqi | NULL | +----------+--------------+ 6 rows in set (0.01 sec) //左表列全显示,右边从表匹配到的蔡先生,匹配不到的nulll;
1.12.3: 右连接
-
右连接也被称为右外连接,在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反,它是以右表为基础表,用于接收右表中的所有行,并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录。
-
-
mysql> alter table accp modify name varchar(10); Query OK, 6 rows affected (0.06 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> desc accp; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | MUL | NULL | | | age | int(5) | NO | UNI | NULL | | | hobby | int(3) | NO | | NULL | | | score | decimal(5,2) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec) mysql> insert into accp (id,age,hobby,score) values (8,22,2,78); Query OK, 1 row affected (0.01 sec)
mysql> select a.name,h.hob_name from accp a right join Hob h on a.hobby=h.id; +----------+--------------+ | name | hob_name | +----------+--------------+ | wangwu | 大数据 | | lisi | 云计算 | | wangwu | 人工智能 | | zhangsan | 大数据 | | NULL | 大数据 | +----------+--------------+ //右表主表列全显示,左表匹配到的显示,匹配不到的null