Basics of SQL
Basics
USE sql_store;
Select *
From customers
Where state = 'Am'
ORDER BY first_name
LIMIT 3;
Note..! Sql is not a case-sensitive language
Note..! In MySQL, ever statement must be terminated with a semicolon.
Comments
We use comments to add notes to our code.
--This is a comment and it won't get executed.
SELECT clause
SELECT (points * 10 + 20) As discount_factor
From customers
Order of operations :
- Parenthesis
- Multiplication / division
- Addition / Subtraction.
--Removing duplicates
SELECT DISTINCT State
From customers
Where Clause
We use WHERE clause to filter data.
Comparison operators:
- Greater than >>>>>> >
- Greater than or equal to >>>>>> >=
- Less than >>>>>> <
- Less than or equal to >>>>>> <=
- Equal >>>>>> ==
- Not equal >>>>>> <>
- Not equal >>>>>> !=
Logical Operators
--AND ( bath conditions must be True)
SELECT *
FROM customers ,
WHERE birthdate >'1990-01-01' AND points > 1000
--OR (at least one condition must be True)
SELECT *
From customers
Where bitrthdate > '1990-01-01' OR Points > 1000
--NOT ( to negate a condition)
SELECT *
From Customers
Where Not (birthdate > '1990-01-01')
TN Operators
--Returns customers in any of these states
--:VA, NY, CA
SELECT *
FROM Customers
Where state IN ('VA' , 'NY ', 'CA')
BETWEEN Operator
Select *
From customers
Where points Between 100 AND 200
LIKE Operator
--Returns customers who first name starts with
SELECT *
From customers
Where first_name Like 'b%'
- % : any number of characters
- _ : Exactly one character
REGEXP Operator
--Returns customers whose first name starts with a
SELECT *
From customers
Where first_name REGEXP '^a'
- ^ : beginning of a string
- $ : end of a string
- | : logical or
- [abc] : match any single characters
- [a -d] : any characters from a to d
IS NULL Operator
--Returns customers who don't have a phone number
SELECT *
From customers
Where phone IS NULL
ORDER By clause
--Sort customers b state ( in ascending order),
--and then by their first name ( in descending order)
SELECT *
FROM customers
ORDER BY state, first_name DESC
LIMIT Clause
--Returns only 3 customers
SELECT *
From customers
LIMIT 3
INNER Joins
SELECT *
FROM Customers c
JOINS orders o
ON C.Customers_ID = o.customer_id
OUTER Joins
SELECT *
FROM CustomersC
LEFT JOINS orders o
ON c.customer_id = 0.customer_id
USING Clause
IF column names are exactly same , you can simplify the join with the using clause.
SELECT *
FROM customers c
JOIN orders o
USING (customer_id)
CROSS Joins
-- Combine every colour with every size
SELECT *
FROM colours
CROSS JOIN Sizes
Unions
-- Combine records from multiple result sets
SELECT name, address
From customers
UNION
SELECT name,address
From Clients
Inserting Data
INSERT INTO Customers ( first_name, phone, points)
--Insert multiple single records
INSERT INTO Customers (first_name,phone,points)
Values
( 'vendan', NULL, Default)
( 'notes', '2411', 24)
#VendanNotes
"If you win you need not to explain .. .. ..!"
Comments
Post a Comment