Changelog

• 2021-05-31
  • Add: Create table.
• 2020-10-05
  • Add: Alter table index.
• 2020-09-09
  • Add User password set.
  • Add Database rename
• 2020-09-04
  • Add Database recover, export
  • Add Database import
  • Add Datebase show
  • Add Table show
• 2018-08-06
  • Add Alert table
• 2018-07-26
  • Add Backup
• 2018-07-24
  • Add Account managment

Account managment

Create

SQL:

USE mysql;
INSERT ... user;
                        

Account-management statements:

CREATE USER user_specification [, user_specification] ...

user_specification:
    user [ identified_option ]

auth_option: {
    IDENTIFIED BY 'auth_string'
    | IDENTIFIED BY PASSWORD 'hash_string'
    | IDENTIFIED WITH auth_plugin
    | IDENTIFIED WITH auth_plugin AS 'hash_string'
}
                        

example:

CREATE USER "user1"@"host" IDENTIFIED BY "password";
GRANT ALL PRIVILEGES ON db1.* TO "user1"@"host";
                        

Grant

GRANT
    priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}]
[WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user_specification
    TO user_specification [, user_specification] ...
    [WITH GRANT OPTION]


object_type: {
    TABLE
    | FUNCTION
    | PROCEDURE
}

priv_level: {
    *
    | *.*
    | db_name.*
    | db_name.tbl_name
    | tbl_name
    | db_name.routine_name
}

user_specification:
    user [ auth_option ]

auth_option: {
    IDENTIFIED BY 'auth_string'
    | IDENTIFIED BY PASSWORD 'hash_string'
    | IDENTIFIED WITH auth_plugin
    | IDENTIFIED WITH auth_plugin AS 'hash_string'
}

tsl_option: {
    SSL
    | X509
    | CIPHER 'cipher'
    | ISSUER 'issuer'
    | SUBJECT 'subject'
}

resource_option: {
    | MAX_QUERIES_PER_HOUR count
    | MAX_UPDATES_PER_HOUR count
    | MAX_CONNECTIONS_PER_HOUR count
    | MAX_USER_CONNECTIONS count
}
                        

example

GRANT ALL PRIVILEGES ON `yiitest`.* TO "yiitest"@"localhost";
                        

Revoke

REVOKE ALL PRIVILEGES ON `yii2advanced`.* FROM "yiitest"@"localhost";
                        

Rename

RENAME USER "yii2advanced"@"localhost" TO "yiitest"@"localhost";
                        

Set password

SET PASSWORD [FOR user] = password_option

password_option: {
    PASSWORD('auth_string')
    | OLD_PASSWORD('auth_string')
    | 'hash_string'
}
                        

The SET PASSWORD statement assigns a password to a MySQL user account, specified as either a cleartext (unencrypted) or encrypted value:

• auth_string represents a cleartext password.
• hash_string represents an encrypted password.

Drop

SQL:

USE mysql;
INSERT ... user;
DELETE ... user;
                        

Account managment statements:

DROP USER user [, user] ...
                        

example

DROP USER "yiitest"@"localhost";
                        

Show

Show users:

USE mysql;
SELECT user FROM user;
                        

Show grants:

SHOW GRANTS FOR "yiitest"@"localhost";
                        

Note

不知为什么，在 phpmyadmin 中进行以上 操作后，这个用户登录不上，只好删除了重新建立。

phpmyadmin User 页面，最下方有这样几句话。

Note: phpMyAdmin gets the users' privileges directly from MySQL's privilege tables. The content of these tables may differ from the privileges the server uses, if they have been changed manually. In this case, you should reload the privileges before you continue.

在 phpmyadmin 中重新建立也不行了。可能是 phpmyadmin 的原因， 以后尽量只用它查看，操作的话，还是命令行吧。

用命令行就行了。

Database managment

Create

SQL:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
                        

Command line:

shell> mysqladmin [options] command [command-arg] [command [command-arg]] ...

create {db_name}
    Create a new database named db_name.

drop {db_name}
    Delete the database named db_name and all its tables.
                        

Drop

SQL:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
                        

Command line:

shell> mysqladmin [options] command [command-arg] [command [command-arg]] ...

create {db_name}
    Create a new database named db_name.

drop {db_name}
    Delete the database named db_name and all its tables.
                        

Show

SQL:

SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
SHOW {DATABASES | SCHEMAS}
    [LIKE 'pattern' | WHERE expr]
SHOW [FULL] TABLES [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]
                        

Command line:

shell> mysqlshow [options] [db_name [tbl_name [col_name]]]
                        

• If no database is given, a list of database names is shown.
• If no table is given, all matching tables in the database are shown.
• If no column is given, all matching columns and column types in the table are shown.

Rename

• RENAME TABLE

MySQL has no single statement to perform rename. You can use this method to move all tables from one database to a different one, in effect renaming the database. As long as two databases are on the same file system, can use RENAME TABLE to move a table from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
                        

note: Triggers, Foreign keys, privileges.

• mysqldump and recover

Backup

Command line:

shell> mysqldump [arguments] > file_name
                        

To dump all databases, invoke mysqldump with the --all-databases option:

shell> mysqldump --all-databases > dump.sql
                        

To dump only specific databases, name them on the command line and use the --databases option:

shell> mysqldump --databases db1 db2 db3 > dump.sql
                        

In the single-database case, it is permissible to omit the --databases option:

shell> mysqldump test > dump.sql
                        

To dump only specific tables from a database, name them on the command line following the database name:

shell> mysqldump test t1 t3 t7 > dump.sql
                        

To dump only data structure from a database:

shell> mysqldump --no-data ouyanglab-yii > conf/ouyanglab-yii.sql
                        

Recover

• SQL:

  mysql> SOURCE dump.sql

If the file is a single-database dump not containing CREATE DATABASE and USE statements, create the database, select it as the default database, and load the dump file:

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> SOURCE dump.sql
                        

• Command line:

  shell> mysql < dump.sql

If the file is a single-database dump not containing CREATE DATABASE and USE statements, create the database first (if necessary). Then specify the database name when you load the dump file:

shell> mysqladmin create db1
shell> mysql db1 < dump.sql
                        

Export

SQL:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
        [HIGH_PRIORITY]
        [STRAIGHT_JOIN]
        [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
        [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
        [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
        [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
        | INTO DUMPFILE 'file_name'
        | INTO var_name [, var_name]]
                        

Import

SQL:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]
                        

Command line:

mysqlimport [options] db_name textfile1 [textfile2 ...]
                        

Table managment

Create

SQL:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }
                        

Example:

CREATE TABLE `article` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `slug` varchar(128) NOT NULL,
    `date` datetime NOT NULL,
    `title` varchar(128) NOT NULL,
    `keywords` text NOT NULL,
    `description` text NOT NULL,
    `content` longtext NOT NULL,
    `category_id` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `slug` (`slug`)
);
                        

Show

SQL:

SHOW CREATE TABLE tbl_name
                        

Command line:

shell> mysqlshow [options] [db_name [tbl_name [col_name]]]
                        

• If no database is given, a list of database names is shown.
• If no table is given, all matching tables in the database are shown.
• If no column is given, all matching columns and column types in the table are shown.

Alert table

ALTER TABLE `file` DROP `basename`;

ALTER TABLE `file` CHANGE `path` `file_path` varchar(100) NOT NULL

INSERT INTO `tbl_name` (`a`, `b`, `c`) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
                        

Index

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
    <table_options>
    ADD {INDEX | KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
    | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
        [index_option] ...
    | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
        [index_name] [index_type] (key_part,...)
        [index_option] ...
    | ADD [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (col_name,...)
        reference_definition
    | DROP {INDEX | KEY} index_name
    | DROP PRIMARY KEY
    | DROP FOREIGN KEY fk_symbol
    [partition_options]
                        

Drop unique index:

ALTER TABLE category DROP INDEX slug;
                        

Last modified: 2021-05-31