MySQL Administrator's Bible- P6

Với tập trung đặc biệt vào việc phát hành lớn tiếp theo của MySQL, nguồn tài nguyên này cung cấp một khuôn khổ vững chắc cho bất cứ ai mới vào MySQL hoặc chuyển từ một nền tảng cơ sở dữ liệu, cũng như các quản trị MySQL kinh nghiệm. Các bộ đôi tác giả cao hồ sơ cung cấp bảo hiểm thiết yếu của các nguyên tắc cơ bản của phạm vi bảo hiểm cơ sở dữ liệu quản lý, bao gồm cả cách tiếp cận độc đáo MySQL của các tính năng cơ sở dữ liệu cơ bản...
Nội dung trích xuất từ tài liệu:
MySQL Administrators Bible- P6 MySQL Data Types 5SummaryThis chapter has discussed: ■ The standard data types as defined by the ISO SQL:2003 standard, and which standard data types MySQL supports ■ The non-standard data types MySQL adds ■ The storage requirements, allowed values (data range), and definable attributes for each possible data type ■ How MySQL handles invalid data, and how you can change that behavior ■ All of the possible values for sql_mode and what each sql_mode does ■ Benefits and consequences of using NULL values ■ How to use PROCEDURE ANALYSE() to find an optimal data type for existing data 217 MySQL Index TypesI n SQL theory, a key is a data constraint, such as a unique key or foreign key. On the other hand, an index is an implementation detail, IN THIS CHAPTER provided to be able access a limited set of data more quickly. MySQL Looking at keys and indexeshas keys that act as data constraints, and indexes that make a smallamount of table data readily accessible in a certain order. MySQL allows Using indexes to speed upkey constraints and indexes to be applied to a single data field or to more lookupsthan one data field. A key constraint or index applied to one data field is asimple key constraint or index; on more than one data field is a composite Creating and dropping keykey constraint or index. constraints Using FULLTEXT indexesLooking at Keys and IndexesUnique key constraints in MySQL (UNIQUE KEY and PRIMARY KEY) limitthe data in a table by allowing only one set of values for the indexeddata. A foreign key constraint (FOREIGN KEY) limits the data in a tableby requiring that the set of values for the indexed data match data fromoutside the table.Regular indexes (INDEX, FULLTEXT INDEX, SPATIAL INDEX) and uniqueindexes (UNIQUE KEY and PRIMARY KEY) create an object separate froma table, with its own data structure, using data from the table. This allowslooking up those values to be simpler.Because UNIQUE KEY and PRIMARY KEY function as both keys andindexes, the term key is sometimes used interchangeably with the termindex. We use the term key when we refer to a constraint, and index whenwe refer to a separate structure primarily used for faster lookups. ForUNIQUE KEY and PRIMARY KEY, we may use either index or key. 219Part II Developing with MySQL The following keys can be used to constrain data: ■ UNIQUE KEY ■ PRIMARY KEY ■ FOREIGN KEY Data constraints are checked when an INSERT or UPDATE statement changes data. In database theory, both UNIQUE KEY and PRIMARY KEY specify that for a set of fields, duplicate values are not allowed. One difference between UNIQUE KEY and PRIMARY KEY is that there can be only one PRIMARY KEY per table, whereas there can be more than one UNIQUE KEY. In MySQL, another difference is that a UNIQUE KEY is allowed to contain a NULL value, but a PRIMARY KEY does not allow a NULL value. A FOREIGN KEY requires a set of fields in one table to correspond to another set of fields in another table. The rental table in the sakila database has UNIQUE KEY, PRIMARY KEY, and FOREIGN KEY definitions: mysql> USE sakila; Database changed mysql> SHOW CREATE TABLE rentalG *************************** 1. row *************************** Table: rental Create Table: CREATE TABLE `rental` ( `rental_id` int(11) NOT NULL auto_increment, `rental_date` datetime NOT NULL, `inventory_id` mediumint(8) unsigned NOT NULL, `customer_id` smallint(5) unsigned NOT NULL, `return_date` datetime default NULL, `staff_id` tinyint(3) unsigned NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`rental_id`), UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`, `customer_id`), KEY `idx_fk_inventory_id` (`inventory_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `idx_fk_staff_id` (`staff_id`), CONSTRAINT `fk_rental_customer` FOREIGN KEY (`cu ...