Follow me to learn sql: (v) Create and modify tables

Create again to welcome you to the basic series of SQL (Structured Query Language) tutorials. This article describes the instructions that database definition language (DDL) uses to create databases and tables, and to modify the results of a table.


Be careful when you use these instructions-it's easy to delete the main structure in your database so that you lose data. So, before you start modifying the database, you need to know what the database is.


--------------------------------------------------------------------------------

Differences between databases
The sample query system in this article follows the SQL92 ISO standard. Not all databases follow this standard, and some databases are improved, which can produce unpredictable results. If you are unsure whether your database supports the standard, please refer to the documentation.


--------------------------------------------------------------------------------

Creating a Database
To create a table, you first need to create a database that can hold the table. The basic statements that SQL uses to create a database are:
CREATE DATABASE dbname;

Your database users must have the appropriate permissions to establish the database. If the user associated with you cannot issue a command to create a new database, ask the database administrator to set up a database for you, and you can log in as an administrator and set up a database and permissions.

For example, use the Create command to create a database for an application to display a directory:
CREATE DATABASE Catalog;

This gives you a table name that you can use to distinguish it from other tables in the query. The next step is to create a table for entering it.

Create a table
As you know, the form is composed of several columns. When creating a table, you can define columns and assign field properties. After the table is established, you can modify it with the ALTER TABLE instruction, which we will mention later.

You can use the following instruction to create a database, the command line parameter is the table name, column name, and each column data type.
CREATE TABLE table_name
(Column1 data_type, Column2 data_type, Column3 data_type);

The criteria for different database providers vary widely. There should be a section in your help document detailing how to use each data and what parameters to accept. For general purposes, I have listed some common data types in table A.

Table A

Data type
Usage
Detailed description

Char
Char (8)
It contains a fixed-length string whose value is often the string length.

Varchar
Varchar (128)
It contains a variable length string that is no longer than the specified value.

Int
Int (32)
This is a bit less than the specified worthy integer, also doing number or integer.

Decimal
Decimal (12,2)
This is a total number of digits and the number of digits after the decimal point is not greater than the specified decimal value, also known as numeric or numbers.

Binary
Binary
Used to store binary objects, which are generally not decomposed and displayed in the database, also known as raw or BLOB.

Boolean
Boolean
Used to just be true or false, and also to be bit or byte.


Common data types


In this example, we set up a table that stores information about inventory items. The columns and data types used are as shown in table B:

Table B

Column Name:
prod_id
Prod_color
Prod_descr
Prod_size

Data type:
Int (16)
Varchar (20)
Varchar (255)
Decimal (8,2)




In this case, I've used three basic data types, but in actual use, I might also use the tinyint, text, and mediumtext data types, depending on what the database supports.

The following instructions are issued to create the form:
CREATE TABLE Products
(prod_id INT (), Prod_color VARCHAR (), PROD_DESCR VARCHAR (255), Prod_size DECIMAL (8,2));

If these instructions are successfully completed, you can insert the information normally in the table. You can participate in the article SQL base one: Data query "get detailed description."


In addition to the data type, you can also define AutoIncrement fields (auto-incremented field), keywords, indexes, and special numeric restrictions when creating tables. These parameters are passed along with the data type when the table is defined. If you define an AutoIncrement prod_id with special numeric restrictions when you create the table product, the commands are as follows:
CREATE TABLE Products
(prod_id INT () auto_increment, Prod_color VARCHAR (), PROD_DESCR VARCHAR (255), Prod_size DECIMAL (8,2), UNIQUE (' Prod_ ID '));

If you have prod_id as an indexed field definition, you can use the CREATE INDEX:
CREATE INDEX Prodindex on Product (prod_id);

It is important to reiterate here that the database provider differs in the processing of keywords. So please refer to your database provider's documentation for details.


--------------------------------------------------------------------------------

More about the index:

Indexing is a relatively deep topic. In addition to introducing theories about keywords and indexing, Builder.com's contributor Eric Roland has written a few good articles that you can use to learn more about the relevant knowledge.

Modify Table

When you start working on a table, you may find it necessary to modify the structure of the table, the type of field, and so on. In the front, I strongly recommend that you avoid doing so in the production environment (production environment). Because some operations, such as adding, deleting, and modifying fields, may delete or destroy data in related fields.

OK, now let's see how to modify the table. First, add a column to the table product. You can specify where the column is inserted relative to the other columns, or you can insert it at the end of the table (default):
ALTER TABLE Product ADD prod_name VARCHAR after prod_id;

To delete a column with a similar statement:
ALTER TABLE Product DROP prod_size;

Finally, change the data type of a column:
ALTER TABLE Product Change Prod_color prod_color_id INT (20);

Now, your form is shown in Table C:

Table C

Column Name:
prod_id
Prod_name
prod_color_id
Prod_descr

Data type:
Int (16)
Varchar (20)
Int (20)
Varchar (255))



Note that some databases do not support keyword drop. In addition, if you change the data type of an existing column, most databases attempt to transform the data type of the column's existing data. However, if you switch to an unsupported data type, the data is likely to be lost. For example, if you change a field with a varchar name to an int type, the result of the conversion might be the default value for the integral type.

Delete tables and databases
Before you delete tables and databases, you need to make sure that the data is lost without any adverse consequences. If you delete the database, all tables and content in the library are cleared. If you delete a table, everything in the table will be lost, but the other tables in the library have no effect.

Before you delete a table or whole column, you must know the structure of the database. If you go into an existing database and mistakenly delete an element, it may affect the trigger condition (?). Trigger), stored procedures, and views. Some databases support the use of keyword restrict and cascade to prevent losses caused by deleting tables. Restrict generally defaults to prevent the loss of the form, and cascade is used to delete the entity associated with the form.

Now that the form created above can be deleted, we begin to delete it:
DROP TABLE Product;

Now delete the database:
DROP DATABASE Catalog;


Most database software providers support the drop Database command, although it is defined in the SQL99 standard rather than SQL92.

Some databases provide a flush command that allows you to delete the contents of a table but also to keep the results of the table:
FLUSH TABLE Product;

As you can see, it's hard to imagine how easy it is to delete the main structure in the database and lose all of the data you've stored, so be careful to use these commands, and don't use them when you don't know what's in the database.
Database management
In the previous article, you learned how to look up data in one or more tables. Now, you've learned how to manipulate your database structure. You've learned to create, modify, and destroy tables and data. These are the operations that must be used to design a database-driven application.