javaweb第一周作业

1.写出上述表的建表语句。

create table course(

cno varchar(10),

cname varchar(20),

tno int(10) unsigned);

 

2.给出相应的INSERT语句来完成题中给出数据的插入。

insert course(cno,cname,tno) values('3-101','数据库',1);

3.以class降序输出student的所有记录（student表全部属性）

 select * from student order by class desc;

 

4.列出教师所在的单位depart（不重复）。

 select depart as depart from teacher group by depart;

5.列出student表中所有记录的name、sex和class列

 select name,sex,class from student;

6.输出student中不姓王的同学的姓名。

select name from student where name not like '王%'

7.输出成绩为85或86或88或在60-80之间的记录（no,cno,degree）

 select * from score where degree=85 or degree=86 or degree=88 or degree>60 and degree<=80;

8.输出班级为95001或性别为‘女’ 的同学（student表全部属性）

mysql> select * from student where class=95001 or sex=1;

9.以cno升序、degree降序输出score的所有记录。（score表全部属性）

select * from score order by cno,degree desc;

10.输出男生人数及这些男生分布在多少个班级中10.

select count(*),count(distinct class) from student where sex=0;

11.列出存在有85分以上成绩的课程编号。

select cno from score where degree>85;

12.输出95001班级的学生人数

select count(*)from student where class='95001';

13.输出‘3-105’号课程的平均分

select avg(degree) from score where cno='3-105';

14.输出student中最大和最小的birthday日期值

select MAX(birthday),MIN(birthday) from student;

15.显示95001和95004班全体学生的全部个人信息（不包括选课）。（student表全部属性）

select*from student where class='95001'or class='95004';

 

DBC 代码封装

package what;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
public abstract class JDBC {
	private static String url = "jdbc:mysql://localhost:3306/demo1";
	private  static String usename = "root";
	private static String password = "123456";
	Connection conn = null;
	Statement st = null;
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			
			e.printStackTrace();
		}
			
		}	
	public static Connection getConnection() throws SQLException {
		
		 return DriverManager.getConnection(url, usename,password);
	}
	
	public void updata(String sql){
		
		try {
			conn = getConnection();
			st = conn.createStatement();
			int bret = st.executeUpdate(sql);
			System.out.println(bret);
		} catch (SQLException e) {
		
			e.printStackTrace();
		}
		
	}
	

	public static void JdbcClose1(Connection conn,Statement st,ResultSet rs) {11
		try {
			rs.close();
			if(rs!=null){
				rs=null;
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				st.close();
				if(st!=null){
					st=null;
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}finally{
				try {
					conn.close();
					if(conn!=null){
						conn=null;
					}
				} catch (SQLException e) {
					
					e.printStackTrace();
				}
			}
		}
		
		
	}
	
	public static void JdbcClose2(Connection conn,Statement st) {
		try {
				st.close();
				if(st!=null){
					st=null;
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}finally{
				try {
					conn.close();
					if(conn!=null){
						conn=null;
					}
				} catch (SQLException e) {
					
					e.printStackTrace();
				}
			}
	}
}