Dyntex.net Support Centre: MySQL Reference Manual p.35

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

Chapter 1: General Information 35 SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id; The queries: SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2); SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 WHERE table1.id=table2.id); Can be rewritten as: SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; For more complicated subqueries you can often create temporary tables to hold the sub- query. In some cases, however, this option will not work. The most frequently encountered of these cases arises with DELETE statements, for which standard SQL does not support joins (except in subselects). For this situation there are two options available until subqueries are supported by MySQL Server. The rst option is to use a procedural programming language (such as Perl or PHP) to submit a SELECT query to obtain the primary keys for the records to be deleted, and then use these values to construct the DELETE statement (DELETE FROM ... WHERE ... IN (key1, key2, ...)). The second option is to use interactive SQL to construct a set of DELETE statements auto- matically, using the MySQL extension CONCAT() (in lieu of the standard || operator). For example: SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', "'", tab1.pkid, "'", ';') FROM tab1, tab2 WHERE tab1.col1 = tab2.col2; You can place this query in a script le and redirect input from it to the mysql command-line interpreter, piping its output back to a second instance of the interpreter: shell> mysql --skip-column-names mydb < myscript.sql | mysql mydb MySQL Server 4.0 supports multi-table deletes that can be used to eciently delete rows based on information from one table or even from many tables at the same time. 1.7.4.2 SELECT INTO TABLE MySQL Server doesn't yet support the Oracle SQL extension: SELECT ... INTO TABLE .... MySQL Server supports instead the ANSI SQL syntax INSERT INTO ... SELECT ..., which is basically the same thing. See Section 6.4.3.1 [INSERT SELECT], page 456. INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100; Alternatively, you can use SELECT INTO OUTFILE... or CREATE TABLE ... SELECT. 1.7.4.3 Transactions and Atomic Operations MySQL Server supports transactions with the InnoDB and BDB Transactional table handlers. See Chapter 7 [Table types], page 494. InnoDB provides ACID compliancy.

MySQL Reference Manual

Our Partners

SpiritHost - web hosting for spiritual and education sites
SpiritHit.com - Religious and Spiritual Portal

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide