DFT Wiki

VARIABLE TYPES

• INT
  • Whole numbers, no decimals.
• DECIMAL(M,N)
  • M is the total number of numbers and N is the number of decimals.
• VARCHAR(N)
  • Stores stings up to N characters.
• BLOB
  • Binary Large Objects, used to store files, for example.
• DATE
  • Format YYYY-MM-DD
• TIMESTAMP
  • Format YYYY-MM-DD HH:MM:SS

COMPARISON OPERATORS

• =
  • Equal to
• <>
  • Not equal to
• AND
  • And
• OR
  • Or
• <
  • Less than
• <=
  • Less than or equal to
• >
  • More than
• >=
  • More than or equal to

COMMENTING

--

CREATING DATABASE

CREATE DATABASE name_of_database;

CREATING TABLES (note the “;” at the end of the command and the “,” separating attributes):

CREATE TABLE name_of_the_table (
attribute_one INT PRIMARY KEY,
attribute_two VARCHAR(10) NOT NULL
);

CREATE TABLE name_of_the_table (
attribute_one INT PRIMARY KEY,
attribute_two VARCHAR(N) FOREIGN KEY REFERENCE name_of_the_table(attribute_one) ON DELETE SET NULL
);

CREATE TABLE name_of_the_table (
attribute_one INT AUTO_INCREMENT,
attribute_two VARCHAR(N) UNIQUE,
PRIMARY KEY(attribute_one, attribute_two),
FOREIGN KEY(attribute_two) REFERENCE name_of_the_table(attribute_one) ON DELETE SET CASCADE
);

MODIFYING TABLES

ALTER TABLE name_of_the_table ADD attibute_three DATE;
ALTER TABLE name_of_the_table ADD attibute_three DECIMAL(3,1) DEFAULT 'default_value';
ALTER TABLE name_of_the_table DROP COLUMN attribute_three;

DESCRIBING, DELETING, AND WHIPPING TABLES

DESCRIBE name_of_the_table;
DROP name_of_the_table;
TRUNCATE name_of_the_table;

INSERTING DATA

INSERT INTO name_of_the_table VALUES(1, 'text_one', 'text_two');
INSERT INTO name_of_the_table(attribute_one, attribute_two) VALUES(1, 'text_one');

DELETING DATA

DELETE FROM name_of_the_table WHERE attribute_one = 3;

CHANGING TABLES

UPDATE name_of_the_table SET attribute_one = '3' WHERE attribute_one = '1' OR attribute_two = '2';
UPDATE name_of_the_table SET attribute_one = '3', attribute_two = 'ABC' WHERE attribute_one = '1' AND attribute_two = 'XYZ';
DELETE FROM name_of_the_table WHERE attribute_one = '1';

ACQUIRING DATA

SELECT * FROM name_of_the_table WHERE 1;
SELECT attibute_one AS id FROM name_of_the_table;
SELECT attribute_two, attribute_three FROM name_of_the_table ORDER BY attribute_two;
SELECT name_of_the_table.attribute_two FROM name_of_the_table ORDER BY attribute_two ASC, attribute_one DESC;
SELECT attribute_two FROM name_of_the_table LIMIT 2;
SELECT * FROM name_of_the_table WHERE attribute_one IN (1, 5, 10, 15);
SELECT DISTINCT attribute_two FROM name_of_the_table;

UNION

SELECT attribute_one FROM name_of_the_table
UNION
SELECT attribute_three FROM name_of_the_table;

JOINS (translate the codes from one table into another)

SELECT name_of_the_table.attibute_one, name_of_other_table.attibute_two
FROM name_of_the_table JOIN name_of_other_table
ON name_of_the_table.attibute_one = name_of_other_table.attribute_two;

LEFT JOIN (also returns the null value from the left table)

SELECT name_of_the_table.attibute_one, name_of_other_table.attibute_two
FROM name_of_the_table LEFT JOIN name_of_other_table
ON name_of_the_table.attibute_one = name_of_other_table.attribute_two;

RIGHT JOIN(also returns the null value from the right table)

SELECT name_of_the_table.attibute_one, name_of_other_table.attibute_two
FROM name_of_the_table RIGHT JOIN name_of_other_table
ON name_of_the_table.attibute_one = name_of_other_table.attribute_two;

FULL JOIN

SELECT name_of_the_table.attibute_one, name_of_other_table.attibute_two
FROM name_of_the_table FULL JOIN name_of_other_table
ON name_of_the_table.attibute_one = name_of_other_table.attribute_two;

INNER JOIN

SELECT name_of_the_table.attibute_one, name_of_other_table.attibute_two
FROM name_of_the_table INNER JOIN name_of_other_table
ON name_of_the_table.attibute_one = name_of_other_table.attribute_two;

SQL FUNCTIONS

SELECT COUNT(attribute_one) FROM name_of_the_table;
SELECT AVG(attribute_one) FROM name_of_the_table;
SELECT SUM(attribute_one) FROM name_of_the_table;
SELECT COUNT(attribute_one), attribute_one FROM name_of_the_table GROUP BY attribute_one;
SELECT SUM(attibrute_three), attribute_one FROM name_of_the_table GROUP BY attribute_one;

WILDCARDS

• %
  • any number of characters.
• _
  • one character.

SELECT * FROM name_of_the_table WHERE attribute_two LIKE '%word_';

NESTED QUERIES (fetched data from one query feeds another query)

SELECT name_of_the_table.attribute_two
FROM name_of_the_table
WHERE name_of_the_table.attibute IN (
  SELECT name_of_other_table.attribute_three
  FROM name_of_other_table
);

SELECT name_of_the_table.attribute_two
FROM name_of_the_table
WHERE name_of_the_table.attibute = (
  SELECT name_of_other_table.attribute_three
  FROM name_of_other_table LIMIT 1
);

TRIGGERS

DELIMITER $$

CREATE TRIGGER trigger_name BEFORE INSERT
ON name_of_the_table
FOR EACH ROW BEGIN
IF NEW.attribute_two = 1 THEN
  INSERT INTO name_of_the_table VALUES('info 1');
ELSEIF NEW.attribute = 2 THEN
  INSERT INTO name_of_the_table VALUES('info 2');
ELSE
  INSERT INTO name_of_the_table VALUES('info 3');
END IF;
END$$

DELIMITER ;

Instead, BEFORE INSERT, try also AFTER, UPDATE, DELETE…