Mysql 数据库 -------- SQL语句进阶查询 ------- 前部分

Mysql进阶查询

按 order by 关键字排序

• 使用 order by 语句来实现排序
• 排序可针对一个或多个字段
• asc ：升序（语法中默认排序方式）
• desc ：降序

order by：
语法结构1：
select 字段名1，字段名2... from 表名 order by 字段名1，字段名2... asc|desc;
 
 示例：
 select id from info order by desc；

语法结构2：
select 字段名1，字段名2... from 表名 where 字段名 >60 order by 字段名 desc，字段名 asc;

示例：
 select socre from info where socre >60 order by socre desc;

对 group by 结果进行分组

• 使用 group by 语句来实现分组
• 通常结合聚合函数一起使用
• 可以按一个或者多个字段对结果进行分组

group by：
语法结构1：
select 聚合函数(根据这个字段名),被统计字段名 from 表名 where 被统计字段名 >=70 group by 被统计字段名;

示例：
select count(name),socre from info where socre >=70 group by socre;

• group by 结合 order by

示例：
select count(name),socre from info where socre >=70 group by socre order by count(name) desc;

limit 限制结果条目

• 只返回 select 查询结果的第一行或前几行
• 使用 limit 语句限制条目

limit：
语法结构：
select 字段名1，字段名2.. from 表名 limit[位置偏移量，从0开始] number;                 ####number 为 返记录行的最大数目

示例：
select * from info limit 3;         ###查找表中第3行
select * from info limit 3,5;       ###从索引号0开始数3行也就是第四行，指定行数五行，包括该行

as 设置别名

• 使用 as 语句设置别名，关键字 as可以省略
• 设置别名时，保证不能与库中其他表或字段名冲突

as ：
结构语法1：给字段设置别名
select 字段名 as 新设的字段名,字段名 as 新设的字段名 from 表名;
示例：
select name as 姓名，socre as 成绩 from info;
select count(*) as cc from info;

语法结构2：给表设别名
select name 姓名,score  成绩 from info as i;
示例：
select name 姓名，score 成绩 from info as i；   ###原字段或表名在库内不会被改变；as 可以省略

• as 作为连接语句

示例：
create table xxx as select * from info;       ###创建xxx表，复制info的表结构
desc xxx;         ###xxx的表结构与info的表结构相同

通配符

• 用于替换字符串中的部分字符
• 通常配合 like 一起使用，并协同 where 完成查询
• 常用通配符
  • % 表示零个、一个或多个
  • _ 表示单个字符
• 常用于模糊查询（如 淘宝、各种商业平台）

语法结构：
select * from 表名 where 字段名 like '字符%';

示例：
select * from info where name like 'z%';      ###匹配多个带有 z 字的字段

select * from info where name like 'l_s_';       ###匹配一个带有l和s的字段

子查询

• 也称作 内查询 或 嵌套查询
• 先于主查询被执行，其结果将作为外层主查询的条件
• 在增删改查中都可以使用子查询
• 支持多层嵌套
• in 语句是用来判断某个值是否在给定的结果集中

语法结构：
select * from 表名 where 字段名1 in (select 字段名1 from 表名 where 字段名2 in (select 字段名 from 表名));

示例：
select * from info where id in (select id from tmp where name in (select name from aba));          ###嵌套查询，先读括号里的值作为结果，给外层作为条件，由内而外
括号中的字段类型要与外层的字段类型匹配，才能被执行，否则不会被执行！

select a.id from (select id,name from info) a;      ####这种嵌套也是可以的，不过得设置一个别名

• view 视图
  • 视图为数据库中的虚拟表，这张虚拟表中不包含任何数据，只是做了数据映射。（动态的保存一个结果集，但这个结果集中是不包含数据的，这个数据是存在于真实的表中）

示例：
create view v_score as select * from info where score >= 80;          ###v_score 表示 视图名；as 连接  ;  查询info表中成绩大于等于80的人连接新建的v_score视图表
select * from v_score;            ### 查看视图表
update into set score=77 where name='libai';              ### 修改info表中libai的成绩
select * from info;         ###查看 info表中 libai 成绩变成 77
select * from v_score;            ### 再次查看 视图表中 libai 的字段 被更新掉了
验证：视图的作用为动态的保存一个结果集，但这个结果集中是不包含数据的，如果条件不满足这张视图，则视图被更新掉，更改的记录在原表中，不会在视图表中，视图表只会记录满足条件的数据（条件：为 where 添加的条件）

NULL值

• 表示缺失值，与数字0 或 空白（spaces）是不同的
• 使用ls null 或 ls not null 进行判断
• null值和空值的区别：
  • 空值长度为0，不占空间；null 值长度为null，占空间
  • ls null 无法判断空值
  • 空值使用 “=”或 “<>”来处理 （<> 意思是不等于；’<>’ = ‘!=’）
  • count()计算时，null 值 会忽略，不被统计 ; ''空值 会被统计

示例：
update info set addr='nj' where score >=70;            ###将 字段addr 默认null值 修改为 nj
select * from info;
select count(addr) from info;         ###null 没有被加入统计

示例：
update info set addr='' where name='xiaoli';          ###将xiaoli 的addr 类型 null值修改为空值
select * from info;
select count(addr) from info;        ###空值加入了统计中

正则表达式

• 根据指定的匹配模式匹配记录中符合要求的特殊字符
• 使用 regexp 关键字指定 正则表达式
• 常用匹配模式：
  • ^ ：匹配开始字符
  • $ ：匹配结束字符
  • . ：匹配任意单个字符
  • *：匹配任意个前面的字符
  • +：匹配前面字符至少1次
  • p1|p2 ：匹配p1或p2
  • […] ：匹配字符集中的任意一个字符
  • [^…] ：匹配不在括号内的任意字符
  • a{n}：匹配a n次
  • a{n,m}:匹配a至少n次，最多m次

语法格式：
select * from info where name regexp '匹配模式';

运算符

• 用于对记录中的字段进行运算
• mysql 的运算符有四种：算术运算符、比较运算符、逻辑运算符、位运算符

算术运算符

• 加“+”、减“-”、乘“*”、除“/”、取余“%”

select 4+2,3-1,2*3,4/2,7%2;
在除法运算和求余数运算中，除数不能为0，若除数是0，返回的结果则为null。
如果有多个运算符，按照先乘除后加减的优先级进行运算，相同优先级的运算符没有先后顺序

create table rest as select 4+2,3-1,2*3,4/2,7%2;           ####处理的结果作为test的表结构
create view v_oot as select 4+2,3-1,2*3,4/2,7%2;           ####处理的结果生成 v_oot视图表

比较运算符

• =、 >、 <、 >=、 <=、 != 或 <> 、ls null 、ls not null

等于运算符

• 等号(=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回1，如果不相等则返回O。如果比较的两者有一个值是 NULL，则比较的结果就是NULL。其中字符的比较是根据ASCIl码来判断的，如果ASCIl码相等，则表示两个字符相同;如果ASCII 码不相等，则表示两个字符不相同。

示例：
select 2=4,2='2','e'='e',(2+2)=(3+1),'r'=null;

1、如果两者都是整数，则按照整数值进行比较
2、如果一个整数一个字符串，则会自动将字符串转换成数字再进行比较。
3、如果两者都是字符串，则按照字符串进行比较
4、如果两者中至少有一个值是null，则比较的结果是null


单个字符比 ASCll 码        0 :  48     A :  65    a :  97
select 'abc' = 'abc'      比字符串比的是内容 比的值为1

不等于运算符

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

示例：
select 'kgc'<>'bdqn',1<>2,3!=3,2.5!=2,null<>null;


比较原理：
select 'abc' < 'bcd'       比的是内容  1,成立，是或的关系
select 'abc' > 'bcd'       比的是内容  0,不成立，是且的关系（都要比，'abc'中a、b、c  都比  'bcd'中b、c、d 大）

select 'abc' < 'baa'       比的是内容  1,成立，是或的关系(只要比第一个：'baa'中b 比 'abc'中a 大，就不比了 )

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

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

比的是ASCII码表

ls null 、ls not null

• ls null 判断一个值是否为null，如果为null返回1，否则返回0
• ls not null 判断一个值是否不为null，如果不为null 返回1，否则返回0

示例：
select 2 ls null,'f' ls not null,null ls null;

between and

select 2 between 2 and 6      成立    2包含在两者范围内

least 、greatest （函数）

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

示例：
select least(10,20,30),greatest(10,20,30);        ###返回结果为 least：10   ；  greatest：30

select least(10.4,20,30),greatest(10,20,40.8);         ###类型不同也能查询；返回结果为 least：10.4   ；  greatest：40.8

select least(10.4,20,30,'a'),greatest(10,20,40.8,'a');       ### 返回结果为 least：0   ,不能被执行成功   ；  greatest：40.8

select least(10.4,20,30,null),greatest(10,20,40.8,null);         ### 返回值都为 null

总结：least 如果放入的是不同的数据类型（如 包含字符 和 数值 的混合摆放），其函数是不能被识别的；
     greatest 数据类型（字符和数值的混合）则屏蔽掉字符。其中一个值为null，返回值就位null

in 、notin

• in ：判断一个值是否在对应的列表中，如果是返回1，否则为0
• notin ：判断一个值是否不在对应的列表中 ，如果不是返回1，否则为0

示例：
select 'a' in ('a','b','c'),'a' not in ('a','b','c');          ###返回值  in ：为1，成立 ；not in ：0，不成立
select 97 in ('a','b','c'),'a' not in ('a','b','c');          ###返回值  in ：为0，不成立，不能与ASCII码相比 ；not in ：0，不成立

like 、 not like

• like ：用来匹配字符串，如果匹配成功则返回1，否则为0。支持两种通配符：’%‘用于匹配任意数目的字符，而’_'只能匹配一个字符
• not like ：和 like 相反，如果没有匹配成功则返回1，否则为0。

示例：
select 'libai' like 'li%';           ### 返回值为1
select 'libai' like 'li___';           ### 返回值为0
select 'libai' not like 'li___';           ###返回值为1

select * from info where name like 'li__';           ###用于模糊查询info表中name字段中 带有li字符后面跟三位 的人名

逻辑运算符

• 对应关系表

且       0&&0=0        1&&0=0      0&&1=0      1&&1=1           

或       0||0=0        1||0=1      0||1=1      1||1=1

异或      0^0=0        1^0=1       0^1=1       1^1=0

• 非关系

select not 0,!2,!(4-4);          ###返回值为 1 ；0 ； 1

• 且关系

select 2&&3,0&&1,0&&null,1and null;         ###返回值为 1 ；0 ； 0 ； null

• 或关系

select 2 or 3,'a' or 0;           ###返回值为 1；0
select 2 or 3,'a' || 0;           ###返回值为 1 ；a0
select 0 or null,1 or null;          ###返回值为 null ；1
select 0 || null,1 || null;           ###返回值为 null ；null
总结：或运算符中 ：建议使用 or ，不建议使用 || ， 结果不一样；  || 的意思是  把左右两边一起输出  ；    或运算返回值判断是 ：只要返回值为非零值，那就为成立，后面不用执行了，所以 or 才是最适合或关系运算的语句
select null or 1;       ###返回值为1
select null or 0;         ###返回值为 null ； null 为空，有长度、占空间 ；0为空值，长度为0，不占空间

• 异或关系

select 2 xor 3;         ###返回值为 0
select 0 xor 3,0 xor 0,0 xor null,1 xor null;              ###返回值为 1 ；0 ；null ；null
总结：异或       碰到null 则为null ；两个非零值 为零

位运算符（运维开发）

• 位运算符 ：对二进制数进行计算的运算符
• mysql 内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看
  

示例：
select 10 | 15;            ### 返回值为15
select 10 ^ 15;             ###返回值为 5
select 5&~1;           ####返回值为 4

• “<<” 左移位运算

select 10 << 3;         返回值为80

• “>>” 右移位运算

select 15 >> 2;          返回值为3

运算符的优先级

• 决定不同的运算符在计算过程中的先后顺序
• 优先级高的先运算，同级的按从左到右进行计算
• 可以使用 () 小括号来改变计算优先级
• ！的优先级最高，而 :=的优先级最低