- The following sql commands(queries) are used to understand how to create database, tables and records.
- We can also update and delete records using following commands
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| banking |
| mysql |
| performance_schema |
| world |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database student;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| banking |
| mysql |
| performance_schema |
| student |
| world |
+--------------------+
6 rows in set (0.00 sec)
mysql> use student;
Database changed
mysql> show tables;
Empty set (0.06 sec)
mysql> create table account(no int , name varchar(20) , balance int);
Query OK, 0 rows affected (0.09 sec)
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| account |
+-------------------+
1 row in set (0.00 sec)
mysql> insert into account values(101, 'amar', 6000);
Query OK, 1 row affected (0.06 sec)
mysql> insert into account values(102, 'annie', 8000);
Query OK, 1 row affected (0.08 sec)
mysql> select *from account;
+------+-------+---------+
| no | name | balance |
+------+-------+---------+
| 101 | amar | 6000 |
| 102 | annie | 8000 |
+------+-------+---------+
2 rows in set (0.05 sec)
mysql> select name, balance from account;
+-------+---------+
| name | balance |
+-------+---------+
| amar | 6000 |
| annie | 8000 |
+-------+---------+
2 rows in set (0.00 sec)
mysql> select * from account where no=102;
+------+-------+---------+
| no | name | balance |
+------+-------+---------+
| 102 | annie | 8000 |
+------+-------+---------+
1 row in set (0.00 sec)
mysql> update account set balance=balance+2000;
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select *from account;
+------+-------+---------+
| no | name | balance |
+------+-------+---------+
| 101 | amar | 8000 |
| 102 | annie | 10000 |
+------+-------+---------+
2 rows in set (0.00 sec)
mysql> update account set balance=balance+5000 where no=102;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from account;
+------+-------+---------+
| no | name | balance |
+------+-------+---------+
| 101 | amar | 8000 |
| 102 | annie | 15000 |
+------+-------+---------+
2 rows in set (0.00 sec)
mysql> delete from account where no=102;
Query OK, 1 row affected (0.05 sec)
mysql> select *from account;
+------+------+---------+
| no | name | balance |
+------+------+---------+
| 101 | amar | 8000 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> delete from account;
Query OK, 1 row affected (0.03 sec)
mysql> select *from account;
Empty set (0.00 sec)
mysql> drop table account ;
Query OK, 0 rows affected (0.11 sec)
mysql> select *from account;
ERROR 1146 (42S02): Table 'student.account' doesn't exist
