SS7 My SQL - KIT502 PDF

Preview

Summary

KIT502...

Description

Semester 1, 2020

SELF-STUDY 7 MYSQL The learning activities in this self-study (a.k.a PRIOR to Tutorial) are designed to consolidate and extend your understanding of the topics covered in lectures in week 7. You should complete all activities and questions until the tutorial in week 8.

MYSQL DATABASE MySQL is the most popular Open Source Relational SQL Database Management System. SQL stands for Structured Query Language. It is the standard language for dealing with Relational Databases. SQL can be used to insert, search, update and delete database records.

WORKING WITH MYSQL (COMMAND LINE) Please refer to Self-Study: Practical for information on connecting to the alacritas server. Use terminal (Mac) or PuTTy (PC). Note: The default password is blank, at the prompt; Enter Password: press return. -

Connecting to the MySQL database Log on to alacritas ssh alacritas.cis.utas.edu.au o (use your password. Initial password is you student ID number.) Connecting to MySQL mysql –u username –p username : your alacritas username Password is your ID number You need to change your MySQL password by set password = password (‘newpassword’); to Exit MySQL, use a command quit; or exit;

Note: MySQL maintains a history file that lists each command you have entered. You can cycle through the older commands with the up and down arrow keys. If you leave yourself logged in, anyone else can view your history file.

1

Semester 1, 2020

D ATABASE AND TABLE When you have successfully logged on to MySQL (you will see the mysql> prompt), do the followings: •

Find what databases are available to you SHOW databases;

•

Find what privileges you have on each of these databases SHOW GRANTS;

•

Select a database that you want to use. For example, if you want to use the database named: ‘syeom’ USE syeom (the message 'Database changed' is then displayed)

•

Create a table and put data into it by running the script named 'countries.sql' available from MyLO. Place the script into a folder, for example named 'SS7', on the Desktop. Execute the script. Check the table has been created / Find the list of tables in the selected database. SHOW tables;

•

•

Find the structure of the table that you want to see. For example, if you want to see the table ‘countries’. DESCRIBE countries;

•

Find all data in the table. SELECT * FROM countries; ‘SELECT * FROM countries;’ is a database query that we use for facilitating the databases. (Create database/table, insert data, update data, delete data, etc.) The asterisk ‘*’ mark means all in MySQL queries. Therefore, the query means: “Select all data from the table ‘countries’ ”.

2

Semester 1, 2020

WORKING WITH PHPMYADMIN You are able to do the same tasks using a web browser as well.

Connect to the phpMyAdmin (https:// is used) http://alacritas.cis.utas.edu.au also See http://alacritas.cis.utas.edu.au/phpMyAdmin

If the database has a password, then you will see this screen - enter the password and select Go.

3

Semester 1, 2020

If the database has the default 'blank' password, then you will see this screen.

Another way to see the list of the databases

Click on the database name to select the database

When you select the database in the side bar, you will see the list of tables in the selected database.

When you click a certain table, you will see all data in the selected table, which is the result of the default query: SELECT * FROM countries;

4

Semester 1, 2020

When you click a ‘Structure’ tab, you will see the structure of the selected table.

When you have successfully logged on to MySQL do the followings:

SQL COMMANDS SELECT UPDATE DELETE INSERT INTO CREATE DATABASE ALTHER DATABASE CREATE TABLE ALTER TABLE DROP TABLE CREATE INDEX DROP INDEX

Extracts data from database Updates data in a database Deletes data from a database Inserts new data into a database Creates a new database Modifies a database Creates a new table Modifies a table Deletes a table Creates an index (search key) Deletes an index

Now, we are going to practice SQL queries using ‘SS7.sql’ available from MyLO. Place the script into a folder, for example named ‘SS7’, on the Desktop. Display all records of customer table. SELECT * FROM customer; Sort - ascending: Display all records of customer table sorted in ascending order of ‘CustID’ number. SELECT * FROM customer ORDER BY CustID ASC; Sort - descending: Display all records of customer table sorted in descending order of ‘CustID’ number.

5

Semester 1, 2020

SELECT * FROM customer ORDER BY CustID DESC; Sort and Limit: Display all details of the customer who has the highest ‘CustID’ number. The LIMIT clause was used to select 1 row from the first row (the offset starts from zero - try experimenting using different numbers, for example LIMIT 2,2) SELECT * FROM customer ORDER BY CustID DESC LIMIT 0,1; Projection: Display customer ID of all customers in customer table. SELECT CustID FROM customer; Selection: Display all records of customer ID '995501' SELECT * FROM customer WHERE CustID = 995501; Projection and Selection: Display the email and last name of customer ID '995501' SELECT email, lastname FROM customer WHERE CustID = 995501; Joining tables: Display the last name of customer who is related to transaction ID '00435' SELECT customer.lastname, transaction.transactionID FROM customer, transaction WHERE customer.CustID = transaction.customerID AND transaction.transactionID = 00435;

Joining tables – left join: Display the full name of customer who have not bought anything. SELECT customer.lastname, customer.firstname FROM customer LEFT JOIN transaction ON customer.CustID = transaction.customerID WHERE transaction.quantity is NULL;

Create Table: Create Table as below. CREATE TABLE Test_Table (ID int NOT NULL AUTO_INCREMENT, varchar(255), lastname varchar(255), PRIMARY KEY (ID));

firstname

*AUTO_INCREMENT: value of the primary key field to be created automatically every time a new record is inserted. *PRIMARY KEY: a column in a table whose values uniquely identify the rows in the table. The primary key is chosen from this list of candidates based on its perceived value to the business as an identifier. Insert: Insert the following details in the table. INSERT INTO Test_Table (ID, firstname, lastname) VALUES ('', 'firstname', 'lastname'); Update: Update the inserted details with your name. UPDATE Test_Table SET firstname = 'John', lastname = 'Smith' WHERE ID = 1; Delete: Delete the row.

6

Semester 1, 2020

DELETE FROM Test_Table WHERE ID = 1;

WHERE CLAUSE It is the extractor of what you want to update/delete. It is more common to use ID (primary key) for extracting a specific record(s).

COMPARISON OPERATORS FOR WHERE CLAUSES

AGGREGATE FUNCTIONS IN MYSQL

AGGREGATE FUNCTIONS E XAMPLE AVG( COLUMN) SELECT AVG(quantity) FROM item;

7

Semester 1, 2020

COUNT( COLUMN) SELECT COUNT(customerID) AS Customer991345 FROM transaction WHERE customerID=991345;

MIN( COLUMN) SELECT number, description, MIN(quantity) FROM item;

MAX( COLUMN) SELECT number, description, MAX(quantity) FROM item;

SUM( COLUMN)& GROUP BY SELECT customerID, SUM(quantity) FROM transaction GROUP BY customerID;

COUNT( COLUMN), GROUP BY & HAVING SELECT customerID, SUM(quantity) FROM transaction GROUP BY customerID HAVING SUM(quantity) < 20;

T YPES OF JOIN & UNION

JOIN & UNION EXAMPLE Try the following queris and find out the differences.

INNER JOIN SELECT customer.email, transaction.transactionID FROM customer INNER JOIN transaction ON customer.CustID=transaction.customerID;

8

Semester 1, 2020

LEFT JOIN SELECT customer.email, transaction.transactionID FROM customer LEFT JOIN transaction ON customer.CustID=transaction.customerID;

RIGHT JOIN SELECT customer.email, transaction.transactionID FROM customer RIGHT JOIN transaction ON customer.CustID=transaction.customerID;

UNION SELECT fullname FROM student UNION SELECT fullname FROM student_HBT;

Let’s take another exercise. When you have classics as follows:

Run different commands as below: ALTER TABLE classics CHANGE type category VARCHAR(16); ALTER TABLE classics DROP year; //removing a column INDEX The way to achieve fast searches is to add an index. There are different index types such as a regular INDEX, PRIMARY KEY, and FULLTEXT. Multiple occurrences of a value may occur within that column by adding “ADD INEX”.

9

Semester 1, 2020

Two are Equivalent: ALTER TABLE classics_2 ADD INDEX(author(20)); CREATE INDEX author ON classics_2 (author(20));

An example with ‘countries’

10

Semester 1, 2020

EXERCISES 1) Working with the Database (tables: customer, transaction, and item) Using the command line interface to MySQL write statements to answer each of the following questions (the exact SQL required is given in the lecture notes). Write it down in the table below. It is recommended to use two terminals for your convenience: One terminal is for viewing the related tables, the other is for developing your answers.

Question

SQL

Who are all our customers? (display only customer name: first name and last name) What is the first name of the customer ID ‘995501’? What Items did Customer ID ‘991345’ buy? (display item ID) What transactions (transaction ID) are related to a Staple Gun (use description in WHERE clauses)? Which customer(s) have no record for transaction? (display customer name: first name and last name) What item (number and description) was sold in transaction ‘00435’? Who (first name and last name) bought the items in transaction ID ‘00122’? Which item(s) have not been sold yet? (display description and item ID) What items have sold 15 or more (Display item ID)? 2) Working with the Database (table: countries)

List the 2 letter IOS2code and the names for each country in the countries table. What is the capital city in United Kingdom? What is the latitude and longitude of Korea, Republic of? Remember keep a copy of your work, and delete your files from the lab computer before leaving the lab.

11

Semester 1, 2020

SELF-STUDY 7 AJAX WITH DATABASE The learning activities in this self-study (a.k.a PRIOR to Tutorial) are designed to consolidate and extend your understanding of the topics covered in lectures in week 7. In this self-study, we are going to learn how to use Asynchronous JavaScript and XML (AJAX) function in jQuery with database. We studied the basic of AJAX in week 4. This activity is based on the assumption that you have completed SS4_AJAX. The self-study includes

example code that checks the existing username in database by using shorthand version of AJAX ($.get). You should complete all activities and questions until the tutorial in week 8.

AJAX WITH DATABASE You are required to download files uploaded on MyLO : checker.php (Also, you will need your own db_conn.php), SS7_1.html. In this example, we are going to use the table ‘users’.

If you have created one before and deleted the table, you can use the same table that you created before. If you have deleted or not created yet, please create the table.

SS7_1.HTML This file contains the HTML form with button. This example code does not check every time user types something on username field. The entered username will be checked when the user clicks the ‘Check’ button. Note: You don’t have to change anything.

Self Study 7



Self Study 7

Username: Check Password:



CHECKER.PHP This file contains the sql query that checks the username that is sent from the form in SS7_1.html. Note: You don’t have to change anything.

2.

Return rows from the table in the database. (SS7_2.php)

The below shows how to return rows of the search result from the table in database.

Format the output to give the data meaning, for example: Customer number: 990021 Last Name: Jones First Name: Jakob Email: [email protected] Change the query ($query) and see the differences:

1. “SELECT * FROM customer ORDER BY customer.CustID ASC” 2. “SELECT * FROM customer ORDER BY customer.CustID DESC” 3. “SELECT * FROM customer ORDER BY customer.email ASC” References new mysqli(): establish connection to database. http://www.php.net/manual/en/mysqli.construct.php $mysqli->query(): send a query and receive a result. http://php.net/manual/en/mysqli.query.php num_rows: return the number of rows in the result set. http://php.net/manual/en/mysqli-result.num-rows.php fetch_array(MYSQLI_NUM)/(MYSQLI_ASSOC)/(MYSQLI_BOTH): fetch a result row as an associative, a numeric array, or both http://php.net/manual/en/mysqli-result.fetch-array.php

ACTIVITY 2 Continuation of your activity from SS7. Check the current structure of classics_2 (describe classics_2) and add another column called isbn as primary key. (See below)

2

Semester 1, 2020

After inserting a few data into it update the table by setting a value to isbn (see below).

The below is the output when is checked. Mind you that this example has only 1 item inserted. Your data might be different to this.

Remember keep a copy of your work, and delete your files from the lab computer before leaving the lab.

3...