MySQL commands

A list of handy, often used MySQL commands.

MySQL Basic Commands

To login as database super admin:
# /bin/mysql -u root -p

To login as specific database admin:
# /bin/mysql -u <db_user> -p <db_name>

Create a database on the mysql server:
mysql> create database <db_name>;

Create an admin user for the database:
mysql> GRANT ALL PRIVILEGES ON <db_name>.* to <db_user>@localhost IDENTIFIED BY '<password>';

List all databases on the mysql server:
mysql> show databases;

Switch to a database:
mysql> use <db_name>;

Show all the tables in the mysql database.
mysql> show tables;

Show all the database’s field formats:
mysql> describe <table_name>;

To delete a db:
mysql> drop database <db_name>;

To delete a table:
mysql> drop table <table_name>;

Show all data in a table:
mysql> SELECT * FROM <table_name>;

Recover the MySQL root password:
# /etc/rc.d/init.d/mysqld stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where user='root';
mysql> flush privileges;
mysql> quit
# /etc/rc.d/init.d/mysqld stop
# /etc/rc.d/init.d/mysqld start

MySQL Query Commands

Some mysql query (select) examples.

Shows the columns field formats.
mysql> show columns from <table_name>;

Show rows with the value “foobar”.
mysql> SELECT * FROM <table_name> WHERE <field_name> = "foobar";

Show all records named “Devil” AND have id-number ‘666666’.
mysql> SELECT * FROM <table_name> WHERE name = "Devil" AND id_number = '666';

Show all records not named “Devil” AND have id-number ‘777’ ordered by id-number.
mysql> SELECT * FROM <table_name> WHERE name != "Devil" AND id_number = '777' order by id_number;

Show all records starting with the letters ‘mac’ AND the id-number ‘555’.
mysql> SELECT * FROM <table_name> WHERE name like "mac%" AND id_number = '555';

Show all records starting with the letters ‘mac’ AND the id-number ‘555’ limit to records 5 through 15.
mysql> SELECT * FROM <table_name> WHERE name like "mac%" AND id_number = '555' limit 5,15;

Example How to use a regular expression to find records:
Show all records beginning with 555.
mysql> SELECT * FROM <table_name> WHERE <column_name> RLIKE "^555";

Show unique records:
mysql> SELECT DISTINCT <column_name> FROM <table_name>;

Show selected records sorted descending (desc) (or use ascending (asc)).
mysql> SELECT * FROM <table_name> ORDER BY <column_name> DESC;

Return number of rows:
mysql> SELECT COUNT(*) FROM <table_name>;

Sum column:
mysql> SELECT SUM(*) FROM <table_name>;

MySQL Update query

To update info already in a table.
mysql> UPDATE <table_name> SET <column_name> = 'value',<column2_name> = 'other_value', WHERE <field_name> = 'uniq-id';

MySQL create query

Example query:
`field_1` CHAR(20) DEFAULT NULL,
`field_2` INT(10) DEFAULT NULL,
`field_3` BIGINT(20),
PRIMARY KEY ( field_id )

MySQL insert query

Insert data into the database:
INSERT INTO foobar(field_id, field_1, field_2, field_3) VALUES('', 'foobardibarfoobarfoo', '1234543210', '555999123423563465831');

MySQL delete and alter queries

Delete a row(s) from a table.
mysql> DELETE from <table_name> where <field_name> = 'value';

Delete a column:
mysql> alter table <table_name> drop column <column_name>;

Add a new column to mysql database:
mysql> alter table <table_name> add column <column_name> char (5);

Change mysql column name:
mysql> alter table <table_name> change <old_column_name> ;new_column_name> char (6);

Make a unique column so you get no duplicates.
mysql> alter table <table_name> add unique (<column_name>);

Make a column bigger:
mysql> alter table <table_name> modify <column_name> char (7);

Delete unique from table.
mysql> alter table <table_name> drop index <column_name>;

MySQL Backup/Restore

Backup all mysql databases:
# /bin/mysqldump -u root -ppassword --opt >/root/all_ur_databases.sql

Backup a single mysql database:
# /bin/mysqldump -u <db_user> -ppassword --databases <db_name> >/root/single_database.sql

Backup a single table from a mysql database:
# /bin/mysqldump -u <db_user> -ppassword --databases <db_name> <table_name> >/root/single_table_from_database.sql

Restore mysql database:
# /bin/mysql -u <db_user> -ppassword <db_name> < /root/single_database.sql

Restore mysql database, single table:
# /bin/mysql -u <db_user> -ppassword <db_name> <table_name> < /root/single_table_from_database.sql

Leave a Reply

}) _0- O-) ;) ;( :{ :o :Y :X :P :N :D :@ :? :9~ :) :(