一、SELECT 语句
1、SELECT NAME,COUNTRY FROM WEBSITES;与SELECT * FROM WEBSITES; 效果一样,但是第一种速度可能会快。
2、SELECT DISTINCT COLUMN_NAME FROM table_name; DISTINCT 关键词用于返回唯一不同的值。
3、ORDER BY 关键字用于对结果集进行排序。SELECT * FROM WEBSITES ORDER BY ALEXA;
二、INSERT 语句
第一种形式无需指定要插入数据的列名,只需提供被插入的值即可,要保证values的数量和表字段一样并一一对应:
INSERT INTO table_name VALUES (value1,value2,value3,...);
第二种形式需要指定列名及被插入的值:
INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
三、UPDATE语句
1、UPDATE 语句用于更新表中的记录。执行没有 WHERE 子句的 UPDATE 要慎重,再慎重
UPDATE table_name SET column1=value1,column2=value2,...WHERE some_column=some_value;
四、SQL DELETE 语句
1、DELETE 语句用于删除表中的行。
DELETE FROM table_name WHERE some_column=some_value;
2、可以在不删除表的情况下,删除表中所有的行。这意味着表结构、属性、索引将保持不变:
DELETE FROM table_name;或DELETE * FROM table_name;
3、DROP;TRUNCATE;DELETE的区别:
DROP:DROP test;
删除表test,并释放空间,将test删除的一干二净。
TRUNCATE:TRUNCATE test;
删除表test里的内容,并释放空间,但不删除表的定义,表的结构还在。
DELETE:
1、删除指定数据
删除表test中年龄等于30的且国家为US的数据
DELETE FROM test WHERE age=30 AND country='US';
2、删除整个表
仅删除表test内的所有内容,保留表的定义,不释放空间。
DELETE FROM test 或者 DELETE FROM test;DELETE * FROM test 或者 DELETE * FROM test;
四、SQL LIKE 操作符
1、SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
(1) '%a' //以a结尾的数据
(2) 'a%' //以a开头的数据
(3) '%a%' //含有a的数据
(4) ‘_a_’ //三位且中间字母是a的
(5) '_a' //两位且结尾字母是a的
(6) 'a_' //两位且开头字母是a的
2、SQL 别名
三个列(url、alexa 和 country)结合在一起,并创建一个名为 "site_info" 的别名:
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info FROM Websites;
五、修改表
1、添加列
² 语法结构:ALTER TABLE 表ADD 新增列名 数据类型;
² 举例:ALTER TABLE STUDENT ADD TEL VARCHAR2(11);
2、删除列
3、修改列数据类型
² 语法结构:ALTER TABLE 表 MODIFY 列名 新数据类型;
² 举例:ALTER TABLE STUDENT MODIFY TEL NUMBER(11,0);
4、修改列名
² 语法结构:ALTER TABLE 表 RENAME COLUMN 列名 TO 新列名;
² 举例:ALTER TABLE STUDENT COLUMN SEX TO GENDER;
5、删除表
² TRUNCATE TABLE 表:该操作用于删除表中的全部数据,并不是把表删除掉,这个删除方式比DELETE 方式删除的速度快,也叫截断表。
² DROP TABLE 表:删除表结构。
六、约束
1、约束是ORACLE提供的自动保持数据完成性的一种方法,他通过限制字段中的数据、记录数据中和表之间的数据来保证数据的完整性。
2、完成性约束的基本语法格式:
[CONSTRAINT constraint_name(约束名)]<约束类型>
说明:约束不指定名称时,系统会给定一个名称。
3、列级约束与表级约束定义上的区别
² 列级约束:必须跟在列的定义后面。
² 表级约束:不与列一起,而是单独定义的。
4、ORACLE中的约束
(1) 主键约束(primary key constraint)
² Primary key 约束定义基本表的主键,他是唯一确定表中每一条记录的标识符,其值不能为NULL,也不能重复,以此来保证实体的完整性。表中主键智能有一个,但可以由多个列构成。如:primary ke(学号,科目编号)
² 创建表时添加主键约束:
CREATE TABLE STUDENT(
SID NUMBER(8,0),
NAME VARCHAR2(10),
SEX CHAR(2),
BIRTHDAY DATE,
CONSTRAINT SID_PK PRIMARY KEY(SID)
);
² 修改表添加主键约束
ALTER TABLE STUDENT ADD CONSTRAINT SID_PK PRIMARY KEY(SID);
² 删除主键约束格式:DROP PRIMARY KEY;
(2) 唯一性约束(unique constraint)
² 唯一性约束用于一个或者多个列的组合值具有唯一性,以防止在列中输入重复的值。比如人的身份证是唯一的。
² 唯一性约束的注意事项
n 列允许为空值;
n 一个表可以允许有多个唯一性约束;
n 可以把唯一性约束定义在多个列上。
² 创建表设置唯一性约束:
CREATE TABLE STUDENT(
SID NUMBER(8,0),
NAME VARCHAR2(10) ,
SEX CHAR(2) ,
BIRTHDAY DATE,
EMAIL VARCHAR2(50) UNIQUE,
CARDID VARCHAR2(20),
CONSTRAINT UK_CARDID UNIQUE(CARDID)
);
² 修改表时添加唯一性约束
ALTER TABLE STUDENT ADD CONSTRAINT UK_CARDID UNIQUE(CARDID);
² 暂时失效唯一约束:
ALTER TABLE STUDENT DISABLE CONSTRAINT UK_STUDENT_CARDID;
² 彻底删除
ALTER TABLE STUDENT CONSTRAINT UK_STUDENT_CARDID;
(3) 默认约束(default constraint)
(4) 非空约束(not null constraint)
² NOT NULL 约束用于确保列不能为null,如果在列上定义了NOT NULL 约束,那么当插入数据时,必须为该列提供数据;当更新列数据时,不能将其值设置为NULL。
² 非空约束是列级约束。
² 创建表时添加非空(NOT NULL)约束
CREATE TABLE STUDENT(
SID NUMBER(8,0),
NAME VARCHAR2(10) NOT NULL,--第一种推荐
SEX CHAR(2) CONSTRAINT NN_SEX NOT NULL,--第二种
BIRTHDAY DATE,
);
² 修改表时添加约束的格式对比
n 修改表添加约束的语法格式:
ALTER TABLE TABLE_NAME ADD [CONSTRAINT constraint_name] constraint_type(column);
n 添加非空约束要使用MODIFY语句
ALTER TABLE table_name MODIFY(column datatype NOT NULL);
² 删除非空约束的方式:
ALTER TABLE table_name MODIFY column_name datatype NULL;
(5) 检查约束(check constraint)
² 检查约束对输入列或者整个表中的值设置检查条件,以限制输入值,保证数据库数据的完整性。例如性别只能输入男或女。
² 创建表时设置检查约束
CREATE TABLE STUDENT(
SID NUMBER(8,0),
NAME VARCHAR2(10) NOT NULL,
SEX CHAR(2) CHECK(SEX=’男’OR SEX=’女’),
BIRTHDAY DATE,
CONSTRAINT CK_SEX CHECK(SEX=’男’OR SEX=’女’)
);
² 修改表时添加检查约束
ALTER TABLE STUDENT ADD CONSTRAINT CK_SEX CHECK(SEX=’男’OR SEX=’女’)
(6) 外部键约束(foreign key constraint)
² 外键是用于建立和加强两个表数据之间的链接的一列或者多列。外键约束是唯一涉及两个表关系的约束。院系信息表为主表,学生信息表为从表,先建立主表,后建立从表。
² 列级约束
CREATE TABLE 从表
(column_name datatype REFERENCES 主表(column_name)[ON DELETE CASCADE],...);
² 表级约束
CONSTRAINT constrain_name FOREIGN KEY(column_name) REFERENCES 主表(column_name) [ON DELETE CASCADE]
² 创建表时设置外键约束(1)
主表
CREATE TABLE DEPARTMENT(
DEPID VARCHAR2(20) PRIMARY KEY,
DEPNAME VARCHAR2(20));
从表
CREATE TABLE STUDENT(
SID NUMBER(8,0),
NAME VARCHAR2(20) ,
SEX CHAR(2),
BIRTHDAY DATE,
ADDRESS VARCHAR2(50),
DEPID VARCHAR2(20) REFERENCES DEPARTMENT(DEPID)
);
² 修改表时添加外键约束
ALTER TABLE STUDENT ADD CONSTRAINT FK_DEPID FOREIGN KEY(DEPID) REFERENCE DEPARTMENT(DEPID) ON DELETE CASCADE;
4、删除约束的方式
(1) 将约束无效化或者激活:
² DISABLE|ENABLE CONSTRAINT constraint_name;
(2) 彻底删除
² DROP CONSTRAINT constraint_name;
七、事物
1、什么是事物?
事物可以看着做是由对数据库的若干操作组成的一个单元,这些操作要么都完成,要么都取消,从而保证数据满足一致性的要求。
2、事物的组成
(1) 一条或者多条DML
(2) 一条DDL
(3) 一条DCL
3、DML语句需要使用COMMIT 提交事物或者使用ROLLBACK回滚事物。
DDL和DCL 是自动提交事物的。
4、为什么使用事物?
(1) 使用事物的原因:保证数据的安全有效。
(2) 当执行事物操作(DML语句)时,ORACLE 会在杯子作用表上加表锁,以防止其他用户改变表结构;同时会在被作用行上加锁,以防止其他事物在相应行上执行DML操作。
5、事物的控制命令
(1) 回滚事物:ROLLBACK
(2) ROLLBACK 只能对未提交的数据撤销,已经COMMIT的数据是无法撤销的,因为COMMIT 之后已经持久化到数据库中。
(3) 保存点(SAVEPOINT):是事物中的一点,用于取消部分事物,当结束事物时,会自动删除该事物所定义的所有保存点。当执行ROLLBACK 时,通过指定保存点可以回退到指定的点。
八、分组函数
1、在关系数据库中,使用数组分组可以取得表数据的汇总信息。数据分组是通过分组函数,GROUP BY 以及HAVING 等子句共同实现的。
2、分组函数与空值。
(1) 分组函数会自动忽略空值;
(2) NVL函数使分组函数无法忽略空值
(3) SELECT COUNT(NVL(COMM,0)) FROM EMP;--不忽略空值
(4) SELECT AVG(COMM) FROM EMP ;--忽略空值
九、GROUP BY 子句使用
1、可以将表中的数据分成若干组
2、语法
3、求出每个部门的平均工资,要求显示:部门,部门的平均工资
SELECT DEPTNO ,AVG(SAL) FREOM EMP GROUP BY DEPTNO;
在select列表中所有未包含在分组函数中的列都应该包含在GROUP BY 子句中。
十、HAVING 子句使用
1、作用:用于对分组后的结果进行过滤
2、例子:求平均工资大于2500的部门,要求显示部门,平均工资
SELECT DEPTNO ,AVG(SAL) FREOM EMP GROUP BY DEPTNO HAVING AVG(SAL)>2500;
3、语法
4、如果条件中没有分组函数,正常使用where 语句就可以;where使得分组记录数大大降低,从而提高效率;从sql优化角度看,尽量使用 where 子句。先过滤后分组
十一、存储过程和函数
1、数据库对象:表、视图、索引、序列、同义词、过程和函数。
2、存储在数据库中的被命名的PLSQL块,供所有用户程序调用,完成疼功能的子程序。
3、过程和函数的区别:是否用return返回值。过程没有返回。
4、创建存储过程的语法
5、创建输入参数的存储过程
CREATE OR REPLACE PROCEDURE proc1 (v_empno IN empnew.empno%TYPE)
IS
BEGIN
--根据员工号删除指定的员工信息
DELETE FROM EMPNEW WHERE EMPNO=v_empno;
END;
6、创建带有输出参数的存储过程
CREATE OR REPLACE PROCEDURE proc2(v_deptno IN NUMBER,v_avgsal OUT NUMBER,v_cnt OUT NUMBER)
IS
BEGIN
SELECT AVG(SAL) ,COUNT(*)
INTO v_avgsal,v_cnt FROM EMP WHERE DEPTNO=v_deptno;
END;
7、带有输入和输出的存储过程
CREATE OR REPLACE PROCEDURE proc3(v_num1 IN OUT NUMBER,v_num2 IN OUT NUMBER) AS
v_temp NUMBER:=0;
BEGIN
v_temp:=v_num1;
v_num1:=v_num2;
v_num2:=v_temp;
END;
十二、序列
1、什么是序列?
是一种用于自动生成唯一数字的数据库对象。主要用于提供主键值。
2、如何创建序列
CREATE SEQUENCE SEQ_FWBZ_MEMBER
MINVALUE 1 --最小值
NOMAXVALUE --不设置最大值
START WITH 1 --从1开始计数
INCREMENT BY 1 --每次加1个
NOCYCLE --一直累加,不循环
NOCACHE; --是否缓存
3、怎样使用序列
(1) 当时用序列时,必须通过伪例NEXTVAL和CURRVAL来引用序列
(2) NEXTVAL用于引用返回下一个序列值。
例如:deptno_seq.nextval
(3) CURRVAL用于引用返回当前序列值
例如:deptno_seq.currval
4、如何查询序列
(1) 查询数据字典视图USER_SEQUENCES获取序列定义信息
5、注意事项
(1) 如果指定cache值,可提高访问效率,但是用cache也会出现跳号的可能。
(2) 序列在下列情况会出现序列缺口(裂缝):
² 回滚
² 系统异常
² 多个表同时使用同一序列
6、修改序列的命令:ALTER SEQUENCE
注意:序列的初始值不能修改.
7、删除序列
DROP SEQUENCE sequence;
十三、索引
1、什么是索引:为了加速对表中数据行的检索而创建的一种存储结构。
2、为什么使用索引
3、索引分类
(1) 按索引列的个数
² 单列索引
² 复合索引
(2) 按索引列值的唯一性
² 唯一索引
² 非唯一索引
4、创建索引
(1) 单列索引
CREATE INDEX INDEX_ENAME ON EMP(ENAME);
(2) 复合索引
CREATE INDEX IDX_DEPTNO_JOB ON EMP(DEPTNO,JOB);
(3) 唯一索引
CREATE UNIQUE INDEX IDX_DNAME ON DEPT(DNAME);
(4) 非唯一索引
CREATE INDEX IDX_JOB ON EMP(JOB);
5、什么时候创建索引
(1) WHERE 子句经常引用的表列上;
(2) 为了提高多表连接的性能,应该在连接列上建立索引;
(3) 经常排序的列上创建索引,可以加快数据排序的速度。
6、怎样查询索引
(1) 可以使用数据字典视图USER_INDEXES 和USER_IND_COLUMNS查看索引的信息
7、删除索引
DROP INDEX index;
十四、多表查询
1、就是从多个表中获取数据
2、笛卡尔集:集合的一种。假设A和B都是集合,A和B 的笛卡尔积用A*B表示,形成的集合叫笛卡尔集。
3、为了避免笛卡尔集,可以在where子句中加入有效的连接条件;连接条件至少有n-1个,n代表表的个数;在表中有相同列时,在列名前加上表名前缀。
4、等值连接:指使用等值比较符=指定连接条件的查询
5、区分重复的列名
6、在连接查询中,当有多个连接条件时,使用AND指定其他条件。
7、不等值连接:
8、外链接:是标准连接的扩展,他不仅会返回满足连接条件的所有记录,而且还会返回不满足连接条件的部分记录
(1) 右外连接
(2) 左外连接
9、自连接:实质上将同一张表看成是多个表。例如:查询所有员工的姓名及其直属上级的姓名。
10、SET运算符
(1) UNION:用于取得两个结果集的并集,会自动去掉结果集中的重复行,并且会以第一列的结果进行升序排序。
(2) UNIONALL:和UNION类似,但是不会取消重复行,也不会自动排序。
(3) INTERSECT:交集,会以第一列的结果进行升序排列。
(4) MINUS:差集
十五、子查询
1、子查询是指嵌入在其他SQL语句中的SELECT 语句,也成为嵌套查询。
SELECT * FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME=’SMITH’);
2、语法
3、HAVING 中使用子查询:例如:查询员工信息表,按部门编号进行分组,要求显示员工的部门编号,平均工资,查询条件时平均工资大于30号部门的最高工资。
4、FROM 中使用子查询
SELECT * FROM (SELECT EMPNO,ENAME,SAL FROM EMP);
5、主查询和子查询
6、一个主查询可以有多个子查询
7、子查询的执行顺序:一般先执行子查询,在执行主查询,但相关子查询例外。
8、相关子查询:当子查询需要引入主查询的列表时,ORACLE 会执行相关子查询。
9、主查询和子查询可以不是同一张表。
10、子查询类型:
(1) 单行子查询:
² 只返回一行数据。
² 单行比较操作符
(2) 多行子查询
² 返回多行数据。
² 多行比较操作符
11、注意:
(1) 不可以在GROUP BY 子句中使用子查询
(2) TOP-N问题,须对子查询排序
(3) 空值问题:要去除子查询的空值NULL
一.单行函数
只处理单个行,并且为每行返回一个结果。
1.字符函数
(1)concat(str1,str2)字符串拼接函数
select concat('Hello ','World') from dual;
--等价于
select 'Hello '||'World' from dual;
(2)initcap(str)将每个单词首字母大写,其他字母小写
select initcap('hello world!') from dual; --返回结果为'Hello World!'
select initcap('HELLO WORLD!') from dual; --返回结果为'Hello World!'
(3)instr(x,find_string[,start][,occurrence])返回指定字符串在某字符串中的位置,可以指定搜索的开始位置和返回第几次搜索出来的结果
----------搜索时下标从1开始计算
select instr('Hello World!','o') from dual;--从1位置开始搜索,返回第一次出现的o的位置,结果为5
select instr('Hello World!','o',6) from dual;--从6位置开始搜索,返回第一次出现的o的位置,结果为8
select instr('Hello World!','o',1,2) from dual;--从1位置开始搜索,返回第二次出现o的位置,结果为8
(4)length(str)返回表达式中的字符数
select length('Hello World!') from dual;--返回结果为12
select length('张三') from dual;--返回结果为2
(5)lengthb(str)返回表达式中的字节数
select lengthb('Hello World!') from dual;--返回结果为12
select lengthb('张三') from dual;--返回结果为6
(6)lower(str)将字符串转换为小写
select lower('Hello World!') from dual;
(7)upper(str)将字符串转换为大写
select upper('Hello World!') from dual;
(8)lpad(str,width[,pad_string])当字符串长度不够时,左填充补齐,可以指定补齐时用什么字符补齐,若不指定,则以空格补齐
select lpad('Hello World!',20) from dual;--返回结果为' Hello World!'
select lpad('Hello World!',20,'*') from dual;--返回结果为'********Hello World!'
(9)rpad(str,width[,pad_string])当字符串长度不够时,右填充补齐,原理同左填充
select rpad('Hello World!',20) from dual;--返回结果为'Hello World! '
select rpad('Hello World!',20,'*+') from dual;--返回结果为'Hello World!*+*+*+*+'
(10)ltrim(x[,trim_string])从字符串左侧去除指定的所有字符串,若没有指定去除的字符串,则默认去除左侧空白符
select ltrim(' Hello World! ') from dual;--返回结果为'Hello World! '
select ltrim('***+*Hello World!***+*','*+') from dual;--返回结果为'Hello World!***+*'
(11)rtrim(x[,trim_string])从字符串右侧去除指定的所有字符串,原理同ltrim()
select rtrim(' Hello World! ') from dual;--返回结果为' Hello World!'
select rtrim('***+*Hello World!***+*','*+') from dual;--返回结果为'***+*Hello World!'
(12)trim(trim_string from x)从字符串两侧去除指定的所有字符串
select trim('*+' from '***+*Hello World!***+*') from dual;
注意,ltrim()和rtrim()的截取集可以使多个字符,但trim的截取集只能有一个字符
select trim('*+' from '***+*Hello World!***+*') from dual;--错误,截取集只能有一个字符
(13)nvl(x,value)将一个NULL转换为另外一个值,如果x为NULL,则返回value,否则返回x值本身
insert into student values(7,'猪猪',default,NULL);
select nvl(address,'北京市') from student;
(14)nvl2(x,value1,value2),如果x不为NULL,返回value1,否则,返回value2
select nvl2(address,'有地址','无地址') from student;
(15)replace(x,search_string,replace_string),从字符串x中搜索search_string字符串,并使用replace_string字符串替换。并不会修改数据库中原始值
select replace('Hello World!','o','HA') from dual;
(16)substr(x,start[,length])返回字符串中的指定的字符,这些字符从字符串的第start个位置开始,长度为length个字符;如果start是负数,则从x字符串的末尾开始算起;如果 length省略,则将返回一直到字符串末尾的所有字符
select substr('Hello World',3) from dual; --返回结果为'llo World'
select substr('Hello World',-3) from dual;--返回结果为'rld'
select substr('Hello World',3,2) from dual;--返回结果为'll'
select substr('Hello World',-7,4) from dual;--返回结果为'o Wo'
2.数值函数
(1)abs(value)返回value的绝对值
select abs(-10) from dual;--返回结果为10
(2)ceil(value)返回大于等于value的最小整数
select ceil(2.3) from dual; --返回结果为3
(3)floor(value)返回小于等于value的最大整数
select floor(2.3) from dual; --返回结果为2
(4)trunc(value,n)对value进行截断,如果n>0,保留n位小数;n<0,则保留-n位整数位;n=0,则去掉小数部分
select trunc(555.666) from dual; --返回结果为555,不加n时默认去掉小数部分
select trunc(555.666,2) from dual;--返回结果为555.66
select trunc(555.666,-2) from dual;--返回结果为500
(5)round(value,n)对value进行四舍五入,保存小数点右侧的n位。如果n省略的话,相当于n=0的情况
select round(555.666) from dual;--返回结果为556,不加n时默认去掉小数部分
select round(555.666,2) from dual;--返回结果为555.67
select round(555.666,-2) from dual;--返回结果为600
注意:1.trunc和round用法类似,只不过trunc是硬生生截取,并不进行四舍五入,而round进行截取时四舍五入
2.都还可以对日期的截取,可以参考写的日期函数笔记
select round(sysdate,'year') from dual;
select trunc(sysdate,'year') from dual;
3.转换函数
将值从一种类型转换成另外一种类型,或者从一种格式转换为另外一种格式
(1)to_char(x[,format]):将x转化为字符串。 format为转换的格式,可以为数字格式或日期格式
select to_char('12345.67') from dual; --返回结果为12345.67
select to_char('12345.67','99,999.99') from dual; --返回结果为12,345.67
(2)to_number(x [, format]):将x转换为数字。可以指定format格式
select to_number('970.13') + 25.5 from dual;
select to_number('-$12,345.67', '$99,999.99') from dual;
(3)cast(x as type):将x转换为指定的兼容的数据库类型
select cast(12345.67 as varchar2(10)),cast('05-7月-07' as date), cast(12345.678 as number(10,2)) from dual;
(4)to_date(x [,format]):将x字符串转换为日期
select to_date('2012-3-15','YYYY-MM-DD') from dual
二.聚集函数
1.常用函数
(1)avg(x):返回x的平均值
select avg(grade) from sc;
(2)count(x):返回统计的行数
select count(name) from sc;
(3)max(x):返回x的最大值
select max(grade) from sc;
(4)min(x):返回x的最小值
select min(grade) from sc;
(5)sum(x):返回x的总计值
select sum(grade) from sc;
2.对分组行使用聚集函数
对分组后的行使用聚集函数,聚集函数会统计每组中的值,对于每组分别统计后返回一个值。
示例
--按照职位分组,求出每个职位的最高和最低工资
select job ,max(sal),min(sal) from emp
group by job
order by job;
注意:1.分组时select子句后边的列名必须与group by子句后的列名一致,除非是聚合函数
select deptno,avg(sal) from EMP;--错误,因为deptno不是聚集函数,也不是group by后面跟的列名
2.不能使用聚集函数作为WHERE子句的筛选条件
select deptno from emp where avg(sal)>1000;--错误
3.分组后,需要使用条件进行筛选,则使用having过滤分组后的行,不能使用where,where只能放在group by前面。
select deptno, avg(sal) from emp where deptno<>10
group by deptno
having avg(sal) > 900;