CoCalc Logo Icon
StoreFeaturesDocsShareSupportNewsAboutSign UpSign In
jackfrued

CoCalc provides the best real-time collaborative environment for Jupyter Notebooks, LaTeX documents, and SageMath, scalable from individual users to large groups and classes!

GitHub Repository: jackfrued/Python-100-Days
 Path: blob/master/Day36-45/code/stock.sql
Views: 729
1
CREATE DATABASE IF NOT EXISTS `stock`;

2


3
USE `stock`;

4


5
DROP TABLE IF EXISTS `tb_baba_stock`;

6


7
CREATE TABLE `tb_baba_stock` (

8
  `stock_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',

9
  `trade_date` date NOT NULL COMMENT '交易日',

10
  `high_price` decimal(16,6) NOT NULL COMMENT '最高价',

11
  `low_price` decimal(16,6) NOT NULL COMMENT '最低价',

12
  `open_price` decimal(16,6) NOT NULL COMMENT '开盘价',

13
  `close_price` decimal(16,6) NOT NULL COMMENT '收盘价',

14
  `trade_volume` bigint unsigned NOT NULL COMMENT '交易量',

15
  PRIMARY KEY (`stock_id`)

16
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

17


18
LOCK TABLES `tb_baba_stock` WRITE;

19


20
INSERT INTO `tb_baba_stock` VALUES (1,'2019-12-31',213.639999,210.729996,212.000000,212.100006,6773600),(2,'2020-01-02',219.979996,216.539993,216.600006,219.770004,15873500),(3,'2020-01-03',218.203003,216.009995,216.350006,217.000000,8604500),(4,'2020-01-06',217.160004,214.089996,214.889999,216.639999,11885500),(5,'2020-01-07',218.940002,216.690002,217.639999,217.630005,9388000),(6,'2020-01-08',220.649994,216.320007,216.600006,218.000000,11959100),(7,'2020-01-09',223.080002,220.820007,221.500000,221.779999,13122500),(8,'2020-01-10',225.960007,222.061005,223.899994,223.830002,12296000),(9,'2020-01-13',231.139999,227.039993,228.809998,230.479996,17544800),(10,'2020-01-14',230.175003,224.880005,230.050003,226.490005,17266900),(11,'2020-01-15',227.820007,224.389999,226.649994,225.059998,10743400),(12,'2020-01-16',226.330002,222.729996,226.300003,223.940002,13700500),(13,'2020-01-17',228.000000,225.350006,225.899994,227.429993,12956200),(14,'2020-01-21',222.600006,220.729996,222.449997,222.259995,15831100),(15,'2020-01-22',225.580002,222.000000,224.690002,222.369995,10059600),(16,'2020-01-23',220.130005,216.770004,217.929993,219.130005,18519900),(17,'2020-01-24',219.830002,211.324997,218.490005,213.750000,18143900),(18,'2020-01-27',208.020004,199.500000,201.220001,205.470001,24574700),(19,'2020-01-28',210.910004,207.169998,209.740005,210.229996,16196700),(20,'2020-01-29',213.979996,209.520004,212.559998,212.020004,12871900),(21,'2020-01-30',209.860001,205.029999,207.880005,208.580002,14376000),(22,'2020-01-31',207.929993,204.727997,206.500000,206.589996,18181400),(23,'2020-02-03',215.020004,208.669998,208.669998,213.100006,14118800),(24,'2020-02-04',224.380005,220.490005,221.350006,222.880005,16695100),(25,'2020-02-05',226.699997,217.539993,226.520004,220.220001,15766100),(26,'2020-02-06',223.649994,219.779999,223.130005,220.899994,10790800),(27,'2020-02-07',217.839996,214.880005,217.460007,216.529999,13790300),(28,'2020-02-10',215.770004,212.199997,213.500000,215.770004,17420300),(29,'2020-02-11',220.009995,215.289993,219.910004,217.210007,16073500),(30,'2020-02-12',225.520004,220.210007,221.130005,224.309998,18671900),(31,'2020-02-13',225.000000,218.990005,220.000000,220.360001,28069700),(32,'2020-02-14',221.639999,218.229996,221.100006,219.630005,10690000),(33,'2020-02-18',220.850006,217.509995,218.550003,220.520004,12987000),(34,'2020-02-19',223.559998,220.750000,221.520004,222.139999,10798100),(35,'2020-02-20',222.500000,214.220001,222.500000,218.039993,14950100),(36,'2020-02-21',217.600006,211.559998,217.539993,212.589996,17681200),(37,'2020-02-24',207.279999,202.509995,203.550003,206.160004,19380200),(38,'2020-02-25',209.949997,204.100006,208.509995,205.610001,18132400),(39,'2020-02-26',213.080002,206.789993,206.800003,208.740005,19482100),(40,'2020-02-27',209.970001,201.860001,205.009995,205.029999,22741200),(41,'2020-02-28',208.919998,198.561005,198.979996,208.000000,31251100),(42,'2020-03-02',211.110001,203.755997,208.589996,210.979996,21542200),(43,'2020-03-03',211.389999,202.240005,211.080002,207.410004,20813800),(44,'2020-03-04',212.699997,208.850006,209.490005,211.960007,12474400),(45,'2020-03-05',215.149994,209.139999,210.000000,211.460007,13462900),(46,'2020-03-06',207.000000,201.100006,206.699997,204.639999,21700500),(47,'2020-03-09',199.889999,193.929993,195.619995,197.660004,25502600),(48,'2020-03-10',207.449997,200.800003,205.529999,206.389999,19022600),(49,'2020-03-11',203.100006,196.110001,201.649994,198.910004,18908200),(50,'2020-03-12',190.487000,183.929993,186.500000,185.100006,30247100),(51,'2020-03-13',195.990005,187.809998,194.899994,194.000000,23650500),(52,'2020-03-16',188.000000,174.500000,176.149994,178.850006,26628500),(53,'2020-03-17',189.389999,179.690002,181.259995,184.809998,19276500),(54,'2020-03-18',182.410004,170.000000,176.000000,180.000000,22054200),(55,'2020-03-19',187.250000,177.389999,179.259995,180.880005,20905700),(56,'2020-03-20',188.300003,180.000000,187.740005,181.300003,21006100),(57,'2020-03-23',178.500000,169.949997,175.270004,176.339996,22554700),(58,'2020-03-24',188.800003,181.179993,183.089996,185.750000,20801500),(59,'2020-03-25',195.190002,184.514999,185.820007,188.559998,19605200),(60,'2020-03-26',196.320007,187.600006,188.639999,195.320007,15416800),(61,'2020-03-27',192.740005,188.000000,189.970001,188.589996,13376400),(62,'2020-03-30',191.479996,187.009995,187.479996,191.270004,12254400),(63,'2020-03-31',196.789993,190.600006,192.000000,194.479996,19084800),(64,'2020-04-01',192.869995,185.039993,189.500000,187.559998,17948600),(65,'2020-04-02',191.300003,185.692001,186.080002,188.899994,13412100),(66,'2020-04-03',190.550003,185.410004,190.119995,187.110001,9483100),(67,'2020-04-06',196.880005,192.699997,194.740005,196.449997,13248700),(68,'2020-04-07',201.449997,197.050003,200.050003,198.000000,14934500),(69,'2020-04-08',198.860001,193.884003,198.339996,195.979996,13608000),(70,'2020-04-09',200.350006,193.399994,198.649994,196.369995,17821300),(71,'2020-04-13',200.324997,195.529999,197.399994,199.440002,16117900),(72,'2020-04-14',207.970001,204.250000,204.949997,204.779999,16757100),(73,'2020-04-15',209.149994,201.309998,204.770004,208.169998,10768100),(74,'2020-04-16',213.250000,209.119995,210.529999,212.660004,16353800),(75,'2020-04-17',214.979996,208.850998,214.979996,209.500000,13558500),(76,'2020-04-20',216.100006,209.360001,209.869995,212.130005,19295400),(77,'2020-04-21',212.130005,205.029999,209.899994,207.339996,14107600),(78,'2020-04-22',212.000000,209.210007,212.000000,209.960007,13471100),(79,'2020-04-23',210.559998,203.460007,210.240005,205.240005,20424000),(80,'2020-04-24',206.500000,202.820007,205.889999,204.360001,14673900),(81,'2020-04-27',207.779999,202.029999,207.550003,203.690002,17286800),(82,'2020-04-28',205.190002,199.406998,204.809998,201.149994,17203800),(83,'2020-04-29',207.080002,202.509995,202.929993,206.699997,19117800),(84,'2020-04-30',206.699997,199.289993,206.250000,202.669998,20107400),(85,'2020-05-01',197.380005,192.860001,195.750000,194.479996,22276200),(86,'2020-05-04',195.000000,189.529999,194.759995,191.149994,25709400),(87,'2020-05-05',198.270004,194.199997,196.380005,195.020004,22957200),(88,'2020-05-06',198.910004,194.929993,197.669998,195.169998,18598900),(89,'2020-05-07',198.089996,194.779999,198.000000,196.490005,16164600),(90,'2020-05-08',203.020004,198.679993,199.800003,201.190002,23819700),(91,'2020-05-11',206.639999,202.380005,202.779999,205.399994,17920700),(92,'2020-05-12',208.050003,200.020004,206.949997,200.309998,17826800),(93,'2020-05-13',204.679993,197.979996,203.619995,199.460007,22429700),(94,'2020-05-14',201.770004,194.029999,195.500000,201.300003,20025900),(95,'2020-05-15',204.490005,200.100006,200.699997,203.679993,17209500),(96,'2020-05-18',215.470001,210.369995,212.500000,215.279999,23556600),(97,'2020-05-19',220.589996,215.190002,216.729996,217.199997,21438100),(98,'2020-05-20',221.156998,210.580002,220.000000,216.789993,38324100),(99,'2020-05-21',214.580002,209.529999,211.289993,212.160004,29850700),(100,'2020-05-22',204.880005,198.990005,203.229996,199.699997,51979300),(101,'2020-05-26',206.800003,201.000000,205.940002,201.720001,28683200),(102,'2020-05-27',202.990005,196.750000,202.990005,201.179993,23605400),(103,'2020-05-28',202.369995,197.619995,199.000000,199.490005,18763200),(104,'2020-05-29',207.880005,196.699997,200.000000,207.389999,43179500),(105,'2020-06-01',207.199997,203.940002,205.899994,206.570007,16169700),(106,'2020-06-02',214.570007,207.169998,207.210007,214.330002,22485800),(107,'2020-06-03',218.970001,213.750000,215.600006,218.610001,19999500),(108,'2020-06-04',220.000000,216.320007,217.279999,218.039993,14962400),(109,'2020-06-05',220.589996,218.699997,220.000000,219.550003,11983200),(110,'2020-06-08',220.399994,215.330002,219.600006,219.000000,12731200),(111,'2020-06-09',220.720001,216.199997,216.309998,220.720001,14140100),(112,'2020-06-10',225.000000,220.600006,222.000000,223.679993,13372400),(113,'2020-06-11',219.949997,213.559998,218.000000,215.240005,15560300),(114,'2020-06-12',219.500000,213.880005,218.500000,217.639999,12716500),(115,'2020-06-15',217.720001,212.750000,214.110001,217.029999,17419900),(116,'2020-06-16',224.839996,220.100006,221.000000,222.619995,17212400),(117,'2020-06-17',225.000000,222.500000,224.399994,224.250000,12486700),(118,'2020-06-18',226.029999,222.919998,223.740005,223.539993,10358800),(119,'2020-06-19',226.500000,220.089996,226.380005,220.639999,19545200),(120,'2020-06-22',222.080002,219.445007,220.919998,221.410004,13303600),(121,'2020-06-23',230.225006,224.529999,225.119995,228.750000,18263400),(122,'2020-06-24',231.029999,224.251999,228.300003,226.220001,12159500),(123,'2020-06-25',224.690002,220.869995,224.589996,222.160004,14953100),(124,'2020-06-26',222.259995,213.500000,222.210007,215.710007,19340600),(125,'2020-06-29',216.500000,211.820007,215.029999,215.020004,12333000),(126,'2020-06-30',216.429993,212.889999,215.740005,215.699997,12933800),(127,'2020-07-01',216.550003,214.740005,215.809998,215.949997,11788100),(128,'2020-07-02',225.024994,220.059998,221.850006,223.600006,18128700),(129,'2020-07-06',240.479996,232.820007,233.309998,240.000000,33702900),(130,'2020-07-07',241.300003,236.130005,241.009995,236.509995,16491600),(131,'2020-07-08',258.489990,244.020004,244.860001,257.679993,39922700),(132,'2020-07-09',268.000000,255.910004,265.549988,261.579987,36803900),(133,'2020-07-10',261.899994,255.710007,260.209991,261.010010,20335000),(134,'2020-07-13',265.660004,250.679993,261.450012,251.669998,21568700),(135,'2020-07-14',249.750000,241.229996,246.570007,248.580002,19782600),(136,'2020-07-15',252.500000,246.539993,251.479996,249.210007,12614700),(137,'2020-07-16',245.050003,240.740005,241.770004,242.500000,18032100),(138,'2020-07-17',247.929993,244.479996,246.550003,247.139999,18742400),(139,'2020-07-20',257.670013,252.429993,256.450012,254.809998,19015000),(140,'2020-07-21',261.920013,256.519989,261.209991,257.899994,17120700),(141,'2020-07-22',256.269989,249.600006,255.759995,251.699997,19682200),(142,'2020-07-23',257.660004,248.690002,252.100006,251.880005,25712000),(143,'2020-07-24',249.479996,241.889999,244.960007,249.000000,17221500),(144,'2020-07-27',252.869995,245.809998,247.110001,250.860001,18966900),(145,'2020-07-28',251.649994,248.529999,251.149994,249.050003,9878700),(146,'2020-07-29',254.300003,250.100006,250.339996,252.449997,11493600),(147,'2020-07-30',253.860001,249.619995,251.699997,252.740005,7032900),(148,'2020-07-31',254.399994,248.100006,254.199997,251.020004,12304100),(149,'2020-08-03',260.649994,254.000000,254.020004,257.940002,11530300),(150,'2020-08-04',262.559998,258.820007,260.079987,262.200012,10994500),(151,'2020-08-05',265.976013,261.755005,263.739990,264.910004,10673800),(152,'2020-08-06',265.700012,259.200012,263.420013,265.679993,10359500),(153,'2020-08-07',257.760010,247.570007,257.410004,252.100006,22529600),(154,'2020-08-10',251.750000,246.100006,249.339996,248.130005,13621700),(155,'2020-08-11',252.880005,247.830002,251.289993,248.419998,10681800),(156,'2020-08-12',256.079987,248.679993,249.250000,255.190002,11112300),(157,'2020-08-13',256.970001,252.880005,256.390015,253.720001,8794500),(158,'2020-08-14',255.770004,251.639999,255.490005,253.970001,7876400),(159,'2020-08-17',257.375000,250.085007,253.000000,256.959991,9760000),(160,'2020-08-18',261.420013,256.059998,258.709991,259.200012,13267800),(161,'2020-08-19',261.290009,257.380005,260.890015,260.589996,14096500),(162,'2020-08-20',258.880005,254.179993,256.890015,257.970001,21460800),(163,'2020-08-21',267.429993,258.309998,259.029999,265.799988,25648200),(164,'2020-08-24',276.970001,271.619995,273.239990,276.019989,22475800),(165,'2020-08-25',289.119995,276.040009,278.059998,286.000000,27535500),(166,'2020-08-26',292.480011,284.100006,289.260010,291.959991,19530300),(167,'2020-08-27',290.250000,282.100006,290.170013,284.170013,14805800),(168,'2020-08-28',289.500000,283.570007,285.089996,289.000000,9681500),(169,'2020-08-31',289.190002,283.609985,288.619995,287.029999,13253800),(170,'2020-09-01',298.000000,288.869995,289.200012,298.000000,13797100),(171,'2020-09-02',299.000000,289.790009,299.000000,296.070007,12638600),(172,'2020-09-03',290.733002,278.160004,289.000000,282.500000,16766200),(173,'2020-09-04',283.779999,267.390015,279.600006,281.390015,15885800),(174,'2020-09-08',273.970001,266.589996,270.230011,270.019989,12734000),(175,'2020-09-09',275.640015,270.880005,274.450012,273.149994,9465700),(176,'2020-09-10',277.100006,267.399994,274.899994,267.549988,10676800),(177,'2020-09-11',274.529999,268.119995,272.630005,271.609985,9412800),(178,'2020-09-14',276.290009,271.760010,275.359985,274.100006,7883300),(179,'2020-09-15',279.100006,274.799988,278.000000,277.959991,8291400),(180,'2020-09-16',283.605011,278.000000,281.029999,278.140015,9583500),(181,'2020-09-17',275.899994,272.321014,273.109985,275.720001,8016900),(182,'2020-09-18',277.269989,270.549988,275.980011,272.410004,11753500),(183,'2020-09-21',274.220001,266.399994,269.100006,273.820007,9076400),(184,'2020-09-22',276.320007,270.049988,276.019989,275.290009,9673300),(185,'2020-09-23',277.829987,271.500000,275.399994,272.950012,7353200),(186,'2020-09-24',271.250000,267.334015,267.929993,269.730011,9562100),(187,'2020-09-25',271.809998,264.559998,267.570007,271.089996,11466600),(188,'2020-09-28',278.839996,274.199005,275.529999,276.010010,8761700),(189,'2020-09-29',279.299988,274.899994,275.429993,276.929993,7673300),(190,'2020-09-30',295.000000,283.709991,284.010010,293.980011,24777700),(191,'2020-10-01',295.589996,288.250000,295.260010,290.049988,16304000),(192,'2020-10-02',291.979004,286.510010,286.619995,288.170013,11482800),(193,'2020-10-05',290.000000,284.880005,289.059998,288.269989,17876400),(194,'2020-10-06',296.839996,286.910004,287.410004,292.390015,15860400),(195,'2020-10-07',299.510010,292.869995,297.989990,296.500000,16669300),(196,'2020-10-08',302.299988,295.441010,298.109985,300.540009,15105800),(197,'2020-10-09',302.609985,297.739990,298.549988,299.739990,11442200),(198,'2020-10-12',309.640015,301.285004,303.950012,306.339996,14780100),(199,'2020-10-13',310.010010,303.029999,306.359985,308.779999,10898500),(200,'2020-10-14',309.959991,300.799988,309.000000,301.040009,15157800),(201,'2020-10-15',299.863007,292.609985,293.799988,299.459991,9709900),(202,'2020-10-16',310.000000,302.950012,303.989990,307.309998,12338000),(203,'2020-10-19',313.809998,303.410004,309.890015,305.290009,14107300),(204,'2020-10-20',311.799988,307.059998,307.600006,309.809998,8806100),(205,'2020-10-21',314.000000,307.700012,311.750000,307.970001,11139700),(206,'2020-10-22',311.850006,304.209991,311.459991,306.279999,8008400),(207,'2020-10-23',310.329987,304.059998,307.269989,309.920013,6231400),(208,'2020-10-26',315.459991,303.200012,309.920013,306.869995,9350100),(209,'2020-10-27',319.320007,305.609985,307.750000,317.140015,13175900),(210,'2020-10-28',313.500000,306.000000,313.500000,307.940002,13644500),(211,'2020-10-29',315.579987,308.910004,309.440002,312.559998,11174500),(212,'2020-10-30',310.829987,300.190002,308.549988,304.690002,14918500),(213,'2020-11-02',311.440002,305.390015,310.950012,310.839996,11857600),(214,'2020-11-03',294.279999,280.779999,286.200012,285.570007,58132500),(215,'2020-11-04',299.170013,285.299988,287.790009,295.709991,28791200),(216,'2020-11-05',291.250000,279.600006,289.089996,287.750000,50065200),(217,'2020-11-06',300.890015,289.786011,291.489990,299.950012,25259600),(218,'2020-11-09',304.000000,290.500000,303.769989,290.529999,22559900),(219,'2020-11-10',280.000000,264.329987,279.970001,266.540009,61064000),(220,'2020-11-11',271.920013,261.204987,261.920013,265.649994,41699000),(221,'2020-11-12',271.200012,263.040009,271.040009,264.309998,27119100),(222,'2020-11-13',265.440002,257.170013,264.720001,260.839996,33942800),(223,'2020-11-16',262.570007,257.029999,260.420013,258.309998,20864200),(224,'2020-11-17',260.279999,256.540009,260.279999,256.799988,19739500),(225,'2020-11-18',258.309998,252.550003,257.760010,255.830002,28307100),(226,'2020-11-19',261.399994,253.559998,255.000000,259.890015,20752800),(227,'2020-11-20',272.369995,261.899994,263.970001,270.739990,34178400),(228,'2020-11-23',275.730011,266.411011,274.750000,270.109985,25317100),(229,'2020-11-24',280.609985,272.799988,276.234985,279.959991,20660500),(230,'2020-11-25',279.329987,274.049988,275.700012,277.720001,15229300),(231,'2020-11-27',278.920013,275.665985,278.799988,276.480011,9583200),(232,'2020-11-30',268.970001,262.519989,268.970001,263.359985,30476100),(233,'2020-12-01',265.670013,261.570007,265.470001,264.010010,14573100),(234,'2020-12-02',263.100006,260.109985,262.000000,261.320007,13919700),(235,'2020-12-03',268.600006,263.140015,263.779999,266.910004,15520500),(236,'2020-12-04',271.299988,266.269989,271.019989,267.250000,15527600),(237,'2020-12-07',265.970001,262.600006,265.700012,264.000000,11388700),(238,'2020-12-08',268.399994,263.869995,265.200012,266.089996,9447700),(239,'2020-12-09',267.910004,263.100006,267.559998,263.799988,9060700),(240,'2020-12-10',266.089996,261.200012,261.989990,264.869995,10290300),(241,'2020-12-11',265.320007,262.500000,264.290009,264.540009,7771300),(242,'2020-12-14',260.855011,256.029999,260.320007,256.029999,18369900),(243,'2020-12-15',255.679993,252.300003,255.339996,255.110001,16595600),(244,'2020-12-16',263.179993,257.040009,257.739990,261.890015,15804800),(245,'2020-12-17',264.850006,260.200012,264.700012,264.429993,14306600),(246,'2020-12-18',264.899994,259.200012,263.899994,260.000000,15981800),(247,'2020-12-21',260.459991,254.500000,255.169998,260.429993,10901800),(248,'2020-12-22',258.339996,255.410004,257.869995,255.830002,11468400),(249,'2020-12-23',257.350006,252.529999,255.500000,256.179993,10729200),(250,'2020-12-24',229.899994,211.229996,228.235001,222.000000,141830000),(251,'2020-12-28',224.990005,215.320007,216.880005,222.360001,73512100),(252,'2020-12-29',239.190002,229.600006,231.759995,236.259995,69715900),(253,'2020-12-30',243.389999,234.645004,243.348007,238.389999,44812300),(254,'2020-12-31',238.919998,231.026993,237.460007,232.729996,23451800);

21


22
UNLOCK TABLES;

23


24