sql查询语句总结.docVIP

数据查询 查询查询格式： SELECT */列名,.../表达式 from 表名/视图 where 条件 order by 列名1 asc,列名2 desc group by 列名1，列名2 HAVING 过滤条件 查询指定列 SELECT Sno,Sname FROM Student 查询全部列 SELECT * FROM Student 更改查询结果中的列标题 old_name as new_name 或 new_name= old_name 查询学生表中的姓名和出生年份,并将系别小写显示 SELECT Sname,’Year of Birth:’,2004-Sage, LOWER(Sdept) FROM Student 消除取值重复的行 SELECT DISTINCT Sno FROM SC 限制结果集中返回的行数 select top 5 * from xs select top 10 percent from xs 查询满足条件的元组 常用的查询条件 SELECT Sname FROM Student WHERE Sdept=‘CS’ SELECT Sname,Sage FROM Student WHERE Sage20 SELECT Sname,Sage FROM Student WHERE NOT Sage=20 SELECT DISTINCT Sno FROM SC WHERE Grade60 SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23 SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23 判断表达式的值是否在子查询的结果中 :IN ,NOT IN SELECT Sname,sgender FROM Student WHERE Sdept IN (‘IS’,’MA’,’CS’) SELECT Sname,sgender FROM Student WHERE Sdept NOT IN (‘IS’,’MA’,’CS’) 谓词LIKE的一般语法格式为 [NOT] LIKE ‘匹配串’ [‘ESCAPE’ 换码字符] 查找姓刘的学生的姓名,年龄,性别 SELECT Sname,Sno,sgender FROM Student WHERE Sname LIKE ‘刘%’ 匹配规则 “%” 匹配零个或多个字符 “_” 匹配任意单个字符 Escape 定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待 如escape “\”，定义 \ 作为转义字符，则可用\%去匹配%，用\＿去匹配＿ 查询课程名称以”db_”开头的所有课程情况 Select * from course where cname like ‘db\_%’ escape ‘\’ 查询课程名称以”db_”开头，且倒数第3 个字符为I的课程和详细情况 Select * From course Where cname like ‘db\_%I__’escape ‘\’ 查询 学号是98001的学生信息(注意：这里的学号是字符型) SELECT * FROM Student WHERE Sno LIKE ‘98001’ SELECT * FROM Student WHERE Sno=‘98001’ 查询“欧阳”姓的学生信息 SELECT Sname FROM Student WHERE Sname like ‘欧阳__’ 查缺少成绩的学生的学号和相应的课程号 SELECT Sno,Cno FROM SC WHERE Grade IS NULL 查所有有成绩的记录的学生学号和课程号 SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL 查CS系年龄在20岁以下的学生姓名 SELECT Sname FROM Student WHERE Sdept=‘CS’ AND Sage20 注意运算符优先级 SELECT sname, sage, sdept FROM student WHERE sage=18 OR sage=19 AND sdept=‘cs’ 排序，分组，多表查询，嵌套查询，集合查询 对查询结果排序 查询学生的学号和成绩，并按成绩的降序排列 SELECT Sno,Grade FROM SC WHERE Cno=‘3’ ORDER BY Grade DESC 查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列 SELECT * FROM Student ORDER BY Sdept,Sage DESC 使