API tools faq
paste
Advertisement
Kabbalah

Livraria - AulaBD

Kabbalah
Nov 13th, 2018
188
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 44.79 KB | None | 0 0
raw download clone embed print report
  1. create database livraria;
  2.  
  3. use livraria;
  4.  
  5. CREATE TABLE `livro` (
  6.   `codigo` int(11) NOT NULL AUTO_INCREMENT,
  7.   `titulo` varchar(20) DEFAULT NULL,
  8.   PRIMARY KEY (`codigo`),
  9.   UNIQUE KEY `codigo` (`codigo`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  11.  
  12. describe livro;
  13.  
  14. alter table livro rename livros;
  15.  
  16. alter table livros add paginas integer;
  17.  
  18. alter table livros add preco double;
  19.  
  20. insert into livros (titulo, preco) values ('BD com Java', 75.82);
  21.  
  22. insert into livros (titulo, preco) values ('Computação Gráfica', 101.32);
  23.  
  24. select codigo, titulo from livros;
  25.  
  26. select * from livros where titulo = 'BD com java';
  27.  
  28. delete from livros where titulo = 'BD com java';
  29.  
  30. --drop table livros;
  31.  
  32. ******************************************************************************************************************
  33. Enter password: *********
  34. Welcome to the MySQL monitor.  Commands end with ; or \g.
  35. Your MySQL connection id is 14
  36. Server version: 8.0.11 MySQL Community Server - GPL
  37.  
  38. Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
  39.  
  40. Oracle is a registered trademark of Oracle Corporation and/or its
  41. affiliates. Other names may be trademarks of their respective
  42. owners.
  43.  
  44. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  45.  
  46. mysql>
  47. mysql>
  48. mysql>
  49. mysql>
  50. mysql>
  51. mysql>
  52. mysql>
  53. mysql>
  54. mysql>
  55. mysql>
  56. mysql>
  57. mysql>
  58. mysql>
  59. mysql>
  60. mysql>
  61. mysql>
  62. mysql>
  63. mysql>
  64. mysql> use aula
  65. Database changed
  66. mysql> show tables;
  67. +----------------+
  68. | Tables_in_aula |
  69. +----------------+
  70. | aluno          |
  71. | user           |
  72. +----------------+
  73. 2 rows in set (0.00 sec)
  74.  
  75. mysql> select * from aluno;
  76. +----+-----------+--------+------------------+
  77. | id | matricula | nome   | email            |
  78. +----+-----------+--------+------------------+
  79. |  1 | 1111      | aluno1 | [email protected] |
  80. |  2 | 123       | Joao   | a@a              |
  81. |  3 | 222       | Pedro  | pedro@pedro      |
  82. +----+-----------+--------+------------------+
  83. 3 rows in set (0.03 sec)
  84.  
  85. mysql> select nome as nomeAluno from aluno;
  86. +-----------+
  87. | nomeAluno |
  88. +-----------+
  89. | aluno1    |
  90. | Joao      |
  91. | Pedro     |
  92. +-----------+
  93. 3 rows in set (0.00 sec)
  94.  
  95. mysql> desc aluno;
  96. +-----------+-------------+------+-----+---------+----------------+
  97. | Field     | Type        | Null | Key | Default | Extra          |
  98. +-----------+-------------+------+-----+---------+----------------+
  99. | id        | int(11)     | NO   | PRI | NULL    | auto_increment |
  100. | matricula | varchar(10) | YES  |     | NULL    |                |
  101. | nome      | varchar(50) | YES  |     | NULL    |                |
  102. | email     | varchar(20) | YES  |     | NULL    |                |
  103. +-----------+-------------+------+-----+---------+----------------+
  104. 4 rows in set (0.02 sec)
  105.  
  106. mysql> select * from aluno as alunos;
  107. +----+-----------+--------+------------------+
  108. | id | matricula | nome   | email            |
  109. +----+-----------+--------+------------------+
  110. |  1 | 1111      | aluno1 | [email protected] |
  111. |  2 | 123       | Joao   | a@a              |
  112. |  3 | 222       | Pedro  | pedro@pedro      |
  113. +----+-----------+--------+------------------+
  114. 3 rows in set (0.00 sec)
  115.  
  116. mysql> select nome from aluno;
  117. +--------+
  118. | nome   |
  119. +--------+
  120. | aluno1 |
  121. | Joao   |
  122. | Pedro  |
  123. +--------+
  124. 3 rows in set (0.00 sec)
  125.  
  126. mysql> select aluno.nome from aluno;
  127. +--------+
  128. | nome   |
  129. +--------+
  130. | aluno1 |
  131. | Joao   |
  132. | Pedro  |
  133. +--------+
  134. 3 rows in set (0.00 sec)
  135.  
  136. mysql> select al.nome, al.matricula from aluno as al;
  137. +--------+-----------+
  138. | nome   | matricula |
  139. +--------+-----------+
  140. | aluno1 | 1111      |
  141. | Joao   | 123       |
  142. | Pedro  | 222       |
  143. +--------+-----------+
  144. 3 rows in set (0.00 sec)
  145.  
  146. mysql> selectg * from user;
  147. 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 'selectg * from user' at line 1
  148. mysql> show tables
  149.     -> ;
  150. +----------------+
  151. | Tables_in_aula |
  152. +----------------+
  153. | aluno          |
  154. | user           |
  155. +----------------+
  156. 2 rows in set (0.00 sec)
  157.  
  158. mysql> select * from user;
  159. Empty set (0.00 sec)
  160.  
  161. mysql> desc user;
  162. +------------+--------------+------+-----+---------+----------------+
  163. | Field      | Type         | Null | Key | Default | Extra          |
  164. +------------+--------------+------+-----+---------+----------------+
  165. | id         | int(11)      | NO   | PRI | NULL    | auto_increment |
  166. | email      | varchar(255) | YES  |     | NULL    |                |
  167. | first_name | varchar(255) | YES  |     | NULL    |                |
  168. | last_name  | varchar(255) | YES  |     | NULL    |                |
  169. +------------+--------------+------+-----+---------+----------------+
  170. 4 rows in set (0.02 sec)
  171.  
  172. mysql> select al.nome, us.first_name from aluno as al, user as us;
  173. Empty set (0.00 sec)
  174.  
  175. mysql> select * from alunos;
  176. ERROR 1146 (42S02): Table 'aula.alunos' doesn't exist
  177. mysql> select * from aluno;
  178. +----+-----------+--------+------------------+
  179. | id | matricula | nome   | email            |
  180. +----+-----------+--------+------------------+
  181. |  1 | 1111      | aluno1 | [email protected] |
  182. |  2 | 123       | Joao   | a@a              |
  183. |  3 | 222       | Pedro  | pedro@pedro      |
  184. +----+-----------+--------+------------------+
  185. 3 rows in set (0.00 sec)
  186.  
  187. mysql> select * from aluno order by email;
  188. +----+-----------+--------+------------------+
  189. | id | matricula | nome   | email            |
  190. +----+-----------+--------+------------------+
  191. |  2 | 123       | Joao   | a@a              |
  192. |  1 | 1111      | aluno1 | [email protected] |
  193. |  3 | 222       | Pedro  | pedro@pedro      |
  194. +----+-----------+--------+------------------+
  195. 3 rows in set (0.01 sec)
  196.  
  197. mysql> select * from aluno order by email desc;
  198. +----+-----------+--------+------------------+
  199. | id | matricula | nome   | email            |
  200. +----+-----------+--------+------------------+
  201. |  3 | 222       | Pedro  | pedro@pedro      |
  202. |  1 | 1111      | aluno1 | [email protected] |
  203. |  2 | 123       | Joao   | a@a              |
  204. +----+-----------+--------+------------------+
  205. 3 rows in set (0.00 sec)
  206.  
  207. mysql> select * from aluno where nome='Pedro' order by email desc;
  208. +----+-----------+-------+-------------+
  209. | id | matricula | nome  | email       |
  210. +----+-----------+-------+-------------+
  211. |  3 | 222       | Pedro | pedro@pedro |
  212. +----+-----------+-------+-------------+
  213. 1 row in set (0.00 sec)
  214.  
  215. mysql> select * from aluno where email='a%' order by email desc;
  216. Empty set (0.00 sec)
  217.  
  218. mysql> select * from aluno where email='a*' order by email desc;
  219. Empty set (0.00 sec)
  220.  
  221. mysql> select * from aluno where email like 'a%' order by email desc;
  222. +----+-----------+--------+------------------+
  223. | id | matricula | nome   | email            |
  224. +----+-----------+--------+------------------+
  225. |  1 | 1111      | aluno1 | [email protected] |
  226. |  2 | 123       | Joao   | a@a              |
  227. +----+-----------+--------+------------------+
  228. 2 rows in set (0.00 sec)
  229.  
  230. mysql> select * from aluno where email like 'a%' order by email asc;
  231. +----+-----------+--------+------------------+
  232. | id | matricula | nome   | email            |
  233. +----+-----------+--------+------------------+
  234. |  2 | 123       | Joao   | a@a              |
  235. |  1 | 1111      | aluno1 | [email protected] |
  236. +----+-----------+--------+------------------+
  237. 2 rows in set (0.00 sec)
  238.  
  239. mysql> select * from aluno where email like 'a%' order by email;
  240. +----+-----------+--------+------------------+
  241. | id | matricula | nome   | email            |
  242. +----+-----------+--------+------------------+
  243. |  2 | 123       | Joao   | a@a              |
  244. |  1 | 1111      | aluno1 | [email protected] |
  245. +----+-----------+--------+------------------+
  246. 2 rows in set (0.00 sec)
  247.  
  248. mysql> desc user;
  249. +------------+--------------+------+-----+---------+----------------+
  250. | Field      | Type         | Null | Key | Default | Extra          |
  251. +------------+--------------+------+-----+---------+----------------+
  252. | id         | int(11)      | NO   | PRI | NULL    | auto_increment |
  253. | email      | varchar(255) | YES  |     | NULL    |                |
  254. | first_name | varchar(255) | YES  |     | NULL    |                |
  255. | last_name  | varchar(255) | YES  |     | NULL    |                |
  256. +------------+--------------+------+-----+---------+----------------+
  257. 4 rows in set (0.00 sec)
  258.  
  259. mysql> inset into user (email, first_name, last_name) values ('[email protected]', 'Joao', 'Silva');
  260. 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 'inset into user (email, first_name, last_name) values ('[email protected]', 'Joao', 'Silva' at line 1
  261. mysql> insert into user (email, first_name, last_name) values ('[email protected]', 'Joao', 'Silva');
  262. Query OK, 1 row affected (0.11 sec)
  263.  
  264. mysql> insert into user (email, first_name, last_name) values ('[email protected]', 'Joao', 'Santos');
  265. Query OK, 1 row affected (0.09 sec)
  266.  
  267. mysql> insert into user (email, first_name, last_name) values ('[email protected]', 'Maria', 'Santos');
  268. Query OK, 1 row affected (0.02 sec)
  269.  
  270. mysql> insert into user (email, first_name, last_name) values ('[email protected]', 'Carlos', 'Pinto');
  271. Query OK, 1 row affected (0.08 sec)
  272.  
  273. mysql> select * from user;
  274. +----+------------------+------------+-----------+
  275. | id | email            | first_name | last_name |
  276. +----+------------------+------------+-----------+
  277. |  1 | [email protected]          | Joao       | Silva     |
  278. |  2 | [email protected]     | Joao       | Santos    |
  279. |  3 | [email protected] | Maria      | Santos    |
  280. |  4 | [email protected] | Carlos     | Pinto     |
  281. +----+------------------+------------+-----------+
  282. 4 rows in set (0.00 sec)
  283.  
  284. mysql> insert into user (email, first_name, last_name) values ('[email protected]', 'Maria', 'Pinto');
  285. Query OK, 1 row affected (0.09 sec)
  286.  
  287. mysql> select * from user;
  288. +----+------------------+------------+-----------+
  289. | id | email            | first_name | last_name |
  290. +----+------------------+------------+-----------+
  291. |  1 | [email protected]          | Joao       | Silva     |
  292. |  2 | [email protected]     | Joao       | Santos    |
  293. |  3 | [email protected] | Maria      | Santos    |
  294. |  4 | [email protected] | Carlos     | Pinto     |
  295. |  5 | [email protected]  | Maria      | Pinto     |
  296. +----+------------------+------------+-----------+
  297. 5 rows in set (0.00 sec)
  298.  
  299. mysql> select * from user order by nome;
  300. ERROR 1054 (42S22): Unknown column 'nome' in 'order clause'
  301. mysql> select * from user order by first_name;
  302. +----+------------------+------------+-----------+
  303. | id | email            | first_name | last_name |
  304. +----+------------------+------------+-----------+
  305. |  4 | [email protected] | Carlos     | Pinto     |
  306. |  1 | [email protected]          | Joao       | Silva     |
  307. |  2 | [email protected]     | Joao       | Santos    |
  308. |  3 | [email protected] | Maria      | Santos    |
  309. |  5 | [email protected]  | Maria      | Pinto     |
  310. +----+------------------+------------+-----------+
  311. 5 rows in set (0.00 sec)
  312.  
  313. mysql> select * from user order by last_name;
  314. +----+------------------+------------+-----------+
  315. | id | email            | first_name | last_name |
  316. +----+------------------+------------+-----------+
  317. |  4 | [email protected] | Carlos     | Pinto     |
  318. |  5 | [email protected]  | Maria      | Pinto     |
  319. |  2 | [email protected]     | Joao       | Santos    |
  320. |  3 | [email protected] | Maria      | Santos    |
  321. |  1 | [email protected]          | Joao       | Silva     |
  322. +----+------------------+------------+-----------+
  323. 5 rows in set (0.00 sec)
  324.  
  325. mysql> select * from user order by last_name, first_name;
  326. +----+------------------+------------+-----------+
  327. | id | email            | first_name | last_name |
  328. +----+------------------+------------+-----------+
  329. |  4 | [email protected] | Carlos     | Pinto     |
  330. |  5 | [email protected]  | Maria      | Pinto     |
  331. |  2 | [email protected]     | Joao       | Santos    |
  332. |  3 | [email protected] | Maria      | Santos    |
  333. |  1 | [email protected]          | Joao       | Silva     |
  334. +----+------------------+------------+-----------+
  335. 5 rows in set (0.00 sec)
  336.  
  337. mysql> select * from user order by first_name;
  338. +----+------------------+------------+-----------+
  339. | id | email            | first_name | last_name |
  340. +----+------------------+------------+-----------+
  341. |  4 | [email protected] | Carlos     | Pinto     |
  342. |  1 | [email protected]          | Joao       | Silva     |
  343. |  2 | [email protected]     | Joao       | Santos    |
  344. |  3 | [email protected] | Maria      | Santos    |
  345. |  5 | [email protected]  | Maria      | Pinto     |
  346. +----+------------------+------------+-----------+
  347. 5 rows in set (0.00 sec)
  348.  
  349. mysql> select * from user order by first_name, last_name;
  350. +----+------------------+------------+-----------+
  351. | id | email            | first_name | last_name |
  352. +----+------------------+------------+-----------+
  353. |  4 | [email protected] | Carlos     | Pinto     |
  354. |  2 | [email protected]     | Joao       | Santos    |
  355. |  1 | [email protected]          | Joao       | Silva     |
  356. |  5 | [email protected]  | Maria      | Pinto     |
  357. |  3 | [email protected] | Maria      | Santos    |
  358. +----+------------------+------------+-----------+
  359. 5 rows in set (0.00 sec)
  360.  
  361. mysql> select * from user where id > 3;
  362. +----+------------------+------------+-----------+
  363. | id | email            | first_name | last_name |
  364. +----+------------------+------------+-----------+
  365. |  4 | [email protected] | Carlos     | Pinto     |
  366. |  5 | [email protected]  | Maria      | Pinto     |
  367. +----+------------------+------------+-----------+
  368. 2 rows in set (0.01 sec)
  369.  
  370. mysql> select * from user where id >= 3;
  371. +----+------------------+------------+-----------+
  372. | id | email            | first_name | last_name |
  373. +----+------------------+------------+-----------+
  374. |  3 | [email protected] | Maria      | Santos    |
  375. |  4 | [email protected] | Carlos     | Pinto     |
  376. |  5 | [email protected]  | Maria      | Pinto     |
  377. +----+------------------+------------+-----------+
  378. 3 rows in set (0.00 sec)
  379.  
  380. mysql> select * from user where nome='maria';
  381. ERROR 1054 (42S22): Unknown column 'nome' in 'where clause'
  382. mysql> select * from user where first_nome='maria';
  383. ERROR 1054 (42S22): Unknown column 'first_nome' in 'where clause'
  384. mysql> select * from user where first_name='maria';
  385. +----+------------------+------------+-----------+
  386. | id | email            | first_name | last_name |
  387. +----+------------------+------------+-----------+
  388. |  3 | [email protected] | Maria      | Santos    |
  389. |  5 | [email protected]  | Maria      | Pinto     |
  390. +----+------------------+------------+-----------+
  391. 2 rows in set (0.00 sec)
  392.  
  393. mysql> select * from user where upper(first_name)=upper('maria');
  394. +----+------------------+------------+-----------+
  395. | id | email            | first_name | last_name |
  396. +----+------------------+------------+-----------+
  397. |  3 | [email protected] | Maria      | Santos    |
  398. |  5 | [email protected]  | Maria      | Pinto     |
  399. +----+------------------+------------+-----------+
  400. 2 rows in set (0.01 sec)
  401.  
  402. mysql> select * from user where id <> 3;
  403. +----+------------------+------------+-----------+
  404. | id | email            | first_name | last_name |
  405. +----+------------------+------------+-----------+
  406. |  1 | [email protected]          | Joao       | Silva     |
  407. |  2 | [email protected]     | Joao       | Santos    |
  408. |  4 | [email protected] | Carlos     | Pinto     |
  409. |  5 | [email protected]  | Maria      | Pinto     |
  410. +----+------------------+------------+-----------+
  411. 4 rows in set (0.00 sec)
  412.  
  413. mysql> select * from user where id = 3;
  414. +----+------------------+------------+-----------+
  415. | id | email            | first_name | last_name |
  416. +----+------------------+------------+-----------+
  417. |  3 | [email protected] | Maria      | Santos    |
  418. +----+------------------+------------+-----------+
  419. 1 row in set (0.00 sec)
  420.  
  421. mysql> select * from user where not(id = 3);
  422. +----+------------------+------------+-----------+
  423. | id | email            | first_name | last_name |
  424. +----+------------------+------------+-----------+
  425. |  1 | [email protected]          | Joao       | Silva     |
  426. |  2 | [email protected]     | Joao       | Santos    |
  427. |  4 | [email protected] | Carlos     | Pinto     |
  428. |  5 | [email protected]  | Maria      | Pinto     |
  429. +----+------------------+------------+-----------+
  430. 4 rows in set (0.00 sec)
  431.  
  432. mysql> select * from user where first_name='joao' and last_name='santos';
  433. +----+--------------+------------+-----------+
  434. | id | email        | first_name | last_name |
  435. +----+--------------+------------+-----------+
  436. |  2 | [email protected] | Joao       | Santos    |
  437. +----+--------------+------------+-----------+
  438. 1 row in set (0.00 sec)
  439.  
  440. mysql> select * from user where first_name='joao' or last_name='santos';
  441. +----+------------------+------------+-----------+
  442. | id | email            | first_name | last_name |
  443. +----+------------------+------------+-----------+
  444. |  1 | [email protected]          | Joao       | Silva     |
  445. |  2 | [email protected]     | Joao       | Santos    |
  446. |  3 | [email protected] | Maria      | Santos    |
  447. +----+------------------+------------+-----------+
  448. 3 rows in set (0.00 sec)
  449.  
  450. mysql> select * from user where id in (1,3,4);
  451. +----+------------------+------------+-----------+
  452. | id | email            | first_name | last_name |
  453. +----+------------------+------------+-----------+
  454. |  1 | [email protected]          | Joao       | Silva     |
  455. |  3 | [email protected] | Maria      | Santos    |
  456. |  4 | [email protected] | Carlos     | Pinto     |
  457. +----+------------------+------------+-----------+
  458. 3 rows in set (0.00 sec)
  459.  
  460. mysql> select * from user where last_name like 's%'
  461.     -> ;
  462. +----+------------------+------------+-----------+
  463. | id | email            | first_name | last_name |
  464. +----+------------------+------------+-----------+
  465. |  1 | [email protected]          | Joao       | Silva     |
  466. |  2 | [email protected]     | Joao       | Santos    |
  467. |  3 | [email protected] | Maria      | Santos    |
  468. +----+------------------+------------+-----------+
  469. 3 rows in set (0.00 sec)
  470.  
  471. mysql> select * from user where last_name like 'sa%';
  472. +----+------------------+------------+-----------+
  473. | id | email            | first_name | last_name |
  474. +----+------------------+------------+-----------+
  475. |  2 | [email protected]     | Joao       | Santos    |
  476. |  3 | [email protected] | Maria      | Santos    |
  477. +----+------------------+------------+-----------+
  478. 2 rows in set (0.00 sec)
  479.  
  480. mysql> select * from user where last_name like '%an%';
  481. +----+------------------+------------+-----------+
  482. | id | email            | first_name | last_name |
  483. +----+------------------+------------+-----------+
  484. |  2 | [email protected]     | Joao       | Santos    |
  485. |  3 | [email protected] | Maria      | Santos    |
  486. +----+------------------+------------+-----------+
  487. 2 rows in set (0.00 sec)
  488.  
  489. mysql> select * from user where last_name like '%os';
  490. +----+------------------+------------+-----------+
  491. | id | email            | first_name | last_name |
  492. +----+------------------+------------+-----------+
  493. |  2 | [email protected]     | Joao       | Santos    |
  494. |  3 | [email protected] | Maria      | Santos    |
  495. +----+------------------+------------+-----------+
  496. 2 rows in set (0.00 sec)
  497.  
  498. mysql> select * from user where last_name not like '%os';
  499. +----+------------------+------------+-----------+
  500. | id | email            | first_name | last_name |
  501. +----+------------------+------------+-----------+
  502. |  1 | [email protected]          | Joao       | Silva     |
  503. |  4 | [email protected] | Carlos     | Pinto     |
  504. |  5 | [email protected]  | Maria      | Pinto     |
  505. +----+------------------+------------+-----------+
  506. 3 rows in set (0.00 sec)
  507.  
  508. mysql> select * from user where not(last_name like '%os');
  509. +----+------------------+------------+-----------+
  510. | id | email            | first_name | last_name |
  511. +----+------------------+------------+-----------+
  512. |  1 | [email protected]          | Joao       | Silva     |
  513. |  4 | [email protected] | Carlos     | Pinto     |
  514. |  5 | [email protected]  | Maria      | Pinto     |
  515. +----+------------------+------------+-----------+
  516. 3 rows in set (0.00 sec)
  517.  
  518. mysql> select * from user where id between 2 and 4;
  519. +----+------------------+------------+-----------+
  520. | id | email            | first_name | last_name |
  521. +----+------------------+------------+-----------+
  522. |  2 | [email protected]     | Joao       | Santos    |
  523. |  3 | [email protected] | Maria      | Santos    |
  524. |  4 | [email protected] | Carlos     | Pinto     |
  525. +----+------------------+------------+-----------+
  526. 3 rows in set (0.00 sec)
  527.  
  528. mysql> select * from user where (id between 2 and 4) and (first_name in ('Joao', 'Carlos'));
  529. +----+------------------+------------+-----------+
  530. | id | email            | first_name | last_name |
  531. +----+------------------+------------+-----------+
  532. |  2 | [email protected]     | Joao       | Santos    |
  533. |  4 | [email protected] | Carlos     | Pinto     |
  534. +----+------------------+------------+-----------+
  535. 2 rows in set (0.00 sec)
  536.  
  537. mysql> select * from user;
  538. +----+------------------+------------+-----------+
  539. | id | email            | first_name | last_name |
  540. +----+------------------+------------+-----------+
  541. |  1 | [email protected]          | Joao       | Silva     |
  542. |  2 | [email protected]     | Joao       | Santos    |
  543. |  3 | [email protected] | Maria      | Santos    |
  544. |  4 | [email protected] | Carlos     | Pinto     |
  545. |  5 | [email protected]  | Maria      | Pinto     |
  546. +----+------------------+------------+-----------+
  547. 5 rows in set (0.00 sec)
  548.  
  549. mysql> select * from aluno;
  550. +----+-----------+--------+------------------+
  551. | id | matricula | nome   | email            |
  552. +----+-----------+--------+------------------+
  553. |  1 | 1111      | aluno1 | [email protected] |
  554. |  2 | 123       | Joao   | a@a              |
  555. |  3 | 222       | Pedro  | pedro@pedro      |
  556. +----+-----------+--------+------------------+
  557. 3 rows in set (0.00 sec)
  558.  
  559. mysql> alter table aluno add usu_cad int;
  560. Query OK, 0 rows affected (0.31 sec)
  561. Records: 0  Duplicates: 0  Warnings: 0
  562.  
  563. mysql> select * from aluno;
  564. +----+-----------+--------+------------------+---------+
  565. | id | matricula | nome   | email            | usu_cad |
  566. +----+-----------+--------+------------------+---------+
  567. |  1 | 1111      | aluno1 | [email protected] |    NULL |
  568. |  2 | 123       | Joao   | a@a              |    NULL |
  569. |  3 | 222       | Pedro  | pedro@pedro      |    NULL |
  570. +----+-----------+--------+------------------+---------+
  571. 3 rows in set (0.00 sec)
  572.  
  573. mysql> update aluno set usu_cad = 1 where id < 3;
  574. Query OK, 2 rows affected (0.12 sec)
  575. Rows matched: 2  Changed: 2  Warnings: 0
  576.  
  577. mysql> update aluno set usu_cad = 3 where id = 3;
  578. Query OK, 1 row affected (0.10 sec)
  579. Rows matched: 1  Changed: 1  Warnings: 0
  580.  
  581. mysql> select * from aluno;
  582. +----+-----------+--------+------------------+---------+
  583. | id | matricula | nome   | email            | usu_cad |
  584. +----+-----------+--------+------------------+---------+
  585. |  1 | 1111      | aluno1 | [email protected] |       1 |
  586. |  2 | 123       | Joao   | a@a              |       1 |
  587. |  3 | 222       | Pedro  | pedro@pedro      |       3 |
  588. +----+-----------+--------+------------------+---------+
  589. 3 rows in set (0.00 sec)
  590.  
  591. mysql> select * from aluno, user;
  592. +----+-----------+--------+------------------+---------+----+------------------+------------+-----------+
  593. | id | matricula | nome   | email            | usu_cad | id | email            | first_name | last_name |
  594. +----+-----------+--------+------------------+---------+----+------------------+------------+-----------+
  595. |  1 | 1111      | aluno1 | [email protected] |       1 |  1 | [email protected]          | Joao       | Silva     |
  596. |  2 | 123       | Joao   | a@a              |       1 |  1 | [email protected]          | Joao       | Silva     |
  597. |  3 | 222       | Pedro  | pedro@pedro      |       3 |  1 | [email protected]          | Joao       | Silva     |
  598. |  1 | 1111      | aluno1 | [email protected] |       1 |  2 | [email protected]     | Joao       | Santos    |
  599. |  2 | 123       | Joao   | a@a              |       1 |  2 | [email protected]     | Joao       | Santos    |
  600. |  3 | 222       | Pedro  | pedro@pedro      |       3 |  2 | [email protected]     | Joao       | Santos    |
  601. |  1 | 1111      | aluno1 | [email protected] |       1 |  3 | [email protected] | Maria      | Santos    |
  602. |  2 | 123       | Joao   | a@a              |       1 |  3 | [email protected] | Maria      | Santos    |
  603. |  3 | 222       | Pedro  | pedro@pedro      |       3 |  3 | [email protected] | Maria      | Santos    |
  604. |  1 | 1111      | aluno1 | [email protected] |       1 |  4 | [email protected] | Carlos     | Pinto     |
  605. |  2 | 123       | Joao   | a@a              |       1 |  4 | [email protected] | Carlos     | Pinto     |
  606. |  3 | 222       | Pedro  | pedro@pedro      |       3 |  4 | [email protected] | Carlos     | Pinto     |
  607. |  1 | 1111      | aluno1 | [email protected] |       1 |  5 | [email protected]  | Maria      | Pinto     |
  608. |  2 | 123       | Joao   | a@a              |       1 |  5 | [email protected]  | Maria      | Pinto     |
  609. |  3 | 222       | Pedro  | pedro@pedro      |       3 |  5 | [email protected]  | Maria      | Pinto     |
  610. +----+-----------+--------+------------------+---------+----+------------------+------------+-----------+
  611. 15 rows in set (0.00 sec)
  612.  
  613. mysql> select nome, first_name from aluno, user;
  614. +--------+------------+
  615. | nome   | first_name |
  616. +--------+------------+
  617. | aluno1 | Joao       |
  618. | Joao   | Joao       |
  619. | Pedro  | Joao       |
  620. | aluno1 | Joao       |
  621. | Joao   | Joao       |
  622. | Pedro  | Joao       |
  623. | aluno1 | Maria      |
  624. | Joao   | Maria      |
  625. | Pedro  | Maria      |
  626. | aluno1 | Carlos     |
  627. | Joao   | Carlos     |
  628. | Pedro  | Carlos     |
  629. | aluno1 | Maria      |
  630. | Joao   | Maria      |
  631. | Pedro  | Maria      |
  632. +--------+------------+
  633. 15 rows in set (0.00 sec)
  634.  
  635. mysql> select nome, first_name, last_name from aluno, user;
  636. +--------+------------+-----------+
  637. | nome   | first_name | last_name |
  638. +--------+------------+-----------+
  639. | aluno1 | Joao       | Silva     |
  640. | Joao   | Joao       | Silva     |
  641. | Pedro  | Joao       | Silva     |
  642. | aluno1 | Joao       | Santos    |
  643. | Joao   | Joao       | Santos    |
  644. | Pedro  | Joao       | Santos    |
  645. | aluno1 | Maria      | Santos    |
  646. | Joao   | Maria      | Santos    |
  647. | Pedro  | Maria      | Santos    |
  648. | aluno1 | Carlos     | Pinto     |
  649. | Joao   | Carlos     | Pinto     |
  650. | Pedro  | Carlos     | Pinto     |
  651. | aluno1 | Maria      | Pinto     |
  652. | Joao   | Maria      | Pinto     |
  653. | Pedro  | Maria      | Pinto     |
  654. +--------+------------+-----------+
  655. 15 rows in set (0.00 sec)
  656.  
  657. mysql> select nome, user.id, first_name, last_name from aluno, user;
  658. +--------+----+------------+-----------+
  659. | nome   | id | first_name | last_name |
  660. +--------+----+------------+-----------+
  661. | aluno1 |  1 | Joao       | Silva     |
  662. | Joao   |  1 | Joao       | Silva     |
  663. | Pedro  |  1 | Joao       | Silva     |
  664. | aluno1 |  2 | Joao       | Santos    |
  665. | Joao   |  2 | Joao       | Santos    |
  666. | Pedro  |  2 | Joao       | Santos    |
  667. | aluno1 |  3 | Maria      | Santos    |
  668. | Joao   |  3 | Maria      | Santos    |
  669. | Pedro  |  3 | Maria      | Santos    |
  670. | aluno1 |  4 | Carlos     | Pinto     |
  671. | Joao   |  4 | Carlos     | Pinto     |
  672. | Pedro  |  4 | Carlos     | Pinto     |
  673. | aluno1 |  5 | Maria      | Pinto     |
  674. | Joao   |  5 | Maria      | Pinto     |
  675. | Pedro  |  5 | Maria      | Pinto     |
  676. +--------+----+------------+-----------+
  677. 15 rows in set (0.00 sec)
  678.  
  679. mysql> select nome, user_cad, first_name, last_name from aluno, user;
  680. ERROR 1054 (42S22): Unknown column 'user_cad' in 'field list'
  681. mysql> select nome, user_cad, first_name, last_name from aluno, user;
  682. ERROR 1054 (42S22): Unknown column 'user_cad' in 'field list'
  683. mysql> desc aluno;
  684. +-----------+-------------+------+-----+---------+----------------+
  685. | Field     | Type        | Null | Key | Default | Extra          |
  686. +-----------+-------------+------+-----+---------+----------------+
  687. | id        | int(11)     | NO   | PRI | NULL    | auto_increment |
  688. | matricula | varchar(10) | YES  |     | NULL    |                |
  689. | nome      | varchar(50) | YES  |     | NULL    |                |
  690. | email     | varchar(20) | YES  |     | NULL    |                |
  691. | usu_cad   | int(11)     | YES  |     | NULL    |                |
  692. +-----------+-------------+------+-----+---------+----------------+
  693. 5 rows in set (0.00 sec)
  694.  
  695. mysql> select nome, usu_cad, first_name, last_name from aluno, user;
  696. +--------+---------+------------+-----------+
  697. | nome   | usu_cad | first_name | last_name |
  698. +--------+---------+------------+-----------+
  699. | aluno1 |       1 | Joao       | Silva     |
  700. | Joao   |       1 | Joao       | Silva     |
  701. | Pedro  |       3 | Joao       | Silva     |
  702. | aluno1 |       1 | Joao       | Santos    |
  703. | Joao   |       1 | Joao       | Santos    |
  704. | Pedro  |       3 | Joao       | Santos    |
  705. | aluno1 |       1 | Maria      | Santos    |
  706. | Joao   |       1 | Maria      | Santos    |
  707. | Pedro  |       3 | Maria      | Santos    |
  708. | aluno1 |       1 | Carlos     | Pinto     |
  709. | Joao   |       1 | Carlos     | Pinto     |
  710. | Pedro  |       3 | Carlos     | Pinto     |
  711. | aluno1 |       1 | Maria      | Pinto     |
  712. | Joao   |       1 | Maria      | Pinto     |
  713. | Pedro  |       3 | Maria      | Pinto     |
  714. +--------+---------+------------+-----------+
  715. 15 rows in set (0.00 sec)
  716.  
  717. mysql> select nome, usu_cad, user.id,first_name, last_name from aluno, user;
  718. +--------+---------+----+------------+-----------+
  719. | nome   | usu_cad | id | first_name | last_name |
  720. +--------+---------+----+------------+-----------+
  721. | aluno1 |       1 |  1 | Joao       | Silva     |
  722. | Joao   |       1 |  1 | Joao       | Silva     |
  723. | Pedro  |       3 |  1 | Joao       | Silva     |
  724. | aluno1 |       1 |  2 | Joao       | Santos    |
  725. | Joao   |       1 |  2 | Joao       | Santos    |
  726. | Pedro  |       3 |  2 | Joao       | Santos    |
  727. | aluno1 |       1 |  3 | Maria      | Santos    |
  728. | Joao   |       1 |  3 | Maria      | Santos    |
  729. | Pedro  |       3 |  3 | Maria      | Santos    |
  730. | aluno1 |       1 |  4 | Carlos     | Pinto     |
  731. | Joao   |       1 |  4 | Carlos     | Pinto     |
  732. | Pedro  |       3 |  4 | Carlos     | Pinto     |
  733. | aluno1 |       1 |  5 | Maria      | Pinto     |
  734. | Joao   |       1 |  5 | Maria      | Pinto     |
  735. | Pedro  |       3 |  5 | Maria      | Pinto     |
  736. +--------+---------+----+------------+-----------+
  737. 15 rows in set (0.00 sec)
  738.  
  739. mysql> select nome, usu_cad, user.id,first_name, last_name from aluno left join user on aluno.usu_cad = user.id;
  740. +--------+---------+------+------------+-----------+
  741. | nome   | usu_cad | id   | first_name | last_name |
  742. +--------+---------+------+------------+-----------+
  743. | aluno1 |       1 |    1 | Joao       | Silva     |
  744. | Joao   |       1 |    1 | Joao       | Silva     |
  745. | Pedro  |       3 |    3 | Maria      | Santos    |
  746. +--------+---------+------+------------+-----------+
  747. 3 rows in set (0.00 sec)
  748.  
  749. mysql> select nome, usu_cad, user.id,first_name, last_name from aluno, user where aluno.usu_cad = user.id;
  750. +--------+---------+----+------------+-----------+
  751. | nome   | usu_cad | id | first_name | last_name |
  752. +--------+---------+----+------------+-----------+
  753. | aluno1 |       1 |  1 | Joao       | Silva     |
  754. | Joao   |       1 |  1 | Joao       | Silva     |
  755. | Pedro  |       3 |  3 | Maria      | Santos    |
  756. +--------+---------+----+------------+-----------+
  757. 3 rows in set (0.00 sec)
  758.  
  759. mysql> select nome, usu_cad, user.id,first_name, last_name from aluno, user where aluno.usu_cad = user.id;
  760. +--------+---------+----+------------+-----------+
  761. | nome   | usu_cad | id | first_name | last_name |
  762. +--------+---------+----+------------+-----------+
  763. | aluno1 |       1 |  1 | Joao       | Silva     |
  764. | Joao   |       1 |  1 | Joao       | Silva     |
  765. | Pedro  |       3 |  3 | Maria      | Santos    |
  766. +--------+---------+----+------------+-----------+
  767. 3 rows in set (0.00 sec)
  768.  
  769. mysql> select nome, usu_cad, user.id,first_name, last_name from aluno left join user on aluno.usu_cad = user.id where user.id is null;
  770. Empty set (0.00 sec)
  771.  
  772. mysql> desc aluno;
  773. +-----------+-------------+------+-----+---------+----------------+
  774. | Field     | Type        | Null | Key | Default | Extra          |
  775. +-----------+-------------+------+-----+---------+----------------+
  776. | id        | int(11)     | NO   | PRI | NULL    | auto_increment |
  777. | matricula | varchar(10) | YES  |     | NULL    |                |
  778. | nome      | varchar(50) | YES  |     | NULL    |                |
  779. | email     | varchar(20) | YES  |     | NULL    |                |
  780. | usu_cad   | int(11)     | YES  |     | NULL    |                |
  781. +-----------+-------------+------+-----+---------+----------------+
  782. 5 rows in set (0.00 sec)
  783.  
  784. mysql> insert into aluno (matricula, nome, email) values ('1010', 'Marcelo', '[email protected]');
  785. Query OK, 1 row affected (0.08 sec)
  786.  
  787. mysql> select * from aluno;
  788. +----+-----------+---------+---------------------+---------+
  789. | id | matricula | nome    | email               | usu_cad |
  790. +----+-----------+---------+---------------------+---------+
  791. |  1 | 1111      | aluno1  | [email protected]    |       1 |
  792. |  2 | 123       | Joao    | a@a                 |       1 |
  793. |  3 | 222       | Pedro   | pedro@pedro         |       3 |
  794. |  4 | 1010      | Marcelo | [email protected] |    NULL |
  795. +----+-----------+---------+---------------------+---------+
  796. 4 rows in set (0.00 sec)
  797.  
  798. mysql> select nome, usu_cad, user.id,first_name, last_name from aluno left join user on aluno.usu_cad = user.id;
  799. +---------+---------+------+------------+-----------+
  800. | nome    | usu_cad | id   | first_name | last_name |
  801. +---------+---------+------+------------+-----------+
  802. | aluno1  |       1 |    1 | Joao       | Silva     |
  803. | Joao    |       1 |    1 | Joao       | Silva     |
  804. | Pedro   |       3 |    3 | Maria      | Santos    |
  805. | Marcelo |    NULL | NULL | NULL       | NULL      |
  806. +---------+---------+------+------------+-----------+
  807. 4 rows in set (0.00 sec)
  808.  
  809. mysql> select nome, usu_cad, user.id,first_name, last_name from aluno left join user on aluno.usu_cad = user.id where user.id is null;
  810. +---------+---------+------+------------+-----------+
  811. | nome    | usu_cad | id   | first_name | last_name |
  812. +---------+---------+------+------------+-----------+
  813. | Marcelo |    NULL | NULL | NULL       | NULL      |
  814. +---------+---------+------+------------+-----------+
  815. 1 row in set (0.00 sec)
  816.  
  817. mysql> select nome, usu_cad, user.id,first_name, last_name
  818.     -> from aluno right join user on aluno.usu_cad = user.id;
  819. +--------+---------+----+------------+-----------+
  820. | nome   | usu_cad | id | first_name | last_name |
  821. +--------+---------+----+------------+-----------+
  822. | aluno1 |       1 |  1 | Joao       | Silva     |
  823. | Joao   |       1 |  1 | Joao       | Silva     |
  824. | Pedro  |       3 |  3 | Maria      | Santos    |
  825. | NULL   |    NULL |  2 | Joao       | Santos    |
  826. | NULL   |    NULL |  4 | Carlos     | Pinto     |
  827. | NULL   |    NULL |  5 | Maria      | Pinto     |
  828. +--------+---------+----+------------+-----------+
  829. 6 rows in set (0.00 sec)
  830.  
  831. mysql> select nome, usu_cad, user.id,first_name, last_name
  832.     -> from aluno right join user on aluno.usu_cad = user.id
  833.     -> where aluno.usu_cad is null;
  834. +------+---------+----+------------+-----------+
  835. | nome | usu_cad | id | first_name | last_name |
  836. +------+---------+----+------------+-----------+
  837. | NULL |    NULL |  2 | Joao       | Santos    |
  838. | NULL |    NULL |  4 | Carlos     | Pinto     |
  839. | NULL |    NULL |  5 | Maria      | Pinto     |
  840. +------+---------+----+------------+-----------+
  841. 3 rows in set (0.00 sec)
  842.  
  843. mysql> select nome, usu_cad, user.id,first_name, last_name
  844.     -> from aluno inner join user on aluno.usu_cad = user.id;
  845. +--------+---------+----+------------+-----------+
  846. | nome   | usu_cad | id | first_name | last_name |
  847. +--------+---------+----+------------+-----------+
  848. | aluno1 |       1 |  1 | Joao       | Silva     |
  849. | Joao   |       1 |  1 | Joao       | Silva     |
  850. | Pedro  |       3 |  3 | Maria      | Santos    |
  851. +--------+---------+----+------------+-----------+
  852. 3 rows in set (0.00 sec)
  853.  
  854. mysql> select nome, usu_cad, user.id,first_name, last_name
  855.     -> from aluno full outer join user on aluno.usu_cad = user.id;
  856. 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 'outer join user on aluno.usu_cad = user.id' at line 2
  857. mysql> from aluno outer join user on aluno.usu_cad = user.id;
  858. 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 'from aluno outer join user on aluno.usu_cad = user.id' at line 1
  859. mysql> from aluno full join user on aluno.usu_cad = user.id;
  860. 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 'from aluno full join user on aluno.usu_cad = user.id' at line 1
  861. mysql>  select nome, usu_cad, user.id,first_name, last_name
  862.     -> from aluno, user;
  863. +---------+---------+----+------------+-----------+
  864. | nome    | usu_cad | id | first_name | last_name |
  865. +---------+---------+----+------------+-----------+
  866. | aluno1  |       1 |  1 | Joao       | Silva     |
  867. | Joao    |       1 |  1 | Joao       | Silva     |
  868. | Pedro   |       3 |  1 | Joao       | Silva     |
  869. | Marcelo |    NULL |  1 | Joao       | Silva     |
  870. | aluno1  |       1 |  2 | Joao       | Santos    |
  871. | Joao    |       1 |  2 | Joao       | Santos    |
  872. | Pedro   |       3 |  2 | Joao       | Santos    |
  873. | Marcelo |    NULL |  2 | Joao       | Santos    |
  874. | aluno1  |       1 |  3 | Maria      | Santos    |
  875. | Joao    |       1 |  3 | Maria      | Santos    |
  876. | Pedro   |       3 |  3 | Maria      | Santos    |
  877. | Marcelo |    NULL |  3 | Maria      | Santos    |
  878. | aluno1  |       1 |  4 | Carlos     | Pinto     |
  879. | Joao    |       1 |  4 | Carlos     | Pinto     |
  880. | Pedro   |       3 |  4 | Carlos     | Pinto     |
  881. | Marcelo |    NULL |  4 | Carlos     | Pinto     |
  882. | aluno1  |       1 |  5 | Maria      | Pinto     |
  883. | Joao    |       1 |  5 | Maria      | Pinto     |
  884. | Pedro   |       3 |  5 | Maria      | Pinto     |
  885. | Marcelo |    NULL |  5 | Maria      | Pinto     |
  886. +---------+---------+----+------------+-----------+
  887. 20 rows in set (0.00 sec)
  888.  
  889. mysql> select * from user;
  890. +----+------------------+------------+-----------+
  891. | id | email            | first_name | last_name |
  892. +----+------------------+------------+-----------+
  893. |  1 | [email protected]          | Joao       | Silva     |
  894. |  2 | [email protected]     | Joao       | Santos    |
  895. |  3 | [email protected] | Maria      | Santos    |
  896. |  4 | [email protected] | Carlos     | Pinto     |
  897. |  5 | [email protected]  | Maria      | Pinto     |
  898. +----+------------------+------------+-----------+
  899. 5 rows in set (0.00 sec)
  900.  
  901. mysql> select first_name from user;
  902. +------------+
  903. | first_name |
  904. +------------+
  905. | Joao       |
  906. | Joao       |
  907. | Maria      |
  908. | Carlos     |
  909. | Maria      |
  910. +------------+
  911. 5 rows in set (0.00 sec)
  912.  
  913. mysql> select first_name from user group by first_name;
  914. +------------+
  915. | first_name |
  916. +------------+
  917. | Joao       |
  918. | Maria      |
  919. | Carlos     |
  920. +------------+
  921. 3 rows in set (0.00 sec)
  922.  
  923. mysql> select first_name, last_name from user group by first_name;
  924. +------------+-----------+
  925. | first_name | last_name |
  926. +------------+-----------+
  927. | Joao       | Silva     |
  928. | Maria      | Santos    |
  929. | Carlos     | Pinto     |
  930. +------------+-----------+
  931. 3 rows in set (0.00 sec)
  932.  
  933. mysql> select count(*) from user;
  934. +----------+
  935. | count(*) |
  936. +----------+
  937. |        5 |
  938. +----------+
  939. 1 row in set (0.01 sec)
  940.  
  941. mysql> select first_name, count(*) from user;
  942. +------------+----------+
  943. | first_name | count(*) |
  944. +------------+----------+
  945. | Joao       |        5 |
  946. +------------+----------+
  947. 1 row in set (0.01 sec)
  948.  
  949. mysql> select first_name, count(*) from user group by first_name;
  950. +------------+----------+
  951. | first_name | count(*) |
  952. +------------+----------+
  953. | Joao       |        2 |
  954. | Maria      |        2 |
  955. | Carlos     |        1 |
  956. +------------+----------+
  957. 3 rows in set (0.00 sec)
  958.  
  959. mysql> select first_name, count(*) from user group by first_name having coutn(*) > 1;
  960. 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 '*) > 1' at line 1
  961. mysql> select first_name, count(*) from user group by first_name having count(*) > 1;
  962. +------------+----------+
  963. | first_name | count(*) |
  964. +------------+----------+
  965. | Joao       |        2 |
  966. | Maria      |        2 |
  967. +------------+----------+
  968. 2 rows in set (0.00 sec)
  969.  
  970. mysql> select first_name, sum(id) from user group by first_name;
  971. +------------+---------+
  972. | first_name | sum(id) |
  973. +------------+---------+
  974. | Joao       |       3 |
  975. | Maria      |       8 |
  976. | Carlos     |       4 |
  977. +------------+---------+
  978. 3 rows in set (0.01 sec)
  979.  
  980. mysql> select max(id) from user;
  981. +---------+
  982. | max(id) |
  983. +---------+
  984. |       5 |
  985. +---------+
  986. 1 row in set (0.00 sec)
  987.  
  988. mysql> select min(id) from user;
  989. +---------+
  990. | min(id) |
  991. +---------+
  992. |       1 |
  993. +---------+
  994. 1 row in set (0.00 sec)
  995.  
  996. mysql> select min(first_name) from user;
  997. +-----------------+
  998. | min(first_name) |
  999. +-----------------+
  1000. | Carlos          |
  1001. +-----------------+
  1002. 1 row in set (0.00 sec)
  1003.  
  1004. mysql> select max(first_name) from user;
  1005. +-----------------+
  1006. | max(first_name) |
  1007. +-----------------+
  1008. | Maria           |
  1009. +-----------------+
  1010. 1 row in set (0.00 sec)
  1011.  
  1012. mysql> select * from user;
  1013. +----+------------------+------------+-----------+
  1014. | id | email            | first_name | last_name |
  1015. +----+------------------+------------+-----------+
  1016. |  1 | [email protected]          | Joao       | Silva     |
  1017. |  2 | [email protected]     | Joao       | Santos    |
  1018. |  3 | [email protected] | Maria      | Santos    |
  1019. |  4 | [email protected] | Carlos     | Pinto     |
  1020. |  5 | [email protected]  | Maria      | Pinto     |
  1021. +----+------------------+------------+-----------+
  1022. 5 rows in set (0.00 sec)
  1023.  
  1024. mysql> insert into user (email, first_name, last_name) values ('[email protected]', 'marcela', 'arruda');
  1025. Query OK, 1 row affected (0.05 sec)
  1026.  
  1027. mysql> select max(first_name) from user;
  1028. +-----------------+
  1029. | max(first_name) |
  1030. +-----------------+
  1031. | Maria           |
  1032. +-----------------+
  1033. 1 row in set (0.00 sec)
  1034.  
  1035. mysql> select first_name from user;
  1036. +------------+
  1037. | first_name |
  1038. +------------+
  1039. | Joao       |
  1040. | Joao       |
  1041. | Maria      |
  1042. | Carlos     |
  1043. | Maria      |
  1044. | marcela    |
  1045. +------------+
  1046. 6 rows in set (0.00 sec)
  1047.  
  1048. mysql> select distinct first_name from user;
  1049. +------------+
  1050. | first_name |
  1051. +------------+
  1052. | Joao       |
  1053. | Maria      |
  1054. | Carlos     |
  1055. | marcela    |
  1056. +------------+
  1057. 4 rows in set (0.00 sec)
  1058.  
  1059. mysql> select distinct first_name, last_name from user;
  1060. +------------+-----------+
  1061. | first_name | last_name |
  1062. +------------+-----------+
  1063. | Joao       | Silva     |
  1064. | Joao       | Santos    |
  1065. | Maria      | Santos    |
  1066. | Carlos     | Pinto     |
  1067. | Maria      | Pinto     |
  1068. | marcela    | arruda    |
  1069. +------------+-----------+
  1070. 6 rows in set (0.00 sec)
  1071.  
  1072. mysql> select * from user;
  1073. +----+------------------+------------+-----------+
  1074. | id | email            | first_name | last_name |
  1075. +----+------------------+------------+-----------+
  1076. |  1 | [email protected]          | Joao       | Silva     |
  1077. |  2 | [email protected]     | Joao       | Santos    |
  1078. |  3 | [email protected] | Maria      | Santos    |
  1079. |  4 | [email protected] | Carlos     | Pinto     |
  1080. |  5 | [email protected]  | Maria      | Pinto     |
  1081. |  6 | [email protected]          | marcela    | arruda    |
  1082. +----+------------------+------------+-----------+
  1083. 6 rows in set (0.00 sec)
  1084.  
  1085. mysql> select avg(id) from user;
  1086. +---------+
  1087. | avg(id) |
  1088. +---------+
  1089. |  3.5000 |
  1090. +---------+
  1091. 1 row in set (0.00 sec)
  1092.  
  1093. mysql> select avg(id), first_name from user group by first_name;
  1094. +---------+------------+
  1095. | avg(id) | first_name |
  1096. +---------+------------+
  1097. |  1.5000 | Joao       |
  1098. |  4.0000 | Maria      |
  1099. |  4.0000 | Carlos     |
  1100. |  6.0000 | marcela    |
  1101. +---------+------------+
  1102. 4 rows in set (0.00 sec)
  1103.  
  1104. mysql> select avg(first_name), first_name from user group by first_name;
  1105. +-----------------+------------+
  1106. | avg(first_name) | first_name |
  1107. +-----------------+------------+
  1108. |               0 | Joao       |
  1109. |               0 | Maria      |
  1110. |               0 | Carlos     |
  1111. |               0 | marcela    |
  1112. +-----------------+------------+
  1113. 4 rows in set, 4 warnings (0.01 sec)
  1114.  
  1115. mysql> select avg(id) as media, first_name from user group by first_name;
  1116. +--------+------------+
  1117. | media  | first_name |
  1118. +--------+------------+
  1119. | 1.5000 | Joao       |
  1120. | 4.0000 | Maria      |
  1121. | 4.0000 | Carlos     |
  1122. | 6.0000 | marcela    |
  1123. +--------+------------+
  1124. 4 rows in set (0.00 sec)
  1125.  
  1126. mysql> select min(id) from user;
  1127. +---------+
  1128. | min(id) |
  1129. +---------+
  1130. |       1 |
  1131. +---------+
  1132. 1 row in set (0.00 sec)
  1133.  
  1134. mysql> select max(id) from user;
  1135. +---------+
  1136. | max(id) |
  1137. +---------+
  1138. |       6 |
  1139. +---------+
  1140. 1 row in set (0.00 sec)
  1141.  
  1142. mysql> select min(id), max(id) from user;
  1143. +---------+---------+
  1144. | min(id) | max(id) |
  1145. +---------+---------+
  1146. |       1 |       6 |
  1147. +---------+---------+
  1148. 1 row in set (0.00 sec)
  1149.  
  1150. mysql>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!