Web Programming
01 MySQL Introduction
Dr. Mostafa Elgendy
2
Agenda
❖ Introduction
❖ MySQL Introduction
❖ SQL Introduction
❖ Summary
Web Programming 28-Mar-23
3
Introduction
Web Programming 28-Mar-23
4
Introduction
❖ Database: A collection of records or data stored in a computer system and
organized in such a way that it can be quickly searched and information can be
rapidly retrieved.
❖ Database Management System (DBMS): A software facilitate the creation and
maintenance of a database.
❖ Database System: The DBMS software together with the data itself. Sometimes,
the applications are also included.
❖ Software + Database
Web Programming 28-Mar-23
5
Introduction
Web Programming 28-Mar-23
6
Introduction
❖ MySQL is probably the most popular database management system(DBMS) for web
servers.
❖ Developed in the mid-1990s.
❖ Free to use
❖ MySQL is very friendly to PHP, the most appreciated language for web development.
❖ MySQL is also highly scalable
❖ It can grow with your website.
Web Programming 28-Mar-23
7
Introduction
Web Programming 28-Mar-23
8
Introduction
❖ Database: The overall container for a collection of MySQL data
❖ Table: A subcontainer within a database that stores the actual data
❖ Row: A single record within a table, which may contain several fields
❖ Column: The name of a field within a row
Web Programming 28-Mar-23
9
MySQL Introduction
Web Programming 28-Mar-23
10
Accessing MySQL
❖ There are three main ways you can interact with MySQL:
❖ Using a command line
❖ Web interface such as phpMyAdmin
❖ Through a programming language like PHP
Web Programming 28-Mar-23
11
Using a command line
❖ From your XAMPP installation directory, you will be able to
access the MySQL executable from the following directory :
❖ C:\xampp\mysql\bin
❖ By default, the initial MySQL user is root, and it will have a
default password of mysql which is nothing.
❖ mysql -u root
Web Programming 28-Mar-23
12
Using a command line
❖ To be sure everything is working as it should be
❖ SHOW databases;
Web Programming 28-Mar-23
13
Using a command line
❖ The semicolon is used by MySQL to separate or end commands.
❖ Forgetting to enter it,
❖ MySQL will issue a prompt and wait for you to do so
❖ There are six different prompts that MySQL may present you with.
Web Programming 28-Mar-23
14
Using phpMyAdmin
❖ It is simpler to use a program
such as phpMyAdmin to
manage your databases and
tables.
❖ To do this, type the following
to open the program
❖ https://localhost/phpmyadmin
Web Programming 28-Mar-23
15
MySQL Commands
❖ Remember a couple of points about MySQL commands:
❖ SQL commands and keywords are case-insensitive.
❖ CREATE, create, and CrEaTe all mean the same thing..
❖ Table names are case-sensitive on Linux and macOS but case-insensitive
on Windows.
❖ So, The recommended style is to use lowercase for table names.
Web Programming 28-Mar-23
16
MySQL Commands
❖ Creating a database:
❖ The following command to create a new database called
publications:
CREATE DATABASE publications;
MySQL
Query OK, 1 row affected (0.00 sec)
output
Web Programming 28-Mar-23
17
MySQL Commands
❖ Using database:
❖ The following command to use the database:
USE publications;
MySQL
Database changed
output
Web Programming 28-Mar-23
18
MySQL Commands
❖ Creating users:
❖ To create a user, issue the following command:
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password’;
MySQL
Query OK, 1 row affected (0.002 sec)
output
Web Programming 28-Mar-23
19
MySQL Commands
❖ Table:
❖ A table is a thing that exists and is distinguishable -- an object, something
in the environment. CREATE TABLE classics (
author VARCHAR(128),
❖ Examples : book, item, student title VARCHAR(128),
type VARCHAR(16),
year CHAR(4))
MySQL
Query OK, 1 row affected (0.028 sec)
output
Web Programming 28-Mar-23
20
MySQL Commands
❖ Renaming a table:
❖ Used to change the name of the table
ALTER TABLE classics RENAME pre1900;
MySQL
Query OK, 1 row affected (0.028 sec)
output
Web Programming 28-Mar-23
21
MySQL Commands
❖ Attribute:
❖ An entity has a set of attributes which defines property of an entity.
❖ Attribute has :
❖ Name, Data type, and value.
❖ Example : classics entity has the following attributes (TITLE, TYPE,
AUTHOR, YEAR)
Web Programming 28-Mar-23
22
MySQL: describe table
❖ To check whether your new table has been created:
DESCRIBE classics;
MySQL
Web Programming 28-Mar-23
23
MySQL: Data Types
❖ VARCHAR and CHAR:
❖ Both accept text strings and impose a limit on the size of the field
❖ The difference is that every string in a CHAR field has the specified size.
❖ If you put in a smaller string, it is padded with spaces.
❖ A VARCHAR field does not pad the text; it lets the size of the field vary to fit the text.
Web Programming 28-Mar-23
24
MySQL: Data Types
❖ Numeric data types:
❖ MySQL supports various numeric data types, from a single byte up to
double precision floating-point numbers
Web Programming 28-Mar-23
25
MySQL: Data Types
❖ Numeric data types:
❖ To specify whether a data type is unsigned, use the UNSIGNED qualifier.
❖ You can also pass an optional number as a parameter
CREATE TABLE tablename (fieldname INT UNSIGNED);
CREATE TABLE tablename (fieldname INT(4));
MySQL
Web Programming 28-Mar-23
26
MySQL: Data Types
❖ DATE and TIME types:
Web Programming 28-Mar-23
27
MySQL: attributes
❖ AUTO_INCREMENT attribute:
❖ Allows a unique number to be generated automatically when a new record
is inserted into a table.
❖ By default, the starting value for AUTO_INCREMENT is 1, and it will
increment by 1 for each new record.
Web Programming 28-Mar-23
28
MySQL: attributes
❖ Primary_Key attribute:
❖ an attribute of an entity type for which each entity must have a unique value; e.g.,
SSN of EMPLOYEE
❖ Foreign Key attribute:
❖ Attributes that define relationships between entities.
❖ The attributes of a foreign key in one entity are the attributes of a primary key in
another entity;
Web Programming 28-Mar-23
29
Relationships
❖ A connection between entity classes.
❖ One-One Relationship: (citizen – passport)
❖ One-Many Relationship: (student-Advisor, Customer-Order)
❖ Many- Many Relationship: (Student-Organization, Order-Products)
Web Programming 28-Mar-23
30
MySQL Commands
❖ Changing the data type of a column:
ALTER TABLE classics MODIFY year SMALLINT;
MySQL
Query OK, 1 row affected (0.028 sec)
output
Web Programming 28-Mar-23
31
MySQL Commands
❖ Adding a new column:
ALTER TABLE classics ADD pages SMALLINT UNSIGNED;
MySQL
Web Programming 28-Mar-23
32
MySQL Commands
❖ Renaming a column:
❖ Note the addition of VARCHAR(16) on the end of this command.
❖ That’s because the CHANGE keyword requires the data type to be
specified, even if you don’t intend to change it,
ALTER TABLE classics CHANGE type category VARCHAR(16);
MySQL
Web Programming 28-Mar-23
33
MySQL Commands
❖ Removing a column:
❖ to remove a column, use the DROP keyword.
❖ Remember that DROP is irreversible so, use it with caution
ALTER TABLE classics DROP pages;
MySQL
Web Programming 28-Mar-23
34
MySQL Commands
❖ Deleting a table:
CREATE TABLE disposable(trash INT);
SHOW tables;
DROP TABLE disposable;
SHOW tables;
MySQL
Web Programming 28-Mar-23
35
SQL Introduction
Web Programming 28-Mar-23
36
What is SQL?
❖ SQL stands for Structured Query Language.
❖ SQL lets you access and manipulate databases.
Web Programming 28-Mar-23
37
What Can SQL do?
❖ Can execute queries against a database.
❖ Can retrieve data from a database.
❖ Can insert records in a database.
❖ Can update records in a database.
Web Programming 28-Mar-23
38
What Can SQL do?
❖ Can delete records from a database.
❖ Can create new databases.
❖ Can create new tables in a database.
Web Programming 28-Mar-23
39
SQL Statements
❖ Most of the actions you need to perform on a database are done
with SQL statements.
❖ Ex: select all the records in the "Person" table.
❖ SELECT * FROM Person.
❖ Keep in Mind That SQL is not case sensitive.
Web Programming 28-Mar-23
40
SQL Statements types
SQL
DML DDL DCL Transaction control
• Select • Create • Grant • Commit
• Insert • Alter • Revoke • Rollback
• Update • Drop
• Delete
Web Programming 28-Mar-23
41
Types of SQL statements
❖ The Data Manipulation Language (DML):
❖ SELECT: extracts data from a database.
❖ UPDATE: updates data in a database.
❖ DELETE: deletes data from a database.
❖ INSERT INTO: inserts new data into a database
Web Programming 28-Mar-23
42
SQL: INSERT INTO
❖ The INSERT INTO statement is used to insert new records in a table.
❖ The first form doesn't specify the column names, only their values:
❖ INSERT INTO table_name VALUES (value1, value2, value3,...)
❖ The second form specifies both the column names and the values to
be inserted:
❖ INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
Web Programming 28-Mar-23
43
SQL: INSERT INTO example
INSERT INTO Person VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')
INSERT INTO Person (P_Id, LastName, FirstName) VALUES (5, 'Tjessem', 'Jakob')
INSERT INTO Person (LastName, FirstName) VALUES ('Tjessem', 'Jakob')
SQL
Web Programming 28-Mar-23
44
SQL: UPDATE
❖ The UPDATE statement is used to update existing records in a table.
❖ SQL UPDATE Syntax
❖ UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
❖ Note: The WHERE clause specifies which record or records that should be
updated. If you omit the WHERE clause, all records will be updated!
Web Programming 28-Mar-23
45
SQL: UPDATE example
UPDATE Person SET Address='Nissestien 67', City='Sandnes‘ WHERE
LastName='Tjessem' AND FirstName='Jakob’
SQL
Before
After
Web Programming 28-Mar-23
46
SQL: UPDATE Warning
UPDATE Person SET Address='Nissestien 67',
❖ If you omit the WHERE clause like this: City='Sandnes‘
SQL
Before
After
Web Programming 28-Mar-23
47
SQL: DELETE
❖ The DELETE statement is used to delete rows in a table.
❖ Syntax
❖ DELETE FROM table_name WHERE some_column=some_value
Web Programming 28-Mar-23
48
SQL: DELETE example
DELETE FROM Person WHERE LastName='Tjessem' AND FirstName='Jakob’
SQL
Before
After
Web Programming 28-Mar-23
49
SQL: Delete All Rows
❖ It is possible to delete all rows in a table without deleting the
table.
❖ DELETE FROM table_name or DELETE * FROM table_name
❖ Note: Be very careful when deleting records. You cannot undo
this statement!
Web Programming 28-Mar-23
50
SQL: SELECT
❖ The SELECT statement is used to select data from a database.
❖ Syntax:
❖ SELECT column_name(s) FROM table_name
❖ SELECT * FROM table_name
Web Programming 28-Mar-23
51
SQL: SELECT example
SELECT * FROM Person
SQL
Web Programming 28-Mar-23
52
SQL: SELECT example
SELECT LastName,FirstName FROM Person
SQL
Web Programming 28-Mar-23
53
SQL: WHERE Clause
❖ Is used to filter records.
❖ Syntax
❖ SELECT column_name(s) FROM table_name
WHERE column_name operator value
SELECT * FROM Person WHERE City='Sandnes'
SQL
Web Programming 28-Mar-23
54
SQL: Allowed Operators
Web Programming 28-Mar-23
55
SQL: LIKE Operator
SELECT * FROM Person WHERE City LIKE 's%'
SQL
SELECT * FROM Person WHERE City LIKE '%s'
SQL
SELECT * FROM Person WHERE City LIKE '_andnes'
MySQL
Web Programming 28-Mar-23
56
SQL: IN Operator
SELECT * FROM Person WHERE LastName IN ('Hansen','Pettersen')
SQL
Web Programming 28-Mar-23
57
SQL: AND & OR Operators
❖ The AND & OR operators are used to filter records based on
more than one condition.
❖ The AND operator displays a record if both the first condition and the
second condition is true.
❖ The OR operator displays a record if either the first condition or the
second condition is true
Web Programming 28-Mar-23
58
SQL: AND & OR Operators
SELECT * FROM Person WHERE FirstName = 'Tove‘
AND LastName = 'Svendson'
SQL
SELECT * FROM Person WHERE FirstName = 'Tove‘
OR FirstName = ‘Ola'
SQL
Web Programming 28-Mar-23
59
SQL: AND & OR Operators
SELECT * FROM Person WHERE LastName = 'Svendson
AND ( FirstName = 'Tove‘ OR LastName = ‘Ola’ )
SQL
Web Programming 28-Mar-23
60
SQL: ORDER BY Keyword
❖ The ORDER BY keyword is used to sort the result-set by a specified
column.
❖ The ORDER BY keyword sort the records in ascending order by
default.
❖ Syntax
❖ SELECT column_name(s) FROM table_name
ORDER BY column_name(s) ASC|DESC
Web Programming 28-Mar-23
61
SQL: ORDER BY example
SELECT * FROM Person ORDER BY LastName
SQL
Web Programming 28-Mar-23
62
SQL: Select from two tables
SELECT Person.LastName, Person.FirstName, Orders.OrderNo FROM
Person,Orders where Person.P_Id=Orders.P_Id
SQL
Web Programming 28-Mar-23
63
Summary
❖ Introduction
❖ MySQL Introduction
❖ SQL Introduction
❖ Summary
Web Programming 28-Mar-23
Questions
