mysql
时间：2021-07-01 10:21:17
帮助过：10人阅读
<5000
#select * from teacher where sal BETWEEN 4000 and 5000
#select * from teacher where age is not null
#查找出教师工资大于4000的 或者 小于 3000的
#select * from teacher where sal>4000 or sal
<3000
#查找出计算机系或者物理系的教师工资大于3000的
#select * from teacher where (dname=‘计算机‘ or dname=‘物理系‘) and sal>3000
#select * from teacher where dname in(‘计算机‘,‘物理系‘) and sal>3000
#select * from teacher where (dname=‘计算机‘ and sal>3000) or (dname=‘物理系‘ and sal>3000)
#查询姓王的老师
#select * from teacher where sal like ‘4%‘
/*create VIEW v2
as
select * from teacher where tno in(select tno from tc where course=‘java‘)*/
#select * from v2
/*create VIEW v1
as
select tname,sal from teacher*/
/*create view v3(t,s)
as
select tname,sal from teacher*/
#select * from v3
/*alter VIEW v1
as
select * from teacher*/
#select * from v1
#insert into v1(tno,tname) values(‘1008‘,‘abc‘)
/*create view v4
AS
select avg(sal) FROM teacher*/
#select * from v4
#update from v4 set avg(sal)=3000
#select * from v1
#select avg(sal) from teacher
#insert into teacher VALUES(‘1100‘,‘aa‘,‘男‘,‘1999-09-09‘,2000,‘数学‘)
#insert into teacher select * from teacher
#select * from teacher where tno=‘1100‘
#alter table teacher
#add index ix(tno)
#select avg(sal) into @a from teacher ;
#select @a
/*create PROCEDURE fun()
BEGIN
set @i=0;
set @sum=0;
while @i<101 DO
set @sum=@sum+@i;
set @i=@i+1;
end while;
select @sum;
end*/
/*create PROCEDURE fun1()
BEGIN
DECLARE sun int;
DECLARE i int;
set sun=0;
set i=0;
while i<101 DO
set sun=sun+i;
set i=i+1;
end while;
select sun;
end*/
#call fun1()
#平均工资大于4000显示工资高，否则显示工资低
/*create PROCEDURE fun2()
BEGIN
select avg(sal) into @a from teacher;
IF(@a>4000) THEN
select ‘工资高‘;
ELSE
select ‘工资低‘;
end if;
end
*/
#call fun2()
#创建一个带输入参数的存储过程，查看指定教师编号的教师信息
/*create PROCEDURE fun3(in id VARCHAR(20))
BEGIN
select * from teacher where tno=id;
end*/
#call fun3(‘1002‘)
#创建一个带有输出参数的存储过程，返回教师表的最高工资
/*create PROCEDURE fun4(out m INT)
BEGIN
select MAX(sal) into m FROM teacher;
END*/
#call fun4(@m);
#select @m
#创建一个函数返回指定系的教师平均工资
/*create FUNCTION f1(d varchar(20))
RETURNS INT
BEGIN
select avg(sal) into @avg from teacher;
return @avg;
END*/
#SELECT f1(‘计算机‘)
#select avg(sal) from teacher
/*create PROCEDURE p1(in d VARCHAR(20))
BEGIN
select * from teacher where dname=d;
end*/
#select * from (call p1(‘计算机‘))
#select STRCMP(‘dbc‘,‘bc‘)
/*select tname,
case
when age>‘1990‘ then ‘年轻‘
when age<‘1985‘ then ‘老年‘
else ‘中年‘ END as ‘age‘
from Teacher*/
/*create table bank(
id VARCHAR(10) PRIMARY key,
bname VARCHAR(10),
money INT
)*/
/*create table info(
carid varchar(10),
transDate datetime,
type VARCHAR(10),
money int
)*/
#update bank set money=money-200 where id=‘1001‘;
#insert into info VALUES(1001,CURRENT_TIMESTAMP(),‘支取‘,‘200‘)
/*create TRIGGER t1
AFTER update on bank
for each ROW
BEGIN
insert into info VALUES(‘1001‘,CURRENT_TIMESTAMP(),‘支取‘,200);
end
*/
/*create TRIGGER t2
AFTER update on bank
for each ROW
BEGIN
if(new.money<0) THEN
insert into a values(1,2);
end if;*/
/*if(old.money-new.money>0) THEN
insert into info VALUES(old.id,‘支取‘,old.money-new.money);
else
insert into info VALUES(old.id,CURRENT_TIMESTAMP(),‘存款‘,new.money-old.money);
end if;*/
#end
update bank set money=money-10000 where id=‘1001‘;
mysql
标签：别名 var mysq timestamp trigger table returns mit val
