Summary of common SQL statements.

After performing project exercises for a period of time, you have basically mastered the use of SQL statements to create tables and data addition, deletion, modification, and query operations, however, in addition to common operations such as adding, deleting, modifying, and querying data tables, primary keys, foreign keys, and non-null keys are often used to ensure database integrity, the default value, automatic growth, and other constraints, after adding these content, the idea is a bit confusing, and some syntaxes are not very clear. To clarify the differences between them, I would like to make the following summary:

1. create and manage data tables:

1. Create a data table:

 
Create Table Name

(

Field name 1 field type,

Segment name 2 field type,

Field name 3 field type,

......

Constraint Definition 1,

Constraint Definition 2,

......

)
 

2. Modify the data table:

1> Add field: alter table table name to be modified add field Name field type

2> Delete field: alter table table to be modified table name drop field name to be deleted

3. delete a data table:

Drop Table Name of the table to be deleted

 

2. Data addition, deletion, modification, and query:

1. Insert data: insert into name of the table to be inserted (Field 1, Field 2, Field 3 ...) values ('value 1', 'value 2', 'value 3 '...);

Example: insert into t_person (fname, Fage, fremark) values ('Tom ', 18, n' ');

Note: A> If the field value after values contains Chinese characters, it is best to add the "N" letter before the field so that it is encoded in Unicode to prevent the inserted data from being garbled.

B> value of the field after values. If it is a character or string type, the value is enclosed in single quotation marks. If it is an integer type, it can be written directly without single quotation marks.

2. data deletion: Where condition for the delete from table name;

Example: delete from t_person where FID = 1;

Note: A> the difference between Delete and drop table is that delete only deletes the data in the table, while the table structure still exists. Drop table not only deletes the data in the table, the table structure is also deleted.

B> after the WHERE clause, it can be connected to multiple conditions and use logical operators, such as and or.

3. Data Update: Update table name set field name = 'value' where condition;

Example: Update t_person set Fage = 27 where FID = 1;

4. Data Query: Select field name from table name where condition;

Example: Select * From t_users where fusername = 'admin ';

Note: A> the field name can be replaced by the wildcard "*" to indicate all fields.

B> after the WHERE clause, it can be connected to multiple conditions and use logical operators, such as and or.

 

3. add and delete constraints (usually when the data table has been created). alter table statement

1. constraint addition: alter table table name

Add constraint name

......

Example: Add a foreign key constraint: alter table t_book

Add constraint fk_book_author

Foreign key (fauthorid) References t_author (FID );

2. Delete constraints: alter table table name

Drop constraint name;

Example: alter table t_book

Drop constraint fk_book_author;

Conclusion: 1> insert into, delete, update, select, and other statements operate on database data, while alter table is mainly used to operate on the table structure and table constraints.

2> master the primary key constraints, foreign key constraints, non-empty constraints, automatic growth, and setting default values to Achieve flexible database operations.