mysql> select * from tabela3;
+--------+--------+-------+------------+
| nome | codigo | idade | data |
+--------+--------+-------+------------+
| gilmar | 1 | 24 | 2011-10-10 |
+--------+--------+-------+------------+
1 row in set (0.00 sec)
mysql> select * from tabela2;
+-----------+-------+--------+------------+
| nome1 | idade | codigo | data |
+-----------+-------+--------+------------+
| higor | 20 | 3 | 2011-10-10 |
| alexandre | 20 | 4 | 2011-10-19 |
+-----------+-------+--------+------------+
2 rows in set (0.00 sec)
mysql> select * from tabela1;
+--------+-------+--------+------------+
| nome | idade | codigo | data |
+--------+-------+--------+------------+
| andre | 20 | 3 | 2011-10-18 |
| murilo | 20 | 4 | 2011-10-19 |
+--------+-------+--------+------------+
2 rows in set (0.00 sec)
Query = select the table 2 that has the date equal to the 'gilmar' table 3.
From the date of the selected second table [ Table 1 lists all of whom date greater than the selected date from table 2 ]
Selecione na 2 tabela que tem a data igual a do 'gilmar' da 3 tabela.
A parti da data selecionada da 2 tabela [ liste todos da 1 tabela quem a data maior que a data selecionada da tabela 2 ]
mysql> select * from tabela1 where data > (select data from tabela2 where data = (select data from tabela3 where nome='gilmar'));
+--------+-------+--------+------------+
| nome | idade | codigo | data |
+--------+-------+--------+------------+
| andre | 20 | 3 | 2011-10-18 |
| murilo | 20 | 4 | 2011-10-19 |
+--------+-------+--------+------------+
2 rows in set (0.00 sec)
Criando uma nova tabela,
Inserindo valores
mysql> create table tabela4 (nome varchar(45), codigo int primary key auto_increment, idade int, data date);
Query OK, 0 rows affected (0.21 sec)
mysql> insert into tabela4 (nome, idade, data) values ('gilmar', 24, '2011-10-10');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tabela4 (nome, idade, data) values ('ediel', 24, '2011-10-11');
Query OK, 1 row affected (0.00 sec)
Query = same query a table with the most (Table 4)
mysql> select * from tabela1 where data > (select data from tabela2 where data = (select data from tabela3 where nome=(select nome from tabela4 where nome='gilmar')));
+--------+-------+--------+------------+
| nome | idade | codigo | data |
+--------+-------+--------+------------+
| andre | 20 | 3 | 2011-10-18 |
| murilo | 20 | 4 | 2011-10-19 |
+--------+-------+--------+------------+
2 rows in set (0.00 sec)
select * from tables [ tabela (1..4)]# -> invalid
mysql> select * from tabela1;
+--------+-------+--------+------------+
| nome | idade | codigo | data |
+--------+-------+--------+------------+
| andre | 20 | 3 | 2011-10-18 |
| murilo | 20 | 4 | 2011-10-19 |
+--------+-------+--------+------------+
2 rows in set (0.00 sec)
mysql> select * from tabela2;
+-----------+-------+--------+------------+
| nome1 | idade | codigo | data |
+-----------+-------+--------+------------+
| higor | 20 | 3 | 2011-10-10 |
| alexandre | 20 | 4 | 2011-10-19 |
+-----------+-------+--------+------------+
2 rows in set (0.00 sec)
mysql> select * from tabela3;
+--------+--------+-------+------------+
| nome | codigo | idade | data |
+--------+--------+-------+------------+
| gilmar | 1 | 24 | 2011-10-10 |
+--------+--------+-------+------------+
1 row in set (0.01 sec)
mysql> select * from tabela4;
+--------+--------+-------+------------+
| nome | codigo | idade | data |
+--------+--------+-------+------------+
| gilmar | 1 | 24 | 2011-10-10 |
| ediel | 2 | 24 | 2011-10-11 |
+--------+--------+-------+------------+
2 rows in set (0.00 sec)
Query = Selection of different tables with the same attribute (exists)
mysql> select * from tabela3 where exists (select nome from tabela4 where tabela4.nome = tabela3.nome);
+--------+--------+-------+------------+
| nome | codigo | idade | data |
+--------+--------+-------+------------+
| gilmar | 1 | 24 | 2011-10-10 |
+--------+--------+-------+------------+
1 row in set (0.00 sec)
try to understand !!!
mysql> select * from tabela1 where data > (select data from tabela2 where data = (select data from tabela3 where nome=(select nome from tabela4 where nome=(select nome from tabela3 where exists (select nome from tabela4 where tabela4.idade = tabela3.idade)))));
+--------+-------+--------+------------+
| nome | idade | codigo | data |
+--------+-------+--------+------------+
| andre | 20 | 3 | 2011-10-18 |
| murilo | 20 | 4 | 2011-10-19 |
+--------+-------+--------+------------+
2 rows in set (0.00 sec)
mysql> create table tabela5 (nome varchar(45), codigo int primary key auto_increment, idade int, data date);Query OK, 0 rows affected (0.51 sec)
mysql> insert into tabela5 (nome, idade, data) values ('andre', 20, '2011-10-18');Query OK, 1 row affected (0.00 sec)
mysql> insert into tabela5 (nome, idade, data) values ('murilo', 20, '2011-10-19');
Query OK, 1 row affected (0.00 sec)
mysql> desc tabela1;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| nome | varchar(45) | YES | | NULL | |
| idade | int(11) | YES | | NULL | |
| codigo | int(11) | NO | PRI | NULL | auto_increment |
| data | date | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc tabela5; # paste table 1 , next post continues ...
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| nome | varchar(45) | YES | | NULL | |
| codigo | int(11) | NO | PRI | NULL | auto_increment |
| idade | int(11) | YES | | NULL | |
| data | date | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_aula1 |
+-----------------+
| tabela1 |
| tabela2 |
| tabela3 |
| tabela4 |
+-----------------+
4 rows in set (0.00 sec)
mysql> Bye
0 comentários:
Post a Comment