SQL Environment
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
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.
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 XAMPP on Windows 10
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:
1 | /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)" |
Install MySQL server through Homebrew:
1 | brew install mysql |
Set up and start MySQL service:
1 | brew tap homebrew/services |
Set your MySQL root user
and password
:
1 | mysqladmin -u root password 'your_password' |
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:
1 | mysql -uroot -p |
It works!
Exit MySQL interface:
1 | exit |
MySQL Security Setting
To check your MySQL version:
1 | mysql --version |
Create option file
under /etc
(May need administrator’s password):
1 | sudo touch /etc/my.cnf |
Show the option file
permission then change it to -rw-rw-rw-
:
1 | ls -l /etc/my.cnf |
Open your option file
:
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/
1 | # MySQL global config file |
Lower the option file
permission to -rw-rw-r--
:
1 | sudo chmod 664 /etc/my.cnf |
Restart MySQL Server
MySQL installation from Home Brew
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:
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 here to download tmpr2k_j017.csv
- A database named
bosbot
. - A table named
sample_311
.
MAMP Management
Login MySQL server
Login mysql local server. Default root password is root
.
- MySQL default port is
3306
but MAMP uses port8889
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.
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…
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.
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."); |
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); |
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.
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:
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:
1 | SELECT * FROM sample_311 LIMIT 1 \G |
ALY6030
Data Manipulation Statements
LOAD DATA Statement
1 | LOAD DATA |
SQL SELECT
SELECT (Statement with) WHERE (filters and) ORDER (clause to sort data tables)
- 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. - All SQL statements have to end in a
;
(semicolon). - Need to specify the database name first, then the table name.
SELECT STATEMENTS - All Fields (Columns), All Values (rows)
1 | USE <DATABASE NAME>; |
OR
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.
1 | USE <DATABASE NAME>; |
SELECT STATEMENTS - All Fields, Some Values
The WHERE
clause is the main way to filter data tables.
1 | USE <DATABASE NAME>; |
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).
1 | USE <DATABASE NAME>; |
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.
1 | USE <DATABASE 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.
1 | USE <DATABASE NAME>; |
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”.
1 | SELECT <COLUMN NAME>, SUM(<COLUMN NAME>) AS <NEW 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.
1 | SELECT <COLUMN NAME>, SUM(<COLUMN NAME>) AS <NEW COLUMN NAME> |
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. Althoughcompany
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
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
- Paste the URL, delete
Install WorkDocs DRIVE to your local machine and WorkSpaces machine:
WorkDocs Sync
- Drag the file you want to share to your
WorkDocs/My Documents
folder. - Wait until the Status icon from uploading to complete.
- Now you can see the file successfully sync in another machine.
- 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
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
1 | Usage: mdb-ver [-M] <file> |
1 | Usage: mdb-schema [options] <file> [<backend>] |
1 | Usage: mdb-export [options] <file> <table> |