Tutorial Addendum on SQL - Data Analogue Statements

This affiliate describes data analogue statements:

    

 

    
CREATE TABLE ...;

    
DROP TABLE ...;

    
CREATE Basis ...;

    
DROP Basis ...;

    
ALTER TABLE ...;

    

Create Table Statements

    

The make table account allows you to make a new table in the database.

    

It has a amount of syntax formats:

    

1. To make a abiding table:

    

    
CREATE TABLE tbl_name (column_list); 

    

where "column_list" defines the columns of the table with the afterward syntax:

    

    
col_name col_type col_options,

    
col_name col_type col_options,

    
...

    
col_name col_type col_options

    

2. To make a acting table:

    

    
CREATE Acting TABLE tbl_name (column_list); 

    

3. To make a abiding table if it doesn t exist:

    

    
CREATE TABLE tbl_name IF NOT EXISTS (column_list); 

    

4. To make a abiding table with the achievement data types and data of a baddest statement:

    

    
CREATE TABLE tbl_name select_statement;

    

To appearance the columns of an absolute table, you can use the appearance columns statement:

    

    
SHOW COLUMNS FROM tbl_name;

    

To annul an absolute table, you can use the bead table statement:

    

    
DROP TABLE tbl_name;

    

Here is an archetype SQL code, CreateTable.sql, assuming you how to make a table

    

by selecting data from absolute tables:

    

 

    
-- CreateTable.sql

    
-- Absorb (c) 1999 by Dr.  Yang

    
--

    
CREATE TABLE IF NOT EXISTS User (Login VARCHAR(8), Countersign CHAR(8));

    
INSERT INTO User Ethics (  , 8IS3KOXW );

    
INSERT INTO User Ethics ( mike , PZ0JG );

    
SELECT  User table: ;

    
SHOW COLUMNS FROM User;

    
SELECT * FROM User;

    
--

    
CREATE TABLE IF NOT EXISTS UserCopy Baddest * FROM User;

    
SELECT  UserCopy table: ;

    
SHOW COLUMNS FROM UserCopy;

    
SELECT * FROM UserCopy;

    
--

    
CREATE TABLE IF NOT EXISTS UserDump 

    
   Baddest CONCAT(Login, : ) AS Login, CHAR_LENGTH(Password) AS Calculation 

    
   FROM User;

    
SELECT  UserCopy table: ;

    
SHOW COLUMNS FROM UserDump;

    
SELECT * FROM UserDump;

    
--

    
DROP TABLE User;

    
DROP TABLE UserCopy;

    
DROP TABLE UserDump

    

If you run the code, you will get:

    

 

    
User table:

    
User table:

    
Field    Blazon       Absent    Key     Absence Extra

    
Login    varchar(8) YES     NULL

    
Password varchar(8) YES     NULL

    
Login   Password

    
  8IS3KOXW

    
mike    PZ0JG

    
UserCopy table:

    
UserCopy table:

    
Field    Blazon       Absent    Key     Absence Extra

    
Login    varchar(8) YES    

    
Password varchar(8) YES    

    
Login   Password

    
  8IS3KOXW

    
mike    PZ0JG

    
UserDump table:

    
UserDump table:

    
Field   Blazon    Absent    Key     Absence Extra

    
Login   char(9) YES     

    
Count   int(10) YES     

    
Login   Count

    
: 8

    
mike:   5

    

A brace of absorbing addendum on the output:

    

    
• The appearance columns account letters that mysql sets all columns to be capricious
  
      

  length, if one cavalcade is capricious length.
  
      



    
• The achievement of the UserCopy table shows that the make table account with baddest
  
      

  sub-statement works perfectly.
  
      



    
• The achievement of the UserDump table shows that the columns types are based the data
  
      

  types of the achievement of the baddest sub-statement, if it acclimated in the make table statement.