Speaking SQL (part 1) - Changing Things Around

(Page 5 of 7 )

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>
<modifiers>

---

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
(NULL,
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
(NULL,
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.

Next: Termination With Extreme Prejudice >>
 

More MySQL Articles
More By icarus, (c) Melonfire

Comments On: Speaking SQL (part 1)

>>> Be the FIRST to comment on this article!