SQL CREATE DATABASE Statement

SQL CREATE DATABASE - database creation statement. It is needed to sort and organize some information. For example, there are several animals, the owner wants to assign each to one group, add a description. Data is added to the table in order to be able to select some views, sort as needed, load information from existing tables into new ones.

The user also wants to add a list of all available electrical devices, for which he creates another table. Since these are not groups connected in any way, for each you need to create your own SQL database. CREATE DATABASE Animals and CREATE DATABASE Electrical. Each can be made available or closed to other users. If it contains several tables, some can be made visible, others can be made invisible.

Next, the administrator adds a table of animal feed names and adds to Animals. All users who have access to the Animals database can only read information from two tables. And only the administrator has the right to make changes, delete or add information. At the same time, users with access to Electrical are trustees, have administrator rights: delete, add, edit records.

About DDL, DML, DCL

SQL is a declarative programming language.

It is used to create data objects, modify strings. It is a collection of operators, instructions, calculated functions. Operators are divided into three types:

• DDL - definition;
• DML - manipulation;
• DCL - administrative operations, rights management.

The following commands are used for data manipulation: SELECT, INSERT, UPDATE, DELETE. To assign certain permissions to a user or group, the GRANT, REVOKE, DENY operators are used.

To work with data, three basic queries are used:

• CREATE
• ALTER;
• DROP.

Create is used to create tables, indexes. Creating databases in SQL - SQL CREATE DATABASE base_name.

Alter changes the properties of the created object. For example, adds, deletes or modifies a column, controls integrity constraints. DROP deletes a pre-existing object.

CREATE

CREATE is a universal operator that is used in many other DBMSs. It creates database objects. It can be:

• table;
• representation;
• database.

Creating a table:

CREATE TABLE table_name 

In this case, table_name must be unique so that there are no errors. Fields are assigned inside parentheses () after the table name.

 CREATE TABLE table_name ( arg ) 

For instance,

A table called Planets is endowed with fields such as ID and OpeningYear, which accept only numeric values. PlanetName - a string of 10 characters. Radius and SunSeason can be floating point numbers. HavingRings is a boolean variable and takes the values ​​true or false.

CREATE VIEW creates a view. This is such a virtual table that contains information taken from other tables. When they change in the parent tables, in the view they also change. Manipulating data is no different from how this happens with tabular data.

Creating a database in SQL - CREATE DATABASE base_name.

CREATE DATABASE

The statement for determining language data in SQL Server is CREATE DATABASE. Used to create a database. It is universal and designed for many DBMSs. And not just SQL. In POSTGRESQL CREATE, DATABASE also creates a new database. The full syntax looks like this:

Using the CREATE DATABASE statement, a database named db_name is created. The name must be unique, therefore, another database with the same name must not exist, otherwise an error occurs when executing the command.

You can assign a password using the SQL CREATE DATABASE User Password command. But there is still an option.

Using SQL CREATE DATABASE Character set utf8, an encoding is assigned.

Database_name

When creating a database in MS SQL CREATE DATABASE, the name is indicated. It must comply with the rules of identifiers. Here are the main ones.

1. Starts with a character from A to Z or a to z.
2. An underscore, @ symbol, dollar sign $, pound sign # can be used in a name.
3. Must not repeat reserved words.
4. Space characters and special characters are not allowed.

The name may contain a single SQL character CREATE DATABASE t.

The name contains a maximum of 120 characters. If not specified, formed by adding a suffix to database_name. Thus, the length is limited to 123 characters so that the generated name does not exceed the established 128 characters.

CONTAINMENT

It has been used in SQL Server since version 2012. It is responsible for database autonomy. It has two meanings:

• NONE
• PARTIAL.

An independent database solves the problem of losing information such as login, password when moving the database between servers. In turn, the autonomous database stores the information necessary for work. Completely independent of SQL server settings, not tied to external dependencies. The default value is NONE. This means that the base is not autonomous.

Before assigning CONTAINMENT = PARTIAL, you must enable the use of independent databases at the server level.

ON and COLLATE

On indicates that disk files for storing partitions are explicitly defined. It is used exclusively in conjunction with the list of <filespec> elements.

PRIMARY <filespec> defines the primary file. If not specified, then the first list file in the CREATE DATABASE statement is used as primary.

LOG ON <filegroup> indicates that the <filegroup> log files are explicitly defined.

COLLATE collation_name is responsible for sorting the database. If not specified, the collation is set by default for the instance of SQL Server. Cannot be specified with FOR ATTACH and FOR ATTACH_REBUILD_LOG clauses. Sorting independent databases is different.

WITH options:

1. <filestream_options> is responsible for non-transactional access; it has three values: OFF (access is disabled), READ_ONLY (read-only), FULL (full access). The directory name is set using DIRECTORY_NAME = <directory_name>. It must be unique, it is checked when registering.
2. DEFAULT_FULLTEXT_LANGUAGE is set if the base is partially autonomous. This is an advanced option, therefore it is recommended to use it only for experienced administrators. With its help, the language for full-text indexes is determined. The default is the server language.
3. DEFAULT_LANGUAGE defines the language for the created login names. An identifier in the range 0-32 is assigned.
4. TRANSFORM_NOISE_WORDS is used to suppress error messages that occur due to the fact that skipped words contribute to the return of 0 lines. The default value is 0, so stop words are not converted, which returns 0 lines. By setting the value to 1, the words are converted and skipped, so no errors occur.
5. In TWO_DIGIT_YEAR_CUTOFF, the year is specified in the range from 1753 to 9999. By default, the time period for SQL is 1950-2049, so entering 30 will return 2030, but 50 will be interpreted as 1950.
6. DB_CHAINING organizes cross-database access, which provides the ability to access objects from another database.
7. Using TRUSTWORTHY, the power of attorney of the database instance to the content is established. By default it is set to off, which protects against vulnerabilities when connecting some databases.

<filespec>

Designed to manage file properties. Logical_file_name is a logical name that is used when accessing a file. Must be unique, comply with the rules for identifiers.

 FILENAME = { 'os_file_name' | 'filestream_path' } 

In this case, the filename variable is assigned the name of the operating system or the path to the file. Size is responsible for the initial size, and maxsize is the maximum.

Database Operators

It is necessary to add some information to the newly created database. SQL CREATE DATABASE example:

The most important SQL language expressions are involved here. On the first line, the administrator creates a database called mydb. It is assigned as the primary using the USE mydb command.

 USE db1; SELECT COUNT(*) FROM mytable; # selects from db1.mytable USE db2; SELECT COUNT(*) FROM mytable; 

In this expression, db1 first becomes primary, but then db2.

After that, mytable is created with the numeric field PRIMARY KEY and a string of 20 characters name. To create a table that will contain the value of another table, a bunch of AS / SELECT / FROM statements is used.

 CREATE TABLE new_table_name AS SELECT column1, column2,... FROM existing_table_name WHERE ....; 

Here you can see that a table is created under the name new_table_name, before inserting data from other tables, columns must be selected with the SELECT command. Here select column1 and column2. The parent table from which the information is imported is determined.

 FROM existing_table_name 

After creating and adding some data, you can check all existing databases with the SHOW DATABASES command.

To insert new values ​​into the table, use the INSERT INTO command, after the word INTO is the name of the table being processed. After the statement, there may be priority values: LOW_PRIORITY, DELAYED, HIGH_PRIORITY or IGNORE.

To determine the added variables, the VALUES command is written, and in parentheses a bunch of values ​​(1, Will), (2, Marry) and (3, Dean). If you intend to add a single value, VALUE is used instead of the VALUES keyword. It is allowed to use the SET operator, it is sometimes easier to use, because you do not need to remember the order of the columns. Values ​​are assigned in the form col = expr.

Then, using the SELECT command, data is selected for subsequent manipulation of their values. Fields are selected such as id, name from the table mytable, id is assigned the value 1. To modify or update existing data, UPDATE is used, the variable name is set to Willy.

Next, data is selected from the table and deleted. To completely destroy an existing table, use DROP DATABASE table_name. But only those for which access is open are deleted.

Creating a database with files and a log

In this example, the Sales database is created, where the first saledat.mdf file becomes primary, because the PRIMARY keyword is not used. SIZE and MAXSIZE values ​​are indicated without MB or KB suffixes; by default, memory is allocated in megabytes. SIZE - 10 MB, MAXSIZE - 15 MB.

If you need to specify several data files, each of them is taken in parentheses, listed with a comma. For example, like this:

Thus, three files were created with a maximum size of 200 MB.

File grouping

If there are several files and they need to be grouped, this is implemented using the FILEGROUP group_name statement.

In this case, there are three groups SalesGroup1, SalesGroup2. They contain two files, each of them has a maximum size of 50 MB. Separately, the Sales_log log file was created. Two - SPri1_dat and SPri2_dat do not belong to any of the groups, they have a size of 10 MB, but 50 MB is allocated for their contents.

Creating a database, filegroups

In the current example, the FileStreamDB database is created. It defines a file group of strings, two file FILESTREAM. Each of them contains only one file.

 USE master; GO -- Get the SQL Server data path. DECLARE @data_path nvarchar(256); SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) FROM master.sys.master_files WHERE database_id = 1 AND file_id = 1); -- Execute the CREATE DATABASE statement. EXECUTE ('CREATE DATABASE FileStreamDB ON PRIMARY ( NAME = FileStreamDB_data ,FILENAME = ''' + @data_path + 'FileStreamDB_data.mdf'' ,SIZE = 10MB ,MAXSIZE = 50MB ,FILEGROWTH = 15% ), FILEGROUP FileStreamPhotos CONTAINS FILESTREAM DEFAULT ( NAME = FSPhotos ,FILENAME = ''C:\MyFSfolder\Photos'' -- SIZE and FILEGROWTH should not be specified here. -- If they are specified an error will be raised. , MAXSIZE = 5000 MB ), ( NAME = FSPhotos2 , FILENAME = ''D:\MyFSfolder\Photos'' , MAXSIZE = 10000 MB ), FILEGROUP FileStreamResumes CONTAINS FILESTREAM ( NAME = FileStreamResumes ,FILENAME = ''C:\MyFSfolder\Resumes'' ) LOG ON ( NAME = FileStream_log ,FILENAME = ''' + @data_path + 'FileStreamDB_log.ldf'' ,SIZE = 5MB ,MAXSIZE = 25MB ,FILEGROWTH = 5MB )' ); GO 

The variable @data_path is declared - a string with a allocated space of 256 characters.

 SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) 

The SUBSTRING function returns part of the string in the first argument, the other two specify the length of the value. The second argument denotes the first character, the third - the last. As the last parameter, the expression CHARINDEX is used, which returns the starting position of the first argument. With its help, a string expression physical_name is converted to lowercase in the master.mdf file.

The EXECUTE statement starts a function in parentheses. This creates the FileStreamDB_data variable, the FileStreamPhotos and FileStreamResumes groups.

The FileStreamPhotos filegroup contains FILESTREAM data: two files, FSPhotos and FSPhotos2. The second group, FileStreamResumes, contains FILESTREAM FileStreamResumes. A FileStream_log was also created with a maximum size of 25 MB.