· 6 years ago · Mar 24, 2019, 09:24 AM
1Microsoft Windows [Version 10.0.14393]
2(c) 2016 Microsoft Corporation. Wszelkie prawa zastrzeżone.
3
4C:\Users\student>mysql -u daszkow1 -p -h mysql.agh.edu.pl
5Enter password: ****************
6Welcome to the MySQL monitor. Commands end with ; or \g.
7Your MySQL connection id is 5952128
8Server version: 5.5.62-0+deb8u1 (Debian)
9
10Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
11
12Oracle is a registered trademark of Oracle Corporation and/or its
13affiliates. Other names may be trademarks of their respective
14owners.
15
16Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
17
18mysql> use daszkow1
19Database changed
20mysql> CREATE TABLE wykladowca ( id int(11) NOT NULL AUTO_INCREMENT, imie varchar(40) NOT NULL, nazwisko varchar(40) NOT NULL, email varchar(255) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;
21ERROR 1050 (42S01): Table 'wykladowca' already exists
22mysql> CREATE TABLE przedmiot ( id int(11) NOT NULL AUTO_INCREMENT, wykladowca_id int(11) NOT NULL, nazwa varchar(80) NOT NULL, rok_akademicki varchar(10) NOT NULL,PRIMARY KEY (id), KEY wykladowca_id (wykladowca_id), CONSTRAINT przedmiot_ibfk_1 FOREIGN KEY (wykladowca_id) REFERENCES wykladowca (id) ON UPDATE CASCADE) ENGINE=InnoDB;
23Query OK, 0 rows affected (0.01 sec)
24
25mysql> CREATE TABLE zaliczenie ( id int(11) NOT NULL AUTO_INCREMENT, student_id int(11) NOT NULL, przedmiot_id int(11) NOT NULL, ocena decimal(5,1) NOT NULL,PRIMARY KEY (id),CONSTRAINT zaliczenie_ibfk_1FOREIGN KEY (student_id)REFERENCES student (id) ON UPDATE CASCADE,CONSTRAINT zaliczenie_ibfk_2 FOREIGN KEY (przedmiot_id) REFERENCES przedmiot (id) ON UPDATE CASCADE) ENGINE=InnoDB;
26ERROR 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 'KEY (student_id)REFERENCES student (id) ON UPDATE CASCADE,CONSTRAINT zaliczenie_' at line 1
27mysql> DROP TABLE if exists zaliczenie;
28Query OK, 0 rows affected, 1 warning (0.00 sec)
29
30mysql> CREATE TABLE zaliczenie (
31 -> id int(11) NOT NULL AUTO_INCREMENT,
32 -> student_id int(11) NOT NULL,
33 -> przedmiot_id int(11) NOT NULL,
34 -> ocena decimal (5,1) NOT NULL,
35 -> PRIMARY KEY (id),
36 -> CONSTRAINT zaliczenie_ibfk_1
37 -> FOREIGN KEY (student_id)
38 -> REFERENCES student (id)
39 -> ON UPDATE CASCADE,
40 -> CONSTRAINT zaliczneie_ibfk_2
41 -> FOREIGN KEY (przedmiot_id)
42 -> REFERENCES przedmiot (id)
43 -> ON UPDATE CASCADE) ENGINE=InnoDB;
44Query OK, 0 rows affected (0.00 sec)
45
46mysql> select * from daszkow1;
47ERROR 1146 (42S02): Table 'daszkow1.daszkow1' doesn't exist
48mysql> select *;
49ERROR 1096 (HY000): No tables used
50mysql> selct * from zaliczenie;
51ERROR 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 'selct * from zaliczenie' at line 1
52mysql> select * from zaliczenie;
53Empty set (0.02 sec)
54
55mysql> LOAD DATA LOCAL INFILE 't:/lab2_przedmiot.csv' INTO TABLE przedmiot LINES TERMINATED BY '\r\n' (wykladowca_id, nazwa, rok_akademicki);
56Query OK, 0 rows affected, 1 warning (0.00 sec)
57Records: 1 Deleted: 0 Skipped: 1 Warnings: 1
58
59mysql> select * from przedmiot
60 -> ;
61Empty set (0.00 sec)
62
63mysql> LOAD DATA LOCAL INFILE 't:/lab2_przedmiot.csv' INTO TABLE przedmiot LINES TERMINATED BY '\r\n' (wykladowca_id, nazwa, rok_akademicki);
64Query OK, 0 rows affected, 2 warnings (0.02 sec)
65Records: 1 Deleted: 0 Skipped: 1 Warnings: 2
66
67mysql> LOAD DATA LOCAL INFILE 't:/lab2_przedmiot.csv' INTO TABLE przedmiot LINES TERMINATED BY '\r\n' (wykladowca_id, nazwa, rok_akademicki);
68Query OK, 0 rows affected, 2 warnings (0.00 sec)
69Records: 1 Deleted: 0 Skipped: 1 Warnings: 2
70
71mysql> select * from przedmiot;
72Empty set (0.00 sec)
73
74mysql> LOAD DATA LOCAL INFILE 't:/lab2_przedmiot.csv' INTO TABLE przedmiot LINES TERMINATED BY '\r\n' (wykladowca_id, nazwa, rok_akademicki);
75Query OK, 0 rows affected, 6 warnings (0.00 sec)
76Records: 3 Deleted: 0 Skipped: 3 Warnings: 6
77
78mysql> select * from przedmiot;
79Empty set (0.00 sec)
80
81mysql> LOAD DATA LOCAL INFILE 't:/lab2_przedmiot.csv' INTO TABLE przedmiot FIELDS TERMINATED BY ',' LINES TERMINATED BY ';' (wykladowca_id, nazwa, rok_akademicki);
82Query OK, 0 rows affected, 6 warnings (0.00 sec)
83Records: 3 Deleted: 0 Skipped: 3 Warnings: 6
84
85mysql> select * from przedmiot;
86Empty set (0.00 sec)
87
88mysql> SOURCE t:/lab2-load-data.sql;
89Query OK, 10 rows affected (0.00 sec)
90Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
91
92Query OK, 2 rows affected (0.02 sec)
93Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
94
95Query OK, 3 rows affected (0.00 sec)
96Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
97
98Query OK, 30 rows affected (0.02 sec)
99Records: 30 Deleted: 0 Skipped: 0 Warnings: 0
100
101mysql>
102mysql> select * from przedmiot;
103+----+---------------+----------------------------+----------------+
104| id | wykladowca_id | nazwa | rok_akademicki |
105+----+---------------+----------------------------+----------------+
106| 1 | 1 | Bazy danych | 2013/2014 |
107| 2 | 2 | In??ynieria oprogramowania | 2013/2014 |
108| 3 | 2 | Projektowanie_obiektowe | 2013/2014 |
109+----+---------------+----------------------------+----------------+
1103 rows in set (0.00 sec)
111
112mysql> select * from wykladowca;
113+----+-----------+------------+----------------------------+
114| id | imie | nazwisko | email |
115+----+-----------+------------+----------------------------+
116| 1 | Pawe?? | Skrzy??ski | pawel.skrzynski@agh.edu.pl |
117| 2 | Rados??aw | Klimek | rklimek@agh.edu.pl |
118+----+-----------+------------+----------------------------+
1192 rows in set (0.00 sec)
120
121mysql> show tables
122 -> ;
123+--------------------+
124| Tables_in_daszkow1 |
125+--------------------+
126| przedmiot |
127| student |
128| wykladowca |
129| zaliczenie |
130+--------------------+
1314 rows in set (0.00 sec)
132
133mysql> select * from student;
134+----+-----------+------------+----------------+--------------------------------+
135| id | imie | nazwisko | urodzenie_data | email |
136+----+-----------+------------+----------------+--------------------------------+
137| 1 | Jan | Nowak | 1990-01-21 | jnowak@student.agh.edu.pl |
138| 2 | Marek | Kowalski | 1990-04-16 | mkowalski@student.agh.edu.pl |
139| 3 | Piotr | Kowalczyk | 1990-03-25 | pkowalczyk@student.agh.edu.pl |
140| 4 | Ryszard | Libera | 1990-08-25 | rlibera@student.agh.edu.pl |
141| 5 | Alicja | Nowakowska | 1990-07-11 | anowakowska@student.agh.edu.pl |
142| 6 | Marta | Pietruska | 1990-03-13 | mpietruska@student.agh.edu.pl |
143| 7 | Maria | Duk | 1990-09-22 | mduk@student.agh.edu.pl |
144| 8 | Magdalena | Trawa | 1990-10-25 | mtrawa@student.agh.edu.pl |
145| 9 | Tomasz | Trawa | 1990-10-25 | ptrawa@student.agh.edu.pl |
146| 10 | Magdalena | Kieltyka | 1990-11-22 | mkieltyka@student.agh.edu.pl |
147+----+-----------+------------+----------------+--------------------------------+
14810 rows in set (0.00 sec)
149
150mysql> select * from zaliczenie;
151+----+------------+--------------+-------+
152| id | student_id | przedmiot_id | ocena |
153+----+------------+--------------+-------+
154| 1 | 1 | 1 | 5.0 |
155| 2 | 2 | 1 | 5.0 |
156| 3 | 3 | 1 | 4.5 |
157| 4 | 4 | 1 | 4.5 |
158| 5 | 5 | 1 | 4.0 |
159| 6 | 6 | 1 | 4.0 |
160| 7 | 7 | 1 | 3.5 |
161| 8 | 8 | 1 | 3.5 |
162| 9 | 9 | 1 | 3.0 |
163| 10 | 10 | 1 | 2.0 |
164| 11 | 1 | 2 | 5.0 |
165| 12 | 2 | 2 | 5.0 |
166| 13 | 3 | 2 | 4.5 |
167| 14 | 4 | 2 | 4.5 |
168| 15 | 5 | 2 | 4.5 |
169| 16 | 6 | 2 | 4.5 |
170| 17 | 7 | 2 | 3.5 |
171| 18 | 8 | 2 | 3.5 |
172| 19 | 9 | 2 | 2.0 |
173| 20 | 10 | 2 | 2.0 |
174| 21 | 1 | 3 | 5.0 |
175| 22 | 2 | 3 | 5.0 |
176| 23 | 3 | 3 | 4.5 |
177| 24 | 4 | 3 | 4.5 |
178| 25 | 5 | 3 | 4.5 |
179| 26 | 6 | 3 | 4.5 |
180| 27 | 7 | 3 | 4.0 |
181| 28 | 8 | 3 | 3.5 |
182| 29 | 9 | 3 | 3.0 |
183| 30 | 10 | 3 | 3.0 |
184+----+------------+--------------+-------+
18530 rows in set (0.00 sec)
186
187mysql> select * FROM zaliczenie WHERE ocena=5.0
188 -> ;
189+----+------------+--------------+-------+
190| id | student_id | przedmiot_id | ocena |
191+----+------------+--------------+-------+
192| 1 | 1 | 1 | 5.0 |
193| 2 | 2 | 1 | 5.0 |
194| 11 | 1 | 2 | 5.0 |
195| 12 | 2 | 2 | 5.0 |
196| 21 | 1 | 3 | 5.0 |
197| 22 | 2 | 3 | 5.0 |
198+----+------------+--------------+-------+
1996 rows in set (0.00 sec)
200
201mysql> select * FROM zaliczenie WHERE ocena=5.0 AND przedmiot_id=2
202 -> ;
203+----+------------+--------------+-------+
204| id | student_id | przedmiot_id | ocena |
205+----+------------+--------------+-------+
206| 11 | 1 | 2 | 5.0 |
207| 12 | 2 | 2 | 5.0 |
208+----+------------+--------------+-------+
2092 rows in set (0.00 sec)
210
211mysql> SELECT COUNT (*) from zaliczenie WHERE ocena=5.0
212 -> ;
213ERROR 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 '*) from zaliczenie WHERE ocena=5.0' at line 1
214mysql> SELECT COUNT (*) from zaliczenie;
215ERROR 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 '*) from zaliczenie' at line 1
216mysql> SELECT COUNT * from zaliczenie;
217ERROR 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 'from zaliczenie' at line 1
218mysql> mysqldump
219 -> ;
220ERROR 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 'mysqldump' at line 1
221mysql> count(*) from zaliczenie where ocena=5.0;
222ERROR 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 'count(*) from zaliczenie where ocena=5.0' at line 1
223mysql> select count(*) from zaliczenie where ocena=5.0;
224+----------+
225| count(*) |
226+----------+
227| 6 |
228+----------+
2291 row in set (0.00 sec)
230
231mysql> select p.nazwa, count(*)
232 -> from zaliczenie z, przedmiot p
233 -> where z.przedmiot_id=p.id AND z.ocena=5.0
234 -> GROUP BY p.nazwa;
235+----------------------------+----------+
236| nazwa | count(*) |
237+----------------------------+----------+
238| Bazy danych | 2 |
239| In??ynieria oprogramowania | 2 |
240| Projektowanie_obiektowe | 2 |
241+----------------------------+----------+
2423 rows in set (0.00 sec)
243
244mysql>