SQL语句

按系统文本框给定的时间查询
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;
}