ডাটাবেজ — Database

14 min readMar 17, 2020

SQL raw query . Just completed Udemy course → The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert here

Content

Creating Databases and Tables
Inserting Data (and a couple other things)
CRUD operation
Strings
Magic of Aggregate function

mysql install করবো আমরা আমাদের পূর্বের একটি ব্লগ থেকে install different types of software. থেকে এরপর নতুন ইউজার তৈরি এবং এদের পারমিশন এর জন্য কিছু জিনিশ আগে জানা দরকার ।

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

এরপর আমরা নিচের কমান্ড দিয়ে এই ইউজারকে সকল পারমিশন গ্রান্ট করে দিবো ।

mysql> GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

এখানে প্রথম asterisk দিয়ে বোঝান হচ্ছে যে সকল ডাটাবেজে পারমিশন দিলাম আর পরের asterisk দিয়ে বোঝান হচ্ছে যে সকল টেবিলে এক্সেস দিলাম । এরপর নিচের কমান্ড দিয়ে activate করে দিবো ।

mysql> FLUSH PRIVILEGES;

প্রকৃতপক্ষে সকল অপারেশন করে এই কমান্ড দিয়ে রিলোড করে নিতে হয় ।

যদি কোন নিদ্রিস্ট ইউজারকে কোন নিদ্রিস্ট পারমিশন দিতে চাই তাহলে নিচের কমান্ডের মতো স্ট্রাকচার মতো করে পারমিশন দেওয়া যায় ।

GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost';

যেকোনো পারমিশন পরিবর্তন করার পর flush privileges করে নিতে হয় আর সকল ডাটাবেজে অথবা টেবিলে পারমিশনের জন্য * চিহ্ন দিয়ে দিতে হয় ।

mysql> REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost';

REVOKE টাও অনেকটা একি রকম ।

কোনও ইউজারের পারমিশন রিভিউ দেখার জন্য নিচের কমান্ড টি দিতে হয় ।

mysql> SHOW GRANTS FOR 'username'@'localhost';

quit কমান্ড দিয়ে ডাটাবেজ থেকে বের হওয়া যায় এরপর নিচের কমান্ড দিয়ে নতুন ইউজারে যেতে হবে ।

mysql -u [username] -p

Creating Databases and Tables

আমরা এখানে MySQL দিয়ে কুয়েরি শিখবো । MySQL install করার নিয়ম আমার পূর্বের ব্লগ এখানে আছে । lets start …

ডাটাবেজ হচ্ছে কতোগুলো টেবিলের সমষ্টি ( অন্তত রিলেসনাল ডাটাবেজে ) আর টেবিলগুলো ডাটা স্টোর করে রাখে ।

create database : নতুন একটি ডাটাবেজ বানানোর জন্য

CREATE DATABASE <name>;

ex : CREATE DATABASE soap_store;

ex : CREATE DATABASE DogApp;

show database : কয়টি ডাটাবেজ আছে

>>> SHOW DATABASES;

use datbase : কোন পার্টিকুলার ডাটাবেজে কাজ করতে চাইলে

>>> USE <database name>;

drop database : ডাটাবেজ ডিলিট করতে চাইলে

>>> DROP DATABASE < database_name>;

select database : বর্তমানে কোন ডাটাবেজে কাজ করতেছি সেটা দেখাবে

>>> SELECT database();

Table → data type

int → a whole number ( max value 4294967295)

varchar → variable length string ( between 1 and 255 character) → ‘aldf fl’

create table : নতুন একটি টেবিল বানানোর জন্য

cat table

show table : database এ কয়টি টেবিল আছে সেটা দেখার জন্য

>>> SHOW TABLES;

show column : টেবিলে কয়টি কলাম আছে এবং সেই কলাম গুলোর ইনফো গুলো দেখার জন্য

>>> SHOW COLUMNS FROM <table_name>;
or
>>> DESC <table_name>;

deleting table: টেবিল ডিলিট করার জন্য

>>> DROP TABLE <table_name>;

exercise :

Create a pastries table

It should include 2 columns: name and quantity. Name is 50 characters max.
Inspect your table/columns in the CLI
Delete your table!

Inserting Data (and a couple other things)

ডাটাবেজে ডাটা insert করার জন্য

যেকোনো অর্ডারেই লেখা যায়, তবে কলামের অর্ডার যেভাবে হবে ভেলু ও সেই অর্ডারে হবে ।

SELECT

ডাটাবেজ থেকে ডাটা retrieve করার জন্য এই কমান্ড ইউজ করা হয় ।

>>> SELECT * FROM cats;

WARNINGS

কোন ওয়ার্নিং দেখা দিলে সেটা দেখার জন্য

>>> SHOW WARNINGS;

ধরা যাক, স্ট্রিং ফিল্ডের লিমিট আমরা দিয়েছি ১০ কিন্তু আমরা insert করলাম ৫০ ক্যারেক্টার তাহলে সেক্ষেত্রে আমাদের ডাটাবেজে প্রথম ১০ ক্যারেক্টার ই সেভ হবে বাকীগুলা সেভ হবে না ।

আবার ধরা যাক, integer ফিল্ডে আমরা স্ট্রিং ভেল insert করতে বললাম সেক্ষেত্রে ডাটাবেজে স্ট্রিং সেভ না করে ইন্টিজারের ডিফল্ট ভেলু হিসেবে 0 সেভ করে দিবে ।

NULL

DESC table এর কোন কলাম ফিল্ডে যদি Null ফিল্ড yes থাকে তারমানে হচ্ছে ঐ ফিল্ডের ভেলু null হতে পারবে ।

NOT NULL

ফিল্ড যেন খালি না থাকে সেজন্য NOT NULL ব্যাবহার করা হয় ।

not null থাকলে সকল ভেলু দিয়ে কোন রো তে ডাটা insert করতে হয় ।

DEFAULT

CREATE TABLE cats3
(
    name VARCHAR(20) DEFAULT 'no name provided',
    age INT DEFAULT 99
);

NOT NULL DEFAULT

বাহির থেকে explecitly null ভেলু সেট করে দেওয়া যায় তাই ভেলু insert করার সময় যেন null দেওয়া না যায় সেজন্য not null default আসছে ।

এখন নিচের মতো করে null ভেলু insert করার সময় error দিবে ।

INSERT INTO cats4(name, age) VALUES('Cali', NULL);

PRIMARY KEY

Exercise and Solution

CRUD operation ( Create, Read, Update, Delete)

CREATE

READ

How do we retrieve and search data ?

SELECT * FROM cats;

* এর মানে হচ্ছে ডাটাবেজ হতে সকল ডাটা নিয়ে আসো ।

SELECT expression দিয়ে বোঝানো হচ্ছে কোন কলাম নিয়ে আসতে চাই ।

শুধু name কলাম নিয়ে আসতে চাইলে -

SELECT name FROM cats;

name এবং age কলাম নিয়ে আসতে চাইলে -

SELECT name, age FROM cats;

ডাটাবেজ থেকে সকল কিছু নিয়ে আসবা যেখানে age=4

SELECT * FROM cats WHERE age=4;

string data

SELECT * FROM cats WHERE name='egg';

string এর ক্ষেত্রে ডাটা case insensetive অর্থাৎ name=’EGG’ অথবা name=’eGg’ একি কাজ করবে ।

Aliases ( AS )

easier to read results

টেবিলের কলামের নাম রিনেম করে দেখার জন্য অথবা কলাম joining এর ক্ষেত্রে নতুন কলাম rename করে visual readable করার জন্য এটি ব্যাবহার করা হয় ।

SELECT cat_id AS id, name FROM cats;

UPDATE

How do we alter existing data ?

UPDATE cats SET breed='shorthair'
WHERE breed='Tabby';

cats টেবিলের breed কলামের যে সকল জায়গায় Tabby ভেলু আছে সেসকল ভেলু shorthair দিয়ে রিপ্লেস করে দাও ।

UPDATE cats SET age=14
WHERE name='Misty';

cats টেবিলের name কলামে যাদের ভেলু Misty আছে সেসব রো এর রিলেটেড age কলামের ভেলু 14 করে দাও ।

DELETE

to delete somethings

DELETE FROM cats WHERE name='Egg';

cats টেবিলের name কলামে যাদের যাদের ভেলু Egg সেই রো গুলো ডিলিট করে দাও ।

DELETE FROM cats;

cats টেবিল ডিলিট করে দাও ।

DELETE FROM cats WHERE cat_id=age;

cat_id এবং age কলামের ভেলু এক হলে সেই রো ডিলিট করে দাও ।

STRINGS

CONCAT

combine data for cleaner output

mysql documentation এ সকল string function আছে ।

CONCAT_WS

normali CONCAT ফাংশনে আমরা একাধিক কলাম কঙ্কেট করার সময় দুই কলামের মাঝে ‘ ‘ অথবা অন্য যেকোনো ক্যারেক্টার দেই । এই একি কাজ টা CONCAT_WS এ করা যায়

SELECT CONCAT(title, '-', author_fname, '-', author_lname)

এই কাজ টাই করা যায় CONCAT_WS ফাংশনের মাধ্যমে ।

SUBSTRING

work with parts of strings

SELECT SUBSTRING('Hello world', 1, 4);
-- output : Hell

অন্যান্য প্রোগ্রামিং ল্যাঙ্গুয়েজে স্ট্রিং এর ইনডেক্স শুরু হয় ০ থেকে কিন্তু sql এর ক্ষেত্রে স্ট্রিং এর ইনডেক্স শুরু হয় 1 থেকে ।

SUBSTRING এর পরিবর্তে SUBSTR ব্যাবহার করা যায় ।

এখন আমার চাই নিচের মতো স্ট্রিং বানাতে ।

সেজন্য আমাদের প্রথমে সাবস্ট্রিং বানাতে হবে এরপর কঙ্কেট করতে হবে ।

SELECT CONCAT(SUBSTRING(title, 1, 10), '...') AS 'short title' 
FROM books;

REPLACE

SELECT REPLACE('Hello World', 'o', '*');
-- output : Hell* W*rld

o এর জায়গায় *রিপ্লেস করতে এই query কাজ করবে ।

SELECT REPLACE(title, 'e', '3') FROM books;

books টেবিলের title থেকে e এর জায়গায় 3 রিপ্লেস করবে ।

REVERSE

SELECT REVERSE('Hello world');
--  output : dlrow olleH

books টেবিল থেকে first name নিয়ে এটাকে রিভার্স করে নিয়ে আসবে নিচের query থেকে ।

SELECT CONCAT(author_fname, REVERSE(author_fname)) 
FROM books;

CHAR_LENGTH

counts characters in string

SELECT CHAR_LENGTH('Hello world');

-- output : 11

এইরকম আউটপুট আনার জন্য নিচের মতো করে query লিখতে হয় ।

SELECT author_lname, CHAR_LENGTH(author_lname) as 'length' 
FROM books;

UPPER() and LOWER()

change a String’s case

Refining our selections

SELECT DISTINCT author_lname FROM books;

author_lname একি নাম একাধিক থাকলে সেখান থেকে শুধু একটা নাম ই নিয়ে আসবে ।

first name এবং last name একসাথে concat করে সেখান থেকে distinct ভেলু নিয়ে আসার জন্য নিচের query লিখতে হয় ।

SELECT DISTINCT CONCAT(author_fname, ' ', author_lname) FROM books;

আর যদি আমরা চাই যে first name এবং last name দুইটাই একসাথে distinct তাহলে নিচের মতো query লিখতে হয় ।

SELECT DISTINCT author_fname, author_lname FROM books;

ORDER BY

sorting our results

স্ট্রিং এর মতো নাম্বার ও সর্ট করা যায় ।

SELECT released_year FROM books ORDER BY released_year;

একাধিক কলাম থাকলে শর্টকাট হিসেবে নাম্বার ও ব্যাবহার করা যায় ।

SELECT title, author_fname, author_lname FROM books ORDER BY 2 DESC;

এখানে ২ দিয়ে বোঝানো হচ্ছে যে author_fname । retype এর দরকার হয় না ।

এখন আমরা চাই যে প্রথমে last name অনুযায়ী সর্ট হবে এরপর একাধিক last name একি থাকলে সেগুলোকে আবার first name অনুযায়ী সর্ট করবে সেজন্য নিচের মতো করে query লিখতে পারি ।

SELECT author_fname, author_lname FROM books
ORDER BY author_lname, author_fname;

LIMIT

আমরা যদি চাই যে কোন লিস্ট থেকে প্রথম ৫ টা বা ১০ টা এলিমেন্ট আনতে তখন LIMIT ইউজ করা হয় ।

SELECT title FROM books LIMIT 5;

books টেবিল থেকে title আর released_year আনবো released_year অনুযায়ী descendin অর্ডার করে, প্রথম ৫ টার ডাটা । এজন্য নিচের মতো করে query লিখতে হয় ।

SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 5;

দ্বিতীয় নাম্বার বই থেকে ৩ টা নিয়ে আসবে ।

SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 1,3;

১১ নাম্বার বই থেকে ১ টা নিয়ে আসবে ।

SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 10,1;

যদি পার্টিকুলার কোন রো থেকে একদম শেষ পর্যন্ত যত বই আছে সবগুলো আনতে চাই তাহলে প্রথম প্যারামিটারে যে রো থেকে শুরু করবো সেই রো নাম্বার আর সেকেন্ড প্যারামিটারে অনেক বড় সংখ্যা দিতে হয় ।

SELECT * FROM tbl LIMIT 95,1847348282234;

LIKE

better searching

books টেবিল থেকে title আর author_fname কলাম নিয়ে আসবে যেখানে author_fname এ ‘da’ থাকে । da এর আশেপাশে যেকোনো কিছু থাকতে পারে ।

SELECT title, author_fname FROM books WHERE author_fname LIKE '%da%';

(235)243–8762 এইটার কোয়েরি লেখার জন্য হবে LIKE '(___)___-____'

অনেকটা রেগুলার এক্সপ্রেশন এর মতো ।
_ মানে হচ্ছে exactly একটাই ডিজিট আর
* মানে হচ্ছে যেকোনো কিছু ।

escape character % sign
% sign দিয়ে বোঝানে হয় যেকোনো কিছু এখন যতবার খুশী ততবার ই % sign ব্যাবহার করা যায় । এখন যদি চাই যে 10% Happier লেখাটি নিয়ে আসতে তখন নিচের মতো করে কোয়েরি লিখতে হয় ।

SELECT title FROM books WHERE title LIKE '%\%%'

প্রথম % দিয়ে বোঝানো হচ্ছে যে প্রথমে যেকোনো কিছু থাকতে পারে এরপর % দিয়ে বোঝানে হচ্ছে exactly % সাইন, এরপর % দিয়ে বোঝানো হচ্ছে এরপর যেকোনো কিছু থাকতে পারে ।

escape character _ sign
_ sign আনতে চাইলে নিচের মতো করে কোয়েরি লিখতে হয় ।

SELECT title FROM books WHERE title LIKE '%\_%'

_ এর প্রথমে আর শেষে যেকোনো কিছু থাকতে পারে ।

Magic of Aggregate function

COUNT

books টেবিলে কয়টি বই আছে !!

SELECT COUNT(*) FROM books;-- output : 19

এখানে * দিয়ে বোঝানো হচ্ছে যে কয়টি রো আছে । সকল রো সংখ্যা count করে নিয়ে আসবে ।

SELECT COUNT(DISTINCT author_fname) FROM books;-- output : 12

author first name কয়টা আছে distinct value.

SELECT COUNT(DISTINCT author_fname, author_lname) FROM books;-- output : 12

multiple row এর ক্ষেত্রে উপড়ের এই কোয়েরি কাজ করবে ।

SELECT title FROM books WHERE title LIKE '%the%';

title এ the word টি আছে এইরকম সকল title নিয়ে এসেছে । এখন সেটা count করবো ।

SELECT COUNT(*) FROM books WHERE title LIKE '%the%';-- output : 6

GROUP BY

summarizes or aggregates identical data into single rows.

group by দিয়ে author last name এর প্রত্যেক নাম কয়টা করে আছে সেটা নিয়ে আসে ।

SELECT author_fname, author_lname, COUNT(*) FROM books GROUP BY author_lname, author_fname;

author_fname এবং author_lname অনুযায়ী গ্রুপ করে এরপর কাউন্ট করে ডাটা নিয়ে আসবে ।

একি সালে কয়টি করে বই রিলিজ হয়েছে সেটা দেখার নিচের কোয়েরি ব্যাবহার করা যায় ।

SELECT released_year, COUNT(*) FROM books GROUP BY released_year;

string concat করে নিচের মতো করে query লিখা যায় ।

SELECT CONCAT('In ', released_year, ' ', COUNT(*), ' book's released') AS year FROM books GROUP BY released_year;

Min(), Max()

SELECT Min(released_year) FROM books;-- output : 1945

count minimum pages of books

SELECT Min(pages) FROM books;-- output : 176

released year সবচেয়ে বেশী

SELECT Max(released_year) FROM books;-- output : 2017

maximum page

SELECT Max(pages) FROM books;-- output : 634

title এবং maximum page নিয়ে আসার জন্য নিচের কমান্ড ব্যাবহার করা যায় ।

SELECT Max(pages), title FROM books;

sub query

SELECT title, pages FROM books WHERE pages=(SELECT MIN(pages) FROM books);

কিন্তু এই কোয়েরিটা অনেক স্লো তাই বিকল্প হিসেবে ORDER BY ব্যাবহার করা হয়, বিশেষ করে অনেক বড় ডাটাসেটের ক্ষেত্রে ।

SELECT title, pages FROM books ORDER BY pages ASC LIMIT 1;
-- output : 176SELECT title, pages FROM books ORDER BY pages DESC LIMIT 1;
-- output : 634

MIN/MAX with Group By

প্রত্যেক author এর মিনিমাম রিলিজ ইয়ার বের করার জন্য নিচের কমান্ড ব্যাবহার করা হয় ।

SELECT author_fname, author_lname, Min(released_year)
FROM books
GROUP BY author_lname, author_fname;

নিচের কোয়েরির মাধ্যমে আমরা প্রথমে author_fname আর author_lname কলাম নিয়ে আসবো যেখানে প্রত্যেক author এর ম্যাক্সিমাম পেজের বই নিয়ে আসবে ।

SELECT author_fname, author_lname, Max(pages) 
FROM books 
GROUP BY author_fname, author_lname;

SUM

sum all pages in the entire database

SELECT Sum(pages) FROM books;-- output : 6623

প্রত্যেক author মোট কতো পেজের বই লিখছে সেটা বের করার জন্য নিচের কমান্ড ব্যাবহার করা হয় ।

SELECT author_fname, author_lname, Sum(pages)
FROM books
GROUP BY author_fname, author_lname;

AVG

books টেবিল থেকে এভারেজ পেজ বের করার জন্য নিচের কমান্ড ব্যাবহার করা যায় ।

SELECT AVG(pages) FROM books;-- output : 348.5789

এখন নিচের কোয়েরির মাধ্যমে আমরা released_year আর average স্টক quantity কলাম নিয়ে আসবো যেখানে প্রত্যেক বছরে কতোটি বই স্টকে থাকে সেটা দেখাবে ।

SELECT released_year, AVG(stock_quantity) 
FROM books 
GROUP BY released_year;

Data Type :

string

char হচ্ছে fixed length আর Varchar ফিক্সড length এর না ।

Number

decimal ছারাও INT আছে ।

এছাড়াও FLOAT এবং DOUBLE আছে ।

Data and Time:

DATE -> YYYY-MM-DD এই ফরম্যাটে হয় সাধারণত ।

TIME -> HH:MM:SS ফরম্যাটে হয় ।

DATETIME -> YYYY-MM-DD HH:MM:SS ফরম্যাটে হয় ।

CREATE TABLE people (
    name VARCHAR(100),
    birthday DATE,
    birthtime TIME,
    birthdt DATETIME
);

CURDATE() -> gives current date

CURTIME() -> gives current time

NOW() -> gives current datetime

INSERT INTO people (name, birthdate, birthtime, birthdt)  VALUES
('Microwave', CURDATE(), CURTIME(), NOW());

formating dates

mysql এর ডকুমেন্টেশনে অনেক ফরমেট আছে এগুলো ফরমেট করার ।

SELECT name, birthdate, DAYNAME(birthdate) FROM people;

এছাড়াও DAY(), DAYNAME(), DAYOFWEEK() DAYOFYEAR() ইত্যাদি ।

DATEDIFF এবং DATE_ADD এই দুটো মেথড দিয়েও অনেক কেল্কুলেসন করা যায় ।

TIMESTAMPS

CREATE TABLE comments (
    content VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

created_at TIMESTAMP DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP

এখানে ON UPDATE CURRENT_TIMESTAMP দেওয়ার মানে হচ্ছে যখন এই রো এর কন্টেন্ট পরিবর্তন হবে তখন সাথে সাথে এর timestamp ও পরিবর্তন হবে ।

The power of Logical operator

!= ( not equal )

NOT LIKE

> ( Greater than ) > = ( greater than or equal)

< ( Less than ) < = ( Less than or equal )

&& ( logical and operator) used as AND

and এর মানে হচ্ছে দুইটা বা একাধিক কন্ডিশনই true হইতে হবে আর or operator এর মানে হচ্ছে একাধিক কন্ডিশনের একটা true হলেই হবে । AND এর জায়গায় && চিহ্ন দিলেও একি রেজাল্ট আসবে ।

|| Logical or

যেকোনো একটি true হলেই পুরোটা true.

BETWEEN

NOT BETWEEN …. AND

আগেরটার উল্টোটা হবে ।

IN and NOT IN

CASE STATEMENTS

প্রোগ্রামিং এর switch-case এর মতো অনেকটা লজিকালি কাজ করে ।

One to Many Relationship :

mysql -u jahangir

terminal এ যেয়ে উপরের এই কমান্ড দিয়ে mysql এ ঢুকবো ।

Relationships and JOINS

Relationship basic এ আমরা ৩ ধরনের relationship দেখবো ।

One to One Relationship
One to Many Relationship
Many to Many Relationship

One to Many Relationship

এটাই সব চাইতে কমন । Customer এবং Order টেবিলের রিলেশনশিপ দেখব এখন । একজন কাস্তমার অনেক গুলো অর্ডার করতে পারে ।

customer table এ customer_id এবং orders টেবিলে order_id হচ্ছে PRIMARY KEY . আর order table এ customer_id হচ্ছে FOREIGN KEY

SELECT * FROM customers WHERE last_name='George';

subquery:

Implicit and Explicit inner join

Left join

RIGHT JOIN and ON DELETE CASCADE

MANY : MANY relationship

Challanges