SQL sever 各种查询语句和建表语句.docx

SQL sever 各种查询语句和建表语句.docx

《SQL sever 各种查询语句和建表语句.docx》由会员分享，可在线阅读，更多相关《SQL sever 各种查询语句和建表语句.docx（21页珍藏版）》请在冰点文库上搜索。

SQLsever各种查询语句和建表语句

SQLsever2000各种查询语句和建表语句

2009-10-2900:

21

CREATETABLECompany

（

CompanyID  CHAR（3）   NOTNULL

  CONSTRAINTPK_Company

  PRIMARYKEY,

CompanyName  NVARCHAR（10）NOTNULL,

CompanyAddressNVARCHAR（50）NULL

）

CREATETABLEWorker

（

WorkerID  CHAR（5）   NOTNULL

  CONSTRAINTPK_Worker

  PRIMARYKEY,

WorkerName  NVARCHAR（5）  NOTNULL,

WorkerSex  NCHAR

（1）  NOTNULL

  CONSTRAINTCK_Worker_WorkerSex

  CHECK（WorkerSexIN（'男','女'））,

WorkerAge  TINYINT   NULL,

WorkerJob  NVARCHAR（10）NULL,

Salary   INT    NULL,

CompanyID  CHAR（3）   NULL

  CONSTRAINTFK_Worker_Company

  FOREIGNKEYREFERENCESCompany（CompanyID）

）

CREATETABLEProject

（

ProjectID  CHAR（3）   NOTNULL

  CONSTRAINTPK_Project

  PRIMARYKEY,

ProjectName  NVARCHAR（20）NOTNULL,

ProjectPlaceNVARCHAR（10）NULL

）

CREATETABLEEnroll

（

WorkerID  CHAR（5）   NOTNULL,

ProjectID  CHAR（3）   NOTNULL,

Job    NVARCHAR（10）NULL,

MonthCount  INT    NULL,

MonthSalary  INT    NULL,

CONSTRAINTPK_Enroll

  PRIMARYKEY（WorkerID,ProjectID）,

CONSTRAINTFK_Enroll_Worker

  FOREIGNKEY（WorkerID）REFERENCESWorker（WorkerID）,

CONSTRAINTFK_Enroll_Project

  FOREIGNKEY（ProjectID）REFERENCESProject（ProjectID）

）

INSERTINTOCompany（CompanyID,CompanyName,CompanyAddress）

VALUES（'A01','北京公司','北京海淀区'）

INSERTINTOCompany（CompanyID,CompanyName,CompanyAddress）

VALUES（'B24','上海公司','上海闵行区'）

INSERTINTOCompany（CompanyID,CompanyName,CompanyAddress）

VALUES（'C13','福建公司','福建福州鼓楼区'）

INSERTINTOCompany（CompanyID,CompanyName,CompanyAddress）

VALUES（'D00','台湾%公司','台北县台北市'）

INSERTINTOWorker（WorkerID,WorkerName,WorkerSex,WorkerAge,WorkerJob,Salary,CompanyID）

VALUES（'A0101','黄伟强','男',33,'工程师',2100,'A01'）

INSERTINTOWorker（WorkerID,WorkerName,WorkerSex,WorkerAge,WorkerJob,Salary,CompanyID）

VALUES（'A0102','陈至',  '男',36,'高级工程师',4300,'A01'）

INSERTINTOWorker（WorkerID,WorkerName,WorkerSex,WorkerAge,WorkerJob,Salary,CompanyID）

VALUES（'B2424','林发清','女',43,'高级工程师',5000,'B24'）

INSERTINTOWorker（WorkerID,WorkerName,WorkerSex,WorkerAge,WorkerJob,Salary,CompanyID）

VALUES（'C1313','陈华仁','男',35,NULL,2500,'C13'）

INSERTINTOWorker（WorkerID,WorkerName,WorkerSex,WorkerAge,WorkerJob,Salary,CompanyID）

VALUES（'C1315','傅星',  '女',23,'助理工程师',2000,'C13'）

INSERTINTOWorker（WorkerID,WorkerName,WorkerSex,WorkerAge,WorkerJob,Salary,CompanyID）

VALUES（'X0001','赵苑言','女',NULL,NULL,3000,NULL）

INSERTINTOProject（ProjectID,ProjectName,ProjectPlace）

VALUES（'101','国道','四川成都'）

INSERTINTOProject（ProjectID,ProjectName,ProjectPlace）

VALUES（'202','高速公路',NULL）

INSERTINTOProject（ProjectID,ProjectName,ProjectPlace）

VALUES（'303','大桥','天津南开区'）

INSERTINTOEnroll（WorkerID,ProjectID,Job,MonthCount,MonthSalary）

VALUES（'A0101','101','项目经理',18,1000）

INSERTINTOEnroll（WorkerID,ProjectID,Job,MonthCount,MonthSalary）

VALUES（'A0101','202',NULL,12,800）

INSERTINTOEnroll（WorkerID,ProjectID,Job,MonthCount,MonthSalary）

VALUES（'A0102','101',NULL,23,800）

INSERTINTOEnroll（WorkerID,ProjectID,Job,MonthCount,MonthSalary）

VALUES（'A0102','202',NULL,17,650）

INSERTINTOEnroll（WorkerID,ProjectID,Job,MonthCount,MonthSalary）

VALUES（'C1313','303','施工员',14,800）

INSERTINTOEnroll（WorkerID,ProjectID,Job,MonthCount,MonthSalary）

VALUES（'C1315','202','施工员',15,900）

INSERTINTOEnroll（WorkerID,ProjectID,Job,MonthCount,MonthSalary）

VALUES（'X0001','101','监理',20,1000）

--1.

SELECT*FROMWorker

--2.

SELECTWorkerNameAS姓名,SalaryFROMWorker

--3

SELECTWorkerID,Salary/22.5FROMWorker

SELECTWorkerID,Convert（Int,Salary/22.5）FROMWorker

SELECTWorkerID,CAST（Salary/22.5ASINT）FROMWorker

SELECTWorkerID,Salary/22.5ASDaySalaryFROMWorker

SELECTWorkerIDAS员工号,Salary/22.5AS日工资FROMWorker

SELECTWorkerID员工号,Salary/22.5日工资FROMWorker

SELECT员工号=WorkerID,日工资=Salary/22.5FROMWorker

SELECT[INT员"工"号]=WorkerID,Salary/22.5"日[工]资"

FROMWorker

--4.

Select*FromEnroll

WhereMonthCount>15

--5.

Select*

FromCompany

Where'A01'=CompanyID

--6

SelectProjectName

FromProject

WhereProjectPlace='四川'

--7

Select*

FromWorker

WhereWorkerNameLIKE'陈%'

--8

Select*

FromWorker

WhereWorkerNameLIKE'%言'

--9

Select*

FromWorker

WhereWorkerNameLIKE'张%言'

--10

Select*

FromCompany

WhereCompanyNameLIKE'%司%'

Select*

FromCompany

WhereCompanyNameLIKE'%%%'

Select*

FromWorker

WhereWorkerIDLIKE'_01_2'

Select*

FromWorker

WhereWorkerNameLIKE'陈__'

--11

Select*

FromWorker

WhereWorkerNameLIKE'[傅赵]%'

--12

Select*

FromWorker

WhereWorkerNameLIKE'[^傅赵]%'

Select*

FromWorker

WhereWorkerNameNOTLIKE'[傅赵]%'

Select*

FromWorker

WhereNOTWorkerNameLIKE'[傅赵]%'

--13

Select*

FromCompany

WhereCompanyAddressIN（'北京海淀区','上海闵行区'）

--13.5

Select*

FromCompany

WhereCompanyNameLIKE'%+%%'ESCAPE'+'

--14

Select*

FromWorker

WhereWorkerAgeBetween20And30

--15

Select*

FromWorker

WhereWorkerAgeNOTBetween20And30

Select*

FromWorker

WhereNOTWorkerAgeBetween20And30

--17

Select*

FromWorker

WhereWorkerAgeISNULL

Select*

FromWorker

WhereWorkerAgeISNOTNULL

--19

Select*

FromWorker

OrderBySalary

Select*

FromWorker

OrderBySalaryASC

--20

Select*

FromWorker

OrderBySalaryDESC

--21

Select*

FromWorker

OrderByCompanyID,SalaryDESC

--22

SelectTOP1*

FromWorker

OrderByWorkerAgeAsc

--23

SelectDistinctCompanyID

FromWorker

/****************************

*回顾

*

*索引的概念

*简单查询

*  选择全部、部分字段

*  计算字段

*  字段重命名

*选择查询WHERE

*  基本的比较条件><=>=<=<>!

>!

=

*  多个条件的逻辑连接NOTANDOR

*  字符串的LIKE匹配%_[][^][acegh][A-Z]

*  IN和BETWEEN的范围比较

*  空值的判断IS[NOT]NULL

*结果排序

*  ORDERBY

*  TOPn[PERCENT]（SQLServer）

*  LIMIT（mysql）

*  ROWID（Oracle）

****************************/

--ANSI:

yyyy-MM-ddHH:

mm:

ss.ttt

SELECTMONTH（'2009-9-2710:

24:

36.234'）

/****************************

*本次计划

*

*分组查询

*  聚合函数

*  GROUPBY子句

*  HAVING子句

*  混合使用

*

*连接查询

*  等值连接

*  不等值连接

*  自连接

****************************/

/*

之前的查询，无论怎么过滤，都是针对原始数据的

假如要查询统计数据，则需要使用聚合函数

*/

--例：

查询工程师的平均年龄

SELECTWorkerAge

FROMWorker

SELECTAVG（WorkerAge）ASAvgAge

FROMWorker

--例：

查询工程师的最大、最小年龄

SELECTMAX（WorkerAge）ASMaxAge,MIN（WorkerAge）ASMinAge

FROMWorker

--例：

查询在编号101的项目中工作的工程师人数

SELECTCOUNT（WorkerID）ASWorkerCount

FROMEnroll

WHEREProjectID='101'

/*

聚合函数对符合条件的记录的指定字段进行统计查询

五个聚合函数：

MIN、MAX、COUNT、SUM、AVERAGE

注意各自的特点

可以先用WHERE进行记录选择

*/

--例：

求工程师年龄的个数，和工程师个数

--    以及性别的种类数

SELECTCOUNT（WorkerAge）ASAgeCount,

   COUNT（*）ASWorkerCount,

   COUNT（DISTINCTWorkerSex）ASSexCount

FROMWorker

/*

注意观察：

聚合函数对空值（NULL）的处理

*/

--例：

查询各个项目所发放的总工资

--思路，即将各工程师参加项目的月津贴乘以月份，再累加

SELECTSUM（MonthSalary*MonthCount）ASTotalSalary

FROMEnroll

/*

假如要对不同类型的数据分别进行统计，怎么进行？

*/

--例：

计算每个工程师参加的项目数量

SELECTWorkerID,COUNT（*）ASProjectCount

FROMEnroll

GROUPBYWorkerID

/*

GROUPBY子句首先将记录根据指定的字段进行分组

然后对各个组分别计算汇总

*/

--例：

查询每个项目参与的工程师数量，以及月工资总和

SELECTProjectID,COUNT（*）ASWorkerCount,

   SUM（MonthSalary）ASSalarySum

FROMEnroll

GROUPBYProjectID

/*

特别的，分组查询时，在SELECT中出现的字段只能是以下两种

   分组字段

   聚合函数

如：

SELECTWorkerNameFROMWorkerGROUPBYCompanyID

是非法的，为什么？

*/

/*

在进行聚合查询之前，可以用WHERE子句对记录进行选择

那么假如需要对聚合后的结果进行选择，用什么办法？

HAVING子句：

用于对分组后的结果进行筛选

*/

--例：

查询公司人数超过2人的公司编号，以及平均工资

SELECTCompanyID,AVG（Salary）ASAvgSalary

FROMWorker

GROUPBYCompanyID

HAVINGCOUNT（*）>=2

/*

注意HAVING和WHERE的区别

*/

/*

以上各种查询可以混合使用

*/

--例：

查出拥有高级员工（指工资超过3000的工程师）

--    超过2人的公司

--并计算高级员工的人数、平均工资和最高工资

/*

当包含多个子句进行查询时，各子句被执行的顺序是：

FROM-->WHERE-->GROUPBY-->HAVING-->

SELECT-->ORDER-->TOP

*/

/*

连接查询

连接查询就是对多个表进行连接，将多个表的数据结合在一起

通常，连接的表都是有外键关系的表

连接查询就是关系连接运算的实现

连接运算是为了将设计时拆分的表组合起来

*/

--例：

查询出每个工程师的信息，以及他工作的单位信息

SELECTWorker.WorkerName,Company.CompanyName

FROMWorkerINNERJOIN

  CompanyONWorker.CompanyID=Company.CompanyID

/*

连接查询的主要工作在FROM子句中

基本语法为：

FROM<表1><连接类型>JOIN<表2>ON<连接条件>

连接类型包括：

内连接、外连接、交叉连接等

下面叙述的主要针对内连接

连接条件，往往是两个表之间的关联字段，一般是等值比较

*/

--例：

查询包括工程师信息的参加工作情况

/*

对于连接查询而言，经过JOIN的两个表，构成了一个新表

我们在之前所讲述的所有查询手段，都可以应用于这个新表

*/

--例：

查询工资超过3000的工程师的姓名和公司名称、电话

/*

在连接查询中，字段可以用<表名>.<字段名>来引用

尤其对于两边同名的字段，必须加表名进行限定

对于重名的字段，往往需要用字段别名的方式在结果中加以区分

名称不重复的字段，可以不限定，但是建议限定

单表查询也可以在字段名前加表名进行限定，但是往往不需要

*/

/*

查询中为了简化书写，可以给表起别名

FROM<表>[AS]<别名>

哪怕是单表查询也可以加别名，但是没有太大意义

别名往往用简单的字母A、B、C等，但往往用表名的简称

*/

--例：

用表别名的方式重写上面的例子

SELECTw.WorkerName,co.CompanyName

FROMWorkerwINNERJOIN

  CompanycoONw.CompanyID=co.CompanyID

/*

自然连接

在数据库的设计中，关联的字段（外键）往往会采取和主键表相同的命名

此时我们可以写成自然连接

语法：

FROM<外键表>NATURALJOIN<主键表>

不需要说明任何条件，自动找同名字段进行等值连接

但是：

SQLServer不提供本语法，以上语法在Oracle中提供

通常我们用INNERJOIN替代

*/

/*

在实际应用中，参与连接的表可以不止两个，可以为多个

多表连接，一般是分部进行的，以内连接为例，语法如下：

FROM<表1>INNERJOIN<表2>ON<条件>INNERJOIN<表3>ON<条件>

我们可以理解为先连接前两个，形成一个新表，然后新表再连接第三个表

*/

--例：

查询完整的参加工作情况，包括工程师、公司和工程项目信息

/*

多表连接时，我们可以用括号指定连接顺序

对于内连接，因为满足交换律、结合律，因此都是等价的

*/

--例：

查询在上海的公司中工资超过3000的工程师的人数、平均工资

/****************************

*         预习

*自连接

*外连接

*嵌套查询（子查询）

****************************/

--1.

SELECT*FROMWorker

--2.

SELECTWorkerNameAS姓名,SalaryFROMWorker

--3

SELECTWorkerID,Salary/22.5FROMWorker

SELECTWorkerID,Convert（Int,Salary/22.5）FROMWorker

SELECTWorkerID,CAST（Salary/22.5ASINT）FROMWorker

SELECTWorkerID,Salary/22.5ASDaySalaryFROMWorker

SELECTWorkerIDAS员工号,Salary/22.5AS日工资FROMWorker

SELECTWorkerID员工号,Salary/22.5日工资FROMWorker

SELECT员工号=WorkerID,日工资=Salary/22.5FROMWorker

SELECT[INT员"工"号]=WorkerID,Salary/22.5"日[工]资"

FROMWorker

--4.

Select*FromEnroll

WhereMonthCount>15

--5.

Select*

FromCompany

Where