Enter password: ****

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE GPY;

Query OK, 1 row affected (0.06 sec)

mysql> USE GPY;

Database changed
mysql> CREATE TABLE STD ( ID INT ,NAME VARCHAR(20), AGE INT);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE DEPT

-> (
-> DEPTCD CHAR(2),
-> DEPTNAME VARCHAR(20)
-> )
->
->
->
-> ;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW TABLES;

+---------------+
| Tables_in_gpy |
+---------------+
| dept |
| std |
+---------------+
2 rows in set (0.02 sec)

mysql> DESC STD;

+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| NAME | varchar(20) | YES | | NULL | |
| AGE | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> DESC DEPT;

+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| DEPTCD | char(2) | YES | | NULL | |
| DEPTNAME | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> DESC DEPT;

+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| DEPTCD | char(2) | YES | | NULL | |
| DEPTNAME | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE STD ADD PER INT;

Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC STD;

+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| NAME | varchar(20) | YES | | NULL | |
| AGE | int(11) | YES | | NULL | |
| PER | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE STD MODIFY NAME VARCHAR(30), DROP COLUMN PER;
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC STD;

+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| NAME | varchar(30) | YES | | NULL | |
| AGE | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> RENAME STD TO STDINFO;

ERROR 1064 (42000): 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 'STD TO
STDINFO' at line 1
mysql> RENAME TABLE STD TO STDINFO;
Query OK, 0 rows affected (0.03 sec)

mysql> DESC STD;

ERROR 1146 (42S02): Table 'gpy.std' doesn't exist
mysql> DESC STDINFO;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| NAME | varchar(30) | YES | | NULL | |
| AGE | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> DROP TABLE STDINFO;

Query OK, 0 rows affected (0.01 sec)

mysql> DESC STDINFO;

ERROR 1146 (42S02): Table 'gpy.stdinfo' doesn't exist
mysql> CREATE TABLE STD ( ID INT ,NAME VARCHAR(20), AGE INT);
Query OK, 0 rows affected (0.05 sec)
mysql> ALTER TABLE STD ADD PER INT;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC STD;

+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| NAME | varchar(20) | YES | | NULL | |
| AGE | int(11) | YES | | NULL | |
| PER | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> INSERT INTO STD VALUES(1,'SSS',20,60);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO STD (NAME,PER,AGE,ID) VALUES('DFD',60,20,2);

Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM STD;

+------+------+------+------+
| ID | NAME | AGE | PER |
+------+------+------+------+
| 1 | SSS | 20 | 60 |
| 2 | DFD | 20 | 60 |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO STD VALUES('DFD',20,2);

ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO STD VALUES(3,'SDFFGSS',20,NULL);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM STD;

+------+---------+------+------+
| ID | NAME | AGE | PER |
+------+---------+------+------+
| 1 | SSS | 20 | 60 |
| 2 | DFD | 20 | 60 |
| 3 | SDFFGSS | 20 | NULL |
+------+---------+------+------+
3 rows in set (0.00 sec)

mysql> INSERT INTO STD (NAME,AGE,ID) VALUES('DFD',20,2);

Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM STD;

+------+---------+------+------+
| ID | NAME | AGE | PER |
+------+---------+------+------+
| 1 | SSS | 20 | 60 |
| 2 | DFD | 20 | 60 |
| 3 | SDFFGSS | 20 | NULL |
| 2 | DFD | 20 | NULL |
+------+---------+------+------+
4 rows in set (0.00 sec)
mysql> UPDATE STD SET PER = 80 WHERE ID = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM STD;

+------+---------+------+------+
| ID | NAME | AGE | PER |
+------+---------+------+------+
| 1 | SSS | 20 | 60 |
| 2 | DFD | 20 | 60 |
| 3 | SDFFGSS | 20 | 80 |
| 2 | DFD | 20 | NULL |
+------+---------+------+------+
4 rows in set (0.00 sec)

mysql> UPDATE STD SET PER = 80,AGE = 30 WHERE ID = 1;

Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM STD;

+------+---------+------+------+
| ID | NAME | AGE | PER |
+------+---------+------+------+
| 1 | SSS | 30 | 80 |
| 2 | DFD | 20 | 60 |
| 3 | SDFFGSS | 20 | 80 |
| 2 | DFD | 20 | NULL |
+------+---------+------+------+
4 rows in set (0.00 sec)

mysql> DELETE FROM STD;

Query OK, 4 rows affected (0.03 sec)

mysql> SELECT * FROM STD;

Empty set (0.00 sec)

mysql> INSERT INTO STD (NAME,AGE,ID) VALUES('DFD',20,2);

Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO STD VALUES('DFD',20,2);

ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO STD VALUES(1,'SSS',20,60);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO STD VALUES(1,'SSS',20,60);

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM STD;

+------+------+------+------+
| ID | NAME | AGE | PER |
+------+------+------+------+
| 2 | DFD | 20 | NULL |
| 1 | SSS | 20 | 60 |
| 1 | SSS | 20 | 60 |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql> DELETE FROM STD WHERE PER IS NULL;

Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM STD;
+------+------+------+------+
| ID | NAME | AGE | PER |
+------+------+------+------+
| 1 | SSS | 20 | 60 |
| 1 | SSS | 20 | 60 |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> TRUNCATE TABLE STD;

Query OK, 2 rows affected (0.03 sec)

mysql> SELECT * FROM STD;

Empty set (0.00 sec)

mysql> DESC STD;

+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| NAME | varchar(20) | YES | | NULL | |
| AGE | int(11) | YES | | NULL | |
| PER | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> ALTER TABLE STD MODIFY ID INT PRIMARY KEY;

Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> INSERT INTO STD VALUES(1,'SSS',20,60);

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM STD;

+----+------+------+------+
| ID | NAME | AGE | PER |
+----+------+------+------+
| 1 | SSS | 20 | 60 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO STD VALUES(1,'SSS',20,60);

ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> INSERT INTO STD VALUES(2,'SSS',20,60);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM STD;

+----+------+------+------+
| ID | NAME | AGE | PER |
+----+------+------+------+
| 1 | SSS | 20 | 60 |
| 2 | SSS | 20 | 60 |
+----+------+------+------+
2 rows in set (0.01 sec)

mysql> INSERT INTO STD VALUES(NULL,'SSS',20,60);

ERROR 1048 (23000): Column 'ID' cannot be null
mysql> INSERT INTO STD VALUES(NUL,'SSS',20,60);
ERROR 1054 (42S22): Unknown column 'NUL' in 'field list'
mysql> INSERT INTO STD VALUES(3,'SSS',20,60);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM STD;

+----+------+------+------+
| ID | NAME | AGE | PER |
+----+------+------+------+
| 1 | SSS | 20 | 60 |
| 2 | SSS | 20 | 60 |
| 3 | SSS | 20 | 60 |
+----+------+------+------+
3 rows in set (0.00 sec)

mysql> UPDATE STD SET ID = 1 WHERE ID = 3;

ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> ALTER TABLE STD ADD UID INT UNIQUE;
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM STD;

+----+------+------+------+------+
| ID | NAME | AGE | PER | UID |
+----+------+------+------+------+
| 1 | SSS | 20 | 60 | NULL |
| 2 | SSS | 20 | 60 | NULL |
| 3 | SSS | 20 | 60 | NULL |
+----+------+------+------+------+
3 rows in set (0.00 sec)

mysql> UPDATE STD SET UID =1234 WHERE ID = 3;

Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM STD;

+----+------+------+------+------+
| ID | NAME | AGE | PER | UID |
+----+------+------+------+------+
| 1 | SSS | 20 | 60 | NULL |
| 2 | SSS | 20 | 60 | NULL |
| 3 | SSS | 20 | 60 | 1234 |
+----+------+------+------+------+
3 rows in set (0.00 sec)

mysql> UPDATE STD SET UID =1234 WHERE ID = 2;

ERROR 1062 (23000): Duplicate entry '1234' for key 2
mysql> SELECT * FROM STD;