MySQL

MySQL Notes

When trying to link to another location in the same page, got error code "bX-fjtq9f", so the above links are actually not working, will fix this issue later.


>>>>> NOTE 1 <<<<<

Get Ready: Installation

NOTE2|NOTE3|NOTE4|NOTE5

First of all, you need to install a PHP/MySQL/Apache combo package such as
XAMPP or
EasyPHP which allow you test your codes on your local machine.
How to run MySQL?
There are three ways to run MySQL:

  • Command mode which is discussed below
  • Through PHP, we will discuss at a later time
  • Through PHPMyAdmin, we will discuss at a later time
The method to run MySQL in command mode after installation of XAMPP:
Step 1: Install XAMPP eg. at location: c:\program files\xampp\
/*Help DOS to find the path where MySQL located*/
Step 2: Start=>Run=>CMD-> prompt> Set path=%PATH%;c:\program files\xampp\mysql\bin
Step 3: shell prompt>mysql -u root -p
TIP
Make sure NOT to enter this line after "mysql>" prompt but after c:\program files\xampp\mysql\bin> , also do NOT include a semicolumn at the end or you might get errors like:
ERROR 1045 (28000): Access denied for user 'root'@localhost (using password:YES)
ERROR 1064 (42000): You have error in your SQL syntax;check the manual that correspondstoyour MySQL server...
Step 4: Enter password: usually simply input "Enter" key

Useful Tip 1: Change Your Default Compand Prompt Directory To Mysql

Usually your default Windows command is your Documents and Settings \ Username, while it is very convenient if we can set the default command prompt to mysql, let's make it happen by simply following:

  1. Start => Run => input "regedit" after open
  2. HKEY_CURRENT_USER => Software => Microsoft => Command Processor, look for string "Autorun" in the right window, if it is there: go to next step, if not go to step 5.
  3. Double click string "Autorun", set value data to the default directory you want say: "CD /d C:\Program Files\xampp\mysql\bin"
  4. Test it!! You should find your default command prompt already changed to "c:\program files\xampp\mysql\bin", simply type " mysql " after command prompt, you are ready to enjoy mysql.
  5. Right click on the right window area => New => String Value => change "New Value #1" to "Autorun" => Continue following Step 3 to 4.

First Database & Table

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
prompt>Set path=%PATH%;c:\program files\xampp\mysql\bin; ;c:\program files\xampp\mysql\
/*If running MySQL on your local machine*/
shell prompt>mysql -u root -p
Enter password: "Enter" key
Now you are ready to use mysql
mysql>

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.8 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
/*List all databases on the sql server; Make sure commands end with ; or\g.*/
mysql> SHOW databases;
/*Using the "pre" tag to define preformatted text such as the table below.Text in a pre element is displayed in a fixed-width font (usually Courier), and it preserves both spaces and line breaks. */
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdcol              |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
| webauth            |
+--------------------+
7 rows in set (0.00 sec)
/*create a database called "materials"*/
mysql> CREATE database materials;
Query OK, 1 row affected (0.05 sec)
/*The use command is used when you have more than one database on a MySQL server and need to switch between them*/
mysql> USE materials;
Database changed
/*
create a table named "composites" under database"materials" which means a database can include multi tables.MyISAM is the default storage engine Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.
Format: CREATE TABLE table_name(var_column1 TYPE, var_column2 TYPE,...
*/
mysql> CREATE TABLE composites(matName VARCHAR(16), matElasticity INT,matPoisson TINYINT)ENGINE MyISAM;
Query OK, 0 rows affected (0.17 sec)
/*provides information about the columns in table composites*/
mysql> DESCRIBE composites; 
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| matName       | varchar(16) | YES  |     | NULL    |       |
| matElasticity | int(11)     | YES  |     | NULL    |       |
| matPoisson    | double      | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)
/*Inseert values of the corresponding variables in table composite,make sure to include single quote ' ' for char variable values*/
mysql> INSERT INTO composites(matName,matElasticity,matPoisson) VALUES(' eglassEpoxy',150,0.4);
Query OK, 1 row affected (0.03 sec)
/*Querying table composites*/
mysql> SELECT * FROM composites; 
+-------------+---------------+------------+
| matName     | matElasticity | matPoisson |
+-------------+---------------+------------+
| eglassEpoxy |           150 |        0.4 |
+-------------+---------------+------------+
1 row in set (0.00 sec)
/*We will practice more...*/
mysql>

MySQL DUMP/Script file

Tired of typing mysql commands like above line by line to create a databse and table?
MySQL dump file is your solution.
What is a dump file? Dump file is simply a file which includes many lines of MySQL commands. Someone says dump file should include only data but structure not included. Anyway, it is some kind of script file which can be executed to create a database.
Here is the dump file which can create the above table and insert data.
CREATE DATABASE IF NOT EXISTS MATERIALS;
USE materials;

-- Create table composites

CREATE TABLE composites (
  matName VARCHAR(16),
  matElasticity int(11)  DEFAULT '',
  matPoisson double DEFAULT '',
  PRIMARY KEY  (matName),
)ENGINE=MyISAM;

INSERT INTO composites VALUES('eglassEpoxy ',150,0.4);
You may save it as a sql format or something like.dump, then using the tip 2 below to load your dump file in your database.

Simple? But keep in mind that database is not simply as easy as a table shown above. anyway, it's good to have our first MySQL database. In note 2, we will study the most commonly used commands to operate our database tables.

Useful Tip 2: How To Load & Import An Existing Database or Dump File

Before concluding this part, I would like to address an important issue: how to load or import an existing database or dump file (eg. you may want to test some example files downloaded from some book's website) which made me confusing for several days, I searched a lot of websites and simply could not find the right answer I need.
Let me suppose your mysql installation directory is " C:\Program Files\ EasyPHP-5.3.6.0\" and the database file "import_db.sql" you want to load is unzipped in directory "C:\db\" , then following the following steps:
  1. CREATE a new database new_db which will be used to load the imported database following the steps described above
  2. Go the directory where you unzipped the files eg. C:\db\ by using cd
  3. After command prompt C:\db> SOURCE [import_db.sql];
  4. Use "DESCRIBE" to check the table details:
    mysql>USE [new_db];
    mysql>SHOW TABLES;
    mysql>DESCRIBE [table_name]; Now you finish loading the contents of import_db.sql into the new_db database.
    I used notation "[variable_name_to_be_changed]" to indicate parameters you need to change, do not include "[]" in your command line.
Note: If you still cannot make it, put your script file *.sql under MySQL directory /bin, then you should be able to dump the file without any problems.

FYI-an excellent reference for explaining similar issue can be found from this link on MySQL website.

>>>>>NOTE 2<<<<<

Operating MySQL Table

NOTE1|NOTE3|NOTE4 |NOTE5 Most commonly used mysql commands are listed in the table below for quick reference purpose.
Commands Description
mysql>CREATE DATABASE [db_name]; Create a database named db_name
mysql> SHOW DATABASES;
SHOW TABLES 		List all databases on the server
Shows the tables in the database, the database should be slected first.
mysql> ALTER TABLE [table_name] ADD INDEX [index_name];
ALTER TABLE [table_name] DROP INDEX [index_name];		 Add or remove (entire)index (column and data)from table
mysql> DROP TABLE table_name
mysql> DROP DATABASE database_name		 Completely delete table or database
mysql>DELETE FROM [table_name]
mysql>DELETE FROM [table_name] WHERE [attribute]= 		Deletes all data in the table but doesn't remove the table
Conditionally delete rows with given specified attribute
mysql>INSERT INTO [table_name] VALUES()
mysql>LOAD DATA INFILE '[file path/file_name.txt]' INTO TABLE [table_name] FIELDS TERMINATED BY ',' ESCAPED BY '"' LINES TERMINATED BY '\N';		 Bulk load data from a formatted ASCII text file
mysql>SELECT * FROM [table_name];


mysql>SELECT [attribute1 or *] FROM [table_name] WHERE [attribute2 = ];


mysql>SELECT [attribute1 or *] FROM [table_name] WHERE [attribute2 = ] AND [attribute3 = ];


mysql>SELECT [attribute1 or *] FROM [table_name] WHERE [attribute2 = ] AND [attribute3 = ] ORDER BY [attribute4];


mysql>SELECT [attribute1],COUNT(*) FROM [table_name] GROUP BY [attribute2];


mysql>SELECT [attribute_from_table1], [attribute_from_table2], FROM [table1_name], [table2_name] WHERE [table1.attribute_to_be_matched]=[table2.attribute_to_be_matched]; 		Quering all attributes in table




Join querying in the condition of




Join querying in the condition of [] and []


Join querying in the condition of [] and [] and the results ordered by []


Groups all the rows for attribute 1 into sets, COUNT(*) counts the number in each group set.


Natural join. Select those rows with a certain match attribute (both tables have this match attribute).

>>>>> NOTE 3<<<<<

NOTE1|NOTE2|NOTE3 |NOTE4

Querying Web Databases

Once we create our own databases, or import/load existed databses, we may want to get info. from them.
This note will introduce the basics of connecting to the MySQL DBMS, querying databases, and retrieving results, and also covers querying databases with user input data such as using HTML <form >

Five-step web database driven querying approach to connect to the DBMS and use a database.

<html>
  <body>
 <?php
  
 //Step 1: Open the database connection
 $connection = mysql_connect ("localhost","user","password");
  
 // Use database db_name
 mysql_select_db("db_name",$connection);
  
 //Step 2: Run the query on the database db_name through the 
 //connection
 $result = mysql_query ("SELECT * FROM table_name 
        WHERE column_name LIKE '%$user_search_key_word_from_form%'",
    $connection);
  
 //Step 3: Fetch the current row into the array $row
  
 while ($row = <b>mysql_fetch_row</b>($result))
  
 //Step 4: Print out each element in $row
 {for ($i=0;$i<mysql_num_fields($result); $i++)
      
        echo $row[$i]."";
 echo "n";
 }
  
 //Step 5: Close the database connection
 mysql_close($connection);
      
 ?>
     
</body>
</html>

>>>>>NOTE 4 <<<<<

Writing to Web Databases

NOTE1|NOTE2|NOTE3 |NOTE5 We do not just retrieve information, data also needs to be written in database.
This note covers writing data in web databases such as databases inserts,updates ,and deletes

>>>>>NOTE 5 <<<<<

A Little on Database Deisgn

NOTE1|NOTE2|NOTE3 |NOTE4 In the very first step when you create your web applications, you may need to design a database which allows PHP manipulation: accessing and writing to MySQL database using PHP possible. Designing a database is not as easy as creating a simple spreadsheet.
So first let's briefly review the design of database.

Concept of ER Modeling

ER(Entity-Relationship) diagram has elements of Entities(nouns, objects,people,...) and their relationships. Each entity has its own attributes(eg. a student has student ID, SSN, birthday, attributes become columns in a table), so does relationships.
  1. Think about all the ENTITIES needed for your application.If the relationship between two entities are many-many (M:M), we we need to create an additional relationship, for example:

    Student-Course Student may take many courses, one course may be taken by many students, so we need a relation: Enrolled.
    Now we have two entities and one relationship
    Student(...)
    Courses(...)
    Enrolled(...)
    At this stage, you will find there may be some repeating or duplicte columns.
  2. Remove the duplicate columns (repeat first.
  3. Identify the key for each table(relationship), the key is the one which can uniquely identifies the row, may times, for the relationship, you may need more than one keys (so called composite key:which can link the relationship to the other two entities).
  4. Find out those non-key attributes which do not depend on ALL(which means NOT just part of the key) keys and get rid of them.
  5. Find out those attributes which depend on other non-key attributes instead of primary keys and get rid of them.
  6. Coming up with final relations (all the tables) by
    • Entities tables(Key, non-key attributes)
    • Relationship tables(key, non-key attributes)
      • Example: Student(StuID, Name, Address,Tel,...) Courses(CourseID,Section,Semester,ClassRoom,Instructor,Date,Time...) Enrolled(StuID,CourseID,Section,Semester,grade,...)
    Notice the above Enrolled relationship, we need four keys as primary key to uniquely identity the course enrollement.

A very good source on database design can be found here DATABASE DESIGN STEPS/HOW TO DEVELOP A DATABASE
An old but excellent paper on Database Design can be downloaded from here.

No comments:

Post a Comment

Subscribe to: Comments (Atom)