2022-03-02
For table querying and manipulation.
Structured Query Language (SQL) is used to retrieve and modify information in a relational database management system like MySQL, PostgreSQL, SQLite, Microsoft SQL, Oracle, and others. Relational databases store data in large relational tables, where each row must conform to the types specified in the table columns, where cell contents must be either data, nothing, or a reference to a row in another table.
Use the W3Schools Try-It Editor to tinker with SQL now.
Object Relational Mappers (ORMs) are abstractions used by web developers to interact with relational databases.
Modern developers could live out a career without ever touching SQL due to the variety of well built ORMs that exist to translate objects built in an object-oriented language to SQL queries for insertion, retrieval, and manipulation. This is unfortunate in the same way a lack of knowledge about a CPU, machine code, assembly languages, or C is unfortunate: It means the programmer in question is operating on blind abstraction. This is obviously useful right up to the moment when performance tuning, a bug, or some other issue necessitating critical introspection of a codebase appears.
All of the aforementioned DBMS (Database Management System) flavors like MySQL and PostgreSQL use similar dialects of a common SQL standard. Statements are often similar or identical, but each flavor has slight differences in syntax.
Flavors differentiate themselves with the features they offer the business and developer, including:
It’s easy to install PostgreSQL on alpine linux with this set of commands. This will install the database system, add it as a default program to run, and open the firewall to allow external access to the database.
sudo su
apk update
apk add postgresql
service start postgresql
rc-update add postgresql default
ufw allow postgresql/tcp
You can then find the configuration files by writing:
psql -U postgres -c 'SHOW config_file'
# /etc/postgresql/postgresql.conf
psql -U postgres -c 'SHOW data_directory'
# /var/lib/postgresql/13/data
Enable remote connections with these actions:
echo "host all all 0.0.0.0/0 md5" >> /var/lib/postgresql/13/data/pg_hba.conf
echo "listen_addresses='*'" >> /var/lib/postgresql/13/data/postgresql.conf
Locking this down once you find you can connect is a good idea.
Check this and that for further setup guidance.
For running commands use DbGate
In the psql shell you can create, list, and drop whole databases. Use caution with these commands.
postgres=# CREATE DATABASE chicken_coop;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------------+----------+----------+---------+---------+-----------------------
chicken_coop | postgres | UTF8 | C | C.UTF-8 |
dog_app | ryan | UTF8 | C | C.UTF-8 |
pet_shop | ryan | UTF8 | C | C.UTF-8 |
postgres | postgres | UTF8 | C | C.UTF-8 |
postgres=# DROP DATABASE chicken_coop;
DROP DATABASE
To change the database you are in use \c.
postgres=# \c dog_app
You are now connected to database "dog_app" as user "postgres".
From here you can use your usual SQL commands. Show tables with \dt.
This section contains a short usage guide for each of the common SQL commands, with one or two composition examples.
CREATE TABLE <name> (
column_name type,
column_name type,
column_name type,
column_name type
);
CREATE TABLE tweets (
username VARCHAR(100),
tweet_content VARCHAR(140),
favourites INT
);
ALTER TABLE <table> ADD <column name> <type>;
ALTER TABLE <table> RENAME TO <new_table_name>;
Retreives data from a table.
SELECT <columns or * for all> FROM <table>;
SELECT * FROM products;
Given a table, provide the columns and data you’d like to insert.
INSERT INTO some_table(col1, col2) VALUES ('data1', 111);
CREATE TABLE people (
first_name VARCHAR(100),
last_name VARCHAR(100),
age int
);
INSERT INTO people(first_name, last_name, age) VALUES
('Michael', 'Sweeny', 23),
('Phillip', 'Frond', 38),
('Calvin', 'Kleinfelter', 65);
UPDATE <table> SET <column>=<value>, <column2>=<value> WHERE <condition>;
DELETE FROM <table> WHERE <condition>;
Here’s a sample table:
CREATE TABLE employees (
id BIGSERIAL PRIMARY KEY,
last_name VARCHAR(100) NOT NULL,
first_name VARCHAR(100) NOT NULL,
middle_name VARCHAR(100),
age INT NOT NULL,
current_status VARCHAR(100) NOT NULL DEFAULT 'employed'
);
Providing the requred values and allowing the defaults to autopopulate:
# insert into employees(first_name, last_name, age) values ('greg', 'torbo', 21);
INSERT 0 1
# select * from employees;
id | last_name | first_name | middle_name | age | current_status
----+-----------+------------+-------------+-----+----------------
1 | torbo | greg | | 21 | employed
Can be provided in either of these formats:
CREATE TABLE board_members (
member_id int PRIMARY KEY, -- option one
name VARCHAR(100),
age INT,
PRIMARY KEY(member_id) -- option two
);
This is also a good place to use AUTO_INCREMENT in other databases, which provides a default of the next integer. Postgres provides the SERIAL type which has the same function.
When designing tables, many different constraints can be provided.
NOT NULL ensures there cannot be an empty insertion and cannot be null. With no default value, an insert without this value will fail.
CREATE TABLE glargons (
name VARCHAR(100) NOT NULL,
age int
);
playground=# \d glorgons
name | character varying(100) | | not null |
age | integer | | |
DEFAULT <value> provides … a default value.
This is just awesome. Included because people should think ethically about the things they create, especially fintech software.
“I could have edited the list down to just those aspects that seem relevant to coding, but that would put me in the position of editing and redacting Benedict of Nursia, as if I were wiser than he. And I considered that. But in the end, I thought it better to include the whole thing without change (other than translation into English). In the preface, I tried to make clear that the introspective aspects could be safely glossed over.” - Richard Hipp
This document was originally called a “Code of Conduct” and was created for the purpose of filling in a box on “supplier registration” forms submitted to the SQLite developers by some clients. However, we subsequently learned that “Code of Conduct” has a very specific and almost sacred meaning to some readers, a meaning to which this document does not conform [1][2][3]. Therefore this document was renamed to “Code of Ethics”, as we are encouraged to do by rule 71 in particular and also rules 2, 8, 9, 18, 19, 30, 66, and in the spirit of all the rest.
This document continues to be used for its original purpose - providing a reference to fill in the “code of conduct” box on supplier registration forms.
The founder of SQLite, and all of the current developers at the time when this document was composed, have pledged to govern their interactions with each other, with their clients, and with the larger SQLite user community in accordance with the “instruments of good works” from chapter 4 of The Rule of St. Benedict (hereafter: “The Rule”). This code of ethics has proven its mettle in thousands of diverse communities for over 1,500 years, and has served as a baseline for many civil law codes since the time of Charlemagne.
No one is required to follow The Rule, to know The Rule, or even to think that The Rule is a good idea. The Founder of SQLite believes that anyone who follows The Rule will live a happier and more productive life, but individuals are free to dispute or ignore that advice if they wish.
The founder of SQLite and all current developers have pledged to follow the spirit of The Rule to the best of their ability. They view The Rule as their promise to all SQLite users of how the developers are expected to behave. This is a one-way promise, or covenant. In other words, the developers are saying: “We will treat you this way regardless of how you treat us.”
Title: SQL
Word Count: 1850 words
Reading Time: 9 minutes
Permalink:
→
https://manuals.ryanfleck.ca/sql/