Web Solutions-Open Source CPAN 561



Web Solutions-Open Source CPAN 561

Lecture 7-1: MySQL Tutorial

In order to use MySQL  database server, you need to download it from 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.

[pic] 

You can use the show databases command to see what databases are available:

[pic]

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.

[pic]

You can view the available table using the show tables command. On my system, I have the following tables under test database::

[pic]

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  to 1.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 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));

[pic]

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);

[pic]

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));

 [pic]

 

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”);

[pic]

And we can join the two tables using the command 

select * from student,sport where student.id=sport.id ;

[pic]

Reference:

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download