YzFDzWPs

1### Tables and Data
2
3```sql
4/*
5 Navicat Premium Data Transfer
6
7 Source Server         : letote-local
8 Source Server Type    : MySQL
9 Source Server Version : 50640
10 Source Host           : localhost:3306
11 Source Schema         : blog_development
12
13 Target Server Type    : MySQL
14 Target Server Version : 50640
15 File Encoding         : 65001
16
17 Date: 20/02/2019 23:38:54
18*/
19
20SET NAMES utf8mb4;
21SET FOREIGN_KEY_CHECKS = 0;
22
23-- ----------------------------
24-- Table structure for physicians
25-- ----------------------------
26DROP TABLE IF EXISTS `physicians`;
27CREATE TABLE `physicians` (
28  `id` bigint(20) NOT NULL AUTO_INCREMENT,
29  `name` varchar(255) DEFAULT NULL,
30  `created_at` datetime NOT NULL,
31  `updated_at` datetime NOT NULL,
32  PRIMARY KEY (`id`)
33) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
34
35-- ----------------------------
36-- Records of physicians
37-- ----------------------------
38BEGIN;
39INSERT INTO `physicians` VALUES (1, '医生1号', '2019-02-20 02:27:25', '2019-02-20 02:27:25');
40INSERT INTO `physicians` VALUES (2, '医生2', '2019-02-19 22:55:41', '2019-02-20 22:55:46');
41COMMIT;
42
43SET FOREIGN_KEY_CHECKS = 1;
44
45
46
47-- ----------------------------
48-- Table structure for patients
49-- ----------------------------
50DROP TABLE IF EXISTS `patients`;
51CREATE TABLE `patients` (
52  `id` bigint(20) NOT NULL AUTO_INCREMENT,
53  `name` varchar(255) DEFAULT NULL,
54  `created_at` datetime NOT NULL,
55  `updated_at` datetime NOT NULL,
56  PRIMARY KEY (`id`)
57) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
58
59-- ----------------------------
60-- Records of patients
61-- ----------------------------
62BEGIN;
63INSERT INTO `patients` VALUES (1, '病人1号', '2019-02-20 02:27:30', '2019-02-20 02:27:30');
64INSERT INTO `patients` VALUES (2, '病人2号', '2019-02-20 22:54:57', '2019-02-21 22:55:05');
65COMMIT;
66
67SET FOREIGN_KEY_CHECKS = 1;
68
69
70-- ----------------------------
71-- Table structure for appointments
72-- ----------------------------
73DROP TABLE IF EXISTS `appointments`;
74CREATE TABLE `appointments` (
75  `id` bigint(20) NOT NULL AUTO_INCREMENT,
76  `physician_id` bigint(20) DEFAULT NULL,
77  `patient_id` bigint(20) DEFAULT NULL,
78  `appointment_date` datetime DEFAULT NULL,
79  `created_at` datetime NOT NULL,
80  `updated_at` datetime NOT NULL,
81  PRIMARY KEY (`id`),
82  KEY `index_appointments_on_physician_id` (`physician_id`),
83  KEY `index_appointments_on_patient_id` (`patient_id`)
84) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
85
86-- ----------------------------
87-- Records of appointments
88-- ----------------------------
89BEGIN;
90INSERT INTO `appointments` VALUES (1, 1, 1, '2019-02-20 22:43:56', '2019-02-20 02:27:33', '2019-02-20 02:27:33');
91INSERT INTO `appointments` VALUES (2, 2, 1, '2019-02-20 22:44:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00');
92INSERT INTO `appointments` VALUES (3, 1, 2, '2019-02-20 22:44:04', '0000-00-00 00:00:00', '0000-00-00 00:00:00');
93COMMIT;
94
95SET FOREIGN_KEY_CHECKS = 1;
96
97
98```
99
100
101### How to Query with multi-tables
102
103```sql
104
105-- 查询ID为1的病人所属的所有医生
106select physicians.* from physicians 
107inner JOIN appointments 
108on physicians.id = appointments.physician_id 
109where appointments.patient_id = 1;
110
111
112-- 查询ID为1的医生所负责的所有病人	 
113select patients.* from patients
114inner join appointments
115on patients.id=appointments.patient_id
116where appointments.physician_id = 1;
117
118
119
120```