MySQL SQL statements Daquan

MySQL SQL statements Daquan

1. Description: Create Database create database database-name2, Description: Delete database Drop database dbname3, Description: Backup SQL Server---Create Back up the data deviceuse masterexec sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat '---start backing up backup Database pubs To testback4, Description: Create a new Table Create table tabname (col1 type1 [not null] [primary  key],col2 type2 [Not null],..) Create a new table from an existing table: A:create table tab_new like tab_old (Create a new table with the old table) B:create table tab_new as  Select col1,col2. From tab_old definition only5, Description: Delete new table Drop table tabname6, Description: Add a column alter  table tabname Add column col Type Note: Columns cannot be deleted after they are added. DB2 the column plus the data type can not be changed, the only change is to increase the length of the varchar type. 7. Description: Add primary key: Alter table tabname Add primary key (COL) Description: Delete primary key: Alter table tabname drop  primary key (COL) 8, Description: CREATE INDEX: Create [unique] index idxname on tabname (col ...) Delete index: Drop  index idxname Note: The index is immutable and you need to remove the rebuild if you want to change it. 9. Description: CREATE VIEW: Create view viewname as select statement Delete View: Drop view viewname10, Description: A few simple basic SQL statement selection:select  * From table1 where  Range Insertion: insert into table1 (field1,field2) VALUES (value1,value2) deletion: Delete  from table1 where  Range Update: Update table1 set field1=value1 where  Range Lookup: select * From table1 where field1 like  '%value1% '---like grammar is very subtle, check the information! Sort: select * from table1 order &NBSP;BY&NBSP;FIELD1,FIELD2 [desc] Total: select count as totalcount from table1 summation:select  SUM (field1) as sumvalue from table1 average: Select avg (field1) as avgvalue from table1 max: Select  max (field1) as maxvalue from table1 min: select min (field1) As minvalue from table111, Description: Several advanced query operators the a:union  operator union  operator derives a result table by combining the other two result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all  is used with union  (that is, union all), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2. b:except  operator except  operationCharacters derive 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 rows are not eliminated. The intersect  operator of the c:intersect  operator derives a result table by including only rows in TABLE1 and TABLE2 and eliminating all duplicate rows. When all  is used with intersect  (intersect all), duplicate rows are not eliminated. Note: Several query result rows that use an operation word must be consistent. 12. Description: Use outer join a, left  (outer) join: Left OUTER join (left connection): The result set includes a matching row for the join table, and also includes all rows of the left join table. SQL:SELECT&NBSP;A.A, A.B, A.C, B.C, B.D, B.f from a left out join b ON a.a = b.cb:right  (o Uter) Join: Right outer join (right Join): The result set includes both the matching join row for the join table and all rows of the right join table. c:full/cross  (outer) Join: Full outer join: Includes not only the matching rows of the symbolic join table, but also all the records in the two join tables. 12, group:group by:   a table, once the group is complete, after the query can only get group-related information.   Group-related information: (statistics) count,sum,max,min,avg   grouping criteria)      when grouping in SQL Server: cannot be text,ntext , the field of the image type is grouped by   the field in the SELECTE statistic function cannot be put together with the normal field; 13. Operate the database:  separate database: sp_detach_db; Additional database: sp_attach_db The following indicates that the append requires a full pathname of 14. How to modify the name of the database: Sp_renamedb ' old_name ', ' new_name '   II, elevation 1, Description: Copy table (copy structure only, source table name: A New table name: B) (Access available) Law I: select * into bFrom a where 1<>1 (SQL Server only) Law II: select top 0 * into b from a2, Description: Copy table (copy data, SOURCE table name: A target table name: B) (Access available) Insert into b (A, B, c) select d,e,f from b;3, Description: Copy of table across databases (exact data using absolute path) ( Access available) Insert into b (A, B, c) select d,e,f from b in  ' specific database ' where  conditions example: 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  (5), Description: Displays the article, the author and the last reply time select a.title,a.username,b.adddate from  table a, (Select max (adddate) adddate From table where table.title=a.title) B6, Description: Outer join 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 out join b ON a.a = b. C7, Description: Online view query (table name 1:a) select * from  (select a,b,c from a) T where t.a > 1;8, Description: Usage of between, Between restrictionsBoundary values are included when querying data ranges, Not between does not include select * from table1 where time between time1 and  Time2select a,b,c, From table1 where a not between  value 1 and  value 29, description:in  Use of the method select * From table1 where a [not] in  (' value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ') 10, Description: Two association tables, Delete information in the primary table that is not already in the secondary table Delete from table1 where not exists (select * from table2 where &NBSP;TABLE1.FIELD1=TABLE2.FIELD1) 11, Description: Four table Joint inquiry: 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  ..... 12, Description: Schedule Five minutes early reminder sql:select * from  schedule Where datediff (' minute ', F start time, GETDATE ()) >513, description: One SQL Statements take care of 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 implementation: About database paging:  declare  @start int , @end int   @sql   nvarchar (+)   set  @sql = ' select top ' +str (@[email  protected]+1) + ' +from t where rid not in (select top ' +str (@str-1) + ' rid from t where  rid>-1) '   exec sp_executesql @sql   NOTE: It is not possible to follow a variable directly after top, so there is only such special handling in the actual application. The RID is an identity column, which is beneficial if there are specific fields after top. Because this avoids the top field if it is a logical index, the result of the query is inconsistent in the actual table (the data in the logical index is likely to be inconsistent with the data table, and when the query is indexed, the index is queried first) 14. Description: Top 10 Records select top  * Form table1 where  range 15, Description: Select the same data in each group B value corresponding to the largest record of all the information (similar to the use of this can be used for the forum monthly leaderboard, monthly hot product analysis, ranked by the subjects, and so on.) Select a,b,c from tablename ta where a= (Select max (a) from tablename TB where tb.b= TA.B) 16, Description: Includes all rows in TableA but not in TableB and TableC and eliminates all duplicate rows to derive a result table (Select a From tablea) except  (select& Nbsp;a From tableb) except  (select a From tablec) 17, Description: Randomly remove 10 data select top 10 * FROM  tablename Order by newid () 18, Description: Random Selection of Records Select newid () 19, Description: Delete 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 tablename  delete from  Tablename  insert into tablename select * from temp Evaluation: This kind of operation is implicated in the movement of large amounts of data, This approach is not suitable for large capacity but data manipulation 3), such as: import data in an external table, for some reason, for the first time only part of the import, but it is difficult to determine the exact location, so that only the next time all imports, so that will produce a lot of duplicate fields, how to delete duplicate fields alter  table tablename--Adding a self-add column Add  column_b int identity ( delete from ) TableName Where column_b not in (Select max (column_b)   From tablename group by  Column1,column2,...) Alter table tablename Drop column column_b20, Description: Lists all the table names in the database select name from  sysobjects where type= ' u '  //u represents user 21, Description: Lists all column names in the table Select name from syscolumns where  id=object_id (' TableName ') 22, Description: Lists the type, Vender, and PCs fields, arranged in the Type field, case can easily implement multiple choices, similar to select 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 Display Result: type vender pcs pc a 1 pc a 1 Disc B 2 disc a 2 mobile B 3 Mobile phone C 323, Description: Initialize table Table1trunc ate table table124, Description: Select a record from 10 to 15 select top 5 * from  (SELECT&NBSP;TOP&NBSP;15 * FROM  table order by id ASC) Table_ alias Order by id desc Three, tricks 1, 1=1,1=2 use, in SQL statement combined with more " Where 1=1 "is to indicate select all    " where 1=2 "all not selected, such as: if @strWhere! =" ' beginset  @strSQL = ' Select COUNT (*) as total from ['  + @tblName + '] where '  + @strWhereendelsebeginset   @strSQL = ' SELECT count (*) as Total from ['  + @tblName + '] ' end we can write the error directly! The catalog item was not found. set  @strSQL = ' SELECT count (*) as total from ['  + @tblName + '] where 1=1 diazepam ' + @strWhere 2, shrinking database-rebuilding index DBCC REIN DEXDBCC INDExdefrag--shrink data and log DBCC SHRINKDBDBCC SHRINKFILE3, compress database DBCC SHRINKDATABASE (dbname) 4, transfer database to new user with existing user rights EXEC&NBSP;SP_ Change_users_login ' Update_One ', ' newname ', ' oldname ' Go5, check backup set restore Verifyonly from disk= ' E:\dvbbs.bak ' 6, Repair Database Alter database [dvbbs] set single_usergodbcc CHECKDB (' Dvbbs ', Repair_allow_data_loss) with  tablockgoalter database [dvbbs] Set multi_usergo7, log cleanup set nocount ONDECLARE @ Logicalfilename sysname,  @MaxMinutes int,  @NewSize int use tablename--database name to operate select  @ Logicalfilename = ' Tablename_log ',---log file name @maxminutes = ten,--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  sysfiles where name = @LogicalFileNameSELECT   ' Original Size of '  + db_name () + ' LOG is '   + convert (varchar), @OriginalSize) + ' 8K pages or '  + convert (varchar (30),(@OriginalSize *8/1024)) + ' MB '  from sysfiles where name = @LogicalFileNameCREATE  table dummytrans   (Dummycolumn char  (8000) not null)  DECLARE  @Counter     int, @ StartTime datetime,  @TruncLog    VARCHAR (255) 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 had not expired and  @OriginalSize = (select& Nbsp;size from sysfiles where name = @LogicalFileName)  AND  (@OriginalSize * 8/1024) > @NewSize  begin --Outer Loop. select  @Counter = 0 while   ((@Counter < @OriginalSize/16) and  (@Counter < 50000))  begin --Update insert dummytrans values  (' Fill Log ') delete dummytrans&nbSp select  @Counter = @Counter + 1 end exec  (@TruncLog)  ENDSELECT  ' Final Size of '  + db _name () + ' LOG is '  + convert (varchar (), size) + ' 8K pages or '  + convert (varchar), (size*8/1024 ) + ' MB '  from sysfiles where name = @LogicalFileNameDROP  table dummytransset  nocount OFF8, Description: Change a table Exec sp_changeobjectowner ' tablename ', ' dbo ' 9, store change 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 forselect  ' Name '      = name,    ' Owner '     = user_name (UID) from sysobjectswhere  user_name (UID) [EMAIL&NBSP;PROTECTED]ORDER&NBSP;BY&NBSP;NAMEOPEN&NBSP;&NBSP;&NBSP;CURobjectfetch next from curobject into  @Name, @OwnerWHILE (@ @FETCH_STATUS =0) begin    if @[email protected]begin   set  @OwnerName = @OldOwner + '. '  + RTrim (@Name)    exec sp_changeobjectowner @OwnerName, @NewOwnerend--select @name, @ Newowner, @OldOwnerFETCH  next from curobject into  @Name, @OwnerENDclose   CUROBJECTDEALLOCATE&NBSP;CUROBJECTGO&NBSP;10, SQL Server directly loops write data declare @i intset @i=1while @i< 30begin    insert into test (userid) VALUES (@i)     set @[ Email protected]+1end case: There is a list of the following, requiring all the failed grades in the mounting, on the basis of each increase of 0.1, so that they just passed: name      Score zhangshan 80 lishi       59 wangwu       50 songquan 69while ((select min (score) from tb_table) <60) beginupdate tb_table set  score =SCORE*1.01WHERE&NBSP;SCORE&Lt;60if  (Select min (score) from tb_table) >60  break else     continueend  Data Development-classic  1. Sort by last name stroke: select * from tablename order by customername Collate chinese_prc_stroke_ci_as//from less than 2. Database encryption: Select encrypt (' original password ') select pwdencrypt (' original password ') Select pwdcompare (' original password ', ' encrypted password ') = same as, otherwise different encrypt (' original password ') select pwdencrypt (' original password ') select  Pwdcompare (' original password ', ' encrypted password ') = the same as the same; 3. Retrieve the fields in the table:declare  @list varchar, @sql nvarchar (+) select @ [email protected]+ ', ' +b.name from sysobjects a,syscolumns b where a.id=b.id and a.name= ' Table A ' set  @sql = ' select ' +right (@list, Len (@list)-1) + ' from Table A ' exec  (@sql) 4. View hard disk partition: Exec master. Xp_fixeddrives5. Compare the equality of A/b table: if (Select checksum_agg (binary_checksum (*)) from a)       =     (Select checksum_agg (binary_checksum (*)) from b) print ' equal ' elseprint ' not Equal ' 6. KillOff all the event Explorer processes: Declare hcforeach cursor global for select  ' kill ' +rtrim (spid) from  Master.dbo.sysprocesseswhere program_name in (' SQL Profiler ', N ' SQL Profiler ') exec sp_msforeach_worker '? ' 7. Record Search: Start to n records select top n * from  table-------------------------------N to M Records (with primary index ID) select top  m-n * from  table Where id in  (select top m ID from  table) order by id    Desc----------------------------------N to end record select top n * from  table Order by id Desc Case Example 1: A table with more than 10,000 records, the first field of the table RecID is the self-growth field, write an SQL statement, find the table of the 31st to 40th Records.  select top 10 recid from a where recid not  in (select top 30 recid FROM&NBSP;A) Analysis: If this writes a certain problem, if RecId has a logical index in the table.  select top 10 recid from a where ... Is looked up from the index, and subsequent select top 30 recid from a are found in the datasheet, so that the order in the index is likely to be inconsistent with the data table, which results in a query that is not the intended data. Solution 1, with order by select top 30 RecId frOm a Order by ricid If the field is not self-growing, the problem 2 occurs, and the condition is also added in that subquery: select top 30 recid from a where  recid>-1 Example 2: The last record in the query table does not know how much data the table has, as well as the table structure. set @s = ' SELECT top 1 * from t   where PID isn't in (select top '  + str (@count-1) + ' pid  FROM&NB Sp T) ' Print @s      exec  sp_executesql  @s9: Gets all the user tables in the current database Select name  from sysobjects where xtype= ' u '  and status>=010: 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 effect is the same in both ways 11: View a table-related view, stored procedure, function select a.* from sysobjects A, syscomments b where a.id = b.id and  B.text like  '% table name% ' 12: View all stored procedures in the current database select name as  stored procedure name from sysobjects where  Xtype= ' P ' 13: Querying all databases created by the user 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 <> 0x0114: Querying the fields and data types of a table Select column_name,data_type From information_ schema.columnswhere table_name = ' table name ' 15: Data manipulation between different server databases--Create a linked server exec sp_addlinkedserver   ' ITSV ', ' ', ' SQLOLEDB ', ' Remote server name or IP address ' exec sp_addlinkedsrvlogin  ' itsv ', ' false ', NULL, ' username ', ' password '--query example SE lect * from itsv. Database name. dbo. Table name--import Example select * into  table from  ITSV. Database name. dbo. Table name--Remove the linked server exec sp_dropserver  ' ITSV ' when no longer in use, ' droplogins '  --connect remote/LAN data (openrowset/ Openquery/opendatasource)--1, openrowset--query Example select * from openrowset (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name, dbo. Table name--select * into  table From openrowset (' SQLOLEDB ', ' SQL Server name ')--Raw cost surface ' User name '; ' Password ', database name, dbo. Table name  --The local table into the remote table Insert openrowset (' SqloleDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name select *from  Local table--Update local table update bset b. Column a=a. Column A from openrowset (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name as a inner join  Local table bon a.column1= B.column1--openquery usage needs to create a connection--first create a connection to create a linked server exec sp_addlinkedserver   ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '--query Select *from openquery (itsv,  ' SELECT *  from database. dbo. Table name ')--Import a local table into a remote table insert  openquery (itsv,  ' SELECT *  from database. dbo. Table name ') select * from  Local table--Update local table Update bset  b. Column b=a. Column bfrom openquery (itsv,  ' SELECT * from database. dbo. Table name ') as ainner join  local table B on  a. Column a=b. Column a --3, opendatasource/openrowsetselect   *from    OpenDataSource (' SQLOLEDB ',  ' Data source=ip/servername; User id= login name; password= password '  ). test.dbo.roy_ta--Import local table to remote table Insert opendatasource (' SQLOLEDB ',  ' Data source=ip/ ServerName; User id= login name;password= password '). database. dbo. Table name select * from  Local table SQL Server basic functions SQL Server Basic functions 1. String function length and analysis with 1,datalength (char_ Expr) returns the string containing the number of characters, but does not contain the trailing space 2,substring (expression,start,length) substring, the subscript of the string is from "1", start is the start position, length is the string length, in practical application with Len (expression) gets its length 3,right (char_expr,int_expr) returns the int_expr character to the right of the string, with the opposite 4,isnull (Check_expression, Replacement_value) If check_expression is empty, returns the value of Replacement_value, NOT NULL, returns the Check_expression character action class 5,sp_addtype Custom data type For example: Exec sp_addtype birthday, datetime, ' NULL ' 6,set nocount {On|off} So the returned results do not contain information about the Transact-SQL The number of rows affected by the statement. If some of the statements contained in a stored procedure do not return much of the actual data, this setting can significantly improve performance because of the significant reduction in network traffic. The Set nocount setting is set at 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). When Set nocount is off , return count common sense   in SQL query: up to how many tables or views can be followed from: 256 in the SQL statement, the order by occurs, the query is sorted first, and then taken in SQL, The maximum capacity for a field is 8000, and for nvarchar (4000), because nvarchar is a Unicode code.

This article is from "peter-unscrupulousness Youth" blog, please be sure to keep this source http://petervip.blog.51cto.com/3790959/1693035

MySQL SQL statements Daquan