一.建表
1.建立两张表CLASSINFO,STUDENTINFO.
--建表CLASSINFO；
CREATE TABLE CLASSINFO ( CLASSID NUMBER(2) PRIMARY KEY,CLASSNAME VARCHAR(10) NOT NULL);
--建表STUDENTINFO；
CREATE TABLE STUDENTINFO ( STUDENTID NUMBER(2) PRIMARY KEY,STUDENTNAME CHAR(10) NOT NULL,STUDENTSEX CHAR(2) CHECK(STUDENTSEX=‘男‘ OR STUDENTSEX=‘女‘),STUDENTAGE NUMBER(2) NOT NULL,STUDENTTEL NUMBER(11) UNIQUE,STUDENTADDRESS VARCHAR(20) DEFAULT ‘上海‘,classid number(2) REFERENCES classinfo(classid));
2.在两张表中插入数据。
--表CLASSINFO；
INSERT INTO CLASSINFO (CLASSID,CLASSNAME) VALUES( 1,‘六班‘); INSERT INTO CLASSINFO (CLASSID,CLASSNAME) VALUES( 2,‘七班‘);
INSERT INTO STUDENTINFO VALUES (1,‘Timmy‘,‘男‘,20,‘13285601693‘,‘suzhou‘,1 ); ALTER TABLE STUDENTINFO MODIFY (STUDENTNAME CHAR(10) );--这里是来修改STUDETNAME字段的数据类型的长度。 INSERT INTO STUDENTINFO VALUES (2,‘Lizmer‘,25,‘18056947153‘,‘anhui‘,1 ); INSERT INTO STUDENTINFO VALUES (3,‘Gray‘,30,‘18056996153‘,‘hubei‘,2 ); INSERT INTO STUDENTINFO VALUES (4,‘Roger‘,31,‘18056886153‘,‘henan‘,2 );
3.在两张表中添加字段。
ALTER TABLE CLASSINFO ADD (Teacher char(20)); ALTER TABLE CLASSINFO ADD (Leader char(20)); ALTER TABLE STUDENTINFO ADD (score NUMBER(20)); ALTER TABLE STUDENTINFO ADD (Teacher char(20));
UPDATE CLASSINFO SET Teacher=‘刘A‘ where CLASSID=‘2‘; UPDATE CLASSINFO SET Leader=‘陈B‘ where CLASSID=‘2‘; UPDATE STUDENTINFO SET Teacher=‘张I‘ where STUDENTID=‘4‘; UPDATE STUDENTINFO SET Leader=‘汤J‘ where STUDENTID=‘4‘; UPDATE STUDENTINFO SET score=‘100‘ where STUDENTID=‘5‘;
5.表CLASSINFO和STUDENTINFO如下所示。
二.高级多表数据查询。
2.1 RANK() OVERORDER BY/ DENSE_RANK() OVERORDER BY/ RANK() OVER PARTITION BY ORDER BY；
##我们通常将group by叫做分组，而partition by称作分区，分组返回通常是一行，partition by返回的是多行。 SELECT STUDENTINFO.*,DENSE_RANK() OVER(ORDER BY STUDENTINFO.score DESC ) AS RANKS FROM STUDENTINFO; SELECT STUDENTINFO.*,RANK() OVER(ORDER BY STUDENTINFO.score DESC ) AS RANKS FROM STUDENTINFO; SELECT RANK() OVER(PARTITION BY STUDENTNAME ORDER BY STUDENTINFO.score DESC ) AS RANKS FROM STUDENTINFO;--这里解释一下，假如Timmy有两次得分记录，那么上述语句就是对Timmy的得分排序。
