Jump to content
Sign in to follow this  
saramgsilva

dúvidas de sql usando o mysql

Recommended Posts

saramgsilva

boas, como alguns ja repararam eu uso o mysql server e tb o wamp pra trabalhar com o sql, e tou a fazer um projecto de base de dados.

Quando estava a ciar um dos codigos, reparei que nao conseguia usar o except, intersect...os que consegui usar foram o union, o in, not in ...pretendia saber se o mysql nao reconhece o intersect e except...e se souberem de mais alguma digam... ;)

Share this post


Link to post
Share on other sites
perdido_e_sozinho

Penso que a melhor forma de saberes os comandos suportados pelo mysql será usares o MySQL Query Browser, uma vez que este possui uma lista de todos os comandos, além disso em conjunto com o MySQL Administrator permite gerir todo a base de dados de mysql.

Estes dois programas poderão ser encontrados na página oficial do mysql.

Com os melhores cumprimentos,

Sérgio Matias


ser.gifsartim.gif

Share this post


Link to post
Share on other sites
saramgsilva
Penso que a melhor forma de saberes os comandos suportados pelo mysql será usares o MySQL Query Browser, uma vez que este possui uma lista de todos os comandos, além disso em conjunto com o MySQL Administrator permite gerir todo a base de dados de mysql.

Estes dois programas poderão ser encontrados na página oficial do mysql.

Com os melhores cumprimentos,

Sérgio Matias

pois eu penso que tb saquei isso e instalei, mas nao consegui fazer nada...pois nao percebi como ia fazer a ligacao...hummm ok vou dar 1 vista de olhos...mas tenho a impressao que nao suporta o intersect nem o except... obrigada ...

Share this post


Link to post
Share on other sites
saramgsilva

bem eu ja tenho o MySQL Administrator, MySQL Query a funcionar...tenho k me ligar ao root@localhost, user root e a pass (é a k defeni qdo instalei o MySQL Server)...ja vi que da pra criar + useres... :D bem em relacao aquilo k falaste, nao resolveu mto o problema, pois nem sequer fala da union...fala de outras coisas...mas obrigada... ;)

Share this post


Link to post
Share on other sites
perdido_e_sozinho

Union

14.1.7.2 UNION Syntax

SELECT ...

UNION [ALL | DISTINCT]

SELECT ...

[uNION [ALL | DISTINCT]

SELECT ...]

UNION is used to combine the result from many SELECT statements into one result set. UNION is available from MySQL 4.0.0 on.

Selected columns listed in corresponding positions of each SELECT statement should have the same type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.) The column names used in the first SELECT statement are used as the column names for the results returned.

The SELECT statements are normal select statements, but with the following restrictions:

* Only the last SELECT statement can have INTO OUTFILE.

* HIGH_PRIORITY cannot be used with SELECT statements that are part of a UNION. If you specify it for the first SELECT, it has no effect. If you specify it for any subsequent SELECT statements, a syntax error results.

If you don't use the keyword ALL for the UNION, all returned rows will be unique, as if you had done a DISTINCT for the total result set. If you specify ALL, you will get all matching rows from all the used SELECT statements.

The DISTINCT keyword is an optional word (introduced in MySQL 4.0.17). It does nothing, but is allowed in the syntax as required by the SQL standard.

Before MySQL 4.1.2, you cannot mix UNION ALL and UNION DISTINCT in the same query. If you use ALL for one UNION, it is used for all of them. As of MySQL 4.1.2, mixed UNION types are treated such that a DISTINCT union overrides any ALL union to its left. A DISTINCT union can be produced explicitly by using UNION DISTINCT or implicitly by using UNION with no following DISTINCT or ALL keyword.

If you want to use an ORDER BY to sort the entire UNION result, you should use parentheses:

(SELECT a FROM tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)

UNION

(SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10)

ORDER BY a;

The types and lengths of the columns in the result set of a UNION take into account the values retrieved by all the SELECT statements. Before MySQL 4.1.1, a limitation of UNION is that only the values from the first SELECT are used to determine result column types and lengths. This could result in value truncation if, for example, the first SELECT retrieves shorter values than the second SELECT:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);

+---------------+

| REPEAT('a',1) |

+---------------+

| a |

| b |

+---------------+

That limitation has been removed as of MySQL 4.1.1:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);

+---------------+

| REPEAT('a',1) |

+---------------+

| a |

| bbbbbbbbbb |

+---------------+

Isto está no separador sintax se clicares no SELECT, e fizeres scoll até ao 14.1.7.2 UNION Syntax.

Nesta pasta poderás alguma informação que penso ser útil para ti.

C:\Programas\MySQL\MySQL Query Browser 1.1\doc

14.1.8.3 Subqueries with ANY, IN, and SOME

Syntax:

operand comparison_operator ANY (subquery)

operand IN (subquery)

operand comparison_operator SOME (subquery)

The ANY keyword, which must follow a comparison operator, means ``return TRUE if the comparison is TRUE for ANY of the rows that the subquery returns.'' For example:

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

Suppose that there is a row in table t1 containing (10). The expression is TRUE if table t2 contains (21,14,7) because there is a value 7 in t2 that is less than 10. The expression is FALSE if table t2 contains (20,10), or if table t2 is empty. The expression is UNKNOWN if table t2 contains (NULL,NULL,NULL).

The word IN is an alias for = ANY. Thus these two statements are the same:

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

However, NOT IN is not an alias for <> ANY, but for <> ALL. See section 14.1.8.4 Subqueries with ALL.

The word SOME is an alias for ANY. Thus these two statements are the same:

SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

Use of the word SOME is rare, but this example shows why it might be useful. To most people's ears, the English phrase ``a is not equal to any b'' means ``there is no b which is equal to a,'' but that isn't what is meant by the SQL syntax. Using <> SOME instead helps ensure that everyone understands the true meaning of the query.

Desculpem o copy paste


ser.gifsartim.gif

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.