T-SQL修改表数据
INSERT语句
语法：
INSERT
[TOP(expression) [PERCENT]] [INTO] { <object> | rowset_function_limited [ WITH ( <Table_Hint_Limited> [ ...n ] ) ] /*指定表提示*/ | view_name } /*视图名*/ { [(column_list) ] /*指定列名*/ [<OUTPUT Clause> ] {VALUES /*指定列名的取值*/ {DEFAULT | NULL | expression}[1…n]) /*列值的构成形式*/ |derived_table|execute_statement } } /*结果集*/ |DEFAULT VALUES /*所有列均取默认值*/
例子：
1 /*插入单个元组*/ 2 /*向student表中插入一个学生记录（‘200’，‘曾雷’，‘女’，‘1978-2-3’，‘05001’）*/ 3 USE test 4 INSERT INTO student_1 5 VALUES(100,'曾雷','女','1995-2-3',20) 6 7 /*查询student表，查看结果*/ 8 select * from student_1 9 -----------------------------------------------------------------------------------10 11 12 /*向student表中插入一个学生记录（‘201’，‘孙浩’，‘男’，‘1977-8-4’，NULL）*/13 INSERT INTO student_1(sno,sname,ssex,sbirthday,sage) 14 VALUES(200,'孙浩','男','1996-8-4',null)15 16 select * from student_117 delete from student_1 where sno=20018 19 INSERT INTO student_1(sno,sname,ssex,sbirthday) 20 VALUES(200,'孙浩','男','1996-8-4')21 -----------------------------------------------------------------------------------22 23 24 /*插入元组集合*/25 /*将student_1表中的相关数据插入到student表中*/26 select * from student27 select * from student_128 29 insert into student(sno,sname,ssex,sage)30 select sno,sname,ssex,sage 31 from student_1 32 33 34 /*向student表中添加两个新生*/35 INSERT INTO student_1(sno,sname,ssex,sbirthday,sage) 36 VALUES(300,'王明','男','1996-8-4',19),(400,'赵强','男','1996-4-1',19)37 -----------------------------------------------------------------------------------38 39 40 /*向自增列添加数据*/41 create table testidentity(42 id int identity,43 words varchar(10))44 45 insert into testidentity values('a') --标识列不指定46 insert into testidentity values('b') --指定除了标识列外的列47 48 set IDENTITY_INSERT testidentity on 49 insert into testidentity(id,words) values(10,'c') --指定标识列50 51 set IDENTITY_INSERT testidentity off 52 insert into testidentity values('d')53 54 select * from testidentity
UPDATE语句
语法：
UPDATE｛table_name｜view_name｝
SET column_name = {expression | DEFAULT | NULL}[1…n] where where_clause例子：
1 /*将sc表中的成绩小于60的加5。*/ 2 UPDATE sc 3 SET grade=grade+5 4 WHERE grade<70 5 6 /*将张三选修1号课程的成绩置零。*/ 7 UPDATE sc 8 SET grade=0 9 WHERE cno=1 and sno in10 (select sno from student where sname='张三')11 12 13 /*将学号为1的学生的姓名改为张三十，年龄改小2岁。*/14 UPDATE student15 SET sname='张三十',sage=sage-2 --同时更新多列16 WHERE sno=117 18 select * from student19 -----------------------------------------------------------------------------------20 21 /*使用top表达式*/22 UPDATE top(2) student23 SET sage=sage-2 24 25 UPDATE top(50) percent student26 SET sage=sage-2
DELETE语句
语法：
DELETE table_name
WHERE search_condition例子：
1 /*删除student表中学号为200的记录*/ 2 select * from student 3 select * from sc 4 5 DELETE student 6 WHERE sno='200' 7 8 /*删除张三的选修1号课程的选课记录*/ 9 DELETE sc 10 WHERE cno=1 and sno in 11 (select sno from student where sname='张三')12 -----------------------------------------------------------------------------------13 14 /*TRANCATE*/15 /*TRUNCATE TABLE table_name*/16 /*一次删除表中所有数据，即清空表，17 但表的结构及约束保持不变，且该操作不记录日志，无法恢复，使用时必须慬慎。*/18 19 20 /*删除student_1表中的记录*/21 TRUNCATE TABLE student_122 23 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]]
简单查询例子：
1 /*查询列*/ 2 3 /*查询student表中所有记录的sname、ssex和sage列*/ 4 SELECT sname,ssex,sage 5 FROM student 6 7 /*查询有选课记录的课程cno*/ 8 select distinct cno --避免重复项 9 from sc 10 11 /*查询有85分以上成绩的课程cno*/ 12 SELECT DISTINCT cno 13 FROM sc 14 WHERE grade>85 15 16 17 /*查询student表的所有记录*/ 18 SELECT * 19 FROM student 20 21 SELECT sno as '学号',sname as '姓名',ssex as '性别' 22 FROM student 23 24 25 /*返回部分结果top*/ 26 select top(1) * from student 27 select top(1) * from student where ssex='女' 28 select top(1) with ties * from student order by sage 29 select top(50) percent * from student 30 31 32 /*计算列*/ 33 select sno,sname,2015-sage as '出生年月' from student 34 select sno,cno,grade*1.1 from sc 35 ----------------------------------------------------- 36 37 38 /*选择查询 39 40 查询sc表中成绩大于60的所有记录*/ 41 SELECT * 42 FROM sc 43 WHERE grade>60 44 45 46 /*查询sc表中1号课程成绩大于60的所有记录*/ 47 SELECT * 48 FROM sc 49 WHERE cno=2 and grade>60 50 51 52 /*查询score表中成绩在60～80之间的所有记录*/ 53 SELECT * 54 FROM sc 55 WHERE grade between 60 and 80 56 57 58 /*查询sc表中成绩为85、86或88的记录*/ 59 SELECT * 60 FROM sc 61 WHERE grade in(85,86,88) 62 63 64 /*字符串匹配*/ 65 66 /* % 匹配任意字符 67 _ 匹配单个字符 68 [] 匹配括号中的任意一个字符 69 [^]或[!]匹配没有出现在括号中的单个字符 70 escape换码字符 */ 71 72 select * from student 73 74 /*查询student表中姓张的或性别为“女”的学生记录*/ 75 SELECT * 76 FROM student 77 WHERE sname like '张%'or ssex='女' 78 79 /*查询student表中姓李的学生*/ 80 SELECT * 81 FROM student 82 WHERE sname like '李%' 83 84 SELECT * 85 FROM student 86 WHERE sname like '[李]%' 87 88 SELECT * 89 FROM student 90 WHERE sname like '李_' 91 92 SELECT * 93 FROM student 94 WHERE sname like '[^李]%' 95 96 SELECT * 97 FROM student 98 WHERE sname not like '[李]%' 99 100 SELECT *101 FROM student102 WHERE sname like '%[四]%'103 104 105 /*查询sc表中没成绩的记录*/106 SELECT *107 FROM sc108 WHERE grade is null109 110 SELECT *111 FROM sc112 WHERE grade is not null113 114 115 /*查询结果排序*/116 SELECT *117 FROM sc118 order by grade 119 120 SELECT *121 FROM sc122 order by cno,grade desc123 124 125 /*分组查询*/126 /*group by group_by_expression[with rollup|cube]*127 having search_condition128 with rollup 只返回第一个分组条件制定的列的统计行;129 而with cube除返回group by制定的列外,还返回按组统计的行*/130 131 SELECT cno,AVG(grade)132 FROM sc133 group by cno134 135 SELECT cno,AVG(grade)136 FROM sc137 group by cno138 having AVG(grade)>60139 140 SELECT cno,tno,AVG(grade)141 FROM sc142 group by cno,tno143 144 SELECT cno,tno,AVG(grade)145 FROM sc146 group by cno,tno with rollup147 148 select AVG(grade)149 from sc
高级查询例子：
1 /*嵌套查询*/ 2 use test 3 4 /*使用IN或NOT IN*/ 5 select sname 6 from student 7 where sno in 8 (select sno 9 from sc 10 where cno=2) 11 12 select sname 13 from student 14 where sno not in 15 (select sno 16 from sc 17 where cno=2) 18 19 20 /*比较运算符的子查询*/ 21 22 select sno,grade 23 from sc sc1 24 where sc1.cno=1 and 25 sc1.grade=(select sc2.grade 26 from sc sc2 27 where sc2.cno=1 and sc2.sno=1) 28 29 select * from sc 30 31 select sno,grade 32 from sc sc1 33 where sc1.cno=1 and 34 sc1.grade>(select sc2.grade 35 from sc sc2 36 where sc2.cno=1 and sc2.sno=1) 37 38 select sno 39 from sc 40 where cno=1 and 41 grade>all(select grade from sc where sno=1) 42 43 select sno 44 from sc 45 where cno=1 and 46 grade>(select max(grade) from sc where sno=1) 47 48 49 select student.sno,sname,cno,grade 50 from sc as a,student 51 where student.sno=a.sno and --不相关子查询 52 grade>(select avg(grade) 53 from sc b 54 where b.cno=a.cno) 55 56 57 /*exists*/ 58 SELECT sname 59 FROM student 60 WHERE EXISTS 61 (SELECT * 62 FROM sc 63 WHERE student.sno=sc.sno and sc.cno=2) 64 65 SELECT sname 66 FROM student 67 WHERE not EXISTS 68 (SELECT * 69 FROM sc 70 WHERE student.sno=sc.sno and sc.cno=2) 71 72 73 /*多层嵌套查询 74 查询最高分的学生姓名*/ 75 76 select * 77 from student 78 where not exists 79 (select * 80 from course 81 where not exists 82 (select * 83 from sc 84 where sc.sno=student.sno and sc.cno=course.cno)) 85 86 87 select sname 88 from student 89 where sno in 90 (select sno 91 from sc 92 where grade= 93 (select max(grade) 94 from sc)) 95 go 96 97 select * from sc 98 select * from student 99 100 101 /*DELETE、UPDATE和INSERT语句中的子查询*/102 ------------------------------------------------------------103 104 105 /*联接查询*/106 use test107 108 /*查询学生的姓名，选课号及成绩*/109 110 select sname,cno,grade111 from student,sc 112 where student.sno=sc.sno113 114 select sname,cname,grade 115 from student,sc,course --多表连接116 where student.sno=sc.sno and sc.cno=course.cno117 118 select sname,cno,grade119 from student inner join sc on(student.sno=sc.sno) --内连接120 121 122 select student.sname,sc.cno,sc.grade123 from student left join sc on (student.sno=sc.sno) --左向外连接124 125 126 select student.sname,sc.cno,sc.grade127 from student right join sc on (student.sno=sc.sno) --右向外连接128 129 130 select student.sname,sc.cno,sc.grade131 from student full outer join sc on (student.sno=sc.sno) --完全外部连接132 133 134 select student.sno,sc.sno,sc.cno,sc.grade 135 from student cross join sc --交叉连接136 137 select student.sno,sc.sno,sc.cno,sc.grade 138 from student cross join sc --带限定条件的交叉连接139 where student.sno<2140 141 142 select c1.cno,c2.cname143 from course c1,course c2 --自连接144 where c1.cpno=c2.cno145 --------------------------------------------------------------------------146 147 /*无法使用ntext、text或image列上直接连接表，148 但是使用substring函数在ntext、text或image列上间接联接表，如*/149 150 select *151 from student join sc152 on substring(student.mytext,1,2)=substring(sc.mytext,1,2)153 ------------------------------154 155 156 /*使用UNION运算符组合多个结果157 查询所有作者和客户的号码和名称*/158 159 select sno,sname from student where sno=1160 union161 select sno,sname from student where sno=2162 go163 ---------------------------164 165 /*在查询的基础上创建新表166 将查询得到的学生学号、姓名、课程和分数输入到新建的表score1中，167 再显示该新表的记录*/168 169 select student.sno,avg(grade) as 平均成绩170 into avggrade --该表自动生成171 from student inner join sc on (student.sno=sc.sno)172 group by student.sno173 174 select * from avggrade175 176 177 drop table avggrade