mysql找出借书超过5本的读者_69期-Java SE-036_MySQL-7 SQL练习 -doing

最新推荐文章于 2022-06-07 22:03:57 发布

weixin_39888049

最新推荐文章于 2022-06-07 22:03:57 发布

阅读量321

点赞数

文章标签： mysql找出借书超过5本的读者

本文链接：https://blog.csdn.net/weixin_39888049/article/details/113975885

版权

--MySQL dump 10.13 Distrib 8.0.11, for macos10.13 (x86_64)--

--Host: 127.0.0.1 Database: mysql_exam2----------------------------------------------------------Server version 8.0.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS*/;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION*/;SETNAMES utf8mb4 ;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE*/;/*!40103 SET TIME_ZONE='+00:00'*/;/*!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*/;--

--Table structure for table `books`--

DROP TABLE IF EXISTS`books`;/*!40101 SET @saved_cs_client = @@character_set_client*/;SET character_set_client =utf8mb4 ;CREATE TABLE`books` (

`BNO`varchar(12) NOT NULL,

`BNAME`varchar(12) DEFAULT NULL,

`AUTHOR`varchar(12) DEFAULT NULL,

`PRICE`varchar(12) DEFAULT NULL,

`QUANTITY`varchar(12) DEFAULT NULL,PRIMARY KEY(`BNO`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client*/;--

--Dumping data for table `books`--LOCK TABLES `books` WRITE;/*!40000 ALTER TABLE `books` DISABLE KEYS*/;INSERT INTO `books` VALUES ('1100011','计算机网络','阿一','86.9','10'),('1100022','红楼梦','阿二','53.6','5'),('1100033','组合数学','阿三','26.3','15'),('1100044','水浒','阿四','98.7','3'),('1100055','三国','阿五','46.8','2'),('1100066','计算方法','阿一','59.6','8'),('1100077','计算方法习题集','阿一','23.5','8'),('123',NULL,NULL,NULL,NULL);/*!40000 ALTER TABLE `books` ENABLE KEYS*/;

UNLOCK TABLES;--

--Table structure for table `borrows`--

DROP TABLE IF EXISTS`borrows`;/*!40101 SET @saved_cs_client = @@character_set_client*/;SET character_set_client =utf8mb4 ;CREATE TABLE`borrows` (

`CNO`varchar(12) DEFAULT NULL,

`BNO`varchar(12) DEFAULT NULL,

`RDATE` dateDEFAULT NULL,KEY`CNO` (`CNO`),KEY`BNO` (`BNO`),CONSTRAINT `borrows_ibfk_1` FOREIGN KEY (`CNO`) REFERENCES`card` (`cno`),CONSTRAINT `borrows_ibfk_2` FOREIGN KEY (`BNO`) REFERENCES`books` (`bno`),CONSTRAINT `borrows_ibfk_3` FOREIGN KEY (`BNO`) REFERENCES`books` (`bno`),CONSTRAINT `borrows_ibfk_4` FOREIGN KEY (`BNO`) REFERENCES`books` (`bno`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client*/;--

--Dumping data for table `borrows`--LOCK TABLES `borrows` WRITE;/*!40000 ALTER TABLE `borrows` DISABLE KEYS*/;INSERT INTO `borrows` VALUES ('sx001','1100011','2019-02-15'),('sx001','1100022','2019-02-15'),('sx001','1100033','2019-02-15'),('sx001','1100044','2019-02-15'),('sx001','1100055','2019-02-15'),('sx001','1100066','2019-02-15'),('sx002','1100066','2019-12-01'),('sx002','1100077','2019-07-09'),('sx003','1100066','2019-06-15'),('sx004','1100055','2019-06-15'),('sx001','123',NULL);/*!40000 ALTER TABLE `borrows` ENABLE KEYS*/;

UNLOCK TABLES;--

--Table structure for table `card`--

DROP TABLE IF EXISTS`card`;/*!40101 SET @saved_cs_client = @@character_set_client*/;SET character_set_client =utf8mb4 ;CREATE TABLE`card` (

`CNO`varchar(12) NOT NULL,

`NAME`varchar(12) DEFAULT NULL,

`CLASS`varchar(12) DEFAULT NULL,PRIMARY KEY(`CNO`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client*/;--

--Dumping data for table `card`--LOCK TABLES `card` WRITE;/*!40000 ALTER TABLE `card` DISABLE KEYS*/;INSERT INTO `card` VALUES ('008','张铭','c02'),('sx001','汪伟','c01'),('sx002','张三','c02'),('sx003','李四','c02'),('sx004','王五','c02'),('sx005','小明','c02'),('sx006','小红','c02');/*!40000 ALTER TABLE `card` ENABLE KEYS*/;

UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE*/;/*!40101 SET SQL_MODE=@OLD_SQL_MODE*/;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS*/;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT*/;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS*/;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION*/;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES*/;--Dump completed on 2019-06-16 22:04:59