Resources That will Make You Better at IT, Education and specifically in Digital Marketing.: MYSQL

About MYSQL

What is MySQL?

MySQL is a relational database management system
MySQL is open-source
MySQL is free
MySQL is ideal for both small and large applications
MySQL is very fast, reliable, scalable, and easy to use
MySQL is cross-platform
MySQL is compliant with the ANSI SQL standard
MySQL was first released in 1995
MySQL is developed, distributed, and supported by Oracle Corporation

MySQL is named after co-founder Monty Widenius's daughter: My

Who Uses MySQL?

Huge websites like Facebook, Twitter, Airbnb, Booking.com, Uber, GitHub, YouTube, etc.

Content Management Systems like WordPress, Drupal, Joomla!, Contao, etc.

A very large number of web developers around the world

Show Data On Your Web Site

To build a web site that shows data from a database, you will need:

An RDBMS database program (like MySQL)

A server-side scripting language, like PHP

To use SQL to get the data you want

To use HTML / CSS to style the page

What is SQL?

⦁ SQL stands for Structured Query Language

⦁ SQL lets you access and manipulate databases

What Can SQL do?

⦁ SQL can execute queries against a database

⦁ SQL can retrieve data from a database

⦁ SQL can insert records in a database

⦁ SQL can update records in a database

⦁ SQL can delete records from a database

⦁ SQL can create new databases

⦁ SQL can create new tables in a database using SQL in Your Web Site

To build a web site that shows data from a database, you will need:

⦁ An RDBMS database program (i.e. MS Access, SQL Server, MySQL)

⦁ To use a server-side scripting language, like PHP or ASP

⦁ To use SQL to get the data you want

⦁ To use HTML / CSS to style the page

RDBMS

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

⦁ SQL keywords are NOT case sensitive: select is the same as SELECT

What is RDBMS?

RDBMS stands for Relational Database Management System.
RDBMS is a program used to maintain a relational database.
RDBMS is the basis for all modern database systems such as MySQL, Microsoft SQL Server, Oracle, and Microsoft Access.
RDBMS uses SQL queries to access the data in the database.

What is a Database Table?

A table is a collection of related data entries, and it consists of columns and rows.

A column holds specific information about every record in the table.

A record (or row) is each individual entry that exists in a table.

SQL Commands

⦁ CREATE DATABASE - creates a new database

⦁ CREATE TABLE - creates a new table

⦁ INSERT INTO - inserts new data into a database

⦁ SELECT - extracts data from a database

⦁ UPDATE - updates data in a database

⦁ DELETE - deletes data from a database

⦁ ALTER DATABASE - modifies a database

⦁ ALTER TABLE - modifies a table

⦁ DROP TABLE - deletes a table

The SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new SQL database.

Syntax

CREATE DATABASE databasename;

CREATE DATABASE Example

The following SQL statement creates a database called "testDB":

Example

CREATE DATABASE testDB;

The SQL DROP DATABASE Statement

The DROP DATABASE statement is used to drop an existing SQL database.

Syntax

DROP DATABASE databasename;

The SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.

Syntax

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

column3 datatype,

....

);

SQL CREATE TABLE Example

The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:

Example

CREATE TABLE Persons (

PersonID int,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

);

Create Table Using Another Table

A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns can be selected.

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

Syntax

CREATE TABLE new_table_name AS

SELECT column1, column2,...

FROM existing_table_name

WHERE ....;

Example

CREATE TABLE TestTable AS

SELECT customername, contactname

FROM customers;

The SQL DROP TABLE Statement

The DROP TABLE statement is used to drop an existing table in a database.

Syntax

DROP TABLE table_name;

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways:

1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)

VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

Insert Data Only in Specified Columns

It is also possible to only insert data in specific columns.

The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):

Example

INSERT INTO Customers (CustomerName, City, Country)

VALUES ('Cardinal', 'Stavanger', 'Norway');

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax

SELECT column1, column2, ...

FROM table_name;

Example

SELECT CustomerName, City FROM Customers;

SELECT * FROM Customers;

The SQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, ...

FROM table_name;

Example

SELECT DISTINCT Country FROM Customers;

Example

SELECT Count(*) AS DistinctCountries

FROM (SELECT DISTINCT Country FROM Customers);

The SQL WHERE Clause

The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified condition.

WHERE Syntax

SELECT column1, column2, ...

FROM table_name

WHERE condition;

ExampleGet your own SQL Server

SELECT * FROM Customers

WHERE Country='India';

Example

SELECT * FROM Customers

WHERE CustomerID=1;

SQL Create Constraints

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

Syntax

CREATE TABLE table_name (

column1 datatype constraint,

column2 datatype constraint,

column3 datatype constraint,

....

);

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value

UNIQUE - Ensures that all values in a column are different

PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

FOREIGN KEY - Prevents actions that would destroy links between tables

CHECK - Ensures that the values in a column satisfies a specific condition

DEFAULT - Sets a default value for a column if no value is specified

CREATE INDEX - Used to create and retrieve data from the database very quickly

The SQL AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

⦁ The AND operator displays a record if all the conditions separated by AND are TRUE.

⦁ The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax

SELECT column1, column2, ...

FROM table_name

WHERE condition1 AND condition2 AND condition3 ...;

OR Syntax

SELECT column1, column2, ...

FROM table_name

WHERE condition1 OR condition2 OR condition3 ...;

NOT Syntax

SELECT column1, column2, ...

FROM table_name

WHERE NOT condition;

AND Example

The following SQL statement selects all fields from "Customers" where country is "Germany" AND city is "Berlin":

Example

SELECT * FROM Customers

WHERE Country='Germany' AND City='Berlin';

OR Example

The following SQL statement selects all fields from "Customers" where city is "Berlin" OR "München":

Example

SELECT * FROM Customers

WHERE City='Berlin' OR City='München';

The MySQL ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

ORDER BY Syntax

SELECT column1, column2, ...

FROM table_name

ORDER BY column1, column2, ... ASC|DESC;

Example

SELECT * FROM Customers

ORDER BY Country;

ORDER BY DESC Example

The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:

Example

SELECT * FROM Customers

ORDER BY Country DESC;

ORDER BY Several Columns

The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

Example

SELECT * FROM Customers

ORDER BY Country, CustomerName;

What is a NULL Value?

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

How to Test for NULL Values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

SELECT column_names

FROM table_name

WHERE column_name IS NULL;

IS NOT NULL Syntax

SELECT column_names

FROM table_name

WHERE column_name IS NOT NULL;

The IS NULL Operator

The IS NULL operator is used to test for empty values (NULL values).

The following SQL lists all customers with a NULL value in the "Address" field:

ExampleGet your own SQL Server

SELECT CustomerName, ContactName, Address

FROM Customers

WHERE Address IS NULL;

he IS NOT NULL Operator

The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).

The following SQL lists all customers with a value in the "Address" field:

Example

SELECT CustomerName, ContactName, Address

FROM Customers

WHERE Address IS NOT NULL;

The MySQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

UPDATE Table

The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.

Example

UPDATE Customers

SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'

WHERE CustomerID = 1;

UPDATE Multiple Records

It is the WHERE clause that determines how many records will be updated.

The following SQL statement will update the PostalCode to 00000 for all records where country is "Mexico":

Example

UPDATE Customers

SET PostalCode = 00000

WHERE Country = 'Mexico';

Update Warning!

Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

Example

UPDATE Customers

SET PostalCode = 00000;

The MySQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE Syntax

DELETE FROM table_name WHERE condition;

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

SQL DELETE Example

The following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table:

Example

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

Delete All Records

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name;

The following SQL statement deletes all rows in the "Customers" table, without deleting the table:

Example

DELETE FROM Customers;

The MySQL LIMIT Clause

The LIMIT clause is used to specify the number of records to return.