概述
提到关系型数据库,就不得不先了解一下以下两个概念:
关系: 描述两个元素的关联或对应关系。使用关系模型把数据组织到二维数据表(Table)中。
表的概念: 一个关系数据库由多个数据表(Table)组成,数据表是关系数据库的基本存储结构。表是二维的,由行和列组成。表的行(Row)是横排数据,也被称作记录(Record)。表的列(Column)是纵列数据,也被称作字段(Field)。表和表之间存在关联关系。
Oracle是著名的Oracle(甲骨文)公司的数据库产品。Oracle是世界上第一个商品化的关系型数据库管理系统。Oracle采用标准SQL(结构化查询语言),支持多种数据类型,提供面向对象的数据支持,具有第四代语言开发工具,支持UNIX、WINDOWS、OS/2等多种平台。Oracle公司的产品丰富,包括Oracle服务器、Oracle开发工具和Oracle应用软件。
1.sql语言的分类
(1)数据定义语言(DDL ): Data Definition Language
用于建立、修改、删除数据库对象。
数据库对象包括:表、视图、索引、序列。
包括:
CREATE :创建表或其他对象的结构
ALTER :修改表或其他对象的结构
DROP:删除表或其他对象的结构
TRUNCATE:删除表数据,保留表结构
(2) 数据操纵语言(DML): Data Manipulation Language
用于改变数据表中的数据。和事务相关,执行完后需要经过事务控制语句提交后才能真 正的将改变应用到数据库中。
包括:
INSERT:将数据插入到数据表中
UPDATE:更新数据表中已存在的数据
DELETE:删除数据表中的数据
(3) 事务控制语言(TCL): Transaction Control Language
用来维护数据一致性的语句。
包括:
COMMIT:提交,确认已经进行的数据改变
ROLLBACK:回滚,取消已经进行的数据改变
SAVEPOINT:保存点,使当前的事务可以回退到指定的保存点,便于取消部分改变
(4)数据查询语言(DQL): Data Query Language
用来查询所需要的数据。
SELECT语句
(5)数据控制语言(DCL): Data Control Language
用于执行权限的授予和收回操作。
包括:
GRANT:授予,用于给用户或角色授予权限
REVOKE:用于收回用户或角色已有的权限
CREATE USER:创建用户
2、常见的数据类型
❤️ 数据库中所有数据类型的默认值都是NULL,所以在取值的时候在0和null中要做好判断,a=0 a is null而不能a=null。
❤️在判断某个字段的值是否等于某个值时或者用于计算时,不能直接 a=0,因为a的值可能为null,会导致丢失为null的记录;应该使用nvl(a,0),表示当a的值为null时转换为0,然后在进行判断
(1)NUMBER
NUMBER表示数字类型。经常被定义成NUMBER(P,S)形式,其中:
P:表示数字的总位数
S:表示小数点后面的位数
eg:
sal NUMBER(6,2)
表示sal列中的数据,整数位最大为4位,小数位最大为2位,也就是最大取值:9999.99
(2)CHAR
表示固定长度的字符类型。经常被定义成CHAR(N)形式,N表示占用的字节数。最大长度是2000字节。
eg:
ename CHAR(20)
表示ename列中最多可储存20个字节的字符串,并且占用的空间是固定的20个字节。
(3)VARCHAR2(Oracle特有的数据类型)
表示变长的字符类型。定义格式是VARCHAR2(N),N表示最多可占用的字节数。
最大长度是4000字节。
eg:
job VARCHAR2(100)
表示job列中最多可存储长度为100个字节的字符串。根据其中保存的数据长度,占用的空间是变化的,最大占用空间为100个字节。
(4)DATE
用于定义日期时间的数据。长度是7个字节,默认格式是:DD-MON-RR,例如:11-APR-17
eg:
hiredate DATE
表示hiredate列中存放的是日期数据。
3、DDL语句
3.1 创建表
(1)CREATE语句(创建表)
eg:
CREATE TABLE employee(
id NUMBER(4),
name VARCHAR2(20) NOT NULL,
gender CHAR(1) DEFAULT 'M',
birth DATE,
salary NUMBER(30),
job VARCHAR2(30),
deptno NUMBER(2)
);
(2)DESC语句(查看表类型)
eg:
DESC employee;
(3)DEFAULT语句
在创建表的时候可以使用DEFAULT为某个字段单独指定一个默认值。
例如,给gender列赋默认值‘M’,如果没有指定性别的员工,默认是男性
❤️ 注意:
1.数据库中的字符串字面量是使用单引号的。
2.虽然SQL语句本身不区分大小写,但是字符串的值是区分大小写的。
(4)NOT NULL 语句
❤️ 注意:
1.非空(NOT NULL)是一种约束条件,用于确保字段值不为空
2.默认情况下,任何列都允许有空值
3.当某个字段被设置了非空约束条件,这个字段中必须存在有效值
4.当执行插入数据的操作时,必须提供这个列的数据
5.当执行更新操作时,不能给这个列的值设置为NULL
3.2 修改表
3.2.1 修改表名
RENAME employee TO myemp
3.2.2 修改表结构
(1) 添加新的字段
给表增加列可以使用ALTER TABLE 的ADD子句实现
ALTER TABLE myemp
ADD(
hiredate DATE DEFAULT SYSDATE
)
❤️ 注意:
列只能增加在最后,不能插入到现有的列中。
(2)修改现有字段
建表之后,可以改变表中列的数据类型、长度、默认值和是否为空。
语法如下:
ALTER TABLE myemp
MODIFY (
job varchar2(40) DEFAULT 'CLERK'
)
❤️ 注意:
修改表结构都应当避免在表中有数据以后进行,若表中有数据,修改表中字段时尽量不要修改类型,若修改长度尽量增大避免缩小,否则可能导致失败。
(3)删除现有字段
使用ALTER TABLE 的子句DROP子句删除不需要的列。
语法如下:
ALTER TABLE myemp
DROP(
hiredate
)
❤️ 注意:
删除字段需要从每行中删掉该字段占据的长度和数据,并释放在数据块中占据的空间,如果表记录比较大,删除字段可能需要比较长的时间。
3.3 DML语句
这里涉及到的是最基本的增删改查
3.3.1 INSERT语句
给数据表里增加记录。
语法如下:
INSERT INTO myemp
(id,name,salary,deptno)
VALUES(1,'jack',5000,10)
SELECT * FROM myemp
COMMIT
❤️ 注意:
执行DML操作后,需要再执行 COMMIT语句,才算真正确认了此操作。
考虑到格式原因,要进行相应的转换:
/*
使用自定义日期格式插入记录,使用to_date('',format:'')
*/
INSERT INTO myemp
(id,name,job,birth)
VALUES(
1003,'donna','MANAGER',
TO_DATE('2009-09-01','YYYY-MM-DD')
)
3.3.2 UPDATE语句
修改表中数据
修改表中数据要添加WHERE过滤条件,这样才会只将满足条件的记录进行修改,否则是全表所有的数据都修改。一次也可修改多个字段。
UPDATE myemp
SET salary=6000, name='rose',gender='F'
WHERE id=1
3.3.3 DELETE语句
删除表中数据,删除数据通常也要添加WHERE语句来限定要删除数据的条件
否则就是清空表操作。
eg:
DELETE FROM myemp
WHERE name='rose'
DESC myemp
❤️ 注意:
如果没有WHERE子句,则全表的数据都会被删除。
在DDL语句中的TRUNCATE语句,同样有删除表数据的作用。
和DELETE语句的区别:
(1)DELETE可以有条件删除,TRUNCATE将表数据全部删除。
(2)DELETE是DML语句,可以回退,TRUNCATE是DDL语句,立即生效,无法回退。
(3)如果是删除全部表记录,且数据量较大,DELETE语句效率比TRUNCATE语句低。
二者都可以删除全部整张表的记录,TRUNCATE小心使用
DELETE FROM myemp;
或者
TRUNCATE TABLEmyemp;
tip1:(一些小函数)
last_day(hiredate):表示hiredate日期中月份的最后一天
add_months(trunc(sysdate,'YYYY'),12):明年的第一天,也可以作为该年的最后一刻
trunc(sysdate,'yyyy')-1:最开始减一天,就是去年最后一天
ADD_MONTHS(sysdate, -12):表示去年的今天,因为月份-12
substr(ename,1,3):表示ename的前三个字符,从1开始而不是0
length(ename):表示ename的字符长度
extract(year from sysdate):只能从一个date类型中截取年月日,这里表示截取年;
initcap(ename):表示将ename的首字母大写,其他的小写
replace(ename,'A','a'):用a替换ename中的所有A
trunc(sal/30,0):表示余数为零,就是取整,也可以选择保留几位小数。
trunc(sysdate,'yyyy'):表示日期取到year,其余的时间显示最开始的时间,2020/01/01 00:00:00
count(empno):empno的个数
sum(empno):empno的和
round(a,0):表示对a进行四舍五入
负数表示从整数位开始四舍五入即:在小数点左侧进行四舍五入(负几就从整数的第几位开始四舍五入);ROUND(-903.53567,-1)=-900;ROUND(-903.53567,-3)=-1000
0表示四舍五入到最接近的整数;
正数表示四舍五入到指定的小数位;
tip2:(一些注意的点)
1.排序有多个字段时,先按第一个排序,然后有相同的记录时,再按第二个进行排序
2.格式转换
TO_CHAR(number, '格式')
TO_CHAR(salary, '$99,999.99')
TO_CHAR(date, '格式')
TO_DATE 把字符串转换为数据库中的日期类型
TO_DATE(char, '格式')
TO_NUMBER 将字符串转换为数字
TO_NUMBER(char, '格式')
3.as后的命名需要使用“”双引号,而不是单引号