データベース作成&データテーブル作成

-- 2_1      

create database Student201038897041

on primary 

(name='student1',

filename='F:\coures\SQL Server     \student\data\student1.mdf',

size=10MB,

maxsize=100MB,

filegrowth=10%),

filegroup[group1]

(name=N'student2',

filename=N'F:\coures\SQL Server     \student\mydb\Student2.ndf',

size=20MB,

maxsize=100MB,

filegrowth=1MB

)

LOG ON 

(name=N'Studentlog1',

filename='F:\coures\SQL Server     \student\log\Studentlog1.ldf',

size=10MB,

maxsize=50MB,

filegrowth=1MB

)

alter database student201038897041

add log file(name=N'studentlog2',

filename='F:\coures\SQL Server     \student\log\Studentlog2.ldf',

size=10MB,

filegrowth=1MB,

maxsize=50MB

)



-- 2_2   Student   ，

--        Student3，     file1

--    ，    5MB，    20MB，

--    15%。



alter database student201038897041

add file 

(name='student3',

filename='F:\coures\SQL Server     \student\mydb\student3.ndf',

size=5MB,

maxsize=20MB,

filegrowth=15%

)to filegroup group1



--      Student3.ndf.









-- 2_3  Student    ，  “   ”，

--      ：  (char(6))、

--  (Char(8))、  (int not null 

--  (Char(2)))．     ，

--   16 30   ，      “ ”．

create table studentTable 

(studentNum char(6)not null primary key,

name char(8) ,age int not null,check(age>=16 and age<=30),

sex char(2) check(sex=' 'or sex=' ') default(' '),

)



-- 2_4      “   ”，          

-- ：    (varchar(30))      (Char(20))．

alter table studentTable

add  studentAddress varchar(30),

studentCollege char(20)



---- 2_5      “   ’，         20

alter table studentTable

add constraint age_s default (20)for age



-- 2_6  “   ”  4   

insert into studentTable(studentNum,name,age,sex,studentAddress

,studentCollege)

values('021101','  ',20,' ','   ','     ')

insert into studentTable(studentNum,name,age,sex,

studentAddress,studentCollege)

values('022111','  ',18,' ','   ','   ')



insert into studentTable(studentNum,name,age,sex,

studentAddress,studentCollege)

values('034320','  ',19,' ','   ','     ')

insert into studentTable(studentNum,name,age,sex,

studentAddress,studentCollege)

values('031202','  ',20,' ','   ','   ')



-- 2_7        。

--(1)      ，                 ，      。



update studentTable set studentCollege='     ' 

where studentNum='021101'



----(2)            。

update studentTable set studentAddress='   ' 

where studentNum='022111';



----(3)       ，            ，         。



delete from studentTable where

 studentCollege='     ';



-- 2_8．      “  ”           IX_XM，       。

create unique  index  IX_XM on 

studentTable(name desc)



-- 2_9             

--     IX_  ，         ，

--      ，       ．

create index Ix_age on studentTable

(age desc,studentNum asc)



-- 2_10            ，       80，

--    address．  SQL      address       ．

create nonclustered index [address]on studentTable

(studentAddress)with fillfactor=80



-- 2_11    address，       90．

--    address “IX_  ”。

drop index [address]on studentTable

go

drop index [IX_age]on studentTable

go

sp_helpindex studentTable



go

-- 2_13 Student     “   ”  

drop table studentTable

go

drop database [student2010388979041]

go

--2_15         studentcourse．

-- studentcourse              S、

--     C        sc，

--      3.6～ 3.8  ：       3.9～ 3.11  ：      3.3～ 3.5  

create database studentcourse



go

create table s (

   char(6)not null primary key default('J0400'),

   char(8)not null,

   char(2)not null,

     datetime not null default('1980-01-01'),

  varchar(20)not null,

   char(8),

CONSTRAINT CK_Num CHECK (   Like'[A-Z][0-9][0-9][0-9][0-9]'),

CONSTRAINT CK_Sex CHECK (   =' ' OR    =' '),

CONSTRAINT CK_tel CHECK (   Like'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

)



go



create table c

(    char(3)not null primary key,

    varchar(20) not null,

   smallint ,      char(3),

   char(8),

CONSTRAINT CK_Cno CHECK (    Like 'C[0-9][0-9]'),

)



 go

 CREATE TABLE     SC (

     Char (6) NOT NULL,

      Char (3) NOT NULL,

     Smallint  NULL,

FOREIGN KEY(   ) REFERENCES  C (   ),

FOREIGN KEY(    ) REFERENCES  S (  ),

CHECK (  >=(0) AND   <=(100) OR    IS NULL),

PRIMARY KEY CLUSTERED (    ASC,    ASC)) 





 --            

 select s.  ,s.  ,SC.   ,c.   ,c.  ,sc.   from s,sc,c 

 where s.  =SC.   and SC.   =C.    and s.  ='  '

 

 --               

 select s.  ,s.  ,c.   ,c.   ,c.  ,SC.   from SC,s,c

 where s.  =SC.   and SC.   =c.    and c.  ='   '