SQL - what it is, what the language is needed for, and basic functions for beginners. How to create a database in SQL Server environment

Introduction

This article opens a short series devoted to the basics of interaction with databases (DB) in Java And introduction to SQL . Many programs are busy processing and modifying information and keeping it up to date. Since the data is very an important part program logic, then separate storage is often allocated for them. The information in it is structured and subject to special rules to ensure correct processing and storage. Data can be accessed and changed using special language queries - SQL (Structured Query Language). Database management system- this is software that ensures the interaction of various external programs with data and additional services (logging, recovery, backup and the like), including through SQL. That is, a software layer between the data and external programs that work with it. In this part, we will answer the questions about what SQL is, what a SQL server is, and create the first program to interact with the DBMS.

Types of DBMS

There are several types of DBMS based on the way they organize data storage:

Hierarchical. The data is organized in a tree structure. Example - file system, which starts from the root of the disk and then grows with file branches different types and folders of varying degrees of nesting.
Network. A modification of hierarchical, each node can have more than one parent.
Object-oriented. Data is organized in the form of classes/objects with their attributes and principles of interaction according to OOP.
Relational. The data of this type of DBMS is organized in tables. Tables can be linked to each other, the information in them is structured.

In this series of articles, we will look at relational DBMSs (as the most common ones) using H2 as an example and create an application from scratch that emulates something similar to how an exchange works. Question: Why not PostgreSQL, MySQL, MSSQL or Oracle? Answer: So as not to be distracted by issues of installing a separate set of programs. Further configuration, creating a database, the intricacies of working in different OSes, versions. To work with H2, you need to perform a minimum of actions. But nothing prevents you from changing the current H2 JDBC to relational DBMS from another manufacturer (only the server address line and driver class name change).

SQL

External programs generate queries to the DBMS in the data management language Structured Query Language. What is SQL and how does it differ from conventional programming languages? One of the features of SQL is declarativeness. That is, SQL is a declarative language. This means that when entering commands, that is, creating queries to the SQL server, we describe what exactly we want to get, and not in what way. Sending to the server SELECT query* FROM CUSTOMER (approximate translation from SQL into Russian: “make a selection from the table COSTUMER, the selection consists of all rows of the table”), we will receive data for all users. It doesn’t matter at all how and from where the server downloads and generates the data we are interested in. The main thing is to formulate the request correctly.

What is SQL Server and how does it work? Interaction with the DBMS occurs on a client-server principle. Some external program sends a request in the form of operators and commands in SQL language, the DBMS processes it and sends a response. For simplicity, let's assume that SQL Server = DBMS.

If you know how to drive a car of the same brand, you will most likely special problems You will be able to drive others too. The basics of driving are the same everywhere, except for small details. Same for SQL servers different manufacturers- each of them has their own SQL version, but it meets the specified standards (SQL92, SQL2003...). We will use operators and commands within the SQL92 framework. Basic SQL statements are divided into the following groups:

Data Definition Language ( DDL) – data definitions. Creation of the database structure and its objects;
Data Manipulation Language( DML) – actual interaction with data: insertion, deletion, modification and reading;
Transaction Control Language ( TCL) – transaction management;
Data Control Language( DCL) – management of access rights to data and database structures.

In a series of articles we will look at the first three groups, paying particular attention to DML.

JDBC

In the 80s of the last century personal computers PC XT/AT types have conquered the market. This was largely due to the modularity of their design. This means that the user could quite easily change one or another component of his computer (processor, video card, disks, etc.). This wonderful property has been preserved to this day: we change the video card and update the driver (sometimes it even updates itself, in automatic mode). Most often, nothing bad happens with such manipulations, and existing programs will continue to work with the updated system without reinstallation. The same applies to working in Java with a DBMS. To standardize work with SQL servers, interaction with it can be performed through a single point - JDBC (Java DataBase Connectivity). It is an implementation of the package java.sql for working with DBMS. Manufacturers of all popular SQL servers release JDBC drivers for them. Consider the diagram below. The application uses instances of classes from java.sql. We then pass the necessary commands to retrieve/modify the data. Further java.sql through jdbc driver interacts with the DBMS and returns the finished result to us. To switch to a DBMS from another manufacturer, it is often enough to change JDBC and run basic settings. The remaining parts of the program do not change.

First program

Let's move on to the practical part. Let's create a Java project using IDE JetBrains IntelliJ IDEA . Please note that the editors Ultimate Edition contains a wonderful tool for working with SQL and databases - Data Grip. However, it is paid for for most users. So for educational purposes we can only use the publicly available IntelliJ IDEA Community Edition. So: Now we know how to connect to and disconnect from the DBMS. Each step is reflected in the console. When connecting to the DBMS for the first time, a database file is created stockExchange.mv.db .

Code parsing

The actual code: package sql. demo; import java. sql. *; public class StockExchangeDB ( // Constant declaration block public static final String DB_URL = "jdbc:h2:/c:/JavaPrj/SQLDemo/db/stockExchange" ; public static final String DB_Driver = "org.h2.Driver" ; public static void main (String args) ( try ( Class. forName (DB_Driver) ;//Check for the presence of a JDBC driver for working with the database Connection connection= DriverManager. getConnection(DB_URL) ; //connection to the database System. out. println( "Connection to the DBMS is complete.") ; connection. close();= DriverManager. getConnection(DB_URL) ; //connection to the database System. out. println( // disconnect from the database System. out. println( "Disconnection from the DBMS has been completed.") ;

) catch (ClassNotFoundException e) ( e. printStackTrace () ;

// error handling Class.forName"The JDBC driver for the DBMS was not found!" ) ;) catch (SQLException e) ( e. printStackTrace () ;
// error handling DriverManager.getConnection: Our database address. Consists of data separated by a colon:
Protocol=jdbc
Vendor (manufacturer/name) DBMS=h2
The location of the DBMS, in our case the path to the file (c:/JavaPrj/SQLDemo/db/stockExchange). For network DBMSs, names or IP addresses are additionally indicated here remote servers, TCP/UDP port numbers and so on.

Error processing:

Calling methods in our code may return errors that you should pay attention to. On at this stage we simply report them in the console. Note that errors when working with a DBMS are most often SQLException.

Logic of work:

Class.forName(DB_Driver) – we make sure that the appropriate JDBC driver is available (which we previously downloaded and installed).
DriverManager.getConnection(DB_URL) – establish a DBMS connection. Using the passed address, JDBC itself will determine the type and location of our DBMS and return a Connection, which we can use to communicate with the database.
connection.close()– close the connection to the DBMS and finish working with the program.

In the next part of the series, we will get acquainted with DDL operators and SQL data types, and also create the initial structure of our database and fill it with tables.

In SQL server, databases are stored in the form regular files on disk. At least one database has 2 such files: *.mdf and *.ldf. The first stores the data itself, tables, indexes, etc., and the second contains the so-called. transaction log, which contains the information necessary to restore the database.

How can I create a new database? This can be done in 2 ways:

using sql server enterprise manager
using sql language and create database statement
The first option is simple and obvious. The second one is convenient when distributing your wonderful products that use ms sql server, since it allows you to create a database without pressing different buttons.

Using sql server enterprise manager

Log in to your computer as administrator or as a login user local group administrators
Launch sql server enterprise manager.
Expand your group sql server ov. Then open your sql server. (to expand means to click on the “+” in the enterprise manager object tree).
Click on your server.
In the tools menu, select wizards.... In the window that appears, expand the database item, click on create database wizard and click ok.
A “wizard” window for creating a database will appear on the screen. Click "next"
At this step, the “wizard” will ask you for the name of the database, as well as its location on the vast expanses of your disks :) Simply put, you need to indicate the name of the database (it is better not to use Russian letters in the name of the database), as well as the location of the .mdf and .ldf files
I called it the proud name sqlstepbystep and will continue to refer to it as such.
Now you will be required to specify the initial size of the database and the name of the file for storing data. indicate the size you don't mind for experiments. imho, 5 MB will be enough. The file name is also quite arbitrary. For example, I entered sqlstep.
This step is called: “Determining the growth of the database”, i.e. you need to determine how (and whether) the size of the database will change as data is added. Two groups of options are given:
do not automatically grow the database files - prohibit automatic growth of database files
automatically grow the database file—automatic growth of database files, selected by default. Let it be.
In the case of automatic file growth, you can set the database growth in megabytes or as a percentage (the default is 10%). In addition, you can limit or not limit maximum size DB (default - unlimited). The choice of certain values depends on the purpose of the database, the intensity and nature of its use, i.e. from yours specific task. We will look at how to choose these parameters correctly (in my opinion 🙂) later, in the steps devoted to optimization issues.
We have decided on the data file parameters, now we need to decide on the log parameters. They are exactly the same. Be sure to enter a log file name that is different from the database file name, just add log to it
Click "next" and you can look at the final report. If everything is ok, click “finish” and the database will be created. You will receive a message about this, and then you will be asked to create a maintenance plan; we don’t need it now, so refuse.

Using the sql create database statement

The create database statement can repeat all of your actions described above. Actually, the enterprise manager “translated” your clicks into this statement and passed it on to the sql server. Almost all enterprise manager functions are performed exactly this way: data is requested from the user, an operator of the wonderful sql language is formed (which we will look at later) and transmitted to sql execution server, and the execution results are shown to the user.

Let's take a closer look at the syntax of the create database statement.

create database database_name (name = logical_file_name, filename = "os_file_name" [, size = size] [, maxsize = max_size] [, filegrowth = growth_increment]) ) [,...n] ] ) ) [,...n] ]

filename - full path and file name to host the database, must point to local disk the computer on which sql server is installed.
size — the initial size of each file in MB.
maxsize — maximum file size in MB; if not specified, the size is not limited.
filegrowth is a unit of file growth, indicated in MB (by default) or as a percentage (i.e., % is added to the number); a value of 0 prohibits file growth.
Now let's see what creating a database looks like:

create database sqlstepbystep on primary (name=sqlstepdata, filename="c:mssql7datasqlstep_data.mdf", size=5, maxsize=10, filegrowth=10%) log on (name=sqlsteplog, filename="c:mssql7datasqlstep_log.ldf", size=1, maxsize=5, filegrowth=1)

To test this command, run query analyzer - very useful utility, to execute sql queries. Connect to your server and type this operator in the command window. Submit a request to execute (using f5 or ctrl-e or clicking on the green arrow button). Everything will rustle and soon at the bottom, in the message window, a message will appear stating that the database was created successfully (or not).

Everything we have created can be destroyed. Deleting a database is very simple, as they say, “breaking does not build.” sql operator:

drop database<имя бд>

Typed into the query analyzer and launched for execution. Fu-yu-i-it, and the database is no longer there.
In enterprise manager, select the database, right-click and context menu select "delete" and that's it... gone.

In the next step, we will look at creating tables, data types in transact-sql.

Each of us regularly encounters and uses various databases data. When we choose an address Email, we are working with a database. Databases use search services, banks for storing customer data, etc.

But despite the constant use of databases, even for many developers software systems There are many “blank spots” left due to different interpretations of the same terms. We will give short definition basic database terms before reviewing SQL language. So.

Database - a file or collection of files for storing ordered data structures and their relationships. Very often a management system is called a database - it is only a repository of information in a specific format and can work with various DBMSs.

Table - Let's imagine a folder in which documents are stored, grouped according to a certain characteristic, for example, a list of orders for last month. This is the table in the computer Separate table has its own unique name.

Data type - the type of information allowed to be stored in a separate column or row. These can be numbers or text of a certain format.

Column and Row- we all worked with spreadsheets, which also contains rows and columns. Any relational base data works with tables in a similar way. Rows are sometimes called records.

Primary key- Each row of a table can have one or more columns to uniquely identify it. Without a primary key, it is very difficult to update, change, and delete the desired rows.

What is SQL?

SQL(English structured queries) was designed only for working with databases and in currently is a standard for all popular DBMSs. The syntax of the language consists of small amount operators and easy to learn. But, despite its apparent simplicity, it allows creating sql queries for complex operations with databases of any size.

Since 1992, there has been a generally accepted standard called ANSI SQL. It defines basic syntax and operator functions and is supported by all DBMS market leaders, such as ORACLE. It is impossible to consider all the capabilities of the language in one short article, so we will briefly consider only the main ones SQL queries. Examples clearly show the simplicity and capabilities of the language:

creating databases and tables;
data sampling;
adding records;
modification and deletion of information.

SQL Data Types

All columns in a database table store the same data type. Data types in SQL are the same as in other programming languages.

We create tables and databases

There are two ways to create new databases, tables and other queries in SQL:

SQL statements via the DBMS console
Using the interactive administration tools included with the database server.

Created new base operator data CREATE DATABASE<наименование базы данных>; . As you can see, the syntax is simple and concise.

We create tables inside the database using the CREATE TABLE statement with the following parameters:

table name
column names and data types

As an example, let's create a Commodity table with the following columns:

Create a table:

CREATE TABLE Commodity

(commodity_id CHAR(15) NOT NULL,

vendor_id CHAR(15) NOT NULL,

commodity_name CHAR(254) NULL,

commodity_price DECIMAL(8,2) NULL,

commodity_desc VARCHAR(1000) NULL);

The table consists of five columns. After the name comes the data type, the columns are separated by commas. The column value can accept empty values (NULL) or must be filled (NOT NULL), and this is determined when the table is created.

Retrieving data from a table

The data fetch operator is the most commonly used SQL query. To obtain information, we must indicate what we want to select from such a table. First a simple example:

SELECT commodity_name FROM Commodity

After the SELECT statement we specify the name of the column to obtain information, and FROM defines the table.

The result of the query will be all rows of the table with Commodity_name values in the order in which they were entered into the database, i.e. without any sorting. To order the result, use additional operator ORDER BY.

To query for several fields, list them separated by commas, as in the following example:

SELECT commodity_id, commodity_name, commodity_price FROM Commodity

It is possible to get the value of all columns of a row as a query result. To do this, use the “*” sign:

SELECT * FROM Commodity

Additionally SELECT supports:
Sorting data (ORDER BY operator)
Selection according to conditions (WHERE)
Grouping term (GROUP BY)

Add a line

To add a row to a table, SQL queries with the INSERT operator are used. Addition can be done in three ways:

add a new whole line;
part of a string;
query results.

To add a complete row, you must specify the table name and column (field) values new line. Here's an example:

INSERT INTO Commodity VALUES("106 ", "50", "Coca-Cola", "1.68", "No Alcogol ,)

Example adds to table new product. Values are listed after VALUES for each column. If there is no corresponding value for the column, then NULL must be specified. The columns are populated with values in the order specified when the table was created.

If you add only part of a row, you must explicitly specify the names of the columns, as in the example:

INSERT INTO Commodity (commodity_id, vendor_id, commodity_name)

VALUES("106 ", ‘50", "Coca-Cola",)

We entered only the identifiers of the product, supplier and its name, and left the remaining fields blank.

Adding Query Results

INSERT is primarily used to append rows, but can also be used to append the results of a SELECT statement.

Changing data

To change information in the fields of a database table, you must use the UPDATE statement. The operator can be used in two ways:

All rows in the table are updated.
Only for a specific line.

UPDATE consists of three main elements:

table in which changes need to be made;
field names and their new values;
conditions for selecting rows to change.

Let's look at an example. Let's say the price of a product with ID=106 has changed, so this line needs to be updated. We write the following operator:

UPDATE Commodity SET commodity_price = "3.2" WHERE commodity_id = "106"

We indicated the name of the table, in our case Commodity, where the update will be made, then after SET - the new value of the column and found the desired entry, specifying the desired ID value in WHERE.

To change multiple columns, the SET statement is followed by multiple column-value pairs separated by commas. Let's look at an example in which the name and price of a product are updated:

UPDATE Commodity SET commodity_name=’Fanta’, commodity_price = "3.2" WHERE commodity_id = "106"

To remove information in a column, you can assign it NULL value, if the table structure allows it. It must be remembered that NULL is precisely “no” value, and not zero in the form of text or number. Let's remove the product description:

UPDATE Commodity SET commodity_desc = NULL WHERE commodity_id = "106"

Removing rows

SQL queries to delete rows in a table are executed using the DELETE statement. There are two use cases:

Certain rows in the table are deleted;
All rows in the table are deleted.

An example of deleting one row from a table:

DELETE FROM Commodity WHERE commodity_id = "106"

After DELETE FROM we indicate the name of the table in which the rows will be deleted. The WHERE clause contains the condition by which rows will be selected for deletion. In the example, we delete the product line with ID=106. Specifying WHERE is very important because omitting this statement will delete all rows in the table. This also applies to changing the value of fields.

The DELETE statement does not specify column names or metacharacters. It deletes rows completely, but it cannot delete a single column.

Using SQL in Microsoft Access

Typically used interactively to create tables, databases, to manage, modify, analyze data in a database and to implement SQL Access queries through a convenient interactive query designer (Query Designer), using which you can build and immediately execute SQL statements of any complexity .

The server access mode is also supported, in which Access DBMS can be used as a generator of SQL queries to any ODBC data source. This feature allows Access applications interact with any format.

SQL extensions

Since SQL queries do not have all the capabilities of procedural programming languages, such as loops, branching, etc., DBMS manufacturers are developing their own version of SQL with advanced capabilities. First of all, this is support for stored procedures and standard operators procedural languages.

The most common dialects of the language:

Oracle Database - PL/SQL
Interbase, Firebird - PSQL
Microsoft SQL Server- Transact-SQL
PostgreSQL - PL/pgSQL.

SQL on the Internet

MySQL DBMS is distributed under free license GNU General Public License. There is a commercial license with the ability to develop custom modules. How component included in the most popular assemblies of Internet servers, such as XAMPP, WAMP and LAMP, and is the most popular DBMS for developing applications on the Internet.

It was developed by Sun Microsystems and is currently supported by Oracle Corporation. Databases up to 64 terabytes in size, SQL:2003 syntax standard, replication of databases and cloud services are supported.

The type of language used in a particular DBMS is called dialectSQL. For example, the dialect of the Oracle DBMS is called P.L./ SQL; MSSQLServer and DB2 use dialect Transact- SQL; in Interbase and Firebird– isql. Each SQL dialect is compatible to a certain extent with the SQL standard, but may have differences and specific language extensions, so to find out the syntax of a particular SQL statement, you should first look Help specific DBMS.

For operations on databases and tables, the sql standard provides the following operators:

The following is the SQL92 syntax for these statements. Since their syntax in the DBMS may differ from the standard, it is recommended to consult the DBMS help system when performing laboratory work.

Names of database objects (tables, columns, etc.) can consist of alphanumeric characters and the underscore character. Special characters (@$#, etc.) usually indicate a special type of table (system, temporary, etc.). It is not recommended to use national (Russian) characters, spaces and reserved words in names, but if they are still used, then such names should be written in quotation marks ".." or in square brackets [..].

Further, when describing the constructions of SQL statements, the following notation will be used: optional parts of the construction are written in square brackets; alternative designs are separated by a vertical bar | ; curly braces () highlight the logical blocks of the structure; ellipses … indicates that the previous part of the structure can be repeated many times. “Expandable” structures are written in angle brackets< >.

Database creation

CREATE DATABASE Database_name

Removing one or more databases

DROP DATABASE Database_name[,Database_name…]

Declaring the current database

USE Database_name –- in SQL Server and MySQL

SET DATABASE Name_ bases_ data – in Firebird

Creating a table

CREATE TABLE TableName (

<описание_столбца> [, <column_description> |

<table_integrity constraint>…]

< column_description> 

ColumnName TYPE

(NO ACTION|CASCADE|SET DEFAULT|SET NULL)]

TYPE The column can be either a standard data type (see Table 1) or a domain name (see section 6.2).

Some DBMSs allow you to create computed columns (computerized columns). These are virtual columns, the value of which is not stored in physical memory, but is calculated by the DBMS server whenever this column is accessed using the formula specified when declaring this column. The formula can include values from other columns in that row, constants, built-in functions, and global variables.

The description of a calculated column in SQL Server is:

<описание_столбца>  ColumnName AS expression

Description of the calculated column in Firebird has the form:

<описание_столбца>  ColumnName COMPUTED BY<expression>

The MySQL 3.23 DBMS does not support computed columns.

< > 

CONSTRAINT Integrity_constraint_name

(UNIQUE|PRIMARY KEY)( list_of_columns_forming_key)

|FOREIGN KEY ( list_ columns_FK)

REFERENCES TableName(list_columns_PK)

(NO ACTION|CASCADE|SET DEFAULT|SET NULL)]

|CHECK ( test_condition)

Some DBMSs allow the declaration of temporary tables (which exist only during a session). In SQL Server, temporary table names must begin with a # (local temporary tables visible only to the user who created them) or ## (global tables visible to all users); MySQL uses the TEMPORARY keyword to create temporary tables, for example:

CREATETEMPORARYTABLE... (for further syntax, see CREATE TABLE).

Changing the table structure

Used to change the column type of existing tables, add and remove columns and integrity constraints.

ALTER TABLE TableName

Changing a column type (in SQLServer and Firebird)

ALTER COLUMN ColumnName new_TYPE

Changing Column Type, Name, and Constraints (in MySQL)

CHANGE COLUMN ColumnName <column_description>

Add a regular or calculated column

|ADD<column_description>

Adding an Integrity Constraint

| ADD

<table_integrity constraint>

Delete a column

|DROP COLUMN ColumnName

Removing an Integrity Constraint

|DROP CONSTRAINT Integrity_constraint_name

Enable or disable integrity constraint checking

BMSSQLServer

|(CHECK|NO CHECK) CONSTRAINT

{List_of_integrity_constraint_names|ALL)

Deleting a table

DROP TABLE TableName



Next, we’ll look at how to declare declarative integrity constraints when creating new tables with the CREATETABLE command or changing the structure of existing tables with the ALTERTABLE command (they are described in more detail in section 4.2).

1. Mandatory availability of data (NULL values)

Declared by the word NULL (the column may have empty cells) or NOT NULL (the column is required). The default is NULL.

Example of creating table 7:

CREATETABLE Clients(

ClientName NVARCHAR(60) NOT NULL,

DateOfBirth DATE NULL,

Phone CHAR(12)); -- also NULL by default

2. Default value (DEFAULT)

A default value can be set for each table column. If a cell is modified and its new value is not specified, the server inserts a default value. The default value can be NULL, a constant, a evaluated expression, or a system function.

Let's look at an example of creating a table Orders(Orders). Column OrderDate defaults to the current date and the column Quantity(quantity) defaults to 0.

CREATE TABLE Orders(

OrderNum INT NOT NULL, -- Order number

OrderDate DATETIME NOT NULL-- order date

DEFAULT GetDate()

The GetDate() function returns the current date 8

Quantity SMALLINT NOT NULL-- quantity of goods, DEFAULT 0);

3. Declaration of primary keys (PRIMARYKEY)

A simple primary key is declared with the words PRIMARYKEY when creating a table. For example,

CREATETABLE Staff( -- table "Employees"

TabNum INTPRIMARYKEY, -- primary key

WName NVARCHAR(40) NOTNULL, -- FULL NAME

... -- description of other columns);

A composite primary key is declared differently:

-- method 1 (adPKwhen creating a table)

CREATE TABLE Clients(

PasSeria NUMERIC(4,0)NOT NULL,-- series passports

PasNumber NUMERIC(6,0)NOT NULL,-- number passports

Name NVARCHAR(40)NOT NULL,

Phone CHAR(12),

-- declaration of a composite primary key

CONSTRAINT Clients_PK

PRIMARY KEY(PasSeria,PasNumber));

-- method 2(PKdeclared after table creation)

-- first create a table withoutPK

CREATE TABLE Clients(

PasSeria NUMERIC(4,0)NOT NULL,--series passports

PasNumber NUMERIC(6,0)NOT NULL,--number passports

ClientName NVARCHAR(40)NOT NULL,

Phone CHAR(12));

-- modification tables – add RK

ALTER TABLE Clients

ADD CONSTRAINT Clients_PK

PRIMARY KEY(PasSeria,PasNumber);

4. Uniqueness of columns (UNIQUE)

Like Primary Key indicates that a column or group of columns cannot contain duplicate values, but is not PK. All columns declared UNIQUE must be NOTNULL. An example of a simple unique column declaration:

CREATE TABLE Students(

SCode INT PRIMARY KEY, -- surrogate RK

FIO NVARCHAR(40) NOT NULL, -- FULL NAME

RecordBook CHAR(6) NOT NULL UNIQUE); -- Record number

Example of a compound unique field declaration:

CREATE TABLE Staff(-- table " Workers"

TabNum INT PRIMARY KEY, -- time sheet number

WName NVARCHAR(40) NOT NULL, -- Full name

PasSeria NUMERIC(4,0) NOT NULL, -- series passports

PasNumber NUMERIC(6,0) NOT NULL, -- number passports

-- announcement composite unique fields

CONSTRAINT Staff_UNQ UNIQUE(PasSeria,PasNumber));

5. Restrictions on values column (CHECK)

This constraint allows you to specify a range, list, or "mask" of logically valid values for a column.

Example of creating a table Workers(Workers):

CREATETABLE Workers(

-- 4-digit personnel numbers

TabNum INT PRIMARY KEY

CHECK(TabNum BETWEEN 1000 AND 9999),

Name VARCHAR(60) NOT NULL, -- Full name employee

-- floor – letter " m" or " and"

Gentry CHAR(1) NOT NULL

CHECK(Gentry IN("m","f")),

Age at least 14 years

Age SMALLINT NOT NULL CHECK(Age>=14),

--No. of pension insurance certificate (by mask)

PensionCert CHAR(14)

CHECK(PensionSert LIKE ""));

This example shows different types of checks. The range of acceptable values is indicated by the BETWEEN…AND construct; normal conditions (as for column Age) use comparison signs =,<>, >, >=, <, <=, связанные при необходимости логическими операциямиAND,OR,NOT(например,Age>=14AND Age<=70); для указания списка допустимых значений используется предикатINи его отрицаниеNOTIN; конструкция

LIKE mask_allowed_values EXCEPT exception_list

used to set a mask of valid values for string columns. The mask uses two special characters: “%” – an arbitrary substring, and “_” – any single character. The EXCEPT clause is optional.

The CHECK selection condition can compare the values of two columns of the same table and columns of different tables.

1 vote

Welcome to my blog site. Today we’ll talk about sql queries for beginners. Some webmasters may have a question. Why learn sql? Isn't it possible to get by?

It turns out that this will not be enough to create a professional Internet project. Sql is used to work with databases and create applications for WordPress. Let's look at how to use queries in more detail.

What it is

Sql is a structured query language. Designed to determine the type of data, provide access to it and process information in short periods of time. It describes the components or some results that you want to see on the Internet project.

To put it simply, this programming language allows you to add, change, search and display information in the database. The popularity of mysql is due to the fact that it is used to create dynamic Internet projects that are based on a database. Therefore, to develop a functional blog, you need to learn this language.

What can it do

The sql language allows you to:

create tables;
change to receive and store various data;
combine information into blocks;
protect data;
create requests in access.

Important! Once you understand sql, you can write applications for WordPress of any complexity.

What structure

The database consists of tables that can be presented as an Excel file.

It has a name, columns and a row with some information. You can create such tables using sql queries.

What you need to know

Key Points to Learn Sql

As noted above, queries are used to process and enter new information into a database consisting of tables. Each line is a separate entry. So, let's create a database. To do this, write the command:

Create database 'bazaname'

We write the database name in Latin in quotes. Try to come up with a clear name for it. Do not create a database like “111”, “www” and the like.

After creating the database, install:

SET NAMES 'utf-8'

This is necessary for the content on the site to be displayed correctly.

Now let's create a table:

CREATE TABLE 'bazaname' . 'table' (

id INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,

log VARCHAR(10),

pass VARCHAR(10),

date DATE

In the second line we wrote three attributes. Let's see what they mean:

The NOT NULL attribute means that the cell will not be empty (the field is required);
The AUTO_INCREMENT value is auto-completion;
PRIMARY KEY - primary key.

How to add information

To fill the fields of the created table with values, the INSERT statement is used. We write the following lines of code:

INSERT INTO 'table'

(login, pass, date) VALUES

('Vasa', '87654321', '2017-06-21 18:38:44');

In brackets we indicate the names of the columns, and in the next - the values.

Important! Maintain consistency in column names and values.

How to update information

To do this, use the UPDATE command. Let's see how to change the password for a specific user. We write the following lines of code:

UPDATE 'table' SET pass = '12345678' WHERE id = '1'

Now change the password ‘12345678’. Changes occur in the line with “id”=1. If you do not write the WHERE command, all lines will change, not a specific one.

I recommend that you purchase the book " SQL for dummies " With its help, you can work professionally with the database step by step. All information is structured according to the principle from simple to complex, and will be well perceived.

How to delete an entry

If you wrote something wrong, correct it using the DELETE command. Works the same as UPDATE. We write the following code:

DELETE FROM 'table' WHERE id = '1'

Sampling information

To retrieve values from the database, use the SELECT command. We write the following code:

SELECT * FROM 'table' WHERE id = '1'

In this example, we select all available fields in the table. This happens if you enter an asterisk “*” in the command. If you need to select some sample value, write this:

SELECT log , pass FROM table WHERE id = '1'

It should be noted that the ability to work with databases will not be enough. To create a professional Internet project, you will have to learn how to add data from a database to pages. To do this, familiarize yourself with the PHP web programming language. It will help you with this cool course by Mikhail Rusakov .

Deleting a table

Occurs using a DROP request. To do this, we will write the following lines:

DROP TABLE table;

Displaying a record from a table based on a specific condition

Consider this code:

SELECT id, countri, city FROM table WHERE people>150000000

It will display records of countries with a population of more than one hundred and fifty million.

An association

It is possible to link several tables together using Join. See how it works in more detail in this video:

PHP and MySQL

Once again I want to emphasize that requests when creating an Internet project are commonplace. To use them in PHP documents, follow the following algorithm:

Connect to the database using the mysql_connect() command;
Using mysql_select_db() we select the desired database;
We process the request using mysql_fetch_array();
Close the connection with the mysql_close() command.

Important! Working with a database is not difficult. The main thing is to write the request correctly.

Beginner webmasters will think about it. What should you read on this topic? I would like to recommend Martin Graber's book " SQL for mere mortals " It is written in such a way that beginners will understand everything. Use it as a reference book.

But this is a theory. How does this work in practice? In reality, an Internet project must not only be created, but also brought to the TOP of Google and Yandex. The video course will help you with this “ Website creation and promotion ».

Video instruction

Still have questions? Watch the online video for more details.

Conclusion

So, figuring out how to write sql queries is not as difficult as it seems, but any webmaster needs to do this. The video courses described above will help with this. Subscribe to my VKontakte group to be the first to know when new interesting information appears.