1995

Handling Persistence

• Flat files
• fseek() and ftell()
• mmap()

BASIC SQL

Crash Course in SQL

• display results (like a print statement)
• create a table
• insert into a table
• delete from a table
• select rows from a table
• drop a table

Displaying results

Making changes

Help on `alter table`

Discovering your schema

select from multiple tables

Joins

mysql> select name, title, street, city, state
    -> from person p, address a
    -> where p.id = a.person_id;
+-------+------------+-----------------------+-------+-------+
| name  | title      | street                | city  | state |
+-------+------------+-----------------------+-------+-------+
| chong | programmer | 123 Sesame St         | Tampa | FL    |
| chong | programmer | 1313 Mockingbird Lane | Tampa | FL    |
+-------+------------+-----------------------+-------+-------+
2 rows in set (0.00 sec)
            

Developer's Guide to SQL

# creating "tables"

class Person(object):
    def __init__(self, name, title):
        self.name = name
        self.title = title

class Address(object):
    def __init__(self, street, city, state, person):
        self.street = street
        self.city = city
        self.state = state
        self.person = person
            

# start with empty "tables"
persons = []
addresses = []

# inserting
persons.append( Person('chong', 'programmer') )
persons.append( Person('jim', 'cto') )

addresses.append( Address('123 Sesame St', 'Tampa', 'FL', persons[0]) )
addresses.append( Address('1313 Mockingbird Ln', 'Tampa', 'FL', persons[0]) )

# selecting
print [ {'name': p.name, 'title': p.title, 'street': a.street, 'city': a.city, 'state': a.state}
        for p in persons
        for a in addresses
        if p == a.person ]

 # output
[
  {
    'city': 'Tampa',
    'state': 'FL',
    'street': '123 Sesame St',
    'name': 'chong',
    'title': 'programmer'
  },
  {
    'city': 'Tampa',
    'state': 'FL',
    'street': '1313 Mockingbird Ln',
    'name': 'chong',
    'title': 'programmer'
  }
]

Person = Struct.new(:name, :title)
Address = Struct.new(:street, :city, :state, :person)

persons = []
addresses = []

persons << Person.new('chong', 'programmer')
persons << Person.new('jim', 'cto')

addresses << Address.new('123 Sesame St', 'Tampa', 'FL', persons.first)
addresses << Address.new('1313 Mockingbird Ln', 'Tampa', 'FL', persons.first)

result = []
persons.each do |p|
  addresses.each do |a|
    result << { name: p.name, title: p.title, street: a.street,
                city: a.city, state: a.state } if a.person == p
  end
end

puts result.inspect
          

Designing a schema

Problem

+----+------------+-------+
| id | date       | total |
+----+------------+-------+
|  1 | 2015-01-02 |    12 |
|  2 | 2015-01-03 |     8 |
|  3 | 2015-01-04 |     8 |
|  4 | 2015-01-06 |     3 |
|  5 | 2015-01-10 |     1 |
+----+------------+-------+
            

• There are days where there's no data
• Write a query that will include all dates given a range

Problem

What table has person_id

mysql> alter table address add index(person_id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select name, street from person join address on person.id = address.person_id;
+----+-------------+---------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | address | ALL  | person_id     | NULL | NULL    | NULL |    2 | NULL                                               |
|  1 | SIMPLE      | person  | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> insert into address(person_id) values (3); -- 7 more times...
Query OK, 1 row affected (0.00 sec)

mysql> explain select name, street from person join address on person.id = address.person_id;
+----+-------------+---------+------+---------------+-----------+---------+-------------------+------+-------+
| id | select_type | table   | type | possible_keys | key       | key_len | ref               | rows | Extra |
+----+-------------+---------+------+---------------+-----------+---------+-------------------+------+-------+
|  1 | SIMPLE      | person  | ALL  | PRIMARY       | NULL      | NULL    | NULL              |    2 | NULL  |
|  1 | SIMPLE      | address | ref  | person_id     | person_id | 9       | ckim_db.person.id |    1 | NULL  |
+----+-------------+---------+------+---------------+-----------+---------+-------------------+------+-------+
2 rows in set (0.00 sec)
            

mysql> show index from address where column_name ='person_id';
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| address |          1 | person_id |            1 | person_id   | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> select cardinality
    -> from information_schema.statistics
    -> where table_schema=database()
    -> and table_name='address'
    -> and column_name='person_id';
+-------------+
| cardinality |
+-------------+
|          10 |
+-------------+
1 row in set (0.00 sec)

CREATE TABLE category(
  category_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  parent INT DEFAULT NULL
);

SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)