Basic MySQL operations and common commands

Basic operations

show databases;
Use library name;
Show tables;
CREATE TABLE table name (field settings list);
describe table name;

Create database name;

drop database name;
drop table name;

Delete from table name;
SELECT * from table name;

Modify New Password

Method One (I used to)

In Terminal input: Mysql-u user name-p password
Use MySQL;
Update user set Password=password (' New password ') where user= ' username ';
Flush privileges; #更新权限

Quit #退出

Method Two:

Using the Set password command

Mysql-u Root

mysql> SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' Newpass ');

Method Three:

With Mysqladmin

mysqladmin-u root Password "Newpass"

If Root has already set a password, use the following method

mysqladmin-u root password Oldpass "Newpass"

Method Four:

This can be done when the root password is lost

Mysqld_safe--skip-grant-tables&

Mysql-u Root MySQL

mysql> UPDATE user SET Password=password ("New password") WHERE user= ' root ';

mysql> FLUSH privileges;

Permissions

First, create a user and authorize

Format: GRANT permissions on library. Table to ' username ' @ ' Specify IP ' identified by ' password ';

GRANT all privileges on * * to ' daxiong1 ' @ ' percent ' identified by ' daxiong1 ';
Flush privileges; "Let the above authorized operation take effect"

GRANT all privileges on * * to ' daxiong2 ' @ ' 192.168.8.100 ' identified by ' daxiong2 ';
Flush privileges; "Let the above authorized operation take effect"

Verify: Use the Navicat Lite for MySQL tool in Windows, log in to our MySQL server using the 2 users above!

GRANT Select On * * to ' daxiong3 ' @ '% ' identified by ' daxiong3 ';
Flush privileges; "Let the above authorized operation take effect"

Show grants for user; "View specified user-owned permissions"

Revoke all privileges on * * from ' user ' @ '% '; "Recover all permissions of a user"

When user rights are usage, this permission is minimal and he can only log in!

"All-purpose Change Password"
Update Mysql.user set Password=password (' New password ') where user= ' username ';
"Let permissions take effect"
Flush privileges;

Show full processlist; "See which users are logged in"

KILL Specifies the user's id "Force quit specified user"

Second, add new users (learn from the online article)

Format: Grant permissions on database. * To User name @ login host identified by "password"

View user's permissions show grants for root;

Revoke permissions on the database. * from username;

You must give a message when setting permissions

1, permissions to grant

2, the database or table that is granted access

3, User name

Grant and revoke can control access at several levels

1, entire server, using grant all and revoke all

2, entire database, using on database.*

3, Feature table, using on database.table

4, a specific column

5, a specific stored procedure

Meaning of the value of the host column in the user table

% matches all hosts

localhost localhost will not be parsed into an IP address and connected directly via Unixsocket

The 127.0.0.1 will be connected via TCP/IP protocol and can only be accessed natively;

:: 1:: 1 is compatible with support IPv6, indicating the 127.0.0.1 with IPv4

MySQL grant permissions can be used on multiple levels, respectively.

1. Grant acts on the entire MySQL server:

Grant SELECT On *. * to [email protected]; --DBAs can query tables in all databases in MySQL.

Grant all on * * to [email protected]; --DBA can manage all databases in MySQL

2. Grant acts on a single database:

Grant Select on testdb.* to [email protected]; --DBAs can query the tables in TestDB.

3. Grant acts on a single data table:

Grant SELECT, INSERT, UPDATE, delete on testdb.orders to [email protected];

4. Grant acts on the columns in the table:

Grant Select (ID, SE, rank) on testdb.apache_log to [email protected];

5. Grant acts on stored procedures, functions:

Grant execute on procedure testdb.pr_add to ' dba ' @ ' localhost '

Grant execute on function Testdb.fn_add to ' dba ' @ ' localhost '

Grant General DBA manages permissions for a MySQL database.

Grant all privileges on TestDB to 'dba ' @ ' localhost '

Where the keyword "privileges" can be omitted.

Grant Advanced DBA manages permissions for all databases in MySQL.

Grant all on * * to 'dba ' @ ' localhost '

This article is from the "Come Together" blog, please be sure to keep this source http://daxionglaiba.blog.51cto.com/11790757/1828090

Basic MySQL operations and common commands