SQL の基本的な操作について

SQLの基本的な操作について

--データベース作成--

sqlite3 データベース名

sqlite3 myfriend.sqlite3

②--CSV インポート方法--

.separator ,
.import sample22.csv page1

③--接続していたデータベースから切断--

.exit

④テーブル一覧表示

.tables  

⑤スキーマの表示

.tables 

customer_id	store_id	firtst_name	last_name	email	adress_id
54	1	TERESA	ROGERS	TERESA.ROGERS@sakilacustomer.org	58
72	2	THERESA	WATSON	THERESA.WATSON@sakilacustomer.org	76
75	2	TAMMY	SANDERS	TAMMY.SANDERS@sakilacustomer.org	79
92	2	TINA	SIMMONS	TINA.SIMMONS@sakilacustomer.org	96
108	1	TRACY	COLE	TRACY.COLE@sakilacustomer.org	112
110	2	TIFFANY	JORDAN	TIFFANY.JORDAN@sakilacustomer.org	114
122	1	THELMA	MURRAY	THELMA.MURRAY@sakilacustomer.org	126
203	1	TARA	RYAN	TARA.RYAN@sakilacustomer.org	207
206	1	TERRI	VASQUEZ	TERRI.VASQUEZ@sakilacustomer.org	210

フィルタ条件について 例:Tを含むFirest_nameのフィルタ

select * from customer where first_name    like 'T%';

重複のみを抽出する

select * from テーブル名
where 列名 in
(select 列名 from テーブル名group by 列名
having count(*) > 1)
order by 列名 desc;

一時的にテーブルを作成する。

WITH CTE1 AS (
    SELECT 列, 列 FROM テーブル
  ), CTE2 AS (
    SELECT ID, title FROM テーブル名
  )
SELECT * FROM CTE1
UNION ALL
SELECT * FROM CTE2;

WITH cte1 AS (SELECT * FROM テーブル名)
   , cte2 AS (SELECT * FROM テーブル名)
SELECt * FROM cte2;

重複なしで結合

select
page3.ID,
page3.title
from
day
inner join
page3
on page3.ID = day.ID;

条件式で値を追加する。

select
ID,count(*),
case 
 when count(*) <5 
 then "5以下"
 when count(*) >6 
 then "6以上"
 else "その他"
end as ranking
from
page1
group by ID
;

ユニークＩＤのカウント

select
ID, count(*)
from
page1
group by ID
having count(*)>5
order by count(*) desc
;

dbを接続する

conn = sqlite3.connect('myfriend')
cursor = conn.cursor()

pythonでsqlite3のテーブル一覧を表示させる。

pd.io.sql.read_sql_query('select * from {table}'.format(table='page3'), conn)

グループ化したものをカウントする

SELECT visit,month, count(visit) FROM (SELECT month,ID,COUNT(month) as visit FROM fb_log GROUP by ID) as a GROUP BY visit,month;

文字列分割でカラム追加

select ID,ページ タイトル,ページ,substr(時間帯, 1, 4) as 年,substr(時間帯, 5, 2) as 月,substr(時間帯, 7, 2) as 日,substr(時間帯, 9, 2) as 時 from tokushu5_1;

含むページで勝てカテゴリ列追加でフラグ立て

select  *,
case 
when page like "%aa%" then 'aa' 
when page like "bb%" then 'bb' 
when page like "%cc%" then 'cc' 
when page like "%dd%" then 'dd' 
else 'Fail' end as category from "5_tokushu"

条件設定からフラグ立てるのも可能

select 閲覧開始数,
       case when 閲覧開始数 < 3 then "×"
            when 閲覧開始数 > 4 then "〇"
else 'Fail' end as Flag from page1;

■IN　を使った記述 閲覧開始数が1or2の時のみ抽出

select title,閲覧開始数
from page1
where 閲覧開始数 in (1,2);

count(*)を追記すると総数をカウントできる。

■HAVING を使った記述
HAVINGはレコードの集合に対して条件指定を行うことができる。
閲覧開始数を1以下総数を抽出

select 閲覧開始数,count(*)
from page1
group by 閲覧開始数
having 閲覧開始数 <1;

日付ごとの合計値、最大値、カウント

select 日付,count(*),max(閲覧開始数),sum(閲覧開始数)
from page1
group by 日付
;