Creating a table of monetary numbers in sql. SQL - what it is, what the language is needed for, and basic functions for beginners
Lecture
Creating a database using SQL.
Data manipulation in SQL
Included in the SQL language includes a data description language, which allows you to manage tables, and a data manipulation language, which is used to manage data ( slide 2 ).
17.1. Building databases using SQL
17.1.1. Table creation command − CREATETABLE
A table is created using the CREATE TABLE command. The general command syntax is as follows (slide 3).
Those. after specifying the table name separated by commas in parentheses All clauses that define individual elements of the table - columns or integrity constraints - must be listed:
table_name – identifier created table, which in general case is built from the database name, the name of the table owner and the name of the table itself. In this case, the combination of the table name and its owner must be unique within the database. If the table is not created in the current database, its identifier must include the database name.
column_definition – specifying the name, data type and parameters of an individual table column. Column names must follow the rules for identifiers and be unique within the table.
table_constraint definition – specifying some integrity constraint at the table level.
Column descriptions
As you can see from the syntax of the CREATE TABLE command, a clause is specified for each column<определение_столбца>, which is used to set the properties of the column. The sentence has the following syntax( slide 3) :
Let's look at the purpose and use of parameters.
Column_name is an identifier that specifies the name of the table column.
data_type – specifies the data type of the column. If the column definition does not explicitly specify a restriction on storing NULL values, then the properties of the data type will be used, i.e. if the selected data type allows you to store NULL values, then the column can also store NULL values. If, when defining a column in the CREATE TABLE command, the storage of NULL values is explicitly allowed or prohibited, then the properties of the data type will be overridden by the restriction set at the column level. For example, if the data type allows you to store NULL values, and the column level is disabled, then attempting to insert a NULL value into the column will fail.
column_constraint – This clause specifies the constraints that will be defined on the column. The sentence syntax is as follows ( slide 4 ):
Let's look at the purpose of the parameters.
CONSTRAINT is an optional keyword followed by the name of the constraint on the column values (constraint_name). Constraint names must be unique within the database.
DEFAULT - Sets the default value for the column. This value will be used when inserting a row if no value is explicitly specified for the column.
NULL|NOT NULL – keywords that allow (NULL) or prohibit (NOT NULL) the storage of NULL values in a column. If a column does not have a default value, inserting a row with an unknown value for the column will attempt to insert a NULL value into the column. If the NOT NULL constraint is specified for the column, the attempt to insert the row will be rejected and the user will receive an appropriate error message.
PRIMARY KEY – definition of a primary key at the single column level (i.e. the primary key will consist of only the values of one column). If it is necessary to form a primary key based on two or more columns, then such an integrity constraint must be specified at the table level. Please remember that only one primary key can be created for each table.
UNIQUE - Specifies that a UNIQUE integrity constraint be created on a column to ensure that each individual value in the column is unique within that column. Multiple UNIQUE integrity constraints can be created on a table.
FOREIGN KEY ... REFERENCES – an indication that the column will serve as a foreign key for the table whose name is specified using the parameter<имя_главной_таблицы>.
(column_name [,...,n]) – a column or list of comma-separated columns of the main table included in the FOREIGN KEY constraint. In this case, columns included in the foreign key can only refer to columns of the primary key or columns with UNIQUE constraint tables.
ON DELETE (CASCADE | NO ACTION) – These keywords define the action taken when a row is deleted from the main table. If the CASCADE keyword is specified, then when a row is deleted from the main (parent) table, the row in the dependent table will also be deleted. When specifying the NO ACTION keyword in such a case an error will be thrown. The default value is NO ACTION.
ON UPDATE (CASCADE | NO ACTION) – These keywords define the actions taken when a row in the main table is modified. If the CASCADE keyword is specified, then when a row from the main (parent) table is modified, the row in the dependent table will also be modified. Using the NO ACTION keyword in this case will generate an error. The default value is NO ACTION.
CHECK – an integrity constraint that initiates control of values entered into a column (or columns).
boolean_expression – a boolean expression used for the CHECK constraint.
Table level restrictions
The CREATE TABLE command syntax uses the clause<ограничение_таблицы>, which defines integrity constraints at the table level. The sentence syntax is as follows (slide 5) .
The purpose of the parameters coincides with the purpose of similar parameters of the proposal<ограничение_столбца > . However, in the proposal<ограничение_таблицы>There are some new options:
column_name – the column (or list of columns) on which you want to apply some integrity constraints.
– a method for organizing data in an index. An index is created by specifying the keywords PRIMARY KEY, UNIQUE. When specifying the ASC value, the data in the index will be ordered in ascending order; when specifying the DESC value, the data in the index will be ordered in descending order. The default value is ASC.
Examples of creating tables
As an example, consider the instructions for creating tables in the “Session” database:
The "Students" table consists of the following columns:
ID_Student – INTEGER data type, unique key;
Group_number - data type CHAR, length 6;
slide 6).
Address And Telephone, restrictions are imposed NOT NULL
To create the “Disciplines” table, the command (slide 7).
The table contains 2 columns (ID_Discipline, Name).
To columns ID_Discipline, Name restrictions are imposed NOT NULL , prohibiting the entry of a line if the column value is undefined.
Column ID_Disciplinedeclared as a primary key, and on the values entered into the column Name, a uniqueness condition is imposed.
The Curriculum_Plan table includes the following columns:
ID_Discipline – data type INTEGER;
Semester - data type INTEGER;
Number_of_hours - data type INTEGER;
The table was created using next command (slide 8).
For column values Semester a logical expression is formulated that allows you to enter only values from 1 to 10.
The “Summary_statement” table consists of the following columns:
ID_Student – INTEGER data type, unique key column;
ID_Plan – INTEGER data type, unique key column;
Evaluation - data type INTEGER;
Submission_date - data type DATETIME;
ID_Teacher - INTEGER data type.
The table was created using the following command (slide 9).
All table columns have restrictions NOT NULL , prohibiting the entry of a line if the column value is undefined.
For column values Grade a logical expression has been formulated that allows you to enter only values from 0 to 5: 0 – failure, 1 – pass, 2 – unsatisfactory, 3 – satisfactory, 4 – good, 5 – excellent.
And finally, we list the “Personnel_composition” columns:
ID_Teacher – INTEGER data type, unique key;
Last name – data type CHAR, length 30;
Name - data type CHAR, length 15;
Middle name - data type CHAR, length 20;
Position - data type CHAR, length 20;
Chair - data type CHAR, length 3;
Address - data type CHAR, length 30;
Phone - data type CHAR, length 8.
The table was created using the following command (slide 10).
For all table columns except columns Address And Telephone, restrictions are imposed NOT NULL , prohibiting the entry of a line if the column value is undefined.
For the “Curriculum_plan” and “Summary_statement” tables, foreign keys must be built that link the “Session” database tables:
FK_Discipline – a foreign key linking the “Curriculum_plan” and “Discipline” tables using the ID_Discipline column;
FK_Personnel_composition – a foreign key linking the “Curriculum_plan” and “Personnel_composition” tables by the ID_Teacher column;
FK_Student – a foreign key connecting the tables “Summary_statement” and “Students” by the ID_Student column;
FK_Plan – a foreign key linking the tables “Summary_statement” and “Curriculum_plan” by column ID _Plan.
Addition foreign keys in the tables we will consider further when discussing the capabilities of the command ALTER TABLE.
17.1.2. Changing table structure - command ALTERTABLE
No matter how carefully the table structure is planned, sometimes it becomes necessary to make some changes to it. Let’s assume that you need to add the number to the already formed “Teachers” table home phone And home address. This operation can be performed in various ways. For example, you can delete a table with the old structure and create a new table with the desired structure instead. The disadvantage of this method is that it will be necessary to copy the data in the table somewhere and rewrite it into a new table after it is created.
Special Team ALTER TABLE is intended to modify the table structure. With its help, you can change the properties of existing columns, remove or add columns to a table, and manage integrity constraints at both the column and table levels, i.e. perform the following functions:
Add a new column definition to the table;
Remove a column from a table;
Change the default value for a column;
Add or remove a table's primary key;
Add or remove a foreign key to a table;
Add or remove a uniqueness condition;
Add or remove a condition on a value.
General command syntax ALTER TABLE presented on the slide (slide 11).
ALTER TABLE command takes care of all the actions of copying data to a temporary table, deleting the old table, creating one instead new table with the required structure and subsequent rewriting of data into it.
Assigning many command parameters and keywords ALTER TABLE similar to assigning the corresponding command parameters and keywords CREATE TABLE (for example, the construction syntax<определение_столбца>coincides with the syntax of a similar command construction CREATE TABLE ).
Basic command usage modes ALTER TABLE are as follows:
Adding a column;
Delete a column;
Column modification;
Changing, adding and removing constraints (primary and foreign keys, default values).
Adding a Column
To add a new column you should use the keyword ADD followed by a column definition.
Let’s add, for example, to the “Students” table the column “Year of admission” (slide 12). After executing this command, another column will be added to the Student table structure with a default value equal to the current year (the default value is calculated using two built-in functions - YEAR() and GETDATE()).
Column Modification
To modify an existing table column, use the keyword ALTER COLUMN . Changing column properties is not possible if:
the column participates in PRIMARY KEY or FOREIGN KEY constraints;
the column is subject to CHECK or UNIQUE integrity constraints (the exception is for columns that have a variable length data type, that is, data types starting with var);
if a default value is associated with the column (in which case the length, total number of digits, or number of digits after the decimal point can be changed, as long as the data type remains unchanged).
When defining a new data type for a column, keep in mind that old type data must be converted to a new one.
An example of modifying the “Group_Number” column of the “Students” table (data type INTEGER is replaced by CHAR) ( slide 12).
Delete a column
To remove a column from a table, use the clause DROP COLUMN <имя_столбца>. When deleting columns, be aware that you cannot delete columns with integrity constraints CHECK, FOREIGN KEY, UNIQUE or PRIMARY KEY , as well as columns that have default values defined (either as a column-level or table-level integrity constraint).
Consider, for example, the command to delete the “Year of Entry” column from the “Student” table (slide 12).
This command will not be executed, i.e. a default value was defined when the column was added.
Adding Constraints at the Table Level
To add restrictions at the table level, use the clause ADD CONSTRAINT <имя_ограничения>.
As an example, let's look at the commands for adding foreign keys to the Session database tables (slide 13):
· adding foreign keys to the “Curriculum_plan” table (creating a relationship with the name FK _Discipline and connections with the name FK_ Personnel_composition);
· adding foreign keys to the “Summary_statement” table (creating a relationship with the name FK _Student and connections with the name FK _Plan).
By design ADD CONSTRAINT a named constraint is created. It should be noted that deleting any table-level constraint occurs only by its name, so the constraint must be named (so that it can be deleted).
Removing restrictions
To remove an integrity constraint from a table, use the clause DROP CONSTRAINT <имя_ограничения>.
Removing an integrity constraint is only possible if it is named (i.e. a clause<определение_ограничения>contains the naming of the constraint CONSTRAINT ).
Command to delete a constructed foreign key FK _The discipline from the “Curriculum_plan” table looks like this (slide 14).
On the slide ( slide 14) shows the removal of the previously constructed constraint on the default value DEF _Group_number.
17.1.3. Delete tables - command DROPTABLE
Removing a table is done using the command DROP TABLE ( slide 14).
The command's only argument specifies the name of the table to be deleted.
The operation of deleting a table in some cases requires some attention. Cannot drop a table if there is an integrity constraint on it FOREIGN KEY another table is referenced: an attempt to delete the “Disciplines” table will cause an error message, because the discipline table is referenced by the “Curriculum_plan” table.
17.2. Data management
The purpose of any database management system is ultimately to enter, modify, delete, and retrieve data. Let's look at data management methods using the SQL language.
17.2.1. Data Extraction - Command SELECT
The main data sampling tool in SQL language is the team SELECT . Using this command, you can access data presented as a collection of tables of almost any complexity.
The most commonly used simplified version of the command is SELECT , which has the following syntax (slide 15).
SELECT statement is divided into separate sections, each of which has its own purpose. From the given syntax description it is clear that only the sections are mandatory SELECT and FROM , and the remaining sections can be omitted. Full list sections are shown on the slide (slide 15).
SELECT section
Main purpose of the section SELECT (one of the two required sections that must be specified in any query) - specifying a set of columns returned after the query is executed, i.e. appearance result. In the simplest case, a column from one of the tables participating in the query is returned. In more complex situations, a set of values in a column is formed as the result of evaluating an expression. These columns are called calculated columns and are not given any name by default.
If necessary, the user can specify a custom name for the column returned after executing the query. This name is called a pseudonym ( alias ). In normal situations, assigning an alias is not necessary, but in some cases it is required to specify it explicitly. This is most often required when working with a section INTO where each of the returned columns must have a name, and that name must be unique.
In addition to the above, using the section SELECT You can limit the number of rows that will be included in the sample result. Section Syntax SELECT next ( slide 16).
Let's look at the purpose of the parameters.
Keywords ALL | DISTINCT. When specifying a keyword ALL the query result displays All rows that satisfy the formulated conditions, thereby allowing the inclusion of identical rows in the result (the sameness of rows is determined at the level of the selection result, and not at the level of the source data). Parameter ALL is the default.
If in a SELECT query the keyword is indicated DISTINCT , then the sample result will not include more than one duplicate row. This way, each row returned will be unique. The uniqueness of a row is determined at the level of rows of the sample result, and not at the level of the source data. If two columns are included in the sample result, uniqueness will be determined by the values of both of those columns. Individually, the values in the first and second columns can be repeated, but the combination of values in both columns must be unique. Similar rules apply to more columns.
Consider the result of using keywords ALL and DISTINCT using the example of selecting the Semester and Reporting columns from the “Curriculum_plan” table of the “Session” database ( slide 17 ). First, let's run a query specifying the keyword ALL . A fragment of the result is presented on the slide. Now let's replace the keyword ALL to DISTINCT . In this case, the query result presented on the slide is the lines containing same values in columns included only once. This result should only indicate the presence of different forms of reporting in semesters.
KeywordTOP n. Using the TOP n keyword, where n is a numeric value, allows you to select not all rows as a result, but only the first n. This selects the first rows of the sample result, not the original data. Therefore, the set of rows resulting from the selection when specifying the TOP keyword may vary depending on the sort order. If the request uses a section WHERE , then the TOP keyword operates on the set of rows returned after applying the Boolean condition defined in section WHERE.
Let's demonstrate the use of the TOP keyword (slide18 )
In this example from the table Students database "Session" the first 5 rows were selected.
You can also select not a fixed number of rows, but a certain percentage of all rows that satisfy the condition. To do this you need to add a keyword PERCENT.
There were 115 rows in the table, so 10% would be 11.5 rows. The result will be 12 lines.
If the specified row percentage is a non-integer, the server always rounds up.
We also provide an example demonstrating the effect of sort order on the returned set of rows (slide19 ).
When specified along with an offer ORDER BY keyword WITH TIES the result will also include rows that match the value of the sorting column with the last displayed query rows SELECT TOP n [PERCENT].
Using a Keyword WITH TIES in the previous example will ensure that, in response to a request, information about all students of the first group in order is provided (slide20 ).
Offer<Список_выбора>. Sentence syntax<Список_выбора>next (slide 21).
The “*” symbol means that the result includes all columns in the list of partition tables FROM.
If the result does not need to include all columns everyone tables, you can explicitly specify the name of the object from which you want to select all columns (<Имя_таблицы>.* or<Table_alias>.*).
A separate table column is included in the selection result by explicitly specifying the column name (parameter<Имя_столбца>). The column must belong to one of the tables specified in the FROM clause. If the column with specified name exists in more than one data source listed in the FROM clause, you must explicitly specify the name of the data source to which the column belongs in the format<Имя_таблицы>.<Имя_столбца>.Otherwise an error message will be displayed.
For example, let's try to select data from the ID_Discipline column, which is available in the "Discipline" and "Curriculum" tables:
An error message will be returned indicating that the name 'ID_Discipline' is not used correctly.
That is, in this case you must explicitly specify the name of the data source to which the column belongs, for example:
Columns returned as query results can be given aliases. Aliases allow you to change the name of the original column or name the column whose contents are obtained as the result of evaluating an expression. The alias name is specified using the parameter<Псевдоним_столбца>. Key words AS is optional when specifying an alias. In general, the server does not require the names of the sample result columns to be unique, so different columns can have same names or pseudonyms.
The columns resulting from the selection can not only be a copy of a column from one of the source tables, but also be formed based on the calculation of an expression. Such a column in the select list is specified using the construct<Выражение> [ <Псевдоним_столбца>]. The expression can contain constants, column names, functions, and combinations thereof. Additionally, you can assign an alias to the column formed based on the calculation of the expression by specifying it using the parameter<Псевдоним_столбца>. By default, a calculated column has no name.
Another way to form a calculated column is to use an equal sign construct:<Псевдоним_столбца> = <Выражение>. The only difference between this method and the previous one is the requirement to specify an alias. In the simplest case, the expression is a column name, constant, variable, or function. If the expression is a column name, then we get another way to set an alias for a column.
Consider the following example. Suppose that for the “Students” table you need to build a query representing the last name, first name and patronymic in one column. Using the concatenation (addition) operation character strings and the value of Full Name as a column alias, we’ll build a query ( slide 22 ).
FROM section
Using the section FROM the data sources with which the query will work are determined.
Section Syntax FROM next ( slide23 )
At first glance, the section design looks simple. However, upon closer inspection it turns out to be quite complex. Mainly working with the section FROM This is a comma-separated list of data sources that the query should work with. The actual data source is indicated using a clause<Источник_данных>, the syntax of which is presented on the slide.
Using the parameter<имя_таблицы>the name of a regular table is indicated. Parameter<псевдоним_таблицы>is used to assign an alias to the table under which it will need to be referenced in a query. Table aliases are often used to provide a link to the required table make it more convenient and short. For example, if the query often mentions the table name “Curriculum_plan”, then you can use an alias, for example, tpl . Specifying a keyword AS is not mandatory.
WHERE clause
WHERE clause is intended for imposing vertical filters on the data processed by the request. In other words, using the section WHERE you can narrow the set of rows included in the sample result. For this purpose it is indicated logical condition, which determines whether the row will be included in the query selection or not. A row is included in the fetch result only if the Boolean expression returns a value TRUE.
In general, a Boolean expression contains the names of the table columns that the query operates on. For each row returned by the query, a Boolean expression is evaluated by substituting the column names for the specific values from the corresponding row. If an expression evaluates to return TRUE , that is, the expression is true, then the string will be included in the final result. Otherwise, the row is not included in the result. If necessary, you can specify more than one logical expression, combining them using logical operators OR and AND.
Let's look at the section syntax WHERE( slide 24).
In design<условие_отбора>You can define any logical condition that will cause the row to be included in the result.
The example shown on the slide demonstrates the logic of the section. WHERE . As a result, a list of all students who entered the faculty before 2000 will be returned.
In addition to comparison operations (=, >,<, >=, <=) и логических операторов OR, AND, NOT When forming a selection condition, additional logical operators can be used to expand data management capabilities. Let's look at some of these operators.
OperatorBETWEEN. Using this operator, you can determine whether the value of a specified quantity lies within a specified range. The syntax for using the operator is as follows ( slide 25 ).
<Выражение>specifies the value to be checked, and the arguments<начало_диапазона>And<конец_диапазона>determine the possible boundaries of its change. Using the operator NOT together with the operator BETWEEN allows you to set a range beyond which the value being tested can change.
When executing the statement BETWEEN transforms into a construction of two comparison operations.
Let's look at an example of using the operator BETWEEN ( slide 25). As a result of following the instructions, we will receive a list of curriculum disciplines with the number of hours from 50 to 100.
OperatorIN. The operator allows you to specify in the selection condition a set of possible values for the value being checked. The syntax for using the operator is as follows (slide 26).
<Выражение>indicates the value being checked, and the arguments<выражение1>,…, <выражение N > specify by comma-separated enumeration a set of values that the value being checked can take. Keyword NOT performs logical negation.
Let's look at an example of using the operator IN ( slide 26). As a result of following the instructions, we will obtain curriculum lines for the disciplines “English Language” and “Physical Education”.
OperatorLIKE. Using an operator LIKE You can compare a character type expression with a given pattern. The operator syntax is as follows (slide 27).
<Образец>specifies a character pattern to compare and is enclosed in quotes. The pattern may contain delimiter characters. The following delimiter characters are allowed (slide 27):
% - can be replaced in a symbolic expression by any number of arbitrary characters;
_ - can be replaced in a symbolic expression by any but only one symbol;
[ ABC0-9] - can be replaced in a character expression by only one character from the set specified in square brackets (the hyphen is used to indicate a range);
[^ ABC0-9] - can be replaced in a character expression by only one character other than those specified in square brackets (the hyphen is used to indicate a range).
Let's look at an example of using the operator (slide 27). Using a sample for the value of the Position column in this case allowed us to select rows with the values “Senior teacher.” and "Prof"
ChapterORDERBY
ORDER BY section is intended to organize the set of data returned after executing a query. Full Section Syntax ORDER BY next ( slide 28).
Parameter<условие_сортировки>requires specifying an expression according to which the rows will be sorted. In its simplest case, this expression is the name of a column from one of the query data sources.
It should be noted that the expression in accordance with which the rows are sorted can also use columns that are not specified in the SELECT section, that is, not included in the selection result.
ORDER BY section allows the use of the ASC and DESC keywords, which can be used to explicitly specify how strings should be ordered. If you specify the ASC keyword, the data will be sorted in ascending order. If you want to sort the data in descending order, use the DESC keyword. The default is to sort in ascending order.
Data can be sorted by multiple columns. To do this, you need to enter the column names separated by commas in sort order. The data is first sorted by the column whose name was specified first in the section ORDER BY . Then, if there are many rows with the same value in the first column, it further sorts those rows by the second column (within the group with the same value in the first column), and so on.
Here's an example of sorting by two columns (slide 28).
UNION section
UNION section serves to combine selection results returned by two or more queries.
Let's look at the section syntax UNION ( slide 29).
To be able to apply a join operation to query results, they must meet the following requirements:
queries must return the same set of columns (and it is necessary to guarantee the same order of columns in each of the queries);
the data types of the corresponding columns of the second and subsequent queries must support implicit conversion or match the data type of the columns of the first query;
none of the results can be sorted using the ORDER BY clause (however the overall result can be sorted, as will be shown below)
Specifying a keyword ALL instructs to include duplicate lines in the result. By default, duplicate rows are not included in the result.
Let's demonstrate the use of the section UNION . Let's look at the "Personnel_Composition" and "Students" tables and try to build, for example, a general list of both students and teachers whose phone numbers begin with 120. Let's sort the resulting list alphabetically by adding a sentence ORDER BY ( slide 29).
When you join tables, the columns in the resulting data set are always given the same names as those specified in the first query you join.
Before you can create an SQL table, you need to define the database model. Design an ER diagram in which to define entities, attributes and relationships.
Basic Concepts
Entities are objects or facts about which information must be stored. For example, an employee of a company or projects implemented by the company. Attributes are components that describe or qualify an entity. For example, the attribute of the “employee” entity is salary, and the attribute of the “project” entity is the estimated cost. Connections are associations between two elements. It can be bidirectional. There is also a recursive connection, that is, the connection of an entity with itself.
It is also necessary to determine the keys and conditions under which the integrity of the database will be maintained. What does it mean? In other words, restrictions that will help keep databases in a correct and consistent form.
Transition from ER diagram to tabular model
Rules for transition to a tabular model:
- Convert all entities to tables.
- Convert all attributes to columns, that is, each entity attribute must appear in the table column name.
- Convert unique identifiers to primary keys.
- Convert all relationships to foreign keys.
- Create the SQL table.
Creating a database
First you need to start the MySQL server. To launch it, go to the "Start" menu, then to "Programs", then to MySQL and My SQL Server, select MySQL-Command-Line-Client.
To create a database, use the Create Database command. This function has the following format:
CREATE DATABASE database_name.
The restrictions on the name of the database are as follows:
- length is up to 64 characters and can include letters, numbers, "" and "" symbols;
- the name can start with a number, but it must contain letters.
You need to remember the general rule: any request or command ends with a delimiter. In SQL, it is common to use a semicolon as a delimiter.
The server needs to indicate which database it will need to work with. There is a USE statement for this. This operator has a simple syntax: USE n database_name.
Creating a SQL Table
So, the model is designed, the database is created, and the server is told exactly how to work with it. Now you can start creating SQL tables. There is Data Definition Language (DDL). It is used to create MS SQL table, as well as to define objects and work with their structure. DDL includes a set of commands.
SQL Server table creation
Using just one DDL command, you can create various database objects by varying its parameters. The Create Table command is used. The tt format looks like this:
CREATE TADLE table_name,(column_name1 Name _column2 datatype [column_constraint],[table_constraints]).
The syntax of this command should be described in more detail:
- The table name must be up to 30 characters long and begin with a letter. Only alphabetic characters, letters, and the symbols "_", "$" and "#" are allowed. The use of Cyrillic alphabet is allowed. It is important to note that table names should not be the same as other object names or database server reserved words such as Column, Table, Index, etc.
- You must specify a data type for each column. There is a standard set used by most. For example, Char, Varchar, Number, Date, Null type, etc.
- The Default parameter allows you to set a default value. This ensures that there are no null values in the table. What does it mean? The default value can be a symbol, an expression, a function. It is important to remember that this default data type must match the column's input data type.
- Constraints on each column are used to enforce integrity conditions for data at the table level. There are other nuances. It is prohibited to delete a table if there are other tables dependent on it.
How to work with the database
Large projects often require the creation of multiple databases, each requiring many tables. Of course, it is impossible for users to retain all the information in their heads. To do this, it is possible to view the structure of databases and tables in them. There are several commands, namely:
- SHOW DATABASES - shows all created SQL databases on the screen;
- SHOW TABLES - displays a list of all tables for the current database that are selected by the USE command;
- DESCRIBE table_name- shows a description of all table columns.
- ALTER TABLE - allows you to change the table structure.
The last command allows:
- add a column or constraint to a table;
- change an existing column;
- delete column or columns;
- remove integrity constraints.
The syntax for this command is: ALTER TABLE table_name( | | | | [(ENABLE | DISABLE) CONSTANT constraint_name ] | }.
There are other commands:
- RENAME - rename the table.
- TRUNCATE TABLE - removes all rows from the table. This function may be needed when it is necessary to fill the table again, but there is no need to store previous data.
There are also situations when the structure of the database has changed and the table should be deleted. There is a DROP command for this. Of course, you first need to select the database from which you want to delete the table, if it is different from the current one.
The command syntax is quite simple: DROP TABLE Name_tables.
In SQL Access, tables are created and modified using the same commands listed above.
Using CREATE TABLE you can create an empty table and then fill it with data. But that is not all. You can also directly create a table from another table. Like this? That is, it is possible to define a table and fill it with data from another table. There is a special keyword AS for this.
The syntax is very simple:
- CREATE TABLE Name_tables[(column_definition)] AS subquery;
- column_definition - column names, integrity rules for newly created table columns, and default values;
- subquery - returns the rows that need to be added to the new table.
Thus, such a command creates a table with certain columns, inserts rows into it, which are returned in the query.
Temporary tables
Temporary tables are tables whose data is erased at the end of each session or earlier. They are used to record intermediate values or results. They can be used as worksheets. You can define temporary ones in any session, but you can use their data only in the current session. Creation of temporary SQL tables occurs in the same way as usual, using the CREATE TABLE command. In order to show the system that the table is temporary, you need to use the GLOBAL TEMPORARY parameter.
The ON COMMIT clause sets the lifetime of data in such a table and can do the following:
- DELETE ROWS - clear the temporary table (delete all session data) after each transaction completion. This is usually the default value.
- PRESERVE ROWS - leave data for use in the next transaction. In addition, you can clear the table only after the session ends. But there are some peculiarities. If a transaction is rolled back (ROLLBACK), the table will be returned to its state at the end of the previous transaction.
The syntax for creating a temporary table can be represented as follows: CREATE TABLE Name_tables,(Name_column1 datatype [column_constraint], Name _column2 data type [column_constraint], [table_constraints]).
Section 4 Information systems
Introduction to SQL.
Creating, modifying and deleting tables.
Retrieving data from a table.
Creating SQL queries.
Data processing in SQL.
Methods of teaching this topic at school.
Introduction to SQL. SQL is a structured query language that makes it possible to create and work in relational databases, which are sets of related information stored in tables. The language is focused on operations with data presented in the form of logically interconnected sets of relation tables. The most important feature of the structures of this language is its focus on the final result of data processing, and not on the procedure for this processing. SQL itself determines where the data is located, the indexes, and even what the most efficient sequence of operations should be used to obtain the result.
Initially, SQL was the main way the user worked with the database and allowed the following set of operations to be performed: creating a new table in the database; adding new records to the table; changing records; deleting records; selecting records from one or more tables (in accordance with a given condition); changing table structures.
Over time, SQL provided the ability to describe and manage new stored objects (such as indexes, views, triggers, and stored procedures). SQL remains the only communication mechanism between the application software and the database. At the same time, modern DBMSs, as well as information systems that use DBMSs, provide the user with developed means of visually constructing queries. Every SQL statement is either request data from the database, or a call to the database that leads to a change in the data in the database.
In accordance with what changes occur in the database, the following types of requests are distinguished: to create or change new or existing objects in the database; to receive data; to add new data (records); to delete data; calls to the DBMS.
The main storage object of a relational database is a table, so all SQL queries are operations on tables. In accordance with this, requests are divided into:
Queries that operate on the tables themselves (creating and changing tables);
Queries that operate on individual records (or table rows) or sets of records.
Each table is described in the form of a listing of its fields (table columns) indicating: the type of values stored in each field; connections between tables (setting primary and secondary keys); information necessary to build indexes.
Thus, the use of SQL essentially comes down to generating all sorts of row selections and performing operations on all the records included in the set.
SQL commands are divided into the following groups:
1. Commands of the data definition language - DDL (Data Definition Language). These SQL commands can be used to create, modify and delete various database objects.
2. Commands of the data control language - DCL (Data Control Language). Using these SQL commands, you can control user access to the database and use specific data (tables, views, etc.).
3. Transaction control language commands - TCL (Tganstation Control Language). These SQL commands allow you to determine the outcome of a transaction.
4. Commands of the data manipulation language - DML (Data Manipulation Language). These SQL commands allow the user to move data into and out of the database.
SQL statements are divided into:
Data definition operators ( Data Definition Language, DDL)
CREATE creates a database object (the database itself, table, view, user, etc.)
ALTER changes the object
DROP removes an object
Data manipulation operators ( Data Manipulation Language, DML)
SELECT reads data that meets specified conditions
INSERT adds new data
UPDATE modifies existing data
DELETE deletes data
Data access definition operators ( Data Control Language, DCL)
GRANT grants a user (group) permissions to certain operations on an object
REVOKE revokes previously issued permissions
DENY specifies a prohibition that takes precedence over permission
Transaction control statements ( Transaction Control Language, TCL)
COMMIT applies the transaction.
ROLLBACK rolls back all changes made in the context of the current transaction.
SAVEPOINT divides a transaction into smaller sections.
Advantages: 1. Independence from a specific DBMS (SQL query texts containing DDL and DML can be quite easily transferred from one DBMS to another). 2. Availability of standards (the presence of standards and a set of tests to determine the compatibility and compliance of a specific SQL implementation with a generally accepted standard only contributes to the “stabilization” of the language). 3. Declarative (using SQL, the programmer describes only what data needs to be extracted or modified)
Flaws: 1. Inconsistency with the relational data model 2. Repeated rows 3. Nulls 4. Explicitly specifying the order of columns from left to right 5. Unnamed columns and duplicate column names 6. Lack of support for the “=” property 7. Use of pointers 8. High redundancy
2.2 Creating, changing and deleting tables.
Creating a table:
Tables are created with the CREATE TABLE command. This command creates an empty table - a table with no rows. Values are entered using the DML INSERT command. The CREATE TABLE command basically defines tables by describing a set of column names specified in a specific order. It also defines data types and column sizes. Every table must have at least one column.
Command syntax:
CREATE TABLE
(
Changing the table:
The ALTER TABLE command is a meaningful form, although its capabilities are somewhat limited. It is used to change the definition of an existing table. Typically, it adds columns to a table. It may sometimes remove or resize columns, and in some programs, add or remove constraints. The typical syntax to add a column to a table is:
ALTER TABLE
