SQL Batabase

SQL:- Structured Query Language

1. Install Xampp. 
2. Install My SQL Workbench.

Create Database using below mentioned query:-

create database Dpk

Datatypes in MySQL:-

1. String
2. Numeric
3. Date and time    

Create Database:-

create database dpk;

execute above query in mysql workbence

then create a table using below query:-

create table Product(

pid int,

    pname varchar(50),

    pcompany varchar(50),

    price int

);

show table by using below query:-
SELECT * FROM dpk.product;

how to insert Data in table:-

insert into personal (id, name, birth_date, phone, gender)

values(1, "Deepak Tewatia", "1997-08-02", "8398979594", "M")

list of constraints in MySQL

• not null
• unique
• default
• check
• foreign key
• primary key

to set conditions

create table test_2( 

id int not null unique,

name varchar(50) not null,

    age int not null check(age>=18),

    phone varchar(12) not null unique,

    gender varchar(1) not null,

    city varchar(15) not null default 'Gurgaon'

    

    );

Single entry

insert into personal (id, name, birth_date, phone, gender)

values(1, "Deepak Tewatia", "1997-08-02", "8398979594", "M");

multiple entry:-

insert into test_2 (id, name, age, phone, gender, city)

values 

(5,"Deepak", "20", "8394975594", "m", "delhi"),

(3,"Deepak", "20", "8356979594", "m", "delhi"),

(4,"Deepak", "20", "8396979594", "m", "delhi");

Show Table

SELECT * FROM dpk.personal;

where comparison operators

photo need to insert from snapshot

filter in database

SELECT * FROM test_2

where name = "deepak";

SELECT * FROM test_2

where age > "21";

SELECT * FROM test_2

where age < "20";

Question:- how to run command execute via keyboard only in mySQL workbench ?

Specific columns required from table then replace * with required table name as below command:-

SELECT id, name, city FROM test_2

where city = "gurgaon";

"and" used for both true conditions

"or" used for single true condition anyone of them

select data with two or more true conditions by using 'and' operator:-

SELECT * FROM test_2

where city = "gurgaon" or name = "beena";

select data with single true condition by using 'or' operator:-

SELECT * FROM test_2

where age >= "20" and name = "deepak";

select data with in operator

select * from test_2

where age in (18,23); 

select data with in operator with not operator

select * from test_2

where age not in (18,23); 

select data with between operator

select * from test_2

where age between 21 and 23; 

Note:- or operator cannot be usable with between operator

            we can use between operator in between numeric value , first alphabet of any name and dates

select data with between operator with not operator

select * from test_2

where age not between 21 and 23; 

select data with between operator in Name column between two alphabet

select * from test_2

where name between "a" and "e"; 

select data with like operator

wildCard Characters:-

% Percentage Sign: Represents zero, one or multiple characters

- Underscore: Represents aa single character

Pattern                Description

like 'a%'             -start with "a"

like '%a'             -end with "a"

like '%am%'       -have "am" in any position 

like 'a%m%'       -start with "a" and ends with "m" 

like '_a%'           -"a" in the second position

like '__a%'         -"a" in the third position

like '_oy%'         -"o" in the second and "y" in the third position

select * from test_2

where name like "b%"; 

select * from test_2

where name like "b%" or name like "d%"; 

select * from test_2

where name not like "d%"; 

select data with case sensitive 

select * from test_2

where binary name like "B%"; 

select data with regular expressions

select * from test_2

where binary name like "B%"; 

Regular expressions patterns with Description 

Sign         Pattern         Description

^          '^ra'                 Beginning of string

$         'an$'                 End of string

[...]         '[rms]'         Any character listed between the square brackets

^[...]         '^[rms]'                 begins with any character listed between the square brackets

[a-z] '[a-h]e'                 match with in the range

p1|p2|p3         'tom|dog|harry' matches any of the patterns p1, p2 or p3