1、创建用户 CREATE USER <用户名> IDENTIFIED BY <密码> ALTER USER SYSTEM IDENTIFIED BY a12345; DROP USER SYSTEM 2、给用户赋予权限连接权限 CONNECT 使用资源权限 RESOURCE 创建表的权限 CREATE TABLE DBA DBA GRANT <权限名称> TO <用户名> REVOKE <权限名称> FROM <用户名> 3、创建表 CREATE TABLE demo_user( ur_id Integer PRIMARY KEY NOT NULL, ur_user_name VARCHAR2(20) NOT NULL UNIQUE, ur_password VARCHAR2(20) NOT NULL ); 4、--创建表结构 DEFAULT NOT NULL UNIQUE CONSTRAINT pk PRIMARY KEY (字段列表) CONSTRAINT ck CHECK (check条件) CONSTRAINT fk FOREIGN KEY (从表外键字段) REFERENCES 主表名(主表被引用的字段) 5、--修改表结构 --修改表名 ALTER TABLE demo_test1 RENAME TO demo_demo1; --修改字段字段名 ALTER TABLE demo_demo1 RENAME COLUMN tst_name TO tst_user_name; --修改字段的类型 ALTER TABLE demo_demo1 MODIFY (tst_sex VARCHAR2(4)); --添加字段 ALTER TABLE demo_demo1 ADD tst_name VARCHAR2(20) ; --删除字段 ALTER TABLE demo_demo1 DROP COLUMN tst_user_name; --删除约束 ALTER TABLE demo_demo1 DROP CONSTRAINT ck_tst_sex; --添加约束 ALTER TABLE demo_demo1 ADD CONSTRAINT ck_tst_sex CHECK (tst_sex='男' OR tst_sex='女' OR tst_sex='a'); 6、例子包含序列对象sequence -- 聊天工具的基本表 --用户表 CREATE TABLE chat_user( ur_id INTEGER NOT NULL, ur_account VARCHAR2(20) NOT NULL UNIQUE, ur_password VARCHAR2(20) NOT NULL, ur_alia_name VARCHAR2(100), ur_name VARCHAR2(100), ur_sex CHAR(4) DEFAULT '女' NOT NULL , ur_age INTEGER DEFAULT 1 NOT NULL, CONSTRAINT pk_user PRIMARY KEY (ur_id), CONSTRAINT ck_ur_sex CHECK (ur_sex = '女' OR ur_sex='男'), CONSTRAINT ck_ur_age CHECK (ur_age>=1 AND ur_age<=150) ); INSERT INTO chat_user VALUES(seq_chat.nextval,'0001','123','Demo','张三','男',20); SELECT * FROM chat_user; -- 聊天消息表 CREATE TABLE chat_message( mg_id INTEGER NOT NULL, mg_from INTEGER NOT NULL, mg_to INTEGER NOT NULL, mg_time timestamp NOT NULL, mg_content VARCHAR2(300), CONSTRAINT pk_message PRIMARY KEY (mg_id), CONSTRAINT fk_mg_from FOREIGN KEY (mg_from) REFERENCES chat_user(ur_id), CONSTRAINT fk_mg_to FOREIGN KEY (mg_to) REFERENCES chat_user(ur_id) ); --好友表 CREATE TABLE chat_friend( fr_self_id INTEGER NOT NULL, fr_friend_id INTEGER NOT NULL, CONSTRAINT pk_friend PRIMARY KEY (fr_self_id,fr_friend_id), CONSTRAINT fk_self_id FOREIGN KEY (fr_self_id) REFERENCES chat_user(ur_id), CONSTRAINT fk_friend_id FOREIGN KEY (fr_friend_id) REFERENCES chat_user(ur_id) ); --群组表 CREATE TABLE chat_group( gr_id INTEGER NOT NULL,--群组ID gr_account VARCHAR2(20) NOT NULL UNIQUE,--群账号 gr_name VARCHAR2(100) NOT NULL,--群名称 gr_create_ur_id INTEGER NOT NULL,--创建者id gr_image VARCHAR2(200),--群头像 gr_create_time TIMESTAMP NOT NULL,--创建时间 gr_title VARCHAR2(400),--群主题 CONSTRAINT pk_gr_id PRIMARY KEY (gr_id), CONSTRAINT fk_gr_create_ur_id FOREIGN KEY (gr_create_ur_id) REFERENCES chat_user(ur_id) ); --群成员表 CREATE TABLE chat_group_member( gm_gr_id INTEGER NOT NULL,--群id gm_ur_id INTEGER NOT NULL,--成员id gm_type VARCHAR2(3) DEFAULT '1' NOT NULL, --成员等级 1普通成员 2 普通管理员 3超级管理员 CONSTRAINT pk_group_member PRIMARY KEY (gm_gr_id,gm_ur_id), CONSTRAINT fk_gm_gr_id FOREIGN KEY (gm_gr_id) REFERENCES chat_group(gr_id), CONSTRAINT fk_gm_ur_id FOREIGN KEY (gm_ur_id) REFERENCES chat_user(ur_id), CONSTRAINT ck_gm_type CHECK (gm_type IN ('1','2','3')) ); --公告表 CREATE TABLE chat_notice( nt_id INTEGER NOT NULL, --公共ID nt_gr_id INTEGER NOT NULL,--群id nt_ur_id INTEGER NOT NULL,--发布人id nt_time TIMESTAMP NOT NULL,--发布时间 nt_content VARCHAR2(500) NOT NULL,-- 公告内容 CONSTRAINT pk_notice PRIMARY KEY (nt_id), CONSTRAINT fk_nt_gr_id FOREIGN KEY (nt_gr_id) REFERENCES chat_group(gr_id), CONSTRAINT fk_nt_ur_id FOREIGN KEY(nt_ur_id) REFERENCES chat_user(ur_id) ); --群聊天信息表 CREATE TABLE chat_group_message( grm_id INTEGER NOT NULL,--消息ID grm_gr_id INTEGER NOT NULL,--群ID grm_ur_id INTEGER NOT NULL,--说话人ID grm_time TIMESTAMP NOT NULL,--说话时间 grm_content VARCHAR2(500) NOT NULL,--说话内容 CONSTRAINT pk_group_message PRIMARY KEY (grm_id), CONSTRAINT fk_grm_gr_id FOREIGN KEY (grm_gr_id) REFERENCES chat_group(gr_id), CONSTRAINT fk_grm_ur_id FOREIGN KEY (grm_ur_id) REFERENCES chat_user(ur_id) ); --序列对象 nextval CREATE SEQUENCE seq_chat; --一般这样就够了 SELECT seq_chat.nextval FROM dual; SELECT seq_chat.currval FROM dual; CREATE SEQUENCE emp_sequence INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 CACHE 10; CREATE SEQUENCE seq_test INCREMENT BY -1 START WITH 100 MAXVALUE 100 NOMINVALUE NOCYCLE CACHE 10; SELECT seq_test2.nextval FROM dual; CREATE SEQUENCE seq_test2 --指定sequence的一些限制 INCREMENT BY 2 START WITH 5 MAXVALUE 10 MINVALUE -10 CYCLE CACHE 5; 7、运算符 java sql > > < < >= >= <= <= != <> != == = && and || OR ! NOT IN LIKE % 0到n个任何符号 _ 一个任何符号 BETWEEN a AND b IS [NOT] --是否等于 null 例子 --1、将账号为001的用户密码改成888888 UPDATE chat_user SET ur_password = '888888' WHERE ur_account = '001'; --2、将账号为002的用户姓名改成李四，年龄改成20 UPDATE chat_user SET ur_name = '李四', ur_age=20 WHERE ur_account = '002'; --3、将姓名中包含有”山“的用户的别名都改成 "华山" --4、将姓名中包含有"A" 和 "B"的年龄都改成17 UPDATE chat_user SET ur_age=17 WHERE ur_name LIKE '%A%' AND ur_name LIKE '%B%'; --5、将年龄在18到20的所有用户的性别，改成女 UPDATE chat_user SET ur_sex='女' WHERE ur_age>=18 AND ur_age<=20; UPDATE chat_user SET ur_sex='女' WHERE ur_name BETWEEN 'a' AND 'b';--表示 a-b之间，a一定要小于等于b --6、将所有聊天消息中包含有 "资本主义" 或 "反党" 的内容，都改成 "*****" UPDATE chat_message SET mg_content='*******' WHERE mg_content LIKE '%资本主义%' OR mg_content like '%反党%'; --7、将id为2的聊天消息前面，都加上 "002说：" UPDATE chat_message SET mg_content='002说:'||mg_content WHERE mg_id=2; --8、给群聊消息表中所有的聊天消息前面，都加上对应的聊天人id+说:开头 UPDATE chat_group_message SET grm_content = grm_id || grm_content; 8、统计函数及日期统计函数 sum--求和 avg--平均 count--总数 max--最大值 min--最小值 NVL (expr1, expr2):expr1为NULL，返回expr2；不为NULL，返回expr1 NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型 NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1 SELECT count(*) FROM demo_user;--10 SELECT count(ur_age) FROM demo_user;--不统计null 9 SELECT sum(ur_age + ur_id) FROM demo_user;--不把null计算 null当0计算 SELECT avg(nvl(ur_age,0)+ur_id) FROM demo_user; SELECT max(ur_age) FROM demo_user; --ASCII --abcdef --abcdefa SELECT seq_test3.nextval FROM dual; SELECT * FROM dual; --日期 --ADD_MONTHS(日期,增值):追加一个月份 SELECT add_months(sysdate,1) FROM dual; --MONTHS_BETWEEN(日期，日期):两日期相差多少月 SELECT months_between(to_date('2015-10-9','yyyy-mm-dd'),sysdate ) FROM dual; LAST_DAY(日期):返回当月最大天数 NEXT_DAY(日期,星期)：获得某一日期后的第一个星期的值 TRUNC(date,[fmt]):舍去某一日期类型 EXTRACT(fmt from date):从当前日期提取一日期类型 9、字符串 --Initcap(char) Select initcap(‘hello’) from dual; Hello SELECT initcap('hello world') FROM dual; Lower(char) Select lower(‘FUN’) from dual; fun Upper(char) Select upper(‘sun’) from dual; SUN --Ltrim(char,set) Select ltrim( ‘xyzadams’,’xyz’) from dual; adams --trim() --Rtrim(char,set) Select rtrim(‘xyzadams’,’ams’) from dual; xyzad SELECT rtrim('123abcefg123123', '123') FROM dual; --Translate(char, from, to) Select translate(‘jack’,’j’ ,’b’) from dual; back SELECT Translate('11223344556633333','3456','abc') FROM dual; --Replace(char, searchstring,[rep string]) Select replace(‘jack and jue’ ,’j’,’bl’) from dual; black and blue SELECT replace('11223344556633333','33','abc') FROM dual; Instr (char, m, n) Select instr (‘worldwide’,’d’) from dual; 5 Substr (char, m, n) Select substr(‘abcdefg’,3,2) from dual; cd Concat (expr1, expr2) Select concat (‘Hello’,’ world’) from dual; Hello world 10、算术 Abs(n) Select abs(-15) from dual; 15 --Ceil(n) Select ceil(44.778) from dual; 45 获取大于这个数的最小整数向上取整 Cos(n) Select cos(180) from dual; -.5984601 Sin(n) Select sin(0) from dual; 0 --Floor(n) Select floor(100.2) from dual; 100 小于自己的最大整数 Math Power(m,n) Select power(4,2) from dual; 16 Mod(m,n) Select mod(10,3) from dual; 1 --Round(m,n) Select round(100.256,2) from dual; 100.26 Trunc(m,n) Select trunc(100.256,2) from dual; 100.25 Sqrt(n) Select sqrt(4) from dual; 2 Sign(n) Select sign(-30) from dual; -1 11、转换 TO_CHAR SELECT to_char(1111) FROM dual; TO_DATE TO_NUMBER SELECT to_number('1111')+2 FROM dual; SELECT '111' + 2 FROM dual; 12、其它 --CHR和ASCII SELECT ascii('A') FROM dual; LPAD和RPAD SELECT lpad('abcd',12,'ab') FROM dual; TRIM LENGTH SELECT length('abc') FROM dual; --DECODE 相当于java的switch语句 SELECT ur_id,decode(ur_id, 1,'啊啊啊啊啊啊啊啊啊', 6,'哦哦哦哦哦哦哦哦') test FROM demo_user; SELECT * FROM demo_user; SELECT * FROM demo_user; 13、经典例子总结 --1.分析清楚结果 select --2.来源 from --3.过滤条件 where --4.其它 order by --1. 找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。 SELECT ename FROM scott.emp WHERE ename like '__A%'; --2. 找出EMP表员工名字中含有A 和N的员工姓名。 SELECT ename FROM scott.emp WHERE ename like '%A%' AND ename like '%N%'; --3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。 SELECT ename 姓名,sal + nvl(comm,0) as 工资, nvl(comm,0) 佣金 FROM scott.emp WHERE comm IS NOT NULL ORDER BY sal + nvl(comm,0) ASC, nvl(comm,0) DESC; --4. 列出部门编号为20的所有职位。 SELECT DISTINCT job FROM scott.emp WHERE deptno=20; --5. 列出不属于SALES 的部门。 SELECT * FROM scott.dept; SELECT dname FROM scott.dept WHERE dname <> 'SALES'; --6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。 SELECT ename, sal + nvl(comm,0) 工资 FROM scott.emp WHERE NOT (sal + nvl(comm,0) BETWEEN 1000 AND 1500) ORDER By (sal + nvl(comm,0)) DESC; --7. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。 SELECT ename, job, (sal + nvl(comm,0))*12 FROM scott.emp WHERE job IN ('MANAGER','SALESMAN') AND (sal + nvl(comm,0))*12 BETWEEN 15000 AND 20000; --8. 说明以下两条SQL语句的输出结果： SELECT EMPNO,COMM FROM scott.EMP WHERE COMM IS NULL; SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL; *9. 让SELECT 语句的输出结果为 (提示：USER_TABLES表中可以查出所有表的信息，如表名) SELECT * FROM SALGRADE; SELECT * FROM BONUS; SELECT * FROM EMP; SELECT * FROM DEPT; SELECT * FROM user_tables; SELECT 'SELECT * FROM ' || table_name || ';' FROM user_tables; …… 列出当前用户有多少张数据表，结果集中存在多少条记录。 --10. 判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错，为什么？ --子查询 --GROUP BY 和 HAVING --联合查询 --在查询语句中，嵌套另外一个查询子查询 SELECT FROM WHERE ORDER BY --select 查询出每个员工的姓名和部门名称 SELECT scott.emp.ename, (SELECT scott.dept.dname FROM scott.dept WHERE scott.dept.deptno = scott.emp.deptno) FROM scott.emp; --from 用子查询查询出sal 在1500 DAO 2000之间的所有员工的姓名 SELECT ename, sal FROM( SELECT * FROM scott.emp WHERE sal>=2500 ) WHERE sal<=4000; --where 查询出SALES 和 RESEARCH部门的所有员工名称 SELECT ename FROM scott.emp WHERE scott.emp.deptno IN (SELECT deptno FROM scott.dept WHERE dname = 'SALES' OR dname='RESEARCH'); --分页查询出第二页的员工信息（每页显示3条） SELECT * FROM (SELECT scott.emp.*, rownum mynum FROM scott.emp WHERE rownum<=12) WHERE mynum>=9; SELECT * FROM scott.emp; -- GROUP BY --统计每个部门的人数 SELECT scott.emp.deptno,count(*) FROM scott.emp GROUP BY scott.emp.deptno; SELECT (SELECT dname FROM scott.dept WHERE scott.dept.deptno=scott.emp.deptno),count(*) FROM scott.emp GROUP BY scott.emp.deptno; --统计每个岗位的平均工资，显示岗位名称和平均工资 SELECT job, avg(sal + nvl(comm,0)) FROM scott.emp --WHERE avg(sal + nvl(comm,0))>2000 GROUP BY job HAVING avg(sal + nvl(comm,0))>2000; -- 统计每个部门中，每个岗位的平均工资，显示部门名称、岗位名称、和平均工资 SELECT (SELECT dname FROM scott.dept WHERE scott.dept.deptno=scott.emp.deptno),job,avg(sal + nvl(comm,0)) FROM scott.emp GROUP BY scott.emp.deptno,job ORDER BY scott.emp.deptno; SELECT FROM WHERE GROUP BY HAVING ORDER BY --1．列出至少有一个员工的所有部门。 SELECT dname FROM dept WHERE deptno IN( SELECT deptno FROM emp GROUP BY deptno HAVING count(*)>0); SELECT d.dname,count(e.ename) FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno GROUP BY d.dname HAVING count(e.ename)>0; SELECT d.dname,count(e.ename) FROM emp e INNER JOIN dept d ON e.deptno = d.deptno GROUP BY d.dname HAVING count(e.ename)>0; --2．列出薪金比“SMITH”多或相同的所有员工 SELECT ename FROM emp WHERE sal >= (SELECT sal FROM emp WHERE ename='SMITH') AND ename <> 'SMITH'; --3．列出所有员工的姓名及其直接上级的姓名。 SELECT B.ename, (SELECT A.ename FROM emp a WHERE a.empno=B.mgr) FROM emp B; SELECT A.ename,B.ename FROM emp A LEFT OUTER JOIN emp B ON A.Mgr = B.Empno; SELECT A.ename,B.ename FROM emp A INNER JOIN emp B ON A.Mgr = B.Empno; --4．列出受雇日期早于其直接上级的所有员工。 SELECT B.ename,B.hiredate FROM emp B WHERE B.hiredate < (SELECT A.hiredate FROM emp A WHERE A.Empno=B.Mgr); SELECT A.Ename, A.Hiredate, B.ename, B.hiredate FROM emp A, emp B WHERE a.mgr = b.empno AND A.Hiredate1500; --8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。 SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname='SALES'); --9．列出薪金高于公司平均薪金的所有员工。 SELECT ename FROM emp WHERE sal>(SELECT avg(sal) FROM emp); 10．列出与“SCOTT”从事相同工作的所有员工。 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 13．列出在每个部门工作的员工数量、平均工资和平均服务期限。 14．列出所有员工的姓名、部门名称和工资。 15．列出所有部门的详细信息和部门人数。 16．列出各种工作的最低工资。 17．列出各个部门的MANAGER（经理）的最低薪金。 18．列出所有员工的年工资,按年薪从低到高排序。 SELECT * FROM employees; --函数 --1. 让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是￥和$。 SELECT to_char(salary,'L99,999.99') FROM employees; --2. 列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。 SELECT FIRST_Name || LAST_NAME, SALARY, ROUND(salary*1.08, 2) FROM employees WHERE ROWNUM <=5; 14、关联及视图 --关联 -- 将多个表的字段，整合在一起，形成一个表（临时表） -- 内关联 -- 相等内关联 -- 不等内关联 -- 外关联 -- 左外 -- 右外 -- 全关联 -- 笛卡尔乘积关联 --视图 create or replace view view_emp_dept as select emp.ename,emp.job,dept.dname from scott.emp left join scott.dept on scott.emp.deptno=scott.dept.deptno --也可以指定列名 create or replace view view_emp_dept (ename,job,dname) as select emp.ename,emp.job,dept.dname from scott.emp left join scott.dept on scott.emp.deptno=scott.dept.deptno --本次这二种效果一模一样 -- 查询出所有员工的部门名称和自己名称 SELECT A.ename, B.dname FROM emp A INNER JOIN dept B ON A.Deptno <> B.Deptno; SELECT A.ename, B.dname FROM emp A, dept B WHERE A.Deptno = B.Deptno; --左外以左边的表为标准，先列出左边的所有记录，跟右边的记录进行关联。能关联上，就关联不能关联上： 1）多余的数据，不敢 2）没有的数据，用null值代替 SELECT A.deptno,A.dname, B.ename FROM dept A LEFT OUTER JOIN emp B ON A.Deptno = B.Deptno; --左外关联 SELECT A.dname, count(B.ename) FROM dept A LEFT OUTER JOIN emp B ON A.Deptno = B.Deptno GROUP BY A.dname; --右外关联 SELECT A.dname, count(B.ename) FROM dept A RIGHT OUTER JOIN emp B ON A.Deptno = B.Deptno GROUP BY A.dname; SELECT A.dname, count(B.ename) FROM dept A INNER JOIN emp B ON A.Deptno = B.Deptno GROUP BY A.dname; -- 全外关联 SELECT A.deptno,A.dname, B.ename FROM dept A FULL JOIN emp B ON A.Deptno = B.Deptno; -- 笛卡尔乘积关联 SELECT A.deptno,A.dname, B.ename FROM dept A CROSS JOIN emp B;

ywzqwwt