四：SQL基本语句

基本的SELECT语句

• 过滤和排序数据

• 分组函数

• 分组查询

• 多表查询

• 分页查询

（1）SELECT

 SELECT *|{[DISTINCT] column|expression [alias],...} FROM table

 示例：select * from student where name = ‘lin min’

选择所有列：  SELECT * FROM departments;

选择特定的列：SELECT department_id, location_id FROM departments;

使用别名：SELECT last_name AS name, commission_pct comm FROM employees;

显示表结构：desc table_name

过滤和排序数据:  SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)];

Select * from employees where 操作符 = ‘’ //

操作符： =，>，>=，<，<=，<>，BETWEEN...AND...，IN(set)

，LIKE，IS NULL，AND，OR，NOT

ORDER BY 子句：使用 ORDER BY 子句排序

– ASC（ascend）:  升序

– DESC（ descend ）:  降序

• ORDER BY  子句在SELECT 语句的 结尾

降序排序：S

SELECT last_name, job_id, department_id, hire_date

FROM employees  ORDER BY hire_date DESC ;

（2）组函数

组函数类型：

• AVG()

• COUNT()

• MAX()

• MIN()

• SUM()

（3）分组

GROUP BY  子句语法

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;

SELECT job_id,MAX(salary) FROM employees  GROUP BY job_id

非法使用组函数：不能在 WHERE  子句中使用组函数。

（4）HAVING  子句

SELECT department_id, MAX(salary)  FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;

（5） 多表查询

select name,boyName from beauty,boys; 两个表之间可以用,隔开

SELECT * FROM departments,employees 本质就是求两个表的交集

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 = table2.column2;

Join连接：

内连接 [inner] join on

– 外连接

左外连接 left [outer] join on

右外连接 right [outer] join on