linux MySQL高级SQL语句 | 进阶（二）| 详情详解 | 超详细

视图view

数据库中的虚拟表，这张虚拟表中不包含真实数据，只是一个动态映射
这张虚拟表能动态的保存结果集
因为视图和真实表之间是动态同步的关系，所以我们修改虚拟表的同时，真实数据也会收到影响
但是视图除了名字什么都没有，是一个投影，所以不占空间
我们只需要对其做权限的设置，就可保证其安全性
我们可以为视图定义展示的条件，为不同的人群展示不同的网内容

创建视图
create view v_score as select * from boos where score >=80;
show table status\G;
查看视图
select * from v_score;
修改原表数据
update  boos set score ='60' where name='wangwu';
查看视图
select * from v_score;

修改视图会影响表结构

NULL值

定义

通常使用 NULL 来表示缺失的值，也就是在表中改字段时没有值的。
如果在创建表时，限制某些字段不为空，则可以使用 NOT NULL 关键字，不使用则默认可以为空。
在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为NULL。
需要注意的是，NULL值与数字0或者空白(spaces)的字段是不同的，值为NUL的字段是没有值的。
在SQL语句中，使用 IS NULL 可以判断表内的某个字段是不是NULL值，相反的用 IS NOT NULL 可以判断不是NULL值。

NULL和空值的区别

空值长度为0，不占空间
NULL值的长度为null，占用空间
is null 无法判断空值
空值使用“=”或者“<>”来处理
count()计算时，NULL会被忽略，空值会加入计算

alter table boos add addr varchar(50);

update boos set addr='nj' where score >=70;
统计数量：检测null是否会加入统计中
select  count(addr) from boos;

将boos表中其中一项修改成一个空值；
update boos set addr='' where name='wangwu';

统计数量：检测null是否会加入统计中
selec  count(addr) from boos;

查询null值
select * from boos where is null;

查询不为空的值
select * from boos where is not null

正则表达式

MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串
MySQL 的正则表达式使用 regexp 这个关键字来指定正则表达式的匹配模式

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

1.以特定字符串开头的记录
select * from boos where name regexp '^li';
2.以特定字符串结尾的记录
select * from boos where name regexp 'u$';
3.以‘.’代替字符串中的任意一个字符的记录
select * from boos where name regexp 'l..i';
4.匹配前面字符串的任意多次
select * from boos where name regexp 'g*';
5.匹配前面字符串至少一次
select * from boos where name regexp 'g+';
6.匹配指定字符串
select * from boos where name regexp 'iu';
7.匹配包含或者关系的字符串
select * from boos where name regexp 'wu|is';
8.匹配指定字符串集中的任意一个
select * from boos where name regexp '[g,l]';
9.匹配不在括号中的任何字符
select * from boos where name regexp '[^lisi]';
10.匹配前面的字符串n次
select * from boos where name regexp 'o{2}';
11.匹配前面的字符串至少n次 只多m次
select * from boos where name regexp 'o{1，2}';

运算符

 +     加法
 -     减法
*      乘法 
/      除法 
 %     取余

除数不能除以0，否则返回null值
优先级，先乘除后加减，同级没有先后顺序

select 1+2,2-1,3*4,6&3,4/2;

create table cf select 1+2,2-1,3*4,6&3,4/2;

比较运算符

字符串的比较默认不区分大小写,可使用binary来区分
常用比较运算符(比较对象:数字,字符)

=  等于
！=或者<> 不等于
like 通用符匹配
>   大于
>=  大于等于
<   小于
<= 小于等于
is null  判断一个值是否为NULL
is not null  判断一个值是否不为NULL
between and  两者之间
createst    俩个或多个参数时返回最大值
least         俩个或多个参数时返回最小值
in      在集合中

等号(=)

用来判断数字、字符串和表达式是否相等的,如果相等则返回1,|如果不相等则返回0。如果比较的两者有一个值是NULL,则比较的结果就是NULL。

其中字符的比较是根据ASCII码来判断的,如果ASCII码相等,则表示两个字符相同;如果ASCII码不相等,则表示两个字符不相同。例如字符串(字母)比较: ('a' > 'b')其实比较的是底层ascll码

select 2=4,2='2','e'='e',(2+2)=(3+1),'4'=null;

从以上查询结果可以看出来:
如果两者都是整数,则按照整数值进行比较。如果一个整数一个字符串,则会自动将字符串转换为数字,再进行比``较。(在程序中,开发人员是不会把这两者放在一起做对比)
如果两者都是字符串,则按照字符串进行比较,
如果两者中至少有一个值是NULL,则比较的结果是NULL

不等于(!=或<>)

用于针对数字、字符串和表达式不相等的比较,如果不相等则返回1,如果相等则返回0,与等于(=)的返回值相反,同时不等于(!=,<>)无法用于判断是否为null

 select 'kgc'<>'bdpn',1<>2,3!=3,2.5!=2,null<>null;

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

大于(>)运算符用来判断左侧的操作数是否大于右侧的操作数,若大于返回1,否则返回0,同样不能用于判断NULL
小于(<)运算符用来判断左侧的操作数是否小于右侧的操作数,若小于返回1,否则返回0,同样不能用于判断NULL
大于等于(>=)判断左侧的操作数是否大于等于右侧的操作数,若大于等于返回1,否则返回0,不能用于判断NULL
小于等于(<=)判断左侧的操作数是否小于等于右侧的操作数,若小子等于返回1,否则返回0,不能用于判断NULL

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

两者之间（between…and…）

此较运算通常用于判断一个值是否落在某两个值之间。例如,判断某数字是否在另外两个数字之间,也可以判断某英文字,母是否在另外两个字母之间,具体操作,条件符合返回1,否则返回0

select 4 between 2 and 6,5 between 6 and 8,'c' between 'a' and 'f';

返回最小值/最大值 least greatest

当有两个或多个参数时,返回其中最大/最小值,如果一个为null,则返回为null

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

数字比较 按大小排列
字母比较，从a-b顺序,字母越前越‘小’

在/不在集合中（in ，not in ）

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

select 2 in (1,2,3,4,5),'c' not in ('a','b','c');

通用符匹配

LIKE用来匹配字符串,如果匹配成功则返回1,反之返回0.LIKE支持两种通配符:’%‘
用于匹配任意数目的字符,而’_‘只能匹配一个字符。NOT LIKE正好跟LIKE相反,如果没有匹配成功则返回1,反之返回0

 select  'bdgn' LIKE 'bdq_ ','kge' LIKE '%c', 'etc' NOT LIKE ' %th';

逻辑运算符

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

not 或 ！ 逻辑非
and 或 && 逻辑与
or 逻辑或
xor 逻辑异或

逻辑非（not 或 ！）

逻辑非将跟在他后面的值取反,如果not后面的操作数为0时,所得值为1:
如果操作数为非0时,所得值为0;
如果操作数为NULL时,所得值为NULL
小结;返回值为0, 1, null值(根据匹配条件判断为何值)

select not 2,!3,not 0,!(4-4);

逻辑与(AND或&)

AND 和 &&都是逻辑与运算符,具体语法规则为:
当所有操作数都为非零值并且不为NULL时,返回值为1
当一个或多个操作数为0时,返回值为0;操作数中有任何一个为NULL时,返回值为NULL

select 2 and 1,2 && 0 , 0 && null,2 and null;

and 和 && 作用相同
AND -1中没有0 或者NULL,所以返回值为1
AND 0中有操作数0,所以返回值为0;
AND NULL虽然有NULL,所以返回值为NULL
null 和 0 返回值为0

逻辑或(OR)

OR是逻辑或运算符,具体语法规则为:
当两个操作数都为非NULL值时,如果有任意一个操作数为非零值,则返回值为1,否则结果为0:
当有一个操作数为NULL时,如果另一个操作数为非零值,则返回值为1,否则结果为NULL
假如两个操作数均为NUL时,则返回值为NULL

select 2 or 3 ,0 or null,1 or 1,0 or 0;

OR -1 OR 0含有0,但同时包含有非0的值1和-1,所以返回结果为1;
OR 2中没有操作数0,所以返回结果为1
OR NULL虽然有NULL,但是有操作数1,所以返回结果为1;
OR NULL中没有非0值,并且有NULL,所以返回值为NULL
NULL OR NULL中只有NULL,所以返回值为NULL

逻辑异或

XOR表示逻辑异或,具体语法规则为:
当任意一个操作数为NULL时,返回值为NULL;
对于非NULL的操作数,如果两个操作数都是非 0值或者都是0值,则返回值为1;
如果一个为0值,另一个为非0值,返回值为1

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

位运算符

位运算符是在二进制数上进行计算的运算符。
位运算会先将操作数变成二进制制数进行位运算。
然后再将计算结果从二进制数变回十进制数。

&     按位与
|     按位或
^     按位异或
!     取反
<<    左移
>>    右移

按位与运算(&) ,是对应的二进制位都是1的,它们的运算结果为1,否则为0,所以10 & 15的结果为10.
select 10 & 15;

按位或运算(|) ,是对应的二进制位有一个或两个为1的,运算结果为1,否则为0, 所以101 15的结果为15

select 10 | 15;

按位异或运算(^) ,是对应的二进制位不相同时,运算结果1,否则为0,所以10^ 15的结果为5
select 10 ^ 15 ;

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

select 10 &~1;

优先级的顺序：

连接查询

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

create table test1 (a_id int(11) default null,a_name varchar(32) default null,a_level int(11) default null);


create table test2 (b_id int(11) default null,b_name varchar(32) default null,b_level int(11) defauult null);



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

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

内连接

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

select a.a_id,a.a_name,a.a_level from test1 as a inner join test2 b on a_id=b_id;
select a_id,a_name,a_level from test1 inner join test2 on a_id = b_id;

左连接

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

select * from test1 left  join test2 on test1.name=test2.name

左连接中左表的记录将会全部表示出来,而右表只会显示符合搜索条件的记录,右表记录不足的地方均为NULL.

右连接

右连接也被称为 右外连接，在 from 字句中 使用 right join 或者 right outer join关键字来表示，右连接跟左连接正好相反，他是以右表为基础表，用于接受右表中的所有行，并用这些记录与记录左表中的行进行匹配

select * from test1 right join test2 on a.name=b.name;

在右表连接的查询结果集中，除了符合匹配规则的行外，还包括右表中有但是左表中不匹配的行，这些记录在左表中以null 补足