首页 > 代码库 > mysql
mysql
/*create table teacher( tno varchar(10) primary key,-- 教师编号 tname varchar(20) not null,-- 教师姓名 sex char(2) default ‘男‘,-- 性别 age date,-- 年龄 sal int,-- 工资 dname varchar(30) -- 所属系别 )*/ /*insert into teacher values(‘1001‘,‘张三‘,‘男‘,‘1987-08-08‘,3500,‘计算机‘); insert into teacher values(‘1002‘,‘李四‘,‘男‘,‘1983-06-10‘,4500,‘计算机‘)*/ #insert into teacher values(‘1003‘,‘李四‘,‘女‘,‘1984-06-10‘,4500,‘计算机‘) #insert into teacher values(‘1004‘,‘王五‘,‘女‘,‘1988-06-10‘,4000,‘计算机‘) #insert into teacher values(‘1005‘,‘啊啊‘,‘女‘,‘1988-06-10‘,4000,‘物理系‘) #insert into teacher values(‘1008‘,‘王二小‘,‘女‘,‘1988-06-10‘,4000,‘计算机‘) #排序 先按照工资排序 如果工资一样那么再按照年龄排序 asc代表升序 desc 代表降序 #select * from teacher ORDER BY sal DESC,age desc #查询工资最高的三个人 #select * from teacher ORDER BY sal desc limit 3 #别名 #select tname ‘姓名‘,sal ‘工资‘ from teacher #查询工资在4000到5000之间 #select * from teacher where sal>4000 and sal<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
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。