SQL-基础select查询语句

显示表结构(DESCRIBE/DESC)

DESCRIBE employees;
DESC employees;

 

最基础的SELECT

SELECT 1,2,3;
SELECT  * FROM departments;

 

列的别名(AS)

SELECT employee_id emp_id FROM employees; #省略写法
SELECT employee_id AS emp_id FROM employees; #AS未省略写法
SELECT employee_id "emp_id" FROM employees; #""双引号写法，适用于别名有空格的

 

去重(DISTINCT)　　

SELECT DISTINCT department_id FROM employees; #正确写法
SELECT salary,DISTINCT department_id FROM employees; #错误写法
SELECT DISTINCT salary,department_id FROM employees; #正确写法（同时满足salary,department_id）　

 

空值(null)  null与' ',0,'null'非同个意思

空值参与运算实例

SELECT employee_id,salary "月薪",salary * (1+commission_pct)*12 "年薪" FROM employees;

 

着重号(` `) ；若与列名与表名的命名与关键字一样时，需要使用着重号表示

SELECT * FROM `order`;　

  

 

查询常数

SELECT '测试',123,employee_id,last_name FROM employees;

 

过略数据(WHERE)

WHEREY一定要写在FROM的后面，且必须要挨着（即FROM后面紧跟着WHERE）

SELECT employee_id,first_name,last_name,department_id FROM employees WHERE department_id = 90;

 

 练习：

#1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY
SELECT employee_id,last_name,salary*12 AS "ANNUAL SALARY" FROM employees;
#2.查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id FROM employees;
#3.查询工资大于12000的员工姓名和工资
SELECT last_name,salary FROM employees WHERE salary > 12000;
#4.查询员工号为176的员工的姓名和部门号
SELECT last_name,department_id FROM employees WHERE employee_id=176;
#5.显示表departments的结构，并查询其中的全部数据
DESCRIBE departments;
SELECT * FROM departments;