🧩 Programming with Mosh - MySQL Tutorial for Beginners [Full Course] (Mar 20, 2019)
Description: MySQL tutorial for beginners - Learn MySQL, the world's most popular open source database.
📕Get my FREE SQL cheat sheet: http://bit.ly/2JCO73l
📔Scripts to create the databases in this course: http://bit.ly/2LNdvCd
If you're looking for a complete SQL tutorial for beginners with examples, this tutorial is a great way to learn SQL basics. Just follow through and you'll be able to write SQL queries in no time.
SQL, short for Structured Query Language, is a language used by software developers, data scientists and anyone who works with data. With SQL, you can store data in relational databases and write queries to extract valuable information.
There are various database management systems (DBMS) that support SQL but each product has its own implementation of SQL. In this course, we'll be using MySQL to learn SQL. MySQL is the most popular free and open-source database management system in the world. If you use a different DBMS (eg SQL Server), you can still benefit from this course as 90% of the skills you'll learn in this course can be transferred to other database management systems.
- 0:00:00 Introduction
- 0:01:02 What is SQL?
- 0:04:24 Cheat Sheet
- 0:04:50 Installing MySQL on Mac
- 0:09:48 Installing MySQL on Windows
- 0:15:08 Creating the Databases for this Course
- 0:23:40 The SELECT Statement
- 0:29:30 The SELECT Clause
- 0:38:18 The WHERE Clause
- 0:43:35 The AND, OR, and NOT Operators
- 0:51:38 The IN Operator
- 0:54:41 The BETWEEN Operator
- 0:56:53 The LIKE Operator
- 1:02:31 The REGEXP Operator
- 1:11:51 The IS NULL Operator
- 1:14:18 The ORDER BY Operator
- 1:21:23 The LIMIT Operator
- 1:24:50 Inner Joins
- 1:33:16 Joining Across Databases
- 1:36:03 Self Joins
- 1:40:17 Joining Multiple Tables
- 1:47:03 Compound Join Conditions
- 1:50:44 Implicit Join Syntax
- 1:53:04 Outer Joins
- 1:59:31 Outer Join Between Multiple Tables
- 2:05:50 Self Outer Joins
- 2:08:02 The USING Clause
- 2:13:25 Natural Joins
- 2:14:46 Cross Joins
- 2:18:01 Unions
- 2:26:29 Column Attributes
- 2:29:54 Inserting a Single Row
- 2:35:40 Inserting Multiple Rows
- 2:38:58 Inserting Hierarchical Rows
- 2:44:51 Creating a Copy of a Table
- 2:53:38 Updating a Single Row
- 2:57:33 Updating Multiple Rows
- 3:00:47 Using Subqueries in Updates
- 3:06:24 Deleting Rows
- 3:07:48 Restoring Course Databases
- PWM-MySQLFull-20190320
- 0:01:02 What is SQL?
- 0:04:24 Cheat Sheet
- 0:04:50 Installing MySQL on Mac
- 0:09:48 Installing MySQL on Windows
- 0:15:08 Creating the Databases
- 0:23:40 The SELECT Statement
- 0:29:30 The SELECT Clause
- 0:38:18 The WHERE Clause
- 0:43:35 The AND, OR, and NOT Operators
- 0:51:38 The IN Operator
- 0:54:41 The BETWEEN Operator
- 0:56:53 The LIKE Operator
- 1:02:31 The REGEXP Operator
- 1:11:51 The IS NULL Operator
- 1:14:18 The ORDER BY Operator
- 1:21:23 The LIMIT Operator
- 1:24:50 Inner Joins
- 1:33:16 Joining Across Databases
- 1 - Basic Usage
- 1:36:03 Self Joins
- 1:40:17 Joining Multiple Tables
- 1:47:03 Compound Join Conditions
- 1:50:44 Implicit Join Syntax
- 1:53:04 Outer Joins
- 1:59:31 Outer Join Between Multiple Tables
- 2:05:50 Self Outer Joins
- 2:08:02 The USING Clause
- 2:13:25 Natural Joins
- 2:14:46 Cross Joins
- 2:18:01 Unions
- 2:26:29 Column Attributes
- 2:29:54 Inserting a Single Row
- 2:35:40 Inserting Multiple Rows
- 2:38:58 Inserting Hierarchical Rows
- 2:44:51 Creating a Copy of a Table
- 2:53:38 Updating a Single Row
- 2:57:33 Updating Multiple Rows
- 3:00:47 Using Subqueries in Updates
- 3:06:24 Deleting Rows
- 3:07:48 Restoring Course Databases
- Collection of data stored in format that can easily be accessed.
- In order to manage databases, we use a software app called "database management system" (DBMS)
- We connect to our DBMS and give it instructions to query and modifying data.
- DBMS will execute our instructions and send results back.
- We store data in tables that are linked to each other using relationships
- Each table stores data about a specific type of object
- SQL is a language used to work with these relational types of DBMS
- MySQL
- SQL Server (Microsoft)
- Oracle
Each RDBMS has different flavor of SQL. but all of these has very similar implementation of SQL according to the standard SQL specification
- No Tables and relationships
- Don't understand SQL
- Invented by IBM in the 70s and was initially called SQUEL (Structured English Query Language)
- changed to SQL and removed "English / E" since SQUEL was a trademark of an airplane company
- Most Non English Countries call it just SQL instead of SQUEL
doesnt work ?
-
Go to MySQL.com
-
Go to downloads
-
find community downloads
-
find community server for macos, download
-
double click on wizard then do stuff
-
Go back to downloads and find MySQL Workbench then download that
- Open MySQL Workbench
- delete the 3306 connection when new
- Test Connection then remember the password you set when you setting it up with the wizard
- then click okay the save the instance
- to open sql editor, just click on that instance
SKIP
Download Link of Course Materials:
^^ seen at vid description
then import in the create-database sql to our sql editor by clicking
the second icon to teh left
then to run the code, you run the lightning icon
then at schemas hit the reload icon to sync the changes from the instructions when we run the thunder icon
-
Tables - where we store data
-
Views - Virtual Cables, combine data from multiple tables and put them in a view. powerful when creating reports
-
Stored Procedures
-
Functions - little programs with stored inside our databases for querying data
Ex: if we have a store procedure for getting all customers in a given city, then this will return all those cutomers
hover above a table say customers, then you should see three icons. click the third one to see the table view
to select a database, say sql_store, we say
USE sql_storeyou can see after executing with lightning icon, the sql_store name will turn bold
USE sql_store;
SELECT *
FROM customersdont forget semicolons
Instead of Executing with lightning icon, you can Shift+Command+Enter
this select statement has two clauses: select and from clause. there are other clauses taht can be used to filter and sort data.
USE sql_store;
SELECT *
FROM customers
WHERE customer_id = 1
like the image above where we commented out our WHERE Clause statement
aside from just using "*" to get all data, you can select what kind of data fields / columns you want to query for
SELECT first_name, last_name
FROM customers
if you made last_name first before first_name, the order of columns will be different
You can also do math operations along with gemdas stuff:
SELECT first_name, last_name, points, points+10
FROM customers
You can also place select in multiline like this
SELECT
first_name,
last_name,
points,
points*10+100
FROM customersSELECT
first_name,
last_name,
points,
(points+10)*100 AS discount_factor
FROM customersif you want to add space instead of underscore, you either use single or double quotes
SELECT
first_name,
last_name,
points,
(points+10)*100 AS "discount factor"
FROM customersSELECT DISTINCT state
FROM customersSELECT *
FROM customers
WHERE state = "va"SELECT *
FROM customers
-- WHERE state <> "va"
WHERE state != "va"SELECT *
FROM customers
WHERE birth_date > '1990-01-01'SELECT *
FROM customers
-- WHERE birth_date > '1990-01-01' AND points > 1000
WHERE birth_date > '1990-01-01'
OR points > 1000
AND state = 'VA'similar to gemdas, AND will be higher order, likewise you can use parenthesis and space it out to make it readable
SELECT *
FROM customers
-- WHERE birth_date > '1990-01-01' AND points > 1000
WHERE birth_date > '1990-01-01'
OR (points > 1000 AND state = 'VA')you also have the not operator
SELECT *
FROM customers
WHERE NOT (birth_date > '1990-01-01'
OR (points > 1000 AND state = 'VA'))Another Example:
USE sql_store;
SELECT * FROM order_items
WHERE order_id = 6
AND (unit_price*quantity > 30)instead of writing all of these separate expressions, you can shorthand the same instructions using IN
SELECT * FROM customers
WHERE state = 'VA' OR state = "GA" OR state = "FL"with in: like this
SELECT * FROM customers
WHERE state IN ('VA',"GA","FL")outside of those states: NOT IN
SELECT * FROM customers
-- WHERE state IN ('VA',"GA","FL")
WHERE state NOT IN ('VA',"GA","FL")another example
SELECT * FROM products
WHERE quantity_in_stock IN (49,39,72)longcut
SELECT * FROM customers
WHERE points >= 1000 AND points <= 3000shortcut with Between Operator
SELECT * FROM customers
-- WHERE points >= 1000 AND points <= 3000
WHERE points BETWEEN 1000 AND 3000Another Example:
SELECT * FROM customers
WHERE birth_date BETWEEN "1990-01-01" AND "2000-01-01"- % represent any number of characters
- _ to represent a single character
similar to includes, b% means anything starting with b
SELECT * FROM customers
WHERE last_name LIKE 'b%'have b anywhere in their last name
SELECT * FROM customers
-- WHERE last_name LIKE 'b%'
WHERE last_name LIKE '%b%'last names ending with y
SELECT * FROM customers
-- WHERE last_name LIKE 'b%'
-- WHERE last_name LIKE '%y'
WHERE last_name LIKE '%b%'SELECT * FROM customers
-- WHERE last_name LIKE 'b%'
-- WHERE last_name LIKE '%y'
-- WHERE last_name LIKE '%b%'
-- WHERE last_name LIKE '_____y'
WHERE last_name LIKE 'b____y'This one executes the same result
SELECT * FROM customers
-- WHERE last_name LIKE '%field%'
WHERE last_name REGEXP 'field'This means that our last name must start with "field"
SELECT * FROM customers
-- WHERE last_name LIKE '%field%'
-- WHERE last_name REGEXP 'field'
WHERE last_name REGEXP '^field'SELECT * FROM customers
-- WHERE last_name LIKE '%field%'
-- WHERE last_name REGEXP 'field'
-- WHERE last_name REGEXP '^field'
WHERE last_name REGEXP 'field$'Example: last name should start with "field" OR shud have the word "mac" OR shud have the word "rose"
SELECT * FROM customers
-- WHERE last_name LIKE '%field%'
-- WHERE last_name REGEXP 'field'
-- WHERE last_name REGEXP '^field'
-- WHERE last_name REGEXP 'field$'
WHERE last_name REGEXP '^field|mac|rose'
Means either last name contains a "ge | ie | me"
SELECT * FROM customers
-- WHERE last_name LIKE '%field%'
-- WHERE last_name REGEXP 'field'
-- WHERE last_name REGEXP '^field'
-- WHERE last_name REGEXP 'field$'
-- WHERE last_name REGEXP '^field|mac|rose'
-- WHERE last_name REGEXP '[gim]e' -- Means either last name contains a "ge | ie | me"
WHERE last_name REGEXP 'e[fmq]' -- Means either last name contains a "ef | em | eq"^^ similar to that but now has a range, not requiring to explicitly state the specific combinations of characters
SELECT * FROM customers
-- WHERE last_name LIKE '%field%'
-- WHERE last_name REGEXP 'field'
-- WHERE last_name REGEXP '^field'
-- WHERE last_name REGEXP 'field$'
-- WHERE last_name REGEXP '^field|mac|rose'
-- WHERE last_name REGEXP '[gim]e' -- Means either last name contains a "ge | ie | me"
-- WHERE last_name REGEXP 'e[fmq]' -- Means either last name contains a "ef | em | eq"
-- Supplying a range of characters - ^^ similar to that but now has a range, not requiring to explicitly state the specific combinations of characters
WHERE last_name REGEXP '[a-h]e'-- Get the customers whose
-- -- first names are ELKA or AMBUR
-- -- last names end with EY or ON
-- -- last names start with MY or contains SE
-- -- last names contain B followed by R or U
USE sql_store;
SELECT * FROM customers
-- WHERE first_name REGEXP 'ELKA|AMBUR'
WHERE last_name REGEXP 'EY$|ON$'
-- WHERE last_name REGEXP '^MY|SE'
-- WHERE last_name REGEXP 'B[RU]'null - absence of a value
get all customers who have null phone fields
SELECT *
FROM customers
WHERE phone IS NULLSELECT *
FROM customers
WHERE phone IS NOT NULL-- Get the orders that are not shipped yet
USE sql_store;
SELECT * FROM orders
WHERE shipped_date IS NULL
-- WHERE shipper_id IS NULL --also correcti switched to usin VS code instead of MySQL Workbench (cuz its better ;) wink wink). anyways to view the tables schema, you right click on the table at your sql explorer tab under sql_store and Tables. then you should see design table option.
this one sorts your data by first_name in ascending order by default.
USE sql_store;
SELECT * FROM customers
ORDER BY first_nameUSE sql_store;
SELECT * FROM customers
-- ORDER BY first_name
ORDER BY first_name DESCso this code arranges data by state in ascending order, then those with same state are then sorted by their first name in descending order.
USE sql_store;
SELECT * FROM customers
ORDER BY state , first_name DESCthis one shows sorted data with only the selected columns of data (first_name, last_name) but sorted by birth_date even without selecting birth_date
USE sql_store;
SELECT first_name, last_name
FROM customers
ORDER BY birth_dateUSE sql_store;
SELECT first_name, last_name, 10 AS points
FROM customers
ORDER BY points, first_nameyou can but pls don't
USE sql_store;
SELECT first_name, last_name, 10 AS points
FROM customers
ORDER BY 1,2
USE sql_store;
SELECT order_id, product_id, quantity, unit_price
FROM order_items
WHERE order_id = 2
ORDER BY (quantity*unit_price) DESCUSE sql_store;
SELECT *, quantity*unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY total_price DESCUSE sql_store;
SELECT * FROM customers
LIMIT 5This example jumps to the 7th data, then selects 3 (7th, 8th, and 9th data)
-- page 1: 1-3
-- page 2: 4-6
-- page 3: 7-9
USE sql_store;
SELECT * FROM customers
LIMIT 6, 3-- Get The Top Three Loyal Customers (Customers who have more points the anyone else)
USE sql_store;
SELECT * FROM customers
ORDER BY points DESC
LIMIT 3the limit clause should always come in the end
USE sql_store;
SELECT * FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_idUSE sql_store;
SELECT order_id, first_name, last_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_idwhat if you want to also display the customer_id as well
if you try to select customer_id, you get ambiguous error since customer_id is in both orders and customers tables.
to fix the error above, you prefix it with where the field is referring to make it not ambiguous. Either from the orders table, or the customers table since theyre both the same anyways
USE sql_store;
SELECT order_id, orders.customer_id, first_name, last_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_idsame result as the one above but with aliases we can work with cleaner code and create shortcuts
USE sql_store;
SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
INNER JOIN customers c
ON o.customer_id = c.customer_idUSE sql_store;
-- SELECT *
SELECT order_id, oi.product_id, quantity, oi.unit_price
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_idJoining orders_items table from sql_store database with products from sql_inventory database
USE sql_store;
SELECT *
FROM order_items oi
JOIN sql_inventory.products p
ON oi.product_id = p.product_id-- START: WAY 1
-- USE sql_store;
-- SELECT *
-- FROM order_items oi
-- JOIN sql_inventory.products p
-- ON oi.product_id = p.product_id
-- END: WAY 1
-- START: WAY 2
USE sql_inventory;
SELECT *
FROM sql_store.order_items oi
JOIN products p
ON oi.product_id = p.product_id
-- END: WAY 2We can also join a table with itself
USE sql_hr;
SELECT *
FROM employees employee
JOIN employees manager
ON employee.reports_to = manager.employee_idUSE sql_hr;
SELECT
employee.employee_id,
employee.first_name,
manager.first_name AS manager
FROM employees employee
JOIN employees manager
ON employee.reports_to = manager.employee_idJoin Two or more tables when writing a query
This example from the orders table in sql_store, joins two tables, customers and order_statuses
USE sql_store;
SELECT *
FROM orders orders_table
JOIN customers customers_table
ON orders_table.customer_id = customers_table.customer_id
JOIN order_statuses order_statuses_table
ON orders_table.status = order_statuses_table.order_status_idUSE sql_store;
SELECT
orders_table.order_id,
orders_table.order_date,
customers_table.first_name,
customers_table.last_name,
order_statuses_table.name AS status
FROM orders orders_table
JOIN customers customers_table
ON orders_table.customer_id = customers_table.customer_id
JOIN order_statuses order_statuses_table
ON orders_table.status = order_statuses_table.order_status_idsql_invoicing
payments
Join with clients and payment_methods table
USE sql_invoicing;
SELECT
payments_table.date AS payment_date,
payments_table.invoice_id,
payments_table.amount AS payment_amount,
clients_table.name AS client_name,
payment_methods_table.name AS payment_method
FROM payments payments_table
INNER JOIN clients clients_table
ON payments_table.client_id = clients_table.client_id
INNER JOIN payment_methods payment_methods_table
ON payments_table.payment_method = payment_methods_table.payment_method_idUSE sql_store;
SELECT *
FROM order_items oi
INNER JOIN order_item_notes oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id-- START: EXPLICIT JOIN SYNTAX EXAMPLE
-- SELECT *
-- FROM orders o_table
-- INNER JOIN customers c_table
-- ON o_table.customer_id = c_table.customer_id
-- END: EXPLICIT JOIN SYNTAX EXAMPLE
-- START: IMPLICIT JOIN SYNTAX VERSION OF THE EXAMPLE
SELECT *
FROM orders o_table, customers c_table
WHERE o_table.customer_id = c_table.customer_id
-- END: IMPLICIT JOIN SYNTAX VERSION OF THE EXAMPLEtho they execute the same results, if at your implicit join syntax u forget the where clause, you will have a cross join issue.
In SQL we have two kinds of joins: the inner join, and the outer join.
With that, there are also two kinds of outer joins: the left and right. using this example:
SELECT
c_table.customer_id,
c_table.first_name,
o_table.order_id
FROM customers c_table
INNER JOIN orders o_table
ON c_table.customer_id = o_table.customer_id
ORDER BY o_table.customer_id-
Left - All records from the left table (customers) will be returned even if it doesn't satisfy our ON condition when joining tables.
SELECT c_table.customer_id, c_table.first_name, o_table.order_id FROM customers c_table LEFT OUTER JOIN orders o_table ON c_table.customer_id = o_table.customer_id ORDER BY o_table.customer_id
-
Right - All records from the right table (orders) will be returned even if it doesn't satisfy our ON condition when joining tables
SELECT c_table.customer_id, c_table.first_name, o_table.order_id FROM customers c_table RIGHT OUTER JOIN orders o_table ON c_table.customer_id = o_table.customer_id ORDER BY o_table.customer_id
Replicate the results
USE sql_store;
SELECT
p_table.product_id,
p_table.name,
oi_table.quantity
FROM products p_table
LEFT OUTER JOIN order_items oi_table
ON p_table.product_id = oi_table.product_idSELECT
c_table.customer_id,
c_table.first_name,
o_table.order_id,
s_table.name AS shipper_name
FROM customers c_table
LEFT OUTER JOIN orders o_table
ON c_table.customer_id = o_table.customer_id
LEFT OUTER JOIN shippers s_table
ON o_table.shipper_id = s_table.shipper_id
ORDER BY c_table.customer_idits advised to just LEFT OUTER JOIN all the way instead of mixing RIGHT OUTER JOINS as well so it would be easier to visualise how we're joining different tables.
create a query to produce the results shown in this pic:
SELECT
o_table.order_date,
o_table.order_id,
c_table.first_name AS customer,
s_table.name AS shipper,
os_table.name AS status
FROM orders o_table
INNER JOIN customers c_table
ON o_table.customer_id = c_table.customer_id
LEFT OUTER JOIN shippers s_table
ON o_table.shipper_id = s_table.shipper_id
INNER JOIN order_statuses os_table
ON o_table.status = os_table.order_status_id
ORDER BY status, o_table.order_id ASCIn sql_hr db, were gonna get every data in employees table whether or not they have a manager.
USE sql_hr;
SELECT
e_table.employee_id,
e_table.first_name,
m_table.first_name AS manager
FROM employees e_table
LEFT OUTER JOIN employees m_table
ON e_table.reports_to = m_table.employee_id-- START: ORIGINAL WAY
-- USE sql_store;
-- SELECT
-- o_table.order_id,
-- c_table.first_name
-- FROM orders o_table
-- JOIN customers c_table
-- ON o_table.customer_id = c_table.customer_id
-- END: ORIGINAL WAY
-- START: ANOTHER WAY W/ USING CLAUSE
USE sql_store;
SELECT
o_table.order_id,
c_table.first_name
FROM orders o_table
JOIN customers c_table
USING (customer_id)
-- END: ANOTHER WAY W/ USING CLAUSE-- START: MULTIPLE JOINS W/ USING CLAUSES
USE sql_store;
SELECT
o_table.order_id,
c_table.first_name,
s_table.name AS shipper
FROM orders o_table
JOIN customers c_table
USING (customer_id)
LEFT OUTER JOIN shippers s_table
USING (shipper_id)
-- END: MULTIPLE JOINS W/ USING CLAUSES-- START: ORIGINAL WAY
-- SELECT *
-- FROM order_items oi_table
-- JOIN order_item_notes oin_table
-- ON oi_table.order_id = oin_table.order_id
-- AND oi_table.product_id = oin_table.product_id
-- END: ORIGINAL WAY
-- START: WITH USING CLAUSE WAY
SELECT *
FROM order_items oi_table
JOIN order_item_notes oin_table
USING (order_id, product_id)
-- END: WITH USING CLAUSE WAYRecreate the results in this table
USE sql_invoicing;
SELECT
p_table.date,
c_table.name AS client,
p_table.amount,
pm_table.name
FROM payments p_table
INNER JOIN clients c_table
USING (client_id)
INNER JOIN payment_methods pm_table
ON p_table.payment_method = pm_table.payment_method_idtheres another simpler way to join tables using natural joins however it is not recommended since sometimes it produces unexpected results
This is an example of using NATURAL JOIN to join orders and customers table in sql_store database
-- START: ORIGINAL WAY
-- USE sql_store;
-- SELECT
-- o_table.order_id,
-- c_table.first_name
-- FROM orders o_table
-- INNER JOIN customers c_table
-- ON o_table.customer_id = c_table.customer_id
-- END: ORIGINAL WAY
-- START: USING NATURAL JOINS
USE sql_store;
SELECT
o_table.order_id,
c_table.first_name
FROM orders o_table
NATURAL JOIN customers c_table
-- END: USING NATURAL JOINSIn this example we combine (cross join) the customers and products table to one.
USE sql_store;
SELECT *
FROM customers c_table
CROSS JOIN products p_tableAnother Example with selected fields
USE sql_store;
-- SELECT *
SELECT
c_table.first_name AS customer,
p_table.name AS product
FROM customers c_table
CROSS JOIN products p_table
ORDER BY customer-- START: EXPLICIT CROSS JOIN
-- USE sql_store;
-- -- SELECT *
-- SELECT
-- c_table.first_name AS customer,
-- p_table.name AS product
-- FROM customers c_table
-- CROSS JOIN products p_table
-- ORDER BY customer
-- END: EXPLICIT CROSS JOIN
-- START: IMPLICIT CROSS JOIN
USE sql_store;
-- SELECT *
SELECT
c_table.first_name AS customer,
p_table.name AS product
FROM customers c_table, products p_table
ORDER BY customer
-- END: IMPLICIT CROSS JOINDo a cross join between shipper and products
-
Using implicit syntax
-- START: IMPLICIT SYNTAX USE sql_store; SELECT * FROM shippers s_table, products p_table -- END: IMPLICIT SYNTAX
-
Using Explicit syntax
-- START: EXPLICIT SYNTAX USE sql_store; SELECT * FROM shippers s_table CROSS JOIN products p_table -- END: EXPLICIT SYNTAX
Not only can you combine columns through joins, you can also combine rows in SQL.
SELECT
order_id,
order_date,
'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT
order_id,
order_date,
'Archived' AS status
FROM orders
WHERE order_date <= '2019-01-01'number of selected columns must be the same number. if they aren't then SQL wouldn't know how to union the two or more things you wish to unify
SELECT first_name
FROM customers
UNION
SELECT name
FROM shippers-- SELECT first_name
-- FROM customers
-- UNION
-- SELECT name
-- FROM shippers
-- if you changed / reversed the order of the queries you try to unify, then the resulting order would also be reversed!
SELECT name
FROM shippers
UNION
SELECT first_name
FROM customersEx: if your first query selected a column called name, while the second query you selected first_name, the resulting would be name in unified column.
the alias also works only on the first query
-- SELECT first_name
-- FROM customers
-- UNION
-- SELECT name
-- FROM shippers
-- if you changed / reversed the order of the queries you try to unify, then the resulting order would also be reversed!
-- SELECT name
SELECT name AS full_name
FROM shippers
UNION
SELECT first_name
FROM customers
SELECT
customer_id,
first_name,
points,
"Bronze" AS type
FROM customers
WHERE points <= 2000
UNION
SELECT
customer_id,
first_name,
points,
"Silver" AS type
FROM customers
WHERE points > 2000 AND points <= 3000
UNION
SELECT
customer_id,
first_name,
points,
"Gold" AS type
FROM customers
WHERE points > 3000 AND points <= 4000
ORDER BY first_nameSELECT
customer_id,
first_name,
points,
"Bronze" AS type
FROM customers
WHERE points <= 2000
UNION
SELECT
customer_id,
first_name,
points,
"Silver" AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT
customer_id,
first_name,
points,
"Gold" AS type
FROM customers
WHERE points > 3000
ORDER BY first_name- INT
- CHAR
- ex: CHAR (50) - VARCHAR might be better since if for example you have data thats only 5 characters, SQL would just insert 45 characters that are empty which isnt good.
- VARCHAR
- DATE
- PK - Primary Key
- NN - Not Null - can accept null values
- Default - Default if nothing set
- AI - Auto Increment - usually with PKs
USE sql_store;
INSERT INTO
customers
VALUES(
DEFAULT,
"firstname",
"lastname",
"1990-01-01",
NULL,
"address",
"city",
"CA",
DEFAULT
)USE sql_store;
INSERT INTO
customers(
last_name,
first_name,
birth_date,
address,
city,
state
)
VALUES(
"lastname",
"firstname",
"1990-01-01",
"address",
"city",
"CA"
)USE sql_store;
INSERT INTO
shippers(name)
VALUES
("Shipper1"),
("Shipper1"),
("Shipper1")-- Insert 3 rows in the products table
USE sql_store;
INSERT INTO
products(name, quantity_in_stock, unit_price)
VALUES
("product1", 50, 5.50),
("product2", 60, 6.50),
("product3", 70, 7.50)Example: parent is orders table and order_items table is the child (parent-child relationship)
--Executing this query will show you the last inserted id
-- SELECT LAST_INSERT_ID()
USE sql_store;
INSERT INTO
orders (customer_id, order_date, status)
VALUES
(1, '2019-01-02', 1);
INSERT INTO
order_items
VALUES
(LAST_INSERT_ID(), 1, 1, 2.95),
(LAST_INSERT_ID(), 2, 1, 3.95)USE sql_store;
CREATE TABLE orders_archived AS
SELECT
*
FROM
ordersRight click on the orders_archived table and click yes to truncate the table, this will clear the table of its records
this example copies records from orders table to orders-archived table that have the dates before the 2019.
USE sql_store;
INSERT INTO
orders_archived
SELECT
*
FROM
orders
WHERE
order_date < "2019-01-01"USE sql_invoicing;
CREATE TABLE invoices_archived AS
SELECT
i.invoice_id,
i.number,
c.name AS client,
i.invoice_total,
i.payment_total,
i.invoice_date,
i.payment_date,
i.due_date
FROM
invoices i
INNER JOIN clients c USING (client_id)
WHERE
payment_date IS NOT NULLUSE sql_invoicing;
-- UPDATE
-- invoices
-- SET
-- payment_total = 10,
-- payment_date = "2019-03-01"
-- WHERE
-- invoice_id = 1 USE sql_invoicing;UPDATE
invoices
SET
payment_total = DEFAULT,
payment_date = NULL
WHERE
invoice_id = 1USE sql_invoicing;
UPDATE
invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE
invoice_id = 3In MySql Workbench, go to preferences to turn off safe update which limits one to updating or deleting more than queries. Then restart your instance
Not sure how to do that in vscode tho but gg hahahhaha.
USE sql_invoicing;
UPDATE
invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE
invoice_id IN (3, 4)it works hahahahha nice
-- write SQL statement to
-- give any customers born before 1990 50 extra points
USE sql_store;
UPDATE
customers
SET
points = points + 50
WHERE
birth_date <= "1990-01-01"This example looks for a client in the clients table with the name "Myworks", then updates the payment total and date
USE sql_invoicing;
UPDATE
invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE
client_id = (
SELECT
client_id
FROM
clients
WHERE
name = "Myworks"
)This on the other hand looks for clients who is either in CA and NY state, then updates their payment informations.
USE sql_invoicing;
UPDATE
invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE
client_id IN (
SELECT
client_id
FROM
clients
WHERE
state IN ("CA", "NY")
)this code looks for customers with 3000+ points and sets the comments to be a Gold Customer
USE sql_store;
UPDATE
orders
SET
comments = "Gold Customer"
WHERE
customer_id IN (
SELECT
customer_id
FROM
customers
WHERE
points > 3000
)USE sql_invoicing;
DELETE FROM
invoices
WHERE
client_id = (
SELECT
client_id
FROM
clients
WHERE
name = "Myworks"
)SKIP
Table of Contents
- PWM-MySQLFull-20190320
- 0:01:02 What is SQL?
- 0:04:24 Cheat Sheet
- 0:04:50 Installing MySQL on Mac
- 0:09:48 Installing MySQL on Windows
- 0:15:08 Creating the Databases
- 0:23:40 The SELECT Statement
- 0:29:30 The SELECT Clause
- 0:38:18 The WHERE Clause
- 0:43:35 The AND, OR, and NOT Operators
- 0:51:38 The IN Operator
- 0:54:41 The BETWEEN Operator
- 0:56:53 The LIKE Operator
- 1:02:31 The REGEXP Operator
- 1:11:51 The IS NULL Operator
- 1:14:18 The ORDER BY Operator
- 1:21:23 The LIMIT Operator
- 1:24:50 Inner Joins
- 1:33:16 Joining Across Databases
- 1 - Basic Usage
- 1:36:03 Self Joins
- 1:40:17 Joining Multiple Tables
- 1:47:03 Compound Join Conditions
- 1:50:44 Implicit Join Syntax
- 1:53:04 Outer Joins
- 1:59:31 Outer Join Between Multiple Tables
- 2:05:50 Self Outer Joins
- 2:08:02 The USING Clause
- 2:13:25 Natural Joins
- 2:14:46 Cross Joins
- 2:18:01 Unions
- 2:26:29 Column Attributes
- 2:29:54 Inserting a Single Row
- 2:35:40 Inserting Multiple Rows
- 2:38:58 Inserting Hierarchical Rows
- 2:44:51 Creating a Copy of a Table
- 2:53:38 Updating a Single Row
- 2:57:33 Updating Multiple Rows
- 3:00:47 Using Subqueries in Updates
- 3:06:24 Deleting Rows
- 3:07:48 Restoring Course Databases