select 1+1,3*2
from dual; #dual 伪表
#1、选择全部列
SELECT *
FROM departments;
#2、选择特定的列:
SELECT department_id, location_id
FROM departments;
#3、列的别名
#AS:alias(别名),可以省略
#列的别名可以用一对双引号""引起来,不要使用''。
select employee_id emp_id, last_name AS lname, department_id "dept_id", salary*12 AS "annual sal"
from employees;
#4、去除重复行
#查询员工表中一共有哪些部门id
SELECT DISTINCT department_id
FROM employees;
#5、空值参与运算
#1.空值:null
#2.null不等同于0,'','null'
select *
FROM employees;
#3.空值参与运算,则结果也一定为空。
SELECT employee_id, salary "月工资", salary*(1+commission_pct)*12 "年工资", commission_pct
from employees;
#实际问题的解决方案:引入IFNULL
SELECT employee_id, salary "月工资", salary*(1+ifnull(commission_pct,0))*12 "年工资", commission_pct
from employees;
#6、着重号`````表名与关键字等重名时使用
select *
FROM `order`;
#7、查询常数
select '尚硅谷', employee_id, last_name
FROM employees;
#8、显示表结构 显示表中字段的详细信息
DESCRIBE employees;
DESC employees;
#9、过滤数据
#练习1:查询90号部门员工信息
select *
from employees
#过滤条件,声明在FROM结构的后面
where department_id = 90;
#练习2:查询last name为‘King’的员工信息
select *
from employees
#过滤条件
where last_name = 'King'; #严格来说King区分大小写,但是MySQL不严谨