sdmEdHha

1
2LOCK TABLES country READ;
3LOCK TABLES country WRITE;
4
5SELECT name 
6FROM country
7LIMIT 5;
8
9UPDATE country
10SET name = 'Aruba*'
11WHERE name= 'Aruba';
12
13UPDATE country
14SET name = 'Aruba'
15WHERE name= 'Aruba*';
16
17UNLOCK TABLES;
18
19
20LOCK TABLES country READ, city WRITE;
21LOCK TABLES country READ, city READ;
22
23SELECT name 
24FROM country
25LIMIT 5;
26
27SELECT name 
28FROM city
29LIMIT 5;
30
31UPDATE country
32SET name = 'Aruba'
33WHERE name= 'Aruba*';
34
35UPDATE city
36SET name = 'New Moscow'
37WHERE name= 'Moscow';
38
39UNLOCK TABLES;
40
41SELECT language
42FROM countrylanguage
43LIMIT 10;
44
45
46LOCK TABLES country READ;
47LOCK TABLES city READ;
48
49UPDATE country
50SET name = 'Aruba'
51WHERE name= 'Aruba*';
52
53UNLOCK TABLES;
54
55Блокировки записей
56SELECT GET_LOCK('mylock', 15);забираем семафор
57SELECT RELEASE_LOCK('mylock');отдаем семафор
58
59SET @t1 = now();
60SQL QUERIES...
61SET @t2 = now();
62
63select get_lock(name,15),name
64from country 
65where name = 'Aruba';
66
67UPDATE country
68SET name = 'Aruba'
69WHERE name= 'Aruba*';
70
71ПСЕВДО
72select get_lock(CONCAT(id,name),15),name
73from users
74where id = 1;
75
76set @flag = 0;
77select get_lock(name,15) into @flag
78from country 
79where name = 'Aruba';
80
81select @flag;
82
83show variables like '%autocommit%';
84
85START TRANSACTION;
86
87UPDATE customer
88SET age = age + 10;
89DELETE FROM customer
90WHERE idcust = 5;
91SELECT * FROM customer;
92
93COMMIT;
94ROLLBACK;
95
96SET autocommit =0;
97show variables like '%autocommit%';
98SET autocommit =1;
99show variables like '%autocommit%';
100
101
102START TRANSACTION;
103
104SELECT count(*) FROM city;
105DELETE FROM city;
106SELECT count(*) FROM city;
107
108COMMIT;
109ROLLBACK;
110
111show create table city;
112
113--lock-tables + хатико
114--я думал, что она InnoDB
115--люда не занимай места - транзакция в аэропорту
116--все переводим на blackhole... и дампы туда же
117--нет базы - нет проблем...
118--я вчера ковырялся с пользователями..
119--права ему дали, а он дропает..
120--дропает по-черному (!!!) в blackhole
121
122SHOW VARIABLES LIKE '%isolation%';
123
124tx_isolation
125
126--ПРИМЕР ИЗМЕНЕНИЯ  уровня изоляции. ПЕРВЫЙ РАЗ ВЫПОЛНЯТЬ ОСТОРОЖНО..
127
128
129
130SET SESSION TRANSACTION ISOLATION LEVEL
131READ UNCOMMITTED;
132
133SET SESSION TRANSACTION ISOLATION LEVEL
134READ COMMITTED;
135
136SET SESSION TRANSACTION ISOLATION LEVEL
137REPEATABLE READ;
138
139SHOW VARIABLES LIKE '%isolation%';
140
141START TRANSACTION;
142
143SELECT * FROM customer;
144
145UPDATE customer 
146SET name = 'Лена'
147WHERE idcust = 3;
148
149UPDATE customer 
150SET age = age - 10
151WHERE idcust = 3;
152
153COMMIT;
154ROLLBACK;
155
156UPDATE customer 
157SET age = age - 5
158WHERE idcust = 3;
159
160UPDATE customer 
161SET age = age - 10
162WHERE name = 'Наташа';
163
164create temporary table c1
165select * from customer;
166commit;
167select * from c1;
168
169
170
171SELECT *
172FROM books2
173WHERE MATCH (title, author)
174AGAINST ('mysql Зандстра')\G
175
176
177
178SELECT title, author
179FROM books2
180WHERE MATCH (title, author )
181AGAINST ('+mysql -Дюбуа' IN BOOLEAN MODE)\G
182
183SELECT title, author
184FROM books2
185WHERE MATCH (title, author )
186AGAINST ('+mysql +Зандстра' IN BOOLEAN MODE)\G
187
188InnoDB 3.24
189MyISAM 10.70
190Archive 5.48
191CSV 5.05
192Memory 0.84
193Blackhole 0
194
195
196CREATE TABLE city0 ENGINE=InnoDB SELECT * FROM world1.city;
197ALTER TABLE city0 ENGINE=InnoDB;
198SELECT COUNT(*)
199FROM city0 c1
200WHERE EXISTS (SELECT * FROM city0 c2 WHERE c1.name = c2.name);
201
202CREATE TABLE city1 ENGINE=MyISAM SELECT * FROM world1.city;
203SELECT COUNT(*)
204FROM city1 c1
205WHERE EXISTS (SELECT * FROM city1 c2 WHERE c1.name = c2.name);
206
207CREATE TABLE city2 ENGINE=ARCHIVE SELECT * FROM world1.city;
208SELECT COUNT(*)
209FROM city2 c1
210WHERE EXISTS (SELECT * FROM city2 c2 WHERE c1.name = c2.name);
211CREATE TABLE city3 ENGINE=CSV SELECT * FROM world1.city;
212SELECT COUNT(*)
213FROM city3 c1
214WHERE EXISTS (SELECT * FROM city3 c2 WHERE c1.name = c2.name);
215
216CREATE TABLE city4 ENGINE=MEMORY SELECT * FROM world1.city;
217SELECT COUNT(*)
218FROM city4 c1
219WHERE EXISTS (SELECT * FROM city4 c2 WHERE c1.name = c2.name);
220
221CREATE TABLE city5 ENGINE=BLACKHOLE SELECT * FROM world1.city;
222SELECT name
223FROM city5 c1
224WHERE EXISTS (SELECT * FROM city5 c2 WHERE c1.name = c2.name);
225
226
227CREATE DATABASE IF NOT EXISTS test;
228USE test;
229CREATE TABLE city0 ENGINE=InnoDB SELECT * FROM world1.city;
230SELECT COUNT(*)
231FROM city0 c1
232WHERE EXISTS (SELECT * FROM city0 c2 WHERE c2.Name = c1.Name);
233
234
235
236CREATE TABLE city1 ENGINE=InnoDB SELECT * FROM world1.city;
237CREATE INDEX ixName ON city1 (name);
238
239CREATE TABLE city2 ENGINE=MyISAM SELECT * FROM world1.city;
240CREATE INDEX ixID ON city2 (ID);
241CREATE INDEX ixName ON city2 (name);
242CREATE INDEX ixCountryCode ON city2 (CountryCode);
243CREATE INDEX ixDistrict ON city2 (District);
244CREATE INDEX ixPopulation ON city2 (Population);
245
246http://vk.com/imysql