SQL> conn / as sysdba
SQL> shutdown immediate; ---停止数据库
----------出现以下情况就是数据库没有打开
SQL> conn zou/zou
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
解决办法：
SQL> conn / as sysdba
SQL> startup; ---启动数据库
---------------查询6月份到单位的人，并且工资大于10000
QL> select * from employees
2 where substr(hire_date,4,1)='6' and salary>10000;
---------------查询全名为stevenking的员工或者工作10年以上的员工
SQL> select * from employees
2 where lower(first_name||last_name)='stevenking'
3 or months_between(sysdate,hire_date)>120;
----------------------多表查询
pk：primary key 主键
性质:区分每一行数据，不允许为null,不允许重复
FK：foreign key 外键
性质：用于多表查询，外键的取值必须来至于主键，或者不填
------------等值查询
SQL> select first_name,department_name
2 from employees,departments
3 where employees.department_id=departments.department_id;
注意:多表查询，如果n个表进行查询，连接条件至少写n-1个，并且用and 连接
SQL> select first_name,salary, department_id,department_name
2 from employees,departments
3 where employees.department_id=departments.department_id;--此语法错误，因为两个表都有department_id
SQL> select employees.first_name,employees.salary,departments.department_id,departments.department_name
2 from employees,departments
3 where employees.department_id=departments.department_id;
SQL> select e.first_name,e.salary,d.department_id,d.department_name
2 from employees e,departments d
3 where e.department_id=d.department_id;
注意:多表查询时，最好是“表名.列名”以免产生歧义
----------查询在IT部门的员工名，工资
select first_name,salary
from employees,departments
where department_name='IT' and employees.department_id=departments.department_id;
注意:多表查询想附加搜索条件，加and
---------------三表或者三表以上的
SQL> select sname,cname,grade
2 from student,course,sc
3 where student.sno=sc.sno and course.cno=sc.cno;
----------------查询计算机系的男生选了哪门课得了多少分
SQL> select sname,cname,grade
2 from student,course,sc
3 where student.sno=sc.sno and course.cno=sc.cno and sdept='计算机' and ssex='男';
------------不等值查询
SQL> select first_name,salary,grade_level
2 from employees,job_grades
3 where salary between LOWEST_SALARY and HIGHEST_SALARY;
-------------外查询
--------------左外连接
-----------哪个人没有部门
select first_name,department_name
from employees left outer join departments
on employees.department_id=departments.department_id;
-------------右外连接
select first_name,department_name
from employees right outer join departments
on employees.department_id=departments.department_id;
-------------全外连接
select first_name,department_name
from employees full outer join departments
on employees.department_id=departments.department_id;
SQL> select *
2 from student left outer join sc
3 on student.sno=sc.sno;
注意:没有where加where，有where加and
---------------------组函数
max，min，avg，sum，count
--------------max最大，min最小，sum求和
SQL> select max(salary),min(salary),sum(salary)
2 from employees;
MAX(SALARY) MIN(SALARY) SUM(SALARY)
----------- ----------- -----------
24000 2500 227100
------------部门号为100的最大，最小，工资总和
SQL> select max(salary),min(salary),sum(salary)
2 from employees;
3 where department_id=100;
-------------avg平均值
SQL> select avg(commission_pct)
2 from employees;-----用总和除以有佣金的人数
AVG(COMMISSION_PCT)
-------------------
.2125
SQL> select avg(nvl(commission_pct,0))
2 from employees;-----用总和除以所有人数
AVG(NVL(COMMISSION_PCT,0))
--------------------------
.032692308
--------------count(*)计算行数
SQL> select count(*)
2 from employees
3 where department_id=100;
-------------计算有佣金的几个人
SQL> select count(*)
2 from employees
3 where commission_pct is not null;
--------------count(列名)按照某列非空值计算行数
SQL> select count(commission_pct)
2 from employees;
--------------count(distinct 列名)去掉重复记录计算行数
SQL> select count(distinct sdept) from student;
----------------group by分组
---------求每个系分别多少人？
select count(*)
from student
group by sdept;
select sdept,count(*)
from student
group by sdept;
注意:1.在group by 后面出现的列可以不在select后面不出现
2.在select后面，组函数以外出现的列，必须在group by 后面出现
-------------每个部门平均工资
select department_id,avg(salary)
from employees
group by department_id;
select department_name,avg(salary)
from employees,departments
where employees.department_id=departments.department_id
group by departments.department_id,department_name;
---------------求每个系男女生分别多少人
select ssex,sdept,count(*)
from student
group by ssex,sdept;
---------------求每个系男生分别多少人
select sdept,count(*)
from student
where ssex='男'
group by sdept;
-----------------having
select department_id,avg(salary)
from employees
group by department_id
having avg(salary)>8000
order by avg(salary) desc;
注意:组函数（min,max,sum,count,avg）过滤必须用having
