Any conceptual information about relational databases and tables is only useful if you know how to interact with the data. The SQL language consists of structured commands for adding, modifying, and deleting data from a database. These commands form the basis for interacting with the PostgreSQL server.

This section is about the "anatomy" of SQL commands. It examines the structural components of commands, describing the functions of each component and their mutual relationships. The standard PostgreSQL command line client, psql, prints the results of the commands in the examples given.

Most of the SQL command examples are specific to the booktown database. All psql output is prefixed with the form booktown=# .

Some examples use the testdb test database. By default, the psql prompt displays only the database name and =# ​​symbols, indicating that the system is ready to issue a new command (although you will see the = symbol dynamically change as the state of the SQL input is monitored). The book provides this prompt along with the SQL input and output to help you get comfortable with the psql client output.

The psql client is described in detail in Chapter 4. It is mentioned here only to clarify the style of the SQL command examples.

Note
The booktown database schema (along with example records) is located in the booktown.sql file on the CD. To install this database, enter the command psql - U postgres template! at the command prompt. - f /mnt/cdrom/booktown.sql, where /mnt/cdrom is the path to the mounted CD and postgres is the PostgreSQL superuser name.

Anatomy of SQL Commands

SQL commands always begin with an action (verb) - a word or group of words that describes the operation being performed. In addition, an SQL command usually contains one or more sections that clarify its meaning. In table 3.2 lists the main actions of SQL.

Table 3.2. Basic PostgreSQL Actions.

Action Description

CREATE DATABASE	Creating a new database
CREATE INDEX	Creating a new index on a table column
CREATE SEQUENCE	Creating a new sequence in an existing database
CREATE TABLE	Creating a new table in an existing database
CREATE TRIGGER	Create a new trigger definition
CREATE VIEW	Create a new view for an existing table
SELECT	Selecting records from a table
INSERT	Inserting one or more new records into a table
UPDATE	Modifying data in existing records
DELETE	Removing existing records from a table
DROP DATABASE	Destroying an existing database
DROP INDEX	Removing a column index from an existing table
DROP SEQUENCE	Destroying an existing sequence generator
DROP TABLE	Destroying an existing table
DROP TRIGGER	Destroying an existing trigger definition
DROP VIEW	Destroying an existing view
CREATE USER	Creating a new PostgreSQL user account in the system
ALTER USER	Modifying an existing PostgreSQL user account
DROP USER	Deleting an existing PostgreSQL user account
GRANT	Granting access rights to a database object
REVOKE	Removing access rights to a database object
CREATE FUNCTION	Creating a new SQL function in a database
CREATE LANGUAGE	Creating a new language definition in the database
CREATE OPERATOR	Creating a new SQL statement in the database
CREATE TYPE	Creating a new SQL data type in a database

Structured Query Language was developed by IBM in the early 1970s. In 1986, SQL was first standardized by the ANSI organizations.

SQL is a powerful and at the same time simple language for database management. It is supported by almost all modern databases. SQL is divided into two subsets of commands: DDL (Data Definition Language) and DML (Data Manipulation Language). DDL commands are used to create new databases, tables and columns, and DML commands are used to read, write, sort, filter, delete data.

This article will take a closer look at DML commands, since they are used much more often.

DDL Commands

CREATE - used to create new tables, columns and indexes.

DROP - used to remove columns and indexes.

ALTER - used to add new columns to tables and change specific columns.

DML Commands

SELECT is the most commonly used command and is used to retrieve a set of data from a database table. The SELECT command has the following syntax:

SELECT field_list1 FROM table_name ]

Operators inside square brackets are optional, and the vertical bar means that one of the specified phrases must be present, but not both.

For example, let's create a simple query to obtain data from the "name" and "phone" fields of the "friends" table:

SELECT name, phone FROM friends

If you need to get all the fields of the table, then it is not necessary to list them, just put an asterisk (*):

SELECT * FROM friends

To exclude duplicate entries from the displayed list, use the DISTINCT keyword:

SELECT DISTINCT name FROM friends

If you need to get a separate record, then the WHERE clause is used. For example, we need to get the phone number of “Vasya Pupkin” from the “friends” table:

SELECT * FROM friends WHERE name = "Vasya Pupkin"

or vice versa, we need to find out who owns the phone 44-65-01:

SELECT * FROM friends WHERE phone = "44-65-01"

In addition, you can use wildcard characters, thereby creating search patterns. The LIKE operator is used for this. The LIKE operator has the following substitution operators:

* - matches a string consisting of one or more characters;

Matches any one character;

Matches one character from a specific set;

For example, to retrieve records from the "name" field containing the word "Vasya", the request would look like this:

SELECT * FROM friends WHERE name LIKE "*Vasya*"

The ORDER BY clause is used to determine the order in which data is returned. Without this operator, the order of the returned data cannot be predicted. The keywords ASC and DESC allow you to determine the direction of sorting. ASC sorts in ascending order, and DESC orders in descending order.

For example, a request to get a list of records from the "name" field in alphabetical order would look like this:

SELECT * FROM friends ORDER BY name

Note that the ASC keyword is not required because it is the default.

INSERT - this command is used to add a new record to the table. It is written as follows:

INSERT INTO table_name VALUES ( value_list)

Please note that the value types in the list of values ​​must match the value types of the table fields, for example:

INSERT INTO friends VALUES ( "Anka the Machine Gunner", "32-09-81" )

This example adds a new entry to the friends table with the specified values.

UPDATE - This command is used to update data in a table and is most often used in conjunction with the WHERE clause. The UPDATE command has the following syntax:

UPDATE table_name SET field_name = meaning

If you omit the WHERE clause, the data in all defined fields in the table will be updated. For example, let’s change Vasya Pupkun’s phone number:

UPDATE friends SET phone = "55-55-55" WHERE name = "Vasya Pupkin"

DELETE - as you probably already understood, this command is used to delete records from the table. Like UPDATE, the DELETE command is usually used with a WHERE clause; if this clause is omitted, all data from the specified table will be deleted. The DELETE command syntax is as follows:

DELETE FROM table_name

For example, let's remove this annoying Vasya Pupkin from our table :) :

DELETE FROM friends WHERE name = "Vasya Pupkin"

The end

In this article I talked about the main SQL operators, they are quite sufficient for working with databases; I will talk about the remaining operators in more detail another time.

SQL or Structured Query Language is a language used to manage data in a relational database system (RDBMS). This article will cover commonly used SQL commands that every programmer should be familiar with. This material is ideal for those who want to brush up on their knowledge of SQL before a job interview. To do this, look at the examples given in the article and remember that you studied databases in pairs.

Note that some database systems require a semicolon at the end of each statement. The semicolon is the standard pointer to the end of every statement in SQL. The examples use MySQL, so a semicolon is required.

Setting up a database for examples

Create a database to demonstrate how teams work. To work, you will need to download two files: DLL.sql and InsertStatements.sql. After that, open a terminal and log into the MySQL console using the following command (the article assumes that MySQL is already installed on the system):

Mysql -u root -p

Then enter your password.

Run the following command. Let's call the database “university”:

CREATE DATABASE university; USE university; SOURCE; SOURCE Commands for working with databases1. View available databases SHOW DATABASES; 2. Creating a new database CREATE DATABASE; 3. Selecting a database to use USE; 4. Import SQL commands from the .sql file SOURCE ; 5. Removing the database DROP DATABASE ; Working with tables6. View the tables available in the SHOW TABLES database;

7. Creating a new table CREATE TABLE ( , , PRIMARY KEY (), FOREIGN KEY () REFERENCES ()); Integrity Constraints When Using CREATE TABLE

You may need to create restrictions on certain columns in a table. When creating a table, you can set the following restrictions:

• a table cell cannot have a NULL value;
• primary key - PRIMARY KEY (col_name1, col_name2, ...) ;
• foreign key - FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn) .

You can specify more than one primary key. In this case, you will get a composite primary key.

Example

Create a table "instructor":

CREATE TABLE instructor (ID CHAR(5), name VARCHAR(20) NOT NULL, dept_name VARCHAR(20), salary NUMERIC(8,2), PRIMARY KEY (ID), FOREIGN KEY (dept_name) REFERENCES department(dept_name));

8. Table information

You can view various information (value type, key or not) about table columns with the following command:

DESCRIBE ;

9. Adding data to the table INSERT INTO (, , , …) VALUES (, , , …);

When you add data to each column in a table, you do not need to specify column names.

INSERT INTO VALUES (, , , ...);

10. Updating table data UPDATE SET = , = , ... WHERE ; 11. Deleting all data from the table DELETE FROM ; 12. Removing a table DROP TABLE ; Commands for creating queries13. SELECT

SELECT is used to retrieve data from a specific table:

SELECT , , ... FROM ;

The following command can display all the data from the table:

SELECT * FROM ;

14. SELECT DISTINCT

Table columns may contain duplicate data. Use SELECT DISTINCT to retrieve only non-duplicate data.

SELECT DISTINCT , , … FROM ;

15. WHERE

You can use the WHERE keyword in SELECT to specify conditions in a query:

SELECT , , ... FROM WHERE ;

The following conditions can be specified in the request:

• text comparison;
• comparison of numerical values;
• logical operators AND (and), OR (or) and NOT (negation).

Example

Try the following commands. Pay attention to the conditions specified in WHERE:

SELECT * FROM course WHERE dept_name=’Comp. Sci.'; SELECT * FROM course WHERE credits>3; SELECT * FROM course WHERE dept_name="Comp. Sci." AND credits>3;

16. GROUP BY

The GROUP BY operator is often used with aggregate functions such as COUNT, MAX, MIN, SUM, and AVG to group output values.

SELECT , , … FROM GROUP BY ;

Example

Let's display the number of courses for each faculty:

SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name;

17. HAVING

The HAVING keyword was added to SQL because WHERE cannot be used with aggregate functions.

SELECT , , ... FROM GROUP BY HAVING

Example

Let's display a list of faculties that have more than one course:

SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name HAVING COUNT(course_id)>1;

18. ORDER BY

ORDER BY is used to sort query results in descending or ascending order. ORDER BY will sort in ascending order unless ASC or DESC is specified.

SELECT , , … FROM ORDER BY , , … ASC|DESC;

Example

Let's display a list of courses in ascending and descending order of credits:

SELECT * FROM course ORDER BY credits; SELECT * FROM course ORDER BY credits DESC;

19. BETWEEN

BETWEEN is used to select data values ​​from a specific range. Numeric and text values, as well as dates, can be used.

SELECT , , … FROM WHERE BETWEEN AND ;

Example

Let's display a list of instructors whose salary is more than 50,000, but less than 100,000:

SELECT * FROM instructor WHERE salary BETWEEN 50000 AND 100000;

20. LIKE

The LIKE operator is used in WHERE to specify a search pattern for a similar value.

There are two free operators that are used in LIKE:

• % (none, one or more characters);
• _ (one character).

SELECT , , ... FROM WHERE LIKE ; Example

Let's display a list of courses whose names contain "to" and a list of courses whose names begin with "CS-":

SELECT * FROM course WHERE title LIKE ‘%to%’; SELECT * FROM course WHERE course_id LIKE "CS-___";

21. IN

Using IN you can specify multiple values ​​for the WHERE clause:

SELECT , , … FROM WHERE IN (, , …);

Example

Let's display a list of students from Comp majors. Sci., Physics and Elec. Eng.:

SELECT * FROM student WHERE dept_name IN ('Comp. Sci.', 'Physics', 'Elec. Eng.');

22. JOIN

JOIN is used to link two or more tables using common attributes within them. The image below shows the different ways to join in SQL. Note the difference between a left outer join and a right outer join:

SELECT , , … FROM JOIN ON = ;

Example 1

We will display a list of all courses and relevant information about the faculties:

SELECT * FROM course JOIN department ON course.dept_name=department.dept_name;

Example 2

We will display a list of all required courses and details about them:

SELECT prereq.course_id, title, dept_name, credits, prereq_id FROM prereq LEFT OUTER JOIN course ON prereq.course_id=course.course_id;

Example 3

We will display a list of all courses, regardless of whether they are required or not:

SELECT course.course_id, title, dept_name, credits, prereq_id FROM prereq RIGHT OUTER JOIN course ON prereq.course_id=course.course_id;

23. View

View is a virtual SQL table created as a result of executing an expression. It contains rows and columns and is very similar to a regular SQL table. View always shows the latest information from the database.

Creating CREATE VIEW AS SELECT , , ... FROM WHERE ; Removing DROP VIEW ; Example

Let's create a view consisting of courses with 3 credits:

24. Aggregate functions

These functions are used to obtain an aggregate result related to the data in question. The following are commonly used aggregate functions:

• COUNT (col_name) - returns the number of rows;
• SUM (col_name) - returns the sum of the values ​​in this column;
• AVG (col_name) - returns the average value of a given column;
• MIN (col_name) - returns the smallest value of a given column;
• MAX (col_name) - Returns the largest value of a given column.

25. Nested subqueries

Nested subqueries are SQL queries that include SELECT , FROM , and WHERE clauses nested within another query.

Example

Let's find courses that were taught in the fall of 2009 and spring of 2010:

SELECT DISTINCT course_id FROM section WHERE semester = 'Fall' AND year= 2009 AND course_id IN (SELECT course_id FROM section WHERE semester = 'Spring' AND year= 2010);

Kirill Pozdeev, translator

How Yandex uses your data and machine learning to personalize services -.

SQL (ˈɛsˈkjuˈɛl; English structured query language - “structured query language”) is a declarative programming language used to create, modify and manage data in a relational database.

Compliance with SQL standards for different databases:

SQL (Structured Query Language - structured query language). SQL is primarily an information logical language designed to describe stored data, to retrieve stored data, and to modify data.

SQL is not a programming language. As SQL has become more complex, it has become more of an application programming language, and users are now able to use visual query builders.

SQL is a case-insensitive language. Strings in SQL are enclosed in single quotes.

The SQL language is a collection of statements. SQL statements are divided into:

Data Definition Language (DDL) - schema description language in ANSI, consists of commands that create objects (tables, indexes, views, etc.) in the database (CREATE, DROP, ALTER, etc.).

Data manipulation operators (Data Manipulation Language, DML) are a set of commands that determine what values ​​are presented in tables at any time (INSERT, DELETE, SELECT, UPDATE, etc.).

data access definition statements (Data Control Language, DCL) - consists of tools that determine whether to allow the user to perform certain actions or not (GRANT/REVOKE, LOCK/UNLOCK).

Transaction Control Language (TCL) operators

Unfortunately, these terms are not used universally across all implementations. They are emphasized by ANSI and are useful on a conceptual level, but most SQL programs do little to treat them separately, so they essentially become functional categories of SQL commands.

SELECT section JOIN

Simple JOIN (=intersection JOIN =INNER JOIN) - means to show only the common records of both tables. How records are considered shared is determined by the fields in the join expression. For example, the following entry: FROM t1 JOIN t2 ON t1. id = t2. id

means that records with the same id that exist in both tables will be shown.

LEFT JOIN (or LEFT OUTER JOIN) means to show all records from the left table (the one that comes first in the join expression) regardless of whether there are corresponding records in the right table. If there are no records in the right table, the empty value NULL is set.

RIGHT JOIN (or RIGHT OUTER JOIN) acts in contrast to LEFT JOIN - it shows all records from the right (second) table and only those that match from the left (first) table.

Other types of JOIN joins: MINUS - subtraction; FULL JOIN - complete join; CROSS JOIN - “Everyone with everyone” or the Cartesian product operation.

SELECT JOIN SUBSTRING INSERT INTO users_auth VALUES (default, "root" , MD5("root"));

INSERT IGNORE If you specify the IGNORE keyword in an INSERT command with rows that have multiple values, then any rows that have duplicate PRIMARY or UNIQUE keys in that table will be ignored and not inserted. If you do not specify IGNORE, this insert operation aborts when it encounters a row that has a duplicate value for an existing key

INSERT IGNORE INTO prices VALUES(DEFAULT, 1, 1111, "Fr", 50, 50, 1, 1)

The REPLACE command differs from INSERT only in that if there is a record in the table with the same value in the indexed field (unique index) as the new record, then the old record is deleted before adding the new one.

UPDATE< tablename>SET ( | ) .,. .< COLUMN name> = < VALUE expresslon>[WHERE< predlcate>| WHERE CURRENT OF< cursor name>(*only for attachment*) ] ; UPDATE peers SET zone= "voip" ; # update all rows in the zone column of the peers table UPDATE stat SET whp= "13x13x13" WHERE id = 1 ; UPDATE countries SET nm_ukr= ( SELECT del_countries. ukrainian FROM del_countries WHERE countries. nm_en= del_countries. english ) ;

WordPress usage, setting: in the wp_posts table, delete all occurrences of the row

UPDATE wp_posts SET post_content = REPLACE (post_content, "" , "" ) ;

DELETE FROM

[ WHERE | WHERE CURRENT OF (*attachment only*) ];

DELETE FROM Peers; // will delete all contents of the Peers table. DELETE FROM FinR where day Like "20120415%"; // DELETE FROM prices WHERE ratesheet_id NOT IN (SELECT id FROM ratesheets);

ALTER

Changing the default value for a column. To set a new default value for a column, use a command like: ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77 ; OR ALTER TABLE nases ALTER COLUMN zone SET DEFAULT "voip" ;

Note that running this command does not affect existing rows in the table; the command will only change the default value for future INSERT commands. To remove any default value, use

ALTER TABLE products ALTER COLUMN price DROP DEFAULT ;

The command does the same thing as setting the default value to null. Since deleting a default value implicitly sets it to null, no errors will be reported if an existing default value is deleted.

Function as the default value for the column. In this case, the timetracking column has a timestamp data type, which means that its default value can be set to the built-in now() function, i.e. when adding a new row to the column, the current date and time will be recorded ALTER TABLE timetracking ALTER COLUMN date_wd SET DEFAULT now();

Adding a constraint. To add a constraint, the table syntax for defining that constraint is used. For example: ALTER TABLE products ADD CHECK (name ""); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

To add a non-null constraint that cannot be written as a table constraint, use the syntax:

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

The specified constraint will be enforced immediately, so the data in the table must satisfy it before adding the constraint.

Aggregation Functions

There are 5 aggregate functions in standard SQL:

COUNT - the function returns the number of rows that meet certain criteria.,

SUM - returns the sum (total) of the values ​​in a specific column. Column rows with NULL values ​​are ignored by the SUM function.

AVG - average value in the column,

Aggregation functions are used as field names in a SELECT query clause, with one exception: field names are used as arguments. The SUM and AVG functions can only work with numeric fields. The COUNT, MAX, MIN functions work with both numeric and character fields. When applied to character fields, the MAX and MIN functions can work with the ASCII equivalents of the characters.

SELECT Count(Books.ID) AS [Number of Books] FROM Books;

Using CROUP BY allows you to apply aggregate functions to groups of records.

SELECT Count(Books.ID) AS [Number of Books] FROM Books GROUP BY [Writer];

Views (VIEW)

A VIEW is a data object that does not contain any data about its owner. It is a type of table whose contents are retrieved from other tables by running a query.

Base tables are tables that contain data. However, there is another type of table: - views (VIEW). Views are tables whose content is selected or obtained from other tables. They work in queries and DML statements just like main tables, but do not contain any data of their own. Views are like windows through which you view information that is actually stored in the underlying table.

CREATE VIEW command. The view is created with the CREATE VIEW command. It consists of the words CREATE VIEW, the name of the view that needs to be created, the word AS (HOW), and then the query. Let's create the Londonstaff view: CREATE VIEW Londonstaff AS SELECT * FROM Salespeople WHERE city = "London";

This view is used just like any other table. It can be queried, modified, inserted into, deleted from, and joined to other tables and views. Request for submission.

This appendix contains abbreviated descriptions of various SQL commands. The goal is to give you quick and accurate reference and definition of SQL.

The first section of this application defines the elements used to create SQL commands; the second is details of the syntax and sentences with a brief description of the commands themselves.

Here are the standard conventions (called BNF conditions):

• Keywords are typed in uppercase.
• SQL and other special conditions are enclosed in angle brackets and typed in italics.()
• Optional parts of commands are enclosed in square brackets ([ and ]).
• An ellipsis (....) indicates that the preceding part of the command can be repeated any number of times.
• A vertical bar (|) means that what precedes it can be replaced by what follows it.
• Curly Brackets (( and )) indicate that everything inside them must be treated as a whole in order to evaluate other characters (such as vertical bars or ellipses).
• The double colon and equal sign (:: =) mean that what follows is a definition of what precedes it.

  Additionally, we will use the following sequence (.,..) to indicate that what precedes it can be repeated any number of times, with individual events separated by commas. Attributes that are not part of the official standard will be marked in the description as (*non-standard*).

  PLEASE NOTE: The terminology we use here is not official ANSI terminology. The official terminology can be very confusing, so we have simplified it somewhat. For this reason, we sometimes use conditions that differ from ANSI, or use the same conditions but in a slightly different way. For example, our definition of C differs from the ANSI combination of the standard definition of C. SQL ELEMENTS

  This section defines the elements of SQL commands.

  They are divided into two categories: Basic elements of language and Functional elements of language.

  Basic elements are created blocks of language; When SQL examines a command, it first evaluates each character in the command text in terms of those elements. A separator separates one part of a command from another; everything between delimiters is treated as a module. Based on this division, SQL interprets the command.

  Functional elements are a variety of elements other than keywords that can be interpreted as modules. These are parts of a command, separated by delimiters, that have a special meaning in SQL. Some of them are special to certain commands and will be described along with those commands later in this appendix.

  The items listed here are common to all of the commands described.

  Functional elements can be defined in terms of each other or even in their own terms. For example, the predicate Our last and most complex case contains a predicate Inside its own definition. This is because a predicate using AND or OR can contain any number of predicates that can operate independently. We have presented predicate B to you in a separate section in this appendix because of the variety and complexity of this functional element of the language. He will be constantly present when discussing other functional parts of the teams.

  BASIC ELEMENTS OF LANGUAGE

  ELEMENT DEFINITION | | -- space implementation-defined end of character string [( | = B AND

  
  The world of free programs and useful tips
  2024 whatsappss.ru