Transcript Session 5: Working with MySQL

Session 5: Working with
MySQL
iNET Academy
Open Source Web Development
Objectives
 Introduction of MySQL
 Managing the Database
 Using phpMyAdmin & SQLyog
 Structure Query Language
MySQL Database
 MySQL client interface
 Login: require an username and a password
 Different user has different access level to databases and tables
 To connect to a MySQL Database we need




The IP address of the database server
The name of the database
The username
The password
 Several ways to connect to MySQL database



The command line
phpMyAdmin
SQLyog
Accessing DB from the Command
Line
 Using MySQL command-line client. First, open the OS command line
 Type mysql -h hostname –u user -p
Prompts & Commands
 At the MySQL prompt, user can enter database command followed by
Enter
 To see the list of commands that MySQL supports, type help or \h
 Eg: show databases, use
Managing the Database
 Creating Users

Creating users need root permission
 Creating a database
 Using a database
Using phpMyAdmin
 Go to the website http://www.phpmyadmin.net/
 Download the package phpMyAdmin 2.11.7…..zip
 Unpack to the C:\www\myadmin
 Create folder C:\www\myadmin\config
 In your web browser,
navigate to
http://localhost/myadmin
/scripts/setup.php
Using phpMyAdmin (cont.)
 In the Servers
section, click the Add
button. The Server
setup page displays
as following
Using phpMyAdmin (cont.)
 Most of the default values can be left alone. You do need to enter the





password for the root MySQL user in the “Password for config auth”
field.
Select “cookie” from Authentication type to limit access to your MySQL
data to only users with a MySQL account.
Click Add
Click “Save” from the Configuration section to save your changes to the
configuration file.
Copy the config.inc.php file tp myadmin.
Remove the config directory
Using phpMyAdmin (cont.)
 In your web browser, navigate to http://localhost/myadmin. Your web
browser displays a login page as following
Using SQLyog
 Go to the \\dc computer and download the SQLyog519.exe
 Install SQLyog using Installation Wizard
 Run the SQLyog program
 Type the root’s password
 Click “Connect”
Using SQLyog (cont.)
 The main window of SQLyog displays as following
Database Concept & SQL
 Database are repository of structured information
 SQL: Structured Query Language is the language used to manage and
manipulate data stored in relational database
Creating tables
 The title_id column is an integer. The auto_increment keyword make
this field’s value automatically is assigned an unique value during row
insertion.
 The title column holds text up to 150 characters
 The pages column is an integer
 The PRIMARY KEY attribute tells MySQL which fields is the key value
Creating Tables (cont.)
 To verify a table, use DESCRIBE
Adding Data to Table
 Use INSERT command. Syntax: INSERT INTO table COLUMNS
([columns]) VALUES ([values]);
 Rules:





Numeric values shouldn’t be quoted
String values should always be quoted
Date and time value should always be quoted
Function shouldn’t be quoted
NULL should never be quoted
Table Definition Manipulation
 Renaming a table: ALTER TABLE table RENAME new_table
 Changing a column’s data type: ALTER TABLE table MODIFY column
datatype
 Changing the order of columns:
 Renaming a column: ALTER TABLE table CHANGE column_name
new_column_definition
 Removing a column: ALTER TABLE table DROP column
 Deleting an entire table: DROP TABLE table
Querying the Database
 SELECT columns FROM tables [WHERE CLAUSE]; [ORDER BY
CLAUSE]
Querying the Database (cont.)
 Limit results with WHERE
 If there is more than
one condition, logical operators
Like AND or OR are used
Querying the Database (cont.)
 Specifying the order
 Join tables together
Querying the Database (cont.)
 Natural joins
 Join on
 Alias
Modifying & Deleting Database Data
 Use UPDATE command to modify the data
 USE DELETE command to delete the data
Search Functions
 Use the LIKE operator

The % sign indicates that anything can match

The _ sign indicates that exact one character can match
Logical Operators
 Use AND, OR and NOT operators in WHERE clause
Practice

In this practice, you will
 Create a table named “user” which have following fields
 ID, integer, PrimaryKey, auto_increment
 Name, varchar (100)
 Gender, tinyint (1)
 Email, varchar (100)
 Insert 5 users into the table “user”
 Create a table named “user_log” which have following fields
 user_id, integer
 time_of_login
 time_of_logout
 number_of_pages_view
 Insert login and logout information of 5 users above
 Find the user detail based on time_of_login or time_of_logout
 Find the user have the maximum number_of_pages_view
 Find the user have the maximum login times
 Practice using the LIKE operator