Quick Start for SQL language (ii)

SQL language | QuickStart CREATE table

The CREATE TABLE statement in the SQL language is used to create a new database table. The use format for the Create table statement is as follows:

CREATE TABLE TableName

(Column1 data type,

Column2 data type,

Column3 data type);

You can use the optional conditional option if the user wants to specify the column restrictions when creating a new table:

CREATE TABLE TableName

(Column1 data type [constraint],

Column2 data type [constraint],

COLUMN3 data type [constraint]);

Examples are as follows:

CREATE TABLE Employee

(FirstName varchar (15),

LastName varchar (20),

Age Number (3),

Address varchar (30),

City varchar (20));

In short, when creating a new table, add the name of the table you want to create after the keyword CREATE TABLE, and then set the column names, data types, and optional restrictions in parentheses. Note that all SQL statements use the ";" Symbol at the end.

The names of the columns in the database tables and tables that you create using SQL statements must begin with a letter, followed by letters, numbers, or underscores, and the name cannot exceed 30 characters in length. Note that users do not use reserved keywords in the SQL language when selecting table names, such as SELECT, Create, insert, and so on, as table or column names.

A data type is used to set the type of data in a specific column. For example, you can only use the data type of varchar or char in the Name column, not the data type of number.

The more commonly used data types in SQL languages are:

char (size): A fixed-length string in which the size in parentheses is used to set the maximum length of the string. The maximum length of a char type is 255 bytes.

varchar (size): A variable-length string with a maximum length set by size.

Number (size): The numeric type in which the maximum number of digits is set by size.

Date: Day type.

Number (SIZE,D): The numeric type, size determines the maximum number of digits for the digit, and D is used to set the number of digits after the decimal point.

Finally, the one thing you need to be aware of when creating a new table is the restrictions on the columns in the table. The limiting condition is the rule that must be followed when entering data into a particular column. For example, a unique constraint requires that two records of the same value cannot exist in a column, and that all records must be unique. In addition to the unique, the more commonly used column restrictions include NOT NULL and primary key. Not NULL is used to specify that the value of a column in a table cannot be empty. Primary Key sets a unique identifier for all records in the table.


Inserting data into a table

The SQL language uses INSERT statements to insert or add new rows of data to a database table. Insert statements are used in the following format:

INSERT INTO TableName

(First_column,... Last_column)

VALUES (First_value,... last_value);

For example:

INSERT INTO employee

(FirstName, LastName, age, address, city)

VALUES (' Li ', ' Ming ', ', ' no.77 changan Road ', ' Beijing ');

In short, when you add a new record to a database table, enter the name of the table you want to add after the keyword insert INTO, and then list the names of the columns that will add the new value in parentheses. Finally, enter all the record values that you want to add, followed by the order of the columns you entered earlier, after the keyword values.


Update records

The SQL language uses the UPDATE statement to update or modify an existing record that satisfies a specified condition. The format of the UPDATE statement is:

Update TableName

Set columnname = NewValue [, Nextcolumn = newvalue2 ...]

where ColumnName OPERATOR value [and|or column OPERATOR value];

For example:

Update employee

Set age = Age+1

where First_name= ' Mary ' and last_name= ' Williams ';

When using the UPDATE statement, the key point is to set the WHERE conditional clause to use for the judgment.


Delete a record

The SQL language uses the DELETE statement to delete rows or records in a database table. The format of the DELETE statement is:

Delete from TableName

where ColumnName OPERATOR value [and|or column OPERATOR value];

For example:

Delete from employee

where LastName = May;

In short, when you need to delete a row or a record, enter the table name after the delete from keyword, and then set the criteria for the deletion of the record in the WHERE clause. Note that if the user does not set a WHERE clause when using the DELETE statement, all records in the table will be deleted.


Delete database table

Use the drop TABLE command in the SQL language to delete a table and all records in that table. The use format of the Drop Table command is:

drop TABLE TableName;

For example:

drop table employee;

If the user wants to delete a database table completely, simply enter the name of the table that you want to delete after the drop table command. The drop TABLE command works differently from all records in the delete table. After you delete all the records in the table, the table still exists and the information in the table is not changed. Using the drop TABLE command removes all information from the entire database table.


Above, we introduce the main commands and statements of SQL language in more detail. It should be said that the syntax of the SQL sentence structure and style is quite simple and intuitive, as long as the user combined with practice more practice, will be quickly mastered in the short term.