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

1、SQL sever 各种查询语句和建表语句SQL sever 2000各种查询语句和建表语句2009-10-29 00:21CREATE TABLE Company(CompanyID CHAR(3) NOT NULL CONSTRAINT PK_Company PRIMARY KEY,CompanyName NVARCHAR(10) NOT NULL,CompanyAddress NVARCHAR(50) NULL)CREATE TABLE Worker(WorkerID CHAR(5) NOT NULL CONSTRAINT PK_Worker PRIMARY KEY,WorkerName

2、 NVARCHAR(5) NOT NULL,WorkerSex NCHAR(1) NOT NULL CONSTRAINT CK_Worker_WorkerSex CHECK (WorkerSex IN (男, 女),WorkerAge TINYINT NULL,WorkerJob NVARCHAR(10) NULL,Salary INT NULL,CompanyID CHAR(3) NULL CONSTRAINT FK_Worker_Company FOREIGN KEY REFERENCES Company(CompanyID)CREATE TABLE Project(ProjectID C

3、HAR(3) NOT NULL CONSTRAINT PK_Project PRIMARY KEY,ProjectName NVARCHAR(20) NOT NULL,ProjectPlace NVARCHAR(10) NULL)CREATE TABLE Enroll(WorkerID CHAR(5) NOT NULL,ProjectID CHAR(3) NOT NULL,Job NVARCHAR(10) NULL,MonthCount INT NULL,MonthSalary INT NULL,CONSTRAINT PK_Enroll PRIMARY KEY (WorkerID, Proje

4、ctID),CONSTRAINT FK_Enroll_Worker FOREIGN KEY (WorkerID) REFERENCES Worker(WorkerID),CONSTRAINT FK_Enroll_Project FOREIGN KEY (ProjectID) REFERENCES Project(ProjectID)INSERT INTO Company (CompanyID, CompanyName, CompanyAddress)VALUES (A01, 北京公司, 北京海淀区)INSERT INTO Company (CompanyID, CompanyName, Com

5、panyAddress)VALUES (B24, 上海公司, 上海闵行区)INSERT INTO Company (CompanyID, CompanyName, CompanyAddress)VALUES (C13, 福建公司, 福建福州鼓楼区)INSERT INTO Company (CompanyID, CompanyName, CompanyAddress)VALUES (D00, 台湾%公司, 台北县台北市)INSERT INTO Worker (WorkerID, WorkerName, WorkerSex, WorkerAge, WorkerJob, Salary, Compan

6、yID)VALUES (A0101, 黄伟强, 男, 33, 工程师, 2100, A01)INSERT INTO Worker (WorkerID, WorkerName, WorkerSex, WorkerAge, WorkerJob, Salary, CompanyID)VALUES (A0102, 陈至, 男, 36, 高级工程师, 4300, A01)INSERT INTO Worker (WorkerID, WorkerName, WorkerSex, WorkerAge, WorkerJob, Salary, CompanyID)VALUES (B2424, 林发清, 女, 43

7、, 高级工程师, 5000, B24)INSERT INTO Worker (WorkerID, WorkerName, WorkerSex, WorkerAge, WorkerJob, Salary, CompanyID)VALUES (C1313, 陈华仁, 男, 35, NULL, 2500, C13)INSERT INTO Worker (WorkerID, WorkerName, WorkerSex, WorkerAge, WorkerJob, Salary, CompanyID)VALUES (C1315, 傅星, 女, 23, 助理工程师, 2000, C13)INSERT IN

8、TO Worker (WorkerID, WorkerName, WorkerSex, WorkerAge, WorkerJob, Salary, CompanyID)VALUES (X0001, 赵苑言, 女, NULL, NULL, 3000, NULL)INSERT INTO Project (ProjectID, ProjectName, ProjectPlace)VALUES (101, 国道, 四川成都)INSERT INTO Project (ProjectID, ProjectName, ProjectPlace)VALUES (202, 高速公路, NULL)INSERT I

9、NTO Project (ProjectID, ProjectName, ProjectPlace)VALUES (303, 大桥, 天津南开区)INSERT INTO Enroll (WorkerID, ProjectID, Job, MonthCount, MonthSalary)VALUES (A0101, 101, 项目经理, 18, 1000)INSERT INTO Enroll (WorkerID, ProjectID, Job, MonthCount, MonthSalary)VALUES (A0101, 202, NULL, 12, 800)INSERT INTO Enroll

10、 (WorkerID, ProjectID, Job, MonthCount, MonthSalary)VALUES (A0102, 101, NULL, 23, 800)INSERT INTO Enroll (WorkerID, ProjectID, Job, MonthCount, MonthSalary)VALUES (A0102, 202, NULL, 17, 650)INSERT INTO Enroll (WorkerID, ProjectID, Job, MonthCount, MonthSalary)VALUES (C1313, 303, 施工员, 14, 800)INSERT

11、INTO Enroll (WorkerID, ProjectID, Job, MonthCount, MonthSalary)VALUES (C1315, 202, 施工员, 15, 900)INSERT INTO Enroll (WorkerID, ProjectID, Job, MonthCount, MonthSalary)VALUES (X0001, 101, 监理, 20, 1000)- 1.SELECT * FROM Worker- 2.SELECT WorkerName AS 姓名, Salary FROM Worker- 3SELECT WorkerID, Salary/22.

12、5 FROM WorkerSELECT WorkerID, Convert(Int, Salary/22.5) FROM WorkerSELECT WorkerID, CAST(Salary/22.5 AS INT) FROM WorkerSELECT WorkerID, Salary/22.5 AS DaySalary FROM WorkerSELECT WorkerID AS 员工号, Salary/22.5 AS 日工资 FROM WorkerSELECT WorkerID 员工号, Salary/22.5 日工资 FROM WorkerSELECT 员工号=WorkerID, 日工资=

13、Salary/22.5 FROM WorkerSELECT INT 员工号=WorkerID, Salary/22.5 日工资FROM Worker- 4.Select * From EnrollWhere MonthCount 15- 5.Select *From CompanyWhere A01 = CompanyID- 6 Select ProjectNameFrom ProjectWhere ProjectPlace = 四川-7 Select *From WorkerWhere WorkerName LIKE 陈%-8Select *From WorkerWhere WorkerNa

14、me LIKE %言-9Select *From WorkerWhere WorkerName LIKE 张%言-10 Select *From CompanyWhere CompanyName LIKE %司%Select *From CompanyWhere CompanyName LIKE %Select *From WorkerWhere WorkerID LIKE _01_2Select *From WorkerWhere WorkerName LIKE 陈_-11Select *From WorkerWhere WorkerName LIKE 傅赵%-12Select *From

15、WorkerWhere WorkerName LIKE 傅赵%Select *From WorkerWhere WorkerName NOT LIKE 傅赵%Select *From WorkerWhere NOT WorkerName LIKE 傅赵%-13Select *From CompanyWhere CompanyAddress IN (北京海淀区, 上海闵行区)-13.5Select *From CompanyWhere CompanyName LIKE %+% ESCAPE +-14Select *From WorkerWhere WorkerAge Between 20 And

16、 30-15Select *From WorkerWhere WorkerAge NOT Between 20 And 30Select *From WorkerWhere NOT WorkerAge Between 20 And 30-17Select *From WorkerWhere WorkerAge IS NULLSelect *From WorkerWhere WorkerAge IS NOT NULL-19Select *From WorkerOrder By SalarySelect *From WorkerOrder By Salary ASC-20Select *From

17、WorkerOrder By Salary DESC-21Select *From WorkerOrder By CompanyID, Salary DESC-22Select TOP 1 *From WorkerOrder By WorkerAge Asc-23Select Distinct CompanyIDFrom Worker/* 回顾* 索引的概念* 简单查询* 选择全部、部分字段* 计算字段* 字段重命名* 选择查询 WHERE* 基本的比较条件 = = ! != 2/*注意HAVING和WHERE的区别*/*以上各种查询可以混合使用*/- 例：查出拥有高级员工（指工资超过3000

18、的工程师）- 超过2人的公司- 并计算高级员工的人数、平均工资和最高工资/*当包含多个子句进行查询时，各子句被执行的顺序是：FROM - WHERE - GROUP BY -HAVING -SELECT - ORDER - TOP*/*连接查询连接查询就是对多个表进行连接，将多个表的数据结合在一起通常，连接的表都是有外键关系的表连接查询就是关系连接运算的实现连接运算是为了将设计时拆分的表组合起来*/- 例：查询出每个工程师的信息，以及他工作的单位信息SELECT Worker.WorkerName, Company.CompanyNameFROM Worker INNER JOIN Compa

19、ny ON Worker.CompanyID = Company.CompanyID/*连接查询的主要工作在FROM子句中基本语法为：FROM JOIN ON 连接类型包括：内连接、外连接、交叉连接等下面叙述的主要针对内连接连接条件，往往是两个表之间的关联字段，一般是等值比较*/- 例：查询包括工程师信息的参加工作情况/*对于连接查询而言，经过JOIN的两个表，构成了一个新表我们在之前所讲述的所有查询手段，都可以应用于这个新表*/- 例：查询工资超过3000的工程师的姓名和公司名称、电话/*在连接查询中，字段可以用.来引用尤其对于两边同名的字段，必须加表名进行限定对于重名的字段，往往需要用字段

20、别名的方式在结果中加以区分名称不重复的字段，可以不限定，但是建议限定单表查询也可以在字段名前加表名进行限定，但是往往不需要*/*查询中为了简化书写，可以给表起别名FROM AS 哪怕是单表查询也可以加别名，但是没有太大意义别名往往用简单的字母A、B、C等，但往往用表名的简称*/- 例：用表别名的方式重写上面的例子SELECT w.WorkerName, co.CompanyNameFROM Worker w INNER JOIN Company co ON w.CompanyID = co.CompanyID/*自然连接在数据库的设计中，关联的字段（外键）往往会采取和主键表相同的命名此时我们可

21、以写成自然连接语法：FROM NATURAL JOIN 不需要说明任何条件，自动找同名字段进行等值连接但是：SQL Server不提供本语法，以上语法在Oracle中提供通常我们用INNER JOIN替代*/*在实际应用中，参与连接的表可以不止两个，可以为多个多表连接，一般是分部进行的，以内连接为例，语法如下：FROM INNER JOIN ON INNER JOIN ON 我们可以理解为先连接前两个，形成一个新表，然后新表再连接第三个表*/- 例：查询完整的参加工作情况，包括工程师、公司和工程项目信息/*多表连接时，我们可以用括号指定连接顺序对于内连接，因为满足交换律、结合律，因此都是等价的

22、*/- 例：查询在上海的公司中工资超过3000的工程师的人数、平均工资/* 预习* 自连接* 外连接* 嵌套查询（子查询）*/- 1.SELECT * FROM Worker- 2.SELECT WorkerName AS 姓名, Salary FROM Worker- 3SELECT WorkerID, Salary/22.5 FROM WorkerSELECT WorkerID, Convert(Int, Salary/22.5) FROM WorkerSELECT WorkerID, CAST(Salary/22.5 AS INT) FROM WorkerSELECT WorkerID,

23、 Salary/22.5 AS DaySalary FROM WorkerSELECT WorkerID AS 员工号, Salary/22.5 AS 日工资 FROM WorkerSELECT WorkerID 员工号, Salary/22.5 日工资 FROM WorkerSELECT 员工号=WorkerID, 日工资=Salary/22.5 FROM WorkerSELECT INT 员工号=WorkerID, Salary/22.5 日工资FROM Worker- 4.Select * From EnrollWhere MonthCount 15- 5.Select *From CompanyWhere