Python Mysql-sql Overview

2017-09-05 20:10:58

I. SQL statements and their types

SQL uses keywords, table names, column names, and so on, to describe the contents of the operation by combining them into a single statement. Keywords are English words that have meaning or are used in a way that is defined first. Depending on the type of instruction given by the RDBMS, the SQL statement can be divided into the following three categories:

• data Definitionlanguage is used to create or delete objects such as databases and tables in a database. The DDL contains several directives.

Create: Creating objects such as databases and tables

Drop: Delete objects such as databases and tables

Alter: Modify the structure of objects such as databases and tables

• Data Manipulation Language DML(manipulation language) is used to query or alter records in a table. DML contains the following directives.

Select: Querying data in a table

Insert: Inserting new data into a table

Update: Change the data in a table

Delete: Deleting data from a table

• The Data Control Language DCL(language) is used to confirm or cancel changes to the data in the database. In addition to this, it is possible to set up an RDBMS user with permission to manipulate objects in the database (database tables, etc.). The DCL contains the following directives.

Commit: Confirm the changes made to the data in the database

Rollback: Canceling changes to data in the database

Grant: Give user permission to operate

Revoke: Canceling the user's operation rights

90% of the SQL statements that are actually used belong to DML.

Ii. Basic Writing rules for SQL

1. SQL statement to end with a semicolon
2. SQL statements are case insensitive (the case of keywords is not distinguished)
3. Constants are written in a fixed way (strings and dates need to be enclosed in quotation marks, but numeric constants do not need to be quoted)
4. Use half-width spaces or newline characters to split between words
5. Naming rules: You can use only half-width English characters, numbers, and underscores as names for databases, tables, and columns. And the beginning of the name must begin with a half-width English character

Third, the data type

• INTEGER

Used to specify the data type of the column that stores the integer and cannot store decimals

• CHAR

Used to specify the data type of the column where the string is stored, such as char (100), which specifies the maximum length of the string in parentheses, the part of the string that exceeds the maximum length cannot be entered in the column. Depending on the RDBMS, the number of characters here is 100, and it may refer to the byte length.

The string is stored in a fixed-length string in a column that is specified as char, and the so-called fixed-length string is the use of half-width spaces to make up when the length of the string stored in the column does not reach the maximum length. Of course, the characters here are case-sensitive.

• VARCHAR

Can be edge long string type, can be like varchar (100) So, in parentheses specify the maximum length of the string, if the string does not reach the maximum length, also does not use half-width space to complement. Of course, the strings here are case-sensitive.

• DATE

The data type of the column that is used to specify the date (month and day) to store

Python Mysql-sql Overview