/* 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: query 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 the I,J,K3 column */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 in table A that are empty e-mail, and display the Name column; SQL statement with IS NULL or is not NULL to Infer 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 the Address column. The column values are ' Beijing ' 6. Query return limit number of rows (keyword:top) example 1:select Top 6 name from a Description: Query table A, display the first 6 rows of column name, top is keyword (no topkeywo in Oracle RD replaces with rownum) SELECT * from a where rownum<6 *//* 7). Query Sort (keyword:order By, ASC, DESC) Example: Select name from a where grade>=60 ORDER by DESC Description: Query table for all rows with scores greater than or equal to 60, and display Nam in descending order e-column; The default is ASC ascending */select id,lastname from the person where ID >5 the order by ID desc/*2. Fuzzy query 1). Use like to make a fuzzy query note: the LIKE operator only uses the term character String. Example: SELECT * from a where name like ' Zhao% ' Description: The query shows Table A, the Name field the first word for Zhao's record 2). Use between to query an example in a range: SELECT * from a where a GE between and 20 description: The query shows a record of age 18 to 20 in table a 3). Use in to query within enumeration values (in the case of multiple data) example: select name from a where address I N (' Beijing ', ' Shanghai ', ' Tangshan ') Description: Query table A for the address value in Beijing or Shanghai or Tangshan records, display the Name field 3. Group Query 1). Use GROUP by to group query example: Select StudentID as student number, A VG (score) as average score (gaze: Here The score is the column name) from score (gaze: here SCORe is the table name) group BY StudentID 2). Use the HAVING clause for grouping filter examples: Select StudentID as learner number, AVG from score Group B Y StudentID having count (score) >1 Description: After the example above, show the rows 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 Conditions. 4. Multi-table Join query 1). INNER JOIN ① Specify the join condition in the WHERE clause: select A.name,b.mark from A, where A.name=b.name Description: Query table A and table B in the name word Records with the same segment, and displays the Name field in table A 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 Except for a single field. Therefore, the field name cannot appear after the delete */select * from Dbo.persondelete from 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 token name, you will insert all columns 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
--Change 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 gaze-Multiline gaze/* 1 line 2 lines 3 line*/
SQL syntax "Increase", "delete", "Change", "check"