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> 姓名 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> ---------删除数据库--------
DROP DATABASE test
---------修改---------
UPDATE student set 成绩='95',备注='勤奋好学' WHERE 姓名='王兰'
ALTER TABLE student ADD 生日 date
ALTER TABLE student DROP COLUMN 生日
EXEC sp_rename 'student','STUDENT'
EXECsp_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 成绩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,***号> 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,'悲惨的很')
页:
[1]