obsidian/Python/SQLite.md

~={yellow}Свойство:=~

• Один пишет, многие читают. https://sqlitestudio.pl/ https://sqlitebrowser.org

~={yellow}Типы:=~

• NULL - значение NULL
• INTEGER - целочисленный тип (занимает от 1 до 8 байт)
• REAL - вещественный тип (8 байт в формате IEEE)
• TEXT - строковый тип (в кодировке данных базы, обычно UTF-8)
• BLOB (двоичные данные, хранятся "как есть", например, для небольших изображений)

~={yellow}Команды=~

• INSERT - добавление записи в таблицу

• SELECT - выборка данных из таблиц (в том числе и при создании свободной выборки из нескольких таблиц)

  INSERT INTO <table_name> (<column_name1>,<column_name2>, ...) VALUES (<value1>, <value2>, ...) 
  
  INSERT INTO <table_name> VALUES (<value1>, <value2>, ...)
  
  
  SELECT col1, col2, ... FROM <table_name>
  SELECT name, old, score FROM users
  SELECT * FROM users
  
  # По критерию
  SELECT col1, col2, ... FROM <table_name> WHERE <условие>
  

~={yellow}Операторы=~

= или ==, >, <, >=, <=, !=, BETWEEN

SELECT * FROM users WHERE score BETWEEN 500 AND 1000

SELECT * FROM users WHERE score == 200

~={yellow}Условия=~

AND - условное И
OR условное ИЛИ
NOT - условное НЕ
IN - вхождение во множество значений
NOT IN - не вхождение во множество значений

SELECT * FROM users WHERE old > 20 AND score < 1000

SELECT * FROM users WHERE old IN(19, 32) AND score < 1000

SELECT * FROM users WHERE old IN(19, 32) AND score <= 1000 OR sex = 1 ORDER BY old DESC

~={yellow}Сортировка и ограничения (это все в SELECT)=~

# Если указать после запроса, то будет сортировка по убыванию
ORDER BY old DESC 

# Если указать после запроса, то будет сортировка по возрастанию
ORDER BY old ASC

# Вывести только N записей
LIMIT 2

# Вывести только 2 записи, но пропустить первые 5
LIMIT 2 OFFSET 5

# Тоже самое
LIMIT 5, 2

~={yellow}UPDATE=~

UPDATE имя_табицы SET имя столбца = новое_значение WHERE условие

UPDATE users SET score = 10000 WHERE roid = 1

UPDATE users SET score = score+500 WHERE sex = 2

UPDATE users SET score = score+500 WHERE name LIKE 'Федор'

В поле ~={yellow}LIKE=~ можно использовать:

• % - любое продолжение строки

• _ - любой символ

  # Добавит 1500 очков всем игрокам, чье имя начинается с буквы 'M'
  UPDATE users SET score = score+1500 WHERE name LIKE 'M%'
  
  # Изменение значений нескольких полей
  UPDATE users SET score = 800, old = 45 WHERE old > 40
  

~={yellow}DELETE=~

DELETE FROM users WHERE rowid IN(2, 5)

---

~={yellow}Агрегирование и группировка записей=~

# Получить число записей пользователя с user_id = 1
SELECT count(user_id) FROM games WHERE user_id = 1 

# Алиас
SELECT count(user_id) as count FROM games WHERE user_id = 1

• count() - подсчет числа записей
• sum() - подсчет суммы указанного поля по всем записям выборки
• avr() - вычисление среднего арифметического указанного поля
• min() - нахождение минимального значения для указанного поля

• max() - нахождение максимального значения для указанного поля

  # Подсчет количества уникальных игроков
  SELECT count(DISTINCT user_id) as count FROM games
  
  # Получение ID уникальных игроков
  SELECT DISTINCT user_id as count FROM games
  
  # Сумма очков для игрока с user_id = 1
  SELECT sum(score) as scores FROM games WHERE user_id = 1
  

  # Подсчитать сумму овков для игроков по user_id. Учитывать только те поля где количество очков больше 100. И вывести определенного число записей (поле LIMIT)
  SELECT user_id, sum(score) as sum
  FROM games
  WHERE score > 100
  GROUP BY user_id # выполнить группировку по user_id 
  ORDER BY sum DESC # отсортировать (по убыванию) по сумме очков
  LIMIT 2 # вывести только 2 записи
  

JOIN - создание сводных отчетов

SELECT name, sex, games.score FROM games
JOIN users ON games.user_id = users.rowid

# Вариант без JOIN но будет другой результат!
SELECT name, sex, games.score FROM users, games

SELECT user_id, sum(score) as sum
FROM games
JOIN users ON games.user_id = users.rowid
GROUP BY user_id
ORDER BY sum DESC