Four kinds of manipulation methods of database--increase, delete, change, check

Four kinds of manipulation methods of database--increase, delete, change, check

Add-a visual increase is the addition of content to a table that already exists at the right-click of the design page.

Another is to query the page, create a query in the code interface to add code. Writing form: INSERT INTO XXX (ZZZ,CCC) VALUES (' AAA ', ' SSS ')--insert is inserted meaning, into is where to go, xxx represents the name of the table to be inserted, ZZZ,CCC represents the column name within the table, values mean ' Value ', followed by XXX and CCC is the column name of the inserted content. The overall meaning is: in the XXX table in the ZZZ column and CCC column inserted AAA and SSS

NOTE * * *: Columns and values to match, the data type to be the same, the same character length, the order of the same, the column can be omitted but the value must be the total number of columns in the table and the order of the column exactly corresponding, self-growing columns, cannot omit the self-increment column, to give the self-increment column value '

Small Knowledge points * * * suggested that we should develop regular backup habits, in some cases will save a lot of unnecessary trouble

Delete--A visual delete is the right button on the design page to delete the row click the Delete button to remove the operation

The other is in the code interface operation, the same way to open the same operation.

Delete from xxx where qqq= ' zzz '--xxx is the name of the table, ZZZ is the name of the column, www--column name

Delete from xxx where qqq= ' zzz ' or www= ' SSS '--Delete multiple columns

Delete from xxx where qqq= ' zzz ' | | www= ' SSS '--Delete multiple columns

Delete from xxx where qqq= ' zzz ' && www= ' sss '--delete the column named zzz or www

Delete from xxx where qqq= ' zzz ' && www>50--Delete column named zzz or www greater than 50

Delete from xxx where qqq= ' zzz ' && www!=50--Delete column named zzz or www not equal to 50

Repair--

Updape xxx Set zzz= ' value ' where aaa= ' value ' means: Update the value of the zzz column in the XXX table, AAA means explicitly specifying which line

...... Categories such as ' delete '

Check--

SELECT * FROM XXX * means all columns, meaning to query all the columns of the table called XXX (* can also be a detailed column name, if it is a detailed column name, then only query the contents of this column, to query the column name can be multiple)--we call this operation projection

SELECT * FROM XXX Where condition--filter with where condition to display filtered content

Equivalence and non-equivalence

SELECT * from xxx where ccc= ' ddd '--to filter all data in all columns of the XXX table named "CCC value equals DDD"

SELECT * from xxx where ccc!= ' ddd '--in all columns of the XXX table, filter out all data that is named CCC value not equal to DDD

SELECT * from XXX where ccc>30--all columns in the XXX table are filtered for all data with a value greater than 30 CCC

SELECT * from XXX where ccc>30&&qqq<60--all columns in the XXX table are filtered column name is called CCC value is greater than 30 and QQQ column value is less than 60 of all data

Fuzzy query

SELECT * FROM XXX where the CCC like ' value% '--queries all data such as the beginning of this value%--any number of arbitrary characters _--underline: a single arbitrary character

SELECT * from XXX where CCC likes '% value% '--query all data in the middle like this value

SELECT * FROM XXX where the CCC like '% value '--query at the end of all data such as this value

SELECT * from XXX where CCC likes ' _ _ Value% '--query all data after the third character like this value

Sort queries

SELECT * FROM table name where ... order by column name [asc/desc]--the contents in parentheses can be written or not, ASC stands for Ascending, the system defaults to ascending order, so you can omit not to write, desc to represent descending, If you need to sort in descending order, then add desc to the rear.

SELECT * FROM table name where ... order by column name 1 [ASC/DESC], column name 2[desc] ... Multi-column sort display

Discrete query

SELECT * FROM table name where column name in (column name, column name ...) )--in (column name, column name) displays only the contents of the In () column

SELECT * FROM table name where column name ont in (column name, column name ...) )--Displays only the contents of the column except in ()

Aggregation functions

Select SUM (column name) from table name--the sum sum () of the contents of the query for all columns

Select count (column name) from table name--Query when column number of data bar
Select Max (column name) from table name--Select the maximum value for the top of the list to be displayed
Select min (column name) from table name--Select the minimum value of the front row to display
Select AVG (column name) from table name--averaging of the sum of the current column

Paging Query
Show 5 data per page, take the 2nd page of data
SELECT * from Car limit (n-1) *5,5--n is the first page, (n-1) is to query the contents of the first page

Go to re-query
Select DISTINCT column name from table name-removes duplicates that are present in the current table when the column contents are multiple

Group queries
Select Column Name 1 (*), column name 2 from table name Group BY column name 2--query How many data are in column 2, according to the column name 2 points the group to take the total number of this group
Select brand from Car GROUP by Brand have Count (*) >3 #分组之后根据条件查询使用having do not use where

2016.5.2 Lonely Nightingale

Four kinds of manipulation methods of database--increase, delete, change, check