Home arrow MySQL arrow Page 5 - Speaking SQL (part 1)

Changing Things Around - MySQL

Structured Query Language is the language used to communicatewith databases of all shapes, sizes and varieties. If you're building a Webapplication which needs to communicate with a database, and don't knowwhere to start, this article will get you up to speed on the basics ofcreating tables and inserting data into them.

  1. Speaking SQL (part 1)
  2. Turntables And Records
  3. Relationships
  4. Not Your Type?
  5. Changing Things Around
  6. Termination With Extreme Prejudice
  7. Old Data For New
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 7
December 21, 2000

print this article


There are a few additional constraints ("modifiers") you can put on your table, so as to increase the consistency of the data that will be entered into it.

You can specify whether the field is allowed to be empty, or must necessarily be filled with data by placing the NULL and NOT NULL modifiers after each field definition, like this:

CREATE TABLE members ( member_id int(11) NOT NULL auto_increment, fname varchar(50) NOT NULL, lname varchar(50) NOT NULL, tel varchar(15), email varchar(50) NOT NULL, PRIMARY KEY (member_id) );

You can specify a primary key for the table with the PRIMARY KEY definition, which is followed by the name of the column designated as the primary key.

You can specify that values entered into a field must be "unique" - that is, not duplicated - with the UNIQUE modifier.

The AUTO_INCREMENT modifier, which is only available for numeric fields, indicates that MySQL should automatically generate a number for that field (by incrementing the previous value by 1).

You can alter a table definition with the ALTER TABLE command, which looks like this:

ALTER TABLE <table_name> ADD <new_field_name> <new_field_type>

or, if you simply want to modify an existing column

ALTER TABLE <table_name> MODIFY <old_field_name> <new_field_type>

And just as you can create a table, you can delete a table with the DROP TABLE command; it looks like this:
DROP TABLE <table_name>

For example,

mysql> DROP TABLE members;
Query OK, 0 rows affected (0.00 sec)

This will immediately wipe out the specified table, together with all the data it contains - so use it with care!{mospagebreak title=Garbage In, Garbage Out} Once you've created a table, it's time to begin entering data into it - and the SQL command to accomplish this is the INSERT command. The syntax of the INSERT command is as follows:
INSERT into <table_name (field_name_1, field_name2, field_name_n) VALUES
(value_1, value_2, value_n)

Here's an example:

mysql> INSERT INTO members (member_id, fname, lname, tel, email) VALUES
John', 'Doe', '1234567', 'jdoe@somewhere.com');
Query OK, 1 row affected (0.06 sec)

I could also use the abbreviated form of the INSERT statement, in which field names are left unspecified.

mysql> INSERT INTO members VALUES (NULL,
John', 'Doe', '1234567', 'jdoe@somewhere.com');
Query OK, 1 row affected (0.06 sec)

The flip side: by specifying field names in the INSERT statement, I have the flexibility of inserting values in any order I please. The statements below are equivalent.

mysql> INSERT INTO members (member_id, fname, lname, tel, email) VALUES
John', 'Doe', '1234567', 'jdoe@somewhere.com');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO members (fname, lname, email, tel, member_id) VALUES
('John', 'Doe', 'jdoe@somewhere.com', '1234567', NULL);
Query OK, 1 row affected (0.00 sec)

Fields which are not specified will automatically be set to their default values.

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: