一：函数
1：创建数据库和表deptartment，
mysql> use DBSC; Database changed mysql> create table deptartment(dept_name varchar(20), -> budget bigint(20), -> building varchar(20)); Query OK, 0 rows affected mysql> insert into deptartment values('电子系',10000,'2号楼'); Query OK, 1 row affected mysql> insert into deptartment values('通信系',40000,'3号楼'); Query OK, 1 row affected mysql> insert into deptartment values('计算机系',100000,'6号楼');
2:创建表 instructor
create table instructor(id int,
salary int,
dept_name varchar(20),
foreign key(dept_name) references deptartment(dept_name)) ENGINE=innodb DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
提示错误：1005 - Can't create table 'dbsc.instructor' (errno: 150)
修改数据表deptartment 的执行引擎：
alter table deptartment engine=innodb;
依然提示错误，因为外键约束的字段必须为被应用的表的主键。 修改 deptartment 中dept_name的定义。
alter table deptartment modify dept_name varchar(20) primary key;
3：向表instructor中添加数据。
mysql> insert into instructor values(1,1000,'电子系'); Query OK, 1 row affected mysql> insert into instructor values(2,1000,'电子系'); Query OK, 1 row affected mysql> insert into instructor values(3,1000,'电子系'); Query OK, 1 row affected mysql> insert into instructor values(4,1000,'电子系'); Query OK, 1 row affected mysql> insert into instructor values(5,1000,'电子系'); Query OK, 1 row affected mysql> insert into instructor values(1,1000,'通信系'); Query OK, 1 row affected mysql> insert into instructor values(2,1000,'通信系'); Query OK, 1 row affected mysql> insert into instructor values(3,1000,'通信系'); Query OK, 1 row affected mysql> insert into instructor values(1,1000,'计算机系'); Query OK, 1 row affected
4：创建函数
创建函数提示错误： https://blog.csdn.net/topasstem8/article/details/8216740/
1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
修改
mysql> set global log_bin_trust_function_creators=TRUE;
创建有参函数： 数据库默认语句分隔符为;，DELIMITER // 将数据库语句执行分隔符改为//。
DELIMITER // create function dept_count(deptName varchar(20)) returns integer begin return( select count(*) from instructor where instructor.dept_name = deptName); end// DELIMITER ;
5：使用函数， 求出instructor中院系教师大于2的deptartment
mysql> select * from deptartment where dept_count(dept_name)>2; +-----------+--------+----------+ | dept_name | budget | building | +-----------+--------+----------+ | 电子系 | 10000 | 2号楼 | | 通信系 | 40000 | 3号楼 | +-----------+--------+----------+ 2 rows in set
https://www.cnblogs.com/taiguyiba/p/6619027.html
6： 在函数中定义变量，返回赋值后的变量。
mysql> DELIMITER // create function dept_count(deptName varchar(20)) returns integer begin declare d_count integer ; select count(*) into d_count from instructor where instructor.dept_name = deptName; return d_count; end// DELIMITER ; Query OK, 0 rows affected mysql> select dept_count('电子系'); +----------------------+ | dept_count('电子系') | +----------------------+ | 5 | +----------------------+ 1 row in set
7：创建无参函数
mysql> CREATE FUNCTION simpleFun()RETURNS VARCHAR(20) RETURN "电子系"; 1304 - FUNCTION simpleFun already exists mysql> select * from deptartment where dept_name=simpleFun(); +-----------+--------+----------+ | dept_name | budget | building | +-----------+--------+----------+ | 电子系 | 10000 | 2号楼 | +-----------+--------+----------+ 1 row in set
二：过程：使用一中的表 https://www.cnblogs.com/oskyhg/p/7679962.html
1：创建无参数的过程，查询的值直接返回。
drop procedure if exists pro1;
mysql> create procedure pro1() select 5 -> ; Query OK, 0 rows affected mysql> call pro1(); +---+ | 5 | +---+ | 5 | +---+ 1 row in set Query OK, 0 rows affected mysql>
mysql> drop procedure if exists pro1;
mysql> create procedure pro1() select * from instructor; Query OK, 0 rows affected mysql> call pro1(); +----+--------+-----------+ | id | salary | dept_name | +----+--------+-----------+ | 1 | 1000 | 电子系 | | 2 | 1000 | 电子系 | | 3 | 1000 | 电子系 | | 4 | 1000 | 电子系 | | 5 | 1000 | 电子系 | | 1 | 1000 | 通信系 | | 2 | 1000 | 通信系 | | 3 | 1000 | 通信系 | | 1 | 1000 | 计算机系 | +----+--------+-----------+ 9 rows in set Query OK, 0 rows affected mysql>
2：创建有输入输出参数的过程。 mysql中的变量 https://blog.csdn.net/qq_34531925/article/details/79483312
mysql> -- 定义执行语句的分割符，遇到//就执行。 DELIMITER // drop procedure if exists pro3 // -- in输入参数和类型，out输出参数和类型 create procedure pro3(in parm1 int, out parm2 int) begin -- 定义自定义变量，局部变量 declare parm3 int; -- 对输入参数值进行判断 if parm1=10 then set parm3=parm1; -- 对parm3进行赋值 else set parm3=20; end if; -- 插入操作，自定义变量的值做为插入值 insert into instructor(id) values(parm3); -- 查询结果赋值给输出参数 select count(*) into parm2 from instructor; end // -- 调用过程，将输出值，赋值给outValue变量 call pro3(10,@outValue) // -- 查询输出值，@outValue 是用户变量 select @outValue // Query OK, 0 rows affected Query OK, 0 rows affected Query OK, 1 row affected +-----------+ | @outValue | +-----------+ | 12 | +-----------+ 1 row in set mysql>
3：当没有输出参数时，会将过程中最后的select查询结果作为过程的结果
mysql> create procedure pro1(name varchar(20)) select * from instructor where dept_name=name;// Query OK, 0 rows affected mysql> call pro1('电子系');// +----+--------+-----------+ | id | salary | dept_name | +----+--------+-----------+ | 1 | 1000 | 电子系 | | 2 | 1000 | 电子系 | | 3 | 1000 | 电子系 | | 4 | 1000 | 电子系 | | 5 | 1000 | 电子系 | +----+--------+-----------+ 5 rows in set Query OK, 0 rows affected
三：函数和过程 支持for while语句。
创建工资表
mysql> create table salarie(name varchar(20),salary int(11)); mysql> insert into salarie values('zhangsan',2000); mysql> insert into salarie values('lisi',2500); mysql> insert into salarie values('wangwu',3000);
1：使用过程中的while语句向sql表中添加数据
mysql> delimiter // drop procedure if exists salary // create procedure salary() begin declare i int default 0; while i<10 do insert into salarie values('wangwu',100); set i=i+1; end while; end // Query OK, 0 rows affected Query OK, 0 rows affected mysql> call salary();// Query OK, 1 row affected mysql> select * from salarie; // +----------+--------+ | name | salary | +----------+--------+ | zhangsan | 2000 | | lisi | 2500 | | wangwu | 3000 | | wangwu | 100 | | wangwu | 100 | | wangwu | 100 | | wangwu | 100 | | wangwu | 100 | | wangwu | 100 | | wangwu | 100 | | wangwu | 100 | | wangwu | 100 | | wangwu | 100 | +----------+--------+ 13 rows in set
2:使用repeat语句向数据库中添加数据
delimiter // drop procedure if exists salary // create procedure salary() begin declare i int default 0; repeat insert into salarie values('lisi',100); set i=i+1; until i>5 end repeat; end //
3: 使用loop循环插入。 read_loop为起的loop名字，可以为任意名。
delimiter // drop procedure if exists StatisticStore; // CREATE PROCEDURE StatisticStore() BEGIN declare i int default 0; read_loop:loop if i>6 then leave read_loop; end if; insert into salarie values('zhangsan',300); set i=i+1; end loop; END; //
四：使用游标，对查询出来的记录进行迭代操作。 游标只能在存储过程中使用。使用三中的表 https://blog.csdn.net/liguo9860/article/details/50848216
向表中添加一个自增的字段，用于区分各个记录。
alter table `salarie` add `id` int AUTO_INCREMENT UNIQUE;//
mysql> select * from salarie; +----------+--------+----+ | name | salary | id | +----------+--------+----+ | zhangsan | 2000 | 1 | | lisi | 2500 | 2 | | wangwu | 3000 | 3 | | wangwu | 100 | 4 | | wangwu | 100 | 5 | | wangwu | 100 | 6 | | wangwu | 100 | 7 | | wangwu | 100 | 8 | | wangwu | 100 | 9 | | wangwu | 100 | 10 | | wangwu | 100 | 11 | | wangwu | 100 | 12 | | wangwu | 100 | 13 | | lisi | 100 | 14 | | lisi | 100 | 15 | | lisi | 100 | 16 | | lisi | 100 | 17 | | lisi | 100 | 18 | | lisi | 100 | 19 | | zhangsan | 300 | 20 | | zhangsan | 300 | 21 | | zhangsan | 300 | 22 | | zhangsan | 300 | 23 | | zhangsan | 300 | 24 | | zhangsan | 300 | 25 | | zhangsan | 300 | 26 | +----------+--------+----+ 26 rows in set
向工资低于2700的员工每人加一百块钱工资的工程如下，并将工资低于2700的员工的总额统计出来。
mysql> delimiter // drop procedure if exists addMoney; // CREATE PROCEDURE addMoney() BEGIN
-- 定义的游标变量，用于接收查询出来的记录 declare oldSalary int; declare nid int; declare total int default 0; declare done int default false; declare cur cursor for select salary,id from salarie where salary<2700;
-- 定义的标记符done, 直到最后将游标中的数据全部取出，设置done为true declare continue HANDLER for not found set done = true; set total = 0; open cur; read_loop:loop fetch cur into oldSalary,nid; if done then leave read_loop; end if; update salarie set salary=salary+100 where id=nid; set total = total + oldSalary; end loop; close cur; select total; END; // Query OK, 0 rows affected Query OK, 0 rows affected mysql> call addMoney;// +-------+ | total | +-------+ | 8200 | +-------+ 1 row in set Query OK, 0 rows affected mysql> select * from salarie; -> // +----------+--------+----+ | name | salary | id | +----------+--------+----+ | zhangsan | 2100 | 1 | | lisi | 2600 | 2 | | wangwu | 3000 | 3 | | wangwu | 200 | 4 | | wangwu | 200 | 5 | | wangwu | 200 | 6 | | wangwu | 200 | 7 | | wangwu | 200 | 8 | | wangwu | 200 | 9 | | wangwu | 200 | 10 | | wangwu | 200 | 11 | | wangwu | 200 | 12 | | wangwu | 200 | 13 | | lisi | 200 | 14 | | lisi | 200 | 15 | | lisi | 200 | 16 | | lisi | 200 | 17 | | lisi | 200 | 18 | | lisi | 200 | 19 | | zhangsan | 400 | 20 | | zhangsan | 400 | 21 | | zhangsan | 400 | 22 | | zhangsan | 400 | 23 | | zhangsan | 400 | 24 | | zhangsan | 400 | 25 | | zhangsan | 400 | 26 | +----------+--------+----+ 26 rows in set
五：触发器
FOR EACH ROW，可以迭代取出每一行中的列的 数据。
创建触发器，并使用。 创建插入触发器，当新加入的薪水大于1000时，减去200。
mysql> drop trigger if exists BeforeInsert; // CREATE TRIGGER BeforeInsert BEFORE insert ON salarie FOR EACH ROW BEGIN IF new.salary> 1000 THEN SET new.salary = new.salary-200; END IF; END; // Query OK, 0 rows affected Query OK, 0 rows affected mysql> insert into salarie(name,salary) values('zhaoliu',1500);// Query OK, 1 row affected mysql> select * from salarie;// +----------+--------+----+ | name | salary | id | +----------+--------+----+ | zhangsan | 2100 | 1 | | lisi | 2600 | 2 | | wangwu | 3000 | 3 | | wangwu | 200 | 4 | | wangwu | 200 | 5 | | wangwu | 200 | 6 | | wangwu | 200 | 7 | | wangwu | 200 | 8 | | wangwu | 200 | 9 | | wangwu | 200 | 10 | | wangwu | 200 | 11 | | wangwu | 200 | 12 | | wangwu | 200 | 13 | | lisi | 200 | 14 | | lisi | 200 | 15 | | lisi | 200 | 16 | | lisi | 200 | 17 | | lisi | 200 | 18 | | lisi | 200 | 19 | | zhangsan | 400 | 20 | | zhangsan | 400 | 21 | | zhangsan | 400 | 22 | | zhangsan | 400 | 23 | | zhangsan | 400 | 24 | | zhangsan | 400 | 25 | | zhangsan | 400 | 26 | | zhaoliu | 1300 | 28 | +----------+--------+----+ 27 rows in set
创建更新触发器，new 代表更新的数据，准备插入的，old代表原来的数据。
当给一个员工改变薪水时，如果改变的薪水大于4000，那么还按原来的薪水。
mysql> drop trigger if exists BeforeUpdate; // CREATE TRIGGER BeforeUpdate BEFORE update ON salarie FOR EACH ROW BEGIN IF new.salary> 4000 THEN SET new.salary = old.salary; END IF; END; // Query OK, 0 rows affected Query OK, 0 rows affected mysql> update salarie set salary=7000 where id=28;// Query OK, 0 rows affected Rows matched: 1 Changed: 0 Warnings: 0 mysql> select * from salarie;// +----------+--------+----+ | name | salary | id | +----------+--------+----+ | zhangsan | 2100 | 1 | | lisi | 2600 | 2 | | wangwu | 3000 | 3 | | wangwu | 200 | 4 | | wangwu | 200 | 5 | | wangwu | 200 | 6 | | wangwu | 200 | 7 | | wangwu | 200 | 8 | | wangwu | 200 | 9 | | wangwu | 200 | 10 | | wangwu | 200 | 11 | | wangwu | 200 | 12 | | wangwu | 200 | 13 | | lisi | 200 | 14 | | lisi | 200 | 15 | | lisi | 200 | 16 | | lisi | 200 | 17 | | lisi | 200 | 18 | | lisi | 200 | 19 | | zhangsan | 400 | 20 | | zhangsan | 400 | 21 | | zhangsan | 400 | 22 | | zhangsan | 400 | 23 | | zhangsan | 400 | 24 | | zhangsan | 400 | 25 | | zhangsan | 400 | 26 | | zhaoliu | 1300 | 28 | +----------+--------+----+ 27 rows in set
mysql中不用给新的数据或旧的数据起别名，默认为new，old
其它数据库的更新或插入：
CREATE TRIGGER TestField1_BeforeInsert BEFORE INSERT ON salarie -- 新插入的行或更新的行的别名为nrow, 相当于mysql中的new 。 例如：update salarie set salary=7000 where id=28;// referencing new row as nrow -- 数据库中原来的旧数据别名为orow，相当于mysql中的old 。例如：| zhaoliu | 1300 | 28 | referencing old row as orow FOR EACH ROW BEGIN IF nrow.salary> 1000 THEN SET nrow.salary = orow.salary-200; END IF; END;
六：递归mysql不支持with recursive 递归查询语句，只能自己写循环语句迭代 https://blog.csdn.net/wickedvalley/article/details/78925041
七：sql与shell脚本的相互调用。
window上面sql与c++的相互调用。 https://blog.csdn.net/swotcoder/article/details/18524
https://blog.csdn.net/shaoyiwenet/article/details/53256103
触发器调用shell文件，shell读取mysql中最新插入的一行，读取邮箱用户名，然后发送邮件给客户。
触发器中执行shll命令， https://zhidao.baidu.com/question/2271230050936210028.html
八： 循环中使用union
delimiter // drop procedure if exists tile;// drop table if exists tempTable;// create temporary table tempTable as select * from salarie where id<3;// create procedure tile(n int) -- set @tb:=table(id int(11),salary int(11), name varchar(20)); begin while(n>0) do set n=n-1; select * from ((select * from tempTable) union (select * from salarie where id<(10+n)))t3; end while; -- select * from tempTable; end// delimiter;
mysql> call tile(3); +----------+--------+----+ | name | salary | id | +----------+--------+----+ | zhangsan | 2100 | 1 | | lisi | 2600 | 2 | | wangwu | 3000 | 3 | | wangwu | 200 | 4 | | wangwu | 200 | 5 | | wangwu | 200 | 6 | | wangwu | 200 | 7 | | wangwu | 200 | 8 | | wangwu | 200 | 9 | | wangwu | 200 | 10 | | wangwu | 200 | 11 | +----------+--------+----+ 11 rows in set +----------+--------+----+ | name | salary | id | +----------+--------+----+ | zhangsan | 2100 | 1 | | lisi | 2600 | 2 | | wangwu | 3000 | 3 | | wangwu | 200 | 4 | | wangwu | 200 | 5 | | wangwu | 200 | 6 | | wangwu | 200 | 7 | | wangwu | 200 | 8 | | wangwu | 200 | 9 | | wangwu | 200 | 10 | +----------+--------+----+ 10 rows in set +----------+--------+----+ | name | salary | id | +----------+--------+----+ | zhangsan | 2100 | 1 | | lisi | 2600 | 2 | | wangwu | 3000 | 3 | | wangwu | 200 | 4 | | wangwu | 200 | 5 | | wangwu | 200 | 6 | | wangwu | 200 | 7 | | wangwu | 200 | 8 | | wangwu | 200 | 9 | +----------+--------+----+ 9 rows in set Query OK, 0 rows affected
九：事件 ，事件可以定时执行一些任务等。
如下：创建一个事件e1，每周执行一次过程procedure1
create event e1 on schedule every 1 week do call procedure1('hello');
mysql 误操作之后的回滚，当mysql误操作之后，又没有开启事务，可以用一下方法回滚。就是生成与误操作相反的语句。
https://www.jb51.net/article/99553.htm