CRUD Quick Start

use postgresql by super user prompt

  • connecting to database server by psql -U postgres
  • starting sql with commandline
  • creating a user ‘sunce’ by create user sunce with password 'sunce';
  • creating a database ‘people’ with owner ‘sunce’ by create database people with owner 'sunce';
  • quit a session by \q

now you got a database and a user.

  • connecting to database ‘people’ in user ‘sunce’ by psql people sunce
  • creating a table ‘users’ by sql
1
2
3
4

create table users ( 
	name varchar(128),
	email varchar(128) 
);
  • list all table by \dt
  • show the schema of the table ‘users’ by \d+ users
  • run sql from a file in the current directory by \i lession.sql
  • quit to back into terminal by \q

notie: connecting a database on remote server by psql -h host -p port -U user database

some basic sqls:

 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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

-- SQL: Create a table
CREATE TABLE users( name VARCHAR(128), email VARCHAR(128) );

-- SQL: Insert a row into a table
INSERT INTO users (name, email) VALUES ('Chuck', 'csev@umich.edu');
INSERT INTO users (name, email) VALUES ('Colleen', 'cvl@umich.edu');
INSERT INTO users (name, email) VALUES ('Ted', 'ted@umich.edu');
INSERT INTO users (name, email) VALUES ('Sally', 'a1@umich.edu');
INSERT INTO users (name, email) VALUES ('Ted', 'ted@umich.edu');
INSERT INTO users (name, email) VALUES ('Kristen', 'kf@umich.edu');

-- SQL: Delete a row in a table based selection criteria
DELETE FROM users WHERE email='ted@umich.edu';

-- SQL: Update a field for rows in a table with Where clause
UPDATE users SET name="Charles" WHERE email='csev@umich.edu';

-- SQL: Select for retrieve all the records
SELECT * FROM users;

-- SQL: Select for retrieve a subset of records with Where clause
SELECT * FROM users WHERE email='csev@umich.edu';

-- SQL: Select for retrieve all the records by sorted in descending or ascending order
SELECT * FROM users ORDER BY email;

SELECT * FROM users ORDER BY name DESC;

-- SQL: Select for retrieve a subset of the records in wildcard match with Where clause using Like operator 
SELECT * FROM users WHERE name LIKE '%e%';
SELECT * FROM users ORDER BY email DESC LIMIT 2;

-- SQL: Limit/Offset clause request some rows after skipping some rows, and it happen after the Where/Order BY clause are applied
SELECT * FROM users ORDER BY email OFFSET 1 LIMIT 2;

-- SQL: Count() is a built-in function can retrieve the count of the rows.
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE email='csev@umich.edu';

-- SQL: Drop a table in a database
DROP TABLE users;

CREATE TABLE users (
  id SERIAL, 
  name VARCHAR(128), 
  email VARCHAR(128) UNIQUE,
  PRIMARY KEY(id)
);

INSERT INTO users (name, email) VALUES ('Chuck', 'csev@umich.edu');
INSERT INTO users (name, email) VALUES ('Colleen', 'cvl@umich.edu');
INSERT INTO users (name, email) VALUES ('Ted', 'ted@umich.edu');

-- Note the SERIAL field auto-supplied
SELECT * from users;

-- Watch for failure due to UNIQUE
INSERT INTO users (name, email) VALUES ('Ted', 'ted@umich.edu');

SQL Data Type

Text field: 8-32 bits of information

  • char(n): a entire chunk, short string where the length is known. such as GUID/UUID.
  • varchar(n): a variable amount of space depending on the data length.
  • text: verying length, such as paragraphs/HTML. Binary types:
  • bytea(n): 8 bits of information. Integer numbers:
  • smallint: -+ 32768.
  • integer: 2 billion.
  • bigint: $10^{18}$. Floating Point numbers:
  • real: 32 bit, 7 digits of accuracy.
  • double precison: 64 bit, 14 digits of accuracy.
  • numeric(accuracy, decimal): specified digits of accuracy afyer the decimal point. For Money, using Numeric. Dates:
  • timestamp:64 bit, YYYY-MM-DD HH:MM:SS
  • date: YYYY-MM-DD
  • time: HH:MM:SS

Key and Index

key:

  • SERIAL: auto increment, primary key.
  • primary key(field): primary key.
  • unique: logical key, constraints.
  • foreign key: a key is pointing to a row in another table. indexs: Hashs or Trees are the common, shorten the scan
  • b-tree index: a tree data structure, keeps data stored and allows searchs. Good for prefix match/sort.
  • hash index: a hash function, maps large data sets to smaller data sets, called keys. Hashs are only good for exact match/GUID. such as MD5/SHA1/SHA256/SHA512.

Demo: Musical Track Database

to get the tables data file:

1

wget https://www.pg4e.com/tools/sql/library.csv

example:

Another One Bites The Dust,Queen,Greatest Hits,55,100,217
Asche Zu Asche,Rammstein,Herzeleid,79,100,231
Beauty School Dropout,Various,Grease,48,100,239
Black Dog,Led Zeppelin,IV,109,100,296
Bring The Boys Back Home,Pink Floyd,The Wall [Disc 2],33,100,87

the structure of the tables:

1
2
3
4
5
6
7
8
9

create table track_raw(
	id serial,
	title text,
	artist text,
	album text,
	count integer,
	rating integer,
	len integer
);

load data from the file ’library.csv’ into the ’track_raw’ table using \copy command:

1

\copy track_raw(title,artist,album,count,rating,len) from 'library.csv' with delimiter ',' csv;

run a query to look for the data:

1

select * from track_raw order by title limit 3;

the result expects to see:

1
2
3
4
5
6
7
8

=> select * from track_raw order by title limit 3;

		   title            |        artist         |               album                | count | rating | len
----------------------------+-----------------------+------------------------------------+-------+--------+------
 A Boy Named Sue (live)     | Johnny Cash           | The Legend Of Johnny Cash          |    37 |        |  226
 A Brief History of Packets | IEEE Computer Society | Computing Conversations            |       |        | 1004
 Aguas De Marco             | Rosa Passos           | Natural Wonders Music Sampler 1999 |   407 |        |  179
(3 rows)

Relational Database Design

Build a data model: basic rule - Don’t put the same string data in twice, use a relationship instead.

For each “piece of info”:

  • Is the column an object, or an attribute of another object?
  • Once we difine objects, to define the relationships between objects.

Database Normalization: 3NF

  • Don’t repilcate data. Instead, reference data. Point at data.
  • Use integers for keys and for references.
  • Add a special key column for each table, which you’ll make references to.

On delete choice:

  • restrict(default): don’t allow changes that break the constraint.
  • cascade: adjust child rows by removing or updating to maintain consistency. it keeps your data model clean.
  • set null: set the foreign key columns in the child rows to null. so must to allow your foreign key have a value of null.

Demo: Desigm One-Many Relationship

An example for splitting the ’track_raw’ table using 3NF:

 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

create table artist(
	id serial,
	name varchar(128) unique,
	primary key(id)
);
-- references 'artist_id' to the field 'id' of the 'artist' table. 
create table album (
	id serial,
	title varchar(128) unique,
	artist_id integer references artist(id) on delete cascade,
	primary key(id)
);

create table genre (
	id serial,
	name varchar(128) unique,
	primary key(id)
);

create table track(
	id serial,
	title varchar(128),
	rating integer,
	len integer,
	count integer,
	album_id integer references album(id) on delete cascade,
	genre_id integer references genre(id) on delete cascade,
	unique(title, album_id),
	primary key(id)
);

run \d track to describe the ’track’ table, it expects to like:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

music=> \d track
                                     Table "public.track"
  Column  |          Type          | Collation | Nullable |              Default
----------+------------------------+-----------+----------+-----------------------------------
 id       | integer                |           | not null | nextval('track_id_seq'::regclass)
 title    | character varying(128) |           |          |
 rating   | integer                |           |          |
 len      | integer                |           |          |
 count    | integer                |           |          |
 album_id | integer                |           |          |
 genre_id | integer                |           |          |
Indexes:
    "track_pkey" PRIMARY KEY, btree (id)
    "track_title_album_id_key" UNIQUE CONSTRAINT, btree (title, album_id)
Foreign-key constraints:
    "track_album_id_fkey" FOREIGN KEY (album_id) REFERENCES album(id) ON DELETE CASCADE
    "track_genre_id_fkey" FOREIGN KEY (genre_id) REFERENCES genre(id) ON DELETE CASCADE

it tells us what kind of an index, unique constraint using b-tree, and primary key using b-tree. now, let’s to insert some data into these tables:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

INSERT INTO artist (name) VALUES ('Led Zeppelin');
INSERT INTO artist (name) VALUES ('AC/DC');

INSERT INTO album (title, artist_id) VALUES ('Who Made Who', 2);
INSERT INTO album (title, artist_id) VALUES ('IV', 1);

INSERT INTO genre (name) VALUES ('Rock');
INSERT INTO genre (name) VALUES ('Metal');

INSERT INTO track (title, rating, len, count, album_id, genre_id) 
    VALUES ('Black Dog', 5, 297, 0, 2, 1) ;
INSERT INTO track (title, rating, len, count, album_id, genre_id) 
    VALUES ('Stairway', 5, 482, 0, 2, 1) ;
INSERT INTO track (title, rating, len, count, album_id, genre_id) 
    VALUES ('About to Rock', 5, 313, 0, 1, 2) ;
INSERT INTO track (title, rating, len, count, album_id, genre_id) 
    VALUES ('Who Made Who', 5, 207, 0, 1, 2) ;

Join Across Tables

Join…on… Using join (also called ‘inner join’) make the connection between the tables with an ON clause.

1
2
3
4
5
6

select album.id, album.title, artist_id, artist.name
from album
join artist on album.artist_id = artist.id;
-- same as 
select album.id, album.title, artist_id, artist.name
from album, artist where album.artist_id = artist.id;

the records expects to like:

1
2
3
4

 id |    title     | artist_id |     name
----+--------------+-----------+--------------
  1 | Who Made Who |         2 | AC/DC
  2 | IV           |         1 | Led Zeppelin

Left (outer) join…on

1
2
3

select album.id, album.title, artist_id, artist.name
from album
left join artist on album.artist_id = artist.id;

Cross join… we do can make all connection:

1
2
3

select track.id, track.title, track.genre_id, genre.id, genre.name
from track
cross join genre;

run it above, then we retrieve all connecttions:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

 id |     title     | genre_id | id | name
----+---------------+----------+----+-------
  1 | Black Dog     |        1 |  1 | Rock
  2 | Stairway      |        1 |  1 | Rock
  3 | About to Rock |        2 |  1 | Rock
  4 | Who Made Who  |        2 |  1 | Rock
  1 | Black Dog     |        1 |  2 | Metal
  2 | Stairway      |        1 |  2 | Metal
  3 | About to Rock |        2 |  2 | Metal
  4 | Who Made Who  |        2 |  2 | Metal
(8 rows)

Demo: Design Many-Many Relationship

start with a fresh database: student-course.

 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
35
36
37
38
39

create table student(
	id serial,
	name varchar(128),
	email varchar(128),
	primary key(id),
	unique(email)
);

create table course (
	id serial,
	title varchar(128),
	primary key(id),
	unique(title)
);

create table member_of(
	student_id integer references student(id) on delete cascade,
	course_id integer references course(id) on delete cascade,
	role integer, -- 0: student, 1: teacher
	primary key(student_id, course_id)
);

INSERT INTO student (name, email) VALUES ('Jane', 'jane@tsugi.org');
INSERT INTO student (name, email) VALUES ('Ed', 'ed@tsugi.org');
INSERT INTO student (name, email) VALUES ('Sue', 'sue@tsugi.org');

INSERT INTO course (title) VALUES ('Python');
INSERT INTO course (title) VALUES ('SQL');
INSERT INTO course (title) VALUES ('PHP');

INSERT INTO member_of (student_id, course_id, role) VALUES (1, 1, 1);
INSERT INTO member_of (student_id, course_id, role) VALUES (2, 1, 0);
INSERT INTO member_of (student_id, course_id, role) VALUES (3, 1, 0);

INSERT INTO member_of (student_id, course_id, role) VALUES (1, 2, 0);
INSERT INTO member_of (student_id, course_id, role) VALUES (2, 2, 1);

INSERT INTO member_of (student_id, course_id, role) VALUES (2, 3, 1);
INSERT INTO member_of (student_id, course_id, role) VALUES (3, 3, 0);

do a complexity query:

1
2
3
4
5

select student.name, member_of.role, course.title
from student
join member_of on member_of.student_id = student.id
join course on member_of.course_id = course.id
order by course.title, member_of.role desc, student.id;

the result expects to like:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

 name | role | title
------+------+--------
 Ed   |    1 | PHP
 Sue  |    0 | PHP
 Jane |    1 | Python
 Ed   |    0 | Python
 Sue  |    0 | Python
 Ed   |    1 | SQL
 Jane |    0 | SQL
(7 rows)