SQL(MySQL&Oracle&SQLServer&DB2&PostgreSQL等)对比学习

(数据库的安装与配置: 在本人博客里有一篇DB for Ubuntu8文章中)    

本人使用测试和运行SQL语句的系统是Ubuntu8(Linux)和Solaris10(Unix)

 

一、各种数据库的差别

数据库名	数据库数量
MySQL	多个
Oracle	一个
SQL Server	多个
DB2	多个
PostgreSQL	多个
Sybase	多个

 

二、如何进入数据库

数据库名	SQL语句
MySQL	mysql -h 127.0.0.1 -u root -p password
Oracle	1、将所有权限授权给scott grant All PRIVILEGES to scott 2、登录 sqlplus scott password 或 PL/SQL登录
SQL Server	启动服务器，然后使用查询分析器登录
DB2	先要编目节点：catalog tcpip node node_id remote ip server port_no 然后编目数据库:catalog db dbname at node nod_name<!---->
PostgreSQL	sudo -u postgres psql postgres 或 pgadmin3登录
Sybase	netstat   -na|more   (查Sybase端口) telnet   xxx.xxx.xxx.xxx(网卡地址)   xxxx(Sybase端口)

 

三、查看所有数据库

数据库名	SQL语句
MySQL	show databases;
Oracle	只有一个数据库
SQL Server	select name from sysdatabases;
DB2
PostgreSQL
Sybase

 

四、进入数据库

数据库名	SQL语句
MySQL	use database_name;
Oracle	只有一个数据库
SQL Server	use database_name go
DB2
PostgreSQL	sudo -u postgres psql db_name;
Sybase	use database_name go

 

五、查看数据库的所有表 

数据库名	SQL语句
MySQL	show tables;
Oracle	select * from tab; select table_name from all_tables where owner='dbuser';
SQL Server
DB2
PostgreSQL
Sybase

 

六、创建数据库sql_db和两张表dept、emp

数据库名	SQL语句
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);
Oracle	Oracle本身就有dept和emp两张表
SQL Server
DB2
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);
Sybase

 

 

 

 

 

 

= =注意：待添补==