ApsaraDB RDS:Common SQL commands for MySQL

Category

SQL syntax

Example

Create a database

-- Create a database.
CREATE DATABASE <database name>;

-- Create a database and specify the character set and collation.
CREATE DATABASE <database_name>
DEFAULT CHARACTER SET <charset>
COLLATE <collation>;

-- Create the app_db database.
CREATE DATABASE app_db;

-- Create the app_db database and specify the character set. We recommend that you use utf8mb4 because it supports more characters.
CREATE DATABASE app_db
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Create a database and specify the Chinese character set for specific scenarios.
CREATE DATABASE cn_db
DEFAULT CHARACTER SET gbk
COLLATE gbk_chinese_ci;

Delete a database

DROP DATABASE <database name>;

DROP DATABASE app_db;

Category

SQL syntax

Example

Create an account

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- Create an account that can be used to log on from any IP address. The percent sign (%) indicates that connections from any IP address are allowed.
-- This method is consistent with how accounts are created in the ApsaraDB RDS console. You can later delete the account in the ApsaraDB RDS console.
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass123!';

-- Create an account that can be used to log on only from a specific CIDR block.
-- If you specify an IP address or a CIDR block when you create the account, an error is reported when you later try to delete the account in the ApsaraDB RDS console. You must delete the account by running an SQL command.
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass123!';

Delete an account

DROP USER 'username'@'host';

-- Delete the app_user account that can be used to log on only from the 192.168.1.x CIDR block.
DROP USER 'app_user'@'192.168.1.%';

Grant permissions

GRANT SELECT ON db01.* TO 'username'@'host';
GRANT <privilege> ON <level> TO 'username'@'host';
-- <privilege>: The permission, such as SELECT, INSERT, UPDATE, DELETE, or ALL PRIVILEGES.
-- <level>: The permission level, such as db_name.* (database level), db_name.table_name (table level), or *.* (global level).

-- Grant the SELECT permission on all tables in the app_db database.
GRANT SELECT ON app_db.* TO 'app_user'@'%';

-- Grant the SELECT, INSERT, UPDATE, and DELETE permissions on the users table in the app_db database.
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.users TO 'app_user'@'%';

-- (Use with caution) Grant all permissions on the app_db database. Only clients from the 192.168.1.x CIDR block can connect.
GRANT ALL PRIVILEGES ON app_db.* TO 'admin_user'@'192.168.1.%';

Query account permissions

-- All created accounts in all instances and the hosts from which logon is allowed.
SELECT user, host FROM mysql.user;

-- View account permissions.
SHOW GRANTS FOR 'username'@'host';

-- View all database permissions that the app_user account has when it is used to log on from a host in the 192.168.1.% CIDR block.
SHOW GRANTS FOR 'app_user'@'192.168.1.%';

Revoke account permissions (the account still exists)

• Revoke all permissions: Revoke all operation permissions and the authorization capability from the account on all databases.

  REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'username'@'host';

• Revoke specified permissions: Revoke the UPDATE permission from the account on all tables in all databases. You can change the permission type as needed.

  REVOKE UPDATE ON *.* FROM 'username'@'host';

-- Revoke all operation permissions and the authorization capability from the app_user account on all databases when the account is used to log on from 192.168.1.%.
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_user'@'192.168.1.%';

-- Revoke the permission to modify data in the users table of the shop_db database from the app_user account when the account is used to log on from 10.10.0.%.
REVOKE UPDATE ON shop_db.users FROM 'app_user'@'10.10.0.%';