创建字符数据数据表

1、USE SAMPLEGO- 创建“字符数据”数据表CREATE TABLE 字符数据(char_string char(5),varchar_string varchar(5),text_string text)GO- 向“字符数据”数据表中添加两行数据INSERT INTO 字符数据VALUES (“test”, “test”, “this is a test for text data type”)INSERT INTO 字符数据VALUES (“this is a test for char data type”, “this is a test for varchar data typ

2、e”, “this is a test for text data type”)GO- 从“字符数据”数据表中检索数据SELECT *FROM 字符数据GOUSE sampleGO- 创建“整型数据”数据表CREATE TABLE 整型数据(bigint_number bigint,int_number int,smallint_number smallint,tinyint_number tinyint)GO- 向“整型数据”数据表中添加两行数据INSERT INTO 整型数据VALUES (9223372036854775807, 2147483647, 32767, 255)INSERT

3、 INTO 整型数据VALUES (-9223372036854775808, -2147483648, -32768, 0)GO- 从“整型数据”数据表中检索数据SELECT *FROM 整型数据GOUSE sampleGO- 创建“数字数据”数据表CREATE TABLE 数字数据(decimal_number decimal (8, 6),numeric_number numeric (5, 3)GO- 向“数字数据”数据表中添加两行数据INSERT INTO 数字数据VALUES (99.99999788，50)INSERT INTO 数字数据VALUES (99.999999，300

4、)GO- 从“数字数据”数据表中检索数据SELECT *FROM 数字数据GOUSE sampleGO-创建“货币数据”数据表CREATE TABLE 货币数据(money_number moneysmallmoney_number smallmoney)GO- 向数据表中添加两条合理记录INSERT INTO 货币数据VALUES ($123.45, $1)INSERT INTO 货币数据VALUES ($123.45, CAST('$111,111.45' AS MONEY) )GO- 向数据表中添加带有逗号分隔符的货币数据INSERT INTO 货币数据VALUES ($

5、123.45, $111,111.45)GO- 从数据表中检索数据SELECT * FROM 货币数据GO 5.6USE sampleGO-创建“二进制数据”数据表CREATE TABLE 二进制数据(binary _number binary(6)vbinary _number vbinary(6)GO- 向数据表中添加三条记录INSERT INTO 二进制数据VALUES (0xABCDE, 0xABCDE)INSERT INTO 二进制数据VALUES (0xABCDEA, 0x46F)INSERT INTO 二进制数据VALUES (0xABCDEABCDEABCDE, 0x46F)G

6、O- 从数据表中检索数据SELECT * FROM 二进制数据GO USE sampleGO-创建“特殊数据”数据表CREATE TABLE 特殊数据(sql_variant_data sql_variant)GO- 向数据表中添加几条带有不同数据类型的记录INSERT INTO 特殊数据VALUES (This is a sql_variant data type test)INSERT INTO 特殊数据VALUES (123456)INSERT INTO 特殊数据VALUES (April 15, 1998)GO- 从数据表中检索数据SELECT * FROM 特殊数据GO 5.9- 定

7、义变量DECLARE Table_Example TABLE (Col_num int PRIMARY KEY, Col_text char (50)- 向该变量中添加两行内容INSERT INTO Table_Example VALUES (1, this is a)INSERT INTO Table_Example VALUES (2, table data type example)- 从临时表中检索SELECT * FROM Table_ExampleGO5.10- 创建birthday 用户定义数据类型，它使用了 datetime 基本数据类型，允许 NULLUSE sampleEX

8、EC sp_addtype birthday, datetime, NULLGO- 在数据表定义时使用birthday 用户定义数据类型CREATE TABLE 特殊数据(char_data char(20),birthday_data birthday)GO- 向数据表中添加几条记录INSERT INTO 特殊数据VALUES (Sarah, 02/22/1976)INSERT INTO 特殊数据VALUES (Tina, 04/15/1998)GO- 从数据表中检索数据SELECT * FROM 特殊数据GO - 删除birthday 用户定义数据类型EXEC sp_droptype bi

9、rthdayCREATE DATABASE database_nameON PRIMARY<filespec> 1, . . . n , <filegroup> 1, . . . nLOG ON <filespec> 1, . . .n FOR LOAD|FOR ATTACH<filespec> : : =( NAME = logical_file_name,FILENAME = os_file_name, SIZE = size, MAXSIZE = max_size|UNLIMITED, FILEGROWTH = growth_increme

10、nt ) , . . .n<filegroup> : : = FILEGROUP filegroup_name <filespec> , . . . n例2：CREATE DATABASE salesON(NAME = sales_dat,FILENAME = c:program filesmicrosoft sql servermssqldatasales_dat.mdf,SIZE = 5MAXSIZE = 30FILEGROWTH = 2)LOG ON (NAME = sales_log,FILENAME = c:program filesmicrosoft sql

11、 servermssqldatasales_log.ldf,SIZE = 2MBMAXSIZE = 20MBFILEGROWTH = 2MB)GOCREATE DATABASE ReportONPRIMARY(NAME = Rep1,FILENAME = c:program filesmicrosoft sql servermssqldatarep1_dat.mdfSIZE = 5MB,MAXSIZE = 10MB,FILEGROWTH = 1MB),(NAME = Rep2,FILENAME = c:program filesmicrosoft sql servermssqldatarep2

12、_dat.ndfSIZE = 5MB,MAXSIZE = 10MB,FILEGROWTH = 1MB), (NAME = Rep3,FILENAME = c:program filesmicrosoft sql servermssqldatarep3_dat.ndfSIZE = 5MB,MAXSIZE = 10MB,FILEGROWTH = 1MB)LOG ON(NAME = Rep1_log,FILENAME = c:program filesmicrosoft sql servermssqldatarep1_log.ldfSIZE = 5MB,MAXSIZE = 10MB,FILEGROW

13、TH = 1MB),(NAME = Rep2_log,FILENAME = c:program filesmicrosoft sql servermssqldatarep2_log.ldfSIZE = 5MB,MAXSIZE = 10MB,FILEGROWTH = 1MB)GOEXEC sp_dboption 产品数据库, SINGLE USER, TrueEXEC sp_renamedb 产品数据库, productEXEC sp_dboption product, SINGLE USER, FalseALTER DATABASE 员工数据库MODIFY FILE (NAME = 员工数据库

14、_dat，SIZE = 15)ALTER DATABASE 产品数据库MODIFY FILE (name = 产品数据库_log, SIZE = 3)DBCC SHRINKFILE ( file_name , target_size | , EMPTYFILE | NOTRUNCATE | TRUNCATEONLY )USE 产品数据库DBCC DHRINKFILE ( 产品数据库_dat, 6)ALTER DATABASE databaseADD FILE <filespec> , . . . n TO FILEGROUP filegroup_name|ADD LOG FILE

15、<filespec> , . . . n|REMOVE FILE logical_file_name|ADD FILEGROUP filegroup_name|REMOVE FILEGROUP filegroup_name|MODIFY FILE <filespec>|MODIFY FILEGROUP file_group_name filegroup_property<filespec> : : = (NAME = logical_file_name, FILENAME = os_file_name, SIZE = size, MAXSIZE = max_

16、size|UNLIMITED, FILEGROWTH = growth_increment)例1:DROP DATABASE TempCREATE DATABASE TempON(NAME = 'Temp1_dat',FILENAME = 'c:program filesmicrosoft sql servermssqldatatemp1_dat.mdf',SIZE = 5MB,MAXSIZE = 15MB,FILEGROWTH = 1)LOG ON(NAME = 'Temp1_log',FILENAME = 'c:program fil

17、esmicrosoft sql servermssqldatatemp1_log.ldf',SIZE = 5MB,MAXSIZE = 15MB,FILEGROWTH = 1)GOALTER DATABASE TempADD FILE(NAME = 'Temp2_dat',FILENAME = 'c:program filesmicrosoft sql servermssqldatatemp2_dat.ndf',SIZE = 5MB,MAXSIZE = 15MB,FILEGROWTH = 1)GOALTER DATABASE TempADD LOG FIL

18、E(NAME = 'Temp2_log',FILENAME = 'c:program filesmicrosoft sql servermssqldatatemp2_log.ldf',SIZE = 5MB,MAXSIZE = 15MB,FILEGROWTH = 1)GO例2：ALTER DATABASE TempMODIFY FILE(NAME = Temp1_dat,SIZE = 10,MAXSIZE = 20,FILEGROWTH = 2)例3： ALTER DATABASE TempADD FILEGROUP TempFileGroupGOALTER DA

19、TABASE TempADD FILE(NAME = 'Temp3_dat',FILENAME = 'c:program filesmicrosoft sql servermssqldatatemp3_dat.ndf',SIZE = 3,MAXSIZE = 10,FILEGROWTH = 1),(NAME = 'Temp4_dat',FILENAME = 'c:program filesmicrosoft sql servermssqldatatemp4_dat.ndf',SIZE = 3,MAXSIZE = 10,FILEGRO

20、WTH = 1)TO FILEGROUP TempFileGroupGOALTER DATABASE TempREMOVE FILE Temp2_logGO例1：DROP DATABASE 产品数据库,员工数据库CREATE TABLE语句创表的语法如下：CREATE TABLE database_name. owner .| owner. table_name( < column_definition > | column_name AS computed_column_expression | < table_constraint > ,.n ) ON filegr

21、oup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT < column_definition > := column_name data_type DEFAULT constant_expression | IDENTITY ( seed , increment ) < column_constraint > .n CREATE TABLE student(学号 int NOT NULL，姓名 char(8)CONSTRAINT constraint_namePRIMARY KEY CLUSTERED|NONCLUSTERED(字

22、段名, . . .n)CREATE TABLE project(项目编号 int CONSTRAINT PK_Pno PRIMARY KEY,项目名称 varchar(40)CREATE TABLE project(项目编号 int PRIMARY KEY,项目名称 varchar(40)CREATE TABLE project(项目编号 int,项目名称 varchar(40),CONSTRAINT PK_Pno_Pname PRIMARY KEY(项目编号, 项目名称)CONSTRAINT constraint_nameUNIQUE CLUSTERED|NONCLUSTERED(字段名,

23、. . .n)CREATE TABLE project(项目编号 int CONSTRAINT PK_Pno PRIMARY KEY,项目名称 char(20) CONSTRAINT UN_Pname UNIQUE)CREATE TABLE project(项目编号 int PRIMARY KEY,项目名称 char(20) UNIQUECREATE TABLE project(项目编号 int CONSTRAINT PK_Pno PRIMARY KEY,项目名称 char(20),项目负责人 char(20), CONSTRAINT UN_Pname_Pm UNIQUE(项目名称,项目负责人

24、)CONSTRAINT constraint_name FOREIGN KEY REFERENCES ref_table ( ref_column ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION - 创建数据表employeesCREATE TABLE employees(员工编号 int PRIMARY KEY,员工姓名 char(20) UNIQUE )GO- 创建数据表projectCREATE TABLE project(项目编号 int PRIMARY KEY,项目名称

25、 char(20),项目负责人 char(20) FOREIGN KEY REFERENCES temp9(员工姓名)ON DELETE CASCADE)GOCONSTRAINT constraint_nameCHECK (logical_expression)CREATE TABLE employees(员工编号 int NOT NULL PRIMARY KEY,员工姓名 char(20) UNIQUE,工资 int NOT NULLCONSTRAINT CK_EsCHECK (工资 >= 1000 AND 工资 <= 20000)CREATE RULE rule_name AS

26、 condition_expressionEXEC sp_bindrule rulename = rule_name , objname = object_nameCREATE TABLE employees(员工编号 int NOT NULL PRIMARY KEY,员工姓名 char(20) ,工资 int NOT NULL）CREATE RULE RU_salaryAS salary>=1000 AND salary <=20000GOEXEC sp_bindrule RU_salary, employee.工资GOCREATE DEFAULT default AS cons

27、tant_expressionsp_bindefault defname = default , objname = object_nameCREATE DEFAULT DE_salary as 1000GOEXEC sp_bindefault DE_salary, employee.工资GOALTER TABLE table ALTER COLUMN column_name new_data_type ( precision , scale ) NULL | NOT NULL - 创建数据表projectCREATE TABLE project(项目编号 int PRIMARY KEY,项目

28、名称 char(20) NOT NULL)- 使用sp_help察看数据表的定义EXEC sp_help projectGO- 修改相应的字段定义ALTER TABLE projectALTER COLUMN 项目名称 varchar(25) NULL- 再次察看数据表的定义EXEC sp_help projectGOALTER TABLE tableADD column_definition|column_name AS computed_column_expression- 向数据表中添加字段ALTER TABLE projectADD项目负责人 varchar(15),项目难度级别 in

29、t DEFAULT 1,预计工期 AS 项目难度级别*20- 察看数据表定义EXEC sp_help projectALTER TABLE tableDROP COLUMN column_name, . . .nALTER TABLE projectDROP COLUMN 项目负责人,预计工期ALTER TABLE tableWITH CHECK|WITH NOCHECKADD CONSTRAINT constraint_name constraint_definition , . . .nUSE company- 创建数据表employeeCREATE TABLE employee(员工编号

30、 int PRIMARY KEY,员工姓名 char(20) UNIQUE)GO- 创建数据表projectCREATE TABLE project(项目编号 int NOT NULL,项目负责人 char(20),项目难度级别 int)GO- 向project数据表中添加约束ALTER TABLE projectADD CONSTRAINT PK_PnoPRIMARY KEY(项目编号),CONSTRAINT FK_PmFOREIGN KEY(项目负责人)REFERENCES employee(员工姓名),CONSTRAINT CK_PlCHECK (项目难度级别>=1 AND 项目难

31、度级别<=5)GOALTER TABLE tableDROP CONSTRAINT constraint_name , . . .nALTER TABLE projectDROP CONSTRAINT FK_Pm, CK_PlDROP TABLE table, . . . nDROP TABLE employee, projectSELECT 语句的基本结构如下：SELECT select_listINTO new_table_nameFROM table_listWHERE search_conditionsGROUP BY group_by_listHAVING search_con

32、ditionsORDER BY order_list ASC | DESC SELECT *FROM ChenHong.TableXSELECT *FROM Sample.员工数据表SELECT DISTINCT 员工数据表.员工编号, 项目数据表.项目名称FROM 员工数据表 JOIN 项目数据表 ON (员工数据表.员工编号 = 项目数据表.负责人)USE SampleSELECT 所属部门FROM 员工数据表USE SampleSELECT DISTINCT 所属部门FROM 员工数据表8.3SELECT *FROM SAMPLE.员工数据表SELECT 员工数据表.员工编号, 项目数据

33、表.项目名称FROM 员工数据表 JOIN 项目数据表 ON (员工数据表.员工编号 = 项目数据表.负责人)SELECT EMP.员工编号, EMP.员工姓名, SP.部门名称FROM 员工数据表AS EMP, (SELECT 部门数据表.部门编号,部门数据表.部门名称 FROM 部门数据表 WHERE 部门数据表.部门编号 > 2 ) AS SPWHERE EMP.部门编号 = SP.部门编号SELECT 员工姓名FROM Sample.员工数据表WHERE 员工工资 > 2000SELECT 员工姓名FROM SAMPLE.员工数据表WHERE 工资BETWEEN 2000

34、AND 3000ORDER BY 工资SELECT 员工姓名FROM SAMPLE.员工数据表WHERE 工资 > 2000 AND 工资 < 3000ORDER BY 工资SELECT 员工姓名FROM SAMPLE.员工数据表WHERE 工资NOT BETWEEN 2000 AND 3000ORDER BY 工资SELECT 员工姓名FROM SAMPLE.员工数据表WHERE 工资 > 3000 OR工资 < 2000ORDER BY 工资SELECT 员工姓名，部门FROM SAMPLE.员工数据表WHERE 部门 IN(1, 3)GROUP BY 部门SELE

35、CT 员工姓名，部门FROM SAMPLE.员工数据表WHERE 部门 = 1 OR 部门 = 3GROUP BY 部门USE SAMPLESELECT 员工姓名，部门FROM 员工数据表WHERE 员工编号 IN (SELECT 负责人 FROM 项目数据表 WHERE 结束日期 <= January 1, 2002)USE SAMPLESELECT 员工姓名，部门FROM 员工数据表WHERE 员工编号 NOT IN (SELECT 负责人 FROM 项目数据表 WHERE 结束日期 <= January 1, 2002)SELECT 项目名称FROM SAMPLE.项目数据表

36、WHERE 项目名称LIKE SiS%SELECT 项目名称FROM SAMPLE.项目数据表WHERE 项目名称NOT LIKE SiS%WHERE Column1 LIKE %54/% ESCAPE /WHERE Column1 LIKE %54%USE SAMPLESELECT 项目名称FROM 项目数据库WHERE 项目名称 LIKE SiS% and 项目名称IS NOT NULLUSE SAMPLESELECT 项目名称FROM 项目数据库WHERE 项目名称 IS（NOT）NULLSELECT 项目名称FROM SAMPLE.项目数据表WHERE 项目名称LIKE SiS%AND

37、结束日期 <= January 1, 2002SELECT 项目名称FROM SAMPLE.项目数据表WHERE 项目名称LIKE SiS%OR项目名称LIKE Pet%SELECT 项目名称FROM SAMPLE.项目数据表WHERE 项目名称NOT LIKE SiS%SELECT 所属部门，AVG(工资) AS 平均工资FROM 员工数据库GROUP BY 所属部门SELECT 所属部门，性别，AVG(工资) AS 平均工资FROM 员工数据库GROUP BY 所属部门，性别SELECT 所属部门，AVG(工资) AS 平均工资FROM 员工数据库GROUP BY 所属部门HAVIN

38、G 平均工资 >= 2000SELECT 所属部门，AVG(工资) AS 平均工资FROM 员工数据库WHERE 所属部门 NOT LIKE 办公室GROUP BY 所属部门SELECT 所属部门，AVG(工资) AS 平均工资FROM 员工数据库WHERE 所属部门 NOT LIKE 办公室GROUP BY ALL所属部门SELECT 所属部门，性别，AVG(工资)FROM 员工数据表GROUP BY 所属部门，性别 WITH CUBESELECT 所属部门，性别，AVG(工资)FROM 员工数据表GROUP BY 所属部门，性别 WITH ROLLUPSELECT 所属部门，性别，A

39、VG(工资)FROM 员工数据表GROUP BY 性别，所属部门 WITH ROLLUP8.5USE SAMPLESELECT *FROM 员工数据库ORDER BY 工资USE SAMPLESELECT *FROM 员工数据库ORDER BY 所属部门，工资 DESCUSE SAMPLESELECT 所属部门，AVG(工资) AS 平均工资FROM 员工数据库GROUP BY 所属部门ORDER BY 工资平均值USE SAMPLESELECT 员工姓名，所属部门，工资FROM 员工数据表COMPUTE SUM(工资)USE SAMPLESELECT 员工姓名，所属部门，工资FROM 员工数

40、据表ORDER BY 所属部门COMPUTE SUM(工资) BY 所属部门8.7SELECT *FROM SAMPLE.员工数据表UNIONSELECT *FROM SAMPLEBEIJING.员工数据表ORDER BY 工资8.8USE SAMPLESELECT e.员工编号，e.员工姓名FROM 员工数据表 AS eUSE SAMPLESELECT 员工数据表.员工编号，e.员工姓名，e.工资FROM 员工数据表 AS eUSE SAMPLESELECT 所属部门，SUM(工资)FROM 员工数据表GROUP BY 所属部门USE SAMPLESELECT 所属部门，AVG(工资)FRO

41、M 员工数据表GROUP BY 所属部门USE SAMPLESELECT 所属部门，MAX(工资)FROM 员工数据表GROUP BY 所属部门USE SAMPLESELECT 所属部门，MAX(员工姓名)FROM 员工数据表GROUP BY 所属部门USE SAMPLESELECT 所属部门，MIN(工资)FROM 员工数据表GROUP BY 所属部门USE SAMPLESELECT 所属部门，MIN(员工姓名)FROM 员工数据表GROUP BY 所属部门USE SAMPLESELECT COUNT(*)FROM 项目数据库WHERE 结束日期 <= January 1, 2002U

42、SE SAMPLESELECT COUNT(DISTINCT 负责人)FROM 项目数据库WHERE 结束日期 <= January 1, 2002SELECT *FROM 员工数据表 INNER JOIN 项目数据表ON 员工数据表.员工编号 = 项目数据表.负责人SELECT *FROM 员工数据表 LEFT JOIN 项目数据表ON 员工数据表.员工编号 = 项目数据表.负责人SELECT *FROM 员工数据表 RIGHT JOIN 项目数据表ON 员工数据表.员工编号 = 项目数据表.负责人USE SAMPLESELECT *FROM 员工数据表 CROSS JOIN 项目数据

43、表- 使用WHERE分句进行内联接SELECT *FROM 员工数据表，项目数据表WHERE 员工数据表.员工编号 = 项目数据表.负责人- 相当于：SELECT *FROM 员工数据表 INNER JOIN 项目数据表ON 员工数据表.员工编号 = 项目数据表.负责人- 使用WHERE分句进行左向外联接SELECT *FROM 员工数据表，项目数据表WHERE 员工数据表.员工编号 *= 项目数据表.负责人- 相当于：SELECT *FROM 员工数据表 LEFT JOIN 项目数据表ON 员工数据表.员工编号 = 项目数据表.负责人- 使用WHERE分句进行右向外联接SELECT *FRO

44、M 员工数据表，项目数据表WHERE 员工数据表.员工编号 =* 项目数据表.负责人- 相当于：SELECT *FROM 员工数据表 RIGHT JOIN 项目数据表ON 员工数据表.员工编号 = 项目数据表.负责人USE SAMPLESELECT 员工姓名FROM 员工数据表WHERE 员工编号 IN (SELECT DISTINCT 负责人 FROM 项目数据表 WHERE 结束日期 < January 1, 2002)USE SAMPLESELECT 员工姓名FROM 员工数据表WHERE 员工编号 NOT IN (SELECT DISTINCT 负责人 FROM 项目数据表)US

45、E SAMPLESELECT 员工姓名FROM 员工数据表WHERE 员工编号 = (SELECT DISTINCT负责人 FROM 项目数据表 WHERE 项目名称= SiS)USE SAMPLESELECT 项目名称FROM 项目数据表WHERE 项目收入 > (SELECT AVG(工资) FROM 员工数据表)- 所有正在负责项目的员工USE SAMPLESELECT 员工姓名FROM 员工数据表WHERE EXISTS (SELECT * FROM 项目数据表 WHERE 员工数据表.员工编号 = 项目数据表.负责人)- 所有没有负责项目的员工USE SAMPLESELECT

46、员工姓名FROM 员工数据表WHERE NOT EXISTS (SELECT * FROM 项目数据表 WHERE 员工数据表.员工编号 = 项目数据表.负责人)INSERT INTO 员工数据表(员工姓名，性别，所属部门，工资) VALUES (王秋萍, 女, 项目部, 3000)INSERT INTO 员工数据表(员工姓名，性别，所属部门，工资) SELECT * FROM 临时员工数据表USE SAMPLEUPDATE 员工数据表SET 工资 = 3000WHERE 所属部门 = 项目部USE SAMPLEGO- 删除来自SiS公司的所有项目DELETE 项目数据表WHERE 客户 =

47、1GO- 删除SiS客户DELETE 客户数据表WHERE 客户编号 = 1GO- 删除用于存储SiS公司临时信息的数据表DELETE SiS_TEMPDROP TABLE SiS_TEMPUSE SAMPLEGO- 将项目部的工资更新为3000UPDATE 员工数据表SET 工资 = 3,000WHERE 所属部门 = 项目部- 检查是否出现限制检查冲突IF ERROR = 547 print “出现限制检查冲突，请检查需要更新的数据限制”USE SAMPLEGO- 将录入部员工的工资更新为3000UPDATE 员工数据表SET 工资 = 3000WHERE 所属部门 = 录入部- 如果没有

48、发生记录更新，则发出警告信息IF ROWCOUNT = 0 print 警告：没有发生记录更新SELECT VERSIONDECLARE Temp_Counter INTSET Temp_Count = 0USE SAMPLEGO- 声明局部变量DECLARE Max_Salary INT- 将其赋值为全体员工的工资最大值SELECT Max_Salary = MAX(工资)FROM 员工数据表GOUSE SAMPLEGOSELECT COL_LENGTH(员工数据表, 员工姓名) AS Name_Col_Length, DATALENGTH(员工姓名) AS Name_Data_Length

49、FROM 员工数据表DECLARE Today DATETIMESET Today = GETDATE()SELECT MONTH(Today), DAY(Today), YEAR(Today)USE SAMPLESELECT 员工姓名 + ， + 员工性别 + CHAR(13) + 所属部门FROM 员工数据表WHERE 员工编号 = 1USE SAMPLESELECT 所属部门，ROUND(AVG (工资)FROM 员工数据表GROUP BY 所属部门CREATE FUNCTION 函数名称 （参数名称 AS 数据类型） RETURNS 返回数据类型BEGIN函数内容RETURN 表达式E

50、ND- 创建奖金计算函数CREATE FUNCTION Bonus （Salary AS INT） RETURNS INTBEGIN- 奖金数量为工资数量的30%RETURN (Salary * 0.3)END- 输出员工姓名和奖金数目SELECT 员工姓名，Bonus(工资) AS 奖金FROM 员工数据表USE SAMPLEGO- 声明用于发布消息的变量DECLARE Message VARCHAR(200)- 判断是否有员工在办公室工作IF EXIST(SELECT *FROM 员工数据表WHERE 所属部门 = 办公室）- 如果有，则列出姓名BEGINSET Message = “下列人员在