SQLQuery项目（国外英文资料）.doc

SQLQuery项目（国外英文资料） / * one, t-sql creates the QQ database * / Use the master The go Create the fyw folder in D disk Exec xp_cmdshell D: yolo_CGT The go If exists (select * from sysdatabase where name = QQ database) The drop database QQ database The go The Create DataBase QQ DataBase On primary ( Name = QQ database _data, Filename = D: \ yolo_CGT \ QQ database _data.mdf Size = 10 MB, Maxsize = unlimited, Filegrowth = 15% ) The log on ( Name = QQ database _log, Filename = D: \ yolo_CGT \ QQ database _log.ldf Size = 5 MB, Maxsize = 50 MB, Filegrowth = 1 MB ) The go / * 2, create tables, constrain * / / * the basic information table BaseInfo * / Use QQ database The go If exists (select * from sys where name = BaseInfo) Drop table BaseInfo The go The create table BaseInfo ( QQID bigint not null, NickName varchar (30), Sex int, The Age int, Province varchar (30), City varchar (30), Address varchar (30), Phone char (30) ) The go Alter table BaseInfo -- the QQID primary key Add the constraint PK_QQID2 Primary key (QQID) The go Alter table BaseInfo - the gender allows null values, and if the input values must be 0 or 1:0 for the male and 1 for the female Add the constraint CK_Sex Check (the Sex = 1 or Sex = 0 ) The go Alter table BaseInfo -- the age must be an integer between 1 and 100 Add the constraint CK_Age (Age between 1 and 100) The go / * build user table QQUser * / Use QQ database The go If exists (select * from sys where name = QQUser) Drop table QQUser The go The create table QQUser ( QQID bigint not null, PassWord varchar (30) not null, LastLogTime datetime, The Online int, Level int ) The go Alter table QQUser -- the QQID primary key Add the constraint PK_QQID Primary key (QQID) The go Alter table QQUser - the QQ password should not be less than 6 Add the constraint CK_PassWord Check (PassWord) = 6 The go Alter table QQUser - the online status must be 0, 1, 2:0 for online, 1 for offline, 2 for stealth Add the constraint CK_Online Check (Online = 0 or Online = 1