SQLServer2005入门2.pptVIP

SQL Server 2005入门2 数据查询语句Select(1) 目录 1 SELECT语句语法 2 SELECT子句查询 3 条件查询 4 嵌套查询 1 SELECT语句语法 Select语句的语法 SELECT [ ALL | DISTINCT ] 目标列表达式 [， 目标列表达式 ] … [ INTO 新表名 ] FROM 表名或视图名 [, 表名或视图名 ] … [ WHERE 条件表达式 ] [ GROUP BY 列名1 [ HAVING 条件表达式 ] ] [ ORDER BY 列名2 [ ASC | DESC ] ] Select语句 SELECT Sno,Sname FROM Tab_Students ORDER BY Sage DESC SELECT * FROM Tab_Students SELECT Sno,Sname,Ssex FROM Tab_Students WHERE Ssex = ‘男’ or Sage 19 2 SELECT子句查询 SELECT子句查询 查询学生姓名和出生日期，并按照出生日期排序 SELECT Sname, 2009 – Sage FROM Student ORDER BY 2009 – Sage SELECT PI(), SIN(PI()/2), COS(PI()/4), EXP(10), POWER(10,2) ALL和DISTINCT ALL 显示所有的数据 DISTINCT 仅仅显示不重复的数据 SELECT Ssex FROM Students SELECT DISTINCT Ssex FROM Students 3 条件查询 条件查询 3.1 确定性查询 3.2 模糊查询 3.3 带查找范围的查询 条件表达式的运算符 3.1 确定性查询 确定性查询 SELECT * FROM Students WHERE Sage 19 SELECT * FROM Students WHERE NOT Sage = 19 确定性查询 SELECT Sno, Sname FROM Students WHERE Sdepartment = ‘CS’ SELECT Sno, Sname FROM Students WHERE Sdepartment ‘CS’ 确定性查询 SELECT Sname, Sage FROM Students WHERE Ssex = ‘男’ OR Sage 19 SELECT Sname, Sage FROM Students WHERE Ssex = ‘男’ AND Sage 19 3.2 模糊查询 模糊查询 LIKE子句中的通配符 模糊查询 带有通配符的字符串必须用单引号引起来 模糊查询 查询所有姓刘的学生的姓名、学号和性别 SELECT Sno, Sname, Ssex FROM Students WHERE Sname like ‘刘%’ 查询姓名中第二个字为“勇”字的学生姓名和学号 SELECT Sno, Sname, Ssex FROM Students WHERE Sname like ‘_勇%’ 模糊查询 如果要查询的字符串中本身就含有通配符，这时要使用ESCAPE关键字，对通配符进行转义 SELECT Cno, Ccredit FROM Course WHERE Cname like ‘DB\_Design’ ESCAPE ‘\’ 3.3 带查找范围的查询 带查找范围的查询 SELECT Sname, Ssex, Sdepartment FROM Students WHERE Sage BETWEEN 19 and 22 SELECT Sname, Ssex, Sdepartment FROM Students WHERE Sage NOT BETWEEN 19 and 22 4 嵌套查询 嵌套查询 SELECT Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno = ‘2’ ) 嵌套查询 4.1 带IN的嵌套查询 4.2 带比较运算符的嵌套查询 4.3 带ANY或者ALL的嵌套查询 4.4 带EXISTS的嵌套查询 4.1 带IN的嵌套查询 带IN的嵌套查询 查询与“刘星宇”在同一个院系学习的学生信息 SELECT Sno, Sname, Sdep FROM Student WHERE Sdep IN ( SELECT Sdep FROM Student WHERE Sname= ‘刘星宇 ) 带IN的嵌