储存过程DELIMITER //CREATE PROCEDURE pro1()BEGINSELECT book_id,book_name,category FROM bookinfo t1JOIN bookcategory t2ON t1.book_category_id = t2.category_id;END//DELIMITER ;DELIMITER //CREATE PROCEDURE pro2(IN cid CHAR(18),OUT num INT)BEGINDELETE 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)BEGINDECLARE 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为开启自动提交BEGININSERT 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)BEGINDECLARE 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  重启mysql2.set default_storage_engine=INNODB3.创建表时设置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
     
      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";