Shulou(Shulou.com)06/01 Report--

There are many kinds and quantities of SQL statements, and many statements are often used. SQL query statement is a typical example. Whether it is high-level query or low-level query, SQL query statement is the most frequent.

This entry introduces the application method of SQL (Structured Query Language) structured query language from the aspects of basic knowledge, judging objects and application skills.

Database Overview:

1. DBMS (DataBaseManagementSystem, database management system) and database. Usually speaking of "database" may have two meanings: MSSQLServer, Oracle and other DBMS; store a classification of a pile of data tables (Catalog)

2, the composition of the database-management software / services / data files (tables, views.)

3. Different brands of DBMS have their own different characteristics: MYSQL, MSSQLServer, DB2, Oracle, Access, Sybase and so on. For developers, it's pretty much the same.

4. The full name of SQL is Structured QueryLanguage (structured query language) SQLSQLServerMSSQLServer. The most common mistake.

5. In addition to Access, SQLServerCE and other file-based databases, most databases need a database server to run. When learning\ development is to connect to the local database, and when running online, the database is running on a separate server.

Basic terminology

Collapse to create a database

Determine whether the database exists before creation

If exists (select * from sysdatabases where name='databaseName')

Drop databasedatabaseName

Go

Create DATABASEdatabasename

Collapse and delete database

Drop databasedbname / / cannot be recovered through log after deletion

Collapse backup sqlserver

-create a device for backing up data

USE master

EXECsp_addumpdevice 'disk',' testBack','c:\ mssql7backup\ MyNwind_1.dat'

-start backup

BACKUP DATABASEpubs TO testBack

Collapse to create a new table

Create tabletabname (col1 type1 [not null] [primary key], col2 type2 [not null],..)

Create a new table based on an existing table:

A:go

Use original database name

Go

Select * into destination database name. Dbo. Destination table name from original table name (create a new table using the old table)

B:create table tab_new as select col1,col2... From tab_olddefinition only

Collapse creation sequence

Create sequenceSIMON_SEQUENCE

Minvalue 1-minimum

Maxvalue999999999999999999999999999-maximum

Start with 1-start valu

Increment by 1 Murray-add a few at a time

Cache 20

Collapse and delete new table

Drop tabletabname

Collapse to add a column

Alter tabletabname add colname coltype

Collapse to delete a column

Alter tabletabname drop column colname

Collapse to add primary key

Alter tabletabname add primary key (col)

Description: delete primary key: Alter table tabname drop primary key (col)

Collapse to create an index

Create [unique] index idxname on tabname (col ….)

Delete index: drop index idxname on tabname

Note: the index is immutable and must be deleted and rebuilt if you want to change it.

Collapse to create a view

Create viewviewname as select statement

Delete View: drop view viewname

Collapse simple basic sql statements

(1) data record filtering:

Sql= "select* from data Table where field name = field value order by field name [desc]"

Sql= "select* from data Table where field name like'% field value% 'order by field name [desc]"

Sql= "selecttop 10 * from data Table where Field name = Field value order by Field name [desc]"

Sql= "selecttop 10 * from data Table order by Field name [desc]"

Sql= "select* from data Table where field name in ('value 1') 'value 2')"

Sql= "select* from data Table where field name between value 1 and value 2"

(2) update data records:

Sql= "update data Table set Field name = Field value where conditional expression"

Sql= "update data Table set field 1 = value 1, field 2 = value 2... field n = value n where conditional expression"

(3) Delete data records:

Sql= "deletefrom data Table where conditional expression"

Sql= "deletefrom data Table" (delete all records of the data table)

(4) add data records:

Sql= "insertinto data Table (Field 1, Field 2, Field 3...) values (value 1, value 2, value 3...)"

Sql= "insertinto destination data Table select * from Source data Table" (add records from the source data table to the target data table)

(5) Statistical function of data record:

AVG (field name) to get a table column average

COUNT (*; field name) statistics on the number of rows of data or the number of rows of data with value for a column

MAX (field name) gets the maximum value of a table column

MIN (field name) gets the minimum value of a table column

SUM (field name) adds the values of the data column

The method of referencing the above function:

Sql= "selectsum (field name) as alias from datasheet where conditional expression"

Setrs=conn.excute (sql)

Use rs ("alias") to get the statistical value, other functions use the same as above.

Duplicate value removed by query: select distinct * from table1

(v) Establishment and deletion of data tables:

CREATE TABLE datasheet name (field 1 type 1 (length), field 2 type 2 (length)... )

(6) single column summation:

SELECT SUM (field name) FROM data sheet

Collapse several advanced query operations

A:UNION operator

The UNION operator derives a result table by combining the other two result tables, such as TABLE1 and TABLE2, and eliminating any duplicate rows in the table. When ALL is used with UNION (that is, UNION ALL), duplicate lines are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.

B: EXCEPT operator

The EXCEPT operator derives a result table by including all rows in TABLE1 but not in TABLE2 and eliminating all duplicate rows. When ALL is used with EXCEPT (EXCEPT ALL), duplicate lines are not eliminated.

C:INTERSECT operator

The INTERSECT operator derives a result table by including only the rows in both TABLE1 and TABLE2 and eliminating all duplicate rows. When ALL is used with INTERSECT (INTERSECT ALL), duplicate lines are not eliminated.

Note: several query result rows that use operands must be consistent.

Fold to use external connection

A 、 left outer join:

Left outer join (left join): the result set includes both the matching rows of the join table and all rows of the left join table.

SQL: select A.A., A.C, B.D., B.F from a LEFT OUT JOIN b ON a.a = B.C.

B:right outer join:

Right outer join (right join): the result set includes both the matching join rows of the join table and all rows of the right join table.

C:full outer join:

Full outer join: includes not only the matching rows of the symbolic join table, but also all records in the two join tables.

Fold and edit the usage of this paragraph

The use of folding 1cm 1m 1m 1m 2

It is often used when combining SQL statements.

"where 1" means to select all "where 1" and none of them.

Such as:

If @ strWhere! ='

Begin

Set @ strSQL = 'selectcount (*) as Total from [' + @ tblName +'] where'+ @ strWhere

End

Else

Begin

Set @ strSQL = 'select count (*) as Total from [' + @ tblName +']'

End

We can write it directly.

Set @ strSQL = 'select count (*) as Total from [' + @ tblName +'] where 1 and'+ @ strWhere

Collapse and shrink database

-- rebuild the index

DBCC REINDEX

DBCC INDEXDEFRAG

-shrinking data and logs

DBCC SHRINKDB

DBCC SHRINKFILE

Collapse and compress the database

Dbccshrinkdatabase (dbname)

Transfer the database to a new user with existing user permissions

Execsp_change_users_login 'update_one','newname','oldname'

Go

Collapse check backup set

RESTOREVERIFYONLY from disk='E:\ dvbbs.bak'

Collapse and repair the database

Alter DATABASE [dvbbs] SET SINGLE_USER

GO

DBCCCHECKDB ('dvbbs',repair_allow_data_loss) WITH TABLOCK

GO

Alter DATABASE [dvbbs] SET MULTI_USER

GO

Collapse log cleanup

SET NOCOUNT ON

DECLARE@LogicalFileName sysname

@ MaxMinutes INT

@ NewSize INT

USE tablename-the name of the database to be operated on

Select@LogicalFileName = 'tablename_log',-- log file name

MaxMinutes = 10,-- Limit on time allowed to wrap log.

@ NewSize = 1-the size of the log file you want to set (M)

-- Setup / initialize

DECLARE@OriginalSize int

Select@OriginalSize = size

FROM sysfiles

Where name = @ LogicalFileName

Select 'OriginalSize of' + db_name () + 'LOG is' +

CONVERT (VARCHAR (30), @ OriginalSize) +'8K pages or'+

CONVERT (VARCHAR (30), (@ OriginalSize*8/1024)) + 'MB'

FROM sysfiles

Where name = @ LogicalFileName

Create TABLEDummyTrans

(DummyColumnchar (8000) not null)

DECLARE @ CounterINT

@ StartTimeDATETIME

@ TruncLogVARCHAR (255)

Select@StartTime = GETDATE ()

@ TruncLog = 'BACKUPLOG' + db_name () + 'WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@ LogicalFileName, @ NewSize)

EXEC (@ TruncLog)

-- Wrap the logif necessary.

WHILE@MaxMinutes > DATEDIFF (mi, @ StartTime, GETDATE ())-time has not expired

AND@OriginalSize = (Select size FROM sysfiles Where name = @ LogicalFileName)

AND (@ OriginalSize * 8 / 1024) > @ NewSize

BEGIN-- Outerloop.

Select @ Counter= 0

WHILE ((@ Counter < @ OriginalSize / 16) AND (@ Counter < 50000))

BEGIN-update

InsertDummyTrans VALUES ('Fill Log')

Delete DummyTrans

Select @ Counter= @ Counter + 1

END

EXEC (@ TruncLog)

END

Select 'FinalSize of' + db_name () + 'LOG is' +

CONVERT (VARCHAR (30), size) +'8K pages or'+

CONVERT (VARCHAR (30), (size*8/1024)) + 'MB'

FROM sysfiles

Where name = @ LogicalFileName

Drop TABLE DummyTrans

SET NOCOUNT OFF

Collapse to change a table

Execsp_changeobjectowner 'tablename','dbo'

Collapse to store changes to all tables

Create PROCEDUREdbo.User_ChangeObjectOwnerBatch

@ OldOwner asNVARCHAR (128C)

@ NewOwner asNVARCHAR (128C)

AS

DECLARE @ Name asNVARCHAR

DECLARE @ Owneras NVARCHAR

DECLARE@OwnerName as NVARCHAR (128)

DECLAREcurObject CURSOR FOR

Select 'Name' = name

'Owner' = user_name (uid)

From sysobjects

Where user_name (uid) = @ OldOwner

Order by name

OPEN curObject

FETCH NEXT FROMcurObject INTO @ Name, @ Owner

WHILE (@ @ FETCH_STATUS=0)

BEGIN

If@Owner=@OldOwner

Begin

Set @ OwnerName = @ OldOwner +'.'+ rtrim (@ Name)

Execsp_changeobjectowner @ OwnerName, @ NewOwner

End

-- select@name,@NewOwner,@OldOwner

FETCH NEXT FROMcurObject INTO @ Name, @ Owner

END

Close curObject

DeallocatecurObject

GO

Circular write data in collapsing SERVER

Declare @ I int

Set @ iTunes 1

While @ I

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.