mysql(java)总结

2012-10-28

mysql(java)小结在线文档参考?jdbc url：详细属性可参考Eg: jdbc:mysql://localhost:3306/test?userroot&

mysql(java)小结

在线文档参考

jdbc url： 详细属性可参考

Eg: jdbc:mysql://localhost:3306/test?user=root&password=&useUnicode=true&characterEncoding=utf8

mysql URL: mysql -uroot -p --default-character-set=utf8

协调 SQL 详细:

? 这就重点说下：SHOW

? 1. 查看全部库支持字符如：'gb%'? -gbk,gb2312

????? SHOW CHARACTER SET LIKE '%' ;

? 2. 查看列

? show columns from user from mysql ;
? show columns from mysql.user ;
? desc mysql.user ;
? //简化
? //更方便的查看列名模糊查询列名为 'Select%' （desc? information_schema.columns 还有更多惊喜）
? select column_name from information_schema.columns where column_name like 'Select%' ;

? 3. 查看数据库，表结构；当然你们也可以仿照下此再建自己的库，表

?? show
?? show create database information_schema\G
?? show create table mysql.user\G

? 4.权限查看

?? SHOW GRANTS FOR 'root'@'localhost';

? .....（详细参考）

SQL 详细

这就上写自己一些有感觉的sql :参考

1.只查询重复 Eg:

create?table?c?(id?int?);
insert?into?c?values?(1),(2),(3),(4),(3),(5),(6),(1);
结果:
????select?id?from?c?group?by?id?having?count(id)>1?;

2.报表查询横向输出 Eg:

Create table d(id int,name varchar(50));
insert into d values(1,'gly');
insert into d values(2,'ptgly');
insert into d values(3,'ybgly');
insert into d values(4,'ptgly');
insert into d values(5,'ybgly');
+---+-----+------+
|gly |ptgly|ybgly| ????
+---+-----+------+-
?|1?? |2???? |2????? |
+---+-----+------+

select?
????sum(case?when?name='gly'?then?1?else?0?end?)?as?gly??,
????sum(case?when?name='ptgly'?then?1?else?0?end?)?as?ptgly??,
????sum(case?when?name='ybgly'?then?1?else?0?end?)?as?ybgly??
from?d?;

3.复杂组合查询

create table table_a (No int, No2 int,num double,itime date);
insert into table_a values
???? (1234,567890,33.5,'2004-12-21'),
???? (1234,598701,44.8,'2004-11-21'),
???? (1234,598701,45.2,'2004-10-01'),
???? (1234,567890,66.5,'2004-9-21'),
???? (3456,789065,22.5,'2004-10-01'),
???? (3456,789065,77.5,'2004-10-27'),
???? (3456,678901,48.5,'2004-12-21');
按月统计销售表中货物的销售量数
查询结果如下：
? No， No2 ，?? 九月，　十月，十一月，十二月
1234，567890， 66.5 ，　 0 ，　0? ，? 33.5
1234，598701，? 0?? ，　45.2，　44.8，　0
3456，789065，　0? ，　 100，　0? ，　 0
3456，678901，　0　，??? 0，??? 0? ，? 48.5
-----------------------------------

//当然也可以使用mysql 时间函数在软件编辑时你可以输入 String[] 并根据数据动态拼写 sql( case部分！！ )
//这个例子很好哦！报表可以一句sql 得出！
select?NO,NO2,
????sum(case??when?itime?like?'2004-%9%'?then?num?else?0?end)?as?9M,
????sum(case??when?itime?like?'2004-10%'?then?num?else?0?end)?as?10M,
????sum(case??when?itime?like?'2004-11%'?then?num?else?0?end)?as?11M,
????sum(case??when?itime?like?'2004-12%'?then?num?else?0?end)?as?12M
from?table_a?group?by?no,no2?order?by?no,no2?;

4.字符集子层关系
1???? a
2???? b
11??? c
(代码11表示为1的下级)
我要通过一条句子,得出如下结果:
Create table TabTest(t_Code varchar(10),t_Name varchar(10));
insert into TabTest values('1','a');
insert into TabTest values('2','b');
insert into TabTest values('11','c');
--------------------------

select?tt1.t_Code,tt1.t_name,(?
?????case?
?????????????when?exists?(select?1?from?tabtest?tt2?
??????????????????????????????????????? where?tt2.t_code?like?CONCAT(tt1.t_code,'%')?and?
??????????????????????????????????????? tt2.t_code?<>?tt1.t_code?)??then?'you'?
?????????????else?'wu'
??????end?)?as?you_wu
from?tabtest??tt1?;

热点排行

Mysql

mysql(java)总结