| MySQL | 1、mysqladmin -h localhost -u root -p 2、password 3、将下面的代码复制到终端执行 #假如存在sql_db删除数据库sql_db
drop database if exists sql_db; #创建数据库sql_db
create database sql_db ; #进入sql_db数据库
use sql_db ; #创建数据表dept(部门表)
#deptno(部门编号) dname(部门名称) loc(部门所在地)
create table dept
(
deptno int not null primary key ,
dname varchar(255) not null ,
loc varchar(255) not null
);
insert into dept values(10 , 'ACCOUNTING' , 'NEW YORK' ) ;
insert into dept values(20 , 'RESEARCH' , 'DALLAS') ;
insert into dept values(30 , 'SALES' , 'CHICAGO') ;
insert into dept values(40 , 'OPERATIONS' , 'BOSTON') ; #创建数据库表emp(员工表)
#empno(员工编号) ename(员工名) job(职业) mgr hiredate(入职时间) sal(工资) comm(提成) deptno(部门编号)
create table emp
(
empno int not null primary key ,
ename varchar(255) not null ,
job varchar(255) not null ,
mgr int,
hiredte date not null ,
sal int not null ,
comm int ,
deptno int not null ,
foreign key(deptno) references dept(deptno)
); insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
insert into emp values(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
insert into emp values(7566,'JONES','MANAGER',7839,'1981-4-02',2975,null,20 );
insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
insert into emp values(7698,'BLAKE','MANAGER',7839,'1981-5-01',2850,null,30);
insert into emp values(7782,'CLARK', 'MANAGER',7839,'1981-6-09',2450,null,10);
insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-4-19',3000,null,20);
insert into emp values(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-9-08',1500,0,30);
insert into emp values(7876,'ADAMS','CLERK',7788,'1987-5-23',1100,null,20);
insert into emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'MILLER','CLERK',7782,'1982-1-23',1300,null,10); |
| PostgreSQL | 1、创建数据库并进入数据库
sudo -u postgres createdb sql_db(mydb为您要建立的数据库名)
sudo -u postgres psql sql_db(这样就可以进去该数据库) 2、按Ctrl+Z退出postgres数据库，并重新登录数据库sql_db
sudo -u postgres psql sql_db ; 3、将下面的代码复制到终端执行 --删除数据表dept--
drop table if exists dept ; --创建数据表dept(部门表)--
--deptno(部门编号) dname(部门名称) loc(部门所在地)--
create table dept
(
deptno integer not null primary key ,
dname text not null ,
loc text not null
);
insert into dept values(10 , 'ACCOUNTING' , 'NEW YORK' ) ;
insert into dept values(20 , 'RESEARCH' , 'DALLAS') ;
insert into dept values(30 , 'SALES' , 'CHICAGO') ;
insert into dept values(40 , 'OPERATIONS' , 'BOSTON') ; --创建数据库表emp(员工表)--
create table emp
(
empno integer not null primary key ,
ename text not null ,
job text not null ,
mgr integer,
hiredte date not null ,
sal integer not null ,
comm integer ,
deptno integer not null ,
foreign key(deptno) references dept(deptno)
); insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
insert into emp values(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
insert into emp values(7566,'JONES','MANAGER',7839,'1981-4-02',2975,null,20 );
insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
insert into emp values(7698,'BLAKE','MANAGER',7839,'1981-5-01',2850,null,30);
insert into emp values(7782,'CLARK', 'MANAGER',7839,'1981-6-09',2450,null,10);
insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-4-19',3000,null,20);
insert into emp values(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-9-08',1500,0,30);
insert into emp values(7876,'ADAMS','CLERK',7788,'1987-5-23',1100,null,20);
insert into emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'MILLER','CLERK',7782,'1982-1-23',1300,null,10); |