一.数据定义语句DDL
1.CREATE
CREATE DATABASE test ON PRIMARY --建立主数据文件 (NAME='test', FILENAME='E:MSSQL_DATA est.mdf', SIZE=5, MAXSIZE=5, FILEGROWTH=1) LOG ON ( NAME=test_log, FILENAME='E:MSSQL_LOG est_log.ldf', SIZE=1, MAXSIZE=2, FILEGROWTH=1 )
use test create table teacher( ID int Identity(1,1) not null, --identity表示自增，从1开始每次自增1 Name varchar(20) not null, Sex char(2) null, Phone varchar(20) not null, Sallary smallmoney null, Address varchar(100) null, Email varchar(30) null) --可变长度，最大为255个字符
2.DROP
DROP DATABASE test DROP TABLE teacher --注意和delete的区别，delete teacher是删除表teacher中的所有数据，DROP是删除表
3.ALTER
add、drop、alter column
ALTER DATABASE test modify name=test_t ALTER TABLE teacher add Subject varchar(30) null ALTER TABLE teacher --每次修改之前都要加一句ALTER，不能连续写2行 DROP COLUMN price
alter table Films alter column FilmName varchar(100) not null; --后面的varchar(100)不能丢
--加入组 ALTER DATABASE test ADD FILEGROUP test_group --连续加入2个次数据文件 ALTER DATABASE test ADD FILE (NAME=test1, FILENAME='E:MSSQL_DATA est1.ndf', SIZE=5, MAXSIZE=5, FILEGROWTH=1), ( NAME=test2, FILENAME='E:MSSQL_DATA est2.ndf', SIZE=5, MAXSIZE=5, FILEGROWTH=1 ) to filegroup test_group GO ALTER DATABASE test ADD LOG FILE( NAME=testlog1, FILENAME='E:MSSQL_LOG estlog1.ldf', SIZE=1, MAXSIZE=3, FILEGROWTH=1) GO --删除数据文件和组 ALTER DATABASE test remove file test2 ALTER DATABASE test remove filegroup tesst_group
二.数据操作语句DML
1.INSERT
- identity
在 insert into 该 table 时, 不能指定该 identity 栏位值.
identity 由资料库维护, 所以一般要在 insert 后取得该 identity 栏位值, 利用全局变量 @@identity 来取得最后影响的 insert 后产生的 identity 值.
显示地插入identity值后，不能update,只能插入。并且插入之后，不改变原来的identity的最大值，仍然在前一次自动插入的值的基础上加1.
查询目前的最大identity值，修改最大值，都用命令dbcc。只有当之前设置的identity比目前的值小，恢复时才会恢复到以前的比较大的identity值，否则不会改变。
--显示插入以及identity值的引用 set identity_insert teacher on insert into teacher(ID,Name,Sex,Phone,Sallary) values(@@IDENTITY+1,'wangying','nv','1812',10000) --在打开了显示插入之后，teacher后面一定要有列列表 select * from teacher set identity_insert teacher off set identity_insert teacher on insert into teacher(ID,Name,Sex,Phone,Sallary) values(1,'wangying','nv','1812',10000) select * from teacher set identity_insert teacher off --在insert into该table时，不能指定该identity栏位的值 insert into teacher(Name,Sex,Phone,Sallary) values('wangying','nv','1812',10000) select * from teacher insert into teacher values('wangying','nv','1812',10000,null,null,null) select * from teacher --查询目前identity的最大值 dbcc checkident('teacher',NORESEED) --修改目前identity的最大值以及重设该值 dbcc checkident('teacher',RESEED,100) dbcc checkident('teacher',RESEED)
--把一个表格中的项插入到另一个表格 insert into teacher(name,sex,phone,sallary) select name,sex,phone,sallary from student --选择部分列插入 set identity_insert teacher on insert into teacher(ID,NAME,SEX,PHONE,SALLARY,ADDRESS) select ID,NAME,SEX,PHONE,SALLARY,ADDRESS from student select * from teacher --如果被插入的表里面有identity项，必须用这样的方式插入
2.DELETE
delete student delete from student where student_id=1
3.UPDATE
update teacher set sex='男' where ID=110 select * from teacher where ID=110
4.select
select * from books select distinct book_name,price from books --distinct关键字使得被列举出来的元素只出现一次 select book_name,price,price*0.8 as newprice from books --as关键字使用别名
select firstname as [Christian Name],lastname as Surname from memberdetails --方括号允许列或者别名包涵通常认为是不合法的字符 select top 10 * from hosts select * from books where not (price>40) select * from books where price<40 and price>20 select * from books where price<40 or price>20 select * from student where 姓名 like ’高%‘ --‘%’匹配一个或多个字符，‘_’匹配一个字符;‘LIKE’与‘NOT LIKE’ select * from books where price between 20 and 40 select * from books order by price asc --asc按升序排序，dest按降序排列
select filmname,rating,yearreleased from films order by rating,yearreleased,filmname --被列举的顺序确定了排序的优先级
select * from books where price in (20,30,40) and rating in (4,5) --in运算符检查数据库，查看指定的列是否匹配圆括号中所列举的值
select 'First name is' + Firstname + ', Lastname is ' + LastName from MemberDetails --用‘+’来连接文本和连接列（MS SQL Server）
selcet Firstname+' '+Lastname from MemberDetails
运算符的优先级顺序：
（）>NOT>AND>ALL=ANY=BETWEEN=IN=LIKE=OR=SOME
从多个表中选择数据（内部联合）：
select FilmName,YearReleased,Rating from Films INNER JOIN Category ON Films.CategoryId=Category.CategoryId WHERE Category.CategoryId=6 select FilmName,YearReleased,Rating from Films,Category ON Films.CategoryId=Category.CategoryId WHERE Category.CategoryId=6 --内部联合的另外一种表达方式 select Category.Category,MemberDetails.FirstName,MemberDetails.LastName FROM FavCategory INNER JOIN Category ON FavCategory.CategoryId=Category.CategoryId INNER JOIN MemberDetails ON FavCategory.MemberId=MemberDetails.MemberId ORDER BY MemberDatails.LastName,MemberDatails.FirstName --建立多个表之间的连接
三.数据控制语句（DCL）
1.GRANT
GRANT SELECT,INSET ON student TO sal WITH GRANT OPTION --把表student的SELECT,INSERT权限授予给用户sal，并且该用户同时获得转授给别的用户的权限
2.DENY
DENY UPDAT ON student To sal
3.REVOKE
REVOKE INSERT ON student FROM sal CASCADE --CASCADE表示收回权限的时候要引起连锁回收
四.其他基本语句（后面再介绍）
1.DECLARE
用于声明一个或多个局部变量、游标变量、函数、过程等。被DECLARE声明的变量赋值为NULL，需要用SET或者SELECT赋值。
2.SET
3.PRINT
五.循环控制语句
1.BEGIN...END
2.GOTO
3.IF..ELSE
4.CASE
5.WHILE BREAK CONTINUE
6.WAITFOR
7.RETURN