NULL value judgment
$t{type1id} = $$pref{dbh}->selectrow_array("SELECT type1id FROM enq1 WHERE id =
3");
if ( $t{type1id} == 0 ) {
print "Type1id is NULL\n";
}
==>If it is not a numerical term, there is a problem with this statement. Special for numerical terms.
if ( length($t{type1id}) == 0 ) {
print "Type1id is NULL\n";
}
==>If Null, there is a problem with this statement
If @rec contains NULL, the following operation will have an error message
$t{line1} = join(' ',@rec);
($t{old1},$t{new1p},$t{new1q}) = $self->dbh->selectrow_array("SELECT
type1id,partsid,QTY FROM enq1 WHERE id = $t{enq1_id}");
91==> if ( $t{old1} == 0 ) {
--------------------------------------------------
[error] [client 127.0.0.1] Use of uninitialized value in numeric eq (==) at
./pro/ line 91, <CONFIG> line 11.,
--------------------------------------------------
How to determine whether the value of an item is NULL (unsolved)
Solve! When INSERT for the first time, put a constant (such as "B")
Origin==>
637==> $t{Nu1} = $self->dbh->selectrow_array("select parts_Unit from parts_nu
where id = $t{Nuid1}");
--------------------------------------------------
[Wed May 14 17:27:51 2008] [error] [client 127.0.0.1] DBD::mysql::db
selectrow_array failed: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use near
'' at line 1 at ./pro/ line 637, <CONFIG> line 11., referer:
--------------------------------------------------
Consider the case where $t{Nuid1} does not exist
Consider the situation where id=C
591==>
@{ $t{p1} } = $self->dbh->selectrow_array("SELECT * FROM $t{ptable}
WHERE id = $t{pid1}");
--------------------------------------------------
[error] [client 127.0.0.1] DBD::mysql::db selectrow_array failed: Unknown
column 'C' in 'where clause' at ./pro/ line 591, <CONFIG> line 11.,
referer:
--------------------------------------------------
Consider the case of $t{pid1}='C'
if ( $#{ $t{pid_list} } == 0 && $t{pid_list}[0] eq 'C' ) {
next;
}
COPY subroutine of a project
use strict;
use DBI;
# Connect to the database
my(%t,$n,@fld,@rec,$pref);
print "This is .\n";
# Connect to the database
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot
connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
$$pref{table} = 'enq2';
$$pref{oldid} = 4;
($pref) = copy_one($pref);
# Close the database
$$pref{dbh}->disconnect;
# COPY A project
sub copy_one {
my($pref) = @_;
my(%t,@rec,$n);
# Remove COLUMNS
$t{sth} = $$pref{dbh}->prepare("SHOW COLUMNS FROM $$pref{table}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $t{columns} },$rec[0]);
}
$t{sth}->finish;
# Get out the data (and remember items that are not NULL)
@{ $t{one} } = $$pref{dbh}->selectrow_array("SELECT * FROM $$pref{table}
WHERE id = $$pref{oldid}");
for $n ( 1 .. $#{ $t{one} } ) {
$t{name} = $t{columns}[$n];
$t{value} = $t{one}[$n];
if ( $t{value} ) {
$t{value} = '"' . $t{value} . '"';
push(@{ $t{names} },$t{name});
push(@{ $t{values} },$t{value});
}
}
$t{name1} = join(',',@{ $t{names} });
$t{value1} = join(',',@{ $t{values} });
# Insert a new project
$t{sql} = 'INSERT INTO ' . $$pref{table} . '(';
$t{sql} .= $t{name1} . ') VALUES(';
$t{sql} .= $t{value1} . ')';
$t{DO} = $$pref{dbh}->do($t{sql});
# print "DO=$t{DO}\n";
return($pref);
}
# There may be a very simple command in MySQL to perform the above operations. The procedures that have been done are put here.
--------------------------------------------------------------------------------
MySQL Operating Program Two
return
--------------------------------------------------------------------------------
No repeated operations allowed for OURREF
$t{enq1_id} = $t{q}->param("enq1_id");
$t{our1_new} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1_id}");
# Get all existing enq1id data of quo2. If there is the same, it is not allowed to switch.
# enq1 and quo2 must be a one-to-one relationship
# Remove all OURREFs
$t{sth} = $self->dbh->prepare("SELECT enq1id FROM quo2");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{our1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $rec[0]");
push(@{ $t{our1s} },$t{our1});
}
$t{sth}->finish;
$t{our1_old} = join(' ',@{ $t{our1s} });
if ( $t{our1_old} !~ /$t{our1_new}/ ) {
$t{sql} = 'UPDATE quo2 SET enq1id ="';
$t{sql} .= $t{enq1_id} . '" WHERE id = "';
$t{sql} .= $t{quo2_id} . '"';
$t{DO} = $self->dbh->do("$t{sql}");
}
Some operations to delete table content
Show the contents of lines 309 to 362 of the table hull_no
mysql> SELECT * from hull_no WHERE id >= 309 AND id <= 362;
Delete HULL_NO from row 309 to row 362 of table hull_no
mysql> UPDATE hull_no SET HULL_NO = "" WHERE id >= 309 AND id <= 362;
Query OK, 54 rows affected (0.16 sec)
Rows matched: 54 Changed: 54 Warnings: 0
Delete the name of row 309 to row 362 of the table hull_no
mysql> UPDATE hull_no SET name = "" WHERE id >= 309 AND id <= 362;
Query OK, 54 rows affected (0.01 sec)
Rows matched: 54 Changed: 54 Warnings: 0
Delete a row of table
mysql> show columns from quo2;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq1id | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| percent0 | int(11) | YES | | NULL | |
| percent | text | YES | | NULL | |
| price | text | YES | | NULL | |
| total | int(11) | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
12 rows in set (0.08 sec)
mysql> ALTER TABLE quo2 DROP enq1id;
Query OK, 6 rows affected (0.27 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> show columns from quo2;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| percent0 | int(11) | YES | | NULL | |
| percent | text | YES | | NULL | |
| price | text | YES | | NULL | |
| total | int(11) | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
11 rows in set (0.02 sec)
mysql> show columns from order1;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| price | text | YES | | NULL | |
| total | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
10 rows in set (0.02 sec)
mysql> ALTER TABLE order1 DROP price;
Query OK, 10 rows affected (0.24 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE order1 DROP total;
Query OK, 10 rows affected (0.17 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> show columns from order1;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
8 rows in set (0.01 sec)
Add one row to the table
mysql> show columns from enq2;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq1id | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| makerid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| type1id | text | YES | | NULL | |
| partsid | text | YES | | NULL | |
| QTY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
12 rows in set (0.06 sec)
mysql> ALTER TABLE enq2 ADD LANGUAGEid INT AFTER enq1id;
Query OK, 1 row affected (0.45 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show columns from enq2;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq1id | int(11) | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| makerid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| type1id | text | YES | | NULL | |
| partsid | text | YES | | NULL | |
| QTY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+------------+---------+------+-----+---------+----------------+
13 rows in set (0.00 sec)
mysql> show columns from quo1;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq2id | int(11) | YES | | NULL | |
| makerref | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
5 rows in set (0.30 sec)
mysql> ALTER TABLE quo1 ADD price TEXT AFTER makerref;
Query OK, 2 rows affected (0.67 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show columns from quo1;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq2id | int(11) | YES | | NULL | |
| makerref | text | YES | | NULL | |
| price | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
Modify a Column operation (change name and change data definition)
mysql> show columns from order1;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| quo2id | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| price | text | YES | | NULL | |
| total | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
10 rows in set (0.16 sec)
mysql> ALTER TABLE order1 CHANGE quo2id orderno TEXT;
Query OK, 6 rows affected (0.56 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> show columns from order1;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| price | text | YES | | NULL | |
| total | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
10 rows in set (0.02 sec)
$t{type1id} = $$pref{dbh}->selectrow_array("SELECT type1id FROM enq1 WHERE id =
3");
if ( $t{type1id} == 0 ) {
print "Type1id is NULL\n";
}
==>If it is not a numerical term, there is a problem with this statement. Special for numerical terms.
if ( length($t{type1id}) == 0 ) {
print "Type1id is NULL\n";
}
==>If Null, there is a problem with this statement
If @rec contains NULL, the following operation will have an error message
$t{line1} = join(' ',@rec);
($t{old1},$t{new1p},$t{new1q}) = $self->dbh->selectrow_array("SELECT
type1id,partsid,QTY FROM enq1 WHERE id = $t{enq1_id}");
91==> if ( $t{old1} == 0 ) {
--------------------------------------------------
[error] [client 127.0.0.1] Use of uninitialized value in numeric eq (==) at
./pro/ line 91, <CONFIG> line 11.,
--------------------------------------------------
How to determine whether the value of an item is NULL (unsolved)
Solve! When INSERT for the first time, put a constant (such as "B")
Origin==>
637==> $t{Nu1} = $self->dbh->selectrow_array("select parts_Unit from parts_nu
where id = $t{Nuid1}");
--------------------------------------------------
[Wed May 14 17:27:51 2008] [error] [client 127.0.0.1] DBD::mysql::db
selectrow_array failed: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use near
'' at line 1 at ./pro/ line 637, <CONFIG> line 11., referer:
--------------------------------------------------
Consider the case where $t{Nuid1} does not exist
Consider the situation where id=C
591==>
@{ $t{p1} } = $self->dbh->selectrow_array("SELECT * FROM $t{ptable}
WHERE id = $t{pid1}");
--------------------------------------------------
[error] [client 127.0.0.1] DBD::mysql::db selectrow_array failed: Unknown
column 'C' in 'where clause' at ./pro/ line 591, <CONFIG> line 11.,
referer:
--------------------------------------------------
Consider the case of $t{pid1}='C'
if ( $#{ $t{pid_list} } == 0 && $t{pid_list}[0] eq 'C' ) {
next;
}
COPY subroutine of a project
use strict;
use DBI;
# Connect to the database
my(%t,$n,@fld,@rec,$pref);
print "This is .\n";
# Connect to the database
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot
connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
$$pref{table} = 'enq2';
$$pref{oldid} = 4;
($pref) = copy_one($pref);
# Close the database
$$pref{dbh}->disconnect;
# COPY A project
sub copy_one {
my($pref) = @_;
my(%t,@rec,$n);
# Remove COLUMNS
$t{sth} = $$pref{dbh}->prepare("SHOW COLUMNS FROM $$pref{table}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $t{columns} },$rec[0]);
}
$t{sth}->finish;
# Get out the data (and remember items that are not NULL)
@{ $t{one} } = $$pref{dbh}->selectrow_array("SELECT * FROM $$pref{table}
WHERE id = $$pref{oldid}");
for $n ( 1 .. $#{ $t{one} } ) {
$t{name} = $t{columns}[$n];
$t{value} = $t{one}[$n];
if ( $t{value} ) {
$t{value} = '"' . $t{value} . '"';
push(@{ $t{names} },$t{name});
push(@{ $t{values} },$t{value});
}
}
$t{name1} = join(',',@{ $t{names} });
$t{value1} = join(',',@{ $t{values} });
# Insert a new project
$t{sql} = 'INSERT INTO ' . $$pref{table} . '(';
$t{sql} .= $t{name1} . ') VALUES(';
$t{sql} .= $t{value1} . ')';
$t{DO} = $$pref{dbh}->do($t{sql});
# print "DO=$t{DO}\n";
return($pref);
}
# There may be a very simple command in MySQL to perform the above operations. The procedures that have been done are put here.
--------------------------------------------------------------------------------
MySQL Operating Program Two
return
--------------------------------------------------------------------------------
No repeated operations allowed for OURREF
$t{enq1_id} = $t{q}->param("enq1_id");
$t{our1_new} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1_id}");
# Get all existing enq1id data of quo2. If there is the same, it is not allowed to switch.
# enq1 and quo2 must be a one-to-one relationship
# Remove all OURREFs
$t{sth} = $self->dbh->prepare("SELECT enq1id FROM quo2");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{our1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $rec[0]");
push(@{ $t{our1s} },$t{our1});
}
$t{sth}->finish;
$t{our1_old} = join(' ',@{ $t{our1s} });
if ( $t{our1_old} !~ /$t{our1_new}/ ) {
$t{sql} = 'UPDATE quo2 SET enq1id ="';
$t{sql} .= $t{enq1_id} . '" WHERE id = "';
$t{sql} .= $t{quo2_id} . '"';
$t{DO} = $self->dbh->do("$t{sql}");
}
Some operations to delete table content
Show the contents of lines 309 to 362 of the table hull_no
mysql> SELECT * from hull_no WHERE id >= 309 AND id <= 362;
Delete HULL_NO from row 309 to row 362 of table hull_no
mysql> UPDATE hull_no SET HULL_NO = "" WHERE id >= 309 AND id <= 362;
Query OK, 54 rows affected (0.16 sec)
Rows matched: 54 Changed: 54 Warnings: 0
Delete the name of row 309 to row 362 of the table hull_no
mysql> UPDATE hull_no SET name = "" WHERE id >= 309 AND id <= 362;
Query OK, 54 rows affected (0.01 sec)
Rows matched: 54 Changed: 54 Warnings: 0
Delete a row of table
mysql> show columns from quo2;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq1id | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| percent0 | int(11) | YES | | NULL | |
| percent | text | YES | | NULL | |
| price | text | YES | | NULL | |
| total | int(11) | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
12 rows in set (0.08 sec)
mysql> ALTER TABLE quo2 DROP enq1id;
Query OK, 6 rows affected (0.27 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> show columns from quo2;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| percent0 | int(11) | YES | | NULL | |
| percent | text | YES | | NULL | |
| price | text | YES | | NULL | |
| total | int(11) | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
11 rows in set (0.02 sec)
mysql> show columns from order1;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| price | text | YES | | NULL | |
| total | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
10 rows in set (0.02 sec)
mysql> ALTER TABLE order1 DROP price;
Query OK, 10 rows affected (0.24 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE order1 DROP total;
Query OK, 10 rows affected (0.17 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> show columns from order1;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
8 rows in set (0.01 sec)
Add one row to the table
mysql> show columns from enq2;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq1id | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| makerid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| type1id | text | YES | | NULL | |
| partsid | text | YES | | NULL | |
| QTY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
12 rows in set (0.06 sec)
mysql> ALTER TABLE enq2 ADD LANGUAGEid INT AFTER enq1id;
Query OK, 1 row affected (0.45 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show columns from enq2;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq1id | int(11) | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| makerid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| type1id | text | YES | | NULL | |
| partsid | text | YES | | NULL | |
| QTY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+------------+---------+------+-----+---------+----------------+
13 rows in set (0.00 sec)
mysql> show columns from quo1;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq2id | int(11) | YES | | NULL | |
| makerref | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
5 rows in set (0.30 sec)
mysql> ALTER TABLE quo1 ADD price TEXT AFTER makerref;
Query OK, 2 rows affected (0.67 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show columns from quo1;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq2id | int(11) | YES | | NULL | |
| makerref | text | YES | | NULL | |
| price | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
Modify a Column operation (change name and change data definition)
mysql> show columns from order1;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| quo2id | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| price | text | YES | | NULL | |
| total | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
10 rows in set (0.16 sec)
mysql> ALTER TABLE order1 CHANGE quo2id orderno TEXT;
Query OK, 6 rows affected (0.56 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> show columns from order1;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| price | text | YES | | NULL | |
| total | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
10 rows in set (0.02 sec)
1234Next pageRead the full text