Oracle数据库学习一（数据，表操作课堂SQL）

--DDL Date definition language 数据定义语言
--建表 CREATE TABLE
CREATE TABLE STUDENT (
ST_TD NUMBER(7),
ST_NAME VARCHAR2(20),
ST_SEX VARCHAR2(3),
ST_JOIN_DATE DATE,
ST_HOBBIES VARCHAR2(100) --！！！最后的字段不要逗号
);

SELECT * FROM STUDENT;

--修改表（字段名称，字段属性，增加，删除字段）
--增加列
ALTER TABLE STUDENT ADD(AGE NUMBER(3));

--删除列
ALTER TABLE STUDENT DROP COLUMN AGE;

--修改字段名
ALTER TABLE STUDENT RENAME COLUMN ST_HOBBIES TO ST_HOBBY;

ALTER TABLE STUDENT RENAME COLUMN ST_TD TO ST_ID;

--修改字段属性
ALTER TABLE STUDENT MODIFY ST_HOBBY VARCHAR2(50);

--修改表名
RENAME STUDENT TO STUDENTS;

--删除表
DROP TABLE STUDENTS;

--DML Data manipilation language 数据操纵语言(不包括SELECT)
--新增语句 INSERT INTO
INSERT INTO STUDENT(ST_ID,ST_NAME,ST_SEX,ST_JOIN_DATE)
VALUES(0000001,'小琪','女','20-5月-18');
INSERT INTO STUDENT VALUES(0002,'冯二','男','30-5月-17','学习');
INSERT INTO STUDENT VALUES(0003,'旭升','男','23-5月-17',NULL);
INSERT INTO STUDENT VALUES(0003,'娜娜','男','01-5月-17','学习');
INSERT INTO STUDENT VALUES(0004,'天蓝','男','26-6月-17','学习');
INSERT INTO STUDENT VALUES(0005,'金刚','男','23-5月-17','打飞机');

--修改当前会话时间格式
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

INSERT INTO STUDENT VALUES(0006,'布偶','男','2017-10-19 21:47:38','吃小鱼干');

--更新语句 UPDATE
UPDATE STUDENT SET ST_HOBBIES='学习' WHERE ST_ID=5;

UPDATE STUDENT SET ST_HOBBIES='游泳' WHERE ST_ID=3 AND ST_HOBBIES IS NULL;

--拼接字符串 ||
UPDATE STUDENT SET ST_HOBBIES=ST_HOBBIES||',游泳' WHERE ST_ID=4;

--删除数据 DELETE FROM
DELETE FROM STUDENT WHERE ST_HOBBIES='学习';