"Simple!!!" "MySQL User management and transaction

MySQL User management and transactions

After learning the installation and basic operation of MySQL on CentOS7, we will learn about MySQL user management and business. First of all, a database is not only one user, when creating a good database, we only have a default root user, how to create a new user? OK, let's study together!

• First log in to the MySQL database using the root user

  [[email protected] ~]# mysql -u root -pEnter password:      //输入root的密码：abc123mysql>      //然后就可以看到进入MySQL数据库模式了mysql> show databases;      //查看数据库信息+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                |+--------------------+5 rows in set (0.00 sec)

mysql> use MySQL; Go to MySQL Database
Database changed

Mysql> select User,authentication_string,host from User; Viewing user information in the system
+-----------+-------------------------------------------+-----------+
| User | authentication_string | Host |
+-----------+-------------------------------------------+-----------+
| Root | 6691484ea6b50ddde1926a220da01fa9e575c18a |
| Mysql.sys | Thisisnotavalidpasswordthatcanbeusedhere | localhost |
| Root | *6691484ea6b50ddde1926a220da01fa9e575c18a | % |
+-----------+-------------------------------------------+-----------+
3 Rows in Set (0.00 sec)
#注: Here you can see another two users, a root user, a Mysql.sys system user

然后这里可以看到系统中有两个用户，且可以看到用户的密码显示时是通过密文的方式显示的。## 创建新用户 然后下面我们创建一个新的用户叫做zyc，密码设置abc123

mysql> create user ' zyc ' @ ' localhost ' identified by ' abc123 '; Create the user Zyc locally and set the password to abc123
Query OK, 0 rows affected (0.01 sec)

Mysql> select User,authentication_string,host from User; View System User Information again
+-----------+-------------------------------------------+-----------+
| User | authentication_string | Host |
+-----------+-------------------------------------------+-----------+
| Root | 6691484ea6b50ddde1926a220da01fa9e575c18a |
| Mysql.sys | Thisisnotavalidpasswordthatcanbeusedhere | localhost |
| Root | 6691484ea6b50ddde1926a220da01fa9e575c18a |% |
| Zyc | 6691484ea6b50ddde1926a220da01fa9e575c18a | localhost |
+-----------+-------------------------------------------+-----------+
4 rows in Set (0.00 sec)
This ZYC user is created, and can be seen here because my ZYC user's password is consistent with the root user, so the ciphertext password shown is consistent

## 用户重命名然后我们将zyc用户重命名为hwj

mysql> rename user ' zyc ' @ ' localhost ' to ' hwj ' @ ' localhost '; Rename the Zyc user to HWJ
Query OK, 0 rows Affected (0.00 sec)

Mysql> select User,authentication_string,host from User;
+-----------+-------------------------------------------+-----------+
| User | authentication_string | Host |
+-----------+-------------------------------------------+-----------+
| Root | 6691484ea6b50ddde1926a220da01fa9e575c18a |
| Mysql.sys | Thisisnotavalidpasswordthatcanbeusedhere | localhost |
| Root | 6691484ea6b50ddde1926a220da01fa9e575c18a |% |
| HWJ | 6691484ea6b50ddde1926a220da01fa9e575c18a | localhost |
+-----------+-------------------------------------------+-----------+
4 rows in Set (0.01 sec)
Here you can see that the ZYC user has been renamed to HWJ.

## 修改用户密码- 修改当前登录用户的密码

Mysql> set Password=password (' 123123 '); To modify the password of the root user who is currently logged on
Query OK, 0 rows affected, 1 Warning (0.00 sec)

Mysql> select User,authentication_string,host from User;
+-----------+-------------------------------------------+-----------+
| User | authentication_string | Host |
+-----------+-------------------------------------------+-----------+
| Root | E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| Mysql.sys | Thisisnotavalidpasswordthatcanbeusedhere | localhost |
| Root | 6691484ea6b50ddde1926a220da01fa9e575c18a |% |
| HWJ | 6691484ea6b50ddde1926a220da01fa9e575c18a | localhost |
+-----------+-------------------------------------------+-----------+
4 rows in Set (0.00 sec)
Here you can see the root user bought the ciphertext has changed, we can exit the login

[Email protected] ~]# mysql-u root-p
Enter Password://Here I enter the previous password abc123
Error 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using password:yes)//Can see an error message, that is, access is restricted, in fact, because the password is not correct, so refused Access

[Email protected] ~]# mysql-u root-p
Enter Password://Enter the password we modified earlier, that is, 123123
mysql>//Then you can see the smooth entry to MySQL mode.

- 修改其他用户密码

mysql> set password for ' hwj ' @ ' localhost ' =password (' 123456 '); Modify the password for the HWJ user to 123456
Query OK, 0 rows affected, 1 Warning (0.00 sec)

Mysql>quit//Exit Database
[Email protected] ~]# mysql-u hwj-p
Enter Password://Here I enter the abc123 (old password)
Error 1045 (28000): Access denied for user ' hwj ' @ ' localhost ' (using password:yes)///Then there will be an same access restricted error

[Email protected] ~]# mysql-u hwj-p
Enter Password://Enter 123456 (after the modified password)
mysql>//Enter MySQL mode here

## 删除用户

Mysql> select User,authentication_string,host from User; See the users in the system first
+-----------+-------------------------------------------+-----------+
| User | authentication_string | Host |
+-----------+-------------------------------------------+-----------+
| Root | E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| Mysql.sys | Thisisnotavalidpasswordthatcanbeusedhere | localhost |
| Root | 6691484ea6b50ddde1926a220da01fa9e575c18a |% |
| HWJ | 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
+-----------+-------------------------------------------+-----------+
4 rows in Set (0.00 sec)

mysql> drop user ' hwj ' @ ' localhost '; Delete HWJ User
Query OK, 0 rows Affected (0.00 sec)

Mysql> select user,authentication_string,host from user;//check again to see HWJ user deleted
+-----------+---------------- ---------------------------+-----------+
| User | authentication_string | Host |
+-----------+-------------------------------------------+-----------+
| root | e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | localhost |
| mysql.sys | Thisisnotavalidpasswordthatcanbeusedhere | localhost |
| root | *6691484ea6b50ddde1926a220da01fa9e575c18a |% |
+-----------+-------------------------------------------+-----------+
3 rows in Set (0.00 sec)

## 事务事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求。事务是一个不可分割的工作逻辑单元。### 事务的特性事务具有四个特性：1. 原子性事务是一个完整的操作，各元素是不可分的，即原子的。2.一致性当事务完成时，数据必须处于一致状态；在事务开始之前，数据库中存储的数据处于一致状态；在正在进行的事务中，数据可能处于不一致的状态；当事务成功完成时，数据必须再次回到已知的一致状态。3.隔离性对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。4.持久性指不管系统是否发生了故障，事务处理的结果都是永久的。### mysql事务操作begin：表示开始一个事务，后面接多条数据库操作语句执行。commit：表示提交一个事务，对应前面的begin操作，他们之间的数据库操作语句一起完成。rollback：表示回滚一个事务，在begin和commit之间。**操作示例：**- 完整的事务操作

Mysql> SELECT * from info; See what data is in the table first
+----------+--------+
| name | Salary |
+----------+--------+
| Zhangsan | 5500 |
| Lisi | 5800 |
+----------+--------+
2 rows in Set (0.00 sec)

Mysql> begin; Start a transaction
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into info values (' test01 ', 5300);
Query OK, 1 row Affected (0.00 sec)

mysql> INSERT into info values (' test02 ', 5100); Insert two consecutive data
Query OK, 1 row Affected (0.00 sec)

Mysql> commit; Commit a transaction
Query OK, 0 rows Affected (0.00 sec)

Mysql> SELECT * from info; See what transactions are available again
+----------+--------+
| name | Salary |
+----------+--------+
| Zhangsan | 5500 |
| Lisi | 5800 |
| test01 | 5300 |
| test02 | 5100 | Here you can see that adding test01, test02 Two statements are made as a whole, executed together
+----------+--------+
4 rows in Set (0.00 sec)

- 只开始事务，不执行提交

mysql> Delete from info where name = ' test01 ';
Query OK, 1 row Affected (0.00 sec)

mysql> Delete from info where name = ' test02 '; First delete test01, test02 two data
Query OK, 1 row affected (0.01 sec)

Mysql> SELECT * from info; When you view the information in the table, only Zhangsan and Lisi two data
+----------+--------+
| name | Salary |
+----------+--------+
| Zhangsan | 5500 |
| Lisi | 5800 |
+----------+--------+
2 rows in Set (0.00 sec)

mysql> INSERT into info values (' test01 ', 5100);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT into info values (' test02 ', 5100); Insert two data again

Mysql> SELECT * from info; At this point we look at the table information
+----------+--------+
| name | Salary |
+----------+--------+
| Zhangsan | 5500 |
| Lisi | 5800 |
| test01 | 5100 |
| test02 | 5100 |
+----------+--------+
4 rows in Set (0.00 sec)

#这里虽然能看到这两条数据已经存在了，但是这是存在于内存中，并没有写入硬盘中我们更换一个客户端再次查看表信息

Mysql> SELECT * from info; At this point we look at the table information
+----------+--------+
| name | Salary |
+----------+--------+
| Zhangsan | 5500 |
| Lisi | 5800 |
+----------+--------+
4 rows in Set (0.00 sec)

这里就说明使用begin开始事务后，执行操作语句后，必须使用commit进行提交，否则数据是不能自动提交的。- rollback回滚

Mysql> SELECT * from info; View information for a table first
+----------+--------+
| name | Salary |
+----------+--------+
| Zhangsan | 5500 |
| Lisi | 5800 |
+----------+--------+
2 rows in Set (0.01 sec)

Mysql>
Mysql> begin; Start a transaction
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into info values (' test01 ', 5300); Insert a piece of data
Query OK, 1 row Affected (0.00 sec)

Mysql> SELECT * from info; You can now see that the test01 has been inserted into the
+----------+--------+
| name | Salary |
+----------+--------+
| Zhangsan | 5500 |
| Lisi | 5800 |
| test01 | 5300 |
+----------+--------+
3 Rows in Set (0.00 sec)

mysql> rollback; Perform a rollback operation
Query OK, 0 rows affected (0.01 sec)

Mysql> SELECT * from info;
+----------+--------+
| name | Salary |
+----------+--------+
| Zhangsan | 5500 |
| Lisi | 5800 | Here you can see the test01 data is gone.
+----------+--------+
2 rows in Set (0.00 sec)

This shows that after the rollback rollback operation, the data is restored to the data before the begin of the execution.

"Simple!!!" "MySQL User management and transaction