学费查询录入导入导出

数据来源：

学生处：学生基本信息（学号，姓名，籍贯，出生日期，专业，班级，民族，政治面貌，身份证号，电话）

退宿信息（学号，姓名，专业，第一年是否退宿，第二年是否退宿，第三年是否退宿）

专业调整（学号，姓名，原专业，现专业）

财务处：缴费信息（学号，姓名，实缴，缴费时间）

缴费标准（系别，专业，缴费标准，学制，住宿标准）

 

要求：每个学生能登陆查询本人的学费情况

每个系能够查询本系学生的学费情况

管理员能够实现学费的增、删、改、查（管理员）

 

 

mysql> create database xuefei
    -> character set utf8;
Query OK, 1 row affected (0.33 sec)

mysql> use xuefei
Database changed
mysql> create table stuInfo
    -> (stu_id varchar(9) primary key,stu_name varchar(8),stu_bir date,
    -> stu_dep varchar(20),stu_zy varchar(30),stu_grade int,stu_tel varchar(15));
Query OK, 0 rows affected (0.40 sec)

mysql> create table stu_charge
    -> (stu_id varchar(9),c_money decimal(10,2),c_date datetime);
Query OK, 0 rows affected (0.14 sec)

mysql> alter table stu_charge
    -> add constraint fk_stu foreign key (stu_id) references stuInfo(stu_id);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table department
    -> (dep_name varchar(20),stu_zy varchar(30),c_biaozhun float,xuezhi int,zhusu float);
Query OK, 0 rows affected (0.42 sec)

mysql> alter table department
    -> add constraint pk_dep primary key (dep_name,stu_zy);
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table stuInfo
    -> add constraint fk_stu_dep foreign key (stu_dep,stu_zy) references
    -> department(dep_name,stu_zy);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table Tuisu
    -> (stu_id varchar(9),fir enum('0','1'),sec enum('0','1'),third enum('0','1'));
Query OK, 0 rows affected (0.36 sec)

mysql> set names gb2312;
Query OK, 0 rows affected (0.03 sec)

mysql> insert department
    -> values('软件工程系','php',8000,3,500);
Query OK, 1 row affected (0.11 sec)

mysql> insert department
    -> values('软件工程系','3G',8000,3,500);
Query OK, 1 row affected (0.00 sec)

mysql> insert department
    -> values('软件工程系','java',8000,3,500);
Query OK, 1 row affected (0.00 sec)

mysql> insert department
    -> values('软件工程系','.net',8000,3,500);
Query OK, 1 row affected (0.00 sec)

mysql> insert department
    -> values('网络工程系','网络技术',8000,3,500);
Query OK, 1 row affected (0.00 sec)

mysql> insert department
    -> values('经济管理系','酒店管理',5000,2,500);
Query OK, 1 row affected (0.00 sec)

mysql> insert department
    -> values('经济管理系','文秘',5000,2,500);
Query OK, 1 row affected (0.00 sec)

mysql> select * from department;
+------------+----------+------------+--------+-------+
| dep_name   | stu_zy   | c_biaozhun | xuezhi | zhusu |
+------------+----------+------------+--------+-------+
| 软件工程系          | php      |       8000 |      3 |   500 |
| 软件工程系          | 3G       |       8000 |      3 |   500 |
| 软件工程系          | java     |       8000 |      3 |   500 |
| 软件工程系          | .net     |       8000 |      3 |   500 |
| 网络工程系        | 网络技术       |       8000 |      3 |   500 |
| 经济管理系         | 酒店管理        |       5000 |      2 |   500 |
| 经济管理系         | 文秘         |       5000 |      2 |   500 |
+------------+----------+------------+--------+-------+
7 rows in set (0.35 sec)

mysql> insert stuInfo
    -> values('111307001','詹金斯','1990-8-12','软件工程系','php',2011,'15031248769');
Query OK, 1 row affected (0.00 sec)

mysql> insert stuInfo
    -> values('111307002','孙可','1990-10-22','软件工程系','3G',2011,'15031248744');
Query OK, 1 row affected (0.00 sec)

mysql> insert stuInfo
    -> values('111307003','苏尔','1990-2-22','软件工程系','java',2011,'15031298044');
Query OK, 1 row affected (0.00 sec)

mysql> select * from stuInfo;
+-----------+----------+------------+------------+--------+-----------+-------------+
| stu_id    | stu_name | stu_bir    | stu_dep    | stu_zy | stu_grade | stu_tel     |
+-----------+----------+------------+------------+--------+-----------+-------------+
| 111307001 | 詹金斯       | 1990-08-12 | 软件工程系          | php    |      2011 | 15031248769 |
| 111307002 | 孙可         | 1990-10-22 | 软件工程系          | 3G     |      2011 | 15031248744 |
| 111307003 | 苏尔        | 1990-02-22 | 软件工程系          | java   |      2011 | 15031298044 |
+-----------+----------+------------+------------+--------+-----------+-------------+
3 rows in set (0.00 sec)

mysql> insert stu_charge
    -> values('111307001',8500,now());
Query OK, 1 row affected (0.08 sec)

mysql> insert stu_charge
    -> values('111307002',2500,now());
Query OK, 1 row affected (0.00 sec)

mysql> insert stu_charge
    -> values('111307003',6000,now());
Query OK, 1 row affected (0.00 sec)

mysql> insert stu_charge
    -> values('111307004',2000,now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu_charge;
+-----------+---------+---------------------+
| stu_id    | c_money | c_date              |
+-----------+---------+---------------------+
| 111307001 | 8500.00 | 2012-10-17 20:05:30 |
| 111307002 | 2500.00 | 2012-10-17 20:05:56 |
| 111307003 | 6000.00 | 2012-10-17 20:06:15 |
| 111307004 | 2000.00 | 2012-10-17 20:06:30 |
+-----------+---------+---------------------+
4 rows in set (0.00 sec)

mysql> create view stu_money_sum
    -> as
    -> select stu_id,sum(c_money) from stu_charge
    -> group by stu_id;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from stu_money_sum;
+-----------+--------------+
| stu_id    | sum(c_money) |
+-----------+--------------+
| 111307001 |      8500.00 |
| 111307002 |      2500.00 |
| 111307003 |      6000.00 |
| 111307004 |      2000.00 |
+-----------+--------------+
4 rows in set (0.06 sec)

mysql> select stu_id,stu_name,stuInfo.stu_dep,stuInfo.stu_zy,(year(now())-stu_grade)*(c_biaozhun+zhusu) as yingjiao from stuInfo join department on stuInfo.stu_zy=department.stu_zy;
+-----------+----------+------------+--------+----------+
| stu_id    | stu_name | stu_dep    | stu_zy | yingjiao |
+-----------+----------+------------+--------+----------+
| 111307001 | 詹金斯       | 软件工程系          | php    |     8500 |
| 111307002 | 孙可         | 软件工程系          | 3G     |     8500 |
| 111307003 | 苏尔        | 软件工程系          | java   |     8500 |
+-----------+----------+------------+--------+----------+
3 rows in set (0.10 sec)

mysql> create view stu_sum_yj
    -> as
    -> select stu_id,stu_name,stuInfo.stu_dep,stuInfo.stu_zy,(year(now())-stu_grade)*(c_biaozhun+zhusu) as yingjiao from stuInfo join department on stuInfo.stu_zy=department.stu_zy;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from stu_sum_yj;
+-----------+----------+------------+--------+----------+
| stu_id    | stu_name | stu_dep    | stu_zy | yingjiao |
+-----------+----------+------------+--------+----------+
| 111307001 | 詹金斯       | 软件工程系          | php    |     8500 |
| 111307002 | 孙可         | 软件工程系          | 3G     |     8500 |
| 111307003 | 苏尔        | 软件工程系          | java   |     8500 |
+-----------+----------+------------+--------+----------+
3 rows in set (0.04 sec)

mysql> drop view stu_sum_yj;
Query OK, 0 rows affected (0.00 sec)

mysql> create view stu_sum_yj
    -> as
    -> select stu_id,stu_name,stuInfo.stu_dep,stuInfo.stu_zy,(year(now())-stu_grade)*(c_biaozhun+zhusu) as yingjiao from stuInfo left outer join department on stuInfo.stu_zy=department.stu_zy;
Query OK, 0 rows affected (0.00 sec)

mysql> drop view stu_money_sum;
Query OK, 0 rows affected (0.00 sec)

mysql> create view stu_money_sum
    -> as
    -> select stu_id,sum(c_money) as sum_money from stu_charge
    -> group by stu_id;
Query OK, 0 rows affected (0.01 sec)

mysql> select yj.stu_id,stu_name,stu_dep,stu_zy,yingjiao,sum_money,yingjiao-sum_money as qianfei from stu_money_sum sj right outer join stu_sum_yj yj on
    -> sj.stu_id=yj.stu_id;
+-----------+----------+------------+--------+----------+-----------+---------+
| stu_id    | stu_name | stu_dep    | stu_zy | yingjiao | sum_money | qianfei |
+-----------+----------+------------+--------+----------+-----------+---------+
| 111307001 | 詹金斯       | 软件工程系          | php    |     8500 |   8500.00 |       0 |
| 111307002 | 孙可         | 软件工程系          | 3G     |     8500 |   2500.00 |    6000 |
| 111307003 | 苏尔        | 软件工程系          | java   |     8500 |   6000.00 |    2500 |
+-----------+----------+------------+--------+----------+-----------+---------+
3 rows in set (0.01 sec)