Advertisement
Guest User

Untitled

a guest
May 1st, 2017
676
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.89 KB | None | 0 0
  1. create table customers(
  2. id int primary key auto_increment,
  3. name varchar(255),
  4. age int (255),
  5. email varchar (255),
  6. address varchar (255)
  7. );
  8.  
  9. create table traders(
  10. id int primary key auto_increment,
  11. name varchar(255),
  12. address varchar (255)
  13. );
  14.  
  15. create table goods(
  16. id int primary key auto_increment,
  17. name varchar(255),
  18. price int
  19. );
  20.  
  21. create table purchases(
  22. id int primary key auto_increment,
  23. customer_id int,
  24. time datetime
  25. );
  26.  
  27. create table goods_traders(
  28. id int primary key auto_increment,
  29. good_id int,
  30. trader_id int
  31. );
  32.  
  33. create table goods_purchases_traders(
  34. id int primary key auto_increment,
  35. goods_trader_id int,
  36. purchase_id int
  37. );
  38.  
  39. insert into customers (name,age,email,address) values ('maeda','21','maeda@gmail.com','tokyo'),
  40. ('kimura','33','kimura@gmail.com','nara'),('nomura','35','nomura@yahoo.co.jp','hyogo');
  41.  
  42. insert into traders (name,address) values ('mitsubishi','tokyo'),('mitsui','saitama');
  43.  
  44. insert into goods (name,price) values ('apple','400'),('banana','200'),('orange','300');
  45.  
  46. insert into goods_traders (good_id,trader_id) values (1,1),(1,2),(2,2),(3,1);
  47.  
  48. insert into purchases (customer_id,time) values (1,'2017-04-27 11:00:00'),(1,'2017-04-27 11:00:00'),(2,'2017-04-27 11:00:00'),(1,'2017-04-27 12:00:00'),(3,'2017-04-28 11:00:00'),(2,'2017-04-29 11:00:00');
  49.  
  50. insert into goods_purchases_traders (goods_trader_id,purchase_id) values (1,1),(2,2);
  51.  
  52. 1.
  53. select id,time from purchases where customer_id =1 order by time;
  54. select goods_trader_id from goods_purchases_traders where purchase_id in ○ and ●;
  55. select good_id from goods_traders where id = △;
  56. select * from goods where id = □;
  57.  
  58. 2.
  59. select purchase_id from purchases where purchase_time is '2015-06-01';
  60. select good_trader_id from goods_purchases_traders where purchase_id between ○ and ●;
  61. select good_id from goods_traders where good_trader_id = △;
  62. select * from goods where good_id = □;
  63.  
  64. 3.
  65. select trader_id from goods_traders where good_id =1;
  66. select * from traders where trader_id = ○;
  67.  
  68. 4.
  69. select good_id from goods_traders where trader_id = 1;
  70. select * from goods where good_id = ○;
  71.  
  72.  
  73.  
  74. --------------------------------------------------------------------------------------
  75.  
  76.  
  77. create table classes(
  78. id int auto_increment primary key,
  79. name varchar(255)
  80. );
  81.  
  82. create table students(
  83. id int auto_increment primary key,
  84. number int (255),
  85. password varchar (255)
  86. );
  87.  
  88. create table majors(
  89. id int auto_increment primary key,
  90. name varchar (255)
  91. );
  92.  
  93. create table rooms(
  94. id int auto_increment primary key,
  95. name varchar (255)
  96. );
  97.  
  98. create table teachers(
  99. id int auto_increment primary key,
  100. name varchar (255)
  101. );
  102.  
  103. create table schedules(
  104. id int auto_increment primary key,
  105. day varchar(255),
  106. number int (255)
  107. );
  108.  
  109. create table classes_rooms(
  110. id int auto_increment primary key,
  111. class_id int (255),
  112. room_id int (255)
  113. );
  114.  
  115. create table classes_majors(
  116. id int auto_increment primary key,
  117. class_id int (255),
  118. major_id int (255)
  119. );
  120.  
  121. create table classes_students(
  122. id int auto_increment primary key,
  123. class_id int (255),
  124. student_id int (255)
  125. );
  126.  
  127. create table classes_teachers(
  128. id int auto_increment primary key,
  129. class_id int (255),
  130. teacher_id int (255)
  131. );
  132.  
  133. create table classes_schedules(
  134. id int auto_increment primary key,
  135. class_id int (255),
  136. schedule_id int (255)
  137. );
  138.  
  139.  
  140. insert into classes (name) values ('suugaku1'),('suugaku2'),('suugaku3'),('seimei1'),('seimei2'),('seimei3');
  141.  
  142. insert into students(number,password) values
  143. (560001,1111),(560002,2222),(560003,3333),(560004,4444),(560005,5555);
  144.  
  145. insert into majors(name) values
  146. ('ri1'),('ri2'),('ri3'),('bun1'),('bun2'),('bun3');
  147.  
  148. insert into rooms(name) values
  149. (101),(102),(103),(104),(105),(106);
  150.  
  151. insert into teachers(name) values
  152. ('Maeda'),('Wada'),('Yoshimura'),('Minoda');
  153.  
  154. insert into schedules(day,number) values
  155. ('Monday','1'),('Tuesday','2'),('Wednesday','3'),('Monday','4'),('Tuesday','1'),('Wednesday','2'),('Friday','3'),('Friday','4');
  156.  
  157. insert into classes_rooms(class_id,room_id) values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
  158.  
  159. insert into classes_majors(class_id,major_id) values (1,1),(1,2),(2,1),(2,2),(2,3),(2,4),(3,3),(4,4),(5,5),(6,6);
  160.  
  161. insert into classes_students(class_id,student_id) values (1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(2,2),(4,2),(3,3),(3,4),(3,5),(4,5),(6,5),(6,6);
  162.  
  163. insert into classes_teachers(class_id,teacher_id) values (1,1),(1,2),(2,1),(2,2),(2,3),(2,4),(3,3),(4,4);
  164.  
  165. insert into classes_schedules(class_id,schedule_id) values (1,1),(1,2),(2,1),(2,2),(2,3),(2,4),(3,3),(4,4),(5,5),(6,6);
  166.  
  167.  
  168.  
  169. 1.
  170. select class_id from classes_schedules where schedule_id = 2 or schedule_id = 5;
  171. select name from classes where id = ○;
  172.  
  173. 2.
  174. select class_id from classes_students where student_id = 1;
  175. select name from classes where id = ○;
  176.  
  177. 3.
  178. select class_id from classes_majors where major_id = 1;
  179. select name from classes where id = ○;
  180.  
  181.  
  182.  
  183. --------------------------------------------------------------------------------------
  184.  
  185.  
  186.  
  187. create table users(
  188. id int auto_increment primary key,
  189. name varchar(255),
  190. email varchar(255),
  191. password varchar(255)
  192. );
  193.  
  194. create table groups(
  195. id int auto_increment primary key,
  196. name varchar(255)
  197. );
  198.  
  199. create table friends(
  200. id int auto_increment primary key,
  201. send_id int (255),
  202. receive_id int (255)
  203. );
  204.  
  205. create table blocks(
  206. id int auto_increment primary key,
  207. send_id int (255),
  208. receive_id int (255)
  209. );
  210.  
  211. create table individual_messages(
  212. id int auto_increment primary key,
  213. send_id int (255),
  214. receive_id int (255),
  215. message text
  216. );
  217.  
  218. create table group_messages(
  219. id int auto_increment primary key,
  220. group_id int (255),
  221. user_id int (255),
  222. message text
  223. );
  224.  
  225. create table groups_users(
  226. id int auto_increment primary key,
  227. group_id int (255),
  228. user_id int (255)
  229. );
  230.  
  231. insert into users(name,email,password) values ('Maeda','maeda@gamil.com','1111'),('Kimura','kimura@gamil.com','2222'),('Minoda','minoda@gamil.com','3333'),('Yano','yano@gamil.com','4444');
  232.  
  233. insert into groups (name) values ('あ'),('い'),('う'),('え');
  234.  
  235. insert into friends (send_id,receive_id) values (1,2),(1,3),(1,4),(2,1),(2,4),(3,2),(4,2);
  236.  
  237. insert into blocks (send_id,receive_id) values (2,3),(3,1),(4,1);
  238.  
  239. insert into individual_messages(send_id,receive_id,message) values (1,2,'ちぇけら'),(1,3,'おはよう'),(1,4,'こんにちは'),(3,2,'hoge');
  240.  
  241. insert into group_messages(group_id,user_id,message) values (1,1,'グループチェットです'),(1,2,'ふむふむ');
  242.  
  243. insert into groups_users(group_id,user_id) values (1,1),(1,2),(1,3);
  244.  
  245.  
  246. 1.
  247. select message from individual_message where send_id=1 and receive_id=2;
  248.  
  249. 2.
  250. select message from group_messages where group_id=1;
  251.  
  252. 3.
  253. select message from individual_messages where individual_messages.message like '%hoge%';
  254.  
  255. select message from group_messages where group_messages.message like '%hoge%';
  256.  
  257.  
  258.  
  259.  
  260. --------------------------------------------------------------------------------------
  261.  
  262.  
  263.  
  264. create table users(
  265. id int auto_increment primary key,
  266. name varchar(255),
  267. email varchar(255),
  268. password varchar(255)
  269. );
  270.  
  271. create table follows(
  272. id int auto_increment primary key,
  273. follow_id int(255),
  274. follower_id int(255)
  275. );
  276.  
  277. create table tweets(
  278. id int auto_increment primary key,
  279. user_id int(255),
  280. created_at datetime,
  281. content text
  282. );
  283.  
  284. create table favorites(
  285. id int auto_increment primary key,
  286. user_id int(255),
  287. tweet_id int(255)
  288. );
  289.  
  290. insert into users(name,email,password) values ('Maeda','maeda@gamil.com','1111'),('Kimura','kimura@gamil.com','2222'),('Minoda','minoda@gamil.com','3333'),('Yano','yano@gamil.com','4444');
  291.  
  292. insert into follows (follow_id,follower_id) values (1,2),(1,3),(1,4),(2,1),(2,4),(3,2),(4,2);
  293.  
  294. insert into tweets(user_id,created_at,content) values (1,'2017-04-30 12:00:00','4月も終わりです'),(1,'2017-05-01 12:00:00','5月が始まりました'),(2,'2017-04-25 12:00:00','hogehoge'),(2,'2017-04-26 12:00:00','hogehogehogehoge');
  295.  
  296. insert into favorites (user_id,tweet_id) values (1,2),(1,3),(1,4),(2,1),(2,4),(3,2),(4,2);
  297.  
  298. 1.
  299. select follower_id from follows where follow_id=1;
  300.  
  301. 2.
  302. select follower_id from follows where follow_id=1;
  303. select content from tweets where user_id = ○;
  304.  
  305. 3.
  306. select tweet_id from favorites where user_id=1;
  307. select content from tweets where id = ○;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement