数据库—查询操作

#列的别名
#as:全称：alias(别名),可以省略
#列的别名可以使用一对引号引起来
SELECT employee_id AS id,last_name AS lname,department_id "部门id",salary*12 "annual_salary "
FROM employees;
#去除重复行
#查询员工表中一共有哪些部门id？
#错误的：没有去重的情况
SELECT department_id
FROM employees;
#正确的：去重了的情况
SELECT DISTINCT department_id
FROM employees;
#错误的：
#Select salary,DISTINCT department_id
#From employees;
#仅仅是没有报错，但是没有实际意义
SELECT DISTINCT department_id,salary FROM employees;

#8.空值参与运算
#1空值 null
#2null不等同于0，'',"null"
SELECT * FROM employees;
#3空值参与运算，结果一定也为null
SELECT employee_id,salary "月工资",salary * (1+commission_pct) *12 "年工资"
FROM employees;、
SELECT employee_id,salary "月工资",salary * (1+IFNULL(commission_pct,0)) *12 "年工资"
FROM employees;

#9.着重号··
SELECT * FROM `ORDER`;
#10查询常数
SELECT '尚硅谷','123',employee_id,last_name 
FROM employees;
#11显示表结构
DESCRIBE employees;#显示了表中字段的详细信息
DESC employees;
DESC departments;
#12过滤数据
#查询90号部门的员工信息
SELECT * 
FROM employees
#过滤条件,声明在From结构的后面
WHERE department_id = 90;

#练习：查询last_name为‘king’的员工的信息
SELECT * 
FROM employees
WHERE last_name = 'king';
SELECT * 
FROM employees;
SELECT employee_id,first_name,last_name,salary "月工资",salary * (1+IFNULL(commission_pct,0))*12 "ANNUAL SALARY"
FROM employees;
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;
DESC departments;
SELECT *
FROM departments;