SQL基础学习笔记（一）

感觉SQL，学的云里雾里的，整理一下笔记吧。

SQL语言分为三种:

DCL:  Data Control Language 数据控制语言

select * (表示查询表中所有的列 ) from employees;
select employee_id , last_name,email from employees;(查询指定）

FROM     标识从哪个表中选择。

注意:
SQL 语言大小写不敏感。 
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写
使用缩进提高语句的可读性。

多表查询，有n个表，就要注意有n-1个链接关系(注意笛卡尔集)

不注意笛卡尔集情况，假设员工表有107个员工数据，部门表有27个部门ID数据，员工表通过department_id和部门表链接，
不写链接关系，

select e.department_id,d.department_name
from employees e,departments d;

107*27 = 2889,多对多关系，致使本来是两个表共享的同一列，成为两个列

解决笛卡尔集问题的两种方式：

方式一：

select e.employee_id , d.department_id , d.department_name
from employees e , departments d
where e.department_id = d.department_id

select e.employee_id , d.department_id , d.department_name
from employees e join departments d
on e.department_id = d.department_id

加多少表就join ... on

select employee_id , d.department_id , department_name ,city 
from employees e join departments d 
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id

<span style="font-family:KaiTi_GB2312;font-size:18px;"><strong>select employee_id , department_id , department_name
from employees join departments
using(department_id)</strong></span>

还有一种

select employee_id , department_id , department_name
from employees natural join departments

select employee_id , d.department_id , department_name
from employees e , departments d
where e.department_id = d.department_id(+)
--where e.department_id(+) = d.department_id

select employee_id , d.department_id , department_name
from employees e left outer join departments d 
on e.department_id = d.department_id

select employee_id , d.department_id , department_name
from employees e right outer join departments d 
on e.department_id = d.department_id

select employee_id , d.department_id , department_name
from employees e full outer join departments d 
on e.department_id = d.department_id

select emp.last_name, mana.last_name , mana.salary,mana.email
from employees emp ,employees mana
where emp.manager_id = mana.employee_id and lower(emp.last_name) = 'chen'

select avg(salary) "averge"
from employees


group by department_id
order by department_id 

区别：不能再where中使用组函数，可以在having中使用

select department_id , avg(salary)
from employees
having avg(salary) > 6000
group by(department_id)

select  max(avg(salary))
from employees
group by department_id

select count(*) "total" ,
       count(decode(to_char(hire_date,'yyyy'),'1995',1,null)) "1995",
       count(decode(to_char(hire_date,'yyyy'),'1996',1,null)) "1996",
       count(decode(to_char(hire_date,'yyyy'),'1997',1,null)) "1997",
       count(decode(to_char(hire_date,'yyyy'),'1998',1,null)) "1998"
from employees
where to_char(hire_date,'yyyy') in('1995','1996','1997','1998')

创建表

/*
--第一种方式
create table emp1(
id number(10),
name varchar2(20),
salary number(10,2),
hire_date date
)
*/
--第二种方式,以现有employees表来创建表，并且把原来的表的数据，都导入新表中了
create table emp2
as 
select employee_id id ,last_name name , hire_date , salary
from employees

create table emp3
as 
select employee_id id ,last_name name , hire_date , salary
from employees  
where department_id = 80  

create table emp4
as 
select employee_id id ,last_name name , hire_date , salary
from employees  
where department_id = 800000

ALTER TABLE emp1
--ADD (eamil varchar2(20))--增加
--modify (id number(15))--修改
--modify (salary number(20,2) default 2000)--默认初始值2000，之前的数据是不会改变的，只改变后来的数据
--modify (email number(20))--修改数据类型，前提是email没有数据，有数据就改不了

ALTER TABLE emp1
DROP COLUMN eamil

ALTER TABLE emp1
rename column salary to sal

<span style="font-family:KaiTi_GB2312;font-size:18px;"><strong>drop table emp5;</strong></span>

<span style="font-family:KaiTi_GB2312;font-size:18px;"><strong>SQL> truncate table emp3;</strong></span>