MySQL笔记
Citing Lv2
 2020-12-28 15:07:53  347 Words

一份给自己看的MySQL指令笔记～

User

Create user

Create a new user and set the passwor.

1
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Delete user

1
DROP USER 'username'@'localhost';

Change password

1
ALTER USER 'username'@'localhost' IDENTIFIED BY 'password';

Privileges

GRANT

1
GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost';
Example:

Allow newuser do anything in any tables of any databases, first * means database, the second * means tables, as same as root.

1
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

REVOKE

1
REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost';

Show grants

1
SHOW GRANTS FOR 'username'@'localhost';
Type of permissions
  • ALL PRIVILEGES- as we saw previously, this would allow a MySQL user full access to a designated database (or if no database is selected, global access across the system)
  • CREATE- allows them to create new tables or databases
  • DROP- allows them to them to delete tables or databases
  • DELETE- allows them to delete rows from tables
  • INSERT- allows them to insert rows into tables
  • SELECT- allows them to use the SELECT command to read through databases
  • UPDATE- allow them to update table rows
  • GRANT OPTION- allows them to grant or remove other users’ privileges

FLUSH

Make the grant effect.

1
FLUSH PRIVILEGES

Database

Create database

1
2
3
4
5
6
7
8
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
  | ENCRYPTION [=] {'Y' | 'N'}
}
Example:
  • Create a database.
1
CREATE DATABASE IF NOT EXISTS test_data_base;
  • Create a database with utf8 encoding.
1
CREATE DATABASE db_name CHARACTER SET UTF8mb4 COLLATE utf8mb4_bin

Drop database

1
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Alter database

1
2
3
4
5
6
7
8
9
ALTER {DATABASE | SCHEMA} [db_name]
    alter_option ...

alter_option: {
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
  | READ ONLY [=] {DEFAULT | 0 | 1}
}
Example:

Alter a database use UTF-8.

1
ALTER DATABASE db_name CHARACTER SET UTF8mb4 COLLATE utf8mb4_bin

Show databases.

1
SHOW DATATASES

Use database

1
USE db_name

Table

 Comments