Created
July 1, 2018 12:27
-
-
Save savchenkoDev/3f4e586ad5c0505ef4dd302b55cf557c to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE DATABASE `test_guru`; | |
| USE `test_guru`; | |
| CREATE TABLE `categories` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `title` varchar(45) COLLATE utf8_bin DEFAULT NULL, | |
| PRIMARY KEY (`id`) | |
| ); | |
| CREATE TABLE `tests` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `title` varchar(45) COLLATE utf8_bin DEFAULT NULL, | |
| `level` varchar(45) COLLATE utf8_bin DEFAULT NULL, | |
| `c_id` int(11) DEFAULT NULL, | |
| PRIMARY KEY (`id`), | |
| KEY `fk_cat_idx` (`c_id`), | |
| CONSTRAINT `fk_cat` FOREIGN KEY (`c_id`) REFERENCES `categories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION | |
| ); | |
| CREATE TABLE `questions` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `body` varchar(45) COLLATE utf8_bin DEFAULT NULL, | |
| `t_id` int(11) DEFAULT NULL, | |
| PRIMARY KEY (`id`), | |
| KEY `fk_quest_test_idx` (`t_id`), | |
| CONSTRAINT `fk_test` FOREIGN KEY (`t_id`) REFERENCES `tests` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION | |
| ); | |
| INSERT INTO `categories` VALUES (1,'Frontend'),(2,'Backend'),(3,'Database'); | |
| INSERT INTO `tests` VALUES (1,'Ruby',1,NULL),(2,'PHP',2,1),(3,'HTML',2,NULL),(4,'MySQL',1,3),(5,'React',2,2); | |
| INSERT INTO `questions` VALUES (1,'q1',1),(2,'q2',2),(3,'q3',3),(4,'q4',4),(5,'q5',5); | |
| SELECT * FROM tests WHERE level = 2 OR level = 3; | |
| SELECT * FROM questions WHERE t_id = 1; | |
| UPDATE tests SET title = 'Python',level = 2 where id = 3; | |
| DELETE FROM questions WHERE id = 5; | |
| SELECT t.title, c.title FROM tests AS t JOIN categories AS c ON c.id = t.id; | |
| SELECT q.body, t.title FROM questions AS q JOIN tests AS t ON t.id = q.t_id; |
Для полей-связок лучше использовать более понятное название. Если следовать конвенции Rails, то там имя формируется из имени ассоциации и постфикса _id. То есть в данном случае будет category_id
Тут стоит использовать алиасы для полей, чтоб было понятно что за данные в одноименных полях лежат
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
А зачем указывать null как дефолтное значение? Без этого указания ведь итак будет null