CS-422 Dr. Mark L. Hornick

1
Introduction to SQL

• Database creation querying

2
SQL

• A standard query language that interacts with the
  database server to manage databases, tables, and
  data.

3
MySQL Command Interpreter

• Used to create and modify databases and tables
• Used to test queries
• Many options
• mysql command line client
• Linux, via remote console
• Windows
• phpMyAdmin web interface
• https//mydb.msoe.edu

4
MySQL Command Interpreterbasic rules

• SQL statements end in a semicolon
• White space generally ignored
• SQL statements are case insensitive
• Database, file names and data values ARE case
  sensitive

5
SQL for creating a Table

• CREATE TABLE Customer
• (
• address VARCHAR(50) NOT NULL,
• city VARCHAR(20) NOT NULL,
• customerID INTEGER NOT NULL AUTO_INCREMENT,
• name VARCHAR(50) NOT NULL,
• state VARCHAR(2) NOT NULL,
• zip INTEGER NOT NULL,
• PRIMARY KEY (customerID),
• UNIQUE (customerID)
• )

6
The Four Major SQL commands used to manipulate
existing database tables

• INSERT adds records
• DELETE removes records
• UPDATE modifies existing records
• SELECT retrieves information

7
Inserting rows

• INSERT INTO Inventory
• (audioPkg, coldWeatherPkg, color, gpsPkg, model,
  transmission, wheelPkg )
• VALUES
• (true, false, Pepper White, false, MINI
  Cooper, 4-speed, false)
• Or
• INSERT INTO Inventory
• VALUES
• (true, false, Pepper White, false, MINI
  Cooper, 4-speed, false)
• Or
• INSERT INTO Inventory SET audioPkgtrue,
• coldWeatherPkgfalse,
• color"Pepper White",
• gpsPkgfalse,
• model"MINI Cooper",
• transmission"4-speed",

8
Some Data Types

• BOOL, BOOLEAN - A value of zero is considered
  false. Non-zero values are considered true. In
  the future, full boolean type handling will be
  introduced in accordance with standard SQL.
• SMALLINT(M) UNSIGNED ZEROFILL - A small
  integer. The signed range is -32768 to 32767. The
  unsigned range is 0 to 65535.
• INT(M) UNSIGNED ZEROFILL - A normal-size
  integer. The signed range is -2147483648 to
  2147483647. The unsigned range is 0 to
  4294967295.
• BIGINT(M) UNSIGNED ZEROFILL - A large
  integer. The signed range is -9223372036854775808
  to 9223372036854775807. The unsigned range is 0
  to 18446744073709551615.
• DECIMAL(M,D) UNSIGNED ZEROFILL - A
  packed exact fixed-point number. M is the total
  number of decimal digits (the precision) and D is
  the number of digits after the decimal point (the
  scale). The decimal point and (for negative
  numbers) the - sign are not counted in M. If D
  is 0, values have no decimal point or fractional
  part. The maximum number of digits (M) for
  DECIMAL is 65. The maximum number of supported
  decimals (D) is 30. If D is omitted, the default
  is 0. If M is omitted, the default is 10.
• UNSIGNED, if specified, disallows negative
  values.
• All basic calculations (, -, , /) with DECIMAL
  columns are done with a precision of 65 digits.

http//dev.mysql.com/doc/
9
Some Data Types

• DATE - A date. The supported range is
  '1000-01-01' to '9999-12-31'. MySQL displays DATE
  values in 'YYYY-MM-DD' format, but allows you to
  assign values to DATE columns using either
  strings or numbers.
• DATETIME - A date and time combination. The
  supported range is '1000-01-01 000000' to
  '9999-12-31 235959'. MySQL displays DATETIME
  values in 'YYYY-MM-DD HHMMSS' format, but
  allows you to assign values to DATETIME columns
  using either strings or numbers.
• TIME - A time. The range is '-8385959' to
  '8385959'. MySQL displays TIME values in
  'HHMMSS' format, but allows you to assign
  values to TIME columns using either strings or
  numbers.
• YEAR(24) - A year in two-digit or four-digit
  format. The default is four-digit format. In
  four-digit format, the allowable values are 1901
  to 2155, and 0000. In two-digit format, the
  allowable values are 70 to 69, representing years
  from 1970 to 2069. MySQL displays YEAR values in
  YYYY format, but allows you to assign values to
  YEAR columns using either strings or numbers.

http//dev.mysql.com/doc/
10
Some Data Types

• NATIONAL CHAR(M) BINARY ASCII UNICODE -
  A fixed-length string that is always right-padded
  with spaces to the specified length when stored.
  M represents the column length. The range of M is
  0 to 255 characters.
• Note Trailing spaces are removed when CHAR
  values are retrieved.
• NATIONAL VARCHAR(M) BINARY - A
  variable-length string. M represents the maximum
  column length. The range of M is 0 to 65,535.
  (The actual maximum length of a VARCHAR is
  determined by the maximum row size and the
  character set you use. The maximum effective
  length is 65,532 bytes.)
• Note MySQL 5.1 follows the standard SQL
  specification, and does not remove trailing
  spaces from VARCHAR values.
• VARCHAR is shorthand for CHARACTER VARYING.
• VARCHAR is stored with a one-byte or two-byte
  length prefix plus data. The length prefix is two
  bytes if the VARCHAR column is declared with a
  length greater than 255.
• BLOB(M) - A Binary Large Object with a maximum
  length of 65,535 (216 1) bytes.
• An optional length M can be given for this type.
  If this is done, MySQL creates the column as the
  smallest BLOB type large enough to hold values M
  bytes long.

http//dev.mysql.com/doc/
11
Modifiers

• name NOT NULL
• address varchar(20) NOT NULL DEFAULT Unknown
• ---- mySQL proprietary ----
• CREATE TABLE Customer (
• customerID integer NOT NULL auto_increment,
• )

12
Deleting Data

• Delete all data.
• DELETE FROM Customer
• Delete a Customer.
• DELETE FROM Customer WHERE customerID 1
• DELETE FROM Customer WHERE name Bill Bored

13
Updating Data

• UPDATE Customer SET name upper(name)
• UPDATE Customer SET name John Smith WHERE
  customerID 7

14
Querying a table

• List all data in the Customer table.
• SELECT FROM Customer

15
Project

• Choosing columns
• A vertical slice
• SELECT name, customerID FROM Customer

16
Restrict

• Choosing rows
• A horizontal slice
• SELECT FROM Customer WHERE customerID lt 3

17
Project and Restrict combo

• Choosing rows and columns

SELECT name, address FROM Customer WHERE
CustomerID lt 1
18
Primary key retrieval

• A query using the primary key returns at most one
  row
• SELECT FROM Inventory WHERE stockNum 10

19
Primary key retrieval

• A query not using the primary key can return more
  than one row
• SELECT FROM Inventory WHERE model MINI
  Cooper

20
Ordering output

• Ordering columns
• Columns are reported in the order specified in
  the SQL command
• Ordering rows
• Rows are ordered using the ORDER BY clause

21
Ordering columns

• SELECT name, address FROM Customer
• SELECT address, name FROM Customer

22
Ordering rows

• SELECT FROM Customer WHERE customerID gt 4
• ORDER BY name
• SELECT FROM Inventory WHERE stockNum gt 4
• ORDER BY color DESC

23
LIKE - Pattern matching

• SELECT FROM Inventory WHERE color LIKE m
• LIKE '__t'
• NOT LIKE 's'

24
DISTINCT

• Eliminating duplicate rows
• SELECT DISTINCT color FROM Inventory