Bài giảng Web technologies and e-Services - Bài 4, Phần 1: MySQL - Nguyễn Bình Minh
25/03/2021
IT4409: Web Technologies and e-Services
2020-1
MySQL
•Instructor: Dr. Thanh-Chung Dao
•Slides by Dr. Binh Minh Nguyen
Department of Information Systems
School of Information and Communication Technology
Hanoi University of Science and Technology
1
In this lecture you will learn
§ What is SQL
§ How to access mySQL database
§ How to create a basic mySQL database
§ How to use some basic queries
§ How to use PHP and mySQL
2
1
25/03/2021
Introduction to SQL
SQL is an ANSI (American National Standards Institute) standard computer language
for accessing and manipulating databases.
•
•
SQL stands for Structured Query Language
using SQL can you can
§ access a database
§ execute queries, and retrieve data
§ insert, delete and update records
SQL works with database programs like MS Access, DB2, Informix, MS SQL Server,
Oracle, Sybase, mySQL, etc.
•
Unfortunately, there are many different versions. But, they must support the same major
keywords in a similar manner such as SELECT, UPDATE, DELETE, INSERT, WHERE, etc.
Most of the SQL database programs also have their own proprietary extensions!
3
SQL Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g.
"Customers" or "Orders"). Tables contain records (rows) with data.
For example, a table called "Persons":
LastName
Hansen
FirstName
Ola
Address
City
Timoteivn 10
Borgvn 23
Storgt 20
Sandnes
Sandnes
Stavanger
Svendson
Pettersen
Tove
Kari
The table above contains three records (one for each person) and four columns (LastName,
FirstName, Address, and City).
4
2
25/03/2021
SQL Queries
With SQL, you can query a database and have a result set returned.
A query like this:
SELECT LastName FROM Persons;
gives a result set like this:
LastName
Hansen
Svendson
Pettersen
The mySQL database system requires a semicolon at the end of
the SQL statement!
5
SQL Data Languages
The query and update commands together form the Data Manipulation Language
(DML) part of SQL:
• SELECT - extracts data from a database table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• INSERT INTO - inserts new data into a database table
The Data Definition Language (DDL) part of SQL permits database tables to be created
or deleted:
• CREATE TABLE - creates a new database table
• ALTER TABLE - alters (changes) a database table
• DROP TABLE - deletes a database table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index
*Here we will use some of them in mySQL
6
3
25/03/2021
Logging into mySQL Server
Log into your mySQL server from Linux/Windows
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 209201 to server version: 5.0.22
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
From here you can create, modify, and drop tables, and modify the data in your tables.
But first, you must specify which database on the server you want to use (you have only
one, however).
mysql> use martin;
Database changed
7
Creating a Table
You can create a table you might use for the upcoming project. For example,
mysql> CREATE TABLE students(
Hit Enter after each line
-> id INT NOT NULL AUTO_INCREMENT,
(if you want). MySQL
-> f_name VARCHAR(48),
doesn’t try to interpret the
-> l_name VARCHAR(48),
command itself until it
sees a semicolon (;)
-> MSSV INT,
-> email VARCHAR(48),
(The “->” characters you
-> PRIMARY KEY(id));
see are not typed by you.)
Query OK, 0 rows affected (0.02 sec)
*If the server gives you a big ERROR, just try again from the top!
8
4
25/03/2021
Viewing The Table Structure
Use DESCRIBEto see the structure of a table
mysql> DESCRIBE students;
+------------+-------------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+------------+-------------+------+-----+---------+----------------+
| num
| int(11)
| NO
| PRI | NULL
| auto_increment |
| f_name
| l_name
| student_id | int(11)
| varchar(48) | YES
| varchar(48) | YES
|
|
|
|
| NULL
| NULL
| NULL
| NULL
|
|
|
|
|
|
|
|
| YES
| email | varchar(48) | YES
+------------+-------------+------+-----+---------+----------------+
9
Inserting Data
Using INSERT INTO you can insert a new row into your table. For example,
mysql> INSERT INTO students
Query OK, 1 row affected (0.00 sec)
Using SELECT FROM you select some data from a table.
mysql> SELECT * FROM students;
+-----+---------+--------+------------+----------------------+
| num | f_name | l_name | student_id | email
+-----+---------+--------+------------+----------------------+
|
|
+-----+---------+--------+------------+----------------------+
1 row in set (0.00 sec)
10
5
25/03/2021
Inserting Some More Data
You can repeat inserting until all data is entered into the table.
mysql> INSERT INTO students
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM students;
+-----+---------+--------+------------+----------------------+
| num | f_name | l_name | student_id | email
|
+-----+---------+--------+------------+----------------------+
|
|
1 | Russell | Martin |
2 | James | Bond
396640 | [email protected] |
|
|
+-----+---------+--------+------------+----------------------+
2 rows in set (0.00 sec)
Note: The value “NULL” in the “num” field is automatically replaced by
the SQL interpreter as the “auto_increment” option was selected when
the table was defined.
11
Getting Data Out of the Table
•
The SELECT command is the main way of getting data out of a table, or set of tables.
SELECT * FROM students;
Here the asterisk means to select (i.e. return the information in) all columns.
You can specify one or more columns of data that you want, such as
SELECT f_name,l_name FROM students;
+---------+--------+
| f_name | l_name |
+---------+--------+
| Russell | Martin |
| James
| Bond
|
+---------+--------+
2 rows in set (0.00 sec)
12
6
25/03/2021
Getting Data Out of the Table (cont.)
• You can specify other information that you want in the query using the WHERE
clause.
SELECT * FROM students WHERE l_name=‘Bond’;
+-----+---------+--------+------------+----------------------+
| num | f_name | l_name | student_id | email
+-----+---------+--------+------------+----------------------+
|
|
|
|
+-----+---------+--------+------------+----------------------+
1 row in set (0.00 sec)
SELECT student_id, email FROM students WHERE l_name=‘Bond’;
+------------+----------------------+
| student_id | email
+------------+----------------------+
|
|
|
+------------+----------------------+
1 row in set (0.00 sec)
13
Altering the Table
The ALTER TABLEstatement is used to add or drop columns in an existing table.
mysql> ALTER TABLE students ADD date DATE;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM students;
+-----+---------+--------+------------+----------------------+------+
| num | f_name | l_name | student_id | email
| date |
+-----+---------+--------+------------+----------------------+------+
|
|
1 | Russell | Martin |
2 | James | Bond
396640 | [email protected] | NULL |
|
+-----+---------+--------+------------+----------------------+------+
2 rows in set (0.00 sec)
14
7
25/03/2021
Updating the Table
The UPDATEstatement is used to modify data in a table.
mysql> UPDATE students SET date='2007-11-15' WHERE num=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM students;
+-----+---------+--------+------------+----------------------+------------+
| num | f_name | l_name | student_id | email
| date
|
+-----+---------+--------+------------+----------------------+------------+
|
|
1 | Russell | Martin |
2 | James | Bond
396310 | [email protected] | 2007-11-15 |
|
|
+-----+---------+--------+------------+----------------------+------------+
2 rows in set (0.00 sec)
Note that the default date format is “YYYY-MM-DD” and I don’t believe this
default setting can be changed.
15
Deleting Some Data
The DELETEstatement is used to delete rows in a table.
mysql> DELETE FROM students WHERE l_name='Bond';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students;
+-----+---------+--------+------------+----------------------+------------+
| num | f_name | l_name | student_id | email
+-----+---------+--------+------------+----------------------+------------+
| date
|
|
+-----+---------+--------+------------+----------------------+------------+
1 row in set (0.00 sec)
16
8
25/03/2021
The Final Table
We’ll first add another column, update the (only) record, then insert more data.
mysql> ALTER TABLE students ADD gr INT;
Query OK,
1
row affected (0.01 sec)
Duplicates: Warnings:
FROM students;
+-----+---------+--------+------------+----------------------+------------+------+
Records:
1
0
0
mysql> SELECT
*
|
num f_name l_name student_id email date gr
+-----+---------+--------+------------+----------------------+------------+------+
+-----+---------+--------+------------+----------------------+------------+------+
row in set (0.00 sec)
|
|
|
|
|
|
|
|
1
|
|
|
|
|
|
|
1
mysql> UPDATE students SET gr=3 WHERE num=1;
Query OK, row affected (0.00 sec)
1
Rows matched:
mysql> SELECT
1
*
Changed:
1
Warnings: 0
FROM students;
+-----+---------+--------+------------+----------------------+------------+------+
num f_name l_name student_id email date gr
+-----+---------+--------+------------+----------------------+------------+------+
+-----+---------+--------+------------+----------------------+------------+------+
row in set (0.00 sec)
|
|
|
|
|
|
|
|
|
1
|
|
|
|
|
|
3 |
1
11-15', 1);
.
.
.
.
.
.
17
The Final Table (cont.)
.
.
. .
.
.
mysql> INSERT INTO students VALUES(NULL,'Hugh,'Milner',75849789,'hugh@poughkeepsie.ny',
CURRENT_DATE, 2);
Note:
CURRENT_DATE is a built-in SQL command which (as expected)
gives the current (local) date.
mysql> SELECT
+-----+---------+----------+------------+----------------------------+------------+------+
num f_name l_name student_id email date gr
+-----+---------+----------+------------+----------------------------+------------+------+
* FROM students;
|
|
|
|
|
|
|
|
|
|
|
|
1
5
3
4
|
|
|
|
Russell
Kate
|
|
|
|
Martin
Ash
|
|
|
|
396310
124309
7
|
|
|
|
|
|
|
|
2007-11-15
2007-11-16
2007-11-15
2007-11-16
|
|
|
|
3
|
|
3
James
Bob
Bond
1|
3
Jones
12190
|
|
|
|
6
7
|
|
Pete
|
|
Lofton
|
|
76
|
|
|
|
2007-11-17
2007-11-17
|
|
2
Polly
Crackers
1717
1|
|
8
|
Hugh
|
Milner
|
75849789
|
|
2007-11-17
|
2
|
+-----+---------+----------+------------+----------------------------+------------+------+
rows in set (0.00 sec)
7
mysql> exit
Bye
18
9
25/03/2021
Other SQL Commands
• SHOW tables; gives a list of tables that have been defined in the database
• ALTER TABLE students DROP email; would drop the “email” column from all
records
• DROP TABLE students; deletes the entire “students” table, and its definition
(use the DROP command with extreme care!!)
• DELETE FROM students; removes all rows from the “students” table (so once
again, use the DELETE command with great caution), the table definition
remains to be used again
• A more useful command is something like
DELETE FROM students WHERE (num > 5) AND (num <= 10);
which selectively deletes students based on their “num” values (for example).
• HELP; gives the SQL help
• HELP DROP; gives help on the DROP command, etc.
19
Backing up/restoring a mySQL database
• You can back up an entire database with a command such as
mysqldump –h mysql –u martin martin > backup.sql
(Run from the Unix command line.)
• This gives a script containing SQL commands to reconstruct the table structure
(of all tables) and all of the data in the table(s).
• To restore the database (from scratch) you can use this type of Unix command:
mysql –h mysql –u martin martin < backup.sql
(Use with caution, as this can overwrite your database.)
• Other commands are possible to backup/restore only certain tables or items in
tables, etc. if that is what you desire. For example
mysqldump –h mysql –u martin martin books clients> backup.sql
stores information about the “books” and “clients” tables in the “martin” database.
20
10
25/03/2021
Backing up/restoring a mySQL database (cont.)
Run from the Windows command line:
• Run the mysqldump.exe program using the following arguments:
mysqldump.exe –e –u[username] -p[password] -h[hostname] [database
name] > C:\[filename].sql
• Run the mysql.exe program using the following arguments.
mysql –u[user name] -p[password] -h[hostname] [database name]<
C:\[filename].sql
21
Putting Content into Your Database with PHP
We can simply use PHP functions and mySQL queries together:
• Connect to the database server and login (this is the PHP command to do so)
mysql_connect("host","username","password");
Host:
mysql
martin
martin
----
• Choose the database
mysql_select_db("database");
Database:
Username:
Password:
• Send SQL queries to the server to add, delete, and modify data
mysql_query("query"); (use the exact same query string as you would
normally use in SQL, without the trailing semi-colon)
• Close the connection to the database server (to ensure the information is stored properly)
mysql_close();
• Note: For this to work properly on the URL server
22
11
25/03/2021
Student Database: data_in.php
<html>
<head>
<title>Putting Data in the DB</title>
</head>
<body>
<?php
/*insert students into DB*/
if(isset($_POST["submit"]))
{
$db = mysql_connect("mysql", "martin");
mysql_select_db("martin");
$date=date("Y-m-d"); /* Get the current date in the right SQL format */
$sql="INSERT INTO students VALUES(NULL,'" . $_POST["f_name"] . "','" .
$_POST["l_name"] . "'," . $_POST["student_id"] . ",'" . $_POST["email"] .
"','" . $date . "'," . $_POST["gr"] . ")";
/* construct the query */
mysql_query($sql);
mysql_close();
/* execute the query */
echo"<h3>Thank you. The data has been entered.</h3> \n";
echo'<p><a href="data_in.php">Back to registration</a></p>' . "\n";
echo'<p><a href="data_out.php">View the student lists</a></p>' ."\n";
}
23
Student Database: data_in.php
else
?>
{
<h3>Enter your items into the database</h3>
<form action="data_in.php" method="post">
First Name: <input type="text" name="f_name" /> <br/>
Last Name: <input type="text" name="l_name" /> <br/>
ID: <input type="text" name="student_id" /> <br/>
email: <input type="text" name="email" /> <br/>
Group: <select name="gr">
<option value ="1">1</option>
<option value ="2">2</option>
<option value ="3">3</option>
</select><br/><br/>
<input type="submit" name="submit" /> <input type="reset" />
</form>
<?php
}
/* end of "else" block */
?>
</body>
</html>
view the output page
24
12
25/03/2021
Getting Content out of Your Database with PHP
Similarly, we can get some information from a database:
• Connect to the server and login, choose a database
mysql_connect("host","username","password");
mysql_select_db("database");
• Send an SQL query to the server to select data from the database into an array
$result=mysql_query("query");
• Either, look into a row and a fieldname
$num=mysql_numrows($result);
$variable=mysql_result($result,$i,"fieldname");
• Or, fetch rows one by one
$row=mysql_fetch_array($result);
• Close the connection to the database server
mysql_close();
25
Student Database: data_out.php
<html>
<head>
<title>Getting Data out of the DB</title>
</head>
<body>
<h1> Student Database </h1>
<p> Order the full list of students by
<a href="data_out.php?order=date">date</a>,
<href="data_out.php?order=student_id">id</a>, or
by <a href="data_out.php?order=l_name">surname</a>.
</p>
<p>
<form action="data_out.php" method="post">
Or only see the list of students in group
<select name="gr">
<option value ="1">1</option>
<option value ="2">2</option>
<option value ="3">3</option>
</select>
<br/>
<input type="submit" name="submit" />
</form>
</p>
26
13
25/03/2021
Student Database: data_out.php
<?php
/*get students from the DB */
$db = mysql_connect("mysql","martin");
mysql_select_db("martin", $db);
switch($_GET["order"]){
case 'date':
$sql = "SELECT * FROM students ORDER BY date"; break;
case 'student_id':
$sql = "SELECT * FROM students ORDER BY student_id";
break;
case 'l_name': $sql = "SELECT * FROM students ORDER BY l_name"; break;
default: $sql = "SELECT * FROM students"; break;
}
if(isset($_POST["submit"])){
$sql = "SELECT * FROM students WHERE gr=" . $_POST["gr"];
}
$result=mysql_query($sql);
/* execute the query */
while($row=mysql_fetch_array($result)){
echo "<h4> Name: " . $row["l_name"] . ', ' . $row["f_name"] . "</h4> \n";
echo "<h5> ID: " . $row["student_id"] . "<br/> Email: " . $row["email"] .
"<br/> Group: " . $row["gr"] . "<br/> Posted: " . $row["date"] . "</h5> \n";
}
mysql_close();
?>
</body>
view the output page
</html>
27
Verifying input/output and database security
•On the previous examples, I have done no error checking to verify that the
database operations were successful (which should normally be performed).
•I have also done nothing in regards to database security issues and so forth.
(I will say more about this later.)
28
14
25/03/2021
Can Do Even More with PHP
• Can create tables in PHP
• Can delete rows and columns
• Can make updates
• Can make queries to several tables
• Can get connected to several databases
* Find more information on PHP/mySQL
29
Learning Outcomes
In these last lectures you have learned
§ What is SQL
§ How to access mySQL database
§ How to create a basic mySQL database
§ How to use some basic queries
§ How to use PHP and mySQL
30
15
16 trang
19440
Free
Bạn đang xem tài liệu "Bài giảng Web technologies and e-Services - Bài 4, Phần 1: MySQL - Nguyễn Bình Minh", để tải tài liệu gốc về máy hãy click vào nút Download ở trên
File đính kèm:
- bai_giang_web_technologies_and_e_services_bai_4_phan_1_mysql.pdf
