欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql的学习(八)-储存过程和事务和导入导出

程序员文章站 2022-07-02 21:46:57
储存过程 DELIMITER // CREATE PROCEDURE pro1() BEGIN SELECT book_id,book_name,category FROM bookinfo t1 JOIN bookcategory t2 ON t1.book_category_id = t2.ca... ......
储存过程
DELIMITER //
CREATE PROCEDURE pro1()
BEGIN
SELECT book_id,book_name,category FROM bookinfo t1
JOIN bookcategory t2
ON t1.book_category_id = t2.category_id;
END//
DELIMITER ;


DELIMITER //
CREATE PROCEDURE pro2(IN cid CHAR(18),OUT num INT)
BEGIN
DELETE FROM reader_info WHERE card_id = cid;
SELECT COUNT(card_id) INTO num FROM readerinfo;
END//
DELIMITER ;

CALL pro2('21513333333',@num);
SELECT @num;

储存过程交换两个数
DELIMITER //
CREATE PROCEDURE pro3(INOUT num1 INT INOUT num2 INT)
BEGIN
DECLARE t INT DEFAULT 0;
SET t=num1;
SET num1=num2;
SET num2=t;
END//
DELIMITER ;

SET @n1=3,@n2=4;
CALL proc3(@n1,@n2);
SELECT @n1,@n2;
DROP PROCEDURE IF EXISTS proc1;


SELECT FLOOR(RAND()*5); 随机数

事务
ROLLBACK 事务回滚 就是不上面写的都不算
COMMIT  事务提交,就是确认
SET autocommit=0  禁止自动提交  =1为开启自动提交


BEGIN
INSERT INTO mytest VALUES(4,'test01');
SAVEPOINT s1;   保存点
INSERT INTO mytest VALUES(4,'test02');
SAVEPOINT s2;  
INSERT INTO mytest VALUES(4,'test03');
ROLLBACK TO s2;
COMMIT;
01 02  会保存  03不会


DELIMITER //
CREATE PROCEDURE borrowproc(cid CHAR(18),bid INT)
BEGIN
DECLARE store_num INT;
DECLARE money FLOAT(7,3);
SELECT store INTO store_num FROM bookinfo WHERE book_id=bid;
SELECT balance INTO money FROM readerinfo WHERE card_id=cid;
SET autocommit=0;  禁止自动提交  同时也是事务的开始
INSERT INTO borrowinfo VALUES(bid,cid,CURDATE()),DATE_ADD(CURDATE(),INTERVAL 1 MONTH),'否');
UPDATE bookinfo SET store=store-1 WHERE book_id=bid;
UPDATE readerinfo SET balance=balance-(SELECT price FROM bookinfo WHERE  book_id=bid)*0.5 WHERE card_id =cid;
IF store_num=0 OR money<=200 THEN
    ROLLBACK;
ELSE 
    COMMIT;
END IF;
END//
DELIMITER ;



存储引擎
SHOW ENGINES;查看支持的引擎
INNODB 安全性能较强
MYISAM 不提供事务 有较高的处理效率
MEMORY 存放临时数据
设置存储引擎
1.my.ini 中 找到default-STORAGE-ENGINE=INNODB  重启mysql
2.set default_storage_engine=INNODB
3.创建表时设置
CREATE TABLE mytest(
 id INT PRIMARY INT,
 NAME VARCHAR(20)
 )ENGINE =INNODB DEFAULT CHARSET=utf-8;
 4.alter TABLE test ENGINE=xxx;
 
 
 
 创建用户
 CREATE USER 'rose'@'localhost' IDENTIFIED BY 'rosepwd'  创建了用户名是rose 主机名是localhost,密码是rosepwd
 //用password的哈希值来创建
 SELECT PASSWORD('roswpwd');
 xxxxxxxxxxxxxxxxxxxxxxx
 CREATE USER 'rose'@'localhost' IDENTIFIED BY PASSWORD ' xxxxxxxxxxxxxxxxxxxxxxx' ;
 
 用grant创建  可以带有权限
 GRANT SELECT,UPDATE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY 'testpwd'; select和update为允许的权限
 删除用户
 DROP USER 'testuser'@'localhost';
 DELETE FROM mysql.user WHERE HOST='hostname'AND USER='username';
 
 
 比如
 GRANT INSERT ,SELECT ON book.* TO 'rose'@'localhost';  为book数据库下所有的表设置查找和插入权限
 FLUSH privileges;  刷新权限表
 GRANT ALL PRIVILEGES ON *.* TO 'rose'@'localhost';为所有东西设置所有权限
 
 
 错误日志
 SHOW VARIABLES ;
 数据库的备份
 mysqldump -uroot -pmy123 book readerinfo >c:\mysqlbackup\tset1.sql;//备份book下的readerinfo表
 mysqldump -uroot -pmy123 --databases book mytese >c:\mysqlbackup\tset1.sql;//备份两个多个数据库
 mysqldump -uroot -pmy123 --all-DATABASES >c:\mysqlbackup\tset1.sql;//备份所有数据库
 恢复
 mysql -uroot -pmy123 book<c:\mysqlbackup\tset1.sql;恢复数据库下的tset表
 source c:\mysqlbackup\tset1.sql;  也可以
 
 
 导入导出
 位置不能随便
 my.ini下  找到secure-FILE-priv='xxxxxxxxxxxxx'看这个路径试什么
 你将它设置成null  这不允许导出
 设置成“”则为可以导出到任何位置
 SELECT * FROM book.readerinfo INTO OUTFILE 'xxxxxxxxxxxxxxxx/文件名。txt';这俩路径要一样
 SELECT * FROM book.readerinfo INTO OUTFILE 'xxxxxxxxxxxxxxxx/文件名。txt'
  FIELDS TERMINATED BY ','  设置列与列的分隔符
  LINES TERMINATED BY '\r\n'  设置行末尾换行符
  
  mysql导出文件
  mysql -uroot -pxxxx --execute='语句(执行并推出)' dbname>filename.txt;
  mysql -uroot -pmy123 --execute="select * from readerinfo;" book>"xxxxxxxxxxxx"
  将book数据库下的readerinfo表导出到xxxxxxxxx

  导入命令
LOAD DATA INFILE "xxxxxxxxxxxx" INTO TABLE book.readerinfo;将xxx导入到book下的readerinfo  
mysqlimport -uroot -pmy123 book "xxxxxxxxxxxxxxx";