MySQL Insert and Select Data

must must A database is useless if you cannot add and retrieve data. This tutorial will cover how to insert and retrieve data. To insert data you use the following syntax:

INSERT INTO table_name (Column Name, Column Name, Column Name) VALUES (Value 1, Value 2, Value 3);

This syntax uses the column list, which is highly recommended because when you use PHP and MySQL it is likely that your queries will use the column list. The other method is:

INSERT INTO table_name VALUES (Value 1, Value 2, Value 3);

The number of listed values must match the number of columns in the table, and they must also match the data type. In our Games table we have columns ID, gName, gYear, and gRating, so you cannot put:

INSERT INTO Games VALUES ("Left for Dead 2", "A", "B");

This is not allowed because the last two columns only accept integer values, not strings. You don’t need to specify the ID column because that is auto incremented.

Example – Insert Data

Make a new file in Notepad++ and name it insert.sql. Next copy this:

INSERT INTO Games (gName, gYear, gRating) VALUES ("Left For Dead 2", 2009, 10);
INSERT INTO Consoles (cName, cManufacturer, cYear) VALUES ("Xbox 360", "Microsoft", 2005);

This will insert data into both of our tables at the same time. In the CLC use the source command to execute this query.

Selecting Data

You can read data from the database using the SELECT command.

Syntax

SELECT * FROM table_name;

The asterisk means select all the columns. If you want to select specific columns you insert the column names like this:

SELECT column 1, column 2 FROM table_name;

Example

In the CLC type:

SELECT * FROM Games;

You should see the entry we entered before.

Where Clause

The Where Clause (keyword) filters out results based another value. For example, the query can look like this:

SELECT * FROM Games WHERE gName= 'GTA 4';

This will get all values from the data where the column gName has values GTA 4. You can use comparison operators with the Where Clause.

Operator	Description
<	Less than
>	More than
<=	Less than or equal to
>=	More than or equal to
!=	Not equal to
=	Equals

For more information visit http://dev.mysql.com/doc/refman/5.0/en///comparison-operators.html

Example

SELECT * FROM Consoles WHERE cYear > 2005;

Order By

You can order data alphabetically when retrieving it.

Syntax

SELECT * FROM table_name Order By Column_name;

This will output the results alphabetically. You can use the DESC keyword to reverse the order:

SELECT * FROM table_name Order By Column_name DESC;

Example

SELECT * FROM Games Order By gName DESC;