MYSQL的基本使用【表的创建和修改、视图、函数、存储过程、触发器和事件调度器】

MySQL5.0以上的基本用法

概况：考虑初学者的方便，特写有关开源数据库MYSQL的基础使用。

附件：

 

1. 表的相关操作。

一、创建表：注意“表和主键（或外键）同时创建”和“创建表后再增加主键（或外键）”这两者的写法区别。注意：

主键》

1、表和主键同时创建

-- 主表

CREATE TABLE MASTER_TABLE (

ID  INT NOT NULL AUTO_INCREMENT,

REMARKS  VARCHAR(255) NULL ,

CONSTRAINT MASTER_TABLE_PKPRIMARY KEY (ID)

) ENGINE=INNODBAUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

2、先创建表，再创建主键

-- 主表

CREATE TABLE MASTER_TABLE (

ID  INT NOT NULL,

REMARKS  VARCHAR(255) NULL

) ENGINE=INNODBAUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

 

添加主键：

     1)ALTER TABLE MASTER_TABLE ADD CONSTRAINT MASTER_TABLE_PKPRIMARY KEY(ID);

     2)ALTER TABLE MASTER_TABLE CHANGE ID ID INT(11) NOT NULLAUTO_INCREMENT;

 

外键》

1. 表和外键同时创建

   -- 副表

   CREATE TABLE SLAVE_TABLE (

   ID  INT NOT NULL AUTO_INCREMENT,

   MASTER_TABLE_ID  INT NULL,

   REMARKS  VARCHAR(255) NULL ,

   CONSTRAINT SLAVE_TABLE_PKPRIMARY KEY (ID),

   CONSTRAINT SLAVE_TABLE_FKFOREIGN KEY (MASTER_TABLE_ID) REFERENCES MASTER_TABLE (ID)

   ) ENGINE=INNODBAUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

2. 先创建表，再创建外键

-- 副表

CREATE TABLESLAVE_TABLE (

ID  INT NOT NULL AUTO_INCREMENT,

MASTER_TABLE_ID  INT NULL,

REMARKS  VARCHAR(255) NULL ,

CONSTRAINT SLAVE_TABLE_PKPRIMARY KEY (ID)

) ENGINE=INNODBAUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

添加外键：

ALTER TABLE SLAVE_TABLE ADDCONSTRAINT SLAVE_TABLE_FK FOREIGN KEY (MASTER_TABLE_ID) REFERENCES MASTER_TABLE(ID)

 

三、删除表

  DROP TABLE TABLE_NAME;

四、编辑表：

 

1. 删除主键

（1）修改主键ID的属性：AUTO_INCREMENT 去掉：alter table TABLE_NAME CHANGE id id int(11) not null;

（2）删除主键：ALTER TABLE TABLE_NAME DROP PRIMARY KEY

1. 删除外键

   ALTER TABLE slave_tableDROP FOREIGN KEY SLAVE_TABLE_FK;

2. 删除表数据

   DELETE FROMTABLE_NAME;或TRUNCATE table TABLE_NAME;

3. 删除列和增加列

1. 删除列：ALTER TABLETABLE_NAME DROP COLUMN COLUMN_NAME(列名)

2. 增加列：ALTER TABLETABLE_NAME ADD COLUMN COLUMN COLUMN_NAME(列名)VARCHAR(100) NOT NULL

3. 修改列：

   ALTER TABLEMASTER_TABLE CHANGE ID ID INT(11) NOT NULL AUTO_INCREMENT;

 

1. 视图的使用

1. 创建视图。

   CREATE VIEWMASTER_TABLE_VIEW AS select * FROM MASTER_TABLE ;

2. 视图的调用。

   SELECT * FROM MASTER_TABLE_VIEW;

3. 思考: 视图有什么用？

    

 

1. 函数的使用

1. 函数可以传参和返回值

   CREATE FUNCTIONOUTPUT_PARAM_FUNC(INPUTPARAM VARCHAR(225))

    RETURNS varchar(225)

   BEGIN

        

         RETURN INPUTPARAM;-- 返回值

    

   END;

 

 

1. 存储过程的使用

1. 创建能执行SQL的存储过程

   CREATE  PROCEDURE EXECUTE_SQL_PROC(IN excuteSqlVARCHAR(1000))

   BEGIN

    

         SET @NEEDEXCSQL:=excuteSql;-- SQL变量赋值

    

         PREPARE stmt from @NEEDEXCSQL;-- 准备执行SQL语句

         EXECUTE stmt;-- 执行SQL语句

    

         DEALLOCATE PREPARE stmt;-- 释放资源

    

   END;

2. 参数输入存储过程：注意@的使用，它可以将值保存到内存里。

   CREATE PROCEDUREINPUT_PARAM_PROC(IN INPUTPARAM VARCHAR(1000))

   BEGIN

    

         SET @INPUTPARAM:=INPUTPARAM;-- 将传入的参数赋给内存对象

    

   END;

   调用：select @INPUTPARAM;

3. 参数输出存储过程

   CREATE PROCEDUREOUTPUT_PARAM_PROC(OUT INPUTPARAM

   VARCHAR(1000))

   BEGIN

    

         SET INPUTPARAM:=123;-- 给输出的对象赋值

    

   END;

   调用测试：CALL OUTPUT_PARAM_PROC(@WRS)

   查看返回的值【已保存在内存里面】:SELECT @WRS

4. 循环调用存储过程

   CREATE PROCEDURELOOP_DEMO_PROC(IN NB INT)

   PRC_EXITS:

      BEGIN

     

      DECLARE n INT DEFAULT 0; -- 定义变量，当不满足n<NB时，退出循环

     

      IF n<NB -- 条件判断

         THEN

         SET n := n+1;

      ELSE

         LEAVE PRC_EXITS;

      END IF;

    

   END;

 

1. 触发器

一、创建触发器

1)删除主表数据之前，触发删除副表的对应数据【主外键原因】

CREATE TRIGGERMASTER_TABLE_TRIGGER BEFORE DELETE ON MASTER_TABLE FOR EACH ROW

BEGIN

   DELETE FROM SLAVE_TABLE WHERE MASTER_TABLE_ID = MASTER_TABLE.ID

END;

二、删除触发器

DROP TRIGGERMASTER_TABLE_TRIGGER;

1. 问题？

   1、插入数据前的触发时如何实现?

   2、修改数据前的触发如何实现？

 

 

1. 事件调度器

1. 事件调度器的使用

   CREATE EVENTCUSTOMER_DEMO_EVENT

   ON SCHEDULE EVERY '30'MINUTE_SECOND STARTS NOW()

   ON COMPLETION NOT PRESERVE

   ENABLE

   DO;

二、问题？

1、如何实现10分钟执行一次？

2、如何实现1个小时执行一次？

3、如何实现每天凌晨00:30执行一次？

友情提示：本人提供相关IT技术开发和支持，与其相关技术交流。

如需请加微信号：