002Sql Server 的SQL语句案例.docx

资源描述

002Sql Server 的SQL语句案例.docx

《002Sql Server 的SQL语句案例.docx》由会员分享，可在线阅读，更多相关《002Sql Server 的SQL语句案例.docx（24页珍藏版）》请在冰豆网上搜索。

002Sql Server 的SQL语句案例.docx

002SqlServer的SQL语句案例

●DBMS（DataBaseManagementSystem,数据库管理系统）和数据库。

平时谈到“数据库”可能有两种含义：

MSSQLServer、Oracle等某种DBMS；存放一堆数据表的一个分类（Catalog）

●数据库的构成-管理软件/服务/数据文件（表,视图...）

●不同品牌的DBMS有自己的不同的特点：

MYSQL、MSSQLServer、DB2、Oracle、Access、Sybase等。

对于开发人员来讲，大同小异

●SQL<>SQLServer<>MSSQLServer。

最常见的错误。

除了Access、SQLServerCE等文件型数据库之外，大部分数据库都需要数据库服务器才能运行。

学习\开发时是连接本机的数据库，上线运行时是数据库运行在单独的服务器。

●Catalog（分类）（又叫数据库DataBase,表空间TableSpace）,不同类的数据应该放到不同的数据库中

1.便于对各个Catalog进行个性化管理

2.避免命名冲突3安全性更高

●Table（表）:

书都放到书架上,碗都放到橱柜中,不同类型的资料放到不同的“格子”中，将这种区域叫做“表”（Table）。

不同的表根据放的数据不同进行空间的优化，找起来也方便。

●列（Column）、字段（Field）

●主键就是一个表中每个数据行的唯一标识。

不会有重复值的列才能当主键。

一个表可以没有主键，但是会非常难以处理，因此没有特殊理由表都要设定主键

主键有两种选用策略：

业务主键和逻辑主键。

业务主键是使用有业务意义的字段做主键，比如身份证号、银行账号等；逻辑主键是使用没有任何业务意义的字段做主键，完全给程序看的，业务人员不会看的数据。

因为很难保证业务主键不会重复（身份证号重复）、不会变化（帐号升位），因此推荐用逻辑主键。

数据库概念

SQLServer2008

●常用字段类型：

bit（可选值0、1）、datetime、int、varchar、nvarchar（可能含有中文用nvarchar）

●Nvarchar（50）、Nvarchar（MAX）

●varchar、nvarchar和char（n）的区别：

char（n）不足长度n的部分用空格填充。

Var：

Variable，可变的。

分类

备注和说明

类型

说明

二进制数据类型

存储非子符和文本的数据

Image

可用来存储图像

文本数据类型

字符数据包括任意字母、符号或数字字符的组合

Char

固定长度的非Unicode字符数据

Varchar

可变长度非Unicode数据

Nchar

固定长度的Unicode数据

Nvarchar

可变长度Unicode数据

Text

存储长文本信息（指针,2G）

Ntext

存储可变长度的长文本

日期和时间

日期和时间在单引号内输入

Datetime

日期和时间

数字数据

该数据仅包含数字，包括正数、负数以及分数

int

smallint

整数

float

real

数字

货币数据类型

用于十进制货币值

Money

Bit数据类型

表示是/否的数据

Bit

存储布尔数据类型

SQL语句入门

SQL主要分DDL（数据定义语言）和DML（数据操作语言）两类。

CreateTable、DropTable、AlterTable等属于DDL，Select、Insert、Update、Delete等属于DML。

创建表

CREATETABLET_Person（IdintNOTNULL,Namenvarchar（50）,AgeintNULL）删除

删除数据：

DELETEFROMT_PersonwhereAge=20

删除表：

DroptableT_Person1

简单插入

INSERTINTOT_Person（Id,Name,Age）VALUES（1,'Jim',20）

更新

一列：

UPDATET_PersonSetAge=30

多列：

UPDATET_PersonSetAge=30,Name='Jim2'

局部数据：

加where语句

样例代码：

CREATETABLET_Employee（FNumberVARCHAR（20）,FNameVARCHAR（20）,FAgeINT,FSalaryNUMERIC（10,2）,PRIMARYKEY（FNumber））;

INSERTINTOT_Employee（FNumber,FName,FAge,FSalary）VALUES（'DEV001','Tom',25,8300）;

INSERTINTOT_Employee（FNumber,FName,FAge,FSalary）VALUES（'DEV002','Jerry',28,2300.80）;

INSERTINTOT_Employee（FNumber,FName,FAge,FSalary）VALUES（'SALES001','John',23,5000）;

INSERTINTOT_Employee（FNumber,FName,FAge,FSalary）VALUES（'SALES002','Kerry',28,6200）;

INSERTINTOT_Employee（FNumber,FName,FAge,FSalary）VALUES（'SALES003','Stone',22,1200）;

INSERTINTOT_Employee（FNumber,FName,FAge,FSalary）VALUES（'HR001','Jane',23,2200.88）;

INSERTINTOT_Employee（FNumber,FName,FAge,FSalary）VALUES（'HR002','Tina',25,5200.36）;

INSERTINTOT_Employee（FNumber,FName,FAge,FSalary）VALUES（'IT001','Smith',28,3900）;

INSERTINTOT_Employee（FNumber,FAge,FSalary）

VALUES（'IT002',27,2800）;

SELECTFNumberAS编号,FNameAS姓名,FAgeAS年龄FROMT_Employee

数据汇总：

SQL聚合函数：

MAX（最大值）、MIN（最小值）、AVG（平均值）、SUM（和）、COUNT（数量）

selectcount（*）as记录总行数fromT_Employee;

selectmax（FSalary）as最大值fromT_Employee;

selectmin（FSalary）as最小值fromT_Employee;

selectavg（FSalary）as平均值fromT_Employee

selectsum（FAge）as总值fromT_Employee;

selectcount（*）as工资大于的总数fromT_Employee

whereFSalary>=5000;

--大于岁的员工的最高工资

SELECTMAX（FSalary）FROMT_EmployeeWHEREFAge>25

--最低工资和最高工资

SELECTMIN（FSalary）as最低工资,MAX（FSalary）as最高工资FROMT_Employee

数据排序

ORDERBY子句位于SELECT语句的末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排序方式是升序（从小到大排列，ASC）还是降序（从大到小排列，DESC）

select*fromT_Employee

orderbyFSalaryASC;

select*fromT_Employee

orderbyFSalaryDESC;

select*fromT_Employee

orderbyFAgeDESC;

--按照年龄从大到小排序，如果年龄相同则按照工资从大到小排序

select*fromT_Employee

orderbyFAgeDESC,FSalaryASC;

--ORDERBY子句要放到WHERE子句之后

SELECT*FROMT_EmployeeWHEREFAge>23ORDERBYFAgeDESC,FSalaryDESC

通配符过滤通配符过滤关键字使用LIKE。

单字符匹配：

SELECT*FROMT_EmployeeWHEREFNameLIKE'_erry'

多字符匹配：

SELECT*FROMT_EmployeeWHEREFNameLIKE'%n%'

通配符

解释

示例

‘_’

一个字符

ALike'C_'

任意长度的字符串

BLike'CO_%'

[]

括号中所指定范围内的一个字符

CLike'9W0[1-2]'

[^]

不在括号中所指定范围内的一个字符

DLike‘%[A-D][^1-2]'

空值处理

SQL中使用isnull、isnotnull来进行空值判断

SELECT*FROMT_EmployeeWHEREFNAMEisnull

SELECT*FROMT_EmployeeWHEREFNAMEisnotnull

多值匹配

--IN

SELECTFAge,FNumber,FNameFROMT_Employee

WHEREFAgeIN（23,25,28）

--判断

SELECT*FROMT_Employee

WHEREFAGE>=23ANDFAGE<=27

--betweenand

SELECT*FROMT_Employee

WHEREFAGEBETWEEN23AND27

数据分组

WHERE子句从数据源中去掉不符合其搜索条件的数据

GROUPBY子句搜集数据行到各个组中，统计函数为各个组计算统计值

--按照年龄进行分组统计各个年龄段的人数

SELECTFAgeas年龄,Count（*）as人数FROMT_EmployeeGROUPBYFage

没有出现在GROUPBY子句中的列是不能放到SELECT语句后的列名列表中的（聚合函数中除外）

--错误：

SELECTFAge,FSalaryFROMT_EmployeeGROUPBYFAge

--正确：

SELECTFAge,AVG（FSalary）as平均工资FROMT_EmployeeGROUPBYFAge

Having语句（分组后查询条件）

在Where中不能使用聚合函数，必须使用Having，Having要位于GroupBy之后：

SELECTFAge,COUNT（*）AS人数FROMT_Employee

GROUPBYFAge

HAVINGCOUNT（*）>1

限制结果集行数

SELECTtop5*FROMT_EmployeeorderbyFSalaryDesc

--检索按照工资从高到低排序检索从第六名开始一共三个人的信息

SELECTtop3*FROMT_Employee

WHEREFNumberNOTIN（SELECTTOP5FNumberFROMT_EmployeeORDERBYFSalaryDESC）

ORDERBYFSalaryDESC

去掉数据重复

--样例

ALTERTABLET_EmployeeADDFSubCompanyVARCHAR（20）;

ALTERTABLET_EmployeeADDFDepartmentVARCHAR（20）;

UPDATET_EmployeeSETFSubCompany='Beijing',FDepartment='Development'

WHEREFNumber='DEV001';

UPDATET_EmployeeSETFSubCompany='ShenZhen',FDepartment='Development'

WHEREFNumber='DEV002';

UPDATET_EmployeeSETFSubCompany='Beijing',FDepartment='HumanResource'

WHEREFNumber='HR001';

UPDATET_EmployeeSETFSubCompany='Beijing',FDepartment='HumanResource'

WHEREFNumber='HR002';

UPDATET_EmployeeSETFSubCompany='Beijing',FDepartment='InfoTech'

WHEREFNumber='IT001';

UPDATET_EmployeeSETFSubCompany='ShenZhen',FDepartment='InfoTech'

WHEREFNumber='IT002';

UPDATET_EmployeeSETFSubCompany='Beijing',FDepartment='Sales'

WHEREFNumber='SALES001';

UPDATET_EmployeeSETFSubCompany='Beijing',FDepartment='Sales'

WHEREFNumber='SALES002';

UPDATET_EmployeeSETFSubCompany='ShenZhen',FDepartment='Sales'

WHEREFNumber='SALES003';

--查看

SELECT*FROMT_Employee

--去重

SELECTFDepartmentFROMT_Employee

SELECTDISTINCTFDepartmentFROMT_Employee

SELECTDISTINCTFDepartment,FSubCompany

FROMT_Employee

联合结果集

基本的原则：

每个结果集必须有相同的列数；每个结果集的列必须类型相容。

--样例

CREATETABLET_TempEmployee（FIdCardNumberVARCHAR（20）,FNameVARCHAR（20）,FAgeINT,PRIMARYKEY（FIdCardNumber））;

INSERTINTOT_TempEmployee（FIdCardNumber,FName,FAge）VALUES（'1234567890121','Sarani',33）;

INSERTINTOT_TempEmployee（FIdCardNumber,FName,FAge）VALUES（'1234567890122','Tom',26）;

INSERTINTOT_TempEmployee（FIdCardNumber,FName,FAge）VALUES（'1234567890123','Yalaha',38）;

INSERTINTOT_TempEmployee（FIdCardNumber,FName,FAge）VALUES（'1234567890124','Tina',26）;

INSERTINTOT_TempEmployee（FIdCardNumber,FName,FAge）VALUES（'1234567890125','Konkaya',29）;

INSERTINTOT_TempEmployee（FIdCardNumber,FName,FAge）VALUES（'1234567890126','Fotifa',46）;

--查看

select*fromT_TempEmployee

--简单的结果集联合

SELECTFNumber,FName,FAgeFROMT_Employee

UNIONSELECTFIdCardNumber,FName,FAgeFROMT_TempEmployee

SELECTFNumber,FName,FAge,FDepartmentFROMT_Employee

UNIONSELECTFIdCardNumber,FName,FAge,'临时工,无部门'FROMT_TempEmployee

--UNION合并两个查询结果集，并且将其中完全重复的数据行合并为一条

SELECTFNameFROMT_Employee

UNION

SELECTFNameFROMT_TempEmployeeORDERBYFNameDESC

--如果不是确定要合并重复行，那么就用UNIONALL

SELECTFNameFROMT_Employee

UNIONALL

SELECTFNameFROMT_TempEmployeeORDERBYFNameDESC

--要求查询员工的最低年龄和最高年龄，临时工和正式员工要分别查询

SELECT'正式员工最高年龄',MAX（FAge）FROMT_Employee

UNIONALL

SELECT'正式员工最低年龄',MIN（FAge）FROMT_Employee

UNIONALL

SELECT'临时工最高年龄',MAX（FAge）FROMT_TempEmployee

UNIONALL

SELECT'临时工最低年龄',MIN（FAge）FROMT_TempEmployee

--查询每位正式员工的信息，包括工号、工资，并且在最后一行加上所有员工工资额合计。

SELECTFNumber,FSalaryFROMT_Employee

UNIONALL

SELECT'工资合计',SUM（FSalary）FROMT_Employee

数字函数

ABS（）：

求绝对值。

CEILING（）：

舍入到最大整数。

3.33将被舍入为4、2.89将被舍入为3、-3.61将被舍入为-3。

Ceiling→天花板

FLOOR（）：

舍入到最小整数。

3.33将被舍入为3、2.89将被舍入为2、-3.61将被舍入为-4。

Floor→地板。

ROUND（）：

四舍五入。

舍入到“离我半径最近的数”。

Round→“半径”。

Round（3.1425,2）

--样例

CREATETABLET_Person（FIdNumberVARCHAR（20）,

FNameVARCHAR（20）,FBirthDayDATETIME,

FRegDayDATETIME,FWeightNUMERIC（10,2））;