jdbc url： 详细属性可/strong>
Eg: jdbc:mysql://localhost:3306/test?user =root&password=&useUnicode=true&characterEncoding=utf8
mysql URL: mysql -uroot -p --default-character-set=utf8
这就重点说下：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';
.....（详细 ）
1.只查询重复 Eg:
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 |
+---+-----+------+
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
-----------------------------------------------------------------------
//这个 例子很好 哦！报表可以一句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');
--------------------------------------------------------------
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 ;
