Additional SQL

MySQL Indexes SLIDES 1-2

MySQL Data Types SLIDES 3-7

Creating Table structures SLIDES 8-11

ALTERing Table structures SLIDES 12-14

Data Manipulation SLIDES 15-18

Auto Increment SLIDE 19

Procedural SQL (review) SLIDE 20-21

  • Variables SLIDE 22
  • Conditional execution SLIDE 23
  • Iteration or looping SLIDES 24-25

SELECT with Cursors SLIDES 26-30

Temporary TABLEs

A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed. Here, a session begins when you connect to the database server and ends when you disconnect.

Thus, two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name (the existing table is hidden until the temporary table is dropped). For example, an application used by all managers in a department can be shared even if it creates a TEMPORARY MySQL Table during its execution because it is session-based and the managers don’t share sessions.

CREATE TEMPORARY TABLE tmpCustomer 
		(customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY, ... );

They will sometimes be created from existing tables:

CREATE TEMPORARY TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;

[!Note]

A TEMPORARY table will not be shown by a SHOW TABLES command, as TEMPORARY tables are never stored and SHOW TABLES shows all stored tables.