sql資料庫語言練習，增刪改查

来源:https://www.cnblogs.com/markReaper/archive/2020/02/21/12342155.html
-Advertisement-
Play Games

資料庫創建 DROP DATABASE IF EXISTS `sql_invoicing`; CREATE DATABASE `sql_invoicing`; USE `sql_invoicing`; SET NAMES utf8 ; SET character_set_client = utf8m ...


資料庫創建

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);

　　

一、查詢

1.1查詢customers中的last_name ，first_name ，points ， discount factor列為points+10

同時限制輸出結果為3行

#語句
use sql_store;#使用哪種資料庫
select 
    last_name, 
    first_name, 
    points, 
    points +10 as 'discount factor'
from customers
limit 3 #限制輸出內容
/**
+------------+------------+--------+-----------------+
| last_name  | first_name | points | discount factor |
+------------+------------+--------+-----------------+
| MacCaffrey | Babara     |   2323 |            2333 |
| Brushfield | Ines       |    997 |            1007 |
| Boagey     | Freddi     |   3017 |            3027 |
+------------+------------+--------+-----------------+
**/

1.2查詢order_item_notes表中的note有多少種值類型

#代碼
use sql_store;#使用哪種資料庫
select * from order_item_notes;
/**
+---------+----------+------------+-------------+
| note_id | order_Id | product_id | note        |
+---------+----------+------------+-------------+
|       1 |        1 |          2 | first note  |
|       2 |        1 |          2 | second note |
|       3 |        1 |          2 | first note  |
+---------+----------+------------+-------------+

**/
#代碼
select 
    distinct note #去除重覆
from order_item_notes
/**
+-------------+
| note        |
+-------------+
| first note  |
| second note |
+-------------+
**/

1.3where子句練習

#代碼
 use sql_store;#使用哪種資料庫
 select 
     * 
 from customers 
 where points >3000#where birth_date > '1992-01-01'#where state = 'va' /**
 +-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+
 | customer_id | first_name | last_name | birth_date | phone        | address        | city      | state | points |
 +-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+
 |           5 | Clemmie    | Betchley  | 1973-11-07 | NULL         | 5 Spohn Circle | Arlington | TX    |   3675 |
 |           6 | Elka       | Twiddell  | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago   | IL    |   3073 |
 +-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+
 **/
 #代碼 邏輯鏈接 and兩個條件同時成立，or兩個條件一個成立即可
 select * 
 from customers
 where points >= 1000 and points <= 3000
 /**
 +-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
 | customer_id | first_name | last_name  | birth_date | phone        | address              | city             | state | points |
 +-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
 |           1 | Babara     | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace       | Waltham          | MA    |   2273 |
 |           3 | Freddi     | Boagey     | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO    |   2967 |
 |           7 | Ilene      | Dowson     | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing  | Nashville        | TN    |   1672 |
 |           9 | Romola     | Rumgay     | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail      | Visalia          | CA    |   1486 |
 +-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
 **/
 select
     *
 from orders
 where order_date >= '2018-01-02' and status >1 or status = 2
 /**
 +----------+-------------+------------+--------+-----------------------------------------------+--------------+------------+
 | order_id | customer_id | order_date | status | comments                                      | shipped_date | shipper_id |
 +----------+-------------+------------+--------+-----------------------------------------------+--------------+------------+
 |        2 |           7 | 2018-08-02 |      2 | NULL                                          | 2018-08-03   |          4 |
 |        5 |           5 | 2017-08-25 |      2 |                                               | 2017-08-26   |          3 |
 |        7 |           2 | 2018-09-22 |      2 | NULL                                          | 2018-09-23   |          4 |
 |        9 |          10 | 2017-07-05 |      2 | Nulla mollis molestie lorem. Quisque ut erat. | 2017-07-06   |          1 |
 |       10 |           6 | 2018-04-22 |      2 | NULL                                          | 2018-04-23   |          2 |
 +----------+-------------+------------+--------+-----------------------------------------------+--------------+------------+
 **/
 #代碼 in where state in ('va', 'GA', 'FL')== where state = 'VA' or state = 'GA' or state = 'FL'
 select * 
 from customers
 #where state = 'VA' or state = 'GA' or state = 'FL'
 where state in ('va', 'GA', 'FL') /**
 +-------------+------------+------------+------------+--------------+------------------------+----------+-------+--------+
 | customer_id | first_name | last_name  | birth_date | phone        | address                | city     | state | points |
 +-------------+------------+------------+------------+--------------+------------------------+----------+-------+--------+
 |           2 | Ines       | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton  | VA    |    947 |
 |           4 | Ambur      | Roseburgh  | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace    | Orlando  | FL    |    457 |
 |           8 | Thacher    | Naseby     | 1993-07-17 | 941-527-3977 | 538 Mosinee Center     | Sarasota | FL    |    205 |
 |          10 | Levy       | Mynett     | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue         | Atlanta  | GA    |    796 |
 +-------------+------------+------------+------------+--------------+------------------------+----------+-------+--------+
 **/

1.4匹配

use sql_store;#使用哪種資料庫
#查詢customers中last_name的第二個字母為y的所有信息
select * 
from customers
where last_name like '_y%'
/**
+-------------+------------+-----------+------------+--------------+----------------+---------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone        | address        | city    | state | points |
+-------------+------------+-----------+------------+--------------+----------------+---------+-------+--------+
|          10 | Levy       | Mynett    | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA    |    796 |
+-------------+------------+-----------+------------+--------------+----------------+---------+-------+--------+
**/
#查詢customers中phone 帶有9的所有信息，並限制輸出3行信息
select *
from customers
#where address like '%trail%' or
    #address like '%avenue%'
where phone like '%9%'
limit 3
/**
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| customer_id | first_name | last_name  | birth_date | phone        | address                | city             | state | points |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
|           1 | Babara     | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace         | Waltham          | MA    |   2273 |
|           2 | Ines       | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton          | VA    |    947 |
|           3 | Freddi     | Boagey     | 1985-02-07 | 719-724-7869 | 251 Springs Junction   | Colorado Springs | CO    |   2967 |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
**/
#regexp用法
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'#ge/ie/me 取一個元素
#where last_name regexp '[a-h]e'#ae/……/he 範圍

#order by 分組 數據顯示限製為3條
select *
from customers
#order by first_name desc#按照first_name分組並倒敘
order by first_name #按照first_name分組
limit 3
/**
+-------------+------------+------------+------------+--------------+---------------------+-----------+-------+--------+
| customer_id | first_name | last_name  | birth_date | phone        | address             | city      | state | points |
+-------------+------------+------------+------------+--------------+---------------------+-----------+-------+--------+
|           4 | Ambur      | Roseburgh  | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando   | FL    |    457 |
|           1 | Babara     | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace      | Waltham   | MA    |   2273 |
|           5 | Clemmie    | Betchley   | 1973-11-07 | NULL         | 5 Spohn Circle      | Arlington | TX    |   3675 |
+-------------+------------+------------+------------+--------------+---------------------+-----------+-------+--------+
**/
select *, quantity * unit_price as total_price
from order_items
where order_id = 2
order by quantity * unit_price desc
limit 3
/**
+----------+------------+----------+------------+-------------+
| order_id | product_id | quantity | unit_price | total_price |
+----------+------------+----------+------------+-------------+
|        2 |          1 |        2 |       9.10 |       18.20 |
|        2 |          4 |        4 |       1.66 |        6.64 |
|        2 |          6 |        2 |       2.94 |        5.88 |
+----------+------------+----------+------------+-------------+
**/

1.5連接

#代碼 聯合orders 和customers 查詢order_id, o.customer_id, last_name, first_name
select order_id, o.customer_id, last_name, first_name
from orders o
join customers 
    on o.customer_id = customers.customer_id
/**
+----------+-------------+------------+------------+
| order_id | customer_id | last_name  | first_name |
+----------+-------------+------------+------------+
|        4 |           2 | Brushfield | Ines       |
|        7 |           2 | Brushfield | Ines       |
|        5 |           5 | Betchley   | Clemmie    |
|        8 |           5 | Betchley   | Clemmie    |
|        1 |           6 | Twiddell   | Elka       |
|       10 |           6 | Twiddell   | Elka       |
|        2 |           7 | Dowson     | Ilene      |
|        3 |           8 | Naseby     | Thacher    |
|        6 |          10 | Mynett     | Levy       |
|        9 |          10 | Mynett     | Levy       |
+----------+-------------+------------+------------+
**/

#聯合orders /customers  和order_statuses 查詢
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 os.order_status_id = o.status
/**
+----------+------------+------------+------------+-----------+
| order_id | order_date | first_name | last_name  | status    |
+----------+------------+------------+------------+-----------+
|        1 | 2019-01-30 | Elka       | Twiddell   | Processed |
|        3 | 2017-12-01 | Thacher    | Naseby     | Processed |
|        4 | 2017-01-22 | Ines       | Brushfield | Processed |
|        6 | 2018-11-18 | Levy       | Mynett     | Processed |
|        8 | 2018-06-08 | Clemmie    | Betchley   | Processed |
|        2 | 2018-08-02 | Ilene      | Dowson     | Shipped   |
|        5 | 2017-08-25 | Clemmie    | Betchley   | Shipped   |
|        7 | 2018-09-22 | Ines       | Brushfield | Shipped   |
|        9 | 2017-07-05 | Levy       | Mynett     | Shipped   |
|       10 | 2018-04-22 | Elka       | Twiddell   | Shipped   |
+----------+------------+------------+------------+-----------+
**/
#聯合payments /clients 和payment_methods 查詢p.date, p.invoice_id, #p.amount, c.name, pm.name
use sql_invoicing;
select p.date, p.invoice_id, p.amount, c.name, pm.name
from payments p
join clients c
    on p.client_id = c.client_id
join payment_methods pm
    on p.payment_method = pm.payment_method_id
/**
+------------+------------+--------+-------------+-------------+
| date       | invoice_id | amount | name        | name        |
+------------+------------+--------+-------------+-------------+
| 2019-02-12 |          2 |   8.18 | Topiclounge | Credit Card |
| 2019-01-03 |          6 |  74.55 | Vinte       | Credit Card |
| 2019-01-11 |         11 |   0.03 | Yadel       | Credit Card |
| 2019-01-26 |         13 |  87.44 | Topiclounge | Credit Card |
| 2019-01-15 |         15 |  80.31 | Yadel       | Credit Card |
| 2019-01-15 |         17 |  68.10 | Yadel       | Credit Card |
| 2019-01-08 |         18 |  32.77 | Topiclounge | Credit Card |
| 2019-01-08 |         18 |  10.00 | Topiclounge | Cash        |
+------------+------------+--------+-------------+-------------+
**/
#右連接customers 和orders 查詢customer_id，first_name，order_id
use sql_store;
select c.customer_id, c.first_name, o.order_id
from customers c
right join orders o#left join orders o
    on c.customer_id = o.customer_id
order by c.customer_id
/**
+-------------+------------+----------+
| customer_id | first_name | order_id |
+-------------+------------+----------+
|           2 | Ines       |        4 |
|           2 | Ines       |        7 |
|           5 | Clemmie    |        5 |
|           5 | Clemmie    |        8 |
|           6 | Elka       |        1 |
|           6 | Elka       |       10 |
|           7 | Ilene      |        2 |
|           8 | Thacher    |        3 |
|          10 | Levy       |        6 |
|          10 | Levy       |        9 |
+-------------+------------+----------+
**/
#左連接orders ，shippers ，customers ，order_statuses
use sql_store;
select o.order_date, o.order_id,c.first_name as customer, s.name, os.name as status
from orders o
left join shippers s
    on o.shipper_id = s.shipper_id
left join customers c
    on c.customer_id = o.customer_id
join order_statuses os
    on o.status = os.order_status_id
/**
+------------+----------+----------+------------------------+-----------+
| order_date | order_id | customer | name                   | status    |
+------------+----------+----------+------------------------+-----------+
| 2019-01-30 |        1 | Elka     | NULL                   | Processed |
| 2017-12-01 |        3 | Thacher  | NULL                   | Processed |
| 2017-01-22 |        4 | Ines     | NULL                   | Processed |
| 2018-11-18 |        6 | Levy     | NULL                   | Processed |
| 2018-06-08 |        8 | Clemmie  | NULL                   | Processed |
| 2018-08-02 |        2 | Ilene    | Mraz, Renner and Nolan | Shipped   |
| 2017-08-25 |        5 | Clemmie  | Satterfield LLC        | Shipped   |
| 2018-09-22 |        7 | Ines     | Mraz, Renner and Nolan | Shipped   |
| 2017-07-05 |        9 | Levy     | Hettinger LLC          | Shipped   |
| 2018-04-22 |       10 | Elka     | Schinner-Predovic      | Shipped   |
+------------+----------+----------+------------------------+-----------+
**/
#using連接orders ，customers ，shippers 查詢order_id, first_name, shipper
#註意連接的兩張表必須有相同的欄位
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)
/**
+----------+------------+------------------------+
| order_id | first_name | shipper                |
+----------+------------+------------------------+
|        1 | Elka       | NULL                   |
|        2 | Ilene      | Mraz, Renner and Nolan |
|        3 | Thacher    | NULL                   |
|        4 | Ines       | NULL                   |
|        5 | Clemmie    | Satterfield LLC        |
|        6 | Levy       | NULL                   |
|        7 | Ines       | Mraz, Renner and Nolan |
|        8 | Clemmie    | NULL                   |
|        9 | Levy       | Hettinger LLC          |
|       10 | Elka       | Schinner-Predovic      |
+----------+------------+------------------------+
**/
#from shippers sh cross join products p == from shippers sh, products p
use sql_store;
select sh.name as shipper, p.name as product    
from shippers sh 
cross join products p #from shippers sh, products p
order by sh.name desc
limit 3
/**
+-----------------------------+---------------------------+
| shipper                     | product                   |
+-----------------------------+---------------------------+
| Waters, Mayert and Prohaska | Foam Dinner Plate         |
| Waters, Mayert and Prohaska | Lettuce - Romaine, Heart  |
| Waters, Mayert and Prohaska | Pork - Bacon,back Peameal |
+-----------------------------+---------------------------+
**/
#union連接兩個查詢
use sql_store;
select order_id, order_date, 'Axtive' 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'
limit 5
/**
+----------+------------+----------+
| order_id | order_date | status   |
+----------+------------+----------+
|        1 | 2019-01-30 | Axtive   |
|        2 | 2018-08-02 | Archived |
|        3 | 2017-12-01 | Archived |
|        4 | 2017-01-22 | Archived |
|        5 | 2017-08-25 | Archived |
+----------+------------+----------+
**/

二、插入

#customers 中插入一行數據
use sql_store;
insert into customers (customer_id, first_name,last_name,birth_date,phone,address,city,state,points)
values (default, 'mark', 'reaper', '1991-01-01', '15690679736', 'address', 'city', 'CA', 200)

#shippers 中插入兩行數據
use sql_store;
insert into shippers (name)
values ('Shippers1'), ('Shippers2')

#products 中插入兩行數據
use sql_store;
insert into products (name,quantity_in_stock, unit_price)
values ('Product1', 10, 1.95), ('Product2', 1, 8.95)

#orders 中插入一行數據
use sql_store;
insert into orders (customer_id, order_date, status)
values (1, '2019-01-02', 1)

#order_items中插入一行數據
insert into order_items
values (LAST_INSERT_ID(),11,1,2.98),(LAST_INSERT_ID(),2,1,2.88)
    
#將orders中order_date <'2019-01-01'的數據插入到orders_archived 中
insert into orders_archived 
select * from orders
where order_date <'2019-01-01'

三、修改

#invoices中invoice_id 為 1的payment_total改為100，payment_date 改為2019-03-02
use sql_invoicing;
update invoices
set payment_total = 100, payment_date = '2019-03-02'
where invoice_id = 1

四、刪除

#刪除orders_archived中customer_id 為 7的數據
use sql_store;
delete from orders_archived
where customer_id = 7

文件來源參考https://www.bilibili.com/video/av75855831?from=search&seid=175084983081237169

本文章僅可用於學習交流，如果用於其他用途責任自行承擔


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Linux 用戶、用戶組管理
    Linux系統是一個多用戶多任務的分時操作系統，每個用戶都有用戶名（唯一）、口令，用戶名唯一標識該用戶賬號。 用戶管理主要涉及到用戶添加、修改和刪除。 切換用戶 su 用戶名 su即switch user，示例：su root 普通用戶切換到其它用戶、管理員，需要輸入目標用戶密碼；管理員切換到普通用 ...
  • fish 設置環境變數；fish shell 相關使用說明記錄；
    最近使用 fish進行工作，發現環境變數忘記如何設置； fish 環境變數保存在兩個地方； ~ 目錄下，.config/fish 目錄下； /etc/fish/ 目錄下 如果配置所有用戶都能用的環境變數，可以在 /etc/fish/config.fish 文件中進行配置； 如果單獨使用，可以在~/. ...
  • 手把手教你如何解決伺服器挖礦病毒
    今天一早打開伺服器發現卡的不行，於是使用top命令查看了一番，果然不出所料，伺服器被挖礦了，下麵帶來完整的解決辦法！ 一、分析產生原因 我是用的docker部署的環境，docker ps 查看一下，發現只開了三個，很有原因是redis被攻擊了 順便用jps命令查看一下有無運行的java進程，發現有一 ...
  • Linux 文件|目錄 屬性
    文件屬性 ls -l 列出的文件|目錄信息如下 第一個字元指定類型，-是文件，d是目錄（dir）。 後面9個字元是該文件|目錄的用戶許可權：r讀，w寫，x執行。 執行是指：如果該文件是一個可執行文件，需要有x許可權才能運行這個可執行文件。 每3個一組，前三個是屬主（文件|目錄所有者）許可權，中間三個是屬組 ...
  • Mysql：初識MySQL
    轉載自：https://www.cnblogs.com/hellokuangshen/archive/2019/01/09/10246029.html Mysql：初識MySQL 只會寫代碼的是碼農；學好資料庫，基本能混口飯吃；在此基礎上再學好操作系統和電腦網路，就能當一個不錯的程式員。如果能再把 ...
  • MySQL面試筆試題集-BAT
    01、MySQL技術特點? Mysql資料庫軟體是一個客戶端或伺服器系統，支持各種客戶端程式和庫的多線程SQL伺服器、不同的後端、廣泛的應用程式編程介面和管理工具 02、MySQL預設埠號? 3306 03、MySQL的優勢? MySQL是開源軟體，隨時可用，無需付費。提供管理、檢查、優化資料庫操 ...
  • hadoop
    恢復內容開始 1、Hadoop是什麼？ Hadoop是一個Apache基金會所開發的分散式系統基礎框架（Hive、Hbase、spark都是基於hadoop架構進行數據存儲）。 主要解決：海量數據存儲和海量數據分析計算問題。 廣義上來說，Hadoop通常是指一個更廣泛的概念-Hadoop生態圈。 2 ...
  • 查詢資料庫創建時間
    可以執行存儲過程sp_helpdb查詢到資料庫相關信息： EXECUTE sp_helpdb 'Test' GO Source Code 或者查詢view視圖sys.databases： SELECT create_date FROM sys.databases WHERE name = 'Test ...
一周排行
    -Advertisement-
    Play Games
  • .NET 6 Entity Framework Core Db First（1） 創建模型
    .Net8.0 Blazor Hybird 桌面端 (WPF/Winform) 實測可以完整運行在 win7sp1/win10/win11. 如果用其他工具打包,還可以運行在mac/linux下, 傳送門BlazorHybrid 發佈為無依賴包方式 安裝 WebView2Runtime 1.57 M ...
  • 資料庫升級PostgreSql+Garnet
    目錄前言PostgreSql安裝測試額外Nuget安裝Person.cs模擬運行Navicate連postgresql解決方案Garnet為什麼要選擇Garnet而不是RedisRedis不再開源Windows版的Redis是由微軟維護的Windows Redis版本老舊，後續可能不再更新Garne ...
  • 如何基於surging架設流媒體視頻推流（視頻講解）
    C#TMS系統代碼-聯表報表學習 領導被裁了之後很快就有人上任了，幾乎是無縫銜接，很難讓我不想到這早就決定好了。我的職責沒有任何變化。感受下來這個系統封裝程度很高，我只要會調用方法就行。這個系統交付之後不會有太多問題，更多應該是做小需求，有大的開發任務應該也是第二期的事，嗯？怎麼感覺我變成運維了？而 ...
  • EAV模型（實體-屬性-值）的設計和低代碼的處理方案（2）--數據的查詢處理
    我在隨筆《EAV模型（實體-屬性-值）的設計和低代碼的處理方案（1）》中介紹了一些基本的EAV模型設計知識和基於Winform場景下低代碼（或者說無代碼）的一些實現思路，在本篇隨筆中，我們來分析一下這種針對通用業務，且只需定義就能構建業務模塊存儲和界面的解決方案，其中的數據查詢處理的操作。 ...
  • C# 程式集、模塊和類型概念及關係
    對某個遠程伺服器啟用和設置NTP服務(Windows系統) 打開註冊表 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\TimeProviders\NtpServer 將 Enabled 的值設置為 1，這將啟用NTP伺服器功 ...
  • Django信號與擴展：深入理解與實踐
    title: Django信號與擴展：深入理解與實踐 date: 2024/5/15 22:40:52 updated: 2024/5/15 22:40:52 categories: 後端開發 tags: Django 信號 松耦合 觀察者 擴展 安全 性能 第一部分：Django信號基礎 Djan ...
  • Django3.2安裝源碼xadmin2步驟以及遇到的問題
    使用xadmin2遇到的問題&解決 環境配置： 使用的模塊版本： 關聯的包 Django 3.2.15 mysqlclient 2.2.4 xadmin 2.0.1 django-crispy-forms >= 1.6.0 django-import-export >= 0.5.1 django-r ...
  • 原創->CommonsCollections1-DefaultMap鏈
    今天我打算整點兒不一樣的內容，通過之前學習的TransformerMap和LazyMap鏈，想搞點不一樣的，所以我關註了另外一條鏈DefaultedMap鏈，主要調用鏈為： 調用鏈詳細描述： ObjectInputStream.readObject() DefaultedMap.readObject ...
  • 05-應用級開發者 AI 時代破局點
    後端應用級開發者該如何擁抱 AI GC？就是在這樣的一個大的浪潮下，我們的傳統的應用級開發者。我們該如何選擇職業或者是如何去快速轉型，跟上這樣的一個行業的一個浪潮? 0 AI金字塔模型 越往上它的整個難度就是職業機會也好，或者說是整個的這個運作也好,它的難度會越大,然後越往下機會就會越多,所以這是一 ...
  • Java面試題：讓依賴註入變得簡單，面對@Autowired和@Resource，該如何選擇？
    @Autowired是Spring框架提供的註解，@Resource是Java EE 5規範提供的註解。 @Autowired預設按照類型自動裝配，而@Resource預設按照名稱自動裝配。 @Autowired支持@Qualifier註解來指定裝配哪一個具有相同類型的bean，而@Resourc... ...
所有分類