How to empty a MySQL database
Emptying a databases involves dropping (deleting) all its tables. The easiest way to do this is to drop and recreate a database:
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.
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.
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:
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);
mysqldump command is used to backup up MySQL databases. mysqldump has the following format:
mysqldump -u [login] -p [pass] [dbname] > [backup.sql]
where [backup.sql] is the file to which the backup should be written.
Backing up specific tables By default, mysqldump would backup the entire database. If you need to backup only specific tables, list the tables as follows: