Course Module 4. Working with databases - Lecture: Working with SQL Server

SQL is our everything

As you probably already guess, all commands to the SQL server can be given through SQL queries. Everything.

These teams are officially divided into 4 groups:

data definition statements (Data Definition Language, DDL ):
- CREATE creates a database object (database itself, table, view, user, and so on)
- ALTER changes an object
- DROP removes an object
data manipulation operators (Data Manipulation Language, DML ):
- SELECT selects data that satisfies given conditions
- INSERT adds new data
- UPDATE changes existing data
- DELETE removes data
data access definition operators (Data Control Language, DCL ):
- GRANT grants a user (group) permissions to perform certain operations on an object
- REVOKE revokes previously issued permissions
- DENY sets a ban that takes precedence over a permit
Transaction Control Language ( TCL ) statements :
- COMMIT applies a transaction
- ROLLBACK rolls back all changes made in the context of the current transaction
- SAVEPOINT divides a transaction into smaller sections

And the first two levels, we studied only varieties of the SELECT statement. Imagine how many interesting things await us in the future.

But we are preparing here primarily about Java programmers, so we will study those scenarios that you will definitely encounter at work.

The system administrator on the project will most likely create all the databases, but you will definitely have to make a selection from the data yourself many times.

Moreover, sometimes your code will not write all the data to the database or write it in the wrong way, so you will often have to climb into it with pens and see what is actually stored there.

Let's go over the things we touched on in previous lectures again.

Creating a schema in a database

To create a new schema in the DBMS, you need to run the command:

CREATE SCHEMA Name;

This is the easiest option. Also, when creating a new schema, you can specify the data encoding format and other parameters.

If you want to delete the schema, but you are not sure if it exists, then you need to run the command:

DROP SCHEMA IF EXIST Name;

You will often see these commands in files with backups of different databases, that's why I bring them here.

Selecting the current scheme

If you have a lot of schemas in your DBMS, then it can easily happen that different schemas have the same tables. To avoid confusion, you can do two things:

Always put the schema name before the table name
Specify default schema

Let's write a query that will select data from the user table , which is in the test schema . It will look something like this:

SELECT * FROM test.user;

This is simply indispensable if you need to join (JOIN) several tables from different schemas in one query.

By the way, in the Java language we often do something similar: if in the code we need to use classes with the same name from different packages, we add the package name before the class name.

The second way is to specify the default schema . If the query specifies a table name but no schema, the default schema is used. To do this, use the USE statement :

USE name - schemes;

Let's rewrite the previous query using the USE statement:

USE test;
SELECT * FROM user;

Creating a View

In addition to tables with real data, SQL allows you to store something like virtual tables, where data is pulled from real tables. Such virtual tables are called VIEW.

Such a table cannot store real data, and each time it is accessed, it pulls data from real tables. The content of such a VIEW is specified using an SQL query.

You can create a VIEW from any SELECT query with a command like:

CREATE VIEW Name AS
SELECT-request;

Let's write a query that will create a public_employee virtual table based on the employee table, where employee salary information will be hidden:

CREATE VIEW public_employee AS
SELECT id, name FROM employee

In the above example, our table (VIEW) public_employee will only contain employee IDs and their names, but not information about their salary. You can use such Views in the same place as real tables.

Why are Views needed? They have a number of advantages:

Flexible control of access to information . You can give certain users access only to VIEW, but not to give access to tables. And in View, take out only public information from tables. In addition, if new columns with important information are added to the tables in the future, it will not accidentally get into the View.

Data denormalization . For convenience of storage, data is often divided into hundreds and thousands of tables, but it is not very convenient for an ordinary person to work with such data - you have to write too complex queries. With View, you can create virtual tables that display data from dozens of different tables in a single table.

Polymorphism and Encapsulation . You can change the structures of your database. At the same time, users of the program who work with your Views will not guess that something has changed. And there will be no need to rewrite the code of programs that have access to View. You will just need to tweak the SQL script that is related to VIEW.

Read only . View can only be set with a SELECT query, so working with View cannot change the data in real tables in any way. By the way, this is another plus in favor of query caching. But more on that next time.