SQL语句 第4章 单表查询

4.1 简单查询

4.1.1  查询指定列

例4.1_1 教师（姓名，职称，工资，参加工作时间）

 

 

SELECT name, title, wage, hire_date 
FROM teachers;

 

例4.1_3 学生（*）不可以改变列的顺序

 

 

SELECT * FROM students; 

 

 

例4.1_6 学生（专业）带DISTINCT关键字

 

SELECT DISTINCT specialty FROM students;

 

 

4.1.2  改变输出

 

SELECT name AS "姓名", dob AS "生日" 
FROM students; 

 

例4.1_8 学生（姓名，生日）学生生日清单（字符串连接）

 

SELECT name||'生日是：' ||dob AS "学生生日清单"
 FROM students;

 

 

4.2 条件查询

4.2.1 单一条件查询

4.2.2 复合条件查询

例4.2_11 AND

SELECT student_id, name, sex, specialty
  FROM students

 WHERE specialty = '计算机' AND sex = '男';

 

例4.2_12 OR

SELECT student_id, name, sex, specialty
  FROM students 

WHERE specialty = '计算机' OR specialty = '自动化';

 

例4.2_13 NOT

SELECT student_id, name, sex, specialty
  FROM students 

WHERE NOT specialty = '计算机';

 

例4.2_14 not in

SELECT student_id, name, specialty, dob
  FROM students

 WHERE name NOT IN('欧阳春岚','高山');

 

例4.2_15 not between and

SELECT student_id, name, specialty, dob
  FROM students

 WHERE dob NOT BETWEEN '1-1月-1989' AND '1-1月-1990';

 

例4.2_16  NOT LIKE

SELECT student_id, name, specialty, dob
  FROM students 

WHERE name NOT LIKE '张%';

 

例4.2_17 IS NOT NULL

SELECT name, hire_date, title, bonus
  FROM teachers 

WHERE bonus IS NOT NULL;

 

例4.2_18

SELECT student_id, name, sex, specialty FROM students
  WHERE specialty = '计算机' AND sex = '女' 
        OR specialty = '机电工程' AND sex = '男';

 

例4.2_19

SELECT name, hire_date, title, bonus, wage FROM teachers
  WHERE NOT title = '工程师' 
        AND hire_date < '1-1月-2002' AND wage < 3000;

 

 

4.3 记录排序

4.3.1 按单一列排序

4.3.2 按多列排序

例4.3_6 升降

SELECT student_id, name, specialty, dob
  FROM students 

ORDER BY specialty, name DESC;

 

 

4.4 分组查询

4.4.1 列（Aggregate）函数及其应用

MAX,MIN,COUNT,SUM,AVG,STDDEV,VARIANCE

4.4.2 GROUP BY子句

 

例4.4_12 GROUP BY子句，WHERE子句

每个系的教师工资在1000元以上的教师平均工资

SELECT department_id, AVG(wage)
  FROM teachers 

WHERE wage > 1000

 GROUP BY department_id;

 

4.4.3 HAVING子句

 
   
例4.4_16 使用ORDER BY子句改变分组查询输出结果的顺序。

在工资低于3000的教师中检索平均工资大于等于2000的部门的部门号和平均工资，按平均工资排序

SELECT department_id, AVG(wage)

 FROM teachers
  WHERE wage < 3000 

GROUP BY department_id HAVING AVG(wage) >= 2000 

ORDER BY 2;