· 6 years ago · Dec 29, 2019, 02:06 PM
1mysql> SELECT 45 || 10;
2+----------+
3| 45 || 10 |
4+----------+
5| 1 |
6+----------+
71 row in set, 1 warning (0.02 sec)
8
9mysql> USE world;
10Database changed
11mysql>
12mysql> LOCK TABLES
13 -> country WRITE
14 -> city READ;
15ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'city READ' at line 3
16mysql> LOCK TABLES
17 -> country WRITE,
18 -> city READ;
19Query OK, 0 rows affected (0.06 sec)
20
21mysql> SELECT language
22 -> FROM countrylanguage;
23ERROR 1100 (HY000): Table 'countrylanguage' was not locked with LOCK TABLES
24mysql> SELECT name FROM city LIMIT 1;
25+-------+
26| name |
27+-------+
28| Kabul |
29+-------+
301 row in set (0.03 sec)
31
32mysql> SELECT name FROM country LIMIT 1;
33+-------+
34| name |
35+-------+
36| Aruba |
37+-------+
381 row in set (0.02 sec)
39
40mysql> UPDATE city SET name = concat(name, '*');
41ERROR 1099 (HY000): Table 'city' was locked with a READ lock and can't be updated
42mysql> UPDATE country SET name = concat(name, '*');
43Query OK, 239 rows affected (0.13 sec)
44Rows matched: 239 Changed: 239 Warnings: 0
45
46mysql> SELECT name FROM country LIMIT 1;
47+--------+
48| name |
49+--------+
50| Aruba* |
51+--------+
521 row in set (0.00 sec)
53
54mysql> UPDATE country SET name = replace(name, '*', '');
55Query OK, 239 rows affected (0.07 sec)
56Rows matched: 239 Changed: 239 Warnings: 0
57
58mysql> SELECT name FROM country LIMIT 1;
59+-------+
60| name |
61+-------+
62| Aruba |
63+-------+
641 row in set (0.00 sec)
65
66mysql> UNLOCK TABLES;
67Query OK, 0 rows affected (0.00 sec)
68
69mysql> SELECT
70 -> GET_LOCK('hello, world!', 5);
71+------------------------------+
72| GET_LOCK('hello, world!', 5) |
73+------------------------------+
74| 0 |
75+------------------------------+
761 row in set (5.00 sec)
77
78mysql> SELECT
79 -> GET_LOCK('hello, world!', 5);
80+------------------------------+
81| GET_LOCK('hello, world!', 5) |
82+------------------------------+
83| 1 |
84+------------------------------+
851 row in set (3.06 sec)
86
87mysql> SELECT
88 -> GET_LOCK(id, 5)
89 -> FROM city
90 -> WHERE id=234;
91+-----------------+
92| GET_LOCK(id, 5) |
93+-----------------+
94| 0 |
95+-----------------+
961 row in set (5.00 sec)
97
98mysql> SELECT
99 -> GET_LOCK(id, 5)
100 -> FROM city
101 -> WHERE id=234;
102+-----------------+
103| GET_LOCK(id, 5) |
104+-----------------+
105| 1 |
106+-----------------+
1071 row in set (2.35 sec)
108
109mysql> SELECT
110 -> IS_FREE_LOCK(id)
111 -> FROM city
112 -> WHERE id=234;
113+------------------+
114| IS_FREE_LOCK(id) |
115+------------------+
116| 0 |
117+------------------+
1181 row in set (0.02 sec)
119
120mysql> SHOW VARIABLES LIKE '%tx_isolation%';
121Empty set, 1 warning (0.00 sec)
122
123mysql> show variables like '%transaction_isolation%';
124+-----------------------+-----------------+
125| Variable_name | Value |
126+-----------------------+-----------------+
127| transaction_isolation | REPEATABLE-READ |
128+-----------------------+-----------------+
1291 row in set, 1 warning (0.00 sec)
130
131mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
132Query OK, 0 rows affected (0.00 sec)
133
134mysql> show variables like '%transaction_isolation%';
135+-----------------------+------------------+
136| Variable_name | Value |
137+-----------------------+------------------+
138| transaction_isolation | READ-UNCOMMITTED |
139+-----------------------+------------------+
1401 row in set, 1 warning (0.00 sec)
141
142mysql> START TRANSACTION;
143Query OK, 0 rows affected (0.00 sec)
144
145mysql> SELECT name FROM city LIMIT 1;
146+--------+
147| name |
148+--------+
149| Kabul2 |
150+--------+
1511 row in set (0.00 sec)
152
153mysql> SELECT name FROM city LIMIT 1;
154+-------+
155| name |
156+-------+
157| Kabul |
158+-------+
1591 row in set (0.00 sec)
160
161mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
162Query OK, 0 rows affected (0.00 sec)
163
164mysql> show variables like '%transaction_isolation%';
165+-----------------------+----------------+
166| Variable_name | Value |
167+-----------------------+----------------+
168| transaction_isolation | READ-COMMITTED |
169+-----------------------+----------------+
1701 row in set, 1 warning (0.00 sec)
171
172mysql> START TRANSACTION;
173Query OK, 0 rows affected (0.00 sec)
174
175mysql> SELECT name FROM city LIMIT 1;
176+--------+
177| name |
178+--------+
179| Kabul2 |
180+--------+
1811 row in set (0.00 sec)
182
183mysql> ROLLBACK;
184Query OK, 0 rows affected (0.00 sec)
185
186mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
187Query OK, 0 rows affected (0.00 sec)
188
189mysql> show variables like '%transaction_isolation%';
190+-----------------------+----------------+
191| Variable_name | Value |
192+-----------------------+----------------+
193| transaction_isolation | READ-COMMITTED |
194+-----------------------+----------------+
1951 row in set, 1 warning (0.00 sec)
196
197mysql> START TRANSACTION;
198Query OK, 0 rows affected (0.00 sec)
199
200mysql> SELECT name FROM city LIMIT 1;
201+-------+
202| name |
203+-------+
204| Kabul |
205+-------+
2061 row in set (0.00 sec)
207
208mysql> SELECT name FROM city LIMIT 1;
209+--------+
210| name |
211+--------+
212| Kabul2 |
213+--------+
2141 row in set (0.00 sec)
215
216mysql> ROLLBACK;
217Query OK, 0 rows affected (0.00 sec)
218
219mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
220Query OK, 0 rows affected (0.00 sec)
221
222mysql> show variables like '%transaction_isolation%';
223+-----------------------+-----------------+
224| Variable_name | Value |
225+-----------------------+-----------------+
226| transaction_isolation | REPEATABLE-READ |
227+-----------------------+-----------------+
2281 row in set, 1 warning (0.00 sec)
229
230mysql> START TRANSACTION;
231Query OK, 0 rows affected (0.00 sec)
232
233mysql> SELECT name FROM city LIMIT 1;
234+--------+
235| name |
236+--------+
237| Kabul2 |
238+--------+
2391 row in set (0.00 sec)
240
241mysql> SELECT name FROM city LIMIT 1;
242+--------+
243| name |
244+--------+
245| Kabul2 |
246+--------+
2471 row in set (0.00 sec)
248
249mysql> SELECT name FROM city LIMIT 1;
250+--------+
251| name |
252+--------+
253| Kabul2 |
254+--------+
2551 row in set (0.00 sec)
256
257mysql> ROLLBACK
258 -> ;
259Query OK, 0 rows affected (0.00 sec)
260
261mysql> SHOW CREATE TABLE city;
262+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
263| Table | Create Table |
264+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
265| city | CREATE TABLE `city` (
266 `ID` int(11) NOT NULL AUTO_INCREMENT,
267 `Name` char(35) NOT NULL DEFAULT '',
268 `CountryCode` char(3) NOT NULL DEFAULT '',
269 `District` char(20) NOT NULL DEFAULT '',
270 `Population` int(11) NOT NULL DEFAULT '0',
271 PRIMARY KEY (`ID`),
272 KEY `CountryCode` (`CountryCode`),
273 CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
274) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
275+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2761 row in set (0.06 sec)
277
278mysql> SHOW WARNINGS;
279Empty set (0.00 sec)
280
281mysql> SHOW ENGINES;
282+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
283| Engine | Support | Comment | Transactions | XA | Savepoints |
284+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
285| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
286| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
287| CSV | YES | CSV storage engine | NO | NO | NO |
288| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
289| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
290| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
291| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
292| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
293| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
294+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
2959 rows in set (0.07 sec)
296
297mysql> SELECT * INTO OUTFILE 'C:/mysql/sql/city.csv'
298 -> FROM city;
299ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
300mysql> SELECT * INTO OUTFILE 'C:/mysql/uploads/city.csv'
301 -> FROM city;
302Query OK, 4079 rows affected (0.02 sec)
303
304mysql> SHOW VARIABLES LIKE '%event_scheduler%';
305+-----------------+-------+
306| Variable_name | Value |
307+-----------------+-------+
308| event_scheduler | ON |
309+-----------------+-------+
3101 row in set, 1 warning (0.00 sec)
311
312mysql> SELECT name, population FROM city WHERE name = 'Moscow';
313+--------+------------+
314| name | population |
315+--------+------------+
316| Moscow | 9228120 |
317+--------+------------+
3181 row in set (0.00 sec)
319
320mysql> CREATE EVENT myevent
321 -> ON SCHEDULE EVERY 5 SECOND
322 -> DO
323 -> UPDATE city SET population = population * 1.01;
324Query OK, 0 rows affected (0.11 sec)
325
326mysql>
327mysql> SELECT name, population FROM city WHERE name = 'Moscow';
328+--------+------------+
329| name | population |
330+--------+------------+
331| Moscow | 9413605 |
332+--------+------------+
3331 row in set (0.00 sec)
334
335mysql> SELECT name, population
336 -> FROM city
337 -> ORDER BY 2 desc
338 -> limit 10;
339+------------------+------------+
340| name | population |
341+------------------+------------+
342| Mumbai (Bombay) | 12312077 |
343| Seoul | 11704231 |
344| S?o Paulo | 11688832 |
345| Shanghai | 11369673 |
346| Jakarta | 11262500 |
347| Karachi | 10868942 |
348| Moscow | 10820696 |
349| Istanbul | 10304571 |
350| Ciudad de M?xico | 10073986 |
351| New York | 9390335 |
352+------------------+------------+
35310 rows in set (0.00 sec)
354
355mysql> ALTER EVENT myevent DISABLE;
356Query OK, 0 rows affected (0.07 sec)
357
358mysql> SELECT name, population
359 -> FROM city
360 -> ORDER BY 2 desc
361 -> limit 10;
362+------------------+------------+
363| name | population |
364+------------------+------------+
365| Mumbai (Bombay) | 14581247 |
366| Seoul | 13861372 |
367| S?o Paulo | 13843135 |
368| Shanghai | 13465151 |
369| Jakarta | 13338230 |
370| Karachi | 12872137 |
371| Moscow | 12815000 |
372| Istanbul | 12203751 |
373| Ciudad de M?xico | 11930665 |
374| New York | 11121015 |
375+------------------+------------+
37610 rows in set (0.00 sec)
377
378mysql> SELECT NOW();
379+---------------------+
380| NOW() |
381+---------------------+
382| 2019-12-29 15:06:26 |
383+---------------------+
3841 row in set (0.00 sec)
385
386mysql> SELECT REPLACE(NOW(),' ','-');
387+------------------------+
388| REPLACE(NOW(),' ','-') |
389+------------------------+
390| 2019-12-29-15:07:06 |
391+------------------------+
3921 row in set (0.02 sec)
393
394mysql> SELECT REPLACE(REPLACE(NOW(),' ','-'),':','');
395+----------------------------------------+
396| REPLACE(REPLACE(NOW(),' ','-'),':','') |
397+----------------------------------------+
398| 2019-12-29-150723 |
399+----------------------------------------+
4001 row in set (0.00 sec)
401
402mysql> SELECT REPLACE(REPLACE(NOW(),' ','-'),':','-');
403+-----------------------------------------+
404| REPLACE(REPLACE(NOW(),' ','-'),':','-') |
405+-----------------------------------------+
406| 2019-12-29-15-07-29 |
407+-----------------------------------------+
4081 row in set (0.00 sec)
409
410mysql> SELECT unix_timestamp();
411+------------------+
412| unix_timestamp() |
413+------------------+
414| 1577621281 |
415+------------------+
4161 row in set (0.00 sec)
417
418mysql> DROP EVENT IF EXISTS backupCountry;
419Query OK, 0 rows affected, 1 warning (0.03 sec)
420
421mysql> \d |
422mysql> CREATE EVENT backupCountry
423 -> ON SCHEDULE EVERY 1 MINUTE
424 -> DO BEGIN
425 -> DECLARE bCountry CHAR(23) DEFAULT '.csv';
426 -> DECLARE stmt VARCHAR(50);
427 ->
428 -> SET bCountry = CONCAT( REPLACE(REPLACE(NOW(),' ','-'),':','-') ,bCountry)
429 -> SET stmt= CONCAT('SELECT * INTO OUTFILE "C:/mysql/uploads/',bCountry,'" FROM country');
430 -> PREPARE stmt1 FROM stmt;
431 -> EXECUTE stmt1;
432 ->
433 -> END|
434ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET stmt= CONCAT('SELECT * INTO OUTFILE "C:/mysql/uploads/',bCountry,'" FROM cou' at line 8
435mysql> \d ;DROP EVENT IF EXISTS backupCountry;
436 -> \d |
437 -> CREATE EVENT backupCountry
438 -> ON SCHEDULE EVERY 1 MINUTE
439 -> DO BEGIN
440 -> DECLARE bCountry CHAR(23) DEFAULT '.csv';
441 -> DECLARE stmt VARCHAR(50);
442 ->
443 -> SET bCountry = CONCAT( REPLACE(REPLACE(NOW(),' ','-'),':','-') ,bCountry);
444 -> SET stmt= CONCAT('SELECT * INTO OUTFILE "C:/mysql/uploads/',bCountry,'" FROM country');
445 -> PREPARE stmt1 FROM stmt;
446 -> EXECUTE stmt1;
447 ->
448 -> END|
449ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EVENT IF EXISTS backupCountry;
450
451CREATE EVENT backupCountry
452ON SCHEDULE EVERY 1 M' at line 1
453mysql> \d ;
454mysql> DROP EVENT IF NOT EXISTS backupCountry;
455ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT EXISTS backupCountry' at line 1
456mysql> \d |
457mysql> CREATE EVENT backupCountry
458 -> ON SCHEDULE EVERY 1 MINUTE
459 -> DO BEGIN
460 -> DECLARE bCountry CHAR(23) DEFAULT '.csv';
461 -> DECLARE stmt VARCHAR(50);
462 ->
463 -> SET bCountry = CONCAT( REPLACE(REPLACE(NOW(),' ','-'),':','-') ,bCountry);
464 -> SET stmt= CONCAT('SELECT * INTO OUTFILE "C:/mysql/uploads/',bCountry,'" FROM country');
465 -> PREPARE stmt1 FROM stmt;
466 -> EXECUTE stmt1;
467 ->
468 -> END|
469ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'stmt;
470 EXECUTE stmt1;
471
472END' at line 9
473mysql> \d ;DROP EVENT IF NOT EXISTS backupCountry;
474 -> \d |
475 -> CREATE EVENT backupCountry
476 -> ON SCHEDULE EVERY 1 MINUTE
477 -> DO BEGIN
478 -> DECLARE bCountry CHAR(23) DEFAULT '.csv';
479 -> DECLARE stmt VARCHAR(50);
480 ->
481 -> SET bCountry = CONCAT( REPLACE(REPLACE(NOW(),' ','-'),':','-') ,bCountry);
482 -> SET stmt = CONCAT('SELECT * INTO OUTFILE "C:/mysql/uploads/',bCountry,'" FROM country');
483 -> PREPARE stmt1 FROM stmt;
484 -> EXECUTE stmt1;
485 ->
486 -> END|
487ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EVENT IF NOT EXISTS backupCountry;
488
489CREATE EVENT backupCountry
490ON SCHEDULE EVERY' at line 1
491mysql> \d ;
492mysql> DROP EVENT IF NOT EXISTS backupCountry;
493ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT EXISTS backupCountry' at line 1
494mysql> \d |
495mysql> CREATE EVENT backupCountry
496 -> ON SCHEDULE EVERY 1 MINUTE
497 -> DO BEGIN
498 -> DECLARE bCountry CHAR(23) DEFAULT '.csv';
499 -> DECLARE stmt VARCHAR(50);
500 ->
501 -> SET bCountry = CONCAT( REPLACE(REPLACE(NOW(),' ','-'),':','-') ,bCountry);
502 -> SET stmt = CONCAT('SELECT * INTO OUTFILE "C:/mysql/uploads/',bCountry,'" FROM country');
503 -> PREPARE stmt1 FROM stmt;
504 -> EXECUTE stmt1;
505 ->
506 -> END|
507ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'stmt;
508 EXECUTE stmt1;
509
510END' at line 9
511mysql> \d ;
512mysql> SET @bCountry = '.csv';
513Query OK, 0 rows affected (0.00 sec)
514
515mysql> SET @stmt;
516ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
517mysql>
518mysql> SET @bCountry = CONCAT( REPLACE(REPLACE(NOW(),' ','-'),':','-') ,@bCountry);
519Query OK, 0 rows affected (0.00 sec)
520
521mysql> SET @stmt = CONCAT('SELECT * INTO OUTFILE "C:/mysql/uploads/',@bCountry,'" FROM country');
522Query OK, 0 rows affected (0.00 sec)
523
524mysql>
525mysql> SELECT @bCountry;
526+-------------------------+
527| @bCountry |
528+-------------------------+
529| 2019-12-29-15-19-18.csv |
530+-------------------------+
5311 row in set (0.02 sec)
532
533mysql> SELECT @stmt;
534+-------------------------------------------------------------------------------+
535| @stmt |
536+-------------------------------------------------------------------------------+
537| SELECT * INTO OUTFILE "C:/mysql/uploads/2019-12-29-15-19-18.csv" FROM country |
538+-------------------------------------------------------------------------------+
5391 row in set (0.00 sec)
540
541mysql> DROP EVENT IF NOT EXISTS backupCountry;
542ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT EXISTS backupCountry' at line 1
543mysql> \d |
544mysql> CREATE EVENT backupCountry
545 -> ON SCHEDULE EVERY 1 MINUTE
546 -> DO BEGIN
547 -> DECLARE bCountry CHAR(23) DEFAULT '.csv';
548 -> DECLARE stmt VARCHAR(50);
549 ->
550 -> SET bCountry = CONCAT( REPLACE(REPLACE(NOW(),' ','-'),':','-') ,bCountry);
551 -> SET stmt = CONCAT('SELECT * INTO OUTFILE "C:/mysql/uploads/',bCountry,'" FROM country');
552 -> PREPARE stmt1 FROM stmt;
553 -> EXECUTE stmt1;
554 ->
555 -> END|
556ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'stmt;
557 EXECUTE stmt1;
558
559END' at line 9
560mysql> \d ;
561mysql> \s
562--------------
563
564Connection id: 18
565Current database: world
566Current user: root@localhost
567SSL: Cipher in use is TLS_AES_256_GCM_SHA384
568Using delimiter: ;
569Server version: 8.0.18 MySQL Community Server - GPL
570Protocol version: 10
571Connection: localhost via TCP/IP
572Server characterset: utf8mb4
573Db characterset: utf8mb4
574Client characterset: cp866
575Conn. characterset: cp866
576TCP port: 3306
577Uptime: 5 hours 7 min 57 sec
578
579Threads: 6 Questions: 3071 Slow queries: 0 Opens: 535 Flush tables: 3 Open tables: 403 Queries per second avg: 0.166
580--------------
581
582mysql> DROP EVENT IF NOT EXISTS backupCountry;
583ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT EXISTS backupCountry' at line 1
584mysql> \d |
585mysql> DROP EVENT IF NOT EXIST backupCountry;
586 -> |
587ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT EXIST backupCountry' at line 1
588mysql> \d ;
589mysql>
590mysql> DROP EVENT IF NOT EXIST backupCountry;
591ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT EXIST backupCountry' at line 1
592mysql> DROP EVENT IF NOT EXISTS backupCountry;
593ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT EXISTS backupCountry' at line 1
594mysql> Terminal close -- exit!