Essential

SELECT count(receiptNo),sum(total) FROM stdsalary WHERE receiptNo BETWEEN 235651 AND 235700;

SELECT SN, date,COUNT(receiptNo),SUM(current),SUM(dues),SUM(advance),SUM(red_crecent),
SUM(guide),SUM(examFee), SUM(others),SUM(total) FROM stdsalary where date='28/06/2018';

select count(others) from stdsalary where others=30;
select roll,others from stdsalary where others=30;
select roll,others from stdsalary where others=30 AND date='25/06/2018';
select roll,examFee from stdsalary where examFee=500;
select count(examFee) from stdsalary where examFee=500;
select roll,examFee from stdsalary where examFee=500 AND date='25/06/2018';
select count(examFee) from stdsalary where date='27/06/2018';

update stdsalary
set receiptNo=119834
where SN=432;

বিভিন্ন তারিখে মোট আদায়

SELECT SN, date, SUM(total),COUNT(receiptNo) FROM stdsalary group by date order by date asc;

When admission going on:

ভর্তির সময় নির্দিষ্ট তারিখে বিভিন্ন খাতে মোট আদায়।

SELECT SN, date,COUNT(receiptNo),SUM(current),SUM(ccc),SUM(school),SUM(red_crecent),
SUM(guide), SUM(others),SUM(total)
FROM stdsalary
where date='23/01/2018';

Every salary date:

বেতনের তারিখে বিভিন্ন খাতে মোট আদায়

SELECT SN, date,COUNT(receiptNo),SUM(current),SUM(dues),SUM(advance),SUM(red_crecent),
SUM(guide), SUM(others),SUM(re_admission),SUM(total) FROM stdsalary where date='10/05/2018';

SELECT * FROM stdsalary where others=25;

SELECT COUNT(*),SUM(others) FROM `stdsalary` WHERE others=25;

SELECT * FROM stdsalary where re_admission=350;

SELECT COUNT(*),sum(re_admission) FROM stdsalary where re_admission=350;

SELECT COUNT(*),SUM(current),SUM(ccc),SUM(school),SUM(red_crecent),
SUM(guide), SUM(others),SUM(total) FROM stdsalary;

এ পর্যন্ত মোট আদায়

SELECT COUNT(receiptNo),SUM(current),SUM(ccc),SUM(school),SUM(red_crecent),
SUM(guide), SUM(others),SUM(total) FROM stdsalary;

CHAR ... ... ... String, length 0 - 255
VARCHAR ... .... String, length 0 - 255
TINYTEXT ... ... String, length 0 - 255
TEXT ... ... ... String, length 0 - 65535
BLOB ... ... ... String, length 0 - 65535
MEDIUMTEXT ... . String, length 0 - 16777215
MEDIUMBLOB ... . String, length 0 - 16777215
LONGTEXT ... ... String, length 0 - 4294967295
LONGBLOB ... ... String, length 0 - 4294967295
* TINYINT ... .. Integer, -128 to 127
* SMALLINT ... . Integer, -32768 to 32767
* MEDIUMINT ... Integer, -8388608 to 8388607
* INT ... ... .. Integer, -2147483648 to 2147483647
* BIGINT ... ... Int, -9223372036854775808 to 9223372036854775807
FLOAT ... ... .. Decimal (precise to 23 digits)
DOUBLE ... ... . Decimal (24 to 53 digits)
DECIMAL ... ... "DOUBLE" stored as string
DATE ... ... ... YYYY-MM-DD
DATETIME ... ... YYYY-MM-DD HH:MM:SS
TIMESTAMP ... .. YYYYMMDDHHMMSS
TIME ... ... ... HH:MM:SS
ENUM ... ... ... One of preset options
SET ... ... ... Selection of preset options

* Note: "UNSIGNED" TINYINT, SMALLINT,
MEDIUMINT, INT, BIGINT have the same
range of values but start at 0, e.g. TINYINT
UNSIGNED is between 0 and 255.

MYSQL FUNCTIOS

mysql_affected_rows ; || mysql_close;|| mysql_connect;|| mysql_data_seek;|| mysql_db_name
mysql_errno;|| mysql_error;|| mysql_fetch_array;|| mysql_fetch_assoc;|| mysql_fetch_field
mysql_fetch_lengths;|| mysql_fetch_object;|| mysql_fetch_row;|| mysql_field_flags;|| mysql_field_len
mysql_field_name;|| mysql_field_seek;|| mysql_field_table;|| mysql_field_type;|| mysql_free_result
mysql_insert_id;|| mysql_list_dbs;|| mysql_list_processes;|| mysql_list_tables;|| mysql_num_fields
mysql_num_rows;|| mysql_pconnect;|| mysql_query;|| mysql_real_escape_string;|| mysql_select_db

Mathematical

ABS, SIGN, MOD, FLOOR, CEILING, ROUND, DIV, EXP, LN, LOG, LOG2, LOG10, POW, POWER, SQRT, PI, COS, SIN, TAN, ACOS, ASIN, ATAN, ATAN2, COT, RAND, LEAST, COUNT GREATEST, DEGREES, RADIANS, TRUNCATE, Date and Time, DAYOFWEEK, WEEKDAY, DAYOFMONTH, DAYOFYEAR, MONTH, DAYNAME, MONTHNAME, QUARTER, WEEK, YEAR, YEARWEEK, HOUR, MINUTE, SECOND, PERIOD_ADD, PERIOD_DIFF, DATE_ADD, DATE_SUB, ADDDATE, SUBDATE, EXTRACT, TO_DAYS, FROM_DAYS, DATE_FORMAT, TIME_FORMAT, CURRENT_DATE, AVG, CURRENT_TIME, NOW, SYSDATE, UNIX_TIMESTAMP, FROM_UNIXTIME, SEC_TO_TIME, TIME_TO_SEC, Group, MIN, MAX, SUM, GROUP_CONCAT, VARIANCE, STD, STDDEV, BIT_OR, BIT_AND,

Control Flow,IFNULL, NULLIF, IF, String, ASCII, ORD, CONV, CHAR, CONCAT, CONCAT_WS, LENGTH, CHAR_LENGTH, BIT_LENGTH, LOCATE, INSTR, RIGHT, SUBSTRING, MID, SUBSTRING_INDEX, LTRIM, RTRIM, TRIM, SOUNDEX, REPLACE, LEFT, HEX, REPEAT, REVERSE, INSERT, ELT, FIELD, LCASE, OCT, RPAD, UCASE, LOAD_FILE, QUOTE, Comparison, STRCMP, CONVERT, Other, BIT_COUNT, DATABASE, USER, SYSTEM_USER, SESSION_USER, CURRENT_USER, PASSWORD, OLD_PASSWORD, DECODE, MD5, SHA1 BIN, LPAD, SPACE, AES_ENCRYPT, AES_DECRYPT, DES_ENCRYPT, DES_DECRYPT, LAST_INSERT_ID, Cast, CAST, FORMAT, VERSION, CONNECTION_ID, GET_LOCK, RELEASE_LOCK, IS_FREE_LOCK, BENCHMARK, INET_NTOA, INET_ATON, FOUND_ROWS

SHOW

show databases; (সকল ডেটাবেজ দেখতে)

USE

USE firozshah;(ফিরোজশাহ্‌ নামক ডেটাবেজ এ ঢুকতে)
show tables; (সকল টেবিল দেখতে)

DESCRIBE

describe stdsalary; (টেবিলের ষ্ট্রাকচার দেখতে)

CREATE

নতুন ডেটাবেজ তৈরীতে-
CREATE DATABASE test; (টেষ্ট নামক ডেটাবেজ তৈরীতে)(
কাষ্টমার নামক নতুন টেবিল তৈরীতে
CREATE TABLE customer(
id int(11) not null auto_increment primary key,
firstName varchar(255),
lastName varchar(255),
address varchar(255),
email varchar(255),
city varchar(255),
division varchar(255));
,


সেল্‌স নামক নতুন টেবিল তৈরীতে
CREATE TABLE sales(
id int(11) not null auto_increment primary key,
billNumber int(11),
date varchar(15),
item varchar(25),
quantity int(11),
rate double(11,2),
paid varchar(15),
customerId varchar(15));

INSERT

কাষ্টমার টেবিলে ডেটা ঢুকাতে
INSERT INTO `customer`
(`id`, `firstName`, `lastName`, `address`, `email`, `city`, `division`) VALUES
(1001,'Abdul','Karim','Shekh Para','mdkarim66@yahoo.com','Rohanpur','Rajshahi');

একই টেবিলে একাধিক ডেটা ঢুকাতে
INSERT INTO `customer`
(`firstName`, `lastName`, `address`, `email`, `city`, `division`)
VALUES
('Farid','Sarkar','Mollique Pur','farid26@yahoo.com','Sonaichondi','Rajshahi'),
('Abdur','Rahim','Shekh Para','rahim420@gmail.com','Rohanpur','Rajshahi'),
('Rahima','Khatun','Parisho','rahima6@yahoo.com','Tanore','Rajshahi'),
('Ayesha','Pervin','Ghatfarhadbeg','ayesha@yahoo.com','Andarkillah','Chittagong');
সেলস টেবিলে ডেটা ঢুকাতে
insert into sales
(billNumber,date,item,quantity,rate,paid,customerId)
values
('101','11/12/99','HDD','7','5500','Yes','c1003');



insert into sales
(billNumber,date,item,quantity,rate,paid,customerId)
values
('102','11/12/99','HDD','10','5500','Yes','c1003'),
('103','11/12/99','Monitor','11','8500','Yes','c1003'),
('104','11/12/99','HDD','9','500','No','c1003'),
('105','11/12/99','Loud Speaker','8','1500','Yes','c1003'),
('106','11/12/99','HDD','4','5500','Yes','c1001'),
('107','11/12/99','HDD','6','5500','Yes','c1002'),
('108','11/12/99','Key Board','3','500','Yes','c1006'),
('109','11/12/99','HDD','9','5500','Yes','c1004'),
('110','11/12/99','Monitor','2','8500','No','c1005'),
('111','11/12/99','HDD','1','5500','Yes','c1007'),
('112','11/12/99','CD Rom','5','3500','Yes','c1002'),
('113','11/12/99','Casing','12','2500','No','c1006'),
('114','11/12/99','Mouse','45','300','Yes','c1003'),
('115','11/12/99','HDD','23','5500','Yes','c1004'),
('116','11/12/99','HDD','21','5500','No','c1001'),
('117','11/12/99','CD Romm','14','2500','Yes','c1005'),
('118','11/12/99','HDD','12','5500','Yes','c1008'),
('119','11/12/99','Pendrive','7','500','No','c1007'),
('120','11/12/99','HDD','4','5500','Yes','c1003');

USE / DESCRIBE

SHOW DATABASES; (সকল ডেটাবেজ দেখতে) USE test; (টেষ্ট নামক ডেটাবেজে ঢুকতে) DESCRIBE customer; (কাষ্টমার টেবিলের ষ্ট্রাকচার দেখতে)
SELECT * FROM customer; (কাষ্টমার টেবিলের সকল ডেটা দেখতে)
select * from stdsalary where SN in (1,2); (শুধুমাত্র টেবিলের প্রথম দুই সারি দেখতে)

UPPER & lower Function

1. SELECT UPPER('i am md. abdul karim');

2. SELECT UPPER(name)FORM student;

3. select upper(name) as stdName from student;

4. SELECT LOWER('I AM MD. ABDUL KARIM');
5. SELECT LOWER(name)FORM student;

6. select lower(name) as stdName from student;

WHERE

SELECT * FROM tablename WHERE condition
# Returns rows that match condition

select * from stdsalary; (টেবিলের সকল কলাম দেখতে)
select * from stdsalary where section in ('science','arts'); (শুধুমাত্র মানবিক ও বিজ্ঞান বিভাগের শিক্ষার্থীদের দেখতে)
select * from stdsalary where SN in (1,2); (শুধুমাত্র টেবিলের প্রথম দুই সারি দেখতে)

DROP/DELETE

1. DROP DATABASE valvee; (ডেটাবেজ মুছতে)
2. DROP TABLE student; (টেবিল মুছতে)
3. delete from student where roll=102; (ষ্টুডেন্ট টেবিলের রোল ১০২ এর সারি মুছতে)
4. DELETE FROM customers where id=1; (কাষ্টমার টেবিলের আইডি ১ এর সারি মুছতে)
5. DELETE FROM customers where id>3; (কাষ্টমার টেবিলের আইডি ৩ এর সারি মুছতে)
6. ALTER TABLE student DROP COLUMN city;(ষ্টুডেন্ট টেবিলেভ সিটি কলাম মুছতে)
7. ALTER TABLE student DROP COLUMN gpa; (ষ্টুডেন্ট টেবিলেভ জি,পি,এ কলাম মুছতে)

TRUNCATE

টেবল রেখে সকল সারি মুছতে-
1. TRUNCATE customer;(customer টেবল থাকবে সকল column থাকবে কিন্তু সকল row মুছে যাবে)
2. TRUNCATE student; (student টেবল থাকবে সকল column থাকবে কিন্তু সকল row মুছে যাবে)

UPDATE / ALTER / MODIFY COLUMN / ADD COLUMN

ALTER TABLE table_name
ADD column_name datatype
or
ALTER TABLE table_name
DROP COLUMN column_name
1. alter table student
change dept phone varchar(20);(ষ্টুডেন্ট টেবিলের dept field কে phone ফিল্ডে রূপান্তর করতে)

2. ALTER TABLE student
ADD COLUMN dept varchar(15);

3. ALTER TABLE customer
change gender phone varchar(20);

4.ALTER TABLE customer
change phone gender varchar(20);

5. ALTER TABLE customer DROP COLUMN newCol; (কাষ্টমার টেবিলের নিউকল কলাম মুছতে)

6. alter TABLE student drop column phone;(ষ্টুডেন্ট টেবিলের ফোন কলাম মুছতে)

টেবিলে নতুন কলাম যোগ করতে
7. ALTER TABLE customer
ADD COLUMN salary double(11,2);

8. update customers set salary=salary+500 where salary>5000;

9. update customers set salary=salary-500 where salary>5000;

10. update student_view
set name='Rohima',gender='Female'
where roll=101;

11. UPDATE customer SET salary =44000 WHERE id= 1001;

12. UPDATE customer set gender='Male' where id=1001;

13. UPDATE stdsalary set re_admission=350 where SN=420;

MODIFY:-
(কাষ্টমার টেবিলের salary কলামের ফিল্ড পরিবর্তন করতে-)
14. ALTER TABLE customer
MODIFY COLUMN salary double(11,2);

15.DELETE FROM `customer` WHERE id>1005; (কাষ্টমার টেবিলের আইডি ১০৫ এর উপরের সারি মুছতে)

SELECT

SELECT * FROM tablename
# Returns all columns
SELECT column FROM tablename
# Returns specific column
1. select stdName from stdsalary;

2. select SN,stdName,section,roll from stdsalary;

SELECT * FROM tablename
# Returns all columns
3. select * from stdsalary;
SELECT column FROM tablename
# Returns specific column

4. SELECT * FROM stdsalary WHERE SN=5;

order by

SELECT * FROM table ORDER BY column
# Return all rows ordering by column

1. select * from stdsalary where others=25 order by roll asc;
2. select * from stdsalary where guide=35 order by roll asc;

DISTINCT

SELECT DISTINCT column FROM tablename
# Returns unique values of column

টেবিলের কোন কলামের কী, কী ফিল্ড আছে তা জানতে
1. SELECT DISTINCT column FROM tablename
# Returns unique values of column
2. SELECT DISTINCT section from stdsalary;
3. SELECT DISTINCT stdName,section from stdsalary where 1;
4. SELECT DISTINCT stdName,section,roll from stdsalary where 2;
5. SELECT DISTINCT date,stdName,section,roll from stdsalary where 2;
6. SELECT DISTINCT others FROM stdsalary;

LIMIT

SELECT * FROM table LIMIT 10, 20
# Return first 20 rows after row 10

টেবিলের প্রথম ৫টি সারি দেখতে

1. SELECT * FROM stdsalary LIMIT 5;
টেবিলে প্রথম ২ সারি বাদ দিয়ে পরবর্তি ৫টি সারি দেখতে
2. SELECT * FROM stdsalary LIMIT 2,5;
3. SELECT * FROM stdsalary where 1 order by rand()LIMIT 10;

CONCAT

1. SELECT CONCAT('i am md. abdul karim','I am fine');

2. select concat(name,' is',age,' years old.') from student;

3. select concat(name,' is',age,' years old.') as Larners from student;

Math(ARITHMAITC OPERATOR/GREATEST/LEAST/POW/LOG/TRUNCATE/RAND/EXP)

1. SELECT 5+2;
2. SELECT 5-2;
3. SELECT 5*2;
4. SELECT 5/2;
5. SELECT 9%2;

1. SELECT GREATEST(5,50,2,30,75,25,36);
2. SELECT LEAST(5,50,2,30,-75,25,36);
3. select POW(2,3);
4. select LOG(1);
5. select LOG10(10);
6. select TRUNCATE(10.2596542,2);
7. select TRUNCATE(LOG(2),5);
8. select RAND();
9. select EXP(3);

COUNT

SELECT COUNT(*) FROM tablename
# Returns number of rows

1. SELECT COUNT(*) FROM stdsalary;
2. SELECT COUNT(*) FROM stdsalary where others=200;
3. SELECT COUNT(*) FROM stdsalary where others=25;
4. SELECT COUNT(*) FROM stdsalary where section in ('science','arts');
5. SELECT COUNT(*) FROM stdsalary where section in ('science','arts','commerce');
6. SELECT COUNT(*) FROM stdsalary where guide=35; 7. SELECT COUNT(*) FROM stdsalary where red_crecent=15;

Maximum and Minimum

SELECT max(column) AS alias
# Return maxium value in column as "alias"

1. select max(others) from stdsalary;
2. select max(total) from stdsalary;
3. select min(total) from stdsalary;

SUM

SELECT SUM(column) FROM tablename
# Returns sum of column

1. SELECT SUM(guide) FROM stdsalary;
2. SELECT SUM(total) FROM stdsalary;
3. SELECT SUM(total) FROM stdsalary where date='16/04/2018';
4. SELECT SUM(others) FROM stdsalary where others=25;
5. SELECT SUM(others) FROM stdsalary where others=200;

Sub Querry(Average)

SELECT SUM(column) FROM tablename
# Returns sum of column

1. SELECT AVG(salary) FROM `teacher`;
2. SELECT * FROM `teacher` WHERE salary>33318.181818;

4. SELECT * FROM `teacher` WHERE salary>(select avg(salary)from teacher);
5. SELECT * FROM `teacher` WHERE salary<(select avg(salary)from teacher);

Ascending and Descending

1. select stdName from stdsalary order by stdName asc;
2. select stdName from stdsalary order by stdName desc;
3. select stdName,section, roll from stdsalary order by stdName desc;
4. select stdName,section, roll from stdsalary order by stdName;
5. select SN,stdName,section, roll from stdsalary order by stdName desc limit 2,10;
6. select SN,stdName,section, roll from stdsalary order by stdName asc limit 250,20;
7. select SN,date,stdName,section, roll from stdsalary order by SN;

Between

SELECT column_name(s) FROM table_name
WHERE condition
AND|OR condition

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
1. SELECT * FROM stdsalary WHERE others BETWEEN 20 AND 30;
2. SELECT * FROM stdsalary WHERE others BETWEEN 175 AND 225;
3. SELECT COUNT(*) FROM stdsalary WHERE others BETWEEN 20 AND 30;
4. SELECT COUNT(*) FROM stdsalary WHERE others BETWEEN 175 AND 225;

LIKE (Underscore single or multiple)

1. select * from student where name like '_a%';
2. select * from student where name like '__a%';
3. select * from student where name like '___J%';
4. select * from student where name like '%n_';
সেকসন ফিল্ডে যে সকল নাম sc দিয়ে শুরু-
5. SELECT * FROM stdsalary WHERE section LIKE "sc%";
সেকসন ফিল্ডে যে সকল নাম sc দিয়ে শুরু তাদের রোল ক্রমানুসারে সাজাবে-
6. SELECT * FROM stdsalary WHERE section LIKE"sc%" ORDER BY roll ASC;
7. stdName ফিল্ডে যে সব নাম Me দিয়ে শুরু হয়েছে-
8. SELECT * FROM stdsalary WHERE stdName LIKE"Me%";
যে সব নামের বানানে মাঝে kh আছে-
9. SELECT * FROM stdsalary WHERE stdName LIKE"%kh%";
10. SELECT * FROM stdsalary WHERE stdName LIKE"%Akter";
11. SELECT * FROM stdsalary WHERE stdName NOT LIKE"%Gupta%";
12. SELECT * FROM stdsalary WHERE stdName LIKE"%marma%";
13. select * from student where name like 's%';
14. select * from student where name like '%s';
15. select * from student where name like '%a';
16. select * from student where name like '%hi%';

RLIKE

1. SELECT * FROM `stdsalary`where stdName rlike('kazi');
2. SELECT * FROM `stdsalary`where stdName rlike('kazi') AND stdName rlike('sabrina');
3. SELECT * FROM `stdsalary`where stdName rlike('kazi') or stdName rlike('bibi')and stdName rlike('amena');
4. SELECT * FROM `stdsalary`where stdName rlike('khatun') or (stdName rlike('kazi')and stdName rlike('sabrina'))
5. SELECT * FROM `stdsalary`where stdName rlike('kazi') or (stdName rlike('bibi') and StdName rlike('akter'))
6. SELECT * FROM `stdsalary`where stdName rlike('kazi') and stdName rlike('bibi') or StdName rlike('akter')
7. SELECT * FROM `stdsalary`where stdName rlike('kazi') or stdName rlike('bibi');
8. SELECT count(*) FROM `stdsalary`where stdName rlike ('khatun')
9. SELECT count(*) FROM `stdsalary`where stdName rlike ('khatun') or stdName rlike('begum')
10. SELECT count(*) FROM `stdsalary`where stdName rlike ('khatun') and stdName rlike('begum')
11. SELECT count(*) FROM `stdsalary`where stdName rlike('kazi') or stdName rlike('bibi') and StdName rlike('akter');
12. SELECT count(*) FROM `stdsalary`where stdName rlike('kazi') and stdName rlike('bibi') or StdName rlike('akter');

IN

1. SELECT * FROM stdsalary WHERE SN in (1,2);
2. SELECT * FROM stdsalary WHERE SN in (1,2,3,4,5,6,7,8,9,10)order by SN asc;
3. SELECT * FROM stdsalary WHERE SN in (1,2,3,4,5,6,7,8,9,10)order by SN desc;
4. SELECT * FROM stdsalary WHERE section IN('science');
5. SELECT * FROM stdsalary WHERE section IN('science','Arts');
6. SELECT * FROM stdsalary WHERE section NOT IN('science','Arts');
7. SELECT * FROM stdsalary WHERE section NOT IN('commerce');
8. SELECT * FROM stdsalary WHERE section in ('science','arts');
9. SELECT * FROM stdsalary WHERE section not in ('science','arts');
10. SELECT * FROM stdsalary WHERE section in ('science','arts','commerce')order by roll asc;

Group by

whereএর আগে group, orderএর পরে group
1. SELECT date, SUM(total) from stdsalary group by date;
2. SELECT date, SUM(total) from stdsalary group by date order by sum(total) desc;
3. SELECT date, SUM(total) from stdsalary group by date order by sum(total) asc;
4. SELECT date, SUM(total) from stdsalary group by date order by date asc;
5. SELECT date, SUM(total) from stdsalary group by date order by SN asc;
6. SELECT SN, date, SUM(total) from stdsalary group by date order by SN asc;

As

1. SELECT roll as ID, stdName as 'First Name' from stdsalary;
2. SELECT section as ID, stdName as 'First Name' from stdsalary;
3. SELECT section as ID, stdName as 'First Name' from stdsalary where SN order by SN asc;

JOINING TABLE

SELECT table1.* FROM table1 INNER JOIN table2 on table1.id = table2.id
# Only return columns from table1

SELECT LAST_INSERT_ID() as new_id
# Returns ID of last created row

SELECT * FROM table1 INNER JOIN table2 on table1.id = table2.id
# Join two tables, return all columns

দুই বা ততোধিক টেবলকে একত্র করে রেকর্ড খুজে বের করা।
roll ম্যাচ করে দুইটি টেবল জয়েন করা
1. SELECT student.roll,reg_Number, name, gender,group_Name, gpa
from student, result
where student.roll=result.roll;

2. SELECT result.roll,reg_Number, name, gender,group_Name, gpa
from student, result
where student.roll=result.roll;

3. SELECT std.roll,rslt.reg_Number, std.name, std.gender,rslt.group_Name, rslt.gpa
from student as std, result as rslt
where std.roll=rslt.roll;

4. SELECT std.roll,rslt.reg_Number, std.name, std.gender,rslt.group_Name, rslt.gpa
from student as std JOIN result as rslt
ON std.roll=rslt.roll;

INNER JOINING

SELECT * FROM tablename WHERE BINARY condition
# Condition is case-sensitive

1. SELECT std.roll,rslt.reg_Number, std.name, std.gender,rslt.group_Name, rslt.gpa,rslt.group_Name
from student as std, result as rslt
where std.roll=rslt.roll;

2. SELECT std.roll,rslt.reg_Number, std.name, std.gender,rslt.group_Name, rslt.gpa,rslt.group_Name
from student as std JOIN result as rslt
ON std.roll=rslt.roll;

3. SELECT std.roll,rslt.reg_Number, std.name, std.gender,rslt.group_Name, rslt.gpa,rslt.group_Name
from student as std INNER JOIN result as rslt
ON std.roll=rslt.roll;

LEFT JOINING

1. SELECT std.roll,rslt.reg_Number, std.name, std.gender,rslt.group_Name, rslt.gpa,rslt.group_Name
from student as std LEFT JOIN result as rslt
ON std.roll=rslt.roll;

RIGHT JOINING

1.SELECT std.roll,rslt.reg_Number, std.name, std.gender,rslt.group_Name, rslt.gpa,rslt.group_Name
from student as std RIGHT JOIN result as rslt
ON std.roll=rslt.roll;

UNION

1. SELECT roll,name,gender
FROM sylhet_tour
UNION
SELECT roll,name,gender
FROM Dhaka_tour;

VIRTUAL TABLE(VIEW)

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

SELECT column_name AS column_alias
FROM table_name
or
SELECT column_name
FROM table_name AS table_alias

1. CREATE VIEW student_view AS
SELECT roll,name,gender
FROM student;
2. SELECT * FROM student_view;
3. SELECT roll,name FROM `student_view`;
4. UPDATE `student_view`
SET `name`='Farjana'
WHERE roll=103;
5. INSERT INTO student_view(roll,name,gender)
VALUESv (019,'Farjana','Female');
6. DELETE FROM student_view where roll=110;
7. DROP VIEW student_view;

8. CREATE VIEW stdsalary_view AS
SELECT `SN`, `date`, `stdId`, `stdName`, `section`, `receiptNo`, `roll`, `months`, `re_admission`, `school`,
`ccc`, `guide`, `red_crecent`, `current`, `dues`, `advance`, `examFee`, `others`, `total`, `pic`
FROM `stdsalary`;
CREATE INDEX index_name
ON table_name (column_name)
or
CREATE UNIQUE INDEX index_name
ON table_name (column_name)