MySQL数据库系列(八)-------高级语句一

本是古典何须时尚 2023-10-01 09:01 30阅读 0赞

**目录**

一、SQL高级语句

1.导入文件至数据库

3. distinct

4.where

5.and;or

6.in

7.between

8.like 通配符

9. order by

10.函数

10.1数学函数

10.3字符串函数

11.group by

12.having

13.别名

14.连接查询

1）inner join(等值相连)

2）left join(左联接)

3）right join(右联接)

15.子查询

16.EXISTS

--------------------

# **一、SQL高级语句** #

##  **1.导入文件至数据库** ##

#进入class数据库
    use class
    
    #查看从class里面表有多少
    show tables;
    
    #把text.sql导入到class数据库中  source /usr/local/mysql/backup/text.sql;  
    source /usr/local/mysql/backup/text.sql;
    
    #查看 students表
    select * from students;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56ul5bCP57qv_size_20_color_FFFFFF_t_70_g_se_x_16][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56ul5bCP57qv_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

示例2：
    select * from info3;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56ul5bCP57qv_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

示例3:
     
        #查找名字和年龄字段的所有记录
        select  name,age from info3;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56ul5bCP57qv_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

## **3. distinct** ##

**查询不重复记录**

#查询不重复记录
    
      #语法 
       
        select distinct 字段 from 表名;
      
      #示例1: 去除年龄字段中重复的
         
         select distinct age from info3;

#查找性别
    
       select distinct address from  info3;

## **4.where** ##

**where 有条件的查询**

#语法
       
       select '字段' from 表名 where 条件
    
    #示例 :显示 name 和 age  并且要找到 age 小于20
     
        select name,age from info3 where age<20;

## **5.and;or** ##

**and 且 ； or 或**

#语法
     
      select 字段名 from 表名 where 条件1 (and|or) 条件2 （and | or） 条件3;
    
    #示例1:显示 name 和 age 并且要找到 age 大于 20 小于 30
    
      select name,age from info3 where age>20 and age<30;

## **6.in** ##

**显示已知值的资料**

#语法
    
     select 字段名 from 表名 where 字段 in ('值1','值2'......);
    
    #示例1: 显示学号为 1、2、3、4的学生记录
    
     select * from  info3 where  id  in (1,2,3,4);
     
    #示例2: 显示班级为1和3的学生记录
    
     select * from info3 where id in (1,3);

## **7.between** ##

**显示两个值范围内的资料**

#语法
      
      select 字段名 from 表名 where 字段 between '值1' and '值2';
    包含 and 两边的值
    
    #示例1: 显示学生姓名在 BGH 和 SDB中的学生记录
    
      select * from info3 where name between 'BGH' and 'SDB';
    
    #示例2:  显示学生号码id在 2-5 的信息
    
      select * from  info3 where id between 2 and 5;
    
    #示例3：显示学生年龄在20-35之间的信息，不需要表中一定有该字段，只会将20到25 已有的都显示出来
    
       select * from students where age between 20 and 25;

## **8.like 通配符** ##

**通配符通常是和 like 一起使用**

#语法:
      
       select 字段名 from 表名 where 字段 like 模式
    
    #示例1:查询名字以 s 开头的学生记录
    
       select * from info3 where name like 's%';
    
    #示例2:查找名字包含ong的学生记录
       
       select * from  info3 where name like '%ong%';
    
    #示例3:查找名字第二个字母为u,第三个字母为a的学生记录
    
       select * from  info3 where name like '_ua%';

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th style="text-align:center;width:98px;">通配符</th> 
   <th style="text-align:center;width:400px;">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th style="width:98px;">%</th> 
   <td style="width:400px;">表示零个，一个或者多个字符</td> 
  </tr> 
  <tr> 
   <th style="width:98px;">&nbsp; &nbsp;_</th> 
   <td style="width:400px;">下划线表示单个字符</td> 
  </tr> 
  <tr> 
   <th style="width:98px;">&nbsp;A_Z</th> 
   <td style="width:400px;">所有以A开头 Z 结尾的字符串 'ABZ' 'ACZ' 'ACCCCZ'不在范围内 下划线只表示一个字符 AZ 包含a空格z</td> 
  </tr> 
  <tr> 
   <th style="width:98px;">ABC%</th> 
   <td style="width:400px;">所有以ABC开头的字符串 ABCD ABCABC</td> 
  </tr> 
  <tr> 
   <th style="width:98px;">%CBA</th> 
   <td style="width:400px;">所有以CBA结尾的字符串 WCBA CBACBA</td> 
  </tr> 
  <tr> 
   <th style="width:98px;">%AN%</th> 
   <td style="width:400px;">所有包含AN的字符串 los angeles</td> 
  </tr> 
  <tr> 
   <th style="width:98px;">_AN%</th> 
   <td style="width:400px;">所有 第二个字母为 A 第三个字母 为N 的字符串</td> 
  </tr> 
 </tbody> 
</table>

## **9. order by** ##

**order by 按关键字排序**

#语法
     
      select 字段名 from 表名 where 条件 order by 字段 [asc,desc];
    
    asc  :正向排序
    desc :反向排序
    默认是正向排序
    
    #示例1:按学生的年龄正向排序显示年龄和姓名字段
    
     select name,age from info3 order by age;
    
    #示例2：按学生的年龄反向排序显示年龄和姓名字段
    
     select age,name from students order by age desc;
     
    #示例3：显示name、age和classid字段的数据 并且只显示classid字段为3 的 并且以age字段排序
    
     select age,name,classid from students where classid=3 order by age;

## **10.函数** ##

###    **10.1数学函数** ###

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th style="text-align:center;width:105px;">函数</th> 
   <th style="text-align:center;width:393px;">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:105px;">abs(x)</td> 
   <td style="width:393px;">返回x 的 绝对值</td> 
  </tr> 
  <tr> 
   <td style="width:105px;">rand()</td> 
   <td style="width:393px;">返回0到1的随机数</td> 
  </tr> 
  <tr> 
   <td style="width:105px;">mod(x,y)</td> 
   <td style="width:393px;">返回x除以y以后的余数</td> 
  </tr> 
  <tr> 
   <td style="width:105px;">power(x,y)</td> 
   <td style="width:393px;">返回x的y次方</td> 
  </tr> 
  <tr> 
   <td style="width:105px;">round(x)</td> 
   <td style="width:393px;">返回离x最近的整数</td> 
  </tr> 
  <tr> 
   <td style="width:105px;">round(x,y）</td> 
   <td style="width:393px;">保留x的y位小数四舍五入后的值</td> 
  </tr> 
  <tr> 
   <td style="width:105px;">sqrt(x)</td> 
   <td style="width:393px;">返回x的平方根</td> 
  </tr> 
  <tr> 
   <td style="width:105px;">truncate(x,y)</td> 
   <td style="width:393px;">返回数字 x 截断为 y 位小数的值</td> 
  </tr> 
  <tr> 
   <td style="width:105px;">ceil(x)</td> 
   <td style="width:393px;">返回大于或等于 x 的最小整数</td> 
  </tr> 
  <tr> 
   <td style="width:105px;">floor(x)</td> 
   <td style="width:393px;">返回小于或等于 x 的最大整数</td> 
  </tr> 
  <tr> 
   <td style="width:105px;">greatest(x1,x2.....)</td> 
   <td style="width:393px;">返回返回集合中最大的值</td> 
  </tr> 
  <tr> 
   <td style="width:105px;">least(x1,x2..........)</td> 
   <td style="width:393px;">返回返回集合中最小的值</td> 
  </tr> 
 </tbody> 
</table>

#示例1:返回-2的绝对值
    
      select abs(-2);
    
    #示例2:随机生成一个数
      
      select rand(1);
    
    #示例3:随机生成排序
      
      select * from  info3 order by rand();
    
    #示例4:返回7除以2以后的余数
    
      select mod(7,2);
    
    #示例5:返回2的3次方
    
      select power(2,3);
    
    #示例6:返回离2 . 6 最近的数
    
      select round(2.6);
    
    #示例7:保留2.335321的3位小数四舍五入后的值
    
      select round(2.335321,2);
    
    #示例8:返回数字2.335321 截断为2位小数的值
     
      select truncate(2.335321,2);
    
    #示例9:返回大于或等于2.335321的最小整数
    
      select ceil(2.335321);
    
    #示例10:返回小于或等于2.335321的最大整数
    
      select floor(2.335321);
    
    #示例11:返回集合中最大的值
    
      select greatest(1,4,3,9,20);
    
    #示例12:返回集合中最小的值
    
      select least(1,4,3,9,20);

**10.2 聚合函数**

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th style="text-align:center;width:73px;">函数</th> 
   <th style="text-align:center;width:425px;">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:73px;">avg()</td> 
   <td style="width:425px;">返回指定列的平均值</td> 
  </tr> 
  <tr> 
   <td style="width:73px;">count()</td> 
   <td style="width:425px;">返回指定列中非 NULL 值的个数</td> 
  </tr> 
  <tr> 
   <td style="width:73px;">min()</td> 
   <td style="width:425px;">返回指定列的最小值</td> 
  </tr> 
  <tr> 
   <td style="width:73px;">max()</td> 
   <td style="width:425px;">返回指定列的最大值</td> 
  </tr> 
  <tr> 
   <td style="width:73px;">sum(x)</td> 
   <td style="width:425px;">返回指定列的所有值之和</td> 
  </tr> 
 </tbody> 
</table>

#示例1:求表中年龄的平均值
    
      select avg(age) from  info3;
    
    #示例2:求表中年龄的总和
    
      select sum(age) from info3;
    
    #示例3:求表中年龄的最大值
    
      select max(age) from  info3;
    
    #示例4:求表中年龄的最小值
    
      select min(age) from info3;
    
    #示例5:求表中有多少班级字段非空记录
    
       select count(heigth) from info3;
    
    #示例6:求表中有多少条记录  count(*) 包含空字段,会忽略空记录
    
       select count(*) from  info3; 
    
    #示例7:看空格字段是否会被匹配
    
      select count(*) from info3;
       
       insert into info3 values(26,' ',28,'f',1,8);
    
       #插入空格字段后有26条记录,说明空格是识别的
      select count(*) from info3;

### **10.3字符串函数** ###

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th style="width:107px;">函数</th> 
   <th style="width:391px;">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:107px;">trim()</td> 
   <td style="width:391px;">返回去除指定格式的值</td> 
  </tr> 
  <tr> 
   <td style="width:107px;">concat(x,y)</td> 
   <td style="width:391px;">将提供的参数 x 和 y 拼接成一个字符串</td> 
  </tr> 
  <tr> 
   <td style="width:107px;">substr(x,y)</td> 
   <td style="width:391px;">获取从字符串 x 中的第 y 个位置开始的字符串，跟substring（）函数作用相同</td> 
  </tr> 
  <tr> 
   <td style="width:107px;">substr(x,y,z)</td> 
   <td style="width:391px;">获取从字符串 x 中的第 y 个位置开始长度为z 的字符串</td> 
  </tr> 
  <tr> 
   <td style="width:107px;">length(x)</td> 
   <td style="width:391px;">返回字符串 x 的长度</td> 
  </tr> 
  <tr> 
   <td style="width:107px;">replace(x,y,z)</td> 
   <td style="width:391px;">将字符串 z 替代字符串 x 中的字符串 y</td> 
  </tr> 
  <tr> 
   <td style="width:107px;">upper(x)</td> 
   <td style="width:391px;">将字符串 x 的所有字母变成大写字母</td> 
  </tr> 
  <tr> 
   <td style="width:107px;">lower(x)</td> 
   <td style="width:391px;">将字符串 x 的所有字母变成小写字母</td> 
  </tr> 
  <tr> 
   <td style="width:107px;">left(x,y)</td> 
   <td style="width:391px;">返回字符串 x 的前 y 个字符</td> 
  </tr> 
  <tr> 
   <td style="width:107px;">right(x,y)</td> 
   <td style="width:391px;">返回字符串 x 的后 y 个字符</td> 
  </tr> 
  <tr> 
   <td style="width:107px;">repeat(x,y)</td> 
   <td style="width:391px;">将字符串 x 重复 y 次</td> 
  </tr> 
  <tr> 
   <td style="width:107px;">space(x)</td> 
   <td style="width:391px;">返回 x 个空格</td> 
  </tr> 
  <tr> 
   <td style="width:107px;">strcmp(x,y)</td> 
   <td style="width:391px;">比较 x 和 y，返回的值可以为-1,0,1</td> 
  </tr> 
  <tr> 
   <td style="width:107px;">reverse(x)</td> 
   <td style="width:391px;">将字符串 x 反转</td> 
  </tr> 
 </tbody> 
</table>

**1）trim**

语法：
    select trim (位置 要移除的字符串 from 字符串)
     
    其中位置的值可以是 
    leading（开始） 
    trailing(结尾)
    both(起头及结尾)
     
    #区分大小写
    要移除的字符串:从字符串的起头、结尾或起头及结尾移除的字符串，缺省时为空格。

#示例1:从名字开头的开始,移除Sun Dasheng中的Sun显示
     
      select trim(leading 'Sun' from 'Sun Dasheng');
    
    #示例2：去除空格
      
      select  trim(both from '  zhang san     ' );

**2）length**

#语法
      
      select length(字段) from 表名;
    
    #示例:计算出字段中记录的字符长度
    
      select name,length(name)  from  info3;

**3）replace**

#语法
      
      select replace(字段,'原字段''替换') from 表名;
    
      select replace(name,'ua','hh') from info3;

**4）concat**

#语法:
      
      select concat(字段1,字段2) from 表名;
    
      #示例1：将name,classid字段拼接成一个字符串
    
       select concat(name,classid) from info3;
    
      #示例2:只显示3ban的名称和classid为一个组合记录
    
       select concat(name,classid) from info3 where classid=3;
    
      #示例3:中间加制表符
       
        select concat(name,'\t',classid) from info3 where classid3;

**4）substr**

#语法：
    
      select substr(字段，开始截取字符，截取的长度)  where 字段='截取的字符串' 
    
    #示例1：截取第6个字符往后
      
      select substr(name,6) from students where name='Yue Lingshan';
    
    #示例2：截取第6个字符往后的两个字符
     
      select substr(name,6,2) from students where name='Yue Lingshan';

## **11.group by** ##

*  对group by 后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的
 *  group by 有一个原则，就是select 后面的所有列中，没有使用聚合函数的列必须出现在 group by 的后面。

#语法：
    
       select 字段1，sum(字段2) from 表名 group by 字段1;
    
    #示例1：求各个班的年龄总和
     
      select classid,sum(age) from students group by classid;
    
    #示例2：求各个班的平均年龄
    
      select classid,avg(age) from students group by classid;
    
    #示例3：根据年龄查看每个班的人数
    
      select classid,count(age) from students group by classid;

## **12.having** ##

*  having：用来过滤由group by语句返回的记录集，通常与group by语句联合使用
 *  having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏，那就不需要GROUP BY子句。
 *  **要根据新表中的字段，来指定条件**

#语法:
    
      SELECT 字段1，SUM("字段")FROM 表格名 GROUP BY 字段1 having（函数条件);
    
    #示例：查看各个班的平均年龄在30以上的班级
    
      select classid,avg(age) from students group by classid having avg(age) > 30;

## **13.别名** ##

**栏位別名 表格別名**

#语法：
    
      SELECT "表格別名"."栏位1" [AS] "栏位別名" FROM "表格名" [AS] "表格別名";
    
    
    #示例：设置表名别名为f，基于班级号来统计各班年龄总和，sum（age）定义别名为total age
    
       select f.classid,sum(age) 'total age' from students as f group by f.classid;

## **14.连接查询** ##

### **1）inner join(等值相连)** ###

**只返回两个表中联结字段相等的行**

SELECT * FROM students A INNER JOIN scores B on A.stuid = B.stuid;

### **2）left join(左联接)** ###

**返回包括左表中的所有记录和右表中联结字段相等的记录**

select * from scores A left join students B on A.stuid = B.stuid;

### **3）right join(右联接)** ###

select * from scores A right join students B on A.stuid = B.stuid;

## **15.子查询** ##

**连接表格，在WHERE 子句或HAVING 子句中插入另一个SQL语句**

语法：
    
      SELECT "栏位1" FROM "表格1" WHERE "栏位2"	[比较运算符]		
    	
    #外查询
    
      (SELECT "栏位1" FROM "表格1" WHERE "条件");	
    
    #示例：查询学生学号为1的得分总和
    
      select sum(score) from scores where stuid in (select stuid from students where stuid=1);

## **16.EXISTS** ##

*  **用来测试内查询有没有产生任何结果类似布尔值是否为真**
 *  **如果有的话，系统就会执行外查询中的SQL语句。若是没有的话，那整个 SQL 语句就不会产生任何结果。**

#语法：
    
      SELECT "栏位1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");
    
    #示例1：先看students表中是否有stuid为1的学生，如果有则执行将scores表中的score求和
      
      select sum(score) from scores  where exists (select * from students where stuid=1);
    
    #示例2：先看students表中是否有stuid为88的学生，如果有则执行将scores表中的score求和
      
      select sum(score) from scores  where exists (select * from students where stuid=88);

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56ul5bCP57qv_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/fd4e69ea9f6840069164e2f883ba7365.PNG?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56ul5bCP57qv,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56ul5bCP57qv_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/748ba7a1a76642e98c9584b48fafa231.PNG?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56ul5bCP57qv,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56ul5bCP57qv_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/dc912307beee4dbd89edc3deda0739b7.PNG?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56ul5bCP57qv,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56ul5bCP57qv_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/79d1ed1b5131474aa2e7ade383ae6eca.PNG?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56ul5bCP57qv,size_20,color_FFFFFF,t_70,g_se,x_16