Create a database.
mysql> create database [databasename];
List all databases on the server.
mysql> SHOW databases;
Switch to a database.
mysql> use [db name];
To see all the tables in the db.
mysql> SHOW tables;
To see table's field formats.
mysql> describe [table name];
Show all data from a table.
mysql> SELECT * FROM [table name];
To return columns and column information.
mysql> SHOW columns from [table name];
Show particular rows with the given value.
mysql> SELECT * FROM [table name] WHERE [field name] = "value";
Show all records containing the name "Something" AND the phone number '0123456789'.
mysql> SELECT * FROM [table name] WHERE name = "Something" AND phone_number = '0123456789';
Show all records not containing the name "Something" AND the phone number '0123456789' order by the phone_number field.
mysql> SELECT * FROM [table name] WHERE name != "Something" AND phone_number = '0123456789' order by phone_number;
Show all records starting with the letters 'Something' AND the phone number '0123456789'.
mysql> SELECT * FROM [table name] WHERE name like "Something%" AND phone_number = '0123456789';
Show all records starting with letters 'Something' AND the phone number '0123456789' limit to records 1 through 5.
mysql> SELECT * FROM [table name] WHERE name like "Something%" AND phone_number = '0123456789' limit 1,5;
Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";
Show unique records.
mysql> SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc).
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Return number of rows.
mysql> SELECT COUNT(*) FROM [table name];
Sum column.
mysql> SELECT SUM(*) FROM [table name];
Show privileges for a specific MySQL user.
SHOW GRANTS FOR 'root'@'localhost';
Show user privileges for all MySQL users.
SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;
You could copy and paste each statement and execute each line to get a list. That is a bit fiddly and time consuming.
One way of achieving this more quickly would be to output the results to a file and then read the file back in line by line.
You can use OUTFILE and SOURCE to do this.
SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user INTO outfile '/tmp/show_grants.txt';
Now read the file you have just produced
SOURCE /tmp/show_grants.txt
To delete a db.
mysql> drop database [database name];
To delete a table.
mysql> drop table [table name];
SELECT @@character_set_server, @@collation_server, @@character_set_database, @@collation_database;
+------------------------+--------------------+--------------------------+----------------------+
| @@character_set_server | @@collation_server | @@character_set_database | @@collation_database |
+------------------------+--------------------+--------------------------+----------------------+
| latin1 | latin1_swedish_ci | latin1 | latin1_swedish_ci |
+------------------------+--------------------+--------------------------+----------------------+
1 row in set (0,03 sec)
SHOW TABLE STATUS;
+--------------------------------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------------------------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-------------------+----------+----------------+---------+
| additional_package_options | InnoDB | 10 | Dynamic | 17 | 963 | 16384 | 0 | 0 | 0 | NULL | NULL | 2021-10-08 07:34:41 | NULL | latin1_swedish_ci | NULL | | |
| additional_packages | InnoDB | 10 | Dynamic | 8 | 2048 | 16384 | 0 | 0 | 0 | NULL | NULL | 2021-10-08 07:34:41 | NULL | latin1_swedish_ci | NULL | | |
...
Bad: ALTER DATABASE <your-database-name> CHARACTER SET utf8 COLLATE utf8_general_ci;
Good: ALTER DATABASE <your-database-name> CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
# https://dba.stackexchange.com/questions/123572/convert-mysql-database-from-latin1-to-utf8mb4-and-take-care-of-german-umlauts
ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;