· 9 years ago · Aug 02, 2017, 04:40 PM
1ТеÑтовое задание #2:
2
3ВерÑии ПО: MySQL 5.6+
4
5ИмеетÑÑ Ð±Ð°Ð·Ð° данных MySQL Ñ Ñ‚Ñ€ÐµÐ¼Ñ Ñ‚Ð°Ð±Ð»Ð¸Ñ†Ð°Ð¼Ð¸: user, user_profile и user_token.
6Ðеобходимо Ñоздать единÑтвенный запроÑ, в результате Ð²Ñ‹Ð¿Ð¾Ð»Ð½ÐµÐ½Ð¸Ñ ÐºÐ¾Ñ‚Ð¾Ñ€Ð¾Ð³Ð¾ данные будут получены в указанном виде.
7
8ПорÑдок полей:
9 id - ID пользователÑ,
10 email - почтовый Ð°Ð´Ñ€ÐµÑ Ð¿Ð¾Ð»ÑŒÐ·Ð¾Ð²Ð°Ñ‚ÐµÐ»Ñ,
11 name - Ð¸Ð¼Ñ Ð¿Ð¾Ð»ÑŒÐ·Ð¾Ð²Ð°Ñ‚ÐµÐ»Ñ,
12 patronymic - отчеÑтво пользователÑ,
13 surname - Ñ„Ð°Ð¼Ð¸Ð»Ð¸Ñ Ð¿Ð¾Ð»ÑŒÐ·Ð¾Ð²Ð°Ñ‚ÐµÐ»Ñ,
14 money - кол-во денег на Ñчету пользователÑ,
15 tokens - кол-во токенов пользователÑ
16
17Ðужно отобразить только активных пользователей - is_active=1
18
19Результат Ð²Ñ‹Ð¿Ð¾Ð»ÐµÐ½ÐµÐ½Ð¸Ñ Ð·Ð°Ð¿Ñ€Ð¾Ñа:
20 =============================================================================================
21 | id | email | name | patronymic | surname | money | tokens |
22 ---------------------------------------------------------------------------------------------
23 | 1 | vitaliy@yandex.ru | Виталий | Виталиевич | Виталенко | 100,00 | 1 |
24 | 3 | petya@gmail.com | Петр | Петрович | Петров | 300,00 | 3 |
25 | 4 | andrew@gmail.com | Ðндрей | Ðндреевич | ÐндреевÑкий | 400,00 | 4 |
26 =============================================================================================
27
28-- --------------------------------------------------------
29-- ХоÑÑ‚: 127.0.0.1
30-- ВерÑÐ¸Ñ Ñервера: 5.6.29-log - MySQL Community Server (GPL)
31-- ОС Сервера: Win32
32-- HeidiSQL ВерÑиÑ: 9.3.0.4984
33-- --------------------------------------------------------
34
35/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
36/*!40101 SET NAMES utf8mb4 */;
37/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
38/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
39
40-- Дамп Ñтруктуры Ð´Ð»Ñ Ñ‚Ð°Ð±Ð»Ð¸Ñ†Ð° user
41CREATE TABLE IF NOT EXISTS `user` (
42 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
43 `email` varchar(128) NOT NULL DEFAULT '',
44 `password_hash` varchar(128) NOT NULL DEFAULT '',
45 `is_active` tinyint(1) unsigned NOT NULL DEFAULT '1',
46 `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
47 `date_edited` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
48 PRIMARY KEY (`id`),
49 UNIQUE KEY `U_email` (`email`),
50 KEY `K_date_created_date_edited` (`date_created`,`date_edited`),
51 KEY `K_is_active` (`is_active`)
52) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
53
54-- Дамп данных таблицы user: ~4 rows (приблизительно)
55/*!40000 ALTER TABLE `user` DISABLE KEYS */;
56INSERT INTO `user` (`id`, `email`, `password_hash`, `is_active`, `date_created`, `date_edited`) VALUES
57 (1, 'vitaliy@yandex.ru', '$2y$13$AHUQEITMB5AyUDNCngAbeeAVMqZBsjgFhbiBRP9JB7gnJS8/BmT3G', 1, '2016-04-24 11:29:23', '0000-00-00 00:00:00'),
58 (2, 'ivan@yandex.ua', '$2y$13$GH39o2.tcPlx5I4g221Sa.tBj942IpCCTl3bjcoDaIJhAh9bAGJpK', 0, '2016-04-24 11:29:24', '2016-08-24 20:58:48'),
59 (3, 'petya@gmail.com', '$2y$13$slZ4VggmwkEjGu07/D9HD.T9OVPBh67KLsqIxU627qlgGucX9SlJu', 1, '2016-04-24 11:29:25', '0000-00-00 00:00:00'),
60 (4, 'andrew@gmail.com', '$2y$13$B6ixfDzuJmJjMYGaIrlB5erPy9ek2DBMO7oLyqzqfOoXTZ9uNChaS', 1, '2016-04-24 11:29:26', '0000-00-00 00:00:00');
61/*!40000 ALTER TABLE `user` ENABLE KEYS */;
62
63-- Дамп Ñтруктуры Ð´Ð»Ñ Ñ‚Ð°Ð±Ð»Ð¸Ñ†Ð° user_profile
64CREATE TABLE IF NOT EXISTS `user_profile` (
65 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
66 `name` varchar(128) NOT NULL DEFAULT '',
67 `patronymic` varchar(128) NOT NULL DEFAULT '',
68 `surname` varchar(128) NOT NULL DEFAULT '',
69 `phone` varchar(128) NOT NULL DEFAULT '',
70 `money` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
71 `date_birthday` date DEFAULT NULL,
72 `user_id` int(11) unsigned DEFAULT NULL,
73 `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
74 `date_edited` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
75 PRIMARY KEY (`id`),
76 KEY `K_date_created_date_edited` (`date_created`,`date_edited`),
77 KEY `K_user_id` (`user_id`)
78) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
79
80-- Дамп данных таблицы user_profile: ~4 rows (приблизительно)
81/*!40000 ALTER TABLE `user_profile` DISABLE KEYS */;
82INSERT INTO `user_profile` (`id`, `name`, `patronymic`, `surname`, `phone`, `money`, `date_birthday`, `user_id`, `date_created`, `date_edited`) VALUES
83 (1, 'Виталий', 'Виталиевич', 'Виталенко', '+38 099 111 11 11', 100.00, '1984-05-02', 1, '2016-04-24 11:29:24', '0000-00-00 00:00:00'),
84 (2, 'Иван', 'Иванович', 'Иванов', '', 200.00, NULL, 2, '2016-04-24 11:29:26', '0000-00-00 00:00:00'),
85 (3, 'Петр', 'Петрович', 'Петров', '', 300.00, '1988-05-24', 3, '2016-04-24 11:29:26', '0000-00-00 00:00:00'),
86 (4, 'Ðндрей', 'Ðндреевич', 'ÐндреевÑкий', '+38 099 444 44 44', 400.00, NULL, 4, '2016-04-24 11:29:26', '0000-00-00 00:00:00');
87/*!40000 ALTER TABLE `user_profile` ENABLE KEYS */;
88
89-- Дамп Ñтруктуры Ð´Ð»Ñ Ñ‚Ð°Ð±Ð»Ð¸Ñ†Ð° user_token
90CREATE TABLE IF NOT EXISTS `user_token` (
91 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
92 `user_id` int(11) unsigned DEFAULT NULL,
93 `token` varchar(255) NOT NULL DEFAULT '',
94 `date_expiry` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
95 `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
96 `date_edited` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
97 PRIMARY KEY (`id`),
98 KEY `K_date_created_date_edited` (`date_created`,`date_edited`),
99 KEY `K_user_id` (`user_id`)
100) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
101
102-- Дамп данных таблицы user_token: ~0 rows (приблизительно)
103/*!40000 ALTER TABLE `user_token` DISABLE KEYS */;
104INSERT INTO `user_token` (`id`, `user_id`, `token`, `date_expiry`, `date_created`, `date_edited`) VALUES
105 (1, 1, '81D408F8-79C8-46DC-988E-5770CF325B91', '2016-07-24 21:07:15', '2016-08-24 21:07:15', '0000-00-00 00:00:00'),
106 (2, 2, 'D4655B0C-3247-4A6B-9D1B-8E52EAF980C2', '2016-07-25 21:07:15', '2016-08-24 21:07:15', '0000-00-00 00:00:00'),
107 (3, 2, 'CB7ED4EE-AA5C-46CE-B1F9-ACC191B3C1A5', '2016-10-11 21:07:15', '2016-08-24 21:07:15', '0000-00-00 00:00:00'),
108 (4, 3, '4CA4388C-5684-4D8A-A406-B1946AD549F4', '2016-08-12 21:07:15', '2016-08-24 21:07:15', '0000-00-00 00:00:00'),
109 (5, 3, '806A4DBB-EFD7-4C2D-B428-2E6C639B477E', '2016-10-01 21:07:15', '2016-08-24 21:07:15', '0000-00-00 00:00:00'),
110 (6, 3, 'D0F45B0A-85B9-420B-99F0-18C3C02FEC4C', '2016-05-19 21:07:15', '2016-08-24 21:07:15', '0000-00-00 00:00:00'),
111 (7, 4, '35CD8498-5982-4359-88E6-DD772E1B42C1', '2016-01-25 21:07:15', '2016-08-24 21:07:15', '0000-00-00 00:00:00'),
112 (8, 4, '7F111047-B233-4B80-BB2E-CDF277841CEA', '2016-06-06 21:07:15', '2016-08-24 21:07:15', '0000-00-00 00:00:00'),
113 (9, 4, '09BF684D-E981-4B5D-961A-DBED88A65430', '2016-09-04 21:07:15', '2016-08-24 21:07:15', '0000-00-00 00:00:00'),
114 (10, 4, 'F5113033-0260-40C0-AF47-B2E25B1CB734', '2016-09-23 21:07:15', '2016-08-24 21:07:15', '0000-00-00 00:00:00');
115/*!40000 ALTER TABLE `user_token` ENABLE KEYS */;
116/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
117/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
118/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
119
120SELECT
121user.id id,
122`email`,
123`name`,
124`patronymic`,
125`surname`,
126`money`,
127(SELECT COUNT(USER_ID) FROM `user_token` WHERE user_token.user_id=user.id) tokens
128FROM `user`
129LEFT JOIN `user_profile` ON user.id = user_profile.user_id ORDER BY id