Sat, 01 Oct 2011
/Admin/databases/MySQL:
Cheat Sheet for the MySQL Command Line
phpmyadmin is a very handy GUI front-end for MySQL administration, but if
there is no web server installed on your MySQL server, it is non-trivial to
get phpmyadmin talking to it from a remote host. Especially if your remote
host is behind a dynamic IP. And of course, the command line is just plain
good for you. ;-) Here are some other cheat sheets for reference[1][2][3].
And here is mine:
Login to your mysql database on hostname as root (-h and -u are
optional, -p forces a password prompt):
mysql -h hostname -u root -p
Create a database:
mysql> create database [databasename];
Delete a database:
mysql> drop database [databasename];
List all databases:
mysql> show databases;
Switch to a database:
mysql> use [db name];
List the tables in the db:
mysql> show tables;
See a table's field formats:
mysql> describe [table name];
mysql> show columns from [table name];
Delete a db:
mysql> drop database [database name];
Delete a table:
mysql> drop table [table name];
Show data in a table:
mysql> SELECT * FROM [table name];
mysql> SELECT * FROM [table name] limit 10;
mysql> SELECT * FROM [table name] limit 100,10;
mysql> SELECT * FROM [table name] order by [col name] limit 10;
mysql> SELECT * FROM [table name] order by [col name] desc limit 10;
Counting the number of rows in a table:
mysql> SELECT COUNT(*) FROM pet;
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
Show rows where "field name" has the value "whatever":
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";
mysql> SELECT * FROM [table name] WHERE [field1] = "what1" AND [field2] = "what2";
Pattern Matching (% is wildcard for an arbitrary # of chars, _ is any single
character):
mysql> SELECT * FROM table WHERE rec LIKE "blah%";
mysql> SELECT * FROM table WHERE rec like "_____";
To insert records into a table:
INSERT into backup_run_log (start_time) VALUE (NOW());
To get the index of the most recently inserted row:
SELECT MAX(backup_run_log_id) FROM backup_run_log;
To change the contents of a table:
mysql> UPDATE table_name SET field1 = 'x',field2 = 'y',field3 = 'z' where fieldname = 'user';
mysql> UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");
mysql> UPDATE `mos2_content` SET introtext = replace(introtext,"{p}","") WHERE `title` REGEXP '-0';
To delete a row, for eg. to delete a user record from MySQL:
mysql> DELETE FROM mysql.user WHERE user='username' and host='localhost';
Column operations:
<![CDATA[
mysql> alter table [table name] drop column [column name];
mysql> alter table [table name] add column [new column name] varchar (20);
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes.
mysql> alter table [table name] add unique ([column name]);
Make a column bigger.
mysql> alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table.
mysql> alter table [table name] drop index [colmn name];
]]>
Dumping databases / tables to a (backup) file:
mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restoring a database from a dump:
mysql> create database databasename;
mysql> quit;
mysql -ppassword databasename < dump_databasename.sql
[1] http://www.tech-geeks.org/contrib/mdrone/mysql-stuff/mysql-cheatsheet.html
[2] http://www.pantz.org/software/mysql/mysqlcommands.html
[3] http://www.nparikh.org/unix/mysql.php
posted at: 09:50 | path: /Admin/databases/MySQL | permanent link to this entry
