SQL

Resources

  • SQL = Structured Query language
  • Query language to work with a database management system (DBMS)

Resources:

MySQL

  • every query ends with a semicolon ;!

SQLite3

  • lightweight database to use e.g. in iOS apps

SQL Comment

-- my comment

Create a table

CREATE TABLE tableName(parameter <TYPE>, ...)

CREATE TABLE posts_database(
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    text VARCHAR(140)
);

CREATE TABLE student(
    student_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    major VARCHAR(40) DEFAULT 'TBA',
    PRIMARY KEY(student_id)
);

CREATE TABLE IF NOT EXISTS tableName(
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  ingredient VARCHAR(128) NOT NULL,
  category VARCHAR(256)
);
  • INTEGER
  • DECIMAL
  • DECIMAL(t,a) (t total precision, a digits after comma)
  • VARCHAR(140)
  • BLOB
  • DATE (YYYY-MM-DD)
  • TIMESTAMP (YYYY-MM-DD HH:MM:SS)

Constraints

  • id INT PRIMARY KEY
  • keyVariable INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(keyVariable)
  • UNIQUE
  • NOT NULL
  • DEFAULT 'TBA'

Describe table structure

MySQL

DESCRIBE myTable

SQLite3

.schema

Delete a table

DROP TABLE myTable

Alter table columns

add column

ALTER TABLE student ADD gpa DECIMAL(3,2)
ALTER TABLE user ADD email TEXT;

delete column

ALTER TABLE student DROP COLUMN gpa

Insert into table

INSERT INTO tableName(<arguments>) VALUES (<valuesForArguments>)

INSERT INTO posts VALUES(1, 'blablabla', 4.7)

INSERT INTO posts(text) VALUES('blablabla')

INSERT INTO posts(name, age) VALUES('Marta', 23)

Selecting from table

select all columns and rows of a database

SELECT * FROM database

select columns

SELECT column1 AS ‘column 1’, column2 AS ‘column 2’ FROM database

selecting rows

SELECT * FROM database WHERE someValue = ''

count all coumns

SELECT COUNT(*) FROM database
SELECT COUNT(DISTINCT name) FROM database

minimum

SELECT MIN(score) FROM database

// ...

SELECT column1, COUNT(column2) FROM database GROUP BY cloumn1 

SELECT student.id, student.name
FROM student

order

SELECT *
FROM student
ORDER BY student_id ASC -- ascending (default)

SELECT *
FROM student
ORDER BY major, name -- order first by major, then by name

limited number of entries

SELECT *
FROM student
ORDER BY student_id DESC
LIMIT 2

filtering

SELECT *
FROM database
WHERE field = 'Biology' OR name = 'Kate'

operators

>, <, >=, <=, <> (not equal to), =, AND, OR

in

SELECT *
FROM database
WHERE name IN ('Claire', 'Kate', 'Mike')

Update table data

UPDATE student
SET major = 'Bio'
WHERE major = 'Biology'

UPDATE student
SET field = 'ok'
WHERE id > 13

Delete table rows

DELETE FROM student
WHERE student_id = 5

delete all table rows

DELETE * FROM table_name;