数据库sql的对比

-- >mysql部分 start-------------------------------------------------------------------------------------
create table student (
id int primary key auto_increment,
name varchar(20) not null,
age int not null
);

--mysql的数据类型 （1）数值类型：TINYINT，SMALLINT，MEDIUMINT，INT或INTEGER，BIGINT，FLOAT，DOUBLE，DECIMA，UNSIGNED，ZEROFILL
--（2）字符类型：CHAR，VARCHAR，BLOB，TEXT，BINARY，VARBINARY
--（3）日期时间类型：DATE,TIME,YEAR,DATETIME,TIMESTAMP
--（4）复合类型： ENUM ，SET

insert into student values(null, 'gxy', 20);
insert into student values(3, 'gxy', 20);
insert into student values(2,'gxy', 20);

create table employee921 (
id int primary key auto_increment,
name varchar(50),
salary bigint,
deptid int
);

insert into employee921 values(null,'zs',1000,1),(null,'ls',1100,1),(null,'ww',1100,1),
(null,'zl',900,1) ,(null,'zl',1000,2), (null,'zl',900,2),(null,'z
l',1000,2) , (null,'zl',1100,2);


select
employee921.id,
employee921.name,
employee921.salary,
employee921.deptid tid
from
employee921
where
salary > (select
avg(salary)
from
employee921
group by deptid
having deptid = tid);

select a.name,a.salary,a.deptid
from employee921 a,
(select deptid,avg(salary) avgsal from employee921 group by deptid ) b
where a.deptid=b.deptid and a.salary > b.avgsal;


DELIMITER $$

CREATE PROCEDURE insert_Student (_name varchar(50), _age int , out _id int)
BEGIN
insert into student values(null,_name, _age);
select max(id) into _id from student;
END;$$
DELIMITER ;

call insert_Student('wfz',23,@id);
select @id;


create trigger update_Student BEFORE update on student FOR EACH ROW
begin

end;
-- mysql部分 end<-------------------------------------------------------------------------------------

-- oracle部分 start-----------------------------------------------------------------------------------

create table student (
id int primary key,
name varchar(20) not null,
age int not null
);

-- oracle的数据类型 （1）数值类型：number,INTEGER,FLOAT,BINARY_FLOAT,BINARY_DOUBLE
--（2）字符串类型 ：CHAR,NCHAR.VARCHAR,VARCHAR2,NVARCHAR2
--（3）日期类型：DATE,TIMESTAMP
--（4）LOB类型(): BLOB、CLOB、NCLOB、BFILE
--（5）RAW & LONG RAW类型:

insert into student values(1, 'gxy', 20);
insert into student values(3, 'gxy', 20);
insert into student values(2,'gxy', 20);

create table employee921 (
id int primary key,
name varchar(50)
salary int,
deptid int
);

insert into employee921 values(null,'zs',1000,1),(null,'ls',1100,1),(null,'ww',1100,1),
(null,'zl',900,1) ,(null,'zl',1000,2), (null,'zl',900,2),(null,'z
l',1000,2) , (null,'zl',1100,2);


select
employee921.id,
employee921.name,
employee921.salary,
employee921.deptid tid
from
employee921
where
salary > (select
avg(salary)
from
employee921
group by deptid
having deptid = tid);

select a.name,a.salary,a.deptid
from employee921 a,
(select deptid,avg(salary) avgsal from employee921 group by deptid ) b
where a.deptid=b.deptid and a.salary > b.avgsal;


DELIMITER $$

CREATE PROCEDURE insert_Student (_name varchar(50), _age int , out _id int)
BEGIN
insert into student values(null,_name, _age);
select max(id) into _id from student;
END;$$
DELIMITER ;

call insert_Student('wfz',23,@id);
select @id;


create trigger update_Student BEFORE update on student FOR EACH ROW
begin

end;