1.sql,
定义：是结构化查询语言的缩写
作用：可以用来查询数据、操作数据、定义数据、控制数据。
分类：DDL( 数据定义语言)，DML（数据操作语言），TCL（事物控制语言）
DQL（数据查询语言），DCL(数据控制语言)
CREATE TABLE student_songchang(
id1 NUMBER(4),
salary NUMBER(6,2),
name1 VARCHAR2(20),
gender CHAR(1),
birth DATE,
job1 VARCHAR2(30),
deptno NUMBER(2)
);
3.数据类型
3.1NUMBER类型
ORACLE中所有的数字都是使用NUMBER类型的，
NUMBER类型的长度定义中包含两部分:长度及精度。
例如 salary NUMBER（6，2）
意思时有意个sal字段，总共有六为数字，其中小数占两位
NUMBER（4）表示就说修饰的是一个整数
3.2字符串类型
CHAR：定长字符串，最大支持2000字节，无乱实际数据多长，都会将数据补充若干空格，
达到该字段实际指定的长度。优点：查询效率好，缺点：浪费磁盘空间
VARCHAR2：ORACLE变长字符串类型，其太数据库只支持VARCHAR类型。最大支持4000个
字节，数据多长就保存多长，用多少占多少。优点：磁盘空间利用好。 缺点:查询效率低。
3.3日期类型
DATE：占用7个字节，用于存放一个日期类型。
7个字节非别存储:世纪，年，月，日，时，分，秒
所以保存的实际按精度做多表示到秒。秒以下的精度
就保存不了了。想保存需要使用男其他日期类型。
默认格式： DD-MON-RR 例如：（11-APR-71）表示：日-月-年
4.DDL 语句（数据定义语言，这一类SQL语言用于增，删，改数据对象）
4.1 CREABLE 语句（主要用于创建一直那个表）
creable table 表名（idnumber（），name varchar2（），.....）
4.2 DESC语句（查看表的结构）
DESC student_songchang
4.3 DEFAULT关键字（可以在创建表的时候，为某个字段指定默认值。数据库中，无论字段的数据类
型是什么，默认值都是NULL。）
例如：gender CHAR（1）DEFAULT ‘2’，
4.4 NOT NULL约束（当表中某个字段使用NOT NULL修饰后，该字段在任何情况下值不能为空）
4.5 修改表
4.5.1：修改表名
RENAME old_name TO new_name;
例如：
RENAME student_songchang TO student_songchang12
DESC student_songchang12;
4.5.2: 追加新字段
ALTER TABLE student_songchang12 ADD(hisddata DATE)
DESC student_songchang12;
4.5.2: 删除列
ALTER TABLE student_songchang12 DROP(hisddata)
DESC student_songchang12;
4.5.3: 删除表
DROP TABLE student_songchang12;
4.5.4: 修改列
ALTER TABLE student_songchang12 MODIFY(gender CHAR(1) DEFAULT 'C' );
5. DML 语句
5.1: INSERT语句
5.1.1用来给数据表增加记录，每次增加一条记录
INSERT INTO student_songchang12(id1,salary,name1,gender)
VALUES(3,2324.22,'hello','1');
5.1.2 :查询表
select *from student_songchang12;
5.1.3：插入日期类型
INSERT INTO student_songchang12(id1,salary,name1,gender，birth)
VALUES(4,2324.22,'hello','1'，TO_DATE('1978-09-01', 'YYYY-MM-DD'));
5.2: UPDATE语句 （更新数据）
UPDATE student_songchang12 SET name1 = 'songchang' WHERE id1 = 3;
5.3: DELETE语句 （删除数据）
DELETE FROM student_songchang12 WHERE id1 = 3;
CREATE TABLE dept_songchang(
deptno NUMBER(2),
dname VARCHAR2(14) ,
loc VARCHAR2(13)
);
CREATE TABLE emp_songchang(
empno NUMBER(8),
ename VARCHAR2(20),
job VARCHAR2(20),
mgr NUMBER(8),
hiredate DATE,
sal NUMBER(11,2),
comm NUMBER(11,2),
deptno NUMBER(8)
);
SELECT * FROM dept_songchang;
INSERT INTO dept_songchang VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO dept_songchang VALUES(20,'RESEARCH','DALLAS');
INSERT INTO dept_songchang VALUES(30,'SALES','CHICAGO');
INSERT INTO dept_songchang VALUES(40,'OPERATIONS','BOSTON');
INSERT INTO emp_songchang VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp_songchang VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp_songchang VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp_songchang VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp_songchang VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp_songchang VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp_songchang VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp_songchang VALUES(7788,'SCOTT','ANALYST',7566,to_date('19-4-87','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp_songchang VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp_songchang VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp_songchang VALUES(7876,'ADAMS','CLERK',7788,to_date('23-5-87','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO emp_songchang VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp_songchang VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp_songchang VALUES(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
select *
from emp_songchang
查看指定字段的信息：
SELECT与FROM之间指定字段可以包括:
1:表中的字段
2:一个表达式
3:一个函数
SELECT .....
1.1: 连接字符串操作符 'concat'
SELECT concat(empno,ename)
FROM emp_songchang
1.2: 连接字符串操作符 '||'
SELECT empno||','||ename
FROM emp_songchang
2: length函数：获取字符串的长度
SELECT ename,LENGTH(ename)
FROM emp_songchang
3.1: upper() 小写转换为大写 （dual是伪表）
SELECT UPPER('hello')
FROM dual
3.2: lower() 大写转换为小写
SELECT LOWER('HELLO')
FROM dual
3.3: initcap() 用于每个单词的首字母大写，其他小写，单词之间空格
SELECT INITCAP('HELLO word')
FROM dual
4.1: TIRM(C2 FROM C1) 从C1的前后去掉C2
SELECT TRIM('e' FROM 'eeeeeeliteeeee')
FROM dual
4.2:LTRIM(c1,C2) 从c1的左边(left)去掉c2(去掉的c2包含的字符)
SELECT LTRIM('egggggHeeeeheeee','eg')
FROM dual
4.34.2:RTRIM(c1,C2) 从c1的右边(right)去掉c2
SELECT RTRIM('eeeeeeliteeee','e')
FROM dual
5: 在字符串参数char1的左端或右端用char2补足到n位，其中参数char2可重复多次
5.1: LPAD(char1, n, char2) 左补位函数
SELECT LPAD(sal,6,'&')
FROM emp_songchang
5.2: RPAD(char1, n, char2) 右补位函数
SELECT RPAD(sal,6,'&')
FROM emp_songchang
6: SUBSTR(char, m, n)表示在一个字符串中截取子串(数据库中下标从1开始！)
参数：从指定字符串的指定位置开始连续取若干字符，第三个参数可以表不传入，
不传入则是取到末尾，若第三个参数的值大于获取的字符长度时也默认为取
到字符串末尾如果m = 0，则从首字符开始，如果m取负数，则从尾部(最后一个)开始.
SELECT SUBSTR('thinging in java',10,2)
FROM dual
7: INSTR(char1, char2, n , m) (n和m可以没有)
参数的含义：
-返回子串char2在源字符串char1中的位置
-从n的位置开始搜索，没有指定n，从第1个字符开始搜索
-m用于指定子串的第m次出现次数，如果不指定取值1
-如果在char1中没有找到子串char2 ，返回0
SELECT INSTR('thinking in java','in',4,2)
from dual
8: ROUND(n, m)的功能是用来四舍五入
参数中的n可以是任何数字，指要被处理的数字
m必须是整数
m取正数则四舍五入到小数点后第m位
m取0值则四舍五入到整数位
m取负数，则四舍五入到小数点前m位
m缺省，默认值是0
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（小数点前的用0补充）
9: TRUNC(n, m)的功能是截取（不需要进行四舍五入），其中n和m的定义和ROUND(n, m)相同
SELECT TRUNC(45.678, 2) FROM DUAL; --45.67
SELECT TRUNC(45.678, 0) FROM DUAL; --45
SELECT TRUNC(45.678, -2) FROM DUAL; --40 （小数点前的用0补充）
10：MOD(m, n)是取模函数，返回m除以n后的余数，如果n为0则直接返回m
SELECT MOD(103,4) FROM dual; --3
SELECT MOD(103,0) FROM dual; --103
11: CEIL ：向上取一个整数 FLOOR ：向下取一个整数
SELECT CEIL(45.678) FROM DUAL; --46
SELECT FLOOR(45.678) FROM DUAL; --45
12: 日期类型
DATE与TIMESTAMP
DATE：表示日期，精确到秒，7个字节分别表示世纪年月日时分秒
TIMESTAMP：时间戳，表示日期，精确到秒，前七个字节与DATE一致，后四个字节记录纳秒以下的精度
SELECT SYSDATE FROM DUAL;
SELECT SYSTIMESTAMP FROM dual;
13: 日期的计算
数据库中，日期时可以计算的，对一个日期类型的值加上一个指定的数字，
等于加上一个指定的天数，返回的日期时计算后的日期，减去也是同理。
两个日期类型的值相减，差是相差的天数。日期越晚的越大。
例如：查看每个员工到今天为止入职多少天？
SELECT ename,TRUNC(SYSDATE-hiredate,0)
FROM emp_songchang
14: TO_DATE的功能是将字符串按照定制格式转换为日期类型
在日期格式字符串中，除英文与符号外的其他字符，都应当使用双引好扩起来
SELECT TO_DATE('2002-01月-01','YYYY-MM"月"-DD')
FROM dual
15: TO_CHAR的作用是将其它类型（日期，数值）的数据转换为字符类型
SELECT ename,TO_CHAR(hiredate,'YYYY-MM-DD')
FROM emp_songchang
16: RR 是2位数字表示年的日期格式中的关键字，
RR与YY的区别在于，RR会跟据当前系统时间自动判定世纪
17: LAST_DAY(date)：返回日期date所在月的最后一天
SELECT TO_CHAR(LAST_DAY(SYSDATE),'YYYY-MM-DD') FROM DUAL;--查询当月的最后一天
SELECT LAST_DAY('20-2月-09') FROM DUAL; --查询09年2月的最后一天
18: ADD_MONTHS(date, i)：返回日期date加上i个月后的日期值(负数就相当与减去月)
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,2),'YYYY-MM-DD') FROM DUAL;
19: MONTHS_BETWEEN(date1, date2)：计算date1和date2两个日期值之间间隔了多少个月，
实际运算是date1-date2，如果date2时间比date1晚，会得到负值。
SELECT MONTHS_BETWEEN(SYSDATE,ADD_MONTHS(SYSDATE,-2)) FROM dual;
SELECT MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp_songchang;
20: NEXT_DAY(date, char)：返回date日期数据的下一个周几，周几是由参数char来决定的
英文环境下，需要使用”WEDNESDAY”这种英文的周几。
为避免麻烦，可以直接用数字1-7表示周日-周六
SELECT TO_CHAR(NEXT_DAY(SYSDATE,2),'YYYY-MM-DD') FROM dual;
21: GREATEST(expr1, expr2, expr3…) 返回参数列表中的最大值
LEAST(expr1, expr2, expr3…) 返参数列表中的最小值
SELECT GREATEST(1,5,67,90) FROM dual; --90
SELECT LEAST(1,5,67,90) FROM dual; --1
22: EXTRACT(date FROM datetime)：从参数datetime中提取参数date指定的数据
比如提取年、月、日。例如取出当前日期的年.
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;--查看当前系统所在的年
SELECT ename,hiredate --查找1981年入职的员工
FROM emp_songchang
WHERE EXTRACT(YEAR FROM hiredate)=1981;
23: NULL
CREATE TABLE students_songchang(
id NUMBER(4),
NAME CHAR(20),
GENDER CHAR(1)
);
INSERT INTO STUDENTS_SONGCHANG VALUES(1000,'李莫愁','F');
INSERT INTO STUDENTS_SONGCHANG VALUES(1001,'林平之',NULL);--显式插入NULL值
INSERT INTO STUDENTS_SONGCHANG(ID,NAME) VALUES(1002,'张无忌');--隐式的插入NULL值
UPDATE students_songchang SET gender = null;
drop table STUDENTS_SONGCHANG;
select * from STUDENTS_SONGCHANG;
SELECT * FROM students_songchang where gender is null;
SELECT * FROM students_songchang where gender is not null;
24: NVL(expr1, expr2)：将NULL转变为非NULL值。如果expr1为NULL，
则取值expr2 (expr2是非空值)
SELECT ename,sal,comm,sal+NVL(COMM,0)
FROM emp_songchang;
24.1:NVL2(expr1, expr2, expr3)：和NVL函数功能类似，都是将NULL转变为非空值。
NVL2用来判断expr1是否为NULL，如果不是NULL，返回expr2，如果是NULL，返回expr3。
SELECT ename,comm,NVL2(comm,'有奖金', '无奖金')
FROM emp_songchang;
第二天 作业
1.查询EMP中的数据，列出一列，内容为名字与职位，显示格式：ename:job
SELECT ename||':'||job FROM EMP_SONGCHANG;
2.查看每个员工职位的字符个数
SELECT length(job) FROM EMP_SONGCHANG;
3.将所有员工的名字以小写形式，与首字母大写形式查询出来，第一列为小写形式，第二列为首字母大写
SELECT lower(ename),INITCAP(ename) FROM EMP_SONGCHANG;
4.将字符串中左右两边的a去掉
SELECT TRIM('a' FROM 'aaaaabaaaaaaa') FROM dual;
5.显示每个员工的名字，要求显示10位，第一列左对齐效果，第二列右对齐效果
SELECT RPAD(sal,10,' '),LPAD(ename,10,' ') FROM emp_songchang;
6.截取字串‘DOCTOR WHO’中的‘WHO’
SELECT SUBSTR('DOCTOR WHO',7) FROM dual;
8.查看‘DOCTOR WHO’中的‘WHO’的位置
SELECT INSTR('DOCTOR WHO','WHO') from dual;
9.分别查看55.789四舍五入保留小数点后2位，整数位，十位后的数字，显示成三列
SELECT ROUND(55.789, 2),ROUND(55.789),ROUND(55.789, -1) FROM DUAL;
10.查看每位员工工资百位以下的数字
SELECT sal FROM emp_songchang where TRUNC(sal, -3)=0;
11.查看每名员工从入职以后到今天一共多少天，若有小数则向上取整
SELECT TRUNC(SYSDATE-hiredate,0) FROM emp_songchang
12.查看从2008-08-08好到今天为止一共经历了多少天？
select trunc(sysdate-TO_DATE('2008-08-08','YYYY-MM-DD'),0) from dual;
13.将每名员工入职时间以例如：
1981年12月3日的形式显示
SELECT TO_CHAR(hiredate,'YYYY"年"MM"月"DD"日"') FROM emp_songchang;
14.查看每个员工入职所在月的月底时哪天
SELECT LAST_DAY(hiredate) FROM emp_songchang;
15.查看每个员工的转正日期（入职以后三个月）
SELECT ADD_MONTHS(hiredate, 3)
FROM emp_songchang ;
16.查看每个员工入职至今共多少个月
SELECT MONTHS_BETWEEN(sysdate, hiredate) FROM emp_songchang ;
17.查看从明天开始一周内的周日是哪天
SELECT NEXT_DAY(hiredate+1,1) FROM emp_songchang ;
18.查看82年以后入职的员工的入职日期，82年以前的按照1982年01月01日内号显示。格式为DD-MON-RR（默认格式）
SELECT GREATEST(hiredate,TO_DATE('1982年01月01日','YYYY"年"MM"月"DD"日'))
FROM emp_songchang ;
19.查看每名员工的入职年份
SELECT to_CHAR(hiredate,'YYYY') FROM emp_songchang
20.显示每名员工的总收入（工资加奖金），奖金为NULL的只看工资
SELECT ename,sal,comm,sal+NVL(COMM,0) FROM emp_songchang;
21，使用NVL2实现20题的要求
SELECT ename,comm,sal+NVL2(comm,comm, 0) FROM emp_songchang;
SQL(基础查询)
1.1. 基本查询语句
1.1.1. FROM子句
SELECT * FROM emp_songchang;
1.1.2. 使用别名
用语法是列的别名跟在列名后，中间可以加或不加一个“AS”关键字
SELECT empno AS id ,ename AS "Name", sal * 12 "Annual Salary"
FROM emp_songchang;
1.1.3. WHERE子句: 限制查询结果
SELECT * FROM SELECT empno, ename, sal, job FROM emp; WHERE deptno = 10;
1.1.4. SELECT子句: 查询表的部分列
SELECT empno, ename, sal, job FROM emp_songchang;
1.2. 查询条件
1.2.1. 使用>, <, >=, <=, !=, <>, =
SELECT ename, sal, job FROM emp_songchang WHERE deptno != 10;
1.2.2. 使用AND，OR关键字 逻辑与（AND）逻辑或（OR）
AND的优先级高于OR的优先级，但是可以通过括号来提高OR的优先级
SELECT ename, sal, job FROM emp_songchang
WHERE sal> 1000 AND (job = 'CLERK' OR job = 'salesman');
SELECT ename, sal, job FROM emp_songchang
WHERE sal> 1000 OR job = 'CLERK';
1.2.3. 使用LIKE条件（模糊查询）：不能完全确定某些信息的查询条件，
或者只知道信息的一部分，可以借助LIKE来实现模糊查询
支持两个通配符：
%：表示0到多个字符
_：标识单个字符
SELECT ename FROM emp_songchang WHERE ename LIKE '_A%';-—查询名字第二个字母是A的人（区分大小写）
SELECT ename FROM emp_songchang WHERE ename LIKE '%A%';--查询名字含有A的人
1.2.4. 使用IN和NOT IN :
IN(list):取出符合列表范围中的数据
NOT IN(list): 取出不符合此列表中的数据记录
SELECT ename, job FROM emp_songchang WHERE job IN ('MANAGER', 'CLERK');
SELECT ename, job FROM emp_songchang WHERE deptno NOT IN (10, 20);
1.2.5. BETWEEN…AND…: 用来查询符合某个值域范围条件的数据
SELECT ename,job,deptno FROM emp_songchang WHERE deptno BETWEEN 10 AND 20;
1.2.6. 使用IS NULL和IS NOT NULL :
空值NULL是一个特殊的值，比较的时候不能使用”=”号，必须使用IS NULL，否则不能得到正确的结果。
SELECT ename,comm FROM emp_songchang WHERE comm IS NULL;
SELECT ename,comm FROM emp_songchang WHERE comm IS NOT NULL;
1.2.7. 使用ANY和ALL条件:可以出现ALL表示“全部”,ANY表示“任一”，
但是ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用.
> ANY：大于最小(大于其中之一，就相当于只需大于里面最小的那一个就符合要求)
< ANY：小于最大(小于其中之一，就相当于只需小于最大的那一个就符合要求)
> ALL：大于最大(大于列表中的所有，就相当于必须大于集合的所有元素，
所以就必须大于集合中最大的元素，这样才满足大于集合中的所有元素)
< ALL：小于最小(小于列表中的所有，就相当于必须小于集合的所有元素，
所以就必须小于集合中最小的元素，这样才满足小于集合中的所有元素)
SELECT empno, ename, job, sal, deptno
FROM emp_songchang
WHERE sal > ANY (
SELECT sal FROM emp_songchang WHERE job = 'SALESMAN');
1.2.8. 查询条件中使用表达式和函数
可以在数字列上使用算术表达式(+、-、*、/)。表达式符合四则运算的
默认优先级，如果要改变优先级可以使用括号。
SELECT ename, sal, job FROM emp_songchang WHERE ename = UPPER('ward');
SELECT ename, sal, job FROM emp_songchang WHERE sal * 12 >10000;
1.2.9. 使用DISTINCT过滤重复,可以对多个字段去除重复行(DISTINCT必须直接写在select后面)
SELECT deptno FROM emp_songchang;
SELECT DISTINCT deptno FROM emp_songchang;--查询员工的部门编码，去掉重复值
DISTINCT后面的列可以组合查询，下例查询每个部门的职位，去掉重复值。注意是deptno和job联合起来不重复：
SELECT DISTINCT deptno, job FROM emp_songchang;
1.3. 排序
1.3.1. 使用ORDER BY字句： 查询出的数据按升序或者降序进行排序操作
ORDER BY子句必须写在SELECT中的最后一个句子上
升序 (ASC)：ASC可以不写，默认升序
降序 (DESC)： 从大到小
SELECT ename, sal
FROM emp_songchang
ORDER BY sal DESC;
排序的字段中若有NULL值，NULL别认做最大值
select ename,comm from emp_songchang ORDER BY comm;
1.3.2. 多个列排序:当以多列作为排序标准时，首先按照第一列进行排序，
如果第一列数据相同，再以第二列排序，以此类推。
SELECT ename, deptno, sal
FROM emp_songchang
ORDER BY deptno ASC, sal DESC;
1.4. 聚合函数
1.4.1. 什么是聚合函数
聚合函数会将指定字段查询出来的每一条记录对应的值全部年进行统计，然后得出一个结果。
1.4.2. MAX和MIN
用来取得指定字段中的最大值和最小值，
SELECT MAX(sal) FROM emp_songchang;
1.4.3. AVG和SUM (聚合函数忽略NULL值)
AVG和SUM函数用来指定字段中的平均值和球和值
SELECT AVG(sal) FROM emp_songchang;--求平均值时有NULL的不计入里面，求平均值的时候人都不算
SELECT AVG(NVL(sal,0)) FROM emp_songchang;-- 此处就可以避免忽略NULL值的情况，相当与把所有的NULL都转化为0
SELECT SUM(sal) FROM emp_songchang;
1.4.4. COUNT:用来计算表中的记录条数，同样忽略NULL值
SELECT COUNT(job) AS total_job FROM emp_songchang;
SELECT COUNT(*) FROM emp_songchang; --查询整张表有多少条记录
1.5. 分组
1.5.1. GROUP BY子句
GROUP BY会根据后面给定的字段值相同的记录进行分组，然后配合聚合函数根据每个组在统计结果。
查看每个部门的最高工资？
SELECT max(sal),deptno
FROM emp_songchang
GROUP BY deptno
查看每个部门，每种职位的平均工资？
SELECT job,deptno,AVG(sal)
FROM emp_songchang
GROUP BY deptno ,job
1.5.2 HAVING字句
HAVING必须在GROUP BY子句之后，作用是在分组统计后，基于统计结果再次过滤进行使用的。
HAVING中可以使用聚合函数的统计结果作为过滤条件来筛选分组
查看每个部门的平均工资，前提是该部门平均工资高于2000
SELECT deptno,AVG(sal)
FROM emp_songchang
GROUP BY deptno HAVING AVG(sal)>2000;
where 的过滤时机是在第一次检索表中数据时进行过滤的
HAVING是在分组统计后在进行过滤的
1.6. 查询语句的执行顺序
当一条查询语句中包含所有的子句，执行顺序依下列子句次序：
1.FROM 子句：执行顺序为从后往前、从右到左。数据量较少的表尽量放在后面。
2.WHERE 子句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE 子句的最右。
3.GROUP BY 子句：执行顺序从左往右分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。
4.HAVING 子句：消耗资源。尽量避免使用，HAVING 会在检索出所有记录之后才对结果集进行过滤，需要排序等操作。
5.SELECT 子句：少用*号，尽量取字段名称。ORACLE 在解析的过程中, 通过查询数据字典将*号依次转换成所有的列名, 消耗时间。
6.ORDER BY 子句：执行顺序为从左到右排序，消耗资源。
2. SQL（关联查询）
2.1. 关联基础
2.1.1. 关联的概念 (N张表连接查询至少要有N-1个链接)
将多张表数据联系起来，进行相关的查询工作
查询每名员工的名字以及所在部门的名字？
SELECT dname,ename
FROM dept_songchang,emp_songchang
where emp_songchang.deptno = dept_songchang.deptno ;
表可以使用别名，这样字段可以使用表的别名标注以避免SQL语句过于繁琐
SELECT dname,ename
FROM dept_songchang e ,emp_songchang d
where e.deptno = d.deptno ;
2.1.2. 笛卡尔积
笛卡尔积指做关联操作的每个表的每一行都和其它表的每一行做组合，假设两个表的记
录条数分别是X和Y，笛卡尔积将返回X*Y条记录。
当两个表关联查询时，不写连接条件，得到的结果即是笛卡尔积 。
SELECT COUNT(*) FROM emp_songchang; --14条记录
SELECT COUNT(*) FROM dept_songchang; --4条记录
SELECT emp.ename, dept.dnameFROM emp, dept;--56条记录
2.2. 关联查询
2.2.1. 内连接:
另一中关联查询(看着清晰很多)
SELECT e.ename,d.dname
FROM emp_songchang e JOIN dept_songchang d
ON e.deptno = d.deptno
WHERE d.dname = 'SALES';
UPDATE emp_songchang SET deptno = 50 where ename='SCOTT';
2.2.2. 左连接，右连接，和全连接：
关联查询中不会将不满足连接条件的记录查询出来，当需要不满足连接条件的记录也查询出来时，
要使用外连接。
外连接分为：左连接，右连接，和全连接[LEFT | RIGHT | FULL]
左外连接：以JOIN左侧的表作为驱动表，
该表中的数据全部要显示出来，那么不满足连接条件的记录在结果集中
来自右侧表中的字段全部以NULL作为值显示
SELECT e.ename,d.dname
FROM emp_songchang e full JOIN dept_songchang d
ON e.deptno = d.deptno;
SELECT e.ename,d.dname
FROM emp_songchang e , dept_songchang d
where e.deptno = d.deptno(+);--右外连接
SELECT e.ename,d.dname
FROM emp_songchang e , dept_songchang d
where e.deptno(+) = d.deptno;--左外连接 （但是没有全连接）
2.2.3. 自连接：
自连接是一种特殊的连接查询，数据的来源是一个表，即关联关系来自于
单表中的多个列。表中的列参照同一个表中的其它列的情况称作自参照表。
SELECT e.ename,m.ename
FROM emp_songchang e,emp_songchang m
WHERE e.mgr = m.empno(+);
第三天 作业
1.查看工资高于2000的员工
SELECT ename from emp_songchang where sal>2000;
2.查看不是‘clerk’职位的员工
SELECT ename from emp_songchang e where ename != 'clark' ;
3.查看工资在1000～2500之间的员工
SELECT ename,sal from emp_songchang where sal between 1000 and 2500;
4.查看名字是以K结尾员工
SELECT ename from emp_songchang where ename like '%K';
5.查看部门时20，30号的员工
SELECT ename,deptno from emp_songchang where deptno in (20,30) ;
6.查看奖金为NULL的员工
SELECT ename,comm from emp_songchang where comm IS null;
7.查看年薪高于20000的员工
SELECT ename from emp_songchang where sal*12>20000;
8.查看公司共有多少种职位
SELECT count( distinct job) total_job from emp_songchang;
9.按部门从小到大排序查看员工
SELECT EMPNO from emp_songchang ORDER BY EMPNO ;
10.查看每个部门的最高，最低，平均工资，和工资总和
SELECT DEPTNO,max(sal) ,min(sal),AVG(SAL),SUM(SAL)
from emp_songchang
GROUP BY DEPTNO;
11.查看平均工资高于2000的部门的最低薪水
SELECT DEPTNO,MIN(SAL)
from emp_songchang
GROUP BY DEPTNO HAVING AVG(SAL)>2000;
12.查看在NEWYORK工作的员工
SELECT ename
from emp_songchang e,dept_songchang d
where e.DEPTNO=d.DEPTNO and loc = 'NEW YORK';
13.查看所有员工及所在部门信息，若该员工没有部门，则部门信息以NULL显示
SELECT DISTINCT e.ename, d.deptno
from emp_songchang e,emp_songchang d
WHERE e.deptno = d.deptno(+);
14.查看ALLEN 的上司是谁
SELECT m.ename
FROM emp_songchang e,emp_songchang m
WHERE e.mgr = m.empno and e.ename ='ALLEN';
1. SQL(高级查询)
1.1. 子查询: 嵌套在其他SQL中的一条查询语句，该查询语句就是子查询。
当我们执行的SQL语句需要先从数据库中获取一些数据才能运行时，那么先期执行
的这条SQL就是子查询，是为了要实际执行的SQL提供数据的
1.1.1. 子查询在WHERE子句中
查看工资高于CLARK的员工？
SELECT ename ,sal
FROM emp_songchang
where sal > ( SELECT sal
FROM emp_songchang
where ename = 'CLARK');
子查询常用语查询语句，但是也可以在DDL，DML中使用。
DDL中使用，
例如：基于一个查询结果快速创建一张表?
CREATE table myemp_songchang
AS
SELECT e.empno,e.ename,e.sal,e.job,d.deptno,d.dname,d.loc
FROM emp_songchang e, dept_songchang d
WHERE e.deptno = d.deptno;
删除CLEARK所在部门的员工?
DELETE FROM myemp_songchang
WHERE deptno = ( SELECT deptno
FROM emp_songchang
where ename = 'CLARK');
子查询根据查询结果分为：
但行但列子查询，多行单列子查询，多行多列子查询
其中单列子查询用在WHERE字句中。er多列子查询通常用于FROM中当作表看列
SELECT ename,EMPNO,job
FROM emp_songchang
where deptno IN ( SELECT deptno
FROM emp_songchang
where job = 'SALESMAN')
AND job <>'SALESMAN'; -- <>等价于！=
EXISTS是用于WHERE中作为判断条件使用的，其后需要紧跟
一个子查询，只要该子查询能查出至少一条记录 ，那么EXIST就返回真。
查看有员工的部门信息？
SELECT deptno,dname
FROM dept_songchang d
WHERE
EXISTS( SELECT *
FROM emp_songchang e
WHERE e.deptno = d.deptno) ;
SELECT deptno,dname
FROM dept_songchang d
WHERE
not EXISTS( SELECT *
FROM emp_songchang e
WHERE e.deptno = d.deptno) ;
1.1.2. 子查询在HAVING部分
查看每个部门的最低薪水，前提是该部门的最低薪水要高于30部门的最低薪水？
SELECT deptno ,min(sal) min_sal
FROM emp_songchang
GROUP BY deptno
HAVING MIN(sal)>(SELECT min(sal)
FROM emp_songchang
where deptno = 30
);
1.1.3. 子查询在FROM部分
查看谁比自己所在部门平均工资高？
SELECT e.ename,sal,e.deptno
FROM emp_songchang e, (SELECT AVG(sal) avg_sal,deptno
FROM emp_songchang
GROUP BY deptno ) s
WHERE e.deptno = s.deptno AND e.sal > s.avg_sal ;
子查询也可以运用在SELECT子句中，可以实现外连接的效果。
1.1.4. 子查询在SELECT部分
查看每个员工以及所在部门的信息？
SELECT e.ename ,(SELECT d.dname
FROM dept_songchang d
WHERE d.deptno = e.deptno) deptno
FROM emp_songchang e;
1.2. 分页查询:
当查询的数据过度时，会导致一些情况；
1；服务端响应慢
2：系统资源占用多
3：数据过剩
为了解决这些问题，通常我们会分批查询数据。这个过程就是分页查询
由于分页查询没有标准的SQL语法，所以不同的数据库对于分页的机制不一致
ORACLE支持一个关键字ROWNUM，
ROWNUM是一个伪列，该列不存在于任何一张表中，但是每张表都可以查询该列。
而该列在结果集的中值时结果集中每条记录的"行号"
ROWNUM给解雇平采集编号时查询的过程中进行的，只要可以从表中查询出来一条记录，
该记录的行号就会作为这条记录ROWNUM字段的值。ROWNUM从1开时递增。
由于ROWNUM从1开始，所以在第一次查询表中数据进行编号时，不要使用ROWNUM
做大于1以上的数字判断，否则查询不到数据
SELECT ROWNUM ,ename,sal
FROM emp_songchang;
查看5-10之间的字段？
SELECT *
FROM (SELECT ROWNUM rn,ename,sal
FROM emp_songchang)
WHERE rn BETWEEN 5 AND 10;
查看公司排名的5-10名?
SELECT *
FROM (SELECT ROWNUM rn,t.*
FROM (SELECT sal,ename
FROM emp_songchang
ORDER BY sal DESC) t)
WHERE rn BETWEEN 5 AND 10;
下面的效率好，不需要的数据就不编号了
SELECT *
FROM (SELECT ROWNUM rn,t.*
FROM (SELECT sal,ename
FROM emp_songchang
ORDER BY sal DESC
) t
WHERE ROWNUM<=10 --只要前十号的编号就行
)
WHERE rn >=6;
页数;page
每页显示的条数:pageSize
start = (page-1)*pageSize+1
end = pageSize*page
一页显示5条，显示第2页
1.3. DECODE函数:
SELECT ename, job, sal,
DECODE(job, 'MANAGER', sal * 1.2,
'ANALYST', sal * 1.1,
'ANALYST', sal * 1.1,
sal
) bonus
FROM emp_songchang;
和DECODE函数功能相似的有CASE语句，实现类似于if-else的操作。
SELECT ename,job,sal,CASE job WHEN 'MANAGER' THEN sal*1.2
WHEN 'ANALYST' THEN sal*1.1
WHEN 'ANALYST' THEN sal*1.1
ELSE sal END
bonus
FROM emp_songchang;
将不同的值分成一组？
SELECT COUNT(*),DECODE(job,
'MANAGER', 'VIP',
'ANALYST', 'VIP',
'OTHER')
FROM emp_songchang
GROUP BY DECODE(job,
'MANAGER', 'VIP',
'ANALYST', 'VIP',
'OTHER');
在GROUP BY中使用DECODE可以做到将字段值不同的记录
看作一组，只要将需要看作一组的记录中该字段的值替换为相同的值即可。
按字段内容排序？
SELECT deptno,dname,loc
FROM dept_songchang
ORDER BY
DECODE(dname,
'OPERATIONS',1,
'ACCOUNTING',2,
'SALES',3,
4);
1.4. 排序函数:
可以按照指定的字符段分组，然后在按照指定的字符段排序，最后为记录生成组内的编号
1.4.1. ROW_NUMBER:生成组内连续且唯一的数字。
ROW_NUMBER() OVER(
PARTITION BY col1 ORDER BY col2)
表示根据col1分组，在分组内部根据col2排序。
此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一。
查看每个部门中工资的排名 ？
SELECT ename,sal,deptno,
ROW_NUMBER() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp_songchang;
1.4.2. RANK
RANK() OVER(
PARTITION BY col1 ORDER BY col2)
表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名.
特点是:跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，
但下一位将是第四名。
SELECT ename,sal,deptno,
RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp_songchang;
1.4.3. DENSE_RANK
DENSE_RANK() OVER(
PARTITION BY col1 ORDER BY col2)
表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。
特点是;连续排序，如果有并列第二，下一个排序将是三，这一点是和RANK的不同，RANK是跳跃排序。
SELECT ename,sal,deptno,
DENSE_RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp_songchang;
1.5. 高级分组函数
1.5.1. UNION|INTERSECT|MINUS 并|交|差
为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并、交、差。
SELECT ename,job,sal
FROM emp_songchang
WHERE job='MANAGER'
UNION
SELECT ename,job,sal
FROM emp_songchang
WHERE sal>2500;
CREATE TABLE salas_tab_songchang(
year_id NUMBER NOT NULL,
month_id NUMBER NOT NULL,
day_id NUMBER NOT NULL,
sales_value NUMBER(10,2) NOT NULL
);
INSERT INTO salas_tab_songchang
SELECT TRUNC(DBMS_RANDOM.value(2010,2012)) AS year_id,
TRUNC(DBMS_RANDOM.value(1,13)) AS month_id,
TRUNC(DBMS_RANDOM.value(1,32)) AS day_id,
TRUNC(DBMS_RANDOM.value(1,100),2) AS sales_value
FROM dual
CONNECT BY level <= 1000;
SELECT * FROM salas_tab_songchang;
1.5.2. ROLLUP:
对ROLLUP的列从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组
(也就是全表分组)。对于n个参数的ROLLUP，有n+1次分组。
GROUP BY ROLLUP(a,b,c);
等价于：
GROUP BY a,b,c
UNION ALL
GROUP BY a,b
UNION ALL
GROUP BY a
UNION ALL
全表;
营业额的统计？
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM salas_tab_songchang
GROUP BY ROLLUP(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id;
1.5.3.CUBE会将每个参数的不同组合进行分组，
然后将所有分组统计的结果并在一起，
如果GROUP BY CUBE(a,b,c)，首先对(a,b,c)进行GROUP BY，
然后依次是(a,b)，(a,c)，(a)，(b,c)，(b)，(c)，最后对全表进行GROUP BY操作
，所以一共是2^3=8次分组。
分组次数时2的参数个数次方。
GROUP BY CUBE(a,b,c)
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM salas_tab_songchang
GROUP BY CUBE(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id;
1.5.3. GROUPING SETS
可以按照指定的参数进行分组
其中每一个参数就是一种组合方式
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM salas_tab_songchang
GROUP BY GROUPING SETS((year_id,month_id,day_id),(year_id,month_id))
ORDER BY year_id,month_id,day_id;
作业 四
1:查看与CLERK相同职位的员工
SELECT ename FROM emp_songchang WHERE JOB = 'CLERK';
2:查看低于公司平均工资的员工
SELECT ename
FROM emp_songchang
WHERE sal<(select AVG(SAL) from emp_songchang);
3:查看与ALLEN同部门的员工
SELECT ename
FROM emp_songchang
WHERE deptno = (select deptno from emp_songchang where ename = 'ALLEN');
4：查看平均工资低于20号部门平均工资的部门平均工资
select deptno,AVG(sal)
FROM emp_songchang
GROUP BY deptno
HAVING AVG(sal)< (SELECT avg(sal)
FROM emp_songchang
where deptno = 20);
5：查看低于自己所在部门平均工资的员工
select ename
from emp_songchang e,
(select AVG(sal) avg_sal,deptno
FROM emp_songchang
GROUP BY deptno) m
where e.deptno = m.deptno and m.avg_sal<e.sal;
6;查看CLERK职位的人数和其他职位人数的总人数个多少
select count(*)
FROM emp_songchang
GROUP BY DECODE(job, 'CLERK', 1,
2
) ;
7；查看每个职位的工资排名
SELECT ename,sal,deptno,
ROW_NUMBER() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp_songchang;
8：查看每个职位的工资排名，若工资一致，排名一致
SELECT ename,sal,deptno,
DENSE_RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp_songchang;
9查看公司排名的1-5名
SELECT *
FROM (SELECT ROWNUM rn,t.*
FROM (SELECT sal,ename
FROM emp_songchang
ORDER BY sal DESC) t)
WHERE rn BETWEEN 1 AND 5;
10;查看每个职位的工资排名，若工资一致，排名一致，不跳名次
SELECT ename,sal,deptno,
DENSE_RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp_songchang;
11;分别查看：同部门，同职位，以及所有员工的工资总和
SELECT deptno,job,ename,SUM(sal)
FROM emp_songchang
GROUP BY GROUPING SETS((deptno),(job),(ename));
12;分别查看：同部门，同职位，同部门以及所有员工的工资总和
SELECT deptno,job,ename,SUM(sal)
FROM emp_songchang
GROUP BY ROLLUP(deptno,job,ename)
13;分别查看同部门同职位的员工的工资总和
SELECT deptno,job,ename,SUM(sal)
FROM emp_songchang
GROUP BY GROUPING SETS((deptno,job),ename)
1. 视图、序列、索引
1.1. 视图
1.1.1. 什么是视图
视图时数据库对象之一
视图在SQL语句中体现的角色与表相同。但视图不是真实的表，
而是一个查询语句对应的结果集。
1.1.2. 创建简单视图（单表）
CREATE VIEW v_emp_songchang_10--创建一个视图
AS
SELECT empno,ename,sal sal,deptno
FROM emp_songchang
WHERE deptno = 10;
1.1.3. 查询视图
SELECT * FROM V_emp_songchang_10;
DESC v_emp_songchang_10;--查询视图的结构
DROP VIEW v_emp_songchang_10; --删除视图
当视图对应的子查询的字段含有函数或表达式时，必须对该字段定义别名，
那么视图该字段使用别名作为字段名。
--创建一个视图(OR REPLACE)若此视图已存在，则覆盖，不存在，直接建立视图
CREATE OR REPLACE VIEW v_emp_songchang_10
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp_songchang
WHERE deptno = 10;
1.1.4: 对视图进行INSERT操作
对视图进行DML操作
对简单视图进行DML操作，但是复杂视图不可以。
对视图进行DML操作就是对视图数据来源的基础表进行DML。
INSERT INTO v_emp_songchang_10
(id,name,salary,deptno) values(1001,'jack',5000,10);
SELECT * FROM v_emp_songchang_10;
SELECT * FROM emp_songchang;
当插入一条视图看不见的数据时，会对基础表造成“污染”
INSERT INTO v_emp_songchang_10
(id,name,salary,deptno) values(1002,'ROSE',5000,20);
SELECT * FROM v_emp_songchang_10;
SELECT * FROM emp_songchang;
更新数据同样会对基础表污染
更新后数据对于视图而言不可控
UPDATE v_emp_songchang_10 SET deptno = 20;--相当与将视图里面的数据全部将部门好改为20
SELECT * FROM v_emp_songchang_10;
SELECT * FROM emp_songchang;
删除不存的问题(当删除视图表中不存在的事物不会对基础表中的事物进行删除)
DELETE FROM v_emp_songchang_10 WHERE deptno = 20;
SELECT * FROM v_emp_songchang_10;
SELECT * FROM emp_songchang;
1.1.5. 创建具有CHECK OPTION约束的视图
为视图添加检查选项，可以避免对视图操作后视图对数据不可控的情况。
当添加检查选项后，对视图进行：
INSERT:插入数据必须对视图可见
UPDATE:根新后驶入必须对数据可见
DELETE:只能删除现有视图里能查到的记录
CREATE OR REPLACE VIEW v_emp_songchang_10
AS
SELECT empno id,ename name, sal salary,deptno
FROM emp_songchang
WHERE deptno = 10
WITH CHECK OPTION;
1.1.6. 创建具有READ ONLY约束的视图
为视图添加只读选项，添加后该视图不能进行修改
CREATE OR REPLACE VIEW v_emp_songchang_10
AS
SELECT empno id,ename name, sal salary,deptno
FROM emp_songchang
WHERE deptno = 10
WITH READ ONLY;
1.1.7. 创建复杂视图（多表关联）：
视图对应名的SELECT语句中含有函数，表达式，分组等操作时，该视图就是一个复杂视图。
对于多表关联查询的视图，叫做连续视图，也算是复杂视图的一种
复杂视图不能进行DML操作。
CREATE VIEW v_emp_songchang_salary
AS
SELECT AVG(e.sal) avg_sal,
SUM(e.sal) sum_sal,
MIN(e.sal) min_sal,
MAX(e.sal) max_sal,
d.dname,d.deptno
FROM emp_songchang e,dept_songchang d
WHERE e.deptno = d.deptno
GROUP BY d.dname,d.deptno;
SELECT *FROM v_emp_songchang_salary;
查看谁比自己所在部门的平均工资高？
SELECT e.ename ,e.sal,e.deptno,TRUNC(v.avg_sal, 0) avg_sal
FROM emp_songchang e JOIN v_emp_songchang_salary v
ON e.deptno = v.deptno
WHERE e.sal>v.avg_sal;
删除视图:
删除视图不会对视图数据来源的基础表左任何操作
DROP VIEW v_emp_songchang_salary;
在数据字典USER_OBJECTS中查询所有视图名称
SELECT object_name FROM user_objects
WHERE object_type ='VIEW';
在数据字典USER_OBJECTS中查询所有视图名称
SELECT object_name,object_type
FROM user_objects;
在数据字典USER_OBJECTS中查询指定视图
SELECT text FROM user_views
WHERE view_name ='V_EMP_SONGCHANG_10';--视图名必须是大写
在数据字典USER_TABLES查询所有的表
SELECT table_name
FROM USER_TABLES;
1.2. 序列
1.2.1. 什么是序列
数据库对象之一
序列可以按照指定的规则生成一系列的数字。
序列生成的数字通常是为了某张表的主键提供值的。
1.2.2. 创建序列
CREATE SEQUENCE aeq_emp_songchang_id
START WITH 1
INCREMENT BY 1 ;
1.2.3. 使用序列
序列支持两个伪列：
NEXTVAL:让序列生成一个数字，是用序列最后生成的数字加上步长得到，
新创建的序列调用时返回START WITH指定的值
CURRVAL:获取序列当前数字（即最后一次通过NEXTVAL生成的数字）。
新创建的序列先至少执行一次NEXTVAL以后才可以使用CURRVAL。
NEXTVAL会导致序列增长，而且增长后不能获取原来生成过的值。
SELECT aeq_emp_songchang_id.NEXTVAL FROM dual;
SELECT aeq_emp_songchang_id.CURRVAL FROM dual;
INSERT INTO emp_songchang
(empno,ename,job,sal,deptno)
VALUES
(aeq_emp_songchang_id.NEXTVAL,'ROSE','CLERK',6000,30);
SELECT * FROM emp_songchang;
1.2.4. 删除序列
DROP SEQUENCE aeq_emp_songchang_id;
1.3. 索引
数据库对象之一
为某张表的某些字段添加索引可以调高表的查询、排序、去重等操作的效率。
序列的算法和维护是数据库自行维护的，我们不能干涉，我们只需要告诉数据库是否创建索引即可。
1.3.1. 创建索引
在EMP表的ENAME列上建立索引
CREATE INDEX idx_emp_songchang_ename ON emp_songchang(ename);
复合索引也叫多列索引，是基于多个列的索引,
如果经常在ORDER BY子句中使用job和salary作为排序依据，可以建立复合索引。
CREATE INDEX idx_emp_songchang_job_sal ON emp_songchang(job, sal);
1.3.3. 创建基于函数的索引
如果需要在emp表的ename列上执行大小写无关搜索，可以在此列上建立一个基于UPPER函数的索引:
CREATE INDEX emp_songchang_ename_upper_idx ON emp_songchang(UPPER(ename));
当做下面的查询时，会自动应用刚刚建立的索引:
SELECT * FROM emp_songchang WHERE UPPER(ename) = 'KING';
1.3.4. 修改和删除索引
如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率.
ALTER INDEX idx_emp_songchang_ename REBUILD;
当一个表上有不合理的索引，会导致操作性能下降，删除索引的语法：
DROP INDEX idx_emp_songchang_ename;
2. 约束
2.1. 约束的类型
约束条件包括：
非空约束(Not Null)，简称NN
唯一性约束(Unique)，简称UK
主键约束(Primary Key)，简称PK
外键约束(Foreign Key)，简称FK
检查约束(Check)，简称CK
2.2. 非空约束
2.2.1. 建表时添加非空约束：
CREATE TABLE employes_songchang(
eid NUMBER(6),
name VARCHAR2(30) NOT NULL,
salary NUMBER(7,2),
hiredate DATE CONSTRAINT employes_songchang_hiredate_nn NOT NULL
);
2.2.2. 修改表时添加非空约束
ALTER TABLE employes_songchang MODIFY(eid NUMBER(6) NOT NULL);
DESC TABLE employes_songchang;--查看表的结构
2.3. 唯一性约束
在建表的时候添加唯一约束
CREATE TABLE employes_songchang1 (
eid NUMBER(6) UNIQUE,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2),
CONSTRAINT employes_songchang1_email_uk UNIQUE(email)
);
INSERT INTO employes_songchang1
(eid,name,email,salary)
VALUES (null,'JACK',NULL,5000);
在建表之后增加唯一性约束条件：
ALTER TABLE employes_songchang1
ADD CONSTRAINT employes_songchang1_name_uk UNIQUE(name);
2.4. 主键约束
主键可以用来在表中唯一的确定一行数据。
一个表上只允许建立一个主键，而其它约束条件则没有明确的个数限制。
即：在主键约束下的单字段或者多字段组合上不允许有空值，也不允许有重复值。
在建表时添加主键约束条件：
CREATE TABLE employes_songchang2 (
eid NUMBER(6) PRIMARY KEY ,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2)
);
INSERT INTO employes_songchang2
(eid,name) VALUES (1,'JACK');
建表后创建主键约束条件，并自定义约束条件名称
CREATE TABLE employes_songchang3 (
eid NUMBER(6) ,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2)
);
ALTER TABLE employes_songchang3
ADD CONSTRAINT
employes_songchang3_eid_pk PRIMARY KEY (eid);
2.5. 外键约束
ALTER TABLE employes_songchang3
ADD CONSTRAINT employes_songchang3_deptno_fk
FOREIGN KEY (deptno) REFERENCES dept(deptno);
(key后面的是参照REFERENCES后面)
2.6. 检查约束
在建表时添加CHECK约束条件：
CREATE TABLE employes_songchang4 (
eid NUMBER(6) ,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7) CHECK (salary>2000)
);
建表后创建CHECK约束条件
ALTER TABLE employes_songchang4
ADD CONSTRAINT employes_songchang4_eid_check
CHECK (eid > 0);
SELECT *
FROM EMP_SONGCHANG
DESC TABLE emp_songchang
CREATE TABLE accounts_songchang(
id VARCHAR2(20),
name varchar2(20),
money number(10,2)
);
INSERT INTO accounts_songchang VALUES('00001','张三',9000);
INSERT INTO accounts_songchang VALUES('00002','李四',4000);
select * from accounts_songchang;
DESC TABLE depts;
DROP TABLE depts_songchang;
select * from depts_songchang;
CREATE TABLE depts_songchang(
DEPTNO NUMBER(8) primary key,
DNAME VARCHAR2(20),
LOC VARCHAR2(100)
);
INSERT INTO depts_songchang VALUES('1','销售部','北京');
INSERT INTO depts_songchang VALUES('2','市场部','上海');
INSERT INTO depts_songchang VALUES('3','开发部','广州');
t
select * from emp_songchang;
DESC TABLE emp_songchang;
delete from cost_songchang where cost_id>=100;
select* from cost_songchang;
--资费信息表
create table cost_songchang(
cost_id number(4) primary key,
name varchar(50) not null,
base_duration number(11),
base_cost number(7,2),
unit_cost number(7,4),
status char(1),
descr varchar2(100),
creatime date default sysdate ,
startime date,
cost_type char(1)
);
create sequence cost_seq_songchang start with 100;
INSERT INTO COST_songchang VALUES (1,'5.9元套餐',20,5.9,0.4,0,'5.9元20小时/月,超出部分0.4元/时',DEFAULT,DEFAULT,'2');
INSERT INTO COST_songchang VALUES (2,'6.9元套餐',40,6.9,0.3,0,'6.9元40小时/月,超出部分0.3元/时',DEFAULT,DEFAULT,'2');
INSERT INTO COST_songchang VALUES (3,'8.5元套餐',100,8.5,0.2,0,'8.5元100小时/月,超出部分0.2元/时',DEFAULT,DEFAULT,'2');
INSERT INTO COST_songchang VALUES (4,'10.5元套餐',200,10.5,0.1,0,'10.5元200小时/月,超出部分0.1元/时',DEFAULT,DEFAULT,'2');
INSERT INTO COST_songchang VALUES (5,'计时收费',null,null,0.5,0,'0.5元/时,不使用不收费',DEFAULT,DEFAULT,'3');
INSERT INTO COST_songchang VALUES (6,'包月',null,20,null,0,'每月20元,不限制使用时间',DEFAULT,DEFAULT,'1');
COMMIT;
select * from admin_info_songchang;
--管理员表
create table admin_info_songchang(
admin_id number(8) primary key not null,
admin_code varchar2(30) not null,
password varchar2(30) not null,
name varchar2(30) not null,
telephone varchar2(15),
email varchar2(50),
enrolldate date default sysdate not null
);
//create sequence admin_seq_songchang start with 10000;
--管理员表
insert into admin_info_songchang values(2000,'admin','123','ADMIN','123456789','admin@tarena.com.cn',sysdate);
insert into admin_info_songchang values(3000,'zhangfei','123','ZhangFei','123456789','zhangfei@tarena.com.cn',sysdate);
insert into admin_info_songchang values(4000,'liubei','123','LiuBei','123456789','liubei@tarena.com.cn',sysdate);
insert into admin_info_songchang values(5000,'caocao','123','CaoCao','123456789','caocao@tarena.com.cn',sysdate);
insert into admin_info_songchang values(6000,'aaa','123','AAA','123456789','aaa@tarena.com.cn',sysdate);
insert into admin_info_songchang values(7000,'bbb','123','BBB','123456789','bbb@tarena.com.cn',sysdate);
commit;
select* from say_say;
CREATE TABLE say_say(
space_id number(10),
user_id number(10),
say_say_id number(10),
say_say_content varchar2(2000),
say_say_publish date,
say_say_type varchar2(20),
say_say_head varchar2(50)
);
create sequence say_say_sc start with 100;
CREATE TABLE leave_word(
space_id number(10),
user_id number(10),
leave_word_id number(10),
leave_word_content varchar2(2000),
leave_word_publish date,
leave_word_type varchar2(20)
);
create sequence leave_word_sc start with 100;
CREATE TABLE daily_record(
space_id number(10),
user_id number(10),
daily_record_id number(10),
daily_record_content varchar2(2000),
daily_record_publish date,
daily_record_type varchar2(20),
daily_record_head varchar2(50)
);
create sequence daily_record_sc start with 100;
select* from t_user_songchang;
create table t_user_songchang(
id number(8) primary key,
name varchar2(50),
age number(3)
);
create sequence t_user_seq_songchang;
227
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
