MySQL study note _ 8_ SQL BASIC Language Review

MySQL study note _ 8_ SQL BASIC Language Review
 SQL language basic review

 

I. Overview

SQL statement comments

1) All contents starting with "#" until the end of the line are comments

2) All content starting with "--" (-- and then a space) until the end of the line is annotated.

3) All content ending with "/*" and ending with "*/" is annotated and can be used to comment multiple lines.

 

Ii. Database Operations

1. Create a database

Create database db_name;

 

Db_name naming rules:

1) The name can consist of any letter, number, "_" or "$". It can start with any of the above characters, but cannot be used as the database name separately.

2) length limit: The name of a database, table, column, and index can be up to 64 characters in length, and the alias can be up to 256 characters in length.

3) You cannot use the MySQL keyword as the database or table name.

 

2. delete a database

Drop database db_name; # drop, terminate

It will unrecoverable Delete the database and all its data tables. We recommend that you back up the database before using drop database.

 

Iii. Table operations

1. Create a data table

Create table <table Name>

(<Column Name> <data type> [<column-level integrity constraints>]

[, <Column Name> <data type> [<column-level integrity constraints>]...

[, Constraints on table-level integrity]

);

 

Extended: create temporary table... # create a temporary table. The temporary table will be automatically deleted when the server interaction ends.

 

2. modify a data table

Modifies the structure of a table, uses the alter talbe statement to modify the attribute of a column in the table, or even modifies the table name.

Alter talbe <Table Name>

[Add <new column Name> <data type> [integrity constraints]

[Drop <integrity constraints>]

[Alter column <column Name> <data type>]; # alter, change

 

3. delete a table

Drop table table_name;

Drop table if exists table_name;

 

Iv. Recorded operations

1. Insert data

Insert into <Table Name>

[(<Attribute column 1>, <attribute Column 2>...)]

Values (<constant 1> [, <constant 2>...])

E.g. insert into student_info (stu_id, stu_name, str_sex, str_age)

Values (234, "xiaofang", "male", 18 );

 

2. update records

Update <Table Name>

Set <column name >=< expression> [, <column name >=< expression>]...

[Where <condition>];

Note: The update statement includes the set clause and the where clause. The set clause specifies the modification method, the columns to be modified, and the modified values. The where clause is used to specify the data records modified by the key, all records in the table are modified by default.The key to updating a statement is to set it for judgment.WhereCondition!

E.g. update student_info set str_age = 22 where stu_id = 9028;

 

3. delete records

Delete from <Table Name> [where <condition>];

Note: If you do not set the where condition when using the delete statement, all records in the table will be cleared!

Delete from student_info where stu_id = 9028;

 

5. Query

Select [all | distinct] <target list expression> [, <target list expression>]…

From <Table name or view Name> [, <Table name or view Name>]...

[Where <conditional expression>]

[Group by <column name 1> [having <conditional expression>]

[Order by <column name 2> [asc | desc];

 

Vi. Example of the database design process of the Course Selection System for students

1. Database Design Process:

System Analysis ---> logical design ---> physical implementation

2. System Analysis

 

3. Logic Design