MySQL Commands Cheat Sheet
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), |
| 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’); |
HOW TO: In PHP the MySQL Client API version doesn’t match the MySQL Server version
WordPress DB Hacks: Determining the ID of a category parents
MySQL Founder Resigns from Sun over Quality of MySQL 5.1
About Author
Hey,
Thanks man! This is sweet.
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/