· 8 years ago · Mar 18, 2018, 01:10 PM
1create database school;
2use school;
3CREATE TABLE students
4(
5ID serial,
6Name varchar(20),
7Lastname varchar(20),
8score tinyint unsigned
9);
10
11insert into students values
12(null,"John", "Cena", 32),
13(null,"Obi", "Kenobi", 99),
14(null,"Post", "Malone", 61),
15(null,"Tupac", "Shakur", 42),
16(null,"Vardas", "Pavarde", 25);
17
18
19
20DELIMITER //
21CREATE PROCEDURE GetTable()
22BEGIN
23select name, lastname, score from students;
24END //
25DELIMITER ;
26
27
28delimiter //
29create procedure CreateTable()
30BEGIN
31CREATE TABLE IF NOT EXISTS students
32(
33ID serial,
34Name varchar(20),
35Lastname varchar(20),
36score tinyint unsigned
37);
38end //
39delimiter ;
40
41DELIMITER //
42CREATE PROCEDURE MoreThan (IN num tinyint unsigned)
43BEGIN
44 SELECT Name, score FROM students WHERE score > num;
45END //
46DELIMITER ;
47CALL MoreThan(30);
48
49DELIMITER //
50CREATE PROCEDURE MaxScore(OUT maxsc tinyint unsigned)
51BEGIN
52SELECT MAX(score) INTO maxsc FROM students;
53END //
54DELIMITER ;
55
56CALL MaxScore(@maxsc);
57SELECT @maxsc;
58
59SET @kint = 30;
60CALL MoreThan(@kint);
61
62
63
64CREATE PROCEDURE MaxScore(INOUT nr tinyint unsigned)
65
66(select)Isveda vardus kuriu score didesnis uz paduota kintamaji
67Priskiria kintamajo reiksme, kuri yra maziausias score lenteleje
68
69
70DELIMITER //
71CReate PROCEDURE MoreMin(INOUT nr tinyint unsigned)
72BEGIN
73select Name from students where score > nr;
74select MIN(Score) into nr from students;
75END //
76DELIMITER ;
77SET @kint = 30;
78CALL MoreMin(@kint);
79select @kint;
80
81
82DELIMITER //
83DROP PROCEDURE IF EXISTS TextWhile//
84CREATE PROCEDURE TextWhile()
85BEGIN
86
87SET @x = 1;
88SET @str = '';
89
90WHILE @x<=3 DO
91SET @str= CONCAT(@str,(SELECT name FROM STUDENTS WHERE ID = @x),',');
92SET @x= @x + 1;
93END WHILE;
94
95SELECT @str;
96END//
97DELIMITER ;
98
99
100
101SELECT IF (1>3,'true','false');
102
103
104Parasyti procedura kuri sukuria lentele Results ir i ja
105patalpina studentu vardus ir kuriu score yra didesnis uz
106paduota skaiciu, stulpelyje passed paraso Yes, jei ne - No
107
108
109DELIMITER //
110DROP PROCEDURE IF EXISTS PassedProc//
111CREATE PROCEDURE PassedProc (IN LimitNr tinyint unsigned)
112BEGIN
113
114DROP TABLE IF EXISTS Results;
115CREATE TABLE RESULTS
116(
117 Name varchar(20),
118 passed varchar(3)
119);
120SET @x = 1;
121WHILE @x <= (select count(name)from students) DO
122INSERT INTO Results VALUES(null, (select name from students where id = @x),(select if(((select score from students where id = @x)>LimitNr),'Yes', 'No')));
123SET @x = @x + 1;
124END WHILE;
125END//
126DELIMITER ;
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174CREATE DATABASE priestesta;
175USE priestesta;
176CREATE TABLE employee
177(
178 ID serial,
179 Name varchar(50) NOT NULL,
180 LastName varchar(50) NOT NULL,
181 Email varchar(150) UNIQUE NOT NULL,
182 Salary float(7,2) unsigned
183);
184INSERT INTO employee VALUES (null, 'Jonas', 'Kazlauskas','Jonukas@one.lt',4682.2),(null, 'Justas', 'Kaziukas','Justelio@gmail.com',7853.2), (null, 'Aloyzas', 'Kazlekas','Aliux@yahoo.com',3243.23);
185UPDATE employee set salary=200 where email='Aliux@yahoo.com';
186
187
188
189
190
191
192
193
194START TRANSACTION;
195insert into employee values(null,'Justas','Jonaitis','lelel@gmail.com', 8888);
196
197CREATE USER 'user1'@'localhost' identified by 'user1';
198
199GRANT SELECT, INSERT, UPDATE, DELETE ON priestesta.* to 'user1'@'localhost';
200
201
202
203
204
205
206
207
208create table stats
209(
210 ID serial,
211 action enum('insert','delete','update'),
212 curr_user varchar(50),
213 curr_id bigint unsigned,
214 mod_date timestamp
215);
216
217
218CREATE TRIGGER <trigger name> <actionas> <query type> on <table> for each row
219BEGIN
220END
221
222<actionas> => before||after
223<query type> => insert||delete||update
224
225
226DELIMITER //
227CREATE TRIGGER InsterTrigger AFTER INSERT ON employee for each row
228BEGIN
229 INSERT INTO stats VALUES(null,1, user(), new.ID, current_timestamp);
230END //
231DELIMITER ;
232
233
234DELIMITER //
235CREATE TRIGGER DeleteTrigger AFTER DELETE ON employee for each row
236BEGIN
237 INSERT INTO stats VALUES(null,2, user(), old.ID, current_timestamp);
238END //
239DELIMITER ;
240
241
242
243CREATE TABLE stats2
244(
245 ID SERIAL,
246 user_id bigint unsigned,
247 oldSalary float(7,2) unsigned,
248 newSalary float(7,2) unsigned,
249 mod_date timestamp
250);
251
252DELIMITER $$
253CREATE TRIGGER UserSalaryTrigger BEFORE UPDATE ON employee for each row
254BEGIN
255 INSERT INTO stats2 VALUES(null, old.ID, old.salary, new.salary, current_timestamp);
256END$$
257DELIMITER ;
258
259DELIMITER $$
260CREATE TRIGGER UserSalaryTrigger AFTER UPDATE ON employee for each row
261BEGIN
262 INSERT INTO stats2 VALUES(null, old.ID, old.salary, new.salary, current_timestamp);
263END$$
264DELIMITER ;