SQL Environment

Posted on Edited on In , , , Symbols count in article: 20k Reading time ≈ 19 mins.

Introduction

Structure Query Language Intro by University of Michigan
The Missing Link: An Introduction to Web Development and Programming
w3schools

SQL is a standard language for storing, manipulating and retrieving data in databases.

SQL Installation

Installing MAMP on a Macintosh

MAMP

After installation, open MAMP

Click on Start

Click on WebStart

Click on phpinfo

Review the configuration of the system and PHP from MAMP

Search for display_

If your display_errors and display_errors are off, scroll up and copy the path of Loaded Configuration File.

Use your text editor to open the initialize file.

Search for display_errors

Make sure display_errors and display_startup_errors are On.
Then save the file.

Stop MAMP then Start it again.

Then go to phpinfo, you can see display_errors and display_startup_errors are On.

Visit localhost:8888.
As you can see the document root is /Applications/MAMP/htdocs

Create a folder named test under /Applications/MAMP/htdocs/.
Create a file named index.php with the following content in the folder.

PHP
1
<h1>Hello from my 1st PHP web page</h1>

Visit http://localhost:8888/test/.
index.php is the default file showed to you when you visit a web path with multiple files.
You can see the web page we just created.

Installing MAMP on Windows 10

Installing MAMP on Windows 10

Installing XAMPP on Windows 10

Installing XAMPP on Windows 10

Installing LAMP On Linux

Installing LAMP On Linux

Installing Only MySQL Server

Installing MySQL Server through Homebrew

If you don’t need MAMP environment, you can install MySQL server without other services..

For Mac User:
Install Homebrew:

Shell
1
/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Install MySQL server through Homebrew:

Shell
1
brew install mysql

Set up and start MySQL service:

Shell
1
2
brew tap homebrew/services
brew services start mysql

Set your MySQL root user and password:

Shell
1
mysqladmin -u root password 'your_password'

MySQL security setting:

Shell
1
mysql_secure_installation

OR Download MySQL Server from MySQL Official Website

You can download a free MySQL database at https://dev.mysql.com/downloads/mysql/.

Click Download then click No thanks, just start my download.

After you download MySQL. Open the MySQL in your System Preferences, then Stop MySQL Server

From now on, you can start your installation.

Test your root user in the bash shell:

Shell
1
mysql -uroot -p

It works!

Exit MySQL interface:

SQL
1
exit

MySQL Security Setting

To check your MySQL version:

Shell
1
mysql --version

Create option file under /etc(May need administrator’s password):

Shell
1
sudo touch /etc/my.cnf

Show the option file permission then change it to -rw-rw-rw-:

Shell
1
2
3
ls -l /etc/my.cnf
sudo chmod 666 /etc/my.cnf
ls -l /etc/my.cnf

Open your option file:

Shell
1
sudo vim /etc/my.cnf

Copy following configuration and save the file:

  • secure_file_priv=”/tmp” means you can ONLY import file from path /tmp/
Vim
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# MySQL global config file
[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_packet=128M
secure_file_priv="/tmp"

[mysqldump]
quick

Lower the option file permission to -rw-rw-r--:

Shell
1
sudo chmod 664 /etc/my.cnf

Restart MySQL Server
MySQL installation from Home Brew
Shell
1
brew services restart mysql
MySQL installation from Official Website

Stop MySQL server:

You can see the default location of configuration file:

Click Apply:

Start MySQL server:

Import data from text file

Step 1

If you do NOT have MySQL option file (also called configuration file). Please follow the following steps. In general, MySQL which runs on the Mac OS with version >= 5.7 does not have the option file.

To check your MySQL version:

Shell
1
mysql --version

If your MySQL version is >= 5.7, click MySQL Security Setting.

Step 2

Import your data from the text file to your table:

  • Remember copy your text file such as .csv to /tmp first.
  • Your table must have the same columns as the .csv file.
  • The length of data_type of each column must be equal or longer than it in the text file.

In our example, I have a .csv file named tmpr2k_j017.csv:

Click to see Example

MAMP Management

Login MySQL server

Login mysql local server. Default root password is root.

  • MySQL default port is 3306 but MAMP uses port 8889
CLI
1
mysql -h 127.0.0.1 -P 8889 -u root -p

phpAdmin

Click on Tools on localhost:8888/MAMP/

phpAdmin - Databases

Click on Databases tab to administer databases.

phpAdmin - SQL

Click on SQL tab for SQL querying.

You can run SQL query on Terminal.

SQL
1
show databases;

OR on phpAdmin - SQL
Click on Go to execute the SQL query sentence.

SQL Terminology

  • Database: contains one or more tables
  • Relation (or table): contains tuples and attributes
  • Tuple (or row): a set of fields which generally represent an “object” like a person or a music track
  • Attribute (also column or field): one of possibly many elements of data corresponding to the object represented by the row
  • Metadata: data about data. The first row of a table in SQL.
  • Schema: The structure of the database. The collection of all metadata of a database.

Common Database Systems

  • Three major Database Management Systems in wide use
    • Oracle: Large, commercial, enterprise-style, very-tweakable
    • MySQL: Simply but very fast and scalable - commercial open source
    • SQL Server: Very nice - from Microsoft(also Access)
  • Many other smaller projects, free and open source
    • HSQL, SQLite, PostgreSQL…

http://datahub.berkeley.edu/hub/user-redirect/git-sync?repo=https://github.com/data-8/materials-sp21&subPath=materials/sp21/lab/lab01/lab01.ipynb

What is SQL?

  • SQL stands for Structured Query Language
  • SQL lets you access and manipulate databases
  • SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

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
  • SQL can create stored procedures in a database
  • SQL can create views in a database
  • SQL can set permissions on tables, procedures, and views

SQL is a Standard - BUT….

Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.

However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!

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.

Example:

SQL Concepts

Database Normalization

First Normal Form (1NF)

The First Normal Form requires the following:

  • All fields within the tables represent single data values that have been reduced to the smallest useful piece of information. This condition is referred to as atomicity. Fields cannot contain multiple values. These single values are referred to as scalar values.
  • The tables cannot have repeating fields or groups of fields. A table should not have multiple fields for the same attribute for the purpose of tracking multiple items.

For example,
Address 360 Huntington Ave., Boston, MA 02115 in 1NF:

Street Name City State ZipCode
360 Huntington Ave Boston MA 02115

If you continue to separate the Street Name into 360 and Huntington Ave, it called over-normalization.

Second Normal Form (2NF)

The Second Normal Form requires that the database meet the conditions of the First Normal Form and that:

  • All fields that are not part of a table’s key must be functionally dependent on the entire key.

Most tables in a relational database have a field or combination of fields that serves to uniquely identify each record in the table. This is called the table’s primary key. A primary key is a special index field within the table. Index fields assist the database in searching or sorting information on that field and the primary key does this while also uniquely identifying each record. Tables can also have fields that store values from another table’s primary key in order to reference records within that table. These fields are referred to as foreign keys.

A table can actually have more than one index field, including the primary key, that indicates values which must be unique for each record such as a driver’s license number or a product serial number. There can be only one primary key that uniquely identifies a specific record to other tables, however. A key or index definition can also contain more than one field so that one of the field values might be duplicated within a table but no two records can have the same combination of values in those fields. This is called a composite key.

  • confidential information should never be used as a table’s primary key.
  • More often, an extra field is added to the tables which provides an artificial unique value for each record as it’s created. This is called a surrogate key.
  • The database management software can generate an automatically incrementing number or unique string of characters as each record is created. This is referred to as a globally unique identifier (GUID).
  • Eliminate redundant data.
recipe_main
1
INSERT INTO `recipe_main` VALUES (3,'Air Fryer Chicken Breasts',1,'Air Fryer Chicken Breasts are easy to make, and take just minutes to cook. You can make juicy, flavorful chicken breasts in the air fryer for a quick meal, or to use for meal prep.',5,20,2,1,"Brush the chicken breast with olive oil. Sprinkle one side with salt, pepper, and garlic powder. Place the breast in the air fryer basket seasoned side down. Then season the other side. Cook at 360° for 9 minutes (for 8 oz chicken breast). Then flip the chicken breast over and cook for another 9 minutes. Larger chicken breasts need more cook time, and smaller ones need less time (see Recipe Notes). Open the air fryer immediately so it doesn't continue cooking in the heat. Take the temperature in the thickest part with an instant read thermometer. The temperature should read about 158°-160° F. If it is below that, close the lid of the air fryer to let the chicken cook in the residual heat for a few minutes. Then check it again. Place it on a plate and loosely cover with foil and let it rest for 5 minutes. It will continue to cook in the residual heat (and the juices will go back into the meat). Then after 5 minutes take the temperature again. The temperature should be at least 165° F. Serve immediately, or save for meal prep or to have for another recipe."),(4,'Instant Pot Garlic Noodles',1,'Instant Pot Garlic Noodles are super garlicky, and easy to make. You can have these flavorful noodles as a side dish or a main dish. Pressure cooker Garlic Noodles are Asian takeout made at home!',15,16,4,2, "Turn on the Sauté setting to the lowest heat. When hot, add the butter and oil. Add the garlic and stir it frequently. While the garlic is cooking, mix together the sauce ingredients. Sauté the garlic until fragrant, but not burnt. Add the sauce and stir. Add 1 cup of the water and stir. Turn off the sauté setting. Add the spaghetti noodles in a random bird nest pattern so fewer will stick together. Add the remaining cup of water over the pasta. Close the lid and set the steam release knob to the Sealing position. Press the Pressure Cook/Manual button or dial, then the +/- button or dial to select 6 minutes. High Pressure. The pot will take a few minutes to come to pressure. When the cook cycle has finished, turn off the pot and let it sit undisturbed for 3 minutes (3 minute natural release). Then turn the steam release knob to the Venting position to release the remaining steam/pressure. When the pin in the lid drops back down, open the lid. Stir the noodles. Place the lid back on, but don't seal the vent. Let the pasta sit for about 5 minutes, then open the lid and stir again. Separate any stuck together noodles. Serve immediately.");
rec_ingredients
1
INSERT INTO `rec_ingredients` VALUES (17,3,1.00,1),(18,3,2.00,3),(19,3,0.25,15),(20,3,0.25,20),(21,3,0.25,21),(22,4,2.00,23),(23,4,1.00,3),(24,4,2.00,24),(25,4,8.00,25),(26,4,1.00,26),(27,4,1.00,27),(28,4,2.00,28),(29,4,0.75,29),(30,4,0.25,15),(31,4,0.13,20);
ingredients
1
INSERT INTO `ingredients` VALUES (20,'Pepper'),(21,'Garlic Powder'),(22, 'Garlic'),(23, 'Unsalted Butter'),(24, 'Water'),(25, 'Spaghetti Noodles'),(26, 'Oyster Sauce'),(27, 'Low Sodium Soy Sauce'),(28, 'Toasted Sesame Oil'),(29, 'Powdered Ginger');

Sandy (July 12, 2019). AIR FRYER CHICKEN BREASTS. Retrieved from https://www.simplyhappyfoodie.com/air-fryer-chicken-breasts/

Sandy (April 1, 2020). INSTANT POT GARLIC NOODLES. Retrieved from https://www.simplyhappyfoodie.com/instant-pot-garlic-noodles/

Third Normal Form (3NF)

The Third Normal Form states that in addition to the requirements of the first two forms being met:

  • All fields that are not part of the key must be functionally independent of each other.
  • Eliminate data not dependent on key.

SQL Syntax

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.

In this tutorial we will use dataset from BOS:311.

Click here to download

Below is a selection from the “Customers” table:

SQL Statements

Most of the actions you need to perform on a database are done with SQL statements.

The following SQL statement selects all the records in the sample_311 table:

SQL
1
SELECT * FROM sample_311;

The following SQL statement selects all the records in the sample_311 table and show in a vertical style, and only show one row:

SQL
1
SELECT * FROM sample_311 LIMIT 1 \G

ALY6030

Data Manipulation Statements

LOAD DATA Statement

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

SQL SELECT

SELECT (Statement with) WHERE (filters and) ORDER (clause to sort data tables)

  1. The * (asterisk) character returns all values for all fields stored in a data table. The end result is just a copy of the original table.
  2. All SQL statements have to end in a ; (semicolon).
  3. Need to specify the database name first, then the table name.

SELECT STATEMENTS - All Fields (Columns), All Values (rows)

SQL SELECT All Fields (Columns), All Values (rows)
1
2
USE <DATABASE NAME>;
SELECT * FROM <TABLE NAME>;

OR

SQL SELECT All Fields (Columns), All Values (rows)
1
SELECT * FROM <DATABASE NAME>.<TABLE NAME>;

SELECT STATEMENTS - Some Fields, All Values

Most tables in the data warehouse contain hundreds of fields, so it’s useful to select only a handful that the analyst is interested in using later on.

SQL SELECT Some Fields, All Values
1
2
USE <DATABASE NAME>;
SELECT <COLUMN NAME>, <COLUMN NAME> ..., <COLUMN NAME> FROM <TABLE NAME>;

SELECT STATEMENTS - All Fields, Some Values

The WHERE clause is the main way to filter data tables.

SQL SELECT Fields, Some Values
1
2
3
USE <DATABASE NAME>;
SELECT * FROM <TABLE NAME>
WHERE <COLUMN> <OPERATOR> <CONDITION>;

https://www.w3schools.com/sql/sql_where.asp

Operator Description Example
= Equal Try it
> Greater than Try it
< Less than Try it
>= Greater than or equal Try it
<= Less than or equal Try it
<> Not equal. Note: In some versions of SQL this operator may be written as != Try it
BETWEEN Between a certain range Try it
LIKE Search for a pattern Try it
IN To specify multiple possible values for a column Try it

SELECT STATEMENTS with WHERE clause - Some Fields, Some Values

Date and characters are queried using single quotes ' ', where as numerical values such as zip do not need quotes.

BETWEEN is a quick way to get a range for your data (inclusive).

SQL SELECT Fields, Some Values
1
2
3
4
5
6
7
8
USE <DATABASE NAME>;
SELECT * FROM <TABLE NAME>
WHERE 
<COLUMN> <OPERATOR> <CONDITION>
AND
<COLUMN> <OPERATOR> <CONDITION>
AND
<COLUMN> <OPERATOR> <CONDITION>;

SELECT STATEMENTS with ORDER clause

ORDER clause will sort numerical values and dates as one would expect, but also character values are sorted alphabetically.

You cannot use the * shortcut in the ORDER clause, you have to write out all of the fields you want sorted.

SQL SELECT with ORDER clause
1
2
3
USE <DATABASE NAME>;
SELECT * FROM <TABLE NAME>
ORDER BY <COLUMN NAME>;

ORDER DESC clause for sorting data by descending order

By default the ORDER clause assumes ascending order but you can also write ASC to specify ascending order in the same way we write DESC above.

SQL SELECT with ORDER clause
1
2
3
USE <DATABASE NAME>;
SELECT * FROM <TABLE NAME>
ORDER BY <COLUMN NAME> DESC;

If you specify more than one field in the ORDER clause and one of those fields is in descending order, write the DESC to the right of whichever field you want descending, otherwise the rest of the fields are ascending.

GROUP BY clause

  • Summarize data according to selected fields
  • Can implement functions: sum(), count(), min(), max(), avg()
  • Useful for data investigations

When using a function on a variable, it is good practice to rename it to indicate the transformation, in this case we renamed the sum of sq_ft as “sum_total_paid”.

SELECT GROUP BY clause
1
2
3
SELECT <COLUMN NAME>, SUM(<COLUMN NAME>) AS <NEW COLUMN NAME>
FROM <TABLE NAME>
GROUP BY <COLUMN NAME>;

Query identifies the most recent service date per patient, then sorts on the patient with the most recent service date.

GROUP BY clause – with HAVING clause

  • HAVING clause is a way to filter on grouped data

SUM(TOTAL_PAID) for patient P00003 was only $450 and so the HAVING clause filtered this person out.

SQL GROUP BY clause – with HAVING clause
1
2
3
4
SELECT <COLUMN NAME>, SUM(<COLUMN NAME>) AS <NEW COLUMN NAME>
FROM <TABLE NAME>
GROUP BY <COLUMN NAME>
HAVING <COLUMN NAME> <OPERATOR> <CONDITION>;

HW

HW1

Sample Data

  • What is a good choice for a primary key here? In contrast, give an example of an attribute (or composite) that would not be a valid primary key.
    • fund_id could be the primary key since it is a unique attribute and designed for searching each unique record. And it is a globally unique identifier (GUID). In this example, company is not a good primary key. Although company identifies company clearly, it is not a unique identifer for each record.

Database Conversion

msa2mys

Reference

How can I convert an MDB (Access) file to MySQL (or plain SQL file)?

How to run an EXE file in PowerShell with parameters with spaces and quotes

Access To MySQL Documentation

mdbtools Documents

Prerequisite (For Windows)

visual studio 2019 x86 redistributable
MySQL Connector/ODBC 32-bit
bullzip - Access To MySQL

Optional: If you need a Windows instance:
Amazon WorkSpaces:

  • Go to your browser:
    • Login your AWS account
    • Services -> WorkSpaces -> WorkSpaces
    • Click on Launch WorkSpaces
    • Choose a Windows OS
    • After created a Windows WorkSpaces, go to your email mailbox and set up your password.
    • Download Amazon WorkSpaces Client on your local machine: https://clients.amazonworkspaces.com/

Optional: If you need a file sharing system between your local machine and Amazon WorkSpaces.
Amazon WorkDocs

  • Go to your AWS WorkSpaces Client window:
    • Open the browser, paste the above URL to your browser, and install the DRIVE software to your WorkSpaces machine.
  • Go to your local machine:
    • Open the browser, paste the above URL to your browser, and install the DRIVE software to your local machine.


For your WorkDocs Code:

  • Go to your browser:
    • Login your AWS account
    • Services -> WorkDocs -> My Sites
    • Copy the site URL
  • Go to your WorkSpaces Client window:
    • Paste the URL, delete https:// and .awsapps.com/workdocs

Install WorkDocs DRIVE to your local machine and WorkSpaces machine:

WorkDocs Sync

  1. Drag the file you want to share to your WorkDocs/My Documents folder.
  2. Wait until the Status icon from uploading to complete.

  1. Now you can see the file successfully sync in another machine.
  2. You could also see your synced files on your browser. Paste your WorkDocs URL to your browser and login.

Convertor

The program will convert your .mdb file to MySQL database and directly upload it to MySQL server.

Source Database
If no Username and Password, leave them blank

Modify movedb to the database you wished to transfer. If there is no destination database, the program will create a database with your specified name.

Select the tables you want to convert, and click on Next

Leave the Transfer Options blank if you don’t have any personal requirements.
Click on Run Now.

In progress

mdbtools

mdbtools

utils

Command Description
mdb-ver Prints the version (JET 3 or 4) of an mdb file.
mdb-schema Prints DDL for the specified table.
mdb-export Export table to CSV or SQL formats.
mdb-json Export table to JSON format.
mdb-tables A simple dump of table names to be used with shell scripts.
mdb-count A simple count of number of rows in a table, to be used in shell scripts and ETL pipelines.
mdb-header Generates a C header to be used in exporting mdb data to a C prog.
mdb-parsecsv Generates a C program given a CSV file made with mdb-export.
mdb-sql A simple SQL engine (also used by ODBC and gmdb).
mdb-queries List and print queries stored in the database.
prcat Prints the catalog table from an mdb file.
prkkd Dump of information about design view data given the offset to it.
prtable Dump of a table definition.
prdata Dump of the data given a table name.
prole Dump of ole columns given a table name and sargs.
mdb-hexdump (in src/extras) Simple hex dump utility that I've been using to look at mdb files.

Examples

mdb-ver
1
Usage: mdb-ver [-M] <file>

mdb-schema
1
2
3
4
Usage: mdb-schema [options] <file> [<backend>]
where options are:
  -T <table>     Only create schema for named table
  -N <namespace> Prefix identifiers with namespace

mdb-export
1
2
3
4
5
6
7
8
9
10
11
12
Usage: mdb-export [options] <file> <table>
where options are:
  -H                   supress header row
  -Q                   don't wrap text-like fields in quotes
  -d <delimiter>       specify a column delimiter
  -R <delimiter>       specify a row delimiter
  -I <backend>         INSERT statements (instead of CSV)
  -D <format>          set the date format (see strftime(3) for details)
  -q <char>            Use <char> to wrap text-like fields. Default is ".
  -X <char>            Use <char> to escape quoted characters within a field. Default is doubling.
  -N <namespace>       Prefix identifiers with namespace
  -b strip|raw|octal   Binary export mode.