Database ———— Select Query

--Create a MyDB database
Create database mydbgo--working with databases
Use Mydbgo--Fruit Table CREATE TABLE Fruit (Ids varchar ( -) primary key, [Name] varchar ( -) notNULL, Pricedecimal(8,2), Source varchar ( -), Numbersint,    --inventory quantity [Image] varchar ( -) go--Add data to the table
Insert into fruit values ('k001','Apple',2.4,'Yantai', -,'Image/0.gif') insert into fruit values ('k002','Pineapple',1.4,'Guangdong', -,'Image/1.gif') insert into fruit values ('k003','Orange',2.4,'Fuzhou', -,'Image/2.gif') insert into fruit values ('k004','Grape',2.4,'New Reins', -,'Image/3.gif') insert into fruit values ('k005','Cherry',2.4,'Qingdao', -,'Image/4.gif') insert into fruit values ('k006','Peach',2.4,'Huaguoshan', -,'Image/5.gif') insert into fruit values ('k007','Banana',2.4,'Jinan', -,'Image/6.gif')--User table CREATE TABLE Login (UserName varchar ( -) primary key,--user name [name] varchar ( -),    --real name Password varchar ( -),    --Password Accountdecimal( -,2)    --account balance) Goinsert into login values ('Zhangsan','Zhang San','666666', -) insert into login values ('Lisi','John Doe','666666', -) insert into login values ('Wangwu','Harry','666666', -)--order Form CREATE TABLE orders (Code varchar ( -) primary key,--As the "user name yyyymmddhhmmssms" in the form of the primary key UserName varchar ( -) References Login (Username),--ordering personnel code for foreign key ordertime datetime,--order time) go--Order Content Table CREATE TABLE OrderDetails (IdsintIdentity primary KEY, Ordercode varchar ( -) References Orders (Code),--Order number Fruitcode varchar ( -) References Fruit (IDS),--Fruit code [Count]int,    --number of fruits) goSelect* fromFruitSelect* fromLoginSelect* fromordersSelect* fromorderdetails--QuerySelect* fromFruit--Check AllSelectName,source fromFruit--Check specific columnsSelectIds'Code', Name'name', Price'Price', Source'Origin'  fromFruit--Modify column namesSelect* fromFruitwhereids='K006'Select* fromFruitwherePrice=2.4and source='Yantai'--check the specified line according to criteriaSelect* fromFruitwherePrice between2.0and4.0--check the designated line by ScopeSelect* fromFruitwhereNumbersinch( -, the, -)--check the specified line, discreteSelectDistinct Numbers fromFruit--go to re-querySelect* fromNewsSelect* fromNewswhereTitle like'% Account'--fuzzy Query, check the end of the accountSelect* fromNewswhereTitle like' Panda%'--fuzzy Query, look at the beginning of the giant pandaSelect* fromNewswhereTitle like'% giant Panda%'--fuzzy query, to contain the giant pandaSelect* fromNewswhereTitle like'% Foreign Currency goods _'--fuzzy Query, check the foreign currency goods after only one characterSelect* fromFruit ORDER BY Numbers ASC--Follow the numbers column in ascending order, if you do not add ASC by default in ascending rowSelect* fromFruit ORDER BY Numbers Desc--sort in descending order of numbers columnsSelect* fromFruit ORDER BY Numbers,price--Follow the numbers row first, then the price rowSelectCOUNT (*) fromFruit--returns how many data are in the fruit tableSelectAVG (Numbers) fromFruit--returns the average of a columnSelectSUM (Numbers) fromFruit--returns all data for a column andSelectMAX (Numbers) fromFruit--returns the maximum value in a columnSelectMIN (Numbers) fromFruit--returns the minimum value in a columnSelect*, (price*0.8) as 'Price after discount'  fromFruit--add a column not in the database, this is the price column after 80 percentSelectNumbers, COUNT (*) fromFruit GROUP BY Numbers--find out the number of members in the group based on a column groupingSelectNumbers, COUNT (*) fromFruit GROUP BY Numbers have COUNT (*) >1--based on a column grouping, the number of members in the group is calculated, returning the number of members greater than 1

%---- represent any number of characters '% account '---End with account , there are multiple characters ' account % '---- account opening, the account has more than one character '% account '---- contains accounts, there are multiple characters before and after the account '% foreign currency goods _ '---- foreign currency goods, there is only one character, preceded by multiple characterslike---- fuzzy query distinct--- Remove Duplicates

Database ———— Select Query