|
|
 |
Tables List of Categories
Page 1 Page 2
- Conventions used in this FAQ.
- How can I check if a mysql table exists?
- How do I create a mysql table?
- How do I add a new column to an existing table?
- How do I drop an existing column?
- Can I rearrange the structure of a table, change column positions by shifting it from one position to another?
- How do I rename a column?
- How do I repair a corrupted or damaged table?
- How do I redefine a column's definition?
- How do I drop a table?
-
Conventions used in this FAQ.
Any mysql statements or commands in this FAQ will appear in gray colored text using the monospace font. mySQL keywords will be in UPPERCASE, while variables such as tablenames will appear in lowercase.
-
How can I check if a mysql table exists?
There are several methods available o test if a mysql database table actually exists, but no functions, I'm aware of, that can be used within a SELECT statement.
1. You can call the mysql_list_tables() function in PHP :
$result = mysql_list_tables(string database, resource [link_identifier]);
Or you can call the listtables method in Perl :
@tables = $VAR{db}->listtables;
In both of the above cases, you then need to sift through the resulting array to see if the table name you're looking for is in the list. Additionally, both have been deprecated.
2. You can use the 'SHOW TABLES' statement which is much quicker and easier to handle. In PHP you would use :
$result = mysql_query("SHOW TABLES FROM database LIKE 'tablename'");
Or in Perl:
$VAR{q1} = qq~SHOW TABLES LIKE "tablename"~;
$VAR{x1} = $DBCONN->query($VAR{q1});
$VAR{n1} = $VAR{x1}->numrows;
If $VAR{n1} is greater than or equal to 1, the table exists. If it equals 0 (zero) the table does not exist.
-
How do I create a mysql table?
You first need the right to create tables, usually assigned to all users. A simple example is as follows :
CREATE TABLE employees (
id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, fname VARCHAR(255)
)
The above statement, once executed, would create a table named "employees". The table would contain 2 columns, id and fname. The first column, id, is the primary key and is auto incremented. That is, each time a new record is created, the value of that column, can not be null and increments by 1. When adding a new record, you would leave the value for the id column null and mySQL would automatically generate a unique value. The value would be treated as an integer that can be up to 11 digits in length.
The second column, can contain null of blank values or values of up to 255 characters.
-
How do I add a new column to an existing table?
ALTER TABLE employees ADD lname VARCHAR(250)
The above command would add a new column to the employees table. The new column can contain up to 250 characters, which would cover most if not all last names. Each time you add a new column, the tablename, column name and column definition must be specified.
-
How do I drop an existing column?
ALTER TABLE employees DROP lname
When dropping columns, all data within that column is permanently erased. Unless you have the table backed up, the data is irretrievable. Every row/record within the table loses the column you drop.
-
Can I rearrange the structure of a table, change column positions by shifting it from one position to another?
Yes, the simplest way is to use the following statement :
ALTER TABLE tablename MODIFY colname definition AFTER colname
Or, to move it to the first position in the structure :
ALTER TABLE tablename MODIFY colname definition FIRST
-
How do I rename a column?
ALTER TABLE employees CHANGE oldcolname newcolname OLDDEFINITION
oldcolname becomes newcolname. The employees table would no longer have a column named "oldcolname". You must also retain and include the old definition. e.g. VARCHAR(255)
-
How do I repair a corrupted or damaged table?
Tables, especially on busy sites can often be corrucpted. Serv crashes, or any other unexpected termination of a process before it's completed can and does corrupt tables. When this happens, it's nice to know the following can quickly repair the damage.
REPAIR TABLE tablename
-
How do I redefine a column's definition?
ALTER TABLE employees MODIFY fname NEWDEFINITION
Modifies the type of data stored in specified column. You may want to change it from VARCHAR(255) to INT(20) for example.
-
How do I drop a table?
Dropping tables is not a common practice unless you're in the development or wind up stage. Once dropped, all data stored with the table is permanently lost, including the table structure itself. Use wisely.
DROP TABLE tablename
Page 1 Page 2
|
|
|
