# SQL [[db|Database]]
- SQL = Structured Query Language
- The correct pronunciation is `SQL`, `sequal` should be referring to the Microsoft SQL Server.
- SQL Database is [[relational-db|relational database]], but not all relational databases uses SQL.
- Examples
- [[postgres|PostgreSQL]]
- [[mysql|MySQL]]
- [[maria|MariaDB]]
- [[sqlite|SQLite]]
- Microsoft SQL Server / Oracle / IBM DB2
- [[sql-injection]]
## Usage
- `\` for admin commands
- `--` for comment
```sql
CREATE DATABASE message_boards;
CREATE TABLE users (
user_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
username VARCHAR ( 25 ) UNIQUE NOT NULL.
full_name TEXT NOT NULL, -- TEXT has no upper bound
last_login TIMESTAMP
);
INSERT INTO users (username, email, full_name) VALUES ('...', ..., NOW());
SELECT * FROM users;
SELECT username, last_login FROM users LIMIT 5;
SELECT username, email FROM users WHERE user_id=150
SELECT username, email FROM users WHERE last_login IS NULL AND created_on < NOW() - interval '6 months' LIMIT 10;
SELECT username, email FROM users ORDER BY created_on DESC LIMIT 10;
SELECT COUNT(*) FROM users; -- * counts all rows included those with NULL
```
### `JOIN`
```sql
CREATE TABLE comments (
user_id INT REFERENCES users(user_id) ON DELETE CASCADE -- or SET NULL
)
SELECT comment_id, user.username, LEFT(comment, 20) -- users. can be omitted
AS preview FROM comments
INNER JOIN users
ON comments.user_id=users.user_id
WHERE borad_id=39;
SELECT comment_id, username, LEFT(comment, 20) -- users. can be omitted
AS preview FROM comments
NATURAL INNER JOIN users -- if you know columns names match
WHERE borad_id=39;
```
- `INNER/LEFT/RIGHT/OUTER/FULL OUTER/CROSS JOIN`
### `GROUP`
```sql
SELECT board_name, COUNT(comment_id) AS comment_count
FROM comments
NATURAL RIGHT JOIN boards -- RIGHT JOIN to include those without comments
GROUP BY board.board_name
ORDER BY comment_count ASC
LIMIT 10;
```