Data Analysis---SQL (Access database)

1. SQL (Structure Quest Language)

A structured query language, which is a common relational database operation language for accessing data, querying, updating and managing databases.

2. Basic statements

Select, Create, Insert, Drop

3. SQL statement Considerations

1) in the SQL statement, the English letter uppercase or lowercase can be

2) The keywords for each SQL statement are delimited with blank symbols

3) separate the fields or parameters with commas

4) In SQL statements, such as the argument is a character type, you need to use single quotation marks, numeric type does not use single quotation marks

5) At the end of the SQL statement, add a semicolon at the end of the statement, and in the Access database, no force is required to add a semicolon at the end of the SQL statement.

6) In an Access database SQL statement, such as a table name, a space in the field name, '/', ' \ ' and other special characters, use square brackets ' [] ' to enclose the table or field name with special characters in order to prevent incorrect results or SQL statements from running.

7) in the SQL statement, ' * ' represents all the fields in the selected data table and is displayed in a fixed order in the database

8)Access database, in function parameters or conditional queries, if the parameter or query condition is date and time type data, you need to add a tic sign (#) at both ends of the data value to indicate that the data type is date

9) The commas, semicolons, single quotes, brackets and other symbols used in the SQL statement need to be the symbols entered in the input method to the English state .

10) Try to avoid a full table scan in the database:

A. First, you should consider using the WHERE clause to filter out the required data;

B. Second, in the WHERE clause, try to avoid using the "! = "or" <> "," or ", etc.

C. Finally, you should try to avoid the function of the field in the WHERE clause, otherwise the full table will be scanned

4. Field splitting

1) Correlation function

A. Left (string, number of bits extracted)

B. Right (string, number of bits extracted)

C. Mid (string, starting position of extraction, number of bits extracted)

2) Example: Extracting Province code, county code, date of birth from the ID number

SELECT ID number, left (identity number, 2) as province code,

Mid (identity number, 7,4) as year,

Mid (identity number, 11,2) as month,

Mid (identity number, 13,2) as day,

Mid (identity number, 17,1) as gender

from user details;

5. Random sampling: A method of extracting samples according to the stochastic principle

Example: Randomly extracting 20 records

SELECT TOP 20 *

From user details

ORDER by Rnd (user ID);

6. Data merging

1) field Merge

A. Using the connector: &

SELECT ID number,

(Mid (ID number, 7,4) & '-' & Mid (ID card number, 11,2) & '-' & Mid (ID number, 13,2)) as date of birth

from user details;

B. Using related functions (such as Date function DateSerial)

SELECT ID number,

DateSerial (Mid (identity card number, 7,4), Mid (identity card number, 11,2), Mid (identity card number, 13,2)) as date of birth

from user details;

2) field match

A. Connection type: Mainly includes the inner connection (INNER join), left connection (right join), three kinds of database relationship of connecting on the starboard side.

B.

3) Record Merge

Data Analysis---SQL (Access database)