Transcript Using Relational Databases and SQL

Using Relational Databases and SQL
Lecture 8:
Data Manipulation Language
Data Definition Language
Department of Computer Science
California State University, Los Angeles
Data Manipulation Language
• Substantially different material than we have been doing.
• Moving on from just querying existing data
Before We Start
• When modifying the database data, you are going to make
mistakes
• If you mess up you can recover the original database by dropping
the tables and rerunning the original script
• You can also protect against disasters by using transactions
Before We Start
• It is easier to use BEGIN and ROLLBACK than to drop the tables
and reinstall!
• Use BEGIN before entering DML commands
• Use ROLLBACK to undo all changes
• USE COMMIT to accept all changes
Transactions
• ACID
• Atomicity: a transaction should be done fully or not at all
• Consistency: when transaction is complete, data should be self-consistent;
no orphan records or inconsistent totals
• Isolation: no other users see any part of the transaction until it is complete
• Durability: when transaction is finished, the results persist
Data Manipulation Language
• DML for short
• Contains commands for modifying table data
• Insertion commands (INSERT INTO)
• Deletion commands (DELETE)
• Update commands (UPDATE)
• Not a query
• Queries extract data from the database
• Commands do not extract data from the database
Inserting Records
• Two syntaxes:
• INSERT INTO
• Insert one record at a time
• INSERT SELECT
• Insert one or more records at a time
Inserting Records
• INSERT INTO Syntax
• -- Form #1: Insert whole record.
INSERT INTO tablename
VALUES(value1, value2, ..., valuen);
• -- Form #2: Insert partial record. Non-specified fieldnames are assigned
default values.
INSERT INTO tablename(field1, field2, ..., fieldn)
VALUES(value1, value2, ..., valuen);
Inserting Records
• INSERT INTO salespeople values(5, "Mary", "Smith", "MSS",
200.00, 4)
• INSERT INTO salespeople (SalesID, FirstName, LastName,
Initials, Base) values(5, "Mary", "Smith", "mss", 100);
Inserting Records
• INSERT SELECT Syntax
• -- Form #1: Insert whole record.
INSERT INTO destination_table
SELECT field1, field2, ..., fieldn
FROM source_tables
WHERE conditions;
• -- Form #2: Insert partial record. Non-specified fieldnames are
assigned default values.
INSERT INTO destination_table(df1, df2, ..., dfn)
SELECT sf1, sf2, ..., sfn
FROM source_tables
WHERE conditions;
INSERT INTO Example
• Example:
• Add new title “Out of Medication” by the Neurotics. Assign it
titleNum 8 and genre alternative. Find the artistid of the Neurotics
by looking them up in the artists table.
Insert With A Transaction
• Begin;
• Select * from titles;
• INSERT INTO titles VALUES (8,(SELECT artistID FROM artists WHERE
artistname = “the Neurotics”), 'Out of Medication', 1, '1234567890',
“alternative”);
• Select * from titles;
• Rollback;
• Select * from titles;
Deleting Records
•
•
•
•
Deletes one or more rows from a table
Can select rows to delete using WHERE clause
Without WHERE condition, will delete all rows
Two syntaxes
• Single-Table DELETE Syntax
• Multi-Table DELETE Syntax
DELETE Syntax
• Syntax:
• DELETE
FROM tablename
WHERE condition;
DELETE Syntax
• Solutions:
• -- Delete all records.
DELETE FROM testtable;
• -- Delete all records for people named “Bob”
DELETE FROM testtable
WHERE name = ‘Bob’;
• Delete the record you added for ‘Out of Medication‘:
• DELETE FROM titles WHERE titleName = ‘Out of Medication’
DELETE Syntax
Delete all titles by the Neurotics:
BEGIN;
DELETE FROM titles WHERE artistID = (SELECT artistID FROM artists
WHERE artistName = 'the Neurotics')
DELETE Syntax
• Add a title ‘No Word From You ’ by Word in the genre rap with
titleID 100. studioID 3, and UPC 1234567890
• INSERT INTO titles VALUES (100, (SELECT artistID FROM artists
WHERE artistName = 'Word'), 'No Word From You', 3, '1234567890',
'rap');
• Then SELECT * FROM titles;
DELETE Syntax
• now delete all titles by Word
• DELETE FROM titles WHERE artistID = (SELECT artistID FROM artists
WHERE artistName = ‘Word’);
• SELECT * from the table again
DELETE Syntax
• Delete can lead to orphaned records if misused (a foreign key
with no primary key).
• DELETE FROM salespeople WHERE lastName = ‘Bentley’
• Unless the DB is set up to enforce foreign keys by refusing to let
you do this, all the studios and members represented by the
salesperson contain salesIDs that do not reference any record in
the salespeople table
Multi-Table DELETE Syntax
• Therefore, be careful what you delete!
• If you delete a record, and that record is referenced somewhere
else, you have an orphaned record!
Updating Records
• To update existing records, you may use one of the following
syntaxes:
• Single-table syntax.
• Multi-table equi-join syntax.
• Only equi-join is supported.
• You may not use any other join syntax (JOIN ON, JOIN USING, etc.)
Updating Records
• To update existing records:
• -- Single-table syntax.
UPDATE [IGNORE] tablename
SET field1 = value1, field2 = value2, ...
WHERE conditions;
• -- Multi-table equi-join syntax.
UPDATE [IGNORE] tablename1, tablename2, ...
SET field1 = value1, field2 = value2, ...
WHERE conditions;
• -- Multi-table subquery syntax.
UPDATE [IGNORE] tablename
SET field1 = subquery1, field2 = subquery2, ...
WHERE conditions;
Updating Records
• Examples:
• Bryce Sanders has decided to drop his stage name in favor of his
birth name, “Ebenezer Hackensack.” Update the members table
accordingly.
• UPDATE members SET lastname = ‘Hackensack’, firstName =
‘Ebenezer’ WHERE lastName = ‘Sanders’ and firstName = ‘Bryce’
• update members set city = "Alvarez" where city = "Alverez";
Updating Records
• Examples:
• -- MakeTrax has been bought by Pacific Rim. Update all titles for
MakeTrax to show the new Studio information.
Updating Records
• Examples:
begin;
UPDATE titles SET StudioID = (SELECT studioID FROM studios WHERE
studioName = 'Pacific Rim') WHERE studioID = (SELECT StudioID
FROM Studios WHERE StudioName = 'MakeTrax');
select * from titles;
rollback;
Updating Records
• The IGNORE keyword causes the DBMS to ignore updates that
would result in primary key duplicates, (instead of producing
error messages)
Updating Records
CREATE TABLE vehicles(
id int primary key,
name varchar(30)
);
INSERT INTO vehicles values(1, “Moby");
INSERT INTO vehicles values(2, “Morticia");
UPDATE vehicles SET id = 1 WHERE id = 2;
ERROR! BEEP! Update is not performed.
Update Ignore
INSERT INTO vehicles values(1, “Moby");
INSERT INTO vehicles values(2, “Morticia");
UPDATE IGNORE vehicles SET id = 1 WHERE id =
2;
Update is still not performed, but no error
messages or beeps.
Data Types
• Storing data in a database uses up resources
• Computers store information in bits
• one bit is equivalent to a switch that is either on or off; two possible
values
• One byte = 8 bits
• 2 ^ 8 = 256 possible values for one byte
Data Types
• What happens if you allocate 30 bytes to hold data, but none of
the records have more than 20 bytes in that field?
• What if you allocate 20 bytes, but one of the records has 30 bytes
of information in the field?
• what if you track dates using two digit years, and then a new century
starts? Do millions of people get charged negative 100 years of
mortgage interest?
Integer Data Types
• Data Types
•
•
•
•
•
TINYINT(width): 1 byte
SMALLINT(width): 2 bytes
MEDIUMINT(width): 3 bytes
INT(width): 4 bytes
BIGINT(width): 8 bytes
Numeric Data Types
• Data Types
•
•
•
•
FLOAT(n, d): 4 bytes
REAL(n, d): 8 bytes
n means n digits total
d means d digits after decimal point
• All (n, d) specifications are optional.
• FLOAT  given 1.06, displays 1.06
• FLOAT(10, 1)  given 1.06, displays 1.1
• FLOAT(10, 6)  given 1.06, displays 1.060000
String and Date Data Types
• Data Types
• DATE: 3 bytes
• CHAR(n): Fixed # of characters. Allocates the specified amount of
storage.
• VARCHAR(n): Variable characters. Stores the amount of data
actually inserted, plus a two-byte length marker, with total not
more than n.
• All (n) specifications are optional
•
•
•
•
CHAR(1)  Given ‘s’, displays s
CHAR(1)  Given ‘sample’, displays ‘s’
CHAR(10)  Given ‘sample’, displays ‘sample’
Prefer VARCHAR to CHAR
Data Definition Language
(DDL)
• You can use DML and DDL commands in the console, query
browser, and within an SQL script file
• MYSQL Workbench has a GUI tool that can generate DDL. You
might use it in real life, but *do not use it for assignments in this
class.* You must first learn DDL yourself!
• Transactions (BEGIN, ROLLBACK, and COMMIT) do not work
with DDL commands.
Special Notes
• Do not try to memorize all these commands
• Use templates
• After a while, you’ll remember them
Creating Tables
• Syntax:
• CREATE TABLE (
fieldname datatype NULL | NOT NULL,
fieldname datatype NULL | NOT NULL,
...
fieldname datatype NULL | NOT NULL
);
• Each row specifies a column definition
• Use NULL or NOT NULL to specify whether or not a column can
contain NULL values
CREATE TABLE Example
• Example:
• CREATE TABLE musician(Name varchar(30));
CREATE TABLE Example
• CREATE TABLE can also take an optional ENGINE parameter at
the end (MySQL only):
• CREATE TABLE Musician (
Name VARCHAR(32) NOT NULL
) Engine=InnoDB;
• MySQL uses pluggable storage engines
•
•
•
•
Default is MyISAM
Another popular one is InnoDB
Currently only InnoDB enforces foreign keys
Many uncommon ones are listed here
Adding Columns
• Syntaxes:
• ALTER TABLE tablename
ADD COLUMN column_definition;
• ALTER TABLE tablename
ADD COLUMN column_definition
FIRST;
• ALTER TABLE tablename
ADD COLUMN column_definition
AFTER column_name;
Adding Columns
• Example:
• ALTER TABLE Musician
ADD COLUMN MusicianID INT PRIMARY KEY
FIRST;
• ALTER TABLE Musician
ADD COLUMN Title VARCHAR(20) NULL;
• INSERT INTO Musician VALUES(1, ‘Sid’, ‘Bassist’);
Editing Columns
• MODIFY lets you change a column’s data definition (without
renaming it)
• CHANGE does the same thing as MODIFY, but you can also
specify a new column name
• Syntax:
• ALTER TABLE tablename
MODIFY fieldname column_definition;
• ALTER TABLE tablename
CHANGE oldname newname column_definition
Editing Columns
• Examples:
• -- Let’s rename the Title column to Instrument.
ALTER TABLE Musician
CHANGE Title Instrument VARCHAR(32) NOT NULL;
Dropping Columns
• Syntax:
• ALTER TABLE tablename
DROP COLUMN column_name;
• Examples:
• ALTER TABLE Musician
DROP COLUMN Instrument;
Constraints
• A constraint is any kind of restriction placed on the data inserted
into a table
• Primary Key Constraints: Enforces uniqueness of data.
• Foreign Key Constraints: A value must refer to an existing piece
of data.
• Default Constraints: Data not specifically inserted will take on
default values.
• Unique Constraints: Requires data (other than the primary key)
to be unique.
Primary Key Constraints
• CREATE TABLE Syntax:
• Can add the keyword PRIMARY KEY at end of column definition
• ALTER TABLE Syntax:
• ALTER TABLE tablename
ADD CONSTRAINT constraint_name
PRIMARY KEY (field1, field2, ...);
Examples
• Examples:
• CREATE TABLE Musician(
MusicianID INT NOT NULL,
CONSTRAINT pk_musicianID
PRIMARY KEY(MusicianID)
);
• CREATE TABLE Musician(
MusicianID INT PRIMARY KEY
);
• Create table tracks(titleid int, tracknum int, primary key(titleid,
tracknum));
Foreign Key Constraints
• Sometimes called integrity constraints
• CREATE TABLE Syntax:
• FOREIGN KEY(field1, field2, ...)
REFERENCES parent_table(field1, field2, ...)
• In members:
• ..foreign key(salesid) references salespeople (salesid)
• ALTER TABLE Syntax:
• ALTER TABLE tablename
ADD CONSTRAINT constraint_name
FOREIGN KEY (field1, field2, ...)
REFERENCES parent_table(field1, field2, ...);
Foreign Key Constraints
• Example:
• ALTER TABLE members ADD CONSTRAINT fk_salespeople foreign
key(salesID) references salespeople(salesID);
• Now try this:
INSERT INTO members values (1, "Anderson", "Aaron", "1 Main St",
"Anytown", "ZZ" , "12345", "USA", 123,
123,"[email protected]","M", "1980-10-10", 500);
• Fails because there is no salesperson #500
Unique Constraints
• Use to make one or more columns (other than the primary key)
contain only unique values
• CREATE TABLE Syntax:
• Just add the UNIQUE keyword at the end.
Unique Constraints
• ALTER TABLE Syntax:
• ALTER TABLE tablename
ADD CONSTRAINT constraint_name
UNIQUE (fieldname1, fieldname2);
eg, ALTER TABLE musician
ADD CONSTRAINT uniq UNIQUE (Instrument);
• Now, insert into musician values(‘Vuvuzuelist’) will work, but insert
into musician values(‘Pianist’) will fail
Unique Constraints
• Examples:
• CREATE TABLE login(
user_id INT PRIMARY KEY,
username VARCHAR(32) NOT NULL UNIQUE,
password VARCHAR(32) NOT NULL
);
• INSERT INTO login values (1, ‘bsmith’, mypassword’);
• INSERT INTO login values (2, ‘bsmith’, ‘anotherpass’);
Default Constraints
• Using CREATE TABLE:
• After specifying the datatype in the column definition, use the
following:
DEFAULT value
• Using ALTER TABLE:
• ALTER TABLE tablename
ALTER fieldname
SET DEFAULT value;
Default Constraints
• Examples:
• DROP TABLE login;
• CREATE TABLE login(user_id INT NOT NULL PRIMARY KEY,
username VARCHAR(32) NOT NULL UNIQUE, password
VARCHAR(32) DEFAULT ‘godzilla’ NOT NULL);
Dropping Constraints
• Do not read the book on this! The book’s code will not work! Use
the following syntax:
• Syntax:
• -- To drop a primary key...
ALTER TABLE tablename
DROP PRIMARY KEY;
• -- To drop a foreign key...
ALTER TABLE tablename
DROP FOREIGN KEY keyname;
Examples
• Examples:
ALTER TABLE members
DROP FOREIGN KEY fk_salespeople;
Identity Columns
• Purpose is to auto-generate primary key values
• MySQL uses the non-standard keyword AUTO_INCREMENT and
you can only define it on the primary key
• SQL standard uses GENERATE keyword
• Syntax:
• fieldname datatype NOT NULL AUTO_INCREMENT
Identity Column Example
• Example:
• DELETE FROM Musician;
• ALTER TABLE musician add column id int primary key
AUTO_INCREMENT;
• INSERT INTO Musician VALUES('Guitarist', 'Keith', 0);
INSERT INTO Musician VALUES('Drummer', 'Charlie', 0);
• What happens with INSERT INTO Musician VALUES(‘Bassist’, ‘Bill', 2) ?
Dropping Tables
• Syntax:
• DROP TABLE [IF EXISTS] tablename;
• Example:
• DROP TABLE musicians;
• NOTE: BEGIN and ROLLBACK do not work with DROP TABLE
Dropping Tables
• When dropping tables where foreign keys are enforced, it is
useful to type in the following foreign key check commands
when using the InnoDB storage engine
•
•
•
•
•
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS tablename;
DROP TABLE IF EXISTS tablename;
DROP TABLE IF EXISTS tablename;
SET FOREIGN_KEY_CHECKS = 1;
• Else you would have to think very carefully about how to order
your deletes, since drops that would create orphans would not
be executed
Scripts
• A script contains a series of SQL/DDL/DML commands that execute
sequentially
• No new syntax
• You ran lyric.sql when you set up the Lyric DB in week one
• In mySQL, run from the command line with the source command,
ed source test.sql
Scripts
• Example:
drop table if exists vehicles;
create table vehicles(
id int not null,
name varchar(30)
);
insert into vehicles values(1, "Ruby");
select * from vehicles;
• Save as test.sql
• mysql> source test.sql;