MySQL Queries with Examples

Table Syntax

• 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

Row Syntax

• 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

>Others:

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

Create Database Examples

SHOW DATABASES:

• 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;

SHOW CREATE DATABASE:

• 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 Table Examples

Create the table "products"

• CREATE TABLE IF NOT EXISTS products (
  productID INT UNSIGNED NOT NULL AUTO_INCREMENT,
  productCode  CHAR(3) NOT NULL DEFAULT '', 
  name VARCHAR(30) NOT NULL DEFAULT '', 
  quantity INT UNSIGNED NOT NULL DEFAULT 0,
  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.

Inserting Rows Examples

• 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);

Show Table Examples

• 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