SQL Commands: Difference between revisions
Jump to navigation
Jump to search
| (18 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
= | == PostgreSQL == | ||
== Functions == | {| class="wikitable" | ||
! 🎯 Purpose || ⌨️ Command | |||
|- | |||
| Connection test<br>(host) || | |||
<syntaxhighlight lang="bash"> | |||
PGPASSWORD='****' psql -U user -h localhost -p 15432 -d dbname -c 'SELECT 1' | |||
PGPASSWORD='****' psql postgres://user@localhost:15432/dbname?sslmode=require -c 'SELECT 1' | |||
</syntaxhighlight> | |||
|- | |||
| Connection test<br>(container) || | |||
<syntaxhighlight lang="bash"> | |||
sudo docker exec -e PGPASSWORD='****' -it postgis-ssl psql -U user -d dbname -c 'SELECT 1' | |||
</syntaxhighlight> | |||
|} | |||
== Common Operations == | |||
=== Functions === | |||
{| class="wikitable" | {| class="wikitable" | ||
| Line 12: | Line 29: | ||
|} | |} | ||
== Visiting == | === Visiting === | ||
| Line 24: | Line 41: | ||
|} | |} | ||
== | === CREATE USER === | ||
{| class="wikitable" | {| class="wikitable" | ||
! || Postgres || MariaDB | ! || Postgres || MariaDB | ||
|- | |- | ||
| | | create user | ||
| <syntaxhighlight lang="sql"> | | <syntaxhighlight lang="sql"> | ||
CREATE USER username WITH PASSWORD 'your_password'; | |||
</syntaxhighlight> | </syntaxhighlight> | ||
| <syntaxhighlight lang="sql"> | | <syntaxhighlight lang="sql"> | ||
</syntaxhighlight> | </syntaxhighlight> | ||
|- | |- | ||
| | | change password | ||
| <syntaxhighlight lang="sql"> | | <syntaxhighlight lang="sql"> | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| <syntaxhighlight lang="sql"> | | <syntaxhighlight lang="sql"> | ||
</syntaxhighlight> | </syntaxhighlight> | ||
|} | |||
=== GRANT === | |||
{| class="wikitable" | |||
! || Postgres || MariaDB | |||
|- | |- | ||
| | | on database to user | ||
| <syntaxhighlight lang="sql"> | | <syntaxhighlight lang="sql"> | ||
GRANT ALL PRIVILEGES | |||
ON DATABASE operation_center | |||
TO db_user; | |||
</syntaxhighlight> | </syntaxhighlight> | ||
| <syntaxhighlight lang="sql"> | | <syntaxhighlight lang="sql"> | ||
</syntaxhighlight> | </syntaxhighlight> | ||
|- | |- | ||
| | | on table to user | ||
| <syntaxhighlight lang="sql"> | | <syntaxhighlight lang="sql"> | ||
GRANT ALL PRIVILEGES | |||
ON ALL TABLES | |||
IN SCHEMA public | |||
TO db_user; | |||
</syntaxhighlight> | </syntaxhighlight> | ||
| <syntaxhighlight lang="sql"> | | <syntaxhighlight lang="sql"> | ||
| Line 54: | Line 85: | ||
|} | |} | ||
= SQLite 3 = | == SQLite 3 == | ||
{| class="wikitable" | {| class="wikitable" | ||
! TODO || Command | ! TODO || Command | ||
Latest revision as of 09:00, 20 October 2026
PostgreSQL
| 🎯 Purpose | ⌨️ Command |
|---|---|
| Connection test (host) |
PGPASSWORD='****' psql -U user -h localhost -p 15432 -d dbname -c 'SELECT 1'
PGPASSWORD='****' psql postgres://user@localhost:15432/dbname?sslmode=require -c 'SELECT 1'
|
| Connection test (container) |
sudo docker exec -e PGPASSWORD='****' -it postgis-ssl psql -U user -d dbname -c 'SELECT 1'
|
Common Operations
Functions
| Postgres | MariaDB | SQLite | |
|---|---|---|---|
| Today |
Visiting
| Postgres | MariaDB | SQLite | |
|---|---|---|---|
| List tables | show tables; |
.tables |
CREATE USER
| Postgres | MariaDB | |
|---|---|---|
| create user | CREATE USER username WITH PASSWORD 'your_password';
|
|
| change password |
GRANT
| Postgres | MariaDB | |
|---|---|---|
| on database to user | GRANT ALL PRIVILEGES
ON DATABASE operation_center
TO db_user;
|
|
| on table to user | GRANT ALL PRIVILEGES
ON ALL TABLES
IN SCHEMA public
TO db_user;
|
SQLite 3
| TODO | Command |
|---|---|
| yyyymmdd to yyyy-mm-dd |
SELECT printf('%s-%s-%s',
SUBSTR(trading_date,1,4),
SUBSTR(trading_date,5,2),
SUBSTR(trading_date,7,2)
) FROM short_sell WHERE trading_date='20190605';
|