xrLEdSry

1SQL
2
3CREATE DATABASE IF NOT EXISTS testdb;
4CREATE DATABASE `testdb`;
5
6CREATE DATABASE `testdb`
7DEFAULT CHARACTER SET latin1;
8
9CREATE DATABASE `testdb`
10DEFAULT CHARACTER SET utf8
11COLLATE utf8_general_ci;
12
13SHOW DATABASES;
14USE testdb;
15SELECT DATABASE();
16
17DROP DATABASE IF NOT EXISTS testdb;
18DROP DATABASE testdb;
19
20
21CREATE TABLE table1 (
22 idtable1 INT PRIMARY KEY AUTO_INCREMENT COMMENT 'PK',
23 text1 VARCHAR(255) DEFAULT 'слово',
24 genre SET('жанр1','жанр2','жанр3') NOT NULL DEFAULT 'жанр1',
25 tmp DATETIME DEFAULT CURRENT_TIMESTAMP
26);
27
28
29SHOW TABLES;
30DESC table1;
31DESC table1\G
32
33SHOW CREATE TABLE table1;
34
35
36CREATE TABLE table2 AS
37SELECT user, host FROM mysql.user;
38SHOW TABLES;
39DESC table2;
40
41SELECT * FROM table2;
42
43
44CREATE TABLE table3 LIKE world.city;
45DESC table3;
46SELECT * FROM table3;
47
48DROP TABLE IF EXISTS table3;
49SHOW TABLES;
50
51ALTER TABLE table2
52  ADD COLUMN bd DATE AFTER user,
53  CHANGE host myhost VARCHAR(30),
54  MODIFY user VARCHAR(32),
55  RENAME table2_1;
56
57SHOW TABLES;
58DESC table2_1;
59
60
61CREATE TABLE table4 (
62 idtable4 INT PRIMARY KEY AUTO_INCREMENT,
63 t TIME DEFAULT '0:0:10'
64);
65SHOW CREATE TABLE table4;
66
67SELECT 
68 TABLE_NAME,
69 ENGINE,
70 TABLE_ROWS,
71 AUTO_INCREMENT,
72 CREATE_TIME,
73 TABLE_COMMENT
74FROM information_schema.TABLES
75WHERE 
76  TABLE_SCHEMA = 'testdb'; 
77
78SELECT 
79 COLUMN_NAME,
80 COLUMN_DEFAULT,
81 IS_NULLABLE,
82 DATA_TYPE,
83 EXTRA,
84 COLUMN_COMMENT
85FROM information_schema.COLUMNS
86WHERE 
87  TABLE_SCHEMA = 'testdb' AND
88  TABLE_NAME = 'table1'; 
89
9034
913
925
9323
9456
95100
967
97
98
993
1005
1017
10223
10334
10456
105100
106
107CREATE INDEX ix_text1
108ON table1 (text1);
109
110CREATE INDEX ix_text1
111ON table1 (text1,tmp);
112
113DROP INDEX ix_text1
114ON table1;
115
116DESC table1;
117SHOW CREATE TABLE table1;
118  
119Яша 34
120Аня 12
121Коля 25
122Аня 26  
123  
124Аня 26
125Аня 12
126Коля 25   
127Яша 34  
128  
129
130  
131  
132CREATE TABLE parent (
133	id INT NOT NULL,
134	PRIMARY KEY (id)
135) ENGINE=INNODB;
136
137CREATE TABLE child (
138	id INT,
139	parent_id INT,
140	INDEX par_ind (parent_id),
141	FOREIGN KEY (parent_id) REFERENCES parent(id)
142	ON DELETE CASCADE
143) ENGINE=INNODB;
144  
145INSERT INTO table1
146VALUES (4,'привет','жанр1',NOW());  
147
148INSERT INTO table1
149VALUES (NULL,'пока','жанр2','2018-03-10 1:1:1');  
150  
151INSERT INTO table1 (text1) 
152VALUES('Ехал Грека через реку');   
153
154INSERT INTO table1 (text1) 
155VALUES
156 ('видит Грека в речке рак'),
157 ('сунул Грека руку в реку'),
158 ('рак за руку Грека цап'),
159 ('ля-ля-ля');  
160  
161INSERT INTO table1 (text1) 
162  SELECT user FROM mysql.user;  
163  
164SELECT * FROM table1;  
165  
166  
167  
168LOAD DATA LOCAL INFILE 'C:/Users/Public/table1.csv'
169    INTO TABLE table1
170    COLUMNS
171        TERMINATED BY ';'
172    LINES
173        TERMINATED BY '\n'
174    IGNORE 1 LINES; 
175
176SELECT * FROM table1;  
177  
178  
179DELETE FROM table1
180WHERE idtable1 = 16;
181  
182DELETE FROM table1
183WHERE idtable1 > 9 AND idtable1 < 13;
184
185USE world;
186
187SELECT id,name FROM city LIMIT 10;
188
189UPDATE city
190SET name = CONCAT(name,' New')
191WHERE id = 5;
192
193UPDATE city
194SET name = 'Amsterdam'
195WHERE id = 5;
196
197CREATE TEMPORARY TABLE ttt(
198 id INT 
199);
200
201SELECT 
202 name,
203 continent,
204 population
205FROM 
206 country
207LIMIT 5; 
208
209
210SELECT 
211 DISTINCT continent
212FROM 
213 country
214LIMIT 5; 
215
216SELECT name,population
217FROM country
218ORDER BY 2 DESC
219LIMIT 5; 
220
221SELECT name,population
222FROM country
223ORDER BY rand()
224LIMIT 5; 
225
226SELECT name,continent
227FROM country
228ORDER BY 2, 1 DESC
229LIMIT 10; 
230
231--Найти 10 самых населённых 
232--городов мира
233SELECT name,population
234FROM city
235ORDER BY population DESC
236LIMIT 10; 
237
238SELECT name,population
239FROM city
240ORDER BY population DESC
241LIMIT 10,8; 
242
243SELECT * FROM country
244WHERE 
245 name = 'Russian Federation'\G
246
247SELECT name FROM country
248WHERE 
249 indepyear BETWEEN 1990 AND 1991;
250  
251  
252SELECT name FROM country  
253 WHERE name LIKE 'Ru%';
254  
255SELECT name FROM country  
256 WHERE name LIKE '%Rus%';
257 
258SELECT name FROM country  
259 WHERE name LIKE '_a_a%';
260
261SELECT name FROM country  
262 WHERE code IN ('RUS','SVN','KAZ');
263
264-- Найти города с населением от одного до двух миллионов человек
265
266-- Найти страны с названиями завершающимися на "nia" 
267
268-- Вывести города с номерами 3456, 2345, 4000, 1111
269
270-- Вывести города с названиями Springfield, Moscow, Kazan
271
272SELECT AVG(lifeexpectancy)
273FROM country;
274
275SELECT AVG(population)
276FROM city;
277
278SELECT COUNT(*)
279FROM country;
280
281SELECT COUNT(*)
282FROM country
283WHERE continent = 'EUROPE';
284
285SELECT COUNT(continent)
286FROM country
287WHERE continent = 'EUROPE';
288
289SELECT COUNT(DISTINCT continent)
290FROM country;
291
292SELECT COUNT(DISTINCT continent)
293AS Количество
294FROM country;
295
296SELECT MAX(surfacearea)
297FROM country;
298
299SELECT SUM(population)
300FROM country;
301
302-- кто раньше всех обрел независомость?
303
304-- сколько стран в Азии?
305
306-- каково население стран БРИКС
307Brasil
308Russian Federation
309India
310China
311South Africa
312
313SELECT SUM(population)
314FROM country
315WHERE name IN (
316'Brasil',
317'Russian Federation',
318'India',
319'China',
320'South Africa'
321);
322
323
324SELECT continent,SUM(population)
325FROM country
326GROUP BY continent;
327
328--найти количество стран по каждой форме правления
329SELECT GovernmentForm,COUNT(*)
330FROM country
331GROUP BY GovernmentForm;
332
333
334SELECT continent,region,count(*)
335FROM country
336GROUP BY continent,region;
337
338SELECT continent,region,count(*)
339FROM country
340GROUP BY continent,region WITH ROLLUP;
341
342SELECT continent,region,count(*)
343FROM country
344GROUP BY continent,region
345ORDER BY 3 DESC;
346
347-- Найти кол-во записей по каждому языку в countrylanguage + отсортировать по убыванию кол-ва записей
348select language, count(*)
349from countrylanguage
350group by 1  
351order by 2 desc
352limit 10;
353
354
355Brazil
356
357SELECT count(*)
358FROM country,city
359WHERE code = countrycode  AND
360country.name = 'Brazil';
361
362SELECT count(*)
363FROM country co,city ci
364WHERE code = countrycode  AND
365co.name = 'Brazil';
366
367SELECT count(*)
368FROM country INNER JOIN city
369ON code = countrycode  
370WHERE country.name = 'Brazil';
371
372SELECT count(*)
373FROM country CROSS JOIN city
374ON code = countrycode  
375WHERE country.name = 'Brazil';
376
377Сотрудники          Отделы
3781 Иванов    2       1 Разработки
3792 Петров    1       2 Тестирования  
3803 Сидоров   2       3 Манагеров
3814 Петрович  5
382
383SELECT name_emp, name_dep
384FROM  employee LEFT OUTER JOIN departments
385ON id_dep = employee_iddep
386
387Иванов    Тестирования
388Петров    Разработки  
389Сидоров   Тестирования
390Петрович  NULL
391
392SELECT name_emp, name_dep
393FROM  employee RIGHT OUTER JOIN departments
394ON id_dep = employee_iddep
395
396Петров  Разработки 
397Иванов  Тестирования
398Сидоров Тестирования
399NULL    Манагеров
400
401SELECT country.name,city.name
402FROM country LEFT OUTER JOIN city 
403ON code = countrycode
404WHERE city.name IS NULL;
405
406--Найти все языки в стране "Russian Federation"
407SELECT language,percentage
408FROM country LEFT OUTER JOIN countrylanguage 
409ON code = countrycode
410WHERE country.name = "Russian Federation"
411ORDER BY 2 DESC;
412
413
414
415CREATE SCHEMA IF NOT EXISTS `mybookshop` DEFAULT CHARACTER SET utf8 ;
416USE `mybookshop` ;
417
418-- -----------------------------------------------------
419-- Table `mybookshop`.`book`
420-- -----------------------------------------------------
421CREATE TABLE IF NOT EXISTS `mybookshop`.`book` (
422  `idbook` INT NOT NULL AUTO_INCREMENT,
423  `title` VARCHAR(100) NOT NULL COMMENT 'Название книги',
424  `author` VARCHAR(255) NOT NULL COMMENT 'Автор книги',
425  `descrition` VARCHAR(5000) NULL COMMENT 'Описание книги',
426  `pubYear` YEAR NULL COMMENT 'Год издания',
427  `price` DECIMAL(6,2) NOT NULL DEFAULT 0.0 COMMENT 'Цена книги',
428  `genre` SET('классика', 'компьютерная', 'фантастика', 'детектив', 'ужасы') NULL DEFAULT 'классика',
429  PRIMARY KEY (`idbook`))
430ENGINE = InnoDB;
431
432
433-- -----------------------------------------------------
434-- Table `mybookshop`.`customer`
435-- -----------------------------------------------------
436CREATE TABLE IF NOT EXISTS `mybookshop`.`customer` (
437  `idcustomer` INT NOT NULL AUTO_INCREMENT,
438  `fio` VARCHAR(100) NOT NULL,
439  `phone` CHAR(10) NOT NULL,
440  `email` VARCHAR(45) NULL,
441  `gender` ENUM('M', 'F') NULL,
442  `bd` DATE NULL,
443  PRIMARY KEY (`idcustomer`))
444ENGINE = InnoDB;
445
446
447-- -----------------------------------------------------
448-- Table `mybookshop`.`order`
449-- -----------------------------------------------------
450CREATE TABLE IF NOT EXISTS `mybookshop`.`order` (
451  `idorder` INT NOT NULL AUTO_INCREMENT,
452  `dateorder` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
453  `address` VARCHAR(100) NOT NULL,
454  `status` ENUM('выполнен', 'обработка', 'принят', 'отказ') NOT NULL DEFAULT 'принят',
455  `customer_idcustomer` INT NOT NULL,
456  PRIMARY KEY (`idorder`),
457  INDEX `fk_order_customer1_idx` (`customer_idcustomer` ASC),
458  CONSTRAINT `fk_order_customer1`
459    FOREIGN KEY (`customer_idcustomer`)
460    REFERENCES `mybookshop`.`customer` (`idcustomer`)
461    ON DELETE NO ACTION
462    ON UPDATE NO ACTION)
463ENGINE = InnoDB;
464
465
466-- -----------------------------------------------------
467-- Table `mybookshop`.`order_has_book`
468-- -----------------------------------------------------
469CREATE TABLE IF NOT EXISTS `mybookshop`.`order_has_book` (
470  `order_idorder` INT NOT NULL,
471  `book_idbook` INT NOT NULL,
472  `q` TINYINT NULL DEFAULT 1,
473  PRIMARY KEY (`order_idorder`, `book_idbook`),
474  INDEX `fk_order_has_book_book1_idx` (`book_idbook` ASC),
475  INDEX `fk_order_has_book_order1_idx` (`order_idorder` ASC),
476  CONSTRAINT `fk_order_has_book_order1`
477    FOREIGN KEY (`order_idorder`)
478    REFERENCES `mybookshop`.`order` (`idorder`)
479    ON DELETE NO ACTION
480    ON UPDATE NO ACTION,
481  CONSTRAINT `fk_order_has_book_book1`
482    FOREIGN KEY (`book_idbook`)
483    REFERENCES `mybookshop`.`book` (`idbook`)
484    ON DELETE NO ACTION
485    ON UPDATE NO ACTION)
486ENGINE = InnoDB;
487
488
489
490- создать 5 книги в таблице book
491- добавить 2 пользователей в customer
492- оформить два заказа с разным набором книг
493- обновить инфу одной книги
494- удалить одну книгу
495- создать выборки с 
496   - сортировкой
497   - лимитированием
498   - группировкой
499   - объединением
500   - агрегированием