Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What are the SQL database statements?

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >

Share

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

What are the SQL database statements? I believe many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Create database CREATE DATABASE database-name delete database drop database dbname backup sql server create device USE masterEXEC sp_addumpdevice 'disk',' testBack','c:\ mssql7backup\ MyNwind_1.dat' start backup BACKUP DATABASE pubs TO testBack create new table create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],.) Create a new table based on an existing table: A:create table tab_new like tab_old (create a new table using the old table) B:create table tab_new as select col1,col2... From tab_old definition only delete new table drop table tabname add a column Alter table tabname add column col type Note: columns cannot be deleted after they are added. The data type cannot be changed after the column is added in DB2, and the only thing that can change is to increase the length of the varchar type. Add primary key Alter table tabname add primary key (col) delete primary key Alter table tabname drop primary key (col) create index create [unique] index idxname on tabname (col... (.) Delete index drop index idxname Note: the index is immutable and must be deleted and rebuilt if you want to change it. Create View create view viewname as select statement Delete View drop view viewname A few simple basic sql statement choices: select * from table1 where range insert: insert into table1 (field1,field2) values (value1,value2) Delete: delete from table1 where scope Update: update table1 set field1=value1 where range search: select * from table1 where field1 like'% value1%'-like syntax is very exquisite, check the information! 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 several advanced query operation words UNION operator 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. The EXCEPT operator 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. The INTERSECT operator 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.

Use external connection left (outer) join

Left outer join (left join): the result set includes the matching rows of the join table, as well as all rows of the left join table.

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.cright (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.

Full/cross (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.

Grouping: Group by

A table, once the grouping is completed, only the group-related information can be obtained after the query.

Group related information: (statistics) criteria for count,sum,max,min,avg grouping)

When grouping in SQLServer: fields of type text,ntext,image cannot be used as grouping basis

Fields in selecte statistics functions cannot be put together with ordinary fields

Operate on the database

Detach database: sp_detach_db

Attach database: sp_attach_db followed by indicates that the attachment requires a full pathname

How to change the name of the database sp_renamedb 'old_name',' new_name' promote replication table (copy structure only, source table name: a new table name: B) (available for Access)

Select * into b from a where 11 (for SQlServer only)

Select top 0 * into b from a

Copy table (copy data, source table name: a destination table name: B) (available for Access) insert into b (a, b, c) select drec eMagi f from b Copies of tables across databases (specific data using absolute paths) insert into b (Access available) select drec from b in 'specific database' where condition subquery (table name 1 select a table name 2 select b) select a journal c from a where an IN (select d from b) or: select a minute b from a where an IN (1 minute 2) displays the article, author and last reply time select a.title A. username from a LEFT OUT JOIN b ON b. Adddate from table a, (select max (adddate) adddate from table where table.title=a.title) b external join query (table name 1V a table name 2V b) user a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c online view query (table name 1adddate a) select * from (SELECT aline bmenade c FROM a) T where t.a > 1 The usage of between. Between includes boundary values when querying the range of data. Not between does not include select * from table1 where time between time1 and time2select, from table1 where a not between, 1 and, 2in, select * from table1 where a [not] in ('value 1', 'value 2', 'value 4',' value 6') two related tables Delete the information delete from table1 where not exists (select * from table2 where table1.field1=table2.field1) that has not been found in the secondary table in the primary table. Select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where. Schedule five minutes in advance to remind SQL: select * from schedule where datediff ('minute',f start time, getdate ()) > 5 A sql statement finishes database paging select top 10 b. * from (select top 20 primary key field, sort field from table name order by sort field desc) a, table name b where b. Primary key field = a. Primary key field order by a. Sort field

Specific implementation:

About database paging:

Declare @ start int,@end int@sql nvarchar set @ sql='select top'+str (@ end-@start+1) +'+ from T where rid not in (select top'+str (@ str-1) + 'Rid from T where Rid >-1)' exec sp_executesql @ sql

Note: you can't follow a variable directly after top, so this is the only way to deal with it in practice. Rid is an identity column, which is very beneficial if there are specific fields after top. Because this can avoid the inconsistency in the actual table after the result of the query if the field of top is logically indexed (the data in the logical index may be inconsistent with that in the data table, and if the field in the query is in the index, the index will be queried first)

The first 10 records select top 10 * form table1 where range selects all the information corresponding to the largest a record in each set of data with the same b value (usage like this can be used for forum monthly rankings, monthly best-selling product analysis, ranking by subject scores, etc.) select Aperior b C from tablename ta where a = (select max (a) from tablename tb where tb.b=ta.b) includes all rows in TableA but not in TableB and TableC and eliminates all duplicate rows and derives a result table (select a from tableA) except (select a from tableB) except (select a from tableC) randomly fetches 10 pieces of data select top 10 * from tablename order by newid () randomly selects records select newid () deletes duplicate records 1), delete from tablename where id not in (select max (id) from tablename group by col1 Col2,...) 2), select distinct * into temp from tablenamedelete from tablenameinsert into tablename select * from temp evaluation: this operation involves the movement of a large amount of data This practice is not suitable for large-capacity data operations 3), for example, if you import data in an external table, for some reason, only part of it is imported for the first time, but it is difficult to determine the specific location, so it is only possible to import all of them the next time. This results in a lot of duplicate fields. How to delete duplicate fields alter table tablename-- add a self-increasing column add column_b int identity (1) delete from tablename where column_b not in (select max (column_b) from tablename group by column1,column2,...) alter table tablename drop column column_b lists all the table names in the database select name from sysobjects where type='U' / / U represents the user lists all the column names in the table select name from syscolumns where id=object_id ('TableName') lists type, vender, pcs fields Arranged in type fields, case can easily implement multiple selections, similar to case in select. 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

Display the results:

Type vender pcs

Computer A1

Computer A1

CD B 2

CD A 2

Mobile phone B3

Mobile phone C 3

The initialization table table1TRUNCATE TABLE table1 chooses records from 10 to 15 select top 5 * from (select top 15 * from table order by id asc) table_ aliases order by id desc techniques 1, 1, and 2, which are often used in the combination of SQL statements

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

Such as:

If @ strWhere! =''begin set @ strSQL =' select count (*) as Total from ['+ @ tblName +'] where'+ @ strWhere endelse 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 contraction 1 and'+ @ strWhere shrinking database-re-indexing DBCC REINDEX DBCC INDEXDEFRAG-- shrinking data and logs DBCC SHRINKDB DBCC SHRINKFILE compressed database dbcc shrinkdatabase (dbname) transferring the database to new users with existing user rights exec sp_change_users_login 'update_one','newname' 'oldname'go check backup set RESTORE VERIFYONLY from disk='E:\ dvbbs.bak' repair database ALTER DATABASE [dvbbs] SET SINGLE_USERGODBCC CHECKDB (' dvbbs',repair_allow_data_loss) WITH TABLOCKGOALTER DATABASE [dvbbs] SET MULTI_USERGO log clear SET NOCOUNT ONDECLARE @ LogicalFileName sysname,@MaxMinutes INT,@NewSize INTUSE tablename-- database name to be operated 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 / initializeDECLARE @ OriginalSize intSELECT @ OriginalSize = size FROM sysfilesWHERE name = @ LogicalFileNameSELECT 'OriginalSize of' + db_name () + 'LOG is' + CONVERT (VARCHAR (30), @ OriginalSize) +'8K pages or'+ CONVERT (VARCHAR (30), (@ OriginalSize*8/1024)) + 'MB'FROM sysfilesWHERE name = @ LogicalFileNameCREATE TABLE DummyTrans (8000) not null) DECLARE @ Counter INT,@StartTime DATETIME TruncLog VARCHAR SELECT @ StartTime = GETDATE (), @ TruncLog = 'BACKUP LOG' + db_name () + 'WITH TRUNCATE_ONLY'DBCC SHRINKFILE (@ LogicalFileName, @ NewSize) EXEC (@ TruncLog)-- Wrap the log if necessary. WHILE @ MaxMinutes > DATEDIFF (mi, @ StartTime, GETDATE ())-time has not expiredAND @ OriginalSize = (SELECT size FROM sysfiles WHERE name = @ LogicalFileName) AND (@ OriginalSize * 8 / 1024) > @ NewSize BEGIN-- Outer loop.SELECT @ Counter = 0WHILE ((@ Counter < @ OriginalSize / 16) AND (@ Counter < 50000) BEGIN-updateINSERT DummyTrans VALUES ('Fill Log') DELETE DummyTransSELECT @ Counter = @ Counter + 1ENDEXEC (@ TruncLog) ENDSELECT' Final Size of'+ db_name () + LOG is + CONVERT (VARCHAR (30)) Size) +'8K pages or'+ CONVERT (VARCHAR (30), (size*8/1024)) + 'MB'FROM sysfiles WHERE name = @ LogicalFileNameDROP TABLE DummyTransSET NOCOUNT OFF change a table exec sp_changeobjectowner' tablename','dbo'

Store changes to all tables

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch@OldOwner as NVARCHAR, @ NewOwner as NVARCHAR, ASDECLARE @ Name as NVARCHAR, DECLARE @ Owner as NVARCHAR, DECLARE @ OwnerName as NVARCHAR, DECLARE curObject CURSOR FOR select 'Name' = name,' Owner' = user_name (uid) from sysobjectswhere user_name (uid) = @ OldOwnerorder by nameOPEN curObjectFETCH NEXT FROM curObject INTO @ Name @ OwnerWHILE (@ @ FETCH_STATUS=0) BEGIN if @ Owner=@OldOwner begin set @ OwnerName = @ OldOwner +'.'+ rtrim (@ Name) exec sp_changeobjectowner @ OwnerName, @ NewOwnerend-- select @ name,@NewOwner,@OldOwnerFETCH NEXT FROM curObject INTO @ Name, @ OwnerENDclose curObjectdeallocate curObjectGO

Direct cyclic data writing in SQL SERVER

Declare @ I intset @ i=1while @ iTable 0 gets all the fields of a table select name from syscolumns where id=object_id ('table name') select name from syscolumns where id in (select id from sysobjects where type ='u 'and name =' table name')

The two methods have the same effect.

View views, stored procedures, functions select a. * from sysobjects a, syscomments b where a.id = b.id and b.text like'% table name% 'view all stored procedures in the current database select name as stored procedure names from sysobjects where xtype='P' query all databases created by users select * from master..sysdatabases D where sid not in (select sid from master..syslogins where name='sa') or select dbid Name AS DB_NAME from master..sysdatabases where sid 0x01 queries the fields and data types of a table select column_name,data_type from information_schema.columnswhere table_name = 'table name' data manipulation between different server databases SQL Server basic function

SQL Server basic function

String function

For length and analysis

Datalength (Char_expr) returns the string containing the number of characters, but not the following spaces

Substring (expression,start,length) takes the substring. The subscript of the string is from "1", with start as the starting position and length as the string length. In practical applications, len (expression) is used to obtain its length.

Right (char_expr,int_expr) returns the int _ expr character to the right of the string, and uses left to reverse it

Isnull (check_expression, replacement_value) if check_expression is empty, the value of replacement_value is returned. If not empty, the check_expression character operation class is returned.

Sp_addtype Custom number Typ

For example: EXEC sp_addtype birthday, datetime, 'NULL'

Set nocount {on | off}

So that the returned results do not contain information about the number of rows affected by the Transact-SQL statement. If some statements contained in the stored procedure do not return a lot of actual data, this setting can significantly improve performance because of a significant reduction in network traffic. The SET NOCOUNT setting is set at execution or run time, not at parse time.

When SET NOCOUNT is ON, no count is returned (representing the number of rows affected by the Transact-SQL statement).

Return count when SET NOCOUNT is OFF

general knowledge

In SQL query: the maximum number of tables or views that can be followed after from: 256

Order by appears in the SQL statement. When querying, sort first, then fetch

In SQL, the maximum capacity of a field is 8000, while for nvarchar (4000), because nvarchar is Unicode code.

After reading the above, have you mastered the methods of SQL database statements? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Development

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report