Commonly used SQL statements

SELECT

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] "

Sql= "select top 10 * from data Table where Field name order by 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 1 and value 2"

Select * (column name) from table_name (table name) where column_name operator value

Ex: (host)

Select * from stock_information where stockid = str (nid)

Stockname = 'str_name'

Stockname like'% find this%'

Stockname like'[a-zA-Z]%'- ([] specifies a range of values)

Stockname like'[^ Fmurm]%'- (^ excludes the specified range)

-wildcards can only be used in where clauses that use the like keyword)

Or stockpath = 'stock_path'

Or stocknumber

< 1000 and stockindex = 24 not stock*** = 'man' stocknumber between 20 and 100 stocknumber in(10,20,30) order by stockid desc(asc) --------- 排序，desc-降序，asc-升序 order by 1,2 --------- by 列号 stockname = (select stockname from stock_information where stockid = 4) --------- 子查询 --------- 除非能确保内层 select 只返回一个行的值， --------- 否则应在外层 where 子句中用一个 in 限定符 select distinct column_name form table_name --------- distinct 指定检索独有的列值， 不重复 select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name select stockname , "stocknumber" = count(*) from table_name group by stockname --------- group by 将表按行分组,指定列中有相同的值 having count(*) = 2 --------- having 选定指定的组 select * from table1, table2 where table1.id *= table2.id -------- 左外部连接，table1 中有的而 table2 中没有得 以 null 表示 table1.id =* table2.id -------- 右外部连接 select stockname from table1 union [all] ----- union 合并查询结果集，all-保留重复行 select stockname from table2 insert sql="insert into 数据表 (字段 1,字段 2,字段 3 ) valuess (值 1,值 2,值 3 )" sql="insert into 目标数据表 select * from 源数据表" (把源数据表的记录添加到 目标数据表) insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx") value (select Stockname , Stocknumber from Stock_table2)---value 为 select 语句 update sql="update 数据表 set 字段名=字段值 where 条件表达式" sql="update 数据表 set 字段 1=值 1,字段 2=值 2 字段 n=值 n where 条件表 达式" update table_name set Stockname = "xxx" [where Stockid = 3] Stockname = default http://hi.baidu.com/ttcc2009 Stockname = null Stocknumber = Stockname + 4 delete sql="delete from 数据表 where 条件表达式" sql="delete from 数据表" (将数据表所有记录删除) delete from table_name where Stockid = 3 truncate table_name ----------- 删除表中所有行，仍保持表的完整性 drop table table_name --------------- 完全删除表 select *(列名) from table_name(表名) where column_name operator value ex:(宿主) select * from stock_information where stockid = str(nid) stockname = 'str_name' stockname like '% find this %' stockname like '[a-zA-Z]%' --------- ([]指定值的范围) stockname like '[^F-M]%' --------- (^排除指定范围) --------- 只能在使用 like 关键字的 where 子句中使用通配符) or stockpath = 'stock_path' or stocknumber < 1000 and stockindex = 24 not stock*** = 'man' stocknumber between 20 and 100 stocknumber in(10,20,30) order by stockid desc(asc) --------- 排序，desc-降序，asc-升序 order by 1,2 --------- by 列号 stockname = (select stockname from stock_information where stockid = 4) --------- 子查询 --------- 除非能确保内层 select 只返回一个行的值， --------- 否则应在外层 where 子句中用一个 in 限定符 select distinct column_name form table_name --------- distinct 指定检索独有的列值， 不重复 select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name select stockname , "stocknumber" = count(*) from table_name group by stockname --------- group by 将表按行分组,指定列中有相同的值 having count(*) = 2 --------- having 选定指定的组 select * from table1, table2 where table1.id *= table2.id -------- 左外部连接，table1 中有的而 table2 中没有得 以 null 表示 table1.id =* table2.id -------- 右外部连接 select stockname from table1 union [all] ----- union 合并查询结果集，all-保留重复行 select stockname from table2 insert sql="insert into 数据表 (字段 1,字段 2,字段 3 ) valuess (值 1,值 2,值 3 )" sql="insert into 目标数据表 select * from 源数据表" (把源数据表的记录添加到 目标数据表) insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx") value (select Stockname , Stocknumber from Stock_table2)---value 为 select 语句 update sql="update 数据表 set 字段名=字段值 where 条件表达式" sql="update 数据表 set 字段 1=值 1,字段 2=值 2 字段 n=值 n where 条件表 达式" update table_name set Stockname = "xxx" [where Stockid = 3] Stockname = default http://hi.baidu.com/ttcc2009 Stockname = null Stocknumber = Stockname + 4 IN 操作符 IN 操作符允许我们在 WHERE 子句中规定多个值。 SQL IN 语法 SELECT column_name(s)FROM table_nameWHERE column_name IN (value1,value2,...) AND 和 OR 运算符 AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。 如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。 如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。 where 如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句。 SELECT 列名称 FROM 表名称 WHERE 列 运算符 值 操作符描述= 等于不等于>

Greater than =

Greater than or equal to