javaweb后端第1次作业

最新推荐文章于 2022-11-04 17:08:12 发布

[(-o-)]

最新推荐文章于 2022-11-04 17:08:12 发布

阅读量1k

点赞数 1

分类专栏： javaweb 文章标签： mysql

本文链接：https://blog.csdn.net/lijin786/article/details/107187291

版权

javaweb 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

1.建表

1.1student表

create table  student (
  no int(10) unsigned  PRIMARY KEY ,
  name varchar(20) NOT NULL,
  sex varchar(10) DEFAULT NULL,
  birthday datetime DEFAULT NULL,
  class  int(10) unsigned DEFAULT NULL);

1.2course表

create table course (
cno varchar(10) PRIMARY KEY,
cname varchar(20) ,
tno int(10) unsigned );

1.3sorce表

create table sorce (
  no int(10) unsigned NOT NULL,
  cno  varchar(10) NOT NULL,
  degree  int(10) ) ;

1.4 teacher表

create table teacher (
  no  int(10) unsigned PRIMARY KEY,
  name  varchar(20) NOT NULL,
  sex  varchar(10) DEFAULT NULL,
  birthday  datetime DEFAULT NULL,
  prof  varchar(20) DEFAULT NULL,
  depart  varchar(20) DEFAULT NULL);

2.插入数据

2.1student表

Insert student values ('107', '杨康', '男', '1987-09-24 00:00:00', '95001');
Insert student values ('108', '赵里', '男', '1987-06-15 00:00:00', '95007');
Insert student values ('109', '丘处机', '男', '1987-06-23 00:00:00', '95008');
Insert student values ('5001', '李勇', '男', '1987-07-22 00:00:00', '95001');
Insert student values ('5002', '刘晨', '女', '1987-11-15 00:00:00', '95002');
Insert student values ('5003', '王敏', '女', '1987-10-05 00:00:00', '95001');
Insert student values ('5004', '李好尚', '男', '1987-09-25 00:00:00', '95003');
Insert student values ('5005', '李军', '男', '1987-07-17 00:00:00', '95004');
Insert student values ('5006', '范新位', '女', '1987-06-16 00:00:00', '95005');
Insert student values ('5007', '张霞东', '女', '1987-08-29 00:00:00', '95006');
Insert student values ('5008', '赵薇', '男', '1987-06-15 00:00:00', '95007');
Insert student values ('5009', '钱民将', '女', '1987-06-23 00:00:00', '95008');
Insert student values ('5010', '孙俪', '女', '1987-09-24 00:00:00', '95002');

2.2curse表

insert course values ('3-101', '数据库', '1');
Insert course values ('3-103', '信息系统', '4');
Insert course values ('3-104', '操作系统', '6');
Insert course values ('3-105', '数据结构', '4');
Insert course values ('3-106', '数据处理', '5');
Insert course values('3-111', '软件工程', '11');
Insert course values ('3-245', '数据挖掘', '10');
Insert course values ('4-107', 'pascal语言', '5');
Insert course values ('4-108', 'c++', '7');
Insert course values ('4-109', 'java', '8');
Insert course values ('5-102', '数学', '3');

2.3sorce表

Insert score values ('5501', '3-105', '69');
Insert score values ('5501', '5-102', '55');
Insert score values ('5503', '4-108', '85');
Insert score values ('5504', '3-105', '77');
Insert score values ('5505', '3-245', '100');
Insert score values ('5506', '3-105', '53');
Insert score values ('5503', '4-109', '45');
Insert score values ('5508', '3-105', '98');
Insert score values ('5504', '4-109', '68');
Insert score values ('5510', '3-105', '88');
Insert score values ('5003', '3-105', '98');
Insert score values ('5005', '4-109', '68');
Insert score values ('5002', '3-105', '88');
Insert score values ('107', '3-105', '98');
Insert score values ('108', '4-109', '68');
Insert score values ('109', '4-105', '98');
Insert score values ('109', '4-109', '80');
Insert score values ('107', '3-111', '88');
Insert score values ('5003', '3-111', '80');

2.4 teacher表

Insert teacher values ('1', '李卫', '男', '1957-11-05 00:00:00', '教授', '电子工程系');
Insert teacher values ('2', '刘备', '男', '1967-10-09 00:00:00', '副教授', 'math');
Insert teacher values('3', '关羽', '男', '1977-09-20 00:00:00', '讲师', 'sc');
Insert teacher values ('4', '李修', '男', '1957-06-25 00:00:00', '教授', 'elec');
Insert teacher values ('5', '诸葛亮', '男', '1977-06-15 00:00:00', '教授', '计算机系');
Insert teacher values ('6', '殷素素', '女', '1967-01-05 00:00:00', '副教授', 'sc');
Insert teacher values ('7', '周芷若', '女', '1947-02-23 00:00:00', '教授', 'sc');
Insert teacher values ('8', '赵云', '男', '1980-06-13 00:00:00', '副教授', '计算机系');
Insert teacher values ('9', '张敏', '女', '1985-05-05 00:00:00', '助教', 'sc');
Insert teacher values ('10', '黄蓉', '女', '1967-03-22 00:00:00', '副教授', 'sc');
Insert teacher values ('11', '张三', '男', '1967-03-22 00:00:00', '副教授', 'sc');

3.练习

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

select * 
from student  
order by  class desc;

2、列出教师所在的单位depart（不重复）

select depart 
from teacher 
group by depart;

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

select name,sex,class 
from student;

4、输出student中不姓王的同学的姓名。、

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

5、输出成绩为85或86或88或在60-80之间的记录
（1）

select * 
from score 
where degree IN(85,86,88) OR  degree BETWEEN 60 and 80;

（2）

select * 
from score 
where degree=85 or degree=86 or degree=88 OR  degree BETWEEN 60 and 80;

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

select * 
from student 
where class = '95001'  or sex = "女 ";

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

select * 
from score 
order BY cno asc,degree desc ;

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

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

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

select distinct cno 
from score 
where degree>85;

10、输出95001班级的学生人数

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

11、输出‘3-105’号课程的平均分

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

12、输出student中最大和最小的birthday日期值

select max(birthday),min(birthday) 
from student ;

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

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

14、输出至少有5个同学选修的并以3开头的课程的课程号，课程平均分，课程最高分，课程最低分

select cno,avg(degree),max(degree),min(degree) 
from score 
group by cno 
having cno like '3%' and count(cno)>5;

15、输出所选修课程中最低分大于70分且最高分小于90分的学生学号及学生姓名

select student.no,student.name 
from score,student where student.no=score.no 
group by student.no,name 
having (MAX(degree)<90 and MIN(degree)>70);

16、显示所教课程选修人数多于5人的教师姓名

select name 
from score,course,teacher 
where score.cno=course.cno and teacher.no =course.tno 
group by teacher.no,name 
having count(*)>5;

17、输出’95001’班级所选课程的课程号和平均分

select cno,AVG(degree) 
from student,score 
where student.no = score.no  
group by cno,class 
having student.class='95001';

18、输出至少有两名男同学的班级编号

select class 
from student 
where  sex='男'  
group by class  
having count(distinct student.no )>1;

19、列出与108号同学同年出生的所有学生的学号、姓名和生日

select no,name,birthday 
from student 
where year(birthday) = (
select year(birthday)
from student 
where no='108');

20、列出存在有85分以上成绩的课程名称

select cname 
from course
where cno in(
select cno 
from score 
where degree>85);

21、列出“计算机系”教师所教课程的成绩表（课程编号，课程名，学生名，成绩）

select score.cno,cname,student.name,degree 
from student,score,course,teacher 
where student.no=score.no and 
course.cno=score.cno and 
course.tno=teacher.no and 
depart='计算机系';

22、列出所有可能的“计算机系”与“电子工程系”不同职称的教师配对信息，要求输出每个老师的姓名（name）和（职称）

select a.name,a.prof,b.name,b.prof  
from teacher a,teacher b
where a.depart='电子工程系' and b.depart='计算机系'or a.prof!=b.prof and a.prof=b.prof;

23、列出所有处于不同班级中，但具有相同生日的学生，要求输出每个学生的学号和姓名

select a.no,a.name,b.no,b.name 
from student a,student b 
where a.class !=b.class and a.birthday=b.birthday;

24、显示‘张三’教师任课的学生姓名，课程名，成绩

select  student.name,cname,degree 
from student,course,score,teacher
where teacher.no =course.tno  and course.cno=score.cno and student.no=score.no and  teacher.name = '张三';

25、列出所讲课已被选修的教师的姓名和系别

select DISTINCT name,depart 
from teacher,score,course 
where teacher.no =course.tno and course.cno=score.cno ;

26、输出所有学生的name、no和degree。（degree为空的不输出和为空的输出两种情况）
（1）degree为空的输出

select name ,student.no,degree 
from student left join score on student.no=score.no;

（2）degree为空的不输出

select name ,student.no,degree 
from student,score 
where student.no=score.no;

4、JDBC代码

连接数据库进行查询

package com.diyi.lianjie;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		//1 加载驱动
		Class.forName("com.mysql.jdbc.Driver");
				
		//2获取连接对象
		Connection conn = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root","123456");
		//3.获取statement对象
		Statement st = conn.createStatement();

		//4.进行查询 返回结果集
		ResultSet rs = st.executeQuery("select * from tb1");
				
		//5. 对结果进行遍历
				
		while(rs.next()){
			System.out.println(rs.getString("usernames")+":"+rs.getInt("age"));
		}
				
		//6.关闭  rs  st conn
				
		rs.close();
		st.close();
		conn.close();
		
		
	}
}

5、JDBC封装

package com.ceshi.diyi;

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  jdbcutil {
	private static String url = "jdbc:mysql://localhost:3306/demo";
	private  static String usename = "root";
	private static String password = "123456";
	Connection conn = null;
	Statement st = null;
	
	//1.加载驱动
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			
			e.printStackTrace();
		}
			
		}
	//2.连接对象
	public static Connection getConnection() throws SQLException {
		
		 return DriverManager.getConnection(url, usename,password);
	}
	//3.添加、删除、修改
	public void updata(String sql){
		
		try {
			conn = getConnection();
			st = conn.createStatement();
			int bret = st.executeUpdate(sql);
			System.out.println(bret);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	//4.关闭
	public static void jdbcClose(Connection conn,Statement st,ResultSet rs) {
		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 jdbcClose(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();
				}
			}
	}
		
}

测试：

package com.diyi.lianjie;

import static org.junit.Assert.*;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.ceshi.diyi.jdbcutil;
public class jdbcutilTest {

	@Test
	public void test() {
		String sql =  "insert into tb1 (username,age) values('wangliu',9)";
		jdbcutil jdbc = new jdbcutil();
		jdbc.updata(sql);
		
	}
	@Test
	public void test2(){
		String sql =  "update tb1 set age = age-1 where username='wangliu'";
		jdbcutil jdbc = new jdbcutil();
		jdbc.updata(sql);
	}
	@Test
	public void test3(){
		String sql =  "delete from tb1 where username='wangliu'";
		jdbcutil jdbc = new jdbcutil();
		jdbc.updata(sql);
	}
	

}

[(-o-)]

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
javaweb后端第1次作业

1.建表1.1student表create table student ( no int(10) unsigned PRIMARY KEY , name varchar(20) NOT NULL, sex varchar(10) DEFAULT NULL, birthday datetime DEFAULT NULL, class int(10) unsigned DEFAULT NULL);1.2curse表create table curse (con .
复制链接

扫一扫

专栏目录