**

<>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,
( Essence )2020 year 8 month 13 day C# Basic knowledge windform Realize two color sphere Paging tool class PageResultrk3399_android7.1 debugging USB Summary of Bluetooth module Hackbar Using the tutorial springboot Multi profile configuration ElementUI In the select Of label value mysql Recursively finds all child nodes of the parent class keras Data generator -- Data enhancement fio Use details about Navicat for mysql Of 2003 error