The basics of the SQL query language are the SELECT statement. SQL commands for transaction management. "Zeroing" to the required data

25.07.2019 Errors

Hello dear reader! With this entry I will open new heading on my blog, in which I will publish posts and not even just posts, but video posts. The rubric will be called SQL and Relational Databases and will publish video tutorials on SQL technology and relational database theory, of course, in video format. I myself do not like the word course, because I believe that courses are taught at universities, but the realities of Runet are such that this word is used very often, and I will sometimes use it too.

My video course is a set of video screencasts on the topic of SQL and databases, the SQLite library was used as a DBMS. Screencasts are divided into topics, topics, in turn, are divided into parts, each part is separate video a 5-15 minute lesson in which we will deal with database theory or SQL commands and queries. But I will not pull the cat by the tail and will immediately give you a playlist link on YouTube: SQL and relational databases... The videos in the playlist are arranged in the order in which I would recommend watching them. And do not forget to subscribe to my channel, it will be even more interesting and more!

Who will benefit from the video tutorials from the SQL course and relational databases for beginners?

These video tutorials will be useful for novice web developers and SQL developers. To study them, in principle, you do not need any specific knowledge, it is enough to be confident user computer, be able to type on the keyboard and it will be absolutely cool if you have written any program in any programming language at least once in your life, even if it is Helloe World in BASIC.

I note that these video tutorials will be useful not only for beginners of SQL, but also for more advanced users who have a certain set of knowledge on SQL and databases, but this set of knowledge is not systematized and does not allow you to go to independent work... Although we start with simple things that I try to tell in an accessible and intelligible way, we end up with complex SQL queries, although who am I scaring? :)) There are no complex SQL queries, there are large queries and queries that consume a lot of resources.

How are these video tutorials built?

For the basis of these video lessons, publications from my blog from the heading were taken, these video lessons have a similar structure, but still there are slight differences. Below you will find the topics that this course is broken down into and short description what I'm trying to tell you about.

SQLite basics and features of this DBMS?

In the first topic, we will talk about the features of the SQLite DBMS, let's see where they use this library and for what purposes, as well as try to install and configure SQLite on a computer running operating system Windows 10. The publications from the first topic were taken as a basis:.

Trying to write SQL queries and work with a SQLite database

In this topic, we will try to write several SQL queries, get acquainted with the syntax of the SQL language implemented in SQLite (in principle, the SQL syntax in different DBMS is very, very similar) and get acquainted with useful system commands, which are in SQLite, which will help us work with databases .. html

Relational Database Theory

One of the basic topics of the entire video course. I will refer to and rely on this topic constantly in all subsequent ones. Here we will get acquainted with the basics of databases and learn how to design the architecture of our databases, we will get acquainted with such seemingly complex terms as normalization, data redundancy, anomalies and others and give them a human explanation ...

SQL Query Language

Another fundamental topic of my video tutorials on SQL and relational databases. Here we are introduced to the SQL language, its basic concepts, structure and concept. We also understand the terminology used by SQL developers and draw a parallel between the SQL language and the theory of relational databases, find differences and similarities, so to speak ... html

Data types in SQLite

Throughout the video course, I use the SQLite DBMS, which is a special DBMS, with its own typing features, I also want to note that the DBMS, in fact, manages data, so the topic of data typing is, in principle, important and can be difficult for beginner SQL developer, at least for me, when I got acquainted with databases, the most difficult moment was typing, because there was a lot to remember ... html

SQL data definition commands (DML commands)

Here we will begin to work closely with the SQL language and get acquainted with SQL commands data definitions that allow you to work with database objects: create, delete, and modify them. Database objects include: the database itself, a table in the database, VIEW, indexes, triggers, etc. Video tutorials on this topic are devoted to SQL syntax commands CREATE, DROP, ALTER and their use..html

SQL Data Manipulation Commands (DML Commands)

This video tutorial topic will introduce you to the syntax (writing rules) of SQL data manipulation commands (DML commands), these commands include: INSERT, DELETE, UPDATE, SELECT. Accordingly, the commands allow you to: insert and add rows to tables, delete rows from tables, modify data in tables and make data selection ... html

SQL commands for transaction management

This section of the video course SQL and relational databases is devoted to the rules for writing commands that allow you to manage transactions, I will not open the spoiler, just say that these commands are cleverly called TCL, and it is also worth adding that we will deal with transactions in more detail in one of the latest topics .. html

Working with database tables

This video tutorial topic gives a deeper knowledge and understanding of how SQL allows you to work with database tables, what features should be taken into account and what we can do with a database table using SQL..html

Ensuring data integrity

This topic of my video tutorials SQL well demonstrates the application of relational database theory in practice in the SQL language. Here we will learn how to set various restrictions in order to secure your data! We'll also look at using keys or key attributes in practice and learn how to do different kinds relationships between tables..html

SQL triggers using an example of a SQLite database

SQL table indexes using SQLite databases as an example

2 Comments on Video course and free lessons on SQL and relational databases for beginners and not only

Hello! Where is the video ??? Where are the video tutorials then ??? Rummaged the entire site, all the links, the entire sitemap ...

Please enable JavaScript to view the

I present to your attention a free translation of the article SQL for Beginners

More modern web applications interact with databases, usually using the language SQL... Luckily for us, this language is pretty easy to learn. In this article, we will start to learn the basics of SQL queries and how they interact with the database. MySQL.

What you need

SQL ( structured language queries) is a language developed to interact with relational database management systems (DBMS), such as MySQL, Oracle, Sqlite and others. To execute the SQL queries from this article, I assume you have installed MySQL... I also recommend using phpMyAdmin as a visual display tool for MySQL.

The following applications will make it easy to install MySQL and phpMyAdmin to your computer:

WAMP for Windows
MAMP for Mac

Let's start executing queries in command line. WAMP already contains it in the console MySQL... For MAMP, you might need to read this.

CREATE DATABASE: Create Database

Our very first request. We will create a database to work with.

First of all, open the console MySQL and login. For WAMP, by default, a blank password is used. For MAMP the password must be "root".

After logging in, type this request and click Enter:

CREATE DATABASE my_first_db;

Note that a semicolon (;) is added at the end of the query, just like at the end of a line in your code.

Also, keywords CREATE DATABASE case insensitive like all keywords in SQL... But we will write them in uppercase for better readability.

Note: character set and collation

If you want to set the default character set and collation order, use a similar query:

CREATE DATABASE my_first_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

You will find a list of supported character sets and collations at MySQL.

SHOW DATABASES: List of all databases

This query is used to display all databases.

DROP DATABASE: Drops a database

With this query, you can remove existing base data.

Be careful with this query because it does not display any warnings. Once you have tables and data in your database, the query will delete all of them in an instant.

WITH technical point view is not a request. It is an "operator" and does not require a semicolon at the end.

He informs MySQL that you need to select the default database and work with it until the end of the session. We are now ready to create tables and the rest in this database.

What is a database table?

You can think of a table in a database as a regular table or as a csv file that has structured data.

As in this example, the table has row names and data columns. Using SQL queries we can create this table. We can also add, read, modify and delete data.

CREATE TABLE: Create table

With this query, we can create a table in the database. Sorry, the documentation for MySQL not very friendly to new users. The structure of this query can be very complex, but we'll start with a simple one.

The following query creates a table with two columns.

CREATE TABLE users (username VARCHAR (20), create_date DATE);

Please note that we can write a query on multiple lines and use Tab for indentation.

The first line is simple. We create a table named users... Further, in brackets, the columns of the table are listed, separated by commas. Each column name is followed by a data type, for example VARCHAR or DATE.

VARCHAR (20) means column string type and can be no more than 20 characters in length. DATE- data type intended for storing dates in the format: "YYYY-MM-DD".

Primary key

Before executing this query, we must insert a column user_id, which will be the PRIMARY KEY. Without going into too much detail, you can think of a primary key as a way to recognize each row of data in a table.

The request becomes like this:

CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR (20), create_date DATE);

INT- 32-bit integer type (numeric). AUTO_INCREMENT automatically creates new number id every time a row of data is added. It is not necessary, but it is more convenient with it.

This column may not be an integer, although this is the most common data type. The primary key column is optional, but it is recommended to use it to improve the performance and architecture of your database.

Let's run the query:

SHOW TABLES: List of all tables

The query allows you to get a list of all tables in the current database.

EXPLAIN: Show table structure

Use this query to see the structure of an existing table.

As a result, the fields (columns) and their properties are shown.

DROP TABLE: Drops a table

As well DROP DATABASES, this query deletes the table and its contents without any warning.

ALTER TABLE: Modify a table

Such a query can be complex because it can make multiple changes to a table. Let's take a look at some simple examples.

Thanks to readability SQL, this request is self-explanatory.

Removing is just as easy. Use the query with caution, data is deleted without warning.

Let's re-add the field email, later it will still be needed:

ALTER TABLE users ADD email VARCHAR (100) AFTER username;

Sometimes you may need to change the properties of a column, you don't have to delete it and create it again.

This request renames the field username v user_name and changes its type from VARCHAR (20) on the VARCHAR (30)... Such changes do not affect the data in the table.

INSERT: Adding data to a table

Let's add records to the table using queries.

As you can see, VALUES () contains a comma-separated list of values. String values are enclosed in single quotes... The values must be in the order specified when the table was created.

Note that the first value is NULL for the primary key, the field of which we named user_id... All because the field is marked as AUTO_INCREMENT and id is generated automatically. The first row of data will have an id of 1. The next row to be added is 2, and so on.

Alternative syntax

Here is another syntax for inserting strings.

This time we used keyword SET instead of VALUES... Let's note a few things:

The column can be omitted. For example, we did not assign a value to the field user_id because it is marked as AUTO_INCREMENT... If you do not assign a value to a field with the type VARCHAR, then by default it will take the value of an empty string (unless another default value was specified when creating the table).
Each column can be referenced by name. Therefore, the fields can be in any order, unlike the previous syntax.

Alternative syntax number 2

Here's another example.

As before, you can refer to the fields by name, they can go in any order.

Use this query to get the id of the last inserted row.

NOW ()

It's time to show you how to use functions MySQL in requests.

Function NOW () returns current date... Use it for auto add current date in a field of type DATE.

Please note that we have received a warning from MySQL but this is not so important. The reason is that the function NOW () actually returns time information.

We created a field create_date which can only contain a date and not a time, so the data has been truncated. Instead of NOW () we could use CURDATE () which only returns the current date, but the end result would be the same.

SELECT: Retrieving data from a table

Obviously, the data we have written is useless until we can read it. A request comes to the rescue SELECT.

The simplest example of using a request SELECT to read data from a table:

The asterisk (*) means we want to get all the columns in the table. If you only need to get certain columns, use something like this:

More often than not, we only want to get certain rows, not all. For example, let's get E-mail address user nettuts.

It is similar to an IF condition. WHERE allows you to set a condition in a query and get the desired result.

The single sign (=) is used for the equality condition, not the double (==), which you probably use in programming.

You can also use other conditions:

AND and OR are used to combine conditions:

Note, numerical values do not need to be quoted.

IN ()

Used to compare against multiple values.

LIKE

Allows you to set a template for the search.

The percent sign (%) is used to specify the pattern.

ORDER BY clause

Use this condition if you want the result to be returned sorted:

The default order is ASC(Ascending). Add DESC to sort in reverse order.

LIMIT ... OFFSET ...

You can limit the number of rows returned.

LIMIT 2 takes the first two lines. LIMIT 1 OFFSET 2 takes one line after the first two. LIMIT 2, 1 means the same thing, only the first number is the offset, and the second one limits the number of lines.

UPDATE: Updating data in a table

This query is used to update data in a table.

In most cases used in conjunction with WHERE to update specific lines. If the condition WHERE not specified, the changes will be applied to all rows.

To restrict mutable strings, can be used LIMIT.

DELETE: Deleting data from a table

As well , this query is often used in conjunction with the condition WHERE.

TRUNCATE TABLE

To remove content from a table, use a query like this:

DELETE FROM users;

For better performance use .

Also resets the field counter AUTO_INCREMENT, so newly added rows will have id equal to 1. When using this will not happen and the counter will continue to grow.

Escaping string values and special words

String values

Some characters need to be escaped or there may be problems.

The backslash (\) is used for escaping.

This is very important for security reasons. Any user data must be escaped before being written to the database. V PHP use mysql_real_escape_string () function or prepared statements.

Special words

Since in MySQL many reserved words like SELECT or To avoid confusion, enclose column and table names in quotation marks. And you need to use not regular quotes, and inverse (`).

Let's say, for some reason, you want to add a column named :

Conclusion

Thanks for reading the article. I hope I was able to show you that language SQL very functional and easy to learn.

Welcome to standard language database development SQL queries... Database management systems (DBMS) have many tools that run on a wide variety of hardware platforms.

Relational Database Basics

SQL Basics

SQL components

In this chapter ... | Database Creation | Data processing | Database protection | SQL is a language specially designed to create and maintain data in relational databases. While companies that provide systems for managing such databases offer their own implementations of SQL, the development of the language itself is defined and controlled by the ISO / ANSI standard.

Creating and maintaining a simple database

In this chapter ... | Create, modify and delete a table from the database using the RAD tool. | Create, modify and delete a table from the database using SQL. | Transferring a database to another DBMS.

Creating a multi-table relational database

Manipulating data from the database

Definition of values

In this chapter ... | Using Variables to Reduce Coding Redundancy | Retrieving Frequently Requested Information Found in a Database Table Field | Combination simple values to create compound expressions | This book continually emphasizes how important the structure of the database is to maintaining the integrity of the database.

Complex expressions with meaning

In this chapter ... | Usage conditional expressions case | Converting an Item from One Data Type to Another | Saving Data Entry Time Using Record Expressions | In Chapter 2, SQL was called data sublanguage.

"Zeroing" to the required data

Relational operators

In this chapter ... | Concatenating tables with similar structure | Concatenation of tables that have different structure| Retrieving the Data You Need from Multiple Tables | SQL is a query language used in relational databases.

Using nested queries

In this chapter ... | Retrieving Data from Multiple Tables with a Single SQL Statement | Finding data items by comparing a value from one table with a set of values from another | Find items by comparing a value from one table with a single value selected using a select statement from another

Martin Graber "SQL for mere mortals" Laurie, 2014, 382 pp. (11.2 MB pdf)

The book can be described as a beginner's guide. Structured Query Language - SQL, a programming language for creating and managing relational databases (applied, logical model building a set (databases) of data). The book is designed for the simplest (lowest) level of training in the IT field, that is, there is enough knowledge in the volume school curriculum... But this does not mean that the material in the manual is only an introduction to this programming language - no, SQL is described quite deeply (author's statement).

Each chapter adds new data describing related concepts and definitions. All subsequent material is based on the previous one - discussed earlier, with a consideration at the end of the chapter practical issues for better assimilation of the acquired knowledge. You will find the answers in Appendix A.

An introduction to SQL is provided in the first seven chapters, which are must-read if you are using the manual like SQL is for a beginner. The next seven (8 through 14) chapters cover more complex examples: combined queries, queries to several tables at once. Other SQL capabilities: creating and editing tables, entering and setting values, opening and closing access to the created tables - described in chapters 15 to 23. In conclusion about the structure of databases and the possibility of using SQL in programs developed in other languages. The appendices provide guidance on SQL commands and job responses. The book is ideal for beginners to learn SQL.
ISBN: 978-5-85582-301-1

Chapter 1. An introduction to relational databases 1
What is a relational database? 3
Database example 5
Outcome 7

Chapter 2. Introduction to SQL 9
How does SQL work? 10
Various data types 12
Outcomes 15

Chapter 3. Using SQL for fetching data from tables 17
Forming a request 18
Sample Definition - WHERE Clause 24
Results 26

Chapter 4. Using relational and boolean operators to create more complex predicates 29
Relational Operators 30
Boolean Operators 32
Results 37

Chapter 5. Using special operators in "conditions" 39
Operator IN 40
Operator BETWEEN 41
LIKE operator 44
IS NULL Operator 47
Results 49

Chapter 6. Summarizing Data Using Aggregation Function 51
What are aggregation functions? 52
Results 61

Chapter 7. Formatting query results 63
Strings and Expressions 64
Ordering Output Fields 67
Results 71

Chapter 8. Using multiple tables in one query 75
Joining tables 76
Results 81

Chapter 9. A join operation whose operands are represented by a single table 83
How the operation of joining two copies of the same table is performed 84
Results 90

Chapter 10. Nesting queries 93
How are subqueries executed? 94
Results 105

Chapter 11. Related subqueries 107
How to Build Linked Subqueries 108
Results 115

Chapter 12. Using the EXISTS operator 117
How does the EXISTS operator work? 118
Using EXISTS with Related Subqueries
Results 124

Chapter 13. Using the ANY, ALL, and SOME Operators 127
Special operator ANY or SOME 128
Special operator ALL 135
Functioning ANY. ALL and EXISTS on data loss or
with unknown data 139
Results 143

Chapter 14. Using the UNION clause 145
Combining multiple queries into one 146
Using UNION with ORDER BY 151
Results 157

Chapter 15. Entering, Deleting, and Changing Zero Values 159
DML 160 update commands
Entering values 160
Excluding rows from table 162
Changing Field Values 163
Results 165

Chapter 16. Using Subqueries with Update Commands 167
Using Subqueries in INSERT 168
Using Subqueries with DELETE 170
Using Subqueries with UPDATE 174
Results 177

Chapter 17. Creating tables 178
CREATE TABLE Command 179
Indexes 181
Modifying a Table That Has Already Been Created 182
Eliminate Table 183
Results 185

Chapter 18. Set constraints acceptable values data 186
Limits in Tables 195
Results 197

Chapter 19. Data integrity support 198
Foreign and parent keys 199
FOREIGN KEY Constraints 204
What Happens When You Execute Update Command 209
Results 211

Chapter 20. Introduction to views 212
What are views? 212
CREATE VIEW Command 221
Results 223

Chapter 21. Modifying Values Using Views 224
Updating views 228
Selecting Values Placed in Views 232
Results 235

Chapter 22. Defining data access rights 236
Users 237
Transfer of Privileges 241
Deprivation of privileges 245
Other types of privileges 247
Results 249

Chapter 23. Global aspects of SQL 250
Renaming tables 252
How is the database hosted for the user? 253
When does change become permanent? 255
How SQL Works with Multiple Users Concurrently Summary 259

Chapter 24. How order is maintained in a SQL database 261
System Catalog 262

Speaking simple language, sql-queries are needed in order to enter and process information in the database.

The base consists of many tables. Each line is one record. For example, simple table for users:

To start working with sql queries, you first need to.

Consider the most simple queries for beginners.

Creating a database - CREATE DATABASE

CREATE DATABASE `mybase`

In quotes we indicate the name of our base (quotes are optional, but easier to work with).

Setting encoding - SET NAMES

SET NAMES "utf-8"

Setting the encoding often helps to avoid "krakozyabr".

Creating a table - CREATE TABLE

Let's create the table that was presented above.

CREATE TABLE `mybase`.`users` (` id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `login` VARCHAR (20),` password` VARCHAR (20), `regdate` DATE)

It's not all that complicated here. Here we write that we are creating a table named "users" in the "mybase" database.

`column name` data type (number of max. values) attributes

Attributes are optional.

For example, here we have created a column named “regdate” with data type “DATE”.

`regdate` DATE

Here, in parentheses, the maximum permissible value is indicated. Data type - character

`login` VARCHAR (20),

When creating the "id" column, we wrote the attributes, consider them:

UNSIGNED - Only positive numbers;
NOT NULL - The cell cannot be empty (mandatory filling);
AUTO_INCREMENT - Automatic filling fields, starting from 0 and +1, when creating a row;
PRIMARY KEY - The field values cannot be repeated in the given cell column, makes the column the primary key;

"Id" has an integer data type.

Adding information to the database - INSERT

INSERT INTO `users` (login`,` password`, `regdate`) VALUES (" Vasya "," 12345 "," 2015-04-22 17:38:50 ")

In the first brackets we write the name of the columns, in the second their meaning. It is important that the sequence of values is followed with the sequence of the column names.

You do not need to fill in the "id" field, it is created automatically.

Updating information - UPDATE

Now let's look at how to update data in a row in a table. For example, let's change the password for a specific user.

UPDATE `users` SET` password` = "54321" WHERE `id` =" 1 "

Change the value of the "password" field to a new one in the line with "id" equal to 1.

If you remove "WHERE" but all lines will change, not a specific line.

Deleting information - DELETE

Now let's delete this line, with all its fields.

DELETE FROM `users` WHERE` id` = "1"

Works the same as add.

Fetching information from the database SELECT

To work with information in the database, you need to select it.

SELECT * FROM `users` WHERE` id` = "1"

Here we have selected all the rows in the "users" table (* - all fields).

And you can only select specific fields.

SELECT `login`,` password` FROM `users` WHERE` id` = "1"