	CS 580 Client-Server Programming Fall Semester, 2000 Some MySQL
	Previous Lecture Notes Index Next © 2000, All Rights Reserved, SDSU & Roger Whitney San Diego State University -- This page last updated 06-Nov-00

Contents of Doc 19, Some MySQL

References
MySQL, Paul DuBois, New Riders Publishing, 2000.

This is a very good book. A number of examples and tables in this lecture are from this text.

On-line MySQL Manual at: http://www.mysql.com/documentation/index.html


Doc 19, Some MySQL Slide # 2

SQL

History

Structured query language (SQL)

Dr. E. F. Codd develops relational database model

Early 1970's

IBM System R relational database

Mid 1970's
Contained the original SQL language

First commercial database - Oracle 1979

SQL was aimed at:

Accountants
Business people

SQL89

Not well followed
ANSI X3.135-1989

SQL92

First commonly followed standard
ANSI X3.135-1992
SQL2

ISO/IEC 9075-1 through 5

New SQL standard


Doc 19, Some MySQL Slide # 3

MySQL

Database & tables

Database consists of a number of tables

Table is a collection of records

Each Column of data has a type

   +----------------------+----------------------+------------+----------+
   | firstname            | lastname             | phone      | code     |
   +----------------------+----------------------+------------+----------+
   | John                 | Smith                | 555-9876   | 2000     |
   | Ben                  | Oker                 | 555-1212   | 9500     |
   | Mary                 | Jones                | 555-3412   | 9900     |
   +----------------------+----------------------+------------+----------+


Doc 19, Some MySQL Slide # 4

MySQL Names

Databases, tables columns & indexes have names

Legal Characters
Alphanumeric characters
'_'
'$'

Names can start with digits

Name length
Up to 64 characters tables, databases, columns & indexes

Name qualifiers
A table is in a database

Full name of a table is databaseName.tableName

A column is in a table

Full name of a table is databaseName.tableName.columnName

Often the full name is not needed


Doc 19, Some MySQL Slide # 5

Example of Nonqualified Names
# Set a default database

USE acm;

/* now select some columns */

SELECT last_name , first_name FROM members;

acm is a database
members is a table in the acm database
last_name & first_name are columns in members

Case Sensitivity
SQL keywords and function names

Not case sensitive

Database & table names

Are implemented using directories and files
Case sensitivity depend on OS

Column and index names

Not case sensitive
Doc 19, Some MySQL Slide # 6

MySQL Data Types

Numeric Values

Integer - decimal or hex
Floating-point - scientific & 12.1234

String Values

Use single or double quotes
"this is a string"
'So is this'

Sequence	Meaning
\0	NUL (ASCII 0)
\'	Single quote
\"	Double quote
\b	Backspace
\n	Newline
\r	Tab
\\	Backslash


Including a quote character in a string
Double quote the character 'Don''t do it' "He said, ""Go home"" "

Use the other quote character "Don't do it" 'He said, "Go home" '
Escape the quote character with a backslash

Date and Time
NULL


Doc 19, Some MySQL Slide # 7

MySQL Columns Types

Numeric

Type	Range
TINYINT[(M)]	Signed Values: -128 to 127 Unsigned Values: 0 to 225
SMALLINT[(M)]	Signed Values: -32,768 to 32,767 Unsigned Values: 0 to 65,535
MEDUIMINT[(M)]	Signed Values: -8,388,608 to 8,388,607 Unsigned Values: 0 to 16,777,215
INT[(M)]	Signed Values: -2,147,683,648 to 2,147,683,647 Unsigned Values: 0 to 4,294,967,259
BIGINT[(M)]	Signed Values: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 Unsigned Values: 0 to 2 ³²-1
FLOAT[(M,D)], FLOAT(4)	MIN VALUES: ±1.175494351E-38 MAX VALUES: ±3.402823466+38
DOUBLE[(M,D)], FLOAT(8)	MIN VALUES: ±2.22507E-308 MAX VALUES: ±1.79769+308
DECIMAL(M,D)	Depends on M & D

Ints & Floats

M = number of digits to the left of the decimal displayed
D = number of decimal places displayed
M & D do not affect how the number is stored

DECIMAL

Stored as a string
M & D determine how many characters are stored


Doc 19, Some MySQL Slide # 8

String Column Types

Type	Max Size
CHAR(M)	M (<=225) bytes
VARCHAR(M)	M (<=225) bytes
TINYBLOB, TINYTEXT	2⁸-1 bytes
BLOB, TEXT	2¹⁶-1 bytes
MEDIUMBLOB, MEDIUMTEXT	2²⁴-1 bytes
LONGBLOB, LONGTEXT	2³²-1 bytes
ENUM("value1", ...)	65535 members
SET("value1", ...)	64 members

CHAR & VARCHAR are the most common string types

CHAR is fixed-width

VARCHAR, BLOBs and TEXTs are variable width

Fixed-length row

row containing just fixed length items
Processed much faster than variable-length rows

MySQL generally converts CHARs to VARCHARS in tables with variable-length rows

BLOB (Binary Large OBject) & Text

BLOBs use case sensitive comparisons
TEXT uses case insensitive comparisons


Doc 19, Some MySQL Slide # 9

Date & Time Column Types

Type	Range
DATE	"1000-01-01" to "9999-12-31"
TIME	"-835:59:59" to "838:59:59"
DATETIME	"1000-01-01 00:00:00" to "9999-12-31 23:59:59"
TIMESTAMP[(M)]	19700101000000 to year 2037
YEAR[(M)]	1901 to 2155

DATE is time of day

TIME is elapsed time

"12:30" represents "00:12:30"


Doc 19, Some MySQL Slide # 10

Basic SQL Commands

CREATE TABLE table_name 
(
   col_name    col_type [ NOT NULL | PRIMARY KEY]
   [, col_name col_type [ NOT NULL | PRIMARY KEY]]*
)
DROP TABLE table_name
INSERT INTO table_name [(column [, column ]*)] 
VALUES (value [, value]*)
DELETE FROM table_name
WHERE column OPERATOR value
[ AND | OR column OPERATOR value ]*
SELECT [table.]column [, [table.]column]*
FROM table [=alias][, table [= alias]]*
[ WHERE [table.]column OPERATOR VALUE
[ AND | OR [table.]column OPERATOR VALUE]*]
[ ORDER BY [table.]column [DESC][, [table.]column [DESC]]

UPDATE table_name SET column=value [,column=value]*
WHERE column OPERATOR value
[ AND | OR column OPERATOR value]*

OPERATOR can be <,>,=,<=,>=,<>, or LIKE

VALUE can be a literal value or a column name


Doc 19, Some MySQL Slide # 11

Indexing

Column indexes make queries more efficient

MySQL before 3.23.2 did not allow indexed columns to be:

NULL
BLOB
TEXT

Unique & Primary Columns
Unique - index with out duplicate values
Primary key - unique column with index name Primary


Doc 19, Some MySQL Slide # 12

Creating Indexes
Can use

ALTER TABLE
CREATE INDEX
CREATE TABLE


Doc 19, Some MySQL Slide # 13

Examples - CREATE Format

CREATE TABLE table_name
(
   #create columns, then declare indexes
   INDEX index_name (column_list),
   UNIQUE index_name (column_list),
   PRIMARY KEY (column_list ),
   # more stuff
)
CREATE TABLE roger
(
   sam INT NOT NULL,
   PRIMARY KEY( SAM)
)
CREATE TABLE roger
(
   sam INT NOT NULL PRIMARY KEY
)
CREATE TABLE students
(
   name CHAR(25),
   address CHAR(60),
   INDEX (name, address)
)


Doc 19, Some MySQL Slide # 14

Alter Table

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE index_name (column_list)
ALTER TABLE table_name ADD PRIMARY KEY  (column_list)

Create Index


CREATE UNIQUE INDEX index_name ON table_name (column_list)
CREATE INDEX index_name ON table_name (column_list)
Doc 19, Some MySQL Slide # 15

Operators

Arithmetic
+, -, *, / , %

Logical
AND, &&
OR, ||
NOT, !

Bit Operators
&
|
<<

a << b left shift of a by b bits

>> right shift


Doc 19, Some MySQL Slide # 16

Comparison Operators

Operator	Example
=
!=, <>
<
<=
>=
>
IN	a IN (x, y, z, ... )
BETWEEN	a BETWEEN b AND c
LIKE	a LIKE b
NOT LIKE
REGEXP, RLIKE	a REGEXP b
NOT REGEXP
<=>	a <=> b (equal even if NULL)
IS NULL	a IS NULL
IS NOT NULL

Binary strings

CHAR BINARY, VARCHAR BINARY, and BLOB types

Binary string comparisons are case sensitive

Non-binary string comparisons are not case sensitive


Doc 19, Some MySQL Slide # 17

BINARY operator (MySQL 3.23)
Convert a string to binary

BINARY "abc" = "Abc"

Like & Regexp
LIKE patterns match only if the entire string is matched

REGEXP patterns match if the pattern is found anywhere in the string

LIKE is not case sensitive unless at least one operand is a binary string

REGEXP starting in 3.23.4 uses LIKE's case sensitive rules


Doc 19, Some MySQL Slide # 18

Like Pattern Matching

Character	Meaning
_	matches any single character
%	matches 0 or more characters of any value
\	escapes special characters

All other characters match themselves


Doc 19, Some MySQL Slide # 19

Regexp Pattern Matching

Sequence	Meaning
^	Match the beginning of the string
$	Match the end of string
. (period)	Match any single character
[...]	Match any character between the brackets
[^...]	Match any character not between the brackets
E*	Match zero or more instance of pattern E
E+	Match one or more instance of pattern E
E?	Match zero or one instance of pattern E
E1 \| E2	Match E1 or E2
E{m}	Match m instances of E
E{,n}	Match zero to n instances of E
E{m,}	Match m or more instances of E
E{m,n}	Match m to n instances of E
(...)	Group elements in to one element

All other characters match themselves

Expression	Result
"abc" REGEXP "a.c"	1
"abc" REGEXP "[a-z]"	1
"abc" REGEXP "[^a-z]"	0
"abc" REGEXP "^abc$"	1
"abcd" REGEXP "^abc$"	0
"abc" REGEXP "(abc){2}"	0
"abcabc" REGEXP "(abc){2}"	1

Previous visitors since 06-Nov-00 Next