MYSQL:
InnoDB is a storage engine for MySQL, included as standard in all current binaries distributed by MySQL AB. Its main enhancement over other storage engines available for use with MySQL is ACID-compliant transaction support
MyISAM is the default storage engine for the MySQL relational database management system versions prior to 5.5 1. It is based on the older ISAM code but has many useful extensions. The major deficiency of MyISAM is the absence of transactions support. Versions of MySQL 5.5 and greater have switched to the InnoDB engine to ensure referential integrity constraints, and higher concurrency.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.51b-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
Mysql>create database bankingsystem;
Database created
mysql> use bankingsystem;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bankingsystem |
| internetmonitoring |
| mysql |
+--------------------+
4 rows in set (0.06 sec)
mysql> show tables;
+-------------------------+
| Tables_in_bankingsystem |
+-------------------------+
| aaa1 |
| bank
| client |
+-------------------------+
mysql> create table client(cid int,cname varchar(15),cmailid varchar(15));
Query OK, 0 rows affected (0.08 sec)
mysql> insert into client(cid,cname,cmailid) values("100","vivek","rvivekshiva@gmail.com");
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> insert into client(cid,cname,cmailid) values("101","karthik"," karthik @gmail.com
");
Query OK, 1 row affected (0.02 sec)
mysql> insert into client(cid,cname,cmailid) values("102","sindhu","sindhu@gmail.com
");
Query OK, 1 row affected (0.02 sec)
mysql> desc client;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| cid | int(11) | YES | | NULL | |
| cname | varchar(15) | YES | | NULL | |
| cmailid | varchar(15) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql> select * from client;
+------+-------+-----------------+
| cid | cname | cmailid |
+------+-------+-----------------+
| 100 | vivek | rvivekshiva@gma |
| 101 | karthik | karthik @gmail.com |
| 102 | sindhu | sindhu @gmail.com |
+------+-------+-----------------+
3 rows in set (0.00 sec)
Mysql>select * from bank where bid=100 and bname='kmu' or bid=105 and bname='kvi';
mysql> select cname from client where cid=100;
+-------+
| cname |
+-------+
| vivek |
+-------+
1 row in set (0.01 sec)
GET CONNECTION DETAILS:
mysql> status;
--------------
c:/wamp/bin/mysql/mysql5.0.51b/bin/mysql.exe Ver 14.12 Distrib 5.0.51b, for Win
32 (ia32)
Connection id: 1
Current database: bankingsystem
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.0.51b-community-nt MySQL Community Edition (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 6 min 18 sec
Threads: 1 Questions: 13 Slow queries: 0 Opens: 14 Flush tables: 1 Open tab
les: 7 Queries per second avg: 0.034
--------------
GET VERSION:
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.0.51b-community-nt |
+----------------------+
1 row in set (0.00 sec)
mysql> desc client;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| cid | int(11) | NO | MUL | 0 | |
| cname | varchar(15) | YES | | NULL | |
| cmailid | varchar(15) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
FIND KEYS IN A TABLE:
mysql> show keys from client;
+--------+------------+----------+--------------+-------------+-----------+-----
--------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Card
inality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-----
--------+----------+--------+------+------------+---------+
| client | 1 | cid | 1 | cid | A |
2 | NULL | NULL | | BTREE | |
+--------+------------+----------+--------------+-------------+-----------+-----
--------+----------+--------+------+------------+---------+
1 row in set (0.03 sec)
mysql> select cname from client where cmailid="rvivekshiva@gma";
+-------+
| cname |
+-------+
| vivek |
+-------+
1 row in set (0.00 sec)
mysql> show tables;
+-------------------------+
| Tables_in_bankingsystem |
+-------------------------+
| bank |
| client |
+-------------------------+
2 rows in set (0.00 sec)
ADD INDEX AFTER CREATION:
mysql> alter table client add index(cid);
Query OK, 3 rows affected (0.26 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from client;
+------+-------+-----------------+
| cid | cname | cmailid |
+------+-------+-----------------+
| 100 | vivek | rvivekshiva@gma |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
+------+-------+-----------------+
3 rows in set (0.00 sec)
mysql> delete from client where cid=100;
Query OK, 1 row affected (0.03 sec)
mysql> insert into client(cid,cname,cmailid) values("100","vivek","rvivekshiva@g
mail.com");
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from client;
+------+-------+-----------------+
| cid | cname | cmailid |
+------+-------+-----------------+
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 100 | vivek | rvivekshiva@gma |
+------+-------+-----------------+
3 rows in set (0.00 sec)
mysql> select * from client order by cid;
+------+-------+-----------------+
| cid | cname | cmailid |
+------+-------+-----------------+
| 100 | vivek | rvivekshiva@gmail.com |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
+------+-------+-----------------+
3 rows in set (0.00 sec)
mysql> select * from client;
+------+-------+-----------------+
| cid | cname | cmailid |
+------+-------+-----------------+
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 100 | vivek | rvivekshiva@gma |
+------+-------+-----------------+
3 rows in set (0.00 sec)
RENAME THE COLUMN TEMPORARILY:
mysql> select cid "vivek",cname "karht",cemailid "vicky" from client;
+-------+---------+------------------------+
| vivek | karht | vicky |
+-------+---------+------------------------+
| 100 | vivek | rvivekshiva@y7mail.com |
| 111 | raja | raja@gmail.com |
| 112 | raji | raji@gmail.com |
| 113 | karthik | kar@gmail.com |
| 114 | vicky | viki@gmail.com |
| 116 | abi | abi@y7mail.com |
| 117 | abi1 | abi1@y7mail.com |
| 118 | vivek20 | vivek20@gmail.com |
| 100 | kvl | kvl@gmail.com |
| 101 | kmb | kmb@gmail.com |
| 102 | tnj | tnj@gmail.com |
| 103 | mtr | mtr@gmail.com |
+-------+---------+------------------------+
12 rows in set (0.00 sec)
INSERTION OF COLUMN:
mysql> alter table client add (column) addr varchar(50);
Query OK, 3 rows affected (0.17 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into client(addr) values("kodavasal");
Query OK, 1 row affected (0.03 sec)
mysql> select * from client;
+------+-------+-----------------+-----------+
| cid | cname | cmailid | addr |
+------+-------+-----------------+-----------+
| 101 | raja | raja@gmail.com | NULL |
| 102 | raji | raji@gmail.com | NULL |
| 100 | vivek | rvivekshiva@gma | NULL |
| NULL | NULL | NULL | kodavasal |
+------+-------+-----------------+-----------+
4 rows in set (0.00 sec)
mysql> alter table client drop addr;
Query OK, 4 rows affected (0.18 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from client;
+------+-------+-----------------+
| cid | cname | cmailid |
+------+-------+-----------------+
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 100 | vivek | rvivekshiva@gma |
| NULL | NULL | NULL |
+------+-------+-----------------+
4 rows in set (0.00 sec)
Thank you so much vivek. Its so helpful for me to learn more about mysql.
ReplyDelete