What is Databases?
Database is any collection of related information. It containt any type of data including word,Images,videos, numbers and files.
What is Database Management Systems(DBMS)?
A special softwware program that helps users create and maintain a database.
make it easy to manage large amounts of information, handle secuirity, backup,importing and exporting data,concurrency.
Some Main Types of Databases:
1. Relational Databases:
It uses SQL language for performing operations.
organized data into one or more tables. each table has columns and rows, A unique Key identifies each row.
2. NoN Relational Databases:
It uses NoSQL language for performing operations.
organized data in anything but a traditional table. Key-value stores,Documents(JSON,XML,etc),Graphs,Flexible Table.
3. Cloud Databases:
A cloud database is used where data requires a virtual environment for storing and executing over the cloud platforms and there are so many cloud computing services for accessing the data from the databases (like SaaS, Paas, etc).
What is Query ?
Query are request made it the database management system for specific information.
Query made up of commands that allows you to manipulate data within databases.
Tables and Keys :
Keys :
It is used to uniquely identify any record or row of data from the table. It is also used to establish and identify relationship between tables.
Tables :
Collections of related data organized in row and column format.
Types of Keys:
1. Primary Key :
The rockstar of keys, the primary key uniquely identifies each record in a table. Ensures data integrity and provides a foundation for relationships between tables. Primary key is unique no same key is used.
2. Foreign Key :
This key establishes a link between two tables, creating relationships. The foreign key in one table points to the primary key in another, creating a parent-child relationship. It helps maintain referential integrity between tables.
3. Candidate Key :
In simple terms, it's a contender for the primary key position.The chosen candidate key becomes the primary key, and the others become alternate keys.
4. Super Key :
A super key is a set of attributes that, when taken together, can uniquely identify a record.
It can be a combination of candidate keys and additional attributes. It's a broader concept than a candidate key, encompassing all potential unique identifiers.
5. Composite Key:
This key is formed by combining two or more attributes to create a unique identifier. Unlike a primary key, which can be a single attribute, a composite key involves multiple attributes working together.
6. Surrogate Key:
It is user defined key and it is used as the primary key .
It doesn't have any inherent meaning but ensures uniqueness.
Commonly used in situations where natural keys might be too complex or prone to changes.
7. Unique Key :
Similar to a primary key, a unique key ensures that each record has a unique value. a table can have multiple unique keys.
8. Compound Key :
A compound key is a combination of two or more attributes that together uniquely identify a record. It's similar to a composite key but might include non-key attributes as well.
SQL Basics :
It is used to for intracting with relational database management sytems. Use SQL for create, retrive,update and delete data. create and manage database,design & create database table.
Actually SQL is Hybrid Langauge it is combination four langugaes :
1. DDL - Data Definition Language:
Imagine DDL as the architect drawing up blueprints. It's all about defining and managing the structure of the database.
Key commands: `CREATE` (for creating tables), `ALTER` (for modifying table structure), and DROP` (for deleting tables).
2.DML - Data Manipulation Language:
Now that we have our structure, it's time to fill it with life. DML commands are the storytellers, shaping and interacting with the data.
Key commands: `SELECT` (for retrieving data), `INSERT` (for adding new data), `UPDATE` (for modifying existing data), and `DELETE` (for removing data).
3.DCL - Data Control Language:
DCL holds the keys to access and permissions. It's the bouncer at the club, deciding who gets in and who doesn't.
Key commands: `GRANT` (for providing access privileges) and `REVOKE` (for revoking access privileges).
4. TCL - Transaction Control Language:
Transactions are like magic spells ensuring data consistency. TCL commands are the sorcerers casting and managing these spells.
Key commands: `COMMIT` (to save transactions) and `ROLLBACK` (to undo transactions).
DataTypes in SQL:
INT : whole no
DECIMAL(M,N) : Decimal Number-exact value
VARCHAR(12) : String of text of Length(12)
BLOB : Binary large object, Stores large data
DATE: "YYYY-MM-DD"
Text: for get text
Number: for getting no
TIMESTAMP: "YYYY-MM-DD HH:MM:SS"
Constraint:(used with tables mostly):
1. NOT NULL : if we this key word while creating table and declare a column as not null so it can not get NULL Value.
2. UNIQUE: if we use this keyword while creating table only unique value we can use no reapetation allows.
3. DEFUALT 'defualt value': If you want to provide defualt value then use this .
4. AUTO_INCREMENT: used with primary key column and at any table that helps to auto fill value it increament like if primary key start from 1 it goes automatic increaments.
Steps for Creeating Databases and managing database
1 Creating Database:
Query: create database Database_name;
2 Use database for Creating table:
Query :use Database_name;
Operations :
1. Create Table:
Query 1 : CREATE TABLE Table_name(Column_name DataType Keyfortableif, Column_name DataType,.. );
Query 1 : CREATE TABLE Table_name(Column_name DataType , Column_name DataType,..PRIMARY KEY(Column_name) );
2. Get all Details of table :
Query : DESCRIBE table_name;
3. Delete Table:
Query : DROP TABLE Table_name;
4. Modify Table:
Query add column : ALTER TABLE Table_name ADD column_name dataType;
Query for Delete specific column : ALTER TABLE Table_name DROP column_name;
5 . Insert data in table:
6.Upadating data in Table :
Query 1 updating a value it applies on every value who match with existing value in column .: UPDATE table_name SET column_name ="New_vale" WHERE column_name="Existing_value";
Query 2 updating a value for specific row specific id's.: UPDATE table_name SET column_name ="New_vale" WHERE column_name="id/ name of that row";
Query 3 (OR operator) if you want to update two diffrent value with same name or combined together : UPDATE table_name SET column_name ="New_vale" WHERE column_name="value1" OR column_name="value2" ;
Query 4 We set multiple values with where change multiple column : UPDATE table_name SET column_name1 ="New_vale1 ",column_name2 ="New_vale2" WHERE column_name="id/ name of that row" ;
7. Deleting data in table:
Query 1: Delete all rows: DELETE FROM Table_name;
Query2 : Delete Specific row: DELETE FROM Table_name WHERE column_name="value/id of row";
Query3 : Delete Specific rows with specific values : DELETE FROM Table_name WHERE column_name="value/id of row" AND column_name2="value/id of row2";
8. SELECT STATEMENT Operation:
Query1 Retrive Specific column data: SELECT Column_name, column_name FROM table_name;
Query2 Retrive Specific column data ORDERBY: SELECT Column_name, column_name FROM table_name ORDER BY column_name;
Query3 Retrive Specific column data ORDERBY DECENDING: SELECT Column_name, column_name FROM table_name ORDER BY DESC column_name;
Query4 Retrive Specific column data ORDERBY DECENDING: SELECT Column_name, column_name FROM table_name ORDER BY ASC column_name;
Query5 Retrive Specific column data LIMIT: SELECT Column_name, column_name FROM table_name LIMIT 1,2,3,..n;
Query6 Retrive Specific column data WHERE: SELECT Column_name, column_name FROM table_name WhERE Column_name ="value" ;
*note: We can use Comparision operators for filter result
Aggregation or Aggregate funtions:
Used to perform the calculations on multiple rows of a single column of a table. It returns a single value.
Aggregation Function:
1.COUNT
2.SUM
3.AVG
4.MAX
5.MIN
JOIN's
1. Inner Join:
Picture a Venn diagram where two circles overlap. An inner join fetches only the rows where there's a match between the joining columns in both tables.
Syntax: `SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;`
2.Left Join (or Left Outer Join):
Imagine the left table as the VIP section, and the right table as the regular attendees. A left join fetches all rows from the left table and matching rows from the right.
Syntax: `SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;`
3. Right Join (or Right Outer Join):
The VIP section switched! A right join fetches all rows from the right table and matching rows from the left.
Syntax: `SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;`
4.Full Outer Join:
It's the grand ballroom where everyone is invited. A full outer join fetches all rows from both tables, filling in with nulls where there's no match.
Syntax: `SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.col = table2.col;
Comments
Post a Comment