MySQL Notes with Examples- AGN HUB
What is database
-it is a collection of data stored in a format that can easily be accessed.
- a collection of data
- made up of "tables"
- tables store similar information
About Tables
- Tables are made of "columns" and "rows"
-Columns are like categories
-each entry goes on a separate row
eg:-
Name Age Profession
John 23 Teacher
Ram 45 developer
Ravi 34 student
what is SQL
-Structured query language
-SQL is the language databases use
-MySQL is a program that understand SQL
-Other software, but MySQL is the best!
Advantages of MySQL
-Cross Platform
-Used with multiple languages (PHP, NodeJS, Python, C#).
-MySQL software is Open Source.
-MySQL is RDBMS.
-The MySQL Database Server is fast, reliable, scalable, and,easy to use.
-MySQL Server works in client/server or embedded systems
Websites using mysql
-Facebook
-Twitter
-Google
-Wikipedia
-Youtube
-Flickr
-Pinterest
Primary Key
- all tables should have "primary keys"
- column that is 100% unique
- no rows can have same primary key!
eg:-
ID Name Age Profession
1 John 23 Teacher
2 Ram 45 developer
3 Ravi 34 student
Software Requirement:-
XAMPP-->All Apache Mysql PHP Perl
1. Login in Xampp - mysql
mysql -u root -p
2. creating Database
create database db_name;
eg:-
create database company;
3. enter into a datbase
use company;
4. see all databases
show Databases;
5. create tables
CREATE TABLE table_name (
columnl datatype,
column2 datatype,
column3 datatype);
Datatypes in MySQL
3 Type of Category in Datatypes :
1. String
2. Numeric
3. Date and Time
String Datatypes in MySQL
-CHAR(size) O to 255
-VARCHAR(size) O to 65535
-BINARY(size)
-VARBlNARY(size) 255 characters
-TINYTEXT
-TEXT(size) 65,535 bytes
-MEDIUMTEXT 16,777,215 characters
-LONGTEXT 4,294,967,295 characters
-TINYBLOB 255 bytes
-BLOB(size) 65,535 bytes
-MEDIUMBLOB 16,777,215 bytes
-LONGBLOB 4,294,967,295 bytes
-ENUM(va11, va12, va13, ...)
-SET(va11, va12, va13, ...)
Numeric Datatypes in MySQL
-BIT(size) 1 to 64
-TINYlNT(size) -128 to 127
-INT(size) -2147483648 to 2147483647
-INTEGER(size)
-SMALLINT(size) -32768 to 32767
-MEDIUMINT(size) -8388608 to 8388607
-BIGINT(size) -9223372036854775808 to 9223372036854775807
-BOOL 0/1 True\False
-BOOLEAN
-FLOAT(decimal)
-DOUBLE(size, d) 255.568
-DECIMAL(size, d) size 60
-DEC(size)decimal
Date and time data types
-DATE '1000-01-01' to '9999-12-31'
-DATETIME YYYY-MM-DD hh:mm:ss
-TIMESTAMP
-TIME hh:mm:ss
-YEAR four-digit format : 1901
---->create table employee(ename varchar(100),ecity varchar(100),esal int);
6. see structure of table
show columns from employee;
or
desc student;
7. Insert data into table
insert into tb_name(col,col1,col2...) values(v1,v2...);
insert into employee(ename,ecity,esal) values('ram','jaipur',23000);
8. Show data of a table:-
select * from employee;
9. delete data from a table:-
delete from employee where ename='ram';
10. update data of a table:-
update employee set ecity='ajmer' where ename='keshav';
11. Insert multiple rows
Syntax:-
INSERT INTO table name ( columnl, column2)
VALUES
( valuel, value2,....),
( valuel, value2,....),
( valuel, value2,....);
eg:-
insert into employee(ename,ecity,esal) values('abhi','kota',34000),('riddhi','jaipur',36000),('priyanka','alwer',24000);
12. Constraints
- restriction on columns of data table
- we use them at the time of table creation
List of Constraints in MySQL
• NOT NULL
• UNIQUE
• DEFAULT
• CHECK
• FOREIGN KEY(keys)
• PRIMARY KEY(keys)
-NOT NUll
It is used when you need to fill a column with data completly(i.e. no blank data required)
-UNIQUE
Duplicate values are not allowed
-DEFAULT
eg:- DEFAULT 'jaipur'
set a by default value for a column
-CHECK
eg:- CHECK(age>=18)
to check for valid parameters
syntax:-
CREATE TABLE table_name (
id INT NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
age INT NOT NULL CHECK(age>18),
gender VARCHAR(1O) NOT NULL,
phone VARCHAR(1O) NOT NULL UNIQUE,
city VARCHAR(1O) NOT NULL DEFAULT 'jaipur');
eg:-
CREATE TABLE customer(id INT NOT NULL UNIQUE,name VARCHAR(50) NOT NULL,age INT NOT NULL CHECK(age>18),gender VARCHAR(1O) NOT NULL,phone VARCHAR(1O) NOT NULL UNIQUE,city VARCHAR(1O) NOT NULL DEFAULT 'jaipur');
13. select query
-see all data of a table
SELECT * FROM table name
eg:-
select * from customer;
-sell choosen columns
SELECT columnl, column2, column3 FROM table name
eg:-
select esal,ename from employee;
- selction with conditions in where clause
SELECT columnl FROM table name WHERE condition;
14. conditions:-
WHERE Comparison Operators
=(Equal)
>(Greater than)
<(Less than)
>=(Greater than or equal)
<=(Less than or equal)
!=(Not equal.)
BETWEEN(Between a certain range)
like(Search for a pattern)
IN(To specify multiple possible values for a column)
eg:-
select * from employee where esal > 30000;
select * from employee where ecity != 'jaipur';
15. AND,OR,NOT Operators
AND
-all the conditions should be true
syntax:-
SELECT columnl, column2, column3
FROM table name
WHERE conditionl AND condition2 AND condition3;
eg:-
select * from employee where ecity='jaipur' and esal > 30000;
OR
-any one of the condition should be true
SELECT columnl, column2, column3
FROM table name
ionl OR condition2 OR condition3.
eg:-
select * from employee where ecity='jaipur' or esal > 30000;
eg:-
select * from employee where not ecity = 'jaipur';
16. In Operator
-it is used to provide a few values in which we can satisfy our condition.
syntax:-
SELECT columnl, column2, column3
FROM table name
where column name in(value1,value2...)
SELECT columnl, column2, column3
FROM table name
where column name not in(value1,value2...)
eg:-
select * from employee where ecity in('jaipur','kota');
select * from employee where ecity not in('jaipur');
17. Between Operator
-works with a range of values
syntax:-
SELECT columnl, column2, column3
FROM table name
where column name between value1 and value2;
SELECT columnl, column2, column3
FROM table name
where column name not between value1 and value2;
eg:-
select * from employee where esal between 25000 and 35000;
select * from employee where esal not between 25000 and 35000;
18. Like Operator
-it is used to check for a sequence of data
wildcards:-
-----------
%(represents zero,one or multiple charcters)
_(represents a single character)
eg:-
Like 'a%'(starts with a)
Like '%a(ends with a)'
Like '%iam%'(iam in between)
Like 'i%a'(starts with i and ends with a)
Like '_a%'(second place a)
Like '__a%'(third place a)
Like '_am'(a in second and m in third position)
syntax:-
SELECT columnl, column2, column3
FROM table name
where column name like 'sequence';
SELECT columnl, column2, column3
FROM table name
where column name not like 'sequence'
eg:-
select * from employee where ename like 'a%';
select * from employee where ename like '%a%';
select * from employee where ename like '_ri%';
select * from employee where ename not like 'a%';
19. Regular Expression(REGEXP)
- can we applied to find or match an expression/pattern
^ '^ra' Beginning of string
$ 'ra$' end of string
[...] '[abc]' any charcter from string
^[] '^[abc]' begins with any one charcter
[a-z] '[a-m]' any charcter from a to m
a|b|c 'ram|ravi' matches any one of the pattern
syntax:-
SELECT columnl, column2, column3
FROM table name
where column name REGEXP 'pattern';
SELECT columnl, column2, column3
FROM table name
where column name not REGEXP 'pattern';
eg:-
select * from employee where ename regexp 'ram|ravi';
select * from employee where ename regexp '^a';
select * from employee where ename regexp 'a$';
select * from employee where ename regexp '^[ar]';
20. Order By
-It is used to arrange the data in ascending or descending order.
1. ASC(ascending)
2. DESC(descending)
syntax:-
SELECT columnl, column2, column3
FROM table name order by column name asc;
SELECT columnl, column2, column3
FROM table name order by column name desc;
eg:-
select * from employee order by ecity;
select * from employee order by ecity desc;
21. Distinct
- selct different data of a column
syntax:-
SELECT distinct columnl, column2, column3
FROM table name;
eg:-
select distinct ecity from employee;
22. Is NULL
-check if value is null
syntax:-
SELECT columnl, column2, column3
FROM table name where column name is null;
eg:-
select * from employee where ename is x;
23. Is not NULL
-check if value is not null
syntax:-
SELECT columnl, column2, column3
FROM table name where column name is not null;
eg:-
select * from employee where ename is not null;
24. Limit
- select the number of data to show
SELECT columnl, column2, column3
FROM table name limit number;
eg:-
select * from employee limit 2;
25. limit with offset
- offset is the starting index of rows
SELECT columnl, column2, column3
FROM table name limit offset,limit number;
eg:-
select * from employee limit 2,2;
26. Aggregation Function
-It is used to calculate and provide estimated values.
1. count(column name)
2. max(column name)
3. min(column name)
4. sum(column name)
5. avg(column name)
syntax:-
SELECT agg_function(columnl)
FROM table name;
eg:-
select count(esal) from employee;
select max(esal) from employee;
select min(esal) from employee;
select sum(esal) from employee;
select avg(esal) from employee;
27. Rollback
-To revert a work done
-It is used to revert a query run before
-applied with insert,update and delete
-keyword used is rollback
28. Commit
-saves query permanently
-applied with insert,update and delete
-keyword used is commit
29. Group by clause
-It is used to group similar data all together
- It is used with the select query and also with aggregate functions
syntax:-
SELECT columns
FROM table name
WHERE condition
GROUP BY column_name;
eg:-
select ecity,count(ecity) as number from employee group by ecity;
for joins:-
SELECT columns
FROM tablel INNER JOIN table2
ON tablel.column name = table2.column name
WHERE condition
GROUP BY column_name;
30. Having Clause
-apply condition when we have group by clause.
syntax:-
SELECT columns
FROM table name
GROUP BY column_name
HAVING condition;
eg:-
select esal,ecity,count(ecity) as number from employee group by ecity having esal > 25000;
31. Union and union all Operator
- used to merge data of different tables
-union all shows duplicate record
- union do not show duplicate results
-each select statement must have same no. of columns
-columns must have similar data types
-columns should be in same order
syntax:-
SELECT columnl, column2 FROM tablel
UNION / UNION ALL
SELECT columnl, column2 FROM table2;
eg:-
select ename,ecity from employee union select name,city from customer;
32. If and case clause
- If clause used to apply conditions and create custom column on that condition
-case clause is used to implement multiple condition and multiple values
syntax:-
If clause
SELECT columnl, column2,column3,
IF(Condition,TRUE Result, FALSE Result ) AS alias_name
FROM table name;
eg:-
select *, if(esal > 30000,"Sr","Jr") as Desg from employee;
case clause
SELECT columnl, column2,column3,
CASE
WHEN Conditionl THEN resultl
WHEN Condition2 THEN result2
WHEN Condition3 THEN result3
ELSE result4
END AS alias name
from table name;
eg:-
select *, case when esal > 30000 then "Sr" when esal > 25000 and esal < 30000 then "Jr" when esal > 20000 and esal <= 25000 then "As" else "Invalid" end as desg from employee;
33. Arithmetic functions
-They are basically used for calculations.
• PI() -value of pi
• ROUND() -round off value
• CEIL() -ceiling value
• FLOOR() -flooring value
• POW() -power value
• SQRT() - square root value
• RAND() -random value(0 to 1)
• ABS() -absolute value
• SIGN() - no>0(1),no=0(0),no<0(-1)
syntax:-
SELECT col1,col2,(col3 * 5) AS "alias name"
FROM table;
select function();
eg:-
select pi();
select ename,round(esal) from employee;
34. String Functions:-
-they are used to operate strings
• UPPER() / UCASE()
• LOWER() / LCASE()
• LENGTH()
• CONCAT()
• LTRIM()
• RTRIM()
• TRIM()
• POSITION()
• CHAR_LENGTH()
• REPEAT()
• REVERSE()
• REPLACE()
syntax:-
select function(colname) as alias from table name;
eg:-
select ucase("ram");
select ename,ucase(ecity) as city from employee;
35. Date functions
- used to operate dates
CURDATE
CURRENT_DATE
SYSDATE
NOW
LAST_DAY
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
EXTRACT
DATE_ADD
ADDDATE
MAKEDATE
DATE_SUB
SUBDATE
DATEDIFF
TO_DAYS
FROM_DAYS
PERIOD_ADD
PERIOD_DIFF
DATE_FORMAT
STR_TO_DATE
syntax:-
select function();
select now();
select curdate();
select last_day("2023-01-17");
36. Time Functions:-
CURTIME
CURRENT_TIMESTAMP
LOCALTIME
LOCALTIMESTAMP
TIMESTAMP
TIME
TIMEDIFF
HOUR
MINUTE
SECOND
MICROSECOND
ADDTIME
SUBTIME
MAKETIME
TIME_FORMAT
SEC_TO_TIME
TIME_TO_SEC
syntax:-
select function();
eg:-
select curtime();
37. Alter Table
-Add column in a table
-Changing Data Type of a Column
-Change Column Name
-Adding Constraints to a Column
-Changing Column Position
-Delete Column
-Renaming Tables
-primary key and unique key does not alter
syntax:-
Add Column
ALTER TABLE table name
ADD column name datatype;
eg:-
alter table customer add email varchar(50);
Modify Column
ALTER TABLE table name
MODIFY column name datatype;
eg:-
alter table customer modify email varchar(100);
Delete Column
ALTER TABLE table name
DROP COLUMN column name
datatype;
eg:-
alter table customer drop email;
Rename column
ALTER TABLE table_name
CHANGE column_name New_name
datatype;
eg:-
ALTER TABLE customer CHANGE city ecity VARCHAR(10);
Rename table
ALTER TABLE table_name
RENAME new_table_name;
eg:-
ALTER TABLE customer rename student;
38. Drop and Truncate
-delete table and data
Syntax:-
DROP TABLE table name;
TRUNCATE TABLE table name;
eg:-
truncate table student;
drop table student;
39. Views
-it is the result generated by a query.
eg:-
CREATE VIEW view_name
AS
Select
columns
FROM student
INNER JOIN city
ON student.city = city.cid;
drop view view_jaipur;
Advantages :
-Simplify complex query.
-Provides Extra layer of Security.
Disadvantages :-
-Performance Decreases.
-Depedency on Table.
40. Index
CREATE INDEX index name
ON table_name(columnl, column2, column3, ....);
eg:-
create index iesal on employee(esal);
DROP INDEX index_name
ON table_name;
eg:-
drop index iesal on employee;
show index from table_name;
-Automatically creates the indexes for PRIMARY KEY and UNIQUE columns.
-Index columns that you frequently use to retrieve the data.
-Index columns that are used for joins to improve join performance.
-Avoid columns that contain too many NULL values.
-Small tables do not require indexes.
41. Primary Key
-It always has unique data.
-a primary key cannot have null value.
-a table can contain only one primary key.
syntax:-
CREATE TABLE table_name (
id INT NOT NULL AUTO_INCREMENT ,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
city VARCHAR(1O) NOT NULL ,
PRIMARY KEY (id));
alter table key
ALTER TABLE table name
ADD PRIMARY KEY (id);
42. FOREIGN KEY
-A FOREIGN KEY is a key used to link two tables together.
-A FOREIGN key in one table used to point PRIMARY key in another table.
CREATE TABLE student(
id INT NOT NULL AUTO INCREMENT,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
city VARCHAR(IO) NOT NULL ,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid));
ALTER TABLE table name
ADD FOREIGN KEY (city) REFERENCES City (cid);
43. Types of Joins
1.INNER JOIN
2.LEFT JOIN
3.RIGHT JOIN
4.CROSS JOIN
-The INNER JOIN selects records that have matching values in both tables.
SELECT columns
FROM tablel
INNER JOIN table2
ON tablel.column name = table2.column name;
-The LEFT JOIN returns all records from the left table (tablel),
and the matched records from the right table (table2)
SELECT columns
FROM tablel
LEFT JOIN table2
ON tablel.column name = table2.column name;
-RIGHT JOIN
The RIGHT JOIN returns all records from the right table (table2),and matched records from the left table(table1).
SELECT columns
FROM tablel
RIGHT JOIN table2
ON tablel.column name = table2.column name;
-cross join is used to match every record of table1 to every record of table2.
SELECT columns
FROM tablel
CROSS JOIN table2
Comments
Post a Comment