简易博客数据库，并进行简单操作

创建简易博客数据库

create database blogdb;
create table users(
	id int unsigned not null primary key auto_increment,
    name varchar(32) null unique,
    email varchar(100) null,
    cdate datetime null);
create table blog(
	id int unsigned not null primary key auto_increment,
    title varchar(100) not null,
    abstract varchar(200) not null,
    content text not null,
    uid int unsigned null,
    pcount int unsigned default 0,
    flag tinyint unsigned default 0,
    cdate datetime);

blogdb.sql

-- MySQL dump 10.13  Distrib 5.7.12, for Win64 (x86_64)
--
-- Host: localhost    Database: blogdb
-- ------------------------------------------------------
-- Server version	5.7.17-log

/*!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 */;
/*!40101 SET NAMES utf8 */;
/*!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 `blog`
--

DROP TABLE IF EXISTS `blog`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `blog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `abstract` varchar(200) NOT NULL,
  `content` text NOT NULL,
  `uid` int(11) DEFAULT NULL,
  `pcount` int(11) DEFAULT '0',
  `flag` tinyint(4) DEFAULT '0',
  `cdate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `blog`
--

LOCK TABLES `blog` WRITE;
/*!40000 ALTER TABLE `blog` DISABLE KEYS */;
INSERT INTO `blog` VALUES (1,'qwer','qwerqwer','qwer',1,888,1,'2000-02-05 00:00:00'),(2,'a','aaaaaaaa','aaaa',2,222,1,'2010-05-05 00:00:00'),(3,'wasd','wasdwasd','wasd',3,333,1,'2005-09-01 00:00:00'),(4,'b','bbbbbbbb','bbbb',4,444,2,'1999-01-01 00:00:00'),(5,'zxc','zxczxcesc','zxc',5,50,1,'2015-06-07 00:00:00'),(6,'c','cccccccc','cccccc',6,666,1,'2016-06-06 00:00:00'),(7,'ijkl','ijklijkl','ijkl',7,77,0,'2018-01-01 00:00:00'),(8,'d','dddddddd','dddddddd',8,90,2,'2018-02-02 00:00:00'),(9,'jkuiol','jkuiol','jkuiol',9,100,1,'2018-03-03 00:00:00'),(10,'123456789','123456789','123456789',10,1,1,'2017-07-05 00:00:00'),(11,'10','10','10',6,606,1,'2016-01-06 00:00:00');
/*!40000 ALTER TABLE `blog` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `cdate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,'zhangsan','2502642350@qq.com','1999-09-09 00:00:00'),(2,'lisi','377030438@qq.com','2000-01-01 00:00:00'),(3,'wangwu','953076834@qq.com','1996-06-06 00:00:00'),(4,'zhaoliu','18706871579@163.com','1998-08-08 00:00:00'),(5,'ll','18892084679@163.com','1997-07-07 00:00:00'),(6,'aa','kiritoliuyhsky@gmail.com','2010-10-10 00:00:00'),(7,'bb','kiritoliu@gmail.com','2002-02-02 00:00:00'),(8,'q','qqqqqq','1990-09-09 00:00:00'),(9,'w','wwwwww','1998-03-03 00:00:00'),(10,'e','eeeeee','2013-08-08 00:00:00'),(11,'r','rrrrrr','2001-05-06 00:00:00');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events for database 'blogdb'
--

--
-- Dumping routines for database 'blogdb'
--
/*!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 2018-03-29 21:04:07

简单的数据库操作

/*1. 在users表中查询注册时间最早的十条会员信息。 */
select * from users order by cdate limit 10
/*2.从两个表中查询点赞数最高的5条博客信息，要求显示字段：（博文id，标题，点赞数，会员名）*/
select b.id,b.title,b.pcount,u.name from blog b,users u where u.id=b.uid order by pcount desc limit 0,5
/*3. 统计每个会员的发表博文数量（降序），要求显示字段（会员id号，姓名，博文数量）*/
select u.id,u.name,count(*) from users u,blog b where u.id=b.uid group by u.id
/*4. 获取会员的博文平均点赞数量最高的三位。显示字段（会员id，姓名，平均点赞数）*/
select u.id,u.name,avg(b.pcount)  from users u,blog b where u.id=b.uid group by u.id order by avg(b.pcount) desc limit 0,3
/*5. 删除没有发表博文的所有会员信息。*/
delete * from users u where u.id not in (select b.uid from blog b)

数据库结构