数据库—mysql高级语句

一、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 1
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');      若中间有空格不会消除

Upper(x)将字符串x的所有字母变成大写字母,Lower(x)将字符串x的所有字母变成小写字母
4、
代码：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);