1.拼音缩写函数
DELIMITER $$
USE `mpos`$$
CREATE
FUNCTION `mysql`.`pysx`( wz VARCHAR(20))
RETURNS VARCHAR(20) CHARSET utf8
BEGIN
SET @i=1;
SET @mcsx='';
SET @len=CHAR_LENGTH(wz);
WHILE(@i<=@len) DO
SET @hz =SUBSTR(wz,@i,1);
SELECT jp INTO @jp FROM hzpyb WHERE hz=@hz;
SET @mcsx=CONCAT(@mcsx,@jp);
SET @i=@i+1;
END WHILE;
RETURN @mcsx;
END$$
DELIMITER ;
2.为hpxxb创建插入与修改触发器，以维护mcsx列 参考代码如下：
DELIMITER $$
USE `mpos`$$
CREATE
TRIGGER `mysql`.`update_mcxs_before_insert_hpxxb` BEFORE INSERT
ON `mysql`.`hpxxb`
FOR EACH ROW BEGIN
SET new.mcsx=pysx(new.hpmc);
END$$
DELIMITER ;
3、创建hpxxbcp存储过程，完成hpxxb的增、删、改操作，参考代码如下：
DELIMITER $$
USE `mpos`$$
CREATE
PROCEDURE `mysql`.`hpxxbcp`(
hpid INT,
hpmc VARCHAR(20),
hptm VARCHAR(20),
jldw VARCHAR(20),
hpzt TINYINT,
dj DECIMAL(18,3),
cxj DECIMAL(18,3)
)
BEGIN
/*增加货品*/
IF hpid=0 THEN
INSERT INTO hpxxb(hpmc,hptm,jldw,dj,cxj,mcsx) VALUES(hpmc,hptm,jldw,dj,cxj,pysx（hpmc));
SET @hpid=@@identity;
INSERT INTO hpkcb(hpid,kcsl,zrkc,yckc) VALUES(@hpid,0,0,0);
/* 修改*/
ELSEIF hpid>0 THEN
UPDATE hpxxb SET
hpxxb.hpmc=hpmc,
hpxxb.hptm=hptm,
hpxxb.jldw=jldw,
hpxxb.dj=dj,
hpxxb.cxj=cxj,
hpxxb.mcsx=pysx(hpmc),
hpxxb.hpzt=hpzt WHERE hpxxb.hpid=hpid;
ELSE
DELETE FROM hpkcb WHERE hpkcb.hpid=hpid;
DELETE FROM hpxxb WHERE hpxxb.hpid=hpid;
END IF;
END$$
DELIMITER ;
4.创建hpxxbcx存储过程，完成hpxxb的查询操作，参考代码如下：
DELIMITER $$
CREATE
PROCEDURE `mysql`.`hpxxbcx`( IN hpid INT)
BEGIN
IF hpid=0 THEN
SELECT * FROM hpxxb;
ELSE
SELECT * FROM hpxxb WHERE hpxxb.hpid=hpid;
END IF;
END$$
DELIMITER ;
5.创建存储过程，要求当一个员工的工作时间大于6年时将其转到经理办公室工作。，
DELIMITER $$
CREATE PROCEDURE zyk()
BEGIN
DECLARE flag BOOLEAN DEFAULT TRUE;
DECLARE mingzi CHAR(10);
DECLARE shijian DATE;
DECLARE xm CURSOR FOR SELECT ename, hiredate FROM emop2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=FALSE;
OPEN xm;
WHILE flag DO
FETCH xm INTO mingzi,shijian;
IF YEAR(NOW()) - YEAR(shijian)>6 THEN
UPDATE emp2 SET job='manger' WHERE ename=mingzi;
END IF;
END WHILE;
DELIMITER ;
6.拼音缩写函数
DELIMITER $$
USE `mypos`$$
DROP FUNCTION IF EXISTS `hzzpy`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `hzzpy`(jx VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
DECLARE danzi VARCHAR(50);
DECLARE danpin VARCHAR(50);
DECLARE cd INT;
DECLARE newpy VARCHAR(50);
DECLARE i INT ;
SET cd = CHAR_LENGTH(jx);
SET newpy='';
SET i=0;
WHILE(i<cd) DO
SET danzi=SUBSTR(jx,1,1);
SELECT jp INTO danpin FROM hanzipinyin WHERE hz=danzi;
SET newpy=CONCAT(newpy,danpin);
SET jx=SUBSTR(jx,2);
SET i=i+1;
END WHILE;
RETURN newpy;
END$$
DELIMITER ;
(3)创建存储过程，使用游标确定一个员工的实际收入是否排在前3名。
结果为TRUE表示是，结果为FALSE表示否。
DELIMITER $$
CREATE PROCEDURE
TOP_ THREE (IN EM_ID CHAR(6), OUT OK BOOLEAN)
BEGIN
DECLARE X_EM_ID CHAR(6);
DECLARE ACT_IN,SEQ INTEGER;
DECLARE FOUND BOOLEAN;
DECLARE SALARY_DIS CURSOR FOR /*声明游标*/
SELECT EmployeeID, InCome-OutCome
FROM Salary ORDER BY 2 DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND /*处理程序*/
SET FOUND=FALSE;
SET SEQ=0;
SET FOUND=TRUE;
SET OK=FALSE;
OPEN SALARY_DIS;
FETCH SALARY_DIS INTO X_EM_ID, ACT_IN; /*读取第一行数据*/
WHILE FOUND AND SEQ<3 AND OK=FALSE DO /*比较前三行数据*/
SET SEQ=SEQ+1;
IF X_EM_ID=EM_ID THEN
SET OK=TRUE;
END IF;
FETCH SALARY_DIS INTO X_EM_ID, ACT_IN;
END WHILE;
CLOSE SALARY_DIS;
END $$
DELIMITER;
2请使用select into...outfi1e语句，备份数据库db_test中表content的全部数据到C盘的backup目录下一个名为 backupcontent. txt的文件中。
select *from content into outfile 'C:/backup/backupcontent.txt';
1请查询出send表中日期（sendtime字段)为当天的所有记录的 sql语句，其中字段sendtime为datetime型。
select *from sendwhere datediff(dd, sendtime, getdate())=0;
2请用create index语句在数据库db_test—的表content中，根据留言标题列的前三个字符采用默认的索引类型创建一个升序索引 index_subject。
create index index_subject on content(subject(3)ASC)
3)写代码返回所有学生的信息。
4)写代码返回所有姓名带J字母的学生信息。
5)写代码返回所有郑州籍贯的学生信息。
6)写代码返回所有学费低于平均学费的学生信息。提示使用嵌套的select查询。
(3)SELECT s_id, s_name, s_fenshu,s_hometown, s_tuition FROM student_web;
(4)SELECT s_id,s_name,s_fenshu,s_hometown,s_tuition FROM student_web WHERE s_name LIKE“%J%";
(5) SELECT s_id,s_name,s_fenshu, s_hometown,s_tuition FROM student_web WHERE s_hometown LIKE“郑州%”;
(6)SELECT s_id, s_name, s_fenshuy, s_hometown,s_tuition FROM student_web WHERE s_tuition〈(SELECT AWG(s_tuition) FROM student_web);
3)查询总分大于180分的所有同学的学号考生
(3)SELECT 学号 FROM score GROUP BY 学号 HAVING SUM(分数)>180;
1)查询存在年龄大于21 岁的员工所对应的部门信息。
2)采用自连接查询方式查询与王红在同一个部门的员工。考生答案:
se1ect * from dept where exists( select did from employee where age>21);
select p1.* from employee p1 join employee p2 on pl.did =p2.did where p2.name='王红';
(3)SELECT学号FROMscore GROUP BY学号 HAVING SUM(分数)>180;
