插入语句
单行插入
注意:
- [INTO]是可选的,也可以省略
- 表名是必需的
- 表的例名是可选的,如果省略,将依次插入所以例
- 多个例名和多个值列表用逗号放隔
语法:
INSERT [INTO] 表名 [(列名列表)] VALUES(值列表)
INSERT INTO text(Name,address,age,email)
VALUES('张明','海口市','17','ZM@Sohu.com')
将旧表的数据添加到新表
注意:
- 新表是不能预先存在的
语法:
SELECT IDENTITY(数据类型,标识种子,标识增长量) AS 列名
INTO 新表
FROM 原始表
SELECT text.Name,text.address,text.age,text.email
INTO AddressList
FROM text
多行插入
注意:
- 数据的个数、顺序、数据类型都一致
INSERT text(Name,address,age,email)
SELECT '张明','海南',19,'zm@qq.com' UNION
SELECT '张飞','北京',12,'zf@qq.com' UNION
SELECT '王明','河南',13,'wm@qq.com' UNION
SELECT '张小','海南',19,'zx@qq.com' UNION
SELECT '白明','海南',15,'bm@qq.com'
更新
注意:
- SET后面可以紧跟随多个’列名=更新值‘,修改多个数据列的值,不限一个,使用逗号分隔
- WHERE子句是可选的,用来限制更新数据的条件。若不限制,则整个表的所有数据行将被更新
- 在更新数据的时候,一般都有条件限制,别忘了书写WHERE条件语句,否则将更新表中所有行的数据,这就可能导致有效数据丢失
语法:
UPDATE 表名 SET 列名 = 更新值 WHERE 更新条件
UPDATE text SET Name = '豆子' WHERE Name = '张明'
例子
将2013年9月13日考试的“走进Java编程世界”课程分数低于60分的学员全部提高5分
UPDATE Result SET StudentResult=StudentResult+5
WHERE ExamDate='2013-9-13' AND SubjectId=7 AND StudentResult<60
修改“走进Java编程世界”课程的学时数为55
UPDATE Subject SET ClassHour=55 WHERE SubjectName='走进Java编程世界'
将学号为S1201302002的学生2013年10月18日的“HTML和CSS网页技术”课程的分数修改为55
UPDATE Result SET StudentResult=55
WHERE ExamDate='2013-10-18' AND SubjectId=8 AND StudentNo='S1201302002'
将电子邮件为空的学生电子邮件统一修改为“未知”
UPDATE Student SET Email='未知@' WHERE Email IS NULL
删除
注意:
DELETE语句删除的是整条记录,不会只删除单个列,所以在DELETE后不能出现列名,例如,以下语句
DELETE SAddress FROM Students
语法:
DELETE [FROM] 表名 [WHERE <删除条件>]
DELETE FROM text WHERE Name = '张飞'
查询
查询使用SELECT语法,最简单的查询语法的格式可以表示如下
SELECT <列名>
FROM <表名>
[WHERE <查询条件表达式>]
[ORDER BY <排序的列名>[ASC 或 DESC]]
其中,WHERE 条件是可选的,若不限制,则查询反回所有行的数据项。ORDER BY 是用来排序的,后续内容将会详细介绍。
示列:查询第一学期的全部学生信息
SELECT * FROM Student
WHERE GradeId = 1
查询所有的数据行和列
把表中所有的行和列都列举出来比较简单,这个时候可以使用 “*” 来表示所有列:
SELECT * FROM Students
查询部分行和列
查询部分列需要列举不同的列名,而查询部分行需要使用WHERE子句进行条件限制,例如:
SELECT SCode,SName,SAddress
FROM Students
WHERE SAddress = '河南新乡'
以上的查询语句,将只查询地址为 ‘’河南新乡‘’ 的学生,并且只显示编号,姓名和地址列。同理,以下语句用来查询地址不是 ‘’河南新乡‘’ 的学生信息。
SELECT SCode,SName,SAddress
FROM Students
WHERE SAddress <> '河南新乡'
示列:查询第二学期的全部学生的姓名和电话
SELECT StudentName,Phone
FROM Student
WHERE GradeId = 2
在查询中使用列的别名
AS子句可以用来改变结果集中列的名称,也可以为组合或者计算出的列指定名称,还有一种情况是让标题列的信息更易懂,例如,把SCode
列名查询后显示为 ‘’学生编号‘’。
在T-SQL中查询命名列名可以使用AS子句,例如:
SELECT SCode AS 学生编号,SName AS 学生姓名,SAeddress AS 学生地址
FROM Students
WHERE SAddress <> '河南新乡'
还有一种情况是使用计算、合并得到新列的命名,例如,假设在某数据库的雇员表 Employees 中存在 FirstName 列和 LastName 列,现在需要将这两列合并成一个叫作 ‘’姓名‘’ 的列,可以使用以下查询语句。
SELECT FirstName + '.' + LastName AS 姓名
重新命名列名还有一种方法,就是采用 ‘’=‘’ 来命名,例如:
SELECT 姓名 = FirstName + '.' + LastName
查询空值
在SQL语句中采用 ''IS NULL'' 或者 ''IS NOT NULL'' 来判断是否为空,因此,如果要查询学生信息表中没有填写Email信息的学生,可以使用以下查询语句。
SELECT SName FROM Students WHERE SEmail IS NULL
在查询中使用常量列
有时候,需要将一些常量的默认信息添加到查询输出中,以方便统计或者计算。例如,查询学生信息的时候,学校名称统一都是''北京新兴桥'',查询语句如下:
SELECT 姓名 = SName,地址 = SAddress,'北京新兴桥' AS 学校名称
FROM Students
查询返回限制的行数
以下查询需要反回限制的行数,例如,在测试的时候,如果数据库有上万条记录,而只要检查前面十行数据是否有效就可以了,没有必要查询输出全部的数据,以提高查询速度,这时候就要用到限制返回行数的查询了。
在 T-SQL 中,限制行数使用 TOP 关键字来约束。例如,要查询返回众多学生记录中前五位女生的姓名和地址信息,查询语句如下。
SELECT TOP 5 SName,SAddress
FROM Students WHERE SSex = 0
还有一种情况是需要从表中按一定的百分比提取记录,这个时候还需要用到 PERCENT 关键字来限制。例如,要提取 20% 的女生数据如下:
SELECT TOP 20 PERCENT SName,SAddress
FROM Students WHERE SSex = 0
如何使用 ORDER BY
如果需要按照一定顺序查询语句选中的行,则需要使用 ORDER BY 子句,并且排序可以是升序(ASC) 或者 降序 (DESC)。如果不指定 ASC 或者 DESC ,结果集按默认ASC升序排序。
上面讲过的SQL语句,都可以在其后面再加上ORDER BY 来继续排序。
例如:按照日期先后、成绩由高到低的次序查询编号为1的科目考试信息:
SELECT SubjectId AS 编号,StudentResult AS成绩
FROM Result
WHERE SubjectId = 1
ORDER BY StudentResult DESC
示例:查询2013年3月22日参加‘’面向对象程序设计‘’考试前的五名学生成绩信息
SELECT TOP 5 * FROM Result
WHERE SubjectId=8 AND ExamDate='2013-3-22'
order by StudentResult DESC
在查询中使用函数
SQL Server提供了一些内部函数,每一个函数都实现了不同的功能,不同类型的函数都可以和SELECT、UPDATE、INSERT语句联合使用。
常用的有四类函数,分别是字符串函数、日期函数、数字函数、系统函数。
字符串函数
字符串函数用于对字符串数据进行处理,并返回一个字符串或数字。下表列出了部分常用的字符串函数
函数名 | 描述 | 举例 |
---|---|---|
CHARINDEX | 用来寻找一个指定的字符串在另一个字符串中的初始位置 | SELECT CHARINDEX ('NAME' , 'My name is Tom',1) 返回:4 |
LEN | 返回传递给它的字符串长度 | SELECT LEN ('SQL Server课程') 返回:13 |
UPPER | 把传递给它的字符串转换为大写 | SELECT UPPER ('sql server 课程') 返回:SQL SERVER课程 |
LTRIM | 清除字符串左边的空格 | SELECT LTRIM (' 张明 ') 返回:张明 (后面的空格保留) |
RTRIM | 清除字符串右边的空格 | SELECT RTRIM (' 张明 ') 返回: 张明 (前面的空格保留) |
RIGHT | 从字符串右边返回指定数目的字符 | SELECT RIGHT ('买买买.奥利奥',3) 返回:奥利奥 |
REPLACE | 替换一个字符串中的字符 | SELECT REPLACE ('澳大利亚.张飞','飞','武') 返回:澳大利亚.王武 |
STUFF | 在一个字符串中,删除指定长度的字符,并在该位置插入一个新的字符串 | SELECT STUFF ('ABCDEFG',2,3,'我的音乐我的世界') 返回:A我的音乐我的世界EFG |
日期函数
日期函数用于操作日期值,我们不能直接对日期运用数学函数。例如,如果执行一个诸如''当前日期+1'' 的语句,SQL Server 无法理解要增加的是一日,一个月还是一年。
日期函数帮助提取日期值中的日,月及年,以便分别操作它们,下表列出了部分常用日期函数。
函数名 | 描述 | 举例 |
---|---|---|
GETDATE | 取得当前系统日期 | SELECT GETDATE( ) 返回:今天的日期 例如:2009-12-25 12:00:00.000 |
DATEADD | 将指定的数值添加到指定的日期部分后的日期 | SELECT DATEADD (mm,4,'01/01/2009') 返回:以当前的日期格式返回 05/01/2019 |
DATEDIFF | 两个日期之间的指定日期部分的间隔 | SELECT DATEDIFF (mm,'01/01/2009','05/01/2009') 返回:4 |
DATENAME | 日期中指定日期部分的字符串形式 | SELECT DATENAME (dw,'01/01/2000') 返回:Saturday或星期六 |
DATEPART | 日期中指定日期部分的整数形式 | SELECT DATEPART (day,'01/15/2000')返回:15 |
下列表列出了SQL Server可识别的日期部分参数以其缩写
日期部分参数 | 缩写 | 日期部分参数 | 缩写 |
---|---|---|---|
year | yy,yyyy | weekday | dw,w |
quarter | qq.q | hour | hh |
month | mm,m | minute | mi,n |
dayofyear | dy,y | second | ss,s |
day | dd,d | millisecond | ms |
week | wk,ww |
数字函数
数字函数用于对数值型进行处理,并返回处理结果,下表列出了部分常用的数字函数。
函数名 | 描述 | 举例 |
---|---|---|
RAND | 返回从0到1之间随机float值 | SELECT RAND( ) 返回:0.79288062146374 |
ABS | 取数值表达式的绝对值 | SELECT ABS (-43) 返回:43 |
CEILING | 向上取值,取大于或等于指定的数值、表达式的最小整数 | SELECT CEILING (43.5) 返回:44 |
FLOOR | 向下取值,取小于或等于指定表达式的最大整数 | SELECT FLOOR (43.5) 返回:43 |
POWER | 取数值表达式的幂值 | SELECT POUND (5,2) 返回:25 |
ROUND | 将数值表达式四舍五入为指定精度 | SELECT ROUND (43.543,1) 返回:43.500 |
SIGN | 对于正数返回+1,对于负数返回-1,对于0则返回0 | SELECT SIGN (-43) 返回:-1 |
SQRT | 取浮点表达式的平方根 | SELECT SQRT (9) 返回:3 |
系统函数
系统函数用来获取有关SQL Server中对象和设置的系统信息,下例表列出部分常用的系统函数。
函数名 | 描述 | 举例子 |
---|---|---|
CONVERT | 用来转换数据类型 | SELECT CONVERT (VARCHAR (5), 12345) 返回:字符串12345 |
CURRENT_USER | 返回当前用户的名称 | SELECT CURRENT_USER 返回:你登入的用户名 |
DATALENGTH | 返回指定表达式的字节数 | SELECT DATALENGTH('中国A联盟') |
HOST_NAME | 返回当前用户所登入的计算机名称 | SELECT HOST_NAME( ) 返回:你所登入的计算机名称 |
SYSTEM_USER | 返回你当前所登入的用户名称 | SELECT SYSTEM_USER 返回:你当前所登入的用户名 |
USER_NAME | 从给定的用户ID返回用户名 | SELECT USER_NAME(1) 返回:从任意数据库中返回"dbo" |
上面的所有函数都可以在T-SQL中混合使用,得到符合特殊要求的查询输出。除了以上介绍的四类函数,SQL Server还提供了很多其他的函数,如配置函数、文本图象函数等。