6.3. 基本SQL语句
6.3.1. DDL
DDL（Data Defination Language）为数据定义语言的简称，主要包括create、alter和drop语句。
1）前期准备
D:\> sqlplus / as sysdba
或
D:\> sqlplus sys/passwd@tns_lhdz_bj as sysdba
SQL> create user lhdz_bj identified by lhdz_bj default tablespace users temporary tablespace temp;
SQL> grant connect,resource to lhdz_bj;
SQL> conn lhdz_bj/lhdz_bj
SQL> conn lhdz_bj/lhdz_bj@tns_lhdz_bj
2）create语句
- 表t_teach
SQL> create table t_teach(teach_no varchar2(5),name varchar(10),age number(3,1),grade varchar2(6),primary key(teach_no));
或
SQL> create table t_teach(teach_no varchar2(5),name varchar(10),age number(3,1),grade varchar2(6),constraint pk_t_teach primary key(teach_no));
或
SQL> create table t_teach(teach_no varchar2(5),name varchar(10),age number(3,1),grade varchar2(6));
SQL> alter table t_teach add constraint pk_t_teach primary key(teach_no);
- 表t_stud
SQL> create table t_stud(stud_no varchar2(12),name varchar(10),age number(3,1),class_no varchar2(10),teach_no varchar2(5),constraint pk_t_stud primary key(stud_no),constraint fk_t_stud foreign key(teach_no) references t_teach(teach_no),constraint chk_age check(age>10 and age<=30));
SQL> create index i_t_stud_teach_no on t_stud(teach_no);
- 表t_sub
SQL> create table t_sub(sub_no varchar2(5),name varchar2(200),wktime varchar2(10),site varchar2(50),class_no varchar2(10),teach_no varchar2(5),constraint pk_t_sub primary key(sub_no),constraint fk_t_sub_teach foreign key(teach_no) references t_teach(teach_no) );
SQL> create index i_t_sub_teach_no on t_sub(teach_no);
- 表t_score
SQL> create table t_score(sub_no varchar2(5),stud_no varchar2(12),score number(5,1),score_no int,remark varchar2(500),constraint pk_t_score primary key(sub_no,stud_no,score_no),constraint fk_t_score foreign key(sub_no) references t_sub(sub_no),constraint fk_t_score_stud foreign key(stud_no) references t_stud(stud_no));
SQL> create index i_t_score_sub_no on t_sub(sub_no);
SQL> create index i_t_score_stud_no on t_stud(stud_no);
- 表t_test
SQL> create table t_test(c1 int,c2 varchar2(10));
SQL> create index i_t_test_c1 on t_test(c1);
3）alter语句
SQL> alter table t_test add (c3 char(20),c4 number);
SQL> desc t_test;
SQL> alter table t_test modify (c2 varchar2(20),c3 char(10));
SQL> desc t_test;
SQL> alter table t_test drop (c3,c4);
SQL> desc t_test;
4）truncate语句
SQL> truncate table t_test;
5）drop语句
SQL> drop table t_test;
SQL> drop index i_t_test_c1;
6.3.2. DML
DML（Data Manipulation Language ）为数据操作语言的简称，主要包括insert,update,delete等语句。
1）insert语句
- 表t_teach
SQL> insert into t_teach(teach_no,name,age,grade)
values('00001','张三',25,'助教');
SQL> insert into t_teach(teach_no,name,age,grade)
values('00002','李四',30,'讲师');
SQL> insert into t_teach(teach_no,name,age,grade)
values('00003','王五',40,'教授');
SQL> commit;
- 表t_stud
SQL> insert into t_stud(stud_no,name,age,class_no,teach_no)
values('201201010101','王龙',18,'2012010101','00001');
SQL> insert into t_stud(stud_no,name,age,class_no,teach_no)
values('201201010102','李虎',17,'2012010102','00002');
SQL> insert into t_stud(stud_no,name,age,class_no,teach_no)
values('201201010103','赵云',18,'2012010103','00003');
SQL> commit;
- 表t_sub
SQL> insert into t_sub(sub_no,name,wktime,site,class_no,teach_no)
values('00001','高等数据','周11','1教205','2012010201','00001');
SQL> insert into t_sub(sub_no,name,wktime,site,class_no,teach_no)
values('00002','C语言','周22','2教302','2012010102','00002');
SQL> insert into t_sub(sub_no,name,wktime,site,class_no,teach_no)
values('00003','大学英语','周35','3教105','2012030101','00003');
SQL> commit;
- 表t_score
SQL> insert into t_score(sub_no,stud_no,score,score_no,remark)
values('00001','201201010101',90,1,null);
SQL> insert into t_score(sub_no,stud_no,score,score_no,remark)
values('00002','201201010102',55,1,null);
SQL> insert into t_score(sub_no,stud_no,score,score_no,remark)
values('00002','201201010102',77,2,'补考');
SQL> insert into t_score(sub_no,stud_no,score,score_no,remark)
values('00003','201201010103',88,1,null);
SQL> commit;
2）update语句
SQL> update t_score set score=67
where sub_no='00002'
and stud_no='201201010102'
and score_no=2;
SQL> commit;
3）delete语句
SQL> delete from t_score
where sub_no=’00002’
and stud_no=’201201010102’
and score_no=2;
SQL> update t_score set score=95
where sub_no=’00002’
and stud_no=’201201010102’
and score_no=1;
SQL> commit;
6.3.3. DCL
DCL（Data Control Language）为数据控制语言的简称，主要包括grant和revoke等操作。
1）准备工作
D:\> slqplus / as sysdba
SQL> create user lhdz_bj1 identified by lhdz_bj1 default tablespace users temporary tablespace temp;
SQL> grant connect,resource,create synonym to lhdz_bj1;
2）grant语句
SQL> conn lhdz_bj/lhdz_bj
SQL> grant select on t_teach to lhdz_bj1;
SQL> conn lhdz_bj1/lhdz_bj1
SQL> select * from lhdz_bj.t_teach;
SQL> create synonym t_teach for lhdz_bj.t_teach;
SQL> select * from t_teach;
3）revoke语句
SQL> conn lhdz_bj/lhdz_bj
SQL> revoke all on t_teach from lhdz_bj1;
SQL> conn lhdz_bj1/lhdz_bj1
SQL> select * from t_teach;
SQL> select * from lhdz_bj.t_teach;
