intTypePromotion=1
zunia.vn Tuyển sinh 2024 dành cho Gen-Z zunia.vn zunia.vn
ADSENSE
 » 
 » 

Mysql your visual blueprint for creating open source databases- P4

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:20

Thêm vào BST
Báo xấu
94
lượt xem 6
download
 
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Mysql your visual blueprint for creating open source databases- P4:The show tables command displays a list of tables available in the currently selected database. You can use this command when you are unsure of the exact name of a table within the database.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Mysql your visual blueprint for creating open source databases- P4

  1. MANAGE DATABASES AND TABLES 2 If you are not sure what length to use for a CHAR or VARCHAR column, specify a value large enough to store a typical value. You can always change the length later. You can also change VARCHAR columns to TEXT columns without losing data. This is explained in Chapter 3. The MySQL server may automatically change the type of some of your columns if there is a conflict between them. In particular, if you use fixed-length CHAR columns and at least one variable-length VARCHAR or TEXT column, the entire row must be variable length, so your CHAR columns are converted to VARCHAR. In addition, the server automatically changes VARCHAR columns with a length of one to three characters to CHAR columns, because it is not practical to use variable length when the column is this small. While it is important to choose the correct column types when you create a table, after you have created it, you can work with VARCHAR and CHAR columns in exactly the same ways. TEXT columns also work in most of the same ways, except that you cannot use the entire body of a TEXT field as an index. s The MySQL monitor ˇ Type description TEXT ); s The table is now created. s The columns you created prompts for another line. and press Enter. for this table are displayed. Á To verify that the › Type url VARCHAR(255), Note: If you want to add additional command worked, type and press Enter. fields, include them before the SHOW COLUMNS FROM links; closing parenthesis. and press Enter. 47
  2. MySQL USING SETS AND ENUMERATIONS the table. You can look at an ENUM column as a list of flags, S ets and enumerations are different from normal text columns in that they are limited to a specific list of each of which can be assigned or not assigned to an item. values. You can use these columns whenever you In the music catalog example, an ENUM column would be need to assign categories to the items in a table. For ideal to store the media the music is available on: example, suppose you were using a database table to media ENUM("CD","DVD","LP","Cassette") catalog music recordings. You could use a SET column to store the category of music that each recording contains: Although you could use separate columns to achieve the same effect, ENUM columns are convenient because they category SET("rock", "pop", "blues", can be assigned values as a single unit. They are also stored "country", "dance") on the server in a highly efficient way, using only one bit per The one potential disadvantage of a SET column is that item, and are thus especially useful when you are storing each item can have only one value. Anything that fits more many rows of data in a table. than one category could only be listed in one. This is where Because ENUM and SET columns can have a large number ENUM columns are useful. of possible values, you may need to split the column With an ENUM column, each item can be assigned any specification into two or more lines when you create the number of values from the list you specify when you create table. USING SETS AND ENUMERATIONS ⁄ From the MySQL monitor, ¤ Type CREATE TABLE music ( s The MySQL monitor ‹ Type title VARCHAR(100), type USE testdb; and press and press Enter. prompts for the next line. and press Enter. Enter. s This starts the table s The database is now definition. selected. Note: If you did not create the testdb database in Chapter 1, you need to create it first. 48
  3. MANAGE DATABASES AND TABLES 2 The values for an ENUM column are actually stored as integers. A value of 1 represents the first possible value, 2 represents the second possible value, and so on. ENUM columns can have a maximum of 65,535 total values available. ENUM values use one byte of storage if there are less than 256 possible values and two bytes if there are 256 or more. The values for a SET column are stored using individual bits. This means that one byte, or eight bits, of storage is required for every eight members of the set. A SET column can have a maximum of 64 members, which requires eight bytes of storage. You can change the definition for a SET or ENUM column using the ALTER TABLE query in MySQL, described in Chapter 3. However, changing the definition does not change the values stored for existing data. If you add a value at the beginning of the list, the numeric values for the entire list will change. You can safely add values at the end of the list, but the best strategy is to determine the possible values in advance and include them when you first create the table. › Type category SET("rock", ˇ Type "blues", "country", Á Type media s This completes the "pop", and press Enter. "dance"), and press Enter. ENUM("CD","DVD", definition for the ENUM and press Enter. column and the CREATE s These two lines define a TABLE command. The table SET column. ‡ Type "LP","Cassette") ); is now created. and press Enter. 49
  4. MySQL CREATE AN INDEXED TABLE You can define the primary key by including the keywords A long with the various columns the table will contain, you can specify a primary key and one or more PRIMARY KEY with one of the column definitions when you indexed columns. In general, you will want to have at create the table, or with a separate PRIMARY KEY definition least a primary key for any table. If there is no primary key, that specifies a column for the key in parentheses. In this there may be no way to uniquely identify a single record. case, you can also use multiple fields as a primary key. This makes it impossible to edit or delete a single row The following CREATE TABLE query creates a MailList table without affecting other rows in the table. with columns for the name, address, city, state, and postal For the primary key, you should choose a column that will code. The name column is defined as the primary key. have a unique value for each row of the table. For example, CREATE TABLE MailList ( for a table that stores a list of names and mailing addresses, name VARCHAR(80) PRIMARY KEY, the name field is usually a good primary key, assuming that address VARCHAR(120), the list is small enough that duplicate names is not a city VARCHAR(50), concern. state VARCHAR(2), postal VARCHAR(5) ); CREATE AN INDEXED TABLE ⁄ From the MySQL monitor, s This selects the testdb s The MySQL monitor s This defines the name field type USE testdb; and press database. prompts for the next line. as the primary key. Enter. ¤ Type CREATE TABLE ‹ Type name VARCHAR(80) MailList ( and press Enter. PRIMARY KEY, and press Enter. 50
  5. MANAGE DATABASES AND TABLES 2 Along with the primary key, one or more additional indexes are often useful. You should only define an index on an additional column if you frequently need to search for values in that column. You can add an index with the INDEX keyword, and optionally specify the UNIQUE keyword to require a unique value for each row. The following example shows an expanded mailing list table with an index on the postal code field. In this case, the UNIQUE keyword is not used because multiple records can have the same code. Example: CREATE TABLE MailList2 ( name VARCHAR(80) PRIMARY KEY, address VARCHAR(120), city VARCHAR(50), state VARCHAR(2), postal VARCHAR(5), INDEX (postal) ); › Type address ˇ Type city VARCHAR(50), ‡ Type postal VARCHAR(5) ); ° Type SHOW COLUMNS VARCHAR(120), and and press Enter. and press Enter. FROM MailList; and press press Enter. Enter. Á Type state VARCHAR(2), s This completes the query, and press Enter. and the table is created. s The columns for the table you created are displayed. 51
  6. MySQL DELETE TABLES AND DATABASES The DROP command does not prompt you for confirmation, I f you no longer have any use for a table, you can delete it using the DROP TABLE command. This command whether you are deleting a table or an entire database. immediately deletes the table, including all data. You After you have issued the DROP command, there is no can specify more than one table to drop with DROP TABLE, way to recover the data unless you have a backup copy. separated by commas. The following command deletes Be sure that you have a backup before you use the DROP the prices table: command. DROP TABLE prices; In order to drop a database or table, the MySQL username you are using must have the correct privileges. Only the When you attempt to drop a table that does not exist, root user is allowed to delete databases by default. MySQL returns an error. You can optionally specify the IF See Chapter 11 for more information about MySQL EXISTS keywords to prevent the error. The following security. example deletes the test1 table only if it exists: In this example you delete the prices table and the newdb DROP TABLE IF EXISTS test1; database, which you created in the sections "Create a Database" and "Create a Simple Table," earlier in this If you want to delete an entire database, you can use the chapter. Be sure you specify the names correctly to avoid DROP DATABASE command. This command deletes the deleting the wrong data. entire database including all tables and data. As with DROP TABLE, you can use the IF EXISTS keyword to prevent an error if the database does not exist. The following command deletes a database called newdb: DROP DATABASE newdb; DELETE A TABLE ⁄ From the MySQL monitor, s This selects the database. ¤ Type DROP TABLE prices; ‹ Type SHOW TABLES; and type USE testdb; and press and press Enter. press Enter. Enter. Note: This task uses the prices table created earlier in this chapter. s The table is deleted. s Verify that the deleted table is not listed. 52
  7. MANAGE DATABASES AND TABLES 2 The DROP TABLE command deletes the disk files that MySQL uses to store a table's data. There are typically three separate files for each table. The DROP DATABASE command deletes the files for each table in the database and additionally deletes the directory for the database. If non-MySQL files are also in this directory, it may prevent the directory from being entirely deleted. Because the DROP command is drastic, be sure you have a backup of all data before deleting a table or database. You will learn more about backing up MySQL databases in Chapter 8. If you are unsure whether to delete a table, you can use the SHOW COLUMNS command to display the columns of the table. Example: SHOW COLUMNS FROM tablename; You can also use a SELECT query to display the data in the table before deleting it. The following command displays all of the rows of a table. Example: SELECT * FROM tablename; DELETE A DATABASE ⁄ From the MySQL monitor, s The database is deleted. ¤ Type SHOW DATABASES; s The list of databases is type DROP DATABASE newdb; and press Enter. displayed. Verify that the and press Enter. database has been deleted. 53
  8. MySQL USING ALTER TABLE QUERIES using the ALTER TABLE command. This command allows W hen you create a table with the CREATE TABLE command in MySQL, you specify the column you to change column names, column types, and other definitions and other options. If you later decide aspects of a table's definition. to change any aspect of the table's definition, you can do so Basic ALTER TABLE Syntax CHANGE You can make any number of changes with a single The CHANGE command changes the definition of an ALTER TABLE command. You can use a variety of existing column. To use this command, specify the old commands within ALTER TABLE to add columns, column name followed by the new name, the column remove columns, and make other changes. If you use type, and any options such as DEFAULT or NULL. multiple keywords to make changes, separate them Specify the old name twice if you are not renaming the with commas. column. Example: The MySQL server attempts to convert any existing data in the column to the new column type. However, in ALTER TABLE address some cases, such as when you change the length of a ADD COLUMN lastvisit DATE, VARCHAR column to a smaller amount, the data in the DROP COLUMN postal; column will be truncated and cannot be restored. ADD COLUMN Example: Use the ADD COLUMN command to add a column to the ALTER TABLE address table. Specify the new column name, the column type, CHANGE name name CHAR(120) NOT NULL; and any attributes. You can use the same syntax as you use when creating a table, as described in Chapter 2. MODIFY You can optionally specify the keyword FIRST after The MODIFY command changes the definition for a ADD COLUMN to add the column at the beginning of the column without changing its name. To use this command, table, or the AFTER keyword and a column name to specify the column name, the new column type, and any add it after an existing column. If you do not specify options. As with CHANGE, the data is converted wherever either of these, the column is added at the end of the possible to the new format. table. Example: DROP COLUMN ALTER TABLE address The DROP COLUMN command enables you to delete an MODIFY name CHAR(120) NOT NULL; existing table column. Use this command with caution, because it deletes all of the data stored in that column in existing table rows without asking for confirmation. To use DROP COLUMN, simply specify the column name. 54
  9. MODIFY TABLES 3 ADD INDEX DROP PRIMARY KEY Use the ADD INDEX command to add an index to the The DROP PRIMARY KEY command removes an table for an existing column. To use this command, existing primary key. This only removes the indexing specify an optional index name followed by the information, not the column or columns that act as the column or columns to index in parentheses. If you key. This command does not require any parameters. use ADD UNIQUE instead of ADD INDEX, a unique index is created. Before adding a unique index, be Example: sure the existing rows of the table have unique ALTER TABLE address values for the column or columns you plan to index. DROP PRIMARY KEY; Example: RENAME ALTER TABLE address The RENAME command renames an existing table. To ADD INDEX postindex (postal); use this command, specify the existing table name and the new name. You can use RENAME TO as a synonym DROP INDEX for RENAME. The DROP INDEX command deletes an existing index. To use this command, specify the index name. The index Example: name is the column name by default, or the name you ALTER TABLE temp specified when creating the index. RENAME TO temp2; Example: Table Options ALTER TABLE address You can change table options using an ALTER TABLE DROP INDEX postindex; command. These options include TYPE, the table type, COMMENT, the optional comment field, and other ADD PRIMARY KEY options. The complete list of options is in Chapter 2. The ADD PRIMARY KEY command adds a primary key. To change options, specify each option followed by an This can only be used if the table does not have an equal sign and the new value. existing primary key. To use this command, specify one or more columns to act as the primary key. Each Example: existing row of the table must have a unique value for ALTER TABLE temp the column or columns specified. The column you TYPE=Heap, specify must also have the NOT NULL attribute. You COMMENT="I changed the table type."; can alter the column to add this attribute if necessary, as described earlier in this section. ORDER BY Example: The ORDER BY command sorts the existing data in a ALTER TABLE address table by the value of a specified column. While you can retrieve the rows of the table in any order, this command ADD PRIMARY KEY (name, address); is useful to make the order permanent when the table is rarely changed and usually retrieved in a particular order. Example: ALTER TABLE address ORDER BY name; 55
  10. MySQL ADD A COLUMN TO A TABLE ALTER TABLE address ADD COLUMN I f you have created a few tables in MySQL, you learned that there are many decisions to be made — the country VARCHAR(10) AFTER state; columns to include, their types, and other aspects of the When you add a column, you can specify the same table. Fortunately, you can change most of these after the attributes that you use when creating a table, as described table is created with the ALTER TABLE command. in Chapter 2. You can specify the column's type and display You can use ALTER TABLE ADD COLUMN to add a column width, select a default value for the column, and specify to an existing table. The basic query syntax specifies the whether null values are allowed. table, the name of the new column, and its column type: You cannot specify values for the data in the table's rows ALTER TABLE address ADD COLUMN for the new column. The default value of the column will be country VARCHAR(10); used for all rows of the table until you change the data. You can optionally specify where the new column should Be sure the new column's name does not conflict with be added. Either specify the keyword FIRST to insert the other columns in the table. The column must also be new column as the first column in the table's definition, or compatible with the existing columns: In particular, if there specify the AFTER keyword followed by the name of an are existing variable-length text columns, such as VARCHAR existing column. The following example adds a country or TEXT, you cannot add a fixed-length CHAR column. column to the address table after the existing state column: ADD A COLUMN TO A TABLE Note: This example uses the testdb ⁄ From the MySQL monitor, ¤ Type SHOW COLUMNS s The current list of columns database and the address table. If type USE testdb; and press FROM address; and press is displayed. you have not created these, follow Enter. Enter. the instructions in Chapter 1 or on the CD-ROM. s The database is now selected. 56
  11. MODIFY TABLES 3 You can add multiple columns within the same ALTER TABLE command by separating them with commas and enclosing the entire list of new columns in parentheses. For example, the following command adds three new columns to the address table: Example: ALTER TABLE address ADD COLUMN ( country VARCHAR(10), unit VARCHAR(5), notes TEXT ); The parentheses shown in this example are not necessary unless you are adding more than one column. When you use this form of the command to add multiple columns, the columns are always added at the end of the table; you cannot use the AFTER or FIRST keywords to control the position of the new columns. You can combine the ADD COLUMN option with other ALTER QUERY commands, described later in this chapter. To include multiple commands, separate them with commas. Although it makes the ALTER TABLE query easier to read, the word COLUMN is actually optional. If you use ADD by itself, MySQL assumes you are adding one or more columns. ‹ Type ALTER TABLE address › Type country ˇ Type SHOW COLUMNS s The list of columns is ADD COLUMN and press VARCHAR(10) AFTER state; FROM address; and press displayed, including the new Enter. and press Enter. Enter. country column. s The MySQL monitor s The column is now added prompts for the next line. to the table. 57
  12. MySQL ADD AN INDEX based on the combination of values in all of the columns A s you work with a table in MySQL, you may find that an index on an additional field would improve you list. This example adds an index that uses both the performance. You can use the ALTER TABLE state and country columns: command to add an index to an existing table. To add an ALTER TABLE address ADD INDEX index, you specify the ADD INDEX keywords, an optional location (state,country); name for the index, and the existing column or columns to be indexed. If you are adding a column to the table with ADD COLUMN, you can use ADD INDEX within the same ALTER TABLE For example, the following ALTER TABLE query adds an command to add an index based on the new column, as index called stateindex to the address table, as an index for long as you specify the index after the column definition: the existing state column: ALTER TABLE address ALTER TABLE address ADD INDEX ADD COLUMN custnum INT UNSIGNED, stateindex (state); ADD INDEX (custnum); Because the ADD INDEX command adds a simple index Keep in mind that adding an index does not always improve rather than a primary key, it is unaffected by existing performance. In fact, extra indexes on fields that are not indexes in the table's definition. When you use this frequently used for searching can slow down access to the command to add an index, the MySQL server immediately table. See Chapter 10 for information on determining begins scanning the table and building the index file. whether an added index will be beneficial. You can optionally specify more than one column for the index, separated by commas. In this case, the index will be ADD AN INDEX Note: This example uses the testdb s The database is now ‹ Type stateindex (state); and › Type SHOW COLUMNS database and the address table. The selected. press Enter. FROM address; and press instructions for creating these are in Enter. Chapter 1 and on the CD-ROM. ¤ Type ALTER TABLE address s The index is now created. ADD INDEX and press Enter. s The list of columns is ⁄ Type USE testdb; and displayed. The value NULL in press Enter. s The MySQL monitor the Key field shows the new prompts for the next line. index on the state column. 58
  13. MODIFY TABLES 3 MySQL version 3.22 and later supports an alternate command for adding indexes to tables. You can use the CREATE INDEX command to add a regular index, a unique index, or a full-text index to an existing column. To add a simple index with CREATE INDEX, specify a name for the new index, the ON keyword, and the table name. This should be followed by one or more column names in parentheses. The following command adds an index on the state column of the address table: CREATE INDEX stateindex ON address (state); You can optionally specify the keyword UNIQUE after CREATE to make the new index a unique index, or FULLTEXT to make it a full-text index. Full-text indexes index the entire text of a VARCHAR or TEXT column. If you add a regular index on a TEXT column, you must specify the length of the column to index in parentheses. This example adds an index on only the first ten characters of the name column: CREATE INDEX first10 ON address (name(10)); Functionally, CREATE INDEX is equivalent to the ADD INDEX and ADD UNIQUE commands with ALTER TABLE, described in this section and the next section. You cannot add a primary key using CREATE INDEX. ADD A NEW INDEXED COLUMN Note: This example uses the testdb s This selects the database. › Type ADD INDEX s This adds the custnum database and the address table. The (custnum); and press Enter. column and creates an index instructions for creating these are in ¤ Type ALTER TABLE address on that column. Chapter 1 and on the CD-ROM. and press Enter. ⁄ From the MySQL Monitor, ‹ Type ADD COLUMN type USE testdb; and press custnum INT UNSIGNED, and Enter. press Enter. 59
  14. MySQL ADD A PRIMARY KEY OR UNIQUE INDEX ALTER TABLE address ADD UNIQUE key1 Y ou can also use the ALTER TABLE command in MySQL to add a unique index or a primary key to an (address, city); existing table. Unique indexes require a unique value While adding an ordinary index to a table is simple, you for each row of the table. The primary key is a special type must consider any data in the table when adding a unique of unique index that is used as the primary identifier for index or primary key. Because these indexes require a each row. Each table can have any number of unique unique value for each row, MySQL will return an error if indexes but only one primary key. there are duplicate values for the column you are indexing. To add a primary key, use ADD PRIMARY KEY with ALTER To successfully add the index, you will need to ensure that TABLE. You can only add a primary key if the table does not the rows have unique values for the column that you are have an existing primary key. The following example adds a making into a key. primary key on the name field of the address table: If you are adding a column that will act as the new primary ALTER TABLE address ADD PRIMARY KEY (name); key, you can specify PRIMARY KEY as part of its column definition. However, this will only work on an empty table, To add a unique index, you use the ADD UNIQUE keywords because existing rows need unique values for the primary with the ALTER TABLE command. This example adds a key. unique index called key1 to the address table, indexing the address and city columns: ADD A PRIMARY KEY Note: This example uses the testdb s The database is selected. ‹ Type ADD PRIMARY KEY › Type SHOW COLUMNS database and the address table. To (name); and press Enter. FROM address; and press create them, see Chapter 1 or the ¤ Type ALTER TABLE address Enter. CD-ROM. and press Enter. s The primary key is now added. s The list of columns is ⁄ From the MySQL monitor, s The MySQL monitor displayed. The value PRI type USE testdb; and press prompts for the next line. in the Key column indicates Enter. that the name field is a primary key. 60
  15. MODIFY TABLES 3 While you can add any type of index or primary key to a table using ALTER TABLE, you can also define them when the table is created with the CREATE TABLE command. This is often the better way to add an index, because it ensures that you define the index before any rows are added to the table, and eliminates problems with non- unique rows. For example, the following CREATE TABLE statement creates an addressindex table. This is identical to the address table, but has the primary key on the name field and the unique index on the address field already defined. Example: CREATE TABLE addressindex ( name VARCHAR(100) PRIMARY KEY, address VARCHAR(120), city VARCHAR(50), state CHAR(2), UNIQUE key1 (address) ); This example uses the PRIMARY KEY keyword with the name column to define the primary key. It uses the UNIQUE keyword in the last line to define a unique index named key1 on the address column. ADD A UNIQUE INDEX Note: This example uses the testdb s This selects the database. ‹ Type ADD UNIQUE key1 s The unique index is now database and the address table. To (address, city); and press Enter. added on the address and create them, see Chapter 1 or the ¤ Type ALTER TABLE address city columns. CD-ROM. and press Enter. ⁄ From the MySQL monitor, type USE testdb; and press Enter. 61
  16. MySQL ADD A TIMESTAMP COLUMN Because the default value for the first timestamp column is T imestamp columns are useful for keeping track of when a row of the table has been updated. Each time the current date and time, all rows will be set to the current you modify or insert a row, the first timestamp date and time when you add the column. column in the table's definition is automatically set to the If you add a second timestamp column to a table, it is not current date and time. updated automatically. The default value for the second As with other column types, you can add a timestamp timestamp column will be zero. You can set this column to column to an existing table using the ALTER TABLE the current time and date manually when you add a row. command in MySQL. For example, the following command You can use a timestamp column as an index or primary adds a timestamp column called updatetime with a display key. However, if data already exists in the table, you cannot width of 14 characters to the address table: make a new timestamp column the primary key when you ALTER TABLE address add it to the table, because all of the rows will have the ADD COLUMN updatetime TIMESTAMP(14); current date and time as the value and the rows will be non-unique. You must assign unique values before adding a When you add a new timestamp column to an existing unique index. table, it is filled with the default value for each existing row. ADD A TIMESTAMP COLUMN Note: This example uses the testdb ⁄ From the MySQL monitor, ¤ Type ALTER TABLE address s The MySQL monitor database and the address table. See type USE testdb; and press and press Enter. prompts for the next line. Chapter 1 or the CD-ROM to create Enter. them. s The database is now 62 selected.
  17. MODIFY TABLES 3 A timestamp column's display width can be up to 14 digits. When 14 digits are used, the column displays the year, month, date, hour, minute, and second values. For example, a TIMESTAMP(14) column set to January 3rd, 2005 at midnight would return the value 20050103000000. Smaller values for the display width display only a partial date, as shown in the table below. The display width does not affect the actual value. Timestamps always store a value down to the second. DISPLAY WIDTH FORMAT 14 YYYYMMDDHHMMSS 12 YYMMDDHHMMSS 10 YYMMDDHHMM 8 YYYYMMDD 6 YYMMDD Because MySQL stores years as four digits, you should always specify all four digits of the year when assigning a value to a timestamp or date field. If you only specify two digits, MySQL assumes that values from 70 to 99 are in the 1900's, and values from 0 to 69 are in the 2000's. ‹ Type ADD COLUMN s The timestamp column is › Type SHOW COLUMNS s The list of columns is updatetime TIMESTAMP(14); now added to the table. FROM address; and press displayed, including the and press Enter. Enter. column you added. 63
  18. MySQL ADD AN AUTO-INCREMENT COLUMN into an auto-increment column, the next sequence number W hile some tables have an obvious choice for a primary key, such as a name column, some tables for the field is inserted instead. Auto-increment columns have no value that is guaranteed to be unique for should also have the UNSIGNED attribute because negative each row. In this case, you can use an auto-increment numbers can cause conflicts. column as a key. When you specify the AUTO_INCREMENT You can use any of MySQL's integer types for an auto- attribute for a column, each row you add is automatically increment column. However, be sure to use a type that can assigned a new unique value for that column. store a wide enough range of values to account for the As with other column types, you can use ALTER TABLE maximum amount of rows you will be adding to the table. to add an auto-increment column to an existing table. For You can change the column type later if you need a larger example, this command adds a new auto-increment column range, as explained in the section "Change a Column Type," called num to the links table: later in this chapter. ALTER TABLE links ADD COLUMN Auto-increment columns are ideal for use as primary keys num INTEGER UNSIGNED NOT NULL or unique indexes, and in fact MySQL requires that any AUTO_INCREMENT PRIMARY KEY; auto-increment column be defined as a unique index. You can use only one auto-increment column per table. Any column you use as an auto-increment column must have the NOT NULL attribute. If you insert a NULL value ADD AN AUTO-INCREMENT COLUMN Note: This example uses the testdb ⁄ From the MySQL monitor, ¤ Type ALTER TABLE links ‹ Type num INTEGER database and the links table. The type USE testdb; and press ADD COLUMN and press UNSIGNED NOT NULL and instructions to create them are in Enter. Enter. press Enter. Chapter 1 and on the CD-ROM. s The database is now s The MySQL monitor selected. prompts for the next line. 64
  19. MODIFY TABLES 3 When you add an auto-increment column to a table with existing data, the rows of the table are assigned values for the column automatically, counting up from one. This allows you to use a new auto-increment column as a primary key or unique index, because the values are guaranteed to be unique. When you delete a row from a table with an auto- increment column, the sequence number on that row is usually not re-used. Thus, you should not assume that every number in a certain range will be used for one of the rows of the table — the only thing you are guaranteed is that every row has a unique value for the auto-increment column. If you delete all of the rows of the table, the auto-increment numbers start over at one. You can specify the first number to be used when assigning values to an auto-increment column when you create the table with the AUTO_INCREMENT parameter. You can also use this when adding a new auto-increment column to a table. When you insert a new row in an auto-increment column, it is assigned the number after the largest number used by a current row of the table. › Type AUTO_INCREMENT s The new column is added ˇ Type SHOW COLUMNS s The list of columns is PRIMARY KEY; and press to the table. FROM links; and press Enter. displayed, including the new Enter. auto-increment column. 65
  20. MySQL RENAME A COLUMN same as when creating a table, but you cannot use a name Y ou can use the ALTER TABLE command in MySQL to change the name of an existing column. To change that is already used in the table. a column's name, use the CHANGE keyword. For Keep in mind that when you rename a column, any example, this command changes the name of the url applications that refer to the column by name will need to column in the links table to link: be modified to use the new name. You will also need to ALTER TABLE links refer to the new name in any further references to the CHANGE url link VARCHAR(255); column. To use CHANGE with ALTER TABLE, you specify the current In MySQL 4.0 and later, you can use the optional keyword name of the column, followed by the new name, the FIRST after the column definition to move the column to column's type, and any attributes such as DEFAULT or the beginning of the column list for the table, or the AFTER UNSIGNED. If you do not want to change the type of the keyword followed by a column name to move the column column, specify the same information you used when to a new position after the specified column. Changing the creating the column. order of columns does not affect existing data or applications. If you are unsure of the column's current type and attributes, you can use the SHOW COLUMNS command to If you specify a different column type or attributes with the find out the details of the column, and repeat them with a CHANGE keyword, the column's definition will be modified. new name. The rules for the new column name are the This is explained in the next section. RENAME A COLUMN Note: This example uses the testdb ⁄ From the MySQL monitor, ¤ Type DESCRIBE links; and s This displays the current database and the links table. See type USE testdb; and press press Enter. column list for the links table. Chapter 1 or the CD-ROM for Enter. information on creating them. s The database is now selected. 66
ADSENSE

CÓ THỂ BẠN MUỐN DOWNLOAD

THÔNG TIN
TRỢ GIÚP
HỖ TRỢ KHÁCH HÀNG
Theo dõi chúng tôi

Chịu trách nhiệm nội dung:

Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA

LIÊN HỆ

Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM

Hotline: 093 303 0098

Email: support@tailieu.vn

Giấy phép Mạng Xã Hội số: 670/GP-BTTTT cấp ngày 30/11/2015 Copyright © 2022-2032 TaiLieu.VN. All rights reserved.

 

Đồng bộ tài khoản
2=>2