Mysql高级第一天(laojia)

select char_length('我们love');

select *, char_length(sname) '姓名字数' from tbl_student;


select '对方' + '5';

select '学号：S10013，姓名：'


select CONCAT('学号：', sno, '，姓名：', sname) '学生西宁' from tbl_student;

select insert('hello,jzp,hi',1, 0, 'dd');

select REPLACE('我们的AAA主席非常厉害，我爱AAA。', 'AAA', '****')
select left('zhangwuji@qq.com', locate('@', 'zhangwuji@qq.com')-1) 'username';

select right('zhangwuji@qq.com', char_length('zhangwuji@qq.com') - locate('@', 'zhangwuji@qq.com') );

select SUBSTRING('zhangwuji@qq.com', locate('@', 'zhangwuji@qq.com')+1);

select *, ifnull(saddr, '未知住址')  as address, if(sex = '男', 'male', 'female') as 'gender' from tbl_student;


select 
    sname, 
    sscore, 
    sdengji,
    case 
        when sscore >= 90 then 'Good'
        when sscore >= 60 then 'Normal'
        else 'Bad'
    end as en,
    case sdengji
        when '优' then '☆☆☆'
        when '中' then '☆☆'
        else '☆'
    end as 'star',
    saddr
from tbl_student;

select now();

select left(current_date(),4);

select current_time();

-- 获取当前日期时间（指定日期时间）的时间戳
select unix_timestamp();


select year(now());
select month(now());
select day(now()), dayofmonth(now());
select hour(now());
select minute(now());
select second(now());
select dayofweek('1995-5-3'), weekday('1995-5-3'), dayname(now());

select DAYOFYEAR(now());
select week(now()), WEEKOFYEAR(now());


select * from where year(sellTime) = year(now()) and month(now()) - month(sellTime) = 1 


select TIMESTAMPDIFF(year, '2016-8-25 8:27:00', now());

select DATE_ADD(now(),INTERVAL (-5) minute)

drop database if exists test;

create database test;

use test;

create table tbl_klass
(
    kid int primary key auto_increment,
    kname varchar(20) not null
);

create table tbl_student
(
    sid int primary key auto_increment,
    sno char(6) not null unique,
    sname varchar(20) not null,
    sbirthdate date,
    sex enum('男', '女') not null,
    saddr varchar(100) default '博为峰学生公寓',
    sregistTime datetime not null,
    skid int not null,
    foreign key (skid) references tbl_klass (kid)
);

insert into tbl_klass (kname) values ('11期'), ('12期'), ('13期');
select * from tbl_klass;

insert into tbl_student values (null, 'S10015', '杨玉松', '1970-9-2', '女', default, now(), 1);
select * from tbl_student;

/* 创建一个视图 */
create view v_students as
    select sno, sname, kname from tbl_student left join tbl_klass on skid = kid;

/* 删除一个视图 */
drop view if exists v_students;


-- 删除触发器
drop trigger if exists trig_tx;


-- 创建触发器
-- trig_tx 会监听tbl_trans表，当在tbl_trans表上发生了一次插入后
-- 执行触发器的主体内容
create trigger trig_tx before insert on tbl_trans for each row
BEGIN
    declare v decimal(20, 2);
    declare b decimal(20, 2);

    select abalance into b from tbl_acount where anum = new.tnum;

    if new.ttype = '收入' or new.tmoney <= b then    
        if new.ttype = '支出'  then
            set v = 0 - new.tmoney;        
        else 
            set v = new.tmoney;
        end if;

        update tbl_acount set abalance = abalance + v where new.tnum = anum;
    ELSE
        -- 用户抛出自定义异常
        signal SQLSTATE 'HY000' set message_text = '交易失败：余额不足！';
    end if;

END;

-- 删除触发器
drop trigger if exists trig_tx2;

create trigger trig_tx2 after update on tbl_trans for each ROW
BEGIN
    if old.ttype = '支出' then
        
        update tbl_acount set abalance = abalance + old.tmoney - new.tmoney where anum = old.tnum;
    ELSE
        update tbl_acount set abalance = abalance - old.tmoney  + new.tmoney where anum = old.tnum;
    end if;
        
end;


drop PROCEDURE if exists stu_regist;

create procedure stu_regist(out total int, no char(6), name varchar(20), birthdate date, se varchar(10), addr varchar(100), klassName varchar(20))
BEGIN
    declare klassId int;

    select kid into klassId from tbl_klass where kname = klassName;
  if addr = '' then
        insert into tbl_student (sno, sname, sbirthdate, sex, saddr, skid, sregistTime) values (no, name, birthdate, se, default, klassId, now());
    else 
        insert into tbl_student (sno, sname, sbirthdate, sex, saddr, skid, sregistTime) values (no, name, birthdate, se, addr, klassId, now());
    end if;

    update tbl_klass set kstucount = kstucount + 1 where kid = klassId;
    
    select kstucount into total from tbl_klass where kid = klassId;
end;


set @t = -1;

call stu_regist(@t, 'S10047', '珠儿3', '1995-5-5', '女', 'fff', '13期');

select @t;

call proc_testWhile();

drop procedure if exists proc_testLoop;
create procedure proc_testLoop()
BEGIN
    declare i int;
    set i = 1;
    
    lp1 : LOOP
        if i = 4 then 
            set i = i + 1;
            iterate lp1;
        end if;
        select i;
        set i = i + 1;
        if i > 10 then
            leave lp1;
        end if;
    end loop lp1;

end;




insert into tbl_acount values ('6223 1234 4567 8888', 1000, now());
insert into tbl_acount values ('6223 1234 4567 9999', 500, now());
insert into tbl_acount values ('6223 1234 4567 0000', 567500, now());
insert into tbl_acount values ('6223 1234 4567 1111', 10, now());
insert into tbl_acount values ('6223 1234 4567 2222', 3200, now());

insert into tbl_trans values (null, '6223 1234 4567 8888', '收入', 1500, now());
insert into tbl_trans values (null, '6223 1234 4567 8888', '支出', 500, now());

drop procedure if exists payAll;

create procedure payAll(m decimal(20, 2))
BEGIN
    declare totalCount int;
    declare i int;
    declare num varchar(19);
    declare ban decimal(20, 2);
    declare tt datetime;

    -- 跟新所有账户的余额
    update tbl_acount set abalance = abalance + m;

    -- 生成所有的交易记录（金额：m，类型：收入，时间：now()）    

    select count(*) into totalCount from tbl_acount;
    set i = 0;
    set tt = now();
    while i < totalCount DO
        select anum into num, abalance into ban from tbl_acount limit i, 1;
        if ban >= 1000 then
            insert into tbl_trans values (null, num, '收入', m, tt);
        end if;
        set i = i + 1;
    end while;

end;

call payAll(1);

select * from tbl_acount;
select * from tbl_trans;


drop PROCEDURE if exists payAll2;

create procedure payAll2( m decimal(20,2) )
begin
    declare num varchar(19);
    
    declare tt datetime default now();

    declare over int default 0; -- 定义了一个普通int类型变量over，初始值0；用over来指示游标是否已经读完
    
    declare c cursor for select anum from tbl_acount; -- 定义（声明）一个游标

    declare continue handler for not found set over = 1; -- 条件定义（当发现某个游标，某次fetch后，没有found到数据，则触发这个条件，将变量over设置为1）

    open c; -- fetch之前先要打开游标

    lp: LOOP

            fetch c into num; -- 获取游标遍历的结果集当前指针的下一行数据，并且抓取到给变量num赋值。也可能这一次（下一行没数据）而没有found到数据

            if over = 1 THEN
                leave lp;
            end if;

            insert into tbl_trans values (null, num, '收入', m, tt);

    end loop lp;

    close c; -- 用完游标后，要关闭游标
    
end;

各科成绩前两名：SELECT * FROM tbl_exam a1 WHERE (SELECT count(*) FROM tbl_exam a2 where a1.ecouse=a2.ecouse and a1.escore<a2.escore)<2 ORDER BY ecouse,escore DESC