2019/04/09 sql的基本操作之建表和插入数据(SQL server以及Oracle)
所需表的创建以及数据的插入
建表
SQL server的建表语句
create table EMP
(
empno int not null,
ename VARCHAR(10),
job VARCHAR(9),
mgr int,
hiredate DATE,
sal float,
comm float,
deptno int,
sex VARCHAR(10) default '男'
)
Oracle的建表语句
create table EMP
(
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
sex VARCHAR2(10) default '男'
)
从上边可以看到在SQL server中 没有 NUMBER这个数据类型
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7777, '中文', 'english', null, null, null, null, null, '男');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7369, 'SMITH', 'CLERK', 7902, '1998/02/01', 800, null, 20, '男');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7499, 'ALLEN', 'SALESMAN', 7698,'1981/02/20', 1600, 300, 30, '女');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7521, 'WARD', 'SALESMAN', 7698, '1981/02/22', 1250, 500, 30, '女');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7566, 'JONES', 'MANAGER', 7839, '1981/04/02', 2975, null, 20, '女');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981/09/28', 1250, 1400, 30, '女');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981/05/01', 2850, null, 30, '女');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7782, 'CLARK', 'MANAGER', 7839, '1981/06/09', 2450, null, 10, '女');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987/04/19', 3000, null, 20, '男');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7839, 'KING', 'PRESIDENT', null, '1981/11/17', 5000, null, 10, '女');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7844, 'TURNER', 'SALESMAN', 7698, '1981/09/08', 1500, 0, 30, '女');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7876, 'ADAMS', 'CLERK', 7788, '1987/05/23', 1100, null, 20, '男');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7900, 'JAMES', 'CLERK', 7698, '1981/12/03', 950, null, 30, '女');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7902, 'FORD', 'ANALYST', 7566, '1981/12/03', 3000, null, 20, '女');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7934, 'MILLER', 'CLERK', 7782, '1982/01/23', 1300, null, 10, '男');
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (6688, 'MLDNJAVA', 'MANAGER', null, null, 2300, null, 20, '男');
在这里需要注意的是Oracle中的DATE 类型和SQL server的不一样
所以在插入数据语句需要把格式更改为一下格式
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno, sex)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30, '女');
在创建完表以及插入完数据之后,想要根据已有表创建新表,该怎么办呢,下面给出三种情况:
1 按emp的格式创建一个emp_01表,保留全部字段全部内容
SQL server:select 列名 into 新表名 from 旧表名
select * into emp_01 from emp;/*根据旧表建新表,含结构和数据*/
Oracle :create table 新表名 as select 列名 from 旧表
create table emp_01 as select * from emp
2 按emp的格式创建一个emp_02表,保留ename, sal和deptno三个字段的内容
SQL server:select ename,sal,deptno into emp_02 from emp;/*根据旧表建新表,迁移部分列的数据*/
Oracle : create table emp_02 as select ename,sal,deptno from emp
3 按emp的格式创建一个emp_03表,保留全部字段但不迁移数据。
SQL server:select * into emp_03 from EMP where 1 = 2;/*根据旧表建新表,只含结构*/
Oracle : create table emp_03 as select * from emp where 1=2
修改列的属性
给emp_01的dname字段修改类型为varchar
这两者语法上没什么区别,但是需要注意数据类型:
alter table emp_01 alter column dname varchar(2);/*更改列属性*/
删除emp_01表中的dname字段
alter table emp_01 drop column dname;/*删除表中一列*/
指定插入一条数据
将40号部门的姓名为LUIGI,工资为1500的员工插入emp_02中
insert into emp_02(deptno,ename,sal) values(40,'LUIGI',1500);
删除表
清空emp_01表并删除
drop table emp_01;