数据库基本创造

 1 -- 查询当前数据库的控制文件
 2 select * from v$controlfile;
 3 -- 查询当前数据库对应的数据文件操作记录
 4 select type record_size,records_total,records_used from
 5        V$controlfile_Record_Section
 6        where type=‘DATAFILE‘;
 7 -- 查询当前用户的表空间信息[必须以dba身份登录]
 8 select * from dba_data_files;
 9 -- 查询当前用户可以访问的所有基表
10 select * from cat;

 1 /*
 2   创建表空间
 3   语法:
 4   表空间:create tablespace 表空间名称 datafile 物理文件存放路径 size 文件大小以M为单位
 5   临时表空间:
 6   create temporary tablespace 表空间名称 tempfile 物理文件存放路径 size 文件大小以M为单位
 7 */ 
 8 
 9 create tablespace test_space datafile ‘E:\YC59\Oracle\space\test_space‘ size 10m;
10 create temporary tablespace temp_space tempfile ‘E:\YC59\Oracle\space\temp_space‘ size 10m;
11 
12 -- 修改表空间
13 alter tablespace test_space add datafile ‘E:\YC59\Oracle\space\test_space_1‘ size 10m;
14 
15 -- 修改数据库的默认表空间
16 --alter database default tablespace test_space;
17 alter database default tablespace system;
18 -- 查询数据库的默认表空间
19 select * from database_properties where property_name = ‘DEFAULT_PERMANENT_TABLESPACE‘;
20 
21 -- 删除表空间
22 drop tablespace test_space including contents and datafiles;

 1 /*
 2      创建用户的语法:
 3      create user 用户名 identified by 密码 default tablespace 表空间名称 
 4 */
 5 create user gerry identified by a123 default tablespace test_sapce;
 6 create user zhangsan identified by a123 default tablespace test_sapce;
 7 
 8 /*
 9      给用户授权:grant 权限名称 to 用户名
10 */
11 -- 给gerry用户授予登录的权限
12 grant create session to gerry;
13 -- 给gerry用户授予resource角色
14 grant resource to gerry;
15 -- 给gerry用户授予查询scott用户emp表的操作
16 grant select,update,delete,insert on scott.emp to gerry;
17 
18 /*
19       创建角色
20 */
21 create role pro_role;
22 drop role pro_role;
23 
24 /*
25        给角色授权
26 */
27 grant resource to pro_role;
28 grant create session to pro_role;
29 grant select on scott.emp to pro_role;
30 
31 /*
32       把创建的角色赋予用户gerry,zhangsan
33 */
34 grant pro_role to gerry;
35 grant pro_role to zhangsan;
36 
37 /*
38      回收权限
39      revoke 权限名称/ 角色名称 from 用户
40 */
41 revoke select on scott.emp from gerry;
42 revoke resource from gerry;
43 revoke create session from gerry;
44 revoke pro_role from gerry;
45 
46 /*
47        修改用户的密码,锁定用户，解锁用户
48 */
49 alter user gerry identified by gerry;
50 
51 -- 锁定用户
52 alter user gerry account lock;
53 -- 解锁用户
54 alter user gerry account unlock;
55 
56 /*
57       删除用户
58 */
59 drop user zhangsan cascade;

 1 -- 查询当前用户使用的默认表空间
 2 select * from user_users;
 3 
 4 -- 创建学生表
 5 create table Student1
 6 (
 7        stuId int primary key,
 8        stuName varchar2(20) not null,
 9        stuSex char(2) not null,
10        stuAge integer not null
11 ) tablespace test_sapce; -- 指定表存放的表空间
12 
13 create table studentType
14 (
15        type_Id int primary key,
16        stuId int not null
17 ) tablespace test_sapce; -- 指定表存放的表空间
18 
19 /*
20   约束类型:
21   唯一约束：unique
22   检查约束:check
23   默认约束:defalut
24   主键约束:primary key
25   外键约束:foreign key
26 */
27 
28 --- 给student表的stuName字段添加唯一约束
29 alter table student1
30  add constraint UQ_STUNAME unique(stuName);
31 -- 检查唯一约束
32 insert into Student1 values(1001,‘张三‘,‘男‘,10);
33 insert into Student1 values(1002,‘张三‘,‘男‘,10);
34 
35 -- 给stuAge添加检查约束
36 alter table student1
37  add constraint CK_AGE check(stuAge>0 and stuAge<200);
38 insert into Student1 values(1002,‘张三‘,‘男‘,1010);
39 
40 -- 给stuSex添加默认约束
41 alter table student1 modify(stuSex char(2) default ‘女‘);
42 insert into Student1(stuId,Stuname,Stuage) values(1002,‘张三‘,100);
43 
44 -- 给studetnType表创建外键约束
45 alter table studentType
46       add constraint FK_STUID foreign key(stuId) references Student1(stuId);
47       
48 insert into studentType values(1001,10002);
49 
50 select * from student1;
51 select * from studentTYpe;
52 
53 --- 给student1表添加一个QQ字段
54 alter table student1 add(qq number(11) not null);
55 
56 
57 /*
58   添加数据的语法:insert into 表名<字段1，字段2..... 字段n> values(值1，值2,..... 值n)
59   修改数据的语法:update 表名 set 字段1=值1，字段2=值2 ..... 字段n = 值n where 条件【修改的条件】
60   删除数据的语法:delete from 表名 where[删除的条件]
61   查询数据语法: select 筛选的字段 from 表名 where [筛选的条件]
62 */

 1 /*
 2       序列：
 3       create sequence 序列名称
 4       start with 初始值
 5       increment by 递增值
 6       minvalue 最小值【初始值不能小于最小值】
 7       maxvalue 最大值
 8       cycle/cache 缓存数;
 9 **/
10 
11 create sequence testInfo_seq
12        start with 10001
13        increment by 1
14        minvalue 1
15        maxvalue 999999
16        cache 20;
17 -- 查询当前序列的值
18 select testInfo_seq.Currval from dual;-- 必须使用一次序列该语句才有效
19 -- 使用当前序列
20 select testInfo_seq.Nextval from dual;
21 -- 使用创建的序列
22 insert into testInfo(testId,Testname,Testdesc,Testsex) 
23        values (testInfo_seq.Nextval,‘测试11‘,‘测网速‘,‘男‘);
24 commit;
25 -- 删除序列
26 drop sequence testInfo_seq;

 1 /*
 2        创建视图:
 3        create <or replace> view 视图名称
 4        as
 5               查询语句;
 6        视图只能用来做查询不能编辑
 7 */
 8 
 9 --select testId 编号,testName 测试名称,testDesc 描述,testPass 密码,testSex 性别 from testInfo;
10 create or replace view queryTestInfo
11        as
12        select testId 编号,testName 测试名称,testDesc 描述,testPass 密码,testSex 性别 from testInfo;
13 -- 使用视图
14 select * from queryTestInfo;
15 
16 -- 删除视图
17 drop view queryTestInfo;

 1 /*
 2      同义词：
 3      create or replace [public] synonym 同义词名 for 表名
 4 **/
 5 -- 创建私有同义词
 6 create or replace synonym u for testInfo;
 7 select * from u;
 8 -- 创建公有的同义词
 9 create or replace public synonym u1 for testInfo;
10 select * from u1;-- 其他用户要使用创建的公有同义词必须拥有该表的查询权限

 1 -- 连接表（自连，左右外连接，内连接，全连接）
 2 select * from grade,course,student -- 全连接
 3 -- 查询Scott用户的上司[自连]
 4 select manager.ename from scott.emp manager,scott.emp employee
 5        where manager.empno = employee.mgr and employee.ename=‘SCOTT‘;-- 字段值区分大小写
 6 -- 左右外连接
 7 select * from grade g left join student stu on g.gradeId = stu.gradeid;
 8 select * from grade g right join student stu on g.gradeId = stu.gradeid;
 9 -- 内联接
10 select * from student stu inner join grade g on g.gradeid = stu.gradeid
11 -- 查询张三学生的所有成绩信息
12 select g.gradename,stu.studentname,c.coursename,s.score
13        from student stu inner join grade g on stu.gradeid = g.gradeid
14        inner join score s on s.studentid = stu.studentid
15        inner join course c on s.courseid = c.courseid where stu.studentname = ‘张三‘;
16        
17 select g.gradename,stu.studentname,c.coursename,s.score
18        from grade g,student stu,course c,score s where
19        stu.gradeid = g.gradeid and
20        s.studentid = stu.studentid and
21        s.courseid = c.courseid and
22        stu.studentname = ‘王昭君‘;
23        
24 -- 子查询作为结果
25 select * from student where studentId not in
26 (select studentId from score where 
27        courseId = (select courseId from course where courseName=‘Java‘))
28 -- 子查询作为表
29 select * from (select g.gradename 班级,stu.studentname 姓名,c.coursename 课程,s.score 成绩
30        from grade g,student stu,course c,score s where
31        stu.gradeid = g.gradeid and
32        s.studentid = stu.studentid and
33        s.courseid = c.courseid) newtable where 班级=‘三维班‘
34 -- 使用伪列分页
35 select * from (select student.*,rownum as rowindex from student where rownum<=1) where rowindex>0;

 1 select * from emp;
 2 select * from dept;
 3 
 4 -- 使用Oracle的聚合函数
 5 -- 统计在职员工人数
 6 select count(*) as 在职人数 from emp;
 7 -- 统计在所有员工薪资情况
 8 select max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资,sum(sal) 总工资 from emp;
 9 -- 统计每一个部门的员工薪资情况
10 select deptno 部门编号,max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资,sum(sal) 总工资 
11        from emp group by deptno
12 -- 排序
13 select * from emp order by empno asc;
14 select * from emp order by empno desc;
15 -- 统计每一个部门的员工薪资平均工资大于2000的部门情况
16 select deptno 部门编号,max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资,sum(sal) 总工资 
17        from emp group by deptno having(avg(sal)>2000);-- having经常和group by联用
18 --去除重复的函数
19 create table test
20 (
21        testId number primary key,
22        testName varchar2(20) not null,
23        testStatus varchar2(20) not null
24 )tablespace users;
25 
26 insert into test values(1,‘test‘,‘准备中‘);
27 insert into test values(2,‘test1‘,‘进行中‘);
28 insert into test values(3,‘test2‘,‘进行中‘);
29 insert into test values(4,‘test3‘,‘以完成‘);
30 insert into test values(5,‘test4‘,‘进行中‘);
31 commit;
32 -- 提取测试存在的状态
33 select distinct(testStatus) from test;