Creating a Database
To create a database you use the
CREATE command:
mysql>
create database Databasename;
Databasename is what you want to name the database that you're creating.
Try creating a database named myfriends:
mysql>
create database myfriends;
Query OK, 1 row affected (0.00 sec)
You can view all of the databases on your server with
SHOW databases;
mysql>
show databases; Code:
+-----------+
| Database |
+-----------+
| myfriends |
| mysql |
| test |
+-----------+
3 rows in set (0.00 sec)
You can select which database you want to use with
USE Databasename; Databasename will be the name of the database you want to use.
mysql>
use myfriends;
Database changed
Creating a Table
To put information in the database you will need to create a table to store the information. You need to plan how you're going to create a table very carefully for maximum efficiency. For the purpouse of this table and example we'll create a table named friends that will hold our friends first name, lastname, birthday, and sex:
mysql>
create table friends (firstname varchar(20), lastname varchar(20),
->
birthday date, sex char(1));
Query OK, 0 rows affected (0.11 sec)
You can check the table with
show tables;
First we create a table named friends, then inside the set of parentheses we put in the fields followed by the data type. So we have firstname and lastname with the varchar data type, birthday with the date data type, and sex with the char data type. Varchar means varying characters and the number 20 in parentheses means it can hold up to 20 characters. Both Varchar and Char can take in a maximum of 255 characters, the main difference is they allocate a different amount of space. You should use char when the data is small and you know it's exact size and varchar when you are not sure how many characters will be. The date data type is in the format year-month-day.
You can verify your table was created the way you expected and view the fields and data types in the table with
Describe TableName;
mysql>
describe friends; Code:
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| firstname | varchar(20) | YES | | NULL | |
| lastname | varchar(20) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
More info on data types can be found here:
http://www.mysql.com/doc/en/Column_types.html Inserting Data Into Tables
To insert data into a table you use
Insert Into TableName;
mysql>
insert into friends values ('Mike', 'Metzger', '1975-02-03', 'm');
Query OK, 1 row affected (0.06 sec)
First we use insert into followed by the table name followed by the word values, then inside the parentheses we enter the values to insert into the table. The values must be entered in the exact same order as the order you specified when creating the table. So Mike would be entered in the firstname column, Metzger would be in lastname, 1975-02-03 would be in birthday, and m would be in sex.
You can aslo specify which fields you want to insert data into and in which order:
mysql>
insert into friends (lastname, sex)
->
values ('Simpson', 'f');
Query OK, 1 row affected (0.00 sec)
First you enter the field names in the order you will be inserting into them, then the values you will be inserting into the fields you specified. So Simpson would go in the lastname column and f would go in the sex column.
And another way to insert data:
mysql>
insert into friends set
->
firstname = 'Bart', lastname = 'Simpson',
->
birthday = '1985-04-12', sex = 'f';
Query OK, 1 row affected (0.00 sec)
Retriving Data From Tables
mysql>
select * from friends; Code:
+-----------+----------+------------+------+
| firstname | lastname | birthday | sex |
+-----------+----------+------------+------+
| Mike | Metzger | 1975-02-03 | m |
| NULL | Simpson | NULL | f |
| Bart | Simpson | 1985-04-12 | f |
+-----------+----------+------------+------+
3 rows in set (0.06 sec)
Here we select all the columns from the table friends. The asterik * is basically a wildcard that means anything and everything. You can also specify what you want to retrive. Say you want to select the firstname of all your male friends:
mysql>
select firstname from friends where sex = 'm'; Code:
+-----------+
| firstname |
+-----------+
| Mike |
+-----------+
1 row in set (0.11 sec)
Changing Data
You can use
UPDATE to change the values in your table. If you wanted to change the sex from male to female if the lastname is simpson you can do it like this:
mysql>
update friends set sex = 'm' where lastname = 'simpson';
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2 Changed: 2 Warnings: 0
Now your table should look like this:
mysql>
select * from friends; Code:
+-----------+----------+------------+------+
| firstname | lastname | birthday | sex |
+-----------+----------+------------+------+
| Mike | Metzger | 1975-02-03 | m |
| NULL | Simpson | NULL | m |
| Bart | Simpson | 1985-04-12 | m |
+-----------+----------+------------+------+
3 rows in set (0.00 sec)
Now try entering more rows into the table. See if you can figure out how to make each row have a value for firstname, lastname, birthday, and sex.
Deleting Data From a Table
You can delete the every row where the firstname field is Mike like this:
mysql>
delete from friends where firstname = 'Mike';
You can make it more specific like this:
mysql>
delete from friends where firstname = 'Mike' AND lastname = 'Metzger';
To delete all the data in your table:
mysql>
delete from friends;
To delete your entire table from the database:
mysql>
drop table friends;
To delete your entire database and all the tables in it:
mysql>
drop database myfriends;
To exit the mysql you can type quit:
mysql>
quit
Bye
To exit out of MS-DOS you can type exit.
That's all for now. To learn some more about MySQL a good place to start is by reading the MySQL Documentation:
http://www.mysql.com/documentation/index.html and there's a good tutorial provided by MySQL here:
http://www.mysql.com/doc/en/Tutorial.htm
Don't forget to keep checking back from time to time for more MySQL and other tutorials and feel free to reply to this thread if you have any questions, comments, etc... :webdev:
