Notes from SAOS: MySQL

Install Apache (httpd), PHP, MySQL (server and client)

# yum -y install httpd php mysql mysql-server php-mysql

Configure the new services to start automatically
# /sbin/chkconfig httpd on # /sbin/chkconfig --add mysqld [not required with FC4 and above] # /sbin/chkconfig mysqld on # /sbin/service httpd start # /sbin/service mysqld start
If everything went okay attempting to open this url:
http://localhost
or http://127.0.0.1 will forward to a page like one displayed in image below. Otherwise it will give an error.

Click image to enlarge
By default the document directory is /var/www/html. You can change it modifying /etc/httpd/conf/httpd.conf Modify the lines and set like this:
DocumentRoot "/home/AR/web"
And another line
<Directory "/home/AR/web">
Changing directory like this may not allow apache to read files or execute scripts. Hence this error message "Forbidded, you don't have permission to access / on this server" can be produced. To get rid of this change the directory attributes to executable.
# chmod a+x /home # chmod a+x /home/AR # chmod a+x /home/AR/web
which means that you are giving permission of execution to apache for the entire directory chain. This actually solves the problem. If still the problem persists then apply this command too.
# chown -R apache:apache /home/AR/web
Change "/home/AR/web" with your www directory.

Add mysqld as startup service

# /sbin/chkconfig mysqld on 
# /sbin/service mysqld start

Change the mysqladmin password for security reason.
# mysqladmin -u root password new-password

Notice # means root user prevelige.

Installing WAMP Server and Starting MySQL Console

To learn SQL you don’t necessarily need an Oracle SQL Server(oracle/sql is really big in size). We can achieve our goals still with MySQL. Install wamp server to get MySQL along with Apache and PHP. Latest release till today is 2.0i
http://www.wampserver.com/en/download.php

W stands for Windows and amp stands for Apache, MySQL and PHP.

Now start wamp server clicking Start Menu -> Programs -> WampServer -> Start WampServer.

You’ll see there is a icon on the right side of the taskbar. Click (not right button click) it. In the Menu under submenu of MySQL click MySQL console.

It will ask for a password. By default there is no password set. Simply press enter.

Working with Databases

First step is to list the databases. You will use the following query to list databases.

show databases

Every query ends with a semicolon. Hence here is how the console responds to the query.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
+--------------------+
3 rows in set (0.01 sec)

Create your own database. The command to create database is

create database databasename

Here is the console response for a sample command.

mysql> create database bankdb;
Query OK, 1 row affected (0.01 sec)

Now you have to select a database to work with. In our case it’s bankdb;

mysql> use bankdb;
Database changed

Working with Tables

To list tables inside the database you have to use “show tables” query.

mysql> show tables;
Empty set (0.00 sec)

Note Oracle SQL query “select * from tab” doesn’t work here.

mysql> select * from tab;
ERROR 1146 (42S02): Table 'bankdb.tab' doesn't exist

As there are no tables on the database we’ll create some.

During creating tables we need to remember that data type number and varchar2 does not exist in mysql. To know more about mysql data types follow here.

http://dev.mysql.com/doc/refman/5.0/en/data-types.html

To compare with oracle data types check link this too.

http://ss64.com/ora/syntax-datatypes.html

Hence we replace varchar2 with varchar and number with float.

And we create tables with following commands.

create table branch
        (branch_name varchar(15),
        branch_city varchar(12) not null,
        assets float(10,2) not null,
        constraint b_pk primary key(branch_name),
        constraint b_chk check(assets>=0));

create table customer
        (customer_name varchar(20),
        customer_street varchar(15),
        customer_city varchar(15)not null,
        constraint c_pk primary key(customer_name));


create table account
        (account_no char(5),
        branch_name varchar(15),
        balance float(10,2) not null,
        constraint a_pk primary key(account_no),
        constraint a_fk foreign key (branch_name) references branch(branch_name),
        constraint a_chk1 check (balance>=0),
        constraint a_chk2 check (account_no like 'A-%'));


create table loan
        (loan_no char(5),
        branch_name varchar(15),
        amount float(10,2) not null,
        constraint l_pk primary key(loan_no),
        constraint l_fk foreign key (branch_name) references branch(branch_name),
        constraint l_chk1 check (amount >= 0),
        constraint l_chk2 check (loan_no like 'L-%'));


create table depositor
        (customer_name varchar(15),
        account_no char(5),
        constraint de_pk primary key (customer_name, account_no),
        constraint de_fk1 foreign key (customer_name) references customer(
        customer_name), constraint de_fk2 foreign key (account_no) references
        account(account_no));


create table borrower
        (customer_name varchar(15),
        loan_no char(5),
        constraint bo_pk primary key (customer_name, loan_no),
        constraint bo_fk1 foreign key (customer_name) references customer (
        customer_name), constraint bo_fk2 foreign key (loan_no) references 
        loan (loan_no));

Show current list of tables.

mysql> show tables;
+------------------+
| Tables_in_bankdb |
+------------------+
| account          |
| borrower         |
| branch           |
| customer         |
| depositor        |
| loan             |
+------------------+
6 rows in set (0.00 sec)

You use desc command to view descriptions of a table.

mysql> desc account;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| account_no  | char(5)     | NO   | PRI |         |       |
| branch_name | varchar(15) | YES  | MUL | NULL    |       |
| balance     | float(10,2) | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

Now it’s time to insert data into the tables.

insert into branch
values('Brighton','Brooklyn',7100000);

insert into branch
values('Downtown', 'Brooklyn',9000000);

insert into branch
values('Mianus', 'Horseneck',400000);

insert into branch
values('North Town', 'Rye',3700000);

insert into branch
values('Perryridge', 'Horseneck',1700000);

insert into branch
values('Pownal', 'Bennington',300000);

insert into branch
values('Redwood', 'Palo Alto',2100000);

insert into branch
values('Round Hill', 'Horseneck',8000000);


insert into customer
values('Adams','Spring','Pittsfield');

insert into customer
values('Brooks','Senator','Brooklyn');

insert into customer
values('Curry','North','Rye');

insert into customer
values('Glenn','Sand Hill','Woodside');

insert into customer
values('Green','Walnut','Stamford');

insert into customer
values('Hayes','Main','Harrison');

insert into customer
values('Jhonson','Alma','Palo Alto');

insert into customer
values('Jones','Main','Harrison');

insert into customer
values('Lindsay','Park','Pittsfield');

insert into customer
values('Smith','North','Rye');

insert into customer
values('Turner','Putnam','Stamford');

insert into customer
values('Williams','Nassau','Princeton');


insert into account
values('A-101','Downtown',500);

insert into account
values('A-102','Perryridge',400);

insert into account
values('A-201','Brighton',900);

insert into account
values('A-215','Mianus',700);

insert into account
values('A-217','Brighton',750);

insert into account
values('A-222','Redwood',700);

insert into account
values('A-305','Round Hill',350);


insert into loan
values('L-11', 'Round Hill',900);

insert into loan
values('L-14', 'Downtown',1500);

insert into loan
values('L-15', 'Perryridge',1500);

insert into loan
values('L-16', 'Perryridge',1300);

insert into loan
values('L-17', 'Downtown',1000);

insert into loan
values('L-23', 'Redwood',2000);

insert into loan
values('L-93','Mianus',500);


insert into depositor
values('Hayes', 'A-102');

insert into depositor
values('Jhonson', 'A-101');

insert into depositor
values('Jhonson', 'A-201');

insert into depositor
values('Jones', 'A-217');

insert into depositor
values('Lindsay', 'A-222');

insert into depositor
values('Smith', 'A-215');

insert into depositor
values('Turner', 'A-305');


insert into borrower
values('Adams', 'L-16');

insert into borrower
values('Curry', 'L-93');

insert into borrower
values('Hayes', 'L-15');

insert into borrower
values('Jackson', 'L-14');

insert into borrower
values('Jones', 'L-17');

insert into borrower
values('Smith', 'L-11');

insert into borrower
values('Smith', 'L-23');

insert into borrower
values('Williams', 'L-17');

Now you can perform queries on existing tables and data for practicing. I’ll add some nice queries soon.

Notes from SAOS

Wednesday, November 10, 2010

How to change Document Root on WAMP

Saturday, January 16, 2010

Installing and Configuring Apache, PHP, MySQL on Fedora Core

Monday, January 11, 2010

Learning SQL through MySQL software

Blog Archive