NEW MYSQL
1. CREATING AND USING NEW DATABASE:
mysql> create database new;
Query OK, 1 row affected (0.03 sec)
mysql> use new;
Database changed
2. CREATING TABLE HEADINGS:
mysql> create table CLASS (ROLL_NO int primary key, NAME varchar(20), F_NAME varchar(20),
TRANSPORT varchar(20));
Query OK, 0 rows affected (0.07 sec)
3. INSERTING RECORDS:
mysql> insert into class values(1,'REYAN','RINOJ','SELF');
Query OK, 1 row affected (0.01 sec)
4. INSERTING COLUMN:
mysql> alter table CLASS add (ADDRESS varchar(20));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from CLASS;
+---------+---------+---------+-----------+---------+
| ROLL_NO | NAME | F_NAME | TRANSPORT | ADDRESS |
+---------+---------+---------+-----------+---------+
| 1 | REYAN | RINOJ | SELF | NULL |
| 2 | NAETHAN | JOSHUA | SELF | NULL |
| 3 | ASHISH | KRISHNA | SELF | NULL |
| 4 | LAX | BAS | SELF | NULL |
| 5 | LUCKY | PANKAJ | BUS | NULL |
+---------+---------+---------+-----------+---------+
5 rows in set (0.00 sec)
5. DELETING COLUMN:
mysql> alter table CLASS drop F_NAME;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from CLASS;
+---------+---------+-----------+---------+
| ROLL_NO | NAME | TRANSPORT | ADDRESS |
+---------+---------+-----------+---------+
| 1 | REYAN | SELF | NULL |
| 2 | NAETHAN | SELF | NULL |
| 3 | ASHISH | SELF | NULL |
| 4 | LAX | SELF | NULL |
| 5 | LUCKY | BUS | NULL |
+---------+---------+-----------+---------+
6. UPDATING RECORDS:
mysql> update CLASS set ADDRESS = 'SULUR' where ROLL_NO = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from CLASS;
+---------+---------+-----------+--------------+
| ROLL_NO | NAME | TRANSPORT | ADDRESS |
+---------+---------+-----------+--------------+
| 1 | REYAN | SELF | PARSN |
| 2 | NAETHAN | SELF | AAKASH HOMES |
| 3 | ASHISH | SELF | CASA GRANDE |
| 4 | LAX | SELF | SINGANALLUR |
| 5 | LUCKY | BUS | SULUR |
+---------+---------+-----------+--------------+
7. DELETING RECORDS:
mysql> delete from CLASS where roll_no = 5;
Query OK, 1 row affected (0.01 sec)
mysql> select * from CLASS;
+---------+---------+-----------+--------------+
| ROLL_NO | NAME | TRANSPORT | ADDRESS |
+---------+---------+-----------+--------------+
| 1 | REYAN | SELF | PARSN |
| 2 | NAETHAN | SELF | AAKASH HOMES |
| 3 | ASHISH | SELF | CASA GRANDE |
| 4 | LAX | SELF | SINGANALLUR |
+---------+---------+-----------+--------------+
4 rows in set (0.00 sec)
8. DISTINCT:
mysql> select distinct TRANSPORT from CLASS;
+-----------+
| TRANSPORT |
+-----------+
| SCHOOL |
| SELF |
+-----------+
2 rows in set (0.00 sec)
9. IN:
mysql> select * from CLASS where TRANSPORT in ('SCHOOL','PUBLIC');
+---------+-------+-----------+-------------+
| ROLL_NO | NAME | TRANSPORT | ADDRESS |
+---------+-------+-----------+-------------+
| 1 | REYAN | SCHOOL | PARSN |
| 4 | LAX | SCHOOL | SINGANALLUR |
+---------+-------+-----------+-------------+
2 rows in set (0.00 sec)
10. BETWEEN:
mysql> select NAME from CLASS where FEES between 10000 and 20000;
#BETWEEN INCLUDES BOTH UPPER AND LOWER VALUES
+---------+
| NAME |
+---------+
| REYAN |
| NAETHAN |
| ASHISH |
+---------+
3 rows in set (0.00 sec)
mysql> select NAME from CLASS where FEES>10000 and FEES<20000;
+---------+
| NAME |
+---------+
| NAETHAN |
+---------+
1 row in set (0.00 sec)
11. LIKE:
mysql> select ROLL_NO,NAME from CLASS where NAME like '%AN';
+---------+---------+
| ROLL_NO | NAME |
+---------+---------+
| 1 | REYAN |
| 2 | NAETHAN |
+---------+---------+
2 rows in set (0.00 sec)
LIKE Operator Description
WHERE Name LIKE ‘A%’ Finds values that start with “A”
WHERE Name LIKE ‘%s’ Finds values that end with “s”
WHERE Name LIKE ‘%or%’ Finds values that have “or” in any position
Finds values that have “a” in the second
WHERE Name LIKE ‘_a%’
position
Finds values that start with “a” and are at
WHERE Name LIKE ‘a_%’
least 2 characters in length
Finds values that start with “a” and are at
WHERE Name LIKE ‘a__%’
least 3 characters in length
Finds any values that start with “a” and ends
WHERE Name LIKE ‘a%s’
with “s”
12. IS NULL:
mysql> select * from CLASS where TRANSPORT is null;
+---------+---------+-----------+--------------+-------+
| ROLL_NO | NAME | TRANSPORT | ADDRESS | FEES |
+---------+---------+-----------+--------------+-------+
| 2 | NAETHAN | NULL | AAKASH HOMES | 15000 |
+---------+---------+-----------+--------------+-------+
1 row in set (0.00 sec)
13. ORDER BY:
mysql> select * from CLASS order by NAME;
+---------+---------+-----------+--------------+-------+
| ROLL_NO | NAME | TRANSPORT | ADDRESS | FEES |
+---------+---------+-----------+--------------+-------+
| 3 | ASHISH | SELF | CASA GRANDE | 20000 |
| 4 | LAX | SCHOOL | SINGANALLUR | 25000 |
| 2 | NAETHAN | NULL | AAKASH HOMES | 15000 |
| 1 | REYAN | SCHOOL | PARSN | 10000 |
+---------+---------+-----------+--------------+-------+
4 rows in set (0.00 sec)
14. GROUP BY:
mysql> select count(*) as STUDENT_NO,TRANSPORT from CLASS group by TRANSPORT;
+------------+-----------+
| STUDENT_NO | TRANSPORT |
+------------+-----------+
| 2 | SCHOOL |
| 1 | NULL |
| 1 | SELF |
+------------+-----------+
3 rows in set (0.00 sec)
15. SUM():
mysql> select sum(fees) as TOTAL_MONEY_FROM_CLASS from CLASS;
+------------------------+
| TOTAL_MONEY_FROM_CLASS |
+------------------------+
| 70000 |
+------------------------+
1 row in set (0.00 sec)
16. AVG():
#RETURNS AS FLOAT VALUE
mysql> select avg(fees) as AVG_MONEY_FROM_CLASS from CLASS;
+----------------------+
| AVG_MONEY_FROM_CLASS |
+----------------------+
| 17500.0000 |
+----------------------+
1 row in set (0.00 sec)
17. MIN():
mysql> select min(fees) from CLASS;
+-----------+
| min(fees) |
+-----------+
| 10000 |
+-----------+
1 row in set (0.00 sec)
18. MAX():
mysql> select max(fees) from CLASS;
+-----------+
| max(fees) |
+-----------+
| 25000 |
+-----------+
1 row in set (0.00 sec)
TABLE1: CLASS-
+---------+---------+-----------+--------------+-------+
| ROLL_NO | NAME | TRANSPORT | ADDRESS | FEES |
+---------+---------+-----------+--------------+-------+
| 1 | REYAN | SCHOOL | PARSN | 10000 |
| 2 | NAETHAN | NULL | AAKASH HOMES | 15000 |
| 3 | ASHISH | SELF | CASA GRANDE | 20000 |
| 4 | LAX | SCHOOL | SINGANALLUR | 25000 |
+---------+---------+-----------+--------------+-------+
TABLE2: MARKS-
+---------+---------+------+------+------+
| ROLL_NO | NAME | PHY | MATH | CHEM |
+---------+---------+------+------+------+
| 1 | REYAN | 97 | 98 | 96 |
| 2 | NAETHAN | 97 | 93 | 96 |
| 3 | ASHISH | 99 | 95 | 96 |
| 4 | LAX | 99 | 99 | 99 |
+---------+---------+------+------+------+
19. NATURAL JOIN:
mysql> select * from CLASS natural join MARKS;
+---------+---------+-----------+--------------+-------+------+------+------+
| ROLL_NO | NAME | TRANSPORT | ADDRESS | FEES | PHY | MATH | CHEM |
+---------+---------+-----------+--------------+-------+------+------+------+
| 1 | REYAN | SCHOOL | PARSN | 10000 | 97 | 98 | 96 |
| 2 | NAETHAN | NULL | AAKASH HOMES | 15000 | 97 | 93 | 96 |
| 3 | ASHISH | SELF | CASA GRANDE | 20000 | 99 | 95 | 96 |
| 4 | LAX | SCHOOL | SINGANALLUR | 25000 | 99 | 99 | 99 |
+---------+---------+-----------+--------------+-------+------+------+------+
4 rows in set (0.00 sec)
20. EQUIJOIN:
mysql> select [Link],PHY,CHEM,MATH,ADDRESS from CLASS,MARKS where
[Link]=[Link];
+---------+------+------+------+--------------+
| NAME | PHY | CHEM | MATH | ADDRESS |
+---------+------+------+------+--------------+
| REYAN | 97 | 96 | 98 | PARSN |
| NAETHAN | 97 | 96 | 93 | AAKASH HOMES |
| ASHISH | 99 | 96 | 95 | CASA GRANDE |
+---------+------+------+------+--------------+
3 rows in set (0.00 sec)