mysql 查询

http://www.cnblogs.com/suxiaolong/p/5534167.html

简单的基本的sql语句

选择：select * from table1 where 范围插入：insert into table1(field1,field2) values(value1,value2)删除：delete from table1 where 范围更新：update table1 set field1=value1 where 范围查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!排序：select * from table1 order by field1,field2 [desc]总数：select count as totalcount from table1求和：select sum(field1) as sumvalue from table1平均：select avg(field1) as avgvalue from table1最大：select max(field1) as maxvalue from table1最小：select min(field1) as minvalue from table1

 

下列查询寻找雇员数超过 4 的部门的最高和最低薪水： 

SELECT WORKDEPT, MAX(SALARY) AS MAXIMUM, MIN(SALARY) AS MINIMUM FROM EMPLOYEE GROUP BY WORKDEPT HAVING COUNT(*) > 4 ORDER BY WORKDEPT 

 

SELECT `uid`,`nickname`,MAX(`level`) as `level`,SUM(online_soon) AS online_soon,
        MAX(online_soon_all) AS online_soon_all,SUM(got_copper) AS got_copper,MAX(now_copper) AS now_copper,
        SUM(got_amber) AS got_amber,MAX(now_amber) AS now_amber,SUM(toy_times) AS toy_times,
        SUM(kill_monster_num) AS kill_monster_num,SUM(kill_moster_gold_num) AS kill_moster_gold_num 
        FROM top100_logs WHERE `statistic_date` BETWEEN 20160526 AND 20160526 GROUP BY `uid`,`nickname` HAVING COUNT(*) >0 ORDER BY `level` DESC LIMIT 0,100

 

1、说明：创建数据库CREATE DATABASE database-name2、说明：删除数据库drop database dbname3、说明：备份sql server--- 创建 备份数据的 deviceUSE masterEXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'--- 开始 备份BACKUP DATABASE pubs TO testBack4、说明：创建新表create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)根据已有的表创建新表：A：create table tab_new like tab_old (使用旧表创建新表)B：create table tab_new as select col1,col2… from tab_old definition only5、说明：删除新表drop table tabname6、说明：增加一个列Alter table tabname add column col type注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。7、说明：添加主键： Alter table tabname add primary key(col)说明：删除主键： Alter table tabname drop primary key(col)8、说明：创建索引：create [unique] index idxname on tabname(col….)删除索引：drop index idxname注：索引是不可更改的，想更改必须删除重新建。9、说明：创建视图：create view viewname as select statement删除视图：drop view viewname