SQL

Postgres

https://www.tutorialspoint.com/postgresql/postgresql_php.htm

Postres

distributes your queries to multiple postgres servers with:https://www.citusdata.com/download/

=-=-=-=-=-=-=-=-=-=-=-=-=-=-

MariaDB

Fork of the MySQL relational database management system intended to remain free under the GNU GPL.
Being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.
Some tools for interacting with MySQL, such as the MySQL Workbench, are not fully compatible with MariaDB.

Percona Server

Close compatibility to the official MySQL releases, while focusing on performance and increased visibility into server operations. Also included in Percona Server is XtraDB, Percona’s fork of the InnoDB Storage Engine. Percona freely includes a number of scalability, availability, security and backup features only available in MySQL’s commercial Enterprise edition.

=-=-=-=-=-=-=-=-=-

Free SQL manager and command editor

1) DBeaver  -> SQL server management GUI-> Has view first 100 rows+Support multilevel authentication

2) Heidisql->SQL server management GUI-> Does not have view first 100 rows

Does not support multilevel authentication

https://www.heidisql.com/download.php

3) pgAdmin3-> Support multilevel authentication -> Unstable

https://www.pgadmin.org/download/pgadmin-3-windows/

4) free all database  manager

http://fishcodelib.com/Database.htm

 

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

ERD tools
======================================================
DataBase:
Sqlite is the most used db; used also for mobile devices, can be installed on any server or as a desktop dll
both Precompiled Binaries for Windows are needed
one is exe the other is dll sqlite-shell-win32-x86-3080802.zip (306.08 KiB)
For table creation DB browser
Sqlite3Explorer  is nice admin tool + sql analyzer
There is a simple program Sqlite Designer
———————————-
Mysql is second mostly used:
 
A tutorial for workbench:
=====================================================

1. Data Definition Language(DDL):

 

CREATE TABLE STUDENT(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);

ALTER TABLE STUDENT ADD(ADDRESS VARCHAR2(20));
ALTER TABLE STUDENT MODIFY (ADDRESS VARCHAR2(20));

DROP TABLE STUDENT;

TRUNCATE TABLE STUDENT;       delete all the rows from the table, and free up the space in the table.

 

2. Data Manipulation Language(DML):

INSERT INTO STUDENT (Name, Subject) VALUES (“Scaler”, “DSA”);

UPDATE STUDENT
SET User_Name = ‘Interviewbit’
WHERE Student_Id = ‘2’

DELETE FROM STUDENT
WHERE Name = “Scaler”;

3. Data Control Language(DCL):

GRANT SELECT, UPDATE ON TABLE_1 TO USER_1, USER_2;

REVOKE SELECT, UPDATE ON TABLE_1 FROM USER_1, USER_2;

4. Transaction Control Language:

COMMIT: Saves all the transactions made on a database.

DELETE FROM STUDENTS
WHERE AGE = 16;
COMMIT;

 

ROLLBACK: It is used to undo transactions which are not yet been saved.

DELETE FROM STUDENTS
WHERE AGE = 16;
ROLLBACK;

SAVEPOINT: Used to roll transaction back to a certain point without having to roll back the entirity of the transaction.

SAVEPOINT SAVED;
DELETE FROM STUDENTS
WHERE AGE = 16;
ROLLBACK TO SAVED;

5. Data Query Language:

 

SELECT Name
FROM Student
WHERE age >= 18;

https://www.interviewbit.com/sql-cheat-sheet/

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

Loading