cRginS4q

1--Выведите список стран в которых есть города с населением более 1000000 чел
2select name from country
3where exists (
4 select name 
5 from city
6 where population > 1e6
7 and code = countrycode
8);
9
10select name from country
11where not exists (
12 select name 
13 from city
14 where population >= 1e6
15 and code = countrycode
16);
17
18select name from city where countrycode = 'RUS'
19union all
20select name from city where countrycode = 'UKR';
21
22--найти города, названия которых встречаются более одного раза
23select name, count(*)
24from city
25group by name
26having count(*) > 1;
27
28--БЫСТРО, ВКУСНО!
29select name, count(*), countrycode
30from city
31where countrycode in ('USA','RUS')
32group by name
33having count(*) > 1;
34
35--найти одинаковые города в одной стране
36select c1.name 
37from city c1 inner join city c2
38where c1.name = c2.name
39and c1.id <> c2.id
40and c1.countrycode = 'RUS';
41
42--найти одинаковые города в двух странах
43select c1.name,c1.countrycode 
44from city c1 inner join city c2
45where c1.name = c2.name
46and c1.id <> c2.id
47and c1.countrycode in ('RUS','UKR');
48
49--найти одинаковые города
50select c1.name,c1.countrycode 
51from city c1 inner join city c2
52where c1.name = c2.name
53and c1.id <> c2.id
54and c1.countrycode in ('USA','UKR', 'EGY')
55order by 1;
56
57
58create database mydb1;
59use mydb1;
60create table tb1(
61 id INT(11) primary key auto_increment,
62 name VARCHAR(20),
63 salary INT(11)
64);
65insert into tb1 values 
66 (null, 'Вася', 15000),
67 (null, 'Петя', 25000),
68 (null, 'John', 180000),
69 (null, 'Зоя', 50000);
70select * from tb1;
71
72drop view salary;
73create view salary 
74as select salary * 1.13 as 'большая зарплата' from tb1;
75
76select * from salary;
77
78use world;
79create view vAsia
80as select name from country 
81where continent = 'Asia';
82
83select * from vAsia;
84
85create view CanadaLanguage
86as 
87select co.name,cl.language, cl.percentage
88from country co inner join countrylanguage cl
89on co.code = cl.countrycode
90where name = 'Canada';
91
92select * from CanadaLanguage;
93
94show create table city;
95show create table CanadaLanguage;
96
97show tables;
98
99world
100
101select table_name, 'таблица'
102from information_schema.tables
103where table_schema = 'world'
104union 
105select table_name, 'просмотр'
106from information_schema.views
107where table_schema = 'world';
108
109use world;
110--аналог show table, но с просмотром view
111select table_name, table_type
112from information_schema.tables
113where table_schema = 'world';
114
115update vasia 
116set name = concat(name, '*')
117order by name desc
118limit 1;
119
120select name from vasia
121order by name desc
122limit 1;
123
124select name from country
125order by name desc
126limit 10;
127
128create temporary table strashnaya (
129 name VARCHAR(30)
130);
131insert into strashnaya values ('fobos');
132create algorithm=TEMPTABLE view neponyatniy 
133as select * from strashnaya;
134select * from neponyatniy;
135
136
137--создание процедуры foo()
138drop procedure if exists foo;
139
140delimiter |
141create procedure foo()
142begin
143  select 'работает foo()' as 'рез';
144end|
145delimiter ;
146
147call foo();
148
149
150--создание процедуры getCodeByName()
151--вернуть код страны, по ее названию
152drop procedure if exists getCodeByName;
153
154delimiter |
155create procedure getCodeByName(in n varchar(52) )
156begin
157  select code 
158  from country 
159  where name = n;
160end|
161delimiter ;
162
163call getCodeByName('Russian Federation');
164
165
166drop procedure if exists getCodeByName2;
167
168delimiter |
169create procedure getCodeByName2(in n varchar(52),out c CHAR(3))
170begin
171  select code into c
172  from country 
173  where name = n;
174end|
175delimiter ;
176
177set @code = '';
178call getCodeByName2('Russian Federation',@code);
179select @code;
180
181select count(*) from city where countrycode = @code;
182
183--тестовая процедура с переменными внутри
184--напишем процедуру, которая получает на вход код страны и выводит количество больших, средних и малых городов (по количеству населения)
185drop procedure if exists test;
186
187delimiter |
188create procedure test(in n char(3))
189begin
190  declare a,b,c smallint default 0;
191  
192  select count(*) into a from city where population > 2e6 and countrycode = n;
193  select count(*) into b from city 
194    where countrycode = n and population between 5e5 and 2e6 ;
195  select count(*) into c from city where population < 5e5 and countrycode = n;
196  
197  select a 'Крупных', b 'Средних', c 'Маленьких';
198end|
199delimiter ;
200
201call test('RUS');
202
203
204--выводим города с классификацией по размеру
205drop procedure if exists test2;
206
207delimiter |
208create procedure test2(in n char(3))
209begin
210  
211  drop temporary table if exists tmp;
212  create temporary table tmp(
213   name CHAR(52),
214   type ENUM('крупный','средний','малый'),
215   population INT(11)
216  );
217  
218  insert into tmp
219    select name,'крупный',population
220	from city where population > 2e6 and countrycode = n;
221	
222  insert into tmp
223	select name,'средний',population from city 
224    where countrycode = n and population between 5e5 and 2e6 ;
225	
226  insert into tmp	
227    select name,'малый',population  from city where population < 5e5 and countrycode = n;
228  
229  select * from tmp;
230  drop temporary table tmp;
231end|
232delimiter ;
233
234call test2('RUS');
235
236
237-- с выводом в файл
238drop procedure if exists test3;
239
240delimiter |
241create procedure test3(in n char(3))
242begin
243  declare query VARCHAR(255);
244  
245  drop temporary table if exists tmp;
246  create temporary table tmp(
247   name CHAR(52),
248   type ENUM('крупный','средний','малый'),
249   population INT(11)
250  );
251  
252  insert into tmp
253    select name,'крупный',population
254	from city where population > 2e6 and countrycode = n;
255	
256  insert into tmp
257	select name,'средний',population from city 
258    where countrycode = n and population between 5e5 and 2e6 ;
259	
260  insert into tmp	
261    select name,'малый',population  from city where population < 5e5 and countrycode = n;
262  
263  SET @query = concat("select * INTO OUTFILE 'C://Users/Public/file",SECOND(NOW()),".txt' from tmp");
264  
265  PREPARE sql_consumer FROM @query;
266  EXECUTE sql_consumer;
267  
268  drop temporary table tmp;
269end|
270delimiter ;
271
272call test3('RUS');
273
274Вопрос. Про php (к теме о переборах данных выборок в циклах) :). А возможно использование хранимых процедур в php-шных реализациях ActiveRecord вместо таблиц? Такое вообще делают? Или если ActiveRecord, то никаких процедур?
275
276
277--Логин: admin Пароль: ' OR 1=1; --
278
279SELECT id FROM users WHERE login = '$login' AND pwd = '$pwd';
280SELECT id FROM users WHERE login = 'admin' AND pwd = '' OR 1=1; --';
281
282PREPARE sql_consumer FROM
283'SELECT fio,email FROM consumer WHERE idconsumer = ?';
284
285SET @consumer = 1;
286EXECUTE sql_consumer USING @consumer;
287SET @consumer = 2;
288EXECUTE sql_consumer USING @consumer;
289
290--используя курсоры, написать функцию sortcities(in d1 INT, in d2 INT), которая принимает диапазоны населенности городов, а на выходе выводит таблицу вида
291крупных 43
292средних 44
293малых   12
294
295drop procedure if exists sortcities;
296
297delimiter |
298create procedure sortcities(in d1 INT, in d2 INT)
299begin
300  declare flag INT default 0;
301  declare popul INT default 0;
302  declare cursorCity cursor for select population from city;
303  declare continue handler for sqlstate '02000' set flag = 1;
304  
305  drop temporary table if exists tmp;
306  create temporary table tmp(
307   type ENUM('крупный','средний','малый')
308  );
309  
310  open cursorCity;
311  repeat
312    fetch cursorCity into popul;
313	if not flag then
314	  if popul < d1 then
315	     insert into tmp values('малый');
316		else if popul >= d2 then 
317	           insert into tmp values('крупный');
318		     else 
319			   insert into tmp values('средний');
320	    end if;
321	  end if;
322	end if;
323  until flag end repeat;
324  close cursorCity;
325  
326  select type,count(*) from tmp group by 1;
327  
328  drop temporary table tmp;
329end|
330delimiter ;
331
332call sortcities(5e5, 2e6);
333
334
335SET @x = 0;
336REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
337
338mysql> select name,field('Вася',idcust,name,age) from customer;
339
340
341--ДЛЯ ПРИЛИЧИЯ: создаем функцию. Возвращает население страны по коду
342use world;
343drop function if exists getPopulByCode;
344delimiter $
345create function getPopulByCode(c CHAR(3))
346returns INT(11)
347begin
348 declare p INT(11) default 0;
349 
350 select population into p
351 from country
352 where code = c;
353 
354 return p;
355 
356end$
357delimiter ;
358
359SELECT getPopulByCode('RUS');
360SELECT getPopulByCode('CHN');
361
362ДЗ
3631. написать процедуры и функции для своей базы (2-3 штуки)
3642. написать процедуру из 17 лабораторной
3653. создать просмотр для своей таблицы