SQL Statement Personal Summary 1

Previous database courses A lot of learning is not very good, now review the previous study.

Create a statement for a database

/* CREATE DATABASE */create wly on primary (    name= ' Wly_data ',                                  --The logical name of the main data file    filename= ' F:\sql statement \mdf database \wly_ Data.mdf ',     --The physical name of the master data file    SIZE=5MB,--the size of the                                         master data file    MAXSIZE=100MB,                                    --the maximum filegrowth=15% of the master data    file                                    --The growth rate of the master data file) log on (/    * Description of the log output file */    name= ' Wly_log ',                                       filename= ' F:\sql statement \log output file \wly_log.ldf ',    SIZE=5MB,    filegrowth=1mb)

Now that you've created the database, there's a statement to delete the database

/* Delete databases */drop database wly

Of course, after that, we have to back up and restore the database.

/* Back up the database */use testexec sp_addumpdevice ' disk ', ' Test1bak ', ' F:\sql statement \ Database backup \test1.bak '--Create the backup data devicebackup database Test to test1bak/* Restore Database */use master/* EXEC sp_addumpdevice ' disk ', ' Test1bak ', ' F:\sql statement \ Database backup \test1.bak ' */restore Database test from Test1bak with replace-     -directly overwriting databases, removing with replace will make an error

After this, sometimes you have to export the database SQL file, below I have a picture to express

1: Right-click on the database, task-and generate script, and then appear:

2: Click Next to appear the database, select the database to generate the script. Then you change the script that writes the data to True,

3: Click two times Next, the options that appear, save the script as a file:

4: After clicking Finish, the desktop will appear test.sql files.

After the operation of the database, of course it is the operation of the table

Build table

/* Instance tables */create table student (    sid int,    sname varchar,    primary KEY (SID)) CREATE TABLE teacher (    tid int,< C4/>tname varchar,    SID int,    primary key (TID),    foreign key (SID) references student (SID)) CREATE TABLE role ( C8/>rid int,    rname varchar,    SID int,    primary key (Rid,rname))

Here are some of the modified operations, the exact format is basically the same

/* Add Column */alter table teacher Add Class varchar/* adds unique constraint */alter table teacher add unique (Class)/* Add FOREIGN KEY constraint */alter table Rol E add Constraint role_student foreign key (SID) references student (SID)/* Delete columns */alter table teacher drop Column class/* Modify columns Define */alter table teacher ALTER COLUMN class INT/* To add an index, it is best to put the data into the database for re-indexing, otherwise the index key value and the record row of the clustered index will be maintained once every time the index is added *//* the index key value of the nonclustered index only Pairs are ordered *//* unique index equivalent UNIQUE constraint */create unique index stu_sid on student (SID) Create unique clustered index Tea_tid on teacher (t ID ASC) DROP index STU_SID on student

The basic content of this, and then I will be a little review of the complex things learned, this one is not much to say, the next article to discuss with you.

SQL Statement Personal Summary 1