SQL is usually called ess-cue-el (IPA: [s? Kjul]), or like the word sequel (IPA: [? Is it? kw？ l])。 According to ANSI, the official pronunciation of SQL is ess-cue-el. However, every major database product (or project) containing the letter SQL has its own convention: MySQL is usually officially pronounced as "my ess cue El"; PostgreSQL is conveniently pronounced as PostgreSQL (the name of the predecessor of PostgreSQL); Microsoft SQL Server is usually called Microsoft-sequel-server.
history
An influential paper "Data Relation Model of Large-scale Shared Database" written by Dr. Edgar F. Codd was published in ACM Newsletter, a journal of the Association of Computing Machinery (ACM), on June 1970, although its draft was circulated in IBM on June 1969. The model of Codd is widely accepted as the final model of relational database management system (RDBMS or RDMS).
In the1970s, a group of IBM San Jose Research Center developed a database system "System R" based on the model of Codd. Structured English Query Language ("SEQUEL") aims to manipulate and retrieve data stored in system R. The abbreviation SEQUEL was later condensed into SQL because the word "SEQUEL" was held as a trademark by Hawker Siddeley aircraft company in Britain. [need to quote] Although SQL is influenced by the work of Codd, Donald D. Chamberlin and Raymond F. Boyce of IBM are the authors of "Language Design Sequel". Their concepts were published to increase interest in SQL.
Ingres, the first non-commercial, relational and non-SQL database, was developed at the University of California, Berkeley 1974.
In 1978, the system test starts at the customer test point. It proves that the system is useful and practical, and this test is successful for IBM. As a result, IBM began to develop commercial products based on their SQL System R prototype, including System/38 (released in1978, commercialized in August/979), SQL/DS (launched in1981year) and DB2 (1year).
At the same time, Relational Software (now Oracle) saw the potential of the concepts described by Chamberlin and Boyce, and developed their own versions of RDBMS for the navy, CIA and other institutions. 1in the summer of 979, Relational Software, Inc. introduced Oracle V2 (2nd edition) for VAX computer as the first commercial SQL implementation. Oracle Bone Inscriptions V2 Company released System/38 to the market several weeks earlier than IBM.
standardization
SQL was adopted as the standard by ANSI (American National Standards Institute) in 1986 and ISO (International Organization for Standardization) in 1987. However, since the dissolution of the NIST data management standard in 1996, there has been no certification meeting the SQL standard, so it is necessary to rely on suppliers for self-certification.
The SQL standard is not provided for free. SQL:2003 and SQL:2006 can be purchased from ISO or ANSI. The latest draft of SQL:2003 can be obtained from Whitemarsh Information System Company. Zip archive contains many PDF files, which define various parts of SQL:2003 specification.
range
SQL is designed for a specific purpose: to query the data contained in a relational database. SQL is a declarative programming language based on sets, rather than imperative languages such as C or BASIC.
Language extensions, such as PL/SQL of Oracle, bridge this gap to some extent by adding process elements, such as control flow structure. Another method is to allow programming language code to be embedded in and interact with the database. For example, Oracle and other companies include Java in their databases, while SQL Server 2005 allows any. NET language is hosted in the database server process, while PostgreSQL allows functions to be written in multiple languages, including Perl, Tcl and C.
There are extensions and changes in standards. Business implementations usually ignore support for basic features of standards, such as date or time data types, and prefer their own variants. Compared with ANSI C or ANSI Fortran, SQL code can rarely be transplanted between database systems without major modifications, while ANSI C or ANSI Fortran can usually be transplanted between platforms without major structural changes.
PL/SQL, SQL PL of IBM and Transact-SQL of Sybase/Microsoft are proprietary because the process programming languages they provide are non-standardized.
Reasons for lack of portability
There are several reasons for the lack of portability between database systems:
The complexity and scale of SQL standard means that most databases can't realize the whole standard.
* The standard does not specify the behavior of the database in several important areas (such as indexing), but the implementation of the database determines how to behave.
The SQL standard precisely specifies the syntax that a database system that meets the standard must implement. However, the semantic specification of language structure is not well defined in this standard, which leads to some ambiguities.
* Many database vendors have a large existing customer base; When the SQL standard conflicts with the previous behavior of the supplier database, the supplier may be reluctant to break the backward compatibility.
SQL keyword
question
The most common operation in SQL database is query, which is represented by SELECT keyword. SQL SELECT query is declarative:
* SELECT retrieves data from tables in the database. Although it is often combined with data manipulation language statements, many people think that SELECT is separate from SQL DML. Selecting a query allows the user to specify the description of the desired result set, but it leaves it to the equipment of the database management system (DBMS) to plan, optimize and perform the physical operations necessary to generate the result set. The SQL query includes a list of columns contained in the final result im, immediately after the SELECT keyword. An asterisk ("*") can also be used as a wildcard indicator to specify that all available columns of a table (or tables) will be returned. SELECT is the most complicated statement in SQL, with several optional keywords and clauses:
The from clause indicates the source table from which data is extracted. The FROM clause can contain an optional JOIN clause to connect related tables to each other.
The WHERE clause contains a comparison predicate to narrow the result set. The WHERE clause deletes all rows from the result set that the comparison predicate does not evaluate to True.
The GROUP BY clause is used to combine rows with related values into elements of a smaller rowset.
The HAVING clause is used to determine which "combined rows" to retrieve (combined rows are generated when the query contains a GROUP BY clause or when the selection contains aggregations). HAVING behaves much like WHERE, but it operates on the results of GROUP by and can contain aggregate functions.
The ORDER BY clause is used to determine which columns are used to sort the result data. Unless the ORDER BY clause is included, the order of rows returned by SELECT can never be guaranteed.
Data retrieval is often combined with data projection; Usually, what users are looking for is not the word-for-word data stored in the original data type, nor is it the service to write a query. Usually, data needs to be expressed in a way different from the way it is stored. SQL allows you to select various formulas contained in the list to project data.
Example 1:
Choose from books *
Where the price & gt 100.00
Sort by title
This is an example that can be used to get a list of expensive books. It retrieves records with a price field greater than 100.00 from the books table. The results are sorted alphabetically by title. An asterisk (*) indicates that all columns of the books table are displayed. Alternatively, you can name specific columns.
Example 2:
Select books.title, count(*) as the author.
From the book
Join Book _ Author
ON books . book _ number = book _ authors . book _ number
Group by book. title
It can also be written as
Select a title and count by author (*)
From the book naturally join the book _ author
Group by title
The premise is that book_number is the only common column name of the two tables, and the column named title only exists in books.
Example 2 shows the use of multiple tables in a connection and aggregation (grouping). This example shows how many authors there are in each book. The sample output may be similar to:
Title author
- -
SQL Examples and Guides 3
The happiness of SQL 1
How to use Wikipedia 2
The trap of SQL 1
How can SQL save my dog 1
data manipulation
First, there are standard data manipulation language (DML) elements. DML is a subset of languages used to add, update and delete data:
* INSERT is used to add rows (in the form of tuples) to an existing table.
* UPDATE is used to modify the values of a set of existing table rows.
* MERGE is used to merge data from multiple tables. It is a combination of inserting and updating elements. It is defined in the SQL:2003 standard; Prior to this, some databases provided similar functions through different grammars, sometimes called "upsert".
* DELETE deletes zero or more existing rows from the table.
Insert example:
Insert my_table (field 1, field2, field3) values ('test',' n', null);
Update example:
UPDATE my_table set field 1 = 'updated value' where field2 =' N
Delete example:
Delete from my_table, where field2 =' N
Transaction control
Transactions (if available) can be used to wrap DML operations:
* BEGIN WORK (or START TRANSACTION, depending on SQL dialect) can be used to mark the beginning of a database transaction, which is either completely completed or not completed at all.
* COMMIT makes all data changes in the transaction permanent.
* Rollback will cause all data changes since the last commit or rollback to be discarded, so the data state will be "rolled back" to the state before these changes were requested.
Commit and rollback interact with areas such as transaction control and locking. Strictly speaking, both terminate any open transactions and release any locks held on the data. In the absence of BEGIN WORK or similar statements, the semantics of SQL depend on implementation.
Example:
Start work;
Update inventory set quantity = quantity-3, where item = "Pants";
Submit;
data definition
The second set of keywords is Data Definition Language (DDL). DDL allows users to define new tables and related elements. Most commercial SQL databases have proprietary extensions in their DDL, which allow to control the nonstandard characteristics of the database system. The most basic items of DDL are create, change, rename, truncate and delete commands:
* CREATE causes an object (for example, a table) to be created in the database.
* DROP causes the existing objects in the database to be deleted, which is usually unrecoverable.
* TRUNCATE deletes all data in the table (non-standard but common SQL command).
* the alter command allows users to modify existing objects in various ways, for example, adding columns to an existing table.
Example:
Create table my_table (
my_field 1 INT，
my_field2 VARCHAR (50)，
My_field3 date is not empty.
Primary key (my field 1, my field 2)
);
Data control
The third set of SQL keywords is Data Control Language (DCL). DCL handles the authorization aspect of data and allows users to control who has the right to view or manipulate the data in the database. Its two main keywords are:
agree
Authorize one or more users to perform an operation or a set of operations on an object.
cancel
Delete or restrict a user's ability to perform an operation or a set of operations.
Example:
Grant SELECT, UPDATE on my_table to one _user and another _user.
other
* ANSI-standard SQL supports double dash,-,as a single-line comment identifier (some extensions also support curly braces or C-style /* comments */ for multi-line comments).
Example:
SELECT * FROM inventory-retrieves everything from the inventory table.
* Some SQL servers allow users to define functions.
Criticism of SQL
Technically, SQL is a declarative computer language for "SQL database". Theorists and some practitioners have noticed that many original SQL features are inspired by the relational model of database management and its tuple calculus implementation, but they violate them. The recent expansion of SQL has achieved relational integrity, but it has also worsened the violations, as stated in the third declaration.
In addition, there are some criticisms about the actual use of SQL:
* Implementations between different vendors are inconsistent and usually incompatible. Specifically, date and time syntax, string concatenation, null values, and case sensitivity usually vary from vendor to vendor.
* This language makes Cartesian connection (connecting all possible combinations) too easy, and when the WHERE clause is typed incorrectly, it will lead to "out of control" result sets. Cartesian connection is rarely used in practice, so an explicit Cartesian keyword may be needed.
* It is also possible to incorrectly construct WHERE when updating or deleting, thus affecting more rows in the table than expected.
* SQL-and its existing relational model-does not provide a standard method to deal with the tree structure, that is, rows recursively refer to other rows in the same table. Oracle provides a "CONNECT BY" clause, and Microsoft provides recursive connection through common table expressions. Other solutions are to use recursive database functions that return rowsets, possibly using PL/PgSQL in PostgreSQL.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Active Server Pages (ASP) is a server-side scripting engine used by Microsoft to dynamically generate web pages. It is listed as an attachment of Internet Information Service (IIS). Various built-in objects make the programming of ASP websites easier. Each object corresponds to a set of common functions used to create dynamic web pages. There are six such built-in objects in ASP 2.0: application, error, request, response, server and session. For example, a Session is a cookie-based session object that maintains variables between pages.
Most ASP pages are written in VBScript, but you can also use the @Language instruction or < script language = "language" runat = "server" >; Grammar. JScript (Microsoft's ECMAScript implementation) is another commonly available language. Perlscript (a derivative of Perl) and other active scripting engines that can be installed as third parties.
InstantASP and ChiliASP are technologies to run ASP without Windows operating system. There are some large open source communities on the WWW, such as ASPNuke, which produce ASP scripts, components and applications that can be used for free under certain license terms.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Adobe Dreamweaver, or Dreamweaver for short, is a web development tool originally created by Macromedia. The original version of the application was used as a simple WYSIWYG HTML editor, but the recent version added significant support for many other web technologies, such as CSS, JavaScript and various server-side scripting frameworks. The software can be used on Mac and Windows platforms, but it can also be run on Unix-like platforms by using simulation software such as Wine. Dreamweaver is currently owned by Adobe Systems, which acquired Macromedia in 2005.
content
characteristic
As a WYSIWYG editor, Dreamweaver can hide the details of the HTML code of pages from users, which enables non-coding personnel to create web pages and websites. A professional criticism of this method is that the file size and HTML code of the generated HTML pages are much larger than they should be, which will lead to poor performance of the web browser. This is especially true because applications make it very easy to create table-based layouts. In addition, some website developers used to criticize that Dreamweaver generated code often did not conform to W3C standards, although this has been greatly improved in recent versions. Dreamweaver 8.0 (the version before 9.0 recently released in CS 3) did not perform well in the Acid2 test developed by the Web standard project. However, Macromedia has added support for CSS and other methods in subsequent versions of the application, which can lay out pages without tables, and can transform tables into layers, and vice versa.
Adobe Dreamweaver CS3
Adobe Dreamweaver CS3
Dreamweaver allows users to preview websites in many browsers, provided that these browsers are installed on their computers. It also has some site management tools, such as being able to find and replace text or code lines by any parameter specified throughout the site, and templating functions for creating multiple pages with similar structures. The Behaviors panel also supports using basic JavaScript without any coding knowledge.
With the emergence of MX version, Macromedia incorporated dynamic content creation tools into Dreamweaver. In the spirit of HTML WYSIWYG tool, it allows users to connect to databases (such as MySQL and Microsoft Access) and use scripting technologies (such as Active Server Pages (ASP), ASP.NET, ColdFusion, JavaServer Pages (JSP), PHP, etc.) to filter and display content without any programming experience. Dreamweaver 8.0 also supports WYSIWYG XSLT editing. Alternative solutions for web database application development are Alpha Five and FileMaker.
Dreamweaver can use "extensions"-small programs (usually HTML and JavaScript) that any web developer can write. Extensions provide additional functions for people who want to download and install them. Dreamweaver is supported by a huge community of extension developers, who provide extensions (both commercial and free) for most web development tasks, from simple flipping effects to full-featured shopping carts.