Stephen's dev blog

MySQL basic reference

Posted on: 6 August, 2008

A couple of colleagues have suggested putting a basic reference to some SQL commands together. Instead of having to reference different tutorials in different places around the web.

Of course there are a large number of tutorials around for this but again, this is just for reference.

Also, a lot of these commands will work for other database engines which support SQL, but I mainly work with MySQL 🙂

I might possibly add a more advanced reference at a later date.

Creating a table

CREATE TABLE `table_name`
(
    `id` INT(11) AUTO_INCREMENT NOT NULL,
    `field_1` VARCHAR(50) NOT NULL,
    `field_2` VARCHAR(255) NOT NULL,
    `field_3` TEXT,
    PRIMARY KEY (`id`)
)

So the lines for adding fields are in the format:

`field_name` FIELD_TYPE EXTRA_OPTIONS

You can get a list of all supported field types in MySQL here.

Extra options that I’ve used here are;

AUTO_INCREMENT – This tells MySQL that this field is going to be this table’s auto incremented field. This means the field will go up by one for each new row. There can only be one auto incremented field per table. This is usually used for primary keys / row identification fields.

NOT NULL – This tells MySQL to not default to NULL values for these fields as there will always be a value inserted here.

I’ve also added a PRIMARY KEY instruction to this query. It’s good practice for every table to have a primary key, even if you see no obvious use for one. This is usually an id field as shown above. To have a field set as auto incremented, it must also be a primary key, otherwise MySQL will throw an error.

Removing or emptying a table

DROP TABLE `table_name`;

DELETE FROM `table_name`;

TRUNCATE TABLE `table_name`;

DROP TABLE will completely remove the table from the database. It will be as if this table never existed and it will have to be created to be used again. I’ve only really had to use DROP TABLE when I’ve made a mistake in creating the table and it’s easier to completely remove it and re-create it instead of amending it.

DELETE FROM `table` will do exactly that. It will delete all rows from the table but leave the table structure in tact. This is useful for a quick clean up of something like test entries but keep in mind that an auto incremented field will keep the last auto incremented value after this query. I.E. if you had 400 rows and you run a DELETE query as shown above, the next time you enter a row into this table, the auto incremented field value will be 401, not 1.

TRUNCATE TABLE does the same as DELETE FROM except that instead of deleting all records, it resets the table. I.E. all rows are removed and auto incremented field value is reset to 0.

Amending a table

ALTER TABLE `table_name` RENAME `new_table_name`;

ALTER TABLE `table_name` ADD `new_field` FIELD_TYPE OPTIONS [FIRST/AFTER `field_name`];

ALTER TABLE `table_name` CHANGE `field_name` `new_field_name` NEW_FIELD_TYPE NEW_OPTIONS;

The first query simple renames a table to a different table name.

The second query is used to add new fields to an existing table. The FIELD_TYPE and OPTIONS can be the same as noted above. You can use the FIRST/AFTER part to add the field to a particular place in the table, I.E.:

ALTER TABLE `table_name` ADD `field_name` VARCHAR(255) NOT NULL AFTER `id`;
ALTER TABLE `table_name` ADD `another_field_name` VARCHAR(255) NOT NULL FIRST;

The third query can be used to alter an existing field’s values. I.E. you want to change a field from VARCHAR(255) NOT NULL to VARCHAR(50).

There is also a way to re-arrange the column order in a table but this isn’t every really needed. If you would really like to do it, you can read more about it here.

Insert, updating and removing specific rows

INSERT INTO `table_name` VALUES ('value_1', 'value_2', 'value_3','value_4');

UPDATE `table_name` SET `field_name` = 'new value', `another_field_name` = 'another new value' WHERE `id` = 56;

DELETE FROM `table_name` WHERE `id` = 56;

The first query is used to insert a new record into a table. If you insert a new record this way, without specifying which fields you are giving values for, you HAVE to give values for every field. If you only want to insert values for certain fields, you can use the following query:

INSERT INTO `table_name` (`field_1`, `field_2`, `field_5`) VALUES ('field_1_value', 'field_2_value', 'field_3_value');

If you want to insert multiple rows at once you can have a comma separated list of values, I.E.:

INSERT INTO `table_name` VALUES ('value_1', 'value_2', 'value_3','value_4'),('value_1', 'value_2', 'value_3','value_4'),('value_1', 'value_2', 'value_3','value_4');

INSERT INTO `table_name` (`field_1`, `field_2`, `field_5`) VALUES ('field_1_value', 'field_2_value', 'field_3_value'),('field_1_value', 'field_2_value', 'field_3_value'),('field_1_value', 'field_2_value', 'field_3_value');

If you are inserting a value for an auto incremented field, you should enter NULL.

The UPDATE query above is for updating an existing row, it consists of a comma separated list of field = value pairs and a WHERE clause to specify which row(s) to update. The list of field = value pairs can contain as many fields as you want.

The DELETE query above is the same as the one mentioned before for emptying a table except it now has a WHERE clause which is used to delete one selection of rows.

1 Response to "MySQL basic reference"

Comments are closed.


  • Patrick: I changed my code, but now I have another problem: Fatal error: Call to a member function isAvailable() on a non-object in /var/www/vhost/web/shopA
  • Stephen Gray: Hi Patrick, That first error is my fault. I had the method name for the refund() method as void() as I had copied the code from the other method!
  • Patrick: Hi Stephen, thanks for sharing your knowledge about creating a custom payment module. I need an extension for magento to handle a credit card payme

Categories

%d bloggers like this: