sql

All of the SQLs call to be "the structure turn search language(the Language of the Structured Query)", at the earliest stage of is an IBM saint invite Se research laboratory is its relation database management system SYSTEM R development of a kind of search language, it of the precursor was a SQUARE language.SQL language the structure be simple and direct, the function is strong, in brief easy to learn, so since the IBM company released in 1981, SQL language, got an extensive application.Regardless be like Oracle, Sybase, Informix now, SQL server these large database management system, still keep being like Visual Foxporo, PowerBuilder these tiny and on board in common use database development system, all support SQL language as to search language.
The SQL is deluxely a non- process to turn plait distance language and allow a customer to work on the high data structure.He doesn't request a customer appointed logarithms according to of deposit a method, also don't need a customer an understanding concrete data to deposit a way, so have totally different first floor the different database system of the structure can use same SQL language as a data an importation and manage of connect.It gathers conduct and actions to manipulate object by record, all SQL language sentence's accepting to gather is an importation and return to gather Be an exportation, this kind of gathers characteristic to allow a SQL lexical output Be another importation with lexical SQL, so SQL language can with the Qian set, this makes him have tremendous vivid and strong function and need in other languages under the most condition a procedure carry out of 1 alone affairs need a SQL language sentence can attain a purpose and this also mean that the useful SQL language can write a very complicated language sentence.
The SQL also is a database document in the meantime the file extension of the format.
The SQL language includes 4 parts:
The data searches language(SELECT language sentence)
The data manipulates language(INSERT, UPDATE, DELETE language sentence)
The data defines language(like CREATE, DROP etc. language sentence)
The data controls language(like COMMIT, ROLLBACK etc. language sentence)
Draw from "http:// zh.wikipedia.org/wiki/SQL"The SQL(the Language of the STructured Query) is a kind of database search and program design language and use the Wu access data and the connection type database system of the search, renewal and management.ASCII bureau(ANSI)'s standardizing organization(ISO) with nations has already drawn up SQL standard.The ANSI is an American industry and business group to organize, development the United States of business and communication standard.The ANSI also is one of the member of ISO and the Commission(IEC) of the International Electrotechnical in the meantime.The ANSI releases the American standard which corresponds with international standard organization.In 1992, ISO and IEC released SQL international standard and be called SQL-92.The ANSI immediately releases of correspond standard is the ANSI SQL-92.The ANSI SQL-92 is sometimes called ANSI SQL.Though the SQL edition of different connection type database usage contains some differences, the majorities all follow ANSI SQL standard.The expand of the usage ANSI of the SQL Server SQL-92 gather, be called T-SQL and it follows the standards SQL-92s of ANSI establishment.
The SQL language includes two kinds of main program design language the statement type of the category: The data definition language(DDL) operates language(DML) with data.Underneath we will introduce these two types of languages.
DDL
The DDL uses Wu definition and management article, for example database, data watch and examine form.( chapter 18 will explain to what is meant by examining form)The DDL statement type usually includes CREATE, ALTER and DROP order of each article.Give examples to say, CREATE TABLE, ALTER TABLE and DROP TABLE these illustrate a type and then can use to build up new data form, modify its attribute(if add or delete data to go) and delete data form etc., underneath we will introduce one by one.
The statement type of CREATE TABLE
Use DDL to build up a the example data which is named Customer_Data form in the MyDB database, the example of this behind we will use to this data form.Such as front say, the CREATE TABLE statement type can be use to build up data form.This example data watch is defined into four data to go and as follows show:
Use MyDBCREATE TABLE Customer_Data(customer_id smallint,first_name char(20),last_name char(20),phone char(10))GOThis statement type can produce Customer_Data data form and this data form will have been empty until the data is stuffed with data form.
The statement type of ALTER TABLE
The ALTER TABLE illustrates a type to use to a definition and attribute of change the data form.In the example of underneath, we make use of to add middle_initial data to go in the Customer_Data data form that the ALTER TABLE is having already exist.
ALTER TABLE Customer_DataADD middle_initial char(1)GOThe definition of data form included five data to go now, not previous of four data go.Close more details of the Wu usage ALTER TABLE, please read chapter 15.
The statement type of DROP TABLE
The DROP TABLE illustrates a type to use to delete data form definition and all data, index, trigger procedure, condition stipulation and data form of legal power.To delete our Customer_Datas data form can make use of the following order:
DROP TABLE Customer_DataGOClose Wu DROP TABLE to illustrate the detailed contents of type, please read chapter 15.
DML
The DML makes use of INSERT, SELECT, UPDATE and DELETE etc. to illustrate a type to operate the data that the database article include.
The INSERT statement type
The INSERT statement type is use to insert one row data in the data form or examine the form.For example, if want to add a customer in the Customer_Data data the form, can use similar following INSERT to illustrate a type:
INSERT INTO Customer_Data(customer_id, first_name, last_name, phone)VALUES (777, "Frankie", "Stein", "4895873900")Please watch for the second data in type of SQL statement goes name detailed list, the data goes appellative order of sequence to come to a decision data number and will be put on the detailed list which data go at.Give examples to say, the first first data that data number will be put in the detailed list to list goes customer_id, the second data number to put to go in the second data and push according to this kind.From Wu we while build up data form, the definition data data line stuff with number of order of sequence is homology to now, so we need not mean to settle column name especially.We can illustrate a type to replace with the following INSERT:
INSERT INTO Customer_DataVALUES (777, "Frankie", "Stein", "4895873900")
Notice
If usage the INSERT of this kind of form illustrate type, but drive insert of number order of sequence ascend with build up data form dissimilarity, the number will be put to go into the false data.If the type of data out of accordance with definition then will receive an error message.
--------------------------------------------------------------------------------
The SELECT statement type
The SELECT statement type uses to the data in the inspectional data form, but which datas drive inspectional from list of the data go to decide with the WHERE clause in the statement type.For example, inspect from before the Customer_Data data form build up customer_id and first_name data go of data, and the data which think that taking out the first_name data in the each row goes to be worth for the Frankie, that 麼 can make use of following SELECT to illustrate a type:
SELECT customer_id, first_name FROM Customer_DataWHERE first_name = "Frankie"If there is 1 row matching the standard within type of the SELECT statement, the result then will run as follow:
customer_id first_name------------- ------------777 FrankieThe UPDATE statement type
The UPDATE statement type is use to renew or changes 1 row or many values in the rows.For example, an is called the customer of Frankie Stein to want to change his surname as Franklin in the record, can use following UPDATE to illustrate a type:
UPDATE Customer_DataSET first_name = "Franklin"WHERE last_name = "Stein" and customer_id= 777We join customer_id item in the WHERE clause to making sure an other the customer who is called Stein and can't be influenced － to only have the customer_id as 777 customers and the surname will have a change.
--------------------------------------------------------------------------------
Explain
When your usage UPDATE illustrates a type, make sure to provide a full sieving condition in the WHERE clause, so that just will not pay no attention ground to change some datas that shouldn't change.
--------------------------------------------------------------------------------
The DELETE statement type
The DELETE statement type is use to delete 1 row or many data of rows in the data form and you can also delete all data rows in the data form.To delete all rows from the Customer_Data data the form you can make use of the following statement type:
DELETE FROM Customer_DataOrDELETE Customer_DataThe data form name ex- FROM key word is optional in the DELETE statement type.In addition, these 2 illustrate a type completely same.
Want to delete customer_id data to go from the Customer_Data data the form of be worth small Wu 100 rows, can make use of following illustrate a type:
DELETE FROM Customer_DataWHERE customer_id < 100We have already quickly browsed now the SQL provide of DDL and the DML statement type, connect Zhao, the underneath will introduce T-SQL.
Five kinds of data types in the SQL
The synopsis describes five kinds of data types in the SQL: character list type, text originally type, number type, logic type and date type
Character list type
VARCHAR VS CHAR
This difference of VARCHAR type and CHAR type data is small, but count for much.All of them are used for storing string length less than 255 character lists.
If you is the BIll GAtES of the importation data in 40 VARCHAR type word segments of character lists toward a length.When you will take out this data from this hereafter, you take out of data its length is ten lengths of the Bill Gates of the character list-strings. Now if you input a length of strings in 40 CHAR type word segments of character lists, is so be you to take out a data, the data length take out will be 40 character lists.The behind of string would drive the blank space of the additional surplus.
When your establishment own station order, you will discover the usage VARCHAR type word segment want to be more convenient than CHAR type word segment of many.While using 1, you don't need for the blank space which shears the surplus in your data but worry.
The another outstanding advantage of VARCHAR type word segment is that it can compare 1 to take up less memory and hard drive space.When you of the database be very big, this kind of memory and disk space of economical will become count for much
Text originally type
TEXT
Use a text originally a type a data, you can deposit more than 2,000,000,000 character lists strings.When you need to save the character list of big string, should use a text originally type data.
Notice a text originally a type data to have no length, but ascend one section in the character list type data speak have length.A text originally the type data within word segment usually wants Yao in order to get empty, want the Yao to be very big.
When you are from the HTML fORM communicate officially this editor frame(TEXTAREA) more collections data, you should save the information of collections at the text originally type word segment in.But, no matter when it is, as long as you can avoid using a text originally type word segment, you should obsolescent.Text originally type word segment since big wait a minute, abuse text originally type the word segment will make the server speed become slowly.Text originally the type word segment will also eat up a great deal of disk space.
Once you to text originally the type inputted any data(even is empty to be worth) in the word segment, will have the space with 2 Ks drive the automatic allotment is to that data.Unless the deletion should record, otherwise you can't take back this part of saving spaces.
Number type
The SQL supports many different number type a data.You can save integral INT, fraction NUMERIC and be few MONEYs with money.
INT VS SMALLINT VS TINYINT
Their differentiations are just character list lengths:
INT type watch few scopes of the data from-2, 147, 483, 647-2, 147, 483, 647 integrals
The SMALLINT type data can save from-32768-32768 integrals
Word segment of TINYINT type can save from 0-255 integrals, can't use to store minus quantity
Usually, for saving space, should use minimum whole data possibly.A TINYINT type data takes up a word stanza;An INT type data takes up four word stanzas.This looks to seem to difference not big, but in the ratio the bigger form, word stanza number of the growth be very quick.On the other hand, once you have already established a word segment modify it is very difficult.Therefore, on the safe side, you should predict the following, a word segment number that need to be save biggest probably is much big, then choose an appropriate data type.
NUMERIC
Have more controls to the data that the word segment deposit for the sake of the ability, you can use a data to mean integral part and fraction part of one piece in the meantime.The NUMERIC type data makes you be able to mean a very big number-the ratio INT type the data want to greatly have to be many.A NUMERIC type word segment can save from-1038-1038 numbers in the scopes.The NUMERIC type data still makes you be able to mean a number of have the fraction part.For example, you can saving fraction in the NUMERIC type word segment 3.14.
While defining a NUMERIC type word segment, your demand specifies the size of the integral part and the size of the fraction part in the meantime.Such as:MUNERIC(23,0)
The integral of a NUMERIC type data part of biggest can have 28, the number of fraction part have to be small in or equal the number of integral part, fraction part can be zero.
MONEY VS SMALLMONEY
You can use INT type or NUMERIC type data come saving money number.But, exclusively there are moreover two kinds of data types useding for this purpose.If you hope you of the net order ability Zheng much money and you can use a data.If you of the greed be not big and you can use a data.The MONEY type data can save from-922, 337, 203, 685, 477.5808-922, 337, 203, 685, 477.5807 money numbers.If you need save to return big amount of money than this, you can use a data.
SMALLMONEY type data can save from-214, 748.3648-214, 748.3647 money numbers.Same, if can of words, you should replace a data to save space with the SMALLMONEY type.
Logic type
BIT
If the your usage indirect election frame( CHECKBOX) is from the web page collection information, you can save this information in the BIT type word segment.BIT type word segment can take two values:0 or 1.
Beware of, after you establish like a form, you can't to add 1 in the form.If you intend to include a BIT type word segment in a form, you have to complete while establish form.
Date type
DATETIME VS SMALLDATETIME
The date scope that can save of the word segment of a DATETIME type is from January 1, 1753 ten cents second is to December 31, 9999 end ten cents second.
If you don't need to overlay date and time of so big scope, you can use a data.It equally uses with DATETIME type data, only it can mean of date and time scope ratio DATETIME type the data be small, and not equal to DATETIME type data precision.The word segment of a SMALLDATETIME type can save from January 1, 1900 to the date in June 6, 2079, it can be accurate to arrive second.
DATETIME type word segment before you input date and time not include an actual data, know this was important.