Skip to content

2. Creating and Populating a Database.md

Latest commit

 

History

History
296 lines (290 loc) · 11.9 KB

2. Creating and Populating a Database.md

File metadata and controls

296 lines (290 loc) · 11.9 KB

Creating and Populating a Database

Creating a MySQL Database

  • Sakila sample database is a fictious movie rental company

Using the mysql Command-Line Tool

  • mysql -u root -p;
  • show databases
  • use sakila use <database>
  • mysql -u root -p sakila
  • Some database services require a FROM statement in your query, for function calls, database services provide a table called dual to complete it.
  • SELECT now()

MySQL Data Types

Character Data

  • Stored as either fixed-length or variable-length strings
    • Fixed-lenght: right-padded with spaces and always consume the same number of bytes
    • Variable-length: not right-padded and don't always consume the same number of bytes
  • When defining a character column, you must specify the maximum size of any string to be stored in the column
    • char(20) /* fixed-length */
    • varchar(20) /* variable-length */
  • Maximum length for char is 255 bytes
  • Maximum length for varchar is 65,535 bytes
  • If you need to store longer strings like emails, and XML documents, then you should use mediumtext and longtext instead

Character sets

  • For latin languages like English, the amount of characters in the alphabet can be stored into a single byte
  • Languages like Japanese and Korean, require multiple bytes of data to store all the characters of the language, these character sets are call multibyte character sets
  • SHOW CHARACTER SET;
  • Prior to version 8, the default character set was latin1, but version 8 defaults to utf8mb4
  • You can define a character set for each character column in your database or for a table in the database, or for a database
    • varchar(20) character set latin1
    • create database european_sales character set latin1;

Text data

  • For storing text above 64KB in size
  • Types:
    • tinytext: 255
    • text: 65,535
    • mediumtext: 16,777,215
    • longtext: 4,294,967,295
  • When using a text type, be aware of the following
    • Data will be truncated if it exceeds the maximum text size for the column
    • Trailing spaces are not removed
    • Only the first 1,024 bytes of a text column is used for sorting ang grouping, although this can change
    • Text types are unique to different mySQL servers
    • There isn't a need to tinytext after V4 - which removed the 255 limit for varchar
  • If storing a dataeentry column, then varchar is adequate
  • If storing a document, then mediumtext or longtext is better

Numeric Data

  • Numeric data can be used in different ways, and the different numeric data types reflect these different ways
    • A boolean column to mark something as True or False
    • A primary key with auto intcrement
    • A postive number to represent item number in a shopping basket
    • Precise positional data
  • Numeric columns can be integers and even unsigned (>0) or signed integers (allow for - numbers)
  • Integer types
    • tinyint, smallint, mediumint, int, bigint
  • floating-point types
    • float, double
    • you are allowed to define the precision and scale of the floating point number
      • precision: the total allowable amount of digits
      • scale: allowable amount of digits to the right of the decimal point
      • storing numbers exceeding the scale or precision will round the number or throw an error
    • can be unsigned or signed

Temporal Data

  • Dates and time
  • Examples
    • Future date of a particular event
    • Shipping date
    • Birth date
    • Year column
    • Elapsed time
  • Types
    • Date: YYYY-MM-DD
    • Datetime: YYYY-MM-DD HH:MI:SS
    • Timestamp: YYYY-MM-DD HH:MI:SS
    • Year: YYYY
    • Time: HHH:MI:SS
  • Time, Datetime, Timestamp allow for fractional seconds up to 6 decimal places.
    • datetime(2): timevalues up to 2 decimal places (hundredths of a second)
  • Implementations
    • employee birth date: date - since knowing the time someone is born is usually excessive
    • when an order has shipped: datetime
    • track when a row has been modified: timestamp -- mysql can automatically populate a timestamp column with the servers time
    • column for year: year
    • record length of time for an action, task, or routine: time

Table Creation

  • Let's define a table to hold information about a person

Step 1: Design

  • Brainstorm about what kind of information would be usefule for a person
    • Name
    • Eye color
    • Birthdate
    • Address
    • Favourite Foods
  • Assign column names and data types
    • name: varchar(40)
    • eye_color: char(2)
    • birth_date: date
    • address: varchar(100)
    • favorite_foods: varchar(200)

Step 2: Refinement

  • The process of normalisation is ensuring there are no duplicate data and compound columns in your database design
  • In looking at our example database table the problems arise
    • name: is a compound object consisting of first and last name
    • there is no column that guaranttees unqiueness
    • address: is a compount object of street, street number, city, state, country, postal code
    • favourite_foods: is a list containing 0, 1, or more independent items. It's better to create a separate table with foreign key relationships
  • After considering these components
    • Person Table:
      • person_id: smallint (unsigned)
      • first_name: varchar(20)
      • last_name: varchar(20)
      • eye_color: char(2)
      • birth_date: date
      • street: varchar(30)
      • city: varchar(20)
      • state: varchar(20)
      • country: varchar(30)
      • postal_code: varchar(20)
    • Favourite_food:
      • person_id: foreign key from person table
      • food: varchar(20)
    • Something to consider:
      • You might decide to control want favourite_foods people are allowed to pick. In this case you can have a food table with food_id and food_name columnes and alter the food column in favourite_food to contain a foreign key to food. This woulld be a fully normalised design but its up to you what you want

Step 3: Building SQL Schema Statements

  • After designing and refining the table its time to construct the table with a schema statement

people Table

CREATE TABLE person
(
	person_id SMALLINT UNSIGNED,
	fname VARCHAR(20),
	lname VARCHAR(20),
	eye_color CHAR(2) CHECK (eye_color IN ('BR', 'BL', 'GR')
	birth_date DATE,
	street VARCHAR(30),
	city VARCHAR(20),
	state VARCHAR(20),
	country VARCHAR(20),
	postal_code VARCHAR(20),
	CONSTRAINT pk_person PRIMARY KEY (person_id)
);
  • Two constraints in this table
    • A check constraint for the eye_color column
    • A primary key constraint for the person_id column
  • A check constraint does not enforce the constraint, if you need it to be enforced then the enum data type is used - it mergers the check constraint with the data type of the column
    • eye_color ENUM('BR', 'BL','GR')
  • What is Null?
    • Sometimes it's not possible to provide a vlaue for a column in your table. In this case this column is null
    • Null can be used when the value is not applicable, unknown, or an empty set
    • When designing a table, you must specify which columns you allow to be null or not null

favourite_food table

CREATE TABLE favorite_food
(
	person_id SMALLINT UNSIGNED,
	food VARCHAR(20),
	CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
	CONSTRAINT fk_fav_food_person FOREIGN KEY (person_id) REFERENCES person (person_id)
);
  • Because a person can have multiple food, we cannot guarantee uniqueness with only the person_id column so we create a two column primary key
  • A foreign key constraint is placed on the person_id column, the only values that can exist in this column are person_id values that exist in the person table
  • Foreign key constraints can be added afterwards using the alter table statement

Populating and Modifying Tables

  • insert, update, delete, select

Inserting Data

  • Three componenets to an insert statement
    • Name of table to insert data into
    • Name of columns to populate
    • Values to populate the table
  • You don't need to provide data for every column in the table, you can update it later for leave it null due to circumstances

Generating numeric key data

  • How are nuemric primary keys generated?
    • Add one to the largest primary key value
    • Let the db provide the value for you
  • The most robust way is to let the db do it for you
  • In MySQL you can do this by turning the primary_key column's auto-increment feature
  • This is usually done at table creation by you can do it afterwards using alter table
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
  • When doing this you need to disable foreign key constraints and renable them afterwards
set foreign_key_checks=0;
ALTER TABLE person
	MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
set foreign_key_checks=1;
  • Now when you insert data into the person_id table, you can add the null value to the person_id field and the database will automatically generate the primary key id

The insert statement

  • Add data
INSERT INTO person
	(person_id, fname, lname, eye_color, birth_date)
	VALUES (null, 'William', 'Turner', 'BR', '1972-05-27');
  • You can insert a string into a date column as long as the string matches the format for the columns date type. It will convert the string for you
INSERT INTO favorite_food (person_id, food)
	VALUES (1, 'pizza');
INSERT INTO favorite_food (person_id, food)
	VALUES (1, 'cookies');
INSERT INTO favorite_food (person_id, food)
	VALUES (1, 'nachos');	
INSERT INTO person
	(person_id, fname, lname, eye_color, birth_date, street, city, state, country, postal_code)
	VALUES (null, 'Susan', 'Smith', 'BL', '1975-11-02', '23 Maple St.', 'Arlington', 'VA', 'USA', '20220');

Can I Get That in XML?

  • you can output a sql query using the --xml option when you login

Updating Data

UPDATE person
	SET 
	street = '1225 Tremont St.',
	city = 'Boston',
	state = 'MA',
	country = 'USA',
	postal_code = '02138'
	WHERE person_id = 1;

Deleting Data

DELETE FROM person
	WHERE person_id = 2;

When Good Statements Go Bad

  • Common mistakes when you are inserting and modifying data

Nonunique Primary Key

mysql> INSERT INTO person
-> (person_id, fname, lname, eye_color, birth_date)
-> VALUES (1, 'Charles','Fulton', 'GR', '1968-01-15');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
  • Due to the primary key constraint on person_id, you cannot provide a duplicate key value to the table if the key value already exists in the table

Nonexistent foreign key

  • Here's what happens if you add a value into a foreign key constraint column that doesn't exist in the foreign key table
mysql> INSERT INTO favorite_food (person_id, food)
-> VALUES (999, 'lasagna');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails ('sakila'.'favorite_food', CONSTRAINT 'fk_fav_food_person_id' FOREIGN KEY
('person_id') REFERENCES 'person' ('person_id'))

Foreign key constrains are enforced if you tables are created using the InnoDB storage engine [[Learning SQL#Transactions|Transactions]]

Column Value Violations
  • If you set a value outside of the enum for eye color
mysql> UPDATE person
-> SET eye_color = 'ZZ'
-> WHERE person_id = 1;
ERROR 1265 (01000): Data truncated for column 'eye_color' at row 1
  • not happy with it

Invalid Data Conversions

  • If you provide a string to a date column that doesn't match the expected format, you receive an error
mysql> UPDATE person
-> SET birth_date = 'DEC-21-1980'
-> WHERE person_id = 1;
ERROR 1292 (22007): Incorrect date value: 'DEC-21-1980' for column 'birth_date'
at row 1
  • It's usally a good idea to explicitly state the format string for your string instead of relying on the database - use the str_to_date function
UPDATE person
	SET birth_date = str_to_date('DEC-21-1980', '%b-%d-%Y')
	WHERE person_id = 1;

Types of MySQL date-formatters

Formatter
%a Weekday short name; Mon, Tue, Sun
%b Month short name
%c Month numeric
%d Day of Month Numeric
%f Microseconds
%H Hour of Day 24h
%h Hour of Day 12h
%i Minutes in Hour
%j Day of Year
%M Month full name
%m Month numeric
%p AM or PM
%s Number of Seconds
%W Weekday Full Name
%w Weekday Numeric 0=Sunday
%Y Year four-digit