資料庫創建
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
本文章僅可用于學習交流，如果用于其他用途責任自行承擔
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/80313.html
標籤：MySQL
上一篇：mysql資料庫批量執行sql檔案對資料庫進行操作【windows版本】
下一篇：MySQL面試筆試題集-BAT