MySQL存储过程题目

来源：互联网发布：java算法书籍推荐编辑：程序博客网时间：2024/06/10 22:20
#1.计算1到指定数之间的整数和    #(1).创建        DELIMITER //        DROP PROCEDURE IF EXISTS pro_sum;//        CREATE PROCEDURE pro_sum(n1 INT,OUT n2 INT)        BEGIN            DECLARE s INT DEFAULT 1;            DECLARE res INT DEFAULT 0;    WHILE res<n1 DO                SET res=res+s;                SET res=res+1;            END WHILE;    SET n2=res;        END;        //    #(2).调用        CALL pro_sum(100,@result);        SELECT @result;#2.九九乘法表    #(1).创建        DELIMITER //        DROP PROCEDURE IF EXISTS pro_tab;//        CREATE PROCEDURE pro_tab()        BEGIN            DECLARE n1 INT DEFAULT 1;            DECLARE n2 INT DEFAULT 1;            DECLARE strlen VARCHAR(500) DEFAULT '';    WHILE n1<=9 DO                SET n2=1;                WHILE n2<=n1 DO                    SET strlen=CONCAT(strlen,n1,'*',n2,'=',n1*n2,' ');                    SET n2=n2+1;                END WHILE;                SET strlen=CONCAT(strlen,'\n');                SET n1=n1+1;            END WHILE;            SELECT strlen;        END;        //    #(2).调用        CALL pro_tab();#3.写出一个分页的存储过程#   定义如下#       tablePager(tableName,curPage,pageSize)#   调用 #       tablePager('Emp',2,10) #       查询emp表中 第二页的数据（每页显示10条 第二页就是 10-20条）    #(1).创建表     DROP TABLE IF EXISTS demo_table;#删除表        CREATE TABLE demo_table(d_id INT PRIMARY KEY AUTO_INCREMENT ,d_name VARCHAR(20));#创建表    TRUNCATE TABLE demo_table;#截断表    SET auto_increment_increment=1;#设置步长    SET auto_increment_offset=1;#设置初始值    SHOW VARIABLES LIKE 'auto_increment%';#查看自增长    SHOW CREATE TABLE demo_table;#查看建表代码    #(2).添加数据        DELIMITER //DROP PROCEDURE IF EXISTS insert_data;//CREATE PROCEDURE insert_data()BEGIN            DECLARE n1 INT DEFAULT 1;    WHILE n1<=500 DO                INSERT INTO demo_table(d_name) VALUES('name');                SET n1=n1+1;            END WHILE;            COMMIT;            SELECT * FROM demo_table;END;//CALL insert_data();//    #(3).创建分页过程        DELIMITER //DROP PROCEDURE IF EXISTS pro_display;//CREATE PROCEDURE pro_display(tab_name VARCHAR(20),cur_page INT,page_size INT)BEGIN    SET @str=CONCAT('SELECT * FROM ',tab_name,' LIMIT ',(cur_page-1)*page_size,',',page_size);            PREPARE sqlstr FROM @str;            EXECUTE sqlstr;            DEALLOCATE PREPARE sqlstr;END;//    #(4).调用        CALL pro_display('demo_table',10,20);#4.定义存储过程 可以传入以下参数#   query(ename,job,sal)#   如果传入了某几个参数 以参数组合的形式查询结果#   要求输出查询的结果#   比如调用过程如下#       query('Cleck',null,null);#       查询的sql为：#           select * from emp where ename like '%Cleck%';#       query('Cleck','Manager',null);#       查询的sql为：#           select * from emp where ename like '%Cleck%' and job like '%Manager%'    #(1).创建emp表        DROP TABLE IF EXISTS EMP;CREATE TABLE EMP(  empno    INT NOT NULL COMMENT '雇员编号',  ename    VARCHAR(10) COMMENT '雇员姓名',  job      VARCHAR(9) COMMENT '工作',  mgr      INT COMMENT '领导编号',  hiredate DATE COMMENT '入职日期',  sal      DECIMAL(7,2) COMMENT '雇员薪资',  comm     DECIMAL(7,2) COMMENT '额外奖金',  deptno   INT COMMENT '部门编号');INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-20', 1250, 500, 30);INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-02-04', 2975, NULL, 20);INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 19300, NULL, 10);COMMIT;SELECT * FROM emp;    #(2).编写储存过程        DELIMITER //        DROP PROCEDURE IF EXISTS pro_query;        //CREATE PROCEDURE pro_query(ename VARCHAR(20),ejob VARCHAR(20),esal INT)BEGIN    SET @str='SELECT * FROM emp where 1=1';    IF (ename IS NOT NULL) THEN        SET @str=CONCAT(@str,' and ename like ','''%',ename,'%''');    END IF;    IF (ejob IS NOT NULL) THENSET @str=CONCAT(@str,' and job like ','''%',ejob,'%''');    END IF;    IF (esal IS NOT NULL) THENSET @str=CONCAT(@str,' and sal=',esal);    END IF;    SELECT @str;            PREPARE sqlstr FROM @str;            EXECUTE sqlstr;            DEALLOCATE PREPARE sqlstr;END;//CALL pro_query(NULL,'CLERK',NULL);//#5.输出EMP表中所有的雇员名称，部门名称，年薪    #(1).创建dept表CREATE TABLE DEPT(  deptno INT NOT NULL COMMENT '部门编号',  dname  VARCHAR(14) COMMENT '部门名称',  loc    VARCHAR(13) COMMENT '部门等级');INSERT INTO DEPT (deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK');INSERT INTO DEPT (deptno, dname, loc) VALUES (20, 'RESEARCH', 'DALLAS');INSERT INTO DEPT (deptno, dname, loc) VALUES (30, 'SALES', 'CHICAGO');INSERT INTO DEPT (deptno, dname, loc) VALUES (40, 'OPERATIONS', 'BOSTON');SELECT * FROM dept;    #(2).编写储存过程        DELIMITER //DROP PROCEDURE IF EXISTS pro_show//CREATE PROCEDURE pro_show()BEGIN            SET @str='SELECT ename,dname,12*(sal+IFNULL(comm,0)) FROM emp NATURAL JOIN dept';    PREPARE sqlstr FROM @str;            EXECUTE sqlstr;            DEALLOCATE PREPARE sqlstr;END;//CALL pro_show();//#6.找出1-1000中的所有质数        DELIMITER //        DROP PROCEDURE IF EXISTS pro_num        //        CREATE PROCEDURE pro_num()        BEGIN    DECLARE sta INT;    DECLARE num INT DEFAULT 1;    DECLARE str VARCHAR(1000) DEFAULT '';    WHILE num<=1000 DO            #判断数字num 是否只能被1和本身整除，如果它能被区间[2~num)之间的整除便是合数，否则它就是质数        SET sta=2; #初始化变量mark:WHILE sta<=num DO #条件语句    IF (sta=num) THEN        SET str=CONCAT(str,num,'\n');    ELSEIF(num%sta=0) THEN        LEAVE mark; #跳转语句：离开循环(标记的循环)    END IF;    SET sta=sta+1; #迭代语句END WHILE;SET num=num+1;    END WHILE;    SELECT str;        END;        //        CALL pro_num();           //#7.定义一个存储过程 传入表名#   删除该表中的重复记录#   比如 deleteMul(tableName)#       调用 deleteMul('emp'); 必须删除表emp的重复数据            #(1).复制emp表        DROP TABLE IF EXISTS emp2;CREATE TABLE IF NOT EXISTS emp2 AS (SELECT * FROM emp);          SELECT * FROM emp2;    #(2).添加重复记录INSERT INTO emp2 SELECT * FROM emp;SELECT * FROM emp2;    #(3).创建存储过程并调用        DELIMITER //DROP PROCEDURE IF EXISTS deleteMul;//CREATE PROCEDURE deleteMul(tab_name VARCHAR(20))BEGIN   DECLARE col_names VARCHAR(1000) DEFAULT ''; #所有列名 empno,ename,job,mgr,hiredate,sal,comm,deptno           DECLARE pri_key VARCHAR(1000) DEFAULT ''; #主键列名 empno_ename_job_mgr_hiredate_sal_comm_deptno        SELECT GROUP_CONCAT(column_name) INTO col_names FROM information_schema.columns WHERE table_name=tab_name; #找出所有列名           SET pri_key=REPLACE(col_names,',','_');   SET @str1=CONCAT('ALTER TABLE ',tab_name,' ADD(',pri_key,' INT PRIMARY KEY AUTO_INCREMENT);'); #添加新列的sql语句(自增长主键)   SET @str2=CONCAT('DELETE FROM ',tab_name,' WHERE ',pri_key,' NOT IN (SELECT t.pri_key FROM (SELECT MIN(',pri_key,') pri_key FROM ',tab_name,' GROUP BY ',col_names,') t);'); #删除重复列的sql语句   SET @str3=CONCAT('alter table ',tab_name,' drop column ',pri_key); #删除新增列的sql语句     #执行@str1的sql语句，添加新列(自增长主键)   PREPARE sqlstr1 FROM @str1;      EXECUTE sqlstr1;   DEALLOCATE PREPARE sqlstr1;      #执行@str2的sql语句，删去重复的记录   PREPARE sqlstr2 FROM @str2;      EXECUTE sqlstr2;   DEALLOCATE PREPARE sqlstr2;      #执行@str3的sql语句，删除新增的列   PREPARE sqlstr3 FROM @str3;      EXECUTE sqlstr3;   DEALLOCATE PREPARE sqlstr3;END;//CALL deleteMul('emp2');//    #(4).查看删除重复后的表        SELECT * FROM emp2;#8.查找出数据库对象下，每张表的记录数#mysql数据库下的每张表记录数        USE information_schema;        SELECT table_name,table_rows FROM TABLES WHERE table_schema='mysql' ORDER BY table_rows ASC;#9.创建表cc 插入数据如下（列 c1,c2）： #       [c1][c2]#       [1 ][西] #       [1 ][安] #       [1 ][的]#       [2 ][天] #       [2 ][气] #       [3 ][好] #   查询显示为 #     1 西安的#     2 天气#     3 好    #(1).建表        DROP TABLE IF EXISTS cc;        CREATE TABLE cc(    c1 INT,    c2 VARCHAR(10)        );        INSERT INTO cc(c1,c2) VALUES(1,'西');        INSERT INTO cc(c1,c2) VALUES(1,'安');        INSERT INTO cc(c1,c2) VALUES(1,'的');        INSERT INTO cc(c1,c2) VALUES(2,'天');        INSERT INTO cc(c1,c2) VALUES(2,'气');        INSERT INTO cc(c1,c2) VALUES(3,'好');        SELECT * FROM cc;    #(2).查询        SELECT REPLACE(GROUP_CONCAT(c2),',','') FROM cc GROUP BY c1;
阅读全文
0 0