T-SQL修改表数据
INSERT语句
语法：
INSERT
[TOP(expression) [PERCENT]]
[INTO]
{
例子：
/*插入单个元组*/
/*向student表中插入一个学生记录（‘200’，‘曾雷’，‘女’，‘1978-2-3’，‘05001’）*/
USE test
INSERT INTO student_1
VALUES(100,'曾雷','女','1995-2-3',20)
/*查询student表，查看结果*/
select * from student_1
-----------------------------------------------------------------------------------
/*向student表中插入一个学生记录（‘201’，‘孙浩’，‘男’，‘1977-8-4’，NULL）*/
INSERT INTO student_1(sno,sname,ssex,sbirthday,sage)
VALUES(200,'孙浩','男','1996-8-4',null)
select * from student_1
delete from student_1 where sno=200
INSERT INTO student_1(sno,sname,ssex,sbirthday)
VALUES(200,'孙浩','男','1996-8-4')
-----------------------------------------------------------------------------------
/*插入元组集合*/
/*将student_1表中的相关数据插入到student表中*/
select * from student
select * from student_1
insert into student(sno,sname,ssex,sage)
select sno,sname,ssex,sage
from student_1
/*向student表中添加两个新生*/
INSERT INTO student_1(sno,sname,ssex,sbirthday,sage)
VALUES(300,'王明','男','1996-8-4',19),(400,'赵强','男','1996-4-1',19)
-----------------------------------------------------------------------------------
/*向自增列添加数据*/
create table testidentity(
id int identity,
words varchar(10))
insert into testidentity values('a') --标识列不指定
insert into testidentity values('b') --指定除了标识列外的列
set IDENTITY_INSERT testidentity on
insert into testidentity(id,words) values(10,'c') --指定标识列
set IDENTITY_INSERT testidentity off
insert into testidentity values('d')
select * from testidentity
UPDATE语句
语法：
UPDATE｛table_name｜view_name｝
SET column_name = {expression | DEFAULT | NULL}[1…n]
where where_clause
例子：
/*将sc表中的成绩小于60的加5。*/
UPDATE sc
SET grade=grade+5
WHERE grade<70
/*将张三选修1号课程的成绩置零。*/
UPDATE sc
SET grade=0
WHERE cno=1 and sno in
(select sno from student where sname='张三')
/*将学号为1的学生的姓名改为张三十，年龄改小2岁。*/
UPDATE student
SET sname='张三十',sage=sage-2 --同时更新多列
WHERE sno=1
select * from student
-----------------------------------------------------------------------------------
/*使用top表达式*/
UPDATE top(2) student
SET sage=sage-2
UPDATE top(50) percent student
SET sage=sage-2
DELETE语句
语法：
DELETE table_name
WHERE search_condition
例子：
/*删除student表中学号为200的记录*/
select * from student
select * from sc
DELETE student
WHERE sno='200'
/*删除张三的选修1号课程的选课记录*/
DELETE sc
WHERE cno=1 and sno in
(select sno from student where sname='张三')
-----------------------------------------------------------------------------------
/*TRANCATE*/
/*TRUNCATE TABLE table_name*/
/*一次删除表中所有数据，即清空表，
但表的结构及约束保持不变，且该操作不记录日志，无法恢复，使用时必须慬慎。*/
/*删除student_1表中的记录*/
TRUNCATE TABLE student_1
select * from student_1
T-SQL查询数据
SELECT 语句语法：
SELECT select_list
[INTO new_table]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC]]
简单查询例子：
/*查询列*/
/*查询student表中所有记录的sname、ssex和sage列*/
SELECT sname,ssex,sage
FROM student
/*查询有选课记录的课程cno*/
select distinct cno --避免重复项
from sc
/*查询有85分以上成绩的课程cno*/
SELECT DISTINCT cno
FROM sc
WHERE grade>85
/*查询student表的所有记录*/
SELECT *
FROM student
SELECT sno as '学号',sname as '姓名',ssex as '性别'
FROM student
/*返回部分结果top*/
select top(1) * from student
select top(1) * from student where ssex='女'
select top(1) with ties * from student order by sage
select top(50) percent * from student
/*计算列*/
select sno,sname,2015-sage as '出生年月' from student
select sno,cno,grade*1.1 from sc
-----------------------------------------------------
/*选择查询
查询sc表中成绩大于60的所有记录*/
SELECT *
FROM sc
WHERE grade>60
/*查询sc表中1号课程成绩大于60的所有记录*/
SELECT *
FROM sc
WHERE cno=2 and grade>60
/*查询score表中成绩在60～80之间的所有记录*/
SELECT *
FROM sc
WHERE grade between 60 and 80
/*查询sc表中成绩为85、86或88的记录*/
SELECT *
FROM sc
WHERE grade in(85,86,88)
/*字符串匹配*/
/* % 匹配任意字符
_ 匹配单个字符
[] 匹配括号中的任意一个字符
[^]或[!]匹配没有出现在括号中的单个字符
escape换码字符 */
select * from student
/*查询student表中姓张的或性别为“女”的学生记录*/
SELECT *
FROM student
WHERE sname like '张%'or ssex='女'
/*查询student表中姓李的学生*/
SELECT *
FROM student
WHERE sname like '李%'
SELECT *
FROM student
WHERE sname like '[李]%'
SELECT *
FROM student
WHERE sname like '李_'
SELECT *
FROM student
WHERE sname like '[^李]%'
SELECT *
FROM student
WHERE sname not like '[李]%'
SELECT *
FROM student
WHERE sname like '%[四]%'
/*查询sc表中没成绩的记录*/
SELECT *
FROM sc
WHERE grade is null
SELECT *
FROM sc
WHERE grade is not null
/*查询结果排序*/
SELECT *
FROM sc
order by grade
SELECT *
FROM sc
order by cno,grade desc
/*分组查询*/
/*group by group_by_expression[with rollup|cube]*
having search_condition
with rollup 只返回第一个分组条件制定的列的统计行;
而with cube除返回group by制定的列外,还返回按组统计的行*/
SELECT cno,AVG(grade)
FROM sc
group by cno
SELECT cno,AVG(grade)
FROM sc
group by cno
having AVG(grade)>60
SELECT cno,tno,AVG(grade)
FROM sc
group by cno,tno
SELECT cno,tno,AVG(grade)
FROM sc
group by cno,tno with rollup
select AVG(grade)
from sc
高级查询例子：
/*嵌套查询*/
use test
/*使用IN或NOT IN*/
select sname
from student
where sno in
(select sno
from sc
where cno=2)
select sname
from student
where sno not in
(select sno
from sc
where cno=2)
/*比较运算符的子查询*/
select sno,grade
from sc sc1
where sc1.cno=1 and
sc1.grade=(select sc2.grade
from sc sc2
where sc2.cno=1 and sc2.sno=1)
select * from sc
select sno,grade
from sc sc1
where sc1.cno=1 and
sc1.grade>(select sc2.grade
from sc sc2
where sc2.cno=1 and sc2.sno=1)
select sno
from sc
where cno=1 and
grade>all(select grade from sc where sno=1)
select sno
from sc
where cno=1 and
grade>(select max(grade) from sc where sno=1)
select student.sno,sname,cno,grade
from sc as a,student
where student.sno=a.sno and --不相关子查询
grade>(select avg(grade)
from sc b
where b.cno=a.cno)
/*exists*/
SELECT sname
FROM student
WHERE EXISTS
(SELECT *
FROM sc
WHERE student.sno=sc.sno and sc.cno=2)
SELECT sname
FROM student
WHERE not EXISTS
(SELECT *
FROM sc
WHERE student.sno=sc.sno and sc.cno=2)
/*多层嵌套查询
查询最高分的学生姓名*/
select *
from student
where not exists
(select *
from course
where not exists
(select *
from sc
where sc.sno=student.sno and sc.cno=course.cno))
select sname
from student
where sno in
(select sno
from sc
where grade=
(select max(grade)
from sc))
go
select * from sc
select * from student
/*DELETE、UPDATE和INSERT语句中的子查询*/
------------------------------------------------------------
/*联接查询*/
use test
/*查询学生的姓名，选课号及成绩*/
select sname,cno,grade
from student,sc
where student.sno=sc.sno
select sname,cname,grade
from student,sc,course --多表连接
where student.sno=sc.sno and sc.cno=course.cno
select sname,cno,grade
from student inner join sc on(student.sno=sc.sno) --内连接
select student.sname,sc.cno,sc.grade
from student left join sc on (student.sno=sc.sno) --左向外连接
select student.sname,sc.cno,sc.grade
from student right join sc on (student.sno=sc.sno) --右向外连接
select student.sname,sc.cno,sc.grade
from student full outer join sc on (student.sno=sc.sno) --完全外部连接
select student.sno,sc.sno,sc.cno,sc.grade
from student cross join sc --交叉连接
select student.sno,sc.sno,sc.cno,sc.grade
from student cross join sc --带限定条件的交叉连接
where student.sno<2
select c1.cno,c2.cname
from course c1,course c2 --自连接
where c1.cpno=c2.cno
--------------------------------------------------------------------------
/*无法使用ntext、text或image列上直接连接表，
但是使用substring函数在ntext、text或image列上间接联接表，如*/
select *
from student join sc
on substring(student.mytext,1,2)=substring(sc.mytext,1,2)
------------------------------
/*使用UNION运算符组合多个结果
查询所有作者和客户的号码和名称*/
select sno,sname from student where sno=1
union
select sno,sname from student where sno=2
go
---------------------------
/*在查询的基础上创建新表
将查询得到的学生学号、姓名、课程和分数输入到新建的表score1中，
再显示该新表的记录*/
select student.sno,avg(grade) as 平均成绩
into avggrade --该表自动生成
from student inner join sc on (student.sno=sc.sno)
group by student.sno
select * from avggrade
drop table avggrade
