MySQL Must Know must Know – Concepts Section

MySQL installation

Recommend several MySQL installation and connection experience articles

  • Install MySQL on a Mac
  • Mysql: Command not found mysql: Command not found

MySQL application

The mysql command line

  • Command input after mysql>;
  • Command to use; Or \g, in other words, just press Enter without executing the command;
  • Type help or \h for help, or you can type more text for help with a particular command (for example, help select for help with trying select statements);
  • Enter quit or exit to exit the command line.

Connecting to a Database

The following information is required to connect to the database:

  • Host name (computer name) — localhost if connected to a local MySQL server;
  • Port (if a port other than the default port 3306 is used);
  • A valid user name;
  • User password (if required)

For example, the following command:

mysql -u root -h localhost -P 3306 -p
Copy the code

Database login and membership management

Access control

The security foundation of MySQL servers is that users should have adequate access to the data they need, no more or no less.

Users need to be given the access they need, and only the access they need. This is called access control. The purpose of access control is not only to prevent malicious attempts by users, but also to help avoid the very common results of unintentional errors, such as typing MySQL statements incorrectly, operating in an inappropriate database or some other user error.

Manage users

Querying an Existing User

MySQL user accounts and information are stored in a MySQL database called MySQL. It is generally only accessed directly if you want to get a list of all user accounts.

# enter USE mysql; SELECT user FROM user; # output + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | user | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | test | | root | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code
Creating a User Account

1. Use the CREATE USER statement (recommended)

# enter CREATE USER chenfangxu IDENTIFIED BY '123456'; SELECT user FROM user; # output + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | user | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | chenfangxu | | test | | root | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

The GRANT statement can also create user accounts. (Newer versions of MySQL 8.0 and above have separated creating accounts from granting permissions, so you can no longer use this method to create users.)

GRANT SELECT ON *.* TO chenfangxu@'%' IDENTIFIED BY '123456';Copy the code

INSERT rows directly into user table (not recommended)

Setting Access Rights

After a user account is created, access rights must be assigned. The newly created user account has no access permission. They can log in to MySQL, but they cannot see the data and perform any database operations.


Check the privileges granted to user accounts SHOW GRANTS FOR

# enter SHOW GRANTS FOR chenfangxu; # output + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Grants for chenfangxu @ % | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + |  GRANT USAGE ON *.* TO `chenfangxu`@`%` | +----------------------------------------+Copy the code

Permission USAGE ON *.*,USAGE means no permission at all, which means no permission ON anything ON any database or any table.

Chenfangxu@ % Since the user is defined as user@host, MySQL permissions are defined using a combination of user name and host name. If no host name is specified, the default host name % is used (that is, the user is granted access regardless of the host name).


GRANT PRIVILEGES ON databasename. Tablename TO ‘username’@’host’;

GRANT SELECT ON performance_schema.* TO chenfangxu@'%'; SHOW GRANTS FOR chenfangxu; # output + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Grants for chenfangxu @ % | +------------------------------------------------------------+ | GRANT USAGE ON *.* TO `chenfangxu`@`%` | | GRANT SELECT  ON `performance_schema`.* TO `chenfangxu`@`%` | +------------------------------------------------------------+Copy the code


REVOKE privileges ON databasename. Tablename FROM ‘username’@’host’;

REVOKE SELECT PERFORMANce_schema.* FROM chenfangxu@'%'; SHOW GRANTS FOR chenfangxu; # output + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Grants for chenfangxu @ % | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | GRANT USAGE ON *.* TO `chenfangxu`@`%` | +----------------------------------------+Copy the code


rename

RENAME USER ‘username’ TO ‘newusername’;

Enter RENAME USER test TO test1; SELECT user FROM user; # output + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | user | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | test1 | | root | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code


Change user password (mysql 8.0.11)

SET PASSWORD FOR ‘username’@’host’ = ‘newpassword’;

SET PASSWORD FOR chenfangxu@'%' = '654321'; ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';Copy the code


Delete user

DROP USER ‘username’@’host’;

# input DROP USER chenfangxu@'%'; SELECT user FROM user; # output + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | user | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | test | | root | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

Before MySQL 5, DROP USER could only be used to DROP USER accounts, not related permissions. As a result, with older versions of MySQL, you need to REVOKE the privileges associated with an account first and then DROP USER the account.

Operating database

CREATE DATABASE learnSQL; Select learnSQL from learnsql; SHOW DATABASES; SHOW TABLES; SHOW COLUMNS FROM customers;Copy the code

The DESCRIBE statement

DESCRIBE can be used as a shortcut for SHOW COLUMNS FROM in MySQL.

SHOW COLUMNS FROM customers; DESCRIBE customers;Copy the code

Front- end-Basics: Watch, Star, Front-End-Basics: Front-End-Basics

MySql must know must know this article