• SQL你必须知道的-查询聚合分组排序


    use MySchoolTwo   
    -- 简单查询   
    select * from Student   
    -- 话说这种查询的效率要比 * 要高级点   
    select sId , sName , sAge , sNo , sBirthday , sClassId , sSex from Student   
    select sName from student   
    -- 给列改名三种方式   
    select sName as '' 姓名'' , sAge as '' 年龄 '', sNo as ''学号 '' from student   
    select sName ''姓名 '' ,sAge ''年龄 '' ,sNo ''学号 '' from student   
    select '' 姓名 ''= sName ,'' 年龄 ''= sAge ,'' 学号 ''= sNo from student   
    -- 添加where 查询条件   
    select sName as '' 姓名'' , sAge as '' 年龄 '', sNo as ''学号 '' from student   
    where sSex ='' 女 ''  
    select sName as '' 姓名'' , sAge as '' 年龄 '', sNo as ''学号 '' from student   
    where sAge >= 20   
    -- 还可以直接 select调用 sql server 的内置函数入 newid()  getdate()   
    select 2* 3   
    select GETDATE ()   
    --top distinict order by   
    --Top 获取前几条数据   
    select top 2 * from Student where sAge < 20   
    order by sAge   
    --Distinct 去除重复数据 DISTINCT是对整个结果集进行数据重复处理的,而不是针对某一个列   
    select distinct sName from Student   
    -- 排除相同是必须满足 distinct之后所有的字段条件的 sName 与sAge 是且的关系   
    select distinct sName, sAge from Student   
    -- 百分之截取前几条数据 即使都按算   
    select top 10 percent sName ,sAge , sNo   
    from Student   
      
    -- 聚合函数   
    --max min avg sum count   
    select MAX ( english) from Score   
    select min ( english) from Score   
    -- 要注意的是 avg是不对 null 值进行计算的如果需要计算缺考人员在内计算平均分的话使用 sum/count   
    select avg ( english) from Score   
    select SUM ( english)/ COUNT (*) from Score   
    select Count (*) from Student where sSex = ''女 ''  
      
    -- 带条件查询   
    select studentId from Score where english>= 60   
    select sName from Student where sAge>= 20 and sAge <=30 and sSex = ''男 ''  
    --between...and....   
    select sName from Student where sAge between 20 and 30 and sSex ='' 男 ''  
    select * from Student where sClassId = 1 or sClassId = 4   
    --in....   
    select * from Student where sClassId in( 1, 4 )   
      
    -- 模糊查询   
    --left 从左向右截取位   
    select LEFT( ''2334'' ,2 )   
    -- 检索姓张的   
    select * from Student where left(sName , 1)= '' 张''  
    --like 的百分号代表一个或多个字符—下滑杠只代表一个字符 [] 代表满足其中的一个字符   
    -- 同样检索姓张的同上   
    select * from Student where sName like ''张 %''  
    -- 这个检索名字中出现亮的   
    select * from Student where sName like ''%亮 %''  
    -- 只检索名称为张某的 名称两个字的   
    select * from Student where sName like ''张 __''  
    -- 检索张飞某或者张亮某   
    select * from Student where sName like ''张 [ 飞亮]%''  
    -- 测试数据   
    insert into Student ( sClassId, sName ,sAge , sSex, sNo ,sBirthday , sPhone) values (4 , ''诸葛亮 '' ,20 , ''男 '' ,223156788011 , ''1989-8-8'', ''123456'' )   
    insert into Student ( sClassId, sName ,sAge , sSex, sNo ,sBirthday , sPhone) values (4 , ''张亮颖 '' ,20 , ''女 '' ,22315678834 , ''1989-8-8'', ''123456'' )   
    -- 使用like 通配符限制 sphone 为六位数字   
    alter table student   
    add constraint CK_Student_sPhone check (sPhone like ''[0-9][0-9][0-9][0-9][0-9][0-9]'')   
      
    -- 空值处理null   
    -- 数据库中,一个列如果没有指定值,那么值就为 null ,这个null 和 C#中的 null ,数据库中的 null表示“不知道”,   
    -- 而不是表示没有。因此 select null+1结果是 null ,因为“不知道”加的结果还是“不知道”。   
    select null+ 123 --返回 null   
    --select * from score where english != null ;都没有任何返回结果,因为数据库也“不知道”。   
    --SQL 中使用is null 、 is not null来进行空值判断   
    select * from student where sPhone is null  
    select * from student where sPhone is not null  
      
    --ORDER BY 子句位于SELECT 语句的末尾,它允许指定按照一个列或者多个列进行排序,   
    -- 还可以指定排序方式是升序(从小到大排列, ASC )还是降序(从大到小排列, DESC )。   
    -- 先按英语升序英语相同再按数学升序   
    select * from Score   
    order by english, math   
    -- 指定前后的排序规则   
    -- 按照英语成绩从大到小排序,如果英语成绩相同则按照数学成绩从小到大排序   
    select * from Score   
    order by english desc ,math asc  
    --order by 一定要出现在 where之后   
    select * from Student   
    where sSex = ''男 ''  
    order by sAge   
      
    -- 分组Group by   
    -- 分组就是把相同值的那些行 合并成为一行   
    -- 当查询条件含有 ''每个 '' 的时候会使用到分组   
    -- 每个班有多少个学生   
    -- 第一个问题 select之后的列必须出现在 group by 子句聚合函数除外   
    select COUNT (*), sClassId from Student   
    group by sClassId   
    -- 每个班中男同学的个数   
    select COUNT (*) as '' 男生个数'' , sClassId as '' 班级 '' from Student   
    where sSex = ''男 ''  
    group by sClassId   
    -- 每个班的平均年龄   
    select COUNT (*) as '' 人数'' , sClassId as '' 班级 '', AVG( sAge )as ''平均年龄 '' from Student   
    group by sClassId   
    -- 错误 聚合函数是不能出现在 where 子句中的   
    -- 必须使用Having , Having要位于 Group By 之后   
    -- 求每个班平均年龄大于的总人数   
    select COUNT (*), sClassId, AVG (sAge ) from Student   
    where AVG ( sAge)>= 20   
    group by   sClassId   
    -- 正确 使用 having对 group by 分组后的数据进行筛选   
    --Having 是Group By 的条件对分组后的数据进行筛选   
    -- 求每个班平均年龄大于的总人数   
    select COUNT (*) as '' 总人数'' , sClassId as '' 班级 '', AVG (sAge ) as '' 平均年龄'' from Student   
    group by sClassId   
    having AVG ( sAge)>= 20   
    -- 注意Having 中不能使用未参与分组的列, Having 不能替代where 。作用不一样, Having 是对组进行过滤。   
    -- 求每个班年龄大于平均年龄的总人数   
    select count (*), sClassId from Student   
    where sAge >=( select AVG (sAge ) from Student)   
    group by sClassId   
    select * from Student   
      
    -- 分组练习   
    -- 求男生女生分别有多少人   
    select COUNT (*), sSex from Student   
    group by sSex   
    -- 求每个班有多少男生   
    select COUNT ( sId), sClassId from Student   
    where sSex = ''男 ''  
    group by sClassId   
    -- 每个班中男同学的平均年龄   
    select COUNT ( sId), sClassId ,AVG ( sAge) as '' 平均年龄 '' from Student   
    where sSex = ''男 ''  
    group by sClassId   
    -- 求平均年龄小于的那些班   
    select sClassId , AVG( sAge ) from Student   
    group by sClassId   
    having AVG ( sAge)< 22   
      
    --union 联合结果集   
    -- 基本的原则:每个结果集必须有相同的列数;每个结果集的列必须类型相容   
    -- 排序 去除重复数据   
    select tSex , tName from teacher union  
    select sSex , sName from Student   
    --union all 与union 的区别在于 union 会去除重复行而且会重新组合排序   
    --UNION 合并两个查询结果集,并且将其中完全重复的数据行合并为一条   
    --Union 因为要进行重复值扫描,所以效率低,因此如果不是确定要合并重复行,那么就用 UNION ALL   
    select tSex , tName from teacher union all  
    select sSex , sName from Student   
    --union 的另一种用法一次插入多条数据   
    insert into teacher ( tName, tSex ,tAge , tSalary)   
    select '' 凤姐 '', '' 女'' , 18, 2400 union  
    select '' 月月 '', '' 女'' , 19, 3000   
      
    -- 要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩   
    select '' 最高成绩 '', MAX (english ) from Score union  
    select '' 最低成绩 '', min (english ) from Score union  
    select '' 平均成绩 '', avg (english ) from Score   
    -- 查询每位老师的信息,包括姓名、工资,并且在最后加上平均工资和最高工资   
    select tName , tSalary from teacher union all  
    select '' 平均工资 '', AVG (tSalary ) from teacher union all  
    select '' 最高工资 '', Max (tSalary ) from teacher   
    -- 把现有表中的数据插入到新表中 (newStudent 表不能存在 )   
    select * into NewStudent from Student   
    -- 基于以上逻辑可以把 newStudent作为零时表实现去除表中重复数据   
    select distinct sAge into newStudent from student   
      
    -- 把现有表的数据复制到一个已存在的表   
    truncate table newStudent   
    -- 复制数据会出现两个问题第一 NewStudent 没有标识列 没有主键   
    insert into newStudent select * from Student   
    insert into newStudent select sName , sAge , sNo , sBirthday , sClassId , sSex , sPhone from Student

    佛为心,道为骨,儒为表,大度看世界; 技在手,能在身,思在脑,从容过生活; 三千年读史,不外功名利禄; 九万里悟道,终归诗酒田园;
  • 相关阅读:
    Git合并开发代码分支到测试代码分支
    用webdriver+phantomjs实现无浏览器的自动化过程
    软件测试工作中涉及的Linux命令整理
    Windows系统端口占用情况检查脚本
    PowerShell调用jira rest api实现对个人提交bug数的统计
    地下城堡游戏小脚本儿——自动炼金
    Java中通过JDBC远程连接Oracle数据库
    PowerShell调用jira rest api实现jira统计自动化
    【Spring】12、Spring Security 四种使用方式
    【hibernate】1、Hibernate的一个注解 @Transient
  • 原文地址:https://www.cnblogs.com/taofx/p/4137019.html
Copyright © 2020-2023  润新知