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