MySQL is an open-source DBMS which is developed and distributed by Oracle Corporation. It’s an open source relational database, which supports windows, linux, mac and all the servers/platforms which are highly used.
mysqli is the enhanced version of mysql with object-oriented and procedural versions. It comes with PDO enabled
Innodb vs MyIsam:
- Definition: InnoDB and MYISAM, are storage engines for MySQL.
- InnoDB (transactional) has row-level locking,
- MyISAM can only do full table-level locking.
- In Innodb transactions, COMMIT and Rollback.
Joins:
- Inner Join:
- Returns records that have matching values in both tables
- Outer Join:
- LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
- FULL (CROSS) (OUTER) JOIN: Return all records
Indexing:
- Indexing is called Data structure that improves the speed of operations in a table.
- This is a way to avoid scanning the full table to obtain the result that you’re looking for.
- When we set an index on a table column, then mysql engine groups the records by that column and stores them into hidden tables. When we run the select query then the mysql engine scans that hidden table so it will not have to scan the whole data.
Full-text index:
- fulltext index is used to make the search faster.:
- A full-text index can be created for VARCHAR, CHAR or TEXT columns.
- Loading data into table: Large data sets without FULLTEXT index are much faster to load data into a table than to load data into a table which has an existing FULLTEXT index. Therefore create the index after loading data.
- It does the indexing of each word on a text so search becomes very fast.
Clustered Index:
- A clustered index is a table where the data for the rows are stored.
- It accesses the data very fast.
- if the table column contains a primary key, MySQL automatically creates a clustered index named PRIMARY.
Transactions
Definition:
A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit.
What is Multilevel Transaction:
Nested transaction, in which the node of a transaction depends on the execution of other transactions.
More about Transaction:
A transaction is a single unit of logic or work, sometimes made up of multiple operations.
Any logical calculation done in a consistent mode in a database is known as a transaction.
It must either be complete in its entirety or have no effect whatsoever.
Problem (Without Transaction):
When execution prematurely and unexpectedly stops (completely or partially) in which case many operations upon a database remain uncompleted, with unclear status.
Solution (Using Transaction):
Transactions either complete the whole execution (All the operations in the transaction are completed), or, will have no database effect whatsoever.
It helps to prevent the incomplete operation in the database, which results in further data related errors.
Database Sharding (शार्डिंग) and Partitioning:
- These are the techniques to efficeintly manage large amount of data by splittting it into the multiple storages.
- What they do: Split the data into smaller pieces
- Purpose: Performance, scalability, manageability.
Sharding:
- Sharding refers to horizontal partitioning, where data is split into smaller, independent databases (called shards),
- Each capable of running on separate servers. Allowing queries to run in parallel
- Queries only target specific shards, reducing the load on individual databases.
Partitioning:
- Dividing a table into smaller pieces based on the field values.
- All partitions remain part of the same database instance.
- Improved Query Performance
| Sharding | Partitioning |
|---|
| Level of Splitting | Data is split across multiple databases, and the DBs can be hosted on multiple servers. | Data is split within a single database. |
| Purpose | Scalability. Used for Large-scale applications with millions of users. | Performance optimization within a database. (Used when the Large tables requiring optimization) |
| Example | Suppose you have a users table with millions of rows. You can shard it based on user_id:– Shard-1 for Users with IDs 1–100,000 – Shard-2 for 100,001–200,000 etc. | Suppose you have a users table with millions of rows and the user’s from different regions. You can partition it based on region: – Partition-1 for users from north region – Partition-2 for users from south region etc. |
| Base for Splitting data | The Data is splited based on the rows (field values) and so, It is also called as horizontal Partitioning or horizontal Scalling. | The data is splited based on one or more fields (columns) |
Foreign Key Rules:
- ON DELETE CASCADE
This constraint is used in MySQL to delete the rows from the child table automatically, when the rows from the parent table are deleted.
- ON UPDATE CASCADE:
SQL Server updates the corresponding rows in the child table when the rows in the parent table are updated.
- ON UPDATE RESTRICT
SQL Server raises an error and rolls back the update action on the row in the parent table.
- ON UPDATE NO ACTION
No action will be taken on update
Procedure (Stored Procedure):
- A procedure is a subprogram stored in a database.
- It can be used to create simple scripts for quickly querying data, updating data, generating reports and to improve overall database design, and database security.
CREATE PROCEDURE STUDENT_MARKS (IN STUDENT_REG_NO CHAR(15),IN TOTAL_MARKS DECIMAL(7,2), NO_SUBJECTS INT(3))
LANGUAGE SQL MODIFIES SQL DATA
UPDATE STUDENTMAST.MARKS
SET PERCENTAGE = TOTAL_MARKS/NO_SUBJECT
WHERE REG_NO = STUDENT_REG_NO
CALL STUDENT_MARKS[()]
DROP PROCEDURE STUDENT_MARKS
Routines:
- A stored routine is a set of SQL statements that can be stored in the server.
- Stored routines are more difficult to debug and test. The application that has a lot of business logic in stored routines is less scalable.
- no version control system.
Scalability : Ability of change
Mysql 8 Features Change Log:
- Expand json support for better performance
- Auto scales the memory usage, which helps in the deployment, memory allocation.
- Performance improvement
Difference between Where and Having:
- Where is used to filter the non-grouped data only (existing fields in the database)
- Having is used to filter the group data only (aggregated data like sum, max, etc.)
Index limit of a table:
16
Primary Vs Unique key:
- A table can have only one primary key which does not allow null values.
- A table can have multiple unique keys which also allow null values.
Composite key:
Composite key is useful when the uniqueness of a record depends on the combination of multiple columns. Doubted: It’s a primary key on the set of myltiple columns.
Heap Table:
Temporary tables which are stored into the memory are called Heap tables. It is made by mysql while indexing a column for the optimisation purpose.
– For better precision, use Decimal
Materialized View in Database:
A materialized view is a database object that contains the results of a query and it can be stored in a database table and will be refreshed as per the requirements. Materialized Views are used when immediate response is needed and the query takes too long to produce a result.
Diff:
Developed by
Latest Version
– Postgress (11.4):
Open source object-relational database (object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language)
PostgreSQL 11.4, 10.9,
Advantages:
- stability
- minimum maintains
- Used since last 30 years, so its bug free.
- Highly extensible
– Object Relational database:
An object-relational database, or object-relational database management system, is a database management system similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language
Postgress vs mysql:
Postgress mysql
Object relational database relational database
Highly extensible Not extensible
pgAdmin phpmyadmin
Provide online backup option Don’t provide a backup option. Can use mysqldump
Benefits of postgres:
It uses lots of standard of SQL. PostgreSQL is fully ACID compliant. Foreign Key support, triggers, and Union are available in PostgreSQL
| Feature | MySQL | PostgreSQL | MongoDB |
| Type | Relational Database (RDBMS) | Relational Database (RDBMS) | NoSQL (Document Store) |
| Data Structure | Tables, Rows | Tables, Rows | Documents (BSON, JSON) |
| Schema | Fixed | Flexible, Extensible |
Mongodb Commands:
- Database Level Queries:
- Create Database: use dbname
- Display Current database: db
- List all databases: show dbs
- Drop database: db.dropDatabase()
- Collection Level Queries:
- Create Collection: db.createCollection(name, options)
- List Collections: show collections
- Drop Collection: db.CollectionName.drop()
- Record Level Queries:
- Insert Record: db.group.insert({“group_name”:”subadmin”});
- Find Record: db.collection_name.find({“_id”:ObjectId(“5a672aca90e34338f92932eb”)})
- Sorting: db.collection_name.find().sort( { _id: -1 } )
- Update Record: db.collection.update({“GameType”:null},{$set:{“GameType”:”Unknown”}},false,true);
- Delete Record: db.collection.findOneAndDelete({‘EntityId’:’HARDCODED’})
- Delete Record: db.collection.remove({“SourceCollection”:{$exists:1}})
- Delete Field: db.collection.update({}, {$unset: {fieldName:1}}, false, true);
- Greater/Less conditions:
- db.collection.count({“Timestamp”:{‘$gte’:ISODate(“2018-01-16 00:00:00”),’$lte’:ISODate(“2018-01-17 23:59:59”)}})
- Setup Unique Index: db.collection.ensureIndex({field: 1}, {unique: true})
- To get the indexes: db.collection.getIndexes()
- To compare 2 fields: db.collection.count({$where : “this.StatisticValue < this.StatisticPreviousValue”})
- Group by : db.collection.group({ “key”:{“EntityId”:1}, “cond”:{“Timestamp”:{‘$gte’:ISODate(“2018-01-30 00:00:00”),’$lte’:ISODate(“2018-01-30 23:59:59”)}},reduce: function ( curr, result ) { },initial: { }})