DML

DML 语句可执行:

插入新数据

修改已有数据

删除数据

INSERT

INSERT语句一次将一个记录的数据输入表中

INSERT INTO table [(column [, column...])]

VALUES (value [, value...]);

例子：

INSERT INTO dept (deptno, dname, loc)

    VALUES (50, 'DEVELOPMENT', 'DETROIT')

INSERT语句中使用函数

例如：

使用NOW()函数插入当前的日期和时间

INSERT INTO emp (empno, ename, job,

  mgr, hiredate, sal, comm,

  deptno)

    VALUES (7196, 'GREEN', 'SALESMAN',

  7782, NoW(), 2000, NULL,

  10);

插入空值

例1

INSERT INTO dept (deptno, dname )

    VALUES (60, 'MIS');

例2

INSERT INTO dept

    VALUES (70, 'FINANCE', NULL);

从其他表中拷贝记录

INSERT INTO managers(id, name, salary, hiredate)

            SELECT empno, ename, sal, hiredate

          FROM  emp

            WHERE job = 'MANAGER';

INSERT INTO emp_copy

            SELECT *

          FROM  emp

            WHERE job = 'MANAGER‘

UPDATE 语句

使用UPDATE语句修改已存在的记录

UPDATE table

SET column = value [, column = value]

[WHERE condition]

修改记录

使用WHERE子句修改指定的记录.

UPDATE emp

    SET    deptno = 20

    WHERE  empno = 7782

修改所有的记录.

UPDATE employee

    SET    deptno = 20

通过查询更新

UPDATE emp_copy

    SET  job = (SELECT job

          FROM    emp

      WHERE  empno = 7698),deptno= (SELECT deptno

      FROM    emp  WHERE  empno = 7698) WHERE empno=7566

DELETE 语句

使用DELETE删除表中所有数据 

DELETE [FROM] table

[WHERE   condition]（使用WHERE来限定删除什么）

从表中删除记录

DELETE FROM dept

  WHERE dname = 'DEVELOPMENT'

删除所有的记录

DELETE FROM dept

数据类型

创建表

你必须有 :

    建表的权限

    有存储区域

    CREATE TABLE [schema.]table

    (column datatype [DEFAULT expr]

你可指定:

    表名 Table name

    列名, 列的数据类型, 列的大小

建表

REATE TABLE dept2(deptno INT,

  dname VARCHAR(14),

  loc VARCHAR(13))

使用子查询创建表

列的数目要和子查询中的匹配

可定义列名和缺省值

CREATE TABLE table

    [column(, column...)]

AS subquery

例：CREATE TABLE dept30

               AS

                       SELECT empno, ename, sal*12 ANNSAL, hiredate

                        FROM emp

                        WHERE deptno = 30

ALTER TABLE 语句

使用ALTER TABLE 语句可:

加一列

ALTER TABLE table

ADD   (column datatype [DEFAULT expr]

  [, column datatype]...)

ALTER TABLE dept30

    ADD   (job VARCHAR(9))

修改列

ALTER TABLE table

MODIFY   (column datatype [DEFAULT expr]

  [, column datatype]...)

ALTER TABLE dept30

MODIFY job VARCHAR(20)

删除列

ALTER TABLE table DROP COLUMN column

ALTER TABLE dept30 DROP COLUMN job

删除表

DROP TABLE dept2

改表名

ALTER TABLE tablename RENAME table_newname

ALTER TABLE dept30 RENAME dept40

什么是约束

约束是表级强制执行的规则.

当表中数据有相互依赖性时,可保护数据不被删除.

有如下类型的约束:

NOT NULL

UNIQUE Key

PRIMARY KEY

FOREIGN KEY

约束概况

创建约束:

在建表的同时创建

建表后创建

可定义列级或表级约束.

CREATE TABLE [schema.]table

    (column datatype [DEFAULT expr]

[column_constraint],

[table_constraint]);

CREATE TABLE emp

      (empno  NUMBER(4),

        ename  VARCHAR2(10),

      deptno  NUMBER(7,2) NOT NULL,

    CONSTRAINT emp_empno_pk

          PRIMARY KEY (EMPNO));

创建约束

列级约束

column [CONSTRAINT constraint_name] constraint_type,

表级约束

column,...

  [CONSTRAINT constraint_name] constraint_type

  (column, ...)

非空约束（NOT NULL）

定义列级约束

CREATE TABLE dept6(deptno INT NOT NULL,

    deptname VARCHAR(20),

    deptloc VARCHAR(50))

唯一性约束（UNIQUE）

定义唯一性约束

CREATE TABLE dept(

  deptno INT,

  dname VARCHAR2,

  loc VARCHAR2,

  CONSTRAINT uk_dept_dname UNIQUE(dname))

主键约束

CREATE TABLE dept6(

    deptno INT,

  dname VARCHAR(14),

  loc VARCHAR(13),

  CONSTRAINT dept_dname_uk UNIQUE(dname),

  CONSTRAINT pk_deptno PRIMARY KEY(deptno))

外键约束（FOREIGN KEY）

定义外键约束

CREATE TABLE `emp_copy` (

  `empno` int(4) NOT NULL,

  `ename` varchar(60) default NULL,

  `job` varchar(30) default NULL,

  `mgr` int(4) default NULL,

  `hiredate` date default NULL,

  `sal` float default NULL,

  `comm` float default NULL,

  `deptno` int(2) default NULL,

  PRIMARY KEY  (`empno`),

  KEY `emp_index` (`deptno`),

  CONSTRAINT `pk_emp_dept` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)

                                                  )

视图

视图是一个虚拟表，限制对数据的访问

很容易的写成复杂的查询

允许数据的独立性

不同的视图可获得相同的数据

创建视图的语句中可嵌入子查询.

CREATE [OR REPLACE] VIEW

view_name [(column_list)]

AS select_statement

创建视图.

CREATE VIEW empvu10

  AS SELECT empno, ename, job

    FROM emp

    WHERE deptno = 10

在子查询中使用列别名创建视图.

CREATE VIEW salvu30

    AS SELECT empno as EMPLOYEE_NUMBER, ename as NAME,

  sal as SALARY

    FROM emp

    WHERE deptno = 30

在查询视图时使用列别名.

SELECT EMPLOYEE_NUMBER, NAME, SALARY

  FROM    salvu30

修改视图

使用CREATE OR REPLACE VIEW 子句修改 视图 ,并为每列加别名.

CREATE OR REPLACE VIEW empvu10

      (employee_number, employee_name, job_title)

    AS SELECT empno, ename, job

    FROM emp

    WHERE deptno = 10

在CREATE VIEW语句中列的别名的顺序是和子查询中一致的.

复杂视图的特点：

从多个表查询

包含函数

包含分组数据

CREATE VIEW dept_sum_vu

      (name, minsal, maxsal, avgsal)

    AS SELECT d.dname, MIN(e.sal), MAX(e.sal),

AVG(e.sal)

    FROM emp e, dept d

    WHERE e.deptno = d.deptno

    GROUP BY d.dname

删除视图

删除视图并不删除基表中的数据.

DROP VIEW view

DROP VIEW empvu10

视图可从其他的表或视图中获取数据.

视图提供如下优点:

限制数据的访问

简化查询

提供独立的数据

允许多个视图使用相同的数据

删除时不影响基表

建用户

设置密码

CREATE USER user

IDENTIFIED BY  password

CREATE USER tester IDENTIFIED BY 'tiger'

设置或修改密码

超级用户可使用 CREATE USER 语句建用户.

SET PASSWORD=PASSWORD (password)

SET PASSWORD=PASSWORD ('tom1')

权限列表

ALTER: 修改表和索引。

CREATE: 创建数据库和表。

DELETE: 删除表中已有的记录。

DROP: 抛弃(删除)数据库和表。

INDEX: 创建或抛弃索引。

INSERT: 向表中插入新行。

REFERENCE: 引用。

SELECT: 检索表中的记录。

UPDATE: 修改现存表记录。

FILE: 读或写服务器上的文件。

PROCESS: 查看服务器中执行的线程信息或杀死线程。

RELOAD: 重载授权表或清空日志、主机缓存或表缓存。

SHUTDOWN: 关闭服务器。

ALL: 所有权限，ALL PRIVILEGES同义词。

USAGE: 特殊的 "无权限" 权限。

赋予权限

GRANT privilege ON object TO host IDENTIFIED BY 'password'

GRANT ALL ON *.* TO 'tester'@'localhost' IDENTIFIED BY 'tester'

说明：授予tester在服务器本机上所有权限。

          *.*  代表所有数据库所有对象。Test.*  代表test数据库所有对象

    ’ tester ‘ @ ’ localhost ‘ 代表tester用户在本机操作权限。    

    ’ tester ‘ @ ’ % ‘ 代表tester用户从所有机器上操作权限。

刷新权限

通常执行完需要刷新权限

FLUSH PRIVILEGES

撤销权限

REVOKE privilege ON object FROM username

REVOKE update ON company.* FROM tester