SQL常用命令

marting · 发表于 2021-2-1 21:15:25

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？立即注册

×

USE sql_store;
-- >>>>>>> The SELECT, WHERE Clause <<<<<<<
SELECT
first_name,
last_name,
points,
(points + 10) * 100 AS 'discount factor'
FROM customers
-- WHERE customer_id = 1
-- ORDER BY first_name
;
-- >>>>>>> The AND, OR and NOT Operators <<<<<<<
SELECT *
FROM customers
WHERE birth_date > '1990-01-01' OR (points > 1000 AND state = 'VA');
-- WHERE birth_date > '1990-01-01' OR points > 1000 AND state = 'VA'
SELECT *
FROM customers
WHERE NOT (birth_date > '1990-01-01' OR points > 1000);
SELECT *
FROM customers
WHERE birth_date <= '1990-01-01' AND points <= 1000;
-- >>>>>>> The IN Operato <<<<<<<
SELECT *
FROM customers
WHERE state IN ('VA','FL','GA')
-- WHERE state = 'VA' OR state = 'GA' OR state = 'FL'
-- WHERE state NOT IN ('VA','FL','GA')
;
-- >>>>>>> The BETWEEN Operator <<<<<<<
SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000
-- WHERE points >= 1000 AND points <= 3000
;
-- >>>>>>> The LIKE Operator <<<<<<<
SELECT *
FROM customers
WHERE last_name LIKE 'b%' ## last name start with 'b/B'
-- WHERE last_name LIKE '%b%'
-- WHERE last_name LIKE '%y'
;
SELECT *
FROM customers
WHERE last_name LIKE 'b____y'
-- % any number of characters
-- _ single character
;
-- >>>>>>> The REGEXP Operator <<<<<<<
SELECT *
FROM customers
WHERE last_name REGEXP 'field$|mac|rose'
-- WHERE last_name REGEXP '^field'
-- WHERE last_name REGEXP 'field
-- WHERE last_name REGEXP 'field|mac'
-- WHERE last_name REGEXP '[gim]e'
-- ^ beginning
-- $ end
-- | logical or
-- [abcd]
-- [a-f]
;
-- >>>>>>> The IS NULL Operator <<<<<<<
SELECT *
FROM customers
WHERE phone IS NOT NULL
-- WHERE phone IS NULL
;
-- >>>>>>> The ORDER BY Clause <<<<<<<
SELECT *
FROM customers
ORDER BY state DESC, first_name DESC
-- ORDER BY state, first_name
;
SELECT first_name, last_name, 10 AS points
FROM customers
ORDER BY points, first_name
;
SELECT first_name, last_name, 10 AS points
FROM customers
ORDER BY 1, 2
;
-- >>>>>>> The LIMIT Clause <<<<<<<
SELECT *
FROM customers
LIMIT 3
;
SELECT *
FROM customers
LIMIT 6, 3
-- page 1: 1 - 3
-- page 2: 4 - 6
-- page 3: 7 - 9
;
-- >>>>>>> Inner Joins <<<<<<<
SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
;
-- >>>>>>> Joining Across Databases <<<<<<<
SELECT *
FROM order_items oi
JOIN sql_inventory.products p
ON oi.product_id = p.product_id
;
USE sql_inventory;
SELECT *
FROM sql_store.order_items oi
JOIN products p
ON oi.product_id = p.product_id
;
-- >>>>>>> Self Joins <<<<<<<
USE sql_hr;
SELECT
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
JOIN employees m
ON e.reports_to = m.employee_id
;
-- >>>>>>> Joining Multiple Tables <<<<<<<
USE sql_store;
SELECT
o.order_id,
o.order_date,
c.first_name,
c.last_name,
os.name AS status
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
JOIN order_statuses os
ON o.status = os.order_status_id
;
-- >>>>>>> Compound Join Conditions <<<<<<<
USE sql_store;
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id
;
-- >>>>>>> Implicit Join Syntax <<<<<<<
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
;
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id ## euqal to the previous one
;
-- >>>>>>> Outer Joins <<<<<<<
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id
;
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id
;
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM orders o
RIGHT JOIN customers c
ON c.customer_id = o.customer_id
ORDER BY c.customer_id
;
-- >>>>>>> Outer Join Between Multiple Tables <<<<<<<
SELECT
c.customer_id,
c.first_name,
o.order_id,
sh.name AS shipper
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id
;
-- >>>>>>> Self Outer Joins <<<<<<<
USE sql_hr;
SELECT
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.reports_to = m.employee_id
;
-- >>>>>>> The USING Clause <<<<<<<
USE sql_store
;
SELECT
o.order_id,
c.first_name,
sh.name AS shipper
FROM orders o
JOIN customers c
USING (customer_id)
LEFT JOIN shippers sh
USING (shipper_id)
;
SELECT *
FROM order_items oi
LEFT JOIN order_item_notes oin
USING (order_id, product_id)
;
-- >>>>>>> Natural Joins <<<<<<<
USE sql_store;
SELECT *
FROM orders o
NATURAL JOIN customers c
;
-- >>>>>>> Cross Joins <<<<<<<
SELECT
c.first_name AS customer,
p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name
;
SELECT
c.first_name AS customer,
p.name AS product
FROM customers c, products p
ORDER BY c.first_name
;
-- >>>>>>> Unions <<<<<<<
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'
;
SELECT first_name AS full_name
FROM customers
UNION
SELECT name
FROM shippers
;
-- >>>>>>> Column Attributes <<<<<<<
-- >>>>>>> Inserting a Row <<<<<<<
INSERT INTO customers (
first_name,
last_name,
birth_date,
address,
city,
state)
VALUES (
'John',
'Smith',
'1990-01-01',
'address',
'city',
'CA')
;
-- >>>>>>> Inserting Multiple Rows <<<<<<<
INSERT INTO shippers (name)
VALUES ('Shipper1'),
('Shipper2'),
('Shipper3')
;
-- >>>>>>> Inserting Hierarchical Rows <<<<<<<
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)
;
-- >>>>>>> Creating a Copy of a Table <<<<<<<
CREATE TABLE orders_archived AS
SELECT *
FROM orders
;
INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01'
;
-- >>>>>>> Updating a Single Row <<<<<<<
UPDATE invoices
SET payment_total = DEFAULT, payment_date = NULL
WHERE invoice_id = 1
;
UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE invoice_id = 3
;
-- >>>>>>> Updating Multiple Rows <<<<<<<
UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id = 3 ## Preferences --> SQL Editor --> safe update
-- WHERE client_id IN (3,4)
;
-- >>>>>>> Using Subqueries in Updates <<<<<<<
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 state IN ('CA', 'NY'))
;
UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE payment_date IS NULL
;
-- >>>>>>> Deleting Rows <<<<<<<
USE sql_invoicing;
DELETE FROM invoices
WHERE client_id = (
SELECT client_id
FROM clients
WHERE name = 'Myworks')
;
-- >>>>>>> Restoring the Databases <<<<<<<
-- >>>>>>> Aggregate Functions <<<<<<<
USE sql_invoicing;
SELECT
MAX(invoice_total) AS hightest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS avverage,
SUM(invoice_total * 1.1) AS total,
Count(invoice_total) AS number_of_invoices,
Count(payment_date) AS count_of_payments,
Count(DISTINCT(client_id)) AS total_records, ## without duplication
Count(*) AS total_records1
FROM invoices
WHERE invoice_date > '2019-07-01'
;
-- >>>>>>> The GROUP BY Clause <<<<<<<
SELECT
state,
city,
SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients
USING (client_id)
WHERE invoice_date >= '2019-07-01'
GROUP BY state, city
ORDER BY total_sales DESC
;
-- >>>>>>> The HAVING Clause <<<<<<<
SELECT
client_id,
SUM(invoice_total) AS total_sales,
COUNT(*) AS number_of_invoivrs
FROM invoices
-- WHERE client_id <= 5 ## Here should not be aggregation function
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoivrs > 5## filter data after group --> aggregation function
;
-- >>>>>>> The ROLLUP Operator <<<<<<<
SELECT
state,
city,
SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c USING (client_id)
GROUP BY state, city WITH ROLLUP
;
-- >>>>>>> Subqueries <<<<<<<
SELECT *
FROM products
WHERE unit_price > (
SELECT unit_price
FROM products
WHERE product_id = 3
)
;
-- >>>>>>> The IN Operator <<<<<<<
USE sql_store;
SELECT *
FROM products
WHERE product_id NOT IN (
SELECT DISTINCT product_id
FROM order_items # product 7 has never been ordered
)
;
-- >>>>>>> Subqueries vs Joins <<<<<<<
USE sql_invoicing;
SELECT *
FROM clients
WHERE client_id NOT IN (
SELECT DISTINCT client_id
FROM invoices
)
;
SELECT *
FROM clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL
;
-- >>>>>>> The ALL Keyword <<<<<<<
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > (
SELECT MAX(invoice_total)
FROM invoices
WHERE client_id = 3
)
;
-- Select invoices larger than all invoices of client 3
SELECT *
FROM invoices
WHERE invoice_total > ALL(
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)
;
-- >>>>>>> The ANY Keyword <<<<<<<
-- SELECY clients with at least two invoices
SELECT *
FROM clients
WHERE client_id IN (
SELECT client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) >= 2
)
;
SELECT *
FROM clients
WHERE client_id = ANY (
SELECT client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) >= 2
)
;
-- >>>>>>> Correlated Subqueries <<<<<<<
-- for each employee
-- calculate the avg salary for employee.office
-- return the employee if salary > avg
USE sql_hr;
SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE office_id = e.office_id
)
;
-- >>>>>>> The EXISTS Operator <<<<<<<
-- Selecy clients that have an invoice
SELECT *
FROM clients
WHERE client_id IN(
SELECT DISTINCT client_id
FROM invoices
)
;
SELECT *
FROM clients c
WHERE EXISTS(
SELECT DISTINCT client_id
FROM invoices
WHERE client_id = c.client_id
)
;
## it is better to use EXISTS when it comes to great data.
-- >>>>>>> Subqueries in the SELECT Clause <<<<<<<
USE sql_invoicing;
SELECT
invoice_id,
invoice_total,
(SELECT AVG(invoice_total)
FROM invoices) AS invoice_average,
invoice_total - (SELECT invoice_average) AS difference
FROM invoices
;
-- >>>>>>> Subqueries in the FROM Clause <<<<<<<
SELECT *
FROM(
SELECT
client_id,
name,
(SELECT SUM(invoice_total)
FROM invoices
WHERE client_id = c.client_id) AS total_sales,
(SELECT AVG(invoice_total)
FROM invoices) AS average,
(SELECT total_sales - average) AS difference
FROM clients c
) AS sales_summary
WHERE total_sales IS NOT NULL
;
-- >>>>>>> Numeric Functions <<<<<<<
SELECT ROUND(5.7345, 1)
UNION
SELECT TRUNCATE(5.7345, 2)
UNION
SELECT CEILING(5.7345)
UNION
SELECT ABS(-5.2)
UNION
SELECT RAND()
;
-- >>>>>>> String Functions <<<<<<<
SELECT LENGTH('sky')
UNION
SELECT UPPER('sky')
UNION
SELECT LOWER('SKY0')
UNION
SELECT LTRIM(' SKY1')
UNION
SELECT RTRIM('SKY2 ')
UNION
SELECT TRIM(' SKY3 ')
UNION
SELECT LEFT('Kindergarten', 4)
UNION
SELECT RIGHT('Kindergarten', 4)
UNION
SELECT SUBSTRING('Kindergarten', 3, 5)
UNION
SELECT LOCATE('N','Kindergarten')
UNION
SELECT LOCATE('garten','Kindergarten')
UNION
SELECT REPLACE('Kindergarten', 'garten', 'garden')
UNION
SELECT CONCAT('first','last')
;
USE sql_store;
SELECT CONCAT(first_name, ' ', last_name) AS FULL_NAME
FROM customers
;
-- >>>>>>> Date Functions in MySQL <<<<<<<
SELECT NOW(), CURDATE(), CURTIME()
;
SELECT YEAR(NOW())
UNION
SELECT MONTH(NOW())
UNION
SELECT DAY(NOW())
UNION
SELECT HOUR(NOW())
UNION
SELECT MINUTE(NOW())
UNION
SELECT DAYNAME(NOW())
UNION
SELECT MONTHNAME(NOW())
;
SELECT EXTRACT(DAY FROM NOW())
UNION
SELECT EXTRACT(YEAR FROM NOW())
;
-- >>>>>>> Formatting Dates and Times <<<<<<<
SELECT DATE_FORMAT(NOW(), '%y')
UNION
SELECT DATE_FORMAT(NOW(), '%M %Y')
;
-- >>>>>>> Calculating Dates and Times <<<<<<<
SELECT DATE_ADD(NOW(), INTERVAL -1 DAY)
UNION
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY)
UNION
SELECT DATEDIFF('2019-01-01 17:00','2019-01-05 09:00')
UNION
SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02')
;
-- >>>>>>> The IFNULL and COALESCE Functions <<<<<<<
USE sql_store;
SELECT
order_id,
IFNULL(shipper_id, 'Not assigned') AS shipper
FROM orders
;
SELECT
order_id,
COALESCE(shipper_id, comments, 'Not assigned') AS shipper # first is shipper_id; second is comments
FROM orders
;
-- >>>>>>> The IF Function <<<<<<<
SELECT
order_id,
order_date,
IF(
YEAR(order_date) = '2019-01-30',
'Active',
'Archived') AS category
FROM orders
;
-- >>>>>>> The CASE Operator <<<<<<<
SELECT
order_id,
order_date,
CASE
WHEN YEAR(order_date) = 2019 THEN 'Active'
WHEN YEAR(order_date) = 2019 - 1 THEN 'Last Year'
WHEN YEAR(order_date) = 2019 < 1 THEN 'Archived'
ELSE 'Future'
END AS category
FROM orders
;
-- >>>>>>> Creating Views <<<<<<<
USE sql_invoicing;
CREATE VIEW sales_by_client AS
SELECT
c.client_id,
c.name,
SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING(client_id)
GROUP BY client_id, name
;
SELECT *
FROM sales_by_client
JOIN clients USING (client_id)
ORDER BY total_sales DESC
;
-- >>>>>>> Altering or Dropping Views <<<<<<<
DROP VIEW sales_by_client
;
CREATE OR REPLACE VIEW sales_by_client AS
SELECT
c.client_id,
c.name,
SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id, name
;
-- >>>>>>> Updatable Views <<<<<<<
-- DISTINCT
-- Aggregaet Functions (MIN, MAX, SUM)
-- GROUP BY / HAVING
-- UNION
CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT
invoice_id,
number,
client_id,
invoice_total,
payment_total,
invoice_total - payment_total AS balance,
invoice_date,
due_date,
payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0
;
DELETE FROM invoices_with_balance
WHERE invoice_id = 1
;
UPDATE invoices_with_balance
SET due_date = DATE_ADD(due_date, INTERVAL 2 DAY)
WHERE invoice_id = 2
;
-- >>>>>>> THE WITH OPTION CHECK Clause <<<<<<<
UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 3
;
CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT
invoice_id,
number,
client_id,
invoice_total,
payment_total,
invoice_total - payment_total AS balance,
invoice_date,
due_date,
payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0
WITH CHECK OPTION
;
-- >>>>>>> What are Stored Procedures <<<<<<<
## Store and organize SQL
## Faster execution
## Data security
-- >>>>>>> Creating a Stored Procedure <<<<<<<
USE sql_invoicing;
DELIMITER $
CREATE PROCEDURE get_clients()
BEGIN
SELECT * FROM clients;
END$
DELIMITER ;
## CALL get_clients()
-- >>>>>>> Creating Procedures Using MySQLWorkbench <<<<<<<
-- >>>>>>> Dropping Stored Procedures <<<<<<<
DROP PROCEDURE IF EXISTS get_clients_by_state
;

USE sql_store; 
 
-- >>>>>>> The SELECT, WHERE Clause <<<<<<< 
SELECT 
        first_name, 
    last_name, 
    points, 
    (points + 10) * 100 AS 'discount factor' 
FROM customers 
-- WHERE customer_id = 1 
-- ORDER BY first_name 
; 
 
-- >>>>>>> The AND, OR and NOT Operators <<<<<<< 
SELECT * 
FROM customers 
WHERE birth_date > '1990-01-01' OR (points > 1000 AND state = 'VA'); 
-- WHERE birth_date > '1990-01-01' OR points > 1000 AND state = 'VA' 
 
SELECT * 
FROM customers 
WHERE NOT (birth_date > '1990-01-01' OR points > 1000); 
 
 
SELECT * 
FROM customers 
WHERE birth_date <= '1990-01-01' AND points <= 1000; 
 
-- >>>>>>> The IN Operato <<<<<<< 
SELECT * 
FROM customers 
WHERE state IN ('VA','FL','GA') 
-- WHERE state = 'VA' OR state = 'GA' OR state = 'FL' 
-- WHERE state NOT IN ('VA','FL','GA') 
; 
 
-- >>>>>>> The BETWEEN Operator <<<<<<< 
SELECT * 
FROM customers 
WHERE points BETWEEN 1000 AND 3000 
-- WHERE points >= 1000 AND points <= 3000 
; 
 
-- >>>>>>> The LIKE Operator <<<<<<< 
SELECT * 
FROM customers 
WHERE last_name LIKE 'b%' ## last name start with 'b/B' 
-- WHERE last_name LIKE '%b%' 
-- WHERE last_name LIKE '%y' 
; 
 
SELECT * 
FROM customers 
WHERE last_name LIKE 'b____y' 
-- % any number of characters 
-- _ single character 
; 
 
-- >>>>>>> The REGEXP Operator <<<<<<< 
SELECT * 
FROM customers 
WHERE last_name REGEXP 'field$|mac|rose' 
-- WHERE last_name REGEXP '^field' 
-- WHERE last_name REGEXP 'field 
 
-- WHERE last_name REGEXP 'field|mac' 
-- WHERE last_name REGEXP '[gim]e' 
-- ^ beginning 
-- $ end 
-- | logical or 
-- [abcd] 
-- [a-f] 
; 
-- >>>>>>> The IS NULL Operator <<<<<<< 
SELECT * 
FROM customers 
WHERE phone IS NOT NULL 
-- WHERE phone IS NULL 
; 
 
-- >>>>>>> The ORDER BY Clause <<<<<<< 
SELECT * 
FROM customers 
ORDER BY state DESC, first_name DESC 
-- ORDER BY state, first_name 
; 
 
SELECT first_name, last_name, 10 AS points 
FROM customers 
ORDER BY points, first_name 
; 
 
SELECT first_name, last_name, 10 AS points 
FROM customers 
ORDER BY 1, 2 
; 
 
-- >>>>>>> The LIMIT Clause <<<<<<< 
SELECT * 
FROM customers 
LIMIT 3 
; 
 
SELECT * 
FROM customers 
LIMIT 6, 3 
-- page 1: 1 - 3 
-- page 2: 4 - 6 
-- page 3: 7 - 9 
; 
-- >>>>>>> Inner Joins <<<<<<< 
SELECT order_id, o.customer_id, first_name, last_name 
FROM orders o 
JOIN customers c 
        ON o.customer_id = c.customer_id 
; 
-- >>>>>>> Joining Across Databases <<<<<<< 
SELECT * 
FROM order_items oi 
JOIN sql_inventory.products p 
        ON oi.product_id = p.product_id 
; 
USE sql_inventory; 
 
SELECT * 
FROM sql_store.order_items oi 
JOIN products p 
        ON oi.product_id = p.product_id 
; 
-- >>>>>>> Self Joins <<<<<<< 
USE sql_hr; 
 
SELECT 
        e.employee_id, 
    e.first_name, 
    m.first_name AS manager 
FROM employees e 
JOIN employees m 
        ON e.reports_to = m.employee_id 
; 
-- >>>>>>> Joining Multiple Tables <<<<<<< 
USE sql_store; 
 
SELECT 
        o.order_id, 
    o.order_date, 
    c.first_name, 
    c.last_name, 
    os.name AS status 
FROM orders o 
JOIN customers c 
        ON o.customer_id = c.customer_id 
JOIN order_statuses os 
        ON o.status = os.order_status_id 
; 
-- >>>>>>> Compound Join Conditions <<<<<<< 
USE sql_store; 
 
SELECT * 
FROM order_items oi 
JOIN order_item_notes oin 
        ON oi.order_id = oin.order_id 
        AND oi.product_id = oin.product_id 
; 
-- >>>>>>> Implicit Join Syntax <<<<<<< 
SELECT * 
FROM orders o 
JOIN customers c 
        ON o.customer_id = c.customer_id 
; 
SELECT * 
FROM orders o, customers c 
WHERE o.customer_id = c.customer_id  ## euqal to the previous one 
; 
-- >>>>>>> Outer Joins <<<<<<< 
SELECT 
        c.customer_id, 
    c.first_name, 
    o.order_id 
FROM customers c 
JOIN orders o 
        ON c.customer_id = o.customer_id 
ORDER BY c.customer_id 
; 
 
SELECT 
        c.customer_id, 
    c.first_name, 
    o.order_id 
FROM customers c 
LEFT JOIN orders o 
        ON c.customer_id = o.customer_id 
ORDER BY c.customer_id 
; 
 
SELECT 
        c.customer_id, 
    c.first_name, 
    o.order_id 
FROM orders o 
RIGHT JOIN customers c 
        ON c.customer_id = o.customer_id 
ORDER BY c.customer_id 
; 
-- >>>>>>> Outer Join Between Multiple Tables <<<<<<< 
SELECT 
        c.customer_id, 
    c.first_name, 
    o.order_id, 
    sh.name AS shipper 
FROM customers c 
LEFT JOIN orders o 
        ON c.customer_id = o.customer_id 
LEFT JOIN shippers sh 
        ON o.shipper_id = sh.shipper_id 
ORDER BY c.customer_id 
; 
-- >>>>>>> Self Outer Joins <<<<<<< 
USE sql_hr; 
 
SELECT 
        e.employee_id, 
    e.first_name, 
    m.first_name AS manager 
FROM employees e 
LEFT JOIN employees m 
        ON e.reports_to = m.employee_id 
; 
-- >>>>>>> The USING Clause <<<<<<< 
USE sql_store 
; 
SELECT 
        o.order_id, 
    c.first_name, 
    sh.name AS shipper 
FROM orders o 
JOIN customers c 
        USING (customer_id) 
LEFT JOIN shippers sh 
        USING (shipper_id) 
; 
SELECT * 
FROM order_items oi 
LEFT JOIN order_item_notes oin 
        USING (order_id, product_id) 
; 
-- >>>>>>> Natural Joins <<<<<<< 
USE sql_store; 
SELECT * 
FROM orders o 
NATURAL JOIN customers c 
; 
-- >>>>>>> Cross Joins <<<<<<< 
SELECT 
        c.first_name AS customer, 
    p.name AS product 
FROM customers c 
CROSS JOIN products p 
ORDER BY c.first_name 
; 
 
SELECT 
        c.first_name AS customer, 
    p.name AS product 
FROM customers c, products p 
ORDER BY c.first_name 
; 
-- >>>>>>> Unions <<<<<<< 
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' 
; 
SELECT first_name AS full_name 
FROM customers 
UNION 
SELECT name 
FROM shippers 
; 
-- >>>>>>> Column Attributes <<<<<<< 
 
-- >>>>>>> Inserting a Row <<<<<<< 
INSERT INTO customers ( 
        first_name, 
    last_name, 
    birth_date, 
    address, 
    city, 
    state) 
VALUES ( 
    'John', 
    'Smith', 
    '1990-01-01', 
    'address', 
    'city', 
    'CA') 
; 
-- >>>>>>> Inserting Multiple Rows <<<<<<< 
INSERT INTO shippers (name) 
VALUES ('Shipper1'), 
           ('Shipper2'), 
       ('Shipper3') 
; 
-- >>>>>>> Inserting Hierarchical Rows <<<<<<< 
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) 
; 
-- >>>>>>> Creating a Copy of a Table <<<<<<< 
CREATE TABLE orders_archived AS 
SELECT * 
FROM orders 
; 
INSERT INTO orders_archived 
SELECT * 
FROM orders 
WHERE order_date < '2019-01-01' 
; 
-- >>>>>>> Updating a Single Row <<<<<<< 
UPDATE invoices 
SET payment_total = DEFAULT, payment_date = NULL 
WHERE invoice_id = 1 
; 
UPDATE invoices 
SET 
        payment_total = invoice_total * 0.5, 
    payment_date = due_date 
WHERE invoice_id = 3 
; 
-- >>>>>>> Updating Multiple Rows <<<<<<< 
UPDATE invoices 
SET 
        payment_total = invoice_total * 0.5, 
    payment_date = due_date 
WHERE client_id = 3 ## Preferences --> SQL Editor --> safe update 
-- WHERE client_id IN (3,4) 
; 
-- >>>>>>> Using Subqueries in Updates <<<<<<< 
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 state IN ('CA', 'NY')) 
; 
UPDATE invoices 
SET 
        payment_total = invoice_total * 0.5, 
    payment_date = due_date 
WHERE payment_date IS NULL 
; 
-- >>>>>>> Deleting Rows <<<<<<< 
USE sql_invoicing; 
 
DELETE FROM invoices 
WHERE client_id = ( 
                        SELECT client_id 
                        FROM clients 
                        WHERE name = 'Myworks') 
; 
-- >>>>>>> Restoring the Databases <<<<<<< 
 
-- >>>>>>> Aggregate Functions <<<<<<< 
USE sql_invoicing; 
 
SELECT 
        MAX(invoice_total) AS hightest, 
    MIN(invoice_total) AS lowest, 
    AVG(invoice_total) AS avverage, 
    SUM(invoice_total * 1.1) AS total, 
    Count(invoice_total) AS number_of_invoices, 
    Count(payment_date) AS count_of_payments, 
    Count(DISTINCT(client_id)) AS total_records, ## without duplication 
    Count(*) AS total_records1 
FROM invoices 
WHERE invoice_date > '2019-07-01' 
; 
-- >>>>>>> The GROUP BY Clause <<<<<<< 
SELECT 
        state, 
    city, 
        SUM(invoice_total) AS total_sales 
FROM invoices 
JOIN clients 
        USING (client_id) 
WHERE invoice_date >= '2019-07-01' 
GROUP BY state, city 
ORDER BY total_sales DESC 
; 
-- >>>>>>> The HAVING Clause <<<<<<< 
SELECT 
        client_id, 
        SUM(invoice_total) AS total_sales, 
    COUNT(*) AS number_of_invoivrs 
FROM invoices 
-- WHERE client_id <= 5 ## Here should not be aggregation function 
GROUP BY client_id 
HAVING total_sales > 500 AND number_of_invoivrs > 5## filter data after group --> aggregation function 
; 
-- >>>>>>> The ROLLUP Operator <<<<<<< 
SELECT 
        state, 
    city, 
        SUM(invoice_total) AS total_sales 
FROM invoices i 
JOIN clients c USING (client_id) 
GROUP BY state, city WITH ROLLUP 
; 
-- >>>>>>> Subqueries <<<<<<< 
SELECT * 
FROM products 
WHERE unit_price > ( 
        SELECT unit_price 
    FROM products 
    WHERE product_id = 3 
) 
; 
-- >>>>>>> The IN Operator <<<<<<< 
USE sql_store; 
 
SELECT * 
FROM products 
WHERE product_id NOT IN ( 
SELECT DISTINCT product_id 
FROM order_items # product 7 has never been ordered 
) 
; 
-- >>>>>>> Subqueries vs Joins <<<<<<< 
USE sql_invoicing; 
 
SELECT * 
FROM clients 
WHERE client_id NOT IN ( 
        SELECT DISTINCT client_id 
    FROM invoices 
) 
; 
 
SELECT * 
FROM clients 
LEFT JOIN invoices USING (client_id) 
WHERE invoice_id IS NULL 
; 
-- >>>>>>> The ALL Keyword <<<<<<< 
USE sql_invoicing; 
 
SELECT * 
FROM invoices 
WHERE invoice_total > ( 
        SELECT MAX(invoice_total) 
    FROM invoices 
    WHERE client_id = 3 
) 
; 
-- Select invoices larger than all invoices of client 3 
 
SELECT * 
FROM invoices 
WHERE invoice_total > ALL( 
        SELECT invoice_total 
        FROM invoices 
        WHERE client_id = 3 
) 
; 
-- >>>>>>> The ANY Keyword <<<<<<< 
 
-- SELECY clients with at least two invoices 
SELECT * 
FROM clients 
WHERE client_id IN ( 
        SELECT client_id 
        FROM invoices 
        GROUP BY client_id 
        HAVING COUNT(*) >= 2 
) 
; 
SELECT * 
FROM clients 
WHERE client_id = ANY ( 
        SELECT client_id 
        FROM invoices 
        GROUP BY client_id 
        HAVING COUNT(*) >= 2 
) 
; 
-- >>>>>>> Correlated Subqueries <<<<<<< 
 
-- for each employee 
--                 calculate the avg salary for employee.office 
--                 return the employee if salary > avg 
USE sql_hr; 
 
SELECT * 
FROM employees e 
WHERE salary > ( 
        SELECT AVG(salary) 
    FROM employees 
    WHERE office_id = e.office_id 
) 
; 
-- >>>>>>> The EXISTS Operator <<<<<<< 
 
-- Selecy clients that have an invoice 
SELECT * 
FROM clients 
WHERE client_id IN( 
        SELECT DISTINCT client_id 
    FROM invoices 
) 
; 
SELECT * 
FROM clients c 
WHERE EXISTS( 
        SELECT DISTINCT client_id 
    FROM invoices 
    WHERE client_id = c.client_id 
) 
; 
## it is better to use EXISTS when it comes to great data. 
 
-- >>>>>>> Subqueries in the SELECT Clause <<<<<<< 
USE sql_invoicing; 
 
SELECT 
        invoice_id, 
    invoice_total, 
    (SELECT AVG(invoice_total) 
                FROM invoices) AS invoice_average, 
        invoice_total - (SELECT invoice_average) AS difference 
FROM invoices 
; 
-- >>>>>>> Subqueries in the FROM Clause <<<<<<< 
SELECT * 
FROM( 
SELECT 
        client_id, 
    name, 
    (SELECT SUM(invoice_total) 
                FROM invoices 
        WHERE client_id = c.client_id) AS total_sales, 
        (SELECT AVG(invoice_total) 
                FROM invoices) AS average, 
        (SELECT total_sales - average) AS difference 
FROM clients c 
) AS sales_summary 
WHERE total_sales IS NOT NULL 
; 
-- >>>>>>> Numeric Functions <<<<<<< 
SELECT ROUND(5.7345, 1) 
UNION 
SELECT TRUNCATE(5.7345, 2) 
UNION 
SELECT CEILING(5.7345) 
UNION 
SELECT ABS(-5.2) 
UNION 
SELECT RAND() 
; 
-- >>>>>>> String Functions <<<<<<< 
SELECT LENGTH('sky') 
UNION 
SELECT UPPER('sky') 
UNION 
SELECT LOWER('SKY0') 
UNION 
SELECT LTRIM('    SKY1') 
UNION 
SELECT RTRIM('SKY2     ') 
UNION 
SELECT TRIM('    SKY3     ') 
UNION 
SELECT LEFT('Kindergarten', 4) 
UNION 
SELECT RIGHT('Kindergarten', 4) 
UNION 
SELECT SUBSTRING('Kindergarten', 3, 5) 
UNION 
SELECT LOCATE('N','Kindergarten') 
UNION 
SELECT LOCATE('garten','Kindergarten') 
UNION 
SELECT REPLACE('Kindergarten', 'garten', 'garden') 
UNION 
SELECT CONCAT('first','last') 
; 
 
USE sql_store; 
 
SELECT CONCAT(first_name, ' ', last_name) AS FULL_NAME 
FROM customers 
; 
-- >>>>>>> Date Functions in MySQL <<<<<<< 
SELECT NOW(), CURDATE(), CURTIME() 
; 
SELECT YEAR(NOW()) 
UNION 
SELECT MONTH(NOW()) 
UNION 
SELECT DAY(NOW()) 
UNION 
SELECT HOUR(NOW()) 
UNION 
SELECT MINUTE(NOW()) 
UNION 
SELECT DAYNAME(NOW()) 
UNION 
SELECT MONTHNAME(NOW()) 
; 
SELECT EXTRACT(DAY FROM NOW()) 
UNION 
SELECT EXTRACT(YEAR FROM NOW()) 
; 
-- >>>>>>> Formatting Dates and Times <<<<<<< 
SELECT DATE_FORMAT(NOW(), '%y') 
UNION 
SELECT DATE_FORMAT(NOW(), '%M %Y') 
; 
-- >>>>>>> Calculating Dates and Times <<<<<<< 
SELECT DATE_ADD(NOW(), INTERVAL -1 DAY) 
UNION 
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY) 
UNION 
SELECT DATEDIFF('2019-01-01 17:00','2019-01-05 09:00') 
UNION 
SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02') 
; 
-- >>>>>>> The IFNULL and COALESCE Functions <<<<<<< 
USE sql_store; 
 
SELECT 
        order_id, 
    IFNULL(shipper_id, 'Not assigned') AS shipper 
FROM orders 
; 
SELECT 
        order_id, 
    COALESCE(shipper_id, comments, 'Not assigned') AS shipper # first is shipper_id; second is comments 
FROM orders 
; 
-- >>>>>>> The IF Function <<<<<<< 
SELECT 
        order_id, 
    order_date, 
        IF( 
                YEAR(order_date) = '2019-01-30', 
        'Active', 
        'Archived') AS category 
FROM orders 
; 
-- >>>>>>> The CASE Operator <<<<<<< 
SELECT 
        order_id, 
    order_date, 
        CASE 
                WHEN YEAR(order_date) = 2019 THEN 'Active' 
        WHEN YEAR(order_date) = 2019 - 1 THEN 'Last Year' 
        WHEN YEAR(order_date) = 2019 < 1 THEN 'Archived' 
        ELSE 'Future' 
        END AS category 
FROM orders 
; 
-- >>>>>>> Creating Views <<<<<<< 
USE sql_invoicing; 
 
CREATE VIEW sales_by_client AS 
SELECT 
        c.client_id, 
    c.name, 
    SUM(invoice_total) AS total_sales 
FROM clients c 
JOIN invoices i USING(client_id) 
GROUP BY client_id, name 
; 
SELECT * 
FROM sales_by_client 
JOIN clients USING (client_id) 
ORDER BY total_sales DESC 
; 
-- >>>>>>> Altering or Dropping Views <<<<<<< 
DROP VIEW sales_by_client 
; 
CREATE OR REPLACE VIEW sales_by_client AS 
SELECT 
        c.client_id, 
    c.name, 
    SUM(invoice_total) AS total_sales 
FROM clients c 
JOIN invoices i USING (client_id) 
GROUP BY client_id, name 
; 
-- >>>>>>> Updatable Views <<<<<<< 
-- DISTINCT 
-- Aggregaet Functions (MIN, MAX, SUM) 
-- GROUP BY / HAVING 
-- UNION 
CREATE OR REPLACE VIEW invoices_with_balance AS 
SELECT 
        invoice_id, 
    number, 
    client_id, 
    invoice_total, 
    payment_total, 
    invoice_total - payment_total AS balance, 
    invoice_date, 
    due_date, 
    payment_date 
FROM invoices 
WHERE (invoice_total - payment_total) > 0 
; 
DELETE FROM invoices_with_balance 
WHERE invoice_id = 1 
; 
UPDATE invoices_with_balance 
SET due_date = DATE_ADD(due_date, INTERVAL 2 DAY) 
WHERE invoice_id = 2 
; 
-- >>>>>>> THE WITH OPTION CHECK Clause <<<<<<< 
UPDATE invoices_with_balance 
SET payment_total = invoice_total 
WHERE invoice_id = 3 
; 
CREATE OR REPLACE VIEW invoices_with_balance AS 
SELECT 
        invoice_id, 
    number, 
    client_id, 
    invoice_total, 
    payment_total, 
    invoice_total - payment_total AS balance, 
    invoice_date, 
    due_date, 
    payment_date 
FROM invoices 
WHERE (invoice_total - payment_total) > 0 
WITH CHECK OPTION 
; 
-- >>>>>>> What are Stored Procedures <<<<<<< 
## Store and organize SQL 
## Faster execution 
## Data security 
-- >>>>>>> Creating a Stored Procedure <<<<<<< 
USE sql_invoicing; 
DELIMITER $ 
CREATE PROCEDURE get_clients() 
BEGIN 
        SELECT * FROM clients; 
 
END$ 
 
DELIMITER ; 
## CALL get_clients() 
 
-- >>>>>>> Creating Procedures Using MySQLWorkbench <<<<<<< 
 
-- >>>>>>> Dropping Stored Procedures <<<<<<< 
DROP PROCEDURE IF EXISTS get_clients_by_state 
;

/db_自贡黄明儒_ · 发表于 2021-2-2 08:26:41

SQL本身就是一种语言了,还是比较复杂。要在lisp中使用，先得在VB中练习好，我的方法就是两个字：放弃。

marting · 发表于 2021-2-2 08:51:32

/db_自贡黄明儒_ 发表于 2021-2-2 08:26
SQL本身就是一种语言了,还是比较复杂。要在lisp中使用，先得在VB中练习好，我的方法就是两个字：放弃。

一通百通，10分钟学会的查询语句，估计可以用到80%的场景

SHUNDocker · 发表于 2021-2-2 10:09:04

CAD结合数据库和Excel开发，是很多好的数据处理方式

csy0415 · 发表于 2023-3-29 14:22:22

结合SQL，程序的应用会更加广泛，也可以做一些大量数据类交互类的编程，很有意义。

kmliro_2017 · 发表于 2023-7-16 08:12:35

谢谢分享！！！！！！

账号		自动登录	找回密码
密码			立即注册

[分享] SQL常用命令

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。