Emptying a databases involves dropping (deleting) all its tables. The easiest way to do this is to drop and recreate a 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.
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.
In the following query, we list all lastname held by 2 or more employees.
SELECT lastname, COUNT(*) c FROM employees GROUP BY lastname HAVING c > 1;
If you don't want a value to unique, you should add a unique constraint to the column. However, you would from time to time have to deal with a badly designed database.
Character: set of symbols and encodings Encoding: set of values assigned to characters Collation: set of rule for comparing a character set
Suppose you have the following characters,
M Y S Q L
that are assigned the following values:
13 25 19 17 12
sequentially. These numbers happen to be their order in the English alphabet. So A = 1, B = 2 and so on.
Recently, I had to copy a large database from one server to another. I had 100G free on the source server. Unfortunately, the server would run out of memory before the mysqldump would complete. So I had to find a way to make the mysqldumps smaller. This can be done by creating separate dumps for each table. Then I had to compress the files to conserve memory. Following is the script I wrote to accomplish this.
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 | +------------+------------+
If you have installed MySQL on your Mac and forgot your root password, these instructions are for you.
If a string contains a single quote, it will generate an error. mysqli->real_escape_string() escapes quotes for MySQL. See example:
Let's begin with some concepts: