Orale数据库基础笔记

第一章、数据库介绍

常见数据库对象

1.  表：用来存储用户数据对象，由行和列构成
2. 约束：保证数据完整性，建立在表的单个字段或多个字段的组合上，这些列的数据必须满足约束条件
3. 索引：构建与表的单个或多个字段的组合上，用于加速查询
4. 视图：虚表，是一个命令的查询。简化查询，不存储数据
5. 序列：用于产生顺序的不重复的字符串，被作为主键约束值的参照
6. 函数：用于进行复杂计算的PL/SQL函数，返回一个计算结果
7. 过程：用于完成某种特定功能的PL/SQL程序
8. 包：一组相关的函数和存储过程的命名集合

第二章、数据库基本访问语句

SQL语句分类

1. 数据操作语句（ Data Manipulation Language)：
   DML语句包括：select 、insert、delete、update  
2. 数据定义语句  （Data Definition Language) ：
   DDL语句包括：create、alter、drop 、truncate
3. 事务控制语句（Transaction Control Language)
   TCL语句包括：commit、rollback、savepoint
4. 数据控制语句（Data Control Language)：
   DCL语句包括：grant、revoke

SQL中常用数据类型

1. char:定长字符串，长度不足的，会以空格填充达到最大长度,例如char(10),总是包含10个字节，不足的以空格填充。char最大长度为2000字节
2. varchar2: 变长字符串,于char不同，不会用空格填充到最大长度，目前于varchar类型完全相同，最大长度4000
3. number:最多达38位数字，number(24,4),表示最多24位数字，其中小数部分最多4位，整数部分最多20位 date:日期类型，精确到秒
4. long:最多存储2GB的字符数据,遗留类型，推荐使用clob类型代替 

DDL语句用途及语法

 create语句用法：创建数据库对象，表或者视图

1、直接对表进行创建

CREATE TABLE SIE_1 (
    employee_id   NUMBER(10) PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    email         VARCHAR2(100),
    hire_date     DATE,
    salary        NUMBER(10, 2),
    department_id NUMBER(10)
);

2、将其他表的内容放在这个新创建的表里

CREATE TABLE SIE_2 AS SELECT * FROM EMP;
//将表结构和数据一起给新表
CREATE TABLE SIE_3 AS SELECT * FROM EMP where 1=2;
//只将后面表的表结构给这个新建的表

3、创建视图的基本语法：

create [or replace] view view_name as
             select columns from table where 	predicates;
//使用了 OR REPLACE，则表示如果该视图已经存在，则将其替换
//WHERE predicates: 可选项，用于指定过滤条件（谓词），仅选择满足条件的行。

alter语句的用法

1、增加删除字段

ALTER TABLE SIE_3 DROP column EMPNO;
ALTER TABLE SIE_3 add (empo VARCHAR2(50));
COMMIT;
//注意这种语句操作之后需要提交操作，并且在增加的时候需要指定列的数据类型

2、修改表结构基本语法

ALTER TABLE SIE_3 RENAME COLUMN ENAME TO NAME;
//修改ename变成name
ALTER TABLE SIE_3 MODIFY(name decimal(10,2));
//修改

3、增加约束

ADD CONSTRAINT constraint_name CONSTRAINT_TYPE (column1, column2, ...)
table_name：要添加约束的表的名称。
constraint_name：约束的名称，应为唯一标识符。
CONSTRAINT_TYPE：约束的类型，例如 PRIMARY KEY、FOREIGN KEY、UNIQUE 等，根据你的需求选择适当的类型。
(column1, column2, ...)：约束所涉及的列的列表。

 drop语句：删除数据库对象

truncate语句：删除表中所有数据并释放所占用空间，不需要提交也不能回滚

DROP TABLE SIE_3;
DROP VIEW view_name;
TRUNCATE TABLE SIE_3;

COMMENT语句用途：给表或表的字段加备注信息

   给表加备注语法：
   comment on table table_name is ‘备注内容’;

   给表中某个字段加备注语法：
   comment on column table.column_name is      
   '备注内容';

RENAME语句用途：

  重命名表名语法：
   RENAME old_name TO new_name

总结：

1. DDL语句会隐式提交，实质是先COMMIT，再DDL语句；
2. 创建主键约束、唯一约束时会默认创建唯一索引；
3. 主键约束的字段（或字段组合）不允许NULL值；
4. 唯一约束的字段允许NULL值； 表中新增字段会出现在表列的最后面；
5. 实际开发中一般不创建CHECK、FOREIGN KEY约束；
6. 创建表时业务中常为NULL值的列一般放在最后；
7. 索引并不是越多越好，索引即有优点又有缺点；
8. 养成好的命名习惯、养成使用别名的习惯。 

DML语句的用途以及基础语法

select语句：用于查询的基本语句

insert语句：用于插入

SELECT语句语法：
SELECT columns 
   	   FROM tables
             [WHERE predicates];

INSERT语句语法：
	INSERT INTO table 
                       (column-1, column-2, ... column-n)
                       VALUES
                       (value-1, value-2, ... value-n);



SELECT * FROM SIE_3;
INSERT INTO SIE_3 (id, name, age) VALUES (1, 'John Doe', 25);

update语句：用于更新表数据

delete语句：用于删除数据

UPDATE语句语法： 
	UPDATE table 
            SET column1 = value1,column2 = value2… 
    [ WHERE predicates];


DELETE语句语法： 
   DELETE  FROM tables 
   [WHERE predicates ];



UPDATE SIE_3
SET age = 30
WHERE id = 1;

DELETE FROM SIE_3
WHERE id = 1;

总结：

1. select语句是难点，需要多练习，常用select语句的类型有：单表查询、多表查询、子查询、集合操作、树结构查询、聚合函数、分析函数、自定义函数；
2. Insert、delete、update三类改动要想存入数据库中必须要使用commit语句，放弃改动时要使用rollback语句；
3. Insert时注意数据类型、唯一约束、非空约束等 ; delete、update使用时，一定要确保WHERE条件的正确性；
4. 注意NULL值，判断空用IS NULL ，判断不空用 IS NOT NULL。

TCL语句用途用法：

1. COMMIT语句用途： 提交事物。在一系列DML后，向数据库提交事务，在执行COMMIT前所有事务按执行先后顺序可见 ，执行COMMIT后，所有DML操作最终效果才会生效并保持至数据库中。
   COMMIT语句语法：     commit;
2. ROLLBACK语句用途： 回滚事物。显式回滚数据库事务，所有DML操作都回滚至最近一次commit后。
   ROLLBACK语句语法：     ROLLBACK;
3. savepoint语句用途：
   实现部分回滚，回滚至某个设置点。        
   savepoint A; --设置回滚点          
   rollback to A;--回滚至设置点

总结

1. 改动数据库中的数据后，一般使用COMMIT显示提交，使用ROLLBACK显示回滚；
2. 注意DDL语句会隐式提交（COMMI）；
3. TCL语句频繁出现会降低效率；
4. 一个事务的开始标志是INSERT、UPDATE、DELETE    语句的出现；
5. 一个事务的结束标志是COMMIT、         ROLLBACK的出现；     ROLLBACK  TO不会结束事物。

DCL语句：授权和取消权限

 grant授权：

授指定表一个权限，给指定用户sie
	grant select on emp to sie;

	授指定表多个权限，给指定用户
	grant select,insert,update on emp to sie;

          	授指定表全部权限，给指定用户
	grant all on emp to sie;

	授权指定表指定权限，给所有用户
	grant select on emp to public;

revoke收回权限：

从指定用户收回指定表一个权限
revoke select on emp from sie;

从指定用户收回指定表多个权限
revoke insert,update,delete on emp from sie;


                      从指定用户收回指定表全部权限
revoke all on emp from sie;

从所有用户收回指定表指定权限
revoke select on emp from public;

 总结：

新创建的用户么有任何权限，登录数据库的权限也没有，所以创建一个新的用户至少赋予的权限有

 connect,resource,unlimited tablespace

第三章、单表查询语句

select语句：具体不展开

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ AS output_name ] [, ...]
    FROM from_item [, ...]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
    [ FOR UPDATE [ OF class_name [, ...] ] ]

 第四章、常用单值函数

4.1、字符操作函数

UPPER(STR)：将字符串全部转换为大写
LOWER(STR):将字符串全部转换为小写

例：SELECT UPPER('WhaT is tHis') 
           FROM DUAL;


例：SELECT LOWER('WhaT is tHis') 
           FROM DUAL;

 substr（c1，n1，【n2】）截取指定长度的字符串

第一个参数：字符串

第二个参数：起始位置，从1开始数，负数则从右往左书

第三个参数：截取长度，为空则表示截取到末尾

SELECT SUBSTR('What is this',5,3) S1,
 	           SUBSTR('What is this',-5,3) S2
      	FROM DUAL;

length（str）:返回字符串长度
INSTR(c1,c2[,n1[,n2]]) 返回c2在c1中位置

第一个参数： c1原字符串

第二个参数：c2要寻找的字符串     

第三个参数：n1起始位置，比如3表示左面第3处开始往后查找,-3    表示右面第3处开始往后查找。     

第四个参数：n2:第几个匹配项，比如查找的有多个，选第几个

ltrim(str),去掉字符串左边空格

rtrim(str),去掉字符串右边空格

trim(str),字符串左右两边空格都去掉

lpad(str,n,c)：    将str补足为n位长度,不足左边用字符c代替

rpad(str,n,c)：    将str补足为n位长度,不足右边以字符c代替

 4.2、数值函数

round(col,n) 四舍五入

round(457.628,2),小数点后2位四舍五入
      		结果 457.63

round(457.628,-1),小数点前1位四舍五入
      		结果460

 TRUNC(n1[,n2]) 截断数值

返回截断到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截断为整数，

如果n2为负值，就截断在小数点左边相应的位上。 

trunc(457.628,2)  结果457.62
	trunc(457.628,-1) 结果450

4.2、日期函数

SYSDATE，系统当前时间

months_between(date1,date2)

add_months(date,m)

trunc,截断日期

 截断函数可以用于截断数值和日期，截断日期的时候选取1号作为补充值，比如截断年就是某年一月一号，月就是某年某月1号

select trunc(sysdate,'YYYY') T1,
                   trunc(sysdate,'MM') T2, 
                   trunc(sysdate) T3 
          from dual;

last_day ,返回一个月的最后一天

to_char(date1,’format_model’)：日期转为字符

 to_date('2007-11-11','YYYY-MM-DD')：字符串转换日期

select to_char(sysdate,'YYYYMMDD HH24:MI:SS') R 
 from dual;

 select  to_char(55676,‘fm99,999.00’)  
 from dual;

nvl（c1，c2）：控制处理函数，如果c1是空值则转换为c2

SELECT NVL(null, '12') 
        FROM DUAL;