分享整理的12条sql语句连同数据
来源:asp之家 时间:2012-07-11 16:14:59
俺觉得自 己试着写写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