Mysql高阶SQL语句

一、Mysql进阶查询：
1、按关键字排序：
使用order by 语句来实现排序
排序可针对一个或多个字段
ASC：升序，（默认排序方式）
DESC:降序
Order by的语法结构：
单字段排序：

msyql > select id,name,level from player where level >=45 order by level desc(降序);
mysql> select name,score from info where score>=70 order by score desc;
mysql> select name,score from info where score>=70 order by score ;（升序）
多字段排序：
mysql> select id, name,score from info where score>=70 order by score desc,id asc;

2、对结果分组：
使用group by 语句来实现分组： 和count（列名)聚合函数，配合这使用
通常结合聚合函数一起使用
可以按一个或多个字段对结果进行分组
Group by的语法结构

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

3、限制结果条目：
只返回select 查询结果的第一行或前几行
使用limit语句限制条目 0 1 2 3 4 5 6 7 8 9 各行排序
limit语句：

mysql> select * from info limit 2,3;   （实际是从第3行开始数3行出来）

4、设置别名：
使用as语句设置别名，关键字as可省略
设置别名时，保证不能与库中其他表或字段名称冲突
别名的语法结构：

mysql> select count(*) as 数量 from info;
mysql> select info.name,Hob.hob_name from info inner join Hob on info.hobby=Hob.id;
mysql> select i.name,h.hob_name from info as i inner join Hob as h on i.hobby=h.id;
mysql> select i.name,h.hob_name from info i inner join Hob h on i.hobby=h.id;
mysql> select i.name 姓名,h.hob_name 兴趣爱好 from info i` inner join Hob h on i.hobby=h.id;
As 的用法：连接语句：
Create table tmp as select * from player;

5、通配符：
用于替换字符串中的部分字符：
通常配合like一起使用，并协同where完成查询
常用统配符：
% ：零个、一个或多个
_ : 单个

Select id,name,level from player where name like ‘%s’ (name列以s结尾的词)
select id,name,leevl from player where name like ‘_uess’;  (单个字符+uess)
mysql> select name,score from info where name like 'z%';
select id,name,leevl from player where name like ‘_es%’;

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

select name,level from player where id in (select id from player where level>=45);
mysql> select name,hobby from info where hobby in (select id from Hob where hob_name='云计算');     “ in可以换成= ”
update info set score=score+5 where scor e<80;
select name,score from info where exists (select id from Hob where hob_name=’大数据’)；                   后面的成立就执行前面的

7、NULL值：
表示缺失的值 没有值
与数字0或者空白是不同的
使用IS NULL或IS NOT NULL进行判断
NULL值和空值的区别：
空值长度为0，不占空间；NULL值的长度为NULL，占用空间
IS NULL无法判断空值
空值使用“=”或者“<>”来处理
Count()计算时，NULL会忽略不参加计算，空值会加入计算
不敲时，默认是NULL。
 

mysql > select * from num where name is not null; (将名字不为空NULL的输出，包括 )

8、正则表达式：
根据指定的匹配模式匹配记录中符合要求的特殊字符
使用REGEXP关键字指定匹配模式
常用匹配模式：

^:匹配开始字符
$:匹配结束字符
. :匹配任意单个字符
*：匹配任意个前面的字符
+：匹配前面字符至少一次
P1|p2匹配p1或p2
[…] 匹配字符集中的任意一个字符
[^…]匹配在中括号内的任意字符
{n}匹配前面的字符串n次
{n,m}匹配前 起前面的字符串至少n次，至少m次。

select * from info where name regexp ‘^wa’;
select * from info where name regexp ‘m$’;
select * from info where name regexp ‘an’;       必须要连接在一起是整体单位
select * from info where name regexp ‘zh|ti’;     只要匹配就输出   
select * from info where name regexp ‘oo*’;      至少一个o 
select * from info where name regexp ‘^[d-f]’;  

9、运算符：
用于对数据中字段值进行运算
字符串分类：
算术运算符
比较运算符
逻辑运算符
位运算符 （二进制综合运算）
1、算数运算符：

select 1+2,2*3,7%2,7/2(保留小数点后 位)

2、比较运算符：
IN：在集合中
LIKE:通配符匹配
IS NUll 判断一个值是否位NULL
IS NOT NULL 判断一个值是否不为NULL
BETWEEN AND 两者之间
GREATEST 两个或多个参数时返回最大值
LEAST 两个或多个参数时返回最小值
不等于运算符不能判断Null 不同：1，同：0；
 

 select least(10,20,30) ,least(‘a’,’b’,’c’);           10    a
 select greatest(10,20,30) ,greatest(‘a’,’b’,’c’);      30    c
 Selesct ‘bdqn’like ’bdq_’,’kgc’ like ‘%c’;’tom’ not ‘%j’;    左边是完整的，右边的模糊查询

10、逻辑运算符；
又称为布尔运算符
用来判断表达式的真假
常用的逻辑运算符：

且 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 2 and 3,0 and NULL,1 and NULL;            1 0 0 NULL
select 2 OR 3,4||0,0 OR NULL,1 OR NULL;          1 1 NULL 
select 2 XOR 3,4||0,0 XOR NULL,1 XOR NULL;      0 1 null  null

11、位运算符（二进制）：
<< 左移

右移

取反

12、连接查询

内查询：
Select i.name,h.hobby_name from info i inner join Hob h on i.hobby=h.id; 两张表的交际
外.左连接： 左为主表 主表全部显示，右表做辅助显示 没有的null
Select i.name,h.hobby_name from info i left join Hob h on i.hobby=h.id;
外.右连接： 右为主表 主表全部显示，右表做辅助显示 没有的null
Select i.name,h.hobby_name from info i right join Hob h on i.hobby=h.id;

13、数据库函数：
Mysql提供了实现各种功能的函数
常用的函数分类：
1、数学函数
2、聚合函数
3、字符串函数
4、日期时间函数
1、数学函数：

Abs(x):返回x的绝对值

mysql> select abs(-100);          》》100

Rand():返回0到1的随机数 取到0，取不到1

mysql> select rand();

Mod(x,y)返回x除以y以后的余数

mysql> select mod(10,3);     1

Power(x,y)返回x的y次方

mysql> select power(3,2);     9

Round(x)返回离x最近的整数

mysql> select round(1.49);      1      只看小数点后的第一位

Round(x,y)保留x的y位小数四舍五入的值

mysql> select round(1.49，1);          1.5
mysql> select round(3.147,2);           3.15

Sqrt(x)返回x的平方根

mysql> select sqrt(9);        3

Truncate(x,y)返回数字x截断为y位小数的值

mysql> select truncate(3.145263,3);      3.145  

Ceil(x)返回大于或等于x的最小整数

mysql> select ceil(1.4);                  2

Floor(x)返回小于或等于x的最大整数

mysql> select floor(1.4);               1

Greatest（x1,x2）返回集合中最大的值，Least(x1,x2)返回集合最小的值

mysql> select greatest(10,20,30),least(10,20,30);     30   10

2、字符串函数：

1、length(x)返回字符串x的长度

mysql> select length('abcd');     空格也算一个字符

2、Trim()返回去除指定格式的值

mysql> select trim(' abc ');    去除前后的空格，不能去除中间的空格

3、Concat(x,y)将提供的参数x和y拼接成一个字符串

mysql> select concat('abc','kgc');      若中间有空格不会消除

4.Upper(x)将字符串x的所有字母变成大写字母,Lower(x)将字符串x的所有字母变成小写字母

mysql> select upper('abcd'),lower('CNCJSD');

5、Left(x,y)返回字符串x的前y个字符

mysql> select left('csnakjvns',3);       csn

6、Right(x,y)返回字符串x的后y个字符

mysql> select right('csnakjvns',3);       vns
Select concat(left(‘vsknjvns’),3,right(‘csnijvnw’,3));

7、Repeat(x,y)将字符串x重复y次

mysql> select repeat('ac',2);    acac

8、Space(x)返回x个空格

mysql> select concat('a',space('3'),'b');     中间有3个空格

9、Replace(x,y,z)将字符串z代替字符串x中的字符串y

mysql> select replace('hello','ll','kgc');    hekgco

10、Strcmp(x,y)比较x和y，返回的值可以为-1，0，1

mysql> select strcmp(3,7);              -1

11、Substring(x,y,z)获取从字符串x中的第y个位置开始长度为z的字符串

mysql> select substring('cdcsvs',3,4);      csvs      123456计算位置

12、Reverse(x)将字符串x反转

mysql> select reverse('abc');    cba

3、日期时间函数：

1、curdate() 返回当前时间的年月日

select curdate();

2、Curtime()返回当前时间的时分秒

select curtime();

3、Now() 返回当前时间的日期和时间

select now();

4、Month(x)返回日期x中的月份值

select month(curdate());

5、Week（x）返回日期x是年度第几个星期

select month(2020-08-25);

6、Hour(x)返回x中的小时值

selecth hour(curtime());

7、Minute(x)返回x中的分种值

selecth minute(curtime());

8、Second（x）返回x中的秒值

selecth second(curtime());

9、Dayofweek(x)返回x是星期几，1是周天，2是周一

selecth dayofweek(curtime());

10、Dayofmonth(x)计算日期x是本月的第几天

selecth dayofmonth(curtime());

11、Dayofyear(x)计算x是本年的第几天

selecth dayofyear(curtime());

存储过程简介：
1、是一组为了完成特定功能的sql语句
2、比传统sql速度更快、执行效率更高
3、存储过程的优点：
执行一次后，会将生成的二进制代码驻留缓存区，提高执行效率
Sql语句加上控制语句的集合，灵活性高
在服务器端存储，客户端调用时，降低网络负载
可多次重复被调用，可随时修改，不影响客户端调用
可完成所有的数据库操作，也可控制数据库的信息访问权限
 

实验1：
mysql> delimiter $$
mysql> create procedure mysqlrole()
    -> begin
    -> select name,score from info;
    -> end$$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> show procedure status where db='school';
mysql> call mysqlrole();

实验2：
mysql> delimiter $$
mysql> create procedure myschool(in my_name varchar(10))
    -> begin
    -> select name,score from info where name=my_name;
    -> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call myschool('lisi');

实验3：
mysql> delimiter $$
mysql> create procedure myupdate(in my_score decimal(5,2))
    -> begin
    -> declare hob int(3);                  定义变量
    -> if my_score >=75 then               判断是否更改值
    -> set hob = 1;
    -> else
    -> set hob = 2;
    -> end if;
    -> update info set score=my_score,hobby=hob where name='zahngsan';
    -> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call myupdate(76);