jsCmWAtz

1CREATE DATABASE IF NOT EXISTS books;
2SHOW DATABASES;
3DROP DATABASE IF EXISTS books;
4SHOW DATABASES;
5
6CREATE TABLE tbooks (
7  idtbooks INT(11) PRIMARY KEY AUTO_INCREMENT,
8  title    VARCHAR(255) NOT NULL DEFAULT '',
9  genre    SET('классика','детектив','ужас'),
10  pages    SMALLINT DEFAULT 5 COMMENT 'кол-во страниц'
11);
12  
13CREATE TABLE users
14  AS SELECT user,host 
15  FROM mysql.user;
16
17CREATE TEMPORARY TABLE country
18  LIKE world.country;
19  
20SELECT name FROM country;
21SHOW CREATE TABLE country;
22
23
24DROP TABLE 
25    tbooks, users;
26    
27
28ALTER TABLE tbooks
29  ADD COLUMN  author VARCHAR(100) AFTER title,
30  CHANGE pages pubYear YEAR DEFAULT '2018',
31  DROP genre;
32DESC tbooks;  
33
34CREATE TABLE t1 (
35  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
36  dt DATETIME DEFAULT CURRENT_TIMESTAMP
37);
38
39SELECT 
40 TABLE_NAME,
41 ENGINE,
42 TABLE_ROWS,
43 AUTO_INCREMENT,
44 CREATE_TIME,
45 TABLE_COMMENT
46FROM information_schema.TABLES
47WHERE 
48  TABLE_SCHEMA = 'world'; 
49
50CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
51    [index_type]
52    ON tbl_name (index_col_name,...)
53    [index_option]
54    [algorithm_option | lock_option] ...
55
56index_col_name:
57    col_name [(length)] [ASC | DESC]
58
59index_option:
60    KEY_BLOCK_SIZE [=] value
61  | index_type
62  | WITH PARSER parser_name
63  | COMMENT 'string'
64
65index_type:
66    USING {BTREE | HASH}
67
68algorithm_option:
69    ALGORITHM [=] {DEFAULT|INPLACE|COPY}
70
71lock_option:
72    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
73
74
751  12
762  1
773  100
784  40
795  87
806  32
817  13
828  90	
83	
846	
85	
862  1	
871  12
887  13
896  32
904  40
915  87
928  90
933  100
94
953
96
97CREATE TABLE tbooks (
98  idtbooks INT(11) PRIMARY KEY AUTO_INCREMENT,
99  title    VARCHAR(255) NOT NULL DEFAULT '',
100  genre    SET('классика','детектив','ужас'),
101  pages    SMALLINT DEFAULT 5 COMMENT 'кол-во страниц'
102);
103ALTER TABLE tbooks
104  ADD COLUMN  author VARCHAR(100) AFTER title,
105  CHANGE pages pubYear YEAR DEFAULT '2018',
106  DROP genre;
107DESC tbooks;  
108
109CREATE INDEX ixtitle
110  ON tbooks(title,author);	
111
112ALTER TABLE tbooks 
113  ADD INDEX (pubYear);  
114	
115SHOW CREATE TABLE tbooks;	
116
117CREATE TABLE `tbooks` (
118  `idtbooks` int(11) NOT NULL AUTO_INCREMENT,
119  `title` varchar(255) NOT NULL DEFAULT '',
120  `author` varchar(100) DEFAULT NULL,
121  `pubYear` year(4) DEFAULT '2018',
122  PRIMARY KEY (`idtbooks`),
123  KEY `ixtitle` (`title`,`author`),
124  KEY `pubYear` (`pubYear`)
125) ENGINE=InnoDB DEFAULT CHARSET=utf8 
126	
127DROP INDEX pubYear on tbooks;	
128	
129
130
131CREATE TABLE parent (
132	id INT NOT NULL,
133	PRIMARY KEY (id)
134) ENGINE=INNODB;
135
136CREATE TABLE child (
137	id INT,
138	parent_id INT,
139	INDEX par_ind (parent_id),
140	FOREIGN KEY (parent_id) REFERENCES parent(id)
141	ON DELETE CASCADE
142) ENGINE=INNODB;
143
144INSERT INTO tbooks VALUES (4, 'MySQL','Поль',2006);
145INSERT INTO tbooks VALUES (NULL, 'MySQL','Поль',2009);
146INSERT INTO tbooks (title,author) VALUE ('JS','Цзы');
147INSERT INTO tbooks (title,author) VALUES
148   ('PHP','qwe'),
149   ('HTML','wewe'),
150   ('HTML5','wewe');
151   
152INSERT INTO tbooks
153 SET title = 'JS', author = 'Цзы';   
154   
155SELECT * FROM tbooks;
156   
157   
158
159INSERT tbooks
160    SELECT NULL,name,headofstate,2018
161	FROM world.country
162	LIMIT 10;
163    
164
165LOAD DATA LOCAL INFILE 'C:/Users/Public/data.csv'
166    INTO TABLE tbooks
167    FIELDS 
168        TERMINATED BY ';'
169    LINES
170        TERMINATED BY '\r\n'
171    IGNORE 1 LINES;
172	
173	
174DELETE FROM tbooks WHERE author = 'wewe';
175DELETE FROM tbooks WHERE idtbooks = 16;
176DELETE FROM tbooks 
177    ORDER BY idtbooks DESC
178    LIMIT 1;
179DELETE FROM tbooks WHERE idtbooks BETWEEN 11 AND 20;	
180SELECT * FROM tbooks;	
181	
182	
183UPDATE tbooks
184    SET 
185	 title = CONCAT(title,'*'),
186	 pubYear = pubYear + 1
187    WHERE idtbooks > 10
188    LIMIT 5;
189
190	
191SELECT 2e6 * .01 /(1+pow(1-.01,-120));
192SELECT * FROM tbooks;
193SELECT idtbooks,title FROM tbooks;
194SELECT DISTINCT pubYear FROM tbooks;
195SELECT pubYear + 10 FROM tbooks;
196
197SELECT * FROM tbooks ORDER BY pubYear; 
198SELECT * FROM tbooks ORDER BY pubYear ASC; 
199SELECT * FROM tbooks ORDER BY pubYear DESC; 
200SELECT * FROM tbooks ORDER BY pubYear DESC,author; 
201SELECT * FROM tbooks ORDER BY rand(); 
202SELECT * FROM tbooks ORDER BY 4 DESC,3; 
203
204SELECT * FROM tbooks LIMIT 2;
205SELECT * FROM tbooks LIMIT 2,5;
206 
207 UPDATE tbooks
208 SET author = NULL
209 WHERE idtbooks = 27;
210 
211 
212SELECT * FROM tbooks WHERE title <> 'JS';
213SELECT * FROM tbooks WHERE author IS NULL;
214SELECT * FROM tbooks WHERE pubYear BETWEEN 2010 AND 2018;
215SELECT * FROM tbooks WHERE title LIKE 'К%';
216SELECT * FROM tbooks WHERE title NOT LIKE 'К%';
217SELECT * FROM tbooks WHERE title IN ('MySQL','JS');
218SELECT * FROM tbooks WHERE title NOT IN ('HTML за 24*');
219 
220 
221Георгий Иванов  25 
222Георгий Сидоров 27 
223Георгий Яшин    23 
224
225Георгий Яшин    23 
226Георгий Иванов  25 
227Георгий Сидоров 27 
228 
229 SELECT DISTINCT author,pubYear
230 FROM tbooks
231 ORDER BY pubYear;
232 
233AVG() среднее 
234COUNT()/COUNT(DISTINCT) кол-во строк
235GROUP_CONCAT() групповая конкатенация
236MIN()/MAX() нахождение минимального/максимального
237SUM() сумма
238
239SELECT AVG(lifeexpectancy) FROM world.country;
240SELECT COUNT(author) FROM tbooks;
241SELECT COUNT(*) FROM tbooks;
242SELECT COUNT(DISTINCT author) FROM tbooks;
243SELECT MAX(pubYear) FROM tbooks;
244SELECT MIN(pubYear) FROM tbooks;
245SELECT SUM(population) FROM world.country;
246SELECT GROUP_CONCAT(title) FROM tbooks WHERE idtbooks < 10;
247
248SELECT MAX(surfacearea) FROM world.country;
249
250SELECT pubYear FROM tbooks GROUP BY pubYear;
251SELECT pubYear FROM tbooks GROUP BY 1;
252SELECT pubYear,count(*) FROM tbooks GROUP BY pubYear;
253SELECT author,count(*) as штук FROM tbooks GROUP BY author;
254SELECT author,count(*) FROM tbooks GROUP BY author HAVING count(*)>1;
255SELECT author,count(*) FROM tbooks GROUP BY author ORDER BY 2;
256
257SELECT continent, count(*)
258FROM world.country
259GROUP BY continent;
260
261SELECT continent,region, count(*)
262FROM world.country
263GROUP BY continent,region;
264
265SELECT continent,region, count(*)
266FROM world.country
267GROUP BY continent,region
268WITH ROLLUP;
269
270
271SHOW CREATE TABLE tbooks;
272
273DROP TABLE torders;
274CREATE TABLE `tbooks` (
275  `idtbooks` int(11) NOT NULL AUTO_INCREMENT,
276  `title` varchar(255) NOT NULL DEFAULT '',
277  `author` varchar(100) DEFAULT NULL,
278  `pubYear` year(4) DEFAULT '2018',
279  PRIMARY KEY (`idtbooks`),
280  KEY `ixtitle` (`title`,`author`)
281) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;
282
283DROP TABLE torders;
284CREATE TABLE torders (
285	idtorders INT PRIMARY KEY  AUTO_INCREMENT,
286	tbooks_idtbooks INT,
287	INDEX tbooks_ind (tbooks_idtbooks),
288	FOREIGN KEY (tbooks_idtbooks) REFERENCES tbooks(idtbooks)
289	ON DELETE CASCADE
290) ENGINE=INNODB;
291INSERT torders VALUES
292 (3, 6),
293 (4, 26),
294 (5, 29),
295 (6, 30),
296 (7, 31),
297 (NULL, 4);
298
299SELECT idtorders,title,author
300 FROM torders INNER JOIN tbooks
301 on idtbooks = tbooks_idtbooks
302ORDER BY 1;
303
304SELECT idtorders,title,author
305 FROM torders CROSS JOIN tbooks
306  on idtbooks = tbooks_idtbooks;
307 
308SELECT idtbooks,title,author,idtorders
309 FROM  tbooks LEFT OUTER JOIN torders
310 on idtbooks = tbooks_idtbooks
311ORDER BY 1;
312  
313SELECT idtbooks,title,author,idtorders
314 FROM  tbooks RIGHT OUTER JOIN torders
315 on idtbooks = tbooks_idtbooks
316ORDER BY 1;
317 
318 USE world;
319 SELECT city.name
320 FROM country INNER JOIN city
321 ON countrycode = code
322 WHERE country.name = 'Russian Federation';
323 
324 SELECT ci.name
325 FROM country co INNER JOIN city ci
326 ON countrycode = code
327 WHERE co.name = 'Russian Federation';