30分チャレンジ!!SQLiteでSQL100本ノック!!

etc

35歳でエンジニアを志し、現在38歳の社内SEとして勤務しているからあげです。

やっとエンジニア系の記事になりそうですねと(^^;)

やっぱり電車に乗っていると、倒れそうになる😭

少し時間をおくと普通になれるのですが、共感してくれる人はいないかもしれないけど共感してほしい!

地下鉄からJRに乗り換えるのですが、その間に一度休憩をすることで復活できるのです。

特に寝不足であると発作？が起きやすい気がします。

汗が止まらないのと呼吸ができなくなるwww

皆さんちゃんと寝ましょう。。。

あと1個だけ教えてほしい!!!

↓↓↓満員電車の一コマ(生成AIで作成した画像です。実際に撮影なんでできませんよ。)↓↓↓

世の男性女性に言いたい。

この生成AIの画像のように、満員電車でなぜ内側を向いているのですか？と。

頼むから外側を向いてください!!

どう対応するのが正解なのかと。

背を向ければ良いですね。後ろにいる人と向かい合わせになりますがそれは男であることを信じましょう。

女性って内側向いている人多くありませんか？？多分身を守っているのでしょう。

さっそくSQLiteでSQL100本ノック

前準備

SQLiteをインストール

  • PowerShellでSQLiteを起動
    • powershell上でsqlite3.exeがあるフォルダへ移動(例→C:\Tools\sqlite3.exeここにexeを置く)
    • .\sqlite3.exeを実行して、プロンプトがsqliteになっていることを確認
    • パスを通す。
    • 一回プロンプトから出る。
  • exeと同じパスに次のパスからHistoryファイルをコピーしてもってくる。C:\Users\<ユーザー名>\AppData\Local\Google\Chrome\User Data\Default\History←このファイル。コピーする際は、Chromeを閉じてコピーしましょう。
    • パスの例。
      • C:\Tools\sqlite3.exe
      • C:\Tools\History
      • Historyファイルの末尾に「_copy.db」を追記してあげる。History_copy.db

早速SQL

あとはどれだけSQL叩くかだと思います!

次以降は例を示しますね!!(改行が嫌いなので改行なしで表示しています!!)

PowerShellで次を実行してDBを開く。

.\sqlite3.exe History_copy.db

テーブルの確認>

sqlite> .tables
cluster_keywords          downloads                 segment_usage
cluster_visit_duplicates  downloads_slices          segments
clusters                  downloads_url_chains      urls
clusters_and_visits       history_sync_metadata     visit_source
content_annotations       keyword_search_terms      visited_links
context_annotations       meta                      visits

表形式で見やすくする呪文↓↓

.mode column
.headers on

SELECTから↓

urlsテーブルの全てを参照

SELECT * FROM urls;

urlsテーブルのカラム指定↓

SELECT url, title FROM urls;

最初の10件のみ参照↓

SELECT url FROM urls LIMIT 10;

urlsテーブル中、urlカラムにgoogleが含まれるもののみurlカラムの値を表示する↓

SELECT url FROM urls WHERE url LIKE '%google%';

完全一致↓

SELECT * FROM urls WHERE id = 1;

urlsテーブルのうち、10回より多く訪問しているレコードを抽出して表示する↓

SELECT * FROM urls WHERE visit_count > 10;

アクセスが多い順に並び変えて表示↓

SELECT url FROM urls ORDER BY visit_count DESC;

昇順↓

SELECT url FROM urls ORDER BY visit_count ASC;

よく見るサイトTOP10↓

SELECT url, visit_count FROM urls ORDER BY visit_count DESC LIMIT 10;

URLの総数をカウント↓

SELECT url, visit_count FROM urls ORDER BY visit_count DESC LIMIT 10;

重複排除↓

SELECT DISTINCT url FROM urls;

LIKE検索、login含むURL↓

SELECT url FROM urls WHERE url LIKE '%login%';

複合条件↓

SELECT url FROM urls WHERE url LIKE '%google%' AND visit_count > 5;
SELECT url FROM urls WHERE url LIKE '%google%' OR url LIKE '%yahoo%';

※レコード追加↓

INSERT INTO urls (url, title, visit_count, last_visit_time) VALUES ('https://test.com', 'test', 1, 0);

insertしたレコードを編集(titleを編集)↓

※必ずwhere句で限定して実行しましょう。

UPDATE urls SET title = 'updated test' WHERE url = 'https://test.com';

削除をする前に、対象レコードのIDを検索↓

SELECT id, url FROM urls WHERE url = 'https://test.com';

対象のレコードを削除↓

※対象のレコードのみ削除するなどの場合、必ずwhere句を使いましょう。

DELETE FROM urls WHERE id = 確認したIDを入力;
DELETE FROM urls WHERE visit_count = 0;

INNER JOIN(両方のテーブルに存在するもののみ表示)↓

SELECT urls.url, visits.visit_time FROM urls INNER JOIN visits ON urls.id = visits.url LIMIT 10;

少し解説↑

  • FROM urls(これで基準テーブルを指定)
  • urls.id = visits.url(urlsテーブルの主キーidと、visitsテーブルの外部キーurlを関連づける。)

SELECT urls.url, visits.visit_time FROM urls LEFT JOIN visits ON urls.id = visits.url LIMIT 10;

少し説明↑

  • urlsテーブルはすべて表示する。(INNER JOINは存在するものだけ。)
  • visits（アクセス履歴）を結合
  • urls.id = visits.urlを紐づけ。

（RIGHT JOINはsqliteに存在しない。)

ランキングを任意に表示↓

SELECT urls.url, COUNT(*) as cnt 
FROM visits 
INNER JOIN urls ON urls.id = visits.url 
GROUP BY urls.url 
ORDER BY cnt DESC 
LIMIT 10;

少し説明↑

  • visitsテーブルを基準にして(from)
  • urlを集計して(group by)
  • 降順で並べて(order by)
  • countをcntとして集計した値もurlと一緒に表示して

あとはお好みで複雑なコマンドを練習してみましょう👍

どうしてもSQL構文を覚えたい場合は、よく使う構文をひたすら打つと覚えられます!!

開発言語も打ちまくると覚えられますが、完璧に覚えている人はあまりいないのでは？と思います。

よく使うのであれば覚えますし、都度ググって調べたりAIに聞いたりすると覚えなくて良いし、、、

お好みで👍

今日も今日とて寂しすぎてしっぽが伸びまくっているからあげでした😭

寝よ。。。

コメント

error: 選択できません。
タイトルとURLをコピーしました