Home
MySQL Commands Cheat Sheet

MySQL Commands Cheat Sheet

By April 19, 2006 MySQL 2 Comments

I wanted to get a list of the tables in my MySQL database a couple weeks ago. I bookmarked on place that had a list of commands. I’m including that info here for furture reference. I’ve found dozens of copies of that list elsewhere, so I’m not gonna link to any particular site.

Check back here periodically as I add more and more commands here. There are already 5 good ones that I need to put on later tonight…but I’ve got a hot date with my wife that takes priority!

Common MySQL Commands

Description Command
Administrative
To login as root (from shell) mysql -uroot -p[password]
Create a database create database [db name];
Grant all permissions for a database to a user grant all privileges on [db name].* to ‘[user]’@’localhost’ identified by ‘[user password]’;
List all databases on the sql server. show databases;
Switch to a database. use [db name];
To see all the tables in the db. show tables;
To see database’s field formats. describe [table name];
To delete a database drop database [db name];
Dump all databases for backup.Backup file is sql commands to recreate all db’s. mysqldump --user=root --password=blah --all-databases

>/tmp/sql-01_backup.sql
Exit mysql command line quit
Queries (SELECTS)
Show all data in a table. SELECT * FROM [table name];
Count rows. SELECT COUNT(*) FROM [table name];
Show certain selected rows with the value “whatever”. SELECT * FROM [table name] WHERE [field name] = “whatever”;
Show all records containing the name “Bob” AND the phone number ‘3444444’. SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ‘3444444’;
Show all records not containing the name “Bob” AND the phone number ‘3444444’ order by the

phone_number field.

 SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ‘3444444’ order by

phone_number;

Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’. SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444’;
Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This

finds any record beginning with a.

SELECT * FROM [table name] WHERE rec RLIKE “^a$”;
Show unique records. SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc). SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Join tables on common columns. select lookup.illustrationid, lookup.personid,person.birthday from lookup
left join person on lookup.personid=person.personid=statement to join birthday in person table with primary

illustration id;
User Management
Switch to the mysql db. Create a new user. INSERT INTO [table name] (Host,User,Password) VALUES(‘%’,’user’,PASSWORD(‘password’));
Change a users password.(from unix shell). [mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password

‘new-password’
Change a users password.(from MySQL prompt). SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
Switch to mysql db.Give user privilages for a db. INSERT INTO [table name]

(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES

(‘%’,’db’,’user’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
Update database permissions/privilages. FLUSH PRIVILEGES;
Table Alteration
To delete a table. drop table [table name];
Returns the columns and column information pertaining to the designated table. show columns from [table name];
To update info already in a table. UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where

[field name] = ‘user’;
Delete a row(s) from a table. DELETE from [table name] where [field name] = ‘whatever’;
Delete a column. alter table [table name] drop column [column name];
Add a new column to db. alter table [table name] add column [new column name] varchar (20);
Change column name. alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes. alter table [table name] add unique ([column name]);
Make a column bigger. alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table. alter table [table name] drop index [colmn name];
Load a CSV file into a table. LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES

TERMINATED BY ‘\n’ (field1,field2,field3);
Create Table
Example 1.		 CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname

VARCHAR(35),suffix VARCHAR(3),
officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups
VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create Table
Example 2.		 create table [table name] (personid int(50) not null auto_increment primary key,firstname

varchar(35),middlename varchar(50),lastname varchar(50) default ‘bato’);

Related Posts

About Author

Capt. Queeg

2 Comments

  1. JAll

    Hey,

    Thanks man! This is sweet.

  2. Ronnie Charrier

    This is fantastic! Thanks for this. Another resource readers might find useful is this tutorial: https://www.libertycenterone.com/blog/the-mysql-cheat-sheet-part-one-seven-useful-tidbits-that-everyone-seems-to-forget/

Add a Comment

Your email address will not be published. Required fields are marked *