Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table customers(
- id int primary key auto_increment,
- name varchar(255),
- age int (255),
- email varchar (255),
- address varchar (255)
- );
- create table traders(
- id int primary key auto_increment,
- name varchar(255),
- address varchar (255)
- );
- create table goods(
- id int primary key auto_increment,
- name varchar(255),
- price int
- );
- create table purchases(
- id int primary key auto_increment,
- customer_id int,
- time datetime
- );
- create table goods_traders(
- id int primary key auto_increment,
- good_id int,
- trader_id int
- );
- create table goods_purchases_traders(
- id int primary key auto_increment,
- goods_trader_id int,
- purchase_id int
- );
- insert into customers (name,age,email,address) values ('maeda','21','maeda@gmail.com','tokyo'),
- ('kimura','33','kimura@gmail.com','nara'),('nomura','35','nomura@yahoo.co.jp','hyogo');
- insert into traders (name,address) values ('mitsubishi','tokyo'),('mitsui','saitama');
- insert into goods (name,price) values ('apple','400'),('banana','200'),('orange','300');
- insert into goods_traders (good_id,trader_id) values (1,1),(1,2),(2,2),(3,1);
- 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');
- insert into goods_purchases_traders (goods_trader_id,purchase_id) values (1,1),(2,2);
- 1.
- select id,time from purchases where customer_id =1 order by time;
- select goods_trader_id from goods_purchases_traders where purchase_id in ○ and ●;
- select good_id from goods_traders where id = △;
- select * from goods where id = □;
- 2.
- select purchase_id from purchases where purchase_time is '2015-06-01';
- select good_trader_id from goods_purchases_traders where purchase_id between ○ and ●;
- select good_id from goods_traders where good_trader_id = △;
- select * from goods where good_id = □;
- 3.
- select trader_id from goods_traders where good_id =1;
- select * from traders where trader_id = ○;
- 4.
- select good_id from goods_traders where trader_id = 1;
- select * from goods where good_id = ○;
- --------------------------------------------------------------------------------------
- create table classes(
- id int auto_increment primary key,
- name varchar(255)
- );
- create table students(
- id int auto_increment primary key,
- number int (255),
- password varchar (255)
- );
- create table majors(
- id int auto_increment primary key,
- name varchar (255)
- );
- create table rooms(
- id int auto_increment primary key,
- name varchar (255)
- );
- create table teachers(
- id int auto_increment primary key,
- name varchar (255)
- );
- create table schedules(
- id int auto_increment primary key,
- day varchar(255),
- number int (255)
- );
- create table classes_rooms(
- id int auto_increment primary key,
- class_id int (255),
- room_id int (255)
- );
- create table classes_majors(
- id int auto_increment primary key,
- class_id int (255),
- major_id int (255)
- );
- create table classes_students(
- id int auto_increment primary key,
- class_id int (255),
- student_id int (255)
- );
- create table classes_teachers(
- id int auto_increment primary key,
- class_id int (255),
- teacher_id int (255)
- );
- create table classes_schedules(
- id int auto_increment primary key,
- class_id int (255),
- schedule_id int (255)
- );
- insert into classes (name) values ('suugaku1'),('suugaku2'),('suugaku3'),('seimei1'),('seimei2'),('seimei3');
- insert into students(number,password) values
- (560001,1111),(560002,2222),(560003,3333),(560004,4444),(560005,5555);
- insert into majors(name) values
- ('ri1'),('ri2'),('ri3'),('bun1'),('bun2'),('bun3');
- insert into rooms(name) values
- (101),(102),(103),(104),(105),(106);
- insert into teachers(name) values
- ('Maeda'),('Wada'),('Yoshimura'),('Minoda');
- insert into schedules(day,number) values
- ('Monday','1'),('Tuesday','2'),('Wednesday','3'),('Monday','4'),('Tuesday','1'),('Wednesday','2'),('Friday','3'),('Friday','4');
- insert into classes_rooms(class_id,room_id) values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
- 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);
- 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);
- 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);
- 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);
- 1.
- select class_id from classes_schedules where schedule_id = 2 or schedule_id = 5;
- select name from classes where id = ○;
- 2.
- select class_id from classes_students where student_id = 1;
- select name from classes where id = ○;
- 3.
- select class_id from classes_majors where major_id = 1;
- select name from classes where id = ○;
- --------------------------------------------------------------------------------------
- create table users(
- id int auto_increment primary key,
- name varchar(255),
- email varchar(255),
- password varchar(255)
- );
- create table groups(
- id int auto_increment primary key,
- name varchar(255)
- );
- create table friends(
- id int auto_increment primary key,
- send_id int (255),
- receive_id int (255)
- );
- create table blocks(
- id int auto_increment primary key,
- send_id int (255),
- receive_id int (255)
- );
- create table individual_messages(
- id int auto_increment primary key,
- send_id int (255),
- receive_id int (255),
- message text
- );
- create table group_messages(
- id int auto_increment primary key,
- group_id int (255),
- user_id int (255),
- message text
- );
- create table groups_users(
- id int auto_increment primary key,
- group_id int (255),
- user_id int (255)
- );
- 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');
- insert into groups (name) values ('あ'),('い'),('う'),('え');
- insert into friends (send_id,receive_id) values (1,2),(1,3),(1,4),(2,1),(2,4),(3,2),(4,2);
- insert into blocks (send_id,receive_id) values (2,3),(3,1),(4,1);
- insert into individual_messages(send_id,receive_id,message) values (1,2,'ちぇけら'),(1,3,'おはよう'),(1,4,'こんにちは'),(3,2,'hoge');
- insert into group_messages(group_id,user_id,message) values (1,1,'グループチェットです'),(1,2,'ふむふむ');
- insert into groups_users(group_id,user_id) values (1,1),(1,2),(1,3);
- 1.
- select message from individual_message where send_id=1 and receive_id=2;
- 2.
- select message from group_messages where group_id=1;
- 3.
- select message from individual_messages where individual_messages.message like '%hoge%';
- select message from group_messages where group_messages.message like '%hoge%';
- --------------------------------------------------------------------------------------
- create table users(
- id int auto_increment primary key,
- name varchar(255),
- email varchar(255),
- password varchar(255)
- );
- create table follows(
- id int auto_increment primary key,
- follow_id int(255),
- follower_id int(255)
- );
- create table tweets(
- id int auto_increment primary key,
- user_id int(255),
- created_at datetime,
- content text
- );
- create table favorites(
- id int auto_increment primary key,
- user_id int(255),
- tweet_id int(255)
- );
- 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');
- insert into follows (follow_id,follower_id) values (1,2),(1,3),(1,4),(2,1),(2,4),(3,2),(4,2);
- 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');
- insert into favorites (user_id,tweet_id) values (1,2),(1,3),(1,4),(2,1),(2,4),(3,2),(4,2);
- 1.
- select follower_id from follows where follow_id=1;
- 2.
- select follower_id from follows where follow_id=1;
- select content from tweets where user_id = ○;
- 3.
- select tweet_id from favorites where user_id=1;
- select content from tweets where id = ○;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement