创建简易博客数据库
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)
数据库结构