1 / 13

Data Definition

After this lecture, you should be able to: Use SQL DDL Statement to define a database structure. Work with mysql or phpMyAdmin to access your MySQL databases. Complete Assignment 2 (Part II - a, b) . Data Definition . SQL DDL Statements. Create Table

beau
Download Presentation

Data Definition

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. After this lecture, you should be able to: Use SQL DDL Statement to define a database structure. Work with mysql or phpMyAdmin to access your MySQL databases. Complete Assignment 2 (Part II - a, b). Data Definition

  2. SQL DDL Statements • Create Table create table s (sno char(5), ... ); • Drop Table drop table s; • Alter Table alter table s add phone char(13); • Create View create view SV as select .... • Drop View drop view SV; • Create Index create index X on s(sno); • Drop Index drop index X;

  3. Suppliers-Parts Database: E-R Diagram sno (key) pno (key) sname pname SP M S M P city status city qty color weight

  4. Supplier-Part Database Table S Table P sno | sname | status | city pno | pname | color | weight | city ------------------------------ ---------------------------------- s1 | Smith | 20 | London p1 | nut | red | 12 | London s2 | Jones | 10 | Paris p2 | bolt | green | 17 | Paris s3 | Blake | 30 | Paris p3 | screw | blue | 17 | Rome s4 | Clark | 20 | London p4 | screw | red | 14 | London s5 | Adams | 30 | Athens p5 | cam | blue | 12 | Paris p6 | cog | red | 19 | London Table SP sno | pno | qty --------------- s1 | p1 | 300 s1 | p2 | 200 s1 | p3 | 400 s1 | p4 | 200 s1 | p5 | 100 s1 | p6 | 100 s2 | p1 | 300 s2 | p2 | 400 s3 | p2 | 200 s4 | p2 | 200 s4 | p4 | 300 s4 | p5 | 400

  5. Create Table Statements create table s ( sno char(5) not null, sname char(20) not null, status smallint, city char(15), primary key (sno) ); create table p ( pno char(6) not null, pname char(20) not null, color char(6), weight smallint, city char(15), primary key (pno) ); create table sp ( sno char(5) not null, pno char(6) not null, qty integer not null, primary key (sno, pno) );

  6. Data Types • integer The magnitude range is -2,147,484,648 ... +2,147,484,647. • real For fixed or floating-point numbers. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. • char For fixed length character strings up to 255 bytes. • varchar For variable-length character strings. The maximum length is 65,532 bytes. • bit[n] n indicates the number of bits, from 1 to 64. The default is 1 if n is omitted . • date • Year values in the range 70-99 are converted to 1970-1999. • Year values in the range 00-69 are converted to 2000-2069. • datetime Date and time. The format is ‘YYYY-MM-DD HH:MM:SS’.

  7. Creating S-P-SP Database % mysql –h mysql.cs.orst.edu –u pham –p pham Enter password: mysql> \. create_s_p_sp.sql; Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) SQL> show tables; TABLE_in_pham ----------------- p s sp

  8. Looking at Table Structures mysql> desc s; Name Null? Type ---------- -------- ---- SNO NOT NULL CHAR(5) SNAME NOT NULL CHAR(20) STATUS SMALLINT(6) CITY CHAR(15) mysql> desc p; Name Null? Type ---------- -------- ---- PNO NOT NULL CHAR(6) PNAME NOT NULL CHAR(20) COLOR CHAR(6) WEIGHT SMALLINT(6) CITY CHAR(15)

  9. Adding Data to a Table insert into s values('s1', 'Smith', 20, 'London'); insert into s values('s2', 'Jones', 10, 'Paris'); insert into s values('s3', 'Blake', 30, 'Paris'); insert into s values('s4', 'Clark', 20, 'London'); insert into s values('s5', 'Adams', 30, 'Athens'); mysql> \. insert_s_p_sp.sql Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) . . .

  10. Showing Table Contents SQL> select * from s; SNO SNAME STATUS CITY ---- ------- -------- ------ s1 Smith 20 London s2 Jones 10 Paris s3 Blake 30 Paris s4 Clark 20 London s5 Adams 30 Athens SQL> select * from p; PNO PNAME COLOR WEIGHT CITY ----- ------- ------- -------- ------- p1 nut red 12 London p2 bolt green 17 Paris p3 screw blue 17 Rome p4 screw red 14 London p5 cam blue 12 Paris p6 cog red 19 London

  11. Sailors-Boats Database:E-R Diagram (Schema) sid (key) sname bid (key) bname Reserves Sailor M M Boat date rating age color

  12. Create Table Statements create table sailors ( sid integer not null, sname char(20) not null, rating smallint, age real, primary key (sid) ); create table boats ( bid integer not null, bname char(20) not null, color char(10), primary key (bid) ); create table reserves ( sid integer not null, bid integer not null, day date not null, primary key (sid, bid, day) );

  13. Sailors-Boats Database: Tables Sailors Boats Reserves

More Related