Structured Query Language - PowerPoint PPT Presentation

About This Presentation
Title:

Structured Query Language

Description:

Structured Query Language Lecture 27 STAT 598 W First value in the list (as sorted) is provided As of November 20, 2012: Latest version of the DB is MySQL Community ... – PowerPoint PPT presentation

Number of Views:294
Avg rating:3.0/5.0
Slides: 60
Provided by: Janu48
Category:

less

Transcript and Presenter's Notes

Title: Structured Query Language


1
Structured Query Language
Lecture 27
  • STAT 598 W

2
Outline
  • Introduction to SQL MySQL
  • Single table Queries
  • Using computed columns
  • Using special operators LIKE, IN, BETWEEN
  • Columns with NULL values
  • Sorting data
  • Using group functions
  • The GROUP BY clause

3
Structured Query Language (SQL)
  • Mid-1970s
  • SQL was developed at IBM under the name SEQUEL
  • 1980
  • Renamed as SQL to avoid confusion with an
    unrelated hardware product called SEQUEL
  • Most relational DBMSes use some version of SQL

4
SQL (cont.)
  • Is an English-like language
  • Communicates with an SQL Server
  • Manipulates data and table definitions in the
    database
  • Supports operations of Relational Algebra

5
SQL Statements
  • Data Retrieval
  • SELECT
  • Data Definition Language (DDL)
  • CREATE, ALTER, DROP, RENAME, TRUNCATE
  • Data Manipulation Language (DML)
  • INSERT, UPDATE, DELETE
  • Data Control Language (DCL)
  • GRANT, REVOKE
  • Transaction control
  • COMMIT, ROLLBACK, SAVEPOINT

6
SQL Statements (cont.)
  • SQL statements are free format
  • SQL statements can be placed on one or more
    lines
  • Statements are entered in SQL Buffer
  • Keywords cannot be abbreviated or split across
    lines
  • Clauses are usually placed one per line
  • Indentations are used to improve readability
  • Functions are utilized to perform data
    manipulation as well as formatting output of a
    query
  • The end of a statement is indicated by a semicolon

7
MySQL Database Universe
GUI Tools
MySQL Administrator
MySQL Workbench
mysqldmin
mysql
mysqldump
Command Line Tools
MySQL Clients and Tools
mysqld MySQL Server
MySQL APIs
Connector/ODBC
Connector/J
Connector/Net
Connector/PHP
Connector/C
Connector/C
8
MySQL Command Line Tools
SELECT UPDATE . . . BACKUP RESTORE CHECK . . .
mysql
mysqld Server Process
DB
mysqladmin
SHUTDOWN
mysqldump
BACKUP
9
Essential mysql Commands
  • mysqlgt SHOW databases to show available
    databases
  • mysqlgt CREATE DATABASE premiere to create new
    database
  • mysqlgt USE premiere to start using Premiere
    database
  • mysqlgt SHOW tables to show available tables in
    default db
  • mysqlgt SOURCE c\premiere.txt to run a script
    file
  • mysqlgt DESCRIBE customer to show structure of
    customer table
  • mysqlgt EXIT to exit the mysql client

10
Help in MySQL
  • Type help at mysqlgt prompt, or
  • Type help followed by name of a statement
  • e.g.
  • help select
  • help union
  • Also available
  • Reference Manual on-line or pdf version

11
  • mysqlgt help
  • For information about MySQL products and
    services, visit
  • http//www.mysql.com/
  • For developer information, including the MySQL
    Reference Manual, visit
  • http//dev.mysql.com/
  • To buy MySQL Enterprise support, training, or
    other products, visit
  • https//shop.mysql.com/
  • List of all MySQL commands
  • Note that all text commands must be first on line
    and end with ''
  • ? (\?) Synonym for 'help'.
  • clear (\c) Clear the current input statement.
  • connect (\r) Reconnect to the server. Optional
    arguments are db and host.
  • delimiter (\d) Set statement delimiter.
  • ego (\G) Send command to mysql server,
    display result vertically.
  • exit (\q) Exit mysql. Same as quit.
  • go (\g) Send command to mysql server.
  • help (\h) Display this help.

12
SQL Editor
  • SQL server is built in most computers, but in
    some cases only administrator has full access to
    it.
  • In order to practice your SQL editing skills, you
    may download some SQL Editor online for free.
  • To make it even simpler, you can directly use
    some kind of SQL online-editor, such as SQL
    Fiddle.

13
Create a simple table
  • CREATE TABLE Contacts
  • (
  • id int auto_increment primary key,
  • type varchar(20),
  • details varchar(80)
  • )
  • INSERT INTO Contacts
  • (type, details)
  • VALUES
  • ('Email', 'wang913_at_purdue.edu'),
  • ('Website', 'www.stat.purdue.edu/wang913'),
  • ('Address', 'Purdue University'),
  • ('Phone', '765-714-4263')
  • -----------------------------------------------
    -------------
  • ID TYPE DETAILS
  • -----------------------------------------------
    -------------
  • 1 Email wang913_at_purdue.edu
  • 2 Website www.stat.purdue.edu
    /wang913
  • 3 Address Purdue University

14
Insert from a data file
Use BULK INSERT
  • BULK INSERT MyTable
  • FROM 'c\data.csv'
  • WITH
  • (
  • FIELDTERMINATOR ',',
  • ROWTERMINATOR '\n'
  • )

15
Premiere Products Model
places
represents
REP
CUSTOMER
(1, 1)
(1, 1)
(1, N)
(1, N)
has
included in
ORDER
PART
ORDER_LINE
(1, N)
(1, 1)
(0, N)
(1, 1)
  • REP (Rep_num, Last_Name, First_Name, Street,
    City, State, Zip, Commission, Rate)
  • CUSTOMER (Customer_num, Customer_Name, Street,
    City, State, Zip, Balance, Credit_limit,
    Rep_num)
  • ORDER (Order_num, Order_date, Customer_num)
  • ORDER_LINE (Order_num, Part_num, Num_ordered,
    Quoted_price)
  • PART (Part_num, Description, Warehouse, Class,
    Price, On_hand)

Source A Guide to MySQL by Philip J. Pratt
and Mary Z. Last , Course Technology, 2006
16
Existing Tables in a Default DB
  • To find out what tables exist in the default
    database, use the SHOW command

mysqlgt show tables --------------------
Tables_in_premiere --------------------
customer order_line
orders part rep
-------------------- 5 rows in
set (0.00 sec)
17
Displaying a Table Structure
  • The DESCRIBE command

mysqlgt desc customer --------------------------
------------------------------ Field
Type Null Key Default Extra
-------------------------------------------
------------- CUSTOMER_NUM char(3)
NO PRI NULL CUSTOMER_NAME
char(35) NO NULL
STREET char(15) YES NULL
CITY char(15) YES
NULL STATE
char(2) YES NULL
ZIP char(5) YES NULL
BALANCE decimal(8,2) YES
NULL CREDIT_LIMIT
decimal(8,2) YES NULL
REP_NUM char(2) YES NULL
-----------------------------------
--------------------- 9 rows in set (0.01 sec)
18
SELECT Statement
  • SELECT column(s)
  • FROM table(s)
  • WHERE row condition
  • GROUP BY column(s)
  • HAVING group condition
  • ORDER BY column(s)
  • LIMIT m, n

19
WHERE Clause
  • Find the number, name, balance, and credit limit
    for each customer with balance that exceeds the
    credit limit.

mysqlgt SELECT customer_num,customer_name,
balance, credit_limit -gt FROM customer
-gt WHERE balance gt credit_limit --------------
--------------------------------------------
customer_num customer_name balance
credit_limit ---------------------------------
------------------------- 408 The
Everything Shop 5285.25 5000.00 842
All Season 8221.00
7500.00 -------------------------------------
--------------------- 2 rows in set (0.02 sec)
20
Compound Condition
  • List the description of every part that is not in
    warehouse number 3 and that has more than 20
    units on hand.

mysqlgt SELECT description -gt FROM part
-gt WHERE warehouse ltgt '3' -gt AND on_hand
gt 20 ---------------- description
---------------- Home Gym
Microwave Oven ---------------- 2 rows in set
(0.05 sec)
21
Expressions
  • Find the number, name, and available credit for
    each customer with at least 5,000 of available
    credit.

mysqlgt SELECT customer_num, customer_name,
-gt (credit_limit - balance) as "Available
Credit" -gt FROM customer -gt WHERE
(credit_limit - balance) gt 5000 --------------
----------------------------------------------
customer_num customer_name
Available Credit -----------------------------
------------------------------- 282
Brookings Direct 9568.50
462 Bargains Galore
6588.00 608 Johnson's
Department Store 7894.00 725
Deerfield's Four Seasons
7252.00 --------------------------------------
---------------------- 4 rows in set (0.00 sec)
22
BETWEEN operator
  • BETWEEN operator makes certain SELECT statements
    simpler
  • List customer number, name, and balance for
    customers with their balance between 2,000 and
    5,000.

mysqlgt SELECT customer_num, customer_name,
balance -gt FROM customer -gt WHERE
balance BETWEEN 2000 AND 5000 -----------------
----------------------------------
customer_num customer_name
balance --------------------------------------
------------- 462 Bargains Galore
3412.00 608 Johnson's
Department Store 2106.00 687
Lee's Sport and Appliance 2851.00
---------------------------------------------
------ 3 rows in set (0.00 sec)
23
LIKE operator
  • LIKE operator is used when exact character type
    matches are not applicable
  • LIKE is used with wildcard searches
  • (percent) matches any string of zero or more
    characters
  • _ (underscore) matches any individual character
  • The ESCAPE option can be used to define escape
    character symbol

24
LIKE operator (cont.)
  • List customer number, name, and complete address
    of each customer with a street name that contains
    Central.

mysqlgt SELECT customer_num, customer_name,
street, city, state, zip -gt FROM customer
-gt WHERE street LIKE 'Central' ------------
----------------------------------------------
-------- customer_num customer_name
street city state zip
---------------------------------------------
--------------------- 462
Bargains Galore 3829 Central Grove FL
33321 ---------------------------------------
--------------------------- 1 row in set
(0.00 sec)
25
LIKE operator (cont.)
  • You have a difficulty reading a report because
    someone spilled coffee on it. You can only tell
    the first digit (4) of the customer and the
    last digit (8). The second digit is hard to
    read. Can you find the customer name and
    complete address?

mysqlgt SELECT customer_num, customer_name,
street, city, state, zip -gt FROM customer
-gt WHERE customer_num LIKE '4_8' ------------
----------------------------------------------
------------ customer_num customer_name
street city state zip
---------------------------------------------
------------------------- 408
The Everything Shop 1828 Raven Crystal FL
33503 ------------------------------------
---------------------------------- 1 row in
set (0.03 sec)
26
IN operator
  • The IN operator provides a concise way to test
    for values in a specified set.
  • List the customer number, name, and credit limit
    for each customer with a credit limit of 5,000,
    10,000, or 15,000.

mysqlgt SELECT customer_num, customer_name,
credit_limit -gt FROM customer -gt WHERE
credit_limit IN (5000, 10000,
15000) ----------------------------------------
---------------- customer_num customer_name
credit_limit ------------------
-------------------------------------- 282
Brookings Direct 10000.00
408 The Everything Shop
5000.00 462 Bargains Galore
10000.00 524 Kline's
15000.00 608
Johnson's Department Store 10000.00
687 Lee's Sport and Appliance
5000.00 --------------------------------------
------------------ 6 rows in set (0.00 sec)
27
Null Values
  • Occasionally, when you enter a new row into a
    table or modify an existing row, the values for
    one or more columns are unknown or unavailable
  • e.g., A sales representative is not assigned to a
    customer
  • This special value is called a null data value,
    or null. The null is not the same as zero or
    blank space.

28
Three Valued Logic
  • Any comparison with null returns unknown value
    e.g.
  • 15 gt null,
  • null null,
  • column lt null,
  • column null
  • Result of WHERE clause predicate is treated as
    false if it evaluates to unknown

29
Three Valued Logic (cont.)
NOT (not unknown) evaluates to unknown
30
Selecting rows with NULL values
  • Do we have a complete address for each customer?
  • List the number and name of each customer with
    an unknown/missing street information.

mysqlgt SELECT customer_num, customer_name -gt
FROM customer -gt WHERE street IS
NULL Empty set (0.00 sec)
31
Rules of Precedence
  • The rules determine the order in which
    expressions are evaluated
  • The default order
  • Parenthesis
  • Arithmetic operators
  • Comparison conditions, IS, LIKE, IN
  • BETWEEN, CASE
  • NOT logical condition
  • AND logical condition
  • OR logical condition
  • This order can be modified by using parentheses

32
Sorting
  • Typically rows are displayed in the order in
    which they were inserted
  • The ORDER BY clause can be used to list data in a
    desired order
  • The column(s) on which data is to be sorted is
    called a sort key(s)
  • The sort keys are listed in the order of
    importance
  • To sort in descending order use the DESC operator
    (default is ASC)

33
Sorting
  • List the customer number, name, and balance of
    each customer. Order the output in ascending
    (increasing) order of balance.

mysqlgt SELECT customer_num, customer_name,
balance -gt FROM customer -gt ORDER BY
balance -gt LIMIT 5 ------------------------
---------------------------- customer_num
customer_name balance
---------------------------------------------
------- 725 Deerfield's Four
Seasons 248.00 282 Brookings
Direct 431.50 608
Johnson's Department Store 2106.00 687
Lee's Sport and Appliance 2851.00
462 Bargains Galore
3412.00 --------------------------------------
-------------- 5 rows in set (0.00 sec)
34
Sorting with multiple keys
  • List the customer number, name, and credit limit
    of every customer, ordered by credit limit in
    descending order and by name within credit limit.

mysqlgt SELECT customer_num, customer_name cname,
credit_limit -gt FROM customer -gt ORDER
BY credit_limit DESC, cname -gt LIMIT
5 --------------------------------------------
------------ customer_num cname
credit_limit -----------------------
--------------------------------- 524
Kline's 15000.00
462 Bargains Galore
10000.00 282 Brookings Direct
10000.00 608 Johnson's
Department Store 10000.00 148
Al's Appliance and Sport 7500.00
---------------------------------------------
----------- 5 rows in set (0.00 sec)
35
Group Functions
  • SUM Sum of values in a column
  • AVG Average value in a column
  • COUNT Number of values in a column
  • MAX Maximum value in a column
  • MIN Minimum value in a column
  • STDDEV Standard Deviation of values in a
    column
  • VARIANCE Variance of values in a column

36
Group Functions (cont.)
  • They operate on a set of values as input and give
    one value as a result
  • COUNT, MAX and MIN functions can be used with any
    data type
  • SUM, AVG, STDDEV, and VARIANCE can be used only
    with numeric data types
  • All group functions ignore null values except
    COUNT()

37
Counting rows in a table
  • How many parts are in item class HW?

mysqlgt SELECT COUNT() -gt FROM part -gt
WHERE class 'HW' ---------- COUNT()
---------- 3 ---------- 1 row in
set (0.00 sec)
38
SUM function
  • Find the total number of customers and the total
    of their balances.

mysqlgt SELECT COUNT() "Number of Customers",
-gt SUM(balance) "Total Balance" -gt
FROM customer -------------------------------
----- Number of Customers Total Balance
------------------------------------
10 47651.75 -----------------
------------------- 1 row in set (0.00 sec)
39
Summary statistics
  • Provide summary statistics of customer balance.

mysqlgt SELECT COUNT(balance) N, AVG(balance)
Xbar, -gt MIN(balance) Min,
MAX(balance) Max, -gt STD(balance) S
-gt FROM customer -------------------------
----------------------- N Xbar
Min Max S
-------------------------------------------
----- 10 4765.175000 248.00 12762.00
3635.106972 --------------------------------
---------------- 1 row in set (0.00 sec)
40
MIN function with character type
  • Alphabetically, what is the first and the last
    part description in the PART Table.

mysqlgt SELECT MIN(description) First, -gt
MAX(description) Last -gt FROM
part ------------------------ First
Last ------------------------
Cordless Drill Washer ---------------------
--- 1 row in set (0.00 sec)
41
DISTINCT operator
  • To avoid duplicates, either when listing or
    counting values, precede the column name with the
    DISTINCT operator
  • DISTINCT operator is not a function
  • Useful when used within COUNT function

42
Results with repeated rows
  • Find the customer number of each customer that
    currently has an open order (i.e., an order in
    the ORDERS table).

mysqlgt SELECT customer_num -gt FROM
orders -------------- customer_num
-------------- 148 356
408 282 608
148 608
-------------- 7 rows in set (0.03 sec)
43
Results without repeated rows
  • Find the customer number of each customer that
    currently has an open order. List each customer
    only once.

mysqlgt SELECT DISTINCT customer_num -gt FROM
orders -------------- customer_num
-------------- 148 356
408 282 608
-------------- 5 rows in set (0.00 sec)
44
DISTINCT used with COUNT
  • Count the number of customers who currently have
    open orders.

mysqlgt SELECT COUNT(customer_num) -gt FROM
orders ---------------------
COUNT(customer_num) ---------------------
7 --------------------- 1 row
in set (0.00 sec) mysqlgt SELECT COUNT(DISTINCT
customer_num) -gt FROM orders -------------
----------------- COUNT(DISTINCT customer_num)
------------------------------
5 ------------------------------
1 row in set (0.00 sec)
45
Describing Groups of Data
  • SELECT column(s), ...
  • group_function(column)
  • FROM table(s)
  • WHERE row condition
  • GROUP BY column(s)
  • HAVING group condition
  • ORDER BY column(s)
  • LIMIT m, n

46
Using the GROUP BY clause
  • GROUP BY clause allows rows that share some
    common characteristics to be grouped
  • Multiple columns and expressions can be used for
    grouping
  • Specified group functions are performed on each
    group
  • Columns in the GROUP BY clause do not have to be
    in the SELECT list

47
Grouping Data
  • List class ID and the average unit price of
    products in each class.

mysqlgt SELECT class, AVG(price) -gt FROM
part -gt GROUP BY class --------------------
class AVG(price) --------------------
AP 400.988000 HW 104.950000
SG 1092.475000 -------------------- 3
rows in set (0.00 sec)
48
How does it work?
Original PART table
Part_Num Description On_hand Class Warehouse Price
AT94 Iron 50 HW 3 24.95
BV06 Home Gym 45 SG 2 794.95
CD52 Microwave Oven 32 AP 1 165.00
DL71 Cordless Drill 21 HW 3 129.95
DR93 Gas Range 8 AP 2 495.00
DW11 Washer 12 AP 3 399.99
FD21 Stand Mixer 22 HW 3 159.95
KL62 Dryer 12 AP 1 349.95
KT03 Dishwasher 8 AP 3 595.00
KV29 Treadmill 9 SG 2 1390.00
49
How does it work?
PART table sorted by class
Part_Num Description On_hand Class Warehouse Price
CD52 Microwave Oven 32 AP 1 165.00
DR93 Gas Range 8 AP 2 495.00
DW11 Washer 12 AP 3 399.99
KL62 Dryer 12 AP 1 349.95
KT03 Dishwasher 8 AP 3 595.00
AT94 Iron 50 HW 3 24.95
DL71 Cordless Drill 21 HW 3 129.95
FD21 Stand Mixer 22 HW 3 159.95
BV06 Home Gym 45 SG 2 794.95
KV29 Treadmill 9 SG 2 1390.00
AVR 400.99
AVR 104.95
AVR 1092.48
We have 5 rows in AP class, 3 rows in HW class, 2
rows in SG class
50
Do use group functions with GROUP BY
  • List class and average unit price in each class.

mysqlgt SELECT class, price -gt FROM part
-gt GROUP BY class --------------- class
price --------------- AP 165.00
HW 24.95 SG 794.95
--------------- 3 rows in set (0.00 sec)
51
Grouping with GROUP_CONCAT()
  • The GROUP_CONCAT() function returns a string
    result with the concatenated values from a group

mysqlgt SELECT class, -gt
GROUP_CONCAT(DISTINCT description) List -gt
FROM part -gt GROUP BY class -------------
--------------------------------------------
class List
---------------------------------------
------------------ AP Washer,Dishwasher,Mi
crowave Oven,Dryer,Gas Range HW Stand
Mixer,Iron,Cordless Drill SG
Home Gym,Treadmill
------------------------------------------
--------------- 3 rows in set (0.00 sec)
52
Grouping with GROUP_CONCAT()
mysqlgt SELECT class, -gt
GROUP_CONCAT(DISTINCT description) List -gt
FROM part -gt GROUP BY class
\G 1. row
class AP List
Washer,Dishwasher,Microwave Oven,Dryer,Gas
Range 2. row
class HW List
Stand Mixer,Iron,Cordless Drill
3. row class
SG List Home Gym,Treadmill 3 rows in set
(0.00 sec)
53
Using WITH ROLLUP clause
  • For each warehouse and class, provide the average
    price of part. Also provide the average price in
    each warehouse.

mysqlgt SELECT warehouse, class, AVG(price) -gt
FROM part -gt GROUP BY warehouse, class
-gt WITH ROLLUP -------------------------------
warehouse class AVG(price)
------------------------------- 1
AP 257.475000 1 NULL
257.475000 2 AP 495.000000
2 SG 1092.475000 2
NULL 893.316667 3 AP
497.495000 3 HW 104.950000
3 NULL 261.968000 NULL
NULL 450.474000 -----------------------
-------- 9 rows in set (0.00 sec)
54
Counting the rows in a group
  • List each credit limit and the number of
    customers having each credit limit.

mysqlgt SELECT credit_limit, COUNT() -gt FROM
customer -gt GROUP BY credit_limit ---------
--------------- credit_limit COUNT()
------------------------ 5000.00
2 7500.00 4
10000.00 3 15000.00 1
------------------------ 4 rows in set (0.00
sec)
55
Using a HAVING clause
  • List the order number and the total value for
    orders over 1,000.

mysqlgt SELECT order_num, -gt
SUM(num_orderedquoted_price) total -gt FROM
order_line -gt GROUP BY order_num -gt
HAVING SUM(num_orderedquoted_price) gt
1000 -------------------- order_num total
-------------------- 21613 1319.80
21614 1190.00 21617 2189.90
21623 2580.00 --------------------
4 rows in set (0.00 sec)
56
Displaying specific groups
  • List each credit limit and the number of
    customers having each credit limit held by more
    than one customer.

mysqlgt SELECT credit_limit, COUNT() -gt FROM
customer -gt GROUP BY credit_limit -gt
HAVING COUNT() gt 1 ------------------------
credit_limit COUNT() ---------------------
--- 5000.00 2 7500.00
4 10000.00 3
------------------------ 3 rows in set (0.00
sec)
57
HAVING vs. WHERE
  • WHERE clause limits/restricts individual rows
  • HAVING clause limits/restricts output to certain
    groups on the basis of aggregate information

58
Restricting the rows to be grouped
  • List each credit limit and the number of
    customers of sales rep 20 that have this credit
    limit.

mysqlgt SELECT credit_limit, COUNT() -gt FROM
customer -gt WHERE rep_num '20' -gt
GROUP BY credit_limit ------------------------
credit_limit COUNT() -------------------
----- 7500.00 2 15000.00
1 ------------------------ 2 rows
in set (0.00 sec)
59
Restricting the rows and the groups
  • Repeat previous example, but list only those
    credit limits held by more than one customer.

mysqlgt SELECT CREDIT_LIMIT, COUNT() -gt FROM
CUSTOMER -gt WHERE REP_NUM '20' -gt
GROUP BY CREDIT_LIMIT -gt HAVING COUNT() gt
1 ------------------------ CREDIT_LIMIT
COUNT() ------------------------
7500.00 2 ------------------------ 1
row in set (0.00 sec)
Write a Comment
User Comments (0)
About PowerShow.com