MySQL Cheat Sheet (For A/L Dummies)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

copy### CREATING DATABASES
CREATE DATABASE zoo; #Make a new databse, obviously lol
SHOW DATABASES;
USE zoo;

SHOW TABLES; 
DESCRIBE animal; #info about a specified table.

DROP DATABASE zoo; #To delete a database.
DROP TABLE animal; #To delete a table.

### CREATING TABLES
CREATE TABLE animal (  
  id INT NOT NULL, 
  name VARCHAR(64),  
  species VARCHAR(64),  
  age INT,  
  habitat_id INT,  
  FOREIGN KEY (habitat_id) REFERENCES habitat(id)  
);

### MODIFIYING TABLES
# Use the `ALTER TABLE` to modify the table structure. Not the data itself
# `ALTER TABLE` with `ADD` adds stuff to the table
ALTER TABLE animal ADD scientific_name VARCHAR(100);
ALTER TABLE animal ADD PRIMARY KEY(scientific_name);
ALTER TABLE animal ADD 
    FOREIGN KEY(scientific_name) 
    REFERENCES scarydatabase(scientific_name);

# `ALTER TABLE` with `DROP` removes Stuff from Tables.
ALTER TABLE animal DROP age;
ALTER TABLE animal DROP PRIMARY KEY;
ALTER TABLE animal DROP FOREIGN KEY;

35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

copy### INSERTING DATA
INSERT INTO animal(id,name,species,age,habitat_id) VALUES (1, 'Dog', 'tikkcutekollo', 2, 001);
INSERT INTO animal VALUES (2, 'Cat', 'cutekollo', 10, 001);
INSERT INTO animal SET 
    id=3,
    name='cockroach',
    species='uglykollo',
    age=1,
    habitat_id=001;

### UPDATING DATA
UPDATE animal SET species='cutekollo', age = 4 WHERE habitat_id = 001;

### DELETING DATA
DELETE FROM animal WHERE id = 1;
TRUNCATE TABLE animal;

### QUERYING DATA aka SELECTING DATA aka RETRIVING DATA
#An example of a single-table query
SELECT name,species FROM animal  
    WHERE id != 3
    GROUP BY species  
    HAVING AVG(age) > 3  
    ORDER BY AVG(age) DESC;

# Use `DISTINCT` to display only the different values among duplicates
SELECT DISTINCT name FROM animal;

## Conditions
SELECT * FROM animal WHERE species='uglykollo' AND age > 9;
SELECT * FROM animal WHERE species='cutekollo' OR habitat_id=001;
SELECT * FROM animal WHERE id NOT IN (1,2);  #`IN` is equal to multiple OR conditions
SELECT * FROM animal WHERE age BETWEEN 13 AND 18;  #to get teenage animals in the zoo XD
SELECT * FROM animal WHERE name LIKE 'S%';
SELECT * FROM animal WHERE name LIKE '%s';
SELECT * FROM animal WHERE name LIKE 'K___';
SELECT * FROM animal WHERE name NOT LIKE 'San%';