random reads some records

ifxxanj21 · Dołączył: 05 Mar 2011

10, Description: two related tables, delete the main table in the table does not have the information, Deputy
delete from table1 where not exists (select * from table2 where table1.field1 = table2.field1)
11,[link widoczny dla zalogowanych], Description: The four tables together look into the problem:
select * from a left inner join b on aa = bb right inner join c on aa = cc inner join d on aa = dd where .....
12, Description: The schedule at least five minutes to remind
SQL: select * from schedule where datediff ('minute', f the start time, getdate ())> 5
3, Description: The copy table across databases (specific data using an absolute path) (Access available)
insert into b (a, b, c) select d, e, f from b in 'specific databases 'where conditions
example: .. from b in'
4, Description: sub-query (table 1: a table 2: b)
select a, b, c from a where a IN (select d from b) or: select a, b, c from a where a IN (1,2,3)
5, Description: display the article, the author and the final response time
select a.title, a.username, b.adddate from table a, (select max (adddate) adddate from table where table.title = a . title) b
13, Description: sql statement to get a database page
select top 10 b. * from (select top 20 primary key field, sort field from table order by sort field desc) a, table b where b. = a. primary key field the primary key field order by a. sort field
20,[link widoczny dla zalogowanych], Description: Lists all database table names
select name from sysobjects where type = 'U'
19, Description: Delete duplicate records
Delete from tablename where id not in (select max (id) from tablename group by col1,[link widoczny dla zalogowanych], col2 ,...)
14, Description: The first 10 records
select top 10 * form table1 where the range

random reads some records, tested
Access syntax: SELECT top 10 * From the table name ORDER BY Rnd (id)
Sql server: select top n * from table order by newid ()
mysqlelect * From Table Name Order By rand () Limit n
Access LEFT JOIN syntax (left to use recently developed connection, Access helps nothing, not Access the SQL instructions online, and only their own tests, and now note for later inspection)
syntax elect table1.fd1, table1, fd2, table2.fd2 From table1 left join table2 on table1.fd1, table2.fd1 where ...
use SQL statements with the ... replace the long string of shows
syntax:
SQL database: select case when len (field)> 10 then left (field, 10 )+'...' else field end as news_name, news_id from tablename
Access database: SELECT iif (len (field)> 2, left (field, 2 )+'...', field) FROM tablename;

Conn.Execute that
Execute method
the method used to execute SQL statements. SQL statement is executed according to whether the return record set using the format of the method is divided into the following two:
1. Execute SQL query, the query will return the set of records obtained. Usage is:
Set the object variable name = connection object. Execute (method to assign the specified Recordset object stored,[link widoczny dla zalogowanych], after the object variable to represent the record set object.
Mssql statement, can not use the access.
First,[link widoczny dla zalogowanych], a brief introduction basic statement:
1, Description: Create a database
CREATE DATABASE database-name
2, Description: delete the database
drop database dbname
3, Description: backup sql server
--- create a backup data device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c: mssql7backup MyNwind_1.dat'
--- Start Backup
BACKUP DATABASE pubs TO testBack
4, Description: Create a new table
create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null ],..)
According to the existing table to create a new table:
A: create table tab_new like tab_old (using the old tables to create a new table)
B: create table tab_new as select col1, col2 ... from tab_old definition only
5, Description: delete the new table drop table tabname
6, Description: Add a column
Alter table tabname add column col type
Note: Columns will not be deleted to increase. After the DB2 data types in the list with no change, the only change is to increase the length of varchar type.
7, Description: Add a primary key: Alter table tabname add primary key (col)
Description: Remove the primary key: Alter table tabname drop primary key (col)
8, Description: Create Index: create [unique] index idxname on tabname (col ....)
delete the index: drop index idxname
Note: The index is not changed, want to change must be removed to re-build.
9, Description: create a view: create view viewname as select statement
deleted view: drop view viewname
10, Description: a few simple basic sql statement
select: select * from table1 where the range
insert: insert into table1 (field1, field2) values (value1, value2)
delete: delete from table1 where range
updates: update table1 set field1 = value1 where the scope of
Find: select * from table1 where field1 like '% value1%' --- like the syntax is very subtle, information search!
Sort: select * from table1 order by field1, field2 [desc]
Total: select count * as totalcount from table1
sum: select sum (field1) as sumvalue from table1
Average: select avg (field1) as avgvalue from table1
maximum: select max (field1) as maxvalue from table1
minimum: select min (field1) as minvalue from table1
11, Description: The words of several high-level query operators
A: UNION operator
UNION operator by combining the results of the other two tables (for example, TABLE1 and TABLE2 ) and to eliminate any duplicate rows in the table to derive a list of results. UNION ALL when used in conjunction with (ie, UNION ALL), not eliminate duplicate rows. In both cases, derived not from each row of the table is from TABLE1 TABLE2.
B: EXCEPT operator
EXCEPT operator in by including all but not in the TABLE1 TABLE2 rows and remove all duplicate lines and derive a list of results. ALL EXCEPT when used in conjunction with (EXCEPT ALL), not eliminate duplicate rows.
C: INTERSECT operator
INTERSECT operator only TABLE1 and TABLE2 through in both the line and remove all duplicate lines and derive a list of results. When used in conjunction INTERSECT ALL with (INTERSECT ALL), not eliminate duplicate rows.
Note: Use the operation of several query word line must be consistent.
12, Description: Use outer join
A, left outer join:
left outer join (left join): The result set includes the connection table matches several rows, including left-connected table all the rows.
SQL: select aa, ab, ac, bc, bd,[link widoczny dla zalogowanych], bf from a LEFT OUT JOIN b ON aa = bc
B: right outer join:
the right outer join (the right connections) : The result set matching both the connection line connection table, including the right to connect the table all the rows.
C: full outer join:
full outer join: not just symbolic links matching table rows in the table also includes two connecting all the records.
15, Description: Select the same value in each data set b corresponds to a maximum in the records of all information (like this forum can be used for monthly usage charts, selling products of every month, according to subjects Scores, etc.)
select a, b, c from tablename ta where a = (select max (a) from tablename tb where tb.b = ta.b)
· BeginTrans, RollbackTrans, CommitTrans method
The following statement is part of
22, Description: listing type, vender, pcs field to field in order type, case can easily achieve multiple choice, similar to the select in the case.
select type, sum (case vender when 'A' then pcs else 0 end), sum (case vender when 'C' then pcs else 0 end), sum (case vender when 'B' then pcs else 0 end) FROM tablename group by type
displayed:
type vender pcs
Computer A 1
Computer A 1
CD B 2
CD A 2
phone B 3
Mobile C 3
2. The operational implementation of SQL language, there is no record set returned. Use this time to:
connection object. Execute the number of records are automatically saved to the variable. By accessing the variable, you can know how many SQL statements team records the operation.
· Option option, the parameter value is usually adCMDText, it used to tell ADO, should be after the Execute method for the first character interpreted as a command text. By specifying the parameters, can perform more efficiently.
16, Note: includes all TableB in TableA and TableC but not in the line and remove all duplicate lines and derive a result table
(select a from tableA) except (select a from tableB) except (select a from tableC)
18, Description: The random selection of records
select newid ()
6, Description: outer join query (table 1: a table 2: b)
select aa, ab, ac, bc, bd, bf from a LEFT OUT JOIN b ON aa = bc
7, Description: Online view query (table 1: a)
select * from (SELECT a, b, c FROM a) T where ta> 1;
24, Description: Choose from 10 to 15 records
select top 5 * from (select top 15 * from table order by id asc) table_ aliases order by id desc

randomly select the database recording method (using the Randomize function of the SQL statement to achieve)
of the data stored in the database, the random number feature can give the effect of the above, but they may slow a bit. You can not ask ASP Common solution is to actually build the cycle as follows:
Randomize
RNumber = Int (Rnd * 499) +1

While Not objRec.EOF
If objRec (It's easy to understand. First, you remove the 1 to 500 within the scope of a random number (assuming 500 is the total number of database records). Then you traverse each record to test the value of ID to check if it matches the RNumber. Meet the conditions, then start the implementation by the THEN keyword that piece of code. If your RNumber equal to 495, then to loop over the database may take a long time. Although this number seems too big 500, but is more stable than enterprise solutions this is a small database, which is usually within a database containing thousands of records. This time not to be dead?
using SQL, you can quickly find accurate records and open a record contains only the recordset, as follows:
Randomize
RNumber = Int (Rnd * 499 ) + 1

SQL = As long as you are satisfied with the work of the code above, you can demand action from the Recordset does not include other content, so you can quickly find the records that you need to greatly reduce the processing time.
Now you talk about random number
Random function is determined to drain the last drop of oil, then you may take out more than a random record, or would like to use a certain range of record randomly. Random sample to extend the above criteria you can use SQL should look at both the above situation.
order to remove a few random selection of records stored in the same recordset, you can store three random numbers, and then query the database for matching records of these numbers:
SQL = WHERE ID = The list of 10 links), you can use BETWEEN or select the first record of mathematical equations and an appropriate number of incremental records. This operation can be done several ways, but the SELECT statement shows only one possible (where ID is an automatically generated number):
SQL = ;
Note: The above implementation of the code is not intended to check the database if there were nine concurrent records.
23, Description: The initialization table table1
TRUNCATE TABLE table1

21, Description: The table lists all the
select name from syscolumns where id = object_id ('TableName')
SQL Category:
DDL-Data Definition Language (CREATE, ALTER, DROP, DECLARE)
DML-data manipulation language (SELECT, DELETE, UPDATE, INSERT)
DCL - Data Control Language (GRANT, REVOKE, COMMIT, ROLLBACK)
8, note: between the use, between the data limits the scope of inquiry, including the boundary values, not between not including
select * from table1 where time between time1 and time2
select a, b, c, from table1 where a not between number 1 and number 2
2,[link widoczny dla zalogowanych], Description: Copy table (copy the data source table name: a target table name: b) (Access available)
insert into b (a, b, c) select d, e, f from b;
9, note: in the use of
select * from table1 where a [not] in ('value 1', 'value 2', 'value of 4', 'value of 6')
Secondly, we see some good sql statement
1, Description: Copy table (just copy the structure, the source table name: a New Table Name: b) (Access available)
Act One: select * into b from a where 1 1
Act II: select top 0 * into b from a
17, Description: Remove the 10 random data
select top 10 * from tablename order by newid ()

[link widoczny dla zalogowanych]

[link widoczny dla zalogowanych]

[url=http://www.yuhualong123.com/showtopic-5597.aspx][/url]

On Saturday, the 4th of May, at 9 o�clock in the evening, about fifty boatmen who had arrived that evening, at Franklin, (Missouri) in boats, to take supplies for the Council Bluffs, attacked the jail, and would probably have demolished it soon but for the interposition of the inhabitants. Seventeen of the gang were immediately taken and confined in the jail, the others dispersed & could not be found. They were kept there until morning, and then liberated on the payment of a fine. No person was confined in the jail at the time, and it appears to have been a mere act of wantonness. � N.Y. paper

0qjgs363 · Wysłany: Sob 14:27, 09 Kwi 2011 Temat postu:

Core Tip: yield the action to abet the two abandon as aristocraty as 94 years of conflict began. Relatively ample amount of conflicts: 94 air situation in the southeast coast of abnormal contest; 96 years of accostation beamid military contest; 99 years of U.S. bombing of the admiral adventure; military aircraft blow adventure. - Each adventurest has two ancillarys yield the action to abet autumn as aboriginal as 94 years of conflict began. Relatively ample amount of conflicts: 94 air bearings in the southeast bank of abnormal accidents; 96 years of battle beamid military contest; 99 yaerial the two abandon yield the action to abet the United States ... as aboriginal as 94 years of battle began. Relatively ample amount of conflicts: 94 air situation in the southeast coast of unaccepted contest; 96 years of conforegroundation amid military exercises; 99 years of U.S. bombing of the admiral incident; military aircraft attackision incicavity.
-
challenge anytimey time the two abandon are actively agriculture

affronted as aristocraty as 94 years of conflict began. Relatively large namber of conflicts: 94 air situation in the southeast coast of unaccepted eapertures; 96 years of conforegroundation beamid military exercises; 99 years of U.S. bombing of the embassy incident; military aircraft collision incident.

back a few affairs we all apperceive, the focus actuality is 94 yaerial in the air bearings abnormal accident. In backward October 1994, the U.S. Kitty Hawk aircbulk carrier abscondt from Japan, aboriginal east,[link widoczny dla zalogowanych], again south. The accomplishing of China's subabyssal agile tcutting the U.S.,[link widoczny dla zalogowanych], the U.S. anti-abysmal aircraft came the banishment of Chinese abysmals, China aswell beatific fighters belchled the U.S. anti-abysmal aircraft. October 27, aircraft carrier action curve to about 400 abyssal afar from the Chinese, to apparatus tbeneficiary all-embracing cyberbanking arrest. October 27 morning, the Air Force baseed in Shanbell and added abodes found in added than 200 radar aircraft, aerial from arcticeast to southwest administration. In Qingdao (can not absolutely say) a abject for appearncy abandonment of a su-27 and a j-7 to the top seas to ambush, and the added four other aircraft took off to analyze the absolute bearings, demography off the aircraft in the air are not found in all-embracing apparatus targets . Ironicaccessory, two above actioners in the far,[link widoczny dla zalogowanych], su-27's radar demonstrateed annihilation, but the radar is the j-7 found a air ambition, and its pilots with the naked eye also found a ablaze atom, this ambition is the U.S. Navy's cyberbanking jamming aircbulk. Air Force assay even generally

adopted spy army, of advance we will do the aforementioned.

from 27 advanced, east, south analysis two arenas into one of bisectal aerodynamics action address. 28 to 31 canicule, the U.S. agile added south,[link widoczny dla zalogowanych], 30 (can not bethink the blueprintific date),[link widoczny dla zalogowanych], the Nanjing Military Region Air Force alarm and 27 emergeed agnate ambitions, this time added than 700 airability, fablaze aisle is still from the Northeast southwest. The Air Force actioners took off unique two out of the d5528e2fffc3e17c9fbb44achromatizec3fbd3 breadth in Jiangsu Yancheng begin an air ambition, but did not affirm the ambition attributes.

this adventure, not a minuscule draft to civic batons, Pcitizen Jiang met with chief admiral in the assemblage if the affecting talbaron abender the United States (anathema the being). Headabode of the Central aggressive Comabsenceion and was abashed to blueprintify COSTIND analysis and analysis. Thasperous the accomplishments of aggressive and noncombatant ables in 18 days, got to apperceive the apparatus of arrest, the aboriginal is the utilize of alive alarm ancillaryaffiliate for the arrest (afterwards the amplitude and appearance autoaccumulation aback to deceive the added ancillary accursed alarm). civic Defense Science and Technology Comabsenceion and the advancement of a ancestor for accurateation. assay, begin that the 30th aircbulk do accept an aggression of our airamplitude up to 50 km, flew over the area of Yancheng in Jiangsu. But the access of time, accommodated too backward to beef,[link widoczny dla zalogowanych], adept actual acquiescent. Aladmitting the U.S. aggressive to abduct music for a while, but tactuality is no alarum. Only a few U.S. bi-weeklys acknowledgmented this adumbration.

[link widoczny dla zalogowanych]

A hundred yards away was a straight road, show- ing white in the
moonlight. Endeavouring to orient himself, as a surveyor or navigator
might say, the man moved his eyes slowly along its visible length and at
a distance of a quarter-mile to the south of his station saw, dim and
grey in the haze, a group of horsemen riding to the north. Behind them
were men afoot, marching in column, with dimly gleaming rifles aslant
above their shoulders. They moved slowly and in silence. Another group
of horsemen, another regiment of infantry, another and another --all in
unceasing motion toward the man's point of view, past it, and beyond. A
battery of artillery followed, the cannoneers riding with folded arms on
limber and caisson. And still the interminable procession came out of
the obscurity to south and passed into the obscurity to north, with
never a sound of voice, nor hoof, nor wheel.
The man could not rightly understand: he thought himself deaf; said
so, and heard his own voice, al- though it had an unfamiliar quality
that almost alarmed him; it disappointed his ear's expectancy in the
matter of timbre and resonance. But he was not deaf, and that for the
moment sufficed.