T_SQL总结笔记

转载请标明出处 http://blog.csdn.net/ylbf_dev/article/details/46662683

一 、基本语法

1、新建数据库

create database db_studentsuse db_studentscreate table db_students(    id int primary key identity(1,1),    name varchar(20))insert into db_students values('a')update db_students set name='123' where id=1select name as 姓名 from db_students where name like '%3'

2、插入数据insert

use studentinsert into students(SNumber,SName,SAddress,SGrade,SEmail)values ('110119','张红',DEFAULT,2,'[e-mail]')语法：插入数据记录insert [into] table_name [(column1,……)] values (valuse1,……)

1、注意事项
A、每次插入一行数据,不可能只插入半行或者几列数据,因此,插入的数据是否有效将按照整行的完整性的要求来检验。
B、每个数据值的数据类型、精度和小数位数必须与相应的列匹配。
C、不能为标识列指定值,因为它的数字是自动增长的。
D、如果在设计表的时候就指定了某列不允许为空,则必须插入数据。
E、插入的数据项,要求符合检查约束的要求。
F、具有缺省值的列,可以使用DEFAULT(缺省)关键字来代替插入的数值。

3、修改数据记录Update

/*** 将学号为110119的姓名改为韩梅梅*/use studentupdate studentsset SName='韩梅梅'where SNumber=110119/*** 将白玲玲的学号改为100000地址改为XXXX职业技术学院*/use studentupdate studentsset SNumber=100000,SAddress='XXXX职业技术学院'where SName='白玲玲'/*** 将“XXXX”的学生记录修改为“XXXX职业技术学院”*/use studentupdate studentsset SAddress='XXX职业技术学院'where SAddress='XXX'/***将成绩表Score中“成绩在50到60分之间”的成绩提高10分*/use studentupdate Scoreset Score=Score+10where Score>50 and Score<60

语法：
Update关键字
Update table_name
Set column1=expression1[,column2=expression2,……]
[whear search_condition]

4、删除

（1）、Delete（记录日志）

use studentdelete from studentswhere SNumber='180311329'

语法：
Delete from table_name
[whear search_condition]

（2）、清空删除Truncate（不记录日志）

use studenttruncate table student

语法：
Truncate table table_name
注意：不能用于有外键约束引用的表。删除后,表的结构、列、约束、索引不变。

（3）、删除表drop（表和日志都删除）

Drop table table_name

语法
Drop table table_name

二、运算符

算术运算符：+ - * / %
赋值运算符：=
关系（比较）运算符：= > >= < <= <>不等于 !=不等于
逻辑运算符：NOT求反 AND且 OR或 LIKE像 BETWEEN…and…介于……之间 IN

三 、通配符

_ 表示一个字符 示例：A Like ‘C_’
% 任意长度的字符串 示例：B Like ‘CO_%’
[ ] 括号中所有指定范围内的一个字符 示例：C Like ‘9W0[1-2]’
[^] 不在括号中所有指定范围内的一个字符 示例：D Like ‘%[A-D][^1-2]’

四 、查询：

SELECT语句格式：

SELECT [ALL|DISTINCT]列表达式 （top 3 *查询前三列）（使用distinct消除重复）[INTO新列表]   多个表用逗号隔开FROM新名列表[WHERE条件表达式]（指定查询条件）[GROUP BY列名][HAVING条件表达式][ORDER  BY列名[ASC升序|DESC降序]] 排序

use studentselect SNumber as 学号,SEmail as Email,SName as 姓名from studentswhere SGrade=1use studentselect top 3 SNumber as 学号,SEmail as Email,SName as 姓名from studentswhere SGrade=1order by SNumber descuse studentselect top 2 SNumber as 学号,SName as 姓名from studentswhere SName like '张%'/*查询姓张的*/use studentselect *from Scorewhere CourseID=1 and Score>=60use studentselect SNumber,CourseID,Scorefrom Scorewhere Score>=80 and Score<=90use studentgoselect SNumber as 学号,SName 姓名into stu1from studentswhere SGrade=3use studentgoselect AVG(Score) 平均分from Scorewhere CourseID=1use studentgoselect min(Score) 最低分,MAX(Score) 最高分from Scorewhere CourseID between 1 and 7use studentgoselect COUNT(*) as 及格人数from Scorewhere Score>=60出错：use studentgoselect CourseID as 课程号,AVG(Score) as 平均分from Score消息8120，级别16，状态1，第1 行选择列表中的列'Score.CourseID' 无效，因为该列没有包含在聚合函数或GROUP BY 子句中。改正：use studentgoselect CourseID as 课程号,AVG(Score) as 平均分from Scoregroup by CourseIDuse studentgoselect Score.CourseID as 课程号,courseName as 课程,SName as 姓名,AVG(Score) as 平均分from Score,students,Coursegroup by Score.CourseID,SName,coursenameuse studentgoselect score.SNumber as 学号,AVG(Score) as 平均分from Score,students,Coursegroup by score.SNumberHAVING avg(Score)>=65use studentgoselect SGrade as 年级,COUNT(SAddress) as 地址为XXXX人数from studentsgroup by SGrade,SAddresshaving SAddress='XXXX'

查询

SELECT <列名> FROM <表名> [WHERE <查询条件表达式>] [ORDER BY <排序的列名>[ASC(升)或DESC(降)] ] --默认为升序 

1、查询全部的行和列

SELECT * FROM Students 

2、查询部分行

SELECT <列名> FROM <表名> WHERE <查询条件表达式>

3、自定义命名查询结果中的列名
(1)使用AS来命名列

SELECT 原表中列名 AS 查询结果中显示的列名 FROM <表名> WHERE <查询条件表达式> SELECT Stuno AS 学员编号,StuName AS学员姓名,StuAddress AS 学员地址 FROM stuinfo WHERE SAddress like '地址不详' 

(2)合并两列数据,以规定格式输出查询结果

SELECT 原表中列名+ '.' +原表中列名 AS 查询结果中显示的列名 FROM 表名 

(3)使用=来命名列

SELECT 查询结果中显示的列名 = 原表中列名 FROM <表名> WHERE <查询条件表达式> SELECT 查询结果中显示的列名 = 原表中列名+ '.' +原表中列名 FROM 表名 

(4)查询某几列为空的行

SELECT 列名A FROM 表名 WHERE 条件列名B IS NULL 

显示结果：B列为空的A列内容。

(5)显示结果中加入常量列

SELECT 列A = SName,列B = SAddress,'常量列内容'AS 常量列名称 FROM Students SELECT 姓名=SName,地址= SAddress,'XXXX' AS 学校名称 FROM Students 

(6)限制查询结果输出的行数

SELECT TOP 5 列名 FROM 表名 WHERE 条件 

显示结果：符合条件的前五行。

(7)返回百分之多少行

SELECT TOP 20 PERCENT 列名 FROM 表名 WHERE 条件 

显示结果：符合条件的行数的前20%行。

(8)升序排列(默认)ASC

SELECT StudentID As 学员编号,(Score*0.9+5) As 综合成绩 FROM Score WHERE (Score*0.9+5)>60 ORDER BY Score 

(9)降序排列DESC

SELECT Au_Lname +'.' +Au_fName AS EMP From 表A Union SELECT fName +'.'+ LName AS EMP SQL From 表B ORDER BY EMP DESC 

显示结果：混合查找两张表中的列,并按格式输出到新列中,并按新列排序输出。

(10)按多列排序

SELECT StudentID As 学员编号, Score As 成绩 FROM 表名 WHERE Score>60 ORDER BY Score,studentID 

显示结果：先按Score排序，再按studentID排序。

模糊查询

1、LIKE

查询时,字段中的内容并不一定与查询内容完全匹配,只要字段中含有这些内容。 SELECT StuName AS 姓名 FROM Stuinfo WHERE stuname LIKE '徐%' 

显示结果：姓为“徐”的人的名字。

2、IS NULL

把某一字段中内容为空的记录查询出来。

SELECT StuName AS 姓名,StuAddress AS 地址。 FROM Stuinfo WHERE StuAddress IS NULL 

显示结果：把地址栏为空的显示出来。

3、BETWEEN

把某一字段中内容在特定范围内的记录查询出来。 SELECT StuNo, Score FROM Stumarks WHERE Score BETWEEN 60 AND 80 显示结果：把分数80>= Score >=60的显示出来。

4、IN

把某一字段中内容与所列出的查询内容列表匹配的记录查询出来。

SELECT StuName AS 学员姓名,StuAddress As 地址 FROM Stuinfo WHERE StuAddress IN ('北京','广州','上海') 

显示结果：把地址在(‘北京’,’广州’,’上海’)里的显示出来。

聚合函数

1、SUM(求和)

SELECT SUM(Score) FROM Stumarks WHERE 条件 

显示结果：把符合条件的Score求和,然后显示结果。

2、AVG(求平均值)

SELECT AVG(Score) AS 平均成绩 From Score WHERE Score >=60 

显示结果：把Score >=60的成绩求平均值,然后显示结果,显示的列名为“平均成绩”

3、MAX、MIN(求最大、最小值)

SELECT MAX (Score) AS 最高分, MIN (Score) AS 最低分 From Score WHERE Score >=60 

显示结果：把Score>=60中的最高分和最低分显示出来。

4、COUNT(计数)

SELECT COUNT (*) AS 及格人数 From Score WHERE Score>=60 

显示结果：把Score列中,>=60的个数统计出来,然后显示统计数目。

分组查询

1、单列分组查询

SELECT CourseID, AVG(Score) AS 课程平均成绩 FROM Score GROUP BY CourseID 

显示结果：按CourseID组求Score的平均值,然后将CourseID和平均值显示出来。

2、多列分组

SELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS平均成绩 FROM Score GROUP BY StudentID,CourseID 

显示结果：显示所有学员的：“学员编号”(StudentID),“内部测试”(CourseID),“平均成绩”(AVG(Score))。如果同一CourseID组中出现了相同的StudentID,则显示出来的是这一CourseID组中相同的StudentID的平均成绩。

3、HAVING(追加条件)

SELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS平均成绩 FROM Score GROUP BY StudentID,CourseID HAVING COUNT(Score)>1 

显示结果：显示补考学员的：“学员编号”(StudentID),“内部测试”(CourseID),“平均成绩”(AVG(Score))。如果同一CourseID组中的同一StudentID组中记录Score的次数,如果次数>1,则显示出来“学员编号”(StudentID),“内部测试”(CourseID),“平均成绩”(AVG(Score))。

4、条件比较顺序

WHERE子句从数据源中去掉不符合其搜索条件的数据。 GROUP BY子句搜集数据行到各个组中,统计函数为各个组计算统计值。 HAVING子句去掉不符合其组搜索条件的各组数据行。

多表联接查询

1、分类

(1)内联接(INNER JOIN)

(2)外联接

A、左外联结 (LEFT JOIN)
B、右外联结 (RIGHT JOIN)
C、完整外联结 (FULL JOIN)

(3)交叉联接(CROSS JOIN)

2、多表内联结查询

(1)建立联接

SELECT S.SName,C.CourseID,C.Score From Score AS C INNER JOIN Students AS S ON C.StudentID = S.SCode 

显示结果：把Score表和Students表建立内联结,查询C.StudentID = S.SCode时,显示S.SName,C.CourseID,C.Score的内容。

(2)未建立联接

SELECT Students.SName, Score.CourseID, Score.Score FROM Students,Score WHERE Students.SCode = Score.StudentID 

显示结果：同上。

(3)多表联接查询—三表联接

SELECT S.SName AS 姓名, CS.CourseName AS 课程, C.Score AS 成绩 FROM Students AS S INNER JOIN Score AS C ON (S.SCode = C.StudentID) INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)

(4)区别

建立联接的查询速度比没有建立的快得多。

3、多表外联接查询

(1)左外联接

(LEFT JOIN或LEFT OUTER JOIN)

SELECT S.SName,C.CourseID,C.Score From Students AS S LEFT JOIN Score AS C ON C.StudentID = S.SCode 

显示结果：Students为左表(left join)Score为右表。左表中有的,右表中没有的显示空值(NULL)。

(2)右外联接

(RIGHT JOIN或RIGHT OUTER JOIN)

SELECT Titles.Title_id, Titles.Title, Publishers.Pub_name FROM titles RIGHT OUTER JOIN Publishers ON Titles.Pub_id = Publishers.Pub_id 

显示结果：与左外连接相反。

(3)完整外联接

(FULL JOIN或FULL OUTER JOIN)
显示结果：左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列为空值；如果有匹配行,则显示结果包括左右表中的所有列值。

4、多表交叉联接查询

(CROSS JOIN) 交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行一一组合,相当于两个表“相乘”。

转载请标明出处 http://blog.csdn.net/ylbf_dev/article/details/46662683