sql

SQL Database

• SQL = Structured Query Language
• The correct pronunciation is SQL, sequal should be referring to the Microsoft SQL Server.
• SQL Database is relational database, but not all relational databases uses SQL.
• Examples
  • PostgreSQL
  • MySQL
  • MariaDB
  • SQLite
  • Microsoft SQL Server / Oracle / IBM DB2
• sql-injection

Usage

• \ for admin commands
• -- for comment

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

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

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;