3. Columns, Tables and Rows

What We Will Cover Announcements Please silence all cell phones and pagers When class is over, please shut down your computer if it is on Elucidations 3.1: Creating Database Tables 3.1.1: About Database Tables 3.1.2: Designing a Table 3.1.2: Creating a Table Exercise 3.1 3.1.4: Integer Types 3.1.5: Floating-Point Types 3.1.6: String Types 3.1.7: Date and Time Types 3.1.8: Choosing Data Types 3.1.9: Summary 3.2: Inserting, Updating and Deleting Data 3.2.1: About Data Manipulation 3.2.2: Inserting Data 3.2.3: Updating Data 3.2.4: Deleting Data 3.2.5: Summary Exercise 3.2 3.3: Other Table Operations 3.3.1: Changing Tables 3.3.2: Altering Tables 3.3.3: Renaming Tables 3.3.4: Dropping Tables 3.3.5: Summary Exercise 3.3 Wrap Up Due Next: A3-Project Proposal (3/12/12) Quiz 3 and Discussion 3 (3/12/12) Elucidations Reminders Assigned reading and discussions Online Discussion Questions from last class? Homework Questions? A2-SQL Queries (3/5/12) Quiz 2 and Discussion 2 (3/5/12) What is the query for questions 4? What is the query for questions 5? Viewing Blackboard assignment results Quiz Questions? Login to Blackboard ^ top 3.1: Creating Database Tables Learner Outcomes At the end of the lesson the student will be able to: Create tables within a database Use the MySQL numeric, string and temporal data types Choose appropriate column types for tables ^ top 3.1.1: About Database Tables In lesson 2.1.4 we learned how to create and delete a database within MySQL In this lesson we learn how to create and manipulate tables Also, we will learn how to insert, update and delete data in a later section of this lesson Recall that relational databases store their data in tables Each table has one or more columns and any number of rows Below you can see columns and rows for a database table named products Example Database Table 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 ^ top 3.1.2: Designing a Table Before you create a table, you need to do some basic planning First you need to choose a name for your table Names (identifiers) in MySQL follow some basic rules (see: Schema Object Names) However, you will have an easier time with MySQL if you follow this modified set of the rules for names: Always choose names that are clear, meaningful and easy to type Only use letters, numbers and the underscore character (no spaces) Do not use a name that is the same as a reserved word (AKA keyword) Treat the name as if it were case sensitive Keep the length of the name to 64 characters or less Make the name unique within its realm The last rule means that you cannot have two tables with the same name Also, a table cannot have two columns with the same name You can, however, use the same column name in two different tables Choosing the Columns Continuing our planning we 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 Whether or not the primary key uses auto_increment Whether or not you want to allow insertions with no data (null) Whether or not you want a default value on a null entry Whether or not the values in a column must be unique In addition, there are other attributes for columns that you can specify depending on the data type Column Data Types Each column can have only one data type for all its entries When designing a table, you must choose an appropriate data type for each column 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 Indexes, Keys and AUTO_INCREMENT Important concepts in table design are indexes and keys An index is a technique that speeds retrieval of data A key column is used to associate one table with another There are two types of keys: primary keys and foreign keys Each table should have one primary key The primary key in one table is often linked to a foreign key in another table The primary key is automatically indexed and not null Also, every value in the primary key must be unique Keys are an important part of relational database design, which we will discuss in the next lesson To make sure that the primary key is unique, we can use the AUTO_INCREMENT feature in MySQL AUTO_INCREMENT tells MySQL to take the current highest number and add one to it for the next entry AUTO_INCREMENT works well with integer numbers, which is a common data type for primary keys Check Yourself Another word for the name of a table, column or other object is ________. Of the following column names, ________ is a good choice. Column 1 Select _a_very_long_variable_name_that_is_hard_to_read person True or false: each row in a table column has the same data type. To use AUTO_INCREMENT on a column, the column must ________. be a primary key have an integer data type not contain NULL data have all of these ^ top 3.1.3: Creating a Table After you finish planning your table, you create it in a database You can create a table using either forms in phpMyAdmin or by using SQL statement We will explore creating tables in the next exercise 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, Street VARCHAR(255) NOT NULL, City VARCHAR(50), State CHAR(10) NOT NULL, ZipCode VARCHAR(9), CustomerID INT(11) ) More Information CREATE TABLE Syntax ^ top Exercise 3.1 In this exercise we create tables for a database. Specifications Open the XAMPP Control Panel by double-clicking the icon on the desktop or by using the Apache Friends entry in the start menu. Start the Apache and MySQL modules, if they are not already running. Open a new Web browser tab or window and enter localhost/phpmyadmin in the address field. You should see phpMyAdmin running in the browser window. Create and select a database named: customer. For more information on creating a database, see lesson: 2.1.4: Creating, Selecting and Deleting Databases. Find the text fields labeled: Create new table on database customer. Then enter the following information for the two fields and press the Go button: Name: customer Number of fields: 5 You should see a new form appear. Note that the database and table can be the same name because they are included in different containers. The database is contained in the global database container and the table name is in the customer database container. For the first field enter the following information: Field: ID Type: INT Attributes: UNSIGNED Null: not null (unchecked) Index: PRIMARY AUTO_INCREMENT (A...I): (checked) Leave the rest of the fields blank or default Notice the INT data type. This is a numerical type with no fractional part, only whole numbers. You choose INT because it works well with AUTO_INCREMENT. For more information, see section: 3.1.4: Integer Types. For the second field enter the following information: Field: FirstName Type: VARCHAR Length/Values: 100 Null: not-null (unchecked) Leave the rest of the fields blank or default Notice the VARCHAR data type. This is a string type for storing non-numeric data. We limit the length to 100 because that seems large enough for a name. For more information, see section: 3.1.6: String Types. For the third field enter the following information: Field: LastName Type: VARCHAR Length/Values: 100 Null: not-null (unchecked) Leave the rest of the fields blank or default We use separate columns for first and last names to make retrieving names more flexible. For instance, we can look up customers based only on their last name rather than their full name. Enter the following for the E-mail column: Field: Email Type: VARCHAR Length/Values: 255 Null: null (checked) Leave the rest of the fields blank or default For this field we use the maximum length of a VARCHAR, which is 255 characters. In addition, we allow null values since not everyone has an email address. For the final field, enter the following information: Field: DateRegistered Type: TIMESTAMP Null: not-null (unchecked) Leave the rest of the fields blank or default Notice the TIMESTAMP data type. This is a date/time data type for storing date and time in the UNIX timestamp format. For more information, see section: 3.1.7: Date and Time Types. Save the table by pressing the Save button (NOT the Go button). You will see a message, "Table `customer`.`customer` has been created." Look over the report on the table provided by phpMyAdmin. Notice how MySQL quotes the names even though we did not enter the quote marks. Also, notice how MySQL used the server default for the Collation. Click the SQL tab to open the SQL page. You can enter SQL statement in the text area 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 ( ID INT AUTO_INCREMENT PRIMARY KEY, Street VARCHAR(255) NOT NULL, City VARCHAR(50), State CHAR(2) NOT NULL, ZipCode VARCHAR(9), CustomerID INT(11) ) This is an example of how to create a table using a SQL statement. Notice that the CustomerID field is a foreign key. Entries in this column must match the primary key in the customer table. Export the customer database and save it to the desktop using the name: create.sql. For more information see lesson: 2.1.5: Importing and Exporting Data. Submit the create.sql file to Blackboard as part of assignment 3. As time permits, be prepared to answer the Check Yourself questions in the section: 3.1.9: Summary. ^ top 3.1.4: 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. Example Table with Integer Types 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 More Information Data Types Data Type Storage Requirements Numeric Types Check Yourself Of the following literal numbers, ________ is an integer. 1 1.2 1.23 1.234 If you want to store with whole numbers precisely, use a(n) ________ data type. Of the following ________ is not a standard SQL data type. TINYINT SMALLINT MEDIUMINT BIGINT ^ top 3.1.5: 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, D) indicates the maximum number of digits you can store M is the maximum number of total digits D indicates the number of digits after the decimal point This is NOT standard SQL syntax Square brackets ('[' and ']') indicate parts of type specifiers that are optional. ZEROFILL will cause leading zeroes rather than spaces 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] Stores floating point values with exact precision up to 65 decimal places. For example, a number such as .0001 is treated as an exact value rather than as an approximation. M is the maximum number of digits (the precision) and has a range of 1 to 65. D is the number of digits to the right of the decimal point and has a range of 0 to 30. NUMERIC(M,D) [ZEROFILL] Synonym for DECIMAL. Example Table with Floating-Point Types 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 More Information Data Types Data Type Storage Requirements Numeric Types Precision Math Check Yourself The most accurate floating-point type is ________. DECIMAL DOUBLE FLOAT REAL When accuracy is less important, use the ________ type because it takes less storage space. DECIMAL DOUBLE FLOAT REAL True or false: NUMERIC is a synonym for DECIMAL. ^ top 3.1.6: String Types You 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 The 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. Example Table with String Types 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_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 More Information Data Types Data Type Storage Requirements String Types Check Yourself True or false: in SQL, "A" and 'A' the same. Of the following ________ stores string data in a fixed amount of space. BLOB CHAR TEXT VARCHAR True or false: CHAR and VARCHAR are always declared with the number of characters they can store. ^ top 3.1.7: Date and Time Types You use date and time types to store times and dates If you only need to store the year, use the YEAR type If you only need a date or time value, use DATE and TIME respectively TIMESTAMP stores date/time values to the nearest second the most efficiently However, you can only store values between 1970 and 2037 DATETIME stores values to the nearest second from the year 1000 to 9999 but takes more storage space 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 It 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 You 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 MySQL converts illegal DATETIME, DATE, or TIMESTAMP values to appropriate zero values: '0000-00-00 00:00:00' '0000-00-00' 00000000000000 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 you do not provide an M, MySQL 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. Storage space is 8 bytes. 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. TIMESTAMP[DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP] Timestamp. Range is '1970-01-01 00:00:00' to sometime in the year 2038 (Y2.038K problem?). A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation because you can set it to automatically set the column to the date and time of the most recent operation using the following options: Nothing: defaults to auto-initialization and auto-update Auto-initialization and auto-update: col_name TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP Auto-initialization only: col_name TIMESTAMP DEFAULT CURRENT_TIMESTAMP Auto-update only: col_name TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP Neither: col_name TIMESTAMP DEFAULT 0 A TIMESTAMP is always stored in 4 bytes. 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. Example Table with Date and Time Types 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 DEFAULT CURRENT_TIMESTAMP, 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 More Information Data Types Data Type Storage Requirements Date and Time Types Automatic Initialization and Updating for TIMESTAMP Check Yourself If you only need to store the year, use the ________ type. True or false: by default, a TIMESTAMP stores the current date and time whenever a table row is updated. True or false: a DATETIME type uses less space than a TIMESTAMP. ^ top 3.1.8: Choosing Data Types When choosing a column type, you need to consider several factors; What kind of values will the column hold? You can represent everything as string Other types can be more efficient (less storage, faster processing, etc.) For monetary values, use the DECIMAL type Stored and operated on with exact precision, so no rounding errors occur 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 Check Yourself True or false: smaller data types tend to process faster than larger data types. True or false: money should be stored in type FLOAT because accuracy does not matter. True or false: string types generally process numbers faster than numeric types . What is the best column type for storing each of the following data? Student grade-point averages Bank account deposits and withdrawals The numbers in the state lottery The names for a telephone book The number of days in a month A record of when a news story was released ^ top 3.1.9: Summary In this section, we looked at the data types you can specify for columns Choosing a correct data type is important to ensure you can perform the operations you want efficiently MySQL has four main column types: Integer: numbers without a decimal point Floating-point: numbers with a decimal point String: non-numeric data Temporal: data and time data We looked at the specific types in each of these categories and showed examples of them In addition, we discussed how to choose the "best" data type Check Yourself Why is choosing a data type important? How can you tell the difference between an integer type and a floating-point type? What does the length of a VARCHAR specify? How do you specify the length of a VARCHAR in a SQL statement? What is the difference between a CHAR and a VARCHAR? 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? What is the best column type for storing each of the following data? Student grade-point averages Bank account deposits and withdrawals The numbers in the state lottery The names for a telephone book The number of days in a month A record of when a news story was released For guidance see section: 3.1.8: Choosing Data Types. What is the advantage of using the AUTO_INCREMENT option for primary key fields? ^ top 3.2: Inserting, Updating and Deleting Data Learner Outcomes 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.2.1: About Data Manipulation Now that we know how to create a table, we are ready to add data to the table Also, one we add data to the table we often want to change or delete the data The SQL language has a syntax to update, insert, and delete rows The INSERT statement adds new rows into a table The UPDATE statement modifies data already existing in a table The DELETE statement removes rows from a table Together with the SELECT statement, these statements form the Data Manipulation Language (DML) part of SQL In this section we will explore the use of the INSERT, UPDATE and DELETE statements ^ top 3.2.2: Inserting Data 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: FirstName: Fred LastName: Flintrock E-mail: fred@yabadoo.com Do NOT enter data into an AUTO_INCREMENT field 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(Street, City, CustomerID) VALUES ('123 Rocky Road', 'Redrock', 1) Note how the fields getting values are specified inside the parenthesis after the table name Then the values for each field are listed in the parenthesis after the VALUES keyword When inserting data into all fields of a table, then the list of field names is optional For instance, to insert another entry into the address table: INSERT INTO address VALUES (NULL, '234 Gravel Lane', 'Rocky Beach', 'CA', 95003, 1) Recall that the ID field of the address table is set to AUTO_INCREMENT By entering a NULL value the auto-increment function will assign the correct value Note that there is a third form of the INSERT statement that uses a SELECT statement to select values from other tables We will discuss this option later in the course More Information INSERT Syntax Check Yourself The correct SQL for adding Flintrock as a last name to the customer table is ________. INSERT INTO customer (LastName) ('Flintrock'); INSERT INTO customer (LastName) VALUES ('Flintrock'); INSERT ('Flintrock') INTO (LastName) FROM customer; INSERT INTO customer ('Flintrock') INTO (LastName); True or false: when inserting only some of the data for a row into a table, you must specify which column each data goes into. True or false: you can insert data into a table without directly specifying the column of the data. ^ top 3.2.3: Updating Data Updating is the operation of changing existing data Using phpMyAdmin Select the database and table. Select the Browse tab. Select the Edit icon 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 from the drop-down list. Press the Go button to make the changes. Using SQL UPDATE statement change a specific field in a row Uses SET clause to specify new values Uses WHERE clause to uniquely identify records to change For example UPDATE address SET State = 'CA' WHERE City = 'Redrock' Note that the WHERE clause is optional However, without the WHERE clause all the rows of the table are updated More Information UPDATE Syntax Check Yourself To change Rubble into Flintrock in the LastName column of the customer table use ________. SAVE customer SET LastName = 'Flintrock' INTO LastName = 'Rubble'; SAVE customer SET LastName = 'Flintrock' WHERE LastName = 'Rubble'; UPDATE customer SET LastName = 'Flintrock' WHERE LastName = 'Rubble'; UPDATE customer CHANGE LastName = 'Rubble' INTO LastName = 'Flintrock'; True or false: in an UPDATE statement, the WHERE clause is optional. Enter the SQL code to set the ZipCode to 95003 everywhere in the "addresses" table. answer ^ top 3.2.4: 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 the FROM clause to specify name of table Uses the WHERE clause to uniquely identify records to delete DELETE FROM address WHERE City = 'Rocky Beach' Note that the WHERE clause is optional However, without the WHERE clause all the rows of the table are deleted More Information DELETE Syntax Check Yourself True or false: if you make a mistake deleting rows from a database, you can always get them back with the UNDO command. Enter a SQL statement to remove all records of the "customers" table where the "LastName" column is "Flintrock". answer True or false: without a where clause, all rows are deleted from a table. ^ top 3.2.5: Summary The SQL language has a syntax to update, insert, and delete rows The INSERT statement adds new rows into a table The UPDATE statement modifies data already existing in a table The DELETE statement removes rows from a table Together with the SELECT statement, these statements form the Data Manipulation Language (DML) part of SQL You can use either phpMyAdmin or SQL statements to INSERT, UPDATE and DELETE rows in a table phpMyAdmin is more useful as an administrative tool When programming with PHP, you will use SQL statements Note that phpMyAdmin shows you the SQL statements it uses for its operations Thus you can use phpMyAdmin to construct your SQL statements Once constructed, you then copy these statements into your PHP scripts Check Yourself The following questions use an address table with the following definition: CREATE TABLE address ( AddressID INT AUTO_INCREMENT PRIMARY KEY, Street VARCHAR(255) NOT NULL, City VARCHAR(50), State CHAR(2) NOT NULL, ZipCode VARCHAR(9), CustomerID INT(11) ); What is a SQL statement for inserting the following data into the address table? Street: 6500 Soquel Dr. City: Aptos State: California Zip code: 95003 Customer Id number: 9 What is a SQL statement for changing a customer ID number from 9 to 10? What is a SQL statement for deleting all rows where the City is Aptos? ^ top Exercise 3.2 In this exercise we add data to the tables we created in the last exercise. In addition we will update and delete data as well. Specifications Make sure that Apache and MySQL are running using the XAMPP control panel. Then open a new Web browser tab or window and enter localhost/phpmyadmin in the address field. You should see phpMyAdmin running in the browser window. In phpMyAdmin, select the customer database you created in Exercise 3.1. You should see both the address and customer tables listed on the left side. Since the address table has a foreign key named CustomerID linking to the customer table's primary key field named ID, your best strategy is to enter the customer information first. Select the customer table by clicking on the customer link in the left menu. In the right frame you will see a list of tabs at the top of the page. Select the Insert tab. In the right frame you will see two forms for entering rows of data into the table. Fill in the fields in the Value column with some data of the appropriate type. However, do NOT add a value to the ID field since it is an AUTO_INCREMENT field and will choose its own value. Note that the DateRegistered field has the value CURRENT_TIMESTAMP. You can either leave the value as is or choose a different date and time. If you chose a different value, it must be entered in an acceptable date and time format. Press the Go button to save the data. Add as many additional entries as you like. Select the Browse tab and look at the entries you made. You should see that the ID for your first customer entry is the number 1. If not, try again or ask a classmate or the instructor for help. For more information on inserting data see section 3.2.2: Inserting Data. For the address table, you will insert data using SQL. Select the SQL tab and enter the following SQL statement into the text area and then press the Go button. INSERT INTO address(Street, City, CustomerID) VALUES ('123 Rocky Road', 'Redrock', 1) Note how the fields getting values are specified inside the parenthesis after the table name. Then the values for each field are listed in the parenthesis after the VALUES keyword. For more information see section 3.2.2: Inserting Data. Also note the relationship between the customer table's ID field and the address table's CustomerID field. This makes it possible for a customer to have more than one address. If you want to enter values for every field in a row, you can use the INSERT statement without specifying the fields. Add the following row to the address table: INSERT INTO address VALUES (NULL, '234 Gravel Lane', 'Rocky Beach', 'CA', 95003, 1) Recall that the ID field of the address table is set to AUTO_INCREMENT. By entering a NULL value in the ID field, you let the auto-increment function assign the correct value. Next we will update fields of the address table using the SQL statement UPDATE. To update data in the address table for the first entry: UPDATE address SET State = 'CA' WHERE City = 'Redrock' Browse the address table to see the changes made. The SET clause specifies the new value and you use a WHERE clause to specify the conditions for making the change. For more information see section 3.2.3: Updating Data. Now we will remove a row of data from a table. Enter the following SQL command: DELETE FROM address WHERE City = 'Rocky Beach' Browse the address table to see the changes made. The WHERE clause specifies the conditions for deleting a row. For more information see section 3.2.4: Deleting Data. Export the customer database and save it to the desktop using the name: dml.sql. For more information see lesson: 2.1.5: Importing and Exporting Data. Submit the dml.sql file to Blackboard as part of assignment 3. As time permits, be prepared to answer the Check Yourself questions in the section: 3.2.5: Summary. ^ top 3.3: Other Table Operations Learner Outcomes At the end of the lesson the student will be able to: ALTER existing tables in a database RENAME tables in a database DROP tables from a database ^ top 3.3.1: Changing Tables Sometimes you need to change your database tables after your create them The SQL language also includes a syntax to alter and delete tables The ALTER TABLE statement alters (changes) a database table The RENAME TABLE statement changes the name of a table The DROP statement is used to delete row a table Together with CREATE TABLE, these statements are known as the Data Definition Language (DDL) part of SQL We will explore these statements in this section Check Yourself Of the following ________ is not part of the data definition language of SQL. ALTER CREATE DROP SELECT The SQL command to change the structure of a database table is ________. True or false: to remove a table use the SQL command DELETE. ^ top 3.3.2: Altering Tables Altering a table changes its structure You can change the information you entered when creating the table You must take care when altering a table that has any data stored You may make a change that destroys some or all of the data 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 customer CHANGE COLUMN LastName LName VARCHAR(100) You can reliably alter only one field at a time in standard SQL Note how you must specify the entire definition of the column even if you are changing only part of the definition, such as the name Another use of ALTER TABLE is to add columns, such as: ALTER TABLE customer ADD COLUMN Gender CHAR(1) Still another use of ALTER TABLE is to delete columns, such as: ALTER TABLE customer DROP Gender The following table list the major clauses of the ALTER TABLE command For other clauses, see: ALTER TABLE Syntax While listed for completeness, we will cover indexes later in the course ALTER TABLE Clauses Clause Usage Explanation ADD COLUMN ALTER TABLE t ADD COLUMN c TYPE Adds a new column to the end of the table CHANGE COLUMN ALTER TABLE t CHANGE COLUMN c d TYPE Changes the column name (from c to d), data type and other properties DROP COLUMN ALTER TABLE t DROP COLUMN c Removes a new column from the table, including all its data ADD INDEX ALTER TABLE t ADD INDEX i (c) Adds a new index on c DROP INDEX ALTER TABLE t DROP INDEX i Removes an index RENAME AS ALTER TABLE t RENAME AS newT Changes the name of a table More Information ALTER TABLE Syntax Check Yourself The following questions use an address table with the following definition: CREATE TABLE address ( AddressID INT AUTO_INCREMENT PRIMARY KEY, Street VARCHAR(255) NOT NULL, City VARCHAR(50), State CHAR(2) NOT NULL, ZipCode VARCHAR(9), CustomerID INT(11) ); Enter a SQL statement to change the "Street" column to the "Address" column? answer Enter a SQL statement to add a new column named Country with a data type of VARCHAR that accepts ups to 25 characters. answer Enter a SQL statement to delete a new column named Country. answer ^ top 3.3.3: 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 The ALTER TABLE command lets you change the name of a table Another command is the RENAME TABLE command For example: RENAME TABLE address TO addresses Compare this to: ALTER TABLE address RENAME AS addresses As you can see, the RENAME syntax is slightly shorter More Information RENAME TABLE Syntax ^ top 3.3.4: 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 More Information DROP TABLE Syntax ^ top 3.3.5: Summary Sometimes you need to change your database tables after your create them The SQL language includes a syntax to alter, rename and delete tables The ALTER TABLE statement alters (changes) a database table The RENAME TABLE statement changes the name of a table The DELETE statement is used to delete rows in a table Together with CREATE TABLE, these statements are known as the Data Definition Language (DDL) part of SQL You can use either phpMyAdmin or SQL statements to ALTER, RENAME or DROP tables Check Yourself The following questions use an address table with the following definition: CREATE TABLE address ( AddressID INT AUTO_INCREMENT PRIMARY KEY, Street VARCHAR(255) NOT NULL, City VARCHAR(50), State CHAR(2) NOT NULL, ZipCode VARCHAR(9), CustomerID INT(11) ); What is a SQL statement for altering the Street column to the name Address? What is a SQL statement for adding a new column named Country with a data type of a VARCHAR that accepts ups to 25 characters. What is a SQL statement for deleting a column name Country? What is a SQL statement for removing a table named address? ^ top Exercise 3.3 In this exercise we alter tables in a database. Specifications Make sure that Apache and MySQL are running using the XAMPP control panel. Then open a new Web browser tab or window and enter localhost/phpmyadmin in the address field. You should see phpMyAdmin running in the browser window. In phpMyAdmin, select the customer database you created in Exercise 3.1. You should see both the address and customer tables listed on the left side. Select the customer table by clicking on the customer link in the left menu. In the right frame you will see a list of tabs at the top of the page. Select the checkbox for the FirstName column and then click the Change icon This will open a new form with the schema for the field. Change the Field name from FirstName to FName and press the Save button. This will redisplay the Structure form. Note that the FName field is now shown rather than the FirstName field. Also note the SQL statement displayed by phpMyAdmin. The ALTER TABLE command is used to change the structure of a table. For more information see section: 3.3.2: Altering Tables. Let us change the LastName field to LName using a SQL statement. Select the SQL tab, enter the following command in the text area and press the Go button. ALTER TABLE customer CHANGE LastName LName VARCHAR(100) Use the Structure tab to view the changes to the field names. Note how we had to specify the column definition, that is VARCHAR(100), as part of the change. For more information see section: 3.3.2: Altering Tables. Another use of ALTER TABLE is to add columns. Select the SQL tab, enter the following command in the text area and press the Go button. ALTER TABLE customer ADD Gender CHAR(1) Use the Structure tab to view the changes to the fields. Still another use of ALTER TABLE is to delete columns. Select the SQL tab, enter the following command in the text area and press the Go button. ALTER TABLE customer DROP Gender Use the Structure tab to view the changes to the fields. Another option for changing tables is the RENAME TABLE command. Select the SQL tab, enter the following command in the text area and press the Go button. RENAME TABLE address TO addresses Click the customer database link on the left menu to view the changed table name. For more information see section: 3.3.3: Renaming Tables. You can drop an entire table, and all its data, from the database. Select the SQL tab, enter the following command in the text area and press the Go button. DROP TABLE addresses Click the customer database link on the left menu to view the remaining tables. For more information see section: 3.3.4: Dropping Tables. Export the customer database and save it to the desktop using the name: altered.sql. For more information see lesson: 2.1.5: Importing and Exporting Data. Submit the altered.sql file to Blackboard as part of assignment 3. As time permits, be prepared to answer the Check Yourself questions in the section: 3.3.5: Summary. ^ top Wrap Up Due Next: A3-Project Proposal (3/12/12) Quiz 3 and Discussion 3 (3/12/12) When class is over, please shut down your computer if it is on ^ top Home | Blackboard | Syllabus | Expectations | Schedule Project | Help | FAQ's | HowTo's | Links Last Updated: March 11 2012 @23:00:58