Alter table query in php

MySQLi — ALTER Command

MySQL ALTER command is very useful when you want to change a name of your table, any table field or if you want to add or delete an existing column in a table.

Let’s begin with creation of a table called tutorials_alter.

root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> create table tutorials_alter → ( → i INT, → c CHAR(1) → ); Query OK, 0 rows affected (0.27 sec) mysql> SHOW COLUMNS FROM tutorials_alter; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | i | int(11) | YES | | NULL | | | c | char(1) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.02 sec)

Dropping, Adding or Repositioning a Column

Suppose you want to drop an existing column i from above MySQL table then you will use DROP clause along with ALTER command as follows −

mysql> ALTER TABLE tutorials_alter DROP i;

A DROP will not work if the column is the only one left in the table.

To add a column, use ADD and specify the column definition. The following statement restores the i column to tutorials_alter −

mysql> ALTER TABLE tutorials_alter ADD i INT;

After issuing this statement, testalter will contain the same two columns that it had when you first created the table, but will not have quite the same structure. That’s because new columns are added to the end of the table by default. So even though i originally was the first column in mytbl, now it is the last one.

mysql> SHOW COLUMNS FROM tutorials_alter; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec)

To indicate that you want a column at a specific position within the table, either use FIRST to make it the first column or AFTER col_name to indicate that the new column should be placed after col_name. Try the following ALTER TABLE statements, using SHOW COLUMNS after each one to see what effect each one has −

ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT FIRST; ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT AFTER c;

The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position.

Changing a Column Definition or Name

To change a column’s definition, use MODIFY or CHANGE clause along with ALTER command. For example, to change column c from CHAR(1) to CHAR(10), do this −

mysql> ALTER TABLE tutorials_alter MODIFY c CHAR(10);

With CHANGE, the syntax is a bit different. After the CHANGE keyword, you name the column you want to change, then specify the new definition, which includes the new name. Try out the following example:

mysql> ALTER TABLE tutorials_alter CHANGE i j BIGINT;

If you now use CHANGE to convert j from BIGINT back to INT without changing the column name, the statement will be as expected −

mysql> ALTER TABLE tutorials_alter CHANGE j j INT;

The Effect of ALTER TABLE on Null and Default Value Attributes −

When you MODIFY or CHANGE a column, you can also specify whether or not the column can contain NULL values and what its default value is. In fact, if you don’t do this, MySQL automatically assigns values for these attributes.

Here is the example, where NOT NULL column will have value 100 by default.

mysql> ALTER TABLE tutorials_alter → MODIFY j BIGINT NOT NULL DEFAULT 100;

If you don’t use above command, then MySQL will fill up NULL values in all the columns.

Changing a Column’s Default Value

You can change a default value for any column using ALTER command. Try out the following example.

mysql> ALTER TABLE tutorials_alter ALTER j SET DEFAULT 1000; mysql> SHOW COLUMNS FROM tutorials_alter; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c | char(10) | YES | | NULL | | | j | bigint(20) | NO | | 1000 | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.02 sec)

You can remove default constraint from any column by using DROP clause along with ALTER command.

mysql> ALTER TABLE tutorials_alter ALTER j DROP DEFAULT; mysql> SHOW COLUMNS FROM tutorials_alter; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c | char(10) | YES | | NULL | | | j | bigint(20) | NO | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.02 sec)

Changing a Table Type

You can use a table type by using TYPE clause along with ALTER command.

To find out the current type of a table, use the SHOW TABLE STATUS statement.

mysql> SHOW TABLE STATUS LIKE 'tutorials_alter'\G *************************** 1. row *************************** Name: tutorials_alter Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2017-02-17 11:30:29 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)

Renaming a Table

To rename a table, use the RENAME option of the ALTER TABLE statement. Try out the following example to rename tutorials_alter to tutorials_bks.

mysql> ALTER TABLE tutorials_alter RENAME TO tutorials_bks;

You can use ALTER command to create and drop INDEX on a MySQL file. We will see this feature in next chapter.

Источник

Изменение структуры существующей таблицы

Для изменения структуры существующей таблицы можно использовать оператор ALTER TABLE .

Применяя его, можно выполнять различные действия с полями таблиц MySQL :

Добавление новых полей в таблицу

Добавить новое поле таблицы

ALTER TABLE имя_таблицы ADD COLUMN название_поля VARCHAR(20) ;

Добавить новое поле после заданного поля таблицы (AFTER — ПОСЛЕ)

ALTER TABLE имя_таблицы ADD COLUMN название_поля VARCHAR(20) AFTER название_предыдущего_поля;

Добавить новое поле на первое месте в таблице (FIRST — ПЕРВЫЙ)

ALTER TABLE имя_таблицы ADD COLUMN название_поля VARCHAR(20) FIRST ;

Одновременное добавление новых полей

ALTER TABLE имя_таблицы ADD COLUMN название_поля_1 INT(30) AFTER название_предыдущего_поля, ADD COLUMN название_поля_2 VARCHAR(30) FIRST ;

Удаление полей таблицы

Удалить определённое поле таблицы

ALTER TABLE имя_таблицы DROP COLUMN название_поля;

Одновременное удаление полей таблицы

ALTER TABLE имя_таблицы DROP COLUMN название_поля_1, DROP COLUMN название_поля_2;

Переименование поля и изменение типа хранимых данных

Переименовать поле и попутно изменить тип хранимых в нём данных

ALTER TABLE имя_таблицы CHANGE старое_название_поля новое_название_поля VARCHAR(50) ;

Изменить исключительно тип данных

ALTER TABLE имя_таблицы MODIFY название_поля VARCHAR(3) ;

Примеры запросов

Создадим в phpMyAdmin базу данных newDB .

В этой базе данных создадим таблицу users :

$servername = «localhost» ;
$username = «root» ;
$password = «» ;
$dbname = «newDB» ;

// Устанавливаем соединение с сервером MySQL:

$conn = new mysqli ( $servername , $username , $password , $dbname );

if ( $conn -> connect_error ) die ( «Не удалось подключиться: » . $conn -> connect_error );
>
echo «Соединение успешно установлено» ;

// Запрос для создания таблицы users

$sql = «CREATE TABLE users(
id INT(11) NOT NULL PRIMARY KEY,
name VARCHAR(30),
surname VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL,
message TEXT NOT NULL
)ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci» ;

// Проверка выполнения создания таблицы
if ( $conn -> query ( $sql ) === TRUE ) echo «Таблица users создана успешно

» ;
> else <
echo «Ошибка при создании таблицы: » . $conn -> error ;
>

// закрываем соединение
$conn -> close ();
?>

1. Добавляем в таблицу «users» новое поле — price , с типом данных — DOUBLE

$sql = «ALTER TABLE users ADD COLUMN price DOUBLE» ;

2. Добавим в таблицу «users» поле number , с типом данных — INT(30), стоящее после поля id ( AFTER — после)

$sql = «ALTER TABLE users ADD COLUMN number INT(30) AFTER id» ;

3. Добавим новое поле status с типом данных — VARCHAR(30), стоящее на первом месте ( FIRST — первый).

$sql = «ALTER TABLE users ADD COLUMN status VARCHAR(30) FIRST» ;

4. Запрос на одновременное добавления новых полей number и status

$sql = «ALTER TABLE users ADD COLUMN number INT(30) AFTER id,
ADD COLUMN status VARCHAR(30) FIRST» ;

$servername = «localhost» ;
$username = «root» ;
$password = «» ;
$dbname = «newDB» ;

// Устанавливаем соединение с сервером MySQL:

$conn = new mysqli ( $servername , $username , $password , $dbname );

if ( $conn -> connect_error ) die ( «Не удалось подключиться: » . $conn -> connect_error );
>
echo «Соединение успешно установлено» ;

// 1. Запрос на добавление нового поля «price»
$sql = «ALTER TABLE users ADD COLUMN price DOUBLE»

// 2. Запрос на добавление нового поля «number», стоящее после поля id (AFTER — после)
// $sql = «ALTER TABLE users ADD COLUMN number INT(30) AFTER id» ;

// 3. Запрос на добавление нового поля «status», стоящее на первом месте (FIRST — первый)
// $sql = «ALTER TABLE users ADD COLUMN status VARCHAR(30) FIRST» ;

// 4. Запрос на одновременное добавление новых полей «number» и «status»
// $sql green»>// ADD COLUMN status VARCHAR(30) FIRST»;

// Проверка выполнения запросов
if ( $conn -> query ( $sql ) === TRUE ) echo «Выполнено

» ;
> else <
echo «Ошибка: » . $conn -> error ;
>

// закрываем соединение
$conn -> close ();
?>

Поочередно выполняем запросы и получим:

Выполнение следующих запросов удалит соответсвующие поля:

$sql = «ALTER TABLE users DROP COLUMN number» ;

$sql = «ALTER TABLE users DROP COLUMN status» ;

$sql = «ALTER TABLE users DROP COLUMN number, DROP COLUMN status» ;

Переименуем поле price и изменим тип хранимых в нём данных (было — price double , станет — age int(11) )

$sql = «ALTER TABLE users CHANGE price age int(11)» ;

Изменим тип данных для поля password (было — VARCHAR(30) , станет — VARCHAR(50) ;

изменится и значение по умолчанию — станет NULL )

$sql = «ALTER TABLE users MODIFY password VARCHAR(50)» ;

В результате выполнения этих запросов, получим изменения в таблице users :

Источник

PHP MySQL
Create & Test MySQL Database

Existing tables can be altered using the mysql_query() function.

You can add or modify fields or columns in an existing table.

You can even delete an entire table and start over.

Note: If you have accessed this web page via a search engine, you should go back and start at the Beginning.
This tutorial is designed to be viewed and executed in sequence.
Learn to build your database right on your PC and Export it to your website.

Add a Column or Field to a Table

To add a column to an existing table the syntax would be:
mysql_query(«ALTER TABLE birthdays ADD street CHAR(30)»);

You can also specify where you want to add the field.
mysql_query(«ALTER TABLE birthdays ADD street CHAR(30) AFTER birthday»);

The simple bit of code shown above would add a new column to the birthdays table named street after birthday. Type and size are also set in the statement.

You can also add multiple fields:

mysql_query("ALTER TABLE birthdays ADD street CHAR(30) AFTER birthday, Add city CHAR(30) AFTER street, ADD state CHAR(4) AFTER city, ADD zipcode CHAR(20) AFTER state, ADD phone CHAR(20) AFTER zipcode");

Modify a Column or Field

Column definitions can be modified using the ALTER method. The following code would change the existing birthday column from 7 to 15 characters.

mysql_query(«ALTER TABLE birthdays CHANGE birthday birthday VARCHAR(15)»);

In the example the column to alter is first named and then the new definition is supplied which includes the column name

Remove a Column or Field

Columns can be removed from an existing table. The next example of code would remove the lastname column.
mysql_query(«ALTER TABLE birthdays DROP lastname»);

Remove a Table

Be careful with this code. It will remove an entire table and all of its contents from your database.
mysql_query(«DROP TABLE table_name»);

This script is included in the download zip file. If you run it, you will need to add the new fields to all of the existing scripts.

Copy & Save as: birthdays_add_fields.php

You should only run this script if you want to increase your knowledge by getting a little practice in updating the existing scripts.

Note: All ofhe operations covered above can be performed from the phpMyAdmin panel.

MySQL Tutorial

To extend your knowledge of MySQL study the Docs and Tutorials at the official MySQL website. MYSQL.com

Источник