Programming/MySQL/Databases: Difference between revisions
< Programming | MySQL
Jump to navigation
Jump to search
Brodriguez (talk | contribs) (Add command flags) |
Brodriguez (talk | contribs) (Add column-statistics note) |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 37: | Line 37: | ||
* {{ ic |-h <host>}} - Allows specification of host, such as for connecting to a remote server. | * {{ ic |-h <host>}} - Allows specification of host, such as for connecting to a remote server. | ||
* {{ ic |-P <port>}} - Allows specification of port, such as for non-standard installations that don't use the default port {{ ic |3306}}. | * {{ ic |-P <port>}} - Allows specification of port, such as for non-standard installations that don't use the default port {{ ic |3306}}. | ||
* {{ ic |<nowiki>--password=<value></nowiki>}} - Replaces the above {{ ic |-p}} flag. Allows specification of the user password in the command itself. Note that this means typing the password in plaintext, and thus is not recommended except for instances such as using the command within a script. | |||
=== Import Database from File === | === Import Database from File === | ||
| Line 43: | Line 43: | ||
Optional Flags: | Optional Flags: | ||
* {{ ic |-h < | * {{ ic |-h <host>}} - Allows specification of host, such as for connecting to a remote server. | ||
* {{ ic |-P < | * {{ ic |-P <port>}} - Allows specification of port, such as for non-standard installations that don't use the default port {{ ic |3306}}. | ||
* {{ ic |<nowiki>--password=<value></nowiki>}} - Replaces the above {{ ic |-p}} flag. Allows specification of the user password in the command itself. Note that this means typing the password in plaintext, and thus is not recommended except for instances such as using the command within a script. | |||
* {{ ic |<nowiki>--column-statistics=0</nowiki>}} - Required if mysqldump gives an error about "column statistics". May or may not show up, depending on database setup. | |||
Latest revision as of 02:23, 7 October 2022
Note: Unless otherwise specified, all commands from this section assumes you're in the MySQL shell.
MySQL splits things up into individual "databases". Generally speaking, each database is a cohesive, separate entity.
General Commands
Show Databases
SHOW DATABASES;
Create a New Database
CREATE DATABASE <database_name>;
Specifying Character Set
Note: The following command should only be used if the database has a different character set from the server's default set. To set the server's character set, see MySQL Setup.
To both create and specify the database character set:
CREATE DATABASE <database_name> DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
Note that utf8mb4 is the preferred character set for most situations. See this link for details.
Load a Database
USE <database_name>;
Note that only one database can be selected/loaded at a time.
Delete a Database
Warn: Note that this action cannot be undone.
DROP DATABASE <database_name>;
Set Database Privileges
Database Backups
Export Database to File
mysqldump -u <username> -p <database_name> > <file_location>
Optional Flags:
-h <host>- Allows specification of host, such as for connecting to a remote server.-P <port>- Allows specification of port, such as for non-standard installations that don't use the default port3306.--password=<value>- Replaces the above-pflag. Allows specification of the user password in the command itself. Note that this means typing the password in plaintext, and thus is not recommended except for instances such as using the command within a script.
Import Database from File
mysql -u <username> -p <database_name> < <file_location>
Optional Flags:
-h <host>- Allows specification of host, such as for connecting to a remote server.-P <port>- Allows specification of port, such as for non-standard installations that don't use the default port3306.--password=<value>- Replaces the above-pflag. Allows specification of the user password in the command itself. Note that this means typing the password in plaintext, and thus is not recommended except for instances such as using the command within a script.--column-statistics=0- Required if mysqldump gives an error about "column statistics". May or may not show up, depending on database setup.