MySQL table operations
Note: In order to perform operations on a database, you will need access to a database.
- Please see the notes on accessing MySQL on the i6 server used in this course.
Create a table
- CREATE TABLE
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 );
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,
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
- SHOW TABLES - lists all the tables in a given database
- DESCRIBE - outputs the properties of all of its fields in the given table
#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
- ALTER TABLE - modifies a table
#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
- DROP TABLE - completely removes it from the database
- TRUNCATE TABLE - deletes the data stored in the table, but otherwise preserves the table structure
#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!