1 of 25

SQL - DDL 2

Mark Fontenot, PhD

Northeastern University

2 of 25

CREATE TABLE Examples - 1

CREATE TABLE actors (

actor_id SMALLINT UNSIGNED NOT NULL,

first_name VARCHAR(45),

last_name VARCHAR(45),

last_update TIMESTAMP,

PRIMARY KEY (actor_id)

);

2

3 of 25

CREATE TABLE Examples - 1

CREATE TABLE actors (

actor_id SMALLINT UNSIGNED PRIMARY KEY,

first_name VARCHAR(45),

last_name VARCHAR(45),

last_update TIMESTAMP

);

3

4 of 25

CREATE TABLE Examples - 2

CREATE TABLE follow_follower AS

SELECT u.username AS userName,

uf.username AS followerName

FROM users u

JOIN follows f ON u.id = f.followee_id

JOIN users uf ON uf.id = f.follower_id;

​

4

5 of 25

auto_increment

  • Column attribute telling MySQL to automatically provide an incrementing value for this attribute
  • Only 1 column per table; must be integer data type

5

CREATE TABLE animals (

id INTEGER AUTO_INCREMENT NOT NULL,

name VARCHAR(20),

PRIMARY KEY (id)

);

​

INSERT INTO animals (name)

VALUES ('dog'), ('cat'), ('penguin');

​

SELECT *

FROM animals;

6 of 25

auto_increment

6

DELETE FROM animals

WHERE name = 'penguin';

​

SELECT * FROM animals;

​

INSERT INTO animals (name)

VALUES ('husky');

​

SELECT * FROM animals;

7 of 25

Time Stamps

7

CREATE TABLE vet_visit (

visit_seq_no INT NOT NULL auto_increment,

pet_id int NOT NULL,

vet_last_name VARCHAR(50),

​

create_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,

update_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP,

CONSTRAINT pk PRIMARY KEY (visit_seq_no),

CONSTRAINT fk_01 FOREIGN KEY (pet_id)

REFERENCES animals (id)

ON UPDATE CASCADE

ON DELETE RESTRICT

);

INSERT INTO vet_visit (pet_id,

vet_last_name)

VALUES (1, 'Temple'),

(1, 'Huang'),

(2, 'Caraway');

8 of 25

Time Stamps

8

Before:

UPDATE vet_visit

SET vet_last_name = 'Temple'

WHERE visit_seq_no = 3;

Then:

9 of 25

Time Stamps

9

Before:

UPDATE vet_visit

SET vet_last_name = 'Temple'

WHERE visit_seq_no = 3;

Then:

After:

10 of 25

Indexing

10

11 of 25

Searching for Data

  • Searching is probably the most frequent operation performed on a database
  • The structure of the table can impact performance
  • Performance has to consider more than just in-memory searching

11

12 of 25

Searching - Linear

12

13

ID Some_other_data

18

21

25

28

30

37

38

41

45

SELECT *

FROM cool_data

WHERE id = 37;

​

13 of 25

Searching - Linear

13

13

ID Some_other_data

18

21

25

28

30

37

38

41

45

SELECT *

FROM cool_data

WHERE id = 37;

​

Is this 37?

Is this 37?

Is this 37?

Is this 37?

Is this 37?

Is this 37?

Is this 37? ✅

14 of 25

Searching - Linear

14

13

ID Some_other_data

18

21

25

28

30

37

38

41

45

SELECT *

FROM cool_data

WHERE id = 37;

​

Is this 37?

Is this 37?

Is this 37?

Is this 37?

Is this 37?

Is this 37?

Is this 37? ✅

In the worst case, how many IDs do we have to examine to fully execute the query?

15 of 25

Searching - Binary Search

15

13

ID Some_other_data

18

21

25

28

30

37

38

41

45

SELECT *

FROM cool_data

WHERE id = 37;

​

Is this 37?

16 of 25

Searching - Binary Search

16

13

ID Some_other_data

18

21

25

28

30

37

38

41

45

SELECT *

FROM cool_data

WHERE id = 37;

​

Is this 37?

Is this 37?

17 of 25

Searching - Binary Search

17

13

ID Some_other_data

18

21

25

28

30

37

38

41

45

SELECT *

FROM cool_data

WHERE id = 37;

​

Is this 37?

Is this 37?

Is this 37?

18 of 25

Searching - Binary Search

18

13

ID Some_other_data

18

21

25

28

30

37

38

41

45

SELECT *

FROM cool_data

WHERE id = 37;

​

Is this 37?

Is this 37?

Is this 37?

Is this 37? ✅

In the worst case, how many IDs do we have to examine to fully execute the query with Binary Search?

19 of 25

Searching

  • Important consideration in Databases - Where’s the data?
  • Unlikely that all the data will be in main memory (RAM)
    • But if not in RAM, where is it? 😳
    • Oh NO! What does this do to the our conclusions about the efficiency of Linear Search vs Binary Search?
  • Binary Search only works if the attribute we are searching is already sorted…

19

20 of 25

Indexes

  • Search Key is the attribute or attributes that are the target of a search
    • In the prior examples, search key was id.
  • Indexes are database structures that improve efficiency of searching a search key.
  • Data in tables are (basically) stored in order by the PK
    • … so it can’t also be stored in some other order without fully duplicating the data. (Don’t even think about it! NO!)

20

21 of 25

Indexes

  • We can create indexes on other non-PK attributes
    • Does not affect the physical ording of the data on disk
    • The index is external to the table
  • Lots of different data structures could be used.
    • B+ Tree - Default used in the InnoDB Storage engine of MySQL
      • extension of the BTree to account for disk-based storage of data

21

22 of 25

B+ Tree Concept

22

https://en.m.wikipedia.org/wiki/File:B%2B-tree-remove-61.png

23 of 25

Unique Indexes and Indexes

23

CREATE TABLE IF NOT EXISTS users

(

id INTEGER AUTO_INCREMENT NOT NULL,

user_name VARCHAR(30) NOT NULL,

first_name VARCHAR(50) NOT NULL,

last_name VARCHAR(50) NOT NULL,

email VARCHAR(50) NOT NULL,

password_hash VARCHAR(32) NOT NULL,

date_registered DATETIME NOT NULL,

last_login DATETIME,

profile TEXT,

PRIMARY KEY (id),

UNIQUE INDEX uq_idx_user_name (user_name),

UNIQUE INDEX uq_idx_email (email),� INDEX idx_name (last_name)

​

);

24 of 25

To Index or not to Index?

  • Why not just index every column?

24

25 of 25

??

25