CRUD Operations for databases

A: Database crud operations, C refers to create new, R refers to retrieve retrieval, u refers to the update changes, d refers to delete deleted

SQL statements are divided into 3 categories:

1.DDL refers to the data definition language such as: Create,drop,alter, etc.;

2.DML refers to data manipulation language: CRUD;

3.DCL refers to the Data Control language: Backup language and the like.

Database types are divided into 3 main categories:

1. Relational database: Use table to store data, easy to retrieve, redundancy is small, now use is a relational database.

2. Hierarchical database, uncommon

3. Web-based database, uncommon

Two: crud operation syntax

First create a database, a fruit table, go is a split, if you do not add go, execute the statement together error, create a fruit table, IDS column with primary key is defined as the primary key column, because the type and source is a keyword, so with a [] enclosed.

Create DATABASE MyDB
Go
Use MyDB
Go
CREATE TABLE Fruit
(
IDS int primary KEY,
Name varchar () is not NULL,
Price float,
[Type] varchar,--type is the keyword plus []
[Source]varchar (20)

)
Go

1. Add an action

Here IDs is not set to self-growing columns, add content with the Insert table, the default is a few columns () to fill in several values such as the first sentence, if you do not have to specify the default of which column to fill in which value, such as the second third sentence.

Insert into fruit values (' 1 ', ' Red Fuji ', ' 5 ', ' apple ', ' Qixia ')
Insert into fruit (Ids,name,price,[type]) VALUES (' 2 ', ' Giant Peaks ', ' 6 ', ' grapes ')
Insert into fruit (Ids,name,[type],price,[source]) VALUES (' 3 ', ' Winter JuJube ', ' Date class ', ' 20 ', ' Zhanhua ')

If IDs is set to self-grow columns, then the first column of IDs is not populated with any values, and the default is four columns, as in the following statement

Insert into fruit values (' Red banana ', ' 4.5 ', ' apple ', ' muping ')
Insert into fruit values (' Black Beauty ', ' 2.0 ', ' watermelon ', ' Zibo ')
Insert into fruit values (' raisins ', ' 10.0 ', ' grapes ', ' Xinjiang ')
Insert into fruit values (' Fat peach ', ' 3.0 ', ' peach ', ' Feicheng ')
Insert into fruit values (' pear ', ' 5.0 ', ' pear ', ' laiyang ')
Insert into fruit values (' chaise laugh ', ' 20.0 ', ' lychee ', ' Shenzhen ')
Insert into fruit values (' laiyang pear ', ' 3.0 ', ' pear ', ' laiyang ')
Insert into fruit values (' Red grapefruit ', ' 5.0 ', ' pear ', ' Guangxi ')
Insert into fruit values (' green ', ' 16 ', ' Dragon Fruit ', ' Shenzhen ')

The result after completion:

2. Delete operation

Delete delete slow, write log, from growth down continue not to start again

Delete from fruit

Delete from fruit where column name relational operator value
Delete from fruit where Source = ' Laiyang '
Delete from fruit where type= ' grapes '--remove the line where the origin is Laiyang, and remove the row of the grape type

More conditions can also be used and or or, this is to delete the origin is the price of Shenzhen less than 18 of the line
Lete from fruit where source= ' Shenzhen ' and price<18

TRUNCATE TABLE Fruit--truncate fast, truncate no, self-growing column resets from 1

3. Update operations
--update fruit Set Column name = value, column name = value, ... where, the following statement:
Update fruit set price=1000000 where name= ' green '

Update fruit set type= ' human ', Source = ' Zibo ' WHERE name = ' Green '

--begin Tran and rollback, a rollback operation, the middle of the sentence operation is wrong, you can return with rollback, to avoid the wrong operation can not be undone.
BEGIN Tran
Update fruit set type= ' human ', Source = ' Zibo '-so that all the rows are changed, the operation is wrong, you can use rollback to return to the state before the operation.
Rollback

4. Query operations

--a filter on a column is called a projection, and a filter on a row is called a filter
--The name of the row is called a record or tuple, and the name of the column is field or attribute

The query operation does not change the contents of the database, but displays the data in the form as needed.

SELECT * FROM Fruit--* is the column representing all

Select Name,type,source from fruit--The specified column, where the Name column, type column, and source column are specified

Select *from fruit where price>=5 and source= ' Laiyang '--Specify the line, where the price is greater than or equal to 5 of the origin is the Laiyang of this line

Select *from fruit where price between and 20-range query, query for rows between 10 and 20
Select *from fruit where price in (3,4,5)--a discrete-value query that queries prices for rows that are 3,4,5

--Column to repeat
Select distinct type from fruit--removes duplicates of type

--Fuzzy query

Wildcard:% is any number of any character, _ the underscore represents an arbitrary character, such as% red is the red can have any number of characters, you need to write in single quotation marks.
Select *from fruit where name like '% Red '--Query the name column with the Scarlet Letter

Select *from fruit where price like '%5% '
Select *from fruit where price like ' _0% ' or price like ' __5% '--or ' _[3.5]% ' [] represents any one

--Sort
Select *from Fruit ORDER BY price--followed by ASC is not written, in ascending order of prices

Select *from Fruit ORDER BY price desc--Descending order
Select *from Fruit ORDER BY Price asc,ids desc--First row in price ascending order, price same as

Select *from fruit where price>5 order by price-first select price greater than 5 and then in ascending order

--Statistics, aggregation functions
Select COUNT (*) from fruit--Find out how many records

Select COUNT (*) from fruit where type like ' pear '--type is the number of pears

Select AVG (price) from fruit-check average
Select AVG. From fruit where type in (' Pear ', ' apple ')--the price average of the pear apples

Select SUM (price) from fruit--summation, () can be an expression such as: Price/ids

SELECT *, (price*0.9) as discounted price from fruit--on the right side of the table and a column of "Discounted price" shows the price after 90 percent, only show that the database is not deposited.

Select IDs serial number, name, price, type, source origin from fruit--use Chinese characters instead of English column names

Select Max from fruit--query price max
Elect min (price) from fruit--Query the minimum value

--Grouping general coordination statistic function
Select Type,count (*) from the fruit group by type Order by COUNT (*) desc--Sorts the grouped data, groups them by type (type), sorts the numbers in descending order of each group, and displays the Type column and each group The number

Select Type,count (*) from fruit where price>5 group by type--grouping by type for prices greater than 5

Select Type,min (price) from fruit Group by type--minimum value per type

Select Type,count (*) from fruit GROUP by type have COUNT (*) >1-Filter The data after grouping, having only to follow the group by; grouping by type, large number of groups The type and number shown in 1

The above is some basic crud operations, where the query is more important here, the content is also more, the use of queries can easily from a lot of data to find the data we want.

CRUD Operations for databases