SQL常用语法

SQL常用语法

-- 创建表
CREATE TABLE employees(
   employee_id NUMBER(6),
   first_name VARCHAR2(20),
   last_name VARCHAR2(25),
   email VARCHAR2(25),
   phone_number VARCHAR2(20),
   hire_date DATE,
   job_id VARCHAR2(10),
   salary NUMBER(8,2),
   commission_pct NUMBER(2,2),
   manager_id NUMBER(6),
   department_id NUMBER(4)
);

-- 插入数据
INSERT INTO employees(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) 
VALUES (100, 'John', 'Smith', 'jsmith@example.com', '515.123.4567', TO_DATE('01-JAN-00', 'DD-MON-RR'), 'IT_PROG', 20000, null, 103, 60);

-- 更新数据
UPDATE employees 
SET salary = 25000 
WHERE employee_id = 100;

-- 删除数据
DELETE FROM employees 
WHERE employee_id = 100;

-- 创建索引
CREATE INDEX emp_name_ix 
ON employees (last_name);

-- 添加主键约束
ALTER TABLE employees 
ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id);

-- 添加外键约束
ALTER TABLE employees 
ADD CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) 
REFERENCES departments(department_id);

-- 创建视图
CREATE VIEW dept_sal_vu AS
SELECT d.department_name, e.salary 
FROM departments d JOIN employees e
ON (d.department_id = e.department_id);

-- 事务控制
COMMIT; -- 提交事务
ROLLBACK; -- 回滚事务
SAVEPOINT savept1; -- 设置保存点
ROLLBACK TO savept1; -- 回滚到保存点