MySQL Commands

Click on the headings to sort

CommandCommon Arguments & DescriptionCommand Description
ALTER TABLEproduct ADD FOREIGN KEY (cus_id) REFERENCES customer(cus_id); (Adds a foreign key "cus_id" that references the column "cus_id" in the table "customer")Add a foreign key to a table
ALTER TABLEproduct AUTO_INCREMENT = 1; (After deleting records, reset the next auto increment value to current largest value in the auto increment column + 1.)Reset an auto_increment column
ALTER TABLEproduct ADD model VARCHAR(30) AFTER name; (Adds a new column in the "product" table called "model" after the column called "name")Add a column to a table
ALTER TABLEproduct CHANGE cus customer VARCHAR(20); (Changes the column name "cus" in the table "product" to "customer")Rename a column
ALTER TABLEproduct DROP COLUMN name; (Drops the column "name" in the table "product")Drop a column
ALTER TABLEproduct DROP FOREIGN KEY product_ibfk_2; (Drops the foreign key "product_ibfk_2" for table "product")Drop a foreign key
ALTER TABLEproduct RENAME software_product; (Renames the table "product" to "software_product")Rename a table
CREATE DATABASEbusinessdata; (Creates a database called "businessdata")Create a database
CREATE USER'lily'@'localhost' IDENTIFIED BY 'Passw0rd'; (Creates a user called lily, able to connect from localhost with password Passw0rd)Create a user
DELETE FROMproduct WHERE name = 'some name'; (Deletes a row from the table "product" where the column "name" has a value of "some name")Deletes a row(s) from a table
DESCRIBEcustomer; (Describes the "customer" table)Display information on the columns of a table
DROP TABLEcustomers; (Removes the table called "customers")Remove a table and its data
GRANT ALL PRIVILEGES ONbig_db.* to 'lily'@'localhost' identified by 'Passw0rd'; (Grant all privileges for all objects (*) in database big_db to the user lily connecting from localhost using password Passw0rd)Grant privileges to user
INSERTINTO customers(name, phone) values('Big Company','0238473923'); (Insert into the table "customers" in the columns "name" and "phone" the values "Big Company" and "0238473923")Insert data into a table
mysql-uroot -ppassw0rd < all_databases.sql (Restore all databases previously backed up using mysqldump)Restore mysql databases
mysqlcheck-u root -p --auto-repair --check --optimize --all-databases (Checks, repairs and optimizes all databases, useful after a database crash. Will prompt for the password of the mysql user specified.)Repair databases
mysqldump-uroot -ppassw0rd --all-databases > all_databases.sql (Dump all databases for backup or for transferring to another database server. The dump contains SQL statements to create the database, tables and/or populate the tables)Dump mysql databases
SELECT* from customers; (Retrieves all rows from the table "customers")Retrieves rows from one or more tables
SHOW DATABASES;Without arguments show all databasesLists databases on the MySQL server host
SHOW GRANTS;for 'lily'@'%'; (Displays the privileges for the user lily connecting from any server (%))Display user privileges
SHOW TABLES;Without arguments show all non-temporary tables in the current databaseLists all non-temporary tables in a given database
TRUNCATE TABLEproducts; (Deletes all rows from the table "products" to leave the table empty)Deletes all data from a table
UPDATEproducts set item="Bread" where id="23"; (Update the table "products", set the record in the "item" column to the new value "Bread" where the "id" column has the value "23")Update the value of an existing record
USEtest; (Select database named "test" to use)Use selected database as the default and current database for subsequent statements