mysql笔记

最新推荐文章于 2022-01-27 19:34:20 发布

置顶传奇之士

最新推荐文章于 2022-01-27 19:34:20 发布

阅读量347

点赞数 1

本文链接：https://blog.csdn.net/QMM_1314521/article/details/81262200

版权

创建mysql数据库

一创建数据库java66_db1;

二创建表：

表一：

tb_card:

表二：

tb_class

表三：

tb_exam

表四：

tb_subject

表五：

tb_user

三：代码如下：

复制即可使用：

/*
SQLyog Ultimate v11.26 (32 bit)
MySQL - 5.5.50 : Database - java66_db1
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`java66_db1` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `java66_db1`;

/*Table structure for table `tb_card` */

DROP TABLE IF EXISTS `tb_card`;

CREATE TABLE `tb_card` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`card_no` int(11) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`),
CONSTRAINT `tb_card_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `tb_card` */

insert into `tb_card`(`id`,`card_no`,`user_id`) values (1,2018001,1),(2,2018002,2),(4,2018003,3);

/*Table structure for table `tb_class` */

DROP TABLE IF EXISTS `tb_class`;

CREATE TABLE `tb_class` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`class_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Data for the table `tb_class` */

insert into `tb_class`(`id`,`class_name`) values (1,'JAVA61'),(2,'JAVA62'),(3,'JAVA63'),(4,'JAVA64'),(5,'JAVA65'),(6,'JAVA66');

/*Table structure for table `tb_exam` */

DROP TABLE IF EXISTS `tb_exam`;

CREATE TABLE `tb_exam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`u_id` int(11) DEFAULT NULL,
`sub_id` int(11) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `u_id` (`u_id`),
KEY `sub_id` (`sub_id`),
CONSTRAINT `tb_exam_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `tb_user` (`id`),
CONSTRAINT `tb_exam_ibfk_2` FOREIGN KEY (`sub_id`) REFERENCES `tb_subject` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `tb_exam` */

insert into `tb_exam`(`id`,`u_id`,`sub_id`,`score`) values (1,1,1,100),(2,1,2,90),(3,2,3,90),(4,2,4,80);

/*Table structure for table `tb_subject` */

DROP TABLE IF EXISTS `tb_subject`;

CREATE TABLE `tb_subject` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sub_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `tb_subject` */

insert into `tb_subject`(`id`,`sub_name`) values (1,'JAVA'),(2,'HTML'),(3,'厨师'),(4,'驾驶');

/*Table structure for table `tb_user` */

DROP TABLE IF EXISTS `tb_user`;

CREATE TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`username` varchar(20) NOT NULL COMMENT '姓名',
`telphone` varchar(11) DEFAULT NULL COMMENT '电话',
`address` varchar(50) DEFAULT NULL COMMENT '住址',
`borndate` datetime DEFAULT NULL COMMENT '出生日期',
`age` int(11) DEFAULT NULL,
`gender` char(1) DEFAULT NULL,
`class_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_user_class` (`class_id`),
CONSTRAINT `fk_user_class` FOREIGN KEY (`class_id`) REFERENCES `tb_class` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

/*Data for the table `tb_user` */

insert into `tb_user`(`id`,`username`,`telphone`,`address`,`borndate`,`age`,`gender`,`class_id`) values (1,'李四1',NULL,'合肥市',NULL,17,'男',1),(2,'李四2',NULL,'合肥市',NULL,18,'女',1),(3,'李四3','15288888888','合肥市',NULL,28,'女',1),(4,'李四4','15288888888','北京市',NULL,19,'女',1),(5,'李四5','15888888888','北京市',NULL,20,'女',6),(6,'李四6','15888888888','北京市',NULL,18,'女',NULL),(7,'李四7','15888888888','上海市',NULL,28,'女',NULL),(8,'李四8','15888888888','上海市',NULL,18,'女',NULL);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

四：对表进行查询：

1.查询JAVA分数在 80－90之间的同学。

select tb_user.username
from tb_exam inner join tb_user on(tb_user.id = tb_exam.u_id)
inner join tb_subject on(tb_exam.sub_id = tb_subject.id)
where (tb_exam.score between 80 and 90) and tb_exam.sub_id = (select tb_subject.id from tb_subject where tb_subject.sub_name = 'java');

2.查询JAVA分数为89,90,91的同学。

select tb_user.username
from tb_exam inner join tb_user on(tb_user.id = tb_exam.u_id)
inner join tb_subject on(tb_exam.sub_id = tb_subject.id)
where (tb_exam.score in(89,90,91)) and tb_exam.sub_id = (select tb_subject.id from tb_subject where tb_subject.sub_name = 'java');

3.查询所有姓李的学生成绩。
select tb_exam.score from
tb_exam inner join tb_user on(tb_exam.u_id = tb_user.id)
where tb_exam.u_id in (select tb_user.id from tb_user where tb_user.username like '李%');

4.查询JAVA>80 并且 HTML分>80的同学的信息。
select * from
tb_exam inner join tb_user on(tb_exam.u_id = tb_user.id)
inner join tb_subject on(tb_exam.sub_id = tb_subject.id)
where (tb_exam.score > 80 and (tb_exam.sub_id = (select tb_subject.id from tb_subject where tb_subject.sub_name = 'java'))) or (tb_exam.score > 80 and (tb_exam.sub_id = (select tb_subject.id from tb_subject where tb_subject.sub_name = 'HTML')));

5.对JAVA成绩排序后输出。

select tb_exam.score
from tb_exam inner join tb_subject on(tb_exam.sub_id = tb_subject.id)
where tb_exam.sub_id = (select tb_subject.id from tb_subject where tb_subject.sub_name = 'java')
order by tb_exam.score desc;

6.对总分排序后输出，然后再按从高到低的顺序输出

select * from
(select sum(tb_exam.score) as score
from tb_exam inner join tb_user on (tb_exam.u_id = tb_user.id)
group by (tb_exam.u_id)) as T order by T.score desc;

7.对姓李的学生数学成绩排序输出.
select * from
(select tb_user.username,tb_exam.score from
tb_exam inner join tb_user on(tb_user.id= tb_exam.u_id)
inner join tb_subject on(tb_exam.sub_id= tb_subject.id)
where tb_exam.u_id in (select tb_user.id from tb_user where tb_user.username like '李%' ) and tb_exam.sub_id = (select tb_subject.id from tb_subject where tb_subject.sub_name = '数学')) as T order by T.score ;

8.展示学生所有信息与所属年级信息.
select tb_user.id ,tb_user.username,tb_user.telphone,tb_user.gender,tb_user.borndate,tb_user.address ,tb_class.class_name
from tb_user left join tb_class on(tb_user.class_id = tb_class.id);
9.展示所有学生的考试信息.(包括学生,考试课程,成绩信息.) 三表内连接.
select tb_user.username , tb_subject.sub_name ,tb_exam.score
from tb_exam inner join tb_user on(tb_exam.u_id= tb_user.id)
inner join tb_subject on(tb_subject.id = tb_exam.sub_id);

10.统计JAVA成绩大于90的学生有多少个？

select count(tb_exam.u_id)
from tb_exam inner join tb_subject on (tb_exam.sub_id = tb_subject.id)
where tb_exam.score > 90 and tb_exam.sub_id = (select tb_subject.id from tb_subject where tb_subject.sub_name = 'java');

https://download.csdn.net/download/qmm_1314521/10569798