一、表的创建
1.1、创建表
CREATE TABLE STUDENT ( ID INT NOT NULL, NAME VARCHAR2(20), BIRTHDAY DATE, STATUS VARCHAR2(10), CONSTRAINT STUDENT_PK PRIMARY KEY ( ID ) ENABLE );
1.2、插入数据
INSERT INTO STUDENT (ID,NAME,BIRTHDAY,STATUS) VALUES (1,'HELLO',TO_DATE('2000-01-01','yyyy-mm-dd'),'GOOD');INSERT INTO STUDENT (ID,NAME,BIRTHDAY,STATUS) VALUES (2,'WORLD',TO_DATE('2000-02-01','yyyy-mm-dd'),'GOOD');COMMIT;
1.3、查看所有表
SELECT * FROM TAB;SELECT * FROM USER_TABLES;
二、表的更改
2.1、更改表名
RENAME STUDENT TO STUDENT1;
2.2、更改表结构
--增加列ALTER TABLE STUDENT ADD (EMAIL VARCHAR2(50));--增加列默认值ALTER TABLE STUDENT MODIFY (BIRTHDAY DEFAULT SYSDATE);--更改列名ALTER TABLE STUDENT RENAME COLUMN STATUS TO INITLEVEL;--更改列类型ALTER TABLE STUDENT MODIFY (NAME VARCHAR2(10 BYTE));--删除列ALTER TABLE STUDENT DROP COLUMN STATUS;
三、表的复制
CREATE TABLE STUDENT01 AS SELECT * FROM STUDENT;
四、表的截断
TRUNCATE TABLE STUDENT01;
五、表的删除
--删除表TRUNCATE TABLE STUDENT01;--删除表(包含约束)DROP TABLE STUDENT01 CASCADE CONSTRAINTS;--删除表(释放资源,不经过回收站。)DROP TABLE STUDENT01 PURGE;
六、表的增删改查
6.1、insert
--直接插入INSERT INTO STUDENT01 (ID,NAME,BIRTHDAY,STATUS) VALUES (3,'HI',TO_DATE('2000-03-01','yyyy-mm-dd'),'GOOD');INSERT INTO STUDENT01 (ID,NAME,BIRTHDAY,STATUS) VALUES (4,'MAN',TO_DATE('2000-04-01','yyyy-mm-dd'),'GOOD');COMMIT;--从其它表插入INSERT INTO STUDENT (ID,NAME,BIRTHDAY,STATUS)SELECT ID,NAME,BIRTHDAY,STATUS FROM STUDENT01 WHERE NOT EXISTS (SELECT 1 FROM STUDENT WHERE ID=STUDENT01.ID);COMMIT;
6.2、delete
6.3、update
6.4、select
原文转载:http://www.shaoqun.com/a/481002.html
oklink:https://www.ikjzd.com/w/1362
亚马逊t恤:https://www.ikjzd.com/w/1932
xinong:https://www.ikjzd.com/w/1368
一、表的创建1.1、创建表CREATETABLESTUDENT(IDINTNOTNULL,NAMEVARCHAR2(20),BIRTHDAYDATE,STATUSVARCHAR2(10),CONSTRAINTSTUDENT_PKPRIMARYKEY(ID)ENABLE);1.2、插入数据INSERTINTOSTUDENT(ID,NAME,BIRTHDAY,STATUS)VALUES(1,'
东杰智能:https://www.ikjzd.com/w/1967
picitup:https://www.ikjzd.com/w/446
做亚马逊七分靠选品,但是你真的会选品吗?:https://www.ikjzd.com/tl/103542
跨境电商如何蓄出自己的流量池--摆脱平台流量勒索:https://www.ikjzd.com/home/17700
史上最难清关的国家目前正处于红灯期,卖家需知这几点!:https://www.ikjzd.com/home/16439
No comments:
Post a Comment