--创建学生表,包含班级编号
--创建班级表,包含班级编号
CREATE TABLE student (
stuName VARCHAR2(10) NOT NULL,-- 定义字符型长度为10不能为空的stuNum字段
stuSex VARCHAR2(2) NOT NULL,
stuAge NUMBER(2) NOT NULL,-- 定义数值型长度为2不能为空的stuAge字段
classno NUMBER(5) NOT NULL);
COMMIT;
CREATE TABLE CLASS (
className VARCHAR2(10) NOT NULL,
classno NUMBER(5) NOT NULL);
COMMIT;
--创建班级表,包含班级编号
CREATE TABLE student (
stuName VARCHAR2(10) NOT NULL,-- 定义字符型长度为10不能为空的stuNum字段
stuSex VARCHAR2(2) NOT NULL,
stuAge NUMBER(2) NOT NULL,-- 定义数值型长度为2不能为空的stuAge字段
classno NUMBER(5) NOT NULL);
COMMIT;
CREATE TABLE CLASS (
className VARCHAR2(10) NOT NULL,
classno NUMBER(5) NOT NULL);
COMMIT;
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('张三','男','21','17');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('李四','男','22','16');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('王五','男','23','16');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('赵六','男','21','16');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('小明','男','22','17');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('小花','女','23','17');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('小兰','女','21','16');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('小鸡','男','22','17');
COMMIT;
SELECT * FROM student;
VALUES ('张三','男','21','17');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('李四','男','22','16');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('王五','男','23','16');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('赵六','男','21','16');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('小明','男','22','17');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('小花','女','23','17');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('小兰','女','21','16');
INSERT INTO student(stuName,stuSex,stuAge,classno)
VALUES ('小鸡','男','22','17');
COMMIT;
SELECT * FROM student;
--upodate语句 用于更新数据表中字段的数据
--update 表名称 set 字段名称1 = 新值,字段名称2=新值,字段名称n=新值 where条件
UPDATE student SET stuName = '小小' WHERE stuName = '赵六';
--update 表名称 set 字段名称1 = 新值,字段名称2=新值,字段名称n=新值 where条件
UPDATE student SET stuName = '小小' WHERE stuName = '赵六';
--delete语句 删除表中的数据
--语法 delete from 表名 where 条件
DELETE FROM student WHERE stuName = '小鸡';
DELETE FROM student;
--语法 delete from 表名 where 条件
DELETE FROM student WHERE stuName = '小鸡';
DELETE FROM student;
--alter table语句 修改表 调整表的结构
--语法 alter table 表名称 操作 操作内容
--语法 alter table 表名称 操作 操作内容
--增加表字段
ALTER TABLE student
ADD score VARCHAR2(3);
ALTER TABLE student
ADD ID INTEGER;
ALTER TABLE student
ADD score VARCHAR2(3);
ALTER TABLE student
ADD ID INTEGER;
--修改字段类型
ALTER TABLE student
MODIFY(score INTEGER);
ALTER TABLE student
MODIFY(score INTEGER);
--删除表字段
ALTER TABLE student
DROP(score);
ALTER TABLE student
DROP(ID);
-- 重命名 可以更改一个表,视图,序列 等名字
--修改表名称
SELECT * FROM student;
RENAME CLASS TO cla;
ALTER TABLE student
DROP(score);
ALTER TABLE student
DROP(ID);
-- 重命名 可以更改一个表,视图,序列 等名字
--修改表名称
SELECT * FROM student;
RENAME CLASS TO cla;
--删除表
drop table edu;
select * from edu;
delete from edu;
commit;
drop table edu;
select * from edu;
delete from edu;
commit;