SQL基础

一、关系数据库的一些概念

1、主键的值一般不可以改变

2、外键：指向另一个表或本表的主键或唯一键的字段。外键的值一定要和某一主键相同，或者为空。

3、数据库对像：表，视图，序列，索引，同义词，程序（进程，函数， sql和pl/sql数据）4、sql command 类别

    data retrieval数据检索 :select

    data manipulationlanguage (DML)数据操作语言 :insert,update,delete

    data definition language (DDL)数据定义语言： create,alter,drop,rename,truncate

    transaction control事务控制 :commit,rollback,savepoint

    data control language（ DCL）数据控制语言：grant,revoke

    DCL 和 DDL命令的执行会导致一次隐式提交，之前未提交的操作（包括DML 命令）都会提交写入数据文件。

二、SQL的语法

1)   连接号： ||

2)   把两个字符连接起来

eg:select game_card_type_id||name from game_card_type;

3)   select distinct dept_id,title from emp： 对多个字段的唯一

4)   order by desc(降序 )

order by asc(升序 )

5)   where column is (not) null

6)   like ‘_a%’    _表示一个字符。％表示多少字符

like ‘%x/_y%’ escape ‘/’:显示包括 x_y的字符

7)   where table1.column(+)=table2.column

place the operator on the side of the join where there is no value to join to.

    8) 联接类型 :

equijoin: 等式查询

non_equijoin: 不等式查询

self: 自己和自己建立关联

out join:where a.column=b.column(+)

可以用的操作符是：’=’，’and’，不可以用’or’，’in’

    9)  COUNT 函数所用的列包含空值时，空值行被忽略。

10) where 后的in any all 的区别

in ：等于子查询的任何一个数

any ：与子查询的每一个值相比

只要比其中一个大（小）就可以了

all:与子查询的所有值相比要比所有的的都大（小）

   !=ALL作用跟NOT IN 一样

三、SQL*PLUS的环境(可以在glogin.sql中定义初始参数)

1)   START 命令用以执行一个已储存的文件 ,等同于@

2)   SAVE命令用以创建一个文件

3)   EDIT命令用以调用编辑器编辑已存文件的内容

4)   CHANGE 是 SQL*Plus的编辑命令，用于在当前一行把旧文段转换为新文段

5)   DEL 命令用以删除文本中一行或多行文字

6)   SPOOL命令用以把查询结果储存在一个已有文件中，注意与 SAVE区别

7)   GET命令用以一个文件的内容写进一个 SQL块中

8)   SPOOL OUT命令用以把文件的结果发送到系统打印机。

9)   set pause off/on:设置页面的滚动。按 enter看下一页。

10) PAGESIZE：指定每页显示的行的数值

11) LONG：设置 LONG类型数据显示的最大宽度

12) FEEDBACK：设置查询返回记录的最大值

13) DESCRIBE :用于显示表和视图的结构，同义词，或指定函数和过程的详述。

14) 在各种数据类型中，只有 NUMBER数据类型的默认显示是靠右对齐的，而CHAR, DATE和VARCHAR2是靠左对齐的

15)  QUIT 是 SQL*PLUS 命令，用以结束一个 SQL*PLUS 的对话。

16) ttitle:
       ttitle ‘selina’ 设 select 的结果的抬头为selina

tti ：显示 ttitle的状态

tti off/on

btitle ‘end’ 设结果的尾部

17)              Column：

a)             column name heading ‘名字 ’format a15

b)             column id justify left format 999999

c)             column start_date format a9 null ‘not hired’//当字段为空的时候则显示成 not hired

d)             column :显示所有的 column设置

e)             column columnname:显示某一个字段的设置

f)             clear column :清除所有 column设置

g)             column columnname clear:清除某一字段的设置

h)             以上的 column可用col代替.clear可以用cle代替

     13）定义变量

用 &：由用户输入变量值，此变量可以存在于where后，做为整个查询语句的变量。也可以在order by 后。做为字段的变量。也可以放在select 后，做为字段或表达式的变量。

用 &&：如果多个地方引用此变量。。只用输入一次

     SQL> SELECT      empno, ename, job, &&column_name

         FROM        emp

        ORDER BY &column_name;

 

 

accept：由用户输入变量值

 accept 变量名 datatype prompt ‘告诉用户需要输入的信息：’ hide

 引用的时候： &变名

define(undefined)：一开始就定义变量值

四、函数：

1、字符函数

lower:把字符转成小写

upper:把字符转成大写

initcap:把单词的第一个字母变成大写

concat:连接字符  concat(‘good’,’morning’)=goodmoring

SUBSTR (column/expression, m[,n]) 用于对字符串进行截取操作，从第 m个位置开始，把其后的连续n个字符的部分截取下来，如果m位负值，则从末尾开始计算。

eg:substr(‘string’,1,3) =str

          substr(‘string’-3,3)=ing

INSTR( ' String ' , ' r ' )=3

LPAD(sal,10, ' * ' )=******5000

 

 

length: 用于返回表达式中的字符数，注意返回的是 NUMBER。

NVL(expression1, expression2)
NVL 函数用以把一个空值转换为一个实值 ,如：
NVL(100/quantity, 0) ,要是quantity为空值，该函数返回一个0。
如果两个字段类型不同必须进行转换。

Min():返回最小值。。如果是字符。。 A<a

 

2、数学函数

round:四舍五入

   round(2.566,2)=2.27

  round(45,-1)=50

trunc: 截断

 trunc(2.566,2)=2.56

 trunc(45,-1)=40

mod:

 mod(m,n):m-n*flood(m/n)      //flood是取整数

 

3、日期函数 :

a)       months_between(date1,date2)：算 date1和date2之间的月的数量,可以是小数可以是负数

months_between(’01-sep-95’,’11-jan-94’)=1.9774194

b)       add_months(date,n)：为 date加上N个月，N只可以是整数

c)       next_date(date,’char’)：查找 date的下一个星期N

next_date(’01-sep-95’,’FRIDAY’)=08-SEP-95

d)       last_day(date):查找 date月的最后一天。

e)       rount(date)：把日期四舍五入

f)       rount(25-MAY-95’,’MONTH’)=01-JUN-95

g)       rount(25-MAY-95’,’YEAR’)=01-JAN-95

h)       trunc(date)：把日期截断

i)       trunc (25-MAY-95’,’MONTH’)=01-MAY-95

j)       trunc (25-MAY-95’,’YEAR’)=01-JAN-95

k)       日期中 RR与YY的区别，RR格式对日期作类似于舍入的操作，YY格式对日期作类似于截取的操作
                RR   YY
1995 27-oct-95 1995 1995
1995 27-oct-17 2017 1917
2001 27-oct-17 2017 2017
2001 27-oct-95 1995 2095

 

4、转换函数

TO_CHAR：
 TO_CHAR(date,’fmt’)：fm前缀用来去除首尾的空字符或 0
 TO_CHAR(total,’fm$999999’)
      如果想转成 $0.25,那就要写成fm$9999990.99
 可以把日期转换成字符
     TO_CHAR(log_time,’MM/YY’)
     TO_CHAR(lot_time,’fmdd’’of;’’mm yyyy’)
     具体格式如下

   HH24:MI:SS AM-----------15:24:32 pm
   DD’’of’’MONTH-----------12 of MAY
   Ddspth------------------------fourteenth
   Ddsp--------------------------fourteen
   ddth---------------------------4^th
   YYYY-----------------------1978
    MM-----------------------------12
    MONTH-------------------------MAY

5、 group 函数avg,count,max,min,stddev,sum,variance

 

五、数据字典

用户表：由用户创建，包含用户的内容

数据字典：由系统建立，包含数据库的信息

 

前缀 :

USER_ ：由用户创建 ,显示用户拥有的所有对象。

ALL_ ：由受权的用户访问 , 用户可以访问的对象名。

DBA_ ：由受了 DBA权限的人访问，显示数据库的所有对象。

V$ ：由受了 DBA权限的人访问，显示数据字典数据库服务器性能信息。通常是 DBA用于显示系统的统计表和动态性能表。

 

数据字典

DICTIONARY数据字典提供用户可以访问的数据字典表和视图的描述。
USER_OBJECTS显示用户拥有的对象。
USER_VIEWS显示用户拥有的视图。
USER_TABLES显示用户拥有的表。
ALL_TABLES显示用户可以访问的表。
ALL_VIEWS显示用户可以访问的视图。
USER_CATALOG显示用户拥有的所有表，视图，同义词和序列。
 USER_CONS_COLUMNS显示带约束的列。
DBA_CONS_COLUMNS显示数据库里的所有表，视图和同义词。
USER_TAB_PRIVS_MADE ：本用户赋给别的用户赋予权限的表

USER_TAB_PRIVS_RECD ：其他用户给本用户赋予权限的表

USER_COL_PRIVS_MADE ：本用户赋给别的用户赋予权限的字段

USER_COL_PRIVS_RECD ：其他用户给本用户赋予权限的字段
ROLE_SYS_PRIVS: 有什么系统权限赋给role

ROLE_TAB_PRIVS: 有什么关于表的权限赋给role

USER_ROLE_PRIVS:role 和用户的对应表

 

常用的表

user_objects：用户对象表（存储用户的所有对象）存储以下的类型的数据

Selina Sql>select distinct object_type from user_objects;

INDEX

LOB

PACKAGE

PACKAGE BODY

PROCEDURE

SEQUENCE

SYNONYM

TABLE

TRIGGER

VIEW

User_catalog:用户类表，存储以下的类型的数据

Selina Sql>select distinct table_type from user_catalog;

SEQUENCE

SYNONYM

TABLE

VIEW

 

 

六、建立对象

1、 表

a)     基本概念

Ø         数据库会分配一定的空间从而定义表的大小，所以表的大小不用指定。

Ø         表可容纳最多 1000列。

Ø         表可在用户使用数据库的同时创建，而且表的结构可在表联机时修改。

Ø         RENAME语句的语法 : RENAME old name TO new name; 注意要是表名中包含空格，符号或数字，必须使用双引号

Ø         在 CREATE TABLE子句中使用子查询，新表中的列数必须与子查询返回的列数相等，列位置对应。另外，除NOT NULL约束外，新列不会继承源列的所有约束。

Ø         你不能对一列重命名。在表中添加一列时，你不能指定该列的位置，它会被默认放置在最后。你可以为一列增加长度。 通常当一列包含数据时，你不能修改该列的数据类型，不能减少一列的长度，但当列中包含空值或你不改变列的大小时，你可以把 CHAR 转换为VARCHAR2数据类型。

Ø         在 DROP TABLE table命令后加上CASCADE CONSTRAINTS回把表中的相关约束一并删除。如命令："DROP TABLE employee CASCADE CONSTRAINTS;"会把employee表中的数据，结构，和相关约束一并删除。

Ø         当你创建一个数据类型为 VARCHAR2的列时，必须指定长度。

Ø         为表加注释 :comment on table tablename is ‘………’;

Ø         COMMENT ON TABLE命令用于在数据字典里添加关于表，视图或快照的注释。

Ø         但你对一表执行了 DML 语句的 INSERT 操作时，但没有提交，别人可以同时访问该表，但看不到你所作的修改，由于对象被加锁，所以别人不能作同样的修改。

b)     建表原则

以字母开头

不可以超过 30个字

只能由数字 ,字母,_,$,#组成

用子查询建立表

create table tablename as select …..

 

c)     删除表

drop table tablename

删除所有的数据

删除所有相关的 index

所有待解决的事务会自动提交

不可以回滚

不释放空间

 

truncate table tablename

删除所有的数据

释放该表所占用的空间

不可以回滚

并存储容量参数重置为定义值

要成功执行 TRUNCATE TABLE 命令，你必须拥有该表或拥有DELETE TABLE的系统权限。

 

2 、约束

Ø约束类别

column level:

NOT NULL

table level:

UNIQUE Key

PRIMARY Key

FOREIGN Key

CHECK

CHECK can be defined either on column level or on table level. Plus, you can say something like:
create table abc
(
column1 number,
column2 number,
constraint less_ck CHECK (column2 < column1)
);
This constraint will make sure column2 is less than column1 when you enter data into the table.

 

Ø建立约束

Table constraint level

column,...

  [CONSTRAINT constraint_name] constraint_type

 (column, ...),

EG:

CREATE TABLE emp(

 empno NUMBER(4),

 ename VARCHAR2(10),

 

deptno NUMBER(7,2) NOT NULL,

 CONSTRAINT emp_empno_pk PRIMARY KEY (EMPNO))

CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)

             REFERENCES dept (deptno)

           CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99)

);

Ø 修改约束

 

添加：

ALTER TABLE 命令可为一个现有的表添加一个约束，所有约束都是用 ADD来添加，但是为一个列添加NOT NULL 约束，只能使用MODIFY子句，但需注意的是，添加NOT NULL约束时，必须符合以下两个条件中的一个：1.表中没有数据 2.添加约束的目标行没有空值。

ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk

     FOREIGN KEY(mgr) REFERENCES emp(empno);

               删除：

删除

SQL> ALTER TABLE  emp

 2 DROP CONSTRAINT emp_mgr_fk;

 

SQL> ALTER TABLE dept

 2 DROP PRIMARY KEY CASCADE;

   删除primary key ，而添加 CASCADE则表示相关的完整性约束也一并 删除。

禁用/启用

禁用、启用

SQL> ALTER TABLE emp

 2 DISABLE/enable CONSTRAINT   emp_empno_pk CASCADE;

 

Ø 数据字典

USER_CONSTRAINTS

SQL> SELECT constraint_name, constraint_type,

 2      search_condition

 3   FROM   user_constraints

 4   WHERE table_name = 'EMP';

 

USER_CONS_COLUMNS( 查看被约束的column)

SQL> SELECT constraint_name, column_name

 2 FROM    user_cons_columns

 3 WHERE    table_name = 'EMP';

 

Ø 知识点：如果 A表中的a列在B表中拥有一个外键约束，在你删除B表记录之前，你必须先删除A表中的所有子记录。如果你尝试在A表中插入一个记录，而B表中不存在它的副记录的会得到一个违反约束的错误。

 

 

3 、序列

CREATE（ ALTER） SEQUENCE name
[INCREMENT BY n] ；指定序列以n递增，如果没指定，默认值1会使用
[START WITH n] ；由n开始计数
[(MAXVALUE n | NOMAXVALUE)] ；设置最大值n或不设置最大值
[(MINVALUE n | NOMINVALUE)] ；设置最小值n或不设置最小值
[(CYCLE | NOCYCLE)] ；当达到最大值时循环或不循环
[(CACHE n | NOCACHE)] ；预分配缓存大小，默认为20

 

删除序列

drop sequence name

 

CURRVAL伪列用于在当前序列中检索连续序列号，它能用在 UPDATE语句的SET子句中和INSERT语句的VALUES子句中，还可以用在INSERT语句的子查询的SELECT列表中。但不能用于视图的SELECT语句和带HAVING子句的SELECT语句中。

 

4 、视图

a)       建立

     修改一个视图最简单的方法是使用带 OR REPLACE的 CREATE VIEW语句，这允许视图的旧版本被代替，避免了删除视图和重建视图的必要，要是删除了视图，你必须在新视图上重新给对象授权。

create [or replace][force/noforce] view viewname

 as subquery                 //子查询不可以用 order by

[with check option] //则指明只有允许被视图访问的行才能被插入或更新。

[constraint constraintname]

[with read only]     //不可以通过 view进行表的修改

 

b)       对 view的DML操作的限制

可以在 simple view执行DML操作

不可以删除一行如果 view 包含

      group funtion

      a group by clause

      the distinct command

不可以修改数据如果 view包含

      以上说的条件      

由表达式定义的列

       the rownum pseudocolumn

不可以添加数据，如果 view包含：

       以上所有的条件

       存在非空的字段没在包含在 view中

 

c)       数据字典

USER_VIEWS数据字典显示用户所拥有的视图的描述

ALL_VIEWS数据字典显示用户有权访问的视图的描述

ALL_OBJECTS数据字典显示用户有权访问的对象（包括视图）的信息 USER_OBJECTS数据字典显示用户所拥有的对象的描述

5 、序列

CREATE SEQUENCE sequence

      [INCREMENT BY n]

      [START WITH n]

      [{MAXVALUE n | NOMAXVALUE}]

      [{MINVALUE n | NOMINVALUE}]

      [{CYCLE | NOCYCLE}]         

      [{CACHE n | NOCACHE}];         // 序列放在内存中。使读取更快

eg: SQL> CREATE SEQUENCE dept_deptno

 2   INCREMENT BY 1

 3   START WITH 91

 4   MAXVALUE 100

 5   NOCACHE

 6   NOCYCLE;

 

可以在 USER_SEQUENCES 查看所建立的表

SQL> SELECT sequence_name, min_value, max_value,

 2    increment_by, last_number

 3 FROM   user_sequences;

 

•The LAST_NUMBER ：显示nextval的值

 

•Gaps in sequence values can occur when:

– 发生 rollback

– 系统崩溃

– 另一个表在用此序列

 

修改序列

你必须是the sequence. 的 拥有者和 对此the sequence.有ALTER privilege 的权限

如果想改开始值的话只有重建序列

 

6、索引

a)       建立

Ø         自动：当指定表的列为 primary key or union时。系统自动为此表建立一个index

Ø         手动：可以为表的非唯一值的列手动建立 index

Ø         create index indexname on table(column)

 

Ø         数据字典

用户建立的索引在 USER_INDEXES 中(详细的信息)

USER_IND_COLUMNS (列的一些简单信息 )

包含表名。索引名和字段名

SQL> SELECT ic.index_name, ic.column_name,ic.column_position col_pos,ix.uniqueness

 FROM    user_indexes ix, user_ind_columns ic

    WHERE   ic.index_name = ix.index_name

    AND     ic.table_name = 'EMP';

 

 

 

Ø         建立索引的方针

在 where 和join中经常用到的列

列的值范围很大

此列包含很多空值

表很大，但只要其中的 2-4％的数据

很多索引不一定能提高速度。

 

Ø         不建索引的方针

表很小

列不常用来查询

查询结果超过表的 2-4％

表常被更新

 

7 、同义词

建立：create synonym 别名 for 原名

CREATE SYNONYM    d_sum FOR          dept_sum_vu;

 

删除： DROP SYNONYM d_sum;

在创建同义词时，语句中出现 PRIVATE 和OR REPLACE 是无效的

 

 

七、安全

数据库的安全：包括系统安全和数据安全

1.       系统权限 :访问数据库的权限，用于执行数据定义和数据控制命令，和其他数据导向性行为。 CREATE SESSION,CREATE USER, DROP USER, 和 BACKUP ANY TABLE,这些是系统权限

 

2.       用户系统权限 :建立会话,建立表,建立序列,建立视图,建立过程

 

3.       赋权

grant select on tablename to username (public//给所有用户 )

     with grant option;   //被赋予的人就可以把权限再给别人

             GRANT update (dname, loc) ON dept TO    scott, manager;

可以具体到列
GRANT ALL ON inventory TO joe;
这命令使用了关键字 ALL,向joe授予了在inventory表上ALTER, INDEX, INSERT, REFERENCES, SELECT, UPDATE, 和DELETE的权限。

 

4.       消权限

revoke select ,insert on tablename from username

REVOKE references
ON inventory
FROM joe
CASCADE CONSTRAINTS;
这命令实现了两个作用：

1 所有由 joe创建的FOREIGN KEY 约束被删除
2 他创建 FOREIGN KEY约束的能力被取消了。

 

撤消只能相对表不能相对字段

如果撤消了某个用户，那么由这个用户为其他用户赋的权限也一起取消

要是你想通过命令 REVOKE来回收用户A的某一权限，那你必须是当初的授权人。

 

 

 

5.       知识点：

a)       INDEX对象权限可被授予一个用户，但不能授予一个角色（ role）。它允许用户使用CREATE INDEX

b)       对象权限 REFERENCE只能授予用户，不能授予角色（role）。

c)       只有拥有 DBA权限或ALTER UAER权限的用户才能替普通用户修改密码

d)       要能创建用户，你必须拥有 CREATE USER权限。要想有权访问数据库，你需拥有CREATE SESSION权限。要想在任何方案（schema）中创建和删除次表，你必须拥有CREATE ANY TABLE 和DROP ANY TABLE权限，要想在自己的方案（schema）中创建和删除次表，你必须拥有CREATE TABLE 和DROP TABLE权限。

 

e)         如果要删除kate select 表的权限。那么删除者必须是当初给kate赋权限的人

f)         CREATE OR REPLACE ROLE rolename是个无效的命令

              正确的只有CREATE ROLE rolename

 

 

八、事务结束标志

明确的提交：

当 commit or rollback command is issued

暗含的的结束：

ddl command,例如 create,或dcl command例如 grant

发生死锁

退出 sqlplus，系统自动回滚

硬件或系统出错，系统自动回滚

 

commit 后的操作

数据写入数据库

之前的数据会丢失

解除受影响的行的锁

所有的 savepoints被释放

 

savepoint 的用法

2.     update……………

3.     savepoint update

4.     insert into …..

rollback to update