Database Learning (2) -- SQL language Overview

Database Learning (2) -- SQL language Overview

Database Learning (1) -- Introduction to relational database http://www.bkjia.com/database/201304/205333.html
SQL
Structured Query language (Structured) is a Structured Query language, which is a standard language for communication with relational databases. SQL standards are jointly developed by ISO (International Standardization Organization) and ANSI (American National Standardization Organization, the following standards have been used since 1983: SQL86, SQL89, SQL92, SQL99, and SQL2003.
SQL products of different vendors and their relational database management systems are mostly compatible with SQL standards, but they do not fully comply with this standard, therefore, the implementation of SQL statements for relational database systems produced by different vendors is still different. In addition, the relational database systems produced by different manufacturers have extended SQL to different degrees. Currently, there are two popular versions of standard SQL, PL/SQL and Transact-SQL.
PL/SQL is a communication language based on Oracle databases. Oracle has integrated PL/SQL into Oracle servers and can run in any Oracle development environment.
Transact-SQL is based on the communication language between the Sybase Database and the Microsoft SQL Server database. Its main runtime environments are SQL Server Management Studio and SQLCMD.
SQL statements mainly include Data Definition Language (DDL), Data Query Language (DQL), data operation language (DML), Data Control Language (DCL), and transaction control language.
Data Definition Language (DDL): it is mainly used to CREATE, modify, and delete database objects (data tables, views, and indexes), including the CREATE, ALTER, and DROP statements.
Data Query Language (DQL): Used to query data in a database. The main statement is the SELECT statement. SELECT statements are the most important part of SQL. The SELECT statement consists of five clauses: FROM clause, WHERE clause, group by clause, HAVING clause, and WITH clause.
Data Operation Language (DML): it is used to UPDATE data in data tables in a database, including INSERT, UPDATE, and DELETE statements. The INSERT statement is used to INSERT data into the database, the UPDATE statement is used to modify data in the database, and the DELETE statement is used to DELETE data in the database.
Data Control Language (DCL): it is mainly used to grant and revoke some permissions to access the database. Including GRANT and REVOKE statements. The GRANT statement is used to GRANT permissions to users, and the REVOKE statement revokes permissions to users.
Transaction Control Language: The full English spelling is Transactional Control Statement. It is mainly used for database transaction control to ensure data consistency in the database, including statements such as COMMIT and ROLLBACK. Here, COMMIT is used for transaction COMMIT; ROLLBACK is used for transaction ROLLBACK.
SQL language features: easy to learn, easy language structure, non-procedural language, set operation, SQL language can be embedded into advanced languages.
Common Data Types
When creating a data table, in addition to the table name and column name of the data table, you also need to select an appropriate data type for each column in the data table. In databases, common data types include integer, floating point, numeric, date and time, character, and binary. Different databases have different data types. This section takes the Oracle database, MySQL database, and Microsoft SQL Server database as an example to introduce the definitions of common data types and their usage in these three databases.
Integer and floating point types

Integer data can be a positive integer or a negative integer. In MySQL databases, these integer types can also contain a parameter to indicate the maximum display width of data. For example, INT (4) indicates that the column width of the displayed data column is 4. Using parameters does not affect the value range of the integer type. This parameter is optional.
In MySQL databases, FLOAT and DOUBLE Floating Point types are mainly included. FLOAT indicates a single-precision floating point number, and DOUBLE indicates a DOUBLE-precision floating point number.

In the MySQL database, these floating point numbers can also contain two parameters. The m parameter indicates the number of digits that are valid for storing data, and the n parameter indicates the number of digits after the decimal point.
In Microsoft SQL Server databases, you can also use TINYINT, SMALLINT, INT, and BIGINT to store integer data, the value range is the same as that of TINYINT, SMALLINT, INT, and BIGINT in Table 2.1.
Floating point data can be defined as REAL and FLOAT. The REAL type occupies 4 bytes of storage space and can be precise to the last 7 digits after the decimal point. The value range is-2.4E38 to 2.4E38. The FLOAT type occupies 8 bytes of storage space, it can be precise to the last 15 decimal places. The value range is-2.7E308 to 2.7E308.
Value Type
In the Oracle database. You can use NUMBER (m, n) to define numeric data. The m parameter indicates the number of digits that are valid for storing data, and the n parameter indicates the number of digits after the decimal point. For example, NUMBER (5, 2), the first parameter 5 indicates that the total NUMBER of digits displayed is 5, and the second parameter indicates the NUMBER of digits after the decimal point. If you insert data 122.456 into a data column defined as NUMBER (122.46), the data actually put into this column is.
Character Type
In databases, character types are used to store string values. Different databases have different character types. The following uses the Oracle database, MySQL database, and Microsoft SQL Server database as an example, this section describes how to define different character types in these three databases.
1. Oracle Database
CHAR or VARCHAR2
2. MySQL database
CHAR, VARCHAR, TEXT, MEDIUMTEXT LONGTEXT
3. Microsoft SQL Server database
CHAR, VARCHAR, TEXT
Note: The space occupied by strings stored in columns defined by CHAR is unchangeable. the space occupied by strings stored in columns defined by VARCHAR2 is variable.
Date and Time Type
In databases, the date and time types are used to store Date and Time values. Different databases have different definitions of Date and Time types. The following uses the Oracle database, MySQL database, and Microsoft SQL Server database as an example, this section describes how to define different date and time types in these three databases.
1. Oracle Database
In Oracle databases, data of the DATE and time type can be defined in the form of DATE and TIMESTAMP. The default format of the DATE type in the English version is the form of a DD-MON-YY. For example, 10-SEP-09; in the Chinese version, the default date format is 'day-month-Year', for example, 10-9-09.
The default format of the TIMESTAMP type in the English version of the date is DD-MON-YY HH. MM. SS. AM. For example, 10-SEP-09 12.22.000000 PM. The default date format in the Chinese version is 'day-month-year hour, minute, second ', for example, 10-9 month-09 12.22.20.00 afternoon. In addition to the hour, minute, and second, it also contains the fractional part of the second. The second value is accurate to 6 digits after the decimal point.
2. MySQL database
The default format of data of the DATE type is YYYY-MM-DD, for example
The default format of TIME data is HH: MM: SS, for example, 18:13:23.
The default format of DATETIME data is YYYY-MM-DD HH: MM: SS, for example, 18:13:23.
3. Microsoft SQL Server database
The DATETIME type requires 8 bytes of storage space. Its date value ranges from January 1, January 1-9, 1753 to January 1, December 31, 999.
The SMALLDATETIME type requires four bytes of storage space. Its date value ranges from January 1, January 1-20, 1900 to January 1, June 6. The time part can be accurate to minutes. The accuracy of the SMALLDATETIME type is not as high as that of the DATETIME type.
In SQL Server 2008, four types of DATETIME data are added: DATE, TIME, DATETIMEOFFSET, and datetime2.
Binary type
Note: BLOB (binary large object) is a large binary object. It is a container that can store binary files.
In databases, binary data is used to store binary data. Different databases have different definitions of binary data types. in Oracle databases, BLOB can be used to store binary data information, up to 4 GB.
In MySQL databases, you can use BLOB to store binary data, TINYBLOB, MEDIUMBLOB, and LONGBLOB to store binary data. In Microsoft SQL Server databases, BINARY, VARIBINARY, and IMAGE can be used to store BINARY data information. The data stored in the IMAGE data type is stored as a bit string. It is not explained by Microsoft SQL Server, but by applications.
SQL statement writing Specification
When using the SQL language to query and update data, you also need to understand the SQL language writing specifications. This section describes some of the major writing standards in the SQL language.
The SQL language is case insensitive to keywords.
SELECT teaID, teaName, age FROM T_teacher WHERE age> 30
Select teaID, teaName, age from T_teacher where age> 30
The SQL language does not distinguish the case sensitivity of column names and object names.
SQL is case sensitive to database data.
"--" Can be used for single-line annotations in SQL. When "--" is used to annotate a single line, there must be at least one space after.
You can use "/* Comment content */" for multi-line comments */". The content starting with "/*" and ending with "*/" belongs to the commented content.
SQL statements can be written in one or multiple rows.