ecskZ99X

1SQL
2
3CREATE DATABASE mydb1;
4CREATE DATABASE IF NOT EXISTS mydb1 ;
5SHOW DATABASES;
6DROP DATABASE mydb1;
7
8
9CREATE DATABASE mydb1;
10USE mydb1;
11CREATE TABLE tb1 (
12 idtb1 INT PRIMARY KEY AUTO_INCREMENT,
13 txt   VARCHAR(20) DEFAULT 'привет',
14 dt    DATE COMMENT 'день рождения'
15);
16DESC tb1;
17SHOW CREATE TABLE tb1;
18
19CREATE TEMPORARY TABLE tb2 (
20 d CHAR(5)
21); 
22SHOW TABLES;
23SHOW CREATE TABLE tb2;
24
25\r
26SHOW CREATE TABLE tb2;
27
28CREATE TABLE tb3 
29AS SELECT user, host
30FROM mysql.user;
31
32DESC tb3;
33SELECT * FROM tb3;
34
35CREATE TABLE tb4
36LIKE tb1;
37
38DESC tb4;
39SELECT * FROM tb4;
40
41DESC tb1;
42
43ALTER TABLE tb1
44 ADD COLUMN num SMALLINT AFTER txt,
45 CHANGE dt bd DATE;
46 
47ALTER TABLE tb1
48DROP txt;
49
50DESC tb1;
51
52
531 34
542 100
553 12
564 45
575 24 
586 37
597 89
60
6124
62
633 12
645 24 
651 34
666 37
674 45
687 89
692 100
70
7135
72
73name, age
74name
75
76Вася 23
77Аня  21
78Аня  25
79Вася 13
80
81Аня  25
82Аня  21
83Вася 23
84Вася 13
85
86CREATE INDEX ix_num
87ON tb1 (num);
88
89DESC tb1;
90SHOW CREATE TABLE tb1;
91
92DROP INDEX ix_num ON tb1;
93
94INSERT INTO book
95VALUE (1, "Книга1", "Автор1", 2018, "классика");
96
97SELECT * FROM book;
98
99INSERT INTO book
100VALUE (NULL, "Книга2", "Автор2", 2017, "классика");
101
102SELECT * FROM book;
103
104INSERT INTO book (title,genre)
105VALUES
106 ("Книга3", "фантастика"),
107 ("Книга4", "фантастика"),
108 ("Книга5", "программирование");
109
110SELECT * FROM book;
111
112
113INSERT INTO book
114 SET 
115  title = "Книга6",
116  author = "Автор10",
117  pubYear = 2018, 
118  genre = "классика";
119
120SELECT * FROM book;
121
122INSERT INTO book
123 SELECT NULL, host, user, 2019, "фантастика"
124 FROM mysql.user; 
125
126SELECT * FROM book;
127
128LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/data.csv'
129    INTO TABLE book
130    FIELDS 
131        TERMINATED BY ';'
132    LINES
133        TERMINATED BY '\r\n';
134
135SELECT * FROM book;
136
137DELETE FROM book;
138
139DELETE FROM book
140WHERE idbook = 3;
141SELECT * FROM book;
142
143--удалит первую запись
144DELETE FROM book
145LIMIT 1;
146
147--удалит последнюю запись
148DELETE FROM book
149ORDER BY idbook DESC
150LIMIT 1;
151SELECT * FROM book;
152
153DELETE FROM book
154WHERE idbook BETWEEN 8 and 10;
155
156UPDATE book
157 SET author = 'Автор4';
158 
159UPDATE book
160 SET author = 'Автор4'
161 WHERE idbook = 4;
162SELECT * FROM book;
163
164UPDATE book
165 SET pubYear = pubYear + 1
166 WHERE 
167  idbook < 4 OR
168  idbook > 5;
169SELECT * FROM book;
170
171UPDATE book
172 SET title = CONCAT(title,'*');
173SELECT * FROM book;
174 
175SELECT 23 * 56; 
176SELECT * FROM book;
177SELECT * FROM book\G
178SELECT title,genre FROM book;
179SELECT DISTINCT genre FROM book;
180
181SELECT title
182FROM book
183ORDER BY title; 
184  
185SELECT title
186FROM book
187ORDER BY title DESC; 
188  
189SELECT title
190FROM book
191ORDER BY RAND(); 
192 
193UPDATE book
194 SET pubYear = 2021
195WHERE idbook = 8; 
196 
197SELECT title,pubYear
198FROM book
199ORDER BY title; 
200
201SELECT title,pubYear
202FROM book
203ORDER BY title,pubYear ; 
204 
205SELECT title,pubYear
206FROM book
207ORDER BY 1 DESC,2; 
208 
209
210 
211SELECT title,pubYear
212FROM book
213LIMIT 5; 
214
215SELECT title,pubYear
216FROM book
217LIMIT 0,5;
218 
219--коммент 
220SELECT title,pubYear
221FROM book
222LIMIT 5,5;
223 
224SELECT title,genre FROM book WHERE genre = 'классика'; 
225SELECT title,author FROM book WHERE author IS NULL; 
226
227SELECT pubYear,title FROM book 
228WHERE pubYear BETWEEN 2019 AND 2021; 
229 
230SELECT title,author FROM book WHERE author LIKE 'Автор%';  
231SELECT title,author FROM book WHERE author LIKE 'Автор_';  
232 
233SELECT pubYear,title FROM book WHERE pubYear IN(2018,2020); 
234 
235 
236SELECT AVG(population)
237FROM world.city; 
238  
239SELECT AVG(lifeexpectancy)
240FROM world.country; 
241 
242SELECT count(*) FROM book;  
243SELECT count(author) FROM book;  
244SELECT count(pubYear) FROM book;  
245SELECT count(idbook) FROM book;    
246SELECT count(genre) FROM book;  
247SELECT count(DISTINCT genre) FROM book;  
248
249SELECT min(pubYear) FROM book;  
250SELECT max(surfacearea),name FROM world.country;  
251 
252SELECT  name,surfacearea 
253FROM world.country
254ORDER BY surfacearea DESC
255LIMIT 1;
256 
257SELECT SUM(population) FROM world.country; 
258 
259SELECT GROUP_CONCAT(DISTINCT genre) 
260FROM book; 
261 
262
263
264SELECT pubYear 
265FROM book
266GROUP BY pubYear; 
267 
268SELECT pubYear, count(*)
269FROM book
270GROUP BY pubYear; 
271 
272SELECT title, pubYear, count(*)
273FROM book
274GROUP BY title, pubYear; 
275 
276SELECT continent, region, count(*) 
277FROM world.country
278GROUP BY 1,2;
279 
280SELECT continent, region, count(*) 
281FROM world.country
282GROUP BY 1,2 WITH ROLLUP; 
283 
284SELECT continent, region, count(*) 
285FROM world.country
286GROUP BY 1,2
287HAVING count(*) > 10; 
288 
289USE world; 
290SHOW TABLES;
291DESC city;
292DESC country;
293
294SELECT city.name, country.name 
295FROM country, city
296WHERE 
297 id = capital AND 
298 country.name='Russian Federation';
299
300SELECT ci.name, co.name 
301FROM country co, city ci
302WHERE 
303 id = capital AND 
304 co.name='Russian Federation';
305
306SELECT ci.name, co.name 
307FROM country co INNER JOIN city ci
308ON id = capital 
309WHERE 
310 co.name='Russian Federation';
311
312
313SELECT ci.name, co.name 
314FROM country co LEFT OUTER JOIN city ci
315ON id = capital 
316WHERE co.name LIKE 'An%';
317 
318
319CREATE TABLE tb1 (
320 idtb1 INT PRIMARY KEY AUTO_INCREMENT,
321 txt1  VARCHAR(10)
322) 
323CREATE TABLE tb2 (
324 idtb2 INT PRIMARY KEY AUTO_INCREMENT,
325 tb1_idtb1 INT,
326 txt2  VARCHAR(10),
327 FOREIGN KEY (tb1_idtb1) REFERENCES tb1(idtb1)
328) 
329INSERT INTO tb1(txt1) VALUES 
330('aaa'),
331('bbb'),
332('ccc');
333 
334INSERT INTO tb2(txt2,tb1_idtb1) VALUES 
335('aaa2',1),
336('bbb2',2);
337 
338 
339 
340 
341CREATE TABLE tb1 (
342 idtb1 INT PRIMARY KEY AUTO_INCREMENT,
343 txt1  VARCHAR(10)
344); 
345CREATE TABLE tb2 (
346 idtb2 INT PRIMARY KEY AUTO_INCREMENT,
347 tb1_idtb1 INT,
348 txt2  VARCHAR(10)
349);
350INSERT INTO tb1(txt1) VALUES ('aaa'),('bbb'),('ccc'); 
351INSERT INTO tb2(txt2,tb1_idtb1) VALUES ('aaa2',1),('bbb2',2),('ddd2',4);
352
353SELECT * FROM tb1; SELECT * FROM tb2;
354 
355SELECT *  FROM tb1 RIGHT JOIN tb2 ON idtb1 = tb1_idtb1;
356  
357SELECT *  FROM tb1 LEFT JOIN tb2 ON idtb1 = tb1_idtb1; 
358 
359SELECT *  FROM tb1 INNER JOIN tb2;  
360 
361SELECT *  FROM tb1 CROSS JOIN tb2 ON idtb1 = tb1_idtb1;
362 
363SELECT city.name 
364INTO OUTFILE 'C:/Users/Public/city.csv'  
365FROM world.city;
366 
367 
368SELECT city.name 
369INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/city.csv'  
370FROM world.city;
371  
372CREATE TEMPORARY TABLE tmp1 
373AS SELECT city.name 
374FROM world.city;
375  
376SELECT count(*) FROM tmp1; 
377
378\r 
379SELECT count(*) FROM tmp1; 
380 
381 
382SHOW VARIABLES LIKE '%event_scheduler%';
383 
384SET GLOBAL event_scheduler = ON;
385SET @@global.event_scheduler = ON;
386SET GLOBAL event_scheduler = 1;
387SET @@global.event_scheduler = 1;
388
389SELECT name, population FROM city WHERE name = 'Moscow';
390
391CREATE EVENT myevent
392    ON SCHEDULE EVERY 5 SECOND
393    DO
394      UPDATE city SET population = population * 1.01;
395 
396 ALTER EVENT myevent DISABLE;
397 ALTER EVENT myevent ENABLE;
398 
399ALTER EVENT myevent
400    DO
401      UPDATE city SET population = population * 2; 
402
403
404SELECT title,author
405FROM mydb.book
406UNION
407SELECT host,user
408FROM mysql.user;	  
409	  
410INSERT INTO book
411VALUE (NULL, "Книга11*",
412 "Автор21", 2020, "фантастика");
413	  
414SELECT title,author
415FROM mydb.book
416UNION DISTINCT
417SELECT host,user
418FROM mysql.user;	
419	  
420SELECT title,author
421FROM mydb.book
422UNION ALL
423SELECT host,user
424FROM mysql.user;	  
425	  	  	 
426			 
427SELECT title,author,genre
428FROM mydb.book
429UNION ALL
430SELECT host,user,'наука'
431FROM mysql.user;	  
432	  	  
433SELECT title,author,genre
434FROM mydb.book
435UNION ALL
436SELECT host,user,'наука'
437FROM mysql.user
438ORDER BY 2 LIMIT 5;	  
439	  	  	  	  	  
440(SELECT title,author,genre
441FROM mydb.book LIMIT 5)
442UNION ALL
443(SELECT host,user,'наука'
444FROM mysql.user LIMIT 5);	  
445	  	  	  
446--Сделайте выборку используя оператор SELECT с UNION: выбрать все названия стран и названия языков.	  
447(SELECT name FROM world.country)
448UNION ALL
449(SELECT language 
450FROM world.countrylanguage);	
451	  
452SELECT language,percentage
453FROM 
454world.country INNER JOIN world.countrylanguage
455ON country.code = countrylanguage.countrycode
456WHERE name = 'Russian Federation'
457ORDER BY 2 DESC; 	  
458
459	  
460SELECT title 
461FROM book
462WHERE pubYear = (
463  SELECT max(pubYear)
464  FROM book
465);	  
466	  	  
467SELECT title 
468FROM book
469WHERE pubYear = (
470  SELECT pubYear
471  FROM book
472  LIMIT 1
473);	  
474	  
475SELECT title 
476FROM book
477WHERE pubYear IN (2019, 2021);	  
478	  	  
479SELECT title 
480FROM book
481WHERE pubYear IN (
482  SELECT pubYear
483  FROM book
484  WHERE pubYear % 2
485);	  
486	
487	
488SELECT * 
489FROM tb1 LEFT JOIN tb2
490ON idtb1 = tb1_idtb1;  
491	  
492--Выведите список стран в которых есть города с населением более или равным 1 млн. чел	  
493
494USE world;
495SELECT name FROM world.country
496WHERE EXISTS (
497 SELECT name FROM world.city
498 WHERE population >= 1000000
499 AND countrycode = code
500);  
501
502SELECT name FROM world.country
503WHERE NOT EXISTS (
504 SELECT name FROM world.city
505 WHERE population >= 1000000
506 AND countrycode = code
507);  
508	  
509SELECT max(population)
510FROM city;	  
511	  
512SELECT name, count(*)
513FROM world.city
514GROUP BY 1
515HAVING count(*) > 1;
516
517SELECT name FROM city as c1
518WHERE EXISTS (
519 SELECT name 
520 FROM city as c2
521 WHERE c1.name = c2.name AND
522 c1.id <> c2.id
523);
524
525SELECT name FROM country 
526WHERE code IN (SELECT countrycode FROM city WHERE population >= 1000000)
527
528
529
530SELECT * FROM book
531WHERE genre = 'классика';
532
533CREATE VIEW fantbook
534 AS SELECT * FROM book
535	WHERE genre = 'фантастика';
536
537SHOW TABLES;
538
539SELECT * FROM fantbook;
540SHOW CREATE TABLE fantbook;
541
542--vEurope
543--vAsia
544--vAfrica
545
546USE world;
547CREATE VIEW vEurope
548 AS SELECT * FROM country
549	WHERE continent = 'Europe';
550CREATE VIEW vAsia
551 AS SELECT * FROM country
552	WHERE continent = 'Asia';
553CREATE VIEW vAfrica
554 AS SELECT * FROM country
555	WHERE continent = 'Africa';
556
557SHOW TABLES;
558
559vw_Asia
560
561SELECT name FROM veurope;
562
563USE world;
564
565DROP PROCEDURE IF EXISTS sp_proc1;	  
566delimiter |
567CREATE PROCEDURE sp_proc1()
568BEGIN 
569 SELECT 'Привет, мир!';
570END|
571delimiter ;
572CALL sp_proc1();	
573
574
575
576SET @name = "test";
577SELECT @name;
578
579SELECT max(population) INTO @p
580FROM city;
581
582
583
584DROP PROCEDURE IF EXISTS sp_proc2;	  
585delimiter |
586CREATE PROCEDURE sp_proc2(IN c CHAR(3), OUT n VARCHAR(52))
587BEGIN 
588 SELECT name INTO n 
589 FROM country
590 WHERE code = c;
591END|
592delimiter ;
593
594CALL sp_proc2('RUS',@name);	  
595SELECT @name;  
596	  
597SELECT routine_name,routine_definition	  
598FROM information_schema.routines	  
599WHERE routine_schema = 'world';
600	  
601SHOW PROCEDURE CODE sp_proc1;	  
602	  
603-- Написать процедуру get_count_cities(), которая по коду страны выведет количество городов этой страны	  
604	
605DROP PROCEDURE IF EXISTS get_count_cities;	  
606delimiter |
607CREATE PROCEDURE get_count_cities(IN c CHAR(3))
608BEGIN 
609 SELECT count(*) 'Кол-во городов'
610 FROM city
611 WHERE countrycode = c;
612END|
613delimiter ;
614
615CALL get_count_cities('RUS');	   
616CALL get_count_cities('CHN');	   
617	  
618	  
619	  
620	
621DROP PROCEDURE IF EXISTS sp_proc3;	  
622delimiter |
623CREATE PROCEDURE sp_proc3()
624BEGIN 
625 DECLARE a,b,c INT DEFAULT 0;
626 SET a = 100;
627 SELECT a;
628END|
629delimiter ;  
630	  
631CALL sp_proc3();	  
632	  
633--написать процедуру, которая будет принимать диапазоны и определять страны как: 'большие','средние' и 'малые'	  
634
635CALL sortBySize(1e7,5e7);
636	  
637большие 34
638средние 100
639малые ...	  
640	  
641DROP PROCEDURE IF EXISTS sortBySize;	  
642delimiter |
643CREATE PROCEDURE sortBySize(d1 INT, d2 INT)
644BEGIN 
645  DECLARE flag  TINYINT DEFAULT FALSE;
646  DECLARE popul INT;
647  DECLARE mycur CURSOR FOR SELECT population FROM country;
648  DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
649  
650  DROP TEMPORARY TABLE IF EXISTS tmp;
651  CREATE TEMPORARY TABLE tmp (
652    p ENUM('большие','средние','малые')
653  );
654 
655 OPEN mycur;    
656  read_loop: LOOP
657    FETCH mycur INTO popul; 
658    IF flag THEN 
659	  LEAVE read_loop;
660    END IF;
661	IF popul <= d1 THEN
662	  INSERT INTO tmp VALUE('малые');
663	ELSE IF popul >= d2 THEN
664			INSERT INTO tmp VALUE('большие');
665	     ELSE
666			INSERT INTO tmp VALUE('средние');
667		 END IF;
668	END IF;
669  END LOOP;
670  CLOSE mycur; 
671
672  SELECT p , count(*) 
673  FROM tmp
674  GROUP BY 1;
675END|
676delimiter ;    
677	  
678CALL sortBySize(1e7,5e7);	  
679	  
680DROP FUNCTION IF EXISTS fn1;	  
681delimiter |
682CREATE FUNCTION fn1(co CHAR(3))
683RETURNS INT DETERMINISTIC
684BEGIN
685 DECLARE t INT DEFAULT 0;
686 SELECT surfacearea INTO t
687 FROM country
688 WHERE code = co;
689 RETURN t;
690END|	  
691delimiter ;
692SELECT fn1('RUS'); 
693SELECT fn1('CHN'); 
694
695--по коду страны вернуть её главу (headofstate)	getCountryHead()	
696
697DROP FUNCTION IF EXISTS getCountryHead;	  
698delimiter |
699CREATE FUNCTION getCountryHead(co CHAR(3))
700RETURNS VARCHAR(60) DETERMINISTIC
701BEGIN
702 DECLARE t VARCHAR(60) DEFAULT '';
703 SELECT headofstate INTO t
704 FROM country
705 WHERE code = co;
706 RETURN t;
707END|	  
708delimiter ;
709SELECT getCountryHead('RUS'); 
710SELECT getCountryHead('CHN'); 
711SELECT getCountryHead('USA'); 
712
713
714SELECT IF(CHAR_LENGTH(title)>5,'большая','маленькая')
715FROM mydb.book;
716
717
718SELECT IF(population < 1e7,'маленькая',
719 IF(population >= 5e7,'большая','средняя')
720), count(*)
721FROM world.country
722GROUP BY 1;
723
724--создать триггер, который при обновлении названия страны изменит названия городов этой страны
725
726delimiter $$
727DROP TRIGGER IF EXISTS tg_after_update_country;
728CREATE TRIGGER tg_after_update_country
729AFTER UPDATE ON country
730FOR EACH ROW BEGIN
731 IF NEW.name <> OLD.name THEN
732   UPDATE city 
733   SET name = CONCAT(name, '*')
734   WHERE countrycode = OLD.code;
735 END IF;
736END $$
737delimiter ;
738
739UPDATE country
740SET name = CONCAT(name,"*")
741ORDER BY RAND()
742LIMIT 1;
743
744SELECT name, code 
745FROM country
746WHERE RIGHT(name,1) = '*';
747
748SELECT name FROM city 
749WHERE countrycode = 'SVK';
750
751
752USE mydb;
753
754--написать триггер, который будет вести лог добавлению новых книг в book
755delimiter $$
756DROP TRIGGER IF EXISTS tg_after_insert_book;
757CREATE TRIGGER tg_after_insert_book
758AFTER INSERT ON book
759FOR EACH ROW BEGIN
760  INSERT INTO log VALUES(NULL, user(),NOW(),NEW.title);
761END $$
762delimiter ;
763
764
765CREATE TABLE log(
766  idlog INT PRIMARY KEY AUTO_INCREMENT,
767  name  VARCHAR(50),
768  `time` DATETIME DEFAULT CURRENT_TIMESTAMP,
769  title VARCHAR(100)
770);
771
772INSERT INTO book VALUE(NULL, 'Книга 100', 'Петя', 2025, 'фантастика');
773SELECT * FROM book ORDER BY idbook DESC LIMIT 1;
774SELECT * FROM log;
775
776
777
778
779LOCK TABLES 
780book WRITE,
781customer READ;
782
783SELECT * FROM book;
784SELECT * FROM customer;
785INSERT INTO customer (firstname, phone) VALUES ('Катя','1111111111');
786
787UPDATE book
788SET pubYear = pubYear-1;
789
790UNLOCK TABLES;
791
792SELECT GET_LOCK('молоко',10);
793SELECT RELEASE_LOCK('молоко');
794
795SELECT idbook,GET_LOCK(idbook,10)
796FROM book WHERE idbook = 21;
797SELECT idbook,RELEASE_LOCK(idbook)
798FROM book WHERE idbook = 21;
799
800CREATE TABLE articles (
801  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
802  title VARCHAR(200),
803  body TEXT,
804  FULLTEXT (title,body)
805) ENGINE=InnoDB;
806
807INSERT INTO articles (title,body) VALUES
808        ('MySQL Tutorial','DBMS stands for DataBase ...'),
809        ('How To Use MySQL Well','After you went through a ...'),
810        ('Optimizing MySQL','In this tutorial we will show ...'),
811        ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
812        ('MySQL vs. YourSQL','In the following database comparison ...'),
813        ('MySQL Security','When configured properly, MySQL ...');
814
815SELECT * FROM articles
816        WHERE MATCH (title,body)
817        AGAINST ('database' IN NATURAL LANGUAGE MODE);
818
819SELECT * FROM articles
820        WHERE MATCH (title,body)
821        AGAINST ('Security' IN NATURAL LANGUAGE MODE);
822
823SELECT * FROM articles WHERE MATCH (title,body)
824    AGAINST ('+MySQL -Security' IN BOOLEAN MODE);
825
826
827SELECT id, title, body, MATCH (title,body)  AGAINST ('database' IN BOOLEAN MODE)
828AS score FROM articles ORDER BY score DESC;
829
830
831SELECT id, body, MATCH (title,body) AGAINST
832    ('Security implications of running MySQL as root'
833    IN NATURAL LANGUAGE MODE) AS score
834    FROM articles WHERE MATCH (title,body) AGAINST
835    ('Security implications of running MySQL as root'
836    IN NATURAL LANGUAGE MODE);
837
838
839	
840JavaScript
841PHP
842MySQL
843HTML	
844	
845innodb_ft_min_token_size = 2