数据库Sql查询综合

Sql查询相关语句

1. Select语句

   • 查询特定列数据

      select 学生编号,学生姓名 from tb_Student
      select  *  from  tb_Student  --查询数据表中所有的数据列--

     | select | 查询语句中，select关键字后面可以添加要查询数据列的名称

     | 学生姓名 | 数据列名称

     | from | from关键字用于指定要查询哪张表的数据

     | tb_Student | 数据表名

     执行原理 From是最先运行的，会根据后面的表生成一个虚拟的表，然后根据Select语句中特定的列名称将虚拟表的数据提取出来，得到最终的虚拟表就是用户需要的结果集

   • 使用列别名 (AS)

      select 学生姓名 AS 姓名 from tb_Student

     | AS | AS前为列名称 | AS后为列的别名

   • 在列上加入计算

      select 学生编号,高数+外语 AS 高数和英语的分数 from  tb_Grade

     注意点： 在数据列加入计算后，数据列是没有名称的 可以使用AS 或空格来设置别名

   • 使用函数设置条件

      select 学生姓名,年龄 from tb_Student where 学生编号 > 2

     | where | 对查询设置条件| where 后面设置你所需要的条件也可以使用AND和OR

2. 查询常量

   • 查询常量

      select 学生姓名,年龄 from tb_Student where 学生编号 = 2

      

   • 查询字符串

      select 学生编号,年龄 from tb_Student where 学生姓名 ='张三' 

   • 查询日期数据

      select 学生姓名,学生年龄 from tb_Student where  出生年月='1998/12/2'

     可以使用Between查询在指定日期区间内的日期信息

   • 查询逻辑型数据

      select 学生编号,年龄 from tb_Student where  统招否='true'

   • 查询空（或null）数据

      select 学生编号,年龄 from tb_Student where 家庭住址 Is null Or 家庭住址 =''

3. 模糊查询

   • 利用“ -”通配符进行查询

      select 学生编号,年龄 from tb_Student where 学生姓名 like '张_'

     可以使用多个通配符 列如 张__

   • 利用“%”通配符进行查询

      select 学生编号,年龄 from tb_Student where 学生姓名 like '张%'

     注意点 “% ” 和 “” 的区别,“” 代表一个字符，"%"代表多个字符

   • 利用“[]”通配符进行查询

      select 学生编号,学生姓名 from tb_Student where 年龄 like '2[0-9]'

     注意点 “[]”代表方括号范围内的单个字符，可以多个使用

   • 利用“[^]”通配符进行查询

      select 学生编号,学生姓名 from tb_Student where 年龄 like '^[A-Z]'

     注意点 “[^]”代表非方括号范围内的单个字符

4. Top和Percent限制查询结果

   • 查询前10名数据

      select top 10 学生编号,学生姓名   from tb_Student Order By 学生编号  ASC

     注意点 在查询语句中，如果使用Top获取前若干条记录，尽量使用Order By 配合使用

   • 取出统计结果的后10名数据

      select top 10 学生编号,学生姓名   from tb_Student Order By 学生编号  DESC

     DESC 降序排列 ASC升序排列

   • 查询第10到第20名的数据

      select top 10 * from (select top 20 * from tb_Student order by  总分 ASC ) as  tb
      Order by  总分  DESC

     tb 表别名

     先在表中升序找到1-20 然后再在这里面按降序排 选10位

   • 查询销售量占前50%的图书信息

      select top 50 percent  *  from tb_book order by 图书编号  ASC

5. 数值查询

   • 判断是否为数值

   •  select  学生姓名, isnumeric(年龄) from tb_Student

      select 年龄,
      case when  isnumeric(年龄)=1
      then  '是数值'
      else  '不是数值' End
      from  tb_Student

   • 在查询时对数值进行取整

      select  dm   as 数值取整前 Ceiling(dm) AS 取整后 from tb_money    

     ceiling 向上取整 3.14 -4

     floor 向下取整 3.14 - 3

   • 对查询到的数值进行四舍五入

      select dm  as 四舍五入前  Round(dm,2) as  四舍五入后 from tb_money

     round(dm,2) 第一个参数是小数数值 第二个参数是四舍五入保留的位数

   • 求数值的绝对值

      select value as 数值  Abs(value) as 求绝对值之后 from tb_value

   • 根据生成的随机数查询记录

      select  *  from  tb_Student where  学生编号=1000+floor(rand()*10)

   • 根据查询数值的符号显示具体文本

      select value as  数值  
      case when sign(value)=1 then '正数'   else 
      case when sign(value)=-1 then '负数'  else '零' end
      end  as  判断值 from  tb_value

6. 字符差查询

   • 实现字符串的大小写转换

      select  friendname as 字符串 Lower(friendname) as 转换为小写
      Upper(friendname)  as 转换为大写  from  tb_string

   • 返回字符串在字符串出现的次数

      select  学生姓名，len(学生姓名) as 字符数量 from tb_Student

   • 返回字符串中的子串

      select 学生姓名,所在学院 substring（所在学院,2,3） as 所在学院字符串 from  tb_Student

     substring 返回指定数据列中的字符串

     接收三个参数 第一个 字符串 第二个 截取字符串的索引位置 第三个 截取字符的数量

   • 删除字符串中的子串

      select 所在学院  as 删除字符串之前 stuff（所在学院,2,3,''） as 删除字符串后 from  tb_Student

     stuff 删除指定长度的字符，并在删除的字符位置插入另一组字符

     接收四个参数 第一个 字符串 第二个 截取字符串的索引位置 第三个 截取字符的数量

     第四个表示移除子串的位置插入的字符串

   • 查询并替换字符串

      select 所在学院 charindex('学院'，所在学院) as 索引位置 from tb_student
      ​
      select 所在学院 replace(所在学院，'学院'，'xueyuan') as 替换后的信息 from tb_student

     charindex 获取字符串中指定字符的索引位置

     replace 替换字符 接收三个参数 第一个 字符串 第二个 将要替换的子串 第三个 替换的子串

7. 周期、日期查询

   • 判断是否为日期

      select  学生编号,学生姓名 
      case when  isdate(出生年月)=1 
      then 日期时间 else 非日期时间 end  as 生日时间判断   from tb_student

   • 查询指定时间段的数据

      select  *  from  tb_student  where 出生年月 between  '1998/01/22' and  '2000/01/11'

   • 按年、月或日查询数据

      select * from  tb_student  where year(日期)='2013'  and month(日期)='02' and day(日期)='23'

   • 返回当前日期时间及对应的日期

      select  getdate() as 当前时间  dataname(day,getdate())

     dataname接收两个参数 第一个用于规定日期的那一部分（年，月，日）

     第二个参数 为datetime的表达式

   • 查询指定时间间隔的数据

      select  学生编号,出生年月 datediff(year,出生年月,getdate())  as 学生年龄 from tb_student

     datediff 接收三个参数 第一个 用于规定时间差的哪一部分 年(year)月(month)日(day)

     第二个参数 为datetime类型的表达式

     第三个参数 为datetime类型的表达式

8. 比较、逻辑、重复查询

   • 利用运算符查询指定条件的数据

      select  * from tb_student where 学生姓名 like '李%'

   • NOT与谓词进行组合条件的查询

      select * from tb_grade   where  软件工程 >90 and 外语 not between 70 and  85

   • 查询时不显示重复记录

      select distinct 学生编号,学生姓名 from tb_student   

     distinct 不显示重复记录， 会降低性能

   • 列出数据中的重复记录和纪录条数

      select count(书号) as 记录条数,书号,书名  from  tb_book group   by   书号,书名  having  count(书号) > 1

     group by 去除重复记录，并显示重复记录的条数 having 设置分组查询条件

9. 在查询中使用OR和And

   • 利用OR运算符进行查询

      select * from  tb_grade where 外语 > 90  or 高数 > 90

   • 利用AND运算符进行查询

      select  * from tb_grade where 高数 > 70  and 外语 >80

   • 同时利用OR、AND运算符进行查询

      select * from tb_grade where  (外语 > 70 or 高数>70) and 软件工程 > 80

10. 排序和分组统计

    • 数组分组统计

       select  出版社,sum (金额) as 总金额 from tb_book  group by  出版

    • 在分组查询中使用ALL关键字

       select 书名,出版社,sum(金额) as 总金额  from  tb_book where 出版社="人邮" group by  all  书名，出版社

      使用分组查询，select 中的非聚合函数的数据列应包含在group by 子句中

      使用all关键字，查询结果将包括由group by子句产生的所有组

    • 在分组查询中使用CUBE运算符

       select 所属部门,性别,avg (工资) as 平均工资 from tb_Employee group  by 所属部门，性别 with cube 

      cube 运算符对分组信息进行分组汇总(多维数据集)

    • 在分组查询中使用ROLLUP

       select 所属部门,性别,avg (工资) as 平均工资 from tb_Employee group  by 所属部门，性别 with rollup

      rollup 对查询的分组信息进行分组汇总运算

      rollup和cube的区别：

      cube生成的结果集显示所选列中值得所有组合的聚合数据，

      rollup生成的结果集显示所选列中值得某一个层次结构的聚合数据

    • 对数据进行降序查询

       select  学生编号,学生姓名  from tb_grade order by 学生编号 desc

    • 对数据进行多条件排序

       select  *  from tb_grade order by 语文 asc, 高数  desc 

    • 对统计结果进行排序

       select top 5 书号,书名,作者,出版社, sum(销售数量) as  合计销售数量 from tb_book 
               group  by  书号，书名，作者，出版社  order by  sum(销售数量)  desc

    • 按姓氏拼音排序

       select 学生编号,性别 from tb_student order by 学生姓名  collate  chinese_prc_cs_as

    • 使用Compute

       select * from  tb_employee  order by 所属部门 compute sum（工资） 

      compute对员工表中全部员工的工资情况进行汇总

    • 使用Compute By

       select *  from  tb_employee order by 所属部门  compute sum(工资) by 所属部门 

      compute by 将与员工中的员工工资按所属部门汇总

11. 聚合函数

    • 利用聚合函数Sum求数学总成绩

       select   sum(数学)  as  数学总成绩   from   tb_book

       

      select sum(数学) as 数学总成绩 from tb_book

    • 利用聚合函数AVG求某班数学的平均年龄

       select  avg(数学) as 数学平均成绩  from   tb_book

    • 利用聚合函数MIN求最低数学成绩

       select min(数学) as 数学最低成绩 from tb_grade 

    • 利用聚合函数MAX求最高数学成绩

       select max(数学) as 数学最高成绩 from tb_grade 

    • 利用聚合函数Count求商品数

       select  count(distinct 商品名称)  from tb_ware

    • 利用聚合函数First或Last求数据表中的第一条或最后一条

       select first(boooknames)  as bookname, last(bookscore)  as bookscore  from  tb_books

12. 多表查询（连接查询）

    • 利用FROM子句进行多表查询

       select  a.学号,a.姓名, b.成绩 from  student as a ,grade as b  where a.学号= b.学号

    • 合并多个结果集

       select  学号,姓名  from  student union select  数学成绩 ,英语成绩  from  grade  

      union 从结果集删除重复的行

      union all 包含所有行不会删除重复行

    • 笛卡尔乘积查询

       select  a.学号,a.姓名, b.成绩 from  student as a ,grade as b

      笛卡尔乘积查询是一个特例，是一种无连接规则的查询，不需要设置连接规则

13. 嵌套查询

    • 简单嵌套查询

       select 学生编号, 学生姓名,性别 from student  where  学生编号 in (select 学生编号 from grade  where 数学成绩>60)

    • 复杂嵌套查询

    • 嵌套查询在查询统计的应用

       select  * from  grade where 数学成绩 > all (select  数学成绩 from  grade  where 学生姓名 in ('张三','李四'))

      '>' all 表示大于条件每一个值 大于最大值

      ’>‘ any 表示至少大于条件中的一个值 大于最小值

14. 子查询

    • 使用Exists运算符引入子查询

       select * from  student as a where exists (select * from grade as  b a.学生编号=b.编号 and  a.姓名='张三')

    • 在Having子句使用子查询过滤数据

       select 学生编号,学生姓名,年龄 （select avg(年龄) from student ）as 平均年龄 from student group by 学生编号,学生姓名,年龄
             having 年龄 > (select avg(年龄) from student)

    • 在Update语句应用子查询

       update student set 学生编号 =（select  学生编号 from grade where  数学成绩=89）where 学生姓名 ='张三'

    • 使用子查询删除数据

       delete from student where 学生编号 in （select 学生编号 from grade where 数学成绩 =89）

15. 组合语句

    • 使用组合查询

       select 学生编号,学生姓名,年龄 from student where 性别='女' union select 学生编号,学生姓名,年龄 from student where 学生编号>70

      通过union 组合查询语句，将两个或者多个查询的结果集组合为一个结果集

    • 多表联合查询

       select 学生姓名 from studnet union select  convert(varchar(20,总分) from grade 总分 > 600

      convert将某种数据类型的表达式转换为另一个数据类型

    • 对组合查询后的结果进行排序

       select 学生姓名 from studnet union select  convert(varchar(20,总分) from grade 总分 > 600  order by  学生姓名  asc

    • 获取组合查询中两个结果集的交集

       select 学生编号,学生姓名 from student  intersect select 学生编号,学生姓名 from grade 

      intersect 用于查询和返回两个结果集中相同的数据记录

    • 获取组合查询中两个结果集的差集

       select 学生编号,学生姓名 from student except select 学生编号,学生姓名 from grade 

      except 用于其左侧不包含右侧查询结果集中的数据记录

16. 内连接查询

    • 简单内连接查询

       select a.学生编号,b.数学成绩  from student as a  inner join  grade  as   b on a.学生编号=b.学生编号

      inner join 来连接两个表 on设置连接规则

    • 复杂内连接查询

       select a.学生编号,b.数学成绩,c.出勤率  from student as a 
       inner join  grade  as   b on a.学生编号=b.学生编号
       inner join  studentTimeCard  as c  as a.学生编号=c.学生编号

    • 使用INNER Join实现自身连接

       select  a.* from student as  a inner join student as b on a.学生编号=b.学生编号 and a.学生姓名='张三'

      自连接查询效率高于子查询

    • 使用INNER Join实现不等连接

       select  a.* from student as  a inner join student as b on a.学生编号<>b.学生编号 and a.学生姓名='张三'select 

    • 使用内连接选择一个表与另一个表中相关的所有行

17. 外连接查询

    • Left Outer Join查询

       select a.学生编号,a.学生编号,b.总分 from  student as a left outer join grade as b on a.学生编号=b.学生编号

    • Right Outer Join查询

       select a.学生编号,a.学生编号,b.总分 from  student as a right outer join grade as b on a.学生编号=b.学生编号

    • 使用外连接进行多表联合查询

18. 利用IN进行查询

    • 使用In查询表中的记录信息

       select 学生姓名,性别,年龄 from  student  where  学生编号 in (select  学生编号 from grade  where 总分 >600)

    • 使用In引入子查询限定查询范围

    • 使用Not In运算符引入子查询

       select 学生姓名,性别,年龄 from  student  where  学生编号 not in (select  学生编号 from grade  where 数学成绩 <60 and  数学成绩 >95 )

19. 交叉表查询

    • 利用Transform分析数据(assess)

       trancform sum(数量) as 库存数量 
       select  语言类别 from 图书排行 where 语言类别 in （'语文','数学'） group  by (语言类别)
       pivot 分析时间

      transform aggfunction selectstatement pivot pivotfield (value[,value2])

      | aggfunction | 运算符所选数据的sql合计函数

      | selectstatement | select语句

      | pivotfield | 在查询结果集用来创建列标题的字段

      | value ,value1 | 用来创建列标题的固定值

    • 利用Tranform动态分析数据

    • 静态交叉表(sqlserver)

    • 动态交叉表

20. 函数查询

    • 在查询语句中使用格式化函数

       select 姓名,出生日期 as 格式化前出生日期 format ('出生日期','yyyy年mm月dd日') as 格式化日期  from  student 

    • 在查询语句中使用字符串函数

    • 在查询中使用日期函数(assess)

21. 索引查询

    • 使用Unique关键字创建唯一索引

       create unique  index  index_name  on 数据表名称 (数据列)

    • 使用Clustered关键字创建聚簇索引

        create clustered index  索引名称  on  数据表名称 (数据列)

      为了提高查询性能，在数据库中设计聚簇索引

      聚簇索引 可以按照物理磁盘相近的位置索引数据记录，一个数据表中只允许有一个聚簇索引

      聚簇索引改变了表中数据存放的物理位置

    • 使用索引视图查询数据

       select

      必须先为视图创建唯一的聚簇索引，才能为该视图才能创建非聚簇索引

      索引视图限制：

      1.定义索引视图的select 语句中不能包含top,distinct,compute,having,union关键字和子查询

      2.在任何联合表中，均不允许进行outer join 操作

    • 删除索引

       drop index 表名称 索引名称

22. 应用存储过程

    • 使用存储过程查询多表中的数据

       create proc  proc_student 
       as
       select * from student 

      关键字 proc

      为了提高安全性和提高存储效率

      使用存储过程向表中添加数据

         create proc pro_select
         (
         @id int ,
         @name varchar(30)
         )
         as
         select * from student where  编号=@id  and 姓名=@name
         go
         exec proc_select  2,"张三"

      exec 调用

    • 使用存储过程删除表中的数

       create  proc proc_delete 
       @id  int 
       as 
       delete student where  编号=@id

23. Having语句

    • 利用Having子句过滤分组数据

       select 所在学院,count(*) as 人数,avg(年龄) as  平均年龄 from student group by 所在学院 having avg(年龄) >22 

      having 过滤分组数据（指定分组和聚合的查询条件）

    • Having子句应用在多表查询中