论坛 › CAD与数据库编程 › SQL常用命令

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

SQL常用命令

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 'e'
-- ^ beginning
-- $ end
-- | logical or
--
--
;
-- >>>>>>> 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，程序的应用会更加广泛，也可以做一些大量数据类交互类的编程，很有意义。{:1_23:}

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

谢谢分享！！！！！！

查看完整版本: SQL常用命令