# Oracle 基础

## 基础

### 日期
标准写法
```sql
INSERT INTO STUDENT VALUES (1,'胖哈',12,66,'12-11月-2015');
```

日期格式化
```sql
INSERT INTO STUDENT VALUES (1,'番说鱼',16,11,to_date('2016-11-11','yyyy-MM-dd'));
```

### 排序
Oracle并不会根据id(primary key) 自动排序,需要手动更改

### url
两种
```sql
jdbc:oracle:thin:@localhost:1521:ORCL
```

```sql
jdbc:oracle:thin:@//localhost:1521/ORCL
```

### 分页
```sql
SELECT * FROM
  (
    SELECT A.*, ROWNUM RN
    FROM (SELECT * FROM BOOKS) A
    WHERE ROWNUM <= 4
  )
WHERE RN >= 2
```sql
1. 选取表BOOKS命名为A
2. 为A创建行数,并选择A中函数<=4的数据
3. 2中选出的数据,选出行数>=2的

### 创建表空间
```sql
CREATE TABLESPACE kumaSpace
DATAFILE 'c:\MOracleSpace\jbit1.DBF'
SIZE 100M ;
```

指定默认表空间
```sql
DEFAULT TABLESPACE tablespace
```

## 用户权限
### SYS 超级用户
登录,不需要密码
```sql
conn /as sysdba
```

修改其他用户密码
```sql
alter user paracide identified by manager;
```

### DBA 管理员角色

### RESIYRCE 可靠正式用户

### CONNECT 临时用户

### 创建用户
创建
```sql
create USER  paracide IDENTIFIED by a123456
```

分配权限
```sql
GRANT DBA TO paracide;
```

撤销权限
```sql
REVOKE  privileges or role FROM user;
```

## 增删改
同mysql,完成后需要手动提交
```sql
commit;
```

### 删除增加列
```sql
ALTER TABLE STUDENT DROP COLUMN AGE;
ALTER TABLE STUDENT ADD (google VARCHAR2(200),tiwtch VARCHAR2(200));
```

### 修改表
```sql
ALTER TABLE STUDENT MODIFY (NAME VARCHAR2(100));
```

### 添加约束

#### 检查约束
```sql
ALTER table account add constraint ck_account_cash CHECK (cash >0 and cash <=100);
```

#### 外键约束
```sql
ALTER TABLE pet //受约束的那个表
ADD CONSTRAINT fk_master_id //约束的名称
FOREIGN KEY (masterid) //外键 受约束表中的受约束元素(源自外部的键)
REFERENCES master(loginid);/别的表的主键!!,约束外键的表的元素
```

### 序列 实现自增长

> **一个表一个序列**

创建序列
```sql
CREATE SEQUENCE sq_account_id ;
```

为表绑定序列实现自增长
```sql
INSERT INTO account VALUES (sq_account_id.nextval,12.3);
```

查看当前序列
```sql
SELECT SQ_ACCOUNT_ID4.currval FROM dual; //dual是系统默认的一个表
```

可以通过多次报错实现填补中间空缺的序列

## 查询
### to_char & 别名
按照指定要求,将日期转为指定格式的字符串,同时起别名 `日期`
```sql
SELECT to_char(HIRE_DATE,'yyyy-MM-dd') 日期 FROM TEACHERS;
```

### 连接
- 将2个列合并到一个列
```sql
SELECT name || DOB  FROM STUDENTS;
```
- 2个合并列中加入其他文字
```sql
SELECT name || '的生日是' || to_char(DOB,'yyyy-MM-dd,HH24:mi:ss') FROM STUDENTS;
```

### 逻辑判断
- 如果其中一个值为null,则结果为null,而不是0+另外一个
```sql
SELECT NAME, BONUS+WAGE 总收入 FROM TEACHERS;
```

- 时间也可以比较
```sql
SELECT name, DOB FROM STUDENTS WHERE DOB < = '24-3月-2015';

SELECT name,DOB FROM STUDENTS WHERE DOB <= to_date('2015-3-24','yyyy-MM-dd');
```

- 包含
  in更安全,in中可以是不存在的值
  而or的取值必须是存在的值,否则报错
```sql
SELECT * FROM TEACHERS WHERE BONUS in(800,1000);

SELECT * FROM TEACHERS WHERE BONUS =800 or BONUS=1000;
```

- 取反
```sql
SELECT * FROM STUDENTS WHERE SPECIALTY NOT IN ('计算机', '软件');

SELECT * FROM STUDENTS WHERE SEX = '男' AND SPECIALTY != '计算机';
```

- 是否为空
   判断是否为null 只能用is 不能用 =
```sql
SELECT * FROM TEACHERS WHERE  WAGE is NOT NULL ;
SELECT * FROM TEACHERS WHERE  WAGE is NULL ;
```

### 子查询
```sql
SELECT * FROM TEACHERS WHERE WAGE < (SELECT avg(WAGE) FROM TEACHERS);
```

### 分组
只能查询分组条件的列
其他列只能包含在聚合函数中 (sum avg max等等)
```sql
SELECT DEPARTMENT_ID,avg(WAGE) FROM TEACHERS GROUP BY DEPARTMENT_ID;
```


- 获取平均工资大于 最低平均工资的组 的组平均工资,编号
```sql
SELECT
  DEPARTMENT_ID,
  avg(WAGE)
FROM TEACHERS
GROUP BY DEPARTMENT_ID
HAVING avg(WAGE) > (SELECT min(avg(wage))
                    FROM TEACHERS
                    GROUP BY DEPARTMENT_ID);
```
having 就是分组后的where

### 分页
1. 使用between,先排序,否则会出现行号与id不匹配的情况
```sql
SELECT *
FROM (SELECT
        t.*,
        ROWNUM rn
      FROM (SELECT *
            FROM TEACHERS
            ORDER BY TEACHER_ID) t)
WHERE rn BETWEEN 2 AND 5;
```

### exists
```sql
SELECT
  COURSE_ID,
  COURSE_NAME
FROM COURSES c
WHERE exists(SELECT 1
             FROM STUDENTS_GRADE g
             WHERE c.COURSE_ID = g.COURSE_ID);
```
然而并不好用,不如直接链接查询
```sql
SELECT
  c.COURSE_ID,
  c.COURSE_NAME
FROM COURSES c, STUDENTS_GRADE g
WHERE c.COURSE_ID = g.COURSE_ID;
```

### inner join
```sql
SELECT
  s.STUDENT_ID 学生编号,
  s.NAME       学校姓名,
  c.COURSE_ID 课程编号,
  c.COURSE_NAME 课程名称,
  sg.SCORE     分数
FROM STUDENTS s INNER JOIN STUDENTS_GRADE sg ON s.STUDENT_ID = sg.STUDENT_ID
INNER JOIN COURSES c on sg.COURSE_ID =c.COURSE_ID;
```
inner join不能在没有on依赖的情况下单独执行,因为inner join再生成总表时就已经开始筛选了

### left out join
满足主表,主表存在就显示,即便主表中与join的表关联的值为null(在inner join中 则不会显示)
```sql
SELECT *
  FROM TEACHERS t LEFT OUTER JOIN DEPARTMENTS d ON  t.DEPARTMENT_ID = d.DEPARTMENT_ID;
```


### right out join
正好相反,如果左边没有,则全部显示为null
```sql
SELECT *
FROM TEACHERS t Right OUTER JOIN DEPARTMENTS d ON  t.DEPARTMENT_ID = d.DEPARTMENT_ID;
```