mysql高级复习题-爱代码爱编程

SELECT playTime AS '日期',
(SELECT COUNT(result)FROM t_a WHERE result='胜' )AS '胜',

(SELECT COUNT(result) FROM t_a WHERE result='负' )AS '负'
FROM t_a 
GROUP BY playTime;
-- 1、
select playTime,
 sum(case when result='胜' then 1 else 0 end)'胜',
sum(case when result='负' then 1 else 0 end)'负' from t_a group by playTime
 -- 2、
SELECT name,
Max(CASE kecheng WHEN '数学' THEN fenshu ELSE 0 END )数学,
Max(CASE kecheng WHEN '语文' THEN fenshu ELSE 0 END )语文,
Max(CASE kecheng WHEN '英语' THEN fenshu ELSE 0 END )英语
FROM t_b
GROUP BY name HAVING 数学>80 AND 语文>80 AND 英语>80;
-- 3、删除除了编号不同，其他都相同的冗余学生信息
DELETE s1 FROM t_c s1 INNER JOIN t_c s2
 WHERE s1.bianhao<s2.bianhao AND s1.xuehao=s2.xuehao 
AND s1.name=s2.name AND s1.kechenghao=s2.kechenghao 
AND s1.kecheng=s2.kecheng AND s1.fenshu=s2.fenshu;
-- 4、四、写一个mysql 通用存储过程分页
DELIMITER $$
 
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `mysqlgj4`.`pro_1`(pageindex INT,rowss INT)
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
SET @pro=CONCAT('select * from student limit ',(pageindex-1)*rowss,',',rowss);
PREPARE pro FROM @pro;
EXECUTE pro;
    END$$
 
DELIMITER ;
#调用存储过程（1表示页数，5表示每页显示多少条数据）
CALL pro_1(1,5);
-- 创建触发器，当用户确定下订单时，同时更新对应商品库存记录，当订单商品数量超过库存时， 修改订单数量为最大库存。