SQL数据库语法总结

1、SQL Sever 2008 数据库语法总结1.数据库创建管理1.1 创建数据库CREATE DATABASE DBON PRIMARY(NAME = DB_data,FILENAME = F:DB_data1.mdf, SIZE = 10MB,MAXSIZE = UNLIMTED),(NAME = DB_data2,FILENAME = F:DB_data2.ndf, SIZE = 11MB,MAXSIZE = 20MB, FILEGROWTH = 2MB)LOG ON(NAME = DB_log1,FILENAME = F:DB_log1.ldf, SIZE = 1MB,MAXSIZE =

2、 30MB,FILEGROWTH = 10%)1.2 修改数据库1.2.1 增加数据文件语法：alter database 数据库名称add file 数据文件to file group 文件组名称add log file 日志文件操作：ALTER DATABASE DB ADD FILE ( NAME = , FILENAME = , SIZE = , FILEGROWTH = )1.2.2 增加日志文件ALTER DATABASE DBADD LOG FILE ( NAME = , FILENAME = , SIZE = , FILEGROWTH = ,MAXSIZE = )1.2.3 修

3、改数据文件语法：ALTER DATABASE 数据库名MODIFY FILE 文件属性操作：将数据库db1中的数据文件data2的初始大小改为10MB，最大容量为20MB，增长幅度为10%alter database db1 modify file(name = data2,size = 10,maxsize = 20,filegrowth = 10%)ALTER DATABASE DBMODIFY FILE( NAME = , SIZE = ,)1.2.4 删除数据文件和日志文件语法：alter database 数据库名称remove file 数据文件或日志文件的逻辑文件名操作：删除数据

4、库db1中的数据文件data4和日志文件log2 alter database db1 remove data4 alter database db1 remove log21.2.5 增加文件组语法：alter database 数据库名add filegroup 文件组名操作：在数据库db1中增加一个g2文件组 alter database db1 add filegroup g21.2.6 重命名文件组语法：alter database 数据库名modify filegroup 文件组名name=新文件组名操作：将数据库db1中的文件组g2更名为g3alter database db1

5、modify filegroup g2 name=g31.2.7 删除文件组语法：alter database 数据库名称remove filegroup 文件组名操作：删除数据库db1的文件组g3 alter database db1remove filegroup g31.2.8 修改数据库名称语法：alter database 数据库名modify name = 新数据库名操作：将数据库db1的名字修改为glalter database db1modify name = gl1.3 删除数据库语法： Drop database 数据库名 ，n操作：删除数据库DB1,DB2,DB3DROP

6、 DATABASE DB1,DB2,DB32. 创建与管理表2.1 创建表（Create table ）语法 Create table 表名（ 列名 数据类型 是否允许为空值， . 列名 数据类型 是否允许为空值）2.2 修改表（Alert table ）语法（1）修改表指定数据类型Alert table 表名Alert column 列名 数据类型 NOT NULL/NULL （2） 增加列Alert table 表名Add column 列名 数据类型 NOT NULL/NULL （3） 删除列Alert table 表名Drop column 列名 （4）添加主键Alert table

7、表名add primary key(列名，列名，)（5）删除主键Alert table 表名Drop 主键名 /主键名是指“PK”+“_”+“表名”（6）删除关系Alert table 表名Drop 关系名 操作：Alert table 学生表Drop FK_学生表_专业表 2.3 删除表（Drop）语法（1）Drop table 表名（2）删除索引Drop index 索引名/2.5 修改基本表2.5.1 增加列语法： alter table 表名 add ,n 关于“标识列”： 每个表中都可以有一个标识列，其作用是由系统自动生成能标识表中每一行数据的惟一序列值。（其实可以理解为行号）“标识

8、列”定义格式： identity (seed, increment) 说明：seed为初始值，increment为增长的步长。意思就是你从几开始增长，每次增长几。省略时，初始值为1，步长为1.操作：在student表中，增加三列alter table studentadd address char(10) constraint df_xsqk_jg default shanghai , email varchar(30), number int identitygo2.5.2 修改列语法： alter table 表名 alter column 列名 新类型(长度,小数位数)操作1：将sc表的

9、grade列的数据类型修改为numeric(4,1)alter table sc alter column grade numeric(4,1)操作2：将sc表的grade列的数据类型修改为intalter table scalter column grade int注意：1.不能修改text、image、ntext、gimestamp类型的列；2.不能修改类型是varchar、nvarchar、varbinary的列的数据类型，但可增加其长度。3.不能修改是主键、外键列的类型，但可增加其长度；4.不能修改包含索引、有默认值、检查约束和惟一性约束列的类型，但可增加其长度。5.不能修改用列表达式

10、定义或被引用在列表达式中的列。6.不能修改复制列。2.5.3 修改表中的数据（对记录进行修改）语法：update 表名set 列名 = 表达式 | null | default , n ) where 逻辑表达式 即：update 表名set 列名 = 新值| null | default , n where 列名=条件表达式 操作：将sc表中的课程号为101的成绩不及格的学生的成绩都加上10分update scset grade = grade + 10where ( cno = 101 and grade 60)2.6 删除基本表2.6.1 删除列（删除的是表结构）注意：若列上有约束，所以

11、应先删除该约束后，再删除该列。语法： alter table 表名 drop column 列名操作：-先删除表中的约束Alter table student drop constraint df_xsqk_jg-再删除表中的列Alter table student drop column address,email,number 2.6.2 删除行（删除的是记录）语法： delete from表名 where 逻辑表达式 delete from表名 where 列名=条件表达式操作：删除student表中姓名为zhangsan的数据记录delete from student where na

12、me = zhangsan2.6.3 重命名数据表语法： exec sp_rename 表名，新表名操作：将student表重命名为rename_studentexec sp_rename student,rename_student2.6.4 删除数据表注意：如果要删除的表T1是其他表T2的参照表，即如果T2有外键约束，参照了T1的数据项，则不能删除。需要先取消T2表中的外键约束载删除T1，或者先删除T2表再删除T1表。语法： drop table 表名 ,n 操作：已知sc表设置了外键约束，参照了表student和表course，现在要删除student表和course表。Drop tab

13、le sc goDrop table student ,course2.7 向表中插入（ insert into ）数据（插入记录）2.7.1 插入单行数据语法： insert into 表名 (字段名列表) values (字段值列表)即：insert into 表名 (列名，列名，列名，) values (数据，数据，数据，)操作：已知student有以下数据项：SNO,SNAME,SAGE,SEX,SAGE,SDEPT，现向student表中插入数据INSERT INTO Student(SNO,SNAME,SAGE,SDEPT) VALUES (132,ZHANGSAN,23,ASD)

14、INSERT INTO Student VALUES (1234,ZHANGSAN,143,MAN,22,YI,)注意：字符型、日期型数据要用单引号括起来。2.7.2 插入多行数据语法： insert into 目的表名 ( 字段列表 ) select 源表名 . 列名 , n from 源表名 , n where 逻辑表达式 操作：将sc表中的成绩不及格的记录，插入到nopass表中,或者完全写出一一对应的列名。参考语法说明insert into nopass select * from sc where grade= 15 AND SAGE 0), senester TINYINT) CR

15、EATE TABLE SC( sno CHAR(7) NOT NULL, cno CHAR(9) NOT NULL, grade TINYINT, PRIMARY KEY (sno, cno) FOREIGN KEY (sno) REFERENCES Student (SNO) FOREIGN KEY (cno) REFERENCES Course (cno)3.4.2索引 PRIMARY KEY主键约束的作用：1.避免输入重复的值2.不能取空值 （当主键是由多个属性组成时：某一属性上的数据可以重复，但其组合必须是惟一的；每个属性的值都不能为空。）3.一个表上只能有一个主键。3.

16、4.2.2 UNIQUE惟一性约束的作用：保证列中不会出现重复的数据。主键约束与惟一性约束的区别：1.一个表中只能定义一个主键约束，但可以定义多个惟一约束。 2.定义了惟一约束的列数据可以为空值，而定义了主键约束的列数据不能为空值。 FOREIGN KEY外间约束的作用：用于建立和强制两个表间的关联，限制外键的取值必须是主表的主键值。 CHECK检查约束的作用： 1.用来限制列上可以接受的数据值 2.使用逻辑表达式来判断数据合法性 DEFAULT默认约束的作用：当列值未确定且该列又不能为空时，可由系统自动为该列添加一个值3.4.3 添加主键约束操作：在

17、学生情况表student中，添加“sno”的主键约束,主键约束命名为pk_student如果表不存在create table student ( sno char (6), sname char (8), ssex bit , sphone char(11) constraint pk_student primary key (sno)如果表已存在alter table studentadd constraint pk_studentprimary key (sno)3.4.4 创建唯一性约束操作：在student表中，创建“sphone”字段惟一性约束,并且将约束命名为uq_sphoneal

18、ter table student add constraint uq_sphone unique (sphone)2.4.4 创建外键约束create table sc( sno char ( 6 ) not null references student ( sno ), cno char ( 3 ), grade tinyint primary key (sno,cno), foreign key ( cno ) references course ( cno )3.4.6 创建检查约束操作1：在student表中，设置名为ck_student的检查约束，该约束限制“性别”为man或wo

19、manalter table student add constraint ck_student check （sex=man or sex=woman）操作2：在学生与课程表sc中,添加名ck_sc j的检查约束，该约束限制“成绩”在0到100之间alter table scadd constraint ck_sccheck (grade=0 and grade=100)3.4.7 设置默认约束操作1：新建表时添加create table student(name char(6) not null,age int(10) null,sex char(5) default man)操作2：修改

20、已有的表alter table student add constraint df_student default man for sex3.4.8创建约束小结create table student (sno char (6) not null , sname char (8) not null , ssex bit , 电话 char(11) )alter table xsqk add constraint pk_xsqk_xh primary key (学号), constraint df_xsqk_xb default 1 for 性别, constraint ck_xsqk_xb c

21、heck (性别=1 or 性别=0 ), constraint uq_xsqk_dh unique (电话)create table student ( sno char (6) primary key (学号) check (学号 like 0-9 0-9 0-9 0-9 0-9 0-9), sname char (8) not null , ssex bit default 1 check (ssex=0 or ssex=1) , sphone char(11) unique (sphone) )3.4.9 删除约束alter table student drop constraint

22、pk_xsqk_xhalter table student drop constraint uq_xsqk_dhalter table student drop constraint ck_xsqk _xbalter table student drop constraint ck_xsqk _xhalter table student drop constraint df_xsqk_xb 4.数据库查询4.1 基本查询语句结构语法：select - 需要哪些列form - 来自于哪些表where - 根据什么条件group by -分组依据having order by 部分能够包含的内容有

23、如下结构：SELECT ALL | DISTINCT TOP expression PERCENT WITH TIES :=*| table_name | view_name | table_alias .*| table_name | view_name | table_alias . column_name | $IDENTITY | expression AS column_alias | column_alias = expression ,.n 4.2 单表查询4.2.1 未做处理的查询 查询部分信息操作：查询student表中的全体学生姓名和年龄select name

24、,age from student 查询全部信息操作：查询全体学生的信息select * from student4.2.2 指定列别名语法： 列名 | 表达式 AS 列别名操作：在Student表中查询学生的姓名，和年龄（年龄由计算得出），将年龄列命名为ageSELECT name,year(getdate() - year(Birthdate) AS age FROM Student4.2.3 消除相同行（distinct）语法：在要求不重复的数据列前使用distinct关键字select distinct 列名from 表名操作：select distinct Sno fr

25、om SC4.2.4 where 指定查询条件常用查询条件如下表where查询条件查询条件谓词比较运算符比较运算符 =, , =, , =, （或!=）确定范围between and, not between and确定集合in,not in字符匹配like,not like空值IS NULL, IS NOT NULL多重条件AND, OR操作：查询不及格的学生姓名SELECT DISTINCT Sno FROM SC WHERE Grade =80 AND Grade =90操作2：查询考试成绩不在8090之间的学生学号、课程号和成绩SELECT Sno, Cno, Grade FROM S

26、C WHERE Grade NOT BETWEEN 80 AND 90等价与下面这句话SELECT Sno, Cno, Grade FROM SC WHERE Grade 904.2.6 IN 确定集合语法：列名 NOT IN (常量1, 常量2, )作用：用来查找属性值属于指定集合的元组操作1：查询信息管理系、通信工程系和计算机系学生的姓名和性别select name, sex from sudentwhere dept in (信息管理系,通信工程,计算机系)等价于select name, sex from sudentwhere dept = 信息管理系 or dept = 通信工程系

27、or dept = 计算机系操作2：查询信息管理系、通信工程系和计算机系三个系之外的其他系学生的姓名和性别SELECT Sname, Sex FROM Student WHERE Dept NOT IN ( 信息管理系, 通信工程系, 计算机系)等价于SELECT Sname, Sex FROM Student WHERE Dept!= 信息管理系 AND Dept!= 通信工程系 AND Dept!= 计算机系4.2.7 LIKE 字符串匹配（用like实现模糊查询）语法：列名 not like escape 说明：匹配串中可包含如下通配符：1.%（百分号）：匹配0个或多个字符。2._（下划

28、线）：匹配一个字符。3.：匹配方括号中的任何一个字符。4.：不匹配方括号中的任何一个字符。如果比较的字符是连续的，则可以用连字符“-”表达，例如，要匹配b、c、d、e中的任何一个字符，则可以表示为：b-e(说实话这不就是简化版的正则表达式么？)操作1：查询姓“张”的学生详细信息select * from student where name like 张%操作2：查询姓“张”、姓“李”和姓“刘”的学生的详细信息select * from student where name like 张刘李%操作3：查询名字的第2个字为“小”或“大”的学生的姓名和学号select * from student

29、 where name like _大小%操作4：查询所有不姓“张”的学生姓名select name from student where name not like 张%操作5：在Student表中查询学号的倒数第三为位不是1、2、3的学生信息select * from student where number like %123_3.2.8 ESCAPE 转义字符语法：ESCAPE 转义字符如果要查找的字符串正好含有通配符，比如下划线或百分号，就需要用ESCAPE来说明。其中“转义字符”是任何一个有效的字符，在匹配串中也包含这个字符，表明位于该字符后面的那个字符将被视为普通字符，而不是通配

30、符。操作：查找字段t1中包含字符串“30%”的记录where t1 like %30!% escape !4.2.9 NULL 空值查询语法： 列名 IS NOT NULL 空值是未确定的值或其值尚不知道。操作：查询还没有考试的学生的学号和相应的课程号SELECT Sno, Cno FROM SC WHERE Grade IS NULL注意：空值不是一个确定的值，所以不可以用等于或不等于来比较或衡量4.2.10 and 、or 多重条件查询语法：当需要多个查询条件时，可以在WHERE子句中使用逻辑运算符AND和OR来组成多条件查询操作：查询C002和C003课程中考试成绩在8090的学生的学号

31、、课程号和成绩select Sno, Cno, Grade from SC where Cno IN( C002, C003) and Grade BETWEEN 80 AND 90注意：OR的优先级小于AND，要改变运算的顺序可以通过加括号的方式实现SELECT Sno, Cno, Grade FROM SC WHERE (Cno = C001 OR Cno = C002) AND Grade BETWEEN 80 AND 904.2.11 order by 对查询结果排序语法：order by asc| desc , 说明：按进行升序（ASC）或降序（DESC）排序；当用多个列排序时，这些

32、列在该子句中出现的顺序决定了对结果集进行排序的方式操作：查询全体学生详细信息，结果按系名升序排列，同一个系的学生按出生日期降序排列SELECT * FROM Student order by Dept ASC, Birthdate desc4.2.12 使用聚合函数语法：COUNT（*）：统计表中元组的个数。COUNT（DISTINCT ）：统计列值个数SUM（）：计算列值的和值（必须是数值型列）。AVG（）：计算列值的平均值（必须是数值型列）。MAX（）：得到列值的最大值。MIN（）：得到列值的最小值。除COUNT（*）外，其他函数在计算过程中均忽略NULL值。操作1：统计选修了课程（SC）

33、的学生人数。SELECT COUNT(DISTINCT Sno) FROM SC操作2：计算学号为“0811101”的学生的考试总成绩SELECT SUM(Grade) FROM SC WHERE Sno = 0811101注意！：聚合函数不能出现在WHERE子句中。操作：查询学分最高的课程名，如下写法是错误的!SELECT Cname FROM Course WHERE Credit = MAX(Credit)应该改为：declare credit intselect credit=max(credit) from courseselect cname from course where c

34、redit=credit4.2.13 GROUP BY 分组语法： GROUP BY 作用：细化聚合函数的作用对象操作1：统计每门课程的选课人数，列出课程号和选课人数。对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然后再对每一组使用COUNT计算，求出每组的学生人数。SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno操作2：统计每个学生的选课门数和平均成绩。SELECT Sno as 学号, COUNT(*) as 选课门数, AVG(Grade) as 平均成绩 FROM SC GROUP BY Sno注意： 1.GROUP BY子句中的分

35、组依据列必须是表中存在的列名，不能使用AS子句指派的列别名。 2.带有GROUP BY 子句的SELECT语句的查询列表中只能出现分组依据列和统计函数，因为分组后每个组只返回一行结果。操作3：带WHERE子句的分组。统计每个系的女生人数。SELECT Dept, Count(*) 女生人数 FROM Student WHERE Sex = 女 GROUP BY Dept操作4：按多个列分组。统计每个系的男生人数和女生人数，结果按系名的升序排序。SELECT Dept, Sex, Count(*) 人数, FROM Student GROUP BY Dept, Sex ORDER BY Dept

36、4.2.14 HAVING 限制分组结果语法：HAVING用于对分组自身进行限制，它有点象WHERE子句，但它用于组而不是对单个记录。操作1： 查询选课门数超过3门的学生的学号和选课门数。SELECT Sno, Count(*) 选课门数 FROM SC GROUP BY Sno HAVING COUNT(*) 3处理过程：先执行GROUP BY子句对SC表数据按Sno进行分组，然后再用统计函数COUNT分别对每一组进行统计，最后筛选出统计结果满足大于3的组。分组的优先级大于查询，having是分组内操作。操作2：查询选课门数大于等于4门的学生的平均成绩和选课门数SELECT Sno, AVG

37、(Grade) 平均成绩, COUNT(*) 选课门数 FROM SC GROUP BY Sno HAVING COUNT(*) = 44.2.15 小结1.在分组操作之前应用的筛选条件，比在WHERE子句中指定更有效。2.在HAVING子句中指定的筛选条件，应该是那些必须在执行分组操作之后应用的筛选条件。3.将所有应该在分组之前进行的筛选条件放在WHERE子句中而不是HAVING子句中。3.3 多表查询多表连接查询分类三种：1.内连接(INNER JOIN)：分为三种：等值连接、自连接、不等连接 2.外连接(OUTER JOIN)：分为三种：左外连接、右外连接、全外连接 3.交叉连接(CRO

38、SS JOIN) ：没有WHERE子句，它返回连接表中所有数据行的笛卡尔积3.3.1 内连接语法：ANSI方式的连接格式FROM 表1 INNER JOIN 表2 ON 连接条件语法格式：内连接执行过程：首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2， 重复这个过程，直到表1中的全部元组都处理完毕为止。操作1：查询学生的选课情况。学生信息在student表中，选课信息在sc表中。两张表都存放着学生的学学号sno。SELECT *

39、FROM Student INNER JOIN SCON Student.Sno=SC.Sno操作2：去除重复列。如果不指定查询结果的列名（如操作1）则直接将两个表拼接在一起，学号列会重复。为了避免重复，需要制定列名。SELECT Student.Sno, Sname, Sex, Sage, Dept, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno操作3：指定列别名 ，参照前面说过的格式： AS SELECT Sname, Cno, Grade FROM Student S JOIN SC ON S.Sno = SC.Sno W

40、HERE Dept = 计算机系注意：当为表指定了别名时，在查询语句中的其他地方，所有用到表名的地方都要使用别名，而不能再使用原表名。操作4：三张表的连接查询。查询“信息管理系”修了“计算机文化学” 的学生姓名和成绩。SELECT Sname, Grade FROM Student s JOIN SC ON s.Sno = SC. Sno JOIN Course c ON c.Cno = SC.Cno WHERE Dept = 信息管理系 AND Cname = 计算机文化学操作5：综合使用聚合函数、多表连接、分组。 有分组和行选择条件的多表连接查询。统计计算机系学生每门课程的选课人数、平均成

41、绩、最高成绩和最低成绩。 SELECT Cno, COUNT(*) AS Total, AVG(Grade) as AvgGrade, MAX(Grade) as MaxGrade, MIN(Grade) as MinGrade FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Dept = 计算机系 GROUP BY Cno 4.索引4.1 创建索引语法：create unique clustered | nonclustered index 索引名on 表名 | 视图名 ( 列名 asc | desc , .n )注意：1.一个表中只能创建1个聚

42、集索引。（由于系统已自动在主键上创建了聚集索引，所以用户不能再创建，除非先删除已有的索引，重新创建）2.一个表中可以创建若干个非聚集索引。操作：在kc表中，重新创建名为“ix_kcm”的索引，使其成为惟一性的非聚集索引create unique index ix_kcm on kc ( 课程名 desc)with drop_existing -删除已存在的索引，创建新的索引4.2 删索索引语法：drop index 表名 . | 视图名 . 索引名 , n 注意：SQL Server系统自动建立的索引不能用drop index删除，只能用alter table语句中的drop constrai

43、nt子句来解除加在该字段上的主键约束或惟一性约束，这些约束一解除，相关的索引也就被删除了。4.3 查看索引语法：exec sp_helpindex 表名 | 视图名 5.视图5.1 视图介绍5.1.1 视图的含义和作用视图是基于某个查询结果的虚表。是用户查看和修改数据表中数据的一种方式。每个视图都有几个被定义的列和多个数据行。5.1.2 视图与基本表1.视图中的数据列和行来源于其所引用的基表。2.视图所对应的数据并不实际存储在数据库中，而是仍存储在视图所引用的基表中。3.数据库中只存储视图的定义。5.1.3 使用视图的目的与好处1.聚焦特定数据：使用户只能看到和操作与他们有关的数据，提高了数据

44、的安全性。2.简化数据操作：使用户不必写复杂的查询语句就可对数据进行操作。3.定制用户数据：使不同水平的用户能以不同的方式看到不同的数据。4.合并分离数据：视图可以从水平和垂直方向上分割数据，但原数据库的结构保持不变。5.2 创建视图语法：create view 视图 (列名表) with encryption -用于加密视图的定义，用户只能查看不能修改。asselect查询语句 with check option -强制所有通过是同修改的数据，都要满足select语句中指定的条件操作1:创建一个 名为“v1”的视图，用于查询计算机网络专业男生的学号、姓名、出生日期，并将视图的列名分别改为：学

45、生学号、男生姓名、生日。create view v1 (学生学号,男生姓名, 生日) as select 学号, 姓名, 出生日期 from xsqk where 专业名=计算机网络 and 性别=1go使用视图select * from v15.3 修改视图语法：alter view 视图 (列名表) with encryption asselect查询语句 with check option 操作：在“v1”的视图中增加两列：专业名和所在系。alter view v1 (学生学号,男生姓名,生日,专业,系) as select 学号,姓名,出生日期,专业名,所在系 from xsqk wh

46、ere 专业名=计算机网络 and 性别=1）5.4 删除视图语法： drop view 视图名 ,n 5.5 通过视图管理表中的数据5.5.1 使用视图插入数据注意：1.可通过视图向基表中插入数据，但插入的数据实际上存放在基表中，而不是存放在视图中。2.如果视图引用了多个表，使用insert语句插入的列必须属于同一个表。3.若创建视图时定义了“with check option”选项，则使用视图向基表中插入数据时，必须保证插入后的数据满足定义视图的限制条件。操作1：向“V1”视图中添加两条记录。 insert into v1 values(020106,张三,1981-04-22,计算机网络,计算机)insert into v1 values(020107,张四,1981-07-08,信息安全,计算机)5.5.2 使用视图删除数据 注意：1.要删除的数据必须包含在视图的结果集中。2.如果视图引用了多个表时，无法用delete命令删除数据。语法：delete from 视图名 where 条件操作：删除“V