首页 > 其他 > 详细

orancle二维表创建与修改

时间:2019-09-12 20:49:31      阅读:80      评论:0      收藏:0      [点我收藏+]

 

 

代码创建表

技术分享图片
 1 ---Oracle的二维表管理
 2     --oracle二维表的创建
 3 ---------------------------------------
 4  --创建表:
 5     --使用:create table 表名(字段名 类型,字段名 类型,....);
 6     --数据类型:
 7         --number类型
 8               --数值类型
 9                    --整数类型 number(a)   总长度为a
10                    --浮点数类型 number(a,b) 总长度为a,小数位长度为b,小数位可以不写。
11         --varchar2类型
12                 --字符类型 varchar2(ln) ln表示字符的最大长度,实际存储内存长度是根据字符大小来分配,但是最大不能超过ln 
13                 --特点:动态分配存储空间,节省空间
14         --char类型
15                 --字符类型 char(ln) 不管字符数据长度是多大,直接开辟ln大小的空间存储数据
16                 --特点:存储效率高于varchar2
17         --date类型
18     create table student(
19       sno number(10),
20       sname varchar2(100),
21       sage number(3),
22       ssex char(100),
23       sfav varchar2(500),
24       sbirth date   
25     )
26  --添加测试数据
27     insert into student values(1,柳岩,18,,拍电影01-1月-1985);
28     insert into student values(2,古力娜扎,20,,拍电影,to_date(1990-01-01,yyyy-mm-dd));
29     select * from student
View Code

 

 二维表的维护:

 1: 添加,修改字段

 2: 修改,删除表名

技术分享图片
 1 ---oracle学习
 2   --oracle的管理系统学习
 3   --oracle的数据管理学习
 4   --oracle的用户管理
 5   --oracle二维表管理
 6     --二维表的创建
 7         --创建语句
 8         --约束
 9     --修改二维表
10 ---------------------------------------------------
11 --二维表的维护
12     --添加新的字段:
13         --alter table 表名 add 字段名 类型
14          alter table student add sphone number(11)--在学生表中添加新的字段
15     --修改原有字段
16          --修改字段类型
17              --alter table 表名 modify 字段名 新的类型
18              alter table  student modify sphone varchar2(11)
19          --修改字段名
20              --alter table 表名 rename column 字段名 to 新的字段名
21              alter table student rename column sphone to phone 
22          --删除字段
23              --alter table 表名 drop column 字段名
24              alter table student drop column phone
25     --修改表名
26          --rename 原有表名 to 新的表名
27          rename student to student2
28          rename student2 to student
29     --删除表
30         --drop table 表名
31         drop table student 
View Code

 

创建表添加约束

  主键,非空,唯一,外键

技术分享图片
  1 ---Oralce的二维表操作
  2     --创建表并同时添加约束
  3       --主键约束
  4       --非空约束
  5       --检查约束
  6       --唯一约束
  7       --外键约束
  8     --简单的表创建和字段类型
  9     --简单的创建语句:
 10       create table student(
 11            sno number(10) ,--primary key
 12            sname varchar2(100) ,--not null
 13            sage number(3), --check(sage<150 and sage>0)
 14            ssex char(4) ,--check(ssex=‘男‘ or ssex=‘女‘)
 15            sfav varchar2(500),
 16            sbirth date,
 17            sqq varchar2(30) --unique
 18            --constraints pk_student_sno primary key(sno)--添加主键约束
 19            --constraints ck_student_sname check(sname is not null)--非空约束
 20            --constraints ck_student_sage check(sage<150 and sage>0)--检查约束
 21            --constraints ck_student_ssex check(ssex=‘男‘ or ssex=‘女‘)--检查约束
 22            --constraints un_student_sqq unique(sqq)--唯一约束
 23       )   
 24       --添加主键约束
 25        alter table student add  constraints pk_student_sno primary key(sno); 
 26        alter table student drop  constraints pk_student_sno;
 27      --添加非空约束
 28         alter table student add  constraints ck_student_sname check(sname is not null);
 29          alter table student drop  constraints ck_student_sname; 
 30       --添加检查约束
 31       alter table student add constraints ck_student_sage check(sage<150 and sage>0)
 32       alter table student drop  constraints ck_student_sage; 
 33       --添加检查约束校验性别
 34        alter table student add constraints ck_student_ssex check(ssex= or ssex=)
 35        alter table student drop  constraints ck_student_ssex; 
 36        --添加唯一约束
 37        alter table student add constraints un_student_sqq unique(sqq)
 38        select * from student
 39        drop table student
 40 ------------------------------------------------------------------------------------------------------------------
 41 --二维表创建约束学习:(主键约束,非空约束,检查约束,唯一约束)
 42     --问题1:学号重复依然可以添加
 43        insert into student values(1,关晓彤,18,,拍电影,to_date(2000-01-01,yyyy-mm-dd),267889900);
 44        insert into student values(1,关晓彤001,18,,拍电影,to_date(2000-01-01,yyyy-mm-dd),267889900);
 45     --问题2:竟然可以没有名字
 46        insert into student values(2,‘‘,18,,拍电影,to_date(2000-01-01,yyyy-mm-dd),267889900);
 47     --问题3:年龄竟然可以超过200岁
 48        insert into student values(3,关晓彤002,300,,拍电影,to_date(2000-01-01,yyyy-mm-dd),267889900);
 49     --问题4:性别竟然可以为任意字符
 50        insert into student values(4,关晓彤,18,a,拍电影,to_date(2000-01-01,yyyy-mm-dd),267889900);
 51     --问题5:qq号竟然可以重复
 52        insert into student values(5,关晓彤003,18,,拍电影,to_date(2000-01-01,yyyy-mm-dd),267889900);
 53        insert into student values(6,关晓彤004,18,,拍电影,to_date(2000-01-01,yyyy-mm-dd),267889900);
 54 ---解决:
 55     --问题1:添加主键,主键特点:非空唯一
 56        --使用:
 57             --直接在创建表的字段后使用 primary key
 58             --在创建表的语句的最后面使用 constraints pk_表名_字段名 primary key(字段名)
 59             --在创建表后使用 alter table 表名 add  constraints pk_表名_字段名 primary key(字段名);
 60             --删除主键     alter table student drop  constraints 主键的约束名;
 61     --问题2:使用非空约束
 62        --使用:
 63             --直接在创建表的字段后使用 not null 关键字 
 64             --在创建表的语句的最后面使用 constraints ck_表名_字段名 check(字段名 is not null)
 65             --在创建表后使用 alter table 表名 add  constraints ck_表名_字段名 check(字段名 is not null);
 66             --删除非空约束 alter table student drop  constraints 非空约束名;
 67     --问题3:使用检查约束
 68             --直接在创建表的字段后使用 check(条件) 例如      sage number(3) check(sage<150 and sage>0),
 69             --在创建表的语句的最后面使用 constraints ck_表名_字段名 check(条件)
 70             --在创建表后使用 alter table 表名 add  constraints ck_表名_字段名 check(条件);
 71             --删除检查约束 alter table student drop  constraints 检查约束名;
 72 
 73     --问题4:使用检查约束
 74             --直接在创建表的字段后使用 check(条件)
 75             --在创建表的语句的最后面使用 constraints ck_表名_字段名 check(条件)
 76             --在创建表之后使用alter table 表名 add  constraints ck_表名_字段名 check(条件);
 77             --删除检查约束 alter table 表名 drop  constraints 检查约束名;
 78     --问题5:使用唯一约束
 79             --直接在创建表的字段后使用 unique
 80             --在创建表的语句后面使用 constraints un_表名_字段名 unique(字段名);
 81             --在创建表后使用 alter table 表名 add  constraints un_表名_字段名 unique(字段名);
 82             --删除约束:alter table 表名 drop  constraints 唯一约束名;
 83 --------------------------------------------------------------------------------------------------------
 84 --二维表创建 外键约束学习:
 85    --创建学生表
 86    create table student(
 87          sno number(10) primary key,
 88          sname varchar2(100) not null,
 89          sage number(3) check(sage>0 and sage<150),
 90          ssex char(4) check(ssex= or ssex=),
 91          sfav varchar2(500),
 92          sqq varchar2(30) unique,
 93          cid number(10) --references clazz(cno)
 94          --constraints fk_student_cid foreign key(cid) references clazz(cno)--外键
 95       )
 96       --添加外键
 97       alter  table student add constraints fk_student_cid foreign key(cid) references clazz(cno) on delete set null
 98       alter  table student drop constraints fk_student_cid 
 99       drop table student
100       --添加测试数据
101       insert into student values(1,张三001,18,,唱歌,657889900,1);
102       insert into student values(2,张三002,18,,唱歌,657889901,1);
103       insert into student values(3,李四001,18,,唱歌,657889903,2);
104       insert into student values(4,李四002,18,,唱歌,657889904,2);
105    --创建班级表
106       create table clazz(
107        cno number(10) primary key,
108        cname varchar2(100) not null,
109        cdesc varchar2(300)
110       
111       )
112       --添加测试数据
113        insert into clazz values(1,java高薪就业班,6666);
114        insert into clazz values(2,python高薪就业班,33333);
115   --查询学生及其班级信息
116       select * from student  s
117       inner join clazz c
118       on s.cno=c.cno
119   --问题:竟然可以在学生表中插入一个不存在班级
120       insert into student values(5,李四003,18,,唱歌,657889905,3);
121   --使用外键:
122       --作用:当在子表中插入的数据在父表中不存在,则会自动报错。
123       --概念:当一张表的某个字段的值需要依赖另外一张表的某个字段的值,则使用外键约束。
124              --其中主动依赖的表称为子表,被依赖的表称为父表。外键加在子表中。
125       --使用: 
126              --在子表中的字段后直接使用   references 父表名(字段) 例如: cid number(10) references clazz(cno)
127              --在创建表语句的最后面使用  constraints fk_子表名_字段名 foreign key(字段名) references 父表名(字段名)
128              --在创建表后使用:alter table 表名 add constraints fk_子表名_字段名 foreign key(字段名) references 父表名(字段名)
129             --删除外键:alter table 表名 drop constraints 外键约束名
130       --外键选取:
131             --一般选取父表的主键作为子表的外键。
132       --外键的缺点:
133             --无法直接删除父表数据,除非级联删除
134             --级联删除:在添加外键约束时,使用关键字 on delete cascade
135                      --使用:当删除父表数据时,自动删除子表相关所有数据。
136                      --缺点:无法保留子表历史数据。
137                      --使用关键字 on delete set null
138                            --删除父表数据时,将子表中的依赖字段的值设置为null。
139                            --注意:子表依赖字段不能添加非空约束。
140            --删除班级1的信息
141            select * from student
142            delete from clazz where cno=1 
View Code

 

 

 

练习

技术分享图片
 1 --1.选择部门30中的所有员工.
 2 select * from emp where deptno=30
 3 --2.列出所有办事员(CLERK)的姓名,编号和部门编号.
 4 select ename,empno,deptno from emp where job=CLERK
 5 --3.找出佣金高于薪金的员工.
 6 select * from emp where sal<comm
 7 --4.找出佣金高于薪金的60%的员工.
 8 select * from emp where sal*0.6 <comm
 9 --5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
10 select * from emp where (job=MANAGER and deptno=10) or (deptno=20 and job=CLERK)
11 --6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
12 select * from emp where (job=MANAGER and deptno=10) or (deptno=20 and job=CLERK)or (job<>MANAGER and job<>CLERK and sal>=2000)
13 --7.找出收取佣金的员工的不同工作.
14 select distinct job from emp where comm is not null
15 --8.找出不收取佣金或收取的佣金低于100的员工.
16 select * from emp where comm is  null or comm <100
17 --9.找出各月倒数第3天受雇的所有员工.
18 select * from emp where hiredate=last_day(hiredate)-2
19 --10.找出早于12年前受雇的员工.
20 --11.以首字母大写的方式显示所有员工的姓名
21 select INITCAP(ename) from emp 
22 --12.显示正好为5个字符的员工的姓名.
23 select ename from emp where length(ename)=5
24 --13.显示不带有"R"的员工的姓名.
25 select * from emp where ename not like %R%
26 --14.显示所有员工姓名的前三个字符.
27 select substr(ename,1,3) from emp
28 --15.显示所有员工的姓名,用a替换所有"A"
29 select replace(ename,A,a) from emp 
30 --16.显示满35年服务年限的员工的姓名和受雇日期.
31 select ename,hiredate, floor((sysdate-hiredate)/365) from emp  where (sysdate-hiredate)/365>35
32 --17.显示员工的详细资料,按姓名排序.
33 select * from emp order by ename
34 --18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
35 select ename ,hiredate,floor((sysdate-hiredate)/365) years from emp order by hiredate asc
36 --19.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
37 select ename,job,sal from emp order by job desc,sal
38 --20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
39 select ename,hiredate,to_char(hiredate,yyyy) years,to_char(hiredate,mm) ms  from emp order by ms,hiredate 
40 --21.显示在一个月为30天的情况所有员工的日薪金,忽略余数. 
41 select floor((sal+nvl(comm,0))/30) from emp 
42 --22.找出在(任何年份的)2月受聘的所有员工。
43 select * from emp where to_char(hiredate,mm)=2
44 --23.对于每个员工,显示其加入公司的天数.
45 select ename,sysdate-hiredate from emp
46 --24.显示姓名字段的任何位置包含"A"的所有员工的姓名.
47 select * from emp where ename like %A%
48 --25.以年月日的方式显示所有员工的服务年限.
49 select ename,floor(floor(months_between(sysdate,hiredate))/12),mod(floor(months_between(sysdate,hiredate)),12),floor(mod(sysdate-hiredate,30)) from emp 
View Code

 

orancle二维表创建与修改

原文:https://www.cnblogs.com/jiefangzhe/p/11514738.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!