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. 创建数据库goods
2. 创建3个表
Users表(用户表)

Ware表(商品信息表)

Shopping表(商品销售表)

3. 基本概念: 表、行、列、属性、字段、域、实体.
4. 数据库重命名,表结构的修改.
5. SQL(Structured Query Language-结构化查询语言):是由IBM公司在70年代开发的关系型数据库原型System R的一部分,现在已成为关系型数据库系统通用的查询语言,它是数据库系统的通用语言.SQL语言主要包括数据定义,数据控制,数据操纵和数据查询等功能,其中最重要的是数据查询功能.
6. 数据定义语言DDL(Data Definition Language)
<1>创建数据库
create database goods
<2>选择数据库
use goods
<3>创建表
create table users(
User_ID int,
User_Name nvarchar(50),
Email nvarchar(50),
Card nvarchar(50),
Tell nvarchar(50),
Address nvarchar(50))
<4>修改表
--添加一列
alter table users
add remark varchar(50)
--修改一列的数据类型
alter table users
alter column remark varchar
--删除一列
alter table users
drop column remark
<5>删除表
drop table Users
<6>删除数据库(注意确定一下当前正在使用的数据库)
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,'jodon123@','1234567890','138888888','美国')
注意:
（1）必须用逗号将各个数据分开，字符型数据要用单引号括起来,且into可以省略。
（2）INTO子句中没有指定列名，则新插入的记录必须在每个属性列上均有值，且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。
（3）将VALUES子句中的值按照INTO子句中指定列名的顺序插入到表中。
（4）对于INTO子句中没有出现的列，则新插入的记录在这些列上将取空值(remark).
（5）当插入的数据包含了每一个列,则可以省略列名.如下:
Insert into users
values(1234,'jodon',35,'jodon123@','1234567890',
'138888888','美国')
2. 修改记录
<1> update users
set user_age=20
<2>update users
set user_age=26
where user_id=1234
<3>update users
set user_age=user_age+5
3. 删除记录
<1>delete
from users
where user_id=4321
<2> delete
from users
4. 主键约束
<1>

<2> 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. 上机练习
<1>练习课堂内容
<2>建立学生数据库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_age>20-->= < <= <> != =
4. 查询年龄在20和50之间的记录
<1>select *
from users
where user_age>=20 and user_age<=50
<2>select *
from users
where user_age between 20 and 50
5. 查询年龄大于30或年龄小于20的记录
select *
from users
where user_age <20 or user_age>30
6. 查询年龄不等于30的记录
<1>select *
from users
where user_age <>30--!=
<2>select *
from users
where not user_age =30
7. 查询年龄不等于20也不等于30的记录
<1> select *
from users
where user_age !=30 and user_age<>20
<2> select *
from users
where not (user_age =30 or user_age=20)
8. 查询年龄等于20或者等于30的记录
<1> select *
from users
where user_age =30 or user_age=20
<2> select *
from users
where user_age in (20,30)
上面7也可以用下面方式实现
select *
from users
where user_age not in (20,30)—not 也可以放在user_age之前

9. 查询users表中有多少条不同的记录
select distinct *
from users
10. 查询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〉 练习课堂所讲内容
2〉2人一组以student数据库为例互相设计题
第四节课
1. 修改users表,添加新列users_sex(性别)
alter table users
add user_sex nchar(10)
2. 查询出前两条记录
select top 2 *
from users
3. 查询users表中的所有记录,并按年龄降序或升序排列
select *
from users
order by user_age asc—asc可以省略不写,默认即为asc

select *
from users
order by user_age desc
4. 查询所有记录，并按年龄升序排列，年龄相同按card降序排列
select *
from users
order by user_age asc , card desc
5. 查询users表中男女各多少人
select count(*)
from users
group by user_sex
注意:使用分组的时候,查询的列必须出现在聚合函数中或者必须出现在group by 后面,下面的写法是错误的:
select user_name,user_sex
from users
group by user_sex
6. 查询users表中男女各多少人,且只有当人数超过3人才输出信息
select count(*)
from users
group by user_sex having count(*)>3
7. 查询users表中最大的年龄是多少
select max(user_age)
from users
8. 查询users表中最小的年龄是多少
select min(user_age)
from users
9. 求users表中所有人年龄的总和
select sum(user_age)
from users
10. 求users表中所有人的平均年龄
select avg(user_age)
from users
连接查询
1. 笛卡尔积
select *
from ware,shopping
2. 等值连接
select *
from ware,shopping
where ware_code=code

select *
from ware join shopping
on ware_code=code
3. 不等值连接
select *
from ware,shopping
where ware_code<>code

select *
from ware join shopping
on ware_code<>code
4. 查询所有已销售商品的名称,进价,销售价,销售数量和日期
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=code
6. 查询所有已销售商品且盈利的名称,进价,销售价,销售数量和盈利额
select name,startprice,saleprice,shopping.amount,
shopping.amount*(saleprice-startprice) as 盈利
from ware join shopping
on ware_code=code
where saleprice-startprice>0
7. 自然连接(特殊的等值连接,去掉了重复的列)
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=code
2. 右外连接
select *
from ware right join shopping
on ware_code=code
3. 全外连接
select *
from ware full join shopping
on ware_code=code
4. 自连接
<1>查找年龄比姚明小的所有记录
select a.*
from users as a,users as b
where a.user_age27、明'
<2>查找年龄不比姚明大的所有记录
错误写法:
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_name<>b.user_name and a.user_age<=b.user_age
and b.user_name='姚明'
5. 交叉连接
select *
from ware cross join shopping
说明:其实就是笛卡儿积
6. 集合查询
<1>并(union)
select ware_code,date
from ware
union
select code,date
from shopping
--注意:所查询的目标列必须相同
<2>交(intersect)
select ware_code
from ware
intersect
select code
from shopping
<3>差(minus)—说明:SQL2005不支持minus运算,oracle支持
第六节课 复习
以student数据库为例复习前面所讲内容

第七节课 子查询—嵌套查询(所有的链接查询都可以用子查询实现)
1. 查询出王小明的所有成绩
步骤：
<1>查询出王小明的学号
select stu_id
from stu
where stu_name=’王小明’
<2>查询出上面所查询出的学号所对应的课程成绩
select s_score
from score
where s_id=stu_id
子查询：
select s_core
from score
where s_id=
(select stu_id
from stu
where stu_name=’王小明’)
按照处理方式分为相关子查询和无关子查询
相关子查询：是一种子查询和外层查询相互交叉的数据检索方法（一般不用）
无关子查询：子查询中不包含对外层查询的任何引用。
2. 查询所有选修了编号为C123课程的学生姓名
select name
from stu
where stu_id in
(select c_id
from xunke
where kc_id=’N123’)
单行子查询—子查询返回的是一行，因此可以可以把子查询作为一个常量对待
3. 查询身份证号为4105221996212的学生都所选课程的编号
select kc_id
from xuanke
where c_id=
(select stu_id
from stu
where code=’4105221996212’)
注意：如果子查询的结果不是单个值，则系统会提示错误。
4. 查询年龄最大的学生的所有信息
select *
from stu
where stu_age=
(select max(stu_age)
from stu)
5. 查询年龄比张三小的所有学生信息
select *
from stu
where stu_age<
(select stu_age
from stu
where stu_name=’张三’)
多列子查询：如果子查询返回多个列的数据，就称为多列子查询。（一般很少应用）
6. 查询男生中年龄最大的学生的学号和姓名
select stu_id, stu_name
from stu
where (stu_id, stu_age) in
(select stu_id, max(stu_age)
from stu
group by stu_sex)
多行子查询：查询返回单列多行数据的子查询称为多行子查询。
7. 查询所有有成绩的学生信息
select *
from stu
where stu_id in
(select s_id
from score
where not s_fenshu is null)
8. 查询所有选修了C语言的学生信息
select *
from stu
where stu_id in
(select s_id
from course, xuanke
where course.kc_id=xunanke.kc_id and course.name=’C语言’)
9. 查询所有没有选修C语言的学生信息
select *
from stu
where stu_id not in
(select s_id
from course, xuanke
where course.kc_id=xunanke.kc_id and course.name=’C语言’)
10. 查询年龄不小于所有女同学的男同学的学号和姓名
select stu_id, stu_name
from stu
where stu_sex=’男’ and stu_age>=any
(selelct stu_age
from stu
where stu_sex=’女’)
11. 查询年龄比所有女同学都大的男同学的学号和姓名
select stu_id, stu_name
from stu
where stu_sex=’男’ and stu_age>all
(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-SQL
1. 变量
->全局变量：由系统定义和维护，名称以两个@字符开始；
->局部变量：由用户定义和赋值，名称以单个@字符开始；
《1》全局变量
--使用全局变量记录SQL SERVER 服务器活动状态，对用户来讲是只读的。
常用全局变量举例：
@@ERROR--这个变量包含当前连接发生的最后一次错误的代码。在执行的语句没有错误时，@@ERROR变量的值是0。
select @@error
from student—先故意写错表名查询，然后再去掉from直接查询

@@ROWCOUNT—存储最近查询的记录数。
select *
from stu

select @@rowcount
@@VERSION—数据库版本信息
select @@VERSION
《2》局部变量
声明： DECLARE 变量名称 数据类型
declare @count int, @name char(10)
赋值：SET 变量名称=变量值
set @count=0

declare @max_age int
select @max_age=max(stu_age)
from stu
2. 运算符
《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 function

create function 函数名称(参数名称 as 数据类型)
returns 返回数据类型
[as]
begin
函数内容
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 int
as
begin
return 1+2;
end

select guest.test()
举例3：
create function dbo.upt