Guides:SQL/How do I/Create and Delete a Table

From CoderGuide

Jump to: navigation, search

Return to the SQL How Do I Index

Creating a table

Tables are created in a database, so we need to first select the database by issuing the USE command, followed by the command to create the database:

USE database_name
CREATE TABLE table_name;

We can also create the table with fields defined in one step:

CREATE TABLE my_table (
  atextfield char(50),
  my_record_ID char(50) NOT NULL,
  PRIMARY KEY (my_record_ID)
 );


The PRIMARY KEY will be what's used to locate the row, and is optimized for searches. For instance, if this were a database of users, the primary would normally be the user's ID. This could easily be a number field or something else. NOT NULL is useful to require that a field has to have some data in it to be accepted. Generally, your primary key would want this. (Why would you have a record with a null primary key anyway? It would be a program bug if there was.)

You can use other types, here are some examples:

CREATE TABLE my_table2 (
  auto_inc_rec INT AUTO_INCREMENT,
  integer_type INT,
  non_neg_int int UNSIGNED,
  varchar_type varchar(15),
  enum_type ENUM('A','B'),
  enum_type2 ENUM('a','b','c'),
  enum_type3 enum('ab','ca'),
  date_field DATE,
  deflts_to int NULL,
  DefWhenNull char(10) NULL DEFAULT 'text',
  PRIMARY KEY (auto_inc_rec)
 );

ENUM lists what values are valid for that field (although, NULL is also valid, unless you specify NOT NULL). If NULL DEFAULT is used, if the field is assigned with a null value, it will take on the default value instead.


You can find a full list of data types at Reference:SQL/Data_Types

You should not use dash when naming the table or database as this will cause you problems later, nor should you use any sql statement names as the table name (such as "database", "table", "select", "where") as that also will be problematic. Oracle implements table called "dual", and, in order to maintain backwards compatibility with Oracle, MySQL also implements the "dual" table, so that is not a name you'd want to use for a table either.

The ANSI standard requires that SELECT contain a FROM clause, yet, some servers allow you to omit this clause in some cases, such as when executing a function by it's self.

SELECT now();            -- Okay in MySQL, but not Oracle
SELECT now() FROM dual;  -- Okay in both MySQL and Oracle (MySQL adds the dual table for compatibility).

Deleting a table

Deleting (removing) a table is just as easy as deleting a database:

USE database_name
DROP TABLE table_name;

Don't forget to use quit; command if you want to exit the MySQL client!

Now, if you want a list of all tables in your database, you can use:

USE database_name;
SHOW TABLES;

If you ever want to find out information about one of your tables, use:

DESCRIBE my_table2;

If the value's won't fit on your screen, end your command with \G instead of a simicolon or \g (case matters in SQL):

DESCRIBE my_table2\G

If you're using Microsoft's SQL server, see Guides:SQL/How_do_I/Get_Database_and_Table_Information_in_a_Microsoft_SQL_Server.

Naturally, you only need to use the USE database command once to change the database, and only again if you need to access a different database.

Personal tools