oralce sql语句大全并附案例
时间: 2023-07-11 10:45:29 浏览: 93
这里是一些Oracle SQL语句的常见用法以及示例:
1. 创建表
```
CREATE TABLE employees (
employee_id NUMBER(4) PRIMARY KEY,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(7, 2),
commission NUMBER(7, 2),
manager_id NUMBER(4),
department_id NUMBER(2)
);
```
2. 插入数据
```
INSERT INTO employees (employee_id, first_name, last_name, hire_date, job_id, salary, commission, manager_id, department_id)
VALUES (1, 'John', 'Doe', TO_DATE('01-01-2000', 'DD-MM-YYYY'), 'MANAGER', 5000, NULL, NULL, 10);
```
3. 更新数据
```
UPDATE employees
SET salary = 5500
WHERE employee_id = 1;
```
4. 删除数据
```
DELETE FROM employees
WHERE employee_id = 1;
```
5. 查询数据
```
SELECT *
FROM employees
WHERE department_id = 10;
```
6. 连接查询
```
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
```
7. 分组查询
```
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
```
8. 排序查询
```
SELECT *
FROM employees
ORDER BY last_name, first_name;
```
9. 子查询查询
```
SELECT *
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'IT'
);
```
10. 聚合函数查询
```
SELECT COUNT(*), AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees;
```
以上是一些Oracle SQL语句的常见用法以及示例,希望对您有所帮助。
阅读全文