Download Document 19 Some MySQL - Study Guide | CS 580 and more Study notes Computer Science in PDF only on Docsity!
CS 580 Client-Server Programming Fall Semester, 2000 Doc 19 Some MySQL Contents SQL............................................................................................... 2 History ........................................................................................ 2 MySQL.......................................................................................... 3 Database & tables ...................................................................... 3 MySQL Names ........................................................................... 4 MySQL Data Types .................................................................... 6 MySQL Columns Types ............................................................. 7 Numeric.................................................................................... 7 String Column Types ............................................................... 8 Date & Time Column Types..................................................... 9 Basic SQL Commands ............................................................. 10 Indexing.................................................................................... 11 Operators ................................................................................. 15
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
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
SQL Not well followed ANSI X3.135-
SQL First commonly followed standard ANSI X3.135- SQL
ISO/IEC 9075-1 through 5 New SQL standard
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
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
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, Unsigned Values: 0 to 65, MEDUIMINT[(M)] Signed Values: -8,388,608 to 8,388, Unsigned Values: 0 to 16,777, INT[(M)] Signed Values: -2,147,683,648 to 2,147,683, Unsigned Values: 0 to 4,294,967, BIGINT[(M)] Signed Values: -9,223,372,036,854,775,808 to 9,223,372,036,854,775, Unsigned Values: 0 to 2^32 - FLOAT[(M,D)], FLOAT(4)
MIN VALUES: ±1.175494351E- MAX VALUES: ±3.402823466+
DOUBLE[(M,D)], FLOAT(8)
MIN VALUES: ±2.22507E- MAX VALUES: ±1.79769+
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
String Column Types
Type Max Size CHAR(M) M (<=225) bytes VARCHAR(M) M (<=225) bytes TINYBLOB, TINYTEXT
28 -1 bytes
BLOB, TEXT 216 -1 bytes MEDIUMBLOB, MEDIUMTEXT
224 -1 bytes
LONGBLOB, LONGTEXT
232 -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
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
Indexing
Column indexes make queries more efficient
MySQL before 3.23.2 did not allow indexed columns to be:
Unique & Primary Columns
Unique - index with out duplicate values Primary key - unique column with index name Primary
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) )
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)
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
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
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 E 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
