emp oracle少了,Oracle SQL基础练习【二】（emp，dept）

一 创建表并插入数据

CREATE TABLE emp

(

empno NUMBER,

ename VARCHAR2(20),

job VARCHAR2(20),

mgr NUMBER,

hiredate DATE,

sal FLOAT,

commn FLOAT,

deptno NUMBER

);

INSERT

INTO emp

(

empno,

ename,

job,

mgr,

hiredate,

sal,

commn,

deptno

)

VALUES

(

1601,

'zhangsan',

'saler',

10,

to_date('2013-2-28 15:42:56','yyyy-mm-dd hh24:mi:ss'),

2000.1,

1000.3,

100

);

雇员表emp如下：

CREATE TABLE dept

( deptno NUMBER, dname VARCHAR2(30), loc VARCHAR2(50)

);

部门表emp如下：

二 操作表

1 基础查询

SELECT*FROM emp;

SELECT empno eno FROM emp;

SELECT DISTINCT job FROM emp;

SELECT job FROM emp;

SELECT 'NO:'||empno||',Name:'||ename||

',Job:'||job FROM emp;

2 添加 主键

ALTER TABLE emp ADD PRIMARY KEY(empno);

ALTER TABLE dept ADD PRIMARY KEY(deptno);

3 IS NOT NULL 和 BETWEEN AND

SELECT * FROM emp WHERE commn IS NOT NULL;

SELECT * FROM emp WHERE sal > 1500 AND commn IS NOT NULL;

SELECT * FROM emp WHERE sal <= 1500 AND commn IS NULL;

SELECT * FROM emp WHERE NOT(sal > 1500 AND commn IS NOT NULL);

SELECT * FROM emp WHERE hiredate BETWEEN '11-01-01' AND '11-12-31';

SELECT * FROM emp WHERE empno NOT IN(1601,1603);

4 oracle对字符串的大小写敏感

SELECT * FROM emp WHERE ename = 'Liulu';

SELECT * FROM emp WHERE ename = 'liulu';

5 LIKE，ASC，DESC

SELECT * FROM emp WHERE hiredate LIKE '%11%';

SELECT * FROM emp WHERE hiredate LIKE '_1%';

SELECT * FROM emp WHERE empno <> 1603;

SELECT * FROM emp WHERE empno != 1603;

SELECT * FROM emp ORDER BY sal ASC;

SELECT * FROM emp ORDER BY sal DESC;

SELECT * FROM emp ORDER BY sal DESC, empno ASC;

6 大小写

SELECT UPPER('jone') FROM dual;

SELECT LOWER('JONE') FROM dual;

SELECT INITCAP(ename) FROM emp;

UPDATE emp SET ename = INITCAP(ename);

UPDATE emp SET ename = 'stu_'||ename;

UPDATE emp SET ename = CONCAT('tu_',ename);

7 oracle中字符串截取从0和1开始都是一样的

SELECT SUBSTR('ABCDEFG',0,4),

LENGTH('CANDIDE'),

REPLACE('JACK and JUE','J','BL')

FROM dual;

SELECT SUBSTR('ABCDEFG',1,4),

LENGTH('CANDIDE'),

REPLACE('JACK and JUE','J','BL')

FROM dual;

8 小插曲1 创建表

CREATE TABLE table_1

( deptno NUMBER, empno NUMBER

);

CREATE TABLE table_2

( empno NUMBER, goods_name VARCHAR2(40)

);

表如下：

插入数据

INSERT INTO table_1(deptno,empno) VALUES(1,001);

INSERT INTO table_1(deptno,empno) VALUES(1,002);

INSERT INTO table_1(deptno,empno) VALUES(2,003);

INSERT INTO table_1(deptno,empno) VALUES(3,004);

INSERT INTO table_1(deptno,empno) VALUES(3,005);

INSERT INTO table_2(empno,goods_name) VALUES(001,'A');

INSERT INTO table_2(empno,goods_name) VALUES(002,'D');

INSERT INTO table_2(empno,goods_name) VALUES(001,'B');

INSERT INTO table_2(empno,goods_name) VALUES(001,'C');

INSERT INTO table_2(empno,goods_name) VALUES(003,'A');

INSERT INTO table_2(empno,goods_name) VALUES(004,'C');

INSERT INTO table_2(empno,goods_name) VALUES(005,'D');

INSERT INTO table_2(empno,goods_name) VALUES(004,'W');

基本操作

SELECT deptno,goods_name

FROM table_1 t1 JOIN table_2 t2

ON t1.empno = t2.empno

ORDER BY t1.deptno;

SELECT deptno,COUNT(*) as goods

FROM table_1 t1 JOIN table_2 t2

ON t1.empno = t2.empno

GROUP BY deptno;

SELECT deptno,COUNT(*) as goods

FROM table_1 t1,table_2 t2

WHERE t1.empno = t2.empno

GROUP BY deptno;

9 小插曲2

VARCHAR2(50)必须指明长度

CREATE TABLE store (

storeno NUMBER

,start_date VARCHAR2(50)

,store_name VARCHAR2(50)

,over_date VARCHAR2(50)

);

ALTER TABLE store MODIFY (storeno VARCHAR2(50));

INSERT INTO store(

storeno

,start_date

,store_name

,over_date)

VALUES(

'C001'

,'2015/1/1'

,'7-11'

,'2099/12/31'

);

INSERT INTO store(

storeno

,start_date

,store_name

,over_date)

VALUES(

'C001'

,'2016/10/1'

,'8-13'

,'2099/12/31'

);

INSERT INTO store(

storeno

,start_date

,store_name

,over_date)

VALUES(

'C003'

,'2015/1/1'

,'JingGong'

,'2099/12/31'

);

表如下：

查询操作：

SELECT s1.storeno,s1.store_name

FROM store s1,

(SELECT storeno,MAX(start_date) AS start_date

FROM store

WHERE start_date <= TO_DATE('2016011','YYYYMMDD')

AND over_date >= TO_DATE('2016011','YYYYMMDD')

GROUP BY storeno) s2

WHERE s1.storeno = s2.storeno

AND s1.start_date = s2.start_date;

上下对比

SELECT s1.storeno,s1.store_name

FROM store s1,

(SELECT storeno,MAX(start_date) AS start_date

FROM store

WHERE start_date <= '20160101'

AND over_date >= '20160101'

GROUP BY storeno) s2

WHERE s1.storeno = s2.storeno

AND s1.start_date = s2.start_date;

修改字段数据类型

ALTER TABLE store MODIFY(start_date DATE);

ALTER TABLE store MODIFY(over_date DATE);

注意：

日期格式数据转换：TO_DATE(),TO_CHAR()

char,varchar,varchar2区别：

char长度固定，varchar只半字和全角字符2字处理，varchar2对所有都2字节处理