Simple SQL statements for database learning and Database SQL statements

Simple SQL statements for database learning and Database SQL statements

1. Database Structure

1.1 Database

Different databases are called Catalog (Database in some DBMS ). After using multiple Catalog, you can bring us
To the following benefits:
It facilitates personalized management of each Catalog. DBMS allows us to specify whether to save different Catalog
On the same disk, because HR data is relatively less important, we can save HR on a general hard disk,
And save BIZ on the RAID hard disk. The maximum disk space and daily space that each Catalog can occupy
This allows you to customize different business data.
This avoids naming conflicts. Duplicate table names are not allowed in the same Catalog, while table names in different Catalog are not allowed.
In this way, the Persons table can be found in HR, and the Persons table can also be found in BIZ.
The structure can be completely different, and the stored data will not interfere with each other.
Higher security. DBMS allows different users to be specified for different Catalog, and allows users to access
. For example, hr123 can only access HR, while sales001 can only access BIZ. This greatly increases
Enhanced system data security.

Table 1.2

The solution to this problem is to put different types of data into different "regions". We call this region "table" (T able ).

1.2.1 column Fields

1.2.2 records

2. add, delete, and modify data

2.1 Table creation statements

(1) crea te t able T_Person (FName VARCHAR (20), FAge INT, FRemark VARCHAR (20), PRI MARY
KEY (FName ));

(2) crea te t able T_Debt (FNumber v archar (20), FAmount DECIMAL (10, 2) not null,
FPerson VARCHAR (20), primary key (FNumber ),
Foreign key (FPerson) REFERENCES T_Person (FName ));

2.2 Data insertion

2.2.1 simple INSERT statements

Insert into T_Person (FName, FAge, FRemark) VALUES ('t om ', 18, 'USA ')

Note that the field names listed before v alues correspond one to one with the field VALUES listed after VALUES.

2.2.2 simplified INSERT statements

In the INSERT statement, we do not need to specify all columns in the table. For example, when inserting data, some fields do not
With values, we can ignore these fields. Insert a data entry with no remarks:
Insert into T_Person (FAge, FName) VALUES (22, 'lxf ').

The INSERT statement has another usage. You do not need to specify the columns to INSERT. In this case
In the field order to insert, we execute the following SQL:
Insert into T_Person VALUES ('luren1', 23, 'China ').

2.2.3 Impact of non-null constraints on data insertion

As "non-empty constraint" means, if a non-empty constraint is added to a field, we cannot
Insert NULL values in fields. The F Amount field of the T_Debt table has non-null constraints. If we execute the following
SQL:
INSERTINTO T_Debt (FNumber, FPerson) v alues ('1', 'Jim ').

2.2.4 primary key impact on data insertion

The primary key must be unique in the same table. If the specified Primary Key and existing
If the data is repeated, it will cause an exception that violates the primary key constraint.

2.2.5 effect of foreign keys on data insertion

The foreign key is a constraint pointing to the existing data in another table. Therefore, the foreign key value must exist in the target table. If you insert
If the input data does not exist in the target table, the foreign key constraint is violated.

2.3 Data Update

2.3.1 simple data update

The UPDATE statement is used to UPDATE data in a data table. UPDATE T_Person SET FRemark = 'superman '.

UPDATE T_PersonSET FRemark = 'json', FAge = 25.

2.3.2 UPDATE statement with WHERE clause

UPDATE T_Person SET FAge = 12 where fname = 'Tom '.

UPDATE T_Person SET FAge = 22 WHERE FName = 'jim' OR FName = 'lxf '.

2.3.2 primary key impact on data update

The primary key must be unique in the same table. If the specified Primary Key and existing
If the data is repeated, it will cause an exception that violates the primary key constraint.

2.3.3 effect of foreign keys on data update

The foreign key is a constraint pointing to the existing data in another table. Therefore, the foreign key value must exist in the target table. If more
If the new data does not exist in the target table, the foreign key constraint is violated.

2.4 data deletion

Data in databases generally has a certain life cycle. when data is no longer needed, we need to delete it,
Execute the DELETE statement to DELETE data from the table. However, you must note that if the deleted data row is
Data referenced in foreign key associations will fail to be deleted.
You can delete the referenced object first.

2.4.1 simple data deletion

Delete from T_Debt; delete from T_Person;

However, the DELETE statement only deletes data rows in the table, while the table structure still exists. The drop t able statement not only deletes all data rows in the table, the table structure is also deleted. The DELETE statement also provides the WHERE statement for data filtering, so that only data rows that meet the filtering conditions can be deleted.

2.4.2 DELETE statement with WHERE clause

Delete from T_Person WHERE FAge> 20 or FRemark = 'mars '.

Drop table T_Debt;
Drop table T_Person;

Do I have to learn SQL statements before learning the database?

Database technology should be the core of computer technology, not only operating systems, but also data storage.
You can participate in a project on your own or perform database operations to improve the learning speed.
Currently, Foxpro is good for small desktop databases, most of which are SQL Server2000 and large Oracle databases.
We recommend that you learn SQL Server2000, which is relatively simple.

Programming is hard to say. It is simple to say. If you want to start with something, I think we should first learn [C Language]. First, we should have a concept about how to program, then you can learn the database [ASSECC] or [SQL]. Both are simple databases, after learning [HTML] and [ASP] (which can be understood to learn the two scripting languages [VBscript] and [JAVAscript]), we have laid the foundation for these languages, you can learn something difficult. Learning the object-oriented concept of C ++, when you think of the three main features of object-oriented {Derived classes and inheritance; polymorphism ;... (3rd are somewhat forgotten. It seems like encapsulation, or separation of derivation and inheritance. In short, there are three major features .....)} you can understand and use [JAVA] flexibly when programming on your own. Maybe you have heard of [JAVA], [C ++] is simple object-oriented, and [JAVA] is like a combination of [C ++] and [C Language]. [JAVA] is basically all object-oriented concepts, however, the programming structure is as follows: for example, -- loop; for judgment, it is a mechanism of C language.
After learning the above, you can take a further step. With the learning of [JAVA], you can learn [J2EE] and [ASP. NET]...
Maybe it's a bit BT to learn so many things after you see my reply. The learning sequence I told you is exactly the teaching order of a professional computer programming School, in addition, the number of out-of-office courses is only one year ...... if you are a worm in this aspect, you will do it.
The last sentence is: "Come on ~~~~!!!".

Beginner, with basic knowledge of database principles, can understand basic SQL statements. Now I want to learn SQL Server, and give some good suggestions.

Database language: Select, Delete, Isnert Into, Update. The four are the most common ones.
How to master it?
First Install SQL 2005 on the machine.
After the installation is complete, here:
Www.w3school.com.cn/ SQL /index.aspfollow this tutorial. Then let's look at the principles of SQL. It should be similar. At least the "Tree" should be known"