{"id":1568,"date":"2022-11-15T23:02:26","date_gmt":"2022-11-15T15:02:26","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1568"},"modified":"2022-11-27T12:40:58","modified_gmt":"2022-11-27T04:40:58","slug":"%e7%ac%ac15%e7%ab%a0%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e5%87%bd%e6%95%b0%e8%af%be%e5%90%8e%e7%bb%83%e4%b9%a0","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1568","title":{"rendered":"\u7b2c15\u7ae0\u5b58\u50a8\u8fc7\u7a0b\u51fd\u6570\u8bfe\u540e\u7ec3\u4e60"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code># \u7b2c15\u7ae0_\u5b58\u50a8\u8fc7\u7a0b\u4e0e\u51fd\u6570\u8bfe\u540e\u7ec3\u4e60\r\n\r\n# \u5b58\u50a8\u8fc7\u7a0b\u7ec3\u4e60\r\n\r\n#0.\u51c6\u5907\u5de5\u4f5c\r\nCREATE DATABASE test15_pro_func;\r\n\r\nUSE test15_pro_func;\r\n\r\n#1. \u521b\u5efa\u5b58\u50a8\u8fc7\u7a0binsert_user(),\u5b9e\u73b0\u4f20\u5165\u7528\u6237\u540d\u548c\u5bc6\u7801\uff0c\u63d2\u5165\u5230admin\u8868\u4e2d\r\nCREATE TABLE admin(\r\n    id INT PRIMARY KEY AUTO_INCREMENT,\r\n    user_name VARCHAR(15) NOT NULL,\r\n    pwd VARCHAR(25) NOT NULL\r\n);\r\n\r\nDELIMITER $\r\n\r\nCREATE PROCEDURE insert_user(IN user_name VARCHAR(15),IN pwd VARCHAR(25))\r\nBEGIN\r\n   INSERT INTO admin(user_name, pwd)\r\n       VALUES (user_name,pwd);\r\nEND $\r\n\r\nDELIMITER ;\r\n\r\n#\u8c03\u7528\r\n\r\nCALL insert_user('Tom','abc123');\r\n\r\nSELECT * FROM admin;\r\n\r\n#2. \u521b\u5efa\u5b58\u50a8\u8fc7\u7a0bget_phone(),\u5b9e\u73b0\u4f20\u5165\u5973\u795e\u7f16\u53f7\uff0c\u8fd4\u56de\u5973\u795e\u59d3\u540d\u548c\u5973\u795e\u7535\u8bdd\r\nCREATE TABLE beauty(\r\n    id INT PRIMARY KEY AUTO_INCREMENT,\r\n    NAME VARCHAR(15) NOT NULL,\r\n    phone VARCHAR(15) UNIQUE,\r\n    birth DATE\r\n);\r\n\r\nINSERT INTO beauty(NAME,phone,birth)\r\nVALUES\r\n    ('\u6731\u8335','13201233453','1982-02-12'),\r\n    ('\u5b59\u71d5\u59ff','13501233653','1980-12-09'),\r\n    ('\u7530\u99a5\u7504','13651238755','1983-08-21'),\r\n    ('\u9093\u7d2b\u68cb','17843283452','1991-11-12'),\r\n    ('\u5218\u82e5\u82f1','18635575464','1989-05-18'),\r\n    ('\u6768\u8d85\u8d8a','13761238755','1994-05-11');\r\n\r\nSELECT * FROM beauty;\r\n\r\nDELIMITER \/\/\r\n\r\nCREATE PROCEDURE get_phone(IN id INT,OUT NAME VARCHAR(15),OUT phone VARCHAR(15))\r\nBEGIN\r\n   SELECT b.NAME,b.phone INTO NAME,phone\r\n    FROM beauty b\r\n        WHERE b.id = id;\r\nEND \/\/\r\n\r\nDELIMITER ;\r\n\r\n#\u8c03\u7528\r\nCALL get_phone(3,@name,@phone);\r\nSELECT @name,@phone;\r\n\r\n#3. \u521b\u5efa\u5b58\u50a8\u8fc7\u7a0bdate_diff()\uff0c\u5b9e\u73b0\u4f20\u5165\u4e24\u4e2a\u5973\u795e\u751f\u65e5\uff0c\u8fd4\u56de\u65e5\u671f\u95f4\u9694\u5927\u5c0f\r\nDELIMITER \/\/\r\n\r\nCREATE PROCEDURE date_diff(IN birth1 DATE,IN birth2 DATE,OUT sum_date INT)\r\nBEGIN\r\n    SELECT DATEDIFF(birth1,birth2) INTO sum_date;\r\nEND \/\/\r\n\r\nDELIMITER ;\r\n\r\n#\u8c03\u7528\r\n\r\nSET @birth1 = '1992-10-30';\r\nSET @birth2 = '1992-09-08';\r\nCALL date_diff(@birth1,@birth2,@sum_date);\r\n\r\nSELECT @sum_date;\r\n\r\n#4. \u521b\u5efa\u5b58\u50a8\u8fc7\u7a0bformat_date(),\u5b9e\u73b0\u4f20\u5165\u4e00\u4e2a\u65e5\u671f\uff0c\u683c\u5f0f\u5316\u6210xx\u5e74xx\u6708xx\u65e5\u5e76\u8fd4\u56de\r\nDELIMITER \/\/\r\n\r\nCREATE PROCEDURE format_date(IN my_date DATE,OUT str_date VARCHAR(25))\r\nBEGIN\r\n    SELECT DATE_FORMAT(my_date,'%Y\u5e74%m\u6708%d\u65e5') INTO str_date;\r\nEND \/\/\r\n\r\nDELIMITER ;\r\n\r\n#\u8c03\u7528\r\nCALL format_date(CURDATE(),@str);\r\nSELECT @str;\r\n\r\n#5. \u521b\u5efa\u5b58\u50a8\u8fc7\u7a0bbeauty_limit()\uff0c\u6839\u636e\u4f20\u5165\u7684\u8d77\u59cb\u7d22\u5f15\u548c\u6761\u76ee\u6570\uff0c\u67e5\u8be2\u5973\u795e\u8868\u7684\u8bb0\u5f55\r\nDELIMITER \/\/\r\n\r\nCREATE PROCEDURE beauty_limit(IN start_index INT,IN size INT)\r\nBEGIN\r\n   SELECT * FROM beauty LIMIT start_index,size;\r\nEND \/\/\r\n\r\nDELIMITER ;\r\n\r\nCALL beauty_limit(1,3);\r\n\r\n#\u521b\u5efa\u5e26inout\u6a21\u5f0f\u53c2\u6570\u7684\u5b58\u50a8\u8fc7\u7a0b\r\n#6. \u4f20\u5165a\u548cb\u4e24\u4e2a\u503c\uff0c\u6700\u7ec8a\u548cb\u90fd\u7ffb\u500d\u5e76\u8fd4\u56de\r\nDELIMITER \/\/\r\n\r\nCREATE PROCEDURE add_double(INOUT a INT,INOUT b INT)\r\nBEGIN\r\n   SET a = a * 2;\r\n   SET b = b * 2;\r\nEND \/\/\r\n\r\nDELIMITER ;\r\n\r\n#\u8c03\u7528\r\nSET @a = 3,@b = 5;\r\nCALL add_double(@a,@b);\r\n\r\nSELECT @a,@b;\r\n\r\n#7. \u5220\u9664\u9898\u76ee5\u7684\u5b58\u50a8\u8fc7\u7a0b\r\nDROP PROCEDURE IF EXISTS beauty_limit;\r\n\r\n#8. \u67e5\u770b\u9898\u76ee6\u4e2d\u5b58\u50a8\u8fc7\u7a0b\u7684\u4fe1\u606f\r\nSHOW CREATE PROCEDURE add_double;\r\n\r\nSHOW PROCEDURE STATUS LIKE 'add_%';\r\n\r\n# \u5b58\u50a8\u51fd\u6570\u7ec3\u4e60\r\n\r\n#0. \u51c6\u5907\u5de5\u4f5c\r\nUSE test15_pro_func;\r\n\r\nCREATE TABLE employees\r\nAS\r\nSELECT * FROM atguigudb.`employees`;\r\n\r\nCREATE TABLE departments\r\nAS\r\nSELECT * FROM atguigudb.`departments`;\r\n\r\nSET GLOBAL log_bin_trust_function_creators = 1;\r\n\r\n#\u65e0\u53c2\u6709\u8fd4\u56de\r\n#1. \u521b\u5efa\u51fd\u6570get_count(),\u8fd4\u56de\u516c\u53f8\u7684\u5458\u5de5\u4e2a\u6570\r\nDELIMITER $\r\n\r\nCREATE FUNCTION get_count()\r\nRETURNS INT\r\n\r\nBEGIN\r\n   RETURN(SELECT COUNT(*) FROM employees);\r\nEND $\r\n\r\nDELIMITER ;\r\n\r\n#\u8c03\u7528\r\nSELECT get_count();\r\n\r\n#\u6709\u53c2\u6709\u8fd4\u56de\r\n#2. \u521b\u5efa\u51fd\u6570ename_salary(),\u6839\u636e\u5458\u5de5\u59d3\u540d\uff0c\u8fd4\u56de\u5b83\u7684\u5de5\u8d44\r\nDELIMITER $\r\n\r\nCREATE FUNCTION ename_salary(emp_name VARCHAR(15))\r\nRETURNS DOUBLE\r\n\r\nBEGIN\r\n   RETURN(SELECT salary FROM employees WHERE last_name = emp_name);\r\nEND $\r\n\r\nDELIMITER ;\r\n\r\n#\u8c03\u7528\r\nSELECT ename_salary('Abel');\r\n\r\n#3. \u521b\u5efa\u51fd\u6570dept_sal() ,\u6839\u636e\u90e8\u95e8\u540d\uff0c\u8fd4\u56de\u8be5\u90e8\u95e8\u7684\u5e73\u5747\u5de5\u8d44\r\nDELIMITER \/\/\r\n\r\nCREATE FUNCTION dept_sal(dept_name VARCHAR(15))\r\nRETURNS DOUBLE\r\n\r\nBEGIN\r\n   RETURN(SELECT AVG(salary) FROM employees e JOIN departments d\r\n       ON e.department_id = d.department_id\r\n   WHERE d.department_name = dept_name);\r\nEND \/\/\r\n\r\nDELIMITER ;\r\n\r\n#\u8c03\u7528\r\nSELECT * FROM departments;\r\n\r\nSELECT dept_sal('Marketing');\r\n\r\n#4. \u521b\u5efa\u51fd\u6570add_float()\uff0c\u5b9e\u73b0\u4f20\u5165\u4e24\u4e2afloat\uff0c\u8fd4\u56de\u4e8c\u8005\u4e4b\u548c\r\n\r\nDELIMITER \/\/\r\n\r\nCREATE FUNCTION add_float(value1 FLOAT,value2 FLOAT)\r\nRETURNS FLOAT\r\n\r\nBEGIN\r\n   RETURN(SELECT value1 + value2);\r\nEND \/\/\r\n\r\nDELIMITER ;\r\n\r\n#\u8c03\u7528\r\nSET @v1 := 12.2;\r\nSET @v2 = 2.3;\r\nSELECT add_float(@v1,@v2);\r\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[47],"class_list":["post-1568","post","type-post","status-publish","format-standard","hentry","category-mysql-code","tag-mysql"],"_links":{"self":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1568"}],"collection":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1568"}],"version-history":[{"count":1,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1568\/revisions"}],"predecessor-version":[{"id":1569,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1568\/revisions\/1569"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1568"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1568"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1568"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}