分享整理的12条sql语句连同数据

俺觉得自 己试着写写sql,调试调试还是有帮助的，读人家sql例子好像读懂了，自己写就未 必思路正确，调试得通，写得简洁。


这篇文字在网上被转载烂了，里面有些sql适合用在应用系统里，有些“报表”的感 觉更重些，主要是想复习前者。前20条大体还挺好，后30条明显偏报表风格了，而 且后面选例良莠不齐，选了12个例子做练习，（其实很多语法，case, any/all, union之类的都没包括），用mysql数据库，并共享自己造出来的数据。关于这12条 sql, 修正了原文中有纰漏的地方。

sql是基本技能，若能写得好也挺精彩的，还在继续练习。绝不提倡努力写复杂sql 解决业务问题。应用系统里如果存在很复杂的sql，往往揭示了业务逻辑向下泄露 到sql层的问题，不利于维护和扩展，虽然这样确实常能提高运行效率。具体情况 自行取舍。
下面的例子都是比较通用的sql, 其实针对特定的数据库，需要学的也挺多，比如 oracle db的decode函数, rowid, rownum, connect by 虽然不通用，但是很实用。

数据可以在这里下载，只是用作练习，没做任何外键关联：

CREATE DATABASE  IF NOT EXISTS `sql_learning` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `sql_learning`;
-- MySQL dump 10.13  Distrib 5.1.40, for Win32 (ia32)
--
-- Host: localhost    Database: sql_learning
-- ------------------------------------------------------
-- Server version 5.5.8

/*!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 `sc`
--

DROP TABLE IF EXISTS `sc`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sc` (
  `S#` int(11) NOT NULL,
  `C#` int(11) NOT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`S#`,`C#`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `sc`
--

LOCK TABLES `sc` WRITE;
/*!40000 ALTER TABLE `sc` DISABLE KEYS */;
INSERT INTO `sc` VALUES (1,1,80),(1,2,60),(1,3,78),(1,4,67),(1,5,80),(1,6,60),(2,1,90),(2,2,80),(2,3,90),(2,4,54),(3,1,30),(3,2,70),(4,5,90),(4,6,80),(5,1,76),(5,4,90);
/*!40000 ALTER TABLE `sc` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `course`
--

DROP TABLE IF EXISTS `course`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `course` (
  `C#` int(11) NOT NULL,
  `Cname` varchar(45) DEFAULT NULL,
  `T#` int(11) DEFAULT NULL,
  PRIMARY KEY (`C#`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `course`
--

LOCK TABLES `course` WRITE;
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
INSERT INTO `course` VALUES (1,'数学',3),(2,'语文',2),(3,'英语',5),(4,'物理',4),(5,'化学',1),(6,'计算机',1);
/*!40000 ALTER TABLE `course` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `teacher`
--

DROP TABLE IF EXISTS `teacher`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teacher` (
  `T#` int(11) NOT NULL AUTO_INCREMENT,
  `Tname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`T#`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `teacher`
--

LOCK TABLES `teacher` WRITE;
/*!40000 ALTER TABLE `teacher` DISABLE KEYS */;
INSERT INTO `teacher` VALUES (1,'叶平'),(2,'周华健'),(3,'刘德华'),(4,'张学友'),(5,'任达华'),(6,'郑伊健'),(7,'张惠妹');
/*!40000 ALTER TABLE `teacher` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
  `S#` int(11) NOT NULL,
  `Sname` varchar(45) DEFAULT NULL,
  `Sage` int(11) DEFAULT NULL,
  `Ssex` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`S#`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='\n\n';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'张三',18,'男'),(2,'李四',19,'男'),(3,'王红',20,'女'),(4,'周六',19,'女');
/*!40000 ALTER TABLE `student` 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 2012-06-25 22:36:36