Transcript Insert Into course

SQL
The Data Manipulation
Language
Copyright © 1998-2012 Curt Hill
Data Manipulation
Language
• The DML is used for most tasks
• Generate queries
– Covered in previous presentations
•
•
•
•
Insert records
Delete records
Update records
All the tables must exist prior to any
of these
Copyright © 1998-2012 Curt Hill
Use Statement in SQL
Server
• The use changes the database that
the next statements will act upon
• The server may have several
databases
– This selects which one to use
– Leaving it out usually lets the rest of the
commands apply to the database
named master
• Example:
Use college
Copyright © 1998-2012 Curt Hill
Insert Form
• Insert one record into a table
• General form:
INSERT INTO table (f1,f2,…fn) VALUES
(v1,…vn)
• Table is the relation to insert a new
record into
• The fn values are the field names
• The v1 values are the values to make
up the record
• If a field and its value are left out a null
is inserted Copyright © 1998-2012 Curt Hill
More on Insert
• The field names and values are
paired
– The Nth name receives the nth value
• The field names may be left out if
every column receives a value
– The values then match the order given
in the Create Table
• All the values are literals
– Character constants in quotes
– Numbers appear as themselves
• All constraints are checked
Copyright © 1998-2012 Curt Hill
Second Insert Example
• Insert Into course
(number,crhr,title, dept)
VALUES (141, 4,
'College Physics‘,
'PHYS')
• Insert Into course
VALUES ('PHYS', 141, 4,
'College Physics')
Copyright © 1998-2012 Curt Hill
Update
• Change values in one or more
records
• General form:
UPDATE table_name
SET field_name = constant,
fn2 = c2, …
WHERE condition
• Like the Select the Where is optional
– Leaving it out updates every record
– A similar thing can be done with the
default when creating table
Copyright © 1998-2012 Curt Hill
Update Examples
• Update students
set s_balance = 0
– Sets all student balances to zero
• UPDATE course
SET c_title = “Dumb course”
WHERE dept = ‘CS’ AND
number = 170
Copyright © 1998-2012 Curt Hill
Commentary
• The database may restrict updates
that violate constraints whether they
would cause problems or not
• Clearly we cannot update a primary
key into another one that exists
• Updates that violate foreign keys or
other constraints will also be
terminated
• If the change is to keys, better to
delete and then insert
Copyright © 1998-2012 Curt Hill
Delete
• Delete one or more records
• Similar form to a Select
• Form:
DELETE
FROM table_name
WHERE condition
• Any records that match are removed
• Always safest if you match the
primary key or do a similar query
Copyright © 1998-2012 Curt Hill
Delete Example
• DELETE
FROM course
WHERE dept = ‘CS’ AND
number = 170
• DELETE
FROM faculty
WHERE naid = 144
Copyright © 1998-2012 Curt Hill
Cascaded Deletions
• Insertions may only be allowed if the
constraints are honored
• A deletion may change constraints
as well
• In the case of deleting a student
– All the grades for that student must
also be deleted
• Different databases may handle the
cascade problem differently
Copyright © 1998-2012 Curt Hill
Example delete
• Delete a student:
delete
from students
where s_id = 1184
• Disallowed unless grades are first
deleted:
delete
from grades
where g_naid = 1184
Copyright © 1998-2012 Curt Hill
Allowing cascaded deletes
• The default behavior is to disallow
any insert/update/delete that
violates integrity
• There is no getting around inserts
• Updates and deletes can allow
cascading, but it must be specified
with the Create Table or Alter
commands
• The ON clause is attached to the
foreign key clause
Copyright © 1998-2012 Curt Hill
Allowing Cascade Deletes
• Specify:
on delete cascade
as part of the foreign key declaration
• Example:
Create Table grades ( …
constraint g_fk2
Foreign Key (g_naid)
References students (s_id)
On delete cascade,
… )
• Could be update as well
Copyright © 1998-2012 Curt Hill
Into Clause
• A Select creates a relation
• The relation is generally used and
then discarded
• You may also create a new
permanent relation with the INTO
clause
• This goes between Select and From
• Usually used for tables needed for a
short period of time
– Minutes to days
Copyright © 1998-2012 Curt Hill
Into Example
• Students taking CS classes:
Select s_id, s_name,
s_address
INTO CS_Students
From students, grades
Where g_naid = s_id AND
g_dept = 'CS'
Copyright © 1998-2012 Curt Hill
Into Commentary
• This does not need the field
definitions like a create table
– They are copied from original fields
• A different field name can be
obtained by using an alias
• A similar thing can be done with a
view, but a view does not require
storage
Copyright © 1998-2012 Curt Hill
Later Updates
• A table created with an Into is now a
separate table with separate data
• A view has no real data
– The data belongs to the queried tables
• When the original table is updated:
– The View is changed
– The Into table stays the same
• The Into table may also be updated
Copyright © 1998-2012 Curt Hill