SQL语句

一.数据定义语句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