Tutorials to .com

Tutorials to .com » Jsp » Database » SQL Syntax Reference

SQL Syntax Reference

Print View , by: iSee ,Total views: 5 ,Word Count: 1432 ,Date: Sat, 18 Apr 2009 Time: 8:37 AM

First, information on the definition of ddl (data definition language)
Data set refers to information on the language of the definition of the format and form of language, he is every time the database is necessary to establish first of all have to face when, all data points which form the relationship, what form the primary key field, forms and form relations between the reference and so on are all in the beginning of the planning that must be good.

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

2, change the form
alter table table_name
add column column_name datatype
Description: add a field (do not delete a field syntax.
alter table table_name
add primary key (column_name)
Description: Change the definition of a table to set up a field-oriented keys.
alter table table_name
drop primary key (column_name)
Note: the definition of the primary key to delete.

3, index
create index index_name on table_name (column_name)
Description: a form of indexing fields in order to increase the rate of inquiries.

4, delete
drop table_name
drop index_name

Second, the information form datatypes
16-bit integer.
32-bit integer.
decimal (p, s)
s exact p value and the size of the decimal integer value of p is the precision of all there are several number (digits) the size of the value, s is used to indicate decimals
A few number of points. If not specified, the system will be set to p = 5; s = 0.
32-bit real number.
64-bit real number.
char (n)
n the length of the string, n can not be more than 254.
varchar (n)
Fixed length and its maximum length n of the string, n can not be more than 4000.
graphic (n)
And char (n) the same, but the unit is the yuan word double-bytes, n can not be more than 127. This form is for
The word length of the support element of the font, such as Chinese characters.
vargraphic (n)
Variable-length and the maximum length of n characters of the two-string, n can not be more than 2000.
Includes year, month, date.
Includes hours, minutes, seconds.
Includes year, month, day, hour, minute, second, thousandths of seconds.

Third, information operations dml (data manipulation language)
After the definition information is information on the next operation. Information on the operation of additional information only (insert), inquiries (query), to change the data (update), delete data (delete) the four models, the following were to introduce their syntax:

1, for additional information:
insert into table_name (column1, column2 ,...)
values (value1, value2, ...)
1. If there is no designated column system will be implemented according to the form fill in the fields of information order.
2. The information fields and fill in the form of information must be in line.
3.table_name landscape can also be view_name.

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

2, query information:
Basic inquiries
select column1, columns2, ...
from table_name
Description: the table_name field specific information on all listed
select *
from table_name
where column1 = ***
[and column2> yyy] [or column3 <> zzz]
1. '*' Mean that all fields are listed.
Followed by 2.where conditional access to information in line with the conditions listed.

select column1, column2
from table_name
order by column2 [desc]
Description: order by is specified to do a sort field, [desc] is the smallest order, if not specified, it is from small to large

Combination of query is a query refers to sources of information are not only a single form, but more than one joint
Forms will be able to get the results.
select *
from table1, table2
where table1.colum1 = table2.column1
1. Query two tables in which the same data value of column1.
2. Of course, compared the two forms of field data must be the same shape.
3. A complex about their use of the forms may be many months.

Integrated query:
select count (*)
from table_name
where column_name = ***
Query the information in line with the conditions there are a few.
select sum (column1)
from table_name
1. To calculate the sum of the selected field must be a few number of patterns.
2. In addition there are avg () is the average, max (), min () calculation of the minimum value of the largest integrated query.
select column1, avg (column2)
from table_name
group by column1
having avg (column2)> ***
1.group by: to a group of column1 to column2 average must be calculated and avg, sum, such as integration of the keyword query
Used together.
2.having: must be used together as a group by integrity constraints.

Complex queries
select *
from table_name1
where exists (
select *
from table_name2
where conditions)
The conditions can be 1.where is another of the query.
In this 2.exists refers to the presence or absence.
select *
from table_name1
where column1 in (
select column1
from table_name2
where conditions)
1. In the back of the following is a collection of collections that exist inside column1.
2. Select information from the form must be consistent with column1.

Other inquiries
select *
from table_name1
where column1 like 'x%'
Description: like to be and behind the 'x%' to echo that as at the beginning of the string x.
select *
from table_name1
where column1 in ('***',' yyy',..)
Note: in the back of the following is a collection of collections that exist inside column1.
select *
from table_name1
where column1 between xx and yy
Note: between the value that column1 between between xx and yy.

3, change the data:
update table_name
set column1 ='***'
where conditoins
1. To change a field value is set'***'。
2.conditions is to be consistent with the conditions, if not the entire table of where the fields would all be changed.

4, the deletion of information:
delete from table_name
where conditions
Description: to delete the information in line with the conditions.

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

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

Jsp And Database Articles

Can't Find What You're Looking For?

Rating: Not yet rated


No comments posted.