Lecture 11 - Modifying DBs and using MySQL - annotated

Lecture 11 - Modifying DBs and using MySQL - annotated -...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Modifying DBs and using MySQL INFO/COM S 230: Intermediate Web Design and Programming Modifying databases via SQL INSERTs We can INSERT tuples into a relation, UPDATE tuples, and DELETE tuples from a relation. INSERT INTO relation VALUES (value1, value2, ..., valuek); E.g. INSERT INTO Movies VALUES (`American Beauty', 1999, 122); Title Gladiator A Beautiful Mind Chicago The Return of the King Million Dollar Baby Year 2000 2001 2002 2003 2004 Length 155 mins. 135 mins. 113 mins. 201 mins. 132 mins. Another variant INSERT INTO Movies(Length, Title, Year) VALUES(122, `American Beauty', 1999); Using queries for INSERTs We can use the result of queries to do an INSERT. INSERT INTO Movies(Title, Year) SELECT DISTINCT Title, Year FROM StarsIn WHERE (Title, Year) NOT IN (SELECT Title, Year FROM Movies); 1 Easy PDF Creator is professional software to create PDF. If you wish to remove this line, buy it now. UPDATEs UPDATE relation SET attribute = expression WHERE condition; E.g. UPDATE Movies SET Length = Length * 60; DELETEs DELETE FROM relation WHERE condition; E.g. DELETE FROM Movies WHERE Title IN (SELECT Title FROM StarsIn WHERE Name=`Robin Williams'); Add a new blue boat, id 105, named `Clipper'. Increase the rating of every sailor by 1. Creating tables in SQL Remove every sailor whose age is over 65. CREATE TABLE To make a relation in a database, we use the CREATE TABLE command. CREATE TABLE attribute1 attribute2 ... attributek ); relation ( type1, type2, typek Attribute types Most common types: int(d): integer of up to d digits decimal(d,w): number with d digits, w after the decimal point char(m): string of exactly m characters (spaces added if necessary) varchar(m): string of up to m characters blob: a "binary large object" up to 64K bytes long date: a date in YYYY-MM-DD format time: a time in HH:MM:SS format datetime: date & time in YYYY-MM-DD HH:MM:SS format year: year in YYYY format 2 Easy PDF Creator is professional software to create PDF. If you wish to remove this line, buy it now. Key constraints E.g. CREATE TABLE Movies ( Title varchar(150), Year year, Length int(5) ); Two ways to specify the primary key: CREATE TABLE relation ( attribute1 type1 PRIMARY KEY, ... attributek typek ); Or CREATE TABLE attribute1 attribute2 ... attributek relation ( type1, type2, typek What has to be true of a primary key? PRIMARY KEY (attribute1, attribute2) ); Not null We can impose that certain attributes are not null. CREATE TABLE Movies ( Title varchar(150) NOT NULL, Year year NOT NULL, Length int(5) PRIMARY KEY (Title, Year) ); Default values We can specify the default value for some attributes if someone inserts a NULL for this attribute, use this instead. CREATE TABLE Movies ( Title varchar(150) NOT NULL, Year year NOT NULL, Length int(5) DEFAULT 120, PRIMARY KEY (Title, Year) ); 3 Easy PDF Creator is professional software to create PDF. If you wish to remove this line, buy it now. Create new tables for the database with the following schema: Dock(did: integer, ddescription: string) CanDock(bid: integer, did: integer) Using MySQL Useful MySQL commands show databases; -- list all DBs create database dbname; -- create a database; use dbname; -- use a particular DB show tables; -- show all relations associated with the current DB show columns from tablename; -- show the attributes of tablename Our favorite queries SELECT Name, Title, Year FROM Movies NATURAL JOIN StarsIn; SELECT Name, Title, Year FROM Movies NATURAL LEFT OUTER JOIN StarsIn; SELECT Title, Year, Length FROM Movies WHERE Length > (SELECT AVG(Length) FROM Movies); SELECT Year, AVG(Length) AS AvgLength FROM Movies GROUP BY Year; Let's make it from scratch... create database movies2; use movies2; CREATE TABLE Movies ( Title VARCHAR(150) NOT NULL, Year YEAR NOT NULL, Length INT(5), PRIMARY KEY (Title, Year) ); CREATE TABLE StarsIn ( Name VARCHAR(50) NOT NULL, Title VARCHAR(150) NOT NULL, Year YEAR NOT NULL, PRIMARY KEY (Name, Title, Year) ); INSERT INTO Movies VALUES('Gladiator', 2000, 155); INSERT INTO Movies VALUES('Crouching Tiger, Hidden Dragon', 2000, 120); INSERT INTO Movies VALUES('Moulin Rouge', 2001, 127); INSERT INTO Movies VALUES('A Beautiful Mind', 2001, 135); INSERT INTO Movies VALUES('Chicago', 2002, 113); INSERT INTO Movies VALUES('Lost in Translation', 2003, 102); INSERT INTO Movies VALUES('The Return of the King', 2003, 201); INSERT INTO Movies VALUES('Million Dollar Baby', 2004, 132); INSERT INTO StarsIn VALUES('Russell Crowe', 'Gladiator', 2000); INSERT INTO StarsIn VALUES('Russell Crowe', 'A Beautiful Mind', 2001); INSERT INTO StarsIn VALUES('Viggo Mortensen', 'The Return of the King', 2003); INSERT INTO StarsIn VALUES('Hillary Swank', 'Million Dollar Baby', 2000); But what if you want to use a database on the 230 server? 4 Easy PDF Creator is professional software to create PDF. If you wish to remove this line, buy it now. phpMyAdmin Using phpMyAdmin For the 230 server, we have a MySQL administration tool, phpMyAdmin. Can access via https://info230.cs.cornell.edu/phpMyAdmin with your INFO230 server login and password. You can also install on your own machine from www.phpmyadmin.net (with some caveats getting it working with MySQL 5.0 is not straightforward). MySQL DBs on the 230 server Each user on the 230 server has a MySQL DB info230_SP08_username. You can use it for your upcoming projects and to do whatever experimentation you want (within reason...) Project 3: Photo gallery Now you can practice your skills by writing your own photo gallery website! Part I: Due 3/5 (WEDNESDAY) Set up tables in your 230 server DB, draft basic navigation of pages Part II: Due 3/30 Working site, with a secure login for photo uploading Review SQL allows us to state constraints on the data in the CREATE TABLE statement, including domain constraints and key constraints. We're now ready to exercise our SQL skills in the MySQL DB, either through installs on own machine or via phpMyAdmin on the INFO 230 server. 5 Easy PDF Creator is professional software to create PDF. If you wish to remove this line, buy it now. ...
View Full Document

This note was uploaded on 04/01/2008 for the course INFO 2300 taught by Professor Williamson during the Spring '08 term at Cornell.

Ask a homework question - tutors are online