· 6 years ago · Dec 09, 2019, 02:10 PM
1mysql> CREATE TABLE Animal (
2 -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
3 -> espece VARCHAR(40) NOT NULL,
4 -> sexe CHAR(1),
5 -> date_naissance DATETIME NOT NULL,
6 -> nom VARCHAR(30),
7 -> commentaires TEXT,
8 -> PRIMARY KEY (id)
9 -> )
10 -> ENGINE=INNODB;
11Query OK, 0 rows affected (0.05 sec)
12
13mysql> commentaires TEXT,^C
14mysql> drop animal
15 -> ;
16ERROR 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 'animal' at line 1
17mysql> DROP TABLE Animal
18 -> ;
19Query OK, 0 rows affected (0.03 sec)
20
21mysql> CREATE TABLE IF NOT EXISTS Animal (
22 -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
23 -> espece VARCHAR(40) NOT NULL,
24 -> sexe CHAR(1),
25 -> date_naissance DATETIME NOT NULL,
26 -> nom VARCHAR(30),
27 -> commentaires TEXT,
28 -> PRIMARY KEY (id)
29 -> )
30 -> ENGINE=INNODB;
31Query OK, 0 rows affected (0.04 sec)
32
33mysql> DESCRIBE Animal;
34+----------------+----------------------+------+-----+---------+----------------+
35| Field | Type | Null | Key | Default | Extra |
36+----------------+----------------------+------+-----+---------+----------------+
37| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
38| espece | varchar(40) | NO | | NULL | |
39| sexe | char(1) | YES | | NULL | |
40| date_naissance | datetime | NO | | NULL | |
41| nom | varchar(30) | YES | | NULL | |
42| commentaires | text | YES | | NULL | |
43+----------------+----------------------+------+-----+---------+----------------+
446 rows in set (0.00 sec)
45
46mysql> DROP TABLE Animal;
47Query OK, 0 rows affected (0.03 sec)
48
49mysql> DROP TABLE Animal;^C
50mysql> CREATE TABLE IF NOT EXISTS Animal (
51 -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
52 -> espece VARCHAR(40) NOT NULL,
53 -> sexe CHAR(1),
54 -> date_naissance DATETIME NOT NULL,
55 -> nom VARCHAR(30),
56 -> commentaires TEXT,
57 -> PRIMARY KEY (id)
58 -> )
59 -> ENGINE=INNODB;
60Query OK, 0 rows affected (0.05 sec)
61
62mysql>
63mysql>
64mysql> DESCRIBE Animal;
65+----------------+----------------------+------+-----+---------+----------------+
66| Field | Type | Null | Key | Default | Extra |
67+----------------+----------------------+------+-----+---------+----------------+
68| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
69| espece | varchar(40) | NO | | NULL | |
70| sexe | char(1) | YES | | NULL | |
71| date_naissance | datetime | NO | | NULL | |
72| nom | varchar(30) | YES | | NULL | |
73| commentaires | text | YES | | NULL | |
74+----------------+----------------------+------+-----+---------+----------------+
756 rows in set (0.00 sec)
76
77mysql> SHOW tables;
78+-------------------+
79| Tables_in_elevage |
80+-------------------+
81| animal |
82+-------------------+
831 row in set (0.00 sec)
84
85mysql> CREATE TABLE Test_tuto (
86 -> id INT NOT NULL,
87 -> nom VARCHAR(10) NOT NULL,
88 -> PRIMARY KEY(id)
89 -> );
90Query OK, 0 rows affected (0.04 sec)
91
92mysql> DESCRIBE Test_tuto
93 -> ;
94+-------+-------------+------+-----+---------+-------+
95| Field | Type | Null | Key | Default | Extra |
96+-------+-------------+------+-----+---------+-------+
97| id | int(11) | NO | PRI | NULL | |
98| nom | varchar(10) | NO | | NULL | |
99+-------+-------------+------+-----+---------+-------+
1002 rows in set (0.00 sec)
101
102mysql> ALTER TABLE Test_tuto
103 -> ADD COLUMN date_insertion DATE NOT NULL;
104Query OK, 0 rows affected (0.06 sec)
105Records: 0 Duplicates: 0 Warnings: 0
106
107mysql> DESCRIBE Test_tuto
108 -> ;
109+----------------+-------------+------+-----+---------+-------+
110| Field | Type | Null | Key | Default | Extra |
111+----------------+-------------+------+-----+---------+-------+
112| id | int(11) | NO | PRI | NULL | |
113| nom | varchar(10) | NO | | NULL | |
114| date_insertion | date | NO | | NULL | |
115+----------------+-------------+------+-----+---------+-------+
1163 rows in set (0.00 sec)
117
118mysql> drop table if exists Test_tuto
119 -> ;
120Query OK, 0 rows affected (0.02 sec)
121
122mysql> DESCRIBE Test_tuto
123 -> ;
124ERROR 1146 (42S02): Table 'elevage.test_tuto' doesn't exist
125mysql> CREATE TABLE IF NOT EXISTS Test_tuto (
126 -> id INT NOT NULL,
127 -> nom VARCHAR(10) NOT NULL,
128 -> PRIMARY KEY(id)
129 -> );
130Query OK, 0 rows affected (0.04 sec)
131
132mysql> ENGINE=INNODB;
133ERROR 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 'ENGINE=INNODB' at line 1
134mysql> CREATE TABLE IF NOT EXISTS Test_tuto (
135 -> id INT NOT NULL,
136 -> nom VARCHAR(10) NOT NULL,
137 -> PRIMARY KEY(id)
138 -> )
139 -> ENGINE=INNODB;
140Query OK, 0 rows affected, 1 warning (0.01 sec)
141
142mysql> ALTER TABLE Test_tuto
143 -> ADD COLUMN date_insertion DATE NOT NULL;
144Query OK, 0 rows affected (0.06 sec)
145Records: 0 Duplicates: 0 Warnings: 0
146
147mysql> ADD COLUMN date_insertion DATE NOT NULL;^C
148mysql> DESCRIBE Test_tuto
149 -> ;
150+----------------+-------------+------+-----+---------+-------+
151| Field | Type | Null | Key | Default | Extra |
152+----------------+-------------+------+-----+---------+-------+
153| id | int(11) | NO | PRI | NULL | |
154| nom | varchar(10) | NO | | NULL | |
155| date_insertion | date | NO | | NULL | |
156+----------------+-------------+------+-----+---------+-------+
1573 rows in set (0.00 sec)
158
159mysql> ALTER TABLE Test_tuto
160 -> DROP COLUMN date_insertion;
161Query OK, 0 rows affected (0.06 sec)
162Records: 0 Duplicates: 0 Warnings: 0
163
164mysql> ALTER TABLE Test_tuto
165 -> CHANGE nom prenom VARCHAR(10) NOT NULL;
166Query OK, 0 rows affected (0.01 sec)
167Records: 0 Duplicates: 0 Warnings: 0
168
169mysql> DESCRIBE Test_tuto
170 -> ;
171+--------+-------------+------+-----+---------+-------+
172| Field | Type | Null | Key | Default | Extra |
173+--------+-------------+------+-----+---------+-------+
174| id | int(11) | NO | PRI | NULL | |
175| prenom | varchar(10) | NO | | NULL | |
176+--------+-------------+------+-----+---------+-------+
1772 rows in set (0.00 sec)
178
179mysql> ALTER TABLE Test_tuto
180 -> MODIFY prenom VARCHAR(10) NOT NULL DEFAULT("Jimmy");
181Query OK, 0 rows affected (0.01 sec)
182Records: 0 Duplicates: 0 Warnings: 0
183
184mysql> ALTER TABLE Test_tuto
185 -> DESCRIBE Test_tuto
186 -> ;
187ERROR 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 'DESCRIBE Test_tuto' at line 2
188mysql> DESCRIBE Test_tuto
189 -> ;
190+--------+-------------+------+-----+-----------------+-------------------+
191| Field | Type | Null | Key | Default | Extra |
192+--------+-------------+------+-----+-----------------+-------------------+
193| id | int(11) | NO | PRI | NULL | |
194| prenom | varchar(10) | NO | | _cp850\'Jimmy\' | DEFAULT_GENERATED |
195+--------+-------------+------+-----+-----------------+-------------------+
1962 rows in set (0.00 sec)
197
198mysql> INSERT INTO Animal
199 -> VALUES(1, 'chien', 'M', '2010-04-05 13:43:00', 'Mordille beaucoup', 'Rox');
200Query OK, 1 row affected (0.01 sec)
201
202mysql> SELECT * FROM Test_tuto
203 ->
204 -> ;
205Empty set (0.00 sec)
206
207mysql> SELECT * FROM Animal;
208+----+--------+------+---------------------+-------------------+--------------+
209| id | espece | sexe | date_naissance | nom | commentaires |
210+----+--------+------+---------------------+-------------------+--------------+
211| 1 | chien | M | 2010-04-05 13:43:00 | Mordille beaucoup | Rox |
212+----+--------+------+---------------------+-------------------+--------------+
2131 row in set (0.00 sec)
214
215mysql> INSERT INTO Animal
216 -> VALUES (2, 'chat', NULL, '2010-03-24 02:23:00', NULL, "Roucky");
217Query OK, 1 row affected (0.00 sec)
218
219mysql> INSERT INTO Animal
220 -> VALUES (NULL , 'chat', 'F', '2010-09-13 15:02:00', NULL, 'Schtroumpfette');
221Query OK, 1 row affected (0.01 sec)
222
223mysql> SELECT * FROM Animal;
224+----+--------+------+---------------------+-------------------+----------------+
225| id | espece | sexe | date_naissance | nom | commentaires |
226+----+--------+------+---------------------+-------------------+----------------+
227| 1 | chien | M | 2010-04-05 13:43:00 | Mordille beaucoup | Rox |
228| 2 | chat | NULL | 2010-03-24 02:23:00 | NULL | Roucky |
229| 3 | chat | F | 2010-09-13 15:02:00 | NULL | Schtroumpfette |
230+----+--------+------+---------------------+-------------------+----------------+
2313 rows in set (0.00 sec)
232
233mysql> INSERT INTO Animal
234 -> VALUES(NULL, 'chien', 'M', '2019-12-09 14:20:00', NULL, 'GPADID2NON');
235Query OK, 1 row affected (0.00 sec)
236
237mysql> SELECT * FROM Animal;
238+----+--------+------+---------------------+-------------------+----------------+
239| id | espece | sexe | date_naissance | nom | commentaires |
240+----+--------+------+---------------------+-------------------+----------------+
241| 1 | chien | M | 2010-04-05 13:43:00 | Mordille beaucoup | Rox |
242| 2 | chat | NULL | 2010-03-24 02:23:00 | NULL | Roucky |
243| 3 | chat | F | 2010-09-13 15:02:00 | NULL | Schtroumpfette |
244| 4 | chien | M | 2019-12-09 14:20:00 | NULL | GPADID2NON |
245+----+--------+------+---------------------+-------------------+----------------+
2464 rows in set (0.00 sec)
247
248mysql> SELECT espece FROM Animal;
249+--------+
250| espece |
251+--------+
252| chien |
253| chat |
254| chat |
255| chien |
256+--------+
2574 rows in set (0.00 sec)
258
259mysql> SELECT espece, nom from Animal;
260+--------+-------------------+
261| espece | nom |
262+--------+-------------------+
263| chien | Mordille beaucoup |
264| chat | NULL |
265| chat | NULL |
266| chien | NULL |
267+--------+-------------------+
2684 rows in set (0.00 sec)
269
270mysql> SELECT espece from Animal LIMIT 1;
271+--------+
272| espece |
273+--------+
274| chien |
275+--------+
2761 row in set (0.00 sec)
277
278mysql> SELECT DISTINCT espece FROM Animal;
279+--------+
280| espece |
281+--------+
282| chien |
283| chat |
284+--------+
2852 rows in set (0.00 sec)
286
287mysql> SELECT count(*) FROM Animal;
288+----------+
289| count(*) |
290+----------+
291| 4 |
292+----------+
2931 row in set (0.00 sec)
294
295mysql> SELECT count(*) from Animal WHERE espece = "chien";
296+----------+
297| count(*) |
298+----------+
299| 2 |
300+----------+
3011 row in set (0.00 sec)
302
303mysql> SELECT * from Animal WHERE espece = "chien";
304+----+--------+------+---------------------+-------------------+--------------+
305| id | espece | sexe | date_naissance | nom | commentaires |
306+----+--------+------+---------------------+-------------------+--------------+
307| 1 | chien | M | 2010-04-05 13:43:00 | Mordille beaucoup | Rox |
308| 4 | chien | M | 2019-12-09 14:20:00 | NULL | GPADID2NON |
309+----+--------+------+---------------------+-------------------+--------------+
3102 rows in set (0.00 sec)
311
312mysql> SELECT * from Animal WHERE espece = "chat" LIMIT 1;
313+----+--------+------+---------------------+------+--------------+
314| id | espece | sexe | date_naissance | nom | commentaires |
315+----+--------+------+---------------------+------+--------------+
316| 2 | chat | NULL | 2010-03-24 02:23:00 | NULL | Roucky |
317+----+--------+------+---------------------+------+--------------+
3181 row in set (0.00 sec)
319
320mysql>