- 对于一个数据mysql数据库,使用的语句就是SQL语句。给出数据库地址:xx.xx.xx.xx:xx ,用户名:xx,密码:xx,数据库名称:xx。本地没有mysqo数据库,使用navicat直接连接远程数据库,填好给定的信息即可连接。navicat支持查询语句和函数的编写,并可以直接运行。
- 对于表answer,SELECT出answer.parentId属性作为第一列,answer.CreationDate和answer.Body两个属性合并成一个,以空格分开作为第二列(将一行中的两列String合并成一列)。条件是answer.Body中含有“http://stackoverflow.com/questions/”字符串。
SQL语句使用的是concat(str1,str2,str3)实现合并项;like '%pattern%'表示含有特定模式SELECT answer.ParentId AS QuestionID, concat(answer.CreationDate,' ',answer.Body) AS caoncat FROM answer WHERE answer.Body LIKE '%http://stackoverflow.com/questions/%'
- UNION用于合并多个SELECT语句的结果集。注意,UNION内部的SELECT语句必须拥有相同数量的列,列的数据类型和顺序必须相同。下面就是对answer表和comment表SELECT的结果进行合并。
SELECT tt.QuestionID AS QuestionID, tt.AnswerBody AS AnswerBody FROM ((SELECT answer.ParentId AS QuestionID, answer.Body AS AnswerBody FROM answer WHERE answer.Body LIKE '%http://stackoverflow.com/questions/%' ) UNION (SELECT `comment`.PostId AS QuestionID, `comment`.Text AS AnswerBodyxiamian FROM `comment` WHERE `comment`.Text LIKE '%http://stackoverflow.com/questions/%' )) AS tt
-
连接包括内连接和外连接。
- 内连接:表中的行相互连接。结果集中的行数等于每个表满足连接条件的行数的乘积,参与连接的表是平等的。
select <属性或表达式列表> from <表名> [inner] join <表名> on <连接条件> [ where <限定条件> ]
上面的例子,就是对得到的UNION结果和tag表做内连接,连接条件是id相等,结果是命名为表bb;对于tag进行GROUP BY,从而可以计算每一个tag的数量count(bb.answerTag),按照数量由大到小排序:ORDER BY count(bb.answerTag) DESC;如果计算tag总数量,则不需要GROUP BY。SELECT bb.answerTag, Count(bb.answerTag), COUNT(bb.answerTag)/20483 FROM (SELECT tt.QuestionID AS QuestionID, tt.AnswerBody AS AnswerBody, gg.tag AS answerTag FROM ((SELECT answer.ParentId AS QuestionID, answer.Body AS AnswerBody FROM answer WHERE answer.Body LIKE '%http://stackoverflow.com/questions/%' ) UNION (SELECT `comment`.PostId AS QuestionID, `comment`.Text AS AnswerBody FROM `comment` WHERE `comment`.Text LIKE '%http://stackoverflow.com/questions/%' )) AS tt, tag AS gg WHERE tt.QuestionID=gg.questionId) AS bb GROUP BY bb.answerTag ORDER BY Count(bb.answerTag) DESC
-
外连接:参与的表有主次之分:主表的每一行数据去匹配从表的数据列,符合连接条件的数据直接返回到结果集中,不符合连接条件的数据列返回null。因此分为左连接、右连接、全连接
上面的语法是左连接的语法。select <属性或表达式列表> from <表名> left outer join <表名> on <连接条件> [ where <限定条件> ]
- 将SELECT的结果数据插入到特定表中的指定列里面:在表questions中,找出所有tag包含apache-poi的项,查询他们的ID,并将他们的title和body合并在一起;将查询的结果插入到apachepoi的表中。
在上述SQL代码中,使用了concat进行字符串连接,需要注意:当连接的字符串中有一个是null,那么得到的结果也是null。要想得到效果:其中需要连接的字符串若是null,就忽略它,继续连接不是null的字符串。那么源代码的第四行应该改成:INSERT INTO apachepoi(QuestionID,TitleQuestion) SELECT questions.Id AS QuestionID, concat(questions.Title,' ',questions.Body) AS TitleQuestion FROM questions WHERE questions.Tags LIKE '%apache-poi%'
INSERT INTO apachepoi(QuestionID,TitleQuestion) SELECT questions.Id AS QuestionID, concat(IFNULL(questions.Title,''),' ',IFNULL(questions.Body,'')) AS TitleQuestion FROM questions WHERE questions.Tags LIKE '%apache-poi%'
- 在mysql中,我们想要对于一列进行group合并,即在A列中,一样的项进行group,在b列中的项进行有分隔符的合并。原来数据是这样:
我想要变成这样:+------+------+ | id| name | +------+------+ |1 | 10| |1 | 20| |1 | 20| |2 | 20| |3 | 200 | |3 | 500 | +------+------+
应该使用GROUP_CONCAT配合GROUPBY来完成。+------+--------------------+ | id| group_concat(name) | +------+--------------------+ |1 | 10,20,20| |2 | 20 | |3 | 200,500| +------+--------------------+
select id,group_concat(name SEPARATOR ',') from aa group by id;
- 使用java代码连接mysql的远程数据库和连接本地数据库是一样的,是不过把连接的地址改变成远程地址。
上述代码主要是展示mysql数据库的连接和SELECT查询,并将查询结果中抽出需要的列进行处理。当然,处理插入数据,就要使用dbstate.executeUpdate(sql);public static Map<String, String> go() { Connection conn = null; Statement dbstate = null; ResultSet dbresult = null; //查询语句执行后的结果 try { Class.forName("com.mysql.jdbc.Driver"); //启动mysql数据库驱动 conn = DriverManager.getConnection( "jdbc:mysql://192.111.1.111:1111/test", "1111", "111111"); //连接语句:连接地址、用户名。密码 System.out.println(" 远程Mysql连接测试:" + conn); dbstate = conn.createStatement(); //生成语句变量 String sql = "SELECT pp.QuestionID AS QuestionID,pp.AnswerBody AS AnswerBody " + "FROM(SELECT tt.QuestionID AS QuestionID,tt.AnswerBody AS AnswerBody,qq.Tags AS tagtemp " + "FROM ((SELECT answer.ParentId AS QuestionID,answer.Body AS AnswerBody FROM answer " + "WHERE answer.Body LIKE '%http://stackoverflow.com/questions/%') UNION " + "(SELECT `comment`.PostId AS QuestionID,`comment`.Text AS AnswerBody FROM `comment` " + "WHERE `comment`.Text LIKE '%http://stackoverflow.com/questions/%')) AS tt," + "questions AS qq WHERE tt.QuestionID=qq.Id)AS pp WHERE pp.tagtemp LIKE '%apache-poi%'"; dbresult = dbstate.executeQuery(sql); //执行查询语句 while (dbresult.next()) { String temp1 = dbresult.getString("QuestionID"); //遍历查询语句,取出相应属性的项 } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { try { conn.close(); //无论如何关闭数据库连接 } catch (SQLException e) { e.printStackTrace(); } } return mapp; }
- 在搬砖过程中,熟悉了set的使用:
Set<String> stopWordSet = new HashSet<String>(); Map<String, Integer> WordDic = new HashMap<String, Integer>(); stopwordset.add(stopword); WordDic.put(tsplit, 1); stopWordSet.contains(tsplit) WordDic.containsKey(tsplit)
遍历输出:(1)对于set直接生成相应的Iterator,然后遍历next();
(2)
map根据value值排序(若value是Integer)
(2)
Set<Map.Entry<String, String>> allset = mapper.entrySet();
Iterator<Map.Entry<String, String>> a = allset.iterator();
while (a.hasNext()) {
Map.Entry<String, String> me = a.next(); // 进行key和value分离
System.out.println(me.getKey() + "--->" + me.getValue()); // 输出关键字和内容
}
List<Map.Entry<String, Integer>> infoIds = new ArrayList<Map.Entry<String, Integer>>(WordDic.entrySet());
Collections.sort(infoIds,
new Comparator<Map.Entry<String, Integer>>() {
public int compare(Map.Entry<String, Integer> o1,
Map.Entry<String, Integer> o2) {
return (o2.getValue() - o1.getValue());
}
});