Use mydb--add insert into Fruit (Ids,name,price) VALUES ('k008','Durian',9.9)--The primary key cannot be empty when added, and the other system defaults to nullinsert into Fruit values ('k009','Melon',4.8,'Zibo', the,'Image/4.gif')--all columns are added, one cannot fall--Delete begin Trandelete fromFruit--and begin tran a piece of execution data can reply (roll back) Rollbackdelete fromFruitwhereids='k008'--Delete the specified line--Modify Update FruitSetSource=NULL whereids='k005'--modifies an item of data for a specified row update FruitSetnumbers= $ whereids='k002'--modifies an item of data for a specified row update FruitSetids='k008'whereName='Melon'and price='4.80'--Modify IDS for specified names and prices--EnquirySelect* fromFruit--Query AllSelectName,price,numbers fromFruit--querying a specified columnSelectName as'name', Price as'Price', Source as 'Origin' fromFruit--query the specified column and change the column name of the virtual table (the table in the database has not changed)Select* fromFruitwhereids='k001'--querying a specified rowSelect* fromFruitwherePrice=2.4and numbers= $--Check all information in the row (column specified condition)SelectName fromFruitwhereNumbers between theand ---Check in row name (column specified condition)SelectName fromFruitwhereSourceinch('Yantai','Guangdong')--Check the name of the row in the specified region onlySelectDistinct numbers fromFruit--to re-query the quantity (the same number is displayed only once)Select* fromNewsSelect* fromNewswhereTitle like'% older%'--fuzzy query, check with older information (%represent many characters)Select* fromNewswhereTitle like'to love%'--fuzzy query, look for love to startSelect* fromNewswhereTitle like'% Determined'--fuzzy Query, to find out the end of theSelect* fromNewswhereTitle like'% Agricultural Households _'--fuzzy Query, check agricultural households after only one character of theSelect* fromFruit ORDER BY Numbers ASC--Sort Ascending by Quantity (desc descending)Select* fromFruit ORDER BY Numbers desc,price Desc--in descending order of quantity, the same in descending order by priceSelect* fromFruit--Query AllSelectCOUNT (*) fromFruit--Returns the number of data in the Fruit table, some enterprises to save resources to search for count (1) can also be returned as a numberSelectAVG (Price) as 'Average price' fromFruit--Query the average of a column, the output is a number, the column name displays the sum-data and the max-maximum value with the average price, min-Minimum ValueSelectSUM (Numbers) fromFruitSelect*, (price*0.8) as 'Price after discount' fromFruit--Add a column not in the database, here is a 80 percent price column, shown as"Price after discount"SelectNumbers,count (1) fromFruit GROUP BY Numbers--based on a column grouping, find out the number of members in the group (grouped according to number, the same numbers are divided into a group, and show the quantity within the group)SelectNumbers,count (*) fromFruit GROUP BY Numbers have COUNT (*) >1---based on Numbers (column) grouping, find out the number of members in each group, return the group with the number of members greater than 1
SQL Server basic additions and deletions (in-table)