Basic keywords
- USE=> keyword used to select Database
- - -used to comment out the following line of query
- SELECT => select database
- WHERE => Add Condition [ <>, != both same, not equalto][= equalto]
- more conditions can be added by AND,OR keyword and NOT can reverse conditions.
- ORDER BY => sort result ASC or DESC based on the particular column.
- for order by column need to stay in table, even not selected by SELECT can be used. Also, it can be alias/expression such as “quantity*time ASC”
USE sql_store
SELECT *
FROM customers
--WHERE customer_id=1
ORDER BY first_name ASC;- SELECT 1,2 => returns 1,2 as answer
- AS keyword use to determine the alias
- The new column can be added easily like “discount_factor” in SQL
SELECT last_name, first_name, (points, points+10)*10 AS'discount factor'
FROM customers- DISTINCT => used to omit duplicates
SELECT DISTINCT state FROM customers- IN => multiple or replacement
select * from customers where state =’va’ or state=’ga’ or state =’ca’select * from customers where state in(’va’,’ga’,’ca’)select * from customers where state NOT IN(’va’,’ga’,’ca’)
Between => used for range
LIKE => used for matching pattern
- LIKE ‘good%’ — starting with good word.
- % = n number of char, _ = 1 char.
REGEXP in MySQL (basic)
^ => starts with ‘^field’
$=> ends with ‘field$’
|=> multiple words (or) = ‘field| mac’
[gim]e => before letter e, there must be g ,i, or m
e[fmq] => after letter e , there must be f , m , or q
[a-h]e => before letter e, there must be letter range from a to h
-- both will produce same result
... wheer lastname like '%field%'
... where last_name REGEXP ‘field’IS NULL => is used to check value null or not
LIMIT => limit the result
SELECT * FROM author WHERE aut_name REGEXP '^w';
Joining tables
Inner join
- The INNER keyword is optional in SQL syntax, other join must be explicitly specified.
SELECT c.order_id, o.customer_id, c.first_name, c.last_name
FROM orders as o
JOIN customers as c
ON o.customer_id=c.customer_id
Joining across databases
// selected database is database 1
USE database1
SELECT *
FROM table_1 t1
JOIN Database2.table2 as t2 --joined from another database
ON t1.id=t2.id//selected database is database 2
USE database2
SELECT *
FROM Database1.table_1 t1 --joined from another database
JOIN table2 as t2
ON t1.id=t2.id
Self Joins
//which employee reports to which employee USE sql hr select
e.first_name as employee,
m.first_name as reports_to
FROM employees e
JOIN employees m
ON e.reports_to=m.employee_id
Joining multiple tables
USE sql_store
SELECT
o.order_id,
o.orderdate,
c.first_name,
c.last_name,
os.name AS status
FROM orders 0
JOIN customers c
ON o.customer_id = c.customer_id
JOIN order_statuses os
ON o.status = os.order_status_idjoining Compound TABLE
- composite primary key (2 key combined)
-- here order_id and product_id are composite key in both table
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_idImplicit join syntax
-- normal inner join syntax
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id=c.customer_id-- Implicit join syntax
-- cross join n x n row will occur if where condition not specified
SELECT *
FROM orders o , customer c
WHERE o.customer_id=c.customer_id
OUTER join
- The OUTER keyword is optional. LEFT or RIGHT keywords mean outer itself.
- There are two types of outer join available,
Let Table A & B
1. Left Join => A + (A and B common item as conditions)
2. Right Join => B +( A and B common items as conditions)
-- inner join = A and B common item
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customer c
JOIN orders o
ON o.customer_id=c.customer_id
ORDER BY c.customer_id-- Left join => A + (A and B common item as conditions)
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customer c -- LEFT TABLE
LEFT OUTER JOIN orders o -- RIGHT TABLE
ON o.customer_id=c.customer_id
ORDER BY c.customer_id-- Right join => B +( A and B common items as conditions)
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM orders o -- LEFT TABLE
RIGHT OUTER JOIN customer c -- RIGHT TABLE
ON o.customer_id=c.customer_id
ORDER BY c.customer_id
Outer JOIN between multiple tables
- Tips: avoid RIGHT and LEFT join together for simplicity
--(customers LEFT_OUTER_JOIN orders ) LEFT_OUTER_JOIN shippers
-- all customer(LEFT) will present in end result SELECT
c.customer_id,
c.first_name,
o.order_id,
sh.name AS shipperFROM customer c -- LEFT TABLE
LEFT OUTER JOIN orders o -- RIGHT TABLE
ON o.customer_id=c.customer_id
LEFT OUTER JOIN shippers sh -- 2nd RIGHT TABLE
ON o.shippers_id=sh.shippers_id
ORDER BY c.customer_id
SELF OUTER JOIN
--unlike inner join, which will return only person who have manager, --self outer join will return all employee including who don't have ---manager USE sql hrselect
e.first_name as employee,
m.first_name as reports_to
FROM employees e
LEFT JOIN employees m
ON e.reports_to=m.employee_id
The using clause
- USING works only for the same column name across tables
-- inner join = A and B common item
--USING keyword // no more on condition SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customer c
LEFT JOIN orders o USING(customer_id)
LEFT JOIN shippers sh USING(shippers_id)
-- using keyword for joining composit key containing table
-- here order_id and product_id are composite key in both table
SELECT *
FROM order_items oi
JOIN order_item_notes oin
USING (order_id,product_id)
Natural join ( automatic joining )
- NOT recommended to use
- Database determines the rules to join by itself
SELECT
o.order_id,
c.first_name
FROM orders o
NATURAL JOIN customer c Cross join
- All combination of A and B
-- explicit syntax
SELECT *
FROM customers c
CROSS JOIN products p-- implicit syntax
SELECT *
FROM customers c , products p
UNIONS (combine multiple rows)
- UNION combines multiple query results.
SELECT order_id,order_date,'ACTIVE' AS status
FROM orders
WHERE order_date >='2020-01-01' ;
UNION
SELECT order_id,order_date,'ARCHIVE' AS status
FROM orders
WHERE order_date <='2020-01-01'
Insert, Update, & Delete
INSERT
- LAST_INSERT_ID() => Returns last inserted id
- Select Query can be used as SubQuery as insert statement.
--INSERT (Single Table)INSERT INTO customers
(
`first_name`,
`last_name`,
`birth_date`,
`phone`,
`address`,
`city`,
`state`,
)
VALUES (
'Abdul',
'Ahad',
'1990-01-01',
'01720513318',
'Wari',
'Dhaka',
'Bangladesh'
)
--INSERT MULTIPLE TABLE ( INSERTING Hierarchical data)
-- one order can have multiple order
INSERT INTO orders (customer_id, order_date, status)
VALUES ( 1, '2019-01-02',1);INSERT INTO order_itemsVALUES
(LAST_INSERT_ID(),1,1,2.95)
(LAST_INSERT_ID(),2,1,2.95)
(LAST_INSERT_ID(),3,1,2.95)
(LAST_INSERT_ID(),4,1,2.95)
-- creating a copy of table from another table CREATE TABLE orders_archived AS
SELECT * FROM orders
WHERE order_date <='2020-01-01'
UPDATE
- DEFAULT => Return default column data.
-- updating single row
UPDATE invoices
SET payment_total=DEAFULT,
payment_date='2019-03-01'
WHERE invoice_id=1-- by default MySQL operates on safe update mode
-- Multiple update not possible by default in MySQL
UPDATE invoices
SET payment_total=invoice_total * 0.5,
payment_date=due_date
WHERE client_id = 3
- subquries in update
-- Single update from subquery UPDATE invoices
SET payment_total=invoice_total * 0.5,
payment_date=due_date
WHERE client_id =
( SELECT client_id FROM clients WHERE name = 'Myworkd')-- Multiple Update from Subquery
-- IN keyword used to match multiple data UPDATE invoices
SET payment_total=invoice_total * 0.5,
payment_date=due_date
WHERE client_id IN
( SELECT client_id FROM clients WHERE IN=('CA','NY'))
DELETE
--delete single
DELETE FROM invoices WHERE invoice_id = 1-- delete subquery
DELETE FROM invoices WHERE client_id =
( SELECT * FROM clients WHERE name = 'myworks')
Summerizing Data
aggregate function
- MAX()
- MIN()
- AVG()
- SUM()
- COUNT()
SELECT
MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average,
SUM(invoice_total) AS total,
COUNT(DISTINCT client_id) AS total_records
COUNT(*) AS total_records
FROM invoices
WHERE invoice_date > '2019-07-01'
Group by
--group by single column
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id
ORDER BY total_sales DESC
-- group by multiple column
--gathering resiult for each state-city combination
SELECT
state,city
SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING( client_id)
GROUP BY state, city
Having
- WHERE must be used before GROUP BY clause
- We can’t filter data retrieved from the aggregate function with where
- Having used to filter aggregate function data (grouped data)
- Having used after GROUP BY clause
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales > 500
The Rollup Operator
- Rollup operator works with the column that aggregate value
- The ROLLUP option allows to include extra rows that represent the subtotals, which are commonly referred to as super-aggregate rows, along with the grand total row. By using the ROLLUP option, you can use a single query to generate multiple grouping sets.
- NOT SQL standard
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP
HAVING total_sales > 500
Writing
Complex
Query
subqueries
-- Find every products that are expensive then Lettuce ( id=3)
SELECT *
FROM products
WHERE unit_price > (
SELECT unit_price
FROM products
WHERE product_id = 3
)-- find employees who earn more then avg
SELECT *
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
IN operator (subQueries)
SELECT *
FROM products
WHERE product_id NOT IN (
SELECT DISTINCT PRODUCT_ID
FROM order_items
)
subQuery vs JOIN
-- find clients without invoices C-I-- using subQuery
SELECT *
FROM clients
WHERE client_id NOT IN (
SELECT DISTINCT client_id
FROM invoices
)--using joins
SELECT * FROM clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL
ALL, ANY/SOME Operator
ANY returns true if any of the subquery values meet the condition.
And ALL/SOME returns true if all of the subquery values meet the condition.
- Must be preceded by the comparison operators.
- “= ANY” is equivalent to “IN” operator
SELECT *
FROM invoices
WHERE invoice_total > ALL(
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)Essential MYSQL functions
Numeric functions
ROUND(5.73)
=> 6
SELECT ROUND(5. 73,1)
=> 5.7
TRUNCATE(5.7345, 2)
=> 5.73
CELLING()
FLOOR()
ABS()
RAND()
=> random floating point number value between 0–1
for more see MYSQL DOCS
String functions
LENGTH()
UPPER()
LOWER()
LTRIM() // LEFT TRIM
RTRIM() // RIGHT TRIM
TRIM ()
LEFT(‘STR’, N) // cut from left
RIGHT(‘STR’, N) // cut from right
………. to be continued
