MySQL高阶语句（二）

实验准备

建表info2

create table info2 (id int,name varchar(10) primary key not null ,score decimal(5,2),address varchar(20),hobbid int(5));

insert into info2 values(1,'liuyi',80,'beijing',2);
insert into info2 values(2,'wangwu',90,'shengzheng',2);
insert into info2 values(3,'lisi',60,'shanghai',4);
insert into info2 values(4,'tianqi',99,'hangzhou',5);
insert into info2 values(5,'jiaoshou',98,'laowo',3);
insert into info2 values(6,'hanmeimei',10,'nanjing',3);
insert into info2 values(7,'lilei',11,'nanjing',5);
insert into info2 values(10,'liujie',100,'guangzhou',5);
mysql> select * from info2;


mysql> select * from info2;
+------+-----------+--------+------------+--------+
| id   | name      | score  | address    | hobbid |
+------+-----------+--------+------------+--------+
|    6 | hanmeimei |  10.00 | nanjing    |      3 |
|    5 | jiaoshou  |  98.00 | laowo      |      3 |
|    7 | lilei     |  11.00 | nanjing    |      5 |
|    3 | lisi      |  60.00 | shanghai   |      4 |
|   10 | liujie    | 100.00 | guangzhou  |      5 |
|    1 | liuyi     |  80.00 | beijing    |      2 |
|    4 | tianqi    |  99.00 | hangzhou   |      5 |
|    2 | wangwu    |  90.00 | shengzheng |      2 |
+------+-----------+--------+------------+--------+
8 rows in set (0.00 sec)

一、正则匹配

regexp
匹配	描述
^		匹配文本的开始字符
$		匹配文本的结束字符
.		匹配任何单个字符
*		匹配零个或多个在它前面的字符
%		匹配所有
+		匹配前面的字符 1 次或多次
?
字符串	匹配包含指定的字符串
p1|p2	匹配 p1 或 p2
[…]		匹配字符集合中的任意一个字符
[^…]	匹配不在括号中的任何字符
{n}		匹配前面的字符串 n 次
{n,m}	匹配前面的字符串至少 n 次，至多 m 次

select id,name from info2 where name regexp '^liu';    #liu开头

select id,name from info2 where name regexp 'i$';     #i结尾

mysql> select id,name from info2 where name regexp 'an';   #查询名字中包含an的学生信息

select id,name from info2 where name regexp 'li.i';   #li开头，i结尾，中间不知道是一个什么字符的学生信息

select id,name from info2 where name regexp 'an|zh';   #查询名字中包含an或者zh的学生信息

select id,name from info2 where name regexp 'ang*'; #查询名字中有an,g可有可无的学生信息

select id,name from info2 where name regexp 'ang+';   #查询名字中含有an，g至少出现一次的学生信息

 select id,name from info where name regexp '^[s-x]';   #查询名字以s-x开头的学生信息

select id,name from info2 where name regexp '[^liujie]';   #查询名字不是liujie的学生信息,实际上假如名称想lilei这样每个字都在这个范围内，也会被选择
+------+-----------+
| id   | name      |
+------+-----------+
|    6 | hanmeimei |
|    5 | jiaoshou  |
|    3 | lisi      |
|    1 | liuyi     |
|    4 | tianqi    |
|    2 | wangwu    |
+------+-----------+
6 rows in set (0.00 sec)


select id,name from info where name regexp '^[^czx]';    #查询学生名字不以czx各字母开头的学生信息

二、运算符

MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别 是：算术运算符、比较运算符、逻辑运算符和位运算符

1、算术运算符

以 SELECT 命令来实现最基础的加减乘除运算，MySQL 支持使用的算术运算符，如表所示：

运算符	描述
+		加法
-		减法
*		乘法
/		除法
%		取余

在除法运算和求余数运算中，除数不能为 0，若除数是 0，返回的结果则为 NULL。需 要注意的是，如果有多个运算符，按照先乘除后加减的优先级进行运算，相同优先级的运算 符没有先后顺序

 select 1+2,2-1,3*4,4/2,5%2;
+-----+-----+-----+--------+------+
| 1+2 | 2-1 | 3*4 | 4/2    | 5%2  |
+-----+-----+-----+--------+------+
|   3 |   1 |  12 | 2.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

create table mt as select 1+2,2-1,3*2,4/2,5%2;  #将结果输出为表

2、比较运算符

比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出 表中有哪些记录是符合条件的，如果比较的结果(以布尔值的方式进行返回判断)为真则返回 1，如果为假则返回 0，比较的结果如果不确定则返回 NULL。其中字符串在进行比较的时候默认是不区分大小写的，如果要区分大小写可以通过 binary关键字来实现

运算符			描述
=				等于
>				大于
<				小于
>=				大于等于
<=				小于等于
!=或<>			不等于
is null			判断一个值是否为 NULL
IS NOT NULL		判断一个值是否不为 NULL
BETWEEN AND		两者之间
IN				在集合中
LIKE			通配符匹配
GREATEST		两个或多个参数时返回最大值
LEAST			两个或多个参数时返回最小值
REGEXP			正则表达式


##等号（=）	是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1，如果不相等则返回 0。如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同
例如字符串（字母）比较：（'a'>'b'）其实比较的就是底层的ASCII码
需要关注的是ascii码有：a、A、0（97、65、48）
那么：
如果比较的是多字符串，如:'abc'='acb',如何比较（字符个数、字符顺序）
如果比较的是多字符，如：'abc' <'baa' 如何比较
与linux返回值表达相反，linux 中运行正常返回值是0，运行异常返回值是非0

mysql> select 2=4,2=2,'e'='e','r'=null;  #字母要加单引号
+-----+-----+---------+----------+
| 2=4 | 2=2 | 'e'='e' | 'r'=null |
+-----+-----+---------+----------+
|   0 |   1 |       1 |     NULL |
+-----+-----+---------+----------+
1 row in set (0.00 sec)
① 如果两者都是整数，则按照整数值进行比较。
② 如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。（在程序中，一般是不会吧这两者进行相比较的）
③ 如果两者都是字符串，则按照字符串进行比较。
④ 如果两者中至少有一个值是 NULL，则比较的结果是 NULL。

##不等于（<>,!=）
不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。 如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需要注意的是不 等于运算符不能用于判断 NULL
select 'abc'<>'cba',2<>2,3!=2,null<>null;  #出现null比较就会是null
+--------------+------+------+------------+
| 'abc'<>'cba' | 2<>2 | 3!=2 | null<>null |
+--------------+------+------+------------+
|            1 |    0 |    1 |       NULL |
+--------------+------+------+------------+
1 row in set (0.00 sec)

BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。包含临界值，即是大于等于和小于等于的
例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间

mysql> select 4 between 2 and 5,'c' between 'a' and 'b';
+-------------------+-------------------------+
| 4 between 2 and 5 | 'c' between 'a' and 'b' |
+-------------------+-------------------------+
|                 1 |                       0 |
+-------------------+-------------------------+
1 row in set (0.00 sec)

least 和greatest（取最小值、取最大值）
LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则 返回结果就为 NULL。

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

若要判断一组数字或字母中哪个最小、哪个最大，可以通过使用 LEAST 和 GREATEST 来实现

mysql> select least(1,2,3),greatest(1,2,3),least('a','b','c'),greatest('a','b','c');
+--------------+-----------------+--------------------+-----------------------+
| least(1,2,3) | greatest(1,2,3) | least('a','b','c') | greatest('a','b','c5') |
+--------------+-----------------+--------------------+-----------------------+
|            1 |               3 | a                  | c                     |
+--------------+-----------------+--------------------+-----------------------+
1 row in set (0.01 sec)

IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0。
NOT IN 判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0
mysql> select 1 in (1,2,3), 2 not in ('a','b','c');
+--------------+------------------------+
| 1 in (1,2,3) | 2 not in ('a','b','c') |
+--------------+------------------------+
|            1 |                      1 |
+--------------+------------------------+
1 row in set, 3 warnings (0.00 sec)

LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回 0
LIKE 支持两种通配符：’%’ 用于匹配任意数目的字符（*匹配的是前面一个字符），而
’_’只能匹配一个字符。
NOT LIKE 正好跟 LIKE 相反，如果没有匹配成功则返回 1，反之返回 0。

mysql> select 'abc' like 'ab%','abc' like '_bc','abc' not like 'a%';
+------------------+------------------+---------------------+
| 'abc' like 'ab%' | 'abc' like '_bc' | 'abc' not like 'a%' |
+------------------+------------------+---------------------+
|                1 |                1 |                   0 |
+------------------+------------------+---------------------+
1 row in set (0.00 sec)

3、逻辑运算符(布尔值)

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1，否则 返回 0，真和假也可以用 TRUE 和 FALSE 表示。MySQL 中支持使用的逻辑运算符有四种， 具体如表所示。

运算符 描述
not 或 ! 逻辑非
and 或 && 逻辑与
or 逻辑或
xor 逻辑异或

（1）逻辑非

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

mysql> select not 2,!3,not 0, ! null;
+-------+----+-------+--------+
| not 2 | !3 | not 0 | ! null |
+-------+----+-------+--------+
|     0 |  0 |     1 |   NULL |
+-------+----+-------+--------+
1 row in set (0.00 sec)

#总结：变非零值为0，变0为1,只要有null依旧为null

（2）逻辑与(and)

当所有的操作数都为非0值且不为null时，返回值为1，否则为0，有null则返回null
（null与0比较特殊）
逻辑与使用 AND 或者&&表示

mysql> select 2 and 3,4 && 0,4 && null,0 and null,null and null;
+---------+--------+-----------+------------+---------------+
| 2 and 3 | 4 && 0 | 4 && null | 0 and null | null and null |
+---------+--------+-----------+------------+---------------+
|       1 |      0 |      NULL |          0 |          NULL |
+---------+--------+-----------+------------+---------------+
1 row in set (0.00 sec)

#总结：逻辑and，两两比较，都为非零时为1，只要有一个零为0，只要有一个null时，为null

（3）逻辑或（or）

逻辑或通常使用 OR
逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL 值时，返回 1，否则返回 0。
当有一个操作数为null时，如果另一个操作数为非0值，则返回值为1，否则为null
如两个操作数均为null，则返回值为null

使用或运算符or 进行逻辑判断，运行结果如果如下：

mysql> select 2 or 3,2 or 0, 2 or null, 0 or 0,null or null,0 or null;
+--------+--------+-----------+--------+--------------+-----------+
| 2 or 3 | 2 or 0 | 2 or null | 0 or 0 | null or null | 0 or null |
+--------+--------+-----------+--------+--------------+-----------+
|      1 |      1 |         1 |      0 |         NULL |      NULL |
+--------+--------+-----------+--------+--------------+-----------+
1 row in set (0.00 sec)


mysql> select 0 or null or 2;
+----------------+
| 0 or null or 2 |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

总结：只要存在非零值则为1，0和null为null，0 or 0 为0

（4）逻辑异或（xor）

两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；
如果一个为 0， 另一个为非 0，则返回结果为 1；
当任意一个值为 NULL 时，返回值为 NULL

示例：

mysql> select 2 xor 3,1 xor 0,0 xor null,null xor null;

+---------+---------+------------+---------------+
| 2 xor 3 | 1 xor 0 | 0 xor null | null xor null |
+---------+---------+------------+---------------+
|       0 |       1 |       NULL |          NULL |
+---------+---------+------------+---------------+
1 row in set (0.00 sec)

总结：两者一个为0一个为非0，即相异时，返回值1，反正为0，有null的为null

4、位运算符（&，|，^，~）

位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式（1010 1111），然后进行位运算，最后在将计算结果
从二进制变回到十进制格式，方便用户查 看。MySQL 支持 6 种位运算符，具体如表所示。

位运算符	描述
&			按位与
|			按位或
~			按位取反
^			按位异或
<<			按位左移
>>			按位右移

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)

10 转换为二进制数是 1010, 15 转换为二进制数是 1111
按位与运算（&），是对应的二进制位都是 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

总结：总换为二进制后，&与运算，位数对齐，都为1时返回1，否则为零；|或运算，位数对齐是只要有一个为1这返回1；^异或运算，数相异时返回1；~按位取反，二进制下（至少按四位）0变1，1变0,如5&~1,1二进制后先取反，再与5进行与计算。

以上不管哪种运算符，在使用过程中都有优先级问题。运算符的优先级决定了不同的运 算符在计算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同， MySQL 会按照顺序从左到右依次进行计算，优先级如下表所示：

优先级	运算符
1		！
2		~
3		^
4		*、/、%
5		+,-
6		>>,<<
7		&
8		|
9		=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
10		BETWEEN,CASE,WHEN,THEN,ELSE
11		NOT
12		&&,AND
13		||,OR,XOR
14		:=

三、连接查询

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

模板:
create table test1 (
id int(11) default null,
name varchar(32) default null,
level int(11) default null);

create table test2 (
id int(11) default null,
name varchar(32) default null,
level int(11) default null);

insert into test1 values (1,'aaaa',10);
insert into test1 values (2,'bbbb',20);
insert into test1 values (3,'cccc',30);
insert into test1 values (4,'dddd',40);

insert into test2 values (2,'bbbb',20);
insert into test2 values (3,'cccc',30);
insert into test2 values (5,'eeee',50);
insert into test2 values (6,'ffff',60);

1、内连接

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件，内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用 关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表

（1）语法
select 表一_name(s) from 表一 inner join 表二 on table1.column_name = table2.column_name;

mysql> select * from test1;
+------+--------+---------+
|   id |   name |   level |
+------+--------+---------+
|    1 | aaaa   |      10 |
|    2 | bbbb   |      20 |
|    3 | cccc   |      30 |
|    4 | dddd   |      40 |
+------+--------+---------+
4 rows in set (0.00 sec)

mysql> select * from test2;
+------+--------+---------+
|  id |    name |   level |
+------+--------+---------+
|    2 | bbbb   |      20 |
|    3 | cccc   |      30 |
|    5 | eeee   |      50 |
|    6 | ffff   |      60 |
+------+--------+---------+
4 rows in set (0.00 sec)

 select test1.id,test1.name from test1 inner join test2 on test1.name=test2.name;
+------+------+
| id   | name |
+------+------+
|    2 | bbbb |
|    3 | cccc |
+------+------+

总结：内连查询：通过inner join 的方式将两张表，on指定的相同字段的记录行输出出来，test1.id,test1.name的test1不能省略

2、左连接

左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参 考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。

select * from test1 left join test2 on test1.name=test2.name;
+------+------+-------+------+------+-------+
| id   | name | level | id   | name | level |
+------+------+-------+------+------+-------+
|    2 | bbbb |    20 |    2 | bbbb |    20 |
|    3 | cccc |    30 |    3 | cccc |    30 |
|    1 | aaaa |    10 | NULL | NULL |  NULL |
|    4 | dddd |    40 | NULL | NULL |  NULL |
+------+------+-------+------+------+-------+
4 rows in set (0.00 sec)

总结：左连接显示左表的全部，右边符合条件的才显示，否则为null

3、右连接

右连接也被称为右外连接，在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配

mysql> select * from test1 right join test2 on test1.name=test2.name;
+------+------+-------+------+------+-------+
| id   | name | level | id   | name | level |
+------+------+-------+------+------+-------+
|    2 | bbbb |    20 |    2 | bbbb |    20 |
|    3 | cccc |    30 |    3 | cccc |    30 |
| NULL | NULL |  NULL |    5 | eeee |    50 |
| NULL | NULL |  NULL |    6 | ffff |    60 |
+------+------+-------+------+------+-------+
4 rows in set (0.00 sec)

总结：右连接显示右表的全部，左边符合条件的才显示，否则为null

四、存储过程

存储过程：存储过程多用于软件开发方向

防止代码在网络传输过程中被截获，做了安全性保障

原始状态：代码中需要嵌入sql语句：通过连接驱动（java就是jdbc）————把sql语句作为参数，传递给mysql数据库进行执行。

存储过程是写在数据库中，并不是程序中。

程序是通过调用存储过程名称去触发操作，（类似shell中的函数），

优点：代码量优化，减少代码量，

​ 传输安全，隐藏sql语句

##创建存储过程
DELIMITER $$          #将语句的结束符号从分号;临时改为两个$$(可以自定义)
CREATE PROCEDURE Proc ()       #创建存储过程,过程名为Proc，不带参数，括号前有空格
->BEGIN      #过程体以关键宁BEGIN开始，无分号
-> select * from store_Info;    #过程体语句，带分号
->END $$       #过程体以关键字END 结束,有的像eof吧，无分号
DELIMITER ;     #将语句的结束符号恢复为分号

##调用存储过程##
CALL Proc;

查看存储过程#
show create procedure 库名.过程名\G;

mysql> delimiter //
mysql> create procedure x1 ()
    -> begin
    -> select * from info;
    -> end //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call x1;
+----+-------+-----+---------+
| id | name  | age | hobby   |
+----+-------+-----+---------+
|  1 | user1 | 20  | running |
|  2 | user2 | 30  | singing |
+----+-------+-----+---------+
2 rows in set (0.00 sec)