MySQL应用实例转载-阿藏博客

以下内容出处：http://blog.csdn.net/u013142781/article/details/50836476

整理的内容可以作为使用或者了解。

1、用一条SQL 语句查询出每门课都大于80 分的学生姓名。（表结构如下图）

答案可以有如下两种：

select distinct student_name from table_test_one where student_name not in 
(select distinct student_name from table_test_one where score<=80);

select student_name from table_test_one group by student_name having min(score)>80;

第二种方法是group by 、min函数结合 having的使用，w3school教程里面也提到过（在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用）

下面我们自己造数据，后面的例子也会用到。

建表然后倒入初始数据：

DROP TABLE IF EXISTS `table_test_one`;
CREATE TABLE `table_test_one` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_no` varchar(10) NOT NULL,
  `student_name` varchar(10) NOT NULL,
  `subject_no` varchar(10) NOT NULL,
  `subject_name` varchar(10) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `table_test_one` VALUES 
('1', '201601', '张三', '0001', '数学', '98'),
('2', '201601', '张三', '0002', '语文', '66'),
('3', '201602', '李四', '0001', '数学', '60'),
('4', '201602', '李四', '0003', '英语', '78'),
('5', '201603', '王五', '0001', '数学', '99'),
('6', '201603', '王五', '0002', '语文', '99'),
('7', '201603', '王五', '0003', '英语', '98');

可以运行一下上面两个语句试试结果是不是你想要的。

2、删除除了id不同, 其他都相同的学生冗余信息，表如下：

答案：

delete table_test_one where id not in 
(select min(id) from table_test_one group by 
student_no, student_name, subject_no, subject_name, score);

先来造数据，题1中的数据只需要执行如下SQL就变成题2中的数据了：

update table_test_one set subject_no = '0001', subject_name = '数学' where id = 6;

然后我们先执行这个看看：

select min(id) from table_test_one group by 
student_no, student_name, subject_no, subject_name, score

这个的执行结果如下：

PS：GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。刚刚就是GROUP BY 对多列的使用场景。

3、行转列：

表数据如下：

希望查询到结果如下：

答案：

select year,
(select amount from table_test_two t where t.month = 1 and t.year = table_test_two.year) as month1,
(select amount from table_test_two t where t.month = 2 and t.year = table_test_two.year) as month2,
(select amount from table_test_two t where t.month = 3 and t.year = table_test_two.year) as month3
from table_test_two group by year;

利用group by 实现行转列，这种场景在数据统计的时候经常用到。

猿友可以造数据自己运行试试：

-- ----------------------------
-- Table structure for `table_test_two`
-- ----------------------------
DROP TABLE IF EXISTS `table_test_two`;
CREATE TABLE `table_test_two` (
  `year` int(11) NOT NULL,
  `month` int(11) NOT NULL,
  `amount` decimal(10,1) NOT NULL,
  PRIMARY KEY (`year`,`month`,`amount`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of table_test_two
-- ----------------------------
INSERT INTO `table_test_two` VALUES ('1991', '1', '1.1'),
('1991', '2', '1.2'),
('1991', '3', '1.3'),
('1992', '1', '2.1'),
('1992', '2', '2.2'),
('1992', '3', '2.3');

4、复制表( 只复制结构, 源表名：table_test_two 新表名：table_test_three)
答案：

create table table_test_three as 
select * from table_test_two where 1=2;

PS：如果需要将数据也复制过去，则上面改成where 1=1

5、复制表数据（将表 table_test_two 的数据复制到表table_test_three 里面）
答案：

insert into table_test_three (year,month,amount) 
select year,month,amount from table_test_two;

6、两张关联表，删除主表中已经在副表中没有的信息
答案：

delete from table_test_student where not exists 
(select * from table_test_class where table_test_student.class_id = table_test_class.calss_id);

我们先造点数据吧：

-- ----------------------------
-- Table structure for `table_test_class`
-- ----------------------------
DROP TABLE IF EXISTS `table_test_class`;
CREATE TABLE `table_test_class` (
  `calss_id` int(11) NOT NULL AUTO_INCREMENT,
  `calss_name` varchar(10) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`calss_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of table_test_class
-- ----------------------------
INSERT INTO `table_test_class` VALUES ('1', '一班');

-- ----------------------------
-- Table structure for `table_test_student`
-- ----------------------------
DROP TABLE IF EXISTS `table_test_student`;
CREATE TABLE `table_test_student` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(10) CHARACTER SET utf8 NOT NULL,
  `class_id` int(11) NOT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of table_test_student
-- ----------------------------
INSERT INTO `table_test_student` VALUES ('1', '罗国辉', '1');
INSERT INTO `table_test_student` VALUES ('2', '小宝鸽', '2');

执行后数据如下：

显然副表student中小宝鸽这条数据的calss_id，主表没有对应的class_id.

执行对应SQL语句就会把小宝鸽这条数据删除掉了。

未经允许不得转载：阿藏博客 » MySQL应用实例转载

MySQL应用实例转载

作者：阿藏

相关推荐