简单的select查询

下面所有章节主要围绕这个表进行操作
一个是员工表，一个是部门表
PRI代表主键约束
UNI是唯一约束
MUL是混合约束

员工表的字段名称，类型及约束如下
Field Type Null Key Default

employee_id int(6) NO PRI 0
first_name varchar(20) YES
last_name varchar(25) NO
email varchar(25) NO UNI
phone_number varchar(20) YES
hire_date date NO
job_id varchar(10) NO MUL
salary double(8,2) YES
commission_pct double(2,2) YES
manager_id int(6) YES MUL
department_id int(4) YES MUL

部门表的字段名称，类型及约束如下
Field Type Null Key Default

department_id int(4) NO PRI 0
department_name varchar(30) NO
manager_id int(6) YES MUL
location_id int(4) YES MUL

员工表有107个记录

部门表中有27条记录

#1.查询员工12个月的工资总和，并起名为ANNUAL SALARY
#一个理解是只算工资的话
SELECT employee_id,first_name,last_name,salary12 AS “ANNUAL SALARY”
FROM employees;
#另一个理解是工资加奖金
SELECT employee_id,first_name,last_name,salary12*(1+IFNULL(commission_pct,0))
FROM employees;
#2.查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id
FROM employees;
#3.查询工资大于12000的员工的姓名和工资
SELECT first_name,last_name,salary
FROM employees
WHERE salary>12000;
#4.查询员工号为176的员工的姓名和部门号
SELECT first_name,last_name,department_id
FROM employees
WHERE employee_id=176;
#5.显示表departments的结构，并查询其中的全部数据
DESC departments;

uae atguigudb;
SELECT * FROM employees;
SELECT 1;
SELECT 2*3+2;

SELECT 1+1,3*2
FROM DUAL;
SELECT employee_id,last_name,salary
FROM employees

#列的别名
SELECT employee_id emp_id,last_name lname,department_id “部门id”,salary*12 “annual sal”
FROM employees;

#去除重复行
#查询员工表中一共有哪些部门id
#没有去重的情况下
SELECT department_id
FROM employees;

#去重的情况下
SELECT DISTINCT department_id
FROM employees;
#错误的如下
SELECT salary,DISTINCT department_id
FROM employees;
#正确的如下
SELECT DISTINCT salary, department_id
FROM employees;
SELECT * FROM employees;

#ifnull函数
SELECT employee_id,salary"月工资",salary*(1+IFNULL(commission_pct,0))*12 “年工资”,commission_pct
FROM employees;
#9.着重号
SELECT * FROM order;
#10.查询常数
SELECT ‘尚硅谷’,employee_id,last_name
FROM employees;

#11.显示表结构
DESCRIBE employees;
DESC employees;
DESC departments;
#查询90号部门的员工信息
SELECT *
FROM employees
WHERE department_id=90;

#查询特定条件员工的信息，如下：
SELECT *
FROM employees
WHERE last_name=‘King’;

SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE department_id=90

SELECT *
FROM employees
WHERE department_id=80

SELECT * FROM employees;