/* Four, check 1. General Query syntax: Select < column name > from < table name > [where < query conditional expression] [order by < sorted column name >[ASC or DESC]] *//* 1). Querying all data rows and columns: SELECT * from a description: Queries all rows in a table and */select *from person
/* 2). Query section Column--Condition Query Example: Select i,j,k from a where f=5 description: Queries all rows in table A f=5, and displays I,J,K3 columns */select
/* 3). Use as in a query to change the column name example: select name as name from a where
/* 4). Query for Empty rows example: Select name from a where e-mail is null Description: Query all rows with empty email in table A and display the Name column; SQL statement with IS NULL or is not NULL Determines whether a blank line */select * from the person where the city is null
/* 5). Use a constant example in a query: select Name ' Beijing ' as address from a Description: Query table A, display the Name column, and add an address column whose column values are ' Beijing ' 6. Query return limit number of rows (keyword: top) example 1:sele CT Top 6 name from a Description: Query table A, display the first 6 rows of column name, top is the keyword (no top keyword in Oracle replaces with RowNum) Selec T * from a where rownum<6 *//* 7). Query Sort (keywords: order BY, ASC, DESC) Example: Select name from a where GR ade>=60 ORDER BY DESC: Queries the table for all rows with a score greater than or equal to 60, and displays the name column in descending order, the default is ASC ascending */select id,lastname from the person where ID >5 ORDER by ID desc/*2. Fuzzy query 1). Use like for fuzzy queries note: the like operation pair is a string, for example: SELECT * from a where name like ' Zhao% ' Description: Query shows Table A, NA The first word of the Me field is Zhao's record 2). Use between to query an example in a range: SELECT * from a where ages between and 20 Description: The query shows a record of age 18 to 20 in table a 3). Make Use in to query in the enumeration value (in the latter is more than one data) example: select name from a where address in (' Beijing ', ' Shanghai ', ' Tangshan ') Description: Query form A in the address value of Beijing or Shanghai or Tangshan records, show Name field 3. Group Query 1). Use GROUP by to group query examples: Select StudentID as learner number, AVG (score) as average (note: Score here are column names) from score (note : Score here is the table name) group by StudentID 2). Use the HAVING clause to group the filter example: Select StudentID as learner number, AVG from score group by StudentID Having count (score) >1 Description: After the example above, displays the row of Count (score) >1 after grouping, because where can only be used when there is no grouping, only after grouping can use having to restrict the condition, 4. Multi-table Join query 1). inline ① Specify the join condition in the WHERE clause: select A.name,b.mark from A, a where A.name=b.name description: A record that queries the Name field in table A and table B, and displays the NA in table A The Me field and the Mark field in table B
--Delete a data/* syntax: Delete from < table name > [where < Delete condition] example: Delete from a where name= ' Wang Weihua ' (Delete row in table A with column value Wang Weihua) Note: Deleting the entire row is not deleted The field name cannot appear after the delete except for a single field */select * from Dbo.persondelete to Dbo.person where firstname= ' Carvin ' select * FROM Dbo.persondelete from person where id=126
/*1. Inserting a single row of data using insert: syntax: Insert [into] < table name > [column Name] values < column values > example: INSERT into strdents (name, gender, date of birth) values (' Wang Weihua ', ' Male ', ' 1983/6/15 ') Note: If you save the token name, all columns will be inserted sequentially 2. Use the Insert,select statement to add data from an existing table to a new table already in the syntax: INSERT INTO < existing new Table > < Column Name > select < original table column name > from < original table name > example: INSERT INTO AddressList (' name ', ' address ', ' email ') Select Name,address,email From strdents Note: The number of data, order, data type, etc. to be queried must be consistent with the inserted item */insert into Dbo.person (id,lastname,firstname) Values (126 , ' Wade ', ' Dedn ') --Insert a data select * FROM person
--Modify a data/* Syntax: Update < table name > set < column name = update value > [where < update condition;] Example: Update AddressList set age =18 where name = ' Wang Weihua ' * * Update person set lastname= ' kkkk ' where ID =5select *from person--Single line Comment-Multiline comment/* 1 line 2 lines 3 line*/
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
SQL syntax "Increase", "delete", "Change", "check"