Sql data definition ii
This presentation is the property of its rightful owner.
Sponsored Links
1 / 18

SQL Data Definition II PowerPoint PPT Presentation


  • 82 Views
  • Uploaded on
  • Presentation posted in: General

SQL Data Definition II. Stanislava Armstrong http://www.cs.nott.ac.uk/~saw/teaching/G64DBS/lecture_slides.html. Creating Tables. From last lecture… CREATE TABLE Columns Data types [NOT] NULL, DEFAULT values Constraints Primary keys Unique columns Foreign keys. CREATE TABLE <name> (

Download Presentation

SQL Data Definition II

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Sql data definition ii

SQL Data Definition II

Stanislava Armstrong

http://www.cs.nott.ac.uk/~saw/teaching/G64DBS/lecture_slides.html

SQL Data Definition II


Creating tables

Creating Tables

  • From last lecture…

    • CREATE TABLE

    • Columns

      • Data types

      • [NOT] NULL, DEFAULT values

    • Constraints

      • Primary keys

      • Unique columns

      • Foreign keys

CREATE TABLE

<name> (

<col-def-1>,

<col-def-2>,

:

<col-def-n>,

<constraint-1>,

:

<constraint-k>

)[Engine = <engine_choice>];

SQL Data Definition II


Deleting tables

Deleting Tables

To delete a table use:

DROP TABLE

[IF EXISTS]

<name>

e.g.:

DROP TABLE Fruit;

  • BE CAREFUL with any SQL statement with DROP in it

    • You will delete any information in the table as well

    • You won’t normally be asked to confirm

    • There is no easy way to undo the changes

SQL Data Definition II


Changing tables

Changing Tables

Sometimes you want to change the structure of an existing table

  • One way is to DROP it then rebuild it

  • This is dangerous, so there is the ALTER TABLE command instead

ALTER TABLE can

  • Add a new column

  • Remove an existing column

  • Add a new constraint

  • Remove an existing constraint

  • Change a column’s name and definition

SQL Data Definition II


Altering columns i

ALTERing Columns I

To add columns use:

ALTER TABLE <table>

ADD COLUMN <col> <col_definition>;

To remove columns use:

ALTER TABLE <table>

DROP COLUMN <name>;

E.g.:

#add a Quality column to the Fruit table

ALTER TABLE Fruit

ADD COLUMN Quality int;

#remove the Quality column from the Fruit table

ALTER TABLE Fruit

DROP COLUMN Quality;

SQL Data Definition II


Altering columns ii

ALTERing Columns II

To change columns use:

ALTER TABLE <table>

CHANGE COLUMN <old_col_name> <new_col_name>

<col_definition>;

To modify a column’s definition use:

ALTER TABLE <table>

MODIFY COLUMN <col_name> <col_definition>;

The new column definitions have to be compatible with all values already entered into the column.

SQL Data Definition II


Altering constraints

ALTERing Constraints

To add constraints use:

ALTER TABLE <table>

ADD CONSTRAINT

<definition>;

To remove UNIQUE constraints use:

ALTER TABLE <table>

DROP INDEX <name>;

To remove any other constraints use:

ALTER TABLE <table>

DROP CONSTRAINT <name>;

Examples:

#make the fruitName column in the Fruit table UNIQUE

ALTER TABLE Fruit

ADD CONSTRAINT

unFruitNameUNIQUE (fruitName);

#remove the UNIQUE constraint on fruitNamecolumn

ALTER TABLE Fruit

DROP INDEX unFruitName;

SQL Data Definition II


Insert update delete

INSERT, UPDATE, DELETE

  • INSERT - add a row to a table

  • UPDATE - change row(s) in a table

  • DELETE - remove row(s) from a table

  • UPDATE and DELETE use ‘WHERE clauses’ to specify which rows to change or remove

  • BE CAREFUL with these - an incorrect WHERE clause can destroy lots of data

SQL Data Definition II


Insert

INSERT

INSERT INTO

<table>

[(col1, col2, …)]

VALUES

(val1, val2, …);

e.g.:

INSERT INTO Fruit

(FruitID, FruitName, FruitPrice)

VALUES (NULL, 'kiwi', 20);

  • The number of columns and values must be the same

  • If you are adding a value to every column, you don’t have to list them, but you will need to put them in the correct order

SQL Data Definition II


Insert1

INSERT

Fruit

Fruit

Fruit

Fruit

ID

1

2

ID

1

2

ID

1

ID

1

2

Name

kiwi

apple

Name

kiwi

Name

kiwi

apple

Name

kiwi

apple

Price

20

Price

20

Price

20

34

Price

20

34

INSERT INTO Fruit

(ID, Name, Price)

VALUES (2, ‘apple’, 34)

INSERT INTO Fruit

(Name, ID)

VALUES (‘apple’, 2)

INSERT INTO Fruit

VALUES (2, ‘apple’, 34)

SQL Data Definition II


Update

UPDATE

UPDATE <table>

SET col1 = val1

[,col2 = val2…]

[WHERE

<condition>];

e.g.:

UPDATE Fruit

SET FruitID = 5

WHERE FruitName = 'kiwi‘;

  • All rows where the condition is true have the columns set to the given values

  • If no condition is given all rows are changed so BE CAREFUL

  • Values are constants or can be computed from columns

  • You can update multiple tables at the same time

SQL Data Definition II


Update1

UPDATE

Fruit

Fruit

Fruit

ID

1

2

3

4

ID

1

2

3

4

ID

1

2

3

4

Name

kiwi

apple

grape

lemon

Name

kiwi

apple

grape

melon

Name

kiwi

apple

grape

lemon

Price

20

34

70

2

Price

20

34

70

100

Price

25

39

75

7

UPDATE Fruit

SET Price = 100,

Name = ‘melon’

WHERE ID = 4;

UPDATE Fruit

SET Price = Price +5;

SQL Data Definition II


Delete

DELETE

Removes all rows which satisfy the condition

DELETE FROM

<table>

[WHERE

<condition>];

  • If no condition is given then ALL rows are deleted - BE CAREFUL

  • Some versions of SQL also have TRUNCATE TABLE <T> which is like DELETE FROM <T> but it is quicker as it doesn’t record its actions

    e.g.:

    DELETE FROM

    Fruit

    WHERE Price > 30;

SQL Data Definition II


Delete1

DELETE

Fruit

Fruit

ID

1

2

3

4

ID

2

3

Name

kiwi

apple

grape

lemon

Name

apple

grape

Price

20

34

70

20

Price

34

70

DELETE FROM

Fruit

WHERE Price = 20;

Fruit

DELETE FROM Fruit;

or

TRUNCATE TABLE Fruit;

ID

Name

Price

SQL Data Definition II


Being careful

Being Careful

  • When using DELETE and UPDATE

    • You need to be careful to have the right WHERE clause

    • You can check it by running a SELECT statement with the same WHERE clause first (more on this later :)

Before running

DELETE FROM Student

WHERE Year = 3;

run

SELECT * FROM Student

WHERE Year = 3;

SQL Data Definition II


Exercise

Exercise

SQL Data Definition II


Exercise1

Exercise

-Sam brought a holiday to Varna and is leaving on 23rd August 2012. He told us that lives in Chilwell, but he hasn’t given us a phone number. Update the database to reflect the new booking.

-Tania hasn’t sent us the payment for the holiday, her address is too vague and we haven’t been able to get hold of her over the phone. Remove her booking.

-Add £10.50 to the price of all holidays that cost more than £200.00.

-Add a new column, Attractions, to the Destination table. This column should hold a string of up to 500 characters, and if no Attractions is provided then it should default to the empty string.

SQL Data Definition II


Reading material

Reading Material

  • The Manga Guide to Databases – chapters 3

  • Database Systems – A Practical Approach to Design, Implementation and Management by Connolly and Begg – chapters 5 and 6

  • Any other book – chapter on SQL data definition

  • http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html- MySQL documentation for data definition

SQL Data Definition


  • Login