SQL Server-Basic-classic SQL statements, server-SQL

SQL Server-Basic-classic SQL statements, server-SQL

1 classic SQL statement 2 basics 3 1. Description: CREATE DATABASE 4 CREATE database-name 5 2. Description: delete DATABASE 6 drop database dbname 7 3. Description: back up SQL server 8 --- create device 9 USE master 10 EXEC sp_addumpdevice 'disk', 'testback', 'c: \ mssql7backup \ MyNwind_1.dat '11 --- start TO back up 12 backup database pubs TO testBack 13 4. Note: create a new table 14 create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...) 15 create A new table based on an existing table: 16 A: create Table tab_new like tab_old (use the old table to create a new table) 17 B: create table tab_new as select col1, col2... From tab_old definition only 18 5. Description: delete new table 19 drop table tabname 20 6. Description: add a column 21 Alter table tabname add column col type default 22. Note: columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type. 23 7. Description: add a primary key: Alter table tabname add primary key (col) 24 Description: delete a primary key: Alter table tabname drop primary key (col) 25 8. Description: Create an index: create [unique] index idxname on tabname (col ....) 26. delete an index: drop index idxname 27 Note: The index cannot be changed. To change the index, you must delete it and recreate it. 28 9. Note: create view viewname as select statement 29 delete view: drop view viewname 30 10. Note: select a few simple basic SQL statements 31: select * from table1 where range 32 insert: insert into table1 (field1, field2) values (value1, value2) 33 delete: delete from table1 where range 34 update: update table1 set field1 = value1 where range 35 search: select * from table1 where field1 like '% value1 %' --- like syntax is very subtle, query information! 36 sorting: select * from table1 order by field1, field2 [desc] 37 total: select count as totalcount from table1 38 sum: select sum (field1) as sumvalue from table1 39 Average: select avg (field1) as avgvalue from table1 40 max: select max (field1) as maxvalue from table1 41 min: select min (field1) as minvalue from table1 42 11, description: several advanced query operators 43 A: UNION operator 44 the UNION operator derives A result table by combining two other result tables (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When ALL is used together with UNION (that is, union all), duplicate rows are not eliminated. In either case, each row of the derived table is from either TABLE1 or table2. 45 B: the distinct t operator 46 distinct t operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When ALL is used with distinct T (distinct t all), duplicate rows are not eliminated. 47 C: INTERSECT operator 48 the INTERSECT operator derives a result table by including only the rows in TABLE1 and TABLE2 and eliminating all repeated rows. When ALL is used with INTERSECT (intersect all), duplicate rows are not eliminated. 49 Note: the query result rows using computation words must be consistent. 50 12. Note: Use join 51 A and left (outer) join: 52 left outer join (left join): the result set contains matching rows of the connection table, it also includes all rows in the left join table. 53 SQL: select. a,. b,. c, B. c, B. d, B. f from a left out join B ON. a = B. c 54 B: right (outer) join: 55 right outer join (right join): the result set includes both matched join rows in the connection table and all rows in the right join table. 56 C: full/cross (outer) join: 57 full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables. 58 D: inner join 59 13. Grouping: Group by: 60: A table. Once grouping is complete, only Group-related information can be obtained after query. 61 groups of related information: (Statistical Information) count, sum, max, min, avg grouping criteria) 62 when grouping in SQLServer: cannot use text, ntext, fields of the image type are grouped based on 63 fields in the selecte statistics function. They cannot be put together with common fields. 64 14. perform operations on the database: 65. Separate the database: sp_detach_db. Attach the database: sp_attach_db indicates that the complete path 66 15 must be attached. how to modify the Database name: 67 sp_renamedb 'old _ name', 'new _ name' 68 69 increased by 70 1. Description: copy a table (only copy structure, source table name: a new table name: B) (Access available) 71 Method 1: select * into B from a where 1 <> 1 (for SQlServer only) (copy table and table data) method 2: select top 0 * into B From a (only including the table structure: Not including the index) 73 2. Description: copy the table (copy data, source table name: a target table name: B) (Access available) 74 insert into B (a, B, c) select d, e, f from B; 75 3. Description: copies of tables across databases (absolute paths are used for specific data) (Access available) 76 insert into B (a, B, c) select d, e, f from B in 'specific database' where condition 77 example :.. from B in '"& Server. mapPath (". ") &" \ data. mdb "&" 'where .. 78 4. Description: subquery (table name 1: a table name 2: B) 79 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) 80 6. Description: External join query (table name 1: Table a name 2: B) 81 select. a,. b,. c, B. c, B. d, B. f from a left out join B ON. a = B. c 82 7. Description: Online View query (table name 1: a) 83 select * from (SELECT a, B, c FROM a) T where t. a> 1; 84 9. Description: in usage method 85 select * from table1 where a [not] in ('value 1', 'value 2', 'value 4 ', 'value 6') 86 10. Description: two joined tables, delete information already unavailable in the secondary table in the primary table 87 delete from table1 where not exists (select * from table2 where table1.field1 = table2.field1) 88 11. Note: Four-table join query: 89 select * from a left inner join B on. a = B. B right inner join c on. a = c. c inner join d on. a = d. d where ..... 90 14. Note: The first 10 records 91 select top 10 * form table1 where range 92 15. Note: select all the information of the largest record corresponding to a in each group of data with the same B value 93 select a, B, c from tablename ta where a = (select max () from tablename tb where tb. B = ta. b) 94 17. Description: 10 data records are randomly retrieved. 95 select top 10 * from tablename order by 96 18. Description: 97 sele records are randomly selected. Ct newid () 98 19. Note: delete duplicate records 99 1), delete from tablename where id not in (select max (id) from tablename group by col1, col2 ,...) 100 2), select distinct * into temp from tablename101 delete from tablename102 insert into tablename select * from temp103 rating: this operation involves moving a large amount of data, this method is not suitable for large-capacity operations, but 104 of data operations. 20. Description: list all the table names in the database. The 105 select name from sysobjects where type = 'U' // U indicates user 106 21. Note: List all columns in the table. The 107 select name from sy Scolumns where id = object_id ('tablename') 108 22. Description: lists the type, vender, and pcs fields in the type field. case can be easily selected, similar to case in select. 109 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 type110 display result: 111 type vender pcs112 computer A 1113 computer A 1114 Disc B 2115 Disc A 2116 mobile phone B 3117 mobile phone C 3118 23, note: initialization TABLE table1119 TRUNCATE TABLE table1120 24, note: select 121 select top 5 * from (select top 15 * from table order by id asc) records from 10 to 15) table _ alias order by id desc122 25 view the column description in the table 123 -- query database description 124 SELECT field name =. name, field description = isnull (g. [value], '') FROM syscolumns a left join sysobjects d on. id = d. id and d. xtype = 'U' and d. name <> 'dtproperties' left join sys. extended_properties g on. id = g. major_id and125. colid = g. minor_id where d. name = 'tyd' order by. codecom126 127 -- Query Table reference to view or other 128 select object_name (object_id) as objname, * from sys. SQL _modules129 where definition like '% tcyxymx %' AND definition like '% zydh %' 130 131 132 ----- query a table with columns with default constraints AND the corresponding default value 133 select SC. name as "Column Name", SM. text as "Default Value" 134 FROM dbo. sysobjects so inner join dbo. syscolumns SC ON SO. id = SC. id135 inner join dbo. syscomments sm on SC. cdefault = SM. id136 where so. xtype = 'U' AND so. name = 'table name' 137 order by so. [name], SC. codecom138-139 140 141 142 143 10. SQL server directly writes data cyclically 144 declare @ I int146 set @ I = 145 while @ I <1147 begin149 insert into test (userid) values (@ I) 150 set @ I = @ I + 1151 end152 153 154 159