Skip to content

Latest commit

 

History

History
232 lines (165 loc) · 7.56 KB

sql.md

File metadata and controls

232 lines (165 loc) · 7.56 KB

[TOC]

Data Storage

OLTP (online transactional processing)

OLAP (online analytical processing)

SQL (Structured Query Language)

"An RDBMS (Relational Database Management System) is a database management system based on the relational model..., which in turn is based on two mathematical branches: set theory and predicate logic" - T-SQL Fundamentals | Itzik Ben-Gan

Dialects

MySQL

  • In most SQL languages, schemas are "conceptually within" databases. In MySQL, "...physically, a schema is synonymous with a database" - Documentation

Useful Links

Overview

ER diagrams allows one to visualize how a concept might map into a RDBMS layout, or visualize an existing RDBMS layout.

SQL is case and whitespace insensitive

  • DML = Data manipulation Language
    • Examples: INSERT, UPDATE, DELETE (sometimes SELECT)
  • DDL = Data Definition Language
    • Examples: CREATE, DROP, ALTER
  • When referencing an object in SQL, proper convention is to explicitly call the entire object (i.e. database_name.schema_name.table_name, etc)

Querying

Basic structure:

SELECT ...
FROM ...
JOIN ...
ON ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...;

Logical processing order of a SQL query:

FROM/JOIN ...
ON ...
WHERE ...
GROUP BY ...
HAVING ...
SELECT ...
DISTINCT ...
ORDER BY ...
TOP(LIMIT, OFFSET, FETCH, etc) ...

Strings

To return rows that match a certain string/character sequence:

WHERE <COLUMN> LIKE '%b'
# return all observations that end with a 'b' (can have any
# characters preceding the 'b')
WHERE <COLUMN> LIKE 'b%'
# return all o0bservations that start with a 'b' (can have any
# characters following the 'b')
WHERE <COLUMN> LIKE '%b%'
# return all observations that contain a 'b' (can have any
# characters before or after the 'b')

JOINS

  • LEFT (OUTER) JOIN - Return all observations in the left table, along with the rows from the right table that have a match in the left table.

  • RIGHT (OUTER) JOIN - Return all observations in the right table, along with the rows from the left table that have a match in the right table. (More commonly one will see a LEFT JOIN with the tables switched as opposed to a RIGHT JOIN)

  • INNER JOIN - Return observations where all information is present in both tables

  • FULL JOIN - Return all observations from both tables, regardless of whether the information is present in the other table.

  • SELF JOIN - When joining a table to itself, table aliases must be used along with the JOIN keyword.

    SELECT alias_1.employee_id, alias_2.manager_id
FROM table_1 alias_1
JOIN table_2 alias_2
    ON alias_1.id = alias_2.id;

    Joining a table with itself is traditionally used when one wants to compare the values in one column to a value to another column within the same table.

Database Management/Creation

Stored Procedures

At its most basic, stored procedures are chunks of code that are saved (hence "stored") as objects in a SQL database. They allow a user to execute that code without having to retype it every time they want to use it. (analogous to writing functions in Python vs writing a script that can't be generalized to other tasks).

Stored procedures can be used to INSERT, UPDATE, DELETE, SELECT and are called with the EXEC statement (short for execute):

EXEC <stored_procedure_name>

To create a stored procedure:

CREATE PROC <procedure_name>
AS
    <T-SQL code>
GO

To alter a stored procedure:

ALTER PROC <procedure_name>
AS
    <T-SQL code>
GO

SSMS (SQL Server Management Studio)

  • Many table will start with 'dbo' - this is the default schema in SSMS
  • Using a TOP clause (synonymous to LIMIT in PostgreSQL) allows you to not overload the system
  • use NOLOCK
    • Ensures your queries have as little impact on other processes as possible.
    • The risk of running your query with NOLOCK is that you might get "dirty/phantom" reads (observations that are not yet committed to the database, but is in an indeterminate state of being inserted, updated or deleted)
    • Should be used primarily for ad-hoc queries, but not in development (NEVER use when updating or deleting observations from a table).
  • Check the estimated query plan before executing your query
  • Knowing the indexes a table has can be helpful in determining what columns to efficiently filter and sort on
  • ORDER BY clauses restrict the performance of queries

To connect to SSMS from the commmand line:

`Z: \> SQLCMD -S <server_instance>`

if you are connected the command prompt will change to:

`1>`

Execution Plans

Execution Plan: The result of the query optimizer's attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted. Execution plans are the primary means of troubleshooting an inefficient query.

  • The Query Optimizer parses your query and generates an execution plan (in binary) that is sent to the storage engine.
  • Highlight a query and press CTRL + L (Windows) to view the execution plan
  • The Query Optimizer determines the best exectution plan based on required CPU usage and I/)

Resources

PostgreSQL Practice

PostgreSQL Commands

DB Backup

  1. Dump the contents of any DB's you want backed up to a file.
# the `-p` stands for password so you will be prompted for the password of the <username> you use

# framework
$ mysqldump -u <username> -p -x -B < DB names you would like copied separated by spaces > <filename_of_your_choice>.sql

# example
$ mysqldump -u Dev1 -p -x -B Stocks Options > ~/data_backup_2022_03_27.sql
  1. SCP that file to your backup server.
# from the backup server
$ scp <username>@<original_server_name/ip>:/path/to/filename_of_your_choice .
  1. Start mariadb on the backup server (the one you just copied the dump file to) and run source <filename_of_your_choice>.sql

Troubleshooting

This section exists because, at some point, I spent enough time banging my head against the wall with one of the problems that I thought it would be worth putting the solution somewhere on the off chance that I run into the same thing again at some point.

  1. Problem: MariaDB won't start/I can't login to the the MariaDB/MySQL REPL
  • After installing MariaDB with $ brew install mariadb, try running $ brew services ls. You should see the following (as it relates to mariadb - you may have other output present as well): 
    $ brew services ls
Name       Status  User              File
mariadb    started marshallmcquillen ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist
  • If for some reason $ brew services start mariadb isn't working (YOU HAVE TO RESTART YOUR COMPUTER AFTER RUNNING THIS FOR THE CHANGES TO TAKE EFFECT), try removing any log files (as well as any *.err files) in the /usr/local/var/mysql/ directory (see this SO answer) and then re-trying $ brew services start mariadb (again, you will have to restart you computer).

TODO

  • ( 2022-09-19 ): Look into the execution order of joins in order to better understand them...