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’)
- SELECT UPPER(‘helloworld’),LOWER(‘HELLOWORLD’),
-
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);