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();
}
}
}
}