mysql操作总结之插入、新建、修改、删除、视图，存储（函数）触发器

86 阅读 0 评论 57 点赞

我是靠谱客的博主笑点低往事，最近开发中收集的这篇文章主要介绍mysql操作总结之插入、新建、修改、删除、视图，存储（函数）触发器，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一，插入

1.不指定列名的整行插入
INSERT INTO .texttable VALUES (‘4’, ‘value2’);
2，指定列名的插入
INSERT INTO texttable (id) VALUES (‘5’);
3、插入多行
INSERT INTO test1.texttable (id) VALUES (‘6’);
INSERT INTO test1.texttable (id, text) VALUES (‘7’, ‘value2’);
4、将检索到的数据也能插入

二、更新数据

1.更新单独列
UPDATE test1.texttable SET text = ‘edfg’ WHERE (id = ‘3’);
2更新多列
UPDATE test1.texttable SET id = ‘9’, text = ‘asdggf’ WHERE (id = ‘5’);

三、删除行

DELETE FROM test1.texttable WHERE (id = ‘9’);

四、创建表

CREATE TABLE test1.tablebane (
id INT NOT NULL,
name VARCHAR(45) NULL,
PRIMARY KEY (id));

1、默认值 DEFAULT
ALTER TABLE test1.texttable
CHANGE COLUMN id id INT(11) NOT NULL DEFAULT 1 ;
2、引擎类型 ENGINE
CREATE TABLE test1.tablebane (
id INT NOT NULL,
name VARCHAR(45) NULL,
PRIMARY KEY (id)) ENGINE=InnoDB；
常见的类型有：InnoDB（不支持全文本搜索）、MSMORY MyISAM
3、主键
每个表都要有一个主键列
4、自增长
创建自增长表
create table TableName(
num int primary key auto_increment,
book_name char(30)
)
auto_increment=5 //设置从5开始，即第一个数5，后面依次6，7
;
");
5、更新表
alter table TableName modify num int;//删除自动编号
alter table TableName modify num int primary key auto_increment;//增加自动编号

增加新列
alter table tablename add name char(29);
增加外键
alter table 外键所在的表名 add foreign key(外键所在的字段) references 对应主键所在表名(主键所在列表);
重命名表
RENAME TABLE name1 TO name2;
删除表
DROP TABLE tablename；

五、视图

视图可以理解为类似于表
表的一个临时变量
可以将复杂的查找结果存储于一个视图中，
后面对查询结果的使用可以直接访问对应的视图
对表的操作基本都可以应用于对视图的操作
不过一般不对视图做插入INSERT、更新UPDATE和删除DELETE操作
1、创建视图
CREATE VIEW viewname AS
SELECT rollname2 FROM tablename;
2、查看视图
select * from viewname;

六、存储（函数）

mysql的存储即类似编程的函数功能
对常用的语句进行封装，有名称、实参、形参
1、创建举例
CREATE DEFINER=root@% PROCEDURE procedure2(in inID int ,out outresult char(45))
BEGIN
select deptname into outresult from test1.tbl_dept where id =inID;
END
2、调用和显示结果
call procedure2 (2,@result);
select @result;
存储过程可以加入：条件语句. if-then-else 语句
case语句：
. 循环语句

while ···· end while
->while var<6 do
-> insert into t values(var);
-> set var=var+1;
-> end while;
2 repeat···· end repea
它在执行操作后检查结果，而 while 则是执行前进行检查。
-> repeat
-> insert into t values(v);
-> set v=v+1;
-> until v>=5
-> end repeat;
3 loop ·····endloop
loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。
-> set v=0;
-> LOOP_LABLE:loop
-> insert into t values(v);
-> set v=v+1;
-> if v >=5 then
-> leave LOOP_LABLE;
-> end if;
-> end loop;
七、游标（数组，列表）
mysql中的游标类似一个存储列表
游标只能在存储中定义和使用
1.定义游标：declare 游标名 cursor for select语句;
2.打开游标：open 游标名;
3获取结果：fetch 游标名 into 变量名[,变量名];
4 关闭游标:close 游标名;
eg：

 create procedure p3()
begin
    declare id int;
    declare name varchar(15);
    declare flag int default 0;
    -- 声明游标
    declare mc cursor for select * from class;
    --设置loop终止条件 not fund时 把flag置位1
    declare continue handler for not found set flag = 1;
    -- 打开游标
    open mc;
    -- 获取结果
    l2:loop 
    
    fetch mc into id,name;
    if flag=1 then -- 当无法fetch会触发handler continue
        leave l2;
    end if;
    -- 这里是为了显示获取结果
    insert into class2 values(id,name);
    -- 关闭游标
    end loop;
    close mc;
    
end;

call p3();
select * from class2;

七、触发器

触发器类似于c#中的委托，事件
1、插入触发器
CREATE TRIGGER 触发器名 AFTER INSERT ON 监控的表名 FOR EACH ROW
begin
触发需要做的具体事情
end
2、update触发器
CREATE TRIGGER 触发器名 AFTER UPDATE ON 监控的表名 FOR EACH ROW
begin
触发需要做的具体事情
end
3、delere 触发器
CREATE TRIGGER 触发器名 AFTER DELETE ON 监控的表名 FOR EACH ROW
begin
触发需要做的具体事情
end
eg：

**创建表user**
use test1;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(20) not null AUTO_INCREMENT,
  `account` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;
**创建表user_history**
DROP TABLE IF EXISTS `user_history`;
CREATE TABLE `user_history` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `operatetype` varchar(200) NOT NULL,
  `operatetime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
**创建插入事件触发器**
DROP TRIGGER IF EXISTS `tri_insert_user`;
DELIMITER ;;
CREATE TRIGGER `tri_insert_user` AFTER INSERT ON `user` FOR EACH ROW begin
    INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (new.id, 'add a user',  now());
end
;;
DELIMITER ;
**创建update事件触发器**
DROP TRIGGER IF EXISTS `tri_update_user`;
DELIMITER ;;
CREATE TRIGGER `tri_update_user` AFTER UPDATE ON `user` FOR EACH ROW begin
    INSERT INTO user_history(user_id,operatetype, operatetime) VALUES (new.id, 'update a user', now());
end
;;
DELIMITER ;
**创建删除事件触发器**
DROP TRIGGER IF EXISTS `tri_delete_user`;
DELIMITER ;;
CREATE TRIGGER `tri_delete_user` AFTER DELETE ON `user` FOR EACH ROW begin
    INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (old.id, 'delete a user', now());
end
;;
DELIMITER ;
**测试效果**

INSERT INTO user(account, name, address) VALUES ('user1', 'user1', 'user1');
INSERT INTO user(account, name, address) VALUES ('user2', 'user2', 'user2');


UPDATE test1.user SET name = 'user3', account = 'user3' where name='user2';

DELETE FROM `user` where name = 'user2';