Week 03 Notes 01

mysql Data Definition Language (ddl)

DDL is SQL used to create tables

 

Here is the Student, Course, Schedule relationships diagram.

 

http://cs.sou.edu/~nordquip/cs360/notes/week06notes01_files/image001.jpg

 

All ID fields are numbers, and the name fields are text.

 

 

See ../demos/w05n03.sql

 

Create table scripts

Usually start with

Drop table      -- removes any table that previously existed

Syntax

CREATE TABLE <Table-name> (

<Field name> <type> <attribute-constraints>,

<Field name> <type> <attribute-constraints>,

<General-constraints> ) ;

 

Table names are case sensitive in mysql.

Field names seem not to be case sensitive.

Data types

See http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Numeric types

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Ints:

Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

 

UNSIGNED (nonstandard) – stores 0 to twice_signed_upperBound

ZEROFILL – implies UNSIGNED

Fixed point decimal:

From the manual: http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html :

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL,NUMERIC is implemented as DECIMAL.

 

Values of type Decimal are stored as ints, e.g., if the type is Decimal(5,2), the value 16.52 will be stored as the INT 1652.  The DBMS will add the decimal point in the appropriate place whenever it outputs one of these values.

Floats:

For FLOAT the SQL standard allows an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. MySQL also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in a four-byte single-precision FLOAT column. A precision from 24 to 53 results in an eight-byte double-precision DOUBLE column.

 

Nonstandard display syntax supported by mysql:

FLOAT(m,d) 

FLOAT(7,4) would be displayed as -999.9999

 

For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of digits.

Note:

Except for FLOAT(m, d), The display width specifications above do not affect the amount of storage the database manager uses to store values.  See http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html | Storage Requirements for Numeric Types

Examples:

Try int(4), float(7,4)

 

Date types

  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | YEAR

 

Curtime() function returns the current time

 

Text types

http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html

CHAR(len) – fixed length string (right padded with spaces)

VARCHAR(len) – variable length string with max = len

 

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.  (http://dev.mysql.com/doc/refman/5.0/en/char.html)

 

 

(VARCHAR is a very commonly used data type.)

 

| TINYTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
| TEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
| MEDIUMTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
| LONGTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]

 

These types store characters from some character set just as varchars do, the difference is a fixed amount of storage is allocated for every value of one of these types.

 

A character set is a set of characters.  Common character sets are latin and utf8, but there are others.  The character set you use determines the number of bytes it takes to store a single character of that set.

 

A collation is a rules for ordering characters in a character set.

 

Binary types

CHAR can be BINARY

VARCHAR can be VARBINARY

Store arbitrary byte strings rather than strings of characters from a particular character set.

 

From http://dev.mysql.com/doc/refman/5.0/en/charset-binary-collations.html:

Binary strings are sequences of bytes and the numeric values of those bytes determine sort order.

 

From http://dev.mysql.com/doc/refman/5.5/en/blob.html:

If you use the BINARY attribute with a TEXT data type, the column is assigned the binary collation of the column character set.

 

The binary collation is named <charset_name>_bin, e.g. latin_bin and utf8_bin.

 

From http://dev.mysql.com/doc/refman/5.0/en/charset-binary-collations.html: 

The unit for sorting and comparison. Binary strings are sequences of bytes. Sorting and comparison is always based on numeric byte values. Nonbinary strings are sequences of characters, which might be multi-byte. Collations for nonbinary strings define an ordering of the character values for sorting and comparison. For the_bin collation, this ordering is based solely on binary code values of the characters (which is similar to ordering for binary strings except that a _bin collation must take into account that a character might contain multiple bytes). 

 

Blob types

Analogous to Text types, but for arbitrary byte strings.

 
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB

 

Blobs could be used, e.g., to store the bytes in an image or audio file.

Constraints

Single attribute constraints:

May add the following constraints following the type of an attribute:

NOT NULL

NULL

UNIQUE

AUTO_INCREMENT – inserting NULL gives the next sequence value.

 

CHECK not supported by mysql

 

DEFAULT (not really a constraint, but can be used to set a default)

Primary Key constraint:

Add using a general constraint entry, as follows:

CONSTRAINT  <constraint_name>  PRIMARY KEY(<attributename1>, <attributename2>, …)

 

<constraint_name> is <TableName>_PK by convention

<attributename1>, <attributename2>, etc.  name the attributes that will be concatenated to make the primary key.

Examples:

Create table Employee(

EmployeeNum  INT Not null,

Email VarChar(100) not null unique,

Constraint Employee_PK Primary Key(EmployeeNum)

);

 

Primary and foreign key constraints must be assigned names, e.g., Employee_PK in the last example for a Primary Key constraint, so they can be referred to later.

(If you need to drop a constraint, you need to refer to it by name.)

 

Foreign Key consraints:

Foreign keys are used by the DBMS to enforce referential integrity, i.e., to ensure tables linked by foreign keys are always synchronized.

 

Add using a general constraint entry, as follows:

CONSTRAINT  <constraint_name>  FOREIGN KEY(<name_of_attr_in_this_table>)

REFERENCES <table_containing_primary_key> ( <name_of_attr_in_referenced_table>)

ON DELETE CASCADE ;

(the ON DELETE CASCADE phrase is optional)

 

<constraint_name> contains <TableName>_FK by convention

To see constraints on a table: 

show create table <table_name>; 

(In mysql workbench look under Foreign Keys tab of the alter table view.)

 

For example:

show create table schedule;

Alter – change a tables ddl

alter table Employee modify email varchar(20); -- still keeps not null constraint

p. 151 in text

 

Try adding some data to the table then altering it (see DML below).

Data Manipulation (DML) SQL commands

Insert

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

 

INSERT inserts new rows into an existing table. The INSERT ... VALUES and INSERT ... SET forms of the statement insert rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. INSERT ... SELECT is discussed further in Section 12.2.5.1, “INSERT ... SELECT Syntax”.

 

Examples

Insert Employee values (1, 'n1@sou.edu');

 

The col_name=expr syntax allows you to leave out fields.

 

Delete

DELETE FROM <tbl_name>                     # delete all records from <tbl_name>

[WHERE <where_condition>]                   # delete records matching <where_condition>

Update 

UPDATE <tbl_name>

SET col_name1=<expression>|DEFAULT [, col_name2=<expression2>|DEFAULT) ] …

WHERE <where_condition>

 

Find rows matching <where_condition> and update the columns specified with the values specified.

 

Constraint violation Examples:

 

Create table TruffleBox(

name VarChar(20),

bSize INT default 0,

Constraint TruffleBox_PK Primary Key(name)

);

 

Create table Flavor(

name VarChar(20),

TBName VarChar(20),

Constraint FlavorPK Primary Key(name),

Constraint FlavorFKTruffleBox Foreign Key (TBName)

References TruffleBox(name) on delete cascade on update cascade

);

 

Try inserting into Flavor when there is no corresponding record in TruffleBox.

Try deleting a record from TruffleBox and see what happens to ‘linked’ records in Flavor.