select from
语法:
SELECT 标识选择哪些列
FROM 标识从哪个表中选择
举例:
SELECT *
FROM departments;
SELECT department_id, location_id
FROM departments;
列别名(AS " ")
- 以在列名和别名之间加入关键字AS,别名使用双引号。
- AS可以省略;双引号在没有空格,特殊字符时可以省略。
举例:
SELECT last_name AS name, commission_pct comm
FROM employees;
去除重复行(distinct)
举例
- select distinct …
- DISTINCT 是对后面所有列名的组合进行去重
SELECT DISTINCT department_id
FROM employees;
SELECT DISTINCT department_id,salary
FROM employees;
空值参与运算
- 所有运算符或列值遇到null值,运算的结果都为null
着重号(``)
- 保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同,SQL语句中使用一对``(着重号)引起来。
SELECT * FROM `order`;
显示表结构(describe/ desc)
- 使用DESCRIBE 或 DESC 命令,表示表结构。
DESCRIBE employees;
DESC employees;
过滤条件(from where)
语法
SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件
举例
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;
例题
- 1.查询员工12个月的工资总和,并起别名为ANNUAL SALARY
select employee_id, last_name, salary * 12 as "ANNUAL SALARY"
from employees;
select employee_id, last_name, salary * 12 * (1 + IFNULL(commission_pct, 0)) as "ANNUAL SALARY"
from employees;
- 2.查询employees表中去除重复的job_id以后的数据
select distinct job_id
from employees;
select last_name, salary
from employees
where salary > 12000;
select last_name, department_id
from employees
where employee_id = 176;
- 5.显示表 departments 的结构,并查询其中的全部数据
desc departments;
select * from departments;