Orcle基本语句（一）

时间：2024-12-19 16:33:26

--创建学生信息表，约束放在所有列定义之后

 CREATE TABLE stu_info(

 stu_id INTEGER,

 stu_name NVARCHAR2(15) NOT NULL,

 stu_sex CHAR(4) NOT NULL,

 stu_age INTEGER NOT NULL,

 stu_address NVARCHAR2(30) NOT NULL,

 stu_email NVARCHAR2(30),

 stu_tele INTEGER NOT NULL,

 tch_id INTEGER,

 CONSTRAINT stu_info_stu_id_pk PRIMARY KEY(stu_id),

 CONSTRAINT stu_info_stu_sex_ck CHECK(stu_sex in ('男','女')),

 CONSTRAINT stu_info_stu_age_ck CHECK(stu_age between 12 and 18),

 CONSTRAINT stu_info_stu_email_uk UNIQUE(stu_email),

 CONSTRAINT stu_info_stu_tele_uk UNIQUE(stu_tele)

 );

--删除数据表

DROP TABLE stu_info;

--创建教师信息表，约束放在列的定义后面

 CREATE TABLE tch_info(

 tch_id INTEGER PRIMARY KEY,

 tch_name NVARCHAR2(15) NOT NULL,

 tch_sub NVARCHAR2(15) NOT NULL,

 tch_sex CHAR(4) CHECK(tch_sex in ('男','女')) NOT NULL,

 tch_email NVARCHAR2(30) NOT NULL,

 tch_tele INTEGER NOT NULL UNIQUE

 );

--添加外键

 ALTER TABLE stu_info ADD CONSTRAINT stu_info_tch_info_tch_id_fk FOREIGN KEY(tch_id) REFERENCES tch_info(tch_id);

--删除外键约束

ALTER TABLE stu_info DROP CONSTRAINT stu_info_tch_info_tch_id_fk;

--添加可以级联操作的外键约束

 ALTER TABLE stu_info ADD CONSTRAINT stu_info_tch_info_tch_id_fk FOREIGN KEY(tch_id) REFERENCES tch_info(tch_id) ON DELETE CASCADE;

--给学生信息表添加新的列

 ALTER TABLE stu_info ADD (stu_subject NVARCHAR2(15) NOT NULL);

--修改学生信息表内某列的属性

 ALTER TABLE stu_info MODIFY (stu_tele NUMBER(11));

--删除一个序列

DROP SEQUENCE stu_info_sequence;

--创建一个序列

CREATE SEQUENCE stu_info_sequence

INCREMENT BY 1

START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;

--向stu_info表中添加内容

 INSERT INTO stu_info VALUES(stu_info_sequence.nextval,'张晓风','男',15,'河南郑州','zhangxiaofeng@163.com',11235463120,null);

 INSERT INTO stu_info VALUES(stu_info_sequence.nextval,'邓国庆','男',15,'河南郑州','dengguoqing@163.com',11235463121,null);

 INSERT INTO stu_info VALUES(stu_info_sequence.nextval,'沙鹏飞','男',15,'河南郑州','shapengfei@163.com',11235463122,null);

 INSERT INTO stu_info VALUES(stu_info_sequence.nextval,'臧豪','男',15,'河南郑州','zanghao@163.com',11235463123,null);

 INSERT INTO stu_info VALUES(stu_info_sequence.nextval,'徐炜','男',15,'河南郑州','xuwei@163.com',11235463124,null);

 INSERT INTO stu_info VALUES(stu_info_sequence.nextval,'侯志文','男',15,'河南郑州','houzhiwen@163.com',11235463125,null);

 INSERT INTO stu_info VALUES(stu_info_sequence.nextval,'孔萌','女',15,'河南郑州','kongmegn@163.com',11235463126,null);

 INSERT INTO stu_info VALUES(stu_info_sequence.nextval,'刘教练','男',15,'河南郑州','liujiaolian@163.com',11235463127,null);

 INSERT INTO stu_info VALUES(stu_info_sequence.nextval,'帅炳超','男',15,'河南郑州','shuaibingchao@163.com',11235463128,null);

 INSERT INTO stu_info VALUES(stu_info_sequence.nextval,'任伟峰','男',15,'河南郑州','renweifeng@163.com',11235463129,null);

 INSERT INTO stu_info VALUES(stu_info_sequence.nextval,'李梦雅','女',15,'河南郑州','limengya@163.com',112354631210,null);

--查看表格内容

 SELECT * FROM stu_info WHERE stu_name = '张晓风';

 SELECT * FROM stu_info;

--更新指定行，指定列的内容

 UPDATE stu_info set stu_address = '河南漯河' WHERE stu_name = '张晓风' ;

--删除表格内容

 DELETE from stu_info [where stu_name = '邓国庆'];

--查询用户内所有的表

 SELECT * FROM tabs;

 SELECT * FROM user_tables;

--若没有where语句则显示所有用户下的表

 SELECT * FROM all_tables where owner = 'SCOTT';

相关文章