MySQL高级SQL语句--MySQL进阶语句篇

按关键字排序：

按关键字排序：
●使用ORDER BY语句来实现排序

●排序可针对一个或多个字段

●ASC：升序，默认排序方式;数据从小到大排列

●DESC：降序;数据从大到小排列

●ORDER BY的语法结构

SELECT column,column2,...FROM table_name ORDER 	BY column1,column2,...
ASC|DESC;        ##order后面跟的是字段；不加默认是升序

按单字段排序

降序：
举例：将分数大于80分的进行降序排列显示出来
1.先查看表中数据；大于等于80分的为’zhangsan’;’wangwu’;’tom’;’daxiong’

2.将分数大于等于80分的进行降序排序

mysql> select name,score from cai where score>=80 order by score desc;

升序：

mysql> select name,score from cai where score>=80 order by score;

按多字段排序

格式：
主参考的字段放在前面；辅助参考字段放在后面，中间用逗号
举例：
1.将‘zhangsan’的分数也改为80

2.对大于等于80分的分数和id进行降序排序；发现分数都是80的情况下；id进行降序排序

mysql> select id,name,score from cai where score>=80 order by score desc,id desc;

对结果进行分组：

●使用GROUP BY语句来实现分组

●通常结合聚合函数一起使用

聚合函数：

• count（*）；“ * ” 代表的是字段名称
• 聚合函数会处理，最终只有一个值会出来

聚合函数的一些用法：
这里有一张表

count (*)  计数 

sum (*)  求和

avg(*)  平均值

max (*)  最大值

min(*)  最小值

●可以按照一个或多个字段对结果进行分组

●GROUP BY的语法结构

SELECT colmn_name,aggregate_function(column_name) FROM table_name
WHERE column_name operator value GROUP BY column_name;

GROUP BY分组：
举例：分数大于等于80分的有几个人

mysql> select count(name),score from cai where score>=80 group by score;

GROUP BY结合ORDER BY
将分数显示出来，并进行降序排序

mysql> select count(name),score from cai where score>=80 group by score order by score desc;

限制结果条目：

●查看区域范围:limit:

●只返回SELECT查询结果的第一行或前几行

●使用LIMIT语句限制条目

●LIMIT语法结构：

SELECT column1,column2,...FROM table_name LIMIT [offset,] number;

LIMIT限制结果条数
举例：显示cai表中的前三行信息

mysql> select * from cai limit 3;

先进行降序排序，在显示；可以搭配一起使用

不从第一条开始取值：
举例：2：代表的是索引；从0开始算所以id对应的是3；3：计算3行，包括索引2自己本身

mysql> select * from cai limit 2,3;

设置别名：

●使用AS语句设置别名，关键字AS可省略

●设置别名时，保证不能与库中其他表或字段名称冲突

●别名的语法结构：

AS的用法

mysql> select count(*)  as 数量 from cai;

设置别名
对表cai和hob分别设置别名；cai–c hob–h

mysql> select c.name,h.name from cai as c inner join hob as h on c.hobby=h.id;

可以进行优化处理：不写as

mysql> select c.name,h.name from cai c inner join hob h on c.hobby=h.id;

还可以既对表进行别名又对列进行别名

mysql> select c.name 姓名,h.name 兴趣爱好 from cai c inner join hob h on c.hobby=h.id;

as作为连接语句
创建test表；内容为cai表的内容

mysql> create table test as select * from cai;

通配符：

●用于替换字符串中的部分字符

●通常配合LIKE一起使用，并协同WHERE完成查询

●常用的通配符：

• %表示零个，一个或多个
• _代表单个字符

通配符%的用法

匹配以z为开头中间是任意字符，like一定要加

mysql> select name,score from test where name like 'z%';

通配符_的用法：

匹配以z开头后面有6个字符
中间的-代表有6个-
mysql> select name,score from test where name like ‘z______’;

两者可以结合一起使用：

匹配‘ax’前面没有字符；后面任意字符的
mysql> select name,score from test where name like ‘_ax%’;

子查询：

●也称作内查询或者嵌套查询

●先于主查询被执行，其结果将作为外层主查询的条件，括号里面的是子语句；作为外部查找的条件

●在增删改查中都可以使用子查询

●支持多层嵌套

●IN语句是用来判断某个值是否在给定的结果集中

子查询用法

Exists相当于是if；代表后面的条件存在；前面的的语句才会被执行（只支持DQL语句select）
举例：将cai表中的name和score显示出来；判断条件为hob表中的name字段中包含游泳

mysql> select name,score from cai where exists (select id from hob where name=' 游泳');

反之：要是后面的条件不成立；前面的语句就不会执行
因为后面的判断条件不成立；所以前面的也不会执行

mysql> select name,score from cai where exists (select id from hob where name=' 听音乐');

示例：
查询兴趣是打篮球的人；

mysql> select name,hobby from cai where hobby=(select id from hob where name='打篮球');

查询兴趣不是打篮球的人

mysql> select name,hobby from cai where hobby!=(select id from hob where name=' 打篮球');

NULL值：

●表示缺失的值

●与数字0或者空白（spaces）是不同的，表示缺失的值

●使用IS NULL或IS NOT NULL进行判断

●NULL值和空值的区别：

• 空值长度为0，不占空间；NULL值得长度为NULL，占用空间
• IS NULL无法判断空值
• 空值使用“=”或者“< >”来处理
• COUNT()计算时，NULL会忽略，空值会加入计算

NULL的用法

查询字段为null的值的记录
示例：
1.先查看hob表结构发现name的字段是允许为空的

2.这时往表中插入数据，不给name字段设置数值

3.查询字段为null的值的记录

mysql> select * from hob where name is null;

查询字段的不为null的值的记录：

mysql> select * from hob where name is not null;

验证count计算时,null会被忽略：

验证空值会被加入计算
1.先在表中插入一个空值

2.再次统计name验证空值会被加入运算

正则表达式：

●根据指定的匹配模式匹配记录中符合要求的特殊字段

●使用REGEXP关键字指定匹配模式，所以REGEXP一定要加

●常用的匹配模式：

示例

一.匹配文本开始的字符：
匹配cai表中name字段以zh开头的字串符

mysql> select * from cai where name regexp '^zh';

二：匹配文本的结束字符
匹配cai表中name字段以u结尾的字串符

mysql> select * from cai where name regexp 'u$';

三．匹配任何单个字符
匹配cai表中name字段中zha和liu中间有一个字符的字符串

mysql> select * from cai where name regexp 'zha.liu';

四．匹配零个或多个在它前面的字符
匹配hob表中name字段中包含三个或者更多个连续的 o 的 字段

mysql> select id,name from hob where name regexp 'oooo*';

五．匹配前面的字符 1 次或多次
匹配hob表中name字段中包含五个或者更多个连续的 o 的 字段

mysql> select id,name from hob where name regexp 'ooooo+';

六．匹配包含指定的字符串
匹配cai表中name字段中tom的字符串

mysql> select * from cai where name regexp 'tom';

七．匹配 p1 或 p2
匹配cai表中name字段中wa或者zh

mysql> select * from cai where name regexp 'wa|zh';

八．匹配字符集合中的任意一个字符
匹配cai表中name字段中包含z a o 的字符

mysql> select * from cai where name regexp '[zao]';

九．匹配不在括号中的任何字符
匹配hob表中name字段中不包含o的字符

mysql> select * from hob where name regexp '[^o]';

十．匹配前面的字符串 n 次
匹配hob表中name字段中包含两个o的字符

mysql> select * from hob where name regexp 'o{2}';

十一．匹配前面的字符串至少 n 次，至多m 次
匹配cai表中name字段中om字符串至少1次，至多3次

mysql> select * from cai where name regexp 'om{1,3}';

匹配cai表中查询包含以 d-z之间字母开头的 name 字段

mysql> select * from cai where name regexp '^[d-z]';

运算符：

●用于对记录中的字段值进行运算

●运算符分类

• 算数运算符
• 比较运算符
• 逻辑运算符
• 位运算符，位运算符：二进制运算符

算数运算符

MySQL支持的算数运算符

举例：
计算1+2；2-1；2*3；4/2；7%2的值，显示出来

mysql> select 1+2 as addition, 2-1 as subtraction, 2*3 as multiplication, 4/2 as division, 7%2 as remainder;

除法会保留小数点后面的位置

比较运算符

比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出 表中有哪些记录是符合条件的，如果比较的结果为真则返回 1，如果为假则返回 0，比较的结果如果不确定则返回 NULL。

比较运算符的用法和示例

等于运算符

等号（=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1，如果不相等则返回 0。如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同
ASCILL表：
●0对应的是48，后面1-9就是一次往后加
●A=65 后面B-Z就是一次往后加
●a=97 后面b-z就是一次往后加
示例：

mysql> select 2=4,2='2','e'='e',(2+2)=(3+1),'r'=NULL;  

其中字符串为null赋予给’r’；所以返回的是null

总结：
●如果两者都是整数，则按照整数值进行比较。

●如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。

●如果两者都是字符串，则按照字符串进行比较。

●如果两者中至少有一个值是 NULL，则比较的结果是 NULL

不等于运算符
不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL。例如，关于数字、字符串和表达式的不等于运算符的使用
示例：

mysql> select 'kgc'< >'bpqn',1< >2,3!=3,2.5!=2,NULL< >NULL;

大于、大于等于、小于、小于等于运算符

• 大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返回 0，同样不能用于判断 NULL。
• 小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回 0，同样不能用于判断 NULL。
• 大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断 NULL。
• 小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断 NULL。

示例：

mysql> select 5>4,'a'>'b',2>=3,(2+3)>=(1+2),4.4<3,1<2,'x'<='y',5<=5.5,'u'>=NULL;

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;

IS NULL 和 IS NOT NULL 一个判断为空，另一个判断不为空，只是有无 NOT 这个关键字的区别，同时返回值不同

BETWEEN AND
BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间
举例：4在2和6之间；5在6和8之间；c在a和f之间

mysql> select 4 BETWEEN 2 AND 6,5 BETWEEN 6 AND 8,'c' BETWEEN 'a' AND 'f';

LEAST、GREATEST：
●LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL。

● GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为 NULL， 则返回结果就为 NULL
示例：

mysql> SELECT least(1,2,3),least('a','b','c'),greatest(1,2,3),greatest('a','b','c');

IN、NOT IN
●IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0。

●NOT IN 判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0。

示例：2处于1 2 3 4 5之间；c不在a b c之间

mysql> SELECT 2 in (1,2,3,4,5),'c' not in ('a','b','c');

LIKE、NOT LIKE
LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回 0。LIKE 支持两种通配符：’%’ 用于匹配任意数目的字符，而’_’只能匹配一个字符。NOT LIKE 正好跟 LIKE 相反，如果没有匹配成功则返回 1，反之返回 0。模糊的匹配写在右边

示例：

mysql> SELECT 'bdqn' LIKE 'bdq_','kgc' LIKE '%c','etc' NOT LIKE '%th';

逻辑运算符：

●又被称为布尔运算符

●用来判断表达式的真假

●逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或！表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL。

逻辑运算符的用法

逻辑非
逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或！表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL

举例：2和1是非0值所以返回的是0；0返回的是1；4-4=0；所以返回的是0

mysql> select not 2,!1,not 0,!(4-4);

逻辑与：
逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0。逻辑与使用 AND 或者&&表示。例如，对非 0 值、0 值和 NULL 值分别作逻辑与运算
示例：2和3是非0值；所以返回的是1；4是非0值和0返回的是0；0和null返回的是0；非0值和null返回的是null

示例：

mysql> SELECT 2 AND 3,4 && 0,0 && NULL,1 AND NULL;

逻辑或
逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL 值时，返回 1，否则返回0。逻辑或通常使用 OR 或者||来表示。
示例：2或3出来的非0值；4或0出来的还是非0值（里面有一个非0值，出来的就是非0值）；0或null出来的是null；1或null出来的是null，格式要用or

示例：

mysql> SELECT 2 OR 3,4 OR 0,0 OR NULL,1 || NULL; 

逻辑异或
两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0， 另一个为非 0，则返回结果为 1；当任意一个值为 NULL 时，返回值为 NULL。例如，对非0 值、0 值和 NULL 值分别作逻辑异或运算，异或里面0或者非0值碰到null都是为null

示例：

mysql> SELECT 2 XOR 3,0 XOR 0,0 XOR 5,1 XOR NULL,NULL XOR NULL;

位运算符：

●位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看与运算

● 位运算符：

示例：对数字进行按位与、或和取反运算

mysql> SELECT 10 & 15, 10 | 15, 10 ^ 15, 5 &~1;

首先10 转换为二进制数是 1010, 15 转换为二进制数是 1111。
1.按位与运算（&），是对应的二进制位都是 1 的，它们的运算结果为 1，否则为 0，所以 10 & 15 的结果为 10。

2.按位或运算（|），是对应的二进制位有一个或两个为 1 的，运算结果为 1，否则为 0， 所以 10 | 15 的结果为 15。

3.按位异或运算（^），是对应的二进制位不相同时，运算结果 1，否则为 0，所以 10 ^ 15的结果为 5。

4.按位取反（~），是对应的二进制数逐位反转，即 1 取反后变为 0, 0 取反后变为 1。数字 1 的二进制是 0001，取反后变为 1110, 数字 5 的二进制是 0101，将 1110 和 0101进行求与操作，其结果是二进制的 0100，转换为十进制就是 4。

对数字进行左移或右移的运算
举例：数字进行左移或右移的运算

mysql> SELECT 1<<2, 2<<2,10>>2,15>>2;

●左移或右移运算符，都是将数转换为二进制后，然后在左移或右移指定的位数，超出的位数将被移除并丢弃，空出来的位置用 0 补齐。例如，“2<<2”将数字 2 的二进制数 0010， 向左移动两位后变成 10，右侧用 00 补齐，最终变为二进制的 1000，转换为十进制是 8。“15>>2”将数字 15 转换为二进制是 1111，向右移动两位，右侧的两位 11 被丢弃，变为 11， 左侧用 00 补齐，最终变为二进制的 0011，转换为十进制就是 3

运算符的优先级：

连接查询：

MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择 性的连接到选定的主表结果集上。使用较多的连接查询包括：内连接、左连接和右连接

内连接

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件

语法：

SELECT column_name(s)FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

举例：查看cai表中的name字段和hob表的name字段；其中cai表中的hobby字段对应的是hob表的id字段

mysql> select c.name,h.name from cai c inner join hob h on c.hobby=h.id;

外连接

MySQL 除了内连接，还可以使用外连接。区别于 MySQL 外连接是将表分为基础表和参考表，再依据基础表返回满足条件或不满足条件的记录。外连接按照连接时表的顺序来分， 有左连接和右连接之分

左连接

左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行；左连接 ----主表放在左边，主表内容都显示出来了，从表对应着主表去匹配，匹配上就显示

示例：

mysql> select c.name,h.name from cai c left join hob h on c.hobby=h.id;

右连接

右连接也被称为右外连接，在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录；右连接 ----主表放在右边
示例：

mysql> select c.name,h.name from cai c right join hob h on c.hobby=h.id;