SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

时间:2023-12-06 09:57:32

Sqlserver基础知识

(1)创建数据库

创建数据库有两种方式,手动创建和编写sql脚本创建,在这里我采用脚本的方式创建一个名称为TSQLFundamentals2008的数据库。脚本如下:

 

同时往数据库表插入一些数据,用户后续对数据库的sql的练习。在这里有需要的可以下载相应的脚本进行数据库的初始化。我放到百度云上面,请戳

我:http://yun.baidu.com/share/link?shareid=3635107613&uk=2971209779,提供了《Sqlserver2008技术内幕》这本书的电子版和脚本。

(2)在这里对TSQLFundamentals2008数据各个表进行表说明一下:

数据库表界面如下:

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

 

HR.Employees

雇员表,存放员工的一些基本信息。

Production.Products

产品信息表

Production.Suppliers

供应商表

Production.Customers

顾客信息表

Production.Categories

产品类别表

Sales.OrderDetails

订单详情表

Sales.Orders

订单表

Sales.Shippers

货运公司表

Sqlserver一些基本命令:

查询数据库是否存在:

if DB_ID("testDB")is not null;

检查表是否存在:

if OBJECT_ID(“textDB”,“U”) is not null ;其中U代表用户表

创建数据库:

create database+数据名

删除数据库:

drop database 数据库名 --删除数据库的

drop table 表名--删除表的

delete from 表名 where 条件 --删除数据的

查询语句:

use  数据库名称 --修改的数据库

select*from +表名称 --要查询的表

select某某,某某,某某 from 表名称 where 条件 --带条件查询的数据

插入数据:

insert into 表名称  (条件)values (相对应的值)

单表查询

(1)分组--对于分组查询,select字句会有限制,需要查询字段要出现在group by 子句中,同时分组以后,可以对分组情况进行统计。

查询雇员表,根据雇员所在国家分组,统计每组的人数情况:

1 select country,count(*) as N'人数'
2 from hr.Employees
3 group by country

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

当要查询的字段不包含在group by子句中,则会报相应的错误,所以此时要注意出现在select 后面的查询字段进行分组后,也同时需要出现在group by后面。

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

(2)在这里提示一下:查询条件不要使用计算列,下面谈谈具体原因:

例如:查询雇员表里面雇员出生为1973年的所有雇员信息,可以这样编写sql语句:

1 select YEAR(birthdate),firstname,lastname from HR.Employees
2 where YEAR(birthdate)='1973'

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

可以看到查询结果将1973年的雇员信息查出来了,但是大家可以思考一下,上面的sql语句在查询的时候,首先是要讲birthdate进行取出年度的计算,

Year(birthdate),其中Year为sql的内置函数,可以用于对字符串日期进行取出年份的计算。同时我们还可以采用下面的sql语句进行查询:

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

 

通过sql执行计划可以看出来,查询条件带计算列走的是索引扫描,而where子句后面采用查找范围限制,则走的是索查找。对比两个查询显然绝大部分情况下

走索引查找的查询性能要高于走索引扫描,特别是查询的数据库不是非常大的情况下,索引查找的消耗时间要远远少于索引扫描的时间。所以在查询条件中尽

量避免计算条件。

(3)说说sqlserver中的null,null在数据库中表示不存在,与C#中的null不同,不表示空引用,没有对象,NULL的运算规则:有null的任何运算都是null。

is [not] null: 只能用做条件判断表达式,是否是null?是 条件为true,不是 条件为false。

isnull():函数,如果第一个参数是null,则用第二个参数的值替换第一个参数的值作为函数的返回值。记住:第二个参数的类型必须和第一个兼容。

nullif():函数,如果两个参数值相等、有一个参数是null、或两个参数是null,函数返回值是null;否则返回第一个参数的值。

(4)top用法:意在取出表中满足条件的前多少位。top 10---前10位

说到top,突然想到了面试题中经常出现的查询某表中的前30—40条记录,注意id可能不连续。利用top可以这样写:

1 select top 10 * from A where ID
2 not in(select top 30 ID from A order by ID asc)
3 order by ID asc

同时也可以采用如下写法,只不过可读性比较差:

1 select top 10 * fron A where ID>
2 (select Max(ID) from (select top 30 ID from A order by ID)as t)
3 order by ID asc

当然既然有范围in存在,就可以用exist实现:

1 select top 10 * from A a1
2 WHERE NOT EXISTS
3 (SELECT * from
4 (SELECT TOP 30 * FROM A ORDER BY id asc) a2
5 WHERE a2.id =a1.id
6 )

但是目前需要考虑到----相关子查询:主查询每遍历一条记录时,都要针对主查询的值执行子查询,所以效率比较低。

下面介绍一下top与percent联合使用,percent表示所占的百分比:例如查询雇员表里面,前面百分之二十的雇员的信息,可以写sql,查询结果为两人。

1 select top(20) percent * from hr.employees  

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

我们在查询一下hr.employees(雇员表),同时查询一下雇员表里面总共有多少人,查出结果显示有9人。

1 select count(*) as N'总人数' from hr.employees

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

可以看出,9个人按百分之二十取整数了,所以查出来的显示有两个人。

(5)with ties附加属性:

当我们查询订单表时,查询sql:

1 select orderid,orderdate
2 from sales.orders order by orderdate desc

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

加入我们查询前五个订单信息时候,加入top 5

1 select top 5 orderid,orderdate
2 from sales.orders order by orderdate desc

查询结果如图:

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

对比没有加top 5,查询结果截取了前五条订单信息,但是有时候我们需要将与最后一条订单日期相同的一起取出来,此时就需要采用附加属性with ties。

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

(6)over开窗函数:

上面讲到要用count聚合函数,在需要分组求和。但采用over 则可以同样实现基于什么的求和。省去group by。

1 select firstname,lastname ,count(*) over()  as N'总人数'
2 from hr.employees

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

其中over(),括号里面可以附加条件,基于什么进行汇总。不添加,则表示对所有的记录进行汇总。例如求每位顾客所消费的订单总额,可以这样写:

1 select orderid,custid,sum(val) over (partition by custid) as N'顾客消费总额',
2 sum(val) over() as N'订单总额' from sales.ordervalues

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

五.排名函数

(1)row_number,行号,一般与over联合使用。over基于什么排名。

1 select row_number() over(order by lastname) as N'行号', lastname,firstname
2 from hr.employees

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

(2)rank ,排名,真正意义上的排名,例如:

1 select country,row_number() over(order by country) as N'rank排名', lastname,firstname
2 from hr.employees

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

可以看出,根据country排名,确实排出来啦,但是发现前四位同为UK,按理来说使部分先后顺序的,所以在此可以用rank来操作。

1 select country,rank() over(order by country) as N'rank排名', lastname,firstname
2 from hr.employees

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

可以看出来,使用rank以后,country同为UK的并列第一,类似于学生考试成绩排名并列第一的情况。

(3)dense_rank,密集排名

通过上面rank排名以后,存在并列第一的情况,但是country为USA的应该为第二,所以就出现了使用密集排名dense_rank进行排名。

1 select country,dense_rank() over(order by country) as N'dense_rank排名', lastname,firstname
2 from hr.employees

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

可以看出采用dense_rank以后,就满足了某一条件下,同属一个名次的需求。

(4)分组ntile。按某一条件进行分组。

1 select country,ntile(3) over (order by country) as N'ntile分组',dense_rank() over(order by country) as N'dense_rank排名', lastname,firstname
2 from hr.employees
3 order by country

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

有时候为了在某一个范围内进行排序,比如:

1 select lastname,firstname,country,row_number() over( order by country) as N'排名'
2 from hr.employees

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数

为了实现根据在country范围内排序,即country为Uk的为一组进行排序,country为USA的为一组进行排序。可以这样写:

1 select lastname,firstname,country,row_number() over( partition by country order by country) as N'排名'
2 from hr.employees

SQLServer学习笔记<>.基础知识,一些基本命令,单表查询(null top用法,with ties附加属性,over开窗函数),排名函数