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;