About SQL Server syntax and application of some reference

  First, the definition of information ddl (data definition language) 
  Information on the language refers to the format and information form under the definition of the language, he is to establish the database each time to face the first time, Jufan information on what form the relationship, what form the primary key field, Forms and forms the relationship between the reference and so on, are necessary at the beginning of good planning. 

  1, built form: 
  create table table_name ( 
  column1 datatype [not null] [not null primary key], 
  column2 datatype [not null], 
  …) 
  Description: 
  datatype – is the format information, see Table. 
  nut null – Can information available to allow the (not yet have information to fill). 
  primary key – this table is the primary key. 

  2, changes form alter table table_name 
  add column column_name datatype 
  Description: Add a field (not delete a field syntax. 
  alter table table_name 
  add primary key (column_name) 
  Note: change the definition of the table in a field located mainly bond. 
  alter table table_name 
  drop primary key (column_name) 
  Note: the primary key to delete the definition. 

  3, the index create index index_name on table_name (column_name) 
  Description: a form of indexing the field to increase the speed of enquiries. 

  4, delete drop table_name 
  drop index_name 

  Second, the information forms datatypes 
smallint
  16-bit integer. 
interger
  32-bit integer. 
  decimal (p, s) 
  p s size and precise value of 10 into the rounded, precise value of p means all of a few (digits) of size, s is that a few number of decimal places.    If not specified, the system will be set p = 5; s = 0. 
float
  32-bit real. 
double
  64-bit real. 
  char (n) 
  n length of the string, n can not be more than 254. 
  varchar (n) 
  Length is not fixed and its maximum length of the string n, n can not be over 4,000. 
  graphic (n) 
  And char (n), but the unit is the word yuan double-bytes, n can not be more than 127.    This pattern is the word yuan to support the length of the font, for example, in the text. 
  vargraphic (n) 
  Variable-length and its maximum length for the two-character string n, n can not be over 2,000. 
date
  Contains year, month, date. 
time
  Includes hours, minutes and seconds. 
timestamp
  Contains year, month, day, hours, minutes and seconds, one in 1,000 seconds. 

  Third, information operations dml (data manipulation language) 
  After the definition of good information is information the next operation.    Information on the operation nothing more than additional information (insert), for information (query), changes to information (update), delete information (delete) four modes, the following were introduced to their script: 

  1, additional information: 
  insert into table_name (column1, column2 ,…) 
  values (value1, value2, …) 
  Description: 
  1. If there is no system will be designated column in the form of a column sequence completed. 
  2. Field of information form and the information must be filled by the anastomosis. 
  3.table_name also be a landscape view_name. 

  insert into table_name (column1, column2 ,…) 
  select columnx, columny, … from another_table 
  Note: You can also go through a sub-query (subquery) to fill in other forms of information. 

  2, for information: 
  Basic query select column1, columns2, … 
  from table_name 
  Description: table_name to the specific fields of information to select * Full list 
  from table_name 
  where column1 = xxx 
  [and column2> yyy] [or column3 <> zzz] 
  Description: 
  1. '*' That all of the fields are listed. 
  2.where after the join-to meet the requirements of the information listed. 

  select column1, column2 
  from table_name 
  order by column2 [desc] 
  Note: order by the designated field to do a sort, [desc] is Congdadaoxiao with, if not specified, it is from small to large combinations for combination with enquiries by the query is not only a source of information A single form, but the more than one form can be the result. 
  select * 
  from table1, table2 
  where table1.colum1 = table2.column1 
  Description: 
  1. Query one of two forms column1 value of the same information. 
  2. Of course, compare the two forms of mutual field, the information must be the same shape. 
  3. A complex for its use to the table may be many months. 

  Integrity query: 
  select count (*) 
  from table_name 
  where column_name = xxx 
  Description: 
  Meet the requirements for a total of several information. 
  select sum (column1) 
  from table_name 
  Description: 
  1. Calculate the sum of the selected field must be a few digital form. 
  2. There are avg () is calculating the average, max (), min () calculation of the minimum largest integrated query. 
  select column1, avg (column2) 
  from table_name 
  group by column1 
  having avg (column2)> xxx 
  Description: 
  1.group by: to column1 for a group of column2, on average, must be calculated and avg, sum, such as integrated for use with the keywords. 
  2.having: must be used together as a group by Integrity restrictions. 

  Complex queries select * 
  from table_name1 
  where exists ( 
  select * 
  from table_name2 
  where conditions) 
  Description: 
  1.where the conditions can be another's query. 
  2.exists here is there or not. 
  select * 
  from table_name1 
  where column1 in ( 
  select column1 
  from table_name2 
  where conditions) 
  Description: 
  1. In the back then was a collection that column1 exist inside the collection. 
  2. Select out the information form must be consistent with column1. 

  Other enquiries select * 
  from table_name1 
  where column1 like 'x%' 
  Note: like to be behind and the 'x%' x echoed that to begin with the string. 
  select * 
  from table_name1 
  where column1 in ( 'xxx', 'yyy',..) 
  Note: in the next behind a collection that column1 exist inside the collection. 
  select * 
  from table_name1 
  where column1 between xx and yy 
  Note: between the values that column1 between xx yy and between. 

  3, changes: 
  update table_name 
  set column1 = 'xxx' 
  where conditoins 
  Description: 
  1. To change a column set its value is' xxx '. 
  2.conditions is to be consistent with the conditions, if not the table where the entire field that will all be changed. 

  4, delete information: 
  delete from table_name 
  where conditions 
  Description: delete the information meet the requirements. 

  Note: If on the back where the conditions include the date, the different databases have different expressions.    As follows: 
  (1) If the access database, is: where mydate> # 2000-01-01 # 
  (2) If the oracle database, is: where mydate> cast ('2000-01-01 'as date) 
  Or: where mydate> to_date ('2000-01-01 ',' yyyy-mm-dd ') 
  In delphi written in: 
  thedate ='2000-01-01 '; 
  query1.sql.add ( 'select * from abc where mydate> cast ('+''''+ thedate +''''+' as date)'); 

  If you compare the time-date, compared with: 
  where mydatetime> to_date ('2000-01-01 10:00:01 ',' yyyy-mm-dd hh24: mi: ss') 

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • DZone
  • Netvouz
  • NewsVine
  • Technorati

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button