SQL Syntax: "add", "delete", "modify", "query", and SQL syntax

SQL Syntax: "add", "delete", "modify", "query", and SQL syntax

/* 4. Check 1. common query syntax: select <column Name> from <Table Name> [where <query condition expression test>] [order by <column name of sorting> [asc or desc] * // * 1 ). query all data rows and columns: select * from a description: Query all rows in Table a and */select * from person

/* 2 ). query some columns -- condition query example: select I, j, k from a where f = 5 Description: Query all rows of f = 5 in Table a, and display I, j, k3 column */select ID FirstName, LastName from person where ID> 5

/* 3 ). in the query, use AS to change the column name. For example, select name as name from a where gender = 'male' Description: Query all rows with male gender in Table a. The name column is displayed, and rename the name column (name) to show */select ID as ID, FirstName as name from person

/* 4 ). query empty rows: select name from a where email is null Description: Query all rows with empty email in Table a and display the name column; in SQL statements, is null or is not null is used to determine whether the row is empty */select * from person where City is null.

/* 5 ). in the query, use the constant example: select name 'beijing' as address from a description: Query Table a, display the name column, and add the address column. The column value is 'beijing' 6 ). query returns the maximum number of rows (Keyword: top). Example 1: select top 6 name from a description: Query Table a. the first six rows of column name are displayed, top is the keyword (oracle does not have the top keyword replaced by rownum) select * from a where rownum <6 * // * 7 ). query sorting (Keyword: order by, asc, desc) Example: select name from a where grade> = 60 order by desc Description: Query all rows with a score greater than or equal to 60 in the table, the name column is displayed in descending order. The default value is ASC in ascending order */select ID, LastName from person where ID> 5 order by ID desc/* 2. fuzzy search 1 ). use like for fuzzy query. Note: The like operator only uses strings. For example, select * from a where name like 'zhao % '. Description: In the query display table, the first word of the name field is Zhao's record 2 ). use between for a query in a certain range. Example: select * from a where age between 18 and 20. Description: query the records between 18 and 20 in table a. 3 ). query using in the enumerated values (multiple data records after in) Example: select name from a where address in ('beijing', 'shanghai', 'tangshan ') description: query records in Table a with the address value Beijing, Shanghai, or Tangshan. The name field is 3. group query 1 ). use group by for grouping query example: select studentID as student number, AVG (score) as average score (Note: here the score is a column name) from score (note: the score here is the table name) group by studentID 2 ). for example, select studentID as student number, AVG from score group by studentID having count (score)> 1. Example of joining, the rows with count (score)> 1 after the group are displayed. Because the where clause can only be used when no group is available, having can only be used after the group. multi-table join query 1 ). inner join ① specify the join condition in the where clause example: select. name, B. mark from a, B where. name = B. name Description: queries records with the Same name field in Table a and Table B, and displays the name field in Table a and the mark field in table B.

-- Delete a piece of data/* Syntax: delete from <Table Name> [where <deletion condition>] example: delete from a where name = 'wang Weihua '(delete the row whose column value is Wang Weihua in Table a) Note: deleting the entire row is not to delete a single field, therefore, the field name */select * from dbo cannot appear after the delete statement. persondelete from dbo. person where FirstName = 'carvin 'select * from dbo. persondelete from person where ID = 126

/* 1. insert a single row of data using insert: Syntax: insert [into] <Table Name> [column name] values <column value> example: insert into Strdents (name, gender, date of birth) values ('wang Weihua ', 'male', '2014/1/15') Note: If the table name is omitted, all columns are inserted in sequence. use the insert and select statements to add data in an existing table to the existing new table 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 queried Data Count, sequence, and data type must be consistent with the inserted items */insert into dbo. person (ID, LastName, FirstName) Values (126, 'wade ', 'destn') -- insert a data select * from person

-- Modify a piece of 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 = 5 select * from person -- single row comment -- multi-row comment/* 1 line 2 Line 3 Line */

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.