Skip to content

MySQL Quick Reference Commands

Note

Applies to variants of MySQL, MariaDB and Percona.

Starting & Stopping

Note

It is assumed the following command examples are executed in the Linux shell or terminal.

Task Command Example Notes
Start MySQL Daemon systemctl start mariadb.service Systemd supported operating systems.
Start MySQL Daemon /usr/local/bin/safe_mysqld & For OpenBSD
Start MySQL Daemon /usr/local/share/mysql.server start For OpenBSD
Start MySQL Daemon /etc/init.d/mysql start For Debian as of v6 (Squeeze).
Stop MySQL Daemon systemctl stop mariadb.service Systemd supported operating systems.
Stop MySQL Daemon /usr/local/share/mysql.server stop For OpenBSD
Stop MySQL Daemon /usr/local/bin/mysqladmin -p shutdown For Debian as of v6 (Squeeze), For OpenBSD /usr/bin/mysqladmin.
Stop MySQL Daemon /etc/init.d/mysql stop For Debian as of v6 (Squeeze).
Restart MySQL Daemon /etc/init.d/mysql restart For Debian as of v6 (Squeeze).
Restart MySQL Daemon systemctl restart mariadb.service Systemd supported operating systems.
MySQL Daemon Status systemctl status mariadb.service Systemd supported operating systems.
MySQL Daemon Status /etc/init.d/mysql status For Debian as of v6 (Squeeze).

User Administration

Task Command Example Notes
Change Password SET PASSWORD FOR 'user-name-here'@'hostname' = PASSWORD('new-password'); For version <= 5.7.5.
Change Password ALTER USER 'user'@'hostname' IDENTIFIED BY 'newPass'; For version >= 5.7.6.
Delete User DROP USER 'usernameHere'@'hostnameHere';
Delete User DROP USER 'usernameHere'@'%'; The "%" is a wildcard to represent any hostname.
Display Grants SHOW GRANTS FOR 'username'@'hostnameHere';
Display Grants SHOW GRANTS FOR 'username'@'%';
Grant Privileges GRANT select, insert ON dbname.tblname TO username@hostname IDENTIFIED BY 'password';
Grant Privileges GRANT ALL ON dbname TO username@'%' IDENTIFIED BY 'password';
Grant Privileges GRANT file ON *.* TO username@'%' IDENTIFIED BY 'password';
Reset (reload) privileges FLUSH privileges;
Revoke privileges REVOKE select ON dbname.* FROM username@hostname;
Revoke all privileges REVOKE ALL PRIVILEGES, GRANT OPTION FROM username@hostname;

Alter

Task Command Example Notes
Add column ALTER table tblname ADD column-name(40) AFTER column-name;
Add column generated ALTER TABLE t1 ADD COLUMN column-name2 INT GENERATED ALWAYS AS (column-name1 + 1) STORED; More Info
Add Constraint ALTER TABLE tblname ADD CONSTRAINT 'constraint-name' FOREIGN KEY ('column-name') REFERENCES 'foreign-tblname' ('id') ON DELETE CASCADE ON UPDATE CASCADE;
Add index ALTER table tblname ADD INDEX index-name (index-column-name);
Change Table Column ALTER TABLE tblname MODIFY column-name smallint(3) unsigned NOT NULL DEFAULT 1834; Change column data type, null property or default value.
Change schema ALTER table tblname CHANGE column-name column-name varchar(10);
Change primary key ALTER TABLE tblname DROP PRIMARY KEY, ADD PRIMARY KEY (col1, col2);
Delete column ALTER table tblname DROP column-name;
Delete Foreign Key ALTER TABLE tblname DROP FOREIGN KEY constraint-name;
Delete index ALTER TABLE tblname DROP INDEX index_name

Create

Task Command Example Notes
Create database CREATE database dbname;
Create table CREATE table dbname(id int NOT Null auto_increment primary key, model varchar(15));
Create table CREATE TEMPORARY table tbl_name LIKE old_tbl_name; A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed.

Delete

Task Command Example Notes
Delete column ALTER table tblname DROP column-name; Display all databases size in megabytes.
Delete data field DELETE FROM tblname WHERE column=37;
Delete Foreign Key ALTER TABLE tblname DROP FOREIGN KEY foreign-key-name;
Delete table DROP TABLE tblname;
Delete table DROP TABLE IF EXISTS tblname; Delete a table if it exists.
Delete index ALTER TABLE tblname DROP INDEX index_name;
Delete database DROP DATABASE dbname;
Delete with Join delete pc from programs_credits as pc left outer join programs as p on pc.programs_id = p.id where p.id is null Delete all records not associated to a record within programs. Removes orphan records.

Export Data

Task Command Example Notes
Export to a file SELECT FROM tblname INTO OUTFILE 'tabledata.txt';
Export to a file SELECT column-name1, column-name2, column-name3 FROM tblname INTO OUTFILE 'tabledata.txt';
Export DB/Table(s) to SQL mysqldump -vu username -p dbname > dbname-all-tbls.sql Command issued via shell or terminal.
Export DB/Table(s) to SQL mysqldump -vu username -p dbname tblname > tblname.sql Command issued via shell or terminal.
Export DB/Table to SQL with Query mysqldump -u username -p database table --where="export_id=156" > data.sql Command issued via shell or terminal.
Export DB/Table to SQL Data Only mysqldump -u username -p database table --no-create-info --where="export_id=156" > data.sql Command issued via shell or terminal.
Export DB/Table(s) to SQL mysqldump --add-drop-table --comments --create-options --dump-date --complete-insert --extended-insert --ignore-table=dbname.dbtable --password dbname > data.sql Command issued via shell or terminal.

Import Data

Task Command Example Notes
Import from file mysql -u root -p example< /home/username/data.sql
Import from file LOAD DATA INFILE 'data.txt' INTO TABLE tblname FIELDS TERMINATED BY ',';
Import from file LOAD DATA INFILE 'data.txt' INTO TABLE tblname FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n' (col1,col2,col3);
Import from file LOAD DATA INFILE '/var/tmp/data.txt' INTO TABLE channels_temp FIELDS TERMINATED BY '<nowiki>|</nowiki>' LINES TERMINATED BY '\r\n' (parent_id,channels_id,channel_types_id,call_sign,name,@languages_id,created) SET created = now(), languages_id = (SELECT id FROM languages WHERE english_name=@languages_id)
Import from file LOAD DATA INFILE '/home/username/data.csv' INTO TABLE tblname FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (id,alt_id,alt_name,@id,created) SET created = now(), id = (SELECT id FROM tblname2 WHERE name=@id)

Insert

Task Command Example Notes
Insert table field data INSERT into tblname(column-name) VALUES('foo');
Insert table field data with SELECT INSERT INTO tblname(column-name) SELECT column-name FROM tblname WHERE column-name > 100;
Insert / Update table INSERT INTO 'table' ('name', 'created', 'modified') VALUES ('Jamie', NOW(), NOW()) ON DUPLICATE KEY UPDATE 'name' = 'Jamie', 'modified' = NOW() Insert new record if duplicate key found update record instead.

Select

Task Command Example Notes
Add Date/Time SELECT date_add(t1.start_time, INTERVAL t1.run_time MINUTE) AS stop_time,t1.run_time FROM programs_schedules AS t1 INNER JOIN programs AS t2 ON t1.programs_id=t2.id WHERE t1.channels_id=5370
Database Size SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema
Display table content SELECT * FROM tblname;
Display table content by order SELECT * FROM tblname ORDER BY column-name;
Display table content (search) SELECT * FROM tblname WHERE column-name LIKE '%foo%';
Event Last Ran SELECT * FROM INFORMATION_SCHEMA.events;
Export to a file SELECT FROM tblname INTO OUTFILE 'tabledata.txt';
Export to a file SELECT column-name1, column-name2, column-name3 FROM tblname INTO OUTFILE 'tabledata.txt';
Select database USE dbname;
Select with date diff (days) SELECT id, TO_DAYS(now()) - TO_DAYS(created) FROM tblname WHERE TO_DAYS(now()) - TO_DAYS(column-name) > 7
Select with date diff (days) SELECT datediff(now(),column-name) FROM tblname
Select with Table Alias SELECT table1.column_name, table2.column_name FROM tblname AS tabel1, people AS table2 WHERE table1.column_name = table2.column_name
Select Equal/Inner Join SELECT People.First_Name, People.Last_Name FROM Company INNER JOIN CompanyRep ON Company.Company_ID = CompanyRep.Sales_Manager_ID INNER JOIN People ON People.Company_ID=Company.Company_ID WHERE CompanyRep.Company_ID=851
Select Left Join SELECT PeopleMailing.Mail_Addr1, PeopleMailing.Mail_City, Company.Mail_Addr1 FROM Company, People LEFT OUTER JOIN PeopleMailing ON People.People_ID=PeopleMailing.People_ID WHERE People.Company_ID=Company.Company_ID AND People.People_ID=100
Select multiple left joins SELECT stb_programs_credits_temp.'programs_id', stb_programs_credits_temp.'fyi_programs_credits_names_id', stb_programs_credits_temp.'fyi_programs_id', stb_programs_credits_temp.'programs_credits_names_id', stb_programs_credits_temp.'character_name', stb_programs_credits_temp.'role_priority', stb_programs_credits_temp.'programs_roles_id', stb_programs_credits_temp.'main_cast', stb_programs_credits_temp.'created' FROM stb_programs_credits_temp LEFT OUTER JOIN stb_programs_credits ON (stb_programs_credits_temp.fyi_programs_id = stb_programs_credits.fyi_programs_id AND stb_programs_credits_temp.fyi_programs_credits_names_id = stb_programs_credits.fyi_programs_credits_names_id AND stb_programs_credits_temp.programs_roles_id = stb_programs_credits.programs_roles_id AND stb_programs_credits_temp.character_name = stb_programs_credits.character_name) WHERE stb_programs_credits.fyi_programs_id IS NULL
Select Distinct Records SELECT DISTINCT id FROM tblname
Select Not Distinct Records SELECT channels_id,count(*) FROM stb_media_files GROUP BY 1 HAVING count(*) > 1 This returns the opposite of using "distinct".
Select Duplicate Records SELECT COUNT(*) as qty,fyi_programs_id,title80 FROM stb_programs GROUP BY fyi_programs_id HAVING qty > 1 ORDER BY title80 ASC
Select Total Records All and Distinct SELECT COUNT(*), COUNT(DISTINCT fyi_programs_id, fyi_programs_credits_names_id) FROM stb_programs_credits Query returns total rows along with total unique rows by criteria.
Select Minimum Value SELECT min(start_time) FROM stb_programs_schedules Returns only one row with the smallest column value found.
Select Maximum Value SELECT max(start_time) FROM stb_programs_schedules Returns only one row with the largest column value found.
Select Table Details SELECT Table_Name, Engine, Table_Collation, Table_Comment FROM TABLES WHERE Table_Schema="tblname" Overview of all database tables from information_schema.
Select Table Column Details SELECT Table_Name, Column_Name, Column_Type, Is_Nullable, Column_Default, Column_Key, Extra, Character_Set_Name, Collation_Name, Column_Comment FROM columns WHERE Table_Schema="tblname" Overview of all database table columns from information_schema.
Select Table Indexes SELECT table_name AS 'Table', index_name AS 'Index', GROUP_CONCAT(column_name ORDER BY seq_in_index) AS 'Columns' FROM information_schema.statistics WHERE table_schema = 'DSI' AND Table_Name NOT LIKE "%_temp" GROUP BY 1,2; List all indexes within database schema. This will work prior to MySQL GA 5.6 and Percona Server 5.5. Learn More
Select Table Indexes SELECT t.name AS 'Table', i.name AS 'Index', GROUP_CONCAT(f.name ORDER BY f.pos) AS 'Columns' FROM information_schema.innodb_sys_tables t JOIN information_schema.innodb_sys_indexes i USING (table_id) JOIN information_schema.innodb_sys_fields f USING (index_id) WHERE t.schema = 'sakila' GROUP BY 1,2; List all indexes within database schema. This only works in MySQL 5.6 or Percona Server 5.5 and only shows InnoDB tables. Learn More
Select Foreign Keys SELECT table_name, column_name, referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE referenced_table_name is not null; AND table_schema = 'tblname' ORDER BY table_name ASC Lists all foreign keys within a database schema. Learn More
Table Size SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "schema_name" Display database size in detail by tables in megabytes.

Show

Task Command Example Notes
Display available tables SHOW tables;
Display available databases SHOW databases;
Display Event Manager Events SHOW events;
Display Event Manager Create Event SHOW create event evet-name-here;
Display InnoDB Status show engine innodb status Read More
Display table schema DESCRIBE tblname; or DESC tblname; or EXPLAIN tblname;
Display table indexes SHOW index FROM tblname;
Display table content SELECT * FROM tblname;
Display Triggers show triggers;
Display server status information SHOW status;
Display table status information SHOW table status;
Display MySQL system variables SHOW variables;
Display MySQL threads running SHOW processlist;
Display MySQL threads running MYSQLADMIN -u username -p processlist Command issued via shell or terminal.
Event List show events;
Event Show Create show create event event-name;
Procedure Create Show show create procedure procedure-name;
Procedure List show procedure status where db = 'dbname';

Set

Task Command Example Notes
Foreign Key Checks On SET FOREIGN_KEY_CHECKS=0;
Foreign Key Checks Off SET FOREIGN_KEY_CHECKS=1;
Increment Variable SET @datetime:=CAST('2015-02-11 07:14:00' as DATETIME); SELECT @datetime:=date_add(@datetime, INTERVAL 1 DAY) AS day_datetime
Logging On SET global general_log = 1;
Logging Off SET global general_log = 0;

Update

Task Command Example Notes
Encrypt UPDATE tblname SET column-name1=encrypt('2000-7-5') WHERE column-name2=36;
Update table UPDATE tblname SET column-name='2000-7-5' WHERE column-name=36;
Update table UPDATE tblname SET column-name1 = Left(column-name2,1) WHERE column-name LIKE '9;
Update table UPDATE tblname SET column-name1 = Right(column-name2,5);
Update table UPDATE tblname SET column-name = concat('0000',column-name);
Update table UPDATE People SET Email = CONCAT( SUBSTRING_INDEX(Email, '@', 1), '@example.com' ) WHERE Email LIKE '%example.com%';
Update table join UPDATE tblname1 AS t1 JOIN tblname2 AS t2 ON t1.name=t2.name SET t1.column-name = t2.column-name WHERE t1.column-name != t2.column-name
Update table join select UPDATE tblname1 AS t1, (SELECT column-name1, column-name2 AS foo FROM tblname2 AS p1 WHERE p1.column-name=4 AND p1.column-name != '') AS t2 SET t1.column-name = t2.foo WHERE t1.column-name=4 AND t1.column-name != '' AND t1.column-name=t2.column-name
Update multi-table UPDATE provinces_states, countries SET 'ISO_3166-2' = concat(countries.'ISO_3166-1-alpha-2','-',provinces_states.alpha2) WHERE provinces_states.countries_id=countries.id