Oracle第一课（学习中笔记）

telnet 10.64.10.31
openlab
open123

sqlplus
username:nanjing
password:nanjing
-----BETWEEN
SELECT * FROM TABLE WHERE AGE BETWEEN 21 AND 32;
-----<,<=,>,>=
SELECT * FROM TABLE WHERE AGE >21;
SELECT * FROM TABLE WHERE AGE >=21;
SELECT * FROM TABLE WHERE AGE <21;
SELECT * FROM TABLE WHERE AGE <=21;
-----'_' ADN '%'
SELECT * FROM TABLE WHERE NAME LIKE '____';(6)
SELECT * FROM TABLE WHERE NAME LIKE '%j%";
SELECT * FROM TABLE WHERE NAME LIKE '%"_j%' ESCAPE '"' 转义字符 此 可以查找出文件中的有‘_‘的字符
-----IS NULL
SELECT * FROM TABLE WHERE NAME IS NULL;
-----多条件查询
利用括号变更它的执行方式。我们使用多条件查询如下
SELECT * FROM TABLE WHERE (NAME IS LIKE '____' AND AGE < 21 ) OR AGE >32;
SELECT * FROM TABLE WHERE AGE >20 AND AGE < 40 ADN NAME LIKE '%j';
SELECT * FROM TABLE WHERE ADDRESS ='bj' OR  address = 'nj' AND age >30;
//上面的内容我们考虑到了版本的问题，那么我们所要执行的语句执行的结果我们要用“（）”来限制执行。
-----ORDER BY 排序
SELECT * FROM TABLE WHERE NAME LIEK '___' ORDER BY NAME ;（升序排序）
SELECT * FROM TABLE WHERE NAME LIKE '___' ORDER BY NAME DESC;(降序排序）
Sorting by Muitiple Columns
SELECT * FROM TABLE WHERE NAME LIKE '___' ORDER BY NAME ,ID ,AGE DESC;
-----SELECT statement 函数
单行函数：SELECT COUNT(*) FROM TABLE ; COUNT();
Character,Number,Date,Conversion
多行函数；所有信息都转换成大写
Group,
SELECT UPPER(NAME) FROM TABLE ; UPPER(NAME)转换成大写；
SELECT LOWER(NAME) FROM TABLE;
SELECT INITCAP(NAME) FROM TABLE; INITCAP(NAME)首字母变成大写
SELECT CONCAT('GOOD','STRING') FROM TABLE;连接两个字符串
SELECT SUBSTR(NAME,1,2) FROM TABLE;截取前两位
SELECT NVL(NAME,'NULLNAME') FROM TABLE;NVL(NAME,'NULLNAME')将空NULL替换为'NULLNAME'
SELECT LENGTH(NAME) FROM TABLE; LENGTH(NAME)当前查找到的name的长度；

---number functions;
round 四舍五入
SELECT ROUND(AGE/10) FROM TABLE;
//前面那位代表要操作的数据，后面那位代表截取的位置（即在哪个开始四舍五入）
ROUND (45.923,2)---->45.92
ROUND (45.923,0)---->46
ROUND (45.923,-1)--->50
TRUNC 直接去除小数
SELECT TRUNC(AGE/10)FROM TABLE;
TRUNC (45.923,2)---->45.92
TRUNC (45.923,0)---->45
TRUNC (45.923,-1)--->40
------TO_CHAR Function with Numbers;Syntax
// to_char ,to _number
SELECT TO_CHAR (3456.789,'$9,999.999') FROM TABLE;
SELECT TO_NUMBER('123.123') FROM TABLE;
函数的嵌套
SELECT last_name,NVl(TO_CHAR(manager_id),'No Manager') FROM s_emp WHERE manager_id IS NULL;


第五部分 夺表连接查询
一、 建立多张表
1.jul12_class_03
CREATE TABLE jul12_class_03(
    id number(4),
    name varchar2(10)
);
2,jul12_student_03
CREATE TABLE jul12_student_03(
    id number(3),
    sname varchar2(10),
    cid number(4)
);
输入记录
insert into jul12_student_03 values ....
insert into jul12_class_03 values....
执行连接
－－－－－－－笛卡尔积
 SELECT a.id,a.name,b.sname FROM jul12_class_03 a, jul12_student_03 b;
－－－－－－－等值连接
SELECT  b.id StudentID ,a.name ClassName,b.sname StudentName FROM jul12_class_03 a, jul12_student_03 b WHERE a.id= b.cid;