Setting up a local SQL Server database
In this guide, we'll talk about how to install and configure a SQL Server instance and the sqlcmd command line client. We will go over how to install and set up these components on your computer for local access.
This guide will cover the following platforms:
Navigate to the sections that match the platforms you will be working with.
Setting up SQL Server on Windows
Microsoft provides native Windows installers for SQL Server on their site and offers various versions of SQL Server suitable for different purposes. For the purposes of this guide, we will download and install the free Developer edition. You can easily upgrade to a paid version from the Developer edition if you want to use it for production.
To begin, visit Microsoft's page for SQL Server. Find the section related to the Developer edition and click Download now:
Once the download completes, double click on the file to run the installer (you may have to confirm that you wish to allow the program to make changes to your computer).
On the initial screen of the installer, you will be asked to choose what type of installation you want to perform:
Choose Basic to continue on with a conventional installation using the most common options.
Next, you'll be asked to agree to the Developer Edition licensing terms:
When you have read the license and agree to the terms, click Accept to continue.
Next, confirm or change the installation location:
When you are ready, click Install to begin the installation process.
The installer will begin to download and install components to set up SQL Server on your computer:
When the installation is finished, a screen will appear noting the current installation properties:
To connect to the new SQL Server instance right away, click Connect Now at the bottom.
A new window will Cmd window will appear and automatically log you into the SQL Server instance using the sqlcmd client:
As shown in a comment at the top of the window, you can connect to SQL Server manually at any time with the sqlcmd client by typing:
To exit the current SQL session, type:
If you are using Prisma Client with SQL Server, you can use the SQL Server connector to connect, map your models, and manage your data.
You can also check out our guides to see how to use Prisma with Microsoft SQL Server on a new project or in an existing project.
Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.
Setting up SQL Server on macOS
While Microsoft does not provide a native installer for macOS, they do support running SQL Server on macOS through Docker. The main SQL Server Docker container is built using a Linux container, allowing any host capable of running Docker containers to run the database server.
You'll need at least 2 GB of memory (probably at least a little more) to successfully run the image, however Docker itself requires at least 4 GB of memory.
To begin, make sure you have the Docker on your system. Docker Desktop for Mac includes Docker Engine and other related applications. If you don't already have Docker installed, follow the instructions included in the above link.
Once you have Docker up and running, you can pull the SQL Server Docker image from Microsoft Container Registry by typing:
This will download all of the required image layers to your local system, allowing a faster startup.
When you're ready to start the container, type the following command.
Remember to replace <password> with the value of your intended password and choose a value that conforms to the image's password policy. At the time of this writing, the policy is defined as: "The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols.":
The SQL Server container will be started up in the background. The string of characters displayed is the new container's ID.
You can verify that the container is up and running by typing:
You should see the mssql container among the list. If the container is not running or you have trouble, you can try viewing its logs to see if there are any helpful messages:
The SQL Server container not only has the database server installed, it also has some of the common tooling available, including the sqlcmd command line client. To use this client to connect to the database instance, you can use docker exec to access the command and authenticate against the database:
You will be authenticated to the SQL Server inside the container and dropped into a SQL shell. You can verify that everything is up and running by typing:
To exit the SQL session and get back to your normal shell, type:
To shut down the SQL Server container when you're done, you can stop it by typing:
To remove the container instance (including all data inside!), type:
If you are using Prisma Client with SQL Server, you can use the SQL Server connector to connect, map your models, and manage your data.
You can also check out our guides to see how to use Prisma with Microsoft SQL Server on a new project or in an existing project.
Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.
Setting up SQL Server on Linux
Installation methods differ depending on the Linux distribution you are using. Follow the section below that matches your Linux distribution. There are also instructions using Docker if you prefer that configuration or want to use a distribution not listed.
The easiest way to install SQL Server on Ubuntu 20.04 is to install from the dedicated repositories provided by Microsoft. Your machine must have at least 2 GB of memory to successfully install and run the necessary software.
To begin, add a new repository definition to your system by typing:
You also need to add a separate repository to get access to the sqlcmd binary and other tools:
Next, add the Microsoft package signing key to apt so that it trusts the packages in the new repository:
With the repository set up, you can install SQL Server and the sqlcmd command line client by typing:
Once the installation is complete, you need to configure your new database instance. To do so, run the included mssql-conf setup script to set some of the basic properties of your new system:
You will be asked a series of questions in order to configure the database server.
First, it will ask you what edition of SQL server you want to use:
If you have a paid license, you can choose the appropriate version. If you are using the server in a non-production environment, it is safe to choose the developer edition.
Next, you'll have to accept the license terms again:
Finally, you'll have to set and confirm a password for the SQL Server system administrator account (called the SA account in many places):
To use the sqlcmd client to connect to your SQL Server instance, it's easiest to add the mssql-tools binary directory to your PATH . To configure this, type:
Afterwards, re-source one of the two files above to evaluate the new PATH for your current session:
You can now connect to your database instance by typing:
You'll be prompted for the password you set up earlier. After successfully authenticating, you will be dropped into an SQL shell. From here, you can verify that everything is working by printing the server's version:
To exit the SQL shell and get back to the command line, you can type:
If you are using Prisma Client with SQL Server, you can use the SQL Server connector to connect, map your models, and manage your data.
You can also check out our guides to see how to use Prisma with Microsoft SQL Server on a new project or in an existing project.
Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.
CentOS and Red Hat
The easiest way to get SQL Server installed on CentOS or Red Hat is to use the repositories provided by Microsoft. Linux hosts must have at least 2 GB of memory to install and run SQL Server.
Before installing SQL Server, you need to install and configure its dependencies. We need both Python 2 and OpenSSL 10 to continue:
After Python 2 is installed, configure the system to use it as the default Python instance:
From the list that follows, select the number associated with the Python 2 installation. In the example below, this will be option 2:
With the dependencies in place, you can now configure the SQL Server YUM repository:
Afterwards, you need to configure an additional repository to get access to the sqlcmd and other tools:
Once the repositories are configured, install SQL Server by typing:
Once the installation is complete, you need to configure your new database instance. To do so, run the included mssql-conf setup script to set some of the basic properties of your new system:
You will be asked a series of questions in order to configure the database server.
First, it will ask you what edition of SQL server you want to use:
If you have a paid license, you can choose the appropriate version. If you are using the server in a non-production environment, it is safe to choose the developer edition.
Next, you'll have to accept the license terms again:
Finally, you'll have to set and confirm a password for the SQL Server system administrator account (called the SA account in many places):
To use the sqlcmd client to connect to your SQL Server instance, it's easiest to add the mssql-tools binary directory to your PATH . To configure this, type:
Afterwards, re-source one of the two files above to evaluate the new PATH for your current session:
You can now connect to your database instance by typing:
You'll be prompted for the password you set up earlier. After successfully authenticating, you will be dropped into an SQL shell. From here, you can verify that everything is working by printing the server's version:
To exit the SQL shell and get back to the command line, you can type:
If you are using Prisma Client with SQL Server, you can use the SQL Server connector to connect, map your models, and manage your data.
You can also check out our guides to see how to use Prisma with Microsoft SQL Server on a new project or in an existing project.
Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.
If you are using a Linux distribution that Microsoft does not provide packages for or if you simply prefer, another option is to run SQL Server with Docker. You'll need at least 2 GB of memory (probably at least a little more) to successfully run the image.
To begin, make sure you have the Docker Engine on your system. You can find detailed instructions for various platforms in the Docker Engine documentation.
Once you have Docker up and running, you can pull the SQL Server Docker image from Microsoft Container Registry by typing:
This will download all of the required image layers to your local system, allowing a faster startup.
When you're ready to start the container, type the following command.
Remember to replace <password> with the value of your intended password and choose a value that conforms to the image's password policy. At the time of this writing, the policy is defined as: "The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols.":
The SQL Server container will be started up in the background. The string of characters displayed is the new container's ID.
You can verify that the container is up and running by typing:
You should see the mssql container among the list. If the container is not running or you have trouble, you can try viewing its logs to see if there are any helpful messages:
The SQL Server container not only has the database server installed, it also has some of the common tooling available, including the sqlcmd command line client. To use this client to connect to the database instance, you can use docker exec to access the command and authenticate against the database:
You will be authenticated to the SQL Server inside the container and dropped into a SQL shell. You can verify that everything is up and running by typing:
To exit the SQL session and get back to your normal shell, type:
To shut down the SQL Server container when you're done, you can stop it by typing:
To remove the container instance (including all data inside!), type:
If you are using Prisma Client with SQL Server, you can use the SQL Server connector to connect, map your models, and manage your data.
You can also check out our guides to see how to use Prisma with Microsoft SQL Server on a new project or in an existing project.
Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.
How do you check your SQL Server version?
Several versions of Microsoft's SQL Server are supported, and there are several methods for determining which version you are running.
Any of the listed methods from Microsoft will return the version and edition of the SQL Server Database Engine you are running.
How can you download SQL Server for free?
There are two free, specialized editions of SQL Server available for download. The Developer and Express version are available for download at Microsoft's page for SQL Server.
The Developer version is a full-featured free edition, licensed for use as a development and test database in a non-production environment.
The Express version is ideal for development and production for desktop, web, and small server applications.
What is the SQL Server Developer edition?
SQL Server 2019 Developer is a full-featured edition, licensed for use as a development and test database in a non-production environment.
Is Azure SQL the same as SQL Server?
Azure SQL is based on SQL Server, so they share many similarities in functionality and compatibility. However, this does not mean they are the same.
Azure SQL is a family of managed products that use the SQL Server database engine in the Azure cloud.
What is the SQL Server Configuration Manager?
SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers.
The configuration manager is installed with your SQL Server installation and is available from the Start menu or can be added to any other Microsoft Management Console display.
Name already in use
sql-docs / docs / ssms / register-servers / create-a-new-registered-server-sql-server-management-studio.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
Create a New Registered Server (SQL Server Management Studio)
This topic describes how to save the connection information for servers that you access frequently, by registering the server in the Registered Servers component of SQL Server Management Studio in [!INCLUDEssnoversion]. A server can be registered before connecting, or when connecting from Object Explorer. There is a special menu option to register the server instances on the local computer.
There are two kinds of registered servers:
Local server groups
Use local server groups to easily connect to servers that you frequently manage. Both local and non-local servers are registered into local server groups. Local server groups are unique to each user. For information about how to share registered server information, see Export Registered Server Information (SQL Server Management Studio) and Import Registered Server Information (SQL Server Management Studio).
[!NOTE]
We recommend that you use Windows Authentication whenever possible.
Central Management Servers
Central Management Servers store server registrations in the Central Management Server instead of on the file system. Central Management Servers and subordinate registered servers can be registered only by using Windows Authentication. After a Central Management Server has been registered, its associated registered servers will be automatically displayed. For more information about Central Management Servers, see Administer Multiple Servers Using Central Management Servers. Versions of [!INCLUDEssNoVersion] that are earlier than [!INCLUDEsql2008-md] cannot be designated as a Central Management Server.
Using SQL Server Management Studio
To create a new registered server
If Registered Servers is not visible in SQL Server Management Studio, on the View menu, click Registered Servers.
Server type
When a server is registered from Registered Servers, the Server type box is read-only, and matches the type of server displayed in the Registered Servers pane. To register a different type of server, click Database Engine, Analysis Server, Reporting Services, or Integration Services on the Registered Servers toolbar before starting to register a new server.
Server name
Select the server instance to register in the format: <servername>[\<instancename>].
Authentication
Two authentication modes are available when connecting to an instance of [!INCLUDEssNoVersion].
Windows Authentication
Windows Authentication mode allows a user to connect through a [!INCLUDEmsCoName] Windows user account.
SQL Server Authentication
When a user connects with a specified login name and password from a nontrusted connection, [!INCLUDEssNoVersion] performs the authentication itself by checking whether a [!INCLUDEssNoVersion] login account has been set up and whether the specified password matches the one previously recorded. If [!INCLUDEssNoVersion] does not have a login account set, authentication fails, and the user receives an error message.
User name
Shows the current user name you are connecting with. This read-only option is only available if you have selected to connect using Windows Authentication. To change User names, log in to the computer as a different user.
Login
Enter the login to connect with. This option is available only if you have selected to connect using [!INCLUDEssNoVersion] Authentication.
Password
Enter the password for the login. This option can be edited only if you have selected to connect by using [!INCLUDEssNoVersion] Authentication.
Remember password
Select to have [!INCLUDEssNoVersion] encrypt and store the password you have entered. This option is displayed only if you have selected to connect using [!INCLUDEssNoVersion] Authentication.
[!NOTE]
If you have stored the password and want to stop storing it, clear this check box, and then click Save.
Registered server name
The name you want to appear in Registered Servers. This name does not have to match the Server name box.
Registered server description
Enter an optional description of the server.
Test
Click to test the connection to the server selected in Server name.
Save
Click to save the registered server settings.
The Query Editor window in SQL Server Management Studio can connect to and query multiple instances of [!INCLUDEssNoVersion] at the same time. The results that are returned by the query can be merged into a single results pane, or they can be returned in separate results panes. As an option, Query Editor can include columns that provide the name of the server that produced each row, and also the login that was used to connect to the server that provided each row. For more information about how to execute multiserver queries, see Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio).
To execute queries against all the servers in a local server group, right-click the server group, point to click Connect, and then click New Query. When queries are executed in the new Query Editor window, they will execute against all servers in the group, using the stored connection information including the user authentication context. Servers registered by using [!INCLUDEssNoVersion] Authentication but not saving the password will fail to connect.
To execute queries against all the servers that are registered with a Central Management Server, expand the Central Management Server, right-click the server group, point to click Connect, and then click New Query. When queries are executed in the new Query Editor window, they will execute against all of the servers in the server group, using the stored connection information and using the Windows Authentication context of the user.
Как создать локальный сервер в sql server management studio 2017
Как создать локальный сервер в sql server management studio 2017
Установка и запуск MS SQL на Linux Ubuntu
Опубликовано: 15.12.2019
Установка MS SQL
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | apt-key add —
add-apt-repository «$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2019.list)»
apt-get install mssql-server
systemctl status mssql-server
systemctl enable mssql-server
Установка средств управления MS SQL
sqlcmd client
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add —
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | tee /etc/apt/sources.list.d/msprod.list
apt-get install mssql-tools
sqlcmd -S localhost -U SA
> SELECT name FROM master.dbo.sysdatabases
Microsoft SQL Server Management Studio
How to connect to your local MSSQL server using SSMS?
This post was most recently updated on July 16th, 2021.
Every now and then you run into a situation, where you really need to run some SQL against your local development database. That database, at least in my case, is hosted on your local SQL Server Express.
Connecting to a local SQL Server should be a walk in a park, right? Eh, well…
While using a connection string to connect to said DB is easy, you can’t do that with the SQL Server Management Studio. I wish you could, but hey – it is what it is.
There’s a couple of ways to connect, though! Let’s start with the easy one, that doesn’t always work:
How to connect to your local database with SQL Server Management Studio?
Table of Contents
Connecting to the local instance might work by using the name of the instance. While I’m not sure what’s the reason it hasn’t worked for me (I wonder if the instance names differs based on what you’re installing the SQL Server or SQL Express with. ), maybe it works for you.
Essentially, just paste this into the connection window:
Or like shown below:
How to log in to local SQL Server database using SQL Server Management Studio.
Workaround: Use Named Pipes instead
If it doesn’t – well, there’s a workaround, although it’s a bit laborious. It in fact requires you to know the instance name pipe – a weird, nonsensical URI-looking piece of textual vomit you simply can’t guess. And then, most of the time, you can just use your local user account to log in.
The workaround has quite a few steps, and finally comes down to this beautiful screen below:
How to log in to local SQL Server using named pipes.
But how do you figure out the right Server name? Check out the steps below!
Time needed: 10 minutes.
-
Try using (localdb)\MSSqlLocalDb first
The location is something like this under your SQL Server’s installation path -> Tools -> Binn.
Easy enough – run this in your console:
SqlLocalDB.exe
Next, let’s run the command with parameter “info”.
> SqlLocalDB info
MSSQLLocalDB
ProjectsV13
This’ll look somewhat like below:
SqlLocalDB info [instancename]
You’ll want to get this server up and running. That’s easy – just run the command below:
SqlLocalDB start MSSQLLocalDB
The output might be something like below:
Or in text form:
SqlLocalDB info MSSQLLocalDB
Name: MSSQLLocalDB
Version: 13.1.4001.0
Shared name:
Owner: [username]
Auto-create: Yes
State: Running
Last start time: 2020-08-12 1:22:49 PM
Instance pipe name: np:.\pipe\LOCALDB#A4E758FA\tsql\query
And boom! You should be good.
References and appendices
Updated 13.2.2020: Added mention of the easier method (hopefully that works for y’all!), thanks Mika Berglund.
For further reference, check out these links:
Appendix 1: the output of SqlLocalDB.exe
I’m including the whole default output of SqlLocalDB.exe here, as it won’t fit into the step-by-step instructions above.
Antti Koskela is a proud digital native nomadic millennial full stack developer (is that enough funny buzzwords? That’s definitely enough funny buzzwords!), who works as a Cloud Solutions Architect for Etteplan Oyj, an engineering company that employs something like 700 devs building and fixing anything even half-digital.
He’s been a developer from 2004 (starting with PHP and Java), and he’s been working on .NET projects, Azure, Office 365, SharePoint and a lot of other stuff. He’s also Microsoft MVP for Office Development.
This is his personal professional (e.g. professional, but definitely personal) blog.
-
— December 1, 2021 — November 23, 2021 — November 16, 2021
Posts Related to «How to connect to your local MSSQL server using SSMS?»:
report this ad
Search this site!
About the site and the author
Welcome! You just stumbled upon the home page of an all-around artisan code crafter and Microsoft MVP, Antti «koskila» Koskela.
Don’t hesitate to leave comments. I read them all and try to reply as well!
More information about me in the About -section!
Solutions are worthless unless shared!
Check out the tech & programming tips, often about ASP.NET MVC, Entity Framework, Microsoft SharePoint Server & Online, Azure, Active Directory, Office 365 or other parts of the ever-growing and more and more intimidating stack that Microsoft offers us.
I’ve been developing both classic server stuff, but also (and actually especially) more cloud-oriented stuff in the past 15 years.
There’s an occasional post about software issues other than on Microsoft’s stack, and a rare post about hardware, too! And sometimes I might post about my sessions at different community events, or experiences as an expat living in a foreign country (in 2017, that country was the USA, in 2018 & 2019 Canada).
And since I’m hosting this site on WordPress, and boy does WordPress experience a lot of issues, I might also post something about solving those cases. Like PHP compatibility issues.
Want the latest tips directly to your inbox?
Like these posts and tips? You can get them automatically right as I post them! Enter your email here or check out the RSS feed here: https://www.koskila.net/feed/
And no worries — it’s just notifications of new posts coming in, nothing else 🙂
Пример создания локальной базы данных Microsoft SQL Server в MS Visual Studio
В данной теме показано решение задачи создания базы данных типа SQL Server с помощью MS Visual Studio . Рассматриваются следующие вопросы:
- работа с окном Server Explorer в MS Visual Studio ;
- создание локальной базы данных типа SQL Server Database ;
- создание таблиц в базе данных;
- редактирование структур таблиц;
- связывание таблиц базы данных между собой;
- внесение данных в таблицы средствами MS Visual Studio .
Содержание
- Условие задачи
- Выполнение
- 1. Загрузить MS Visual Studio .
- 2. Активировать окно Server Explorer .
- 3. Создание базы данных “ Education ”.
- 4. Объекты базы данных Education .
- 5. Создание таблицы Student .
- 6. Создание таблицы Session .
- 7. Редактирование структуры таблиц.
- 8. Установление связей между таблицами.
- 9. Ввод данных в таблицы.
Поиск на других ресурсах:
Условие задачи
Используя средства MS Visual Studio создать базу данных типа MS SQL Server с именем Education. База данных содержит две таблицы Student и Session. Таблицы между собой связаны по некоторыму полю.
Структура первой таблицы «Student».
Структура второй таблицы “ Session ”.
Выполнение
1. Загрузить MS Visual Studio .
2. Активировать окно Server Explorer .
Для работы с базами данных корпорация Microsoft предлагает облегченный сервер баз данных Microsoft SQL Server . Существуют разные версии Microsoft SQL Server , например: Microsoft SQL Server 2005 , Microsoft SQL Server 2008 , Microsoft SQL Server 2014 и прочие версии.
Загрузить эти версии можно с сайта Microsoft www.msdn.com.
Этот сервер отлично подходит для работы с базами данных. Он бесплатен и имеет графический интерфейс для создания и администрирования баз данных с помощью SQL Server Management Tool .
Прежде всего, перед созданием базы данных, нужно активировать утилиту Server Explorer . Для этого, в MS Visual Studio нужно вызвать (рис. 1)
Рис. 1. Вызов Server Explorer
После вызова окно Server Explorer будет иметь приблизительный вид, как показано на рисунке 2.
Рис. 2. Окно Server Explorer
3. Создание базы данных “Education”.
Чтобы создать новую базу данных, базирующуюся на поставщике данных Microsoft SQL Server , нужно кликнуть на узле Data Connections, а потом выбрать “ Create New SQL Server Database … ” (рис. 3).
Рис. 3. Вызов команды создания базы данных SQL Server
В результате откроется окно « Create New SQL Server Database » (рис. 4).
В окне (в поле «Server Name») указывается имя локального сервера, установленного на вашем компьютере. В нашем случае это имя “ SQLEXPRESS ”.
В поле « New database name: » указывается имя создаваемой базы данных. В нашем случае это имя Education.
Опцию Use Windows Autentification нужно оставить без изменений и нажать кнопку OK .
Рис. 4. Создание новой базы данных SQL Server 2008 Express с помощью MS Visual Studio 2010
После выполненных действий, окно Server Explorer примет вид, как показано на рисунке 5. Как видно из рисунка 5, в список имеющихся баз данных добавлена база данных Education с именем
Рис. 5. Окно Server Explorer после добавления базы данных Education
4. Объекты базы данных Education.
Если развернуть базу данных Education (знак « + »), то можно увидеть список из следующих основных объектов:
- Database Diagrams – диаграммы базы данных. Диаграммы показывают связи между таблицами базы данных, отношения между полями разных таблиц и т.п.;
- Tables – таблицы, в которых помещаются данные базы данных;
- Views – представления. Отличие между представлениями и таблицами состоит в том, что таблицы баз данных содержат данные, а представления данных не содержат их, а содержимое выбирается из других таблиц или представлений;
- Stored procedures – хранимые процедуры. Они представляют собою группу связанных операторов на языке SQL, что обеспечивает дополнительную гибкость при работе с базой данных.
5. Создание таблицы Student.
На данный момент база данных Education абсолютно пустая и не содержит никаких объектов (таблиц, сохраненных процедур, представлений и т.д.).
Чтобы создать таблицу, нужно вызвать контекстное меню (клик правой кнопкой мышки) и выбрать команду “ Add New Table ” (рисунок 6).
Рис. 6. Команда добавления новой таблицы
Существует и другой вариант добавления таблицы базы данных с помощью команд меню Data:
Рис. 7. Альтернативный вариант добавления новой таблицы
В результате откроется окно добавления таблицы, которое содержит три столбца (рисунок 8). В первом столбце “Column Name” нужно ввести название соответствующего поля таблицы базы данных. Во втором столбце “Data Type” нужно ввести тип данных этого поля. В третьем столбце “ Allow Nulls ”указывается опция о возможности отсутствия данных в поле.
Рис. 8. Окно создания новой таблицы
С помощью редактора таблиц нужно сформировать таблицу Student как изображено на рисунке 9. Имя таблицы нужно задать при ее закрытии.
В редакторе таблиц можно задавать свойства полей в окне Column Properties. Для того, чтобы задать длину строки (nvchar) в символах, в окне Column Properties есть свойство Length. По умолчанию значения этого свойства равно 10.
Рис. 9. Таблица Student
Следующим шагом нужно задать ключевое поле. Это осуществляется вызовом команды “ Set Primary Key ” из контекстного меню поля Num_book. С помощью ключевого поля будут установлены связи между таблицами. В нашем случае ключевым полем есть номер зачетной книжки.
Рис. 10. Задание ключевого поля
После установки первичного ключа окно таблицы будет иметь вид как изображено на рисунке 11.
Рис. 11. Таблица Student после окончательного формирования
Теперь можно закрыть таблицу. В окне сохранения таблицы нужно задать ее имя – Student (рис. 12).
Рис. 12. Ввод имени таблицы Student
6. Создание таблицы Session.
По образцу создания таблицы Student создается таблица Session.
На рисунке 13 изображен вид таблицы Session после окончательного формирования. Первичный ключ ( Primary Key ) устанавливается в поле Num_book. Имя таблицы задается Session.
Рис. 13. Таблица Session
После выполненных действий, в окне Server Explorer будут отображаться две таблицы Student и Session.
Таким образом, в базу данных можно добавлять любое количество таблиц.
⇑
7. Редактирование структуры таблиц.
Бывают случаи, когда нужно изменить структуру таблицы базы данных.
Для того, чтобы вносить изменения в таблицы базы данных в MS Visual Studio, сначала нужно снять опцию “Prevent Saving changes that require table re-creation ” как показано на рисунке 14. Иначе, MS Visual Studio будет блокировать внесения изменений в ранее созданную таблицу. Окно Options, показанное на рисунке 14 вызывается из меню Tools в такой последовательности:
Рис. 14. Опция “ Prevent Saving changes that require table re-creation ”
После настройки можно изменять структуру таблицы. Для этого используется команда “ Open Table Definition ” (рисунок 15) из контекстного меню, которая вызывается для выбранной таблицы (правый клик мышкой).
Рис. 15. Вызов команды “ Open Table Definition ”
Также эта команда размещается в меню Data:
Предварительно таблицу нужно выделить.
8. Установление связей между таблицами.
В соответствии с условием задачи, таблицы связаны между собою по полю Num_book.
Чтобы создать связь между таблицами, сначала нужно (рисунок 16):
- выделить объект Database Diagram;
- выбрать команду Add New Diagram из контекстного меню (или из меню Data).
Рис. 16. Вызов команды добавления новой диаграммы
В результате откроется окно добавления новой диаграммы Add Table (рисунок 17). В этом окне нужно выбрать последовательно две таблицы Session и Student и нажать кнопку Add.
Рис. 17. Окно добавления таблиц к диаграмме
Рис. 18. Таблицы Student и Session после добавления их к диаграмме
Чтобы начать устанавливать отношение между таблицами, надо сделать клик на поле Num_book таблицы Student, а потом (не отпуская кнопку мышки) перетянуть его на поле Num_book таблицы Session.
В результате последовательно откроются два окна: Tables and Columns (рис. 19) и Foreign Key Relationship (рис. 20), в которых нужно оставить все как есть и подтвердить свой выбор на OK.
В окне Tables and Columns задается название отношения ( FK_Session_Student ) и названия родительской (Student) и дочерней таблиц.
Рис. 19. Окно Tables and Columns
Рис. 20. Окно настройки свойств отношения
После выполненных действий будет установлено отношение между таблицами (рисунок 21).
Рис. 21. Отношение между таблицами Student и Session
Сохранение диаграммы осуществляется точно также как и сохранение таблицы. Имя диаграммы нужно выбрать на свое усмотрение (например Diagram1).
После задания имени диаграммы откроется окно Save, в котором нужно подтвердить свой выбор (рисунок 22).
Рис. 22. Подтверждение сохранения изменений в таблицах
9. Ввод данных в таблицы.
Система Microsoft Visual Studio разрешает непосредственно вносить данные в таблицы базы данных.
В нашем случае, при установлении связи (рис. 19) первичной ( Primary Key Table ) избрана таблица Student. Поэтому, сначала нужно вносить данные в ячейки именно этой таблицы. Если попробовать сначала внести данные в таблицу Session, то система заблокирует такой ввод с выводом соответствующего сообщения.
Чтобы вызвать режим ввода данных в таблицу Student, нужно вызвать команду Show Table Data из контекстного меню (клик правой кнопкой мышки) или с меню Data (рис. 23).
Рис. 23. Команда Show Table Data
Откроется окно, в котором нужно ввести входные данные (рис. 24).
Рис. 24. Ввод данных в таблице Student
После внесения данных в таблицу Student нужно внести данные в таблицу Session.
При внесении данных в поле Num_book таблицы Session нужно вводить точно такие же значения, которые введены в поле Num_book таблицы Student (поскольку эти поля связаны между собой).
Например, если в поле Num_book таблицы Student введены значения “101”, “102”, “103” (см. рис. 24), то следует вводить именно эти значения в поле Num_book таблицы Session. Если попробовать ввести другое значение, система выдаст приблизительно следующее окно (рис. 25).
Рис. 25. Сообщение об ошибке ввода данных связанных таблиц Student и Session
Таблица Session с введенными данными изображена на рисунке 26.
Рис. 26. Таблица Session с введенными данными
Итак, база данных создана. Ввод и обработку данных в таблицах можно реализовать программным путем.