个人博客地址:https://www.vastyun.com

 

储存过程
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";

一、MYSQL约束

Preface

 

1.暗许值限定,当字段未有插入值的时候,mysql自动给该字段分配暗中认可值。
暗中认可值的字段允许为空。
对暗中认可值字段也得以插入null。

 

1 CREATE TABLE STUDENT(
2     ID INT,
3     NAME VARCHAR(20),
4     ADDRESS VARCHAR(20) DEFAULT '京口区'
5 );
6 INSERT INTO STUDENT (ID, NAME ) VALUES (1,'张三');
7 INSERT INTO STUDENT (ID , NAME,ADDRESS ) VALUES (2,'李四' ,NULL);

    User privileges regulation is pretty
important in DBAs routine job.As we all know,it’s the less the better to
reduce risks when someone who has no privileges manipulates data by
malevolence.On the other hand,when we implement HA,replication or the
other tools,it’s significant and efficient to grant proper privileges to
specific users.

 

 

澳门微尼斯人手机版 1

Introduce

2.非空封锁

 

1 CREATE TABLE STUDENT(
2     ID INT,
3     NAME VARCHAR(20),
4     GENDER VARCHAR(2) NOT NULL
5 );

    pt-show-grants is the right tool which
can directly provide an overview of grants to all users on connected
MySQL db server what is rather convenient for us.There’re also several
advartages of using it.At first,you can dump user grants from one server
to another one simply. Secondly,you can organize version control by
placing the user grants with it since it will sort the grant statements
in order what “show grants;” may not do.Thirdly,now that it provide a
normalized format than “show grants;” does,you can distinguish the
different user grants between two servers efficiently.

 

 

2.1非空字段必得赋值(错误显示)

Procedure

INSERT INTO STUDENT (ID , NAME) VALUES(1, '李四');

 

 

Usage

澳门微尼斯人手机版 2

1 pt-show-grants [OPTIONS] [DSN]

2.2无法插入null(错误展现卡塔 尔(英语:State of Qatar)

 

INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三' ,NULL);

**Parameters**

 

 1 //Regular parameters.
 2 --drop //Add "DROP USER" before each user of output,which can be used to get a ddl of droping user.
 3 --flush //Add "FLUSH PRIVILEGES" after output(version ahead 4.1.1 need).
 4 --ignore //Speicify the ignore user.
 5 --only //on the contrary of "--ignore" does,specify the only user you want.
 6 --include-unused-roles //This options merely for MySQL 8.0 + version which support roles.
 7 --seperate //List the grant and revoke statement respectively.
 8 
 9 //Dump hearder relevant.
10 --no-header //Don't print head information of dump.
11 --no-timestamp //Don't add timestam to the head of dump.

澳门微尼斯人手机版 3

 

 

Examples

3.唯意气风发限定

 

1 CREATE TABLE STUDENT (
2     ID INT UNIQUE,
3     NAME VARCHAR(20)
4 );
5 INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三');

Execute without  any parameters.(it
will read connection options in defatul my.cnf )

 

 1 [root@zlm1 06:17:09 ~]
 2 #pt-show-grants
 3 -- Grants dumped by pt-show-grants
 4 -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 07:59:32
 5 -- Grants for 'bkuser'@'localhost'
 6 CREATE USER IF NOT EXISTS 'bkuser'@'localhost';
 7 ALTER USER 'bkuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3DE5D9E4FBC1E464DA1B1172D6333CE89FDE5C61' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
 8 GRANT LOCK TABLES, PROCESS, RELOAD, REPLICATION CLIENT ON *.* TO 'bkuser'@'localhost';
 9 -- Grants for 'mysql.session'@'localhost'
10 CREATE USER IF NOT EXISTS 'mysql.session'@'localhost';
11 ALTER USER 'mysql.session'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
12 GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost';
13 GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';
14 GRANT SUPER ON *.* TO 'mysql.session'@'localhost';
15 -- Grants for 'mysql.sys'@'localhost'
16 CREATE USER IF NOT EXISTS 'mysql.sys'@'localhost';
17 ALTER USER 'mysql.sys'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
18 GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';
19 GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';
20 GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';
21 -- Grants for 'repl'@'192.168.56.%'
22 CREATE USER IF NOT EXISTS 'repl'@'192.168.56.%';
23 ALTER USER 'repl'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
24 GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.56.%';
25 -- Grants for 'root'@'localhost'
26 CREATE USER IF NOT EXISTS 'root'@'localhost';
27 ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
28 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
29 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

下边语句执行发生错误

 

INSERT INTO STUDENT (ID , NAME) VALUES (1, '李四');

**Specify the only user
“root”.**

 

1 [root@zlm1 08:38:28 ~]
2 #pt-show-grants -hlocalhost -P3306 -uroot -pPassw0rd --only=root
3 -- Grants dumped by pt-show-grants
4 -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 08:38:30
5 -- Grants for 'root'@'localhost'
6 CREATE USER IF NOT EXISTS 'root'@'localhost';
7 ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
8 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
9 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

怪诞提醒

 

澳门微尼斯人手机版 4

**Specify the only user “root” with
revoke statement separated with grant statement.**

4.主键约束(非空+唯风姿洒脱),日常每张表都会设置二个主键字段。用于标明表记录的唯生龙活虎性。
 主键平常都是尚未事情含义的。

 1 [root@zlm1 08:38:30 ~]
 2 #pt-show-grants -hlocalhost -P3306 -uroot -pPassw0rd --only=root --separate --revoke 
 3 -- Grants dumped by pt-show-grants
 4 -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 08:38:46
 5 -- Revoke statements for 'root'@'localhost'
 6 REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'localhost';
 7 REVOKE GRANT OPTION ON *.* FROM 'root'@'localhost';
 8 REVOKE PROXY ON ''@'' FROM 'root'@'localhost';
 9 REVOKE GRANT OPTION ON *.* FROM 'root'@'localhost';
10 -- Grants for 'root'@'localhost'
11 CREATE USER IF NOT EXISTS 'root'@'localhost';
12 ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
13 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
14 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
1 CREATE TABLE STUDENT(
2     ID INT PRIMARY KEY,
3     NAME VARCHAR(20)
4 );
5 
6 INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三');

 

上边语句实行展现错误

**Specify the only user “root” with drop
statement.**

INSERT INTO STUDENT (ID , NAME ) VALUES (NULL , '李四');
 1 [root@zlm1 08:38:46 ~]
 2 # pt-show-grants -hlocalhost -P3306 -uroot -pPassw0rd --only=root --drop
 3 -- Grants dumped by pt-show-grants
 4 -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 08:39:03
 5 DROP USER 'root'@'localhost';
 6 DELETE FROM `mysql`.`user` WHERE `User`='root' AND `Host`='localhost';
 7 -- Grants for 'root'@'localhost'
 8 CREATE USER IF NOT EXISTS 'root'@'localhost';
 9 ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
10 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
11 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

不当提醒

 

澳门微尼斯人手机版 5

**Specify the only user “repl” with no
timestamp.**

上边错误也是豆蔻梢头种错误

1 [root@zlm1 08:40:57 ~]
2 #pt-show-grants -h192.168.56.100 -P3306 -urepl -prepl4slave --only repl --no-timestamp
3 -- Grants dumped by pt-show-grants
4 -- Dumped from server 192.168.56.100 via TCP/IP, MySQL 5.7.21-log
5 -- Grants for 'repl'@'192.168.56.%'
6 CREATE USER IF NOT EXISTS 'repl'@'192.168.56.%';
7 ALTER USER 'repl'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
8 GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.56.%';

发表评论

电子邮件地址不会被公开。 必填项已用*标注