Structured query language is an advanced non-procedural programming language, which allows users to work on advanced data structures. It does not require users to specify the storage mode of data, nor does it require users to know the specific storage mode of data, so different database systems with completely different underlying structures can use the same structured query language as the interface for data input and management. Structured query language statements can be nested, which makes it very flexible and powerful.
1986 10, the American National Standards Institute standardized SQL and became the standard language of relational database management system (ANSI X3. 135- 1986) and became an international standard in 1987 with the support of the international organization for standards. However, various popular database systems have made some modifications and extensions to the SQL specification in practice. Therefore, in fact, SQL between different database systems is not completely universal.
Basic introduction Chinese name:? Structured query language mbth:? 10 adopts structured query language. Domain: Database Duration: 28 years. Language introduction, application, supported standards, other versions, sentence structure, development history, data type, character type, text type, numerical type, logical type, date type, language characteristics, brief usage, selection list, FROM clause, WHERE clause, query result ranking, related functions, language introduction. Structured Query Language (SQL) is the most important relational database operation language, and its influence has gone beyond the database field, and it has also been paid attention to and adopted in other fields, such as data retrieval in the field of artificial intelligence, and the language embedded with SQL in the fourth generation software development tools. The supporting standard SQL is the American database language standard adopted by the American National Bureau of Standards (ANSI) in June 1986+00, and then the International Organization for Standardization (ISO) issued the formal international standard of SQL. 1April, 989, ISO put forward the SQL89 standard with integrity characteristics,1October, 1992, 165438 published the SQL92 standard. In this standard, the database is divided into three levels: basic set, standard set and complete set. Other versions of different databases have slightly different support and standards for SQL language, because some products were developed before the release of standards, and in addition, product developers need to extend the standards in order to achieve special performance or new features. There are more than 100 kinds of database products SQL distributed from microcomputer to mainframe, including DB2, SQL/DS, ORACLE, INGRES, SYBASE, SQLSERVER, DBASE, PARADOX, MICROSOFTACCESS, etc. SQL language is basically independent of the database itself, the machine used, the network and the operating system. DBMS products based on SQL can run on various computer systems based on personal computers and workstations, and have good portability. It can be seen that standardization work is very meaningful. As early as 1987, people of insight predicted that the standardization of SQL was a "revolution" and a "turning point of relational database management system". Database and various products use SQL as the interface of the same data access language and standard, which makes the interoperability between different database systems have the same foundation, and then realizes the sharing and transplantation of heterogeneous computers and various operating environments. 1974, in the large-scale relational database management system SYSTEM R developed by IBM San Jose research laboratory, the SEQUEL language (proposed by BOYCE and CHAMBERLIN) was used, and then the SQL language was developed on the basis of SEQUEL. SQL language is an interactive query language, which allows users to query the stored data directly. However, it is not a complete programming language. For example, it can be embedded in another language, or it can be directly transmitted to the database management system through the call-level interface using VB, C, JAVA and other languages. SQL is basically domain relational calculus, but it can realize relational algebraic operation. The sentence structure structured query language includes six parts: 1. Data query language (DQL): its sentence is also called "data retrieval sentence", which is used to obtain data from tables and determine how the data is given in the application. The reserved word SELECT is the most used verb in DQL (and all SQL). Other reserved words in DQL are WHERE, ORDER BY, GROUP BY and HAVING. These DQL reserved words are usually used for other types of SQL statements. Second, data manipulation language (DML): its statements include the verbs INSERT, UPDATE and DELETE. They are used to add, modify and delete rows in the table, respectively. Also known as action query language. Third, Transaction Processing Language (TPL): Its statements can ensure that all rows of the table affected by DML statements are updated in time. TPL statements include start transaction, commit and rollback. 4. Data Control Language (DCL): Its statements are authorized by GRANT or REVOKE to determine the access rights of individual users and user groups to database objects. Some RDBMS can use GRANT or REVOKE to control access to form columns. Five: Data Definition Language (DDL): Its statements include verbs CREATE and DROP. Create a new table or delete a table (CREAT TABLE or drop table) in the database; Add indexes to tables, etc. DDL includes many reserved words related to the data obtained in the directory of human database. It is also part of the action query. Six: Pointer Control Language (CCL): Its statements, such as DECLARE CURSOR, FETCH INTO and UPDATE WHERE CURRENT, are used to operate independently on one or more forms. In the early1970s, edgar codd of IBM's research laboratory in San Jose, California published the relational algebra of codd. 1974, D.D.Chamberlin and R.F. Boyce of the same laboratory developed a set of standard language-sequel (structured English query language) when developing the relational database management system R of CODD algebra, and implemented it in 1976 1. A new version of SQL (called SEQUEL/2) was released in D. 1980 and renamed as SQL. 1979, ORACLE first provided commercial SQL, and IBM also implemented SQL in DB2 and SQL/DS database systems. 1986 10, American ANSI adopts SQL as the standard language of relational database management system (ANSI X3. 135- 1986), which was adopted as an international standard by the international organization for standardization (ISO). In 1989, American ANSI adopted the SQL standard language of relational database management system defined in ANSI X3. 135- 1989 report, called ANSI SQL 89, which replaced ANSI X3. 135- 1986 version. This standard has been adopted by the following organizations: International Organization for Standardization (ISO), which reported "SQL with integrity enhancement" for ISO 9075- 1989, the United States * * *, published in the Federal Information Processing Standards Publication (FIPS publication) 127, the main (265438+early 20th century). Data types briefly describe five data types in structured query language: character type, text type, numerical type, logical type and date type. The difference between CHARacter VARCHAR VS CHAR VARCHAR and CHAR data is subtle, but very important. They are all used to store characters whose string length is less than 255. Suppose you enter data Bill Gates in a 40-character VARCHAR field. When you take this data from this field in the future, the length of the data you take out is ten characters-the length of the string Bill Gates. If you enter a string in a 40-character CHAR field, when you retrieve the data, the retrieved data will be 40 characters long. Extra spaces will be appended to the string. When you build your own site, you will find it much more convenient to use the VARCHAR field than the CHAR field. When using the VARCHAR field, you don't have to worry about deleting extra spaces in the data. Another outstanding advantage of VARCHAR field is that it takes up less memory and hard disk space than CHAR field. When the database is large, saving memory and disk space will become very important. Text TextUsing text data, you can store strings of more than 2 billion characters. When you need to store long strings, you should use text data. Note that the text data has no length, while the aforementioned character data has length. The data in a text field is usually either empty or very large. When you collect data from the multi-line text editing area of an HTML form, you should store the collected information in a text field. However, you should not use a text field as long as it can be avoided. Text fields are large and slow, and abusing them will slow down the server. Text fields also consume a lot of disk space. Once you enter any data in the text field (even if it is null), 2K space will be automatically allocated for the data. This storage space cannot be reclaimed unless the record is deleted. Numerical integer int, decimal NUMERIC, money money int vs small vs tiny generally, in order to save space, try to use the smallest integer data. TINYINT data only takes up one byte; An INT data occupies 4 bytes. It doesn't seem to make much difference, but in a relatively large table, the number of bytes will increase rapidly. On the other hand, once a field is created, it is difficult to modify it. Therefore, to be safe, you should predict the maximum possible value that a field needs to store, and then choose the appropriate data type. Numbers In order to better control the data stored in fields, numerical data can be used to represent the integer and decimal parts of numbers. Numeric data enables you to represent very large numbers-much larger than INT data. Numeric fields can store numbers ranging from-10 38 to 10 38. Numeric data also enables you to represent numbers in decimal parts. For example, you can store the decimal 3. 14 in a numeric field. You can use integer or numerical data to store money. However, there are two other data types dedicated to this purpose. If you want your outlets to make a lot of money, you can use money data. If you are not ambitious, you can use SMALLMONEY data. Monetary data can store monetary amounts from-922,337,203,685,477.5808 to 922,337,203,685,477.5807. If you need to store more money, you can use digital data. SMALLMONEY data can only store amounts from -2 14748.48 to 2 14748.647. Similarly, if possible, you should use SMALLMONEY instead of MONEY data to save space. Logical BitIf you use a check box to collect information from a web page, you can store the information in a bit field. A bit field can only take two values: 0 or 1. Note that you cannot add a bit field to a table after it is created. If the plan includes a bit field in the table, it must be completed when the table is created. DATETIME and SMALLDATETIME DATETIME fields can store dates ranging from 1 75365438+1the first millisecond in October1to the last millisecond in February 3 1, 999. If you don't need to cover such a large range of dates and times, you can use SMALLDATETIME data. It is the same as DATETIME data, but the date and time range it can represent is smaller than DATETIME data, and it is not as accurate as DATETIME data. The SMALLDATETIME field can store the date from19001June1to June 6, 2079, and it can only be accurate to the second. Before entering the date and time, be sure to realize that the date and time field does not contain actual data. Language features 1. Integration: SQL integrates data definition DDL, data operation DML and data control DCL, which can complete all the work in the database. 2. Flexible use: There are two ways to use it, that is, it can be used directly and interactively through commands; It can also be embedded in mainstream languages such as C, C++, FORTRAN, COBOL and JAVA. 3. Non-procedural: only the operation requirements are mentioned, and there is no need to describe the operation steps or navigate. When you use it, you just need to tell the computer what to do, not how to do it. 4. The language is concise, the grammar is simple, easy to learn and use: In ANSI standard, only 94 English words are included, and only 6 verbs are used in the core function, so the grammar is close to spoken English. SQL (Structured Query Language) is a database operation language. Queries using simple transaction structured query language only include select list, FROM clause and WHERE clause. They describe the columns of the query, the tables or views of the query and the search conditions respectively. Select list select list (select _ list) represents the column of the query, which can be a list of column names, asterisks, expressions, variables (including local variables and global variables), etc. 1. Select all columns. For example, the following statement displays the data of all columns in the testtable table: SELECT * FROM testtable 2. Select some columns and specify their display order. The data in the query result set is in the same order as the column names specified in the selection list. 3. Change the column headings. In the selection list, you can specify the column headings again. The definition format is: column header = column header If the specified column header is not in the standard identifier format, the quotation mark separator should be used. For example, the following statement displays column headings in Chinese characters: SELECT nickname =nickname, email =email FROM testtable. 4. Delete duplicate rows Use the ALL or DISTINCT option in the SELECT statement to display all eligible rows in the table or delete duplicate data rows. The default is all. When the DISTINCT option is used, only one row remains in the result set returned by SELECT for all duplicate data rows. 5. Limit the number of rows returned. Use the TOP n [PERCENT] option to limit the number of rows of data returned. Top n means to return n rows, while TOP n percent means that n represents a hundred points, and specifies that the number of rows returned is equal to the percentage of the total number of rows. The TOP command is only applicable to SQL Server series databases, and does not support Oracle databases. FROM clause The FROM clause specifies the query of the SELECT statement and the tables or views related to the query. In the FROM clause, you can specify up to 256 tables or views, separated by commas. When multiple tables or views are specified in the FROM clause at the same time, if there are the same columns in the selection list, the tables or views to which these columns belong should be qualified with object names. For example, there are columns in both usertable and citytable. When querying cityids in two tables, you should use the following statement format to restrict them: select username, citytable. City id in user table, city table in user table. Cityid = citytable。 Cityid can specify aliases for tables or views in the from clause in the following two formats:
Table name as alias
The table name alias WHERE clause WHERE clause sets the query conditions and filters out unnecessary data rows. The WHERE clause can contain various conditional operators: comparison operator (size comparison): >; ; 、gt； =、=、& lt； 、& lt= 、& lt& gt； 、! & gt； 、! & lt range operator (whether the expression value is within the specified range): BETWEEN…AND… NOT BETWEEN…AND… ... and ... and ... list operator (judging whether the expression is the specified item in the list): in (item 1, item 2 ... 2 ...) is not available (item 1, Item 2 ...) Pattern matcher (to judge whether the value conforms to the specified wildcard format): LIKE, NOT LIKE null judger (to judge whether the expression is empty): IS NULL, IS NOT NULL logical operators (for multi-conditional logical connection): NOT, AND, OR 1, and range operators: age between en10 and 30 are equivalent to age. = 10 and age < = 30 2. Example of list operator: country (IN) ('German',' China') 3. Pattern matcher example: it is often used in fuzzy search to judge whether the column value matches the specified string format. It can be used for various types of queries, such as char, varchar, text, ntext, datetime and *** alldatetime. You can use the following wildcards: percent sign%: You can match characters of any type and length. If you are from China, please use the symbol of two percent, that is,%%. Underline _: Matches a single arbitrary character, which is usually used to limit the character length of an expression. Square brackets []: specify a character, string or range, and the matching object is required to be any one of them. []: Its value is also the same as [], but the matching object is required to be any character other than the specified character. Query result sorting uses the ORDER BY clause to sort the results returned by the query by one or more columns. The syntax format of the ORDER BY clause is: order by {column _ name [ASC | desc]} [,…n] where ASC stands for ascending order, which is the default value, and Desc stands for descending order. ORDER BY cannot be sorted by ntext, text and image data types. Correlation function SQL aggregate function SQL aggregate function calculates the value obtained from the row and returns a single value. Useful aggregate functions: AVG()- Returns the average count ()-Returns the number of rows first ()-Returns the value of the first record LAST()- Returns the value of the last record MAX()- Returns the maximum value MIN (). -Returns the minimum sum ()-Returns the sum SQL scalar function SQL scalar function returns a single value based on the input value. Useful scalar functions: UCASE()- Convert a field to uppercase LCASE()- Convert a field to lowercase MID()- Extract the character len () from a text field-Return the length of the text field ROUND (). -Rounds the numeric field to the specified number of decimal places immediately ()-Returns the current system date and time format ()-Formats the display of the field.