· 6 years ago · Nov 08, 2019, 01:10 AM
1drop table users, news, comments_news;
2
3create table users
4(
5 id serial /*unique*/ primary key,
6 email text unique
7 constraint chk_email
8 check (email LIKE '%_@__%.__%'),
9 password text
10 constraint chk_password
11 check ( Length(password) >= 6 and length(password) <= 16),
12 first_name text,
13 second_name text,
14 last_name text,
15 bonus int default 0
16 constraint chk_bonus
17 check ( bonus >= 0 )
18);
19
20create table news
21(
22 id serial unique,
23 name text not null,
24 date date default current_date
25 constraint chk_date
26 check ( date <= current_date ),
27 image text,
28 detail_text text
29);
30
31create table comments_news
32(
33 id serial unique,
34 news_id int
35 references news(id),
36 user_id int
37 references users(id),
38 date date default current_date
39 constraint chk_date
40 check ( date <= current_date ),
41 comment text not null
42);
43
44--select * from users;
45
46drop function get_comments_by_id_news(id int);
47create function get_comments_by_id_news(id int)
48returns table(Имя text, Комментарии text, Дата date)
49as $$
50 select first_name as Имя, comment as Комментарий, comments_news.date as Дата
51 from comments_news
52 inner join users on users.id = comments_news.user_id
53 inner join news on comments_news.news_id = news.id
54
55 where news_id = $1;
56
57 $$ language sql;
58select * from get_comments_by_id_news(5);
59
60select first_name, last_name, email, password from users;
61------------------------------------------------------------
62drop table if exists section, item;
63
64create table section (
65 id serial primary key,
66 name text not null,
67 parent_id int references section(id)
68);
69
70create table item (
71 id serial primary key,
72 name text not null unique,
73 image text,
74 description text,
75 price decimal not null,
76 section_id int references section(id) not null
77);
78--------------------------------------------------
79
80--select name from selection where parent_id is null;
81
82select count(price) from item;
83
84select name, image, description, price
85 from item
86 where price = (select max(price) from item);
87
88select * from item where id = 43; --найти 43
89
90select item.id, section.id --section.name, count(*) --count - функ для посчета кол-ва
91from section, item
92 --inner join section on item.section.id = section.id
93where section.id = item.section_id;
94
95select section.name, count(*) --count - функ для посчета кол-ва
96from section, item
97 group by section.name --по какому полю грппируем
98 --having count(*) > 40; --
99 having section.name = 'etiam';
100
101drop table if exists bascet;
102
103create table basket (
104 id serial primary key, -- целые, от 1, авто++
105 user_id int references users(id)
106
107);
108
109create table sh_for_my_data_base.position
110(
111 id serial not null
112 constraint position_pk
113 primary key,
114 item_id int not null
115 constraint position_item_id_fk
116 references sh_for_my_data_base.item,
117 basket_id int not null,
118 quantity int default 1,
119 constraint position_basket_id_fk
120 foreign key (basket_id) references sh_for_my_data_base.basket(id)
121);
122
123
124select sum(quantity) as кол, sum(total_price) as сумма
125from
126 get_items_basket_by_user_id(1);
127
128CREATE OR REPLACE FUNCTION get_items_basket_by_user_id (user_id int)
129RETURNS table(image text, name text, quantity int, price decimal, total_price decimal)
130AS $$
131 select i.image, i.name, position.quantity, i.price, (position.quantity * i.price) as total_price
132 from position
133 inner join basket b on b.id = basket_id
134 inner join item i on position.item_id = i.id
135 where b.user_id = $1;
136$$ LANGUAGE sql;
137
138
139alter function get_comments_by_id_news(integer) owner to postgres;
140
141-- auto-generated definition
142drop table friends;
143create table friends
144(
145 user_id integer not null
146 constraint friends_users_id_fk
147 references users,
148 friend_id integer not null
149 constraint friends_users_id_fk_2
150 references users,
151 constraint friends_pkey
152 primary key (user_id, friend_id)
153);
154
155alter table friends
156 owner to postgres;
157
158create trigger t_inser_friend
159 before insert on friends for each row execute procedure insert_friend();
160
161create function insert_friend() returns trigger as $$
162 declare
163 user_id_insert int;
164 friend_id_insert int;
165 count_exist int;
166 begin
167 if (tg_op = 'INSERT') then
168 user_id_insert = new.user_id;
169 friend_id_insert = new.friend_id;
170 count_exist = (select count(*)
171 from friends
172 where (friend_id = friend_id_insert and user_id = user_id_insert) or
173 (friend_id = user_id_insert and user_id = friend_id_insert)
174 );
175
176 if (count_exist > 0) then
177 raise notice 'Такая связка уже существует';
178 return null;
179 end if;
180
181 return new;
182 end if;
183 end;
184 $$ language plpgsql;
185
186insert into friends values (2, 1);
187
188create type type_characters as enum('text', 'file', 'select')
189
190create table characters(
191 id serial primary key,
192 name text not null,
193 type type_characters
194);
195
196create table characters_items(
197 id serial primary key,
198 character_id int references characters,
199 item_id int references item,
200 value text
201);
202
203select c.name, ci.value
204from item
205inner join characters_items ci on item.id = ci.item_id
206inner join characters c on ci.character_id = c.id
207where item.id = 2;
208
209create table account(
210 id serial primary key,
211 user_id int references users,
212 account decimal check ( account >= 0 )
213);
214
215begin;
216
217update account set account = account + 300 where id = 2;
218update account set account = account - 300 where id = 1;
219
220commit;
221
222create view get_all_items as select * from item;
223
224select * from get_all_items;
225--------------------------------------------------------------------------------
226select name, price from item where name = 'd';
227
228select
229 item.name,
230 section.name,
231 item.price,
232 dense_rank() over (partition by section.name order by price)
233 from item
234 inner join section on item.section_id = section.id;
235
236--минимкальное или первое значение
237select
238 item.name,
239 section.name,
240 item.price,
241 first_value( item.price) over (partition by section.name order by price)
242 from item
243 inner join section on item.section_id = section.id;
244
245select
246 item.name,
247 section.name,
248 price,
249 last_value(price) over (partition by section.name order by price)
250from item
251inner join section on item.section_id = section.id;
252
253--сравинвает с минимумом
254select
255 item.name,
256 section.name,
257 item.price,
258 min(price) over w
259 from item
260 inner join section on item.section_id = section.id
261 window w as (partition by section.name order by price)
262;
263
264
265select
266 item.name,
267 section.name,
268 item.price,
269 count(*) over w
270 from item
271 inner join section on item.section_id = section.id
272 window w as (partition by section.name )
273;
274
275select
276 item.name,
277 section.name,
278 item.price,
279 ntile(3) over w
280 from item
281 inner join section on item.section_id = section.id
282 window w as (partition by section.name order by price)
283;
284
285select
286 item.name,
287 section.name,
288 item.price,
289 min(price) over w
290 from item
291 inner join section on item.section_id = section.id
292 window w as (partition by section.name order by price)
293;
294
295-- insert for section
296--------------------------------------------------
297insert into section (name, parent_id) values ('eu', null); -- 1
298insert into section (name, parent_id) values ('eget eros', 1);
299insert into section (name, parent_id) values ('eu orci', 1);
300insert into section (name, parent_id) values ('etiam', 1);
301insert into section (name, parent_id) values ('ut', null); -- 5