In the Unix ecosystem, there isn't a
single "database" built-in as a classic SQL software. Instead,
the Unix philosophy uses the
file system and
standard Bash commands to manipulate text
files (usually CSV or TSV) as if they were a relational database.
Here is how to perform database
operations (CRUD) using only standard commands:
1.
Storage and Structure (Tables)
In Unix, a "table" is typically a
plain text file where each line is a record and columns are
separated by a delimiter like a comma, tab, or colon.
- Create a
table:
touch
users.csv
- Add a header:
echo "id,name,email"
> users.csv
2. Data Operations (CRUD)
- Create
(Insert): Append
data to the end of the file using
echo and the >> redirector.
echo
"1,Alice,alice@example.com" >> users.csv
- Read
(Select):
- View all:
cat
users.csv
- Filter
rows (WHERE):
grep
"Alice" users.csv
- Extract
columns (PROJECT):
cut
-d',' -f1,2 users.csv
- Update: Use the stream
editor
sed to replace text
within the file.
sed
-i 's/Alice/Alicia/g' users.csv
- Delete:
- Remove
specific row:
grep
-v "Alice" users.csv > temp.csv && mv
temp.csv users.csv
- Clear
table:
>
users.csv
For complex logic, use these
specialized Unix power tools:
awk:
A complete pattern-scanning and processing language designed
for column-based data.sort & uniq:
Used to order data and remove or identify duplicate records.join:
Merges two files based on a common field (equivalent to a SQL JOIN).
4. Interacting with "Real" Databases
Bash is frequently used as an
interface to automate queries for external database engines like
MySQL or PostgreSQL:
- MySQL:
mysql
-u user -p -e "SELECT * FROM table" db_name
- PostgreSQL:
psql -c "SELECT *
FROM table" db_name
- Automation: Bash scripts
often handle automated database
backups using
tools like
mysqldump.