MySQL Commands
Click on the headings to sort
| Command | Common Arguments & Description | Command Description |
|---|---|---|
| ALTER TABLE | product 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 TABLE | product 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 TABLE | product 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 TABLE | product CHANGE cus customer VARCHAR(20); (Changes the column name "cus" in the table "product" to "customer") | Rename a column |
| ALTER TABLE | product DROP COLUMN name; (Drops the column "name" in the table "product") | Drop a column |
| ALTER TABLE | product DROP FOREIGN KEY product_ibfk_2; (Drops the foreign key "product_ibfk_2" for table "product") | Drop a foreign key |
| ALTER TABLE | product RENAME software_product; (Renames the table "product" to "software_product") | Rename a table |
| CREATE DATABASE | businessdata; (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 FROM | product 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 |
| DESCRIBE | customer; (Describes the "customer" table) | Display information on the columns of a table |
| DROP TABLE | customers; (Removes the table called "customers") | Remove a table and its data |
| GRANT ALL PRIVILEGES ON | big_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 |
| INSERT | INTO 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 databases | Lists 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 database | Lists all non-temporary tables in a given database |
| TRUNCATE TABLE | products; (Deletes all rows from the table "products" to leave the table empty) | Deletes all data from a table |
| UPDATE | products 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 |
| USE | test; (Select database named "test" to use) | Use selected database as the default and current database for subsequent statements |