mssql sql语句简明教程

 

/*下面的例子将创建表S。*/

CREATE TABLE S

(

   sno  char(10)  NOT NULL       /*学号字段*/

         CONSTRAINT PK_sno PRIMARY KEY CLUSTERED/*主键约束*/

         CHECK (sno like '31300501[0-9][0-9]')/*检查约束*/,

   sname      char(8)     NULL, /*姓名字段*/

   sex     char(2)   NULL, /*性别字段*/

   age    int  NULL, /*年龄字段*/

   dept   varchar(20)   NULL/*系别字段*/

  )

 

/*修改S表，增加一个班号列。*/

/*程序清单如下：*/

ALTER TABLE S 

ADD

CLASS_NO CHAR(6) 

/*删除S表。*/

/*程序清单如下：*/

   DROP table S

/*在S表中插入一条学生记录（学号：S7；姓名：郑冬；性别：女；年龄：21；系别：计算机）。*/

/*程序清单如下：*/

INSERT INTO  S VALUES ('3130050107','郑冬','女',21,'计算机')

/* 创建SC表（学生选课表），并向SC表中插入一条选课记录（’S7’,’C1’）。*/

/*程序清单如下：*/

CREATE TABLE SC

(

   sno  char(10)  NOT NULL, 

   cno char(2)    NULL, /*课程编号字段*/

  score  numeric(4,1) NULL  /*成绩字段*/

)

Go

INSERT INTO SC (sno,cno) VALUES ('3130050101', 'c1')

Go

/* 使用 column_list 及 VALUES 列表显式地指定将被插入每个列的值。*/

/*程序清单如下：*/

CREATE TABLE T1

 ( column_1 int, 

column_2 varchar(30)) 

Go

INSERT T1 (column_2, column_1) VALUES ('This is a test',1) 

/*求出各位学生的平均成绩，把结果存放在新表AVGSCORE中。

程序清单如下：*/

/*首先建立新表AVGSCORE，用来存放学号和学生的平均成绩。*/

CREATE TABLE AVGSCORE

(SNO CHAR(10),

AVGSCORE SMALLINT) 

Go

/*利用子查询求出SC表中各位学生的平均成绩，把结果存放在新表AVGSCORE中。*/

INSERT INTO AVGSCORE

SELECT SNO,AVG(SCORE) 

FROM SC

GROUP BY SNO 

/*一个带有WHERE条件的修改语句。*/

/*程序清单如下：*/

use adventureworks

go

update person.address 

set city='Boston' where  addressid=1

/* 将所有学生年龄增加1岁*/

/*程序清单如下：*/

UPDATE S SET AGE=AGE+1

/*下面的示例修改 SalesPerson 表中的 SalesYTD 列，以反映 SalesOrderHeader 表中记录的最近销售情况。*/

/*程序清单如下：*/

USE AdventureWorks;

GO

UPDATE Sales.SalesPerson SET SalesYTD = SalesYTD + SubTotal

FROM Sales.SalesPerson AS sp JOIN Sales.SalesOrderHeader AS so

    ON sp.SalesPersonID = so.SalesPersonID

    AND so.OrderDate = (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader 

              WHERE SalesPersonID = sp.SalesPersonID)

/*将UPDATE语句与TOP子句一起使用，更新Employee 表中10个随机行的VacationHours 列，使之变成原来的1.25倍。*/

/*程序清单如下：*/

USE AdventureWorks;

GO

UPDATE TOP (10) HumanResources.Employee

SET VacationHours = VacationHours * 1.25

/* 创建T表（教师基本情况表）的结构为T(TNO,TN,SEX,AGE,PROF,SAL,DEPT)分别表示教师的编号，姓名，性别，年龄，职称，工资，系别。TC表（教师授课表）的结构为TC(TNO,CNO)分别表示教师的编号，课程编号。*/

USE example

GO

CREATE TABLE t

(

TNO char(10) NOT NULL

CONSTRAINT PK_TNO PRIMARY KEY,/*教师编号字段*/

TN char(10) NULL,/*教师姓名字段*/

SEX char(2) NULL,/*教师性别字段*/

AGE int NULL,/*教师年龄字段*/

PROF char(20) NULL,/*教师职称字段*/

SAL int NULL,/*教师工资字段*/

DEPT char(10) NULL,/*教师所在系别字段*/ 

)

GO

INSERT INTO t VALUES('0001','张老师','男',41,'副教授',2200,'电力系')

GO

INSERT INTO t VALUES('0002','张益琳','女',32,'讲师',1500,'动力系')

GO

CREATE TABLE TC

(

TNO char(10) NOT NULL,/*教师编号字段*/

CNO char(10) NULL,/*教师执教课程字段*/ 

)

GO

INSERT INTO tc VALUES('0001','C5')

GO

/*把讲授C5课程的教师的工资增加100元。*/

/*程序清单如下：*/

UPDATE T  SET SAL=SAL+100

WHERE TNO IN  

(SELECT T.TNO FROM T,TC

WHERE T.TNO=TC.TNO AND TC.CNO='C5')

 /*通过连接查询找到讲授C5课程的教师编号。*/

/* 把所有教师的工资提高到平均工资的1.2倍*/

/*程序清单如下：*/

UPDATE T SET SAL =(SELECT 1.2*AVG(SAL) FROM T)

/* 删除张益琳教师的记录。*/

/*程序清单如下：*/

DELETE FROM T WHERE TN='张益琳'

/*删除所有教师的授课记录。*/

/*程序清单如下：*/

DELETE FROM TC

/*删除李明同学选课的记录。*/

DELETE FROM SC WHERE SNO=  

(SELECT SNO FROM S  WHERE sname='李明')

/*给用户 Mary 授予多个语句权限。*/

/*程序清单如下：*/

GRANT CREATE DATABASE, CREATE TABLE

TO Mary

/* 为用户ZhangYiLin授予CREATE TABLE的语句权限。*/

/*程序清单如下：*/

GRANT CREATE TABLE 

TO ZhangYiLin

/*在权限层次中授予对象权限。首先，给所有用户授予SELECT权限，然后，将特定的权限授予用户Mary、John和Tom*/

/*程序清单如下：*/

GRANT SELECT

ON s                          /*需先创建s表*/

TO public

GO 

GRANT INSERT, UPDATE, DELETE

ON s

TO Mary, John, Tom          /*需首先创建用户Mary, John, Tom */

GO

/*  将查询T表和修改教师职称的权限授予USER3，并允许将此权限授予其他用户。*/

/*程序清单如下：*/

GRANT SELECT,UPDATE

ON T TO user3

WITH grant OPTION

/*上例中，USER3具有此对象权限，并可使用GRANT命令给其他用户授权，如下例，USER3将此权限授予USER4：*/

GRANT SELECT,UPDATE(PROF)

ON T

TO USER4

/* 收回用户USER1对jobs表的查询权限。*/

/*程序清单如下：*/

REVOKE SELECT 

ON jobs

FROM mary

/*收回用户mary查询T表和修改教师职称的权限。*/

/*程序清单如下：*/

REVOKE SELECT,UPDATE(PROF)

ON T

FROM mary

/*首先从 public 角色中收回 SELECT 权限，然后，收回用户 Mary,Jhon和Tom的特定权限。*/

/*程序清单如下：*/

REVOKE  SELECT

ON s FROM public

GO

REVOKE  INSERT, UPDATE, DELETE

ON s FROM Mary, John, Tom

/*首先给public角色授予SELECT权限，然后拒绝用户Mary、John和Tom的特定权限*/

/*程序清单如下：*/

GRANT SELECT

ON s

TO public

GO

DENY SELECT, INSERT, UPDATE, DELETE

ON s

TO Mary,John,Tom

/*使用两种注释类型的例子。*/

/*程序清单如下：*/

USE AdventureWorks

GO

-- First line of a multiple-line comment.

-- Second line of a multiple-line comment.

SELECT * FROM person.address

GO

/* 注释语句的第一行.

  注释语句的第二行. */

SELECT * FROM Production.Product

GO

-- 在Transact-SQL语言调试过程中使用注释语句。

SELECT title, /* birthdate, */ hiredate

FROM humanresources.employee

-- 在代码后面加上注释。

USE AdventureWorks 

GO

UPDATE sales.salesorderdetail

SET unitprice = unitprice * .9 -- 降低价格以提高市场份额。

GO

/*创建一个@myvar 变量，然后将一个字符串值放在变量中，最后输出 @myvar 变量的值。*/

/*程序清单如下：*/

DECLARE @myvar  char(20)

select  @myvar = 'This is a test'

SELECT @myvar

GO

/*通过查询给变量赋值。*/

/*程序清单如下：*/

USE adventureworks

GO

DECLARE @rows int

SET @rows = (SELECT COUNT(*)  FROM  humanresources.employee)

/*在SELECT语句中使用由 SET 赋值的局部变量。*/

/*程序清单如下：*/

USE adventureworks

GO

DECLARE @phone char(12)

SET @phone = '398-555-0132'

SELECT RTRIM(firstname) + ' ' + RTRIM(lastname) AS Name, phone

FROM person.contact

WHERE phone = @phone

GO

/*显示到当前日期和时间为止试图登录SQL Server的次数。*/

/*程序清单如下：*/

SELECT GETDATE()  AS  '当前的时期和时间', 

   @@CONNECTIONS AS  '试图登录的次数'

/*在 FirstColumnHeading 列标题中为所有的行都显示字符串 xyz，然后，在 SecondColumnHeading 列标题中列出来自 Products 表的每个产品 ID。*/

/*程序清单如下：*/

USE adventureworks

GO

DECLARE @phone char(12)

SET @phone = '398-555-0132'

SELECT RTRIM(firstname) + ' ' + RTRIM(lastname) AS Name, phone

FROM person.contact

WHERE phone = @phone

GO

/*创建一个具有 int 数据类型的表以显示值，并将该表放入一行中。*/

/*程序清单如下：*/

CREATE TABLE t1

( a int NOT NULL, b int NOT NULL ) 

 INSERT t1 VALUES (170, 75)

  

/*下面的查询在 a列和 b列上执行按位 AND 运算。*/

SELECT a& b FROM t1 

/*在SET语句使用的表达式中，括号使其首先执行加法，表达式的结果为18。*/

/*程序清单如下：*/

DECLARE @MyNumber int

SET @MyNumber = 2 * (4 + 5)

SELECT @MyNumber

/*通过行集函数OPENQUERY()执行一个分布式查询，以便从服务器local中提取表department中的记录。*/

/*程序清单如下：*/

select * from  openquery( SERVER , 'select * from Northwind.customers')

/*计算 Adventure Works Cycles 的副总所用的平均休假小时数以及总的病假小时数。对检索到的所有行，每个聚合函数都生成一个单独的汇总值。*/

/*程序清单如下：*/

USE AdventureWorks;

GO

SELECT AVG(VacationHours)as 'Average vacation hours', 

    SUM  (SickLeaveHours) as 'Total sick leave hours'

FROM HumanResources.Employee

WHERE Title LIKE 'Vice President%'

/*下列语句返回产品的平均标价。如果不使用DISTINCT，AVG函数将计算出Production.Product表中所有产品的平均价格*/

/*程序清单如下：*/

USE AdventureWorks;

GO

SELECT AVG(DISTINCT ListPrice)

FROM Production.Product

/*下面的示例查找居住在不同城市的人员数量*/

/*程序清单如下：*/

USE adventureworks

GO

SELECT COUNT(DISTINCT city)

FROM person.address

GO

/*返回production.product表中成本最高的产品以及价格的标准偏差。*/

/*程序清单如下：*/

USE adventureworks

GO

SELECT MAX(standardcost),STDEV(listprice)

FROM production.product

/*使用 LTRIM 函数删除字符变量中的起始空格。*/

/*程序清单如下：*/

DECLARE @string_to_trim varchar(60)

SET @string_to_trim = 'Five spaces are at the beginning of this

   string.'

SELECT 'Here is the string without the leading spaces: ' + 

   LTRIM(@string_to_trim)

/*使用可选的start_location参数从addressline1列的第2个字符开始查找“shoe”*/

/*程序清单如下：*/

USE adventureworks

SELECT CHARINDEX('shoe', addressline1,2) FROM person.address

WHERE addressid = '5'

/*显示如何只返回字符串的一部分。该查询在一列中返回person.contact表中的姓氏，在另一列中返回person.contact表中的名字首字母。*/

/*程序清单如下：*/

USE adventureworks

SELECT lastname, SUBSTRING(firstname, 1, 1)

FROM person.contact

ORDER BY lastname

/*在第一个字符串（abcdef）中删除从第二个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，创建并返回一个字符串。*/

/*程序清单如下：*/

SELECT STUFF('abcdef', 2, 3, 'ijklmn')

/*显示在humanresources.employee 表中雇用日期到当前日期间的天数。*/

/*程序清单如下：*/

USE adventureworks

SELECT DATEDIFF(day, hiredate, getdate())  AS diffdays

FROM humanresources.employee

/*从GETDATE函数返回的日期中提取月份名。*/

/*程序清单如下：*/

SELECT DATENAME(month, getdate())  AS  'Month Name'

/*此示例输出 AdventureWorks 数据库中订单的时间范围的列表。此时间范围为当前订单日期加上 21 天。*/

/*程序清单如下：*/

USE AdventureWorks;

GO

SELECT DATEADD(day, 21, OrderDate)AS TimeFrame 

FROM Sales.SalesOrderHeader

GO

/*在同一表达式中使用CEILING()，FLOOR()，ROUND()函数。*/

/*程序清单如下：*/

select  ceiling(13.4),  floor(13.4),  round(13.4567,3)

/*示例检索列表价格的第一位是 3 的产品的名称，并将ListPrice转换为 int。*/

/*程序清单如下：*/

USE AdventureWorks

GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice

FROM Production.Product

WHERE CAST(ListPrice AS int) LIKE '3%'

GO

/*用style 参数将当前日期转换为不同格式的字符串。*/

/*程序清单如下：*/

SELECT '101'=CONVERT(char, GETDATE(), 101),

'1'=CONVERT(char, GETDATE(), 1),

'112'=CONVERT(char, GETDATE(), 112)

/*从adventureworks数据库中返回person.contact表的首列名称*/

/*程序清单如下：*/

USE adventureworks

SELECT COL_NAME(OBJECT_ID('person.contact'), 1)

/*检查 sysdatabases 中的每一个数据库，使用数据库标识号来确定数据库名称。*/

/*程序清单如下：*/

USE master

SELECT dbid, DB_NAME(dbid)  AS  DB_NAME

FROM sysdatabases

ORDER BY dbid

/*查找所有产品的平均价格，用值$10.00替换production.product表listprice列中所有NULL条目*/

/*程序清单如下：*/

USE adventureworks

SELECT AVG(ISNULL(listprice, $10.00))

FROM production.product

/*例 显示带有语句块的IF条件。如果产品的平均价格不低于$15，那么就显示文本：Average price is more than $15*/

/*程序清单如下：*/

USE adventureworks

IF (SELECT AVG(listprice) FROM production.product WHERE color = 'blue') < $15

BEGIN

   PRINT 'The following product are excellent:'

   PRINT ' '

   SELECT SUBSTRING(name, 1, 15) AS name

   FROM production.product

   WHERE color = 'blue' 

END

ELSE

   PRINT 'Average price is more than $15.'

/*例利用 BEGIN 和 END 语句使得 IF 语句在取值为 FALSE 时跳过语句块。*/

/*程序清单如下：*/

DECLARE  @ErrorSaveVariable char

IF (@@ERROR <> 0)

BEGIN

   SET @ErrorSaveVariable = @@ERROR

   PRINT 'Error encountered, ' + 

         CAST(@ErrorSaveVariable AS VARCHAR(10))

END

/*例 用Go 语句作为批的结束语句。*/

/*程序清单如下：*/

USE adventureworks

GO

DECLARE @MyMsg VARCHAR(50)

SELECT @MyMsg = 'Hello, World.'

GO 

/*-- @MyMsg 在Go语句后失效。*/

/*例使用CASE函数去更改产品的分类显示，以使其更易于理解*/

/*程序清单如下：*/

USE adventureworks

SELECT   Category = 

      CASE color

         WHEN 'red' THEN 'red color'

         WHEN 'black' THEN 'black color'

         WHEN 'silver' THEN 'silver color'

         WHEN 'yellow' THEN 'yellow color'

         WHEN 'blue' THEN 'blue color'

      END,

   CAST(name AS varchar(25)) AS 'Shortened name',

   listprice AS Price

FROM production.product

WHERE listprice IS NOT NULL

ORDER BY color, price

COMPUTE AVG(listprice) BY color

/*例使用搜索CASE函数，根据产品的价格范围将价格显示为文本注释*/

/*程序清单如下：*/

USE adventureworks

SELECT    'Price Category'= 

     CASE 

        WHEN listprice IS NULL THEN 'Not yet priced'

         WHEN listprice < 10 THEN 'Very Reasonable Title'

         WHEN listprice >= 10 and listprice < 20 THEN 'Coffee Table Title'

         ELSE 'Expensive!'

      END,

   CAST(name AS varchar(20)) AS 'Shortened name'

FROM production.product

ORDER BY listprice

GO

/*在嵌套的 IF...ELSE 和 WHILE 中使用 BREAK 和 CONTINUE。如果平均价格少于 $30，WHILE 循环就将价格加倍，然后选择最高价。如果最高价少于或等于 $50，WHILE 循环重新启动并再次将价格加倍。该循环不断地将价格加倍直到最高价格超过 $50，然后退出 WHILE 循环并打印一条消息。*/

/*程序清单如下：*/

USE adventureworks

WHILE (SELECT AVG(listprice)  FROM production.product) < $30

BEGIN

   UPDATE production.product

      SET listprice = listprice * 2

   SELECT MAX(listprice) FROM production.product

   IF (SELECT MAX(listprice) FROM production.product) > $50

      BREAK

   ELSE

      CONTINUE

END

PRINT 'Too much for the market to bear'

/*利用GOTO语句求出从1加到5的总和。*/

/*程序清单如下：*/

declare    @sum   int,   @count   int

select   @sum=0,   @count=1

label_1:

select   @sum=@sum+@count

select   @count=@count+1

if   @count<=5

goto   label_1

select   @count,   @sum

/*使用WAITFOR TIME语句，以便在晚上10:20执行存储过程 update_all_stats。*/

/*程序清单如下：*/

BEGIN

   WAITFOR TIME '22:20'

   EXECUTE update_all_stats

END

/*显示如果在执行 findjobs 时没有给出用户名作为参数，RETURN 则将一条消息发送到用户的屏幕上然后从过程中退出。如果给出用户名，将从适当的系统表中检索由该用户在当前数据库内创建的所有对象名。*/

/*程序清单如下：*/

CREATE PROCEDURE findjobs @nm sysname = NULL

AS 

IF @nm IS NULL

   BEGIN

     PRINT 'You must give a username'

      RETURN

   END

ELSE

   BEGIN

      SELECT o.name, o.id, o.uid

      FROM sysobjects o INNER JOIN master..syslogins l

         ON o.uid = l.sid

      WHERE l.name = @nm

   END