Ms_sql script statements that must be understood (retained)

----------Create a new database-----------------

CREATE DATABASE TEST

On (

Name=test_data,

Filename= ' C:\temp\test_data.mdf ',

Size=6,

MAXSIZE=50,

filegrowth=2

)

LOG on (

Name=test_log,

Filename= ' C:\temp\test_log.ldf ',

Size=1,

MAXSIZE=10,

filegrowth=10%

)

-----------New Table--------------

CREATE TABLE Student

(

Student number int IDENTITY (+),

Name nvarchar (50),

varchar (+) PRIMARY KEY,

The class tinyint,

Score tinyint,

Remark nvarchar (2000)

)

------------Inserting Data-----------

INSERT into student (name, * * Number, class, score)

VALUES (' Wang Lan ', ' 123456789123456788 ', 2,90)

-----------Delete Data------

DELETE from student WHERE name = ' Wang Lan '

DELETE from Student

TRUNCATE TABLE Student

--------Delete a table----------

DROP TABLE Class

---------Delete a database--------

DROP DATABASE Test

---------Modify---------

UPDATE Student Set score = ' 95 ', remark = ' studious ' WHERE name = ' Wang Lan '

ALTER TABLE student ADD Birthday Date

ALTER TABLE student DROP COLUMN Birthday

EXEC sp_rename ' student ', ' student '

EXEC sp_renamedb ' class ', ' class '

--------Query Command--------------------------

SELECT * FROM Student

SELECT name, class, score from student

SELECT name from student where class =7

SELECT * FROM student WHERE score between and 100

SELECT * FROM student WHERE score <90 or score >95

SELECT * FROM student WHERE score in (89,90,91)

SELECT * FROM student WHERE name like ' Liu% '

SELECT * FROM student where name = ' Liu Ting ' and where class =2

SELECT * FROM Student WHERE memo is not NULL

SELECT Top 5 * from student

SELECT name as ' name ', * * * as ' idcard ' from student

SELECT name name,*** number Idcard from student

SELECT * FROM Student order BY ' results ' desc

SELECT * into Student_back from student

SELECT * into student_2 from student where class =2

---------------The complete tabular data------------

INSERT into student (name, * * Number, class, score)

VALUES (' Zhang Fei ', ' 123456789123456789 ', 2,90)

INSERT into student

VALUES (' Zhang Qiang ', ' 123456789123456780 ', 3, 88, ' like Music ')

INSERT into student (name, * * Number, class, score)

VALUES (' Liu Bei ', ' 123456789123456781 ', 3,92)

INSERT into student (name, * * Number, class, score)

VALUES (' Liu Ting ', ' 123456789123456782 ', 4,88)

INSERT into student (name, * * Number, class, score)

VALUES (' Wang Lan ', ' 123456789123456783 ', 2,90)

INSERT into student

VALUES (' Faye Wong ', ' 123456789123456784 ', 2, 83, ' song melodious ')

INSERT into student

VALUES (' Li Shuangjiang ', ' 123456789123456785 ', 2, 80, ' miserable very ')

Ms_sql script statements that must be understood (retained)