SQL

SQL分类:

DQL: select

DML: insert delete update

DDL: create drop alter

DCL: grant rewoke

DTL: commit rollback savepoint

 

select的七个部分:

表--二维关系表

行(row)--对应一条完整的信息记录

列(column)--一条信息的一个字段

 

Aselect子句

查询某个字段

SELECT salary FROM s_emp;

查询某个几字段

SELECT first_name, salary FROM s_emp;

注意:查询多个字段用逗号分开

!clear ----- clear screan;

*号可以代表所有的字段

SELECT * FROM s_emp;

注意:公司不推荐*号代替,业务含义不明确

SELECT first_name, salary FROM s_emp;

SELECT * FROM s_emp;

如果想显示年薪

对字段进行数学运算 + - * /

SELECT salary*12 FROM s_emp;

可以用别名控制表头显示

(如果想显示成小写,或别名中有特殊字符,可以加双引号处理)

SELECT salary*12 AS year_salary FROM s_emp;

SELECT salary*12 AS "year salary" FROM s_emp;

如果想拼接字符

SELECT last_name || '_' || first_name FROM s_emp;

注意:oracle中表达字符串用单引号

如果是想表达单引号

SELECTlast_name || '''' || first_name FROM s_emp;

null值的处理

SELECT first_name,

salary+salary*12*(nvl(commission_pct,0)/100) AS"salary"

FROM s_emp;

null值要尽早处理,否则后果很严重

nvl(p1, p2)-- 处理null

p1就是要处理的字段或者值

p2如果是null就返回p2的值,不为null则返回p1

重复数据的处理

SELECT DISTINCT last_name FROM s_emp;

通用SQL

SELECT [DISTINCT] [*, column [alias], column [alias], ...]

FROM tablename;

B where子句

WHRER 子句 -- 限制查询的记录

查询的工资大于1000的员工名字

SELECT last_name||first_name FROM s_emp WHERE salary>1000;

数学比较

> < <= >= =

逻辑运算符

and or not

SQL的比较运算符

查询工资在这个区间的[1000,1500]人的名和工资

SELECT last_name, salary

FROM s_emp

WHERE salary>=1000 AND salary<=1500;

or

SELECT last_name, salary

FROM s_emp

WHERE salary BETWEEN 1000 AND 1500;

IN, IS NULL, LIKE

IN(list) 查询id是1, 或3, 或5的id, last_name

SELECT id, last_name FROM s_emp WHERE id IN(1,3,5);

注意:list中数组排放对结果没有影响,对效率可能有影响

因为以1,3,5依次比较,如果5的出现几率较大,

则会多次与1,3比较之后才与5比较,浪费时间,若5在前,则可提高效率

 

LIKE 模糊查询

通配符:

%        0-N个任意字符

_        一个任意字符

user_tables 数据字典表

table_name  数据库中表的名字

找出以S_开头的表名

1,数据字典中的数据都是大写

2,下划线有双重含义

SELECTtable_name

FROMuser_tables

WHEREtable_name LIKE 'S\_%' ESCAPE '\';

补充:

如果有多个条件 条件的优先级可能改变

数据库中也可以用小写括号改变优先级

SQL中的

>                        <=

>=                        <

=                        !=^= <>

not [between and/in/is/like]

and                        or

注：ISNULL的否定：IS NOT NULL

C orderby

D singlefunction

1,对数据进行计算

2,修改非法数据

3,对一组数据进行统一处理(组函数的作用)

4,改变数据类型

5,改变数据的显式格式

 

函数:

组函数:对一组数据处理之后得到一个结果

COUNT()

单行函数:对一个数据处理之后得到一个结果

分类:

UPPER(str)          转换成大写

LOWER(str)   转换成小写

INITCAP(str)把每个单词的首字母大写

COMCAT(str1,str2)  连接字符串

SUBSTR(str,index,num)  截取字符串(*)

SELECTSUBSTR('helloworld',2,3) FROM dual;

注意:起始位置是1

若第二个参数为负数则从后往前数的位置开始

若第三个参数超出范围,则以字符结束为止

LENGTH(str)  求字符串长度

TRIM(str)          去掉首尾空格

REPLACE(str,oldstr,newstr)替换字符串

数组处理函数:

TO_NUMBER(numstr)ORACLE会做隐式类型转换,所以此函数无太大用

ROUND(num)四舍五入

TRUNC(num)截取(丢弃尾数)

格式控制函数:

格式:fm

9:        任意数字

0:        前导0

$:        美元符

l:        local货币符号￥ RMB

,:        国际数字分割符

.:        小数点

to_char(salary,'格式')

SELECTTO_CHAR(salary,'fmL099,999.99') FROM s_emp;

将语言设置为中文:

NLS_LANG='SIMPLFIEDCHINESE_CHINA.ZHS16GBK'

 

为了测试单行函数,提供了一张测试表:dula

SELECTLOWER('abcdef') FROM dual;

 

 

E 多表查询

col name for a15

name 是列名 a15代表宽度

表的连接：

内连接:

a)等值连接

用等号做的表连接

b)非等值连接

不用等号做的连接

c)自连接

外连接:

外连接=内连接+匹配不上的(一个也不能少)

 

SQL99如何表达上式

哪张表发起连接,那么这张表的数据就会被全部匹配出来

 

F 组函数

count()        统计一组数据的个数

max()        求一组数据的最大值

min()        求一组数据的最小值

avg()        求一组数据的平均值

sum()        求一组数据的和

注意:

select sum(distinct salary) from s_emp;

null和组函数的关系:

组函数不对null进行处理

如果不想忽略掉null,应用NVL先处理null值

G 子查询

 

数据库设计:

E-R

实体-关系

#        唯一

*        非空

o        可选

#*        唯一非空

 

主键一个表只有一个,但非空唯一的可以有多个

对象和对象的关系

1:1

1:n

m:n

------- 非强制关系

_______        强制关系

三范式:

第一范式:每个字段不可再分

关系型数据库必需满足第一范式

第二范式:在满足第一范式的基础上,非主属性完全依赖于主属性

第三范式:在满足第二范式的基础上,消除属性之间的传递依赖

 

Oracle的数据类型:

number(m,n) m[1-38]长度 n[-84-127]精度

varchar(n)  n[1-4000]长度

date                日期类型

char(n)                n[1-4000]长度

clob                大字符类型(4G)

blob                大二进制类型(4G)

//复制s_emp表,并复制数据

CREATE TABLE ecps_emp

AS SELECT * FROM s_emp WHERE 1=1;

//复制s_emp表,但只要表结构

CREATE TABLE ecps_emp

AS SELECT * FROM s_emp WHERE 1=2;

 

CREATE TABLE 表名(最大30)

(字段名 类型, 字段名 类型, ...);

 

当查询'abc '时,CHAR可以查出,因为定长在之后补空格

而VARCHAR不能查出,应为不定长不补空格

 

当字段长度不变时,选定长(CHAR),可使得查询效率提高

其他情况一般使用VARCHAR,可以节省空间

 

日期类型:

当前日期的表达:

sysdate        表达当前日期

默认的英文格式: dd-三位月份的英文缩写-yy

默认的中文格式: dd-阿拉伯数字 月-yy

当环境为中文:

'12-SEP-11'        不能插入成功

解决方法:

1,改为'13-9月-11'

2,将环境变量的语言环境变成英文

日期处理函数:

TO_CHAR(日期,'')

yyyy        四位年

mm                两位月

dd                两位天

hh                默认12小时制,hh24表达24小时制

mi                分钟

ss                秒

day                星期几

month        英文全写

mon                英文缩写

 

插入日期类型

TO_DATE('要处理的字符串','日期格式')

ROUND(sysdate,'dd')以天为单位进行四舍五入

TRUNC(sysdate,'mm')以月为单位进行截取

ADD_MONTHS(sysdate,2)

LAST_DAY(sysdate)        这个月的最后一天

NEXT_DAY(sysdate,'FRIDAY')下一个星期几

MONTHS_BETWEEN(d1,d2)        计算两个日期相差几个月

 

SQL的约束:

主键: primary key 对一条记录的唯一标识(非空+唯一,一张表只有一个)

外键: foreign key

非空: not null//只有列级

唯一: unique

检查: check

 

列级约束: 约束直接跟在列后面

CREATE TABLE ed_test_pk (

id NUMBER CONSTRAINT pk_ed_test_pk PRIMARY KEY,

name VARCHAR(30)

);

删除约束:

ALTER TABLE ed_test_pk DROP CONSTRAINT pk_ed_test_pk;

使约束失效:

ALTER TABLE ed_test_pk DISABLE CONSTRAINT pk_ed_test_pk;

使约束生效:

ALTER TABLE ed_test_pk ENABLE CONSTRAINT pk_ed_test_pk;

表级约束:主要解决联合约束问题

CREATE TABLE ed_test_pk (

id NUMBER,

name VARCHAR(30),

CONSTRAINT pk_ed_test_pk PRIMARY KEY(id)

);

如果一个字段要做为另一个表的外键,那么这个表必需唯一

 

ON DELETE SETNULL        删除外键所引用的主键时,相应的外键置空

ON DELETECASCADE        删除外键所引用的主键时,相应的外键所在记录被删除

 

INSERTINTO...VALUES

将查询出的结果插入到表中作为一条记录

INSERT INTO s_emp(id, first_name, last_name)

SLELCT MAX(id),MAX(name),MIN(name) FROM s_dept;

UPDATE..SET..

UPDATE s_emp SET first_name='a', last_name='b' WHERE id=1;

DELETEFROM...WHERE

 

DML语言需要commit才能提交,否则使用rollback回滚

而DDL语言是自动提交的

 

序列:

一个自动增长的数字对象

一个共享对象

比较典型的应用是做表的主键

可以减少我们的程序代码

可以通过缓存加速主键的生成

CREATE SEQUENCE ed_s;

SELECT ed_s.NEXTVAL FROM dual;

SELECT ed_s.CURRVAL FROM dual;

一般一个表一个SEQUENCE

 

CREATE SEQUENCE ed_s

START WITHn                        默认值为1

INCREMENT BYn                        默认值为1

MAXVALUE/NOMAXVALUEn        默认是pow(10,27)

MINVALUE/NOMINVALUEn        pow(10,-28)

nocycle/cycle                        默认是nocycle,如果有循环回到默认的1

nocache/cachen                        默认cache20

 

索引:

rowid: 伪列

是供DBMS底层查找使用的,是一条记录的物理位置

 

CREATE INDEX ed_index ON s_emp(dept_id);

DROP INDEX ed_inedx;

 

SET AUTOTRACE ON 打开跟踪

UNIQUE的字段是自动创建索引

 

视图:

CREATE VIEW ed_view AS SELECT sname FROM ed_student;

简单视图可以增删改源数据

复杂视图限制很多:

GROUP BY, DISTINCT ,组函数, NOT NULL 未被选的, 表连接

满足上面一条的就是复杂视图

 

如果按某个字段排序,取第n夜的数据时,必需三层嵌套

要先排序,后编号

 

Oracle相关的编程技术:

JDBC SQLJ

OCI                        Oracle底层的通讯接口

ODBC

PL/SQL                过程化的SQL

PROC/C++        用C/C++这种高效语言访问数据库

 

PL/SQL:

声明区 declare ... begin

执行区 begin ...

异常区 exception ...

 

变量的声明:在声明区定义

constant 常量

not null 必需有初值

:=                赋值

=                代表相等(==)

如果没有赋初值,任何变量都是null

例: DECLARE

var_n CONSTANT NUMBER NOT NULL :=100;

注释:

单行注释:--

多行注释:/* ... */

数据类型:

标量类型:number char varchar2 date boolean

组合类型:record table

大数据类型:blob, clob

引用类型:ref

%type 取得对应字段的类型:

var_name s_emp.first_name%type;

 

RECORD类型:

record类型的变量可以直接相互赋值,也可以逐个字段赋值

DECLARE

    TYPE reco IS RECORD (

var_no dept.deptno%type,

    var_namedept.dname%type

    ); 

    var_record reco;

    var_reco2 reco;

BEGIN

    SELECT deptno,dnameINTO var_record FROM dept WHERE deptno=10;

    var_reco2 :=var_record;

dbms_output.put_line

(var_record.var_no ||',' || var_record.var_name);

           dbms_output.put_line

(var_reco2.var_no ||',' || var_reco2.var_name);

END;

 

取一行的类型:%rowtype

DECLARE

var_dept dept%rowtype;

BEGIN

SELECT * INTO var_dept FROM dept WHERE deptno=20;

DBMS_OUTPUT.PUT_LINE(var_dept.deptno || ',' || var_dept.dname);

END;

 

TABLE类型:

first(): 第一个元素下标

last():  最后一个元素下标

next(n); key=n的元素的下一个元素坐标

DECLARE

TYPE table_dept IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

var_nums table_dept;

BEGIN

var_nums(1) := 100;

var_nums(20) := 500;

var_nums(101) := 1000;

DBMS_OUTPUT.PUT_LINE(var_nums(1));

DBMS_OUTPUT.PUT_LINE(var_nums(20));

DBMS_OUTPUT.PUT_LINE(var_nums(101));

END;

 

如果装的类型是字符串,那要指明长度

不指明长度,就只能存取1个字符

 

只有 DML 和 DTL 可以直接在PL/SQL中使用 注意SELECT

DDL需要特殊的语法

之前学过的所有的内置函数可以直接在PL/SQL中使用

 

PL/SQL中不支持BOOL类型输出

 

CURSOR:

四步:

1,声明CURSOR

CURSOR cursor_ed IS SELECT * FROM emp;

2,打开CURSOR

OPEN cursor_ed;

3,提取所需元素

FETCH cursor_ed INTO var_emp;

4,关闭CURSOR

CLOSE cursor_ed;

FOR循环中的REVERSE,对CURSOR无影响

 

DECLARE

    var_dept dept%ROWTYPE;

    CURSOR cursor_dept ISSELECT * FROM dept;

BEGIN

    OPEN cursor_dept;

    FETCH cursor_dept INTOvar_dept;

    WHILEcursor_dept%FOUND LOOP

       DBMS_OUTPUT.PUT_LINE(var_dept.deptno||', '||var_dept.dname);

        FETCH cursor_deptINTO var_dept;

    END LOOP;

    CLOSE cursor_dept;

END;

可以给CURSOR传参数:

CURSOR cursor_dept(no NUMBER) IS SELECT * FROM dept WHEREdeptno>no;

OPEN cursor_dept(20);

注意:

参数定义中不能有()来指明类型长度,如NUMBER(5,2),

但可以通过%TYPE来变相指明

FOR UPDATE在查询时锁定查询的记录集

EXCEPTION:

一般异常处理过程:

1,自定义异常

2,根据实际情况抛出异常 RAISE

3,异常处理块中处理异常

1)回滚之前的事务

2)释放资源

3)记录日志

一旦发生系统异常,代码直接转到EXCEPTION块,

WHEN就是捕捉相应异常,以作相应处理

SQLCODE:代表一条sql的执行情况,0表示成功

SQLERRM:代表异常的相关信息

PROCEDURE

定义:

CREATE OR REPLACE PROCEDURE hello

IS

BEGIN

END;

执行:exec procedure_name;

若PROCEDURE有参数,desc procedure_name可以查看参数

in参数可以是常量,也可以是变量,但不能在过程中赋值

out参数必需是变量,可以赋值

in out参数必需是变量,可以赋值

 

过程的赋值:

1,顺序赋值--位置参数

2,名字赋值

3,混合赋值,但第一个参数必需是位置赋值

注:

如果参数都是in参数

exec 两种赋值均支持

call 不支持名字赋值

在编译PROCEDURE时,出现警告则使用show errors

 

FUNCTION

CREATE OR REPLACE FUNCTION hello

RETURN NUMBER IS

BEGIN

...

RETURN ...;

END;

函数和过程的区别:

函数也返回值,过程没有

函数是表达式的一部分,过程是PL/SQL语句的一部分(函数若有返回值不能直接调用,而过程没有返回值所以可以)

 

PACKAGE:

将相关的函数过程,变量,类型等组织在一起的对象

DBMS_OUTPUT 输出包

DBMS_RANDOM 随机包

DBMS_JOB        定时任务包

生成0-10的数:

SELECT DBMS_RANDOM.value(0,10) FROM dual;

1.建一个序列 

 

           create sequencetest_r_s;

 

  2.建一张表 id  name money

 

       create table test_moni(

 

        id number primary key,

 

name varchar(30),

 

money  number

 

                  );  

 

                 3.写一个存储过程有一个参数name

 

                   向数据库中存入一条数据id 通过序列

 

           name是传入的参数money是1-100的随机整数

 

          create  or replace procedure insert_data

 

           is

 

           begin

 

       insert into test_moni values

 

               (test_r_s.nextval,

 

'test'||trunc(dbms_random.value(0,100)),

 

trunc(dbms_random.value(0,100)));

 

              commit;

 

           end;

 

           callinsert_data('zhangsan');

 

                 4.定时调用这个存储过程

 

           declare

 

            jobno binary_integer;

 

            begin

 

           dbms_job.submit(jobno,'insert_data();',

 

           sysdate,'sysdate+1/(24*30)');

 

           dbms_job.run(jobno);

 

          dbms_output.put_line(jobno);

 

           commit;

 

           end;

 

   

 

          dbms_job.remove(jobno);

 

定义:

1,包头部分

CREATE OR REPLACE PACKAGE test_pack IS

FUNCTION  test_fun  RETURN NUMBER; --函数申明

 

      PROCEDURE test_pro;--过程申明

END test_pace;

2,包体

CREATE OR REPLACE PACKAGE BODY test_pack IS

 

             FUNCTION test_fun RETURNNUMBER IS

 

             BEGIN

 

             RETURN 1;

 

             END;

PROCEDURE test_pro IS

 

             BEGIN

 

             DBMS_OUTPUT.PUT_LINE('hellopackage body');

 

             END;

 

          END test_pack;

触发器:

1,更加完备的数据完整性解决方案

2,跟踪用户操作,审计

3,可以向其他程序发信号

 

DML 3

before/after 2

row/statement 2

 

定义:

CREATE OR REPLACE TRIGGER up_dept BEFORE UPDATE ON dept

BEGIN

DBMS_OUTPUT.PUT_LINE('update dept');

END;

 

FOR EACH ROW

这个触发器影响多少条 就触发多少次

 

                     如果没有不管触发器影响多少行 只触发一次