SQL Getting Started Classic (ii) Database Basic query, add, update and delete

Use SQL query:

SQL query Basic syntax:

SELECT [all| DISTINCT] [TOP (<expression>) [PERCENT] [with TIES]] <column list> [from <source table (s)/view>] [wher E <restrictive Condition>]

[Groud by<column name or expression using a column in the SELECT List>]

[Have <restrictive condition based on the GROUP by Results>]

[ORDER by <column List>]

[For XML {row| auto| Explicit| path[(<element>)]},xmldata][,elements][,binary Base 64]]

[OPTION (<query hint>,[,..... n])]

The SQL statement above looks very complex. Now we are going to explain.

SELECT tells SQL what to do. From the table to be manipulated (note: * cannot be used as a field in a query.) How many fields are queried, and how many field names are written?

SELECT * FROM Person.Contact-I want to check this table in Person.Contact

WHERE Query Condition Filtering

SELECT * from Person.Contact where FirstName like ' M% '-I'm looking for a contact under person.contact This table to be FirstName starting with M

The following operators are available in the WHERE clause:

The GROUP by clause aggregates data functions:

As a boss, you want to know the sales and the total price of each item. You need to use aggregate functions

Select   salesorderid,sum (UnitPrice) as Totalprice from Sales.SalesOrderDetail Group by salesorderid--aggregate function Select   Salesorderid,sum (UnitPrice) as Totalprice from Sales.SalesOrderDetail Group by SalesOrderID have sum (UnitPrice) & gt;10000

Note: Filter out products with sales less than 10000. I don't know why totalprice cannot be filtered as an aggregate function. can only be filtered with sum (UnitPrice). You can use the where filter before the aggregation function. The Where filter cannot use aggregate functions. Easy error point: Use the where filter after group by. GROUP by can only be filtered with having. Having the aggregate functions such as filtering is complete before filtering.

The aggregate function has: count () sums the sum () sum AVG () averages min () min Max () max () These are more commonly used aggregate functions that can be used alone or with group by.

Now introduce the count () aggregate function: see 2 codes and results first

Select COUNT (1) from person.contact--Results 19,972 Data Select COUNT (middlename) from  person.contact--11,473 data

The above 2 data results are different, this need not be careful. The following blog will say this question (it is recommended not to take the column name as the Count aggregate function)

Order by Sort query:

Boss asked to see the product sales Top 10: Here also put top this keyword introduced. Order BY that field Desc is sorted in descending order by that field, ASC ascending row. Top to take the number of previous data

SELECT TOP   salesorderid,sum (UnitPrice) as Totalprice from Sales.SalesOrderDetail Group by SalesOrderID have sum ( UnitPrice) >10000 order  by Totalprice DESC--ranking the top 10 data by sales from high to bottom

Predicates for DISTINCT and all: If the database has 100 identical data. All loads the same data 100 times. Distinct loads only one piece of data. The remaining 99 are discarded.

The FOR XML is not introduced here. There is a special blog to introduce this query later.

INSERT Add Data

Basic syntax: INSERT [TOP (<expression>) [PERCENT] [into] <table object>[(<column listt>)][output <output Clause>] {VALUES (<data value>) [, (DataValues)] [,.... n]|<table source>| EXEC <procedure>| DEFAULT VALUES}

The above syntax is not as complex as it seems, simplifying syntax: INSERT into<tables object>[(<column list>)] VALUES (<data value>) [, (DataValues) [,.... N]

For the following study we set up a data sheet:

Use  AdventureWorks  gocreate TABLE Stores (  storescode char (4) Not NULL Primary KEY,  Name         nvarchar ( ) not NULL,  Address      nvarchar (a) null, city            nvarchar (+) NULL, state          char (2)  DEFAULT (' 0 ')  is not null,  Zip              Char (5) is not null) CREATE TABLE Sales (   ordernumber Varchar) is not null Primary key,   Storescode     char (4)  FOREIGN key REFERENCES   Stores (storescode) not NULL,   OrderDate      datetime  Default (GETDATE ()) not NULL,   Quantity         int not          null,   Terms             Varchar () not  null,   TitleID int            not   null)

Try: Insert a piece of data (you can write directly to add data to the column name of the data table)

INSERT into Stores Values (' Test ', ' Test Stores ', ' China ', ' ShenZhen ', ' 0 ', ' 100000 ')

First Insert value correct second error: violation of PRIMARY KEY constraint ' pk__stores__6f4a8121 '. cannot be in object ' dbo. Insert duplicate key in Stores '. Later blogs will be dedicated to keys and constraints.

I'm making a change to insert a piece of data:

INSERT into Stores (storescode,name,city,zip) Values (' Tes2 ', ' Test Store ', ' here ', ' 10000 ')

You can notice the INSERT SQL statement: With less address,state inserted, the column set to a null value can be ignored to provide the value of the column. If the column cannot be set to NULL. You must be one of the following 3 conditions, or the system will prompt for an error. The INSERT termination command was denied execution.

1. The column defines the default value, and the default value is that a constant value is inserted automatically when the insertion value is not provided. (The following key and the default will say the problem) 2. The column is defined to accept some form of system generated value, and the most common generated value is the identity value. 3 The inserted data already provides the value of the column.

To insert data in bulk:

INSERT into Stores (storescode,name,city,zip) VALUES (' tes5 ', ' Test Stores ', ' BJ ', ' 10000 '),            (' tes4 ', ' Test Stores ', ' BJ ', ' 10000 ');--(2 rows affected) This is a SQL Server 2008 database to add new features

INSERT into ..... SELECT statement:

Syntax: INSERT into <table_name>[column list] <select statement>

CREATE TABLE #temp (  ID char (4) NOT NULL,  name  nvarchar (no) null) INSERT into #tempSELETE storescode,name F ROM Storesselect * from #temp-results--test       test store--tes3    test stores--tes5    test stores--test    Test Stores

UPDATE statement changes the obtained data

Syntax: UPDATE <table name> SET <column>=<value>[,column=value] [from <source Tables>] [WHERE < Restrictive condition>]

Update Stores set state= ' 1 '--4 affected (4 data successfully updated) Update Stores set state= ' 0 ' WHERE storescode= ' tes2 '-1 strip affected

DELETE statement

Delete statement is very simple, syntax: delete <table name> [WHERE < condition>]

Delete Stores--Remove all data from delete Stores WHERE storescode= ' test '--delete storescode= ' test ' data

SQL Getting Started Classic (ii) Database Basic query, add, update, and delete