您的位置:68399皇家赌场 > 虚拟主机 > ORACLE_SQL

ORACLE_SQL

发布时间:2019-05-03 21:48编辑:虚拟主机浏览(98)

    例子: 把Student表中Sno的int类型 修改为 varchar 类型

    --更新表中的多寡
    update Course set cname='数据结构C版'where cno=五;

    --以Class降序查询Student表的装有记录。
    select * from student order by class desc;

          alter table Student alter column Sno varchar(20) not null

    --创建2个学科的表
    create table Course(
           Cno char(4)primary key,
           Cname char(40) not null,
           Cpno char(4),
           Ccredit int,
           foreign key (Cpno) references Course(Cno)
    );

    108 6-166         81

    alter table [表名]  alter column [字段名][ 属性] 

    select * from student;


    SQL> select * from score;

    drop table Student;
    commit;
    select *from student

    insert into grade values(80,89,'B');

    --构建学生表
    create table Student (
           Sno char(9) primary key,
           Sname char(20)unique,
           Sex  char(4),
           Sage int,
           Sdept char(20)
    );


    --向student表中追加“入学时间”字段 为日期型的
    alter table student add S_entrance date;
    --删除“入学时间”字段   报错 缺乏关键字??  必须要抬高column关键字
    alter table student drop column S_entrance;
    --将学生表中的char型数据修改为 varchar二型  因为 char是不行变长的  varchar可变长 节省空间??类型不合营
    alter table student modify (Sno varchar2(9));

    insert into score values('103', '3-245',  '86');
    insert into score values('105', '3-245',  '75');
    insert into score values('109', '3-245',  '68');
    insert into score values('103', '3-105',  '92');
    insert into score values('105', '3-105',  '88');
    insert into score values('109', '3-105',  '76');
    insert into score values('101', '3-105',  '64');
    insert into score values('107', '3-105',  '91');
    insert into score values('108', '3-105',  '78');
    insert into score values('101', '6-166',  '85');
    insert into score values('107', '6-166',  '79');
    insert into score values('108', '6-166',  '81');

    insert into Student values('201215121','李勇','男',20,'CS');
    insert into Student values('201215122','刘晨','女',19,'CS');
    insert into Student values('201215123','王敏','女',18,'MA');
    insert into Student values('201215124','张立','男',19,'CS');

    create table course(
    cno varchar2(25) not null,
    cname varchar2(10) not null,
    tno varchar2(3) not null);

    select * from Course order by Cno;
    --插入课程表中数据
    --出现了错误 因为外键的牢笼原则?消除方案 :壹先布置未有外键联系的  再插入其余的
    --贰.改变约束
    insert into Course (Cno,Cname,Cpno,Ccredit)values('1','数据库','5',4);
    insert into Course (Cno,Cname,Ccredit)values('2','数学',2);
    insert into Course (Cno,Cname,Cpno,Ccredit)values('三','消息系统','1',四);
    insert into Course (Cno,Cname,Cpno,Ccredit)values('四','操作系统','陆',3);
    insert into Course (Cno,Cname,Cpno,Ccredit)values('五','数据结构','5',肆);
    insert into Course (Cno,Cname,Ccredit)values('陆','数据管理',四);
    insert into Course (cno,Cname,Cpno,Ccredit)values('7','PASCAL','6',4);

    表30%绩表:

    insert into SC values('201215121','1',92);
    insert into SC values('201215121','2',85);
    insert into SC values('201215121','3',88);
    insert into SC values('201215122','2',90);
    insert into SC values('201215122','3',80);

    create table grade(low  number(3),upp  number (3),rank  char(1))

    drop table sc;

    ---查询Score表中战绩在60到80里头的有所记录。
    SQL> select * from score where degree between 60 and 80;

    update  student set s_entrance='1-1月-2015' where sname='李勇';

    CNO                       CNAME           TNO

    select * from SC;

    --查询Student表中“9503一”班或性别为“女”的校友记录。
    select * from student where seex ='nv' or class='95031';

    create table SC (
           Sno char(9),
           Cno char(4),
           Grade int,
           primary key (Sno,Cno),
           foreign key (Sno) references Student(Sno),
           foreign key (Cno) references Course(Cno)
    );

    create table student(
       sno varchar2(3) not null,
       sname varchar2(8) not null,
       seex varchar2(2) not null,
       sbirthday date,
       class varchar2(5));

    --建设构造学生选课表

    表一/
    SQL>  select * from student;

    **您会意识标题简单,但是找相关标准很劳顿,告诉我们,以往写查询的时候,尽量把列,表的定义放在1边,随时查询

    ORA-02270: no matching unique or primary key for this column-list

    1一、 查询每门课的平均成绩。
    select cno,avg(degree) from score group by cno;

    insert into Student values('105','kuangming','nan',to_date ('1975-10-02','yyyy-mm-dd'),'95031');


    103 3-245         86
    105 3-245         75
    109 3-245         68
    103 3-105         92
    105 3-105         88
    109 3-105         76
    101 3-105         64
    107 3-105         91
    108 3-105         78
    101 6-166         85
    107 6-166         79

    澳门皇家赌场55533网址,--以Cno升序、Degree降序查询Score表的全体记录。
    select * from score order by cno,degree desc;

    12 rows selected.

    select sum(bytes/1024/1024) from dba_data_files;
    2076.25
    [oracle@localhost jx]$ du -h
    2.8G  
    SQL> select sum(bytes/1024/1024) from dba_segments;
    1760.9375

    1陆、 查询“9503三”班学员的平均分。
    select cno,avg(degree) from student a join score b on (a.sno=b.sno and a.class='95033') group by cno;  

    --B
    select cno,avg(degree) from score where cno like '3%'group by cno
     intersect
    select cno,avg(degree) from score having(count(*))>=5 group by cno;

    现查询全数同学的Sno、Cno和rank列。
    select distinct a.sno,a.cno,b.rank from score a join grade b on (a.degree between b.low and b.upp);

    insert into grade values(0,59,'E');

    SQL> alter table student modify(seex varchar2(3));
    alter table student modify(SNAME varchar2(10));

    12、查询Score表中足足有5名上学的儿童选修的并以三方始的学科的平分分数。
    select cno,avg(degree) from score 
      where cno like '3%' having(count(cno))>=5 
    group by cno

    comment on column student.sno is '学号';
    alter table student add constraint pk_sno primary key(sno);

    insert into Course values('3-245'   ,'caozuoxitong','804');

    本文由68399皇家赌场发布于虚拟主机,转载请注明出处:ORACLE_SQL

    关键词: 68399皇家赌场 SQL oracle