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

SQL sever 2000各种查询语句和建表语句CREATE 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 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 CHAR(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, ProjectID),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, CompanyAddress)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, CompanyID)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, 高级工程师, 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 INTO 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 INTO 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 (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 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.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, 日工资=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 WorkerName 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 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 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 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的工程师）- 超过2人的公司- 并计算高级员工的人数、平均工资和最高工资/*当包含多个子句进行查询时，各子句被执行的顺序是：FROM - WHERE - GROUP BY -HAVING -SELECT - ORDER - TOP*/*连接查询连接查询就是对多个表进行连接，将多个表的数据结合在一起通常，连接的表都是有外键关系的表连接查询就是关系连接运算的实现连接运算是为了将设计时拆分的表组合起来*/- 例：查询出每个工程师的信息，以及他工作的单位信息SELECT Worker.WorkerName, Company.CompanyNameFROM Worker INNER JOIN Company ON Worker.CompanyID = Company.CompanyID/*连接查询的主要工作在FROM子句中基本语法为：FROM JOIN ON 连接类型包括：内连接、外连接、交叉连接等下面叙述的主要针对内连接连接条件，往往是两个表之间的关联字段，一般是等值比较*/- 例：查询包括工程师信息的参加工作情况/*对于连接查询而言，经过JOIN的两个表，构成了一个新表我们在之前所讲述的所有查询手段，都可以应用于这个新表*/- 例：查询工资超过3000的工程师的姓名和公司名称、电话/*在连接查询中，字段可以用.来引用尤其对于两边同名的字段，必须加表名进行限定对于重名的字段，往往需要用字段别名的方式在结果中加以区分名称不重复的字段，可以不限定，但是建议限定单表查询也可以在字段名前加表名进行限定，但是往往不需要*/*查询中为了简化书写，可以给表起别名FROM AS 哪怕是单表查询也可以加别名，但是没有太大意义别名往往用简单的字母A、B、C等，但往往用表名的简称*/- 例：用表别名的方式重写上面的例子SELECT w.WorkerName, co.CompanyNameFROM Worker w INNER JOIN Company co ON w.CompanyID = co.CompanyID/*自然连接在数据库的设计中，关联的字段（外键）往往会采取和主键表相同的命名此时我们可以写成自然连接语法：FROM NATURAL JOIN 不需要说明任何条件，自动找同名字段进行等值连接但是：SQL Server不提供本语法，以上语法在Oracle中提供通常我们用INNER JOIN替代*/*在实际应用中，参与连接的表可以不止两个，可以为多个多表连接，一般是分部进行的，以内连接为例，语法如下：FROM INNER JOIN ON INNER JOIN ON 我们可以理解为先连接前两个，形成一个新表，然后新表再连接第三个表*/- 例：查询完整的参加工作情况，包括工程师、公司和工程项目信息/*多表连接时，我们可以用括号指定连接顺序对于内连接，因为满足交换律、结合律，因此都是等价的*/- 例：查询在上海的公司中工资超过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, 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 A01 = CompanyID- 6 Select ProjectNameFrom ProjectWhere ProjectPlace = 四川-7 Select *From WorkerWhere WorkerName LIKE 陈%-8Select *From WorkerWhere WorkerName 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 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 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 WorkerOrder By Salary DESC-21Select *From WorkerOrder By CompanyID, Salary DESC-22Select TOP 1 *From WorkerOrder By WorkerAge Asc-23Select Distinct CompanyIDFrom WorkerCreate Table City( CityID Char(4) Primary Key, CityName nvarchar(10), UpCityID Char(4)INSERT INTO City(CityID, CityName, UpCityID) VALUES (0590, 福建, NULL)INSERT INTO City(CityID, CityName, UpCityID) VALUES (0591, 福州, 0590)INSERT INTO City(CityID, CityName, UpCityID) VALUES (0592, 厦门, 0590)SELECT * FROM CitySELECT *FROM City c1 INNER JOIN City c2 ON c1.UpCityID = c2.CityID/* 回顾 * * 分组查询 * 聚合函数 * GROUP BY 子句 * HAVING 子句 * 混合使用 * * 连接查询 * 等值连接 */*聚合函数：SUM(ALL|DISTINCT )AVG(ALL|DISTINCT )COUNT(* | ALL|DISTINCT )MIN()MAX()注意：聚合函数对空值（NULL）的处理*/*GROUP BY 子句首先将记录根据指定的字段进行分组然后对各个组分别计算汇总*/*特别的，分组查询时，在SELECT中出现的字段只能是以下两种 分组字段 聚合函数*/*HAVING 子句：用于对分组后的结果进行筛选注意HAVING和WHERE的区别*/*以上各种查询可以混合使用*/*当包含多个子句进行查询时，各子句被执行的顺序是：FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER - TOP*/*连接查询连接查询就是对多个表进行连接，将多个表的数据结合在一起连接查询的主要工作在FROM子句中基本语法为：FROM JOIN ON 连接类型包括：内连接、外连接、交叉连接等对于连接查询而言，经过JOIN的两个表，构成了一个新表之前所讲述的所有查询手段，都可以应用于这个新表*/*在连接查询中，字段可以用.来引用尤其对于两边同名的字段，必须加表名进行限定对于重名的字段，往往需要用字段别名的方式在结果中加以区分名称不重复的字段，可以不限定，但是建议限定单表查询也可以在字段名前加表名进行限定，但是往往不需要*/*查询中为了简化书写，可以给表起别名FROM AS 哪怕是单表查询也可以加别名*/* 本次课 * * 连接查询 * 自然连接 * 多表连接 * 连接分组查询 * 不等值连接 * 自连接 * 外连接、交叉连接 * 多表外连接 * * 嵌套查询（子查询） * 概念 * IN谓词 * 相关子查询与不相关子查询 */*自然连接在数据库的设计中，关联的字段（外键）往往会采取和主键表相同的命名此时我们可以写成自然连接语法：FROM NATURAL JOIN 不需要说明任何条件，自动找同名字段进行等值连接但是：SQL Server不提供本语法，以上语法在Oracle中提供通常我们用INNER JOIN替代*/*在实际应用中，参与连接的表可以不止两个，可以为多个多表连接，一般是分部进行的，以内连接为例，语法如下：FROM INNER JOIN ON INNER JOIN ON 我们可以理解为先连接前两个，形成一个新表，然后新表再连接第三个表*/- 例：查询完整的参加工作情况，包括工程师、公司和工程项目信息SELECT *FROM Worker w INNER JOIN Company c ON w.CompanyID = c.CompanyID INNER JOIN Enroll e ON w.WorkerID = e.WorkerID INNER JOIN Project p ON e.ProjectID = p.ProjectID- 例：查询各个公司参与的项目情况，列出公司名称和项目名称SELECT c.CompanyName, p.ProjectNameFROM Worker w INNER JOIN Company c ON w.CompanyID = c.CompanyID INNER JOIN Enroll e ON w.WorkerID = e.WorkerID INNER JOIN Project p ON e.ProjectID = p.ProjectID/*多表连接时，有时某些表只是起到中间过度作用，在最终结果中不出现*/*多表连接时，我们可以用括号指定连接顺序对于内连接，因为满足交换律、结合律，因此都是等价的*/SELECT c.CompanyName, p.ProjectNameFROM (Worker w INNER JOIN Company c ON w.CompanyID = c.CompanyID) INNER JOIN Enroll e ON w.WorkerID = e.WorkerID) INNER JOIN Project p ON e.ProjectID = p.ProjectIDSELECT c.CompanyName, p.ProjectNameFROM (Worker w INNER JOIN Company c ON w.CompanyID = c.CompanyID) INNER JOIN (Enroll e INNER JOIN Project p ON e.ProjectID = p.ProjectID) ON w.WorkerID = e.WorkerID/*连接查询可以和分组等查询相结合*/- 例：查询在上海的公司中工资超过3000的工程师的人数、平均工资SELECT COUNT(*) AS WorkerCount, AVG(Salary) AS AvgSalaryFROM Worker w INNER JOIN Company c ON w.CompanyID = c.CompanyID WHERE c.CompanyAddress LIKE %上海% AND w.Salary = 2000- 例：查询各公司名称，以及各公司人数、平均工资SELECT *FROM Worker w INNER JOIN Company c ON w.CompanyID = c.CompanyIDSELECT c.CompanyName, COUNT(*) AS WorkerCount, AVG(w.Salary) AS AvgSalaryFROM Worker w INNER JOIN Company c ON w.CompanyID = c.CompanyID GROUP BY c.CompanyNameSELECT c.CompanyName, COUNT(*) AS WorkerCount, AVG(w.Salary) AS AvgSalaryFROM Worker w INNER JOIN Company c ON w.CompanyID = c.CompanyID GROUP BY c.CompanyIDSELECT c.CompanyName, COUNT(*) AS WorkerCount, AVG(w.Salary) AS AvgSalaryFROM Worker w INNER JOIN Company c ON w.CompanyID = c.CompanyID GROUP BY c.CompanyID, c.CompanyNameSELECT MIN(c.CompanyName) AS CompanyName, COUNT(*) AS WorkerCount, AVG(w.Salary) AS AvgSalaryFROM Worker w INNER JOIN Company c ON w.CompanyID = c.CompanyID GROUP BY c.CompanyID/*注意：本例中的公司名称问题！*/- 例：计算每位工程师在工程项目中每个月能获得的总收入- 不包括其基本工资/*连接的另外一种写法：将条件写在WHERE中这种做法基本上是等价的，但是不建议这样做，因为容易将选择条件和连接条件混在一起，不利于阅读*/- 例：将上面几个查询用WHERE改写SELECT c.CompanyName, p.ProjectNameFROM Worker w, Company c, Enroll e, Project p WHERE w.CompanyID = c.CompanyID AND w.WorkerID = e.WorkerID AND e.ProjectID = p.ProjectIDSELECT COUNT(*) AS WorkerCount, AVG(Salary) AS AvgSalaryFROM Worker w, Company c WHERE c.CompanyAddress LIKE %上海% AND w.Salary = 2000 AND w.CompanyID = c.CompanyID/*在连接中，连接条件为不等号时，称为不等值连接不等值连接比较少用*/*自连接当参加连接的两个表是同一个表的时候，称为自连接自连接往往用于表中的记录自己和自己有关联的时候在自连接中，至少一个表要用别