ADD PRIMARY KEY AFTER CREATION OF TABLE
mysql> alter table client add primary key(cid);
RENAME A TABLE
mysql> alter table client add primary key(cid);
Query OK, 4 rows affected, 1 warning (0.15 sec)
Records: 4 Duplicates: 0 Warnings: 1
mysql> select * from client;
+-----+-------+-----------------+
| cid | cname | cmailid |
+-----+-------+-----------------+
| 0 | NULL | NULL |
| 100 | vivek | rvivekshiva@gma |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
+-----+-------+-----------------+
4 rows in set (0.00 sec)
DROP PRIMARY KEY
mysql> alter table client drop primary key;
Query OK, 4 rows affected (0.13 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from client;
+-----+-------+-----------------+
| cid | cname | cmailid |
+-----+-------+-----------------+
| 0 | NULL | NULL |
| 100 | VIVEK | rvivekshiva@gma |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
+-----+-------+-----------------+
4 rows in set (0.01 sec)
USING AFTER COMMAND:
mysql> alter table client add addr varchar(50) after cid;
Query OK, 4 rows affected (0.17 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from client;
+-----+------+-------+-----------------+
| cid | addr | cname | cmailid |
+-----+------+-------+-----------------+
| 0 | NULL | NULL | NULL |
| 100 | NULL | vivek | rvivekshiva@gma |
| 101 | NULL | raja | raja@gmail.com |
| 102 | NULL | raji | raji@gmail.com |
+-----+------+-------+-----------------+
4 rows in set (0.00 sec)
USING FIRST COMMAND:
mysql> alter table client add addr2 varchar(50) first;
Query OK, 4 rows affected (0.24 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from client;
+-------+-----+------+-------+-----------------+
| addr2 | cid | addr | cname | cmailid |
+-------+-----+------+-------+-----------------+
| NULL | 0 | NULL | NULL | NULL |
| NULL | 100 | NULL | VIVEK | rvivekshiva@gma |
| NULL | 101 | NULL | raja | raja@gmail.com |
| NULL | 102 | NULL | raji | raji@gmail.com |
+-------+-----+------+-------+-----------------+
4 rows in set (0.00 sec)
DROP COLUMN FROM A TABLE:
mysql> alter table client drop (column) addr;
Query OK, 4 rows affected (0.14 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> alter table client drop addr2;
Query OK, 4 rows affected (0.17 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from client;
+-----+-------+-----------------+
| cid | cname | cmailid |
+-----+-------+-----------------+
| 0 | NULL | NULL |
| 100 | vivek | rvivekshiva@gma |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
+-----+-------+-----------------+
4 rows in set (0.00 sec)
mysql> select * from client;
+-----+-------+-----------------+
| cid | cname | cmailid |
+-----+-------+-----------------+
| 0 | NULL | NULL |
| 100 | vivek | rvivekshiva@gma |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
+-----+-------+-----------------+
4 rows in set (0.07 sec)
CHANGE COLUMN NAME,TYPE AND SIZE
mysql> alter table client change cmailid cemailid varchar(50);
Query OK, 4 rows affected (0.19 sec)
Records: 4 Duplicates: 0 Warnings: 0
RENAME A TABLE
mysql> alter table client rename (to) client2;
Query OK, 0 rows affected (0.09 sec)
mysql> select * from client2;
+-----+---------+------------------------+
| cid | cname | cemailid |
+-----+---------+------------------------+
| 100 | vivek | rvivekshiva@y7mail.com |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 103 | karthik | kar@gmail.com |
| 104 | vicky | viki@gmail.com |
| 105 | vinoth | vin@gmail.com |
+-----+---------+------------------------+
6 rows in set (0.00 sec)
mysql> delete from client where cid=0;
Query OK, 1 row affected (0.05 sec)
mysql> select * from client;
+-----+-------+-----------------+
| cid | cname | cemailid |
+-----+-------+-----------------+
| 100 | vivek | rvivekshiva@gma |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
+-----+-------+-----------------+
3 rows in set (0.00 sec)
ADD PRIMARY KEY
mysql> create table aaa(no int primary key,name varchar(15));
mysql> create table aaa(no int primary key,name varchar(15));
Query OK, 0 rows affected (0.09 sec)
mysql> desc aaa;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no | int(11) | NO | PRI | NULL | |
| name | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
mysql> drop table aaa;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from aaa;
Empty set (0.00 sec)
mysql> desc aaa;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no | int(11) | NO | PRI | 0 | |
| name | varchar(15) | YES | | NULL | |
| mail | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into client(cid,cname,cemailid) values(103,"karthik","kar@gmail.co
m"),(104,"viki","viki@gmail.com");
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from client;
+-----+---------+-----------------+
| cid | cname | cemailid |
+-----+---------+-----------------+
| 100 | vivek | rvivekshiva@gma |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 103 | karthik | kar@gmail.com |
| 104 | viki | viki@gmail.com |
+-----+---------+-----------------+
5 rows in set (0.00 sec)
mysql> insert into client(cid,cname,cemailid) values(104,'vinoth','vin@gmail.com
');
Query OK, 1 row affected (0.03 sec)
mysql> select * from client;
+-----+---------+-----------------+
| cid | cname | cemailid |
+-----+---------+-----------------+
| 100 | vivek | rvivekshiva@gma |
| 101 | raja | raja@gmail.com |
| 102 | rajiv | rajiv@gmail.com |
| 103 | karthik | kar@gmail.com |
| 104 | viki | viki@gmail.com |
| 104 | vinoth | vin@gmail.com |
+-----+---------+-----------------+
6 rows in set (0.00 sec)
DELETE A PARTICULAR ROW
mysql> delete from client where cname="vinoth";
Query OK, 1 row affected (0.04 sec)
mysql> select * from client;
+-----+---------+-----------------+
| cid | cname | cemailid |
+-----+---------+-----------------+
| 100 | vivek | rvivekshiva@gma |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 103 | karthik | kar@gmail.com |
| 104 | viki | viki@gmail.com |
+-----+---------+-----------------+
5 rows in set (0.00 sec)
mysql> insert into client(cid,cname,cemailid) values(105,'vinoth','vin@gmail.com
');
Query OK, 1 row affected (0.01 sec)
mysql> select * from client;
+-----+---------+-----------------+
| cid | cname | cemailid |
+-----+---------+-----------------+
| 100 | vivek | rvivekshiva@gma |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 103 | karthik | kar@gmail.com |
| 104 | viki | viki@gmail.com |
| 105 | vinoth | vin@gmail.com |
+-----+---------+-----------------+
6 rows in set (0.00 sec)
INSERTION OF SPECIAL CHARACTER:
mysql> insert into client(cid,cname,cemailid) values(106,'\'swami\'','swami@gmail.com');
Query OK, 1 row affected (0.02 sec)
mysql> select * from client;
+-----+---------+-----------------+
| cid | cname | cemailid |
+-----+---------+-----------------+
| 100 | vivek | rvivekshiva@gma |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 103 | karthik | kar@gmail.com |
| 104 | viki | viki@gmail.com |
| 105 | vinoth | vin@gmail.com |
| 106 | 'swami' | swami@gmail.com |
+-----+---------+-----------------+
7 rows in set (0.48 sec)
mysql> select cid,cname from client where cid=103;
+-----+---------+
| cid | cname |
+-----+---------+
| 103 | karthik |
+-----+---------+
1 row in set (0.01 sec)
mysql> select cid,cname from client where cid=103 or cid=104;
+-----+---------+
| cid | cname |
+-----+---------+
| 103 | karthik |
| 104 | viki |
+-----+---------+
2 rows in set (0.00 sec)
CONCATENATION
mysql> select concat(cid,cemailid) as new from client;
+--------------------+
| new |
+--------------------+
| 100rvivekshiva@gma |
| 101raja@gmail.com |
| 102raji@gmail.com |
| 103kar@gmail.com |
| 104viki@gmail.com |
| 105vin@gmail.com |
+--------------------+
6 rows in set (0.02 sec)
mysql> select concat(cid,"|",cemailid) as new from client;
+---------------------+
| new |
+---------------------+
| 100|rvivekshiva@gma |
| 101|raja@gmail.com |
| 102|raji@gmail.com |
| 103|kar@gmail.com |
| 104|viki@gmail.com |
| 105|vin@gmail.com |
+---------------------+
6 rows in set (0.00 sec)
mysql> select concat(cid,"|",cemailid) as new1 from client;
+---------------------+
| new1 |
+---------------------+
| 100|rvivekshiva@gma |
| 101|raja@gmail.com |
| 102|raji@gmail.com |
| 103|kar@gmail.com |
| 104|viki@gmail.com |
| 105|vin@gmail.com |
+---------------------+
6 rows in set (0.00 sec)
mysql> select * from client;
+-----+---------+-----------------+
| cid | cname | cemailid |
+-----+---------+-----------------+
| 100 | vivek | rvivekshiva@gma |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 103 | karthik | kar@gmail.com |
| 104 | viki | viki@gmail.com |
| 105 | vinoth | vin@gmail.com |
+-----+---------+-----------------+
6 rows in set (0.00 sec)
CHANGE DATA
mysql> update client set cname="vicky" where cid=104;
(update bank set age=70,bage=100 where bid=102;)
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from client;
+-----+---------+-----------------+
| cid | cname | cemailid |
+-----+---------+-----------------+
| 100 | vivek | rvivekshiva@gma |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 103 | karthik | kar@gmail.com |
| 104 | vicky | viki@gmail.com |
| 105 | vinoth | vin@gmail.com |
+-----+---------+-----------------+
6 rows in set (0.00 sec)
mysql> update client set cemailid="rvivekshiva@gmail.com" where cid=100;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from client;
+-----+---------+-----------------------+
| cid | cname | cemailid |
+-----+---------+-----------------------+
| 100 | vivek | rvivekshiva@gmail.com |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 103 | karthik | kar@gmail.com |
| 104 | vicky | viki@gmail.com |
| 105 | vinoth | vin@gmail.com |
+-----+---------+-----------------------+
6 rows in set (0.00 sec)
mysql> update client set cid=cid+10;
Query OK, 6 rows affected (0.04 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> select * from client;
+-----+---------+-----------------------+
| cid | cname | cemailid |
+-----+---------+-----------------------+
| 110 | vivek | rvivekshiva@gmail.com |
| 111 | raja | raja@gmail.com |
| 112 | raji | raji@gmail.com |
| 113 | karthik | kar@gmail.com |
| 114 | vicky | viki@gmail.com |
| 115 | vinoth | vin@gmail.com |
+-----+---------+-----------------------+
6 rows in set (0.00 sec)
USING OF LIMIT COMMAND:
mysql> update client set cid=cid+10 limit 3;
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from client;
+-----+---------+-----------------------+
| cid | cname | cemailid |
+-----+---------+-----------------------+
| 120 | vivek | rvivekshiva@gmail.com |
| 121 | raja | raja@gmail.com |
| 122 | raji | raji@gmail.com |
| 113 | karthik | kar@gmail.com |
| 114 | vicky | viki@gmail.com |
| 115 | vinoth | vin@gmail.com |
+-----+---------+-----------------------+
6 rows in set (0.00 sec)
DELETE ROWS USING LIMIT:
mysql> delete from aaa1 limit 2;
Query OK, 2 rows affected (0.04 sec)
mysql> select * from aaa1;
+------+-------+-----------------+
| no | name | mail |
+------+-------+-----------------+
| 107 | bhara | bhara@gmail.com |
+------+-------+-----------------+
1 row in set (0.00 sec)
ORDER A TABLE AFTER CREATION:
mysql> alter table test2 order by no;
Query OK, 6 rows affected (0.17 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from test2;
+------+
| no |
+------+
| 2 |
| 12 |
| 22 |
| 32 |
| 42 |
| 52 |
+------+
6 rows in set (0.01 sec)
mysql> update client set cid=cid-10 limit 3;
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> update client set cid=cid-10;
Query OK, 6 rows affected (0.02 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> select * from client;
+-----+---------+-----------------------+
| cid | cname | cemailid |
+-----+---------+-----------------------+
| 100 | vivek | rvivekshiva@gmail.com |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 103 | karthik | kar@gmail.com |
| 104 | vicky | viki@gmail.com |
| 105 | vinoth | vin@gmail.com |
+-----+---------+-----------------------+
6 rows in set (0.00 sec)
mysql> select * from bank;
+------+-------+---------------+
| bid | bname | bmailid |
+------+-------+---------------+
| 100 | kvl | kvl@gmail.com |
| 101 | kmb | kmb@gmail.com |
| 102 | tnj | tnj@gmail.com |
| 103 | mtr | mtr@gmail.com |
+------+-------+---------------+
4 rows in set (0.14 sec)
mysql> select * from client;
+-----+---------+-----------------------+
| cid | cname | cemailid |
+-----+---------+-----------------------+
| 100 | vivek | rvivekshiva@gmail.com |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 103 | karthik | kar@gmail.com |
| 104 | vicky | viki@gmail.com |
| 105 | vinoth | vin@gmail.com |
+-----+---------+-----------------------+
6 rows in set (0.03 sec)
RENAME A TABLE
mysql> alter table aaa1 rename (to) test;
(alter table aaa1 rename to test;)
Query OK, 0 rows affected (0.07 sec)
mysql> show tables;
+-------------------------+
| Tables_in_bankingsystem |
+-------------------------+
| bank |
| client |
| test |
+-------------------------+
3 rows in set (0.00 sec)
mysql> select * from test;
+------+-------+-----------------+
| no | name | mail |
+------+-------+-----------------+
| 107 | bhara | bhara@gmail.com |
+------+-------+-----------------+
1 row in set (0.01 sec)
TRUNCATE A TABLE
mysql> truncate table test;
Query OK, 1 row affected (0.03 sec)
mysql> select * from test;
Empty set (0.00 sec)
TEMPORARY TABLE:
The temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be knows for temporary tables is that they will be deleted when the current client session terminates.
create global temporary table temp4 on commit delete rows as select * from bank;
create global temporary table temp2(no number,name varchar2(45))on commit delete rows;
The temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be knows for temporary tables is that they will be deleted when the current client session terminates.
create global temporary table temp4 on commit delete rows as select * from bank;
create global temporary table temp2(no number,name varchar2(45))on commit delete rows;
DELETE ROWS
Specify DELETE ROWS for a transaction-specific
temporary table (this is the default). Oracle will
truncate the table (delete all its rows) after each
commit.
PRESERVE ROWS
Specify PRESERVE ROWS for a session-specific temporary
table. Oracle will truncate the table (delete all its
rows) when you terminate the session.
mysql> create temporary table temp(no int,name varchar(15));
Query OK, 0 rows affected (0.04 sec)
mysql> select * from temp;
Empty set (0.00 sec)
mysql> insert into temp(no,name)values(1,"vivek");
Query OK, 1 row affected (0.05 sec)
mysql> select * from temp;
+------+-------+
| no | name |
+------+-------+
| 1 | vivek |
+------+-------+
1 row in set (0.00 sec)
mysql> show tables;
+-------------------------+
| Tables_in_bankingsystem |
+-------------------------+
| bank |
| client |
| test |
+-------------------------+
3 rows in set (0.00 sec)
mysql> create temporary table temp select * from bank;
Query OK, 4 rows affected (0.08 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from temp;
+------+-------+---------------+
| bid | bname | bmailid |
+------+-------+---------------+
| 100 | kvl | kvl@gmail.com |
| 101 | kmb | kmb@gmail.com |
| 102 | tnj | tnj@gmail.com |
| 103 | mtr | mtr@gmail.com |
+------+-------+---------------+
4 rows in set (0.00 sec)
mysql> drop temporary table temp;
Query OK, 0 rows affected (0.03 sec)
mysql> create temporary table temp select bid,bname from bank where bname="kmb";
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from temp;
+------+-------+
| bid | bname |
+------+-------+
| 101 | kmb |
+------+-------+
1 row in set (0.00 sec)
mysql> desc bank;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| bid | int(11) | YES | | NULL | |
| bname | varchar(15) | YES | | NULL | |
| bmailid | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
CHANGE COLUMN TYPE AND SIZE
mysql> alter table bank modify bname varchar(50);
Query OK, 4 rows affected (0.21 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc bank;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| bid | int(11) | YES | | NULL | |
| bname | varchar(50) | YES | | NULL | |
| bmailid | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
COPY A TABLE WHILE CREATING A NEW TABLE
mysql> create table simple select * from bank;
Query OK, 4 rows affected (0.11 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from simple;
+------+-------+---------------+
| bid | bname | bmailid |
+------+-------+---------------+
| 100 | kvl | kvl@gmail.com |
| 101 | kmb | kmb@gmail.com |
| 102 | tnj | tnj@gmail.com |
| 103 | mtr | mtr@gmail.com |
+------+-------+---------------+
4 rows in set (0.00 sec)
RENAME COLUMN WHILE PASTE TO NEW TABLE:
mysql> create table bank3 as select bid "first",bname "name" from bank;
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from bank3;
+-------+------+
| first | name |
+-------+------+
| 100 | kvl |
| 101 | kmb |
| 102 | tnj |
| 103 | mtr |
+-------+------+
4 rows in set (0.00 sec)
DROP COLUMN.
mysql> alter table simple drop (column) bmailid;
Query OK, 4 rows affected (0.25 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from simple;
+------+-------+
| bid | bname |
+------+-------+
| 100 | kvl |
| 101 | kmb |
| 102 | tnj |
| 103 | mtr |
+------+-------+
4 rows in set (0.01 sec)
USE OF UNION
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order
mysql> select cid,cname,cemailid from client union select bid,bname,bmailid from bank order by cid;
+------+---------+------------------------+
| cid | cname | cemailid |
+------+---------+------------------------+
| 100 | vivek | rvivekshiva@y7mail.com |
| 100 | kvl | kvl@gmail.com |
| 101 | kmb | kmb@gmail.com |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 102 | tnj | tnj@gmail.com |
| 103 | mtr | mtr@gmail.com |
| 103 | karthik | kar@gmail.com |
| 104 | vicky | viki@gmail.com |
| 105 | vinoth | vin@gmail.com |
+------+---------+------------------------+
10 rows in set (0.02 sec)
mysql> select cid,cname from client union select * from bank;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> select cid,cname from client union select bid,bname from bank;
+------+---------+
| cid | cname |
+------+---------+
| 100 | vivek |
| 111 | raja |
| 112 | raji |
| 113 | karthik |
| 114 | vicky |
| 116 | abi |
| 117 | abi1 |
| 118 | vivek20 |
| 100 | kvl |
| 101 | kmb |
| 102 | tnj |
| 103 | mtr |
+------+---------+
12 rows in set (0.00 sec)
mysql> show tables;
+-------------------------+
| Tables_in_bankingsystem |
+-------------------------+
| bank |
| client |
| simple |
| test |
| test1 |
| test2 |
+-------------------------+
6 rows in set (0.03 sec)
mysql> create table test4 select cid,cname,cemailid from client union select bid ,bname,bmailid from bank order by cname;
Query OK, 10 rows affected (0.13 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from test4;
+------+---------+------------------------+
| cid | cname | cemailid |
+------+---------+------------------------+
| 103 | karthik | kar@gmail.com |
| 101 | kmb | kmb@gmail.com |
| 100 | kvl | kvl@gmail.com |
| 103 | mtr | mtr@gmail.com |
| 101 | raja | raja@gmail.com |
| 102 | raji | raji@gmail.com |
| 102 | tnj | tnj@gmail.com |
| 104 | vicky | viki@gmail.com |
| 105 | vinoth | vin@gmail.com |
| 100 | vivek | rvivekshiva@y7mail.com |
+------+---------+------------------------+
10 rows in set (0.00 sec)
mysql> select * from test2;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
| 1 | Learn PHP | John Poul | 2007-05-24 |
| 2 | Learn MySQL | Abdul S | 2007-05-24 |
| 3 | JAVA Tutorial | Sanjay | 2007-05-06 |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)
mysql> select * from test3;
+-----------------+
| tutorial_author |
+-----------------+
| mahran |
| mahnaz |
| gen |
| jill |
| jhon paul |
| sanjay |
| Learn PHP |
| Learn MySQL |
| JAVA Tutorial |
+-----------------+
9 rows in set (0.00 sec)
mysql> select * from test1;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran | 20 |
| mahnaz | 40 |
| gen | 30 |
| jill | 20 |
| jhon paul | 1 |
| sanjay | 1 |
+-----------------+----------------+
6 rows in set (0.00 sec)
mysql> select * from test1;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran | 20 |
| mahnaz | 40 |
| gen | 30 |
| jill | 20 |
| jhon paul | 1 |
| sanjay | 1 |
+-----------------+----------------+
6 rows in set (0.00 sec)
mysql> select * from test2;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
| 1 | Learn PHP | John Poul | 2007-05-24 |
| 2 | Learn MySQL | Abdul S | 2007-05-24 |
| 3 | JAVA Tutorial | Sanjay | 2007-05-06 |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)
mysql> select a.tutorial_author FROM test1 a, test2 b WHERE a.tutorial_author =
b.tutorial_author;
+-----------------+
| tutorial_author |
+-----------------+
| sanjay |
+-----------------+
1 row in set (0.02 sec)
mysql> delete from test2 where tutorial_id=1;
Query OK, 1 row affected (0.06 sec)
No comments:
Post a Comment
Your comments and suggestions will help me to serve you better.