DBA04-Multi-table query MySQL management tool, user authorization and revocation

DBA DAY04

One, multi-table query

STU_ID Name Class
Payment Form
Class table
Score Table
Employment Chart
1.1 Copy Table (function: Backup table, quick build table)

Mysql> CREATE TABLE table name SQL query; CREATE database DB4;
Use DB4;

CREATE TABLE User2 SELECT * from Db3.user;
Show tables;
SELECT * from User2;

CREATE TABLE User3 Select Name,uid,shell from Db3.user ORDER BY uid DESC limit 5;
SELECT * from User3;

SELECT * from user where shell= "abc";
CREATE TABLE User4 SELECT * from Db3.user where 1=2;

SELECT * from User4;
Desc USER4;
1.2 Multi-Table query
Format 1:select field Name list from table name list [where condition];


Use DB4;
CREATE TABLE T1 Select Name,uid,shell from Db3.user limit 3;
SELECT * from T1;


CREATE TABLE T2
Select Name,password,uid,homedir from Db3.user limit 5;
select * from T2;




Cartesian Set 3 * 5 = 15
SELECT * from T1,t2;


Select T1.name, t2.name from T1,t2;
Select t1.*, t2.password,t2.homedir from T1,t2;


Select t1.*, T2.password, T2.homedir from T1,t2
where t1.uid = T2.uid;


Format 2 nested query: The query result of the inner layer as the query condition of the outer query
Select field List from table name where condition (select field List from table name where condition);




Select name from Db3.user ORDER BY uid desc LIMIT 1;


Select AVG (UID) from Db3.user;
Select Name,uid from Db3.user where uid< (select AVG (UID) from Db3.user);




Select name from Db4.t1;
Select name from Db3.user where name in (select name from DB4.T1);



Format 3 Connection Query
Left JOIN query: The table on the left is the main display of query results
Select field List from table name a LEFT JOIN table name B on condition;


Right JOIN query: The table on the right is the main display of query results
Select field List from table name a right join table name B on condition;


CREATE TABLE Db4.t3 Select Name,uid,shell from Db3.user limit 5;
SELECT * from Db4.t3;


CREATE TABLE Db4.t4 Select Name,uid,shell from Db3.user limit 7;
SELECT * from Db4.t4;




SELECT * from T3 left join T4 on t3.uid = T4.uid;


SELECT * from T3 right join T4 on t3.uid = T4.uid;


Select T3.name,t4.name from T3 right join T4 on t3.uid = T4.uid;


Ii. MySQL Graphics management tool *
192.168.4.50
Phpmyadmin-2.11.11-all-languages.tar.gz


Step 1: Deploy a running environment lamp or LNMP
Rpm-q httpd
Yum-y Install httpd
Systemctl Start httpd
Systemctl Enable httpd


Rpm-q PHP
Rpm-q Php-mysql
Yum-y Install PHP Php-mysql
Systemctl Restart httpd


Step 2: Install the software phpMyAdmin
TAR-ZXVF phpmyadmin-2.11.11-all-languages.tar.gz
LS phpmyadmin-2.11.11-all-languages


MV Phpmyadmin-2.11.11-all-languages/var/www/html/phpadmin

cd/var/www/html/


Step 3: Create the configuration to specify the management database server.
] #cd Phpadmin
] #cp config.sample.inc.php config.inc.php
] #vim config.inc.php
<?php
.....
$cfg [' blowfish_secret '] = ' pljabc ';
$cfg [' Servers '] [$i] [' host '] = ' localhost ';
......
?>
: Wq
Step 4: Client Access
Open Browser Input URL http://192.168.4.50/phpadmin
User root
Password 123456

Third, user authorization * * (user name used when adding connections on the database server)

User authorization: Adds a new connection user name on the database server, by default only the database administrator root user has authorized permissions on the database server native login. By default, administrators can only connect from the database native connection service, where no other user can connect to the database server.

Authorization Command format:
]# mysql-uroot-p123456
Mysql>
Grant permission list on database name to user name @ "Client Address"
Identified by "password" [with GRANT option];

With GRANT option, you have permission to add users after they connect to the server.
Identified by "password" when a newly added user connects to the database server, the password used

Client address function: Those hosts on the network can connect to the database server with the newly added user. There are the following representations:

All hosts%
Network Segment 192.168.4.%
Specify host 192.168.4.51
Native localhost
Host name pc1.tedu.cn
Domain%.tedu.cn

User name The name that the client host uses when connecting to the database server, and when the authorization is customized, the name must be identified.

Database name: The newly added connection user, the library that can be manipulated after access, is represented as follows:
* * All libraries All tables
Library name. * All tables under the library
Name of the library. Indicates that a table

Permissions List: Newly added connection users, access to the library that can operate, and permissions are represented as follows:

All permissions
command, command a permission (for example, select, INSERT, delete)

Authorization library MySQL library records authorization information, uses different tables to record different authorization information
Use MySQL
User record the connection username that has been added
DB record the added connection user access to the library
Tables_priv record the added connection user access to the table
Columns_priv record the added connection user access to a table field

DESC user;
DESC DB;
Desc Tables_priv;
Desc Columns_priv;

SELECT * from User\g;
SELECT * from DB;
SELECT * from Tables_priv;
SELECT * from Columns_priv;

Select User from Mysql.user;
Select User,host from Mysql.user;

To view access rights for authorized users
Show grants for user @ "client address";

Show grants for [email protected] "localhost";
SELECT * from user where user= "root" and host= "localhost" \g;

Authorization Example 1
Add user admin on 50 server
Mysql> Grant Select,insert on db3.* to [e-mail protected] "192.168.4.%" identified by "123QQQ ... A ";

Mysql> Select User from Mysql.user where user= "admin";

In client 51, use the service to add a new user connection host 50 authentication Example 1 authorization
]# MYSQL-H192.168.4.50-UADMIN-P123QQQ ... A
Mysql> SELECT @ @hostname; To view the host name of the current host

?
Mysql> Select User (); View the name of the current user and the host that is logged on

?
Mysql> Show grants; See the permissions you have

?

mysql> Execute SQL command validation permissions

Authorization Example 2
In 50 server Licensing host 52 You can use root to connect to yourself, all library tables have full permissions and have the right to login password is 123QQQ ... A

Grant all on * * to [e-mail protected] "192.168.4.52" identified by "123QQQ ... A "with GRANT option;

The client uses the service newly added user connection host 50 authentication Example 2 authorization
[Email protected] ~]# MYSQL-H192.168.4.50-UROOT-P123QQQ ... A
Mysql> SELECT @ @hostname;
Mysql> Select User ();
Mysql> Show grants;
Mysql> testing access to a library table
Mysql> Grant Select,update (Name,uid) on Db3.user to [e-mail protected] "%" identified by "123QQQ ...  A "; #测试授权权限

The client is using an authorized user to add a user to the database server 50
]# MYSQL-H192.168.4.50-UWEBUSER-P123QQQ ... A
Mysql> Show grants;
Mysql> Testing access rights

Viewing authorization information on a 50 host
Select User,host from Mysql.user;
SELECT * from Mysql.db \g;
SELECT * from Mysql.tables_priv \g;
SELECT * from Mysql.columns_priv \g;

Revoke permissions (remove access for newly added users)
Mysql> revoke permissions on database name from user name @ "Client Address";

Select User,host from Mysql.user;
Show grants for [email protected] "192.168.4.52";

Revoke GRANT OPTION on * * FROM [email protected] "192.168.4.52"; In essence, modify the Grant_priv in the user table to Y.
Show grants for [email protected] "192.168.4.52";

SELECT * from Mysql.user where user= "root" and host= "192.168.4.52" \g;

Modify a record to revoke a user's permissions
mysql> Update mysql.user set delete_priv= "N" where user= "root" and host= "192.168.4.52";
Mysql>flush privileges;

SELECT * from Mysql.user where user= "root" and host= "192.168.4.52" \g;

Revoke all on * * from [email protected] "192.168.4.52";
Show grants for [email protected] "192.168.4.52";

Remove authorized users (remove added connected users)
Mysql> Drop user username @ "Client address";
mysql> drop user [email protected] "192.168.4.52";

SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CR Eate temporary TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, create VIEW, SHOW view, create ROUTINE , ALTER ROUTINE, create USER, EVENT, TRIGGER, create tablespace
Change Password:
To modify the connection password after authorizing the user to connect to the database server
Mysql> set Password=password ("New password"); The essence is to modify the Authentication_string field in the user table

Administrator resets the connection password for authorized users
Mysql> Set password for user name @ "Client Address" =password ("New password");

DBA04-Multi-table query MySQL management tool, user authorization and revocation