Basic SQL Queries
Now that you've set up your first table, you can practice some of the basic SQL queries using the command-line interface of the MySQL server. These exercises will help you get comfortable with basic SQL queries.
First, let's talk about the structure of your new table before we start entering data into it. If you want to see the structure of a particular table, you can ask MySQL to describe it by simply issuing the command describe.
Type:
mysql> describe news;
and the server displays the description of your news table
+--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | news_id | int(11) | | PRI | 0 | auto_increment | | heading | varchar(48) | YES | | NULL | | | body | text | YES | | NULL | | | date | date | YES | | NULL | | | author_name | varchar(48) | YES | | NULL | | | author_email | varchar(48) | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)
The table that MySQL returns from the describe command gives you all the relevant details of your table.
Here is a brief explanation of the output from the describe command:
Each of the Fields holds your data.
The Type of field is what kind of data that particular field can hold.
If the field has YES in its Null column, then it can be empty for any row on the table.
If the field has PRI in the Key column, then that field acts as an index of sorts for the table. No two values can be the same in any row of a table that is the primary key.
The Default column describes what values, by default, are entered into the field.
Finally, the Extra column describes any extra attributes a row might have, such as auto_increment, which increments your primary key by 1 every time you add a new row into the table.
mysql> INSERT INTO news -> VALUES(NULL,'A Heading','The Body', -> '08-16-2000','Chris C.', -> 'chris@domain.com');
The server responds with:
Query OK, 1 row affected (0.00 sec)
Now let's query the database and see how it looks in the table by issuing a select statement. The * operator works just like a standard UNIX wildcard.
Type the following at the MySQL prompt:
mysql> select * from news;
The server responds with:
+---------+-----------+----------+------------+-------------+------------------+ | news_id | heading | body | date | author_name | author_email | +---------+-----------+----------+------------+-------------+------------------+ | 1 | A Heading | The Body | 0000-00-00 | Chris C. | chris@domain.com | +---------+-----------+----------+------------+-------------+------------------+ 1 row in set (0.06 sec)
Modifying Data
Now what if you entered something incorrectly and you want to modify it? Let's look at the above output. Notice anything strange? The date is listed as 0000-00-00. And no, that isn't some kind of Y2K bug. It's because we didn't enter the date into the table using the correct format. We tried to enter it as 08-16-2000 when we should have entered 2000-08-16. The date type of data in MySQL won't accept a value that doesn't fit the required parameters. The required parameters state that the date must be a four-digit year, followed by a dash, followed by a two-digit month, followed by a dash, followed by a two-digit day.
To correct the error, you can issue the update command. Type the following at the MySQL prompt:
mysql> update news set date='2000-08-16' where news_id='1';
The server responds:
Query OK, 1 row affected (0.00 sec)
Just to make sure everything is correct, let's issue the select command again. Type the following at the MySQL prompt: mysql> select * from news;
The server responds:
+---------+-----------+----------+------------+-------------+------------------+ | news_id | heading | body | date | author_name | author_email | +---------+-----------+----------+------------+-------------+------------------+ | 1 | A Heading | The Body | 2000-08-16 | Chris C. | chris@domain.com | +---------+-----------+----------+------------+-------------+------------------+ 1 row in set (0.06 sec)
And as you can see from the output, the date is now entered correctly.
Deleting Data
Now that you have learned how to insert, retrieve, and modify data in your database, the last basic command you need to know is the delete command.
To delete a row in your table, type this command at the MySQL prompt:
mysql> delete from news where news_id='1';
The server responds:
Query OK, 1 row affected (0.00 sec)
Now do a select statement and see what the table looks like.
mysql> select * from news;
The server responds with:
Empty set (0.00 sec)
MySQL returns "Empty set" because the criteria you used for the select statement (the * operator) returned no values, so this table is empty. If you had used some form of criteria, such as select * from news where news_id='5', an empty set might also be returned, but there could still be data in the table. For example, the table could still contain data where news_id equals 2, or 3, or 4. But since you asked for everything in the table, and nothing was returned, you know the table is empty.
The delete command simply deletes a row (or rows) that meets the criteria. In this case you deleted the row from your table that had a news_id of 1. You could have also chosen to delete rows that had an author_name of Chris C., which could delete multiple rows. If the data had hundreds of rows, we could delete them all by using a command such as delete from news where news_id > '0';.