MS_SQL必须了解的脚本语句（自留）

----------新建数据库-----------------

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%

)

-----------新建表--------------

CREATE TABLE student

( 

学生编号 int IDENTITY(1,1),

姓名 nvarchar(50),

***号 varchar(18) PRIMARY KEY,

所在班级 tinyint,

成绩 tinyint,

备注 nvarchar(2000)

)

------------插入数据-----------

INSERT INTO student (姓名,***号,所在班级,成绩)

VALUES(‘王兰‘,‘123456789123456788‘,2,90)

-----------删除数据------

DELETE FROM student WHERE 姓名=‘王兰‘

DELETE FROM student

TRUNCATE TABLE student

--------删除表----------

DROP TABLE class

---------删除数据库--------

DROP DATABASE test

---------修改---------

UPDATE student set 成绩=‘95‘,备注=‘勤奋好学‘ WHERE 姓名=‘王兰‘

ALTER TABLE student ADD 生日 date

ALTER TABLE student DROP COLUMN 生日

EXEC sp_rename ‘student‘,‘STUDENT‘ 

EXEC  sp_renamedb  ‘class‘, ‘CLASS‘

--------查询命令--------------------------

SELECT * FROM student

SELECT 姓名,所在班级,成绩 FROM student

SELECT 姓名 FROM student WHERE 所在班级=7

SELECT * FROM student WHERE 成绩 between 90 and 100

SELECT * FROM student WHERE 成绩<90 or 成绩>95

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

SELECT * FROM student WHERE 姓名 like ‘刘%‘

SELECT * FROM student WHERE 姓名=‘刘婷‘ and 所在班级=2

SELECT * FROM student WHERE 备注 is not null

SELECT top 5 * FROM student

SELECT 姓名 as ‘name‘,***号 as ‘idcard‘ FROM student

SELECT 姓名 name,***号 idcard FROM student

SELECT * FROM student order by ‘成绩‘ desc

SELECT * into student_back FROM student

SELECT * into student_2 FROM student where 所在班级=2

---------------完整的表格数据------------

INSERT INTO student (姓名,***号,所在班级,成绩)

VALUES(‘张飞‘,‘123456789123456789‘,2,90)

INSERT INTO student 

VALUES(‘张强‘,‘123456789123456780‘,3,88,‘喜欢音乐‘)

INSERT INTO student (姓名,***号,所在班级,成绩)

VALUES(‘刘备‘,‘123456789123456781‘,3,92)

INSERT INTO student (姓名,***号,所在班级,成绩)

VALUES(‘刘婷‘,‘123456789123456782‘,4,88)

INSERT INTO student (姓名,***号,所在班级,成绩)

VALUES(‘王兰‘,‘123456789123456783‘,2,90)

INSERT INTO student 

VALUES(‘王菲‘,‘123456789123456784‘,2,83,‘歌声悠扬‘)

INSERT INTO student 

VALUES(‘李双江‘,‘123456789123456785‘,2,80,‘悲惨的很‘)