按系统文本框给定的时间查询select Booknames,Parents,pulishTime,price,synopsis,maker,pulisher from tb_mrbooks where pulishtime='"+request.getParameter("text1")+"'
-----------------------------------------------------------查询制定时间段的数据
String sql ="select * from tb_BookSell where selldate between '"+request.getParameter("txt1")+"' and '"+request.getParameter("txt2")+"'order by selldate "; String sql ="select * from tb_BookSell where selldate >'"+request.getParameter("txt1")+"' and selldate < '"+request.getParameter("txt2")+"'order by selldate ";
---------------------------------------------------------------多条件排序select bookid,bookname,author,price,selldate from tb_BookSell order by bookid asc,selldate desc---------------------------------------------------------------------对统计结果进行排序select top 3 bookid,bookname,author,publisher, sum(total) as sum from tb_BookSell group by bookid,bookname,author,publisher order by 5 desc---------------------------------------------------------------------单列数据分组统计select publisher,sum(total) as totalmoney from tb_BookSell group by publisher order by 2 desc------------------------------------------------------------------------多列数据分组统计
select StorePosition,bookname,sum(storecount) as totalcount from tb_Bookrkb group by storeposition,bookname
-----------------------------------------------------------------------多表数据分组统计
select k.bookid,k.bookname,x.author, k.currCount ,sum(x.sellcount)as sellcount from tb_tsxs x ,tb_tskc k where x.bookid=k.bookid group by k.bookid,k.bookname,x.author, k.currCount
-----------------------------------------------------------------------利用聚集函数进行汇总
select sum(math) as summath ,sum(english) as sumenglish ,sum(chinese) as sumchinese ,sum(history) as sumhistory from tb_stuMark"; ResultSet rs=connection.executeQuery(sql)-----------------------------------------------------------------------------利用聚集函数求平均值select avg(math) as avgmath ,avg(english) as avgenglish ,avg(chinese) as avgchinese ,avg(history) as avghistory from tb_stuMark------------------------------------------------------------------------------利用聚集函数（MIN）求最小值select id,jewelname,price as minprice, shop from tb_jewelsell where price in (select min(price) from tb_jewelsell)----------------------------------------------------------------------------------
利用聚集函数（Max）求最大值
select tb1.seller,tb1.jewelsale as maxsale from ( select seller,sum(jewelsale) as jewelsale from tb_jewelseller where month(selldate)='12' group by seller )as tb1 where tb1.jewelsale = ( select max(jewelsale) as maxsale from ( select seller,sum(jewelsale) as jewelsale from tb_jewelseller where month(selldate)='12' group by seller )as tb1)"-------------------------------------------------------------------------------
利用聚集函数COUNT求日销售额大于某值的图书种类数
select count(distinct(bookname)) as booktype from (select bookname,selldate,sum(total) as sumtotal from tb_BookSell group by bookname,selldate having sum(total) > 150 ) as tb1
--------------------------------------------------------------------------
利用FROM子句进行多表查询
select tb_stuInfo.StuID,tb_stuInfo.StuName,tb_stuInfo.StuSex,tb_stuInfo.StuBirthDay,tb_stuInfo.StuAge,tb_stuInfo.StuSpeciality,tb_StuMark.math from tb_stuInfo ,tb_StuMark where tb_stuInfo.stuID=tb_StuMark.id and tb_StuMark.math >85
-------------------------------------------------------------------------
使用表的别名
select S.StuID as stuid,S.stuname as stuname,M.math as math,M.english as english,M.chinese as chinese,M.history as history,S.stucollege as college from tb_stuinfo as S,tb_stumark as M where S.stuid=M.id and S.stucollege='管理学院'---------------------------------------------------------------------------
合并多个结果集
select id as id,name as name,city,postalcode from tb_Customers union select id,name,address,postalcode from tb_employees
---------------------------------------------------------------------------简单嵌套查询
select stuid,stuname,stusex,stubirthday,stuage,stucollege from tb_stuinfo where stuname in (select stuname from tb_stumark where ((math+english+chinese+history)/4)>=80 )
------------------------------------------------------------------------------复杂嵌套查询
select * from tb_mrgzslb where salaryMonth=10 and name in ( select principal from tb_mrbmb where principal in (select name from tb_mrryb where educationallevel='本科')) order by id
-------------------------------------------------------------------------子查询
select * from (select id,name,arith,chinese,english from tb_cjd )tb------------------------------------------------------------------------
用子查询作为表达式
select id,stuname,english,( select avg(english) from tb_stuMark ) as avgEnglish,(english-( select avg(english) from tb_stumark )) as diffAvgEnglish from tb_stuMark
---------------------------------------------------------------------------用子查询关联数据
select stuname,stucollege,stuaddress from tb_stuInfo I where exists (select StuName from tb_stuMark M where M.stuName=I.stuName and english >80)
----------------------------------------------------------------------------多表联合查询
select * from tb_stu2004 union select * from tb_stu2005 union select * from tb_stu2006
--------------------------------------------------------------------------------
对联合查询的结果进行排序
select * from tb_stu2004 union select * from tb_stu2005 union select * from tb_stu2006 order by age desc
-----------------------------------------------------------------------------
条件联合查询
select * from tb_stu2004 where speciality='计算机' union select * from tb_stu2005 where speciality='计算机' union select * from tb_stu2006 where speciality='计算机'
-----------------------------------------------------------------------
简单内连接查询
select * from tb_mrgzslb as a inner join tb_mrbmb as b on a.departmentName=b.name where salaryMonth='3'
------------------------------------------------------------------复杂内连接
select fullname as name, tsum1 as price from (select a.tradecode, a.fullname, a.averageprice, b.qty1, b.tsum1 from tb_stock a inner join (select sum(qty) as qty1, sum(tsum) as tsum1, fullname from tb_warehouse_detailed group by fullname) b on a.fullname = b.fullname where (a.price > 0 )) tb1
-------------------------------------------------------------------自连接
select a.name as name1,a.realincome as realincome1,b.name as name2,b.realincome as realincome2 from tb_salary a inner join tb_salary b on a.realincome>b.realincome
----------------------------------------------------------------------左外连接LEFT OUTER JOIN查询
select b.id as ryid,b.name as name, b.educationallevel as educationallevel,b.technical as technical,a.id as bmid,a.name as bmname,a.principal as principal from tb_mrbmb a left outer join tb_mrryb b on a.name=b.departmentname
-----------------------------------------------------------------------
右外连接right OUTER JOIN查询
select b.id as ryid,b.name as name,b.salary as salary, b.salarymonth as salarymonth,a.id as bmid,b.departmentname,a.principal as principal from tb_mrbmb a right outer join tb_mrgzslb b on a.name=b.departmentname where b.salaryMonth='10'
---------------------------------------------------------------------使用外连接进行多表联合查询
select tb_employee.id, tb_employee.employeeid, tb_employee.employeename, tb_laborage.laborageid, tb_laborage.salarymonth, tb_laborage.basepay, tb_Job.leavedays, tb_Job.amerce from (tb_employee left join tb_Job on tb_employee.employeeid=tb_Job.employeeid) left join tb_laborage on tb_employee.employeeid=tb_laborage.employeeid
------------------------------------------------------------------------利用IN谓词限定查询范围
select * from tb_mrgzslb where salary in (select salary from tb_mrgzslb where salary between '"+request.getParameter("text1")+"' and '"+request.getParameter("text2")+"' ) and salarymonth='10'
----------------------------------------------------------------------
用IN查询表中的记录信息
select b.ISBN,BookName,Type,publisher,Writer,b.Price,sum(number)as total from tb_bookinfo as b INNER JOIN tb_order_detail as d ON b.ISBN=d.ISBN where b.BookName IN('"+request.getParameter("text1")+"') Group by b.ISBN,BookName,Type,publisher,Writer,b.Price
---------------------------------------------------------------------由IN引入的关联子查询
select StuName,StuSpeciality,StuAddress from tb_StuInfo where StuName in (select StuName from tb_StuMark where english >80)
---------------------------------------------------------------------
静态交叉表查询
select name, sum(case departmentname when 'food' then achievement else NULL end) as [foodDepartment],sum(case departmentname when 'cloth' then achievement else NULL end) as [clothDepartment],sum(case departmentname when 'furniture' then achievement else NULL end) as [furnitureDepartment] from tb_xs group by name
-------------------------------------------------------------------------动态交叉表
----------------------
对查询结果进行格式化（四舍五入）
select id,name,basepay,allowame,assistance,round(dibsoflastmonth,0) as dibsoflastmonth,round(total,0) as total,round(dibs,0) as dibs,round(total,0) as total ,realincome from tb_gzb"; ResultSet rs=connection.executeQuery(sql)
----------------------------------------------------------------------------
在查询中使用字符串函数（根据员工的身份证号码获取出生日期）
select id,ygname,substring(code,7,8)as birthday,code,degree,job,dimission from tb_staffer
----------------------------------------------------------------------------
在查询中使用日期函数（根据员工的生日计算员工的年龄）
select id,name,birthday ,datediff(year,birthday,'"+datestr+"')as age ,address,postalcode from tb_employees--------------------------------------------------------------------------
利用HAVING语句过滤分组数据
select distinct departmentname,count(*) gs,max(salary) maxsalary,avg(salary) as avgsalary from tb_mrgzslb where salarymonth='10' group by departmentname having avg(salary)>1500
--------------------------------------------------------------------------
创建数据库
创建数据库 public int createDB(String name) { String sql = "create database "+name+";" ; System.out.println("SQL:"+sql); int rtn=0; try{ conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mysql?user=root&password=111&useUnicode=true"); stmt = conn.createStatement(); rtn = stmt.executeUpdate(sql); System.out.println("rtn:" + rtn); }catch(SQLException ex){ rtn=0; System.out.println(ex.getMessage()); }finally{} return rtn;}
