Web Solutions-Open Source CPAN 561
Lecture 7-1: MySQL Tutorial
In order to use MySQL database server, you need to download it from http://mysql.com.The server will be install into a folder called c:\mysql.
To start the server , double click the file: c:/mysql/bin/winmysqladmin.exe. An icon of the server will be displayed on your computer status bar. The first time you login , you will be asked to enter the root user name and password, type test for both.
To connect and use SQL commands, you need to run the tool :
c:/mysql/bin mysql
You will receive a welcome message, and you will be prompted with mysql line command.
You can use the show databases command to see what databases are available:
You can create a new database using create database command, For example:
mysql> create database cpan561;
In order to use a specific database, you have to select this database using use command and you will receive a message stating that database changed.
You can view the available table using the show tables command. On my system, I have the following tables under test database::
If there are no tables, you will receive the message empty set.
At any time you can quit the database using the command:
mysql> quit;
To find out which database is currently selected, use the database() function:
mysql> select database();
If you decide you don't want to execute a command that you are in the process of entering, cancel it by typing \c:
To create a table use the create table command. You should be aware of the following data type categories supported by mysql: numeric types, data and time types and string types.
Following are some of the data types supported by Mysql , where:
M indicates the maximum display size. The maximum legal display size is 255.
D applies to floating-point types and indicates the number of digits following the decimal point. The maximum possible value is 30, but should be no greater than M minus 2. Square brackets, [ and ], indicate a part of type specifies that are optional. Note that if you specify ZEROFILL for a column, MySQL will automatically add the UNSIGNED attribute to the column.
· INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
· DOUBLE[(M,D)] [ZEROFILL]
A normal-size (double-precision) floating-point number. Cannot be unsigned. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to1.7976931348623157E+308. The M is the display width and D is the number of decimals
· FLOAT[(M,D)] [ZEROFILL]
A small (single-precision) floating-point number. Cannot be unsigned. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. The M is the display width and D is the number of decimals. FLOAT without an argument or with an argument of <= 24 stands for a single-precision floating-point number.
· DATE A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers.
· DATETIME A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using either strings or numbers.
CHAR(M)
A fixed-length string that is always right-padded with spaces to the specified length when stored. The range of a M is 1 to 255 characters. Trailing spaces are removed when the value is retrieved. CHAR values are sorted and compared in case-insensitive fashion according to the default character set.
· VARCHAR(M)
A variable-length string. Trailing spaces are removed when the value is stored. The range of M is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion.
· DECIMAL[(M[,D])] [ZEROFILL]
An unpacked floating-point number. Cannot be unsigned. Behaves like a CHAR column: 'unpacked' means the number is stored as a string, using one character for each digit of the value. The decimal point and, for negative numbers, the minus sign, are not counted in M (but space for these are reserved). If D is 0, values will have no decimal point or fractional part. The maximum range of Decimal values is the same as for Double, but the actual range for a given Decimal column may be constrained by the choice of M and D. If D is left out, it's set to 0. If M is left out, it's set to 10.
· ENUM ('value1','value2',...)
An enumeration. A string object that can have only one value, chosen from the list of values, 'value1', 'value2', ..., NULL or the special "" error value.
An ENUM can have a maximum of 65535 distinct values.
· SET('value1','value2',...)
A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET can have a maximum of 64 members.
· BLOB is a binary large object that can hold a variable amount of data.
· TEXT is the same as BLOB but it is case insensitive. In comparison, BLOB is case sensitive.
Assume for example that we want a table called person that has two columns id and name. ID is the primary key, and we want it to be auto incremented:
create table person (id int(5) not null auto_increment ,name varchar(30),primary key(id));
After creating your table, you need to populate it using insert statement. In its simplest form, you supply values for each column, in the order in which the columns were listed in the create table statement.
Suppose you need to add a new record to person. Use an insert statement like this:
insert into person values (NULL, 'John Smith');
Since we specified auto increment for id, the database server will assign the next available one.
One of the important features of mysql is that you can also run it in batch mode. To do this, put the commands you want to run in a file, then tell mysql to read its input from that file. The following is an example of a simple batch file called batch.txt stored under c:\mysql\bin:
use test;
show tables;
drop table person;
create table person (id int(5) not null auto_increment ,name varchar(30),primary key(id));
insert into person values (NULL, 'John Smith');
commit;
You can catch the output in a file for further processing:
mysql> mysql < batch-file-in > batch-file-out
Examples
Example 1:
This example shows you how to create a table called student. On of the data types used here is enum type::
create table student (id int not null, name varchar(30),year enum(“first”,”second”,”third”), primary key (id));
After that we can insert values as follows:
insert into student values (1,'John Steve',1);
insert into student values (2,'Smith William',2);
insert into student values (3,'Robert Scotte',3);
Example 2:
In the following example, we will create a second table called sports that uses set type to represent the kind of sports with other types including the student id so we can join them together later:
create table sport (num int not null, id int, kind set(“swimming”, “tennis” ,”football”), primary key(num));
After that we can insert values as follows:
insert into sport values(1,1,”tennis,football”);
insert into sport values(2,2,””);
insert into sport values(3,3,”tennis”);
And we can join the two tables using the command
select * from student,sport where student.id=sport.id ;
Reference: http://www.mysql.com
8