35.7. Viewing and Editing Table Contents

The MySQL module allows you to view the contents of any table in any database. Tables that have a primary key can have their records modified or deleted and new ones added as well.

Table 35.1. MySQL Field Types and Their Uses
TypeDescription
intStores a single integer, which can be signed or unsigned. An int field can, at most, store numbers in the range –231 to +231, but this can be further restricted by specifying a maximum number of decimal digits when the field is added.
tinyintLike an int, but only numbers in the range –128 to +127 can be stored.
smallintLike an int, but only numbers in the approximate range –32768 to +32767 can be stored.
mediumintLike an int, but only numbers in the approximate range –223 to +223 can be stored.
bigintA bigint is similar to an int, but supports the much larger maximum range of –263 to +263.
floatStores a non-integer number, using the potentially imprecise floating point format. When a field of this type is added, you must specify the number of bytes used internally to store it, which changes the number of digits of precision that a float field can support. By default, a float occupies 4 bytes.

If a number is inserted that contains more digits than the float can represent, it will be rounded to the nearest supported value. For this reason, you should not use a field of this type for storing numbers that must be recorded precisely, such as amounts of money. Instead, a decimal field (which has a specific decimal precision) should be used.

You must also specify the number of digits to the right of the decimal point that should be stored and displayed for a float field. If a value with more fractional digits is inserted, it will be rounded off.
doubleA double field is like a float, except that it uses 8 bytes for internal storage and thus can be used to store values with more digits.
decimalA field of this type can store a noninteger number to a specific decimal precision. When adding one, you must enter the total number of digits to store and the number to the right of the decimal point. Any inserted value that uses more digits will be truncated or rounded off. Because decimal fields are stored internally as strings, using them in mathematical SQL expressions will not be as fast as using a float or double. They do, however, have a guaranteed known precision.
dateA date field stores a day, month, and year. The year is always in 4-digit format, and if you attempt to insert a date with a 2-digit year, MySQL will convert it to 4 digits by adding 1900 or 2000, depending on whether the year is before or after 1970. Any valid date in the years 1000 to 9999 can be stored. When displayed or inserted, dates always use the format YYYY-MM-DD, such as 1970-01-24.
datetimeLike a date field, this type stores the time as well. For insertion or display, datetime fields must always use the format YYYY-MM-DD HH:MM:SS.
timestampFields of this type are typically used to store the date and time that a record was last modified. Unless a value is explicitly specified, MySQL will update any timestamp field in a record with the current time whenever it is inserted or updated. Internally, timestamp fields use the UNIX time format, which means they are limited to the years 1970 to 2037. When displayed, the format YYYYMMDDHHMMSS is always used.
timeA time field stores only a time without a date. The HH:MM:SS format is always used for display and updates. Because they can be used to represent an elapsed period of time as well, the hours can range from –838 to +838.
yearThis kind of field is used to store a 4-digit year, but only in the range 1901 to 2155.
charThis type stores a string of characters up to a specified length. Internally, any value in a char field is padded with spaces to the right, which are removed when it is displayed. This means that if you add a char field with a size of 100, it will use up 100 bytes for each row. The maximum size of a field of this type is 255.
varcharStores a variable-length string of characters, up to the maximum specified when the field is added. The number of bytes used internally is related to the amount of data inserted, instead of being fixed at the specified size. Like a char, the maximum size is 255 characters. varchar is a better field type than char in almost all situations and should be used for all storage of short text strings.
blobA blob (which stands for Binary Large Object) can store any kind of data up to a maximum 216 bytes. The number of bytes used internally is proportional to the amount of data inserted.
textThis type is similar to a blob, but is used for storing text. The biggest difference between the two is that text fields are sorted and compared case-insensitively, while blob fields are case sensitive.
tinyblobLike a blob, but can only store 256 bytes of data.
tinytextLike a text field, but can only store 256 bytes of text.
mediumblobLike a blob, but can store more data - 224 bytes to be exact.
mediumtextLike a text field, but can store 224 bytes of text.
longblobAn even larger kind of blob, which can hold up to 232 bytes of data.
longtextAn even larger kind of text field which can hold up to 232 bytes.
enumAn enum field stores a single text value, which must be a member of a list that is specified when adding or modifying the field. Up to 65535 different possible values are allowed.
setThe set type is like enum, but fields of this type can store from 0 to 64 different values from the allowed list, instead of just a single value.

Unfortunately, there is no way to edit the contents of a table without a key, as the module needs some way of identifying specific records. All tables in a database should have one, however.

To view the contents of a table, follow these steps:

1.
On the main page, click on the icon for the database that contains the table, and then on the icon for the table itself.

2.
On the table editing form, click on the View Data button at the bottom. This will bring you to a page displaying the first 20 rows in the table.

3.
If the table contains more rows than can be displayed on one page, the start and end of the visible range and the total number of rows will be displayed at the top. Next to it are left and right arrows for moving to the next or previous 20 records.

4.
For large tables, a search form is also displayed at the bottom of the page. To use it, select a field name from the first menu and a comparison type from the second, and enter a value to search for in the final text box. When the Search button is clicked, only rows for which the chosen field matches will be displayed. To switch back to viewing all records, click the Reset search link that now appears above the table.

The contains comparison type finds records in which the field contains the entered text, while the matches type finds records for which the field value matches an SQL pattern as used in a like clause. In such a pattern, % matches any string of characters, and _ matches any single character, just like * and ? do at the shell prompt.

5.
When viewing a large table, a button labeled Jump to is also displayed at the bottom of the page. If a number is entered into the adjacent field and the button is clicked, the display will move immediately to that row.

If the table has a primary key, this same page can also be used to edit, delete, or add records. Records to edit must first be selected using the checkboxes to the right of each row or the Select all and Invert selection links. When you click the Edit selected rows button, the page will be re-displayed with the values of all chosen records in text boxes. Make whatever changes you like and click the Save button at the bottom of the page to update the database. Or, hit Cancel if you want to stop editing without saving your modifications.

To delete records, select them using the same checkboxes and selection links, and click the Delete selected rows button. The chosen records will be immediately removed from the database with no further confirmation.

To add a new record, hit the Add row button below the table. An additional row will appear containing empty text boxes for you to enter new details. Clicking Save will add the new record to the table and move the display so that you can see the new row. You can also click Cancel if you change your mind about adding a record.

Records are normally edited or added in text fields that appear in the table in the appropriate columns. If you are editing a table that contains a blob or text field, however, or if the Use vertical row adding interface module configuration option is enabled, a different layout is used. Text boxes for fields are listed in a separate box inside or below the table instead, with field name labels to the right. For text or blob fields, a text box is displayed so you can enter multiple lines of text if necessary.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.189.43.211