T-SQL 查询、修改数据表
来源:互联网 发布:小马网络 编辑:程序博客网 时间:2024/05/16 14:40
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 /*所有列均取默认值*/
例子:
/*插入单个元组*//*向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_1delete from student_1 where sno=200INSERT INTO student_1(sno,sname,ssex,sbirthday) VALUES(200,'孙浩','男','1996-8-4')-----------------------------------------------------------------------------------/*插入元组集合*//*将student_1表中的相关数据插入到student表中*/select * from studentselect * from student_1insert 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 scSET grade=grade+5WHERE grade<70/*将张三选修1号课程的成绩置零。*/UPDATE scSET grade=0WHERE cno=1 and sno in (select sno from student where sname='张三') /*将学号为1的学生的姓名改为张三十,年龄改小2岁。*/UPDATE studentSET sname='张三十',sage=sage-2 --同时更新多列WHERE sno=1select * from student-----------------------------------------------------------------------------------/*使用top表达式*/UPDATE top(2) studentSET sage=sage-2 UPDATE top(50) percent studentSET sage=sage-2
DELETE语句
语法:
DELETE table_name
WHERE search_condition
例子:
/*删除student表中学号为200的记录*/select * from studentselect * from scDELETE 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_1select * 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,sageFROM student/*查询有选课记录的课程cno*/select distinct cno --避免重复项from sc/*查询有85分以上成绩的课程cno*/SELECT DISTINCT cnoFROM sc WHERE grade>85/*查询student表的所有记录*/SELECT *FROM studentSELECT sno as '学号',sname as '姓名',ssex as '性别' FROM student/*返回部分结果top*/select top(1) * from studentselect top(1) * from student where ssex='女'select top(1) with ties * from student order by sageselect top(50) percent * from student/*计算列*/select sno,sname,2015-sage as '出生年月' from studentselect sno,cno,grade*1.1 from sc-----------------------------------------------------/*选择查询查询sc表中成绩大于60的所有记录*/SELECT *FROM scWHERE grade>60/*查询sc表中1号课程成绩大于60的所有记录*/SELECT *FROM scWHERE cno=2 and grade>60/*查询score表中成绩在60~80之间的所有记录*/SELECT *FROM scWHERE grade between 60 and 80/*查询sc表中成绩为85、86或88的记录*/SELECT *FROM scWHERE grade in(85,86,88)/*字符串匹配*//* % 匹配任意字符 _ 匹配单个字符 [] 匹配括号中的任意一个字符 [^]或[!]匹配没有出现在括号中的单个字符 escape换码字符 */select * from student/*查询student表中姓张的或性别为“女”的学生记录*/SELECT *FROM studentWHERE sname like '张%'or ssex='女'/*查询student表中姓李的学生*/SELECT *FROM studentWHERE sname like '李%'SELECT *FROM studentWHERE sname like '[李]%'SELECT *FROM studentWHERE sname like '李_'SELECT *FROM studentWHERE sname like '[^李]%'SELECT *FROM studentWHERE sname not like '[李]%'SELECT *FROM studentWHERE sname like '%[四]%'/*查询sc表中没成绩的记录*/SELECT *FROM scWHERE grade is nullSELECT *FROM scWHERE grade is not null/*查询结果排序*/SELECT *FROM scorder by grade SELECT *FROM scorder 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 scgroup by cnoSELECT cno,AVG(grade)FROM scgroup by cnohaving AVG(grade)>60SELECT cno,tno,AVG(grade)FROM scgroup by cno,tnoSELECT cno,tno,AVG(grade)FROM scgroup by cno,tno with rollupselect AVG(grade)from sc
高级查询例子:
/*嵌套查询*/use test/*使用IN或NOT IN*/select snamefrom studentwhere sno in (select sno from sc where cno=2)select snamefrom studentwhere sno not in (select sno from sc where cno=2) /*比较运算符的子查询*/select sno,gradefrom sc sc1where sc1.cno=1 and sc1.grade=(select sc2.grade from sc sc2 where sc2.cno=1 and sc2.sno=1) select * from scselect sno,gradefrom sc sc1where sc1.cno=1 and sc1.grade>(select sc2.grade from sc sc2 where sc2.cno=1 and sc2.sno=1) select snofrom sc where cno=1 and grade>all(select grade from sc where sno=1) select snofrom sc where cno=1 and grade>(select max(grade) from sc where sno=1) select student.sno,sname,cno,gradefrom sc as a,studentwhere student.sno=a.sno and --不相关子查询 grade>(select avg(grade) from sc b where b.cno=a.cno) /*exists*/SELECT snameFROM student WHERE EXISTS (SELECT * FROM sc WHERE student.sno=sc.sno and sc.cno=2) SELECT snameFROM student WHERE not EXISTS (SELECT * FROM sc WHERE student.sno=sc.sno and sc.cno=2)/*多层嵌套查询查询最高分的学生姓名*/select *from studentwhere not exists (select * from course where not exists (select * from sc where sc.sno=student.sno and sc.cno=course.cno))select snamefrom studentwhere sno in (select sno from sc where grade= (select max(grade) from sc))goselect * from scselect * from student/*DELETE、UPDATE和INSERT语句中的子查询*/------------------------------------------------------------/*联接查询*/use test/*查询学生的姓名,选课号及成绩*/select sname,cno,gradefrom student,sc where student.sno=sc.snoselect sname,cname,grade from student,sc,course --多表连接where student.sno=sc.sno and sc.cno=course.cnoselect sname,cno,gradefrom student inner join sc on(student.sno=sc.sno) --内连接select student.sname,sc.cno,sc.gradefrom student left join sc on (student.sno=sc.sno) --左向外连接select student.sname,sc.cno,sc.gradefrom student right join sc on (student.sno=sc.sno) --右向外连接select student.sname,sc.cno,sc.gradefrom 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<2select c1.cno,c2.cnamefrom course c1,course c2 --自连接where c1.cpno=c2.cno--------------------------------------------------------------------------/*无法使用ntext、text或image列上直接连接表,但是使用substring函数在ntext、text或image列上间接联接表,如*/select *from student join scon substring(student.mytext,1,2)=substring(sc.mytext,1,2)------------------------------/*使用UNION运算符组合多个结果查询所有作者和客户的号码和名称*/select sno,sname from student where sno=1unionselect sno,sname from student where sno=2go--------------------------- /*在查询的基础上创建新表将查询得到的学生学号、姓名、课程和分数输入到新建的表score1中,再显示该新表的记录*/select student.sno,avg(grade) as 平均成绩into avggrade --该表自动生成from student inner join sc on (student.sno=sc.sno)group by student.snoselect * from avggradedrop table avggrade
博客园博客:欠扁的小篮子
- T-SQL 查询、修改数据表
- T-SQL入門攻略之13-修改数据表
- T-SQL入門攻略之13-修改数据表
- T-SQL入門攻略之13-修改数据表
- T-SQL入門攻略之13-修改数据表
- T-SQL查询access的数据表名的列表
- T-SQL查询access的数据表名的列表
- My SQL 修改数据表
- 利用T-SQL创建数据表
- 使用一个T-SQL语句批量查询数据表占用空间及其行数
- sql 查询数据表的结构
- SQL查询数据表及字段
- Oracle查询数据表结构SQL
- T-SQL分割数据表中的字段
- T-Sql表查询
- T-SQL SELECT 查询
- T-SQL语句查询
- T-SQL查询基础
- vmware 网络连接方式的说明
- LeetCode91——Decode Ways
- Java日志管理最佳实践
- JAVA并发原理(下)
- SQLite一条SQL语句插入多条记录,批量插入
- T-SQL 查询、修改数据表
- 最长递增子序列(非连续)
- Android的日期选择器
- MVC中将上一次导出的EXCEL文件导入验证并保存到数据库中
- C++程序员如何向一个java工程师解释extern "C"的作用
- LCS(Longest Common Subsequence)算法
- eclipse中安装genymotion教程
- Linux系统运维——vi的使用技巧——3
- 1087. All Roads Lead to Rome (30)