MySQL Examples for Beginners

MySQL Examples for Beginners

This basic MySQL query which will help beginners to learn.

-- Delete if it exists
mysql> DROP DATABASE IF EXISTS databaseName
-- Create only if it does not exists
mysql> CREATE DATABASE IF NOT EXISTS databaseName
-- Set the default (current) database
mysql> USE databaseName
-- Show the default database
mysql> SELECT DATABASE()
-- Show the CREATE DATABASE statement
mysql> SHOW CREATE DATABASE databaseName 

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

-- Create the table "products".
mysql> CREATE TABLE IF NOT EXISTS products (
productID bigint(11) 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 0000.00,
PRIMARY KEY (productID)
);

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

-- Show the complete CREATE TABLE statement used by MySQL to create this table
mysql> SHOW CREATE TABLE products \G
Note: The attribute "NOT NULL" specifies that the column cannot contain the NULL value. NULL is a special value indicating "no value", "unknown value" or "missing value".
AUTO_INCREMENT column results in max_value + 1

-- Insert a row with all the column values
mysql> INSERT INTO products VALUES (1001, 'Garments', 'T-Shirts', 5000, 1.23);
-- Insert multiple rows in one command
-- Inserting NULL to the auto_increment column results in max_value + 1
mysql> INSERT INTO products VALUES
(NULL, 'Garments', 'T-Shirts', 8000, 1.25),
(NULL, 'Garments', 'Pants', 2000, 1.25);

-- Insert commands for specific fields
Syntax: mysql> INSERT INTO table (column1, column2) VALUES ('','$id');
Example: mysql> INSERT INTO Employees (firstname, lastname, email) VALUES ('Sudhir', 'Pandey', 'shidhu047@gmail.com');

mysql> INSERT INTO blog SET title = 'SUdhir set', content = 'Sudhir testing set command'

-- Insert into table from csv file
mysql> LOAD DATA LOCAL INFILE 'd:/employee/employee_list.csv' INTO TABLE employees
COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; 

Querying the Database - SELECT
-- List all rows for the specified columns
mysql> SELECT name, price FROM products;
Note: * denotes ALL columns

-- Select command with where clause
mysql> SELECT * FROM employees WHERE employees_id=1003;
-- Select with Comparison Operators
mysql> SELECT name, quantity FROM products WHERE quantity <= 1500;
-- Select with use of DISTINCT. DISTINCT removes duplicate records
mysql> SELECT DISTINCT price AS `Distinct Price` FROM products;

IN, NOT IN
You can select from members of a set with IN (or NOT IN) operator.
mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black');

BETWEEN, NOT BETWEEN
To check if the value is within a range, you could use BETWEEN ... AND ... operator.
mysql> SELECT * FROM products 
WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000);

Pattern Matching - LIKE and NOT LIKE
'_' matches any single character; '%' matches any number of characters (including zero). For example,
'sud%' matches strings beginning with 'sud';
'%sud' matches strings ending with 'sud';
'%sud%' matches strings containing 'sud';
'___' matches strings containing exactly three characters; and
's_d%' matches strings beginning with 's', followed by any single character, followed by 'd', followed by zero or more characters.
-- "name" begins with 'Sud'

mysql> SELECT name, price FROM products WHERE name LIKE 'Sud%';

Update Query:
UPDATE employees SET employees_name='Sudhir' WHERE employees_id=1003;

Replace Query:
SELECT name, REPLACE(name,'ee','i') FROM employees;
UPDATE urls SET url = REPLACE(url, '192.168.103.213/images/', 'google.com/images/');

Delete Query:
DELETE FROM employees WHERE employees_id=1003;
DELETE FROM employees WHERE employees_id IN (1003,1008);


ORDER BY Clause
You can order the rows selected using ORDER BY clause
SELECT * FROM products order by name desc

LIMIT Clause
Use the LIMIT clause to limit the number of rows displayed
-- Display the first two rows
mysql> SELECT * FROM products ORDER BY price LIMIT 2;
-- Skip the first two rows and display the next 1 row
mysql> SELECT * FROM products ORDER BY price LIMIT 2, 1;

ALTER TABLE
mysql> ALTER TABLE products ADD COLUMN supplierID INT UNSIGNED NOT NULL;
-- Add a foreign key constrain
mysql> ALTER TABLE products ADD FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID);
-- Drop COLUMN
mysql> ALTER TABLE products DROP supplierName


Modify Table
alter table table_name modify column_name int(5)

You can also use this:

ALTER TABLE [tablename] CHANGE [columnName] [columnName] DECIMAL (10,2)
Example: Alter table employees change id id BIGINT(20);

GROUP BY
The GROUP BY clause returns one row for each group.It is used to group rows that have the same values. We often use the GROUP BY clause with aggregate functions such as SUM, AVG, MAX, MIN, and COUNT.
For example, if we want to know how many orders in each status
SELECT count(orderNumber) as Cnt , status FROM orders group by status;
Output
Cnt status
6 Cancelled
3 Disputed
6 In Process
10 Shipped

MySQL GROUP BY with expression example
SELECT count(orderNumber) as Cnt, Year(orderDate) as year
FROM orders group by Year(orderDate);
Output
Cnt year
111 2003
151 2004
64 2005

MySQL GROUP BY with HAVING clause
To filter the groups returned by GROUP BY clause, we use HAVING clause.

Example: SELECT YEAR(orderDate) AS year, SUM(quantityOrdered * priceEach) AS total
FROM orders INNER JOIN orderdetails USING (orderNumber)
WHERE status = 'Shipped' GROUP BY year HAVING year > 2003;

GROUP_CONCAT: GROUP_CONCAT function concatenates strings from a group into a single string with various options.
SELECT GROUP_CONCAT(DISTINCT v ORDER BY v ASC SEPARATOR ';') FROM t;
Each customer has one or more sale representatives.

SELECT employeeNumber, firstName, lastName, GROUP_CONCAT(DISTINCT customername ORDER BY customerName)
FROM employees INNER JOIN customers ON customers.salesRepEmployeeNumber = employeeNumber
GROUP BY employeeNumber ORDER BY firstName , lastname;

List suppliers for each part:

SELECT partID,GROUP_CONCAT(supID ORDER BY supID) AS Suppliers 
FROM supparts GROUP BY partID;

JOIN

INNER JOIN: Returns all rows when there is at least one match in BOTH tables.INNER JOIN would return the records where table1 and table2 intersect.
Syntax:
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Example:
mysql > SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table.It would return the all records from table1 and only those records from table2 that intersect with table1.
Syntax:
SELECT columns FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
Example:
mysql > SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers Left JOIN orders ON suppliers.supplier_id = orders.supplier_id;

RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table.It would return the all records from table2 and only those records from table1 that intersect with table2.
Syntax:
SELECT columns FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
Example:
mysql > SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers RIGHT JOIN orders ON suppliers.supplier_id = orders.supplier_id;