MySQL

Delete (Drop) MySQL database

It is very easy to drop a MySQL database:

drop database database_name

Tons of blogs and articles online mention this command. Recently, however, I came across a database name which had spaces in them. For example:

company balances
electronics inventory
store employees
...

If I do:

drop database company balances

MySQL would complain that the databases company and balances do not exists. Naturally, the first thought on my mind was to enclose them the database.

MySQL Alter Table

Alter table command is used for altering tables, table fields and table indexes

To rename a table:

ALTER TABLE mytable RENAME yourtable;

To add a column:

ALTER TABLE mytable ADD COLUMN update TIMESTAMP FIRST
ALTER TABLE mytable ADD COLUMN update TIMESTAMP AFTER id
ALTER TABLE mytable ADD COLUMN update TIMESTAMP LAST

The first example create a column in the first position. The second command creates a column after the column id. The last example creates a column in the last position.

Listing all tables and their table counts in a MySQL database

The Following SQL query will list all tables in a MySQL database and also list the row counts for each.

SELECT TABLE_NAME, TABLE_ROWS 
FROM `information_schema`.`tables` 
WHERE `table_schema` = 'mydatabase';

where mydatabase is the name or your database. The output will like something like the following:

+------------+------------+
| table_name | table_rows |
+------------+------------+
| lines      |       2271 |
| links      |        484 |
| word       |      25004 |
+------------+------------+

MySQL cheat sheet

Selecting a database:

mysql> USE database;

Listing databases:

mysql> SHOW DATABASES;

Listing tables in a db:

mysql> SHOW TABLES;

Describing the format of a table:

mysql> DESCRIBE table;

Creating a database:

mysql> CREATE DATABASE db_name;

Creating a table:

mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE)); Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);

Pages