sql2005教案.doc

SQL2005教案(40课时)第一节课 sql2005的安装,数据库和表的创建以及基本概念1.图示说明 2.基本概念 数据库(DB,Database):数据库是长期储存在计算机内,有组织,可共享的大量数据的集合. 数据(Data):数据是数据库中存储的基本对象,它是描述事物的符号记录.数据是信息的载体,信息是数据反映出的一种关系. 数据库管理系统(DBMS,Database Management System):是数据库的机构,是一种软件,负责数据库中的数据组织,数据操作,数据维护,控制及保护和数据服务等. 数据库系统(DBS):包含数据,数据库,数据库管理系统,数据管理员等.3.数据库系统的发展 人工管理阶段 文件系统阶段 数据库系统阶段4.SQL2005的版本及常用数据库 SQL2005版本: * SQL Server Enterprise Edition：具有企业级功能的SQL Server版本，适用于大型企业以及大型数据库或数据仓库的服务器版本。 * SQL Server Standard Edition：具有标准功能的SQL Server版本，适用于一般企业的服务器版本。 * SQL Server Workgroup Edition：自SQL Server 2000开始才有的版本，专为工作组群或部门所设计，适用于较小规模的组织。 * SQL Server Web Edition：自SQL Server 2008开始才有的版本，专为Web 服务器与Web Hosting所设计，功能上较Workgroup Edition少一些。 * SQL Server Express Edition：免费的SQL Server版本，适用于小型应用程序或是单机型应用程序，但在功能上有设限，如只能使用一颗处理器，以及最大数据库大小为4GB等。请见SQL Server Express。 目前常用数据库: 开源公司的mysql 微软的mssql我们也经常说是SQL SERVER oracle公司的oracle SYBASE的powerbuilder IBM的DB2 BORLAND公司的产品interbase， 美国Informix软件公司的Informix 数据库,表的创建1. 创建数据库goods2. 创建3个表Users表(用户表)Ware表(商品信息表)Shopping表(商品销售表)3. 基本概念: 表、行、列、属性、字段、域、实体.4. 数据库重命名,表结构的修改.5. SQL(Structured Query Language-结构化查询语言):是由IBM公司在70年代开发的关系型数据库原型System R的一部分,现在已成为关系型数据库系统通用的查询语言,它是数据库系统的通用语言.SQL语言主要包括数据定义,数据控制,数据操纵和数据查询等功能,其中最重要的是数据查询功能.6. 数据定义语言DDL(Data Definition Language)创建数据库create database goods 选择数据库 use goods 创建表 create table users(User_ID int, User_Name nvarchar(50), Email nvarchar(50), Card nvarchar(50), Tell nvarchar(50), Address nvarchar(50)修改表 -添加一列 alter table usersadd remark varchar(50)-修改一列的数据类型 alter table usersalter column remark varchar-删除一列 alter table usersdrop column remark删除表drop table Users删除数据库(注意确定一下当前正在使用的数据库)drop database t第二节课 数据处理语句DML(Data Manipulation Language)1. 插入数据insert into users(user_id,user_name,user_age,email,card,tell,address) values(1234,jodon,35,,1234567890,138888888,美国) 注意: （1）必须用逗号将各个数据分开，字符型数据要用单引号括起来,且into可以省略。（2）INTO子句中没有指定列名，则新插入的记录必须在每个属性列上均有值，且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。（3）将VALUES子句中的值按照INTO子句中指定列名的顺序插入到表中。（4）对于INTO子句中没有出现的列，则新插入的记录在这些列上将取空值(remark).（5）当插入的数据包含了每一个列,则可以省略列名.如下:Insert into usersvalues(1234,jodon,35,,1234567890,138888888,美国)2. 修改记录 update usersset user_age=20 update users set user_age=26 where user_id=1234 update users set user_age=user_age+53. 删除记录 delete from users where user_id=4321 delete from users4. 主键约束 create table users(User_ID int primary key, User_Name nvarchar(50), Email nvarchar(50), Card nvarchar(50), Tell nvarchar(50), Address nvarchar(50)5. 上机练习练习课堂内容建立学生数据库student,并建立四张表:学生基本信息表,课程设置表,选课表,成绩表,并练习所学的SQL语句 第三节课 简单查询语句1. 查询users表中所有记录的所有字段 select *-也可以把所有的列都写上 from users 注释补充: -注释一行 /*/注释一段2. 查询所有记录的姓名和年龄 select user_name,user_age from users 别名补充: select user_name as 姓名,user_age as 年龄 from users -其中as可以省略3. 查询年龄超过20的所有记录 select * from users where user_age20-= = != =4. 查询年龄在20和50之间的记录select * from users where user_age=20 and user_age=50 select * from users where user_age between 20 and 505. 查询年龄大于30或年龄小于20的记录 select * from users where user_age 306. 查询年龄不等于30的记录select * from users where user_age 30-!= select *from userswhere not user_age =307. 查询年龄不等于20也不等于30的记录 select *from userswhere user_age !=30 and user_age20 select *from userswhere not (user_age =30 or user_age=20)8. 查询年龄等于20或者等于30的记录 select *from userswhere user_age =30 or user_age=20 select *from userswhere user_age in (20,30) 上面7也可以用下面方式实现 select * from users where user_age not in (20,30)not 也可以放在user_age之前9. 查询users表中有多少条不同的记录 select distinct * from users10. 查询users表中都有哪些年龄的记录 select distinct user_age from users模糊查询（通配符_,%）11. 查询所有姓姚且名字为两个字的记录 select * from users where user_name like 姚_12. 查询所有姓姚的记录 select * from users where user_name like 姚%13. 查找第二个字为“小”的所有记录 select * from users where user_name like %小%14. 查询所有不姓姚的记录 select * from users where user_name like 姚%15. 查询所有姓姚或姓王的记录 select * from users where user_name like 姚,王%16. 查询所有姓姚且第二个字不为大或小的记录 select * from users where user_name like 姚大，小%17. 查询姓名不为空的记录 select * from users where user_name is not null上机练习：1 练习课堂所讲内容22人一组以student数据库为例互相设计题第四节课1. 修改users表,添加新列users_sex(性别) alter table users add user_sex nchar(10)2. 查询出前两条记录 select top 2 * from users3. 查询users表中的所有记录,并按年龄降序或升序排列 select * from users order by user_age ascasc可以省略不写,默认即为asc select *from usersorder by user_age desc4. 查询所有记录，并按年龄升序排列，年龄相同按card降序排列 select * from users order by user_age asc , card desc5. 查询users表中男女各多少人 select count(*) from users group by user_sex 注意:使用分组的时候,查询的列必须出现在聚合函数中或者必须出现在group by 后面,下面的写法是错误的: select user_name,user_sex from users group by user_sex6. 查询users表中男女各多少人,且只有当人数超过3人才输出信息 select count(*) from users group by user_sex having count(*)37. 查询users表中最大的年龄是多少 select max(user_age) from users8. 查询users表中最小的年龄是多少 select min(user_age) from users9. 求users表中所有人年龄的总和 select sum(user_age)from users10. 求users表中所有人的平均年龄 select avg(user_age) from users 连接查询 1. 笛卡尔积 select * from ware,shopping2. 等值连接 select * from ware,shopping where ware_code=code select * from ware join shopping on ware_code=code3. 不等值连接 select * from ware,shoppingwhere ware_codecode select * from ware join shopping on ware_codecode4. 查询所有已销售商品的名称,进价,销售价,销售数量和日期 select name,startprice,saleprice,shopping.amount,shopping.date from ware join shopping on ware_code=code 注意:当连接的表中出现相同列名时,必须加表名来区分5. 查询所有已销售商品的名称,进价,销售价,销售数量和盈利额 select name,startprice,saleprice,shopping.amount, shopping.amount*(saleprice-startprice) 盈利 from ware join shopping on ware_code=code6. 查询所有已销售商品且盈利的名称,进价,销售价,销售数量和盈利额 select name,startprice,saleprice,shopping.amount, shopping.amount*(saleprice-startprice) as 盈利 from ware join shopping on ware_code=code where saleprice-startprice07. 自然连接(特殊的等值连接,去掉了重复的列) select ware.*,shopping.amount,shopping.saleprice,shopping.date from ware inner join shopping on ware_code=code 补充:inner join 也叫内连接,inner也可以省略不写,即默认为内连接第五节课 外连接1. 左外连接 select * from ware left join shopping on ware_code=code2. 右外连接 select * from ware right join shopping on ware_code=code3. 全外连接 select *from ware full join shoppingon ware_code=code4. 自连接查找年龄比姚明小的所有记录 select a.* from users as a,users as b where a.user_ageb.user_age and b.user_name=姚明查找年龄不比姚明大的所有记录 错误写法: select a.* from users as a,users as b where a.user_age=b.user_age and b.user_name=姚明 正确写法: select a.* from users as a,users as b where a.user_nameb.user_name and a.user_age=b.user_age and b.user_name=姚明5. 交叉连接 select * from ware cross join shopping 说明:其实就是笛卡儿积6. 集合查询并(union) select ware_code,date from ware union select code,date from shopping-注意:所查询的目标列必须相同交(intersect) select ware_code from ware intersect select code from shopping差(minus)说明:SQL2005不支持minus运算,oracle支持第六节课 复习 以student数据库为例复习前面所讲内容第七节课 子查询嵌套查询(所有的链接查询都可以用子查询实现)1. 查询出王小明的所有成绩步骤：查询出王小明的学号 select stu_id from stu where stu_name=王小明查询出上面所查询出的学号所对应的课程成绩 select s_score from score where s_id=stu_id子查询： select s_core from score where s_id= (select stu_id from stuwhere stu_name=王小明)按照处理方式分为相关子查询和无关子查询相关子查询：是一种子查询和外层查询相互交叉的数据检索方法（一般不用）无关子查询：子查询中不包含对外层查询的任何引用。2. 查询所有选修了编号为C123课程的学生姓名select namefrom stuwhere stu_id in(select c_id from xunke where kc_id=N123) 单行子查询子查询返回的是一行，因此可以可以把子查询作为一个常量对待3. 查询身份证号为4105221996212的学生都所选课程的编号select kc_idfrom xuankewhere c_id=(select stu_id from stu where code=4105221996212)注意：如果子查询的结果不是单个值，则系统会提示错误。4. 查询年龄最大的学生的所有信息select *from stuwhere stu_age=(select max(stu_age) from stu)5. 查询年龄比张三小的所有学生信息select *from stuwhere stu_age=any(selelct stu_age from stu where stu_sex=女)11. 查询年龄比所有女同学都大的男同学的学号和姓名select stu_id, stu_namefrom stuwhere stu_sex=男 and stu_ageall(select stu_age from stu where stu_sex=女) exists使用：存在检查即只要子查询返回的结果不为空，则认为是真，反之认为是假。（有可能子查询和外层查询没有任何关系）12. select * from stu where exists (select * from score) select * from stu where not exists (select * from score) 多层嵌套：13. 查询选修了C语言的学生信息select * from stu where stu_id= (select xuanke.id from xuanke where kc_id= (select kc_id from course where kc_name=C语言)相同表结构之间拷贝数据14. insert into stu1 values(select * from stu2)15. 上机练习. 第八节课 T-SQL1. 变量-全局变量：由系统定义和维护，名称以两个字符开始；-局部变量：由用户定义和赋值，名称以单个字符开始；1全局变量 -使用全局变量记录SQL SERVER 服务器活动状态，对用户来讲是只读的。 常用全局变量举例： ERROR-这个变量包含当前连接发生的最后一次错误的代码。在执行的语句没有错误时，ERROR变量的值是0。 select errorfrom student先故意写错表名查询，然后再去掉from直接查询 ROWCOUNT存储最近查询的记录数。 select *from stuselect rowcount VERSION数据库版本信息 select VERSION2局部变量 声明： DECLARE 变量名称 数据类型 declare count int, name char(10) 赋值：SET 变量名称=变量值 set count=0 declare max_age int select max_age=max(stu_age) from stu2. 运算符1 算术运算符+ - * / %2 比较运算符（关系运算符） = = != 3 逻辑运算符and or not& | 3. 函数1 系统函数DB_NAME: 获得当前使用的数据库名 select db_name()HOST_NAME: 获得服务器主机名 select host_name()USER_NAME: 获得用户名称 select user_name() select user_name(2)USER_ID: 获得用户id select user_id()2 日期函数GETDATE: 获得系统日期 select getdate()DATEADD: 在给定日期的指定部位上增加指定的值select dateadd(day,2,getdate() DATEDIFF： 计算给定两个日期在指定部位上的差值 select datediff(day,2011/1/1,getdate() YEAR：获得给定日期的年份 select year(getdate() MONTH：获得给定日期的月份 select month(getdate() DAY： 获得给定日期的日 select day(getdate()3 字符串函数基本函数UPPER：小写转大写 select upper(abc) LOWER：大写转小写 select lower(ABC) SPACE：添加空格 select 姚+space(2)+名 REPLACE：替换字符 select replace(abbccdd,c,Q) STUFF：在给定的字符串中删除指定位置开始的指定长度，并在此位置插入给定的新字符串 select stuff(abcdef,2,3,123456) LTRIM：删除给定字符串左边的空格 select ltrim( abc d ); RTRIM：删除给定字符串右边的空格 select rtrim( abc d ); 查找函数 CHARINDEX：从指定位置开始查询指定的字符串，返回该字符串的位置编号 select charindex(bc,abcdefg,1); PATINDEX：（和上面的一样，但支持通配符select patindex(_b%,abcdefg) 长度和分析函数 DATALENGTH：求给定字符串的长度,或者是给定数据所占空间值 select datalength(123) select datalength(123) SUBSTRING：取子字符串 select substring(abcdefg,2,4) RIGHT：从给定字符串有变取指定长度子字符串select right(abcdefg,4) 转换函数 CHAR：求指定asc码值对应的字符 select char(97) STR：把给定整数转换成字符串 select str(12345) 4 数学函数 ABS：求绝对值 select abs(-2)CEILING：返回大于或等于给定数字的最小整数 select ceiling(3.56) FLOOR：返回小于或等于给定数字的最大整数 select floor(3.56) ROUND： select round(3.56478,3)select round(4321,-3)5 用户自定义函数定义：create function修改：alter function删除：drop functioncreate function 函数名称(参数名称 as 数据类型)returns 返回数据类型asbegin函数内容return 表达式 end 举例1：定义一个用来计算员工奖金的函数，并使用该函数输出员工的奖金 create function bonus(salary as money) returns money as begin return (salary *0.3) end -输出 select 姓名，dbo.bonus(工资) as 奖金 from 员工表 举例2： create function guest.test()returns intasbegin return 1+2;endselect guest.test()举例3：create function dbo.uptable(age as int)returns tableasreturn (select stu_id 学号,stu_name 姓名,stu_age 年龄from stuwhere stu_age=age)select *from dbo.uptable(44)4. 上机练习第九节课 T-SQL 语句块： Begin 语句块 End 流程控制语句：1 IfelseIf 条件表达式 begin语句块 end Else begin 语句块 end 举例1： declare age intselect age=max(sage) from studentif(age80) begin select age endelse begin select 无 end 举例2 declare name varchar(20)select name=林冲if exists(select * from student where sname=林冲) begin select * from student where sname=林冲 endelse begin print 没有+name+这个人 end2 While语句While 条件表达式 Begin 语句块 End举例：计算1到100之间的整数和declare a int,sum intset a=1set sum=0while a=100begin set sum=sum+a set a=a+1endprint 1到的100和为：+cast(sum as varchar) 3 Waitfor delay 时间 | time 时间作用：指定其后的语句在某一时刻或在一段时间间隔后继续执行。说明：1delay: 等待一个指定的时间间隔，最长为24小时 2time: 等待一个指定的时间。 3时间：指定等待的时间必须为datetime类型，不能包括日期举例1：延迟6秒后，执行如下查询waitfor delay 00:00:06select *from student举例2：在指定的时间执行如下查询waitfor time 16:31:00select *from student4 goto5 case表达式case表达式可以计算多个条件，并将其中一个符合条件的结果表达式返回。case表达式的语法为：case 字段名或变量名when 逻辑表达式1 then 结果表达式1when 逻辑表达式2 then 结果表达式2when 逻辑表达式3 then 结果表达式3end 举例： select stu_name,stu_age,性别=case stu_sex when 男 then 男士 when 女 then 女士endfrom student6 Return 作用：无条件地从过程、批或语句块中退出，其之后的语句不在执行7 上机练习 第十节课 数据的完整性 1.数据的完整性 实体完整性：要求表中的所有行都有唯一的标识符，即主关键字，因此，表中不会出现完全相同的两行。 域完整性：把属性值限定在一定的范围内，即不能取任意值。 参照完整性：属于表间的规则，即不能删除和其它参照表有关联的数据 用户定义的完整性：用户定义的一些特殊规则. 2.约束 不为空约束 not null 主键约束 primary check约束 unique约束 举例1： create table stu(stu_id int primary key check(stu_id0),stu_name varchar(6) not null,stu_age int check(stu_age=0 and stu_age0),stu_name varchar(6) not null,stu_age int check(stu_age=0 and stu_age0),stu_name varchar(6) not null,stu_age int check(stu_age=0 and stu_age1000),stu_sex varchar(6) default 男,primary key(stu_id),unique(stu_name) 3.索引 概念：索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构（B 树）中，使 SQL Server 可以快速有效地查找与键值关联的行。 创建索引： create index stu_stu_id on stu(stu_id) 说明： 对于用户来讲，只负责创建索引，至于如何来执行所以则有系统来决定；索引的创建会加快数据的检，因此，通常在唯一列，主键或经常查询的列设置索引。 第十一节课 事务1、 事务的概念和模式概念：数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元。性质：1 原子性：即不可分割的操作序列。2 一致性：即数据的一致性。3 隔离性：即独立性，不依赖于其它任何事务。4 持久性：即当事务提交后，操作才会完成，否则取消全部操作。 模式：显示模式（用户定义事务），隐式模式，自动模式2、 事务的commit和rollbackBegin transaction: 事务的 开始Commit: 事务的提交