example.txt

常用链接 我的链接 软件下载 站点链接 PB程序设计 网上课堂 ...课程学 习资料 ◇ 04级课程设计通讯录源程序下载 ◇ 04计算机(1)班...doc) ◇ PB 期末复习思考题 ◇ 数据库SQ

创建数据库
例程：

/* 
** CREABASE.SQL
** Create the library database.
** Remember sizes are specified in megabytes.
** Do not forget to make master your current database when you do this.
** If you already have a library database, you must drop it first before re-creating it
*/
USE master
/*  Drop the library database if it already exists  */
IF DB_ID('library') IS NOT NULL
BEGIN
    DROP DATABASE library
END

/*  Create the Database  */
CREATE DATABASE library
ON
  PRIMARY ( NAME=library_data,  
  FILENAME='c:\mssql7\data\library.mdf', 
  SIZE=15MB,
  MAXSIZE=30MB,
  FILEGROWTH=1MB) 
LOG ON 
  ( NAME=library_log, 
  FILENAME='c:\mssql7\data\library.ldf', 
  SIZE=2MB,
  MAXSIZE=7MB,
  FILEGROWTH=1MB)
GO

/*  
**  View information on the library database 
**  and to verify that the database was created.  
*/

EXEC sp_helpdb library





创建表
例程：
/*
** This script file creates all of the tables for the Library database. There are a total of nine tables.
** Tables are dropped first if this is a re-creation.
*/

USE library
IF OBJECT_ID('dbo.member') IS NOT NULL
    DROP TABLE dbo.member
IF OBJECT_ID('dbo.adult') IS NOT NULL
    DROP TABLE dbo.adult
IF OBJECT_ID('dbo.juvenile') IS NOT NULL
    DROP TABLE dbo.juvenile
IF OBJECT_ID('dbo.title') IS NOT NULL
    DROP TABLE dbo.title

IF OBJECT_ID('dbo.item') IS NOT NULL
    DROP TABLE dbo.item
IF OBJECT_ID('dbo.copy') IS NOT NULL
    DROP TABLE dbo.copy
IF OBJECT_ID('dbo.reservation') IS NOT NULL
    DROP TABLE dbo.reservation
IF OBJECT_ID('dbo.loan') IS NOT NULL
    DROP TABLE dbo.loan
IF OBJECT_ID('dbo.loanhist') IS NOT NULL
    DROP TABLE dbo.loanhist
GO

CREATE TABLE member
  (
     member_no        member_no IDENTITY(1,1)   NOT NULL
  ,  lastname         shortstring  NOT NULL 
  ,  firstname        shortstring  NOT NULL 
  ,  middleinitial    letter           NULL
  ,  photograph       image            NULL
  )

CREATE TABLE adult
  (
     member_no        member_no    NOT NULL
  ,  street           shortstring  NOT NULL
  ,  city             shortstring  NOT NULL
  ,  state            statecode    NOT NULL
  ,  zip              zipcode      NOT NULL
  ,  phone_no         phonenumber      NULL
  ,  expr_date        datetime     NOT NULL
  )

CREATE TABLE juvenile
  (
     member_no        member_no    NOT NULL
  ,  adult_member_no  member_no    NOT NULL
  ,  birth_date       datetime     NOT NULL
  )

CREATE TABLE title
  (
     title_no         title_no     IDENTITY(1,1) NOT NULL
  ,  title            longstring   NOT NULL
  ,  author           normstring   NOT NULL
  ,  synopsis         text             NULL
  )

CREATE TABLE item
  (
     isbn             isbn         NOT NULL
  ,  title_no         title_no     NOT NULL
  ,  translation      item_info        NULL
  ,  cover            item_info        NULL
  ,  loanable         yes_no           NULL
  )

CREATE TABLE copy
  (
     isbn             isbn         NOT NULL
  ,  copy_no          smallint     NOT NULL
  ,  title_no         title_no     NOT NULL
  ,  on_loan          yes_no       NOT NULL
  )

CREATE TABLE reservation
  (
     isbn             isbn         NOT NULL
  ,  member_no        member_no    NOT NULL
  ,  log_date         datetime         NULL
  ,  remarks          remarks          NULL
  )

CREATE TABLE loan
  (
     isbn             isbn         NOT NULL
  ,  copy_no          smallint     NOT NULL
  ,  title_no         title_no     NOT NULL
  ,  member_no        member_no    NOT NULL
  ,  out_date         datetime     NOT NULL
  ,  due_date         datetime     NOT NULL
  )

CREATE TABLE loanhist
  (
     isbn             isbn         NOT NULL
  ,  copy_no          smallint     NOT NULL
  ,  out_date         datetime     NOT NULL
  ,  title_no         title_no     NOT NULL
  ,  member_no        member_no    NOT NULL
  ,  due_date         datetime         NULL
  ,  in_date          datetime         NULL
  ,  fine_assessed    money            NULL
  ,  fine_paid        money            NULL
  ,  fine_waived      money            NULL
  ,  remarks          remarks          NULL
  )

GO

-- Display results.
SELECT table_name
  FROM information_schema.tables
  WHERE table_name IN (  'member'
                       , 'adult'
                       , 'juvenile'
                       , 'title'
                       , 'item'
                       , 'copy'
                       , 'reservation'
                       , 'loan'
                       , 'loanhist'
                      )
GO
BACKUP LOG library WITH TRUNCATE_ONLY
GO


创建查询（检索）
例程：

SELECT * FROM student
SELECT id, name, score FROM student
SELECT id, name='abc', score=85 FROM student
SELECT id, average = avg(score) FROM student GROUP BY(year)ORDER BY (id)

SELECT id, name, score
FROM student
WHERE (score>60 AND score<85)   良
  OR (name='abc')

SELECT name FROM student WHERE name LIKE 'M%'
SELECT id, name, score FROM student WHERE id BETWEEN 5 AND 15

HAVING和GROUP BY连用
SELECT id, year, name, score FROM student
GROUP BY (year)
HEVING year<1999

SELECT year, id, score FROM student
WHERE name LIKE 'M%'
COMPUTER AVE(score) BY year
从多张表查询：
SELECT name, id, gender, birthplace
FROM student, information
WHERE student.id = information.id

SELECT name, id
FROM student
UNION                     把查询结果连接在一起
SELECT gender, birthplace
FROM information


/*
**       CREATIND3.SQL
**  
**  Create all Foreign Key indexes.
**
**  Drop the old indexes first.
**
**  All tables that are updated during the normal working day will be
**      given a FILLFACTOR.  Moderately volatile tables will have enough
**      free space for one new row.  Volatile tables, such as Loan, will
**      be given more free space.  Relatively static tables will be given
**      none.
**  
**  Don't forget to say 'USE library' first.
*/

USE library
SET NOCOUNT ON
GO

/*    
**  If the objects already exist (i.e. if this is a rebuild), drop them.
*/

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'juvenile_adult_link')
    DROP INDEX  juvenile.juvenile_adult_link
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'item_title_link')
    DROP INDEX  item.item_title_link
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'copy_title_link')
    DROP INDEX  copy.copy_title_link
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'loan_title_link')
    DROP INDEX  loan.loan_title_link
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'loan_member_link')
    DROP INDEX  loan.loan_member_link
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'reserve_item_link')
    DROP INDEX  reservation.reserve_item_link
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'loanhist_member_link')
    DROP INDEX  loanhist.loanhist_member_link
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'loanhist_title_link')
    DROP INDEX  loanhist.loanhist_title_link
GO

DUMP TRANSACTION library WITH TRUNCATE_ONLY
GO


创建索引
/***************************  Create the indexes. ***************************/

/******  Member related indexes.  ******/

DECLARE @message char(255)  DECLARE @began datetime
SELECT @began = GETDATE()

CREATE	NONCLUSTERED INDEX juvenile_adult_link ON juvenile (adult_member_no)

SELECT @message = 'Time (in minutes:seconds) to create Member related indexes.  '
     + CONVERT( char(2), DATEDIFF(ss,@began,GETDATE())/60 ) + ':'
     + CONVERT( char(2), DATEDIFF(ss,@began,GETDATE())%60 )
PRINT @message
GO

/******  Book related indexes.  ******/

DECLARE @message char(255)  DECLARE @began datetime  SELECT @began = 
GETDATE()

CREATE	   CLUSTERED INDEX item_title_link   ON item        (title_no)
CREATE     CLUSTERED INDEX copy_title_link   ON copy        (title_no)
CREATE     CLUSTERED INDEX loan_title_link   ON loan        (title_no)
CREATE  NONCLUSTERED INDEX loan_member_link  ON loan        (member_no)
    WITH FILLFACTOR = 75
CREATE     CLUSTERED INDEX reserve_item_link ON reservation (isbn)

SELECT @message = 'Time (in minutes:seconds) to create Book related indexes.  '
     + CONVERT( char(2), DATEDIFF(ss,@began,GETDATE())/60 ) + ':'
     + CONVERT( char(2), DATEDIFF(ss,@began,GETDATE())%60 )
PRINT @message
GO

DUMP TRANSACTION library WITH TRUNCATE_ONLY
GO


/******  Loan History related indexes.  ******/

DECLARE @message char(255)  DECLARE @began datetime  SELECT @began = 
GETDATE()

CREATE	NONCLUSTERED INDEX loanhist_member_link ON loanhist (member_no)
CREATE	NONCLUSTERED INDEX loanhist_title_link  ON loanhist (title_no)

SELECT @message = 'Time (in minutes:seconds) to create Loan History related indexes.  '
     + CONVERT( char(2), DATEDIFF(ss,@began,GETDATE())/60 ) + ':'
     + CONVERT( char(2), DATEDIFF(ss,@began,GETDATE())%60 )
PRINT @message
GO


/***************  Display the results  ***************/

PRINT 'CREATED INDEXES:'
SELECT name FROM sysindexes
   WHERE name IN (  'juvenile_member_link'
                   , 'item_title_link'
                   , 'copy_title_link'
                   , 'loan_title_link'
                   , 'loan_member_link'
                   , 'reserve_item_link'
                   , 'loanhist_member_link'
                   , 'loanhist_title_link'
                 )


PRINT 'SPACE REQUIRED:'
EXEC  sp_spaceused
EXEC  sp_spaceused juvenile
EXEC  sp_spaceused item
EXEC  sp_spaceused copy
EXEC  sp_spaceused loan
EXEC  sp_spaceused reservation
EXEC  sp_spaceused loanhist
GO


/*  Truncate the log.  */

DUMP TRANSACTION library WITH TRUNCATE_ONLY
GO

SET NOCOUNT OFF
GO


创建视图
/*
** CREAVIEW.SQL
** This scripts creates all the VIEWS for the Library database.
*/

USE library
GO

/*    
** If the objects already exist (i.e. if this is a rebuild), drop them.
*/

IF EXISTS ( SELECT table_name FROM information_schema.views
            WHERE table_name = 'AdultwideView' )
     DROP VIEW AdultwideView

IF EXISTS ( SELECT table_name FROM information_schema.views
            WHERE table_name = 'ChildwideView' )
     DROP VIEW ChildwideView

IF EXISTS ( SELECT table_name FROM information_schema.views
            WHERE table_name = 'CopywideView' )
     DROP VIEW CopywideView

IF EXISTS ( SELECT table_name FROM information_schema.views
            WHERE table_name = 'LoanableView' )
     DROP VIEW LoanableView

IF EXISTS ( SELECT table_name FROM information_schema.views
            WHERE table_name = 'OnshelfView' )
     DROP VIEW OnshelfView

IF EXISTS ( SELECT table_name FROM information_schema.views
            WHERE table_name = 'OnloanView' )
     DROP VIEW OnloanView

IF EXISTS ( SELECT table_name FROM information_schema.views
            WHERE table_name = 'OverdueView' )
     DROP VIEW OverdueView
GO

/*
** Create views for the library database;
**   AdultwideView - On the adult table.    Name & address for the adults.
**   ChildwideView - On the juvenile table. Name & address for the juveniles.
**   CopywideView  - On the copy table.     Widened to include title and item info.
**   LoanableView  - On the copywide view.  The copies marked as loanable.
**   OnshelfView   - On the copywide view.  The copies not currently on loan.
**   OnloanView    - On the copy table.     The copies currently on loan.  
**                                          Widened to include member info.
**   OverdueView   - On the onloan view.    The copies currently overdue.
*/

CREATE VIEW dbo.AdultwideView
AS
    SELECT 
         adult.member_no
      ,  member.lastname
      ,  member.firstname
      ,  member.middleinitial
      ,  adult.street
      ,  adult.city
      ,  adult.state
      ,  adult.zip
      ,  adult.phone_no
      ,  adult.expr_date
    FROM adult
    JOIN member
    ON   adult.member_no = member.member_no 
GO

CREATE VIEW dbo.ChildwideView
AS
    SELECT 
         juvenile.member_no
      ,  member.lastname
      ,  member.firstname
      ,  member.middleinitial
      ,  adult.street
      ,  adult.city
      ,  adult.state
      ,  adult.zip
      ,  adult.phone_no
      ,  adult.expr_date
    FROM juvenile
    JOIN member
    ON   member.member_no = juvenile.member_no
    JOIN adult
    ON   adult.member_no = juvenile.adult_member_no 
GO

CREATE VIEW dbo.CopywideView
AS
    SELECT 
         copy.isbn
      ,  copy.copy_no
      ,  title.title
      ,  title.author
      ,  item.[translation]
      ,  item.loanable
      ,  copy.on_loan
    FROM copy
    JOIN item
    ON   item.isbn = copy.isbn
    JOIN title
    ON   title.title_no = copy.title_no
GO

CREATE VIEW dbo.LoanableView
AS
    SELECT *
    FROM CopywideView
    WHERE loanable = 'Y'
GO

CREATE VIEW dbo.OnshelfView
AS
    SELECT *
    FROM CopywideView
    WHERE on_loan ='N'
GO

CREATE VIEW dbo.OnloanView
AS
   SELECT
         loan.isbn
      ,  loan.copy_no
      ,  loan.title_no
      ,  title.title
      ,  title.author
      ,  loan.member_no
      ,  member.lastname
      ,  member.firstname
      ,  loan.out_date
      ,  loan.due_date
    FROM loan
    JOIN title
    ON   title.title_no = loan.title_no
    JOIN member
    ON   member.member_no = loan.member_no
GO

CREATE VIEW dbo.OverdueView
AS
    SELECT *
    FROM OnloanView
    WHERE OnloanView.due_date < GETDATE()
GO

/*
** Display results.
*/
PRINT 'VIEWS CREATED'
SELECT table_name AS View_Name
FROM information_schema.views
WHERE table_schema = 'dbo'
AND table_name NOT LIKE 'sys%'
ORDER BY table_name