| 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); |