Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум программистов > СУБД, общие вопросы > Учебная база данных в примерах


Автор: Vit 31.1.2006, 04:32
Решил написать очень(!) краткий пример промышленной бызы данных и примеры запросов для получения самых насущных сведений. Надеюсь такой простой пример поможет тем кто делает первые шаги в мир баз данных понять как оно там работает, а так же ознакомиться с простыми примерами SQL запросов.

У нас есть предпиятие, "Рога и копыта" которое предоставляет некоторый спектр услуг клиентам:

Таблица Clients - клиенты

Id Autoincrement - порядковый номер записи, ID код клиента
First varchar(30) - имя клиента
Last varchar(30) - фамилия клиента
Phone varchar(10) - телефон клиента
Created datetime - дата внесения клиента

В заполненном виде:

ID First Last Phone Created
1 Иванов Иван 33-44-55 01.01.2005
2 Петров Петр 12-23-34 02.01.2005
3 Сидоров Сидор 24-36-48 03.02.2005



Таблица Services - предоставляемые услуги

Id Autoincrement - порядковый номер записи, ID сервиса
Name varchar(100) - название сервиса
Amount float - цена услуги

В заполненном виде:

ID Name Amount
1 Продажа рогов $10.99
2 Продажа копыт $12.95
3 Продажа хвостов $15.75



Таблица Payments - продажи услуг

Id Autoincrement - порядковый номер записи, номер транзакции
Service int - id проданного сервиса
Client int - id клиента
TransactionDate datetime - когда прошла оплата
Amount float - сумма переведенных денег


Id Service Client TransactionDate Amount
1 1 2 01.02.2005 $10.99
2 1 1 02.02.2005 $10.99
3 1 2 11.02.2005 $20.99
4 1 2 12.02.2005 $-10.00
5 2 1 01.03.2005 $10.99
6 1 2 01.03.2005 $12.95
7 3 3 01.03.2005 $15.75



Теперь запросы.


Начнём с запросам про клиентов:
1. Выдать список клиентов:

Код

Select * From Clients


2) Выдать количество клиентов

Код

Select count(*) From Clients


3) Выдать всех зарегистрированных клиентов в январе 2005 года:

Код

Select * From Clients 
Where Created between '2005-01-01' and '2005-01-31'


4) Найти всех клиентов у которых фамилии начинаются на букву "И"

Код

Select * From Clients 
Where Last like 'И%'

Автор: Vit 31.1.2006, 04:52
Теперь более сложные запросы:

1) Показать историю платежей Иванова:



Код

Select 
  Payments.id as PaymentTransactionNumber,
  Payments.TransactionDate as TransactionDate,
  Services.Name as ServiceName,
  Payments.Amount as AmountPayed
From Payments
Inner Join Clients on Client.id=Payments.Client
Inner Join Services on Service.id=Payments.Service
Where Client.Last='Иванов'
Order by Payments.TransactionDate 



2) Показать деньги заработанные на каждом сервисе:

Код

Select 
  Services.Name as ServiceName,
  Sum(Payments.Amount)
From Payments
Inner join Services on Service.id=Payments.Service
Group by Services.Name 
Order by Services.Name 


3. Показать возвраты денег по каждому сервису

Код

Select 
  Services.Name as ServiceName,
  Sum(Payments.Amount)
From Payments
Inner join Services on Service.id=Payments.Service
Where Payments.Amount<0
Group by Services.Name 
Order by Services.Name 


4. Показать сервисы которые не дают дохода

Код

Select 
  Services.Name as ServiceName,
  Sum(Payments.Amount)
From Payments
Inner join Services on Service.id=Payments.Service
Group by Services.Name 
Having Sum(Payments.Amount)<=0
Order by Services.Name 


5. Показать заработанные деньги в янаваре 2005 года

Код

Select sum(Amount)
From Payments
Where TransactionDate between '2005-01-01' and '2005-01-31'


6. Показать суммарные затраты денег каждым клиентом в январе

Код

Select 
  Client.First+' '+Client.Last,
  sum(IsNull(Payments.Amount, 0))
From Client
Left Outer Join Payments on Payments.Client=Clients.id
Where TransactionDate between '2005-01-01' and '2005-01-31'
Group By Client.First+' '+Client.Last
Order by Client.Last

Автор: achepkunov 19.12.2006, 22:21
Нормально, только в самой первой таблице first - Иванов, Петров, а last - Иван. Поправить бы.

Автор: Cooper 13.1.2007, 15:23
Цитата(Vit @  31.1.2006,  04:32 Найти цитируемый пост)
Решил написать очень(!) краткий пример промышленной бызы данных и примеры запросов для получения самых насущных сведений. Надеюсь такой простой пример поможет тем кто делает первые шаги в мир баз данных понять как оно там работает, а так же ознакомиться с простыми примерами SQL запросов.


Спасибо, Vit!!! 

Автор: It_tomahawk_vh 18.1.2007, 12:50
Vit,  не плохое начало у меня получается  smile 
Спасибо...

Автор: pretender 24.12.2007, 08:30
Цитата

6. Показать суммарные затраты денег каждым клиентом в январе

Код

Select 
  Client.First+' '+Client.Last,
  sum(IsNull(Payments.Amount, 0))
From Client
Left Outer Join Payments on Payments.Client=Clients.id
Where TransactionDate between '2005-01-01' and '2005-01-31'
Group By Client.First+' '+Client.Last
Order by Client.Last



Простите за не понимание, а как мне сделать выборку за период не по всем клиентам, а только по определенному?

Автор: LSD 24.12.2007, 12:36
Цитата(pretender @  24.12.2007,  08:30 Найти цитируемый пост)
Простите за не понимание, а как мне сделать выборку за период не по всем клиентам, а только по определенному?

Добавь еще одно условие в where.

Автор: pretender 24.12.2007, 13:21
Цитата(LSD @ 24.12.2007,  12:36)
Добавь еще одно условие в where.

Спасибки. Вчера добавлял - не работало, сегодня добавил - работает... Что за ерунда  smile 

Автор: LSD 24.12.2007, 13:26
Цитата(pretender @  24.12.2007,  13:21 Найти цитируемый пост)
 Вчера добавлял - не работало, сегодня добавил - работает... Что за ерунда

Это не ерунда, это просто выходной день, никто не работает smile

Автор: Vit 26.12.2007, 18:00
Цитата(LSD @  24.12.2007,  04:26 Найти цитируемый пост)
Это не ерунда, это просто выходной день, никто не работает 


 smile 

Автор: Xonxt 27.4.2008, 17:12
А можно такой вопрос... Как where правильно написать, если у меня есть таблица, а которой, скажем, в столбце Name есть набор фамилий.. К примеру такая:

Код
Коваленко
Иваненко
Петров
Иванюк
Петренко
Шушкевич
Калабин


И я хочу выбрать из этой таблицы все фамилии, которые заканчиваются на 'енко'. Не могу же я проссто написать where name = 'енко'...

Автор: LSD 27.4.2008, 19:00
Надо использовать like
Код

where name like '%енко'

Автор: Xonxt 27.4.2008, 19:42
Спасибо!
А если мне нужно, чтобы вывело все фамилии, содержащие "енк", то надо писать 
Код
where name like "%енк%"
?

Автор: Fortop 27.4.2008, 19:54
да

Автор: Temdegon 21.1.2009, 02:13
ИМХО неплохо бы добавить скрипты для создания и заполнения этих таблиц БД. Меня например особо интересуют Foreign Keys - как создавать constrats, что бы при удалении записи из одной таблицы, что-то происходило с данными в таблицах, которые на нее ссылаются (Ну там дефолтные значения ставились, или удалялись соответсвующие записи). Когда пользовался EMS, таких вопросов не возникало, ибо там это пара кликов мышью. А вот как только появилась необходимость создать несколько взаимосвязанных таблиц вручную - начались проблемы... Кроме того, волнуют Primary Keys. Как сделать что бы он автоматически увеличивался? Я думаю это будет интересно не только мне. Тем более, что в вашей учебной БД явно в Payments должны быть два foreign key. 

Автор: LSD 21.1.2009, 14:23
Цитата(Temdegon @  21.1.2009,  02:13 Найти цитируемый пост)
как создавать constrats, что бы при удалении записи из одной таблицы, что-то происходило с данными в таблицах, которые на нее ссылаются (Ну там дефолтные значения ставились, или удалялись соответсвующие записи)

Цитата(Temdegon @  21.1.2009,  02:13 Найти цитируемый пост)
Кроме того, волнуют Primary Keys. Как сделать что бы он автоматически увеличивался?

Зависит от конкретной СУБД. Т.е. если простой SQL ещё хоть как-то переносим между базами, то DDL практически не переносим.

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)