title: Sql Basics 1
tags: sql basics

# Example 1: Clip
The idea here is to have a LAN wide clipboard using a Mariadb server to store and retrieve clips.

## Creating
```sql
create database clip;
grant all on clip.* to me@'%' identified by 'boingboing'; 
create table clip (
    id integer auto_increment primary key, 
    name varchar(256) unique, 
    value text, 
    index clipindex (name), 
    time timestamp default current_timestamp on update current_timestamp,
    fulltext index clipcontent (value));
create table history (
    id integer auto_increment primary key, 
    name varchar(256), 
    value text, 
    time timestamp default current_timestamp on update current_timestamp,
    index historyindex (name));
```

## Store
Here we insert or update (upsert)
```sql
insert into clip (name,value) 
    values ("boing","random fluffball") 
    on duplicate key update name=VALUES(name), value=VALUES(value);
insert into history (name,value) 
    values ("boing","random fluffball") 
    on duplicate key update name=VALUES(name), value=VALUES(value);
```
With `WHERE`:
```sql
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
```

## Read
```sql
select name, value, time from clip where name = "boing";
```
This will return 1 or 0.
```sql
select name, value, time from history where name = "boing";
```
This will return all values stored under that name.
We can have a background process to prune old values if desired.

## Sorting and Pagination
```sql
SELECT id, name, value, time FROM clip WHERE name = "boing"
    ORDER BY id DESC
```

# Examples In Python
* Clips_Examples_01