sql代码.docx

1、sql代码-回顾我们之前的创建数据库和表的代码我们会发现create database myOffice-创建数据库(默认创建的是C盘)drop database myOffice-删除数据库create database myOffice on primary-创建主数据库文件（可以创建到我们指定的地方）(-每个数据库都有一个主数据库也是唯一的一个 name=myOffice_DB, -命名方式有：驼峰式 例如stuName,stuId首字母小写，其他字母都大写。 -帕斯卡式例如StuInfo首字母全都大写 filename=D:lollymyOffice_DB.mdf,-主数据库文件的后缀

2、名是.mdf size=5mb,-初始大小最小5mb maxsize=1000mb,-最大范围 filegrowth=15%-文件的自动增长（不能大于最大范围）),( name=myOfficeNDB,-创建此数据库文件，一个数据库可以有多个次数据库文件 filename=D:lollymyOfficeNDB.ndf,-次数据库的文件的后缀名为.ndf size=5mb, maxsize=500mb, filegrowth=30mb)log on-创建日志文件(一个数据库可以有多个日志文件)( name=myOffice_log,-文件的名称 filename=D:lollymyOffice_

3、log.ldf,-日志文件的后缀名是.ldf size=2mb,-初始大小 maxsize=1000mb,-最大尺寸 filegrowth=20mb-文件的自动增长)go-修改数据库alter database myOffice modify file(name=myOffice_DB,size=40mb,maxsize=400mb)gouse myOffice-使用数据库gosp_spaceused-存储过程create table student-创建表( stuId int primary key identity(1,1) not null,-主键和自动增长列 stuName varc

4、har(8) not null)-DQLselect * from student-查询所有信息-=DMLinsert into student values(wangwu)-插入语句insert into student values(lisi)insert into student values(zhangsan)insert into student values(zhaoliu)-更新语句update student set stuName=王小六where stuId=3-删除表数据delete student where stuId=1-注视，单行注视和多行注释/*在创建表的时候我

5、们也是需要考虑数据类型的，分为结构化，半结构化，非结构化的数据类型*/-数据类型-数值,字符串,日期和时间, 二进制declare a nvarchar(4)set a=aselect a as 日期-char varchar nchar nvarchar的区别-查询数据2.5页/*char： 它用于将固定长度字符数据储存在变量中， char(50)-10-40(浪费)varchar:它用于将可变长度字符数据存储在变量中， 它在变量声明的时候不保存内存空间。相反 它根据存储在变量中的数据大小分配内存。 varchar(100) 50 就生成50个字符的空间， 不会有内存损失nchar: 它支持

6、固定长度最长为4000个字符的unicode数据。 它需要的存储空间是char数据类型所需存储空间的两倍。nvarchar:它分配类似于varchar数据类型的内存。 但是，它只存储unicode字符。 他需要的是varchar所需存储空间的两倍。其中所说的Unicode是国际码，表示字符的行业通用计算标准。它为每个字符分配唯一的数字，以使此类字符在不同的平台和语言中以相同方式表示。-国际化（WEB）*/-检索特定属性-USE AdventureWorks-数据库中不区分大小写，但是规则是以大写为准GOSELECT * FROM HumanResources.Employee-回顾查询全部信息

7、GOselect count(*)from HumanResources .Employee GO-查看部分列信息SELECT k.EmployeeID,k.NationalIDNumber FROM HumanResources .Employee as kgoSELECT EmployeeID, ContactID, LoginID, TitleFROM HumanResources.EmployeeGO-使用 AS 起别名-首先给表起别名/*SELECT * FROM HumanResources.Department AS aselect 姓名=a.Name ,- 指示 ,fff=a.

8、GroupName from HumanResources.Department aselect t.GroupName+-+t.Name 看一看from HumanResources.Department t*/-部分列信息查看可以使用多种方法- 使用as 其别名通过.操作符调用对应的列名比较方便。select a.DepartmentID,a.GroupNamefrom HumanResources.Department as ago-给类添加列头select a.DepartmentID 部门编号,a.GroupName 部门名称from HumanResources.Departmen

9、t a-此时as 可以省略go-小练习，查询员工表employee表中的所有信息（查询数据 2.9）-小练习，并查询任意三列信息-小练习，给这三列分别添加中文列头-通过字面值来进行标记显示字面值将在单独的列中打印-字面值用于显示用途-1SELECT 部门编号= DepartmentID,Department Name= Name FROM HumanResources.Department -2SELECT DepartmentID Department Number, Name Department Name FROM HumanResources.Department-3SELECT De

10、partmentID AS Department Number, Name AS Department Name FROM HumanResources.Departmentgo-字面值-自定义显示-显示用途SELECT EmployeeID, Designation-, TitleFROM HumanResources.Employeego-并置输出中的文本值-字符串类型SELECT Name + department comes under- + GroupName + group AS Department FROM HumanResources.Department select e.

11、ManagerID+e.EmployeeIDfrom HumanResources.Employee e go/*我们已经学过了关系运算符，现在开始学习其它运算符包括算术运算符，比较运算符，逻辑运算符，范围运算符*/-算数运算符都能在-select语句中以任何组合形式和数字常量一起使用SELECT EmployeeID, Rate, Per_Day_Rate = 8 * Rate FROM HumanResources.EmployeePayHistorygo-比较运算符-在where子句中可以使用比较运算符来指定条件-但是不能用在text,ntext,image-数据类型表达式中SELECT

12、 * FROM HumanResources.Department WHERE GroupName Research and DevelopmentgoSELECT EmployeeID, NationalIDNumber, Title, VacationHoursFROM HumanResources.Employee WHERE VacationHours 2000.00-5-. SELECT * FROM Sales.SalesOrderDetail WHERE ProductID=843-6. SELECT * FROM Sales.SalesOrderHeader WHERE Ord

13、erDate = 06-06-2004-7. SELECT Order ID = SalesOrderID, Order Quantity = OrderQty,Unit Price = UnitPrice, Total Cost = OrderQty * UnitPrice FROM Sales.SalesOrderDetail-8SELECT * FROM Sales.SalesOrderDetail WHERE LineTotal BETWEEN 2000 AND 2100-9SELECT Name, CountryRegionCode, SalesYTD FROM Sales.Sale

14、sTerritory WHERE TerritoryID = 1-10SELECT * FROM Sales.SalesOrderHeader WHERE TaxAmt = 10000-11SELECT * FROM Sales. SalesTerritory WHERE Name IN (Canada, France, Germany)-12SELECT Sales Person ID=SalesPersonID, Territory ID = TerritoryID FROM Sales.SalesTerritoryHistory WHERE TerritoryID = 2 OR Terr

15、itoryID = 4-13SELECT * FROM Sales.CreditCard WHERE CardType = Vista AND ExpYear = 2006-14SELECT * FROM Sales.SalesOrderHeader WHERE ShipDate 2004-07-12-15SELECT Order Number = SalesOrderID, Order Date = OrderDate, Status, Total Cost = TotalDue FROM Sales.SalesOrderHeader WHERE OrderDate = 07-01-2001

16、 AND TotalDue 10000-16SELECT * FROM Sales.SalesOrderHeader WHERE OnlineOrderFlag = 1-17SELECT Order ID = SalesOrderID, Total Due = TotalDue FROM Sales.SalesOrderHeader ORDER BY TotalDue DESC-18SELECT SalesOrderID, TaxAmt FROM Sales.SalesOrderHeader WHERE TotalDue 2000 ORDER BY TotalDue ASC-19SELECT

17、SalesOrderID, TotalDue FROM Sales.SalesOrderHeader ORDER BY TotalDue ASC-20SELECT * FROM Sales.Currency WHERE Name LIKE %Dollar%-21SELECT * FROM Sales.SalesTerritory WHERE Name LIKE N%-22SELECT Sales Person ID = SalesPersonID, Territory ID= TerritoryID, Sales Quota = SalesQuota FROM Sales.SalesPerson WHERE SalesQuota IS NOT NULL-23SELECT TOP 3* FROM Sales.SalesPerson ORDER BY bonus DESC-24SELECT * FROM Sales.Store WHERE Name LIKE %bike%-25SELECT DISTINCT CardType FROM Sales.CreditCard-26Select EmployeeId,LogInId,Titlefrom HumanResources.EmployeeOrder By EmployeeId