达内学习日志Day28：表的创建

Oracle作业1：表的创建

Oracle版本：

CREATE TABLE emp(

empno NUMBER(4),

    ename VARCHAR2(10),

    job VARCHAR2(9),

    mgr NUMBER(4),

    hiredate DATE,

    sal NUMBER(7,2),

    comm NUMBER(7,2)

);

MySQL版本：

CREATE TABLE emp(empno int,ename varchar(10),job varchar(9),mgr int,hiredate date,sal double,comm double,deptno int);

CREATE TABLE dept(deptno int,dname varchar(14),loc varchar(13));

删除一张表：DROP TABLE dept;

修改表结构：ALTER TABLE emp MODIFY(empno int primary key auto_increment);

插入数据

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7369,'SMITH','CLERK',7902,19801217,800,NULL,20);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7499,'ALLEN','SALESMAN',7698,19810220,1600,300,30);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7521,'WARD','SALESMAN',7698,19810222,1250,500,30);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7566,'JONES','MANAGER',7839,19810402,2975,NULL,20);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7654,'MARTIN','SALESMAN',7698,19810928,1250,1400,30);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7698,'BLAKE','MANAGER',7839,19810501,2850,NULL,30);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7782,'CLARK','MANAGER',7839,19810609,2450,NULL,10);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7788,'SCOTT','ANALYST',7566,19870419,3000,NULL,20);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7839,'KING','PRESIDENT',NULL,19811117,5000,NULL,10);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7844,'TURNER','SALESMAN',7698,19810908,1500,0,30);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7876,'ADAMS','CLERK',7788,19870523,1100,NULL,20);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7900,'JAMES','CLERK',7698,19810312,950,NULL,30);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7902,'FORD','ANALYST',7566,19810312,3000,NULL,20);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(7934,'MILLER','CLERK',7782,19820123,1300,NULL,10);

查看表结构：DESC emp;

保留表结构删除表数据：TRUNCATE emp;

Date对比：

Date： Oracle版本Date有to_date可以转换为多种格式

MySQL版本Date默认写法yyyyMMdd，读取时为yyyy-MM-dd