Mysql的语句练习

现在有一教学管理系统,具体的关系模式如下:

Student (no, name, sex, birthday, class)

Teacher (no, name, sex, birthday, prof, depart)

Course (cno, cname, tno)

Score (no, cno, degree)

其中表中包含如下数据:

Course表:

Score表:

Student表:

Teacher表:

根据上面描述完成下面问题:

1、写出上述表的建表语句:

Course表
    Create table Course(
    cno varchar(20),
    cname varchar(20),
    tno int unsigned);

Score表

    Create table Score(
    no int unsigned,
    cno varchar(20),
    degree int unsigned);

Student表

    Create table Student(
    no int unsigned,
    name varchar(20),
    sex varchar(20),
    birthday varchar(20),
    class varchar(20));

Teacher表

    Create table Teacher(
    no int unsigned,
    name varchar(20),
    sex varchar(20),
    birthday varchar(20),
    prof varchar(20),
    depart varchar(20));

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

Course表 
 insert course values('3-101','数据库',1);

 insert course values('5-102','数学',3)

 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 cours values('3-107','pascal语言',5);

 insert cours values('4-108','c++',7);

 insert course values('4-109','java',8);

 insert course values('3-245','数据挖掘',10);

 insert course values('3-111','软件工程',11);
Score表
     insert score values (5001,'3-105',69);

    insert score values (5001,'5-102',55);

    insert score values (5003,'4-108',85);

    insert score values (5004,'3-105',77);

    insert score values (5005,'3-245',100);

    insert score values (5006,'3-105',53);

    insert score values (5003,'4-109',45);

    insert score values (5008,'3-105',98);

    insert score values (5004,'4-109',68);

    insert score values (5010,'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,'3-105',88);

    insert score values (109,'4-109',80);

    insert score values (107,'3-111',88);

    insert score values (5003,'3-111',80);

Student表

    insert student values (5001,'李勇','男','1987-7-22 0:00:00','95001');

    insert student values (5002,'刘晨','女','1987-11-15 0:00:00','95002');

    insert student values (5003,'王敏','女','1987-10-5 0:00:00','95001');

    insert student values (5004,'李好尚','男','1987-9-25 0:00:00','95003');

    insert student values (5005,'李军','男','1987-7-17 0:00:00','95004');

    insert student values (5006,'范新位','女','1987-6-18 0:00:00','95005');

    insert student values (5007,'张霞东','女','1987-8-29 0:00:00','95006');

    insert student values (5008,'赵薇','男','1987-6-15 0:00:00','95007');

    insert student values (5009,'钱民将','女','1987-6-23 0:00:00','95008');

    insert student values (5010,'孙俪','女','1987-9-24 0:00:00','95002');

    insert student values (108,'赵里','男','1987-6-15 0:00:00','95007');

    insert student values (109,'丘处机','男','1987-6-23 0:00:00','95008');

    insert student values (107,'杨康','男','1987-9-24 0:00:00','95001');

Teacher表

    insert teacher values (1,'李卫','男','1957-11-5 0:00:00','教授','电子工程系');

    insert teacher values (2,'刘备','男','1967-10-9 00:00:00','副教授','math');

    insert teacher values (3,'关羽','男','1977-9-20 00:00:00','讲师','sc');

    insert teacher values (4,'李修','男','1957-6-25 00:00:00','教师','elec');

    insert teacher values (5,'诸葛亮','男','1977-6-15 00:00:00','教授','计算机系');

    insert teacher values (6,'殷素素','女','1967-1-5 00:00:00','副教授','sc');

    insert teacher values (7,'周芷若','女','1947-2-23 00:00:00','教授','sc');

    insert teacher values (8,'赵云','男','1980-6-13 00:00:00','副教授','计算机系');

    insert teacher values (9,'张敏','女','1985-5-5 00:00:00','助教','sc');

    insert teacher values (10,'黄蓉','女','1967-3-22 00:00:00','副教授','sc');

    insert teacher values (11,'张三','男','1967-3-22 00:00:00','副教授','sc');

3、以class降序输出student的所有记录(student表全部属性)

select * from student  order by  class desc;

4.列出教师所在的单位depart(不重复)

select distinct depart from teacher;

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 no,cno,degree from score where degree=85 or degree=86 or degree=88 or degree between 60 and 80;

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

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

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

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

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

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

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

select distinct 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;

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

select cno,avg(degree),MAX(degree),MIN(degree) from score where cno like '3%' group by cno having count(cno)>=5;

 

 

JDBC代码

package com.open.jdbc;
 
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 {
		
		Class.forName("com.mysql.jdbc.Driver");
			
		Connection conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root","123456");
		Statement st = conn.createStatement();
 

		ResultSet rs = st.executeQuery("select * from tb1");
				
		while(rs.next()){
		  System.out.println(rs.getString("usernames")+":"+rs.getInt("age"));
		}
				
				
		rs.close();
		st.close();
		conn.close();
		
		
	}

封装

package com.openlab.jdbc;
 
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  jdbcfz {
	private static String url = "jdbc:mysql://localhost:3306/cao";
	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 Close_1(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 Close_2(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();
				}
			}
	}
		
}
	
 

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值