One database every Day ----- day 2 create and manage database tables, ----- Day Database

One database every Day ----- day 2 create and manage database tables, ----- Day Database

One database every Day ----- Day 2 create and manage database tables

---- Reprinted with the source: coder-pig

This section introduces:

This section describes how to create and manage tables in a database.

The supported data types are different, so I will not elaborate on them here. If you need them, Baidu ~

1. Create the simplest table

Suppose: we define a name

T_Person table. The table has three fields: FId (person id) and FName (person name)

FAge (age of a person), data types in sequence: integer, uncertain length string type, integer

The SQL statement is as follows:

Create table T_Person (FId INTEGER, FName VARCHAR (20), FAgeINTEGER );

Although it can be written in this way, it is not recommended to write in this way, because when there are too many fields, this write will be too messy,

In addition, it is easy to write errors. In addition, when an error is reported, the error message is prompted Based on the row number. Therefore, we recommend that you

Write as follows:

Create table T_Person ( FId INTEGER, FName VARCHAR (20 ), FAge   INTEGER ) Ps: In addition, remember that the outermost layer uses "()" Parentheses instead of "{}" angle brackets !!!

2. Define non-empty Constraints

Sometimes, the values of some fields cannot be NULL. We can add not null after defining the type.

For example, if the above table is created, we set the FId to non-empty. Delete the T_Person table first!

Run the following SQL code:

Create table T_Person (FId integer not null, FName VARCHAR (20), FAge INTEGER)

The FId In the table structure is changed to a NULL value not allowed:

Of course, you can set not null for multiple fields.

3. Define the default initial value

Most of the time, when entering data into the database, some fields usually have default initial values, such as the employee table's working condition.

Most of them are active users. With the default value set, you can write less than one field ~ Here we will demonstrate setting the initial age to 18

For example, we add a simple record: 1 Tom, deliberately did not write the FAge value, after running the following code:

Create table T_Person ( FId integer not null, FName VARCHAR (20 ), FAge integer default '18' )

Query results:

4. Define the primary key

Primary Key. As mentioned earlier, the field that uniquely identifies a record is unique and non-empty !!!

We also recommend that you define a primary key for each table, because when the table is associated, the associated media is

Primary Key of the joined table! When we create a new table, set the FId as the primary key. The SQL statement is as follows:

Create table T_Person (FId integer not null, FName VARCHAR (20), FAge integer default '18 'primary key (FId )) If it is in MSSQL, you can also see a small key:

Of course, we can also verify the unique and non-empty primary key by ourselves. We only need to get a duplicate Fid or NULL value when adding data.

The result is displayed!

In addition, there is another thing called the joint primary key: that is, the combination of two or more fields replaces the primary key,

For examplePRIMARY KEY (Cid, SidBut we recommend that you use it less because it is less efficient and will make the database

The structure is poorly designed, and the problem of foreign key Association increases the development difficulty! So unless in special cases,

Otherwise, a unique primary key should be used!

5. Foreign keys for table Association

Day 1 we will introduce table Association. Here we will code it.

Create company info table T_FIRM first:

Create table T_FIRM (FId INTEGER, FName VARCHAR (20), FAddress VARCHAR (40) primary key (FId ))

Next we will go to the employee information table. Here we will use the foreign key!

Create table T_STAFF (PId INTEGER, PName VARCHAR (20), CId INTEGER, primary key (PId), foreign key (CId) REFERENCES T_FIRM (Fid )) Now, the two tables are associated with foreign keys. If you open the database relationship diagram in MSSQL, you can see:

6. Modify database tables

Maybe when we create a table at the beginning, we may not consider the week or other reasons. Now we need to set the fields in the table

To delete or add, ALTER is required at this time.

Current table structure:

Instance:

① Add a FSex field to the previously created T_Person table

Alter table T_PERSON add fsex varchar (10)

Results Before and After running SQL:

② There is nothing to do. We will remove the added FSEX field.

Alter table T_Person DROP Column FSEX

Check the running result and it is deleted!

7. delete a database table:

Use drop table directly.

For example, the following SQL statement is used to delete the T_Person table:

Drop table T_Person

In addition, the following error is reported when the following statement is used in MSSQL:

You cannot delete the table 't_person 'because it does not exist or you do not have the required permissions.

I log on using the sa account and should not have permission issues. In addition, the T_Person table also exists,

The database where the table is located was not selected when the query was created !!! Remember! The comparison before and after deletion is as follows:

Ps: When the deleted table has an associated table, you must delete it before deleting it. Otherwise, an error is reported,

For example, to delete a personnel table and a company table, you must first Delete the personnel table and then delete the company table. Otherwise, an error will be reported!

Last few words:

For more information about creating and deleting tables in a database, see here ~