Oracle BASIC TABLE Statement ____oracle

--Create a user
Create user Han identified by Han default tablespace
Users temporary tablespace Temp;
Grant Connect,resource,dba to Han; Grant the user Han Developer rights

--------------------action on a table--------------------------
--Create a table
CREATE TABLE Classes (
ID Number (9) NOT null primary key,
ClassName VARCHAR2 () NOT NULL
)
--Query table
SELECT * from Classes;

--Delete Table
drop table students;

--Modify the name of the table
Rename Alist_table_copy to alist_table;

--Display table structure
Describe test--no, no, no.

-----------------------The operation of the field-----------------------------------
--Adding columns
ALTER TABLE test add address varchar2 (40);

--Delete Column
ALTER TABLE test drop column address;

--Modify the name of the column
ALTER TABLE test Modify address addresses varchar (40;

--Modifying the properties of a column
ALTER TABLE Test Modi

CREATE TABLE Test1 (
ID Number (9) primary key NOT NULL,
Name Varchar2 (34)
)
Rename test2 to test;

--Create a self-added sequence
Create sequence Class_seq increment by 1-start with 1 MAXVALUE 999999 nocycle;

Select Class_seq.currval from dual

--Inserting data
Insert into classes values (Class_seq.nextval, ' software class ')
Commit

--Update data
Update Stu_account set username= ' AAA ' where count_id=2;
Commit

--Create a unique index
Create unique index username on stu_account (username); --Unique index cannot insert the same data

--row lock cannot operate on this row in a newly opened dialog
SELECT * from Stu_account t where t.count_id=2 for update; --ROW lock

--alter Table Stuinfo Modify sty_id to stu_id;

ALTER TABLE students drop constraint CLASS_FK;
ALTER TABLE students add constraint CLASS_FK foreign key (class_id) references classes (ID);--FOREIGN KEY constraint
ALTER TABLE STUINFO add constraint STU_FK foreign key (stu_id) references students (ID) on delete cascade;--foreign KEY constraint, cascading deletion

ALTER TABLE Stuinfo drop constant STU_FK;

INSERT into students values (Stu_seq.nextval, ' John ', 1,sysdate);

INSERT into stuinfo values (stu_seq.currval, ' Weihai ');

SELECT * from Stuinfo;

CREATE TABLE Zhuce (
ZC_ID Number (9) is not null primary key,
STU_ID Number (9) is not NULL,
Zhucetime Date Default Sysdate

)

CREATE TABLE Feiyong (
FY_ID Number (9) is not null primary key,
STU_ID Number (9) is not NULL,
MX_ID Number (9) is not NULL,
Yijiao number (7,2) NOT null default 0,
Qianfei number (7,2) NOT NULL

)

Create Talbe Fymingxi (
MX_ID Number (9) is not null primary key,
Feiyong number (7,2) not NULL,//Total 7 digits, two digits after decimal
CLASS_ID Number (9) NOT NULL
}

CREATE TABLE Card (
CARD_ID Number (9) primary key,
STU_ID Number (9) is not NULL,
Money Number (7,2) is not null default 0,
Status number (1) NOT NULL default 0--0 table available, 1 table loss Reporting
)

--linked list query

Select C.classname| | ' _'|| S.stu_name as Class _ name, si.address from classes c,students s, Stuinfo si where c.id=s.class_id and s.id=si.stu_id;
INSERT into students values (stu_seq.nextval, ' Dick ', 1,sysdate);
INSERT into stuinfo values (stu_seq.currval, ' Nanjing ');

--function
Select Rownum,id,stu_name from students T-ID ASC;

--Multi-Many-to-many Association for intermediate Table implementations
--(1 1, 1 n,n 1,n N)

--1 N describes 1 of tables that do not handle n the table has 1 table fields
Description of--1 1 primary foreign Key Association
--n N's description of the middle table implementation of Many-to-many Association

CREATE TABLE Course (
COURSE_ID Number (9) is not NULL,
Couser_name varchar2 () NOT NULL
)
ALTER TABLE course to couse;
CREATE TABLE Stu_couse (
STU_COUSE_ID Number (9) primary key,
STU_ID Number (9) is not NULL,
COUSE_ID Number (9) NOT NULL

)

Create unique index STU_COUSE_UNQ on stu_couse (stu_id,couse_id); --The only student
Create sequence Stu_couse_seq increment by 1-start with 1 MAXVALUE 999999 nocycle;

Create sequence Couses_seq increment by 1-start with 1 MAXVALUE 999999 nocycle;
Insert into course values (couses_seq.nextval, ' computer Principles ');
Insert into course values (couses_seq.nextval, ' compiler principle ');
Insert into course values (couses_seq.nextval, ' Database Principles ');
Insert into course values (couses_seq.nextval, ' data structure ');
Insert into course values (couses_seq.nextval, ' Computer Basics ');
Insert into course values (Couses_seq.nextval, ' C language Preliminary ');
Commit

INSERT into stu_couse values (stu_couse_seq.nextval,1,1);
INSERT into stu_couse values (stu_couse_seq.nextval,1,3);
INSERT into stu_couse values (stu_couse_seq.nextval,1,5);
INSERT into stu_couse values (stu_couse_seq.nextval,1,5);

INSERT into stu_couse values (stu_couse_seq.nextval,2,1);
Commit
SELECT * from Stu_couse;
SELECT * from course;

--select s.stu_name,sc.couse_id, c.couser_name from students S,course C,stu_couse SC where stu_id=1

--select couse_id from Stu_couse where stu_id=1

Select Cl.classname,s.stu_name,c.couser_name from Stu_couse SC, students s,course c,classes CL where s.id=sc.stu_id and SC . couse_id=c.course_id and S.class_id=cl.id and s.id=1;

--Class--Name
Select C.classname,s.stu_name from Students s,classes C where s.class_id=c.id and s.id=2;

SELECT * from students s where s.id=2
--Class--name--course

Select Cl.classname,s.stu_name,c.couse_name from Stu_couse sc,students s,classes cl,couse C where sc.stu_id=s.id and SC.C ouse_id=c.couse_id and s.id=26;

--sql statement, now write out the associated table, and then write out the fields to find, third write the associated conditions, remember that write the table associated with the data before writing a table, so as to help improve the efficiency of SQL

Select C.couser_name,s.stu_name from Stu_couse sc,students s,course C where c.course_id=1 and c.course_id=sc.couse_id and Sc.stu_id=s.id;

Select S.stu_name from Students s,stu_couse SC where s.id=sc.stu_id GROUP by S.id,s.stu_name;

Select C.classname,count (sc.couse_id) from Stu_couse sc,students s,classes C where s.class_id=c.id and s.id=sc.stu_id Gro Up by C.classname;

Select S.stu_name, COUNT (sc.couse_id) from Stu_couse sc,students s,classes cl where s.id=sc.stu_id GROUP by S.id,s.stu_nam E having count (sc.stu_couse_id) >3;
Number of class students selected
Select Cl.classname,count (sc.stu_couse_id) from Stu_couse sc,students s,classes cl where s.id=sc.stu_id and s.class_id= Cl.id GROUP BY Cl.classname;

--the number of class students selected class
Select Cl.classname,s.stu_name,count (sc.stu_couse_id) from Stu_couse sc,students s,classes CL where s.id=sc.stu_id and S . class_id=cl.id GROUP BY S.stu_name;

Select Cl.classname,s.stu_name,count (sc.stu_couse_id) from Stu_couse SC, students s,classes CL where sc.stu_id=s.id and S . class_id=cl.id GROUP BY S.id;

Select Cl.classname,s.stu_name,count (sc.stu_couse_id) from Stu_couse sc,students s,classes CL where sc.stu_id=s.id and S . class_id=cl.id GROUP BY S.stu_name;
--Class student selected Course ID selected course Name

--Create an attempt to unite a table and then view it as a table, in conjunction with other queries
CREATE View xsxk as Select Cl.classname, s.stu_name,c.couse_id, c.couse_name from Stu_couse sc,students s,classes cl,couse C where Sc.stu_id=s.id and sc.couse_id=c.couse_id and s.class_id=cl.id;

SELECT * FROM Xsxk

CREATE View Classstu as select S.id,c.classname,s.stu_name from students s,classes C where c.id=s.class_id;
Drop View Classstu; --Delete View
SELECT * from Classstu;
CREATE View Stu_couse_view as select S.id c.couse_name from Stu_couse sc,students s,couse C where s.id=sc.stu_id and SC.C ouse_id=c.couse_id;
SELECT * from Stu_couse_view;
CREATE View CSC as select Cs.classname,cs.stu_name,scv.couse_name from Classstu cs,stu_couse_view SCV where cs.id=scv.id;
SELECT * from CSC;

SELECT * FROM classes cross join students; --full connection, equivalent to select * from Classes,students;

SELECT * FROM classes CL LEFT JOIN students s on cl.id=s.class_id; --left connection regardless of whether the left table is displayed
SELECT * FROM classes CL Right join students s on cl.id=s.class_id; --Right connection
SELECT * FROM classes CL full join students s on cl.id=s.class_id; --Full connection

Insert into classes values (Class_seq.nextval, ' software Class four ');

CREATE TABLE Sales (
Nian VARCHAR2 (4),
Yeji Number (5)

);
INSERT into sales values (' 2001 ', 200);
INSERT into sales values (' 2002 ', 300);
INSERT into sales values (' 2003 ', 400);
INSERT into sales values (' 2004 ', 500);
Commit
SELECT * from sales;
drop table Sale;

Select S1.nian,sum (S2.yeji) from the sales s1,sales S2 where S1.nian>=s2.nian GROUP by S1.nian ORDER by S1.nian Desc;

Select S1.nian,sum (S2.yeji) from the sales s1,sales S2 where S1.nian>=s2.nian Group by S1.nian;

S
Annual Performance sum
2001 200
2002 500
2003 900
2004 1400

CREATE TABLE Test1 (
T_ID Number (4)
);

CREATE TABLE Org (
ORG_ID Number (9) is not null primary key,
Org_name varchar2 () NOT NULL,
PARENT_ID Number (9)
);

Create sequence Org_seq increment by 1-start with 1 MAXVALUE 999999 nocycle;
Drop sequence org_seq;
INSERT into org values (1, ' Hua Jian Group ', 0);
INSERT into org values (2, ' Hua Jian Group One branch ', 1);
INSERT into org values (3, ' Hua Jian Group Two branch ', 1);
INSERT into org values (4, ' Hua Jian Group Finance Department ', 1);
INSERT into org values (5, ' Hua Jian Group Engineering Department ', 1);
INSERT into org values (6, ' Hua Jian Group One cent company finance department ', 2);
INSERT into org values (7, ' Hua Jian Group branch of the Agency ', 2);

select * from org;
--Incorrect loop not implemented
Select b.org_id, B.org_name, b.parent_id from org a,org b where a.org_id=7 and a.parent_id=b.org_id;
SELECT * from org Connect by prior parent_id=org_id start and org_id=7 order by org_id;
SELECT * from org Connect by prior org_id=parent_id start and org_id=1 order by org_id;

CREATE TABLE Chengji (
       cj_id Number (9) NOT null primary key,
        stu_cou_id Number (9) NOT NULL,
       fen number (4,1)
& nbsp;      
);
INSERT into Chengji values (1,1,62);
INSERT into Chengji values (2,2,90);
INSERT into Chengji values (3,3,85);
INSERT into Chengji values (4,4,45);
INSERT into Chengji values (5,5,68);
INSERT into Chengji values (6,6,87);
Commit;
Select * from Chengji;
Select * from Stu_couse;
--It seems not to be applicable in Oracle to ALTER TABLE Chengji change stu_cou_id stu_couse_id;alter table SHOP_JB change Price1 price double; /p>

Student's name average score
Select S.stu_name,avg (Cj.fen) from Stu_couse Sc,chengji cj,students s where s.id=sc.stu_id and sc.stu_couse_id=cj.stu_ COUSE_ID GROUP BY S.id,s.stu_name;
Select S.stu_name from students s,stu_couse Sc,chengji CJ where s.id=sc.stu_id and sc.stu_couse_id=cj.stu_couse_id Group B Y s.id,s.stu_name;
Select S.stu_name,cj.fen from students s,stu_couse Sc,chengji CJ where s.id=sc.stu_id and Sc.stu_couse_id=cj.stu_couse_ ID and cj.fen>60;

Student Name Subject Score
Select S.stu_name,c.couse_name,cj.fen from Stu_couse sc,students s,couse C,chengji CJ where Sc.stu_id=s.id and Sc.couse_i d=c.couse_id and sc.stu_couse_id=cj.stu_couse_id and cj.fen>60 order by=;

SELECT * from Stu_couse;

--Set operation
--The student union who selected course 3 chose the course 5 students and set
--Select a course 3 or a student who has selected 5 of the course
Select S.stu_name from students s,couse C,stu_couse SC where s.id=sc.stu_id and sc.couse_id=c.couse_id and c.couse_id=3
Union
Select S.stu_name from students s,couse C,stu_couse SC where s.id=sc.stu_id and sc.couse_id=c.couse_id and c.couse_id=5

--The students who choose the course 3,5,2 intersect choose the course 1,2,4 the intersection of the students
--Ask for a course 2 and select the intersection of 3 students
Select S.stu_name from students s,couse C,stu_couse SC where s.id=sc.stu_id and sc.couse_id=c.couse_id and c.couse_id=2
Intersect
Select S.stu_name from students s,couse C,stu_couse SC where s.id=sc.stu_id and sc.couse_id=c.couse_id;

--Students who have selected course 3,5,8    minus have chosen the course 1,7,8 student   -the difference set
-The student difference set for all courses with a score greater than 60
SELECT DISTINCT (s.stu_name) from Stu_couse sc,students s,couse C,chengji CJ where Sc.stu_id=s.id and sc.couse_id=c.couse_id and Sc.stu_co use_id=cj.stu_couse_id and cj.fen>60
minus
Select DISTINCT (s.stu_name) from Stu_couse sc,students s,couse C, Chengji CJ where Sc.stu_id=s.id and sc.couse_id=c.couse_id and sc.stu_couse_id=cj.stu_couse_id and cj.fen<60;