**

<>SQL Some basic statements

**

1, explain : Create database
CREATE DATABASE database-name
2, explain : Delete database
drop database dbname
3, explain : backups sql server
— establish Backup data device
USE master
EXEC sp_addumpdevice ‘disk’, ‘testBack’, ‘c:\mssql7backup\MyNwind_1.dat’
— start backups
BACKUP DATABASE pubs TO testBack
4, explain : 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 using the old table )
B:create table tab_new as select col1,col2… from tab_old definition only
5, explain : Delete new table
drop table tabname
6, explain : Add a column
Alter table tabname add column col type
notes : Columns cannot be deleted after being added .DB2 The data type cannot be changed after the middle column is added , The only thing that can change is increase varchar Length of type .
7, explain : Add primary key : Alter table tabname add primary key(col)
explain : Delete primary key : Alter table tabname drop primary key(col)
8, explain : Create index :create [unique] index idxname on tabname(col….)
Delete index :drop index idxname
notes : Index is immutable , To change, you must delete and rebuild .
9, explain : Create view :create view viewname as select statement
Delete view :drop view viewname
10, explain : Some simple basic sql sentence
choice :select * from table1 where Range
insert :insert into table1(field1,field2) values(value1,value2)
delete :delete from table1 where Range
to update :update table1 set field1=value1 where Range
lookup :select * from table1 where field1 like ’%value1%’ —like The grammar is very exquisite , Search for information !
sort :select * from table1 order by 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
maximum :select max(field1) as maxvalue from table1
minimum :select min(field1) as minvalue from table1
11, explain : Several advanced query terms
A: UNION operator
UNION Operator by combining the other two result tables ( for example TABLE1 and TABLE2) And eliminate any duplicate rows in the table to derive a result table . When ALL along with UNION
When used together ( Namely UNION ALL), Don't de duplicate lines . In both cases , Each row of a derived table does not come from TABLE1 It's from TABLE2.
B: EXCEPT operator
EXCEPT Operator by including all TABLE1 Medium but not TABLE2 Rows in and all duplicate rows are eliminated to derive a result table . When ALL along with EXCEPT
When used together (EXCEPT ALL), Don't de duplicate lines .
C: INTERSECT operator
INTERSECT Operators are passed by including only TABLE1 and TABLE2 A result table is derived by eliminating all duplicate rows . When ALL along with INTERSECT
When used together (INTERSECT ALL), Don't de duplicate lines .
notes : Several query result lines using operation words must be consistent .
12, explain : Use external connection
A,left (outer) join:
Left outer connection ( Left connection ): The result set includes the matching rows of the join table , All rows of the left join table are also included .
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B:right (outer) join:
Right outer connection ( Right connection ): The result set includes both the matching join rows of the join table , All rows of the right join table are also included .
C:full/cross (outer) join:
Total external connection : Not only include matching rows of sign join tables , It also includes all the records in the two connection tables .
12, grouping :Group by:
A table , Once the grouping is complete , Only group related information can be obtained after query .
Group related information :( statistical information ) count,sum,max,min,avg Criteria for grouping )
stay SQLServer When grouping in : You can't text,ntext,image Field of type as grouping basis
stay selecte Fields in statistical functions , Cannot be put together with normal fields ;
13, Operate on the database :
Detach database : sp_detach_db; Additional database :sp_attach_db Followed by indication , Append requires full pathname
14. How to modify the name of a database :
sp_renamedb ‘old_name’, ‘new_name’

Technology
©2019-2020 Toolsou All rights reserved,
It's unexpected Python Cherry tree (turtle The gorgeous style of Library ) Some East 14 Pay change 16 salary , Sincerity or routine ? Browser kernel ( understand )java Four functional interfaces ( a key , simple )HashMap Explain in detail html Writing about cherry trees , Writing about cherry trees os Simple use of module