Using ALTER TABLE command to add the names_num column to the cust_names table : Alter Table « Table Index « SQL / MySQL






  1. SQL / MySQL
  2. Table Index
  3. Alter Table

Using ALTER TABLE command to add the names_num column to the cust_names table

     
mysql>
mysql> CREATE TABLE product(
    ->     cust_num MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->     cust_title TINYINT,
    ->     cust_last CHAR(20) NOT NULL,
    ->     cust_first CHAR(15) NOT NULL,
    ->     cust_suffix ENUM('Jr.', 'II', 'III','IV', 'V', 'M.D.','PhD'),
    ->     cust_add1 CHAR(30) NOT NULL,
    ->     cust_add2 CHAR(10),
    ->     cust_city CHAR(18) NOT NULL,
    ->     cust_state CHAR(2) NOT NULL,
    ->     cust_zip1 CHAR(5)NOT NULL,
    ->     cust_zip2 CHAR(4),
    ->     cust_duckname CHAR(25) NOT NULL,
    ->     cust_duckbday DATE,
    ->     PRIMARY KEY (cust_num)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 1, 'XML', 'Red', 'III', '1022 N.E. Sea of Rye', 'A207', 'Seacouver', 'WA', '98601', '3464', 'Netrek Rules'
, '1967:10:21');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 4, 'SQL', 'Vicki', 0, '2004 Singleton Dr.', 0, 'Freedom', 'KS', '67209', '4321', 'Frida Kahlo de Tomayo',
'1948:03:21');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 9, 'HTML', 'Chantel', 0, '1567 Terra Cotta Way', 0,  'Chicago', 'IL', '89129', '4444', 'Bianca', '1971:07:
29');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 7, 'Robert', 'David', 'Sr.', '20113 Open Road Highway', '#6', 'Blacktop', 'AZ', '00606', '1952', 'Harley',
 '1949:08:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 5, 'Kazui', 'Wonko', 'PhD', '42 Cube Farm Lane', 'Gatehouse', 'Vlimpt', 'CA', '45362', 0, 'Fitzwhistle', '
1961:12:04');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 6, 'iPhone', 'Karen', 0, '3113 Picket Fence Lane', 0,  'Fedora', 'VT', '41927', '5698', 'Tess D''urbervill
e', '1948:08:19');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 8, 'Mac', 'Jenny', 0, '9 Wishing Well Court', 0, 'Meadowlark Hill', 'KS', '67048', '1234', 'Spike', '1961:
03:21');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE cust_names
    -> SELECT cust_first, cust_last
    -> FROM product;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> ALTER TABLE cust_names
    -> ADD names_num MEDIUMINT(9) PRIMARY KEY AUTO_INCREMENT FIRST,
    -> CHANGE cust_first names_first CHAR(15),
    -> CHANGE cust_last names_last CHAR(20);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql> drop table product;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table cust_names;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.Altering Database Tables: Add a Column
2.Altering Database Tables: Add More Columns
3.Altering Database Tables: Drop a Column
4.Altering Database Tables: Drop Unique Constraint and Add Primary Key
5.Alter table: add unique
6.Alter table: drop primary and foreign key
7.Alter table: add primary key, foreign key and add column
8.Altering and Dropping Tables
9.Alter table to change the auto_increment setting and comments
10.Alter table to change the order
11.Alter table to add column
12.Alter table to change column sequence
13.Alter table to add columns
14.Alter table to drop primary key
15.Alter table to drop foreign key
16.Alter table to add fulltext search
17.Changing the Table Design (ALTER TABLE)
18.Use ALTER TABLE to change the column type
19.The Effect of ALTER TABLE on Null and Default Value Attributes
20.Create the table and then set the initial sequence value with ALTER TABLE
21.Alter table to set the primary key start
22.ALTER TABLE statement modifies the table accordingly
23.Alter table to add primary key, add new column, change column, drop column
24.Two ALTER TABLE statements can be combined
25.Alter table to add foreign key
26.Syntax for Altering a Column
27.Syntax for altering table and delete a Column
28.Storing Altered Character Strings
29.To drop a default value, use ALTER col_name DROP DEFAULT:
30.ALTER column to BINARY
31.Use the asterisk to try matching against the name ian or the alternative spelling, iain.
32.Specifying Multiple Alterations
33.Alter table to add an index
34.Alter table to add unique index
35.Alter table to Delete an Index
36.Alter table to add index
37.To drop an index with ALTER TABLE
38.Reduce the number of significant characters per index in the index to the first 16 characters
39.Add IGNORE to the statement, then use SELECT to have a look at the table contents to see how the duplicates ha
40.Change j from INT to BIGINT
41.MODIFY statement should specify the null and default value attributes explicitly
42.Redefine i to be NOT NULL, then try again
43.Change column definition