1 / 25

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

garran
Download Presentation

Session 5: Working with MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Session 5: Working with MySQL iNET Academy Open Source Web Development

  2. Objectives • Introduction of MySQL • Managing the Database • Using phpMyAdmin & SQLyog • Structure Query Language

  3. 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

  4. Accessing DB from the Command Line • Using MySQL command-line client. First, open the OS command line • Type mysql -h hostname –u user -p

  5. 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

  6. Managing the Database • Creating Users • Creating users need root permission • Creating a database • Using a database

  7. 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

  8. Using phpMyAdmin (cont.) • In the Servers section, click the Add button. The Server setup page displays as following

  9. 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

  10. Using phpMyAdmin (cont.) • In your web browser, navigate to http://localhost/myadmin. Your web browser displays a login page as following

  11. 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”

  12. Using SQLyog (cont.) • The main window of SQLyog displays as following

  13. 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

  14. 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

  15. Creating Tables (cont.) • To verify a table, use DESCRIBE

  16. 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

  17. 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

  18. Querying the Database • SELECT columns FROM tables [WHERE CLAUSE]; [ORDER BY CLAUSE]

  19. Querying the Database (cont.) • Limit results with WHERE • If there is more than one condition, logical operators Like AND or OR are used

  20. Querying the Database (cont.) • Specifying the order • Join tables together

  21. Querying the Database (cont.) • Natural joins • Join on • Alias

  22. Modifying & Deleting Database Data • Use UPDATE command to modify the data • USE DELETE command to delete the data

  23. Search Functions • Use the LIKE operator • The % sign indicates that anything can match • The _ sign indicates that exact one character can match

  24. Logical Operators • Use AND, OR and NOT operators in WHERE clause

  25. 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

More Related