SQL-基础select查询语句

显示表结构(DESCRIBE/DESC)

?

1 2	DESCRIBE employees; DESC employees;

 

最基础的SELECT

?

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

 

列的别名(AS)

?

1 2 3

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

 

去重(DISTINCT)　　

?

1 2 3

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'非同个意思

空值参与运算实例

?

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

 

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

?

1	SELECT * FROM `order`;

  

 

查询常数

?

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

 

过略数据(WHERE)

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

?

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

 

 练习：

?

1 2 3 4 5 6 7 8 9 10 11

#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;