ç®å½
ä¸ï¼åæ¥è¯¢ä¸è¡¨è¿æ¥
1ï¼åæ¥è¯¢ï¼åµå¥sqlï¼
2ï¼å©ç¨åæ¥è¯¢è¿è¡è¿æ»¤
3ï¼ä½ä¸ºè®¡ç®å段使ç¨åæ¥è¯¢
äºï¼è¡¨å ³ç³»
1ï¼å ³ç³»è¡¨
2ï¼ä¸å¯¹ä¸å ³ç³»ä¸å¤é®
2.1 å¤é®
3ï¼ä¸å¯¹å¤
4ï¼å¤å¯¹å¤
ä¸ï¼è¡¨èç»
1ï¼whereä¸joinçç¨æ³
1.1 使ç¨whereè¿è¡è¡¨èç»
1.2 使ç¨joinè¿è¡è¡¨èç»
1.3 èç»å¤ä¸ªè¡¨
1.4 使ç¨è¡¨å«åAS
2ï¼èç»æ¥è¯¢ä¸çèªèç»
3ï¼å¤é¨èç»-left joinä¸right join
åï¼UNIONèåæ¥è¯¢
äºï¼SQLè¿é¶ç»ä¹
è¡¥å ï¼è¯¦è§£sql_mode
å ï¼SQLè¿é¶é¢è¯é¢
ä¸ï¼MySQLäºå¡
1ï¼äºå¡çè¯æ³
2ï¼äºå¡çACIDç¹æ§
2.1 ååæ§ï¼Atomicityï¼
2.2 ä¸è´æ§ï¼Consistencyï¼
2.3 é离æ§ï¼Isolationï¼
2.4 æä¹ æ§ï¼Durationï¼
3ï¼äºå¡ç并åé®é¢
4ï¼äºå¡é离级å«
4.1 æ¥çå½åä¼è¯ä¸äºå¡çé离级å«
4.2 读æªæ交ï¼READ_UNCOMMITTEDï¼
4.3 读已æ交ï¼READ_COMMITTEDï¼
4.4 å¯éå¤è¯»ï¼REPEATABLE_READï¼
4.5 顺åºè¯»ï¼SERIALIZABLEï¼
5ï¼ä¸åçé离级å«çéçæ åµ(äºè§£)
6ï¼éå¼æ交(äºè§£)
å «ï¼MySQLè¿é¶æ©å±
1ï¼åå¨è¿ç¨
1.1 ä»ä¹æ¯åå¨è¿ç¨?
1.2 为ä»ä¹è¦ä½¿ç¨åå¨è¿ç¨?
1.3 å®ææ»ç»
1.4 åå¨è¿ç¨æä½
2ï¼è§¦åå¨
2.1 触åå¨çå®ä¹
2.2 触åå¨è¯æ³
2.3 触åå¨Demo
2.4 ç»ä¹
3ï¼è§å¾
3.1 ä»ä¹æ¯è§å¾ï¼
3.2 è§å¾çä½ç¨
3.3 è§å¾çåºç¡ç¨æ³
ä¹ï¼MySQLç´¢å¼
1ï¼MySQLç´¢å¼ä¸SQLä¼åç´¢å¼çæ¦è¿°ä¸åç±»
1.1 ä»ä¹æ¯ç´¢å¼
1.2 ç´¢å¼åç±»
2ï¼MySQLç´¢å¼åçç´¢å¼ä¸B+Tree
2.1 Bæ ç®ä»
2.2 B+æ ç®ä»
2.3 Bæ ä¸B+æ 对æ¯
2.4 èç°ç´¢å¼åéèç°ç´¢å¼
2.5 MyISAMåInnoDBçåºå«
2.6 æ»ç»
åï¼MySQLæ ¢æ¥è¯¢ä¸SQLä¼å
1ï¼ä»ä¹æ¯æ ¢æ¥è¯¢
2ï¼æ ¢æ¥è¯¢é ç½®
3ï¼æµè¯
4ï¼ç´¢å¼ä½¿ç¨
4.1 éå½å»ºç«ç´¢å¼
4.2 åç使ç¨ç´¢å¼
4.3 å¤åç´¢å¼ç使ç¨
5ï¼æ»ç»
ä¸ï¼åæ¥è¯¢ä¸è¡¨è¿æ¥
é¦å åå¤æµè¯ç¨ç表ç»æåæ°æ®ï¼ï¼å°ä»£ç å¤å¶å°txtæ件ï¼ä¿®æ¹åç¼å为sql并ä¿åï¼å¨navicat ä¸æ°å»ºæ°æ®åºï¼å³å»å°è¦å建表çæ°æ®åºï¼éæ©è¿è¡sqlæ件ï¼å³å»ã表 / æ°å»ºçæ°æ®åºãå·æ°å³å¯çå°æ°å»ºç表ï¼
/*
Navicat Premium Data Transfer
Source Server : Mysql
Source Server Type : MySQL
Source Server Version : 50728
Source Host : localhost:3306
Source Schema : chuange
Target Server Type : MySQL
Target Server Version : 50728
File Encoding : 65001
Date: 04/06/2020 10:52:20
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for customers
-- ----------------------------
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
`cust_id` int(11) NOT NULL AUTO_INCREMENT,
`cust_name` char(50) NOT NULL,
`cust_address` char(50) DEFAULT NULL,
`cust_city` char(50) DEFAULT NULL,
`cust_state` char(5) DEFAULT NULL,
`cust_zip` char(10) DEFAULT NULL,
`cust_country` char(50) DEFAULT NULL,
`cust_contact` char(50) DEFAULT NULL,
`cust_email` char(255) DEFAULT NULL,
PRIMARY KEY (`cust_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- ----------------------------
-- Records of customers
-- ----------------------------
BEGIN;
INSERT INTO `customers` VALUES (10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', '[email protected]');
INSERT INTO `customers` VALUES (10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse', NULL);
INSERT INTO `customers` VALUES (10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', '[email protected]');
INSERT INTO `customers` VALUES (10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', '[email protected]');
INSERT INTO `customers` VALUES (10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd', NULL);
COMMIT;
-- ----------------------------
-- Table structure for orderitems
-- ----------------------------
DROP TABLE IF EXISTS `orderitems`;
CREATE TABLE `orderitems` (
`order_num` int(11) NOT NULL,
`order_item` int(11) NOT NULL,
`prod_id` char(10) NOT NULL,
`quantity` int(11) NOT NULL,
`item_price` decimal(8,2) NOT NULL,
PRIMARY KEY (`order_num`,`order_item`) USING BTREE,
KEY `fk_orderitems_products` (`prod_id`) USING BTREE,
CONSTRAINT `fk_orderitems_orders` FOREIGN KEY (`order_num`) REFERENCES `orders` (`order_num`),
CONSTRAINT `fk_orderitems_products` FOREIGN KEY (`prod_id`) REFERENCES `products` (`prod_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- ----------------------------
-- Records of orderitems
-- ----------------------------
BEGIN;
INSERT INTO `orderitems` VALUES (20005, 1, 'ANV01', 10, 5.99);
INSERT INTO `orderitems` VALUES (20005, 2, 'ANV02', 3, 9.99);
INSERT INTO `orderitems` VALUES (20005, 3, 'TNT2', 5, 10.00);
INSERT INTO `orderitems` VALUES (20005, 4, 'FB', 1, 10.00);
INSERT INTO `orderitems` VALUES (20006, 1, 'JP2000', 1, 55.00);
INSERT INTO `orderitems` VALUES (20007, 1, 'TNT2', 100, 10.00);
INSERT INTO `orderitems` VALUES (20008, 1, 'FC', 50, 2.50);
INSERT INTO `orderitems` VALUES (20009, 1, 'FB', 1, 10.00);
INSERT INTO `orderitems` VALUES (20009, 2, 'OL1', 1, 8.99);
INSERT INTO `orderitems` VALUES (20009, 3, 'SLING', 1, 4.49);
INSERT INTO `orderitems` VALUES (20009, 4, 'ANV03', 1, 14.99);
COMMIT;
-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`order_num` int(11) NOT NULL AUTO_INCREMENT,
`order_date` datetime DEFAULT NULL,
`cust_id` int(11) NOT NULL,
PRIMARY KEY (`order_num`) USING BTREE,
KEY `fk_orders_customers` (`cust_id`) USING BTREE,
CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20010 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- ----------------------------
-- Records of orders
-- ----------------------------
BEGIN;
INSERT INTO `orders` VALUES (20005, '2005-09-01 00:00:00', 10001);
INSERT INTO `orders` VALUES (20006, '2005-09-12 00:00:00', 10003);
INSERT INTO `orders` VALUES (20007, '2005-09-30 00:00:00', 10004);
INSERT INTO `orders` VALUES (20008, '2005-10-03 00:00:00', 10005);
INSERT INTO `orders` VALUES (20009, '2005-10-08 00:00:00', 10001);
COMMIT;
-- ----------------------------
-- Table structure for productnotes
-- ----------------------------
DROP TABLE IF EXISTS `productnotes`;
CREATE TABLE `productnotes` (
`note_id` int(11) NOT NULL AUTO_INCREMENT,
`prod_id` char(10) NOT NULL,
`note_date` datetime DEFAULT NULL,
`note_text` text,
PRIMARY KEY (`note_id`) USING BTREE,
FULLTEXT KEY `note_text` (`note_text`)
) ENGINE=MyISAM AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- ----------------------------
-- Records of productnotes
-- ----------------------------
BEGIN;
INSERT INTO `productnotes` VALUES (101, 'TNT2', '2005-08-17 00:00:00', 'Customer complaint:\r\nSticks not individually wrapped, too easy to mistakenly detonate all at once.\r\nRecommend individual wrapping.');
INSERT INTO `productnotes` VALUES (102, 'OL1', '2005-08-18 00:00:00', 'Can shipped full, refills not available.\r\nNeed to order new can if refill needed.');
INSERT INTO `productnotes` VALUES (103, 'SAFE', '2005-08-18 00:00:00', 'Safe is combination locked, combination not provided with safe.\r\nThis is rarely a problem as safes are typically blown up or dropped by customers.');
INSERT INTO `productnotes` VALUES (104, 'FC', '2005-08-19 00:00:00', 'Quantity varies, sold by the sack load.\r\nAll guaranteed to be bright and orange, and suitable for use as rabbit bait.');
INSERT INTO `productnotes` VALUES (105, 'TNT2', '2005-08-20 00:00:00', 'Included fuses are short and have been known to detonate too quickly for some customers.\r\nLonger fuses are available (item FU1) and should be recommended.');
INSERT INTO `productnotes` VALUES (106, 'TNT2', '2005-08-22 00:00:00', 'Matches not included, recommend purchase of matches or detonator (item DTNTR).');
INSERT INTO `productnotes` VALUES (107, 'SAFE', '2005-08-23 00:00:00', 'Please note that no returns will be accepted if safe opened using explosives.');
INSERT INTO `productnotes` VALUES (108, 'ANV01', '2005-08-25 00:00:00', 'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.');
INSERT INTO `productnotes` VALUES (109, 'ANV03', '2005-09-01 00:00:00', 'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.');
INSERT INTO `productnotes` VALUES (110, 'FC', '2005-09-01 00:00:00', 'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.');
INSERT INTO `productnotes` VALUES (111, 'SLING', '2005-09-02 00:00:00', 'Shipped unassembled, requires common tools (including oversized hammer).');
INSERT INTO `productnotes` VALUES (112, 'SAFE', '2005-09-02 00:00:00', 'Customer complaint:\r\nCircular hole in safe floor can apparently be easily cut with handsaw.');
INSERT INTO `productnotes` VALUES (113, 'ANV01', '2005-09-05 00:00:00', 'Customer complaint:\r\nNot heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.');
INSERT INTO `productnotes` VALUES (114, 'SAFE', '2005-09-07 00:00:00', 'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.\r\nComment forwarded to vendor.');
COMMIT;
-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
`prod_id` char(10) NOT NULL,
`vend_id` int(11) NOT NULL,
`prod_name` char(255) NOT NULL,
`prod_price` decimal(8,2) NOT NULL,
`prod_desc` text,
PRIMARY KEY (`prod_id`) USING BTREE,
KEY `fk_products_vendors` (`vend_id`) USING BTREE,
CONSTRAINT `fk_products_vendors` FOREIGN KEY (`vend_id`) REFERENCES `vendors` (`vend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- ----------------------------
-- Records of products
-- ----------------------------
BEGIN;
INSERT INTO `products` VALUES ('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO `products` VALUES ('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO `products` VALUES ('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO `products` VALUES ('DTNTR', 1003, 'Detonator', 13.00, 'Detonator (plunger powered), fuses not included');
INSERT INTO `products` VALUES ('FB', 1003, 'Bird seed', 10.00, 'Large bag (suitable for road runners)');
INSERT INTO `products` VALUES ('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO `products` VALUES ('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO `products` VALUES ('JP1000', 1005, 'JetPack 1000', 35.00, 'JetPack 1000, intended for single use');
INSERT INTO `products` VALUES ('JP2000', 1005, 'JetPack 2000', 55.00, 'JetPack 2000, multi-use');
INSERT INTO `products` VALUES ('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO `products` VALUES ('SAFE', 1003, 'Safe', 50.00, 'Safe with combination lock');
INSERT INTO `products` VALUES ('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO `products` VALUES ('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO `products` VALUES ('TNT2', 1003, 'TNT (5 sticks)', 10.00, 'TNT, red, pack of 10 sticks');
COMMIT;
-- ----------------------------
-- Table structure for vendors
-- ----------------------------
DROP TABLE IF EXISTS `vendors`;
CREATE TABLE `vendors` (
`vend_id` int(11) NOT NULL AUTO_INCREMENT,
`vend_name` char(50) NOT NULL,
`vend_address` char(50) DEFAULT NULL,
`vend_city` char(50) DEFAULT NULL,
`vend_state` char(5) DEFAULT NULL,
`vend_zip` char(10) DEFAULT NULL,
`vend_country` char(50) DEFAULT NULL,
PRIMARY KEY (`vend_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1007 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- ----------------------------
-- Records of vendors
-- ----------------------------
BEGIN;
INSERT INTO `vendors` VALUES (1001, 'Anvils R Us', '123 Main Street', 'Southfield', 'MI', '48075', 'USA');
INSERT INTO `vendors` VALUES (1002, 'LT Supplies', '500 Park Street', 'Anytown', 'OH', '44333', 'USA');
INSERT INTO `vendors` VALUES (1003, 'ACME', '555 High Street', 'Los Angeles', 'CA', '90046', 'USA');
INSERT INTO `vendors` VALUES (1004, 'Furball Inc.', '1000 5th Avenue', 'New York', 'NY', '11111', 'USA');
INSERT INTO `vendors` VALUES (1005, 'Jet Set', '42 Galaxy Road', 'London', NULL, 'N16 6PS', 'England');
INSERT INTO `vendors` VALUES (1006, 'Jouets Et Ours', '1 Rue Amusement', 'Paris', NULL, '45678', 'France');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
1ï¼åæ¥è¯¢ï¼åµå¥sqlï¼
SELECTè¯å¥æ¯SQLçæ¥è¯¢ãè¿ä»ä¸ºâ½æ们æçå°çææSELECTè¯å¥é½æ¯ç®åæ¥è¯¢ï¼å³ä»å个æ°æ®åºè¡¨ä¸æ£ç´¢æ°æ®çåæ¡è¯å¥ã
SQLè¿å 许å建⼦æ¥è¯¢ï¼subqueryï¼ï¼å³åµå¥å¨å ¶ä»æ¥è¯¢ä¸çæ¥è¯¢ã
- å°±æ¯å¨ä¸ä¸ªsqlå½ä¸ï¼å®çwhereæ¡ä»¶æ¥æºäºå¦å¤ä¸ä¸ªsqlï¼
- æè åè¿æ¥ç解ï¼ä¸ä¸ªsqlè¯å¥çç»æï¼ä½ä¸ºå¤å±sqlè¯å¥çæ¡ä»¶ã
2ï¼å©ç¨åæ¥è¯¢è¿è¡è¿æ»¤
æ¥ä¸æ¥å°éè¿å®ä¾æ¥ç解åæ¥è¯¢ï¼
å·²ç¥æ¡ä»¶Â
- 订ååå¨å¨ä¸¤ä¸ªè¡¨ä¸ã对äºå å«è®¢åå·ã客æ·IDã订åâ½æçæ¯ä¸ªè®¢åï¼orders表åå¨â¼â¾ãÂ
- å订åçç©ååå¨å¨ç¸å ³çorderitems表ä¸ã
- orders表ä¸åå¨å®¢æ·ä¿¡æ¯ãå®åªåå¨å®¢æ·çIDãå®é ç客æ·ä¿¡æ¯åå¨å¨customers表ä¸ã
ç°å¨ï¼åå¦éè¦ååºè®¢è´ç©åTNT2çææ客æ·ï¼åºè¯¥ææ ·æ£ç´¢ï¼
ç´è§æµç¨å¦ä¸ï¼
1ï¼å¨orderitems表ä¸æ¾å°prod_id为TNT2ç订åç¼å·order_num
è¿è¡ï¼ç»æå¦ä¸ï¼ÂSELECT order_num, prod_id FROM orderitems WHERE prod_id = 'TNT2'
2ï¼å¨è®¢å表ä¸æ¾å°order_num对åºç顾客idcust_id
ÂSELECT cust_id, order_num FROM orders WHERE order_num in(20005, 20007)
Â
3ï¼å¨é¡¾å®¢è¡¨ä¸æ¾å°é¡¾å®¢id对åºç顾客信æ¯
SELECT cust_name, cust_contact, cust_id FROM customers WHERE cust_id in(10001, 10004)
å¯ä»¥æå ¶ä¸çWHERE⼦å¥è½¬æ¢ä¸ºâ¼¦æ¥è¯¢â½½ä¸æ¯ç¡¬ç¼ç è¿äºSQLè¿åçæ°æ®
SELECT cust_id, cust_name
FROM customers
WHERE cust_id IN (
SELECT cust_id
FROM orders
WHERE order_num IN (
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2' )
);
- 为äºæ§â¾ä¸è¿°SELECTè¯å¥ï¼MySQLå®é ä¸å¿ é¡»æ§â¾3æ¡SELECTè¯å¥ã
- æ⾥边ç⼦æ¥è¯¢è¿å订åå·å表ï¼æ¤å表⽤äºå ¶å¤â¾¯ç⼦æ¥è¯¢çWHERE⼦å¥ã
- å¤â¾¯ç⼦æ¥è¯¢è¿å客æ·IDå表ï¼æ¤å®¢æ·IDå表⽤äºæå¤å±æ¥è¯¢çWHERE⼦å¥ã
- æå¤å±æ¥è¯¢ç¡®å®è¿åæéçæ°æ®ã
3ï¼ä½ä¸ºè®¡ç®å段使ç¨åæ¥è¯¢
åå¦éè¦æ¾ç¤ºcustomers表ä¸æ¯ä¸ªå®¢æ·ç订åæ»æ°ã订åä¸ç¸åºç客æ·IDåå¨å¨orders表ä¸ã
1ï¼ä»customers表ä¸è·åç¨æ·å表
select cust_id,cust_name from customers
2ï¼å è·åä¸ä¸ªç¨æ·å¨orders表ä¸ç订åæ°
select count(*) as orders_num from orders where cust_id = 10001;
3ï¼èèå¦ä½è·åæ¯ä¸ªå®¢æ·ç订åæ°ï¼å¯¹æ¯ä¸ªå®¢æ·è¿è¡countå½æ°çç»è®¡è®¡ç®ï¼å³ä½ä¸ºè®¡ç®å段使ç¨åæ¥è¯¢ï¼
select cust_id,cust_name,
(select count(*) from orders where orders.cust_id = customers.cust_id) as orders_num
from customers;
注æï¼è¿é计ç®å段使ç¨åæ¥è¯¢æ¶ï¼éç¨å®å ¨éå®è¡¨å
Â
äºï¼è¡¨å ³ç³»
1ï¼å ³ç³»è¡¨
åå¦æâ¼ä¸ªå å«äº§å⽬å½çæ°æ®åºè¡¨ï¼å ¶ä¸æ¯ç§ç±»å«çç©åå â¼â¾ã
对äºæ¯ç§ç©åè¦åå¨çä¿¡æ¯å æ¬äº§åæè¿°åä»·æ ¼ï¼ä»¥å⽣产该产åçä¾åºåä¿¡æ¯ã
Â
ç°å¨ï¼åå¦æç±åâ¼ä¾åºå⽣产çå¤ç§ç©åï¼é£ä¹å¨ä½å¤åå¨ä¾åºåä¿¡æ¯ï¼å¦ï¼ä¾åºååãå°åãè系⽠æ³çï¼å¢ï¼
             Â
å¯ä»¥çåºï¼è¥ä¾åºåç¸åï¼ä¾åºåä¹åçå段å°ä¼å ¨é¨ä¸è´
ç¸åæ°æ®åºç°å¤æ¬¡å³ä¸æ¯â¼ä»¶å¥½äºï¼æ¤å ç´ æ¯å ³ç³»æ°æ®åºè®¾è®¡çåºç¡ã
å ³ç³»è¡¨ç设计就æ¯è¦ä¿è¯æä¿¡æ¯å解æå¤ä¸ªè¡¨ï¼â¼ç±»æ°æ®â¼ä¸ªè¡¨ã
å表éè¿æäºå¸¸â½¤çå¼ï¼å³å ³ç³»è®¾è®¡ä¸çå ³ç³»ï¼relationalï¼ï¼äºç¸å ³èã
å¨è¿ä¸ªä¾â¼¦ä¸ï¼å¯å»ºâ½´ä¸¤ä¸ªè¡¨ï¼â¼ä¸ªåå¨ä¾åºåä¿¡æ¯ï¼å¦â¼ä¸ªåå¨äº§åä¿¡æ¯ã
vendors表ç主é®â¼å«ä½productsçå¤é®ï¼å®å°vendors表ä¸productsè¡¨å ³èï¼å©â½¤ä¾åºåIDè½ ä»vendors表ä¸æ¾åºç¸åºä¾åºåç详ç»ä¿¡æ¯ã è¿æ ·åç好å¤å¦ä¸ï¼
- ä¾åºåä¿¡æ¯ä¸éå¤ï¼ä»â½½ä¸æµªè´¹æ¶é´å空é´ï¼
- å¦æä¾åºåä¿¡æ¯åå¨ï¼å¯ä»¥åªæ´æ°vendors表ä¸çå个记å½ï¼ç¸å ³è¡¨ä¸çæ°æ®ä¸â½¤æ¹å¨ï¼
- ç±äºæ°æ®â½éå¤ï¼æ¾ç¶æ°æ®æ¯â¼è´çï¼è¿ä½¿å¾å¤çæ°æ®æ´ç®å
å ³ç³»æ°æ®å¯ä»¥ææå°åå¨å⽠便å°å¤çãå æ¤ï¼å ³ç³»æ°æ®åºçå¯ä¼¸ç¼©æ§è¿â½â¾®å ³ç³»æ°æ®åºè¦å¥½ã
2ï¼ä¸å¯¹ä¸å ³ç³»ä¸å¤é®
2.1 å¤é®
è¡¨å ³ç³»ï¼è¡¨ä¸è¡¨ä¹é´çå ³ç³»
å¤é®ï¼
- å¨ä¸ä¸ªè¡¨ä¸ï¼å®ä¹ä¸ä¸ªå段ï¼è¿ä¸ªå段ä¸åå¨çæ°æ®æ¯å¦å¤ä¸å¼ 表ä¸ç主é®
- å°±æ¯å¨ä¸ä¸ªè¡¨ä¸çå段ï¼ä»£è¡¨çè¿ä¸ªæ°æ®å±äºè°
äºè§£ï¼
å¤é®å®ç°çæ¹å¼ï¼æ两ç§ï¼ç©çå¤é®ãé»è¾å¤é®
ç©çå¤é®ï¼
é»è¾å¤é®ï¼æ¨è
- å°±æ¯å¨å建表æ¶ï¼å°±æå®è¿ä¸ªè¡¨ä¸çå段æ¯ä¸ä¸ªå¤é®ï¼å¹¶ä¸å¼ºå ³èæ个表ä¸çæ个å段
- éè¦å¨å®ä¹å段æ¶ï¼ä½¿ç¨sqlè¯å¥æ¥å®ç°
- 缺ç¹ï¼å é¤å½å表æ¶éè¦å å é¤å ¶ä¾èµç表ï¼ç±äºå¼ºå ³ç³»çåå¨ï¼å¹¶åæ¶æçä¼åå°å½±åï¼
- å°±æ¯å¨è¡¨ä¸å建ä¸ä¸ªæ®éçå段ï¼æ²¡æå¼ºå ³èå ³ç³»ï¼éè¦éè¿ç¨åºé»è¾æ¥å®ç°
ä¸å¯¹ä¸ï¼
å°±æ¯å¨ä¸ä¸ªè¡¨ä¸çæ°æ®ï¼å¯¹åºçå¦å¤ä¸å¼ 表ä¸çä¸ä¸ªæ°æ®ï¼åªè½æä¸ä¸ª
举ä¾ï¼
å工表ï¼ç±ä¸é¢çä¸ä¸ªè¡¨ï¼æåæ两个表
- idï¼å§åãæ§å«ãå¹´é¾ãç±è´¯ãèç³»æ¹å¼ãå¦åãå·¥é¾ããããã
ä¸é¢çè¡¨å ³ç³»å°±æ¯ä¸å¯¹ä¸çè¡¨å ³ç³»ï¼éè¿è¯¦æ 表ä¸çyidè¿ä¸ªå段æ¥æ è®°å工表ä¸ç主é®ã
ä¸ä¸ªåå·¥æçä¸ä¸ªå¯¹åºç详æ ä¿¡æ¯ï¼åå¨å¨è¯¦æ 表ä¸ï¼
å¨è¯¦æ 表ä¸çæ°æ®ï¼ä¹åªå±äºæä¸ä¸ªåå·¥ã
3ï¼ä¸å¯¹å¤
å¨ä¸ä¸ªè¡¨ä¸çä¸æ¡æ°æ®å¯¹åºçå¦å¤ä¸ä¸ªè¡¨ä¸çå¤æ¡æ°æ®
å¨ä¸ä¸ªè¡¨ä¸çå¤æ¡æ°æ®ï¼å¯¹åºçå¦å¤ä¸å¼ 表ä¸ä¸ä¸ªæ°æ®
4ï¼å¤å¯¹å¤
举ä¾ï¼ä¾å¦ä¸æ¬ä¹¦ï¼æå¤ä¸ªæ ç¾ï¼åæ¶æ¯ä¸ä¸ªæ ç¾ä¸å对åºå¤æ¬ä¹¦
Â
ä¸ï¼è¡¨èç»
1ï¼whereä¸joinçç¨æ³
1.1 使ç¨whereè¿è¡è¡¨èç»
å¦ææ°æ®åå¨å¨å¤ä¸ªè¡¨ä¸ï¼ææ ·â½¤åæ¡SELECTè¯å¥æ£ç´¢åºæ°æ®ï¼
çæ¡æ¯ä½¿â½¤èç»ãç®åå°è¯´ï¼èç»æ¯â¼ç§æºå¶ï¼â½¤æ¥å¨â¼æ¡SELECTè¯å¥ä¸å ³è表ï¼å æ¤ç§°ä¹ä¸ºèç»ã
使⽤ç¹æ®çè¯æ³ï¼å¯ä»¥èç»å¤ä¸ªè¡¨è¿åâ¼ç»è¾åºï¼èç»å¨è¿â¾æ¶å ³è表ä¸æ£ç¡®çâ¾ã
ä¾å¦ï¼æ们éè¦æ¥è¯¢åºææçååå对åºçä¾åºåä¿¡æ¯æä¹åï¼
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id = products.vend_id
order by vend_name,prod_name;
è¿ä¸¤ä¸ªè¡¨â½¤WHERE⼦å¥æ£ç¡®èç»ï¼WHERE⼦å¥æ示MySQLå¹é vendors表ä¸çvend_idåproducts表ä¸çvend_idã
å¯ä»¥çå°è¦å¹é ç两个å以 vendors.vend_id å products. vend_idæå®ãè¿â¾¥éè¦è¿ç§å®å ¨éå®ååï¼å 为å¦æåªç»åºvend_idï¼åMySQLä¸ç¥éæçæ¯åªâ¼ä¸ªï¼å®ä»¬æ两个ï¼æ¯ä¸ªè¡¨ä¸â¼ä¸ªï¼ã
å¨å¼â½¤çåå¯è½åºç°â¼ä¹æ§æ¶ï¼å¿ 须使⽤å®å ¨éå®ååï¼â½¤â¼ä¸ªç¹åéç表ååååï¼
å¨èç»ä¸¤ä¸ªè¡¨æ¶ï¼ä½ å®é ä¸åçæ¯å°ç¬¬â¼ä¸ªè¡¨ä¸çæ¯â¼â¾ä¸ç¬¬â¼ä¸ªè¡¨ä¸çæ¯â¼â¾é 对ã
WHERE⼦å¥ä½ä¸ºè¿æ»¤æ¡ä»¶ï¼å®åªå å«é£äºå¹é ç»å®æ¡ä»¶ï¼è¿â¾¥æ¯èç»æ¡ä»¶ï¼çâ¾ã
ä½ è½æ³è±¡ä¸â¾¯çsqlå¦æ没æwhereæ¡ä»¶æ¶ä¼ææ ·åï¼
select vend_name,prod_name,prod_price from vendors,products
å¦æ没æwhereæ¡ä»¶ï¼ç¬¬â¼ä¸ªè¡¨ä¸çæ¯ä¸ªâ¾å°ä¸ç¬¬â¼ä¸ªè¡¨ä¸çæ¯ä¸ªâ¾é 对ï¼â½½ä¸ç®¡å®ä»¬é»è¾ä¸æ¯å¦å¯ä»¥é å¨â¼èµ·
ç±æ²¡æèç»æ¡ä»¶çè¡¨å ³ç³»è¿åçç»æ为ç¬å¡â¼ç§¯ãæ£ç´¢åºçâ¾çæ°â½¬å°æ¯ç¬¬â¼ä¸ªè¡¨ä¸çâ¾æ°ä¹ä»¥ç¬¬â¼ä¸ªè¡¨ä¸çâ¾æ°ã
ä¸è¦å¿äºWHERE⼦å¥
åºè¯¥ä¿è¯ææèç»é½æWHERE⼦å¥ï¼å¦åMySQLå°è¿åâ½æ³è¦çæ°æ®å¤å¾å¤çæ°æ®ã
åçï¼åºè¯¥ä¿è¯WHERE⼦å¥çæ£ç¡®æ§ãä¸æ£ç¡®çè¿æ»¤æ¡ä»¶å°å¯¼è´MySQLè¿åä¸æ£ç¡®çæ°æ®
1.2 使ç¨joinè¿è¡è¡¨èç»
å ¶å®ï¼å¯¹äºè¿ç§èç»å¯ä»¥ä½¿â½¤ç¨å¾®ä¸åçè¯æ³æ¥æç¡®æå®èç»çç±»åã
select vend_name,prod_name,prod_price
from vendors
inner join products
on vendors.vend_id = products.vend_id;
两个表ä¹é´çå ³ç³»æ¯FROM⼦å¥çç»æé¨åï¼ä»¥INNER JOINæå®ã
å¨ä½¿â½¤è¿ç§è¯æ³æ¶ï¼èç»æ¡ä»¶â½¤ç¹å®çON⼦å¥â½½ä¸æ¯WHERE⼦å¥ç»åºã
ä¼ éç»ONçå®é æ¡ä»¶ä¸ä¼ éç»WHEREçç¸åã
SQLè§è⾸éINNER JOINè¯æ³ãï¼è¿éç´æ¥ä½¿ç¨JOINç»æç¸åï¼
1.3 èç»å¤ä¸ªè¡¨
SQL对ä¸æ¡SELECTè¯å¥ä¸å¯ä»¥èç»ç表çæ°ç®æ²¡æéå¶ã
å建èç»çåºæ¬è§åä¹ç¸åãé¦å ååºææ表ï¼ç¶åå®ä¹è¡¨ä¹é´çå ³ç³»ã
æ¡ä¾ï¼æ¥è¯¢åºè®¢åå·ä¸º20005ç订åä¸è´ä¹°çååå对åºç产åä¾åºåä¿¡æ¯
select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
where products.vend_id = vendors.vend_id
and orderitems.prod_id = products.prod_id
and order_num = 20005;
æ¹å为joinè¯æ³
select prod_name,vend_name,prod_price,quantity
from orderitems
inner join products on orderitems.prod_id = products.prod_id
inner join vendors on products.vend_id = vendors.vend_id
where order_num = 20005;
MySQLå¨è¿â¾æ¶å ³èæå®çæ¯ä¸ªè¡¨ä»¥å¤çèç»ã è¿ç§å¤çå¯è½æ¯â¾®å¸¸èè´¹èµæºçï¼å æ¤åºè¯¥ä»ç»ï¼ä¸è¦èç»ä¸å¿ è¦ç表ãèç»ç表è¶å¤ï¼æ§è½ä¸éè¶å害ã
1.4 使ç¨è¡¨å«åAS
å«åé¤äºâ½¤äºååå计ç®å段å¤ï¼SQLè¿å 许ç»è¡¨åèµ·å«åã
è¿æ ·åæ两个主è¦çç±ï¼
- 缩çSQLè¯å¥ï¼
- å 许å¨åæ¡SELECTè¯å¥ä¸å¤æ¬¡ä½¿â½¤ç¸åç表
åºè¯¥æ³¨æï¼è¡¨å«ååªå¨æ¥è¯¢æ§â¾ä¸ä½¿â½¤ãä¸åå«åä¸â¼æ ·ï¼è¡¨å«åä¸è¿åå°å®¢æ·æº
Â
2ï¼èç»æ¥è¯¢ä¸çèªèç»
èªèç»:å½åè¿ä¸ªè¡¨ä¸èªå·±è¿ä¸ªè¡¨ åèç»ï¼joinï¼
举ä¾
åå¦ä½ åç°æç©åï¼å ¶ID为DTNTRï¼åå¨é®é¢ï¼å æ¤æ³ç¥é⽣产该ç©åçä¾åºå⽣产çå ¶ä»ç©åæ¯å¦ä¹åå¨è¿äºé®é¢ã
æ¤æ¥è¯¢è¦æ±â¾¸å æ¾å°â½£äº§ID为DTNTRçç©åçä¾åºåï¼ç¶åæ¾åºè¿ä¸ªä¾åºå⽣产çå ¶ä»ç©åã
-- 使⽤⼦æ¥è¯¢(åµå¥æ¥è¯¢)
select prod_id,prod_name
from products
where vend_id = (select vend_id from products where prod_id = 'DTNTR');
-- ä½¿ç¨ èªèç»æ¹å¼æ¥è¯¢
select p1.prod_id,p2.prod_name
from products as p1
join products as p2
on p1.vend_id = p2.vend_id
where p2.prod_id = 'DTNTR';
-- æ¹æwhereè¯å¥
select p1.prod_id,p2.prod_name
from products as p1, products as p2
where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';
-- æ¤æ¥è¯¢ä¸éè¦ç两个表å®é ä¸æ¯ç¸åç表ï¼å æ¤products表å¨FROM⼦å¥ä¸åºç°äºä¸¤æ¬¡ãè½ç¶è¿æ¯å®å ¨åæ³çï¼ä½å¯¹productsçå¼â½¤å ·æâ¼ä¹æ§ï¼å 为MySQLä¸ç¥éä½ å¼â½¤çæ¯products表ä¸çåªä¸ªå®ä¾ã
-- 为解å³æ¤é®é¢ï¼ä½¿â½¤äºè¡¨å«åãproductsç第â¼æ¬¡åºç°ä¸ºå«åp1ï¼ç¬¬â¼æ¬¡åºç°ä¸ºå«åp2ãç°å¨å¯ä»¥å°è¿äºå«å⽤ä½è¡¨åã
--ä¾å¦ï¼SELECTè¯å¥ä½¿â½¤p1åç¼æç¡®å°ç»åºæéåçå ¨åãå¦æä¸è¿æ ·ï¼MySQLå°è¿åé误ï¼å 为åå«åå¨ä¸¤ä¸ªå为prod_idãprod_nameçåãMySQLä¸ç¥éæ³è¦çæ¯åªâ¼ä¸ªåï¼å³ä½¿å®ä»¬äºå®ä¸æ¯åâ¼ä¸ªåï¼ãWHEREï¼éè¿å¹é p1ä¸çvend_idåp2ä¸çvend_idï¼â¾¸å èç»ä¸¤ä¸ªè¡¨ï¼ç¶åæ第â¼ä¸ªè¡¨ä¸çprod_idè¿æ»¤æ°æ®ï¼è¿åæéçæ°æ®
⽤â¾èç»â½½ä¸â½¤â¼¦æ¥è¯¢ãâ¾èç»é常ä½ä¸ºå¤é¨è¯å¥â½¤æ¥æ¿ä»£ä»ç¸å表ä¸æ£ç´¢æ°æ®æ¶ä½¿â½¤ç⼦æ¥è¯¢è¯å¥ã
è½ç¶æç»çç»ææ¯ç¸åçï¼ä½ææ¶åå¤çèç»è¿â½å¤ç⼦æ¥è¯¢å¿«å¾å¤ã
æ·±å ¥äºè§£ joinï¼ä¾æ¬¡æ§è¡ä¸é¢ä¸æ¡SQLè¯å¥ï¼è§å¯ç»æÂ
1ï¼ä¸æ·»å whereè¯å¥
select
p1.prod_id,p1.prod_name,p1.vend_id,
p2.prod_id,p2.prod_name,p2.vend_id
from products as p1,products as p2;
ä¸¤å¼ è¡¨æ°æ®çä¹ç§¯14*14
2ï¼åªéå®ä¸¤å¼ 表çidç¸ç
select
p1.prod_id,p1.prod_name,p1.vend_id,
p2.prod_id,p2.prod_name,p2.vend_id
from products as p1,products as p2
where p1.vend_id = p2.vend_id;
3ï¼éå®p2表prod_id为DTNTRÂ
select
p1.prod_id,p1.prod_name,p1.vend_id,
p2.prod_id,p2.prod_name,p2.vend_id
from products as p1,products as p2
where p1.vend_id = p2.vend_id
and p2.prod_id = 'DTNTR';
4ï¼ä¸å±ç¤ºp2表ç¸å ³å段
select
p1.prod_id,p1.prod_name,p1.vend_id
from products as p1,products as p2
where p1.vend_id = p2.vend_id
and p2.prod_id = 'DTNTR';
åæ¥è¯¢ï¼åµå¥æ¥è¯¢ï¼ æ¯ç®åå¯æç¡®ç¥éç sqlä¸è¿è¡æçæä½çä¸ç§æ¹å¼ï¼å°½å¯è½ä¸ä½¿ç¨åµå¥è¯å¥ã
3ï¼å¤é¨èç»-left joinä¸right join
许å¤èç»å°â¼ä¸ªè¡¨ä¸çâ¾ä¸å¦â¼ä¸ªè¡¨ä¸çâ¾ç¸å ³èãä½ææ¶åä¼éè¦å å«æ²¡æå ³èâ¾çé£äºâ¾ã
ä¾å¦ï¼å¯è½éè¦ä½¿â½¤èç»æ¥å®æ以ä¸â¼¯ä½ï¼
- 对æ¯ä¸ªå®¢æ·ä¸äºå¤å°è®¢åè¿â¾è®¡æ°ï¼å æ¬é£äºâ¾ä»å°æªä¸è®¢åç客æ·ï¼ï¼ä¹åèç»çç»æåªä¿çäºè¡¨ä¹é´æå ³èçè®°å½ï¼å¦æ订å表ä¸æ²¡æ客æ·cç订åè®°å½ï¼é£ä¹å®¢æ·cçä¿¡æ¯å°±ä¸ä¼åºç°å¨èç»çç»æä¸ï¼ä¹å°±æ²¡æ³ç»è®¡ææ客æ·ç订åä¿¡æ¯ï¼
- ååºææ产å以å订è´æ°éï¼å æ¬æ²¡æâ¼è®¢è´ç产åï¼
- 计ç®å¹³åéå®è§æ¨¡ï¼å æ¬é£äºâ¾ä»å°æªä¸è®¢åç客æ·ï¼
å¨ä¸è¿°ä¾â¼¦ä¸ï¼èç»å å«äºé£äºå¨ç¸å ³è¡¨ä¸æ²¡æå ³èâ¾çâ¾ãè¿ç§ç±»åçèç»ç§°ä¸ºå¤é¨èç»ã
è¿ä»¥ä¹å使ç¨çæ°æ®åºä¸ºä¾ï¼æ¥è¯¢ææ客æ·ç订åæ°æ®ï¼
-- å
é¨è¿æ¥
select customers.cust_id,orders.order_num
from customers,orders
where orders.cust_id = customers.cust_id;
select customers.cust_id,orders.order_num
from customers
join orders
on orders.cust_id = customers.cust_id;
select cust_id from customers;
å¯ä»¥çåºå®¢æ·10002çè®°å½å¹¶æªåºç°å¨å é¨èç»çç»æä¸ã
é£ä»ä¹æ¯å¤é¨èç»å¢ï¼
left join ï¼ æ¯ä»¥ left join 左侧表为åºåï¼å»å ³èå³ä¾§ç表è¿è¡èç»ï¼å¦æææªå ³èçæ°æ®ï¼é£ä¹ç»æ为null
right join ï¼æ¯ä»¥ right join å³ä¾§è¡¨ä¸ºåºåï¼å»å ³è左侧ç表è¿è¡èç»ï¼å¦æææªå ³èçæ°æ®ï¼é£ä¹ç»æ为null
-- 以ç¨æ·è¡¨ä¸ºåºåï¼å»å
³èæ¥è¯¢ 订å表æ°æ®
select customers.cust_id,orders.order_num
from customers
left join orders
on customers.cust_id = orders.cust_id;
select customers.cust_id,orders.order_num
from orders
right join customers
on customers.cust_id = orders.cust_id;
-- 以ä¸ä¸¤æ¡è¯å¥çä»·
èéå½æ°ä¹å¯ä»¥â½ 便å°ä¸å ¶ä»èç»â¼èµ·ä½¿â½¤ã
å¦æè¦æ£ç´¢ææ客æ·åæ¯ä¸ªå®¢æ·æä¸ç订åæ°ï¼ä¸â¾¯ä½¿â½¤äºCOUNT()å½æ°ç代ç å¯å®ææ¤â¼¯ä½
å å«é£äºæ²¡æä»»ä½ä¸è®¢åç客æ·ã
-- 对æ¯ä¸ªå®¢æ·ä¸äºå¤å°è®¢åè¿è¡è®¡æ°ï¼å
æ¬é£äºè³ä»å°æªä¸è®¢åç客æ·ï¼
select customers.cust_id,count(orders.order_num) as nums
from customers
left join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;
注æï¼
- ä¿è¯ä½¿â½¤æ£ç¡®çèç»æ¡ä»¶ï¼å¦åå°è¿åä¸æ£ç¡®çæ°æ®ã
- åºè¯¥æ»æ¯æä¾èç»æ¡ä»¶ï¼å¦åä¼å¾åºç¬å¡â¼ç§¯ã
- å¨â¼ä¸ªèç»ä¸å¯ä»¥å å«å¤ä¸ªè¡¨ï¼çâ¾å¯¹äºæ¯ä¸ªèç»å¯ä»¥é⽤ä¸åçèç»ç±»åãè½ç¶è¿æ ·åæ¯åæ³çï¼â¼è¬ä¹å¾æ⽤ï¼ä½åºè¯¥å¨â¼èµ·æµè¯å®ä»¬ååå«æµè¯æ¯ä¸ªèç»ãè¿å°ä½¿æ éæé¤æ´ä¸ºç®å
Â
åï¼UNIONèåæ¥è¯¢
MySQLä¹å 许æ§â¾å¤ä¸ªæ¥è¯¢ï¼å¤æ¡SELECTè¯å¥ï¼ï¼å¹¶å°ç»æä½ä¸ºå个æ¥è¯¢ç»æéè¿åã
è¿äºç»åæ¥è¯¢é常称为并ï¼unionï¼æå¤åæ¥è¯¢ï¼compound queryï¼ã
UNIONè§å
- UNIONå¿ é¡»ç±ä¸¤æ¡æ两æ¡ä»¥ä¸çSELECTè¯å¥ç»æï¼è¯å¥ä¹é´â½¤å ³é®åUNIONåéï¼å æ¤ï¼å¦æç»å4æ¡SELECTè¯å¥ï¼å°è¦ä½¿â½¤3个UNIONå ³é®åï¼ã
- UNIONä¸çæ¯ä¸ªæ¥è¯¢å¿ é¡»å å«ç¸åçåã表达å¼æèéå½æ°ï¼ä¸è¿å个åä¸éè¦ä»¥ç¸åç次åºååºï¼
- åæ°æ®ç±»åå¿ é¡»å ¼å®¹ï¼ç±»åä¸å¿ å®å ¨ç¸åï¼ä½å¿ é¡»æ¯DBMSå¯ä»¥éå«å°è½¬æ¢çç±»åï¼ä¾å¦ï¼ä¸åçæ°å¼ç±»åæä¸åçâ½æç±»åï¼ã
åå¦éè¦ä»·æ ¼â¼©äºçäº5çææç©åçâ¼ä¸ªå表ï¼â½½ä¸è¿æ³å æ¬ä¾åºå1001å1002⽣产çææç©åã
-- å
æ¥è¯¢ç¬¬â¼ä¸ªç»æ
select vend_id,prod_id,prod_price from products where prod_price <= 5;
-- åæ¥è¯¢ç¬¬â¼ä¸ªç»æ
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);
--使⽤unionå°ä¸¤ä¸ªsqlâ¼å¹¶æ§â¾
select vend_id,prod_id,prod_price from products where prod_price <= 5
union
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);
-- 使⽤unionå°ä¸¤ä¸ªsqlâ¼å¹¶æ§â¾
-- è¿æ¡è¯å¥ç±å⾯ç两æ¡SELECTè¯å¥ç»æï¼è¯å¥ä¸â½¤UNIONå
³é®ååéã
-- UNIONæ示MySQLæ§â¾ä¸¤æ¡SELECTè¯å¥ï¼å¹¶æè¾åºç»åæå个æ¥è¯¢ç»æé
-- 以ä¸æ¯åæ ·ç»æ,使⽤whereçå¤æ¡ä»¶æ¥å®ç°
select vend_id,prod_id,prod_price
from products
where prod_price <= 5
or vend_id in (1001,1002);
å¨è¿ä¸ªç®åçä¾â¼¦ä¸ï¼ä½¿â½¤UNIONå¯è½â½ä½¿â½¤WHERE⼦å¥æ´ä¸ºå¤æã
ä½å¯¹äºæ´å¤æçè¿æ»¤æ¡ä»¶ï¼æè ä»å¤ä¸ªè¡¨ï¼â½½ä¸æ¯å个表ï¼ä¸æ£ç´¢æ°æ®çæ å½¢ï¼ä½¿â½¤UNIONå¯è½ä¼ä½¿å¤çæ´ç®åã
ç°å¨æèâ¼ä¸ªé®é¢,ä¸â¾¯çè¯å¥åå«è¿åäºâ¼æ¡æ°æ®?
第â¼æ¡sqlè¿å4â¾,第â¼æ¡sqlè¿å5â¾,é£ä¹unionè¿åäºâ¼â¾?
UNIONä»æ¥è¯¢ç»æéä¸â¾å¨å»é¤äºéå¤çâ¾ï¼æ¢å¥è¯è¯´ï¼å®çâ¾ä¸ºä¸åæ¡SELECTè¯å¥ä¸ä½¿â½¤å¤ä¸ªWHERE⼦å¥æ¡ä»¶â¼æ ·ï¼ã
è¿æ¯UNIONçé»è®¤â¾ä¸ºï¼ä½æ¯å¦æéè¦ï¼å¯ä»¥æ¹åå®ãå¦ææ³è¿åææå¹é â¾ï¼å¯ä½¿â½¤UNION ALLâ½½ä¸æ¯UNION
select vend_id,prod_id,prod_price from products where prod_price <= 5 union all select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);
对ç»åæ¥è¯¢ç»ææåº
- SELECTè¯å¥çè¾åºâ½¤ORDER BY⼦å¥æåºãå¨â½¤UNIONç»åæ¥è¯¢æ¶ï¼åªè½ä½¿â½¤â¼æ¡ORDER BY⼦å¥ï¼å®å¿ é¡»åºç°å¨æåâ¼æ¡SELECTè¯å¥ä¹åã
- 对äºç»æéï¼ä¸åå¨â½¤â¼ç§â½ å¼æåºâ¼é¨åï¼â½½â¼â½¤å¦â¼ç§â½ å¼æåºå¦â¼é¨åçæ åµï¼å æ¤ä¸å 许使⽤å¤æ¡ORDER BY⼦å¥ã
select vend_id,prod_id,prod_price from products where prod_price <= 5
union
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002)
order by prod_price;
è¿æ¡UNIONå¨æåâ¼æ¡SELECTè¯å¥å使⽤äºORDER BY⼦å¥ã
è½ç¶ORDER BY⼦å¥ä¼¼ä¹åªæ¯æåâ¼æ¡SELECTè¯å¥çç»æé¨åï¼ä½å®é ä¸MySQLå°â½¤å®æ¥æåºææSELECTè¯å¥è¿åçææç»æã
Â
äºï¼SQLè¿é¶ç»ä¹
å 容è¿å¤ï¼åå¨å¦ä¸ç¯ç¬è®°éå¦ï¼ä¼ éé¨<( ̄︶ ̄)â[GO!]ï¼@&åè§è¤ç«è«&ã05-æ°æ®åº_æ°æ®åºé«çº§_SQLè¿é¶ç»ä¹ ï¼é¨åä¹ é¢ï¼ã
Â
è¡¥å ï¼è¯¦è§£sql_mode
sql_modeæ¯MySQLæ°æ®åºä¸çä¸ä¸ªç¯å¢åéï¼
å®ä¹äºmysqlåºè¯¥æ¯æçsqlè¯æ³ï¼æ°æ®æ ¡éªçï¼
å¯ä»¥éè¿ select @@sql_mode; æ¥çå½åæ°æ®åºä½¿ç¨çsql_mode
1ï¼ä»¥æ¥è¯¢ä¸ä¸ªå¦ççé课æ°ç®ä¸ºä¾ï¼æ åçSQLè¯å¥ä¸º
select stu.sid,stu.sname,count(sc.cid)
from student as stu
left join sc on sc.sid = stu.sid
group by stu.sid,stu.sname;
2ï¼ç¶èæåªæ³æç §ä¸åè¿è¡åç»æä¹åï¼ï¼åºç°1055é误ï¼
-- æå®ååç»,èä¸æ¯ç¨å
¨é¨å
select stu.sid,stu.sname,count(sc.cid)
from student as stu
left join sc on sc.sid = stu.sid
group by stu.sid;
-- ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'chuange.stu.Sname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
3ï¼æ¥çæ°æ®åºä¸çsql_mode
select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
ONLY_FULL_GROUP_BY
-- é对grouo by èåæä½,å¦æå¨selectä¸çå,没æå¨group byä¸åºç°,é£ä¹å°è®¤ä¸ºsqlä¸åæ³
 4ï¼ä¿®æ¹sql_mode
临æ¶ä¿®æ¹(æå¡å¨éå¯å失æ)
-- å»é¤ONLY_FULL_GROUP_BY
set @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
ä¿®æ¹mysqlé ç½®æ件 my.cnf(éå¯åçæ)
-- å¨my.cnfç[mysqld]çä¸é¢å»é
ç½®
[mysqld]
xxx = xxx
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
5ï¼æ»ç»å»ºè®®:
- 1. 建议å¼å¯,符åSQLæ å
- 2. å¨MySQLä¸æä¸ä¸ªå½æ° any_value(filed),å 许è¿åéåç»å段(åå ³éonly_full_group_by模å¼ç¸å)
-- ä½¿ç¨ any_value å½æ°
select stu.sid,any_value(stu.sname),any_value(stu.sage),count(sc.cid)
from student as stu
left join sc on sc.sid = stu.sid
group by stu.sid;
6ï¼sql_modeå¼çå«ä¹
Â
å ï¼SQLè¿é¶é¢è¯é¢
ä¼ éé¨<( ̄︶ ̄)â[GO!]ï¼@&åè§è¤ç«è«&ã05-æ°æ®åº_æ°æ®åºé«çº§_SQLè¿é¶é¢è¯é¢ã
Â
ä¸ï¼MySQLäºå¡
äºå¡ï¼Transactionï¼æ¯ç±â¼ç³»å对系ç»ä¸æ°æ®è¿â¾è®¿é®ä¸æ´æ°çæä½æç»æçâ¼ä¸ªç¨åºæ§â¾é»è¾åå ã
1ï¼äºå¡çè¯æ³
1. start transaction;/ begin;
2. commit; 使å¾å½åçä¿®æ¹ç¡®è®¤
3. rollback; 使å¾å½åçä¿®æ¹è¢«æ¾å¼
2ï¼äºå¡çACIDç¹æ§
2.1 ååæ§ï¼Atomicityï¼
äºå¡çå⼦æ§æ¯æäºå¡å¿ é¡»æ¯â¼ä¸ªå⼦çæä½åºååå ãäºå¡ä¸å å«çå项æä½å¨â¼æ¬¡æ§â¾è¿ç¨ä¸ï¼åªå 许åºç°ä¸¤ç§ç¶æä¹â¼ã
- å ¨é¨æ§â¾æå
- å ¨é¨æ§â¾å¤±è´¥
äºå¡å¼å§åæææä½ï¼è¦ä¹å ¨é¨åå®ï¼è¦ä¹å ¨é¨ä¸åï¼ä¸å¯è½åæ»å¨ä¸é´ç¯èã
äºå¡æ§â¾è¿ç¨ä¸åºéï¼ä¼åæ»å°äºå¡å¼å§åçç¶æï¼ææçæä½å°±å没æåâ½£â¼æ ·ã
ä¹å°±æ¯è¯´äºå¡æ¯â¼ä¸ªä¸å¯åå²çæ´ä½ï¼å°±ååå¦ä¸å¦è¿çå⼦ï¼æ¯ç©è´¨ææçåºæ¬åä½ã
2.2 ä¸è´æ§ï¼Consistencyï¼
äºå¡çâ¼è´æ§æ¯æäºå¡çæ§â¾ä¸è½ç ´åæ°æ®åºæ°æ®çå®æ´æ§åâ¼è´æ§ï¼â¼ä¸ªäºå¡å¨æ§â¾ä¹ååæ§â¾ä¹åï¼æ°æ®åºé½å¿ é¡»å¤ä»¥â¼è´æ§ç¶æã
â½å¦ï¼å¦æä»Aè´¦æ·è½¬è´¦å°Bè´¦æ·ï¼ä¸å¯è½å 为Aè´¦æ·æ£äºé±ï¼â½½Bè´¦æ·æ²¡æå é±ã
2.3 é离æ§ï¼Isolationï¼
äºå¡çé离æ§æ¯æå¨å¹¶åç¯å¢ä¸ï¼å¹¶åçäºå¡æ¯äºç¸é离çãä¹å°±æ¯è¯´ï¼ä¸åçäºå¡å¹¶åæä½ç¸åçæ°æ®æ¶ï¼æ¯ä¸ªäºå¡é½æåâ¾å®æ´çæ°æ®ç©ºé´ã
â¼ä¸ªäºå¡å é¨çæä½å使⽤çæ°æ®å¯¹å ¶å®å¹¶åäºå¡æ¯é离çï¼å¹¶åæ§â¾çå个äºå¡æ¯ä¸è½äºç¸â¼²æ°çãé离æ§å4个级å«ï¼ä¸â¾¯ä¼ä»ç»ã
2.4 æä¹ æ§ï¼Durationï¼
äºå¡çæä¹ æ§æ¯æäºå¡â¼æ¦æ交åï¼æ°æ®åºä¸çæ°æ®å¿ é¡»è¢«æ°¸ä¹ çä¿åä¸æ¥ã
å³ä½¿æå¡å¨ç³»ç»å´©æºææå¡å¨å®æºçæ éãåªè¦æ°æ®åºéæ°å¯å¨ï¼é£ä¹â¼å®è½å¤å°å ¶æ¢å¤å°äºå¡æåç»æåçç¶æã
3ï¼äºå¡ç并åé®é¢
è读ï¼è¯»åå°äºæ²¡ææ交çæ°æ®ãäºå¡A读åäºäºå¡Bæ´æ°çæ°æ®ï¼ç¶åBåæ»æä½ï¼é£ä¹A读åå°çæ°æ®æ¯èæ°æ®ã
ä¸å¯éå¤è¯»ï¼åâ¼æ¡å½ä»¤è¿åä¸åçç»æéï¼æ´æ°ï¼ãäºå¡ A å¤æ¬¡è¯»ååâ¼æ°æ®ï¼äºå¡ B å¨äºå¡Aå¤æ¬¡è¯»åçè¿ç¨ä¸ï¼å¯¹æ°æ®ä½äºæ´æ°å¹¶æ交ï¼å¯¼è´äºå¡Aå¤æ¬¡è¯»ååâ¼æ°æ®æ¶ï¼ç»æ ä¸â¼è´ã
幻读ï¼éå¤æ¥è¯¢çè¿ç¨ä¸ï¼æ°æ®å°±åâ½£äºéçååï¼insertï¼ deleteï¼ã
4ï¼äºå¡é离级å«
4ç§äºå¡é离级å«ä»ä¸å¾ä¸ï¼çº§å«è¶â¾¼ï¼å¹¶åæ§è¶å·®ï¼å®å ¨æ§å°±è¶æ¥è¶â¾¼ã â¼è¬æ°æ®é»è®¤çº§å«æ¯è¯»ä»¥æ交æå¯éå¤è¯»
4.1 æ¥çå½åä¼è¯ä¸äºå¡çé离级å«
select @@tx_isolation;
4.2 读æªæ交ï¼READ_UNCOMMITTEDï¼
读æªæ交ï¼è¯¥é离级å«å 许è读åï¼å ¶é离级å«æ¯æä½çã
æ¢å¥è¯è¯´ï¼å¦æâ¼ä¸ªäºå¡æ£å¨å¤çæâ¼æ°æ®ï¼å¹¶å¯¹å ¶è¿â¾äºæ´æ°ï¼ä½åæ¶å°æªå®æäºå¡ï¼å æ¤è¿æ²¡ææ交äºå¡ï¼â½½ä»¥æ¤åæ¶ï¼å 许å¦â¼ä¸ªäºå¡ä¹è½å¤è®¿é®è¯¥æ°æ®ã
è读示ä¾ï¼
å¨äºå¡Aåäºå¡Båæ¶æ§â¾æ¶å¯è½ä¼åºç°å¦ä¸åºæ¯ï¼
ä½é¢åºè¯¥ä¸º1500å æ对ã请çT5æ¶é´ç¹ï¼äºå¡Aæ¤æ¶æ¥è¯¢çä½é¢ä¸º0ï¼è¿ä¸ªæ°æ®å°±æ¯èæ°æ®ï¼ä»æ¯äºå¡Bé æçï¼å¾ææ¾æ¯äºå¡æ²¡æè¿â¾é离é æçã
4.3 读已æ交ï¼READ_COMMITTEDï¼
读已æ交æ¯ä¸åçäºå¡æ§â¾çæ¶ååªè½è·åå°å·²ç»æ交çæ°æ®ã è¿æ ·å°±ä¸ä¼åºç°ä¸â¾¯çè读çæ åµäºãä½æ¯å¨åâ¼ä¸ªäºå¡ä¸æ§â¾åâ¼ä¸ªè¯»å,ç»æä¸â¼è´
ä¸å¯éå¤è¯»ç¤ºä¾
å¯æ¯è§£å³äºè读é®é¢ï¼ä½æ¯è¿æ¯è§£å³ä¸äºå¯éå¤è¯»é®é¢ã
äºå¡Aå ¶å®é¤äºæ¥è¯¢ä¸¤æ¬¡ä»¥å¤ï¼å ¶å®ä»ä¹äºæ é½æ²¡åï¼ç»æé±å°±ä»1000åæ0äºï¼è¿å°±æ¯ä¸å¯éå¤è¯»çé®é¢ã
4.4 å¯éå¤è¯»ï¼REPEATABLE_READï¼
å¯éå¤è¯»å°±æ¯ä¿è¯å¨äºå¡å¤çè¿ç¨ä¸ï¼å¤æ¬¡è¯»ååâ¼ä¸ªæ°æ®æ¶ï¼è¯¥æ°æ®çå¼åäºå¡å¼å§æ¶å»æ¯â¼è´çã
äºå¡Aæ§è¡è¿ç¨ä¸æ¥è¯¢çæ°æ®åäºå¡Aå¼å§æ¶ç¸åï¼äºå¡Aæ交åæ¥è¯¢çæ°æ®ææ¯äºå¡Bä¿®æ¹åçæ°æ®ã
å æ¤è¯¥äºå¡çº§å«éå¶äºä¸å¯éå¤è¯»åè读ï¼ä½æ¯æå¯è½åºç°å¹»è¯»çæ°æ®ã
幻读
幻读就æ¯æåæ ·çäºå¡æä½ï¼å¨åå两个æ¶é´æ®µå æ§â¾å¯¹åâ¼ä¸ªæ°æ®é¡¹ç读åï¼å¯è½åºç°ä¸â¼è´çç»æã
åæ¬å¨äºå¡Aç»æä¹åï¼æ¯çä¸å°äºå¡Bä¸æ°å¢æ¯æ°æ®çï¼ä½æ¯è¿è¡èå´ä¿®æ¹åï¼æ¯å¦æä¸åå ¨é¨å ä¸ä¸ä¸ªå¼ï¼ï¼åè½å¨æ¬æ¬¡äºå¡Aå æ¥è¯¢å°æ°å¢çæ°æ®ï¼æ以被称为â诡å¼çæ´æ°äºä»¶â
4.5 顺åºè¯»ï¼SERIALIZABLEï¼
顺åºè¯»æ¯æä¸¥æ ¼çäºå¡é离级å«ãå®è¦æ±ææçäºå¡æé顺åºæ§â¾ï¼å³äºå¡åªè½â¼ä¸ªæ¥â¼ä¸ªå°å¤çï¼ä¸è½å¹¶åã
两个äºå¡å¯ä»¥åæ¶è¯»ï¼ä½æ¯ä¸ä¸ªäºå¡Aæ§è¡æ´æ°æ°æ®çè¯å¥åå°ä¼å¡ä½ï¼ç´å°å¦ä¸äºå¡Bæ交/åæ»åï¼äºå¡Aæè½ç»§ç»è¿è¡
5ï¼ä¸åçé离级å«çéçæ åµ(äºè§£)
- 1. 读æªæ交ï¼RUï¼: æâ¾çº§çéï¼æ²¡æé´ééãå®ä¸RCçåºå«æ¯è½å¤æ¥è¯¢å°æªæ交çæ°æ®ã
- 2. 读已æ交ï¼RCï¼ï¼æâ¾çº§çéï¼æ²¡æé´ééï¼è¯»ä¸å°æ²¡ææ交çæ°æ®ã
- 3. å¯éå¤è¯»ï¼RRï¼ï¼æâ¾çº§çéï¼ä¹æé´ééï¼æ¯æ¬¡è¯»åçæ°æ®é½æ¯â¼æ ·çï¼å¹¶ä¸æ²¡æ幻读çæ åµã
- 4. åºååï¼Sï¼ï¼æâ¾çº§éï¼ä¹æé´ééï¼è¯»è¡¨çæ¶åï¼å°±å·²ç»ä¸éäº
6ï¼éå¼æ交(äºè§£)
- DQL:æ¥è¯¢è¯å¥
- DML:åæä½(æ·»å ,å é¤,ä¿®æ¹)
- DDL:å®ä¹è¯å¥(建åº,建表,ä¿®æ¹è¡¨,ç´¢å¼æä½,åå¨è¿ç¨,è§å¾)
- DCL:æ§å¶è¯â¾(ç»â½¤æ·ææ,æå é¤ææ)
DDLï¼Data Define Languageï¼ï¼é½æ¯éå¼æ交ã
éå¼æ交ï¼æ§â¾è¿ç§è¯å¥ç¸å½äºæ§â¾commit; DDL
Â
å «ï¼MySQLè¿é¶æ©å±
1ï¼åå¨è¿ç¨
1.1 ä»ä¹æ¯åå¨è¿ç¨?
⽬å使⽤ç⼤å¤æ°SQLè¯å¥é½æ¯é对â¼ä¸ªæå¤ä¸ªè¡¨çåæ¡è¯å¥ã并⾮æææä½é½è¿ä¹ç®åï¼ç»å¸¸ä¼æâ¼ä¸ªå®æ´çæä½éè¦å¤æ¡è¯å¥ æè½å®æã
ä¾å¦ä»¥ä¸çæ å½¢ã
- 为äºå¤ç订åï¼éè¦æ ¸å¯¹ä»¥ä¿è¯åºåä¸æç¸åºçç©åã
- å¦æåºåæç©åï¼éè¦é¢å®ä»¥ä¾¿ä¸å°å®ä»¬ååç»å«çâ¼ï¼ 并åå°å¯â½¤çç©åæ°é以åæ æ£ç¡®çåºåéã
- åºåä¸æ²¡æçç©åéè¦è®¢è´ï¼è¿éè¦ä¸ä¾åºåè¿â¾æç§äº¤äºã
æ§â¾è¿ä¸ªå¤çéè¦é对许å¤è¡¨çå¤æ¡MySQLè¯å¥ãå¯è½éè¦æ§â¾çå ·ä½è¯å¥åå ¶æ¬¡åºä¹ä¸æ¯åºå®çã
é£ä¹ï¼ææ ·ç¼åæ¤ä»£ç ?å¯ä»¥åç¬ç¼åæ¯æ¡è¯å¥ï¼å¹¶æ ¹æ®ç»æææ¡ä»¶å°æ§â¾å¦å¤çè¯å¥ã
å¨æ¯æ¬¡éè¦è¿ä¸ªå¤çæ¶(以åæ¯ä¸ªéè¦å®çåºâ½¤ä¸)é½å¿ é¡»åè¿äºâ¼¯ä½ã
å¯ä»¥å建åå¨è¿ç¨
åå¨è¿ç¨ç®åæ¥è¯´ï¼å°±æ¯ä¸ºä»¥åç使⽤⽽ä¿å çâ¼æ¡æå¤æ¡MySQLè¯å¥çéåãå¨åè¿ç¨æ¯â¼ç»ä¸ºäºå®æç¹å®åè½çSQLè¯å¥éï¼ç»è¿ç¼è¯ä¹ååå¨å¨æ°æ®åºä¸ï¼å¨éè¦æ¶ç´æ¥è°â½¤ã
åå¨è¿ç¨å°±åèæ¬è¯â¾ä¸å½æ°å®ä¹â¼æ ·ã
1.2 为ä»ä¹è¦ä½¿ç¨åå¨è¿ç¨?
ä¼ç¹:
- å¯ä»¥æâ¼äºå¤æçsqlè¿â¾å°è£ ,ç®åå¤ææä½
- ä¿è¯äºæ°æ®çå®æ´æ§,é²â½é误
- ç®åçåå¨åªéè¦æ´æ¹åå¨è¿ç¨ç代ç å³å¯
- æâ¾¼æ§è½ãå 为使⽤åå¨è¿ç¨â½ä½¿â½¤åç¬çSQLè¯å¥è¦å¿«ã(é¢å ç¼è¯)
缺ç¹:
- åå¨è¿ç¨çç¼åâ½SQLè¯å¥å¤æ
- â¼è¬å¯è½è¿æ²¡æå建åå¨è¿ç¨çæé,åªè½è°â½¤
1.3 å®ææ»ç»
å¨çµä¿¡ãé¶â¾ä¸ã⾦è⽠⾯以åå½ä¼é½æ®é使⽤åå¨è¿ç¨æ¥çæä¸å¡é»è¾ï¼ä½å¨äºèâ½¹ä¸ç¸å¯¹è¾å°ã
- ä¸å¡é»è¾ä¸è¦å°è£ å¨æ°æ®åºâ¾¥â¾¯,åºè¯¥ç±åºâ½¤ç¨åº(JAVAãPythonãPHP)å¤çã
- 让æ°æ®åºåªåå®æ â»åå¿ é¡»åçï¼åå°æ°æ®åºèµæºåæ§è½çæ¶èã
- ç»´æ¤å°é¾ï¼â¼¤éä¸å¡é»è¾å°è£ å¨åå¨è¿ç¨ä¸ï¼é æä¸å¡é»è¾å¾é¾å¥ç¦»åºæ¥ãå¨Aå½±åBã
- â¼åä¹é¾æèï¼å 为æ¢æåå¨è¿ç¨ï¼â¼æä¸å¡çâ¼å°ã使⽤å°é¾ã
1.4 åå¨è¿ç¨æä½
å建åå¨è¿ç¨
\d // ä¿®æ¹MySQLé»è®¤çè¯å¥ç»å°¾ç¬¦ ; ï¼æ¹ä¸º // ãï¼å 为ï¼å¨åå¨è¿ç¨ä¸æå«çç¨éï¼
create procedure å建è¯å¥
BEGINåENDè¯å¥â½¤æ¥éå®åå¨è¿ç¨ä½
-- å®ä¹åå¨è¿ç¨
\d //
create procedure p1()
begin
set @i=10;
while @i<90 do
insert into users values(null,concat('user:',@i),@i,0);
set @[email protected]+1;
end while;
end;
//
æ§è¡åå¨
call p1()
æ¥çåå¨è¿ç¨
show create procedure p1\G
å é¤åå¨è¿ç¨
drop procedure p1
Â
2ï¼è§¦åå¨
MySQLè¯å¥å¨éè¦æ¶è¢«æ§â¾ï¼åå¨è¿ç¨ä¹æ¯å¦æ¤ã
ä½æ¯ï¼å¦æä½ æ³è¦ææ¡è¯å¥(ææäºè¯å¥)å¨äºä»¶åâ½£æ¶â¾å¨æ§â¾ï¼æä¹åå¢?
ä¾å¦:
- æ¯å½å¢å â¼ä¸ªé¡¾å®¢å°æ个æ°æ®åºè¡¨æ¶ï¼é½æ£æ¥å ¶çµè¯å·ç æ ¼å¼æ¯å¦æ£ç¡®;
- æ¯å½è®¢è´â¼ä¸ªäº§åæ¶ï¼é½ä»åºåæ°éä¸åå»è®¢è´çæ°é;
- â½è®ºä½æ¶å é¤â¼â¾ï¼é½å¨æ个å档表ä¸ä¿çâ¼ä¸ªå¯æ¬ã
- ææè¿äºä¾â¼¦çå ±åä¹å¤æ¯å®ä»¬é½éè¦å¨æ个表åâ½£æ´æ¹æ¶â¾å¨å¤çãè¿ç¡®åå°è¯´å°±æ¯è§¦åå¨ã
2.1 触åå¨çå®ä¹
触åå¨æ¯MySQLååºåæä½(å¢ãå ãæ¹)â½½â¾å¨æ§â¾çâ¼æ¡æâ¼ç»å®ä¹å¨BEGINåENDä¹é´çMySQLè¯å¥
æå¯ç解为ï¼æåå®ä¹å¥½â¼ä¸ªæâ¼ç»æä½,å¨æå®çSQLæä½åæåæ¥è§¦åæå®çSQLâ¾å¨æ§â¾
触åå¨å°±åæ¯JavaScriptä¸çäºä»¶â¼æ ·
举ä¾: å®ä¹â¼ä¸ªupdateè¯å¥,å¨åæ个表ä¸æ§â¾insertæ·»å è¯å¥æ¶æ¥è§¦åæ§â¾,å°±å¯ä»¥ä½¿â½¤è§¦åå¨
Â
2.2 触åå¨è¯æ³
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
说æï¼
# trigger_nameï¼è§¦åå¨å称
# trigger_time:触åæ¶é´ï¼å¯åå¼ï¼BEFOREæAFTER
# trigger_eventï¼è§¦åäºä»¶ï¼å¯åå¼ï¼INSERTãUPDATEæDELETEã
# tb1_nameï¼æå®å¨åªä¸ªè¡¨ä¸
# trigger_stmtï¼è§¦åå¤çSQLè¯å¥ã
-- æ¥çææç 触åå¨
show triggers\G;
-- å é¤è§¦åå¨
drop trigger trigger_name;
2.3 触åå¨Demo
-- å建â¼ä¸ªå é¤ç触åå¨,å¨users表ä¸å é¤æ°æ®ä¹å,å¾del_users表ä¸æ·»å â¼ä¸ªæ°æ®
tipsï¼
å¨INSERT触åå¨ä»£ç å ï¼å¯å¼â½¤â¼ä¸ªå为NEWçèæ表ï¼è®¿é®è¢« æâ¼çâ¾;Â
å¨DELETE触åå¨ä»£ç å ï¼å¯ä»¥å¼â½¤â¼ä¸ªå为OLDçèæ表ï¼è®¿é®è¢«å é¤çâ¾;
å¨UPDATE触åå¨ä»£ç ä¸
- OLDä¸çå¼å ¨é½æ¯åªè¯»çï¼ä¸è½æ´æ°ã
- å¨AFTER DELETEç触åå¨ä¸â½æ³è·åOLDèæ表
- å¯ä»¥å¼â½¤â¼ä¸ªå为OLDçèæ表访é®æ´æ°ä»¥åçå¼
- å¯ä»¥å¼â½¤â¼ä¸ªå为NEWçèæ表访é®æ° æ´æ°çå¼;
1ï¼å¤å¶ä¸ä¸ªå·²ç»åå¨ç表ç»æ
create table del_users like users;
2ï¼å建 å é¤è§¦åå¨
注æå¨å建å é¤è§¦åå¨æ¶,åªè½å¨å é¤ä¹åï¼å¿ é¡»æ¯beforeï¼æè½è·åå°old(ä¹åç)æ°æ®
\d //
create trigger deluser before delete on users for each row
begin
insert into del_users values(old.id,old.name,old.age,old.account);
end;
//
\d ;
å ¶ä¸çoldæ¯å é¤æ¶å¼ç¨çä¸ä¸ªèæ表ï¼è¯¦è§ä¸æ¹tipsÂ
3ï¼å é¤users表ä¸çæ°æ®å»å®éª
2.4 ç»ä¹
⽤触åå¨æ¥å®ç°æ°æ®çç»è®¡
-- 1.å建â¼ä¸ªè¡¨, users_count ⾥⾯æâ¼ä¸ª numçå段 åå§å¼ä¸º0æè
æ¯ä½ å½åusers表ä¸çcount
-- 2,ç»users表å建â¼ä¸ªè§¦åå¨
-- å½ç»users表ä¸æ§â¾insertæ·»å æ°æ®ä¹å,就让users_count⾥⾯num+1,
-- å½users表ä¸çæ°æ®å é¤æ¶,就让users_count⾥⾯num-1,
-- æ³è¦ç»è®¡users表ä¸çæ°æ®æ»æ°æ¶,ç´æ¥æ¥ç users_count
Â
3ï¼è§å¾
3.1 ä»ä¹æ¯è§å¾ï¼
- è§å¾æ¯èæç表ãä¸å å«æ°æ®ç表ä¸â¼æ ·ï¼è§å¾åªå å«ä½¿â½¤æ¶å¨ææ£ç´¢æ°æ®çæ¥è¯¢ã
- è§å¾ä» ä» æ¯â½¤æ¥æ¥çåå¨å¨å«å¤çæ°æ®çâ¼ç§è®¾æ½æâ½ æ³ã
- è§å¾æ¬èº«ä¸å å«æ°æ®ï¼å æ¤å®ä»¬è¿åçæ°æ®æ¯ä»å ¶ä»è¡¨ä¸æ£ç´¢åºæ¥çã
- å¨æ·»å ææ´æ¹è¿äºè¡¨ä¸çæ°æ®æ¶ï¼è§å¾å°è¿åæ¹åè¿çæ°æ®ã
- å 为è§å¾ä¸å å«æ°æ®ï¼æ以æ¯æ¬¡ä½¿â½¤è§å¾æ¶ï¼é½å¿ é¡»å¤çæ¥è¯¢æ§â¾æ¶æéçä»»â¼ä¸ªæ£ç´¢ã
- å¦æä½ â½¤å¤ä¸ªèç»åè¿æ»¤å建äºå¤æçè§å¾æè åµå¥äºè§å¾ï¼å¯è½ä¼åç°æ§è½ä¸éå¾å¾å害ã
3.2 è§å¾çä½ç¨
- 1. é⽤SQLè¯å¥ã
- 2. ç®åå¤æçSQLæä½ãå¨ç¼åæ¥è¯¢åï¼å¯ä»¥â½ 便å°é⽤å®â½½ä¸å¿ ç¥éå®çåºæ¬æ¥è¯¢ç»èã
- 3. 使⽤表çç»æé¨åâ½½ä¸æ¯æ´ä¸ªè¡¨ã
- 4. ä¿æ¤æ°æ®ãå¯ä»¥ç»â½¤æ·æäºè¡¨çç¹å®é¨åç访é®æéâ½½ä¸æ¯æ´ä¸ªè¡¨ç访é®æéã
- 5. æ´æ¹æ°æ®æ ¼å¼å表示ãè§å¾å¯è¿åä¸åºå±è¡¨ç表示åæ ¼å¼ä¸åçæ°æ®ã
- 6. 注æ:è§å¾ä¸è½ç´¢å¼ï¼ä¹ä¸è½æå ³èç触åå¨æé»è®¤å¼ã
3.3 è§å¾çåºç¡ç¨æ³
-- å建è§å¾:
create view v_users as select id,name,age from users where age >= 25 and age <= 35;
Query OK, 0 rows affected (0.00 sec)
--viewè§å¾ç帮å©ä¿¡æ¯:
mysql> ? view
ALTER VIEW
CREATE VIEW
DROP VIEW
-- æ¥çå½ååºä¸ææçè§å¾
show tables; --å¯ä»¥æ¥çå°ææç表åè§å¾
show table status where comment='view'; --åªæ¥çå½ååºä¸çææè§å¾
-- å é¤è§å¾v_t1:
mysql> drop view v_t1;
ä¸æ³å±ç¤ºæææ°æ®ï¼æ¯å¦è¿éçaccontï¼ï¼æ以éæ©å建è§å¾Â
Â
Â
ä¹ï¼MySQLç´¢å¼
1ï¼MySQLç´¢å¼ä¸SQLä¼åç´¢å¼çæ¦è¿°ä¸åç±»
1.1 ä»ä¹æ¯ç´¢å¼
ç´¢å¼ç±»ä¼¼å¾ä¹¦çç®å½ç´¢å¼ï¼å¯ä»¥æé«æ°æ®æ£ç´¢çæç,éä½æ°æ®åºçIOææ¬ã
MySQLå®æ¹å¯¹ç´¢å¼çå®ä¹ä¸º:
- ç´¢å¼(Index)æ¯å¸®å©MySQLé«æè·åæ°æ®çæ°æ®ç»æã
- æ们å¯ä»¥ç®åç解为:å¿«éæ¥æ¾æ好åºçä¸ç§æ°æ®ç»æã
1.2 ç´¢å¼åç±»
ãæçä»é«å°ä½ã
1ï¼ä¸»é®ç´¢å¼
å³ä¸»ç´¢å¼ï¼æ ¹æ®ä¸»é®å»ºç«ç´¢å¼ï¼ä¸å 许éå¤ï¼ä¸å 许空å¼;
2ï¼å¯ä¸ç´¢å¼
ç¨æ¥å»ºç«ç´¢å¼çåçå¼å¿ é¡»æ¯å¯ä¸ç,å 许空å¼
3ï¼æ®éç´¢å¼
ç¨è¡¨ä¸çæ®éåæ建çç´¢å¼ï¼æ²¡æä»»ä½éå¶
4ï¼å ¨æç´¢å¼
ç¨å¤§ææ¬å¯¹è±¡çåæ建çç´¢å¼ãï¼æ¯å¦ç¨æç« ä½ä¸ºç´¢å¼ï¼å¯ä»¥å°æç« å为è¥å¹²è¯ï¼éè¿è¯å»ºç«ç´¢å¼ï¼è¿æ ·å¯ä»¥æç´¢è¯å°±è½å¿«éæ¾å°æç« ï¼
- MYSQL5.6ä¹åççæ¬ä¸ï¼å ¨æç´¢å¼åªè½ç¨äºMyISAMåå¨å¼æ
- MYSQL5.6å以åççæ¬ï¼MyISAM åInnoDBåæ¯æå ¨æç´¢å¼
- å¨ä¹åçMySQL ä¸ï¼å ¨æç´¢å¼åªå¯¹è±ææç¨ï¼ç®å对ä¸æè¿ä¸æ¯æï¼MYSQL8ä¹åæ¯æï¼
5ï¼ç»åç´¢å¼
ç¨å¤ä¸ªåç»åæ建çç´¢å¼ï¼è¿å¤ä¸ªåä¸çå¼ä¸å 许æ空å¼
ç»åç´¢å¼çæå·¦ååï¼
Â
2ï¼MySQLç´¢å¼åçç´¢å¼ä¸B+Tree
åªæmemoryï¼å åï¼åå¨å¼æâ½æåå¸ç´¢å¼ï¼åå¸ç´¢å¼â½¤ç´¢å¼åçå¼è®¡ç®è¯¥å¼çhashCodeï¼ç¶åå¨hashCodeç¸åºçä½ç½®åæ§è¯¥å¼æå¨â¾æ°æ®çç©çä½ç½®ï¼å 为使⽤æ£åç®æ³ï¼å æ¤è®¿é®é度⾮常快ï¼ä½æ¯â¼ä¸ªå¼åªè½å¯¹åºâ¼ä¸ªhashCodeï¼â½½ä¸æ¯æ£åçåå¸â½ å¼ï¼å æ¤åå¸ç´¢å¼ä¸â½æèå´æ¥æ¾åæåºçåè½
æ£å¸¸æ åµä¸ï¼å¦æä¸æå®ç´¢å¼çç±»åï¼é£ä¹â¼è¬æ¯æB+Treeç´¢å¼ï¼æè B+Treeç´¢å¼ï¼ã
åå¨å¼æ以ä¸åçâ½ å¼ä½¿â½¤B+Treeç´¢å¼ãæ§è½ä¹åæä¸åï¼ä½æ¯InnoDBæç §åæ°æ®æ ¼å¼è¿â¾åå¨ã
2.1 Bæ ç®ä»
å½æ°æ®åå¨å åä¸ï¼çº¢é»æ æçé常é«ï¼ä½æ¯æ件系ç»åæ°æ®åºé½æ¯åå¨ç¡¬çä¸çï¼å¦ææ°æ®é大çè¯ï¼ä¸ä¸å®è½ä¸æ¬¡æ§ å è½½å°å åãæ以ä¸æ£µæ é½æ æ³ä¸æ¬¡æ§å è½½è¿å åï¼åå¦ä½è°æ¥æ¾ã
å æ¤å°±åºç°äºä¸ä¸ºç£ççåå¨è®¾å¤è设计çä¸ç§å¹³è¡¡å¤è·¯æ¥æ¾æ ï¼ä¹å°±æ¯Bæ
- ä¸çº¢é»æ ç¸æ¯,å¨ç¸åççèç¹çæ åµä¸,ä¸é¢B/B+æ çé«åº¦è¿è¿å°äºçº¢é»æ çé«åº¦
- Bæ å³å¹³è¡¡æ¥æ¾æ ï¼ä¸è¬ç解为平衡å¤è·¯æ¥æ¾æ ï¼ä¹ç§°ä¸ºB-æ ãB_ æ ã
- Bæ æ¯ä¸ç§èªå¹³è¡¡æ ç¶æ°æ®ç»æï¼ä¸è¬è¾å¤ç¨å¨åå¨ç³»ç»ä¸ï¼æ¯å¦æ°æ®åºææ件系ç»ã
Â
éè¿ä¸â¾¯çä¸ä¸ªæ¡ä¾å°±åç°äºâ¼ä¸ªé®é¢
- 1.æ¥æ¾7å10 æ⽤æ¥æ°ä¸â¼æ ·ï¼å°±æå³çæ¶é´ä¸åï¼æçå°±ä¸åè¡¡ï¼æçå¯è½å¿«ï¼æçå¯è½æ ¢
- 2.å¨è¿â¾èå´æ¥æ¾æ¶ï¼éè¦åå¤çè¿åä¸â¼èç¹ï¼å¨è¿â¼ä¸â¼èç¹ï¼è¿ç§æ åµå ¶å®æ¯æ çéåï¼å«åä¸åºéåï¼æ¶èäºæ¶é´
- 3.è¿ææéè¦çâ¼ç¹ï¼å¨Bæ ä¸ï¼ç±äºæ¯â¼ä¸ªèç¹å°±æ¯â¼â¾æ°æ®ï¼é£ä¹å°±æ¯â¼æ¬¡IO读åºçèç¹æ´å°ã
- å¨è®¡ç®æºä¸,ææä¸ç©ºé´ç¸å ³çä¸â»é½æ¯æç §å(block)è¿â¾åååæä½ç
- æ¯æ¬¡è¯»åé½æå³çâ¼æ¬¡I/Oï¼å设计ç®æºä¸æ¯ä¸ªåç⼤⼩为4K,â¾ç⼤⼩为1k,ç´¢å¼ç⼤⼩为0.06K
- å¦æéè¦å¯»åéåç次æ°å¤ï¼å°±æå³çæ´å¤çIO
2.2 B+æ ç®ä»
è¿éæç¯å客ç®åä»ç»B+ æ ï¼ä¸ªäººæè§æ¯è¾æ¯è¾æ¸ æ°ææï¼ä¼ éé¨<( ̄︶ ̄)â[GO!]@zhifeng687ãb+æ å¾æ详解ã
2.3 Bæ ä¸B+æ 对æ¯
1ç£ç读å代价æ´ä½
- Bæ çæ°æ®åç´¢å¼é½å¨åä¸ä¸ªèç¹ä¸ï¼é£ä¹æ¯ä¸ªåä¸å å«çç´¢å¼æ¯å°éçï¼å¦ææ³è¦ååºæ¯è¾æ·±å±çæ°æ®ï¼æå³çè¦è¯»åæ´å¤çåï¼æè½å¾å°æ³è¦çç´¢å¼åæ°æ®ï¼é£ä¹å°±å¢å äºIO次æ°
- B+æ ä¸æ¯ä¸ªåè½åå¨çç´¢å¼æ¯Bæ çå¾å¤åï¼é£ä¹è·åæ¯è¾æ·±å±çæ°æ®ï¼ä¹åªéè¦è¯»åå°éçåå°±å¯ä»¥ï¼é£ä¹å°±åå°äºç£ççIO次æ°
2.éæºIOç次æ°æ´å°
- éæºI/Oæ¯æ读åæä½æ¶é´è¿ç»ï¼ä½è®¿é®å°åä¸è¿ç»,æ¶é¿çº¦ä¸º10msã
- 顺åºI/Oæ¯æ读åååå ¥æä½åºäºé»è¾åé个è¿ç»è®¿é®æ¥èªç¸é»å°åçæ°æ®,æ¶é¿çº¦ä¸º0.1ms
- å¨ç¸åæ åµä¸,Bæ è¦è¿è¡æ´å¤çéæºIOï¼èB+æ éè¦æ´å¤ç顺åºIO,å æ¤B+æ ,æçä¹æ´å¿«
3.æ¥è¯¢é度æ´ç¨³å®
- ç±äºB+Treeéå¶åèç¹ä¸åå¨æ°æ®(data) ï¼å æ¤ææçæ°æ®é½è¦æ¥è¯¢è³å¶åèç¹ï¼èå¶åèç¹ç
- é«åº¦é½æ¯ç¸åçï¼å æ¤æææ°æ®çæ¥è¯¢é度é½æ¯ä¸æ ·çã
2.4 èç°ç´¢å¼åéèç°ç´¢å¼
ç´¢å¼åå为èç°ç´¢å¼åéèç°ç´¢å¼ä¸¤ç§ã
- å¨ç´¢å¼çåç±»ä¸ï¼æ们å¯ä»¥æç §ç´¢å¼çé®æ¯å¦ä¸ºä¸»é®æ¥å为â主索å¼âåâè¾ å©ç´¢å¼â
- 使ç¨ä¸»é®é®å¼å»ºç«çç´¢å¼ç§°ä¸ºâ主索å¼âï¼å ¶å®ç称为âè¾ å©ç´¢å¼âã
- å æ¤ä¸»ç´¢å¼åªè½æä¸ä¸ªï¼è¾ å©ç´¢å¼å¯ä»¥æå¾å¤ä¸ªã
以ä¸å ³äºç´¢å¼åçåèç°ä¸éèç°ç´¢å¼é½æ¯ä»¥InnoDB表å¼æ为åºç¡
- æ们ç¥éInnoDBä¸ç´¢å¼å³æ°æ®ï¼ä¹å°±æ¯èç°ç´¢å¼çé£æ£µB+æ çå¶â¼¦èç¹ä¸å·²ç»æææå®æ´çæ°æ®é½å å«äºï¼â½½MyISAMçç´¢å¼â½ æ¡è½ç¶ä¹ä½¿â½¤æ å½¢ç»æï¼ä½æ¯å´å°ç´¢å¼åæ°æ®åå¼åå¨ï¼ä¹å°±æ¯æç´¢å¼ä¿¡æ¯åç¬åå°â¼ä¸ªâ½ä»¶ä¸ï¼è¿ä¸ªâ½ä»¶ç§°ä¸ºç´¢å¼â½ä»¶
- MyISAMä¼åç¬ä¸ºè¡¨ç主é®å建â¼ä¸ªç´¢å¼ï¼åªä¸è¿å¨ç´¢å¼çå¶â¼¦èç¹ä¸åå¨çä¸æ¯å®æ´çæ°æ®è®°å½ï¼â½½æ¯ä¸»é®å¼ + â¾å·çç»åãä¹å°±æ¯å éè¿ç´¢å¼æ¾å°å¯¹åºçâ¾å·ï¼åéè¿â¾å·å»æ¾å¯¹åºçè®°å½ï¼å ¶å®â¾®ä¸»é®ç´¢å¼ä¹æ¯â¼æ ·çï¼è¿ç§æ åµæ们称为âåâ¾âãæ以å¨MyISAMä¸ææçç´¢å¼é½æ¯â¾®èç°ç´¢å¼ï¼ä¹å«â¼çº§ç´¢å¼
2.5 MyISAMåInnoDBçåºå«
æ°æ®åå¨â½ å¼ï¼
- InnoDBç±ä¸¤ç§â½ä»¶ç»æï¼è¡¨ç»æï¼æ°æ®åç´¢å¼
- MyISAMç±ä¸ç§â½ä»¶ç»æï¼è¡¨ç»æãæ°æ®ãç´¢å¼
ç´¢å¼çâ½ å¼ï¼
- ç´¢å¼çåºå±é½æ¯åºäºB+Treeçæ°æ®ç»æ建⽴
- InnoDBä¸ä¸»é®ç´¢å¼ä¸ºèç°ç´¢å¼ï¼è¾ å©ç´¢å¼æ¯â¾®èç°ç´¢å¼
- MyISAMä¸æ°æ®åç´¢å¼åå¨ä¸åçâ½ä»¶ä¸ï¼å æ¤é½æ¯â¾®èç°ç´¢å¼
äºå¡çâ½æï¼
- InnoDBâ½æäºå¡
- MyISAMä¸â½æäºå¡
2.6 æ»ç»
- æ°æ®åºçç´¢å¼å°±æ¯ä¸ºäºæé«æ°æ®æ£ç´¢é度
- èæ°æ®åºçç´¢å¼å°±æ¯åºäºB+ Treeçæ°æ®ç»æå®ç°ç
- å¨InnoDBä¸ä¸»é®æ¯èç°ç´¢å¼èè¾ å©ç´¢å¼|æ¯éèç°ç´¢å¼
- å¨MyISAMä¸ä¸»é®ç´¢å¼|åè¾ å©ç´¢å¼é½æ¯éèç°ç´¢å¼
Â
åï¼MySQLæ ¢æ¥è¯¢ä¸SQLä¼å
1ï¼ä»ä¹æ¯æ ¢æ¥è¯¢
MySQLçæ ¢æ¥è¯¢ï¼å ¨åæ¯æ ¢æ¥è¯¢æ¥å¿ï¼æ¯MySQLæä¾çä¸ç§æ¥å¿è®°å½ï¼ç¨æ¥è®°å½å¨MySQIä¸ååºæ¶é´è¶ è¿éå¼çè¯å¥ã
é»è®¤æ åµä¸ï¼MySQLæ°æ®åºå¹¶ä¸å¯å¨æ ¢æ¥è¯¢æ¥å¿ï¼éè¦æå¨æ¥è®¾ç½®è¿ä¸ªåæ°ã
å¦æä¸æ¯è°ä¼éè¦çè¯ï¼ä¸è¬ä¸å»ºè®®å¯å¨è¯¥åæ°ï¼å¼å¯æ ¢æ¥è¯¢æ¥å¿ä¼æå¤æå°å¸¦æ¥ä¸å®çæ§è½å½±åã
2ï¼æ ¢æ¥è¯¢é ç½®
3ï¼æµè¯
1ï¼å建表ï¼æå ¥æµè¯æ°æ®
2ï¼æ¥çæ ¢æ¥è¯¢æ¥å¿
3ï¼Explainè¯å¥
ç®ä»ï¼Âåæ°ï¼
- ä¸æ¡ æ¥è¯¢è¯å¥å¨ç»è¿MySQLæ¥è¯¢ä¼åå¨çåç§åºäºææ¬åè§åçä¼åä¼åçæä¸ä¸ªæè°çæ§è¡è®¡å
- è¿ä¸ªæ§è¡è®¡åå±ç¤ºäºæ¥ä¸æ¥å ·ä½æ§è¡æ¥è¯¢çæ¹å¼ï¼æ¯å¦å¤è¡¨è¿æ¥ç顺åºæ¯ä»ä¹ï¼å¯¹äºæ¯ä¸ªè¡¨éç¨ä»ä¹è®¿é®æ¹æ³æ¥å ·ä½æ§è¡æ¥è¯¢ççã
- MySQL为æ们æä¾äºEXPLAINè¯å¥æ¥å¸®å©æ们æ¥çæ个è¯å¥çå ·ä½æ§è¡è®¡åã
- idï¼å¨â¼ä¸ªâ¼¤çæ¥è¯¢è¯å¥ä¸æ¯ä¸ªSELECTå ³é®åé½å¯¹åºâ¼ä¸ªå¯â¼çid
- select_typeï¼ SELECTå ³é®å对åºçé£ä¸ªæ¥è¯¢çç±»å
- tableï¼ è¡¨å
- partitionsï¼ å¹é çååºä¿¡æ¯
- typeï¼ é对å表ç访é®â½ æ³
- possible_keysï¼ å¯è½â½¤å°çç´¢å¼
- keyï¼ å®é ä¸ä½¿â½¤çç´¢å¼
- key_lenï¼ å®é 使⽤å°çç´¢å¼â»åº¦
- refï¼ å½ä½¿â½¤ç´¢å¼åçå¼æ¥è¯¢æ¶ï¼ä¸ç´¢å¼åè¿â¾çå¼å¹é ç对象信æ¯
- rowsï¼ é¢ä¼°çéè¦è¯»åçè®°å½æ¡æ°
- filteredï¼ æ个表ç»è¿æç´¢æ¡ä»¶è¿æ»¤åå©ä½è®°å½æ¡æ°çç¾åâ½
- Extraï¼ â¼äºé¢å¤çä¿¡æ¯
使ç¨ExplainåæSQLè¯å¥Â
æ ¹æ®nameè¿è¡æ¥è¯¢
Â
æ ¹æ®ä¸»é®idè¿è¡ç´¢å¼
为nameæ·»å æ®éç´¢å¼ï¼å次æ§è¡
Explainåæ
大家çå°ï¼ç´¢å¼è½ç»æ°æ®æ£ç´¢æé«çæçé常ææ¾
é£ä¹æ¯å¦æå³çæ们åªè¦å°½å¯è½å¤çå»å»ºç«ç´¢å¼å°±å¯ä»¥äºå¢?
- æ¯å»ºç«ä¸ä¸ªç´¢å¼é½ä¼å»ºç«ä¸æ£µB+æ ï¼å¹¶ä¸éè¦ç»´æ¤ï¼è¿æ¯å¾è´¹æ§è½ååå¨ç©ºé´çã
4ï¼ç´¢å¼ä½¿ç¨
4.1 éå½å»ºç«ç´¢å¼
- 1.å建并使ç¨èªå¢æ°åæ¥å»ºç«ä¸»é®ç´¢å¼
- 2.ç»å¸¸ä½ä¸ºwhereæ¡ä»¶çå段建ç«ç´¢å¼.
- 3.æ·»å ç´¢å¼|çå段尽å¯è½çä¿æå¯ä¸æ§
- 4.å¯èè使ç¨èåç´¢å¼å¹¶è¿è¡ç´¢å¼è¦ç
4.2 åç使ç¨ç´¢å¼
1ï¼ä¸è¦å¨åä¸ä½¿ç¨å½æ°åè¿è¡è¿ç®
2ï¼éå¼è½¬æ¢ï¼æ°æ®ç±»åä¸ä¸è´ï¼å¯è½å½±åç´¢å¼å¤±æ
3ï¼likeè¯å¥çç´¢å¼å¤±æé®é¢
4.3 å¤åç´¢å¼ç使ç¨
1ï¼å¤ä¸ªååç´¢å¼å¹¶ä¸æ¯æä½³éæ©
MySQLåªè½ä½¿ç¨ä¸ä¸ªç´¢å¼ï¼ä¼ä»å¤ä¸ªç´¢å¼ä¸éæ©ä¸ªéå¶æä¸ºä¸¥æ ¼çç´¢å¼ï¼å æ¤ï¼ä¸ºå¤ä¸ªåå建 ååç´¢å¼ï¼å¹¶ä¸è½æé«MySQLçæ¥è¯¢æ§è½ã
å¯ä»¥ä½¿ç¨å¤åç´¢å¼|ä¿è¯åé½è¢«ç´¢å¼è¦ç
2ï¼å¤åç´¢å¼çæå·¦åç¼åå
æ¥è¯¢æ¡ä»¶ä¸ä½¿ç¨äºå¤åç´¢å¼ç第ä¸ä¸ªå段ï¼ç´¢å¼è¢«ä½¿ç¨ã å æ¤ï¼å¨å¤åç´¢å¼ä¸ç´¢å¼ |åç顺åºè³å ³éè¦ã
å¦æä¸æ¯æç §ç´¢å¼çæå·¦åå¼å§æ¥æ¾ï¼åæ æ³ä½¿ç¨ç´¢å¼ã
3ï¼å°½å¯è½è¾¾æç´¢å¼è¦ç
å¦æä¸ä¸ªç´¢å¼å å«ææéè¦çæ¥è¯¢çå段çå¼ï¼ç´æ¥æ ¹æ®ç´¢å¼çæ¥è¯¢ç»æè¿åæ°æ®ï¼èæ é读表ï¼è½å¤æ大çæé«æ§è½ã
å æ¤ï¼å¯ä»¥å®ä¹ä¸ä¸ªè®©ç´¢å¼å å«çé¢å¤çåï¼å³ä½¿è¿ä¸ªå对äºç´¢å¼èè¨æ¯æ ç¨çã
Â
5ï¼æ»ç»
SQLè¯å¥çä¼å
- 1.é¿å åµå¥è¯å¥(åæ¥è¯¢)
- 2.é¿å å¤è¡¨æ¥è¯¢(å¤ææ¥è¯¢ç®åå)
ç´¢å¼ä¼å
- 1.éå½å»ºç«ç´¢å¼
-
2.åç使ç¨ç´¢å¼
Â
ç« èæ±æ»å¨è¿éï¼ã¥ï¿£3ï¿£ï¼ã¥ââ¤ï½@&åè§è¤ç«è«&ã05-æ°æ®åºã
对å¦ä¹ Javaæå ´è¶£çåå¦æ¬¢è¿å å ¥QQå¦ä¹ 交æµç¾¤ï¼1126298731
æé®é¢æ¬¢è¿æé®ï¼å¤§å®¶ä¸èµ·å¨å¦ä¹ Javaçè·¯ä¸ææªå级ï¼(oãâ½ã)oâ[BINGO!]