MySQL

Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use prim
Database changed
mysql> select * from prim;
+----+-------------------+-------+------------+----------------+
| id | name              | Bal   | DOB        | city           |
+----+-------------------+-------+------------+----------------+
|  1 | Random Stranger 1 | 25000 | 1998-01-20 | yogi ka shahar |
|  2 | Changed           |  2500 | 1998-01-10 | yogi ka shahar |
|  5 | Random Stranger 5 |  2000 | 1998-10-14 | yogi ka shahar |
|  8 | Random Stranger 8 |  2000 | 1998-10-02 | yogi shahar    |
+----+-------------------+-------+------------+----------------+
4 rows in set (0.00 sec)

mysql> alter table prim add(code int(20)not null);
Query OK, 0 rows affected (1.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from prim;
+----+-------------------+-------+------------+----------------+------+
| id | name              | Bal   | DOB        | city           | code |
+----+-------------------+-------+------------+----------------+------+
|  1 | Random Stranger 1 | 25000 | 1998-01-20 | yogi ka shahar |    0 |
|  2 | Changed           |  2500 | 1998-01-10 | yogi ka shahar |    0 |
|  5 | Random Stranger 5 |  2000 | 1998-10-14 | yogi ka shahar |    0 |
|  8 | Random Stranger 8 |  2000 | 1998-10-02 | yogi shahar    |    0 |
+----+-------------------+-------+------------+----------------+------+
4 rows in set (0.00 sec)

mysql> update prim set code=273005 where id=1;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from prim;
+----+-------------------+-------+------------+----------------+--------+
| id | name              | Bal   | DOB        | city           | code   |
+----+-------------------+-------+------------+----------------+--------+
|  1 | Random Stranger 1 | 25000 | 1998-01-20 | yogi ka shahar | 273005 |
|  2 | Changed           |  2500 | 1998-01-10 | yogi ka shahar |      0 |
|  5 | Random Stranger 5 |  2000 | 1998-10-14 | yogi ka shahar |      0 |
|  8 | Random Stranger 8 |  2000 | 1998-10-02 | yogi shahar    |      0 |
+----+-------------------+-------+------------+----------------+--------+
4 rows in set (0.00 sec)

mysql> SELECT name FROM prim WHERE name REGEXP 'Ra$';
Empty set (0.04 sec)

mysql> SELECT name FROM prim WHERE name REGEXP 'om';
+-------------------+
| name              |
+-------------------+
| Random Stranger 1 |
| Random Stranger 5 |
| Random Stranger 8 |
+-------------------+
3 rows in set (0.00 sec)

mysql> SELECT name FROM prim WHERE name REGEXP 'Ra$';
Empty set (0.00 sec)

mysql> SELECT name FROM prim WHERE name REGEXP '^Ra';
+-------------------+
| name              |
+-------------------+
| Random Stranger 1 |
| Random Stranger 5 |
| Random Stranger 8 |
+-------------------+
3 rows in set (0.00 sec)

mysql> alter table prim modify column Bal int(20);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table prim modify column Bal varcchar(30);
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 'varcchar(30)' at line 1
mysql> alter table prim modify column Bal varchar(30);
Query OK, 4 rows affected (0.84 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from prim;
+----+-------------------+-------+------------+----------------+--------+
| id | name              | Bal   | DOB        | city           | code   |
+----+-------------------+-------+------------+----------------+--------+
|  1 | Random Stranger 1 | 25000 | 1998-01-20 | yogi ka shahar | 273005 |
|  2 | Changed           | 2500  | 1998-01-10 | yogi ka shahar |      0 |
|  5 | Random Stranger 5 | 2000  | 1998-10-14 | yogi ka shahar |      0 |
|  8 | Random Stranger 8 | 2000  | 1998-10-02 | yogi shahar    |      0 |
+----+-------------------+-------+------------+----------------+--------+
4 rows in set (0.00 sec)

mysql> alter table prim modify column city int(20);
ERROR 1366 (HY000): Incorrect integer value: 'yogi ka shahar' for column 'city' at row 1
mysql> alter table prim add (country varchar(30));
Query OK, 0 rows affected (0.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from prim;
+----+-------------------+-------+------------+----------------+--------+---------+
| id | name              | Bal   | DOB        | city           | code   | country |
+----+-------------------+-------+------------+----------------+--------+---------+
|  1 | Random Stranger 1 | 25000 | 1998-01-20 | yogi ka shahar | 273005 | NULL    |
|  2 | Changed           | 2500  | 1998-01-10 | yogi ka shahar |      0 | NULL    |
|  5 | Random Stranger 5 | 2000  | 1998-10-14 | yogi ka shahar |      0 | NULL    |
|  8 | Random Stranger 8 | 2000  | 1998-10-02 | yogi shahar    |      0 | NULL    |
+----+-------------------+-------+------------+----------------+--------+---------+
4 rows in set (0.00 sec)

mysql> update prim set country='India' where id=1;
Query OK, 1 row affected (0.16 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from prim;
+----+-------------------+-------+------------+----------------+--------+---------+
| id | name              | Bal   | DOB        | city           | code   | country |
+----+-------------------+-------+------------+----------------+--------+---------+
|  1 | Random Stranger 1 | 25000 | 1998-01-20 | yogi ka shahar | 273005 | India   |
|  2 | Changed           | 2500  | 1998-01-10 | yogi ka shahar |      0 | NULL    |
|  5 | Random Stranger 5 | 2000  | 1998-10-14 | yogi ka shahar |      0 | NULL    |
|  8 | Random Stranger 8 | 2000  | 1998-10-02 | yogi shahar    |      0 | NULL    |
+----+-------------------+-------+------------+----------------+--------+---------+
4 rows in set (0.00 sec)

mysql> update prim set country='India' where id=2;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update prim set country='India' where id=5 or Bal=2000;
Query OK, 2 rows affected (0.16 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from prim;
+----+-------------------+-------+------------+----------------+--------+---------+
| id | name              | Bal   | DOB        | city           | code   | country |
+----+-------------------+-------+------------+----------------+--------+---------+
|  1 | Random Stranger 1 | 25000 | 1998-01-20 | yogi ka shahar | 273005 | India   |
|  2 | Changed           | 2500  | 1998-01-10 | yogi ka shahar |      0 | India   |
|  5 | Random Stranger 5 | 2000  | 1998-10-14 | yogi ka shahar |      0 | India   |
|  8 | Random Stranger 8 | 2000  | 1998-10-02 | yogi shahar    |      0 | India   |
+----+-------------------+-------+------------+----------------+--------+---------+
4 rows in set (0.00 sec)

mysql>