Chapter 4:
MYSQL
Objectives
After taking this chapter, you should be able to:
Creating Database and tables
Inserting Records
Selecting Data
Using Conditionals
Using Like and Not Like
Sorting Query Results
Limiting Query Results
Updating Data
Deleting Data
Using Functions
2
Introduction
This chapter provides a quick introduction to
MySQL. The focus there is on two topics: using
MySQL’s rules and data types to define a database,
and how to interact with the MySQL server. In this
chapter you’ll learn all the SQL you need to know
to create tables, populate them, and run other
basic queries. 3
Cont.
SQL, stands for Structured Query
Language, is a group of special words used
exclusively for interacting with databases.
Every major database uses SQL, and
MySQL is no exception. There are multiple
versions of SQL and MySQL has its own
variations on the SQL standards, but SQL is
still surprisingly easy to learn and use. 4
Creating Database and
Tables
The first logical use of SQL will be to create a
database. The syntax for creating a new
database is simply:
CREATE DATABASE databasename
The CREATE term is also used for making
tables: 5
To Create Databases
CREATE DATABASE CMS;
USE sitename;
This first line creates the database (assuming that
you are connected to MySQL as a user with
permission to create new databases).
The second line tells MySQL that you want to work
within this database from here on out. Remember
that within the MySQL client, you must terminate
every SQL command with a semicolon, although
6
Creating Tables
CREATE TABLE users(
user_id int unsigned PRIMARY KEY AUTO_INCREMENT,
First_name varchar(50) not null,
Last_name varchar(50) not null,
Gender varchar(50) not null,
email varchar(100) not null,
mobile int not null,
age int not null,
username varchar(50) not null,
pass varchar(100) not null,
registred_date Date not null); 7
Confirm the existing Table
SHOW TABLES;
SHOW COLUMNS FROM users;
The SHOW command reveals the
tables in
a database or the column names and
types in a table. 8
Inserting Records
After a database and its table(s) have
been created, you can start populating
them using the INSERT command. There
are two ways that an INSERT query can be
written. With the first method, you name
the columns to be populated:.
9
INSERT INTO tablename (column1,
Cont.’
The second format for inserting
records is not to specify any columns
at all but to include values for every
one:
INSERT INTO tablename VALUES
(value1, value2, value3, …) 10
To Insert data into a table
Insert one row of data into the users table,
naming the columns to be populated:
INSERT INTO users (first_name,
last_name, Gender, Email, Mobile, age,
username, PASS, registred_date)
VALUES ("Ahmed", "Hassan", "Male",
"Ahmed@[Link]", 618345678, 25,
11
"Ahmed", SHA1("Ahmed@123"), NOW());
Cont.’
For the password and registred_date
columns, two functions are being used to
generate the values (see the sidebar “Two
MySQL Functions”). The SHA1() function will
encrypt the password (Ahmed@123 in this
example).
The NOW() function will set the registred
12
_date as this moment
Cont.’
2. Insert one row of data into the users
table, without naming the columns
INSERT INTO users VALUES (NULL,
'Zoe', ‘Ali',
‘ali@[Link]',
SHA1('mojito'), NOW()); 13
Cont.’
In this second syntactical example, every
column must be provided with a value.
The user_id column is given a NULL value,
which will cause MySQL to use the next
logical number, per its AUTO_INCREMENT
description. In other words, the first record
will be assigned a user_id of 1, the second,
14
2, and so on.
Insert Several Values into the
Users table
INSERT
INTO users (first_name, last_name, gender, ema
il, mobile, age, username, pass, Registered_dat
e)
VALUES
('Abdirizak', 'Hassan', 'Male', 'Abdi@[Link]
m', 615894590, 35, 'Abdi', 'Abdi@123', NOW()),
'Hamdi', 'Hassan', 'Female', 'Hamdi@[Link]',
615459089, 25, 'hamdi', 'hamdi@123', NOW()), ('A
hmed', 'Hussein', 'Male', 'Ahmed@[Link]', 77
15
Selecting Data
Now that the database has some records in it,
you can retrieve the stored information with the
most used of all SQL terms, SELECT.
A SELECT query returns rows of records using
the syntax
SELECT which_columns FROM which_table
The simplest SELECT query is:
SELECT * FROM tablename 16
To select data from a table:
1. Retrieve all the data from users' table:
SELECT * FROM users;
The asterisk means that you want to view every
column
2. Retrieve just the first and last names from
users
SELECT first_name, last_name FROM
users;
17
C# - Connection with MySQL
First make sure you have installed MySQL Connector.
Add connectionStrings.
- connectionStrings is a string used to establish a
connection between an application and a database.
It typically contains essential information such as the
servername, authentication credentials, the database name,
and additional parameters that define how the connection
should behave
This will help keep our code easier to read and more efficient.
Cont.
//Add MySql Library
using [Link];
Cont.
Store your connectionString in [Link] file
<connectionStrings>
<add name="DBCS" connectionString=
"Server=localhost;Database=cms;uid=root;pwd=‘’
”
providerName="[Link]" />
</connectionStrings>
Break
MySQL Operators
Operator Meaning
= Equals
!= Not equal to
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
IS NOT NULL Has a value
IS NULL Does not have a value
BETWEEN Within a range
NOT BETWEEN Outside of a range
IN Within a list of values
OR (also ||) Where one of two conditionals is true 23
AND (also &&) Where both conditionals are true
Using Conditionals
The SELECT query as used thus far will always retrieve
every record from a table. But often you’ll want to limit what
rows are returned, based upon certain criteria. This can be
accomplished by adding conditionals to SELECT queries.
These conditionals use the SQL term WHERE and are written
much as you’d write a conditional in PHP.
SELECT column_name FROM table_name WHERE
condition(s)
SELECT First_name,age FROM users WHERE age=25;
24
Cont.’
The operators can be used together,
along with parentheses, to create more
complex expressions:
SELECT * FROM users WHERE age
BETWEEN 20 and 30 AND
first_name='Aisha'
25
SELECT user_id, first_name,
Using LIKE and NOT LIKE
SELECT * FROM users WHERE last_name LIKE ‘%Ahmed'
This query will return all rows who's last_name value begins with
Ahmed. Because it’s a case-insensitive search by default, it
would also apply to names that begin with smith.
Select the name for every record whose email address is not of
the form something@[Link]
SELECT first_name,last_name,email from users WHERE
email not LIKE '%@[Link]'
To rule out the presence of values in a string, use NOT LIKE with
26
the wildcard.
Sorting Query Results
By default, a SELECT query’s results will be returned in
a meaningless order. To sort them in a meaningful way,
use an ORDER BY clause.
SELECT * FROM tablename ORDER BY column
SELECT * FROM users ORDER BY first_name
The default order when using ORDER BY is ascending
(abbreviated ASC), meaning that numbers increase
from small to large, dates go from older to most recent,
and text is sorted alphabetically. You can reverse this by
27
specifying a descending order (abbreviated DESC).
Cont.’
Show all of the non-Simpson users by date
registered
SELECT * FROM users WHERE last_name !=
‘Ahmed'
ORDER BY Registered_date DESC;
You can use an ORDER BY on any column type,
including numbers and dates. The clause can
also be used in a query with a conditional,
28
placing the ORDER BY after the WHERE.
Limiting Query Results
Another SQL clause that can be added to most
queries is LIMIT. In a SELECT query, WHERE
dictates which records to return, and ORDER BY
decides how those records are sorted, but LIMIT
states how many records to return. It is used like
so:
SELECT * FROM tablename LIMIT x
In such queries, only the initial x records from the
29
query result will be returned.
Cont.’
Using this format
SELECT * FROM tablename LIMIT x, y
you can have y records returned, starting at x. To have records 11
through 20 returned, you would write
SELECT * FROM tablename LIMIT 11, 20
You can use LIMIT with WHERE and/or ORDER BY clauses, always placing
LIMIT last.
SELECT which_columns FROM tablename WHERE conditions
ORDER BY column LIMIT x
SELECT first_name FROM users ORDER BY Registered_date DESC
30
LIMIT 5;
Updating
Once tables contain some data, you have the
potential need to edit those existing records.
This might be necessary if information was
entered incorrectly or if the data changes (such
as a last name or email address). The syntax for
updating records is
UPDATE tablename SET column=value
You can alter multiple columns at a single time,
31
Cont.’
You will almost always want to use a WHERE
clause to specify what rows should be updated;
otherwise, the change would be applied to every
record.
UPDATE tablename SET column2=value
WHERE column5=value
UPDATE users SET last_name=‘faarax’
WHERE user_id = 1 32
Deleting Data
Along with updating existing records, another step you might
need to take is to entirely remove a record from the
database. To do this, you use the DELETE command.
DELETE FROM tablename
That command as written will delete every record in a table,
making it empty again.
Once you have deleted a record, there is no way of retrieving
it. In most cases you’ll want to delete individual rows, not all
of them. To do so, use a WHERE clause
33
DELETE FROM tablename WHERE condition
Cont.’
Delete the record :
DELETE FROM users WHERE user_id = 5 LIMIT 1;
Note:
The preferred way to empty a table is to use TRUNCATE:
TRUNCATE TABLE tablename
■ To delete all of the data in a table, as well as the table itself, use
DROP TABLE:
DROP TABLE tablename
■ To delete an entire database, including every table therein and
34
all of its data, use
Using Function
To apply a function to a column’s values, the
query would look like
SELECT FUNCTION(column) FROM
tablename
To apply a function to one column’s values while
also selecting some other columns, you can write
a query like either of these:
◆ SELECT *, FUNCTION(column) FROM tablename
◆ SELECT column1, FUNCTION(column2),
column3 FROM tablename
e.g: SELECT UPPER(first_name) FROM users
35
Aliases
alias is a temporary name assigned to a table or
column for the duration of a query. Aliases make
complex queries easier to read and can help
organize output neatly.
Aliases are created using the term AS:
SELECT Registered_date AS reg FROM users
SELECT
first_name AS name FROM users WHERE last_name=
'Ahmed’; 36
ANY
QUESTIONS
و ا ل س ال م
عليكم ورحمة
الله وبركاته