SQLServer高级培训.ppt

1、SQL SERVER高级培训,主讲人:郑维勇,数据库的概述, 数据库对象,表、视图、存储过程、触发器、默认值、规则、 索引、用户。, SQL Server数据库的版本,个人版 标准版 开发版 企业版, Transact-SQL语言主要的4个组成部份,数据定义语言DDL 数据操作语言DML 数据控制语言DCL 共它语言元素,Transact-SQL, 数据定义语言DDL,用来创建数据库、数据库对象和定义列。包括： CREATE,ALTER,DROP。, 数据操纵语言DML,用来操纵数据库的命令。包括： SELECT,INSERT,UPDATE,DELET。, 数据控制语言DCL,用来控制数据库组

2、件的存取许可、存取权限等的命令 包括：GRANT,DENY,REVOKE。,管理SQL Server服务, 启动 SQL Server服务,Net start mssqlserver, 暂停 SQL Server服务,Net pause mssqlserver, 停止 SQL Server服务,Net stop mssqlserver,SQL Server服务,SQL Server 2000的各种服务和功能说明,SQL Server服务器的注册与删除,1、通过控制台目录 2、使用Transact-sql sp_addserver server= server ,local= local ,du

3、plicate_ok= duplicate_ok Eg sp_addserver local,配置链接服务器,在SQL Server 2000中,允许SQL Server用户访问基于SQL Server的服务器 以外的数据。通过配置链接服务器可以实现访问其他服务器上的OLE DB 数据源。链接服务器是对OLE DB数据源的抽象，是OLE DB提供程序和 连接属性的来源。,在链接服务器的边接过程中，为了使该连接有效，需要在链接服务器 之间创建登录映射。 链接服务器登录映射可以通过系统存储过程sp_addlinkedsrvlogin添加，相应地， 链接服务器登录映射可以通过系统存储过程sp_dro

4、plinkedsrvlogin删除。,配置链接服务器,添加键接服务器 Sp_addlinkedserver server= server ,srvproduct= product_name ,provider=provider_name ,datasrc=data_source ,location=location ,provstr=provider_string ,catalog=catalog,Eg: exec sp_addlinkedserver server=exam2, provider=Microsoft.Jet.OLEDB.4.0, srvproduct=,datasrc=d:d

5、b2.mdb,sp_addlinkedserver 链接服务器名, 产品名, Microsoft.Jet.OLEDB.4.0, Access数据库路径及文件名,sp_addlinkedserver Anna, Access 2000, Microsoft.Jet.OLEDB.4.0, D:db2.mdb,配置链接服务器,配置链接服务器,创建链接服务器登录,通过系统存储过程sp_addlinkedsrvlogin可以创建或更新本地SQL Server 实例上的登录与链接服务器上远程登录之间的映射。其语法为：,Sp_addlinkedsrvlogin rmtsrvname=rmtsrvname ,

6、useself= useself ,location=location ,rmtuser=rmtuser ,rmtpassword=rmtpassword,其中，参数: (1)rmtsrvname= rmtsrvname:是应用登录映射的链接服务器名称 (2)useself=useself:决定用于连接到远程服务器的登录名称，默认 设置为ture.true值指定SQL Server验证的登录使用其自己的凭据以连接 到rmtsrvname,而忽略rmtuser和rmtpassword参数 (3)locallogin=locallogin:是本地服务器上的登录,默认设置为Null.如 果为null

7、,则指定应用于所有连接到rmtsrvname的本地登录若不为null 则locallogin可以是SQL Server登录或windows NT用户 (4)rmtuser=rmtuser:当useself为false时,用来连接rmtsrvname的用户名, 默认设置为null.,配置链接服务器,创建一个映射，使得所有到本地服务器的登录都使用其各自的用户凭据 连接到链接服务器exam,Exec sp_addlinkedsrvlogin exam 或 Exec sp_addlinkedsrvlogin exam,true,使用指定的用户和密码将所有本地登录连接到链接服务器,Exec sp_add

8、linkedsrvlogin exam,false,null,user,password,创建一个映射，使得所有到本地服务器的登录都使用相同的登录user和 密码password连接到链接服务器exam,不使用登录和密码,Exec sp_addlinkedsrvlogin exam,false,配置链接服务器,数据访问 从SQL Server 2000中访问OLE DB数据源的方法有两种：通过特殊名称 以及通过链接服务器进行数据访问。在通过特殊名称访问OLE DB数据 源的方法中，可以通过函数openrowset和opendatasource来进行。其语法:,Openrowset (provi

9、der_name, datasource;user_id;password|provider_string ,catalog.schema.object |query ),Eg: SELECT * FROM OPENROWSET(Microsoft.Jet.OLEDB.4.0, d:db2.mdb; admin;, bbs),查询分析器,如何进入查询分析器？,SQL语言基础(变量与常量),定义和使用局部变量,定义局部变量 Declare variable_name datatype ,.n,Declare sex char(2),为局部变量赋值 为局部变量赋值一般有两种,一种是使用select

10、语句，另一种是使用 Set语句 Select variable_name=expression from table_name,n where clause,declare bookname varchar(30) select bookname = vc print bookname GO,SQL语言基础(变量与常量),定义和使用全局变量,全局变量是SQL SERVER系统内部事先定义好的变量，不 用用户参与定义，对用户而言是只读的。,identity:返回最近一次插入的identity列的数值,返回值是numeric procid:返回当前存储过程的ID标识 version:返回当前SQL

11、 Server服务器安装日期、版本以及处理器的类型 language:返回当前使用的语言名 servername:返回当前SQL SERVER服务器名称,SQL语言基础(变量与常量), 使用日期和时间常量,SQL 提供的日期/时间数据类型可以存储日期和时间的组给数据。 SQL 规定日期、时间和时间间隔的常量值被指定为字符中常量。,SQL语言基础(数据类型), 常见的数据类型,整型(int,smallint,tinyint,bigint) 浮点数据类型(real,float,decimal) 二进制数据类型(binary,varbinary) 逻辑数据类型 字符型数据类型(char(n),nch

12、ar(n),varchar(n),nvarchar(n),SQL语言基础(复合语句),定义复合语句(beginend),declare A int,B int,C int set A=20 set B=30 begin set C=A set A=B set B=C end print A print B,SQL语言基础(条件判断语句),使用if语句进行条件判断,If 命令行|程序块,declare x int set x=-100 if x0 print x是一个负数,新建数据库,Create database命令,Create database database_name on ,.n ,

13、.n log on,.n collate collation_name for load|for attach,新建数据库,create database student on ( name=学生管理数据库, filename=f:datastudent.mdf, size=1MB, MAXSIZE=50MB, FILEGROWTH=5MB ) log on ( name=学生管理日志, filename=f:datastudent.ldf, size=1MB, MAXSIZE=50MB, FILEGROWTH=10% ),修改数据库,ALTER DATABASE命令,alter databa

14、se student modify file( name=学生管理数据库, maxsize=unlimited ),删除数据库,Drop Database命令,新建表,Create table 命令,Create table table_name ( column_name data_type null|not null primary key|unique , ),新建表,use student go create table stu_info ( stu_id int not null, stu_name varchar(10), gender bit, major varchar(20)

15、, address varchar(100) ),修改表(增加字段),增加字段 Alter table table_name Add column_name data_type,Eg: alter table stu_info add memo text,修改表(增加主键),Alter table add primary key(column_name) Eg: alter table stu_info add primary key(stu_id),修改表(更改字段),更改字段属性 Alter table table_name Alter column column_name new_dat

16、a_type,Eg: alter table stu_info alter column stu_name varchar(12),修改表(删除字段),Alter table table_name Drop column column_name,Eg: alter table stu drop column memo,表的重命名,Exec sp_rename old_table,new_table,Eg: exec sp_rename stu_info,stu,表的删除,Drop table table_name,显示数据库信息, 显示SQL SERVER数据库中所有数据库详细信息,Selec

17、t * from sysdatabases,显示系统/用户表, 显示数据库中所有表的详细信息,use stu select * from sysobjects, 显示数据库中系统表的详细信息,use stu select * from sysobjects where xtype=s, 显示数据库中用户表的详细信息,use stu select * from sysobjects where xtype=u,显示表中的字段名称及属性, 显示表中的字段名称,Select col_name(object_id(tbl_stu,3), 显示表中的字段长度,Select col_length(tbl_

18、stu,stu_id), 显示表中结构,Sp_help tbl_stu,Select语句(列的查询), 单列查询,Select field_name from table_name, 多列查询,Select field_name1,field_name2,. from table_name, 查询所有列,Select * from table_name, 使用列名,Select field_name as new_name from table_name,Select语句(列的计算), 简单列计算, 复杂列计算, 执行算术组合运算, 连接列值,Select 图书名称,(单价 -进价)as 利润

19、 from tb_xsb02,Select sum(数量)as 总数量,sum(数量)%2 剩余 from tb_xsb02,Select name 姓名,(math+english+chinese)总分, (math+english+chinese)/3 平均分 from tb_score02,Select (名字+( )+姓氏) 姓名 from tb_abstu02,Select语句(Where条件), 检索单行值, 不匹配检索,Select * from tb_stu02 where 学号=ID001,Select * from tb_stu02 where 学号ID001,Select

20、语句(区间查询), 利用符号进行区间查询, 利用Bewteen进行区间查询,Select * from tb_abstu02 where 年龄 18 and 年龄 22,Select * from tb_abstu02 where 年龄 between 18 and 22,Select语句(组合Where语句), 利用AND运算符连接where子句,Select 学生姓名,数学成绩,音乐成绩,英语成绩 from tb_StuScore03 where 数学成绩 90 and 英语成绩 90, 利用OR运算符连接where子句,Select 学生姓名,数学成绩,音乐成绩,英语成绩 from tb

21、_StuScore03 where 数学成绩 90 or 英语成绩 90,Select语句(IN运算符),Select 教师姓名,系别,年龄,性别 from tb_TeacherInfo03 where 系别 in(计算机系,英语系,生物系),Select语句(NOT运算符),Select 房间号,房间类型,价格,房态,配置 from tb_kf03 where (房态 NOT IN (入住) and (not(价格 between 80 and 150),Select语句(like运算符), 使用%匹配字符,Like %mr%-包含字符mr的任何文本,Like mr%-以字符mr开头的任何文

22、本,Like %mr-以字符mr结尾的任何文本,select 考生课程,考生人数,有效时间 from tb_StuExam where 考生课程 like 计算机%,Select语句(like运算符), 使用_匹配任意单个字符,Select 学生编号,学生姓名,年龄,所在学院 from tb_StuInfo03 where 学生编号 like 20070101_,Select语句(like运算符), 使用匹配某一范围的字符,a-m表示a到m的所有字符,0-9表示0到9的所有数字,Select 学生编号,学生姓名,性别,年龄,所学专业 from tb_Student05 where 年龄 lik

23、e 2-2_,Select语句(like运算符), 使用匹配非某一范围的字符,Select 学生编号,学生姓名,性别,年龄,所学专业 from tb_Student05 where 年龄 like 2-2_,语句Select * from stu where name like MC 功能？,Select语句(数据排序), 升序排序,Select * from tb_employee06 order by 奖金, 降序排序,Select * from tb_employee06 order by 奖金 desc, 列出前n条记录,Select top n * from tbl_stu, 列出前

24、n-m条记录(mn),Select top n+1 * from (select top m * from tbl_question) tbl_question order by id desc,Select语句(数据排序), 列出n%条记录,Select top 10 percent * from tbl_stu, 随机列出n条记录,Select top 5 * from tbl_question order by newid(), 不显示重复值,Select distinct stu_name from tbl_stu,Select语句(汉字排序), 按姓氏笔画排序,select * fr

25、om tb_stu06 order by 姓名 collate chinese_prc_stroke_cs_as_ks_ws, 按音序排序,select * from tb_stu06 order by 姓名 collate chinese_prc_cs_as,Select语句(动态排序),CASE条件语句的语法格式 简单CASE函数 Case input_expression When when_expression then result_expression .n Else else_result_expression END 2.CASE搜索函数 Case When boolean_e

26、xpression then result_expression . .n ELSE else_result_expression END,Select语句(动态排序),Select 学生编号,学生名称,所在班级,学生成绩= case when 学生成绩 is null then 暂无成绩！ when 学生成绩 is not null then 有成绩！ end from tb_StuXueSheng03,Select语句(动态排序),select rq, sum(case when shengfu=胜 then 1 else 0 end) 胜, sum(case when shengfu=负

27、then 1 else 0 end) 负 from match group by rq,Select语句(操作字符串), 截取字符串,Select stu_id,substring(stu_name,1,4),age from tbl_stu,Select stu_id,left(stu_name,1),age from tbl_stu,Select stu_id,Right(stu_name,1),age from tbl_stu, 去除尾部空格,Select 学号,rtrim (学号)as 去空格后, 姓名,rtrim(姓名)as 去空格后, 专业,rtrim(专业)as 去空格后 fro

28、m tb_stu04,Select语句(操作字符串), 使用”+”连接字符串,select 书名,类别, (书名+ 的类别为 + 类别) as 说明 from tb_book04,Select语句(操作字符串),使用stuff函数插入字符串 Stuff(character_expression,start,length,character_expression),Select 学号,姓名, stuff(学号,2,2,200800) as 新学号 from tb_stu04,获取字符在字符串中出现的次数,Select len(apples is in the apple tree)- len(r

29、eplace (apples is in the apple tree,p,)/len(p) as a from tb_name04,Select语句(操作字符串), 替换字符串,使用replace函数将字符串中某一字符替换成另一字符,Eg: select replace(dsfsdfsfdsf*fd,d,*)as 替换后, 字符串大小写转换,Uper(character_expression) Lower(character_expression),select upper(firstname)as up_firstname, lower(lastname )as low_lastname

30、from tb_name04,Select语句(格式化字符串),在字符串查询中,可以将字符串格式化为需要的格式,以 增加结果集的可读性。, charindex函数,Charindex函数用于返回字符串中指定表达式的起始位置,语法： Charindex(expression1,expression2,start_location),Eg: select 电话号码 as 提取前, left (电话号码,charindex(-,电话号码)-1)as 区号, substring(电话号码,charindex(-,电话号码)+1, len(电话号码)-charindex(-,电话号码)as 号码 fro

31、m tb_tel04,Select语句(格式化字符串), 用“”代替过长的字符串显示,select str1 , case when len(str1)10 then left(str1,10)+. else str1 end as news_str from tb_tb04, 求07/08级各有多少学生?,select sum(case when left(stu_id,2)=07 then 1 else 0 end) 07级人数, sum(case when left(stu_id,2)=08 then 1 else 0 end) 08级人数 from tbl_stu,Select语句(聚

32、合函数), SUM函数(汇总),SELECT SUM(数量) AS 总数量,SUM(单价) AS 总金额 FROM tb_xsb07, AVG函数(平均),SELECT AVG(语文) AS 语文 ,AVG(代数) AS 代数 ,AVG(几何) AS 几何 ,AVG(英语) AS 英语 FROM tb_stuAchievement07, MAX函数(最大值),SELECT MAX(销售额) AS 最高销售额 FROM tb_seller07, MIN函数(最小值),SELECT MIN(销售额) AS 最低销售额 FROM tb_seller07,Select语句(聚合函数), COUNT函数

33、(总计记录条数),SELECT COUNT(*) AS 日销售数量 from tb_bookSell07 where 日期=2005-10-6,Select语句(分组统计), GROUP BY子句创建简单分组,SELECT 商品类别 FROM tb_goods07 GROUP BY 商品类别, GROUP BY子句创建多列组合列,SELECT 书名,出版社 FROM tb_bookSell07 GROUP BY 书名,出版社, GROUP BY对统计结果进行排序,SELECT 学生编号,学生姓名, sum(高数+外语+计算机文化基础+马经+数据库管理+ 数据结构+软件工程) AS 总成绩 FR

34、OM tb_StuMark07 GROUP BY 学生编号 ,学生姓名 ORDER BY 总成绩 DESC,Select语句(分组统计), Having子句,SELECT 书名,出版社,SUM(金额) AS 总计金额 FROM tb_BookSell07 GROUP BY 书名,出版社 HAVING SUM(金额)500, Having子句与WHERE子句的联合使用,Select class_name,count(*) as 人数 from tbl_stu,tbl_class where tbl_stu.class_id=tbl_class.class_id group by class_na

35、me,Select语句(分组统计), 在分组查询中使用CUBE运算符,cube运算符的主要作用是自动对Group By子句中列出的字段 进行分组汇总运算。,Select 班级,性别,avg(语文) 语文平均成绩, avg(代数) 代数平均成绩,avg(几何) 几何平均成绩, avg(英语)英语平均成绩 from tb_stuAchievement07 group by 班级,性别 with cube,Select语句(分组统计), 在分组查询中使用ROLLUP,select 班级,性别,avg(语文) 语文平均成绩, avg(代数) 代数平均成绩,avg(几何) 几何平均成绩, avg(英语)

36、英语平均成绩 from tb_stuAchievement07 group by 班级,性别 with rollup,Select语句(分组统计), 使用COMPUTE子句在同一结果集中显示明细和汇总,compute AVG|COUNT|MAX|MIN|STDEV|STDEVP |VAR|VARP|SUM (expression),.n By expression,n,select * from tb_pay07 order by 所属部门 compute avg(工资),SELECT * FROM tb_pay07 ORDER BY 所属部门 COMPUTE SUM(工资) BY 所属部门,

37、Select语句(使用子查询), 简单子查询,SELECT 姓名,工资,所属部门, (SELECT AVG(工资) FROM tb_apay08) AS 平均工资, (工资-( SELECT AVG(工资) FROM tb_apay08) AS 与平均工资的差额 FROM tb_apay08, 为子查询命名,SELECT * FROM (SELECT 挂账单位,SUM(欠款金额) AS 金额 FROM tb_agzmx08 GROUP BY 挂账单位)tb1 ORDER BY 金额 ASC,Select语句(使用子查询), 在子查询中使用聚合函数,SELECT 姓名,工资,所属部门 FROM

38、tb_apay08 WHERE 工资 ( SELECT AVG(工资) FROM tb_apay08 ), 使用子查询更新数据,UPDATE tb_aMesell08 SET 药品名称=润洁 WHERE 药品名称 IN (SELECT 药品名称 FROM tb_aMesell08 WHERE 药品编号=YB004),Select语句(使用子查询), 使用子查询删除数据,DELETE tb_aMesell08 WHERE 药品名称 IN ( SELECT 药品名称 FROM tb_aMesell08 WHERE 药品编号=YB001),Select语句(使用子查询), 在嵌套中使用EXISTS谓

39、词,SELECT 学生姓名,所在学院,家庭住址 FROM tb_aStuInfo08 I WHERE EXISTS ( SELECT 学生姓名 FROM tb_aStuMark08 M WHERE M.学生姓名=I.学生姓名 AND 外语 80),select 学生姓名,所在学院,家庭住址 from tb_aStuInfo08 where 学生编号 in ( select 学生编号 from tb_aStuMark08 where 外语80 ),在一些情况下,只要子查询返回一个真值和假值.只考虑 是否满足谓词条件,数据内容本身并不重要.,Select语句(日期查询), 使用between an

40、d 查询某一时间段内的数据,select * from tb_student06 where 入校时间 between 01.09.01 and 01.09.05, 使用关系运算符查询某一时间段内的数据,select 姓名,入校时间 from tb_student06 where 入校时间 01.09.01 and 入校时间 01.09.05, 使用in运算符查询几个时间的数据,SELECT 书名,出版日期 FROM tb_booksell06 where 出版日期 in(2005-1-12,2005-1-25),Select语句(日期查询), 查询某一月份的数据 要实现按月查询数据可以使用日

41、期函数month(). 语法：month(date),select 书名,出版日期 from tb_bookSell06 where month(出版日期)=1 order by 出版日期, 查询今天是周几,select datename(dw,getdate() as 今天是周几,Datename(interval,date),Select语句(日期查询), datename()函数中的interval,yyyy:返回指定时间的年份 Q:返回指定时间的季度 M:返回指定时间的月份 D:返回指定时间是本月的第几天 DY:返回指定时间是本年的第几天 W:返回指定日期是本周的周几 Ww:返回指定日

42、期是一年中的第几周,select datename(yyyy,getdate() as 年份 select datename(m,getdate() as 月份 select datename(q,getdate()as 季度 select datename(w,getdate() as 今天是周几 select datename(ww,getdate() as 第几周 select datename(d,getdate() as 本月的第几天 select datename(dy,getdate() as 本年的第几天,Select语句(日期查询), 查询两个日期之间的天数,Datediff

43、函数用于返回跨两个指定日期的日期与时间边界数 其语法格式如下: Datediff(datepart,startdate,enddate),yyyy:返回年份差 mm:返回月份差 D:返回日差,select datediff(yyyy,2007-10-1,2008-10-1) as 年份差 select datediff(mm,2007-10-1,2008-10-1) as 月份差 select datediff(d,2007-10-1,2007-12-1) as 日差,Select语句(日期查询), 查询3个月后的日期,Dateadd函数用于在向指定日期加上一段时间的基础上,返回 新的date

44、time值.语法格式如下: Dateadd(datepart,number,date),select dateadd(mm,3,getdate() as DAY NUMBER, 获取某月的第一天,select dateADD(mm,datediff(mm,0,2007-12-20),0) as DAY_NUMBER,Select语句, 复制表结构,Select * into destion_table from source_table where 11, 批量插入数据,Insert into table1 (field1,field2,field3 ) select field1,field

45、2,field3 from table2,多表查询, 笛卡尔乘积查询,笛卡尔乘积查询实际上就是两表之间的交叉联接实现查询 的方式。,SELECT tb_userInfo09.*,tb_profession09.* FROM tb_userInfo09 CROSS JOIN tb_profession09, 在多表联接中设置联接条件,SELECT 学生姓名,学生性别,学生年龄,课程名称,老师姓名 FROM tb_student09,tb_course09,tb_teacher09 WHERE tb_student09.自动编号=tb_course09.编号 and tb_course09.自动编

46、号=tb_teacher09.编号,多表查询, 使用表别名,SELECT 学生姓名,学生性别,学生年龄,课程名称,老师姓名 FROM tb_student09 AS a,tb_course09 AS b,tb_teacher09 AS c WHERE a.自动编号=b.编号 and b.自动编号=c.编号, 在多表联接中返回某个表的所有列名,SELECT tb_student09.*,课程名称 FROM tb_student09,tb_course09 WHERE tb_student09.自动编号=tb_course09.编号,多表查询(内联接), 等值联接,等值联接是指在联接条件中使用等号

47、运算符比较被联接列的值,在查 询结果中列出本联接表中的所有列,包括其中的重复列.,语法： Select fieldlist From table1INNER JOIN table2 On table1.column=table2.column,SELECT tb_course09.课程名称,tb_teacher09.老师姓名 FROM tb_course09 INNER JOIN tb_teacher09 ON tb_course09.自动编号=tb_teacher09.编号,多表查询(内联接), 不等联接,不等联接是指在联接条件中使用等于运算符以外的其它比较 运算符比较被联接的值,SELEC

48、T tb_course09.自动编号,tb_course09.课程名称, tb_teacher09.编号,tb_teacher09.老师姓名 FROM tb_course09 INNER JOIN tb_teacher09 ON tb_course09.自动编号tb_teacher09.编号,多表查询(内联接), 自然联接,自然连接是一种特殊的联接，它只在两个表有相同名称的列且列的 含义相似时才能使用，并将在同名列上进行相等联接自然联接是 指在联接条件中使用等号运算符比较被联接列的值，但它使用选择 列表指出查询结果集合中所包括的列，并删除联接表中重复列,SELECT a.*,b.实发合计 FR

49、OM tb_mMRem09 AS a INNER JOIN tb_nMRpay09 AS b on a.人员编号=b.人员编号 WHERE b.工资月份=3,Select a.*,b.实发合计 from tb_mMRem09 a, tb_nMRpay09 b where a.人员编号=b.人员编号 and b.工资月份=3,多表查询(外联接), 左外联接(left outer join),SELECT a.部门编号,a.部门名称,a.负责人, b.人员编号,b.人员姓名,b.部门名称, b.学历,b.技术职称 FROM tb_nMRde09 a LEFT OUTER JOIN tb_mMRem

50、09 b ON a.部门名称=b.部门名称,多表查询(外联接), 右外联接(right outer join),SELECT * FROM tb_nMRde09 a RIGHT OUTER JOIN tb_nMRpay09 b ON a.部门名称=b.部门名称 WHERE b.工资月份=10,多表查询(外联接), 完全联接(full join),SELECT * FROM tb_student09 a FULL JOIN tb_course09 b ON a.自动编号=b.编号,多表查询(自联接),SELECT a.员工姓名,a.基本工资,b.员工姓名,b.基本工资 FROM tb_pay09

51、 a INNER JOIN tb_pay09 b ON a.基本工资b.基本工,多表查询(静态交叉表),SELECT 员工姓名, SUM( CASE 所在部门 WHEN 食品部 THEN 销售业绩 ELSE NULL END) AS 食品部业绩, SUM( CASE 所在部门 WHEN 家电部 THEN 销售业绩 ELSE NULL END) AS 家电部业绩 FROM tb_sell09 GROUP BY 员工姓名,多表查询(组合查询), 使用UNION运算符组合两个表的数据,SELECT 学生编号 AS 编号 , 学生姓名 AS 姓名 FROM tb_StuMark07 WHERE 高数8

52、5 UNION SELECT 编号 ,姓名 FROM tb_stuScore09 WHERE 高数85, 使用UNION ALL保留重复行,SELECT * FROM tb_nemployee09 UNION ALL SELECT * FROM tb_nemployee09,INSERT语句,INSERT语句语法:,INSERT INTO VALUES(column value,., lastcolumn value),INSERT语句, 插入单行数据,INSERT INTO tb_em11 VALUES (WY003,王三,女,1981-01-03,本科,长春理工大学, 长春市春城大街),

53、特定字段数据插入,INSERT INTO tb_em11 (员工编号,员工姓名,员工家庭住址) VALUES (WY004,王三,长春市自由大街), NULL值的插入,INSERT INTO tb_em11 VALUES (NULL,NULL,NULL,1981-01-04,本科,东北师范大学, NULL),INSERT语句, 插入日期数据,INSERT INTO tb_goods11 VALUES (LZW003,明日数码相机,数码设备,500万像素,50, Getdate() ), 使用UNION ALL语句批量插入数据,INSERT tb_employee11 SELECT WY005,

54、王五,男,1981-04-05,专科,长春工程学院,长春市大经路 UNION ALL SELECT WY006,王六,女,1982-04-05,专科,长春中医学院,长春市大马路 UNION ALL SELECT WY007,王七,男,1985-04-05,专科,长春工程学院,长春市解放大路,INSERT语句, 使用INSERT SELECT语句批量插入数据,INSERT INTO tb_employee11 SELECT * FROM tb_em11, 创建数据表之后插入数据,CREATE TABLE tb_sell11( 编号 CHAR(20),产品名称 CHAR(50),销售单价 MONE

55、Y ) GO INSERT INTO tb_sell11 VALUES (XS001,办公用品,60),UPDATE语句,UPDATE SET = .,= WHERE ,UPDATE语句, UPDATE语句更新列值,UPDATE tb_Student12 SET 学号=LEFT(学号,2)+1+RIGHT(学号,4) WHERE 专业名=计算机, 利用子查询更新多行的值,UPDATE tb_Grade12 SET 成绩=成绩+10 WHERE 学号 IN (SELECT 学号 FROM tb_Student12 WHERE 专业名=计算机) AND 课程号=01,UPDATE语句, 使用CAS

56、E表达式更新列值,update tb_case12 set 消费水平 = case when 商品价格2000 then 昂贵 when (商品价格=800) and (商品价格=2000) then 一般 end, 只更新存在于其它表中的数据,update tb_update12 set 浮动工资=200 where 职工姓名 in (select 姓名 from tb_personnel12),DELETE语句,DELETE FROM WHERE,DELETE语句, 删除单条数据,DELETE FROM tb_Student12 where 姓名=孙秀斌, 删除多行数据,DELETE FR

57、OM TB_GRADE12 WHERE 课程号=01, 删除所有数据,DELETE FROM tb_Student12, 删除部分重复的行,Delete from tb_deleteAll12 where 编号 not in ( SELECT MAX(编号) AS 编号 FROM tb_deleteAll12 GROUP BY 姓名),DELETE语句, 使用子查询删除数据,DELETE FROM tb_Test12 WHERE 学号 IN ( SELECT 学号 FROM tb_Grade12 WHERE tb_Test12.学号=tb_Grade12.学号 AND tb_Test12.课程

58、号=tb_Grade12.课程号 ),使用视图, 基本知识点,视图是一种常见的数据库对象,可以把它看成是虚拟表或者 存储在数据库中的查询,它为查看和存取数据提供了另一种途径。,视图的主要用途是: 限制用户访问敏感的数据 帮助用户执行复杂的SQL查询,创建视图语法格式: Create view view_name(column,n) with encryption As Select_statement with check option,使用视图, 使用视图过滤不想要的数据,Use db_sql go create view v_ware as select 编号,商品名称,售价 from t

59、b_ware14 go select * from v_ware, 使用视图与计算数据,Use db_sql go create view v_ware2 (商品名称, 利润) as select 商品名称,(售价-进价)as 利润 from tb_ware14 go select * from v_ware2,使用视图, 使用视图简化复杂的连接,use db_sql go create view v_employee as select tb_employee14.员工编号,tb_employee14.员工姓名, tb_laborage14.薪资编号,tb_laborage14.基本工资,tb_job14.请假天数 from tb_employee14 left join tb_job14 on tb_employee14.员工编号 = tb_job14.员工编号 left join tb_laborage14 on tb_laborage14.员工编号 = tb_job14.员工编号 go