常用数据库维护命令
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1.1 Oracle常用维护命令
创建表空间
关键字：CREATE TABLESPACE
示例：
CREATE TABLESPACE tbs_02
DATAFILE 'diskb:tbs_f5.dat' SIZE 500K REUSE
AUTOEXTEND ON NEXT 500K MAXSIZE 100M;
创建存储过程
关键字：CREATE PROCEDURE
示例：
CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
tot_emps NUMBER;
BEGIN
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;
创建表
关键字：CREATE TABLE
示例：
CREATE TABLE employees_demo
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25) CONSTRAINT
emp_last_name_nn NOT NULL
, email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE DEFAULT SYSDATE CONSTRAINT
emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2) CONSTRAINT
emp_salary_nn NOT NULL
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, dn VARCHAR2(300)
, CONSTRAINT emp_salary_min CHECK (salary > 0)
, CONSTRAINT emp_email_uk UNIQUE (email)
) ;
创建用户
关键字：CREATE USER
示例：
CREATE USER app_user1
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE example
QUOTA 5M ON example
PROFILE app_user;
授予用户权限
关键字：GRANT
示例：
GRANT SELECT, UPDATE
ON emp_view TO PUBLIC;
查询表
关键字：SELECT
示例：
SELECT *
FROM employees
WHERE job_id = 'PU_CLERK'
ORDER BY commission_pct DESC;
更新表
关键字：UPDATE
示例：
UPDATE employees a
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = '2100'),
(salary, commission_pct) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) FROM employees b
WHERE a.department_id = b.department_id)
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700);
删除表
关键字：DROP TABLE
示例：
DROP TABLE table_to_be_delete;
1.2 SQL Server常用维护命令
创建数据库
关键字：CREATE DATABASE
示例：
use master
GO
CREATE DATABASE Employees
ON
( NAME = Empl_dat,
FILENAME = 'd:\sample data dir\',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'd:\sample log dir\',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
创建存储过程
关键字：CREATE PROCEDURE
示例：
CREATE PROCEDURE proc2
AS
DECLARE @middle_init char(1)
SET @middle_init = NULL
SELECT au_id, middle_initial = @middle_init
FROM authors
GO