I. Concept and classification of indexesThe concept of indexing everyone knows that we also use common clustered indexes, nonclustered indexes in our daily development. However, in addition to these two, SQL Server also provides other indexes, such as:A. Unique index: An index that does not contain duplicate keys, and either a clustered index or a nonclustered index can be a unique index.B. Index containing columns: it expands to include not only key
SQL Example 9– subqueryDatabase PreparationCreate Table Student(IDintAuto_incrementPrimary Key,name varchar(10));Create Table scores(IDintAuto_incrementPRIMARY Key, scoreint default0,Foreign Key(ID)ReferencesStudent (ID) on Delete Cascade on Update Cascade);A subquery can return a scalar, a row, a column, or a subquerySELECT * from t1 where column1 = (select Column1 from T2);The first select is called an o
available)Insert into B (A, B, c) Select d,e,f from B in ' specific database ' where conditionExample:.. From B in ' "server.mappath (". ") "\data.mdb" "' Where."4, Description: Sub-query (table name 1:a table name 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 (all-in-a-5, Description: External connection query (table name 1:a table name 2:b)Select A.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C6, Description: Two related tables, dele
different DataContext. For example, in a northwinddatacontext named Tempdb, the customer and order are queried, and in another NorthwindDataContext, the customer's address is updated to 123 first Ave, The Order's CustomerID is updated to chops. Typically, you get an entity to attach by deserializing the XML from another layer//does not support attaching an entity from one DataContext to another DataContext//So to replicate the actions of the deserialized entity, these entities are recreated her
1. Install weblogic7 and jbuilder9
2. Download and install the patch package for SQL Server for Java on the Microsoft website.
3. Set the jbuileder9 environment after jbuilder9 is installed: Set the properties of the project to modify the temporary project file directory and the default program directory (these directories cannot contain spaces ), choose configure libraries from the Tools menu and set a Lib
meanSQL ServerPress the final outputORDER byordered order of the clauses requested;and the sequence that is used to parse the queryJOIN,GROUP byand theUNIONthe sequence is different. Usually, there are things you can do to avoid this. Conclusion Query plan displaySQL Serverthe method that you intend to use or have used to execute the query. It is implemented by detailing the operations that will be used, from the operation to the line of action, and the parallelism involved. You treat this inf
objection to the simple mode The downside of simple mode is that you will have a high chance of losing data because you can only restore the database to the nearest full or differential backup. As mentioned earlier, if the lost data is measured in minutes, not measured in hours, do not use simple mode. However, if you are running a development or test database, or even a read-only production database, then using simple mode can be a viable, even a sensible choice, which can greatly reduce the m
had a good way to edit the written SQL statements to re-query, now we can do this, see the following example://1. Create compiled queryNorthwindDataContext db =NewNorthwindDataContext ();varfn =Compiledquery.compile (northwinddatacontext DB2,stringCity) = fromCinchDB2. CustomerswhereC.city = = CitySelectc);//2. Query the city for the London consumer, represented by the Loncusts collection, which can be bound with the data controlvarloncusts = FN (db,
worker Set name = ' Transaction modification ' where employee number = ' Zg2 'ROLLBACK TRANSACTION Update_data9.3 Save TransactionSyntax format:Save{tran | transaction} {savepoint | @savepoint_variable}Note: When a transaction begins, the resources used during transaction processing are persisted until the transaction completes (that is, locked). When a part of a transaction is rolled back to the savepoint, the resource continues to be retained until the transaction completes (or the entire tra
5. Check the record of 85, 86 or 88 in the score table.Select * from where inch (+,+)6, inquires the student table "95031" class or the sex is "the female" the classmate record.Select * from where class='95031'or=' female '7. Query the student table for all records in descending order of class.Select * from Order by desc8, in CNO Ascending, Degree descending query score all records of the table.Select * from Order by desc9. Check the number of students in "95031" class.Select Count (1from
Label:Select
date_format (server_time, '%h:%i '), (sum (flow_value)-max (flow_value)-min (Flow_value))/7
from
t Able_name
where
server_time >= ' Date9 00:00:00 ' and Server_time SQL (MYSQL) statement for the average of the same time for the last 9 days (minus the maximum, minimum value)
is 0
rollback Tran--rolling back things
Else
Commit Tran--Submit Things
--three paradigms of---database design
--1. The values in each column are single
--2. Meet 1, each table must have a primary key
--3. Satisfy 2, the foreign key table only appears in the primary key table in the primary key column, the other columns do not appear. When there are three columns in a table and more of the data is often duplicated, you need to take these columns out to create a separate table
--set a primary
=2 is not valid, it does not retrieve the data that matches the condition, and generates a blank table with no data.Use Sales management goselect product name, model, sales price-price as spread, stock into #商品表副本 from commodity table WHERE 1=2goselect * from #商品表副本SQL Rollup Query aggregation functionThere are 6 commonly used aggregate functions:
Count (*), counts all records
count[DISTINCT] field, number of values in the statistics fiel
and the modified name--can be To track the deletion of the databaseInserts the output result into a single table--Create an audit table Db_audit ( ID int not NULL, name varchar (a) NOT null, sex varchar () is not NULL, Age int, deletedate datetime NOT NULL constraint df_deletedate_today default (GETDATE ())--defaults constraint current time) Delete from Studentoutput deleted.id,deleted.name,deleted.sex,deleted.ageinto Db_audit (id,name,sex,age) where ID The merge uses the
."Backing up and replying to a medium-sized database is much more convenient than manipulating a database that stores terabytes of data."Although it is not wrong to model a data object and map everything in an entire object to a separate database, it is reasonable to size more thanThe database separation of critical values can simplify the work of database management.】solution : Partitioning and standardizingWhen the data for a table is very large, in addition to manually splitting the table, yo
SQL prompt smart hints and their formatting is easy to use.
Free Download Address: http://www.jb51.net/database/201355.html
1. Download SQL Prompt 5.3.4.1, is a compressed package, which has three files
No-sign-in-free points download address: http://www.jb51.net/database
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.