preamble
Finally waited for this day, I want to start to re-learn the database system, about the database this piece, no surprise, every day will be updated regularly and will not contain too much content, short content, in-depth understanding.
SQL statement categories
SQL statements include the following three categories
（1）Data Definition Language (Data Definnition Language) or DDLThe first thing we have to do is to create tables, so it includes CREATE, ALTER and DROP tables.
（2）Data Manipulation Language (Data Manipulation Language) i.e. DML, what we need to operate on the data, of course, is nothing more than adding, deleting, changing and checking, so it includes SELECT, INSERT, UPDATE, DELETE, which also includes TRUNCATE, MERGE.
（3）Data Control Language (Data Control Language) i.e. DCLWhen we manipulate the database, we grant different privileges to different users.
database paradigm
Paradigm is what the thing, it means the normalization of rules, a little easier to understand is the definition of the norms, rules, we need to comply with, then why set this set of rules? We think the other way around, must have been encountered before, if not defined this set of rules, this or that problem, in order to avoid the emergence of such a problem is out of this set of rules, mainly to solve the following two problems.
(1) Avoid anomalies in the data modification process.
(2) Maintain minimum data redundancy.
The most basic paradigms of database paradigms are the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), and there are higher level paradigms, but they are too complex for us to explore, and most of the books say so, and that's the way we're going to understand them.
First Paradigm (1NF)
Definition: rows in a relational table must be unique and attributes are atomic.
Too obscure, too abstract, not quite understand, let's analyze one by one, let's look at the above definition focuses on the line [unique], attributes [atomicity].
So what exactly is considered line unique?
First: since it is unique, an identifier in the row must be known and not unknown i.e. not null
Second: unique, which means that it cannot be duplicated.
Third: How do you ensure that rows are unique? Unique rows are achieved by defining a unique key.
So what exactly is atomicity?
The first see the word atom is not immediately associated with China's independent development of the outbreak of atomic bombs, or on the chemistry class encountered the first chemical formula 2H2 + O2 = 2H2O, 2 hydrogen that is, 4 hydrogen atoms and 1 oxygen that is, 2 oxygen atoms set to generate a water molecule, oh, recall that the molecule is made up of atoms, atoms are made up of the atomic nucleus and electrons outside of the nucleus, the atomic nucleus consists of protons and neutrons, and what quarks and so on, both in programming languages and databases have been talking about atomicity, why not talk about the proton and quarks? Neutrons, and what quarks and so on, both the programming language or database have been talking about atomicity, why did not speak of proton and quarks, because the atom has been considered relatively small, so has been used to emphasize the atomicity and division, in order to facilitate the understanding of this can be so thinking, the latter do not need to be in the narrative to here do not understand it is equivalent to the atomic composition of the molecule, will be the atom as the The smallest granularity that can not be divided, then we in turn think of the atomicity of the attribute that the attribute can not be re-divided, which is what it means, such as in the table there is an address attribute, if we store such as (Hunan Province, Yueyang City, Huarong County) so that it violates the first paradigm, the attribute can still be re-divided into provinces, cities, counties.
By this point we can make the conditions that summarize the first paradigm fulfillment:
(1) Uniquely identified keys
(2) The key cannot be empty
(3) Keys cannot be duplicated
(4) Attributes not subdividable
Second Paradigm (2NF)
Definition: subject to the first paradigm, every non-key attribute must satisfy full functional dependence on the entire candidate key i.e., a non-key attribute cannot be a full functional dependence on a part of the candidate key.
Well, let's continue into the above explanation of the first paradigm to explain the obscure definition of the second paradigm. Let's look at the following table
The candidate keys defined above are all primary keys. Both OrderId and ProductId in the table given above are used as candidate keys, i.e., primary keys, but at this point we can use some of the candidate keys (primary keys) such as OrderId to get columns such as OrderDate, CustomerId, and CompanyName, etc., which is a partial dependency on OderId rather than a complete dependency on both OderId and ProductId. ProductId. At this point, in order to show the complete dependence on the candidate key should be divided into the following two tables.
So a generalization of the above definition would be that an attribute should have a full dependency on the primary key and not a partial dependency, otherwise the second paradigm is violated.
Third Paradigm (3NF)
Similarly the third paradigm is viewed in the context of satisfying the first and second paradigms.
Definition: all non-key attributes must be dependent on non-passing candidate keys, i.e. non-key attributes must be independent of each other, furthermore non-key attributes cannot form dependencies with each other.
Let's look at the above modifications to meet the second paradigm of the two tables, this time the order table OrderId as the primary key, the customer Id that is CustomerId and the company name that is CompanyName on the OrderId is completely dependent on the OrderId, we can get through the OrderId to the CustomerId, you can get through the OrderId to the CompanyName, at the same time, we can get through the CustomerId to the customer company name, that is to say, CustomerId and CompanyName is a pass-through relationship rather than independent of each other. At the same time, we can also get the customer company name through CustomerId, that is, at this time CustomerId and CompanyName is a kind of passing relationship, rather than independent of each other. At this point, if we need to meet the third paradigm should be expressed as follows:
We can see that the third paradigm focuses on the independence between non-key attributes and non-key attributes, while the second paradigm focuses on the complete dependency of non-key attributes on the candidate primary key. So the second paradigm and the third paradigm we uniformly generalize that non-key attributes must be dependent on the key, not on each other, and on the whole key.
System database composition
When you open the database in the database under the default there will be a system database, the contents of which are as follows:
master
The master database stores instance-wide metadata information, server configuration, all database information in the instance, and initialization information.
Resource
The Resource database is a hidden, read-only database that stores the definitions of all system objects.
model
The model database is a template for creating new databases, and each new database created is initialized with a copy of the model created.
tempdb
The tempdb database is where SQL Server stores temporary data such as worksheets, sort spaces, row version control information. Also SQL Server allows us to create temporary tables for our own use and the location of these temporary tables is tempdb, but we need to be aware that whenever the SQL Server instance is restarted, this database will be destroyed and created by the model copy.
msdb
The msdb database is where the SQL Server agent's services store data. The SQL Server agent is responsible for automating operations, including jobs, schedules, and alerts, as well as replication services and more.
summarize
In this section we focused on the composition of SQL statements and the three paradigms of the database, a short introduction to the composition of the system database, belongs to the category of understanding, right, we are here today, we will meet again in the next section.
The above is the entire content of this article, I hope that the content of this article on your learning or work can bring some help, if there are questions you can leave a message to exchange, but also hope that more support me!