MySQL, like most networked systems, requires you to log in with a specific user account before doing anything else. This is a fairly obvious security measure, and it limits access to the data by specifying permissions for each account.
For example, one user may only have permission to view existing data, whereas another may have permission to add new data, and perhaps even change other users ’ permissions.
Creating a New Database
It ’ s easy to create a new MySQL database. First, fire up the MySQL command – line tool Open a shell prompt, change to the correct folder (if using Windows or Mac OS X), and then on Ubuntu or Windows type:
mysql -u root -p
On the Mac type:
./mysql -u root -p
Press Enter. Now enter the root password you specified earlier, and press Enter again. You should see the prompt appear:
mysql >
To create a new database, all you have to do is use the CREATE DATABASE command. Type the following to create a new database called mydatabase :
CREATE DATABASE mydatabase;
Press Enter, and MySQL creates your new database. You can see a list of all the databases in the system— including your new database — by typing the command SHOW DATABASES.
Creating a Table
As you know, tables are where you actually store your data. To start with, you ’ ll create a very simple table, fruit , containing three fields: id (the primary key), name (the name of the fruit), and color (the fruit ’ s color).
The first thing to do is select the database you just created. Once you ’ ve selected a database, any
database – manipulation commands you enter work on that database. Type the following:
USE mydatabase;
Press Enter, and you should see:
Database changed
mysql >
Now create your table. Type the following at the mysql > prompt:
mysql > CREATE TABLE fruit (
- > id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
- > name VARCHAR(30) NOT NULL,
- > color VARCHAR(30) NOT NULL,
- > PRIMARY KEY (id)
- >);
Press Enter at the end of each line. Don ’ t enter the “ -> “ arrows; MySQL displays these automatically each time you press Enter, to inform you that your statement is being continued on a new line. If all goes well, you should see a response similar to the following:
Query OK, 0 rows affected (0.06 sec)
You ’ ve created a table with the following three fields:
id is the primary key. It uniquely identifies each row of the table.
We created the id field as SMALLINT UNSIGNED , which means it can hold integer values up to 65,535 (which should be enough for even the most ardent fruit fan). We used the keywords NOT NULL , which means that
NULL values aren ’ t allowed in the field.
We also specified the keyword AUTO_INCREMENT . This ensures that, whenever a new row is added to the table, the id field automatically gets a new, unique value (starting with 1).
This means we don ’ t have to specify this field ’ s value when inserting data name will store the name of each fruit. It ’ s created as VARCHAR(30) , which means it can hold strings of up to 30 characters in length.
Again, the NOT NULL keywords specify that NULL values aren ’ t allowed for this field color was created in the same way as name , and will be used to store the color of each fruit.
By the way, if you ever want to create a regular key (as opposed to a primary key) for a field in a table, use the keyword KEY or INDEX instead of PRIMARY KEY .
So if you wanted to add an index for the name field (because your table contained a large number of fruit records and you frequently wanted to look up fruit by name), you could use (again, don ’ t type the arrows):
mysql > CREATE TABLE fruit (
- > id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
- > name VARCHAR(30) NOT NULL,
- > color VARCHAR(30) NOT NULL,
- > PRIMARY KEY (id),
- > KEY (name)
- >);
Adding Data to a Table
Now try adding some fruit to your table. To add a new row to a table, you use the SQL INSERT
statement. In its basic form, an INSERT statement looks like this:
INSERT INTO table VALUES ( value1 , value2 , … );
This inserts values into each of the fields of the table, in the order that the fields were created.
Alternatively, you can create a row with only some fields populated. The remaining fields will contain NULL (if allowed), or in the case of special fields such as an AUTO_INCREMENT field, the field value will be calculated automatically. To insert a row of partial data, use:
INSERT INTO table ( field1 , field2 , ... ) VALUES ( value1, value2 , ... );
So you can add three rows to the fruit table by inserting data into just the name and color fields (the
id field will be filled automatically):
mysql > INSERT INTO fruit ( name, color ) VALUES ( ‘banana’, ‘yellow’ );
Query OK, 1 row affected (0.06 sec)
Reading Data from a Table
To read data in SQL, you create a query using the SELECT statement. We can also specify just the field or fields you want to retrieve:
mysql > SELECT name, color from fruit;
Updating Data in a Table
You change existing data in a table with the UPDATE statement. As with the SELECT statement, you can (and usually will) add a WHERE clause to specify exactly which rows you want to update. If you leave out the WHERE clause, the entire table gets updated.
Here ’ s how to use UPDATE to change values in your fruit table:
mysql > UPDATE fruit SET name = ‘grapefruit’, color = ‘yellow’ WHERE id = 2;
Query OK, 1 row affected (0.29 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Deleting Data from a Table
Deleting works in a similar way to updating. To delete rows, you use the DELETE statement. If you add a WHERE clause, you can choose which row or rows to delete; otherwise all the data in the table are deleted (though the table itself remains). Here ’ s an example:
mysql > DELETE FROM fruit WHERE id = 2;
Query OK, 1 row affected (0.02 sec)
Deleting Tables and Databases
To delete a table entirely, use the DROP TABLE statement. Similarly, you can delete an entire database with DROP DATABASE .
mysql > DROP TABLE fruit;
Query OK, 0 rows affected (0.25 sec)