2006年10月26日数据库

        2006年10月26日 星期天 天气晴         Last Modify： Nov 1, 2006

==================================================================================

今天的主要内容就是数据库上机了。

SQL Server 2000上机实验

 

Microsoft SQL Server 支持两种类型的注释字符：

 

--（双连字符）。这些注释字符可与要执行的代码处在同一行，也可另起一行。从双连字符开始到行尾均为注释。对于多行注释，必须在每个注释行的开始使用双连字符。

/* ... */（正斜杠-星号对）。这些注释字符可与要执行的代码处在同一行，也可另起一行，甚至在可执行代码内。从开始注释对 (/*) 到结束注释对 (*/) 之间的全部内容均视为注释部分。对于多行注释，必须使用开始注释字符对 (/*) 开始注释，使用结束注释字符对 (*/) 结束注释。注释行上不应出现其它注释字符。 多行 /* */ 注释不能跨越批处理。整个注释必须包含在一个批处理内。

 

4．1 综合实验1

现有一个图书管理数据库BookShop，其中存放两张表，书Book（书名，作者，书号，价格，出版社），即Title char(26), author char(10), t_no char(5), price float, pub char (30)；出版Book_Page（书号，页数），即T_no char(5), page int，试用SQL语言完成下列功能：

1.    建表

    create table book(

    title    char(26),

    author    char(10),

    t_no    char(5),

    price    float,

    pub    char(30)

    )

 

 

    create table Book_Detail(

    T_no    char(5),

    page    int,

    )

 

2.    修改表的定义：

(1)    删除表Book中的出版社属性。

    alter table book

    drop column pub

(2)    更改表Book_Page的名字为Book_Detail。

    sp_rename book_page,book_detail

(3)    向表Book中插入一个属性：库存量QTY(int)。

    alter table book

    add QTY int

(4)    向表Book_Detail中插入一个属性：出版年 pub_date (int)。

    alter table book_detail

    add pub_date int

(5)    更改表Book_Detail中页数的类型为字符型

    alter table book_detail

    alter column page char

 

3.    创建、删除索引：

(1)    将书名定义为Book表的唯一性索引NameIDX。

    create unique index NameIDX

    on book ( title)

(2)    将书号分别定义为Book和Book_detail表的聚簇索引NumIDX和DNumIDX。

    create clustered index NumIDX

    on book(t_no)

    

    create clustered index DNumIDX

    on book_detail(t_no)

(3)    将价格定义为Book表的索引PriceIDX。

    create index PriceIDX

    on book(price)

(4)    删除索引NameIDX。

    drop index book.NameIDX

 

似乎索引储存于sysindexes表中，可以这样检测并删除该索引

    USE pubs

    IF EXISTS (SELECT name FROM sysindexes

     WHERE name = 'au_id_ind')

     DROP INDEX authors.au_id_ind

    GO

似乎表名储存于INFORMATION_SCHEMA.TABLES中，可以这样检测该表

    IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

     WHERE TABLE_NAME = 'T1')

     DROP TABLE T1

    GO

    CREATE TABLE T1 ( column_1 int, column_2 varchar(30))

    INSERT T1 (column_2, column_1) VALUES ('Row #1',1)

 

 

4.    向表中插入数据：

    insert into book(title, author, t_no, price, qty)

    values('计算机原理','张一平','S3092',20.80,200)

    insert into book(title, author, t_no, price, qty)

    values('C语言程序设计','李华','H1298',15.30,300)

    insert into book(title, author, t_no, price, qty)

    values('数据库原理','王家树','D1007',22.70,150)

    insert into book(title, author, t_no, price, qty)

    values('计算机网络','高明','S5690',18.90,230)

    insert into book(title, author, t_no, price, qty)

    values('Artificial intelligence','P.Winston','D2008',20.50,400)

    insert into book(title, author, t_no, price, qty)

    values('Expert systems','R.Ullman','H3067',17.00,370)

    insert into book(title, author, t_no, price, qty)

    values('软件工程','鲁廷璋','S2005',35.00,200)

    insert into book(title, author, t_no, price, qty)

    values('Fortran 程序设计','顾学峰','S5006',18.00,180)

    

    alter table book_detail

    alter column page int

    

    insert into book_detail(T_no, page, pub_date)

    values('S3092',304,1986)

    insert into book_detail(T_no, page, pub_date)

    values('D1007',280,1993)

    insert into book_detail(T_no, page, pub_date)

    values('S5006',315,1987)

    insert into book_detail(T_no, page, pub_date)

    values('S5690',300,1993)

    insert into book_detail(T_no, page, pub_date)

    values('H1298',210,1989)

    insert into book_detail(T_no, page, pub_date)

    values('D2008',358,1994)

    insert into book_detail(T_no, page, pub_date)

    values('S2005',298,1995)

    insert into book_detail(T_no, page, pub_date)

    values('H3067',307,1995)

 

通配符

使用通配符可以搜索任何被视为文本字符串的列。带有数据类型字符的列可以始终作为文本字符串处理。

 

若要按样式搜索，请使用 LIKE 运算符，然后用通配符代替搜索字符串中的一个或多个字符。可使用下面两个通配符的任意一个：

 

通配符 含义

%（百分号） 在该位置有零个或多个字符

_（下划线） 在该位置有一个字符

 

 

例如，若要搜索所有以"Mac"开始的名字，可指定搜索条件 LIKE 'Mac%'。若要查找"Jan"、"Jen"和"Jon"这类名字，可指定搜索条件 LIKE 'J_n'。

 

 

 

5.    记录的插入、删除与更新：

(1)    利用子查询方式建立表Book_1,该表的属性应有书号、书名和价格。

    create table book_1 (

    book_no    char(5),

    title    char(26),

    price    float

    )

 

    insert into book_1

    select t_no, title, price FROM book

(2)    在Book_1表中插入一元组：S7028，Digital Image Processing，36.00。

    insert into book_1

    values('S7028','Digital Image Processing',36.00)

(3)    删除书名为"Fortran 程序设计"的那个元组。

    delete from book_1

    where title='Fortran 程序设计'

(4)    删除书号以 H 开头的元组。

    delete from book_1

    where title like 'H%'

(5)    把书价调整到原来价格的 95% 。

    update book_1

    set price=price*0.95

(6)    把书号以 D 开头的那些书的书价减掉2.00元。

    update book_1

    set price = price -2

    where book_no like 'D%'

(7)    将"计算机原理"的书号改为 S1135。

    update book_1

    set book_no = 'S1135'

    where title = '计算机原理'

 

6.    统计：

(1)    计算Book表中这些书籍的最高书价、最低书价及平均书价。

    select max(price) as max, min(price) as min, avg(price) as average

    from book

(2)    计算Book表中的书的种类是多少。('Questions:where is the category to group by?')

    select distinct count( title)

    from book

(3)    计算Book_Detail表中1990年后出版的书有多少。

    select * from book_detail

    where pub_date > 1990

(4)    计算总共有多少本书。

    select count(title)

    from book

4．2 综合实验2

现有一个学生选修课程的数据库School，其中存放以下三个表：学生Student（学号，姓名，性别，年龄，系别），即Sno char (3), Sname char(4), Ssex char (2), Sage int, Sdept char(6)；课程Course（课程号，课程名，任课教师），即Cno char(3), Cname char(8), teacher char(4)；选修SC（学号，课程号，分数），即Sno char (3), Cno char (3), Grade int，试用SQL语言完成下列功能：

1.    建表，在定义中要求声明：

(1)    每个表的主外码；

(2)    学生的年龄介于16到40之间；

(3)    学生的姓名和课程名不能为空值；

(4)    选课成绩要么为空值，要么取0到100之间的整数。

    create table student(

    sno     char(3) primary key,

    sname     char(4) not null,

    ssex    char(2),

    sage    int check(sage> 16 and sage<40),

    sdept    char(6))

 

    create table course(

    cno    char(3),

    cname    char(8) not null,

    teacher    char(4),

    primary key (cno)

    )

    

    为没有设置主键的表设置主键

    --alter table course

    --add primary key (cno)

 

    create table sc(

    sno    char(3) not null,

    cno    char(3) not null,

    grade    int

    check(grade = null or (grade>0 and grade<100))

    )

 

2.    往表中插入数据

    insert into student(sno, sname, ssex, sage, sdept)

    values ('101','张三','男',18,'计算机')

    insert into student(sno, sname, ssex, sage, sdept)

    values ('102','李四','男',16,'数学')

    insert into student(sno, sname, ssex, sage, sdept)

    values ('103','王玲','女',17,'中文')

    insert into student(sno, sname, ssex, sage, sdept)

    values ('105','李飞','男',19,'计算机')

    insert into student(sno, sname, ssex, sage, sdept)

    values ('109','赵四','女',18,'历史')

    insert into student(sno, sname, ssex, sage, sdept)

    values ('110','李平','男',20,'化学')

 

    insert into course(cno, cname,teacher)

    values ('203','操作系统','程羽')

    insert into course(cno, cname,teacher)

    values ('279','高等数学','王备')

    insert into course(cno, cname,teacher)

    values ('210','现代文学','沈同')

    insert into course(cno, cname,teacher)

    values ('243','有机化学','程羽')

    insert into course(cno, cname,teacher)

    values ('204','数据结构','张青')

 

    insert into sc(sno, cno,grade)

    values ('101','203',82)

    insert into sc(sno, cno,grade)

    values ('105','203',59)

    insert into sc(sno, cno,grade)

    values ('102','279',90)

    insert into sc(sno, cno,grade)

    values ('101','279',88)

    insert into sc(sno, cno,grade)

    values ('105','279',82)

    insert into sc(sno, cno,grade)

    values ('110','279',68)

    insert into sc(sno, cno,grade)

    values ('109','210',72)

    insert into sc(sno, cno,grade)

    values ('103','210',90)

    insert into sc(sno, cno,grade)

    values ('110','243',92)

    insert into sc(sno, cno,grade)

    values ('101','204',85)

    insert into sc(sno, cno,grade)

    values ('105','204',91)

    insert into sc(sno, cno,grade)

    values ('102','204',56)

 

3.    用SQL语句完成下列查询：

(1)    列出张三同学选修的所有课程的名称及其成绩。

    select sc.cno, cname, grade

    from sc, course

    where sc.cno = course.cno and

    sc.sno = (select sno from student

         where sname ='张三')

(2)    查询各学生的平均成绩。

    select sno, avg(grade)

    from sc

    group by sno

(3)    查询计算机、数学专业学生所选修的课程详细信息。

    select *

    from course

    where cno in (select cno

         from sc

         where sno in (select sno

                 from student

                 where sdept = '计算机' or sdept='数学'));

(4)    将学生选课情况按课程号升序、成绩降序排列。

    select *

    from sc

    order by cno, grade DESC

4.    记录更新：

(1)    删除所有成绩不及格的选课记录。

    delete from sc

    where grade< 60

(2)    将"279"号课程的考试成绩置成空值。

    update sc

    set grade = null

    where cno = 279

 

4．3 综合实验3

现有一个图书借阅管理的数据库BookBorrow，其中存放以下三个表：书 Book (书号，类别，书名，出版社，作者，价格，总藏书量，库存，出版年份)，即bno char(8), category char(10), title varchar(40), press char(30), author char(20), price decimal(7,2), total integer, stock integer, pressdate datetime；借书证 Card(卡号，姓名，单位，类别)，即cno char(7), cname char(10), department varchar(40), type char(1)；借书记录borrow(卡号，书号，借期，还期)，即cno char(7), bno char(8), borrowdate datetime, returndate datetime，试用SQL语言完成下列功能：

1.    建表，在定义中要求声明：

(1)    每个表的主外码；

(2)    图书的库存量不能多于总藏书量；

(3)    图书还期不能早于借期。

    create table book(

    bno        char(8) primary key,

    category    char(10),

    title        varchar(40),

    press        char(30),

    author        char(20),

    price        decimal(7,2),

    total        integer,

    stock        integer,

    pressdate    datetime,

    constraint dateLimit

    check (stock <= total)

    )

 

    create table card(

    cno        char(7) primary key,

    cname        char(10),

    department    varchar(40),

    type        char(1)

    )

 

    create table borrow(

    cno        char(7) foreign key references card(cno),

    bno        char(8) foreign key references book(bno),

    borrowdate    datetime,

    returndate    datetime,

    constraint returndateLimite

    check (returndate > borrowdate)

    )

2.    向表中插入数据：

    insert into book

    values('10000001', 'TP80010001', '数据库系统原理教程', '清华大学出版社', '王珊', 35.2,20, 19, '2000-01-01')

    insert into book

    values('10000002', 'TP80010001', '数据库系统概论', '高等教育出版社', '萨师煊', 25.1, 12, 11, '2004-03-01')

    insert into book

    values('10000011', 'TP82010001', '计算机原理', '高等教育出版社', '张一平', 27.1, 39, 37, '2004-08-01')

    insert into book

    values('10000013', 'TP82010001', ' C语言程序设计', '高等教育出版社', '李华', 21.1, 54, 52, '2001-05-01')

    insert into book

    values('10000005', 'TP80010001', '数据库原理', '高等教育出版社', '王家树', 18.5, 43, 43, '2001-05-01')

    insert into book

    values('10000015', 'TP82010001', '计算机网络', '高等教育出版社', '高明', 15.9, 23, 23, '1998-08-01')

 

    insert into card

    values('C100001', '王娜', '计算机', 'A')

    insert into card

    values('C200001', '李锋', '数学', 'A')

    insert into card

    values('C100002', '刘晨', '计算机', 'A')

    insert into card

    values('C300001', '李平', '金融', 'B')

    insert into card

    values('C300002', '陈旭', '金融', 'B')

    insert into card

    values('C300004', '董升', '经济', 'B')

 

    insert into borrow

    values('C100001', '10000001', '2005-01-01 ', '2005-03-01')

    insert into borrow

    values('C100001', '10000002', '2005-02-19 ', '2005-03-01')

    insert into borrow

    values('C100001', '10000013', '2005-02-19 ', '2005-04-19')

    insert into borrow

    values('C200001', '10000013', '2006-02-19 ', '2006-04-19')

    insert into borrow

    values('C200001', '10000011', '2006-02-19 ', '2006-03-19')

    insert into borrow

    values('C300001', '10000011', '2004-12-19 ', '2005-01-19')

 

3.    用SQL语句完成下列查询：

(1)    求藏书类别数、图书的总册数、图书最高价和最低价信息

    select count(distinct category) as Cat_Amount,

    sum(total) as TotalBooks,

    max(price) as MaxPrice,

    min(price) as MinPrice

    from book

(2)    求藏书类别数和各类别图书的总册数、最高价、最低价信息。

    select count(distinct category) as Cat_Amount,

    sum(total) as TotalBooks,

    max(price) as MaxPrice,

    min(price) as MinPrice

    from book

    group by category

(3)    列出藏书在十本以上的书（书名、作者、出版社、年份）。

    select title, author, press, pressdate

    from book

    where total>10

(4)    哪些出版社的总藏书超过（多于）100本？

    select press

    from book

    group by press

    having (sum(total) > 100)

(5)    哪些出版社的藏书类别多于30种？

    select press

    from book

    group by press

    having (count(distinct category) > 30)

(6)    目前已借出多少册书？

    select sum(total - stock) as totalBorrow

    from book

(7)    "数据库系统原理教程，王珊编，清华大学出版社，2000年出版"还有几本？

    select (total - stock) as BookLeft

    from book

    where title = '数据库系统原理教程'

    and author = '王珊'

    and press ='清华大学出版社'

    and pressdate ='%2000%'

 

 

 

 

        Rev 1.0        Creat Document. Oct 26, 2006

        Rev 1.1        Implement more content.    Oct 31, 2006

        Rev 1.2        Complete This Document.    Nov 1, 2006