In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article introduces the knowledge of "what are the most commonly used SQL sentences". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
(1) data record filtering: sql= "select*from data Table where Field name = Field value orderby Field name [desc]" sql= "select*from data Table where Field name like'% Field value% 'orderby Field name [desc]" sql= "selecttop10*from data Table where Field name orderby Field name [desc]" sql= "select*from data Table where Field name in (' value 1 'value 2' 'value 3') "sql=" select*from data Table where field name between value 1and value 2 "(2) Update data record: sql=" update data Table set field name = field value where conditional expression "sql=" update data table set field 1 = value 1, field 2 = value 2. Field n = value nwhere conditional expression "(3) Delete data record: sql=" deletefrom data Table where conditional expression "sql=" deletefrom data Table (delete all records from the data table) (4) add data record: sql= "insertinto data Table (Field 1, Field 2, Field 3...) Values (value 1, value 2, value 3...) " Sql= "insertinto destination data Table select*from Source data Table" (add records from the source data table to the destination data table) (5) data record statistics function: AVG (field name) obtains a table column average COUNT (* | field name) statistics on the number of data rows or data rows with value for a column MAX (field name) gets the maximum value of a table column MIN (field name) The smallest value of the table bar SUM (field name) the method of adding the values of the data column to refer to the above function: sql= "selectsum (field name) as alias from data table where conditional expression" setrs=conn.excute (sql) uses rs ("alias") to obtain the value of the system The other functions are the same as above. (5) data table creation and deletion: CREATETABLE data table name (field 1 type 1 (length), field 2 type 2 (length) …) Example: CREATETABLEtab01 (namevarchar (50), datetimedefaultnow ()) DROPTABLE data table name (permanently delete a data table) 4. Recordset object method: rs.movenext moves the record pointer down one line from the current position rs.moveprevious moves the record pointer up one line from the current position rs.movefirst moves the record pointer to the first row of the data table rs.movelast moves the record pointer to the last row of the data table rs.absoluteposition=N moves the record pointer to row N of the data table rs.absolutepage=N moves the record pointer to the first row of page N rs.pagesize=N settings per page Rs.pagecount returns the total number of pages for N records according to the setting of pagesize rs.recordcount returns the total number of records rs.bof returns whether the record pointer exceeds the header of the data table True means yes, false means no rs.eof returns whether the record pointer exceeds the end of the data table, true means yes, false means no rs.delete deletes the current record, but the record pointer does not move rs.addnew down to add records to the end of the data table rs.update update data table record Sql = "Select Distinct field name From data table" Distinct function Query the non-duplicated records in the database Sql = "Select Count (*) From data table where field name 1 > # 18 From 0 and field name 1 < # 19 From 0 records" count function, query how many records are in the database table "Field name 1" refers to the same field example: set rs=conn.execute ("select count (id) as idnum from news") response.write rs ("idnum") sql= "select * from data Table where field name between value 1 and value 2" Sql= "select * from data Table where field name between # 2003-8-1" and # 2003-8-1 "look for all records from 2003-8-10 to 2003-8-12 in fields with date class values of 2003-8-10 19:55:08 No matter what time it is. The data format in the select * from tb_name where datetime between # 2003-8-1 "and # 2003-8-1" field is 2003-8-10 19:55:08. All records from 2003-8-10 to 2003-8-12 are found by sql, no matter what time it is. Sql= "select * from data Table where Field name = Field value order by Field name [desc]" Sql= "select * from data Table where Field name like'% Field value% 'order by Field name [desc]" Fuzzy query Sql= "select top 10 * from data Table where Field name order by Field name [desc]" find the first 10 records in the database Sql= "select top n * form data Table order by newid ()" randomly fetch the database The method of several records in top n N is the number of records to be fetched Sql= "select * from data table where field name in ('value 1' SQL 'value 2')" delete a column of a table alter table bankbill drop column zsl alter table xx alter/add/drop column xx replication database table select * into new table name from table name where 1 copy 2 to completely copy where 1 to 2, and that's all for "what are the most commonly used SQL statements?" Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 280
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.