mysql复习

insert into tb_emp values(20,'xa',1);
select * from tb_emp order by empno desc
delete from tb_emp where empno=20;
update tb_emp set empname='xs',deptno=5 where empno=101;
select * from t_stdudent;
select * from t_course;
select stu_id,course_id,grade from t_Score where grade between 70 and 80;
select grade from t_Score where course_id=2 order by grade desc limit 0,1
select max(grade) from t_score where course_id=2
select course_name,course_id from t_course where course_id in(select distinct course_id from t_Score)
select avg(grade),max(grade),min(grade) from t_score where course_id=2
select stu_Dept,count(*) from t_student group by stu_Dept;
select count(distinct(stu_id)),avg(grade) from t_score;
select stu_id,sum(grade) from t_score group by stu_id having sum(grade)>200
select course_id,course_name from t_course where course_id not in(select distinct course_id from t_score);

select count(*) from t_Studnet;
select count(distinct stu_id) from t_Score;
select avg(grade) from t_score where course_id=1
select format(avg(grade),2) from t_Score where course_id=1
select max(grade) from t_Score where course_id=1
select course_id,count(stu_id) from t_score group by course_id
select stu_id from t_score group by stu_id having count(*)>2
select * from tb_dept inner join tb_emp on tb_dept.deptno = tb_emp.deptno
select * from tb_dept a,tb_emp b where a.deptno=b.deptno
select * from tb_emp left join tb_dept on tb_emp.deptno=tb_Dept.deptno
select * from t_student order by stu_id limit 1,5

　　

create procedure sting_a(in x int,in y int)
begin
set @x1=y*(x-1);
set @x2=CONCAT('select * from t_student order by stu_id',' limit ', @x1,',',y);
prepare se from @x2;
execute se;
end;

 

 

create procedure a5(in x int,in y int,in c varchar(50),in d varchar(20),in e varchar(50),in ziduan varchar(100),out _pagecount int,out _totalcount int)
begin
set @x1=y*(x-1);
set @rowindex=0;
set @x2=CONCAT('select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,',ziduan,' from ',c,
CASE IFNULL(e,' ')
when '' then ''
else concat(' where ',e)
end,' order by ',
d,
' limit ',
@x1,
',',
y);
prepare se from @x2;
execute se;
deallocate prepare se;
set _totalcount=found_rows();
  if(_totalcount <=y)
  then
    set _pagecount=1;
   else
    if(_totalcount % y>0)
    then
      set _pagecount =ceil(_totalcount / x);
      else
      set _pagecount=_totalcount / y;
      end if;
    end if;      
end;
call a5(1,10,'t_student','stu_id',"age=20",'stu_id,stu_name',@num1,@num2)
drop procedure sting_a