mysql中常用的公式及个人演示

学生,院系表

-- phpMyAdmin SQL Dump
-- version 4.1.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: 2014-10-31 13:29:55
-- 服务器版本： 5.5.24
-- PHP Version: 5.3.13

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `php32`
--

-- --------------------------------------------------------

--
-- 表的结构 `学生表`
--

CREATE TABLE IF NOT EXISTS `学生表` (
`学生ID` int(11) NOT NULL AUTO_INCREMENT,
`学生` varchar(10) DEFAULT NULL,
`性别` enum('男','女') DEFAULT NULL,
`籍贯` varchar(10) DEFAULT NULL,
`院系ID` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`学生ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- 转存表中的数据 `学生表`
--

INSERT INTO `学生表` (`学生ID`, `学生`, `性别`, `籍贯`, `院系ID`) VALUES
(1, '罗弟华', '男', '江西', 1),
(2, '韩顺平', '男', '四川', 2),
(3, '吴英雷', '男', '黑龙江', 1),
(4, '王玉虹', '女', '河北', 3),
(5, '赵玉川', '男', '河北', 3),
(6, '刘招英', '女', '江西', 1);

-- --------------------------------------------------------

--
-- 表的结构 `院系`
--

CREATE TABLE IF NOT EXISTS `院系` (
`院系ID` int(11) NOT NULL AUTO_INCREMENT,
`院系名称` varchar(10) DEFAULT NULL,
`系办地址` varchar(20) DEFAULT NULL,
`系办电话` varchar(20) DEFAULT NULL,
PRIMARY KEY (`院系ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- 转存表中的数据 `院系`
--

INSERT INTO `院系` (`院系ID`, `院系名称`, `系办地址`, `系办电话`) VALUES
(1, '计算机系', '行政楼302', '010-66886688'),
(2, '数学系', '科研楼108', '010-80808800'),
(3, '物理系', '行政楼305', '010-68688787');

/*!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 */;

成绩表

-- MySQL dump 10.13 Distrib 5.5.24, for Win32 (x86)
--
-- Host: localhost Database: mysqltest
-- ------------------------------------------------------
-- Server version 5.5.24

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

DROP TABLE IF EXISTS `kecheng`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `kecheng` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`kecheng_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`kecheng_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `kecheng`
--

LOCK TABLES `kecheng` WRITE;
/*!40000 ALTER TABLE `kecheng` DISABLE KEYS */;
INSERT INTO `kecheng` VALUES (4,'html'),(5,'JQuery'),(3,'js'),(2,'Mysql'),(1,'PHP');
/*!40000 ALTER TABLE `kecheng` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `stu`
--

DROP TABLE IF EXISTS `stu`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`gender` enum('男','女') DEFAULT NULL,
`class_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `stu`
--

LOCK TABLES `stu` WRITE;
/*!40000 ALTER TABLE `stu` DISABLE KEYS */;
INSERT INTO `stu` VALUES (1,'张三','男',1),(2,'张四','男',2),(3,'张五','女',1),(4,'张六','男',2),(5,'张七','女',3),(6,'张八','男',1);
/*!40000 ALTER TABLE `stu` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `stu_kecheng`
--

DROP TABLE IF EXISTS `stu_kecheng`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `stu_kecheng` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stu_id` int(11) NOT NULL DEFAULT '0',
`kecheng_id` int(11) NOT NULL DEFAULT '0',
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`stu_id`,`kecheng_id`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `stu_kecheng`
--

LOCK TABLES `stu_kecheng` WRITE;
/*!40000 ALTER TABLE `stu_kecheng` DISABLE KEYS */;
INSERT INTO `stu_kecheng` VALUES (1,1,1,88),(2,1,2,77),(3,2,1,78),(4,2,2,91),(5,3,1,55),(6,3,2,65),(7,3,3,75),(10,4,3,74),(9,4,4,64),(11,5,2,46);
/*!40000 ALTER TABLE `stu_kecheng` 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 2014-10-28 21:40:27

购物表

-- phpMyAdmin SQL Dump
-- version 4.1.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: 2014-10-23 21:25:45
-- 服务器版本： 5.5.24
-- PHP Version: 5.3.13

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `gouwu`
--

-- --------------------------------------------------------

--
-- 表的结构 `order_goods`
--

CREATE TABLE IF NOT EXISTS `order_goods` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`pro_id` int(11) NOT NULL,
`price` float NOT NULL,
`count` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单商品表' AUTO_INCREMENT=9 ;

--
-- 转存表中的数据 `order_goods`
--

INSERT INTO `order_goods` (`id`, `order_id`, `pro_id`, `price`, `count`) VALUES
(1, 1, 1, 1999, 1),
(2, 1, 4, 5499, 1),
(3, 1, 12, 3238, 2),
(4, 2, 4, 5499, 1),
(5, 3, 5, 11499, 1),
(6, 3, 12, 3238, 1),
(7, 4, 2, 3999, 4),
(8, 5, 4, 5499, 1);

-- --------------------------------------------------------

--
-- 表的结构 `order_info`
--

CREATE TABLE IF NOT EXISTS `order_info` (
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`order_time` datetime NOT NULL,
`order_total` decimal(10,2) NOT NULL,
`order_addr` varchar(200) DEFAULT NULL,
PRIMARY KEY (`order_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单信息表' AUTO_INCREMENT=6 ;

--
-- 转存表中的数据 `order_info`
--

INSERT INTO `order_info` (`order_id`, `user_id`, `order_time`, `order_total`, `order_addr`) VALUES
(1, 1, '2014-04-05 06:07:08', '10736.00', '北京市昌平区回龙观镇金燕龙写字楼101室'),
(2, 2, '2014-06-15 16:17:18', '5499.00', '金燕龙102室'),
(3, 5, '2014-06-22 21:20:21', '14737.00', '金燕龙写字楼201室'),
(4, 1, '2014-07-06 05:04:03', '3999.00', '金燕龙101'),
(5, 1, '2014-07-08 09:10:11', '5499.00', '金燕龙写字楼前台代收');

-- --------------------------------------------------------

--
-- 表的结构 `product`
--

CREATE TABLE IF NOT EXISTS `product` (
`pro_id` int(11) NOT NULL AUTO_INCREMENT,
`pro_name` varchar(100) NOT NULL,
`protype_id` int(11) NOT NULL,
`price` decimal(10,2) NOT NULL,
`pinpai` varchar(25) NOT NULL,
`chandi` varchar(25) NOT NULL,
PRIMARY KEY (`pro_id`),
KEY `pro_name` (`pro_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品信息表' AUTO_INCREMENT=16 ;

--
-- 转存表中的数据 `product`
--

INSERT INTO `product` (`pro_id`, `pro_name`, `protype_id`, `price`, `pinpai`, `chandi`) VALUES
(1, '康佳（KONKA）42英寸全高清液晶电视', 1, '1999.00', '康佳', '深圳'),
(2, '索尼（SONY）4G手机（黑色）', 2, '3238.00', '索尼', '深圳'),
(3, '海信（Hisense）55英寸智能电视', 1, '4199.00', '海信', '青岛'),
(4, '联想（Lenovo）14.0英寸笔记本电脑', 3, '5499.00', '联想', '北京'),
(5, '索尼（SONY）13.3英寸触控超极本', 3, '11499.00', '索尼', '天津'),
(11, '索尼（SONY）60英寸全高清液晶电视', 1, '6999.00', '索尼', '北京'),
(12, '联想（Lenovo）14.0英寸笔记本电脑', 3, '2999.00', '联想', '北京'),
(13, '联想双卡双待3G手机', 2, '988.00', '联想', '北京'),
(15, '惠普（HP）黑白激光打印机', 3, '1169.00', '惠普', '天津');

-- --------------------------------------------------------

--
-- 表的结构 `product_type`
--

CREATE TABLE IF NOT EXISTS `product_type` (
`protype_id` int(11) NOT NULL AUTO_INCREMENT,
`protype_name` varchar(50) NOT NULL,
PRIMARY KEY (`protype_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='产品类型表' AUTO_INCREMENT=12 ;

--
-- 转存表中的数据 `product_type`
--

INSERT INTO `product_type` (`protype_id`, `protype_name`) VALUES
(1, '家用电器'),
(2, '手机数码'),
(3, '电脑办公'),
(4, '图书音像'),
(5, '家居家具'),
(6, '服装配饰'),
(7, '个护化妆'),
(8, '运动户外'),
(9, '汽车用品'),
(10, '食品酒水'),
(11, '营养保健');

-- --------------------------------------------------------

--
-- 表的结构 `user_info`
--

CREATE TABLE IF NOT EXISTS `user_info` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(25) NOT NULL,
`user_pswd` varchar(128) NOT NULL,
`real_name` varchar(25) NOT NULL,
`birthday` date DEFAULT NULL,
`QQ` varchar(12) DEFAULT NULL,
`reg_time` datetime NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表' AUTO_INCREMENT=7 ;

--
-- 转存表中的数据 `user_info`
--

INSERT INTO `user_info` (`user_id`, `user_name`, `user_pswd`, `real_name`, `birthday`, `QQ`, `reg_time`) VALUES
(1, 'ldh', '123', '罗弟华', '1989-02-10', '12500791', '2013-04-05 06:07:08'),
(2, 'hzk', '456', '韩中康', '1995-01-01', '19950101', '2009-08-07 06:05:04'),
(3, 'hsp', '789', '韩顺平', '1995-02-02', '', '2010-11-12 13:14:15'),
(4, 'ssh', '1234', '孙书华', '1991-02-03', '19910203', '2002-02-22 22:22:22'),
(5, 'wyl', '2234', '王玉虹', '1995-05-05', '555991', '2008-08-08 08:08:08'),
(6, 'ycj', '3234', '姚长江', '1991-02-03', '', '2011-11-11 11:11:11');

/*!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 */;

1.查出最贵的一件商品的商品信息。

2查出最大(最新)的商品的编号（ID）。

3查出最便宜商品的价格。

4取出最小(最旧)的商品编号。

5查出所有商品的总数量。

6查出所有商品的平均价格。

7查出联想品牌的所有商品的平均价格。

8按价格由高到低排序。

9按商品类型由低到高排序，类型内部按价格由高到低排序。

10.取出价格最高的前三个商品。

11查出每个产地各有多少数量的商品

12查出每个品种各有多少个商品

1) 查出“计算机系”的所有学生信息。

2) 查出“韩顺平”所在的院系信息。

3) 查出在“行政楼”办公的院系名称。

4) 查出男生女生各多少人。

5) 查出人数最多的院系信息。

6) 查出人数最多的院系的男女生各多少人。

7) 查出跟“罗弟华”同籍贯的所有人。

8) 查出有“河北”人就读的院系信息。

9) 查出跟“河北女生”同院系的所有学生的信息。

1)查询选修了 MySQL 的学生姓名；

2)查询张三同学选修了的课程名字；

3)查询只选修了1门课程的学生学号和姓名；

4)查询选修了至少3门课程的学生信息；

5)查询选修了所有课程的学生；

6)查询选修课程的学生人数；

7)查询所学课程至少有一门跟张三所学课程相同的学生信息。

8)查询两门及两门以上不及格同学的平均分

1，查出最贵的一件商品的商品信息。
方法1：
select * from product order by price desc limit 0, 1;

2，查出最大(最新)的商品的编号（ID）。
select pro_id from product order by pro_id desc limit 0, 1;

3, 查出最便宜商品的价格。
select price from product order by price asc limit 0, 1;

4, 查出所有商品的总数量。
select count(*) as num from product;
select max(price) as p from product;//最高价格
select min(price) as p from product;//最低价格
select avg(price) as p from product;//平均价格
select sum(price) as p from product;//总价格

5，查出联想品牌的所有商品的平均价格。
select avg(price) as p from product where pinpai='联想';//平均价格

6，按商品类型由低到高排序，类型内部按价格由高到低排序。
select * from product order by protype_id asc, price desc

7, 查出每个产地各有多少数量的商品
select chandi, count(*) as c from product group by chandi;

8，查出每个品种各有多少个商品
select protype_id, count(*) as c from product group by protype_id;

作业10两表查询:
4)查出男生女生各多少人。
select 性别, count(*) as c from 学生表 group by 性别;

5)查出人数最多的院系信息。
select * from 院系 where 院系ID = (
select 院系ID from 学生表
  group by 院系ID order by count(*) desc limit 0,1
)
上述语句没有考虑“院系人数最多且相等”的情况，则分析如下：
step 1：如果已知某两个院系ID（1和3），其人数最多且相等：P
select * from 院系 where 院系ID in (1,3);
step 2：如果已知该人数最多的数量是10，则可得到这些院系ID
select 院系ID from 学生表 group by 院系ID having count(*) = 10;
step 3：得到人数最多的院系的人数：
select count(*) as c from 学生表 group by 院系ID
  order by count(*) desc limit 0,1
step 4：将step3套入step2 的数字“10”,再将step2套入step1的数字列表"1,3"就得到：
select * from 院系 where 院系ID in (
select 院系ID from 学生表 group by 院系ID having count(*) = (
  select count(*) as c from 学生表 group by 院系ID
   order by count(*) desc limit 0,1
)
);
某学生（涛哥）提供的解决办法：
select 院系.* from 学生表 inner join 院系 on 学生表.院系ID=院系.院系ID
group by 院系ID having count(*)>= all(
  select count(*) from 学生表 group by 院系ID
);

6)查出人数最多的院系的男女生各多少人。
select 性别, count(*) as c from 学生表
where 院系ID = (
select 院系ID from 学生表
group by 院系ID order by count(*) desc limit 0,1
)
group by 性别;

7)查出跟“罗弟华”同籍贯的所有人。
select * from 学生表 where 籍贯=(
select 籍贯 from 学生表 where 学生='罗弟华'
);

8)查出有“河北”人就读的院系信息。
select * from 院系 where 院系ID in(
select distinct 院系ID from 学生表 where 籍贯='河北'
);

9)查出跟“河北女生”同院系的所有学生的信息。
step 1：找出有河北女生的院系ID
select distinct 院系ID from 学生表 where 籍贯='河北' and 性别='女'
steop 2: 以上述院系ID为条件，找出其中的所有学生信息：
select * from 学生表 where 院系ID in (
select distinct 院系ID from 学生表 where 籍贯='河北' and 性别='女'

);

作业11（三表查询）：

1)查询选修了 MySQL 的学生姓名；
方法1：子查询法：
select name from stu where id in(
select stu_id from stu_kecheng where kecheng_id = (
select id from kecheng where kecheng_name = 'Mysql'
)
);
方法2：连接查询法：
select stu.name from stu
inner join stu_kecheng as sk on sk.stu_id=stu.id
inner join kecheng as kc on kc.id = sk.kecheng_id
where kc.kecheng_name = 'Mysql';

2)查询张三同学选修了的课程名字；
方法1：子查询法：
select kecheng_name from kecheng where id in(
select kecheng_id from stu_kecheng where stu_id = (
select id from stu where name = '张三'
)
);
方法2：连接查询法：
select kecheng_name from stu
inner join stu_kecheng as sk on sk.stu_id=stu.id
inner join kecheng as kc on kc.id = sk.kecheng_id
where stu.name = '张三';

3)查询只选修了1门课程的学生学号和姓名；
select id, name from stu where id in(
/*这里子查询要从分数表中找出只选修了一门课程的学生ID*/
select stu_id from stu_kecheng group by stu_id having count(*)=1
)

4)查询选修了至少3门课程的学生信息；
select * from stu where id in(
/*这里子查询要从分数表中找出只选修了一门课程的学生ID*/
select stu_id from stu_kecheng group by stu_id having count(*)>=3
)

5)查询选修了所有课程的学生；
select * from stu where id in(
/*这里子查询要从分数表中找出只选修了一门课程的学生ID*/
select stu_id from stu_kecheng group by stu_id having count(*)=(
select count(*) from kecheng
)
)

6)查询选修课程的学生人数；
step 1：找出所有选修了课程的学生ID
select distinct stu_id from stu_kecheng;
step 2: 再对该结果数据，进行“计数”（取得行数），就是要的结果
select count(*) from (
select distinct stu_id from stu_kecheng
) as t1

某同学（凡大大）提供的做法：
select count(distinct(stu_id)) from stu_kecheng;

6a)查询每门课程的选修人数；
select kecheng_name, count(*) as c from stu_kecheng as sk
inner join kecheng as kc on kc.id = sk.kecheng_id
group by kecheng_id;

7)查询所学课程至少有一门跟张三所学课程相同的学生信息。
select * from stu
where id in(
/*找出那些学生id，其所学课程跟张三有至少一门相同*/
select distinct stu_id from stu_kecheng where kecheng_id in(
  /*找出张三所学课程的ID*/
  select kecheng_id from stu_kecheng as sk
   inner join stu on stu.id = sk.stu_id
   where stu.name='张三'
)
)
and name <> '张三'
;
另一个方法：
select * from stu
where id in(
/*找出那些学生id，其所学课程跟张三有至少一门相同*/
select distinct stu_id from stu_kecheng where kecheng_id in(
  /*找出张三所学课程的ID*/
  select kecheng_id from stu_kecheng where stu_id=(
   /*张三的id*/
   select id from stu where name ='张三'
  )
)
)
and name <> '张三'
;

8)查询两门及两门以上不及格同学的平均分
step 1:先找出2门或2门以上不及格的同学的id
select stu_id from stu_kecheng where score < 60
group by stu_id having count(*)>=2
step 2：以上述id为条件，找出这些学生的平均分：
select stu_id, avg(score) as c from stu_kecheng
inner join stu on stu.id = stu_kecheng.stu_id
where stu_id in (
  /*2门或2门以上不及格的同学的id*/
  select stu_id from stu_kecheng where score < 60
   group by stu_id having count(*)>=2
)
group by stu_id
step 3：将以上结果当做“数据源”，跟stu连接查询，就可以显示出学生姓名：
select stu.name, c from stu
inner join (
  select stu_id, avg(score) as c from stu_kecheng
  inner join stu on stu.id = stu_kecheng.stu_id
  where stu_id in (
   /*2门或2门以上不及格的同学的id*/
   select stu_id from stu_kecheng where score < 60
    group by stu_id having count(*)>=2
  )
  group by stu_id
) as t1
on stu.id = t1.stu_id