SQL 部分常用命令

hj170520

MYSQL 常用命令的使用，
下面是我学习做的笔记。 很简单的命令，很简单的书写方式。后续会补充

比较适合初学者，一个个命令来调试着学。
这段代码需要的数据集命令我放在了“一楼”，下载MySqlWorkBench运行那行代码就有数据来练手了！

[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 '[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
;

hj170520

上述命令可以用下面这个数据集来运行，我就不发文件了，直接发命令，先运行这个

[SQL] 纯文本查看 复制代码

DROP DATABASE IF EXISTS `sql_invoicing`;
CREATE DATABASE `sql_invoicing`; 
USE `sql_invoicing`;

SET NAMES utf8 ;
SET character_set_client = utf8mb4 ;

CREATE TABLE `payment_methods` (
  `payment_method_id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`payment_method_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `payment_methods` VALUES (1,'Credit Card');
INSERT INTO `payment_methods` VALUES (2,'Cash');
INSERT INTO `payment_methods` VALUES (3,'PayPal');
INSERT INTO `payment_methods` VALUES (4,'Wire Transfer');

CREATE TABLE `clients` (
  `client_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `address` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` char(2) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `clients` VALUES (1,'Vinte','3 Nevada Parkway','Syracuse','NY','315-252-7305');
INSERT INTO `clients` VALUES (2,'Myworks','34267 Glendale Parkway','Huntington','WV','304-659-1170');
INSERT INTO `clients` VALUES (3,'Yadel','096 Pawling Parkway','San Francisco','CA','415-144-6037');
INSERT INTO `clients` VALUES (4,'Kwideo','81674 Westerfield Circle','Waco','TX','254-750-0784');
INSERT INTO `clients` VALUES (5,'Topiclounge','0863 Farmco Road','Portland','OR','971-888-9129');

CREATE TABLE `invoices` (
  `invoice_id` int(11) NOT NULL,
  `number` varchar(50) NOT NULL,
  `client_id` int(11) NOT NULL,
  `invoice_total` decimal(9,2) NOT NULL,
  `payment_total` decimal(9,2) NOT NULL DEFAULT '0.00',
  `invoice_date` date NOT NULL,
  `due_date` date NOT NULL,
  `payment_date` date DEFAULT NULL,
  PRIMARY KEY (`invoice_id`),
  KEY `FK_client_id` (`client_id`),
  CONSTRAINT `FK_client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `invoices` VALUES (1,'91-953-3396',2,101.79,0.00,'2019-03-09','2019-03-29',NULL);
INSERT INTO `invoices` VALUES (2,'03-898-6735',5,175.32,8.18,'2019-06-11','2019-07-01','2019-02-12');
INSERT INTO `invoices` VALUES (3,'20-228-0335',5,147.99,0.00,'2019-07-31','2019-08-20',NULL);
INSERT INTO `invoices` VALUES (4,'56-934-0748',3,152.21,0.00,'2019-03-08','2019-03-28',NULL);
INSERT INTO `invoices` VALUES (5,'87-052-3121',5,169.36,0.00,'2019-07-18','2019-08-07',NULL);
INSERT INTO `invoices` VALUES (6,'75-587-6626',1,157.78,74.55,'2019-01-29','2019-02-18','2019-01-03');
INSERT INTO `invoices` VALUES (7,'68-093-9863',3,133.87,0.00,'2019-09-04','2019-09-24',NULL);
INSERT INTO `invoices` VALUES (8,'78-145-1093',1,189.12,0.00,'2019-05-20','2019-06-09',NULL);
INSERT INTO `invoices` VALUES (9,'77-593-0081',5,172.17,0.00,'2019-07-09','2019-07-29',NULL);
INSERT INTO `invoices` VALUES (10,'48-266-1517',1,159.50,0.00,'2019-06-30','2019-07-20',NULL);
INSERT INTO `invoices` VALUES (11,'20-848-0181',3,126.15,0.03,'2019-01-07','2019-01-27','2019-01-11');
INSERT INTO `invoices` VALUES (13,'41-666-1035',5,135.01,87.44,'2019-06-25','2019-07-15','2019-01-26');
INSERT INTO `invoices` VALUES (15,'55-105-9605',3,167.29,80.31,'2019-11-25','2019-12-15','2019-01-15');
INSERT INTO `invoices` VALUES (16,'10-451-8824',1,162.02,0.00,'2019-03-30','2019-04-19',NULL);
INSERT INTO `invoices` VALUES (17,'33-615-4694',3,126.38,68.10,'2019-07-30','2019-08-19','2019-01-15');
INSERT INTO `invoices` VALUES (18,'52-269-9803',5,180.17,42.77,'2019-05-23','2019-06-12','2019-01-08');
INSERT INTO `invoices` VALUES (19,'83-559-4105',1,134.47,0.00,'2019-11-23','2019-12-13',NULL);

CREATE TABLE `payments` (
  `payment_id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `amount` decimal(9,2) NOT NULL,
  `payment_method` tinyint(4) NOT NULL,
  PRIMARY KEY (`payment_id`),
  KEY `fk_client_id_idx` (`client_id`),
  KEY `fk_invoice_id_idx` (`invoice_id`),
  KEY `fk_payment_payment_method_idx` (`payment_method`),
  CONSTRAINT `fk_payment_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`invoice_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_payment_method` FOREIGN KEY (`payment_method`) REFERENCES `payment_methods` (`payment_method_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `payments` VALUES (1,5,2,'2019-02-12',8.18,1);
INSERT INTO `payments` VALUES (2,1,6,'2019-01-03',74.55,1);
INSERT INTO `payments` VALUES (3,3,11,'2019-01-11',0.03,1);
INSERT INTO `payments` VALUES (4,5,13,'2019-01-26',87.44,1);
INSERT INTO `payments` VALUES (5,3,15,'2019-01-15',80.31,1);
INSERT INTO `payments` VALUES (6,3,17,'2019-01-15',68.10,1);
INSERT INTO `payments` VALUES (7,5,18,'2019-01-08',32.77,1);
INSERT INTO `payments` VALUES (8,5,18,'2019-01-08',10.00,2);


DROP DATABASE IF EXISTS `sql_store`;
CREATE DATABASE `sql_store`;
USE `sql_store`;

CREATE TABLE `products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `quantity_in_stock` int(11) NOT NULL,
  `unit_price` decimal(4,2) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `products` VALUES (1,'Foam Dinner Plate',70,1.21);
INSERT INTO `products` VALUES (2,'Pork - Bacon,back Peameal',49,4.65);
INSERT INTO `products` VALUES (3,'Lettuce - Romaine, Heart',38,3.35);
INSERT INTO `products` VALUES (4,'Brocolinni - Gaylan, Chinese',90,4.53);
INSERT INTO `products` VALUES (5,'Sauce - Ranch Dressing',94,1.63);
INSERT INTO `products` VALUES (6,'Petit Baguette',14,2.39);
INSERT INTO `products` VALUES (7,'Sweet Pea Sprouts',98,3.29);
INSERT INTO `products` VALUES (8,'Island Oasis - Raspberry',26,0.74);
INSERT INTO `products` VALUES (9,'Longan',67,2.26);
INSERT INTO `products` VALUES (10,'Broom - Push',6,1.09);


CREATE TABLE `shippers` (
  `shipper_id` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`shipper_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `shippers` VALUES (1,'Hettinger LLC');
INSERT INTO `shippers` VALUES (2,'Schinner-Predovic');
INSERT INTO `shippers` VALUES (3,'Satterfield LLC');
INSERT INTO `shippers` VALUES (4,'Mraz, Renner and Nolan');
INSERT INTO `shippers` VALUES (5,'Waters, Mayert and Prohaska');


CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `birth_date` date DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` char(2) NOT NULL,
  `points` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `customers` VALUES (1,'Babara','MacCaffrey','1986-03-28','781-932-9754','0 Sage Terrace','Waltham','MA',2273);
INSERT INTO `customers` VALUES (2,'Ines','Brushfield','1986-04-13','804-427-9456','14187 Commercial Trail','Hampton','VA',947);
INSERT INTO `customers` VALUES (3,'Freddi','Boagey','1985-02-07','719-724-7869','251 Springs Junction','Colorado Springs','CO',2967);
INSERT INTO `customers` VALUES (4,'Ambur','Roseburgh','1974-04-14','407-231-8017','30 Arapahoe Terrace','Orlando','FL',457);
INSERT INTO `customers` VALUES (5,'Clemmie','Betchley','1973-11-07',NULL,'5 Spohn Circle','Arlington','TX',3675);
INSERT INTO `customers` VALUES (6,'Elka','Twiddell','1991-09-04','312-480-8498','7 Manley Drive','Chicago','IL',3073);
INSERT INTO `customers` VALUES (7,'Ilene','Dowson','1964-08-30','615-641-4759','50 Lillian Crossing','Nashville','TN',1672);
INSERT INTO `customers` VALUES (8,'Thacher','Naseby','1993-07-17','941-527-3977','538 Mosinee Center','Sarasota','FL',205);
INSERT INTO `customers` VALUES (9,'Romola','Rumgay','1992-05-23','559-181-3744','3520 Ohio Trail','Visalia','CA',1486);
INSERT INTO `customers` VALUES (10,'Levy','Mynett','1969-10-13','404-246-3370','68 Lawn Avenue','Atlanta','GA',796);


CREATE TABLE `order_statuses` (
  `order_status_id` tinyint(4) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`order_status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `order_statuses` VALUES (1,'Processed');
INSERT INTO `order_statuses` VALUES (2,'Shipped');
INSERT INTO `order_statuses` VALUES (3,'Delivered');


CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `order_date` date NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `comments` varchar(2000) DEFAULT NULL,
  `shipped_date` date DEFAULT NULL,
  `shipper_id` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `fk_orders_customers_idx` (`customer_id`),
  KEY `fk_orders_shippers_idx` (`shipper_id`),
  KEY `fk_orders_order_statuses_idx` (`status`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_order_statuses` FOREIGN KEY (`status`) REFERENCES `order_statuses` (`order_status_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_shippers` FOREIGN KEY (`shipper_id`) REFERENCES `shippers` (`shipper_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `orders` VALUES (1,6,'2019-01-30',1,NULL,NULL,NULL);
INSERT INTO `orders` VALUES (2,7,'2018-08-02',2,NULL,'2018-08-03',4);
INSERT INTO `orders` VALUES (3,8,'2017-12-01',1,NULL,NULL,NULL);
INSERT INTO `orders` VALUES (4,2,'2017-01-22',1,NULL,NULL,NULL);
INSERT INTO `orders` VALUES (5,5,'2017-08-25',2,'','2017-08-26',3);
INSERT INTO `orders` VALUES (6,10,'2018-11-18',1,'Aliquam erat volutpat. In congue.',NULL,NULL);
INSERT INTO `orders` VALUES (7,2,'2018-09-22',2,NULL,'2018-09-23',4);
INSERT INTO `orders` VALUES (8,5,'2018-06-08',1,'Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis.',NULL,NULL);
INSERT INTO `orders` VALUES (9,10,'2017-07-05',2,'Nulla mollis molestie lorem. Quisque ut erat.','2017-07-06',1);
INSERT INTO `orders` VALUES (10,6,'2018-04-22',2,NULL,'2018-04-23',2);


CREATE TABLE `order_items` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_price` decimal(4,2) NOT NULL,
  PRIMARY KEY (`order_id`,`product_id`),
  KEY `fk_order_items_products_idx` (`product_id`),
  CONSTRAINT `fk_order_items_orders` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_order_items_products` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `order_items` VALUES (1,4,4,3.74);
INSERT INTO `order_items` VALUES (2,1,2,9.10);
INSERT INTO `order_items` VALUES (2,4,4,1.66);
INSERT INTO `order_items` VALUES (2,6,2,2.94);
INSERT INTO `order_items` VALUES (3,3,10,9.12);
INSERT INTO `order_items` VALUES (4,3,7,6.99);
INSERT INTO `order_items` VALUES (4,10,7,6.40);
INSERT INTO `order_items` VALUES (5,2,3,9.89);
INSERT INTO `order_items` VALUES (6,1,4,8.65);
INSERT INTO `order_items` VALUES (6,2,4,3.28);
INSERT INTO `order_items` VALUES (6,3,4,7.46);
INSERT INTO `order_items` VALUES (6,5,1,3.45);
INSERT INTO `order_items` VALUES (7,3,7,9.17);
INSERT INTO `order_items` VALUES (8,5,2,6.94);
INSERT INTO `order_items` VALUES (8,8,2,8.59);
INSERT INTO `order_items` VALUES (9,6,5,7.28);
INSERT INTO `order_items` VALUES (10,1,10,6.01);
INSERT INTO `order_items` VALUES (10,9,9,4.28);

CREATE TABLE `sql_store`.`order_item_notes` (
  `note_id` INT NOT NULL,
  `order_Id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `note` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`note_id`));

INSERT INTO `order_item_notes` (`note_id`, `order_Id`, `product_id`, `note`) VALUES ('1', '1', '2', 'first note');
INSERT INTO `order_item_notes` (`note_id`, `order_Id`, `product_id`, `note`) VALUES ('2', '1', '2', 'second note');


DROP DATABASE IF EXISTS `sql_hr`;
CREATE DATABASE `sql_hr`;
USE `sql_hr`;


CREATE TABLE `offices` (
  `office_id` int(11) NOT NULL,
  `address` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) NOT NULL,
  PRIMARY KEY (`office_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `offices` VALUES (1,'03 Reinke Trail','Cincinnati','OH');
INSERT INTO `offices` VALUES (2,'5507 Becker Terrace','New York City','NY');
INSERT INTO `offices` VALUES (3,'54 Northland Court','Richmond','VA');
INSERT INTO `offices` VALUES (4,'08 South Crossing','Cincinnati','OH');
INSERT INTO `offices` VALUES (5,'553 Maple Drive','Minneapolis','MN');
INSERT INTO `offices` VALUES (6,'23 North Plaza','Aurora','CO');
INSERT INTO `offices` VALUES (7,'9658 Wayridge Court','Boise','ID');
INSERT INTO `offices` VALUES (8,'9 Grayhawk Trail','New York City','NY');
INSERT INTO `offices` VALUES (9,'16862 Westend Hill','Knoxville','TN');
INSERT INTO `offices` VALUES (10,'4 Bluestem Parkway','Savannah','GA');



CREATE TABLE `employees` (
  `employee_id` int(11) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `job_title` varchar(50) NOT NULL,
  `salary` int(11) NOT NULL,
  `reports_to` int(11) DEFAULT NULL,
  `office_id` int(11) NOT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `fk_employees_offices_idx` (`office_id`),
  KEY `fk_employees_employees_idx` (`reports_to`),
  CONSTRAINT `fk_employees_managers` FOREIGN KEY (`reports_to`) REFERENCES `employees` (`employee_id`),
  CONSTRAINT `fk_employees_offices` FOREIGN KEY (`office_id`) REFERENCES `offices` (`office_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `employees` VALUES (37270,'Yovonnda','Magrannell','Executive Secretary',63996,NULL,10);
INSERT INTO `employees` VALUES (33391,'D\'arcy','Nortunen','Account Executive',62871,37270,1);
INSERT INTO `employees` VALUES (37851,'Sayer','Matterson','Statistician III',98926,37270,1);
INSERT INTO `employees` VALUES (40448,'Mindy','Crissil','Staff Scientist',94860,37270,1);
INSERT INTO `employees` VALUES (56274,'Keriann','Alloisi','VP Marketing',110150,37270,1);
INSERT INTO `employees` VALUES (63196,'Alaster','Scutchin','Assistant Professor',32179,37270,2);
INSERT INTO `employees` VALUES (67009,'North','de Clerc','VP Product Management',114257,37270,2);
INSERT INTO `employees` VALUES (67370,'Elladine','Rising','Social Worker',96767,37270,2);
INSERT INTO `employees` VALUES (68249,'Nisse','Voysey','Financial Advisor',52832,37270,2);
INSERT INTO `employees` VALUES (72540,'Guthrey','Iacopetti','Office Assistant I',117690,37270,3);
INSERT INTO `employees` VALUES (72913,'Kass','Hefferan','Computer Systems Analyst IV',96401,37270,3);
INSERT INTO `employees` VALUES (75900,'Virge','Goodrum','Information Systems Manager',54578,37270,3);
INSERT INTO `employees` VALUES (76196,'Mirilla','Janowski','Cost Accountant',119241,37270,3);
INSERT INTO `employees` VALUES (80529,'Lynde','Aronson','Junior Executive',77182,37270,4);
INSERT INTO `employees` VALUES (80679,'Mildrid','Sokale','Geologist II',67987,37270,4);
INSERT INTO `employees` VALUES (84791,'Hazel','Tarbert','General Manager',93760,37270,4);
INSERT INTO `employees` VALUES (95213,'Cole','Kesterton','Pharmacist',86119,37270,4);
INSERT INTO `employees` VALUES (96513,'Theresa','Binney','Food Chemist',47354,37270,5);
INSERT INTO `employees` VALUES (98374,'Estrellita','Daleman','Staff Accountant IV',70187,37270,5);
INSERT INTO `employees` VALUES (115357,'Ivy','Fearey','Structural Engineer',92710,37270,5);


DROP DATABASE IF EXISTS `sql_inventory`;
CREATE DATABASE `sql_inventory`;
USE `sql_inventory`;


CREATE TABLE `products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `quantity_in_stock` int(11) NOT NULL,
  `unit_price` decimal(4,2) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `products` VALUES (1,'Foam Dinner Plate',70,1.21);
INSERT INTO `products` VALUES (2,'Pork - Bacon,back Peameal',49,4.65);
INSERT INTO `products` VALUES (3,'Lettuce - Romaine, Heart',38,3.35);
INSERT INTO `products` VALUES (4,'Brocolinni - Gaylan, Chinese',90,4.53);
INSERT INTO `products` VALUES (5,'Sauce - Ranch Dressing',94,1.63);
INSERT INTO `products` VALUES (6,'Petit Baguette',14,2.39);
INSERT INTO `products` VALUES (7,'Sweet Pea Sprouts',98,3.29);
INSERT INTO `products` VALUES (8,'Island Oasis - Raspberry',26,0.74);
INSERT INTO `products` VALUES (9,'Longan',67,2.26);
INSERT INTO `products` VALUES (10,'Broom - Push',6,1.09);

hj170520

pigeon11 发表于 2021-2-1 21:27
sql学了两次了，命令就是记不住

多练练，就行了。

希望这次可以帮到你

Eaglecad

厉害了，学习

cherrypi

厉害，学习sql中。

pigeon11

sql学了两次了，命令就是记不住

非墨不白

感谢分享

myxj28

很棒的教案，学习一下方案，参考一下，对自己以后会有帮助，感谢你的分享

gary.sun

谢谢楼主分享

Greeny1016

厉害，学习sql中