• Choose a category
• All
• Classic-Fiction

By Root 1421 0

SELECT, INSERT, UPDATE, DELETE, and DROP operations, but only on the database called test (and only when connected from the local machine):

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON test.* TO gonzo@localhost

IDENTIFIED BY 'gonzo_passwd';

mysql>FLUSH PRIVILEGES;

If you haven't worked with SQL databases before, these operations will probably not make much sense to you. Since you are going to need to use them anyway when setting up your LAMP system, we might as well shortly describe them here:

SELECT

This is the most commonly used SQL command. It queries the database for data with certain properties—for example, you could ask for all customers in a certain town. SELECT never changes anything in the database.

INSERT

This SQL command inserts new records into a database table. You use this (either interactively or, more likely, as part of a program) to insert a customer record into the customer table in your database, for example.

UPDATE

This SQL command changes existing records in a database. You could use this to, for example, increase the retail prices of all articles in the database by 15%. (Talk about inflation!)

DELETE

This SQL command deletes entire records from the database. Be careful with this command, as there is no way of restoring the data short of restoring from a (hopefully available) backup tape.

There are even more SQL commands and corresponding privileges (such as DROP, which lets you delete entire tables or even entire databases), but these are used less often than the "big four" listed here.

Now we want to create a new database, which we can then fill with tables and data later. This is done with the SQL command CREATE DATABASE :

mysql> create database test_database;

Query OK; 1 row affected (0.03 sec)

The output from MySQL already indicates that everything went fine, but to be really sure, we can ask anew which databases the server manages:

mysql> show databases;

+-----------------+

| Database |

+-----------------+

| mysql |

| test |

| test_database |

+-----------------+

6 rows in set (0.00 sec)

Creating and Populating a Database

Now we want to define a table in our new database, but the first thing we need to do is tell the MySQL server that we actually want to use this database:

mysql> use test_database

Database changed

As you can see, we didn't use a semicolon at the end here, since this is again not a SQL command, but rather a control statement for the MySQL console client. It wouldn't hurt to add a semicolon here, too.

You define a table, which is ultimately where your data will be stored, by means of the SQL command CREATE TABLE. Here is an example:

mysql> CREATE TABLE comment_table(

-> id INT NOT NULL auto_increment,

-> comment TEXT,

-> PRIMARY KEY(id));

Query OK, 0 rows affected (0.10 sec)

Here we defined a table called comment_table with two columns—that is, there are two data fields in each record. One is called id. This serves as a unique identifier for each record and is therefore marked as the primary key, which is just a fancy term in database-speak for "unique identifier." The other column is a variable of type TEXT that can store up to 65,535 characters.

Now we can check which tables we have within our database test_database:

mysql> show tables;

+-------------------------+

| Tables_in_test_database |

+-------------------------+

| comment_table |

+-------------------------+

1 row in set (0.00 sec)

Now we know that everything is all right and can start to add data records to our table. This is done with the SQL command INSERT:

mysql> INSERT INTO comment_table VALUES ('0','comment');

Query OK, 1 row affected (0.06 sec)

Finally, we can check which data our table contains:

mysql> SELECT * FROM comment_table;

+----+---------+

| id | comment |

+----+---------+

| 1 | comment |

+----+---------+

1 row in set (0.01 sec)

Here we ask for all (*) columns in the table comment_table. But you might have noticed something odd here: we have asked MySQL to insert a 0 in the first column, but instead there is a