MySQL Queries with Examples

  • DROP DATABASE databaseName-- Delete the database (irrecoverable!)

  • DROP DATABASE IF EXISTS databaseName-- Delete if it exists

  • CREATE DATABASE databaseName-- Create a new database

  • CREATE DATABASE IF NOT EXISTS databaseName-- Create only if it does not exists

  • SHOW DATABASES -- Show all the databases in this server

  • USE databaseName-- Set the default (current) database

  • SELECT DATABASE()-- Show the default database

  • SHOW CREATE DATABASE databaseName-- Show the CREATE DATABASE statement

  • DROP TABLE [IF EXISTS] tableName, ...

  • CREATE TABLE [IF NOT EXISTS] tableName (columnName columnType columnAttribute)

  • PRIMARY KEY(columnName)

  • FOREIGN KEY (columnName) REFERENCES tableName (columnName)

  • SHOW TABLES -- Show all the tables in the default database

  • DESCRIBE|DESC tableName-- Describe the details for a table

  • ALTER TABLE tableName ... -- Modify a table, e.g., ADD COLUMN and DROP COLUMN

  • ALTER TABLE tableName ADD columnDefinition

  • ALTER TABLE tableName DROP columnName

  • ALTER TABLE tableName ADD FOREIGN KEY (columnName) REFERENCES tableName (columnName)

  • ALTER TABLE tableName DROP FOREIGN KEY constraintName

  • SHOW CREATE TABLE tableName-- Show the CREATE TABLE statement for this tableName

  • INSERT INTO tableNameVALUES (column1Value, column2Value,...) -- Insert on all Columns

  • INSERT INTO tableNameVALUES (column1Value, column2Value,...), ... -- Insert multiple rows

  • INSERT INTO tableName (column1Name, ..., columnNName) VALUES (column1Value, ..., columnNValue) -- Insert on selected Columns

  • DELETE FROM tableName WHERE criteria

  • UPDATE tableName SET columnName = expr, ... WHERE criteria

  • SELECT * | column1Name AS alias1, ..., columnNName AS aliasN FROM tableName
    WHERE criteria GROUP BY columnName ORDER BY columnNameASC|DESC, ...HAVING groupConstraints LIMIT count | offset count


  • SHOW WARNINGS; -- Show the warnings of the previous statement


  • mysql> SHOW DATABASES;

  • Creating and Deleting a Database - CREATE DATABASE and DROP DATABASE

  • mysql> CREATE DATABASE southwind;

  • mysql> DROP DATABASE southwind;

  • mysql> CREATE DATABASE IF NOT EXISTS southwind;

  • mysql> DROP DATABASE IF EXISTS southwind;


  • mysql> SHOW CREATE DATABASE southwind\G

Setting the Default Database

  • mysql> USE southwind;

  • mysql> SELECT DATABASE(); -- Shows the current (default) database

  • mysql> SHOW TABLES; -- Shows all the tables in the current database.

Create the table "products"

    productCode  CHAR(3) NOT NULL DEFAULT '', 
    name VARCHAR(30) NOT NULL DEFAULT '', 
    price DECIMAL(7,2) NOT NULL DEFAULT 99999.99,
    PRIMARY KEY (productID)
  • mysql> SHOW TABLES; -- Shows all the tables to confirm that the "products" table has been created.

  • mysql> DESCRIBE products; -- Describe the fields (columns) of the "products" table.

  • mysql> SHOW CREATE TABLE products \G -- Show the complete CREATE TABLE statement used by MySQL to create this table.

  • mysql> INSERT INTO products VALUES (1001, 'PEN', 'Pen Red', 5000, 1.23);
    -- Inserts a row with all the column values

  • mysql> INSERT INTO products VALUES(NULL, 'PEN', 'Pen Blue', 8000, 1.25), (NULL, 'PEN', 'Pen Black', 2000, 1.25);
    -- Inserting NULL to the auto_increment column results in max_value + 1

  • mysql> INSERT INTO products (productCode, name, quantity, price) VALUES ('PEC', 'Pencil 2B', 10000, 0.48),('PEC', 'Pencil 2H', 8000, 0.49);
    -- Insert value to selected columns. -- Missing value for the auto_increment column also results in max_value + 1.

Missing columns get their default values:

  • mysql> INSERT INTO products (productCode, name) VALUES ('PEC', 'Pencil HB'); -- 2nd column (productCode) is defined to be NOT NULL

  • mysql> INSERT INTO products values (NULL, NULL, NULL, NULL, NULL);

  • mysql> SELECT * FROM products;
    -- Show all data from table

  • mysql> DELETE FROM products WHERE productID = 1006;
    -- Remove the specific row

  • mysql> SELECT name, price FROM products;
    -- List all rows for the specified columns