基本 SQL 查询

1、SQL查询语言

  • select distinct 列名|*|表达式 列别名
  • from 表名|视图名|子查询
  • where 限制行的条件 （>,<,>=,<=,<>,=,is null,between and,in(),like,and,or,not）
  • group by 列名|表达式 （sum,avg,count,max,min）
  • having 限制组的条件 条件中带组函数
  • order by 列名|列别名|列序号 desc

CASE expr WHEN value_1 THEN return_1 WHEN value_2 THEN return_2 …… ELSE return_n END

select name,case sex when 0 then ‘男’ else ‘女’ end sex from stus

CASE WHEN condition_1 THEN return_1 WHEN condition_2 THEN return_2 …… ELSE return_n END

多表查询：

  • 等值连接 select last_name,e.department_id,department_name from employees e,departments d where e.department_id=d.department_id
  • 非等值连接 select last_name,salary,grade_level from employees e,job_grades j where e.salary between j.lowest_sal and j.highest_sal
  • 外连接 select last_name,e.department_id,department_name from employees e left join departments d on(e.department_id=d.department_id)
  • 自连接 select w.last_name,l.last_name from employees w,employees l where w.manager_id=l.employee_id

子查询：

  • 单行子查询 子查询只能返回一行一列一个值，标志符号> < >= <== = <> select * from employees where salary>(select salary from employees where last_name=‘Abel’)
  • 多行子查询 子查询可以返回多行一列多个值，标志符号in any all select * from employees where department_id in (select department_id from employees where job_id=‘ST_CLERK’)

Contents