ELI5: SQL (Standard Query Language)
SQL stands for Standard Query Language and is the standard language for relational database management systems. Using SQL, data in a database can be manipulated with commands pertaining to CRUD (Create, Read, Update, Delete).
Quick pause, let’s review Table Basics —
Unlike object-oriented programming language, a relational database system holds object information in tables. Like Excel, these tables have both columns and rows with columns containing your datatype restrictions (datatypes explained further below) while your rows hold instances. Primary keys provide a unique identification of each row in a table. By converting our object-oriented classes into tables, querying to find what we want becomes much easier. 2:29 jpg
What are data types?
Data types specify what type of data value can be put in the rows. The most common ones being:
- char(size): a fixed-length character string. Specify the length in parenthesis.
- varchar(size): variable-length character string. Again, specify the length in parenthesis.
- number(size): number value with max number of digits specified in parenthesis.
- number(size, d): like number(size), but d indicating the max number of digits (“d”) to the right of the decimal.
- date: a date value
How to select data with SQL?
By using a select statement, the database can be queried to retrieve data we’re interested in.
Example: Parsing down a common SQL command:
“SELECT * FROM x WHERE y;”
(note: SQL commands end with a semi-colon)
“SELECT” — select the values which fit the specified parameters
“*” — all values
“FROM” + the table name to be queried
“WHERE” + conditionals — specifies which data values or rows to be returned. You can use conditional selections here including the following: equal (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), not equal to (<>), and like (“LIKE”).
What is “LIKE”?
“LIKE” is a pattern matching operator that selects only rows that are “like” what you specified. In conjunction with this, the percent sign, %, indicates that anything can come before (if % is before) or after (if % is after) the conditional.
Practice interpreting SQL commands:
SELECT first, last, city FROM x; = select the first, last, and city
SELECT * FROM x;
SELECT first name, last name, age FROM x WHERE last LIKE ‘%anything’;
SQL Command Cheat Codes
Create a simple table: create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type");
Create a table with constraints:create table "tablename"
("column1" "data type"
[constraint],
"column2" "data type"
[constraint],
"column3" "data type"
[constraint]);
[ ] = optional
Insert a new row to a table: insert into "tablename"
(first_column,...last_column)
values ('first_value',...'last_value');
*note: strings should be enclosed with single quotes ('')
Update your table:update "tablename"
set "columnname" = "newvalue"
[,"nextcolumn" = "newvalue2"...]
where "columnname" OPERATOR "value" [and|or "column"
OPERATOR "value"];
[] = optional
Delete everything from your table but keep the table: delete from "tablename"
Delete rows from your table:
(It’s the delete statement with your parameters)delete from "tablename"
where "columnname"
OPERATOR "value"
[and|or "column"
OPERATOR "value"];
[ ] = optional
If you want to delete/drop the whole table: drop table "tablename"
Easy, peasy, lemon squeezy
References:
http://www.sqlcourse.com/
https://en.wikipedia.org/wiki/SQL
https://www.w3schools.com/sql/default.asp
