MySQL table operations

From Knowledge Kitchen
Jump to navigation Jump to search


Note: In order to perform operations on a database, you will need access to a database.


Create a table

Commands

  • CREATE TABLE

Examples

1 #the following query creates a table named 'viking_metal_bands' in the current database, and specifies all of the table's fields, including which one is the primary key.
2 CREATE TABLE viking_metal_bands (
3 	 id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
4 	 band VARCHAR(50) NOT NULL,
5 	 formed INT(4) NOT NULL,
6 	 origin VARCHAR(50) NOT NULL,
7 	 created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
8 	 PRIMARY KEY (id)
9 );

Primary keys

One field in every table must be the designated "primary key".

  • The value in the primary key field for a given record is that record's unique identifier
  • No two records can have the same value in the primary key field
  • If a given data set does not have a good candidate for primary key (i.e. no field is guaranteed to have a unique value), you can have MySQL automatically create a unique integer for each row inserted into your table.
    • In the example command above, the "id" field is created as an automatically-incrementing integer, and is also set as the primary key. When a new row is inserted into this table, this field will automatically be assigned a unique integer value in that field.
3 	 id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,

Timestamps

It is often very useful to keep a record of the date and time when any given record was inserted into the table.

  • a field of the type TIMESTAMP can have the current date and time automatically entered into it when a new row is inserted
7 	 created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

Read information about a table

Commands

  • SHOW TABLES - lists all the tables in a given database
  • DESCRIBE - outputs the properties of all of its fields in the given table

Examples

#the following query will output a list of all of the tables in the current database
SHOW TABLES;
#the following query will output all of the fields of the current database
DESCRIBE viking_metal_bands;

Update a table

Commands

  • ALTER TABLE - modifies a table

Examples

#the following query will add a new column to the table
ALTER TABLE viking_metal_bands ADD is_awesome BOOLEAN NOT NULL DEFAULT FALSE;
#the following query will modify a column in the table
ALTER TABLE  viking_metal_bands CHANGE band band_name VARCHAR(100);
#modify it again!
ALTER TABLE  viking_metal_bands CHANGE band_name band VARCHAR(150);
#the following query will rename the table
ALTER TABLE viking_metal_bands RENAME favorite_viking_metal_bands;
#the following query will delete the given column from the table
ALTER TABLE favorite_viking_metal_bands DROP COLUMN is_awesome;

Delete a table

Commands

  • DROP TABLE - completely removes it from the database
  • TRUNCATE TABLE - deletes the data stored in the table, but otherwise preserves the table structure

Examples

#the following query will completely remove the table from the database
DROP TABLE favorite_viking_metal_bands;
#the following query will wipe out the data from the table, but keep the structure of the table
TRUNCATE TABLE favorite_viking_metal_bands;

Save data to the table

Once a table is created, it is time to create, read, update, and delete (CRUD) records in it!


What links here