Guest User

Untitled

a guest
Dec 9th, 2019
97
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. mysql> 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;
  11. Query OK, 0 rows affected (0.05 sec)
  12.  
  13. mysql> commentaires TEXT,^C
  14. mysql> drop animal
  15. -> ;
  16. ERROR 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
  17. mysql> DROP TABLE Animal
  18. -> ;
  19. Query OK, 0 rows affected (0.03 sec)
  20.  
  21. mysql> 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;
  31. Query OK, 0 rows affected (0.04 sec)
  32.  
  33. mysql> 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. +----------------+----------------------+------+-----+---------+----------------+
  44. 6 rows in set (0.00 sec)
  45.  
  46. mysql> DROP TABLE Animal;
  47. Query OK, 0 rows affected (0.03 sec)
  48.  
  49. mysql> DROP TABLE Animal;^C
  50. mysql> 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;
  60. Query OK, 0 rows affected (0.05 sec)
  61.  
  62. mysql>
  63. mysql>
  64. mysql> 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. +----------------+----------------------+------+-----+---------+----------------+
  75. 6 rows in set (0.00 sec)
  76.  
  77. mysql> SHOW tables;
  78. +-------------------+
  79. | Tables_in_elevage |
  80. +-------------------+
  81. | animal |
  82. +-------------------+
  83. 1 row in set (0.00 sec)
  84.  
  85. mysql> CREATE TABLE Test_tuto (
  86. -> id INT NOT NULL,
  87. -> nom VARCHAR(10) NOT NULL,
  88. -> PRIMARY KEY(id)
  89. -> );
  90. Query OK, 0 rows affected (0.04 sec)
  91.  
  92. mysql> 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. +-------+-------------+------+-----+---------+-------+
  100. 2 rows in set (0.00 sec)
  101.  
  102. mysql> ALTER TABLE Test_tuto
  103. -> ADD COLUMN date_insertion DATE NOT NULL;
  104. Query OK, 0 rows affected (0.06 sec)
  105. Records: 0 Duplicates: 0 Warnings: 0
  106.  
  107. mysql> 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. +----------------+-------------+------+-----+---------+-------+
  116. 3 rows in set (0.00 sec)
  117.  
  118. mysql> drop table if exists Test_tuto
  119. -> ;
  120. Query OK, 0 rows affected (0.02 sec)
  121.  
  122. mysql> DESCRIBE Test_tuto
  123. -> ;
  124. ERROR 1146 (42S02): Table 'elevage.test_tuto' doesn't exist
  125. mysql> CREATE TABLE IF NOT EXISTS Test_tuto (
  126. -> id INT NOT NULL,
  127. -> nom VARCHAR(10) NOT NULL,
  128. -> PRIMARY KEY(id)
  129. -> );
  130. Query OK, 0 rows affected (0.04 sec)
  131.  
  132. mysql> ENGINE=INNODB;
  133. ERROR 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
  134. mysql> 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;
  140. Query OK, 0 rows affected, 1 warning (0.01 sec)
  141.  
  142. mysql> ALTER TABLE Test_tuto
  143. -> ADD COLUMN date_insertion DATE NOT NULL;
  144. Query OK, 0 rows affected (0.06 sec)
  145. Records: 0 Duplicates: 0 Warnings: 0
  146.  
  147. mysql> ADD COLUMN date_insertion DATE NOT NULL;^C
  148. mysql> 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. +----------------+-------------+------+-----+---------+-------+
  157. 3 rows in set (0.00 sec)
  158.  
  159. mysql> ALTER TABLE Test_tuto
  160. -> DROP COLUMN date_insertion;
  161. Query OK, 0 rows affected (0.06 sec)
  162. Records: 0 Duplicates: 0 Warnings: 0
  163.  
  164. mysql> ALTER TABLE Test_tuto
  165. -> CHANGE nom prenom VARCHAR(10) NOT NULL;
  166. Query OK, 0 rows affected (0.01 sec)
  167. Records: 0 Duplicates: 0 Warnings: 0
  168.  
  169. mysql> 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. +--------+-------------+------+-----+---------+-------+
  177. 2 rows in set (0.00 sec)
  178.  
  179. mysql> ALTER TABLE Test_tuto
  180. -> MODIFY prenom VARCHAR(10) NOT NULL DEFAULT("Jimmy");
  181. Query OK, 0 rows affected (0.01 sec)
  182. Records: 0 Duplicates: 0 Warnings: 0
  183.  
  184. mysql> ALTER TABLE Test_tuto
  185. -> DESCRIBE Test_tuto
  186. -> ;
  187. ERROR 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
  188. mysql> 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. +--------+-------------+------+-----+-----------------+-------------------+
  196. 2 rows in set (0.00 sec)
  197.  
  198. mysql> INSERT INTO Animal
  199. -> VALUES(1, 'chien', 'M', '2010-04-05 13:43:00', 'Mordille beaucoup', 'Rox');
  200. Query OK, 1 row affected (0.01 sec)
  201.  
  202. mysql> SELECT * FROM Test_tuto
  203. ->
  204. -> ;
  205. Empty set (0.00 sec)
  206.  
  207. mysql> 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. +----+--------+------+---------------------+-------------------+--------------+
  213. 1 row in set (0.00 sec)
  214.  
  215. mysql> INSERT INTO Animal
  216. -> VALUES (2, 'chat', NULL, '2010-03-24 02:23:00', NULL, "Roucky");
  217. Query OK, 1 row affected (0.00 sec)
  218.  
  219. mysql> INSERT INTO Animal
  220. -> VALUES (NULL , 'chat', 'F', '2010-09-13 15:02:00', NULL, 'Schtroumpfette');
  221. Query OK, 1 row affected (0.01 sec)
  222.  
  223. mysql> 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. +----+--------+------+---------------------+-------------------+----------------+
  231. 3 rows in set (0.00 sec)
  232.  
  233. mysql> INSERT INTO Animal
  234. -> VALUES(NULL, 'chien', 'M', '2019-12-09 14:20:00', NULL, 'GPADID2NON');
  235. Query OK, 1 row affected (0.00 sec)
  236.  
  237. mysql> 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. +----+--------+------+---------------------+-------------------+----------------+
  246. 4 rows in set (0.00 sec)
  247.  
  248. mysql> SELECT espece FROM Animal;
  249. +--------+
  250. | espece |
  251. +--------+
  252. | chien |
  253. | chat |
  254. | chat |
  255. | chien |
  256. +--------+
  257. 4 rows in set (0.00 sec)
  258.  
  259. mysql> SELECT espece, nom from Animal;
  260. +--------+-------------------+
  261. | espece | nom |
  262. +--------+-------------------+
  263. | chien | Mordille beaucoup |
  264. | chat | NULL |
  265. | chat | NULL |
  266. | chien | NULL |
  267. +--------+-------------------+
  268. 4 rows in set (0.00 sec)
  269.  
  270. mysql> SELECT espece from Animal LIMIT 1;
  271. +--------+
  272. | espece |
  273. +--------+
  274. | chien |
  275. +--------+
  276. 1 row in set (0.00 sec)
  277.  
  278. mysql> SELECT DISTINCT espece FROM Animal;
  279. +--------+
  280. | espece |
  281. +--------+
  282. | chien |
  283. | chat |
  284. +--------+
  285. 2 rows in set (0.00 sec)
  286.  
  287. mysql> SELECT count(*) FROM Animal;
  288. +----------+
  289. | count(*) |
  290. +----------+
  291. | 4 |
  292. +----------+
  293. 1 row in set (0.00 sec)
  294.  
  295. mysql> SELECT count(*) from Animal WHERE espece = "chien";
  296. +----------+
  297. | count(*) |
  298. +----------+
  299. | 2 |
  300. +----------+
  301. 1 row in set (0.00 sec)
  302.  
  303. mysql> 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. +----+--------+------+---------------------+-------------------+--------------+
  310. 2 rows in set (0.00 sec)
  311.  
  312. mysql> 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. +----+--------+------+---------------------+------+--------------+
  318. 1 row in set (0.00 sec)
  319.  
  320. mysql>
RAW Paste Data