Oracle数据库基础一：函数+DDL+DML

最新推荐文章于 2024-07-22 20:03:02 发布

Myzoneliver

最新推荐文章于 2024-07-22 20:03:02 发布

阅读量626

点赞数

分类专栏：培训班

培训班专栏收录该内容

23 篇文章 0 订阅

订阅专栏

SQL语句本身不区分大小写，但是字符串的值是区分大小写的
SQL字符串用单引号’ ’

#基本概念：
DB：Database
DBMS:Database Management System
DBA:Database Administrator
一个关系数据库由多个数据表组成，数据表是关系数据库的基本存储结构，由行和列组成，行（Row）也就是横排数据，也经常被称作记录(Record)，列（Column）就是纵列数据，也被称作字段(Field)。表和表之间是存在关联关系的。

##常用数据类型

NUMBER
NUMBER表示数字类型，经常被定义成NUMBER（P，S）形式，其中：
P表示数字的总位数
S表示小数点后面的位数
CHAR
CHAR表示固定长度的字符类型，经常被定义成CHAR（N）形式, N表示占用的字节数，N的最大取值是2000。也可以CHAR(N CHAR),其中最多保存2000个英文字符，1000个汉字（GBK）。
VARCHAR2
VARCHAR2表示变长的字符类型，定义格式是VARCHAR2（N）, N表示最多可占用的字节数，N的最大取值是4000。也可以VARCHAR(N CHAR)最多保存4000个英文字符，2000个汉字（GBK）。

CHAR如果不指定长度，默认为1个字节， VARCHAR2必须指定长度。

DATE
DATE用于定义日期时间的数据，长度是7个字节，默认格式是：DD-MON-RR, 例如：“11-APR-71”。如果是中文环境，是“11-4月-71”这种形式。
LONG和CLOB类型
LONG类型可以认为是VARCHAR2的加长版，用来存储变长字符串，最多达2GB的字符串数据，但是LONG类型有诸多限制，所以不建议使用
CLOB用来存储定长或变长字符串，最多达4GB的字符串数据，ORACLE建议开发中使用CLOB替代LONG类型
#函数
##字符串函数
CONCAT(char1,char2)：连接字符串char1,char。必须两个字符！
- SELECT CONCAT(ename,sal) FROM emp
- SELECT CONCAT(CONCAT(ename,’,’),sal) FROM emp
"||"也是用来连接字符串使用的，方便连接多个字符
SELECT ename||’,’||sal FROM emp
LENGTH(char)：查看给定字符串的长度
SELECT ename,LENGTH(ename) FROM emp
UPPER,LOWER,INITCAP
将字符串转换为全大写，全小写，首字母大写
dual:伪表，当查询的数据与任何表中数据无关时，可以在FROM子句中使用伪表。
- SELECT UPPER(‘helloworld’),LOWER(‘HELLOWORLD’),
  INITCAP(‘HELLO WORLD’) FROM dual
- SELECT ename,sal,deptnoFROM emp
  WHERE ename=UPPER(‘smith’)
TRIM,LTRIM,RTRIM
去除字符串两边的指定重复字符，或单独去除左边，单独去除右边。
SELECT TRIM(‘e’ FROM ‘eeeeliteee’)FROM dual
LTRIM,RTRIM删除方式是从左或右，只要字符是第二个参数
指定的字符之一就去除。
SELECT LTRIM(‘efsfefsfffffesefsliteee’,‘fes’) FROM dual
LPAD,RPAD：补位函数
LPAD(char1,n,char2)：要将char1显示n位长度，若不足则在左侧补充若干个char2字母以达到该长度。char2字符必须是单一的一个字符。若char1超过n位，则从左开始截取到n位。
SELECT ename,RPAD(sal,6,’$’)FROM emp
SUBSTR(char,m[,n])
截取字符串char,从第m个字符开始截取若n不指定，或n超过实际可以截取的长度是，则表示截取到字符串末尾。n为截取的字符长度。需要注意，在数据库中下标是从1开始的！
m若为负数，则是从倒数位置开始截取。
SELECT SUBSTR(‘thinking in java’,-7,2) FROM dual
INSTR(char1,char2[,m[,n]])
查找char2在char1中的位置。m,n不指定默认值都是1,。
m表示从第几个字符开始查找。n表示第几次出现。
SELECT INSTR(‘thinking in java’,‘in’,4,2) FROM dual

##数值函数

ROUND(m,n)
对m进行四舍五入，保留小数点后n位。若n为0则是保留到整数位。若n为负数，则是保留十位以上的位数。
SELECT ROUND(45.678, 2) FROM DUAL：45.68
SELECT ROUND(45.678, 0) FROM DUAL：46
SELECT ROUND(45.678, -1) FROM DUAL：50
TRUNC函数
直接截取数字，参数意义与ROUND一致
SELECT TRUNC(45.678, 2) FROM DUAL：45.67
SELECT TRUNC(45.678, 0) FROM DUAL：45
SELECT TRUNC(45.678, -1) FROM DUAL：40
MOD(m,n)
求余数，计算是依据m除以n。n若为0，函数直接返回m的值
SELECT MOD(13,4) FROM dual
CEIL,FLOOR
向上取整与向下取整
CEIL:返回大于参数的最小整数
FLOOR:返回小于参数的最大整数
SELECT CEIL(45.678) FROM dual
SELECT FLOOR(45.678) FROM dual

##日期转换函数

在日期格式中，凡不是英文和符号的其他字符都需要使用双引号括起来.

TO_DATE()
将给定的字符串按照指定的日期格式解析为一个DATE类型数据
SELECT TO_DATE(‘2008年08月08日 20:08:08’,
‘YYYY"年"MM"月"DD"日" HH24:MI:SS’)FROM dual
TO_CHAR()
将一个DATE按照指定的日期格式转换为字符串
TO_CHAR也可以将数字转换为字符串，但是常用的就是将日期进行相应转换。
SELECT TO_CHAR(SYSDATE,‘YYYY-MM-DD HH24:MI:SS’)
FROM dual
日期可以进行计算:
两个日期之间相减，差为相差的天数。对一个日期加减一个数字结果等同加减了天数
查看每个员工入职至今过多少天?
SELECT ename,SYSDATE-hiredate
FROM emp
根据生日查看到今天为止活了多少天?
SELECT SYSDATE - TO_DATE(‘1992-06-05’,‘YYYY-MM-DD’)
FROM dual
查看100天以后是哪天?
SELECT SYSDATE+100
FROM dual
RR也是用两位数字表示年，与YY的不同之处在于，当使TO_DATE函数将一个字符串中两位数字转换为对应年的时候YY与RR区别是YY用当前系统时间作为实际，而RR会根据该数字与当前系统时间的世纪进行相应判断来决定，具体如下图：
- SELECT TO_CHAR( TO_DATE(‘87-08-02’,‘YY-MM-DD’),
  ‘YYYY-MM-DD’ )FROM dual //2087年
- SELECT TO_CHAR(TO_DATE(‘87-08-02’,‘RR-MM-DD’),
  ‘YYYY-MM-DD’ )FROM dual //1987年

##日期常用函数

LAST_DAY(date)：返回给定日期所在月的月底日期
当月月底是哪天？
SELECT LAST_DAY(SYSDATE) FROM dual
ADD_MONTHS(date,i)
对给定日期加指定的月。若i为负数则是减去指定的月。
查看每个员工入职20周年纪念日是哪天?
SELECT ename,ADD_MONTHS(hiredate,12*20)
FROM emp
MONTHS_BETWEEN(date1,date2)
计算两个日期之间相差的月，计算是根据date1-date2得到的。
查看每个员工入职至今多少个月?
SELECT ename,MONTHS_BETWEEN(SYSDATE,hiredate)
FROM emp
NEXT_DAY(date,i)
i对应的数字为:1-7分别表示周日-周六。NEXT_DAY返回给定日期第二天开始一周之内对应的指定周几的日期。
SELECT NEXT_DAY(SYSDATE,4) FROM dual
LEAST,GREATEST
求最小值与最大值
凡是可以比较大小的数据都可以做为这两个函数的参数。但是所有参数的类型要统一。对于日期而言，最大值即最晚的日期，最小值即最早的日期
SELECT LEAST(SYSDATE,TO_DATE(‘2008-08-08’,‘YYYY-MM-DD’)) FROM dual
EXTRACT()
获取指定日期中指定分量的值
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual
查看82年入职的员工?
SELECT ename,sal,hiredate FROM emp
WHERE EXTRACT(YEAR FROM hiredate)=1982

##NULL值操作

插入NULL值

CREATE TABLE student(
  id NUMBER(4), 
  name CHAR(20) NOT NULL, 
  gender CHAR(1)
);
INSERT INTO student VALUES(1000, '李莫愁', 'F');
INSERT INTO student VALUES(1001, '林平之', NULL);
INSERT INTO student(id, name) VALUES(1002, '张无忌');

更新为NULL
UPDATE student SET gender=NULL WHERE id=1000
过滤条件中判断NULL值
判断要使用 IS NULL 或 IS NOT NULL
DELETE FROM student WHERE gender IS NOT NULL
NULL的计算
字符串与NULL连接等于什么也没做
NULL与数字运算结果还是NULL
查看每个员工的收入:(工资+绩效)
SELECT ename,sal,comm,sal+comm FROM emp
空值函数

NVL(arg1,arg2)
若arg1为NULL，函数返回arg2的值，
若arg1不为NULL，函数返回arg1自身。
所以NVL函数是将NULL替换为一个非NULL值。

SELECT ename,sal,comm,sal+NVL(comm,0) FROM emp

NVL2(arg1,arg2,arg3)
当arg1不为NULL时，函数返回arg2
当arg1为NULL时，函数返回arg3

SELECT ename,comm NVL2(comm,‘有绩效’,‘没有绩效’) FROM emp
SELECT ename,sal,comm,NVL2(comm,sal+comm,sal)
FROM emp

#SQL
SQL(Structured Query Language) 是结构化查询语言，在关系数据库上执行数据操作、检索及维护所使用的标准语言。

SQL分为：
数据定义语言（DDL） : Data Definition Language
数据操纵语言（DML） : Data Manipulation Language
事务控制语言（TCL）：Transaction Control Language
数据查询语言（DQL）：Data Query Language
数据控制语言（DCL） : Data Control Language

##数据定义语言DDL : Data Definition Language

DDL用于维护数据库对象使用数据库对象:表，视图，索引，序列。
主要包括：
create：创建数据库或者数据库对象
alter：对数据库或数据库对象进行修改
drop：删除数据库或者数据库对象

创建表

CREATE TABLE employee_xxx(
	id NUMBER(4),
	name VARCHAR2(20),
	gender CHAR(1),
	birth DATE,
    salary NUMBER(6,2),
    job VARCHAR2(30),
    deptno NUMBER(2)
)

删除表：DROP TABLE employee_xxx
修改表
修改表名：RENAME table old_name TO new_name
如：RENAME table employee TO myemp
修改表结构
- 添加新的字段
  ALTER TABLE myemp ADD (hiredate DATE);

- 删除现有字段

ALTER TABLE myemp DROP hiredate;

- 修改现有字段

可以修改字段的类型，长度，默认值，非空约束但是应当在表中没有数据的时候进行，否则尽量不要修改类型，长度尽量只增不减。否则可能会修改失败。
将JOB字段的长度变为40:
ALTER TABLE myemp MODIFY (job VARCHAR2(40))
##default与not null

在数据库中，所有字段的默认值都是NULL，可以通过DEFAULT关键字指定一个默认值。对于字符串而言，数据库中字面量是使用单引号括起来的，这一点需要与java区分记忆。

default

CREATE TABLE employee(
	id NUMBER(4),
	name VARCHAR2(20),
	gender CHAR(1) DEFAULT 'M',
	birth DATE,
    salary NUMBER(6,2) DEFAULT 3000,
    job VARCHAR2(30),
    deptno NUMBER(2)
)

NOT NULL约束
当一个字段被NOT NULL修饰后，该字段不允
许为空。

CREATE TABLE employee(
	id NUMBER(4),
	name VARCHAR2(20) NOT NULL,
	gender CHAR(1) DEFAULT 'M',
	birth DATE,
    salary NUMBER(6,2) DEFAULT 3000,
    job VARCHAR2(30),
    deptno NUMBER(2)
)
显示表结构:DESC employee

##数据操作语言DML：Data Manipulation Language

DML是用来增，删，改表中数据。DML是伴随事务(TCL)控制的。
主要包括：
insert：将数据插入到表或者视图中
update：修改表或视图中的数据
delete：从表或视图中删除数据

增：INSERT

INSERT语句用于向表中插入新数据
INSERT INTO myemp (id,name,job,deptno) VALUES (1,‘jack’,‘CLERK’,10)
不指定字段则是全列插入，给定的值的类型与顺序要与表中字段一致。
INSERT INTO myempVALUES (2,‘rose’,‘F’,SYSDATE,5000,‘CLERK’,10)

删：DELETE

DELETE FROM myemp WHERE salary<5000

改：UPDATE

将id为2的员工性别改为"M",部门改为"20"
UPDATE myemp SET gender=‘M’,deptno=20 WHERE id=2

#约束

约束的作用
约束（CONSTRAINT）的全称是约束条件，也称作完整性条件。约束是在数据表上强制执行的一些数据校验规则。

##约束的类型

约束条件包括：
非空约束(Not Null)，简称NN
主键约束(Primary Key)，简称PK
外键约束(Foreign Key)，简称FK
检查约束(Check)，简称CK

##非空约束

非空约束用于确保字段值不为空。

建表时添加非空约束

CREATE TABLE employees (
eid NUMBER(6),
name VARCHAR2(30) NOT NULL,
salary NUMBER(7, 2),
hiredate DATE CONSTRAINT employees_hiredate_nn NOT NULL
);

修改表时添加非空约束

ALTER TABLE employees MODIFY (eid NUMBER(6) NOT NULL);

取消非空约束

ALTER TABLE employees  MODIFY (eid NUMBER(6));

##唯一性约束

唯一性(Unique)约束条件用于保证字段或者字段的组合不出现重复值。

建表时添加

CREATE TABLE employees2 (
  eid NUMBER(6) UNIQUE,
  name VARCHAR2(30),
  email VARCHAR2(50),
  salary NUMBER(7, 2),
  hiredate DATE,CONSTRAINT employees_email_uk UNIQUE(email)
)

建表之后增加唯一性约束条件：

ALTER TABLE employees  ADD CONSTRAINT employees_name_uk UNIQUE(name);

##主键约束

一张表只能有一个字段定义主键约束，主键约束要求该字段非空且唯一(非空性+唯一性)

建表时添加

CREATE TABLE employees3 (
  eid NUMBER(6) PRIMARY KEY,
  name VARCHAR2(30),
  email VARCHAR2(50),
  salary NUMBER(7, 2),
  hiredate DATE
)

建表后添加

ALTER TABLE employees3 ADD CONSTRAINT  employees3_eid_pk PRIMARY KEY (eid);

#外键约束

外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系。比如emp表的deptno列参照dept表的deptno列，则dept称作主表或父表，emp表称作从表或子表。

先建表，在建表后建立外键约束条件

CREATE TABLE employees4 (
eid NUMBER(6),
name VARCHAR2(30),
salary NUMBER(7, 2),
deptno NUMBER(4)
);
ALTER TABLE employees4 ADD CONSTRAINT employees4_deptno_fk 
FOREIGN KEY (deptno) REFERENCES dept(deptno);

##检查约束

检查(Check)约束条件用来强制在字段上的每个值都要满足Check中定义的条件。

ALTER TABLE employees4 ADD CONSTRAINT employees4_salary_check
CHECK (salary > 2000);

Myzoneliver

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle数据库基础一：函数+DDL+DML

SQL语句本身不区分大小写，但是字符串的值是区分大小写的 SQL字符串用单引号’ ‘基本概念：DB：Database DBMS:Database Management System DBA:Database Administrator 一个关系数据库由多个数据表组成，数据表是关系数据库的基本存储结构，由行和列组成，行（Row）也就是横排数据，也经常被称作记录(Re...
复制链接

扫一扫

专栏目录