Z-Blog博客转换到Blogger的方法

虽然Google的Blogspot在国内无法访问,但Blogspot依旧是国际上最大的BSP之一,其稳定性非常不错,并且完全免费,使用Z-Blog的用户,也可以通过一些方法将博客备份到Google Blogger,下面我就介绍一下如何将Z-Blog的博客转换到Google Blogger的方法。

这个方法针对大数据量的Z-Blog博客,首先通过SQL语句转换Z-Blog数据到MySQL数据库,之后在本地安装一个PHP+MySQL+Apache的系统,安装phpMyadmin和Wordpress程序,然后在phpMyadmin中建立一个blog_Article表,然后使用“ACCESS数据库转换MYSQL数据库的软件”将数据库转换到MySQL,然后直接在MySQL之中将blog_Article表的数据插入到wp_posts表中,这样就完成了Z-Blog的数据导出到Wordpress中。

接着登录本地的Wordpress系统,在“工具”-“导出”中,将文章导出为一个XML文件。

接着,就可以将WordPress博客转换到Blogger,进行转换之前,需要将WordPress导出的XML文件进行分割,推荐使用YO2的一个小工具DivXML来分割。

将文件分割为1M左右的XML文件后,就可以到Google Blog Converters提供的这个地址进行XML转换,将WordPress的XML文件转换为Blogger的XML文件。

拿到转换后的XML文件,登录Blogger后台,使用博客导入功能将这个XML文件导入到系统,文章和评论都会被导入进来。

导入之后,登录博客的域名管理系统,将博客的域名修改为CNAME的解析方式,指到ghs.google.com,这样,就实现了Z-Blog博客转换为Google Blogger系统。

最后,访问Google Blogspot的“设置”-“电子邮件和移动博客”,设置一个电子邮件发布地址(也称为 Mail2Blogger),然后在Z-Blog中启用Mailpost发送邮件插件,这样就可以实现Z-Blog的新文章也实时同步到Google Blogger上。

最终效果展示,我这里用“月光博客”做个了一个例子,将Z-Blog的数据,成功转换到了Blogspot的这个地址:http://info.williamlong.info/ 。

View the original article here

自动生成SQL语句的工具类

大量字段的修改,插入操作的SQL语句拼写起来实在令人挠头,项目又没有用HIBERNATE之类的ORM框架,怎么办呢?我写了几个类简单应付一下,献丑了 。

表字段类:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Common.db
{
    public class FieldTypeValue
    {
        public FieldTypeValue(string fieldName, string fieldValue, bool isNum)
        {
            this.fieldName = fieldName;
            this.fieldValue = fieldValue;
            this.isNum = isNum;
        }
        public FieldTypeValue(string fieldName, string fieldValue)
        {
            this.fieldName = fieldName;
            this.fieldValue = fieldValue;
        }
        private string fieldName;
        public string FieldName
        {
            get { return fieldName; }
            set { fieldName = value; }
        }
        private bool isNum = false;
        public bool IsNum
        {
            get { return isNum; }
            set { isNum = value; }
        }
        private string fieldValue;
        public string FieldValue
        {
            get { return fieldValue; }
            set { fieldValue = value; }
        }
    }
}

数据类:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Common.db
{
    public class DataMaterial
    {
        private string tableName;
        private List<FieldTypeValue> ftvlist;
        public DataMaterial(string tableName, List<FieldTypeValue> ftvlist)
        {
            this.tableName = tableName;
            this.ftvlist = ftvlist;
        }
        public List<FieldTypeValue> Ftvlist
        {
            get { return ftvlist; }
            set { ftvlist = value; }
        }
        public string TableName
        {
            get { return tableName; }
            set { tableName = value; }
        }
    }
}

生成SQL语句的工具类:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Common.db
{
    public class SqlBuilder
    {
        private DataMaterial dm;
        private string pkName;
        private string pkValue;
        public SqlBuilder(DataMaterial dm)
        {
            this.dm = dm;
        }
        public string createInsertSql()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into ");
            sb.Append(dm.TableName);
            sb.Append("(");
            List<FieldTypeValue> ftvlist = dm.Ftvlist;
            for (int i = 0; i < ftvlist.Count; i++)
            {
                FieldTypeValue ftv = (FieldTypeValue)ftvlist[i];
                if (i != ftvlist.Count - 1)
                {
                    sb.Append(ftv.FieldName + ",");
                }
                else
                {
                    sb.Append(ftv.FieldName);
                }
            }
            sb.Append(") values(");
            for (int i = 0; i < ftvlist.Count; i++)
            {
                FieldTypeValue ftv = (FieldTypeValue)ftvlist[i];
                if (ftv.IsNum)
                {
                    if (i != ftvlist.Count - 1)
                    {
                        sb.Append(ftv.FieldValue + ",");
                    }
                    else
                    {
                        sb.Append(ftv.FieldValue);
                    }
                }
                else
                {
                    if (i != ftvlist.Count - 1)
                    {
                        sb.Append("'" + ftv.FieldValue + "',");
                    }
                    else
                    {
                        sb.Append("'" + ftv.FieldValue + "'");
                    }
                }
            }
            sb.Append(")");
            return sb.ToString();
        }
        public string createUpdateSql(string pkName, string pkValue)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update ");
            sb.Append(dm.TableName);
            sb.Append(" set");
            List<FieldTypeValue> ftvlist = dm.Ftvlist;
            for (int i = 0; i < ftvlist.Count; i++)
            {
                FieldTypeValue ftv = (FieldTypeValue)ftvlist[i];
                if (i != ftvlist.Count - 1)
                {
                    if (ftv.IsNum)
                    {
                        sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + ",");
                    }
                    else
                    {
                        sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "',");
                    }
                }
                else
                {
                    if (ftv.IsNum)
                    {
                        sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + "");
                    }
                    else
                    {
                        sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "'");
                    }
                }
            }
            sb.Append(" where " + pkName + "=" + pkValue);
            return sb.ToString();
        }
    }
}

增加例子:

public void addCustomer(int id, string code, string photo_building, string xingming, string cover,
          string size, string pages, string inpage, string receivedtime, string registertime, string finishtime, string special, string cover_special, int inprint, int outprint, string again, string onlycover,string reCover,string reInpage)
        {
            List<FieldTypeValue> ftvlist = new List<FieldTypeValue>();
            ftvlist.Add(new FieldTypeValue("cid", id.ToString(), true));
            ftvlist.Add(new FieldTypeValue("code", code));
            ftvlist.Add(new FieldTypeValue("photo_building", photo_building, true));
            ftvlist.Add(new FieldTypeValue("xingming", xingming));
            ftvlist.Add(new FieldTypeValue("cover", cover, true));
            ftvlist.Add(new FieldTypeValue("size", size, true));
            ftvlist.Add(new FieldTypeValue("pages", pages));
            ftvlist.Add(new FieldTypeValue("inpage", inpage, true));
            ftvlist.Add(new FieldTypeValue("receivedtime", receivedtime == "" ? DateTime.Now.ToString() : receivedtime));
            ftvlist.Add(new FieldTypeValue("registertime", registertime == "" ? DateTime.Now.ToString() : registertime));
            ftvlist.Add(new FieldTypeValue("finishtime", finishtime == "" ? DateTime.Now.ToString() : finishtime));
            ftvlist.Add(new FieldTypeValue("special", special));
            ftvlist.Add(new FieldTypeValue("cover_special", cover_special));
            ftvlist.Add(new FieldTypeValue("inprint", inprint.ToString(), true));
            ftvlist.Add(new FieldTypeValue("outprint", outprint.ToString(), true));
            ftvlist.Add(new FieldTypeValue("again", again, true));
            ftvlist.Add(new FieldTypeValue("onlycover", onlycover, true));
            ftvlist.Add(new FieldTypeValue("ReCover", reCover));
            ftvlist.Add(new FieldTypeValue("ReInpage", reInpage));
            DataMaterial dm = new DataMaterial("photo_customer", ftvlist);
            SqlBuilder sqlBuilder = new SqlBuilder(dm);
            string sql = sqlBuilder.createInsertSql();
            DbUtil du = new DbUtil();
            du.updateData(sql);
        }

更新例子:

public void updateCustomer(string cid, string building, string cover, string inpage, string size, string pages, string receivedtime, string registertime, string finishtime, string special, string cover_special, string inprint, string outprint, string again, string onlycover) //更新用户信息
        {
            List<FieldTypeValue> ftvlist = new List<FieldTypeValue>();
            ftvlist.Add(new FieldTypeValue("photo_building", building, true));
            ftvlist.Add(new FieldTypeValue("cover", cover, true));
            ftvlist.Add(new FieldTypeValue("size", size, true));
            ftvlist.Add(new FieldTypeValue("pages", pages));
            ftvlist.Add(new FieldTypeValue("inpage", inpage, true));
            ftvlist.Add(new FieldTypeValue("receivedtime", receivedtime == "" ? DateTime.Now.ToString() : receivedtime));
            ftvlist.Add(new FieldTypeValue("registertime", registertime == "" ? DateTime.Now.ToString() : registertime));
            ftvlist.Add(new FieldTypeValue("finishtime", finishtime == "" ? DateTime.Now.ToString() : finishtime));
            if (special != null)
            {
                ftvlist.Add(new FieldTypeValue("special", special));
            }
            if (cover_special != null)
            {
                ftvlist.Add(new FieldTypeValue("cover_special", cover_special));
            }
            if (!string.IsNullOrEmpty(inprint))
            {
                ftvlist.Add(new FieldTypeValue("inprint", inprint, true));
            }
            if (!string.IsNullOrEmpty(outprint))
            {
                ftvlist.Add(new FieldTypeValue("outprint", outprint, true));
            }
            ftvlist.Add(new FieldTypeValue("again", again, true));
            ftvlist.Add(new FieldTypeValue("onlycover", onlycover, true));
            DataMaterial dm = new DataMaterial("photo_customer", ftvlist);
            SqlBuilder sqlBuilder = new SqlBuilder(dm);
            string sql = sqlBuilder.createUpdateSql("cid", cid);
            dbutil.updateData(sql);
        }

数据库操作类:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;
namespace Common.db
{
    public class DbUtil
    {
        public SqlConnection strcon;
        public DbUtil()
        {
            strcon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString);
        }

        /*
         * 执行增删改操作
         * 参数 sql语句
         * 
         * */
        public void updateData(string sql)
        {
            openConn();
            SqlCommand cmd = new SqlCommand(sql, strcon);
            cmd.CommandTimeout = 0;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {

                throw e;
            }

            closeConn();
        }
        public object ExecuteScalar(string sql) 
        {
            openConn();
            SqlCommand cmd = new SqlCommand(sql,strcon);
            cmd.CommandTimeout = 0;
            object obj = cmd.ExecuteScalar();
            closeConn();
            return obj;
        }
        public DataSet getDataSet(string sql)
        {
            openConn();
            SqlDataAdapter sda = new SqlDataAdapter(sql, strcon);
            sda.SelectCommand.CommandTimeout = 0;
            DataSet ds = new DataSet();
            sda.Fill(ds);
            closeConn();
            return ds;
        }
        public DataSet getDataSet(string sql, SqlParameter[] paras, bool proc) 
        {
            if (proc)
            {
                openConn();
                SqlCommand cmd = new SqlCommand(sql, strcon);
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;
                if (paras != null && paras.Length > 0) 
                {
                    cmd.Parameters.AddRange(paras);
                }
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                closeConn();
                return ds;
            }
            else 
            {
                return getDataSet(sql);
            }
        }
        public SqlParameter CreateSqlParameter(string name, SqlDbType type, object value) 
        {
            SqlParameter p = new SqlParameter(name,type);
            p.Value = value;
            return p;
        }
        /*
         * 关闭连接
         * */
        public void closeConn()
        {
            if (strcon.State != ConnectionState.Closed)
            {
                strcon.Close();
            }
        }
        private void openConn()
        {
            strcon.Open();
        }
    }
}

 

面试sql语句

以学生,课程,成绩,教师四张表来讲解常用的sql,先来看,这四张表的关系

Student(S#,Sname,Sage,Ssex) 学生表

Course(C#,Cname,T#) 课程表

SC(S#,C#,score) 成绩表

Teacher(T#,Tname) 教师表

清晰明了,下面用sql建立表结构:

create database db_school;
use db_school;
drop table t_student;
create table t_student(
id char(3) primary key,
sname varchar(20),
sage  int,
ssex  varchar(2)
);
exec sp_help t_student;

create table t_teacher(
id char(3) primary key,
tname varchar(20)
);
create table t_course(
id char(3) primary key,
cname varchar(20),
t_id char(3),
foreign key(t_id) references t_teacher(id)
);
exec sp_help t_course;
create table t_sc(
s_id char(3),
c_id char(3),
primary key(s_id,c_id),
foreign key(s_id) references t_student(id),
foreign key(c_id) references t_course(id)
);
alter table t_sc add score int;

查询语句:
–1 查询“001”课程比“002”课程成绩高的所有学生的学号 ;
select * from t_sc;
select sc1.s_id from t_sc sc1,t_sc sc2 where sc1.s_id=sc2.s_id and sc1.c_id = ’001′ and sc2.c_id = ’002′ and sc1.score>sc2.score;
–2 查询平均成绩大于60分的同学的学号和平均成绩;
select s_id, avg(score) 平均成绩 from t_sc group by s_id having avg(score)>60 ;
–3 查询所有同学的学号、姓名、选课数、总成绩;
select * from t_sc;
select id,sname from t_student t;
select id,sname,tt.cnum,tt.totalScore
from t_student t
left join (
select s_id, count(c_id) cnum,sum(score) totalScore from t_sc group by s_id
)tt
on t.id= tt.s_id
–4 查询姓“李”的老师的个数
select * from t_teacher;
select count(*) from t_teacher where tname like ‘李%’
–5 查询没学过“叶平”老师课的同学的学号、姓名;
select * from t_teacher;
select ss.id,ss.sname from t_student ss where ss.id not in(
select s.id from t_student s,t_sc sc where s.id = sc.s_id and sc.c_id in (
select c.id from t_course c,t_teacher t where t.tname= ‘叶平′ and c.t_id = t.id
)
);
–6 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名 ;

select * from t_sc;
select * from t_sc sc, t_student s where s.id = sc.s_id and c_id = ’001′  and s_id in(
select s_id from t_sc sc, t_student s where s.id = sc.s_id and c_id = ’002′)

–7 查询学过“张老师”老师所教的所有课的同学的学号、姓名;

select c.id from t_teacher t,t_course c where c.t_id = t.id and t.tname = ‘张老师’;

select s.*,tt.* from t_student s right join
(
select distinct s_id from t_sc sc where sc.c_id in(
select c.id from t_teacher t,t_course c where c.t_id = t.id and t.tname = ‘张老师’
)
)tt on s.id = tt.s_id;

–8 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

思路:先查出002号课程成绩比001课程号成绩好的学生的学号,如何知道002号课程比001号课程成绩好呢?首先比较的都是成绩字段,又都涉及 到t_sc这张表,如果参加比较的是同一张表,就可以考虑自连接,下面我们先用自连接查处“002”的成绩比课程编号“001”课程低的所有同学的学号, 有了学号,就可以得到姓名了。

select sc1.s_id from t_sc sc1,t_sc sc2
where sc1.s_id = sc2.s_id — 同一个学生
and sc1.c_id = ’001′ –001科目
and sc2.c_id = ’002′    –002科目
and sc2.score<sc1.score

得到id,有了学号,怎么得到姓名呢?学生表里就有学号和姓名,关键是怎么把上面的表和学生表连接起来呢?

有几种方式,可以用表连接,也可以用右连接

1.

select t.id,t.sname from t_student t,
(select sc1.s_id from t_sc sc1,t_sc sc2
where sc1.s_id = sc2.s_id — 同一个学生
and sc1.c_id = ’001′ –001科目
and sc2.c_id = ’002′    –002科目
and sc2.score<sc1.score) tt
where t.id = tt.s_id

这种方式,我不常用,看起来很别扭.

2 看用join 的方式

select t.id,t.sname from t_student t right join(
select sc1.s_id from t_sc sc1,t_sc sc2
where sc1.s_id = sc2.s_id — 同一个学生
and sc1.c_id = ’001′ –001科目
and sc2.c_id = ’002′    –002科目
and sc2.score<sc1.score
)tt on t.id = tt.s_id –join条件,两个表的id一致才表示是一个人嘛。

以求出的学号为主表(与主从表不是一个概念),t_student为附表,因为学号为主表,所以用right join,right join 表示右边表主表,可以与第三个问题的left join,体会join 的用法。

—-9、查询所有课程成绩小于60分的同学的学号、姓名;
select t.id,t.sname from t_student t right join(
select distinct s_id from t_sc where score<60
)tt on t.id = tt.s_id

这里为什么用right join ,那么我们看一看left join 和right join 的区别

 

left join : 左边表为主表,查询结果为:连接查询结果+左表中有但右表中不符合条件的数据。

right join:右边表为主表,查询结果为:连接查询结果+右表中有但左表中不符合条件的数据。

–10 、 查询没有学全所有课的同学的学号、姓名;

select t.id,t.sname from t_student t right join(
select s_id from t_sc group by s_id having count(c_id)<(select count(*) from t_course)
) tt on t.id = tt.s_id

–11、查询至少有一门课与学号为“002”的同学所学相同的同学的学号和姓名;

12 、查询至少学过学号为“ 001 ”同学所有一门课的其他同学学号和姓名;

select t.id,t.sname from t_student t join(
select distinct s_id from t_sc where c_id in(
select c_id from t_sc where s_id = ’002′
)
)tt on t.id = tt.s_id and t.id != ’002′

13 、把“SC”表中“ 张老师’”老师教的课的成绩都更改为此课程的平均成绩;

1.join方式

update sc
set sc.score = avgscore
from t_sc sc
join (
select c_id,avg(score) avgscore from t_sc group by c_id
)t on sc.c_id = t.c_id
join t_course tt on tt.id = sc.c_id
join t_teacher ttt on ttt.id = tt.t_id and ttt.tname = ‘张老师’

2.非join

update
sc
set
sc.score = tt.avg_score
from t_sc sc,
(
select c_id , avg(score) avg_score from t_sc where c_id in(
select c.id from t_teacher t,t_course c where t.id = c.t_id and t.tname = ‘张老师’
)group by c_id
)tt
where tt.c_id = sc.c_id

14 、查询和“ 001 ”号的同学学习的课程完全相同的其他同学学号和姓名;

select stuid,Sname from t_student where stuid
in(
select aa.s_id from
(
select s_id from t_sc
group by s_id having count(*)=(select count(*) from t_sc where s_id=’001′)
–计算和001号学生上课课程数目相等的学生
) aa
inner join
(
select s_id from t_sc where c_id in(
select c_id from t_sc where s_id=’001′
) –学了001号学生所学的任何一门课程的学生
group by s_id having count(*)=(select count(*) from t_sc where s_id=’001′)
–并且与001号学生学的课程个数与001学生所学课程数目一致的学生
)bb on aa.s_id=bb.s_id and aa.s_id<>’001′
)

15 、删除学习“叶平”老师课的SC表记录

delete from t_sc
where c_id in(
select distinct c_id from t_sc sc
join t_course c on sc.c_id = c.id
join t_teacher t on c.t_id = t.id
and t.tname = ‘叶平′)

17 、按平均成绩从高到低显示所有学生的“语文”、“数学”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,有效课程数,有效平均分    

select tttt.t_s_id,
max(case cname when ‘语文’ then score else ” end)语文,
max(case cname when ‘数学’ then score else ” end)数学,
max(cnum) 有效课程数,
max(avgscore)  平均成绩
from
(
select t.s_id t_s_id,t.score,tt.cname,ttt.avgscore,ttt.cnum from t_sc t
join t_course tt on t.c_id = tt.id
and (tt.cname = ‘语文’ or tt.cname=’数学’)
join (
select s_id,avg(score) avgscore,count(c_id) cnum from t_sc group by s_id
)ttt on t.s_id = ttt.s_id –order by avgscore desc
)tttt
group by tttt.t_s_id order by 平均成绩 desc

18 、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分

select c_id,max(score) 最高分,min(score)最低分 from t_sc group by c_id;

19 、按各科平均成绩从低到高和及格率的百分数从高到低顺序    

select ttt.c_id1,ttt.avgscore,ttt.百分比 from
(
select *
from
(select c_id c_id1,avg(score) avgscore from t_sc t group by c_id)t
join (
select c_id,sum(case when score-60>=0 then 1 else 0 end)*100/count(*) 百分比 from t_sc group by c_id
)tt on tt.c_id = t.c_id1
)ttt
order by ttt.avgscore,百分比 desc

20 、查询如下课程平均成绩和及格率的百分数(用 “1行” 显示): 数学( 001 ),语文( 002 )

select
max(case when ttt.cname=’数学’ then ttt.avgscore else ” end) 数学平均成绩,
max(case when ttt.cname=’数学’ then ttt.jgpercent else ” end) 数学及格百分比,
max(case when ttt.cname=’语文’ then ttt.avgscore else ” end) 语文平均成绩,
max(case when ttt.cname=’语文’ then ttt.jgpercent else ” end) 语文及格百分比
from(
select * from
(select c_id,avg(score) avgscore,
sum(case when score-60>=0 then 1 else 0 end)*100/count(*) jgpercent
from t_sc t group by c_id)t
join
t_course tt on tt.id =  t.c_id and tt.cname in(‘语文’,'数学’)
)ttt

21 、查询不同老师所教不同课程平均分从高到低显示    

select tttt.tname,tttt.cname,avg(tttt.score) avgscore from
(
select t.*,tt.cname,tt.t_id,ttt.tname from t_sc t
join t_course tt on t.c_id = tt.id
join t_teacher ttt on ttt.id = tt.t_id
)tttt
group by tttt.cname,tttt.tname order by tttt.tname,tttt.avgscore desc;

–22、查询如下课程成绩第 2 名到第 3 名的学生成绩单:语文(001),数学(002),英语 (003) ,以及学生平均成绩

select
*
from
(
select *,row_number() over(partition by tttt.c_id order by tttt.score desc) rn from
(
select * from t_sc t
join t_course tt on t.c_id = tt.cid
join (
select s_id sid,avg(score) avgscore from t_sc group by s_id
)ttt on t.s_id = ttt.sid
join t_student s on t.s_id = s.stuid
)tttt
)ttttt
where ttttt.rn >=2 and ttttt.rn<=3

 –23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select *
from
(select t.c_id,
sum(
case when score>=0 and score<60 then 1 else 0 end
) score_0_60,
sum(
case when score>=60 and score<70 then 1 else 0 end
) score_60_70,
sum(
case when score>=70 and score<80 then 1 else 0 end
) score_70_85,
sum(
case when score>=80 and score<100 then 1 else 0 end
) score_85_100
from t_sc t
group by t.c_id)tt join t_course ttt on tt.c_id = ttt.cid

–24、查询学生平均成绩及其名次
select *,row_number() over(order by avgscore desc) rn from
(select s_id,avg(score) avgscore from t_sc group by s_id)t;

–25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
select * from t_sc order by c_id,score desc;
select * from
(select c_id,score,row_number() over(partition by c_id order by score desc) rn  from t_sc )tt
where tt.rn <=3;

 –26、查询每门课程被选修的学生数
select c_id,count(s_id) from t_sc group by c_id

–27、查询出只选修了二门课程的全部学生的学号和姓名
select * from
(
select s_id from t_sc  group by s_id having count(c_id)=2
)t
left join t_student tt on t.s_id = tt.stuid

–28、查询男生、女生人数
select * from t_student;
select ssex,count(stuid) from t_student group by ssex;

–29查询姓“张”的学生名单
select * from t_student where sname like ‘张%’;

30、查询同名同性学生名单,并统计同名人数
select sname,ssex, count(stuid) from t_student group by sname,ssex

纵表转横表

原表

select tt.*
from
  (select
  t.city,
  to_char(t.oper_date,'yyyy') 年度,
   sum(
   decode(t.status,'优',1,'良',1,0)
   ) 优良天数,
   sum(
    decode(t.grade,'Ⅰ',1,0)
   ) 一级天数,
   sum(
    decode(t.grade,'Ⅱ',1,0)
   ) 二级天数
  from 
      city_day t  
  group by to_char(t.oper_date,'yyyy'),t.city)tt
where tt.年度 = to_char(sysdate,'yyyy')-1 
or
    tt.年度 = to_char(sysdate,'yyyy')-2
order by tt.年度

转后

select ttt.city,
sum(decode(ttt.年度,to_char(sysdate,'yyyy'),ttt.优良天数,null)) 今年优良天数,
sum(decode(ttt.年度,to_char(sysdate,'yyyy'),ttt.一级天数,null)) 今年一级天数,
sum(decode(ttt.年度,to_char(sysdate,'yyyy'),ttt.二级天数,null)) 今年二级天数,
sum(decode(ttt.年度,to_char(sysdate,'yyyy')-1,ttt.优良天数,null)) 去年优良天数,
sum(decode(ttt.年度,to_char(sysdate,'yyyy')-1,ttt.一级天数,null)) 去年一级天数,
sum(decode(ttt.年度,to_char(sysdate,'yyyy')-1,ttt.二级天数,null)) 去年二级天数
from (select tt.*
      from
        (select
        t.city,
        to_char(t.oper_date,'yyyy') 年度,
         sum(
         decode(t.status,'优',1,'良',1,0)
         ) 优良天数,
         sum(
          decode(t.grade,'Ⅰ',1,0)
         ) 一级天数,
         sum(
          decode(t.grade,'Ⅱ',1,0)
         ) 二级天数
        from 
            city_day t  
        group by to_char(t.oper_date,'yyyy'),t.city)tt
      where tt.年度 = to_char(sysdate,'yyyy')
      or
          tt.年度 = to_char(sysdate,'yyyy')-1
      order by tt.年度
     )ttt
group by city

 

java笔试:记录自己的java笔试题目java written examination

java笔试是初级开发人员必过的一道门槛。这一段找工作,参加了很多java笔试,顺便把面试的题目记录下来。

一.java笔试之读txt文件

1.java笔试读txt文件方法一

import java.io.File;
import java.io.FileReader;
public class ReadTxt {
    public static void main(String[] args) {
        File f = new File("D:/sql.txt");
        try {
            FileReader fr = new FileReader(f);
            char[] temp= new char[(int)f.length()];
            fr.read(temp);
            StringBuffer sb = new StringBuffer();
            for(int i=0;i<temp.length;i++){
                sb.append(temp[i]);
            }
            System.out.println(sb.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2.java笔试读txt文件方法2

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
public class ReadTxt1 {
    public static void main(String[] args) {
        try {
            FileInputStream fis = new FileInputStream("D:/sql.txt");
            BufferedReader br = new BufferedReader(new InputStreamReader(fis));
            String s;
            StringBuffer sb = new StringBuffer();
            while((s = br.readLine())!=null){
                sb.append(s + " /n");
            }
            System.out.println(sb.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

对于java笔试,一定要端正态度,这些都是基本功,不要像我当时,碰了很多钉子,才知道回头抓基本功。