Урок-практикум «создание простейшей базы данных в microsoft access 2007»

Создание базы данных в Excel: пошаговая инструкция

Пошаговое создание базы данных в Excel. Перед нами стоит задача – сформировать клиентскую БД. За несколько лет работы у компании появилось несколько десятков постоянных клиентов. Необходимо отслеживать сроки договоров, направления сотрудничества. Знать контактных лиц, данные для связи и т.п.

Как создать базу данных клиентов в Excel:

1. Вводим названия полей БД (заголовки столбцов).
2. Вводим данные в поля БД. Следим за форматом ячеек. Если числа – то числа во всем столбце. Данные вводятся так же, как и в обычной таблице. Если данные в какой-то ячейке – итог действий со значениями других ячеек, то заносим формулу.
3. Чтобы пользоваться БД, обращаемся к инструментам вкладки «Данные».
4. Присвоим БД имя. Выделяем диапазон с данными – от первой ячейки до последней. Правая кнопка мыши – имя диапазона. Даем любое имя. В примере – БД1. Проверяем, чтобы диапазон был правильным.

Основная работа – внесение информации в БД – выполнена. Чтобы этой информацией было удобно пользоваться, необходимо выделить нужное, отфильтровать, отсортировать данные.

Отчеты Microsoft Access

Отчеты – многофункциональный способ получения со всех возможных источников таблицы. Конфигурация отчетов настолько многогранна, что получить сведения возможно почти любые, зависит это лишь от количества введенной в базу.

Microsoft Access предоставляет для применения несколько типов отчетов:

• Отчет – аксесс создаст автоматически отчет, собрав всю имеющуюся информацию;
• Пустой отчет – чистая форма, для заполнения которой вам нужно будет выбрать одно из необходимых для заполнения полей;
• Мастер отчетов – удобный инструмент для создания отчетов и форматирования полученных материалов.

Преимуществом пустого отчета является то, что пользователь самостоятельно имеет возможность добавить, удалить и настроить поля, а также заполнить их так, как будет удобно.

Об авторе

Павел Угрюмов

Основатель и главный редактор компьютерного журнала PClegko. Маркетолог, предприниматель и и путешественник. Проект интернет-журнал PClegko — моё хобби. Но я планирую вырастить его в мировой проект. Каждая ваша оценка статьи и оставленный комментарий очень важны для меня. Если проект вам нравится, то буду рад если вы меня поддержите:) В ссылке находится мой основной проект. Буду рад, если загляните!

Связывание таблиц

Если для какой-то из таблиц не было определено ключевое поле, то в поле Тип отношения отображается текст «Не определено».

1. Откройте окно Схема данных, нажав кнопку на панели инструментов
2. В диалоговом окне Добавление таблицы выберите вкладку Таблицы и, нажимая кнопку Добавить, разместите в окне Схема данных все ранее созданные таблицы базы данных, список которых будет отображен в диалоговом окне. Можно добавить все таблицы сразу, выделив 1-ую таблицу и нажав Shift — последнюю таблицу.
3. Нажмите кнопку Закрыть. В результате в окне Схема данных будут представлены все таблицы базы данных КОЛЛЕДЖ со списками своих полей.
4. Установите связь между таблицами ГРУППА и СТУДЕНТ по простому ключу Номер Группы или Код группы (смотри в своей БД). Для этого в окне Схема данных установите курсор мыши на ключевое поле НГ главной таблицы ГРУППА и перетащите это поле на поле Номер Группы в подчиненной таблице СТУДЕНТ  Для удаления ошибочной связи в окне Схема данных выделите ненужную связь и нажмите Del.
5. В открывшемся окне Изменение связей в строке Тип отношения установится один-ко-многим. Отметьте доступный для этого типа отношений параметр Обеспечение целостности данных.
6. Установите флажки каскадное обновление и удаление связанных полей, тогда будет обеспечена автоматическая корректировка данных для сохранения целостности во взаимосвязанных таблицах. Нажмите Создать. Чтобы линии связи не пересекались и были удобны для восприятия, расположите таблицы в окне Схемы данных в соответствии с их относительной подчиненностью.
7. Установите связи по простому ключу для других пар таблиц:

ПРЕДМЕТ—ПРЕПОДАВАТЕЛЬ 

ПРЕДМЕТ-ЗАНЯТИЯ 

ПРЕПОДАВАТЕЛЬ—ГРУППА

ГРУППА—ЭКЗАМЕН 

Создание таблицы базы данных

Создание таблицы базы данных состоит из двух этапов.

На первом этапе определяется ее структура: состав полей, их имена, тип данных каждого поля, размер поля, ключи, индексы таблицы и другие свойства полей.

На втором этапе производится создание записей таблицы и заполнение их данными.

1 Этап: Создание структуры таблицы

Access предоставляет несколько способов создания таблицы:

• Режим таблицы — создание таблицы в Режиме таблицы;
• Конструктор — создание таблицы с помощью Конструктора таблиц;
• Мастер таблиц — создание таблицы с помощью Мастера таблиц;
• Импорт таблиц — создание таблицы путем импортирования данных из внешнего файла или другой базы данных;
• Связь с таблицами — присоединение внешнего файла или таблицы другой базы данных.

Наиболее полно использовать все возможности системы позволяет Конструктор таблиц, с помощью которого можно в диалоговом режиме определить структуру таблицы и установить параметры всех ее элементов.

Начните создание таблицы ГРУППА с определения ее структуры в режиме Конструктора таблиц.

1. В окне Новая таблица выберите строку Конструктор.

Ключевое поле

Ключевое поле

Это та запись, которая определяет запись в таблице.

Нажимаем в колонке слева на названии таблицы Читатель. Справа появилась таблица. Правой кнопкой нажимаем на названии – конструктор – в пустом поле пишем код читателя.

Сделаем это поле ключевым (на панели задач – ключевое поле) и закроем таблицу.

Это первичный ключ. Для ключевых полей используют тип – счетчик или числовой.

Определим ключевое поле для каждой таблицы аналогично предыдущей.

Книги – код книги.

Издательство – код издательства (тип данных –мастер подстановок – Издательство- выберите поле код и наименование).

Выдача – код выдачи (код читателя – таблица Читатель /код читателя и фамилия/ и код книги – таблица Книги/ код книги и название).

Любое поле можно перетащить мышкой в начало таблицы или в другое нужное место. Ключевые поля обычно ставят на первое месте

Связывание таблиц

Переходим на вкладку – Работа с базами данных – схема данных – появилось окно.

Поочередно нажимаем на название каждой таблицы и закрываем окно.

Появилась схема данных. Определим как будем связывать таблицы.

Издательства выпускают книги. Значит, в таблицу Книги надо добавить Код издательства. Для этого открываем таблицу Книги в режиме конструктора и добавляем код издательства.

Возвращаемся в схему данных и перетаскиваем Код издательства из одной таблицы в Код издательства другой. Появляется окно. Ставим Обеспечение целостности данных и в двух других пунктах ниже. Далее нажимаем создать. Появляется связь – один ко многим, т.е. одно издательство выпускает много книг.

Аналогично свяжем две другие таблицы.

Откроем таблицу Выдача через конструктор. Добавляем поле Код читателя.

Сохраняем, закрываем.

Теперь Код читателя таблицы Читатель переносим на Код читателя таблицы Выдача.

Ставим везде галочки — создать. Появилась связь (читатель берет много книг).

Теперь свяжем таблица Книги и Выдача. Для этого в таблицу Выдача добавим Код книги. И проделаем те же манипуляции.

Заполнение таблиц

Берем таблицу Читатель. Код читателя ставим на первое место. Нумерация будет автоматическая в этом поле. Вводим остальные данные (не менее 10) и сохраняем правой кнопкой.

Заполняем остальные таблицы по аналогии.

БД из трех таблиц

Сначала необходимо спроектировать структуру базы данных. Например, БД «Учеба».

В ней будет  3 таблицы с полями (полужирным начертанием выделены ключевые поля):

1. «Список курсантов» — №, фамилия, имя, день рождения, пол, улица, дом, кв, группа.

2. «Группы» — номер группы, название группы, преподаватель.  

3. «Успеваемость» — код, фамилия, имя, любые 6 предметов.

Ключевые поля можно сделать тип счетчик или числовой.

Откройте новую базу данных Microsoft Access

Сохраните ее в своей папке с именем «Учеба».

Таблицы в ней создадим в режиме Конструктор. 

Таблицы заполните произвольными 20 строками в режиме таблицы.

Создадим схему базы данных для данных таблиц во вкладке Работа с базами данных.

Чтобы создать схему данных, надо поочередно выбрать таблицы и протянуть связи левой кнопкой мыши от одной таблицы к другой. В открывшемся окне нажать Ок, поставив галочки в полях таблички.

Таблицы «Группы» и «Список учеников» связать связью «один-ко-многим», а таблицы «Список учеников» и  «Успеваемость» — связью «один-к-одному». Таблицы «Группы» и «Успеваемость» напрямую не связаны. 

Задание.

Запрос выполняется на вкладке Создание. Выполнить запрос для выделения учащихся (их группу и преподавателей), у которых одновременно экзаменационный балл по химии меньше 75 и больше 50, а по информатике балл  меньше 80 и больше 60. Предоставь результат для проверки.

Использование Transact-SQLUsing Transact-SQL

Создание схемыTo create a schema

1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database EngineDatabase Engine.In Object Explorer, connect to an instance of Компонент Database EngineDatabase Engine.

2. На стандартной панели выберите пункт Создать запрос.On the Standard bar, click New Query.

3. В следующем примере создается схема , а затем таблица .The following example creates a schema named , and then creates a table named .

4. Дополнительные операции могут быть выполнены в рамках одной инструкции.Additional options can be performed in a single statement. В следующем примере создается принадлежащая Annik схема , которая содержит таблицу .The following example creates the schema owned by Annik that contains table . Инструкция предоставляет разрешение для Mandar и запрещает для Prasanna.The statement grants to Mandar and denies to Prasanna.

5. Чтобы просмотреть схемы в этой базе данных, выполните следующую инструкцию.Execute the following statement, to view the schemas in this database:

Изголовья и шкафы

Перед началомBefore You Begin

ОграниченияLimitations and Restrictions

• Новая схема принадлежит одному из следующих участников уровня базы данных: пользователю базы данных, роли базы данных или роли приложения.The new schema is owned by one of the following database-level principals: database user, database role, or application role. Объекты, создаваемые в схеме, принадлежат владельцу схемы и имеют значение NULL для principal_id в sys.objects.Objects created within a schema are owned by the owner of the schema, and have a NULL principal_id in sys.objects. Владение объектами, содержащимися в схеме, можно передать любому участнику уровня базы данных, однако у владельца схемы всегда остается разрешение CONTROL на объекты в схеме.Ownership of schema-contained objects can be transferred to any database-level principal, but the schema owner always retains CONTROL permission on objects within the schema.

• Если при создании объекта базы данных указать допустимый субъект домена (пользователя или группу) в качестве владельца объекта, то этот субъект добавляется в базу данных в качестве схемы.When creating a database object, if you specify a valid domain principal (user or group) as the object owner, the domain principal is added to the database as a schema. Новая схема принадлежит этому субъекту домена.The new schema is owned by that domain principal.

PermissionsPermissions

• Требует разрешения CREATE SCHEMA в базе данных.Requires CREATE SCHEMA permission on the database.

• Чтобы назначить другого пользователя владельцем создаваемой схемы, у участника должно быть разрешение IMPERSONATE на этого пользователя.To specify another user as the owner of the schema being created, the caller must have IMPERSONATE permission on that user. Если роль базы данных указана в качестве владельца, то вызывающий объект должен входить в роль или иметь на нее разрешение ALTER.If a database role is specified as the owner, the caller must meet one of the following criteria: membership in the role or ALTER permission on the role.

Основные сведения о типах пользователейUnderstanding the Types of Users

Среда Management StudioManagement Studio имеется шесть вариантов создания пользователя базы данных.presents 6 options when creating a database user. На схеме ниже в зеленой рамке приведены 6 вариантов и их краткое описание.The following graphic shows the 6 options in the green box, and indicates what they represent.

Выбор типа пользователяSelecting the Type of User

Имя для входа или пользователь, который не сопоставлен с именем для входаLogin or user that is not mapped to a login

Если вы не знакомы с SQL ServerSQL Server, то при выборе типа создаваемого пользователя могут возникнуть трудности.If you are new to SQL ServerSQL Server, it can be difficult to determine what type of user you want to create. Сначала спросите себя, имеется ли имя для входа у пользователя или группы, которым нужен доступ к базе данных?First ask yourself, does the person or group that needs to access the database have a login? Имена для входа в базе данных master обычно нужны людям, которые управляют SQL ServerSQL Server , а также пользователям, которым требуется доступ к нескольким или всем базам данных в экземпляре SQL ServerSQL Server.Logins in the master database are common for the people who manage the SQL ServerSQL Server and for people who need to access many or all of the database on the instance of SQL ServerSQL Server. В этом случае создается пользователь SQL с именем для входа.For this situation, you will create a SQL user with login. Пользователь базы данных — это идентификатор имени входа при подключении к базе данных.The database user is the identity of the login when it is connected to a database. Имя пользователя базы данных может совпадать с именем входа, но это не является обязательным требованием.The database user can use the same name as the login, but that is not required. Этот раздел предполагает наличие имени входа в SQL ServerSQL Server.This topic assumes that a login already exists in SQL ServerSQL Server. Дополнительные сведения о создании имени для входа см. в статье Создание логина.For information about how to create a login, see Create a Login

Если у пользователя или группы, которым нужен доступ к базе данных, нет имени для входа либо если им требуется доступ к одной или нескольким базам данных, то лучше создать пользователя Windows или пользователя SQL с паролем.If the person or group that needs to access the database does not have a login and if they only need access to one or few databases, create a Windows user or a SQL user with password. Этот тип называется также пользователем автономной базы данных и он не связан с каким-либо именем для входа в базе данных master.Also called a contained database user, it is not associated with a login in the master database. Это лучший выбор, если вы хотите иметь возможность легко перемещать базы данных между экземплярами SQL ServerSQL Server.This is an excellent choice when you want to be able to easily move your database between instances of SQL ServerSQL Server. Чтобы использовать этот вариант в SQL Server 2016 (13.x);SQL Server 2016 (13.x), администратор сначала должен включить автономные базы данных для SQL ServerSQL Server, а база данных должна поддерживать автономность.To use this option on SQL Server 2016 (13.x);SQL Server 2016 (13.x), an administrator must first enable contained databases for the SQL ServerSQL Server, and the database be enabled for containment. Дополнительные сведения см. в разделе Пользователи автономной базы данных — создание переносимой базы данных.For more information, see Contained Database Users — Making Your Database Portable.

Выберите Пользователь SQL с паролем или Пользователь SQL с именем входа , выбрав имя входа для проверки подлинности SQL Server, если проверка подключающегося пользователя невозможна с помощью Windows.Select SQL user with password or a SQL user with login based on a SQL Server authentication login, when the person connecting cannot authenticate with Windows. Эта ситуация часто возникает у пользователей за пределами вашей организации (например, у клиентов), которые подключаются к SQL ServerSQL Server.This is common when people outside of your organization (for example customers) are connecting to your SQL ServerSQL Server.

Использование среды SQL Server Management StudioUsing SQL Server Management Studio

Создание базы данныхTo create a database

1. В обозревателе объектовподключитесь к экземпляру компонента Компонент SQL Server Database EngineSQL Server Database Engine и разверните его.In Object Explorer, connect to an instance of the Компонент SQL Server Database EngineSQL Server Database Engine and then expand that instance.

2. Щелкните правой кнопкой мыши элемент Базы данных, а затем выберите пункт Создать базу данных.Right-click Databases, and then click New Database.

3. В поле Новая база данныхвведите имя базы данных.In New Database, enter a database name.

4. Чтобы создать базу данных, приняв все значения по умолчанию, нажмите кнопку ОК; иначе продолжайте выполнение следующих дополнительных шагов.To create the database by accepting all default values, click OK; otherwise, continue with the following optional steps.

5. Чтобы изменить имя владельца, нажмите ( … ) и выберите другого владельца.To change the owner name, click (…) to select another owner.

   Примечание

   Параметр Использовать полнотекстовое индексирование всегда установлен и недоступен (т. к. начиная с SQL Server 2008SQL Server 2008все пользовательские базы данных поддерживают полнотекстовый поиск).The Use full-text indexing option is always checked and dimmed because, beginning in SQL Server 2008SQL Server 2008, all user databases are full-text enabled.

6. Чтобы изменить значения первичных данных по умолчанию и файлов журнала транзакций, щелкните соответствующую ячейку в сетке Файлы базы данных и введите новое значение.To change the default values of the primary data and transaction log files, in the Database files grid, click the appropriate cell and enter the new value. Дополнительные сведения см. в статье AДобавление файлов данных или журналов в базу данных.For more information, see Add Data or Log Files to a Database.

7. Чтобы изменить параметры сортировки базы данных, выберите страницу Параметры и выберите из списка желаемые параметры сортировки.To change the collation of the database, select the Options page, and then select a collation from the list.

8. Чтобы изменить модель восстановления, выберите страницу Параметры и модель восстановления из списка.To change the recovery model, select the Options page and select a recovery model from the list.

9. Чтобы изменить параметры базы данных, выберите страницу Параметры и измените параметры базы данных.To change database options, select the Options page, and then modify the database options. Описание каждого параметра см. в статье Параметры ALTER DATABASE SET (Transact-SQL).For a description of each option, see ALTER DATABASE SET Options (Transact-SQL).

10. Чтобы добавить новую файловую группу, перейдите на страницу Группы файлов .To add a new filegroup, click the Filegroups page. Нажмите Добавить и введите значения для файловой группы.Click Add and then enter the values for the filegroup.

11. Чтобы добавить расширенное свойство в базу данных, выберите страницу Расширенные свойства .To add an extended property to the database, select the Extended Properties page.

    1. В столбце Имя введите имя расширенного свойства.In the Name column, enter a name for the extended property.

    2. В столбце Значение введите текст расширенного свойства.In the Value column, enter the extended property text. Например, введите одно или несколько предложений, которые описывают базу данных.For example, enter one or more statements that describe the database.

12. Чтобы создать базу данных, нажмите кнопку ОК.To create the database, click OK.

Перед началомBefore You Begin

ОграниченияLimitations and Restrictions

В экземпляре SQL ServerSQL Serverможет быть задано не более 32 767 баз данных.A maximum of 32,767 databases can be specified on an instance of SQL ServerSQL Server.

Предварительные требованияPrerequisites

Инструкция CREATE DATABASE должна выполняться в режиме автоматической фиксации (режим управления транзакциями по умолчанию) и не может применяться в явной или неявной транзакции.The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

РекомендацииRecommendations

• Резервную копию базы данных master необходимо создавать каждый раз при создании, изменении или удалении пользовательской базы данных.The master database should be backed up whenever a user database is created, modified, or dropped.

• При создании базы данных файлы данных следует делать как можно большего размера, в соответствии с максимальным предполагаемым объемом данных в базе данных.When you create a database, make the data files as large as possible based on the maximum amount of data you expect in the database.

PermissionsPermissions

Требуется разрешение CREATE DATABASE в базе данных master или разрешение CREATE ANY DATABASE или ALTER ANY DATABASE.Requires CREATE DATABASE permission in the master database, or requires CREATE ANY DATABASE, or ALTER ANY DATABASE permission.

В целях сохранения управления над использованием диска в экземпляре SQL ServerSQL Serverразрешение на создание баз данных обычно предоставляется небольшому числу учетных записей входа.To maintain control over disk use on an instance of SQL ServerSQL Server, permission to create databases is typically limited to a few login accounts.

Заполнение таблиц даннымиPopulate the tables with data

В Обозреватель сервера или Обозреватель объектов SQL Serverразверните узел образца базы данных.In Server Explorer or SQL Server Object Explorer, expand the node for the sample database.

Откройте контекстное меню для узла таблицы , выберите Обновить, а затем разверните узел таблицы .Open the shortcut menu for the Tables node, select Refresh, and then expand the Tables node.

Откройте контекстное меню таблицы Customers и выберите команду отобразить данные таблицы.Open the shortcut menu for the Customers table, and then select Show Table Data.

Добавьте необходимые данные для некоторых клиентов.Add whatever data you want for some customers.
Можно указать любые пять символов как ИД клиентов, но хотя бы один нужно выбрать и запомнить для дальнейшего использования в этой процедуре.You can specify any five characters you want as the customer IDs, but choose at least one that you can remember for use later in this procedure.

Откройте контекстное меню таблицы Orders и выберите пункт отобразить данные таблицы.Open the shortcut menu for the Orders table, and then select Show Table Data.

Добавление данных для некоторых заказов.Add data for some orders.

Важно!
Убедитесь, что все идентификаторы заказов и количества заказов — целые числа, и каждый идентификатор клиента соответствует значению, указанному в столбце CustomerID таблицы клиентов.Make sure that all order IDs and order quantities are integers and that each customer ID matches a value that you specified in the CustomerID column of the Customers table.

В строке меню выберите файл > сохранить все.On the menu bar, select File > Save All.

Варианты создания баз данных в Microsoft Access

Пустую базу данных можно создать в проводнике Windows, щелкнув правой кнопкой на пустом пространстве и выбрав «Создать… Базу данных Microsoft Access». Появится пустой файл (ему сразу же можно присвоить подходящее имя), который следует открыть двойным щелчком. Однако в современных версиях программы предусмотрены более эффективные способы выполнения этого действия.

Работа с шаблонами

Можно создать новую БД:

• из стандартного шаблона: несколько готовых шаблонов поставляются вместе с программой;
• из шаблона с сайта Office.com: там имеется обширная коллекция заготовок для баз данных различного назначения; их можно загружать по сети с помощью меню (вкладка «Создать»).

Рисунок 1. Варианты создания новой базы данных в MS Access. Автор24 — интернет-биржа студенческих работ

Шаблоны представляют собой предподготовленные незаполненные базы данных для различных нужд: ведения учета материальных ценностей, списков сотрудников, клиентов, финансов и т.п. В шаблонах имеются необходимые таблицы с уже установленными связями, формы, отчеты и т.д.

Шаблоны, как и пустые базы данных, можно редактировать: добавлять и изменять таблицы, формы и другие объекты.

Замечание 2

С сайта Office.com можно загружать не только целые шаблоны, но и так называемые «части приложения» — связанные между собой компоненты, например, таблица и построенная на ее основе форма.

Выбрав на вкладке «Создать» подходящий шаблон, следует указать имя файла. По окончании процесса можно сразу начинать вводить данные.

Создание пустой базы данных

Для создания пустой БД следует на вкладке «Файл» выбрать пункт «Создать» и вариант «Пустая база данных», затем указать имя файла. Access создаст БД с незаполненной таблицей «Таблица1». Она сразу же будет открыта в режиме таблицы. Для добавления данных нужно просто вводить их, отложив оптимизацию структуры колонок. Процесс ввода данных похож на работу с листом Excel.

Замечание 3

Файл Blank.accdb в папке :\Program Files\Microsoft Office\Templates\1049\Access. используется как шаблон для всех новых локальных пустых баз данных. Все новые базы данных наследуют содержимое этого файла. Это отличный способ распространения содержимого по умолчанию.