数据库语句

CREATE TABLE TBL
(id int(3) not null auto_increment,
Chinese float(3) not null,
Math float(3) not null,
English float(3) not null,
sum float(3) not null,
primary key(id)
)ENGINE=InnoDB DEFAULT CHARSET=gbk AUTO_INCREMENT=1;


SELECT * FROM data WHERE id = 1 
Insert into tbl (id,Chinese,Math,English,sum)values(4,97,37,62,233)
delete from data where id = 4
SELECT * FROM `data` order by id desc 倒叙
SELECT * FROM `tbl` order by Chinese 正序
SELECT * FROM `tbl` order by id desc limit 2 倒叙后取前两条
SELECT * FROM `tbl` order by id desc limit 0,x 倒叙后取前x条
SELECT * FROM `tbl` order by id desc limit x,y (y>x且x！=0)倒叙后取从第(x+1)条到(y+1)条


select sum(Chinese) as sumvalue from tbl
select avg(Chinese) as avgvalue from tbl
select max(Chinese) as "maxvalue" from tbl
select min(Chinese) as "minvalue" from tbl


alter table tbl add column history float(3) not null
alter table tbl add primary key(Chinese)
create index Chinese_Math on tbl(Chinese,Math)
CREATE UNIQUE INDEX Chinese_Math on tbl(Chinese,Math)
DROP index Chinese_Math on tbl
注：索引是不可更改的，想更改必须删除重新建。
select * from tbl left join data on tbl.id = data.id
select * from tbl right join data on tbl.id = data.id
SELECT * FROM `data` WHERE address like 'h%'
SELECT * FROM `data` where address = "hello"

SELECT * FROM `data` where num = 28

select id,count(num) 数量 from data group by id

GROUP BY:

所有被select的项（除sum，max等项之外），都必须出现在group by 中。

1.AVG 返回指定组中的平均值，空值被忽略。

例：select prd_no,avg(qty) from sales group by prd_no

2. COUNT 返回指定组中项目的数量。

例：select count(prd_no) from sales

3. MAX 返回指定数据的最大值。

例：select prd_no,max(qty) from sales group by prd_no

4. MIN 返回指定数据的最小值。

例：select prd_no,min(qty) from sales group by prd_no

5. SUM 返回指定数据的和，只能用于数字列，空值被忽略。

例：select prd_no,sum(qty) from sales group by prd_no

6. COUNT_BIG 返回指定组中的项目数量，与COUNT函数不同的是COUNT_BIG返回bigint值，而COUNT返回的是int值。

例：select count_big(prd_no) from sales

7. GROUPING 产生一个附加的列，当用CUBE或ROLLUP运算符添加行时，输出值为1.当所添加的行不是由CUBE或ROLLUP产生时，输出值为0.

例：select prd_no,sum(qty),grouping(prd_no) from sales group by prd_no with rollup

8. BINARY_CHECKSUM 返回对表中的行或表达式列表计算的二进制校验值，用于检测表中行的更改。

例：select prd_no,binary_checksum(qty) from sales group by prd_no

9. CHECKSUM_AGG 返回指定数据的校验值，空值被忽略。

例：select prd_no,checksum_agg(binary_checksum(*)) from sales group by prd_no

10. CHECKSUM 返回在表的行上或在表达式列表上计算的校验值，用于生成哈希索引。

11. STDEV 返回给定表达式中所有值的统计标准偏差。

例：select stdev(prd_no) from sales

12. STDEVP 返回给定表达式中的所有值的填充统计标准偏差。

例：select stdevp(prd_no) from sales

13. VAR 返回给定表达式中所有值的统计方差。

例：select var(prd_no) from sales

14. VARP 返回给定表达式中所有值的填充的统计方差。

例：select varp(prd_no) from sales

use Database
select * into TableB
from TableA where MEID in
('685257','691644','689960','685761','690795','206360','174483','206695','706379','2170','11044','3389','681035','680513','680575','211185','2281','174875','87089','5607','690441','1467','5597','680736','5660','690673','685865','4545','689591','691500','691122','175018','705865','825','8395','4579','206494','706341','4588','705973','173994','173100','3521','6306','706287','6304','10423','173016','691932','3534','208666','1517','7209','1100','5861','175589','686028','9028','169561','691150','2372','4898','4424','691288','7814','4926','6647','86484','2826','706261','4774','2507')

在Database数据库中，从表TableA里取出MEID在上述范围的所有数据，建立表TableB，将取出的所有数据放进表TableB中。

（可通过此方法，实现将A数据库中的某个表的部分数据导入到另一数据库中，即在上述SQL语句结束后将TableB导入到另一数据库中）

{=================================BetterComm=================================

delete FROM [iSON_DC_ZTE_LTE_NLB_bak2].[dbo].[CDD_ZTELTE_OMM_ACTDD_60_161026]
  where MEID not in('1315',  '1755',  '935',  '935',  '836',  '836',  '1450',  '2066',  '1066',  '676',  '336',  '336')






将源表中的数据导入到新建表
select * into CDD_ZTELTE_OMM_ACTDD_60_161026_temp from CDD_ZTELTE_OMM_ACTDD_60_161026






  update [chenkaida_ZTE_LTE_new1].[dbo].[PLUGIN_ZTE_LTE_CELL] set [IsAdjust] = 1






update [YUXB_GD_LTE_ZTE_DC].[dbo].[CDD_ZTELTE_OMM_UeEUtranMeasurementTDD_60_160414]
  set EXCHID = 'GZ2OMMB4' where EXCHID = 'ZZOMMB1' 




做数据：
  select * into iSON_DC_ZTE_LTE_1109.[dbo].[SYS_ZTELTE_CELL_CONFIG]
   from [iSON_DC_ZTE_LTE_1108].[dbo].[SYS_ZTELTE_CELL_CONFIG]
  where MEID in('172759',  '173032',  '11212',  '680495',  '685788',  '692106',  '207342',  '690842',  '8652',  '206473',  '207290',  '4711',  '4809',  '173883',  '196877',  '175587',  '3383',  '689812',  '689444',  '681712')






select * into [CKD_GuangZhou_iSON_DC_ZTE_LTE_20160418_BeiXiang].[dbo].[CDD_ZTELTE_OMM_CellMeasGroupTDD_15_160422]
   from [HGR_GuangZhou_iSON_DC_ZTE_LTE_20160418_BeiXiang].[dbo].[CDD_ZTELTE_OMM_CellMeasGroupTDD_15_160417]
  where MEID in('706341', '706379', '7209', '7814', '825', '8395', '86484', '87089', '9028')






alter table [YUXB_GD_LTE_ZTE_DC].[dbo].[CDD_ZTELTE_OMM_EUtranReselectionTDD_60_160414]
 add [selQrxLevMin] varchar(255)




alter table [YUXB_GD_LTE_ZTE_DC].[dbo].[CDD_ZTELTE_OMM_EUtranReselectionTDD_60_160414]
 drop column [selQrxLevMin]


先建新表OBJ_ZTELTE_OMM_EutranCellTdd_15_160415，然后
Insert into OBJ_ZTELTE_OMM_EutranCellTdd_15_160415(
[DATE],
[PERIOD] ,
[SubNetwork] ,
[MEID] ) 
select 
[DATE],
[PERIOD] ,
[SubNetwork] ,
[ManagedElement]  
from OBJ_ZTELTE_OMM_EutranCellTdd_15_160414

=================================BetterComm=================================}

实现行转列：

源表：

year month number
2016 1 11.1
2016 2 22.2
2016 3 33.3
2017 1 11.1
2017 2 22.2
2017 3 33.3

select year,
 MAX(case MONTH when '1' then number else 0 end)  N1,
MAX(case MONTH when '2' then number else 0 end ) N2,
MAX(case MONTH when  '3' then number else 0 end) N3
from [chenkaida_iSON_ZTE_LTE_ADJ].[dbo].[table3]
group by year

通过以上语句可转为：

year N1 N2 N3
2016 11.1 22.2 33.3
2017 11.1 22.2 33.3

数据库为SQL server 2008，如果是MySql，则用IF函数

表结构中，year monthnumber三列为float，如果为VARCHAR，则执行时会出错，如果为int型，则number列为整数时，上面的SQL语句执行依然正常