Orcle基本语句（一）

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

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';