3. Columns, Tables and Rows

What We Will Cover Announcements Please silence all cell phones and pagers Please sit at a computer station and start your computer When class is over, please shut down your computer Elucidations 3.1: Data (Column) Types 3.1.1: About Data Types 3.1.2: Integer Types 3.1.3: Floating-Point Types 3.1.4: String Types 3.1.5: Date and Time Types 3.1.6: Choosing Data Types Exercise 3.1 3.2: Defining and Changing Tables 3.2.1: Valid Identifiers in MySQL 3.2.2: Creating Tables 3.2.3: Altering Tables 3.2.4: Renaming Tables 3.2.5: Dropping Tables 3.2.6: Summary Exercise 3.2 3.3: Inserting, Updating and Deleting Data 3.3.1: Inserting Data 3.3.2: Updating Data 3.3.3: Deleting Data 3.3.4: Summary Exercise 3.3 Wrap Up Due Next: A3: Project Proposal (3/1/06) Exercise 3 and Quiz 3 (3/1/06) Elucidations Homework Questions? A2: SQL Queries (2/22/06) Exercise 2 and Quiz 2 (2/22/06) Viewing WebCT Assignment Results WebCT Quiz Questions? WebCT Questions from last class? Assignment-Query-Review Exercise Use the next 10 minutes to complete the following. We will divide into four groups Goups 1 and 3 will agree on one solution to query 3 Goups 2 and 4 will agree on one solution to query 4 Select one person from your group as the spokesperson to present the combined algorithm. ^ top 3.1: Data (Column) Types Objectives At the end of the lesson the student will be able to: Use the MySQL numeric, string and temporal data types Choose appropriate column types for tables ^ top 3.1.1: About Data Types Recall that relational databases store their data in tables Each table has one or more columns and any number of rows ProductID ProductName ProductDescription Price InStock  1  Canvas  Good canvas for quality paint  22.50  30  2  Brush, Big  Big brush for large areas  4.75  47  3  Brush, Small  Small brush of fine material  3.75  34 When designing a table, you must choose an appropriate data type This is important because databases store data in different ways depending on the data type Some types are processed more efficiently than others Some types require more storage space than others You must choose a type appropriate for your needs ^ top 3.1.2: Integer Types For numbers with no fractional part (whole numbers) use an integer type MySQL supports all ANSI/ISO SQL92 numeric types MySQL has additional integral types: TINYINT, MEDIUMINT, and BIGINT All integer types have an optional (non-standard) attribute UNSIGNED Notes on the Integer Type Reference In following descriptions, M indicates the maximum display size Maximum legal display size is 255 characters If M is not given, the column defaults to the longest value for each type Square brackets ('[' and ']') indicate parts of type specifiers that are optional. ZEROFILL causes default padding of spaces to be replaced with zeroes Specifying ZEROFILL for a column will automatically add the UNSIGNED attribute Integer Type Reference TINYINT[(M)] [UNSIGNED] [ZEROFILL] A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255. BIT or BOOL Synonyms for TINYINT(1). SMALLINT[(M)] [UNSIGNED] [ZEROFILL] A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535. MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] * A medium-size integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215. INT[(M)] [UNSIGNED] [ZEROFILL] A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295. INTEGER[(M)] [UNSIGNED] [ZEROFILL] A synonym for INT. BIGINT[(M)] [UNSIGNED] [ZEROFILL] * A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615. * = Non-standard SQL data types. For Example Open phpMyAdmin Select the test database Select the SQL tab Copy the following code into the text area and press the Go button DROP TABLE IF EXISTS my_ints; CREATE TABLE my_ints ( itiny TINYINT, itiny_u TINYINT UNSIGNED, ibit BIT, ismall SMALLINT, ismal_u SMALLINT UNSIGNED, imedium MEDIUMINT, imedium_u MEDIUMINT UNSIGNED, int_default INT, int_M5 INT(5) NOT NULL, int_zerofill INT ZEROFILL, ibig BIGINT, ibig_u BIGINT UNSIGNED ); Now you can select the my_ints table and work with it Note the descriptions of the types You can change the types by selecting the (Change) icon under the Action columns Further Information Data Types Data Type Storage Requirements Numeric Types ^ top 3.1.3: Floating-Point Types Use floating-point types when you need decimal points in your numbers FLOAT or DOUBLE efficiently represents approximate numeric values Note: for maximum portability, use FLOAT or DOUBLE PRECISION with no specification of precision or number of decimal points Use NUMERIC or DECIMAL types when you need to preserve exact precision For instance: money Notes on the Floating Point Type Reference In following descriptions, M indicates the maximum display size Maximum legal display size is 255 If M not given, will default to longest value for each type Square brackets ('[' and ']') indicate parts of type specifiers that are optional. ZEROFILL will cause default padding of spaces to be replaced with zeroes Specifying ZEROFILL for a column will automatically add the UNSIGNED attribute Floating-Point Type Reference FLOAT(precision) [ZEROFILL] Floating-point number. Precision can be <=24 for a single-precision floating-point number and between 25 and 53 for a double-precision floating-point number. These types are like FLOAT and DOUBLE types described below. FLOAT(X) has the same range as the corresponding FLOAT and DOUBLE types, but the display size and number of decimals is undefined. FLOAT[(M,D)] [ZEROFILL] Single-precision floating-point number. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. M is the display width and D is number of decimal places. FLOAT without an argument or with an argument of <= 24 stands for a single-precision floating-point number. DOUBLE[(M,D)] [ZEROFILL] Double-precision floating-point number. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. M is the display width and D is the number of decimal places. DOUBLE without an argument or FLOAT(X) where 25 <= X <= 53 stands for a double-precision floating-point number. DOUBLE PRECISION[(M,D)] [ZEROFILL] REAL[(M,D)] [ZEROFILL] Synonyms for DOUBLE. DECIMAL[(M[,D])] [ZEROFILL] DEC[(M[,D])] [ZEROFILL] Unpacked floating-point number. Behaves like a CHAR column: ''unpacked'' means the number is stored as a string, using one character for each digit of the value. The decimal point and, for negative numbers, the '-' sign, are not counted in M (but space for these are reserved). If D is 0, values will have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. D defaults to 0 and M defaults to 10. NUMERIC(M,D) [ZEROFILL] Synonym for DECIMAL. For Example Open phpMyAdmin Select the test database Select the SQL tab Copy the following code into the text area and press the Go button DROP TABLE IF EXISTS my_fps; CREATE TABLE my_fps ( fp_float FLOAT(10,4), fp_float_u FLOAT UNSIGNED, fp_double DOUBLE(5,2), fp_dblprec DOUBLE PRECISION(4,2), fp_real REAL(4,1), fp_decimal DECIMAL(6,3), fp_numeric NUMERIC(10,2) UNSIGNED ); Now you can select the my_fps table and work with it Note the descriptions of the types You can change the types by selecting the (Change) icon under the Action columns Further Information Data Types Data Type Storage Requirements Numeric Types ^ top 3.1.4: String Types Use string types when you need to store non-numeric data CHAR and VARCHAR types efficiently store strings of up to 255 characters TEXT and BLOB types can store longer strings but use a few extra bytes BLOB is a Binary Large OBject that can hold a variable amount of data TEXT type is a case-insensitive BLOB ENUM is a string object whose value is chosen from a list The list is stored as part of the column definition Can have up to 65535 values Can only store a single item from the list Only requires 1-2 bytes of storage per row SET is also a string object whose value is chosen from a list, like an ENUM Can have up to 64 values on the list Can store any combination of items on the list Only requires 1-8 bytes of storage per row Neither ENUM nor SET are standard SQL types How would one implement an ENUM or SET type in another database? CHAR vs. VARCHAR CHAR and VARCHAR are similar but differ in how they are stored Length of a CHAR column is fixed When stored, they are right-padded with spaces to the specified length When retrieved, trailing spaces are removed Length of a VARCHAR column is variable Stored using only as many characters as are needed Values are not padded Trailing spaces are removed when values are stored VARCHAR columns tend to take up less space Notes on the String Type Reference Generally, values assigned exceeding maximum lengths are truncated Each BLOB or TEXT value is represented internally by separately allocated object In following descriptions, M indicates the maximum display size Maximum legal display size is 255 If M not given, will default to longest value for each type Square brackets ('[' and ']') indicate parts of type specifiers that are optional. String Type Reference [NATIONAL] CHAR(M) [BINARY] Fixed-length string that is always right-padded with spaces to the specified length when stored. The range of M is 1 to 255 characters. Trailing spaces are removed when the value is retrieved. CHAR values are sorted and compared in case-insensitive fashion according to the default character set unless the BINARY keyword is given. NATIONAL CHAR (or NCHAR) is the ANSI SQL way to define that a CHAR column that should use the default CHARACTER set. This is the default in MySQL. CHAR is a shorthand for CHARACTER. MySQL allows you to create a column of type CHAR(0). This is mainly useful when you have to be compliant with some old applications that depend on the existence of a column but that do not actually use the value. This is also quite nice when you need a column that can take only 2 values: A CHAR(0), that is not defined as NOT NULL, will only occupy one bit and can only take 2 values: NULL or "". CHAR This is a synonym for CHAR(1). [NATIONAL] VARCHAR(M) [BINARY] A variable-length string. VARCHAR is a shorthand for CHARACTER VARYING. The range of M is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. Note: Trailing spaces are removed when the value is stored (this differs from the ANSI SQL specification). TINYBLOB TINYTEXT BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters. BLOB TEXT BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters. MEDIUMBLOB MEDIUMTEXT BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1) characters. LONGBLOB LONGTEXT BLOB or TEXT column with a maximum length of 4294967295 (2^32 - 1) characters. Note that because the server/client protocol and MyISAM tables has currently a limit of 16M per communication packet / table row, you can't yet use this the whole range of this type. ENUM('value1','value2',...,valueN) Enumeration: string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., 'valueN', NULL or the special "" error value. An ENUM can have a maximum of 65535 distinct values. SET('value1','value2',...) Set: string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET can have a maximum of 64 members. For Example Open phpMyAdmin Select the test database Select the SQL tab Copy the following code into the text area and press the Go button DROP TABLE IF EXISTS my_strs; CREATE TABLE my_strs ( s_char_2 CHAR(2), s_char_10 CHAR(10), s_varchar_10 VARCHAR(10), s_Enum ENUM('Yes','No') default 'No', s_Set SET('One','Two') default 'One', s_Enum2 ENUM('ASP','C','C++','Java','PHP') default 'PHP', s_Set2 SET('ASP','C','C++','Java','PHP') default 'PHP', s_strtinytext TINYTEXT, s_blob BLOB, s_text TEXT, s_mediumblob MEDIUMBLOB, s_longtext LONGTEXT ); Now you can select the my_strs table and work with it Note the descriptions of the types Note how s_char_2 becomes a VARCHAR even though it is specified as CHAR You can change the types by selecting the (Change) icon under the Action columns Further Information Data Types Data Type Storage Requirements String Types ^ top 3.1.5: Date and Time Types Use date and time types to store temporal data If you only need to store the year, use the YEAR type If only a date or time value is needed, use DATE and TIME respectively TIMESTAMP stores date/time values to the nearest second the most efficiently However, can only store values between 1970 and 2037 DATETIME stores values to the nearest second from the year 1000 to 9999 An application (such as your PHP code) must handle date checking MySQL only checks month is in range 0-12, day is in range of 0-31 Note that you can store a zero value when you do not know an exact date 2005-00-00 2005-01-00 MySQL Interpretation of Date and Time Input MySQL tries to interpret values in several formats Always expects the year part of date values to be leftmost MySQL interprets dates with ambiguous year values using the following rules: Year values in the range 00-69 are converted to 2000-2069 Year values in the range 70-99 are converted to 1970-1999 Can specify DATETIME, DATE, and TIMESTAMP values in variety of ways: 'YYYY-MM-DD HH:MM:SS' e.g.: '2002-02-25 18:30:45' 'YY-MM-DD HH:MM:SS' e.g.: '02-02-25 18:30:45' 'YYYY-MM-DD' e.g.: '2002-02-25' 'YY-MM-DD' e.g.: '02-02-25' 'YYYYMMDDHHMMSS' e.g.: '20020225183045' 'YYMMDDHHMMSS' e.g.: '020225183045' 'YYYYMMDD' e.g.: '20020225' 'YYMMDD' e.g.: '020225' 'HHH:MM:SS' e.g. '127:27:30 YYYYMMDDHHMMSS e.g.: 20020225183045 YYMMDDHHMMSS e.g.: 020225183045 YYYYMMDD e.g.: 20020225 YYMMDD e.g.: 020225 Illegal DATETIME, DATE, or TIMESTAMP values converted to appropriate zero values: '0000-00-00 00:00:00' '0000-00-00' 00000000000000 If using delimited strings, not necessary to specify two digits for month or day values less than 10 Any punctuation can be used as date or time delimiters Notes on the Date Type Reference In following descriptions, M indicates the maximum display size Maximum legal display size is 255 If M not given, will default to longest value for each type Square brackets ('[' and ']') indicate parts of type specifiers that are optional. Date and Time Type Reference DATE Date. Supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers. DATETIME Date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using either strings or numbers. TIMESTAMP[(M)] Timestamp. Range is '1970-01-01 00:00:00' to sometime in the year 2037 (Y2.037K problem?). MySQL displays TIMESTAMP values in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or missing), 12, 8, or 6, but allows you to assign values to TIMESTAMP columns using either strings or numbers. A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation because it is automatically set to the date and time of the most recent operation if you don't give it a value yourself. You can also set it to the current date and time by assigning it a NULL value. A TIMESTAMP is always stored in 4 bytes. The M argument only affects how the TIMESTAMP column is displayed. Note that TIMESTAMP(X) columns where X is 8 or 14 are reported to be numbers while other TIMESTAMP(X) columns are reported to be strings. This is just to ensure that one can reliably dump and restore the table with these types! TIME Time. Range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format, but allows you to assign values to TIME columns using either strings or numbers. YEAR[(2|4)] Year in 2- or 4-digit format (default is 4-digit). The allowable values are 0000 and 1901 to 2155 in the 4-digit year format, and 1970-2069 if you use the 2-digit format (70-69). MySQL displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers. For Example Open phpMyAdmin Select the test database Select the SQL tab Copy the following code into the text area and press the Go button DROP TABLE IF EXISTS my_dts; CREATE TABLE my_dts ( dt_Date DATE, dt_Time TIME, dt_DT DATETIME, dt_TS TIMESTAMP(12), dt_Year YEAR(4) ); Now you can select the my_dts table and work with it Note the descriptions of the types You can change the types by selecting the (Change) icon under the Action columns Further Information Data Types Data Type Storage Requirements Date and Time Types ^ top 3.1.6: Choosing Data Types When choosing a column type, you need to consider several factors; What kind of values will the column hold? Can represent everything as string Other types can be more efficient (less storage, processing, etc.) For monetary values, use the DECIMAL type Stored as a string, so no loss of accuracy occurs Restrict the size of any field to the smallest possible value Based on what the largest possible input can be Also set the maximum length for any char or varchar field What are the performance and efficiency issues? Some types processed more efficiently than others Numeric operations usually faster than string operations Smaller types processed more quickly than longer types -- less disk I/O Performance better for fixed-length types than variable-length types More information: Choosing the Right Type for a Column ^ top Exercise 3.1 In this exercise we exame the "best" column type for storing the various types of data. Specifications Start a text file named exercise3.txt. Prepare the exercise header as described in the HowTo on submitting exercises Label this exercise: Exercise 3.1 Determine the best column type for storing the following data: The numbers in the state lottery Student grade-point averages Bank account transactions The names for a telephone book The number of days in a month A record of when a news story was released Record your answers in the exercise3.txt file. ^ top 3.2: Defining and Changing Tables Objectives At the end of the lesson the student will be able to: CREATE tables in a database ALTER existing tables in a database RENAME tables in a database DROP tables from a database ^ top 3.2.1: Valid Identifiers in MySQL Database, table, index, column, and alias names are MySQL identifiers In general, MySQL identifiers can be 64 characters in length or less Usually should be shorter Identifiers cannot contain ASCII 0, ASCII 255, or the quoting character (backtick) Database and table names cannot contain '/', '\', or '.' If you use a reserved word as an identifier, you must quote it CREATE TABLE `group` You must also quote identifiers that contain special characters Special characters are any character that is not alphanumeric, the underscore '_' or the dollar sign '$' The best practice is to avoid using reserved words or special characters Further Information Database, Table, Index, Column, and Alias Names ^ top 3.2.2: Creating Tables Creating a table is the process of making new tables in a particular database Tables are used to store all raw data Before defining a table, need to decide on the following: How many columns you need What you will name each column The data type for each column Which field is the PRIMARY KEY For the examples in this section, we will use a database named customer Primary Keys and Auto-numbering Recall that most tables need a primary key that provides a unique identifier for a row One common method for providing a unique primary key is AUTO_INCREMENT AUTO_INCREMENT is a function that provides a unique number when a row is added Using phpMyAdmin Select the database to which you want to add a table Find the text fields labeled: Create new table... Enter a table name and the number of fields (columns) you need Press the Go button For each field, enter a name and data type, using appropriate length/values and attributes Decide if you want to allow NULL data or if a default value is appropriate Decide which field is the primary key, and check the (Primary) radio button If the Primary key is auto-incremented, select this option under Extra Leave the rest of the index fields unused at this time -- cover later Using SQL Statements You can create a table using a SQL statement For example: DROP TABLE IF EXISTS address; CREATE TABLE address ( AddressID INT AUTO_INCREMENT PRIMARY KEY, Address1 VARCHAR(255) NOT NULL, Address2 VARCHAR(255), Address3 VARCHAR(255), City VARCHAR(50), State CHAR(10) NOT NULL, ZipCode VARCHAR(9), CustomerID INT(11) ); Further Information CREATE TABLE Syntax ^ top 3.2.3: Altering Tables Altering a table changes its structure You can change the information you entered when creating the table Must be careful when altering a table that has any data stored Good practice: create a backup of the table before making any changes Using phpMyAdmin Select the database in which you want to alter a table Select the table that you want to alter Select the (Change) icon under the Action columns Make any changes you want and press the Save button Using SQL You can alter a table using a SQL statement For example, to change the name of a column: ALTER TABLE address CHANGE Address3 Address4 VARCHAR(255); You can reliably alter only one field at a time Further Information ALTER TABLE Syntax ^ top 3.2.4: Renaming Tables You can change the name of a table by renaming it Using phpMyAdmin Select the database in which you want to rename a table Select the table that you want to rename Select the Operations tab In the Rename table to text field, enter the new name and press its Go button Using SQL Can rename a table using a SQL statement For example: RENAME TABLE address TO addresses; Further Information RENAME TABLE Syntax ^ top 3.2.5: Dropping Tables Dropping a table removes it from the database The action cannot be reversed Using phpMyAdmin Select the database in which you want to drop a table Select the table that you want to drop Select the Drop tab Using SQL You can drop a table using a SQL statement For example: DROP TABLE addresses; Further Information DROP TABLE Syntax ^ top 3.2.6: Summary Database, table, index, column, and alias names are all MySQL identifiers You must create valid identifiers when creating these names You can CREATE, ALTER, RENAME and DROP tables Use either phpMyAdmin or SQL statements phpMyAdmin is useful for administrative roles When programming with PHP, you will use SQL statements When creating or altering tables, must understand the table structure Table structure includes: How many columns Name each column Data type for each column Which field is the PRIMARY KEY This structure information is stored in the database ^ top Exercise 3.2 In this exercise we practice creating tables for a database. Specifications In your exercise3.txt file, label this exercise: Exercise 3.2 Create a New Database Open a Web browser and enter localhost/phpmyadmin in the address field. You should see phpMyAdmin running in the browser window. Create and select a database named "customer". Add a customer Table using phpMyAdmin Find the text fields labeled: Create new table... Enter customer for the table name. Enter 3 for the number of fields Press the Go button. A new form should appear at this point. Enter the following for the CustomerID column: Field: CustomerID Data Type: INT Null: not null Extra: auto_increment Primary: selected () Enter the following for the Name column: Field: Name Type: VARCHAR Length/Values: 100 Null: null Enter the following for the E-mail column: Field: E-mail Type: VARCHAR Length/Values: 255 Null: null Save the table by pressing the Save button. A new form should appear at this point with the message: Table Customer has been created. Copy the SQL-query statement that phpMyAdmin shows you into your exercise3.txt file. Create an address Table Using SQL Scroll down the page to the SQL text area. The text area is labeled: Run SQL query/queries on database customer. Copy the following SQL statements into the text area and press the Go button. CREATE TABLE address ( AddressID INT AUTO_INCREMENT PRIMARY KEY, Address1 VARCHAR(255) NOT NULL, Address2 VARCHAR(255), Address3 VARCHAR(255), City VARCHAR(50), State CHAR(2) NOT NULL, ZipCode VARCHAR(9), CustomerID INT(11) ); Copy the SQL-query statement that phpMyAdmin shows you into your exercise3.txt file. ^ top 3.3: Inserting, Updating and Deleting Data Objectives At the end of the lesson the student will be able to: Insert data into a database table Update existing data in a database table Delete data from a database table ^ top 3.3.1: Inserting Data After you create a table, you need to insert data into the table Inserting is the operation of adding a row of data to a table Using phpMyAdmin Select the database and table For instance: customer database and customer table Select the Insert tab Fill in the Value fields with the data For instance: Name: Fred Flintrock E-mail: fred@rockoo.com Do not enter data into an AUTO_INCREMENT field We will cover functions later in the course Press the Go button to save the data Browse the table and notice the row we entered had a CustomerID assigned automatically This is because we assigned the auto-increment option when we created the table Using SQL The INSERT statement adds records into database You use the INTO clause to specify name of table and names of fields The VALUES clause specifies field values of each record you are adding The following example assumes the address table exists: INSERT INTO address(Address1, State, CustomerID) VALUES ('123 Rocky Road', 'Redrock', 1) More Information INSERT Syntax ^ top 3.3.2: Updating Data Updating is the operation of changing existing data Using phpMyAdmin Select the database and table. Select the Browse tab. Select the Edit control for the row you want to change. Change the data in the fields to what you want. Choose Save or Insert as a new row. Press the Go button to make the changes. Using SQL UPDATE statement used to change records in database Uses SET clause to specify new values Uses WHERE clause to uniquely identify records to change UPDATE address SET State = 'CA' WHERE State = 'Redrock' More Information UPDATE Syntax ^ top 3.3.3: Deleting Data Deleting is the operation of removing data from a table Using phpMyAdmin Select the database and table. Select the Browse tab. Select the Delete control for the row you want to remove. Using SQL DELETE statement used to remove records from database Uses FROM clause to specify name of table Uses WHERE clause to uniquely identify records to delete DELETE FROM address WHERE State = 'CA' More Information DELETE Syntax ^ top 3.3.4: Summary You can INSERT, UPDATE and DELETE rows in a table You can use either phpMyAdmin or SQL statements phpMyAdmin is useful for administrative roles When programming with PHP, you will use SQL statements Note that phpMyAdmin shows you the SQL statements it uses for its operations You can copy these statements into your PHP scripts ^ top Exercise 3.3 Specification In this exercise we add data to the tables we created in the last exercise. In your exercise3.txt file, label this exercise: Exercise 3.3 Add Data to a Table To add data to the tables, you should add them in the correct order. Make sure you add customer data before address data. Open a Web browser and enter localhost/phpmyadmin in the address field. You should see phpMyAdmin running in the browser window. Select the customer table. Select the Insert tab Fill in the Value fields with some data. Since the CustomerID is an AUTO_INCREMENT field, do NOT add data to that field. Press the Go button to save the data Copy the SQL-query statement that phpMyAdmin shows you into your exercise3.txt file. Add as many additional entries as you like. Add two separate addresses for the first customer you created. You relate an address to a customer by filling in the customer's CustomerID in the address table's CustomerID field. Copy the SQL-query statements that phpMyAdmin shows you into your exercise3.txt file. ^ top Wrap Up Reminders Due Next: A3: Project Proposal (3/1/06) Exercise 3 and Quiz 3 (3/1/06) When class is over, please shut down your computer You may complete unfinished exercises at the end of the class or at any time before the next class. Instructions on submitting exercises are available from the HowTo's page. Review Questions Why is choosing a data type important? How can you tell the difference between an integer type and a floating-point time? What is the advantage of a TIMESTAMP data type over a DATETIME data type? What is the advantage of a DATETIME data type over a TIMESTAMP data type? How is a CREATE TABLE statement coded? How is a DROP TABLE statement coded? What is the SQL keyword used to change the structure of a table? How is an INSERT statement coded? How is an UPDATE statement coded? How is a DELETE statement coded? ^ top Home | WebCT | Announcements | Course info | Expectations | Schedule Project | Help | FAQ's | HowTo's | Links Last Updated: February 22 2006 @16:50:58