Oracle与mysql基础语法

创建表

Oracle：

//用户表
CREATE  TABLE  user(
	id int PRIMARY KEY,
	username varchar2(60) not NULL,
	password varchar2(60) NOT NULL,
	email varchar2(60) NOT null
	
);
//创建一个自增序列
CREATE SEQUENCE seq_USER
	 MINVALUE 1
	 MAXVALUE 99999
	 START WITH 1
	 INCREMENT BY 1
	 nocache;

//创建user表的自增触发器
CREATE OR REPLACE TRIGGER  USER_insert //User_insert为触发器的名字吗，唯一。
	BEFORE INSERT ON "USER" //触发的表，User为oracle系统表，所以加""
	FOR EACH ROW 
	BEGIN 
		SELECT seq_USER.nextval //select后跟自增序列
		INTO:NEW.ID//ID为要自增的组件
		FROM dual;
	END;

CREATE TABLE province(
	id int PRIMARY KEY,
	name varchar2(60),
	tags varchar(60),
	placecounts int
);

CREATE OR REPLACE TRIGGER  province_insert
	BEFORE INSERT ON province
	FOR EACH ROW 
	BEGIN 
		SELECT seq_USER.nextval
		INTO:NEW.ID
		FROM dual;
	END province_insert;



 create table place(
    id Integer PRIMARY key,
    provinceid Integer,
    name varchar2(60),
    picpath varchar2(100),
    hottime Date,
    hotticket binary_double,
    dimticket binary_double,
    placedes varchar2(300),
   
  );
 CREATE OR REPLACE TRIGGER  place_insert
	BEFORE INSERT ON place
	FOR EACH ROW 
	BEGIN 
		SELECT seq_USER.nextval
		INTO:NEW.ID
		FROM dual;
	END province_insert;


 
 ALTER TABLE place ADD CONSTRAINT fK_t_place provinceid REFERENCES province(id);
			//表名     添加约束      约束名字    外键          关键的另一个表的键
			//place表里的provinceid 关联province表里的id

Mysql

  --用户表
  create table t_user(
    id int(6) PRIMARY key auto_increment,
    username varchar(60),
    password varchar(60),
    email varchar(60)
  );


  --省份表
  create table t_province(
    id int(6) PRIMARY key auto_increment,
    name varchar(60),
    tags varchar(80),
    placecounts int(4)
  );

  --景点表
  create table t_place(
    id int(6) PRIMARY key auto_increment,
    name varchar(60),
    picpath varchar(100),
    hottime TIMESTAMP,
    hotticket double(7,2),
    dimticket double(7,2),
    placedes varchar(300),
    provinceid int(6) REFERENCES t_province(id)
  );