Come in and see if you can't see what you're going to see. manipulate data with SQL statements no night city, silent

　　　　　　　　　　　　　　　　　　　　　　Kindergarten class, Silent

you are welcome to continue to follow my work believe that after n years after I have published all the things you will reap quite well  

Well, then how do you manipulate the data with a statement? Don't say much. Please look down ↓

1, click the "New Query" button, open the SQL command edit box, the operation of the database table and maintenance can be achieved by editing the SQL command.

2. Edit the code in the edit box to create the database table, confirm the code is correct, click the "Execute" button to create the data table.

Share with the freshmen operator Laosheng ignore the line

Arithmetic operators in T-SQL

Operator	Description
+	The sum of two numbers or expressions added
-	Minus operation, the difference between two number or expression subtraction
*	Multiply operations, the product of multiplying two numbers or expressions
/	In addition to operations, the quotient that divides two numbers or expressions, such as 5/5 with a value of 1,5.7/3, is 1.900000
%	Modulo operation, for the remainder of the division of two numbers or expressions, as a column, the value of 5%3 is 2

　　　　　　　　　　　　　　　　　　　　　　　Assignment operators in T-SQL　

Operator	Description
=	Assign a number or variable or expression to another variable, such as: Name= ' Wang Hua '

　　　　　　　　　　　　　　　　　　　　　　　Comparison operators in T-SQL

Operator	Description
=	equals, column such as: age=23
>	Greater than, column such as: price>100
<	Less than
<>	Not equal to
>=	Greater than or equal
<=	Less than or equal
! =	Not equal to (non-SQL-92 standard)

logical operators in T-SQL

Operator	Description
and	Returns True when and only if two Boolean expressions are true
OR	Returns False when and only if two Boolean expressions are false
Not	Reverse the value of a Boolean expression with the highest precedence

Then there are some statements to share with you!

To create a database:

CREATE DATABASE Database-name

To delete a database:

Drop Database dbname

Backing up SQL Server

To create a device that backs up data
Use master
EXEC sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat '
Start Backup
BACKUP DATABASE pubs to Testback

Create a new table
CREATE TABLE TabName (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..)

To create a new table from an existing table:

A:create table tab_new like Tab_old (create new table with old table)

B:create table tab_new as Select Col1,col2 ... from tab_old definition only

Delete a new table
drop table TabName
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.

Add primary key: Alter table TabName Add primary key (COL)

Delete primary key: Alter table tabname drop primary key (COL)
Creating an index: Create [unique] index idxname on tabname (col ...)
Drop index: Idxname
Note: The index is immutable and you must remove the rebuild if you want to change it.
Creating Views: Create VIEW viewname as SELECT statement
Delete view: Drop view viewname
A few simple basic SQL statements
Select: SELECT * FROM table1 where range
insert: INSERT INTO table1 (field1,field2) VALUES (value1,value2)
Delete: Delete from table1 where range
Updated: Update table1 set field1=value1 where range
Find: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, 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
min: select min (field1) as MinValue from table1
several advanced query arithmetic words

A:union operator

The 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 the 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
The 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 rows are not eliminated.

C:intersect operator
The 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.

A, left (outer) join:

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 join (right connection): 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 joins: Includes not only the matching rows of the symbolic join table, but also all the records in the two join tables.

Group: GROUP BY:
A table, once the grouping is complete, you can only get group-related information after the query.
Group-related information: (statistical information) Count,sum,max,min,avg grouping criteria)
When grouping in SQL Server: You cannot group by a field of type Text,ntext,image
The fields in the SELECTE statistic function cannot be combined with ordinary fields;

To manipulate a database:
Separate database: sp_detach_db; Additional database: sp_attach_db followed by indicates that the append requires a full path name

How to modify the name of the database:
Sp_renamedb ' Old_name ', ' new_name '

The following statement queries the nickname field and the email field named "Zhang San" in the TestTable table.

SELECT Nickname,email  from testtable WHERE Name=' Zhang San '

The following statement displays data for all columns in the TestTable table:

SELECT *  from

The data in the query results collection is arranged in the same order as the column names specified in the select list:

SELECT Nickname,email  from TestTable

Little friends today to play to share a limited number of shortcomings please include

Please continue to pay attention to the small friends here to thank!

Come in and see if you can't see what you're going to see. manipulate data with SQL statements no night city, silent