SQL实例整理

版权声明:本文为博主原创文章,转载注明出处http://blog.csdn.net/u013142781

本文适合将w3school的SQL教程(http://www.w3school.com.cn/sql/sql_create_table.asp)都基本看过一遍的猿友阅读。

说说博主的情况吧。毕业找工作之前确实有大概看过w3school的SQL教程,然后参加校园招聘,每次遇到一些SQL笔试题,立马懵逼了(大写的)。其实我那时候大概知道怎么写的,只是总是写不正确,或者是对一些特定的而且没有见过的场景的SQL语句,根本写不出来。相信不少猿友工作之后,其实挺多都用得不熟吧(如果白板编写的话)。

因为大部分Java猿友工作做的事情,其实比较少情况自己去动手写特定场景的SQL(可能有也是百度,接触过一个会一个),简单SQL也是直接由框架(hibernate和Mybatis)提供接口。当然,那种专门做后台,经常跟数据打交道的Java猿友除外,因此只能说大部分。

如果还是继续保持这样的状态的话,下次自己找工作遇到SQL笔试题,估计也会继续懵逼(大写的)。

下面小宝鸽整理了一些实例(实例主要来自网上),以提升自己写SQL的某些关键字的理解。

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');
INSERT INTO `table_test_one` VALUES ('2', '201601', '张三', '0002', '语文', '66');
INSERT INTO `table_test_one` VALUES ('3', '201602', '李四', '0001', '数学', '60');
INSERT INTO `table_test_one` VALUES ('4', '201602', '李四', '0003', '英语', '78');
INSERT INTO `table_test_one` VALUES ('5', '201603', '王五', '0001', '数学', '99');
INSERT INTO `table_test_one` VALUES ('6', '201603', '王五', '0002', '语文', '99');
INSERT INTO `table_test_one` VALUES ('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');
INSERT INTO `table_test_two` VALUES ('1991', '2', '1.2');
INSERT INTO `table_test_two` VALUES ('1991', '3', '1.3');
INSERT INTO `table_test_two` VALUES ('1992', '1', '2.1');
INSERT INTO `table_test_two` VALUES ('1992', '2', '2.2');
INSERT INTO `table_test_two` VALUES ('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语句就会把小宝鸽这条数据删除掉了。



未完待续……….(TODO),边学习边写博客真的很花时间,累并快乐着~~~

时间: 2024-10-31 08:17:03

SQL实例整理的相关文章

JavaScript语言精粹经典实例(整理篇)_javascript技巧

数据类型 JavaScript 是 弱类型 语言,但并不是没有类型,JavaScript可以识别下面 7 种不同类型的值: 基本数据类型 1.Boolean 2.Number 3.String 4.null 5.undefined 6.Symbol Object 1.Array 2.RegExp 3.Date 4.Math 5.... 可以使用 typeof 判断数据类型,操作符返回一个字符串,但并非返回的所有结果都符合预期 typeof false // "boolean" type

AngularJS入门教程中SQL实例详解_AngularJS

AngularJS SQL 在前面章节中的代码也可以用于读取数据库中的数据. 使用 PHP 从 MySQL 中获取数据 AngularJS 实例 <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <script src="http://apps.bdimg.com/libs/angular.js/1.4.6/angular.min.js"></s

Sql Server 整理收集

server 正在学习SQL Server 期望收集一些常见的Sql Server 问题 Sql Server问题摘要 1.         合并若干个表? 描述:建立一个新表,其字段结构是其他若干个表Join以后的结果. 解决思路:select * into 新表名 from 旧表1名,旧表2名,-- 实例: --表NewTableName的字段为表titles和titleauthor字段表相加: ---记录为记录为select * from titles,titleauthor的记录 sel

一些有用的sql语句整理 推荐收藏_MsSql

1.说明:创建数据库 CREATE DATABASE database-name 2.说明:删除数据库 drop database dbname 3.说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始 备份 BACKUP DATABASE pubs TO testBack 4.说明:创建新表 cr

艾伟_转载:LINQ to SQL、NHibernate比较(二)-- LINQ to SQL实例

    用ADO.NET操作数据库大家一定再熟悉不过了,select.insert.update等等SQL语句大家也都必然滚瓜烂熟.我将自己在学习LINQ to SQL过程中的动手经历记录下来,作为今后学习的参考,也希望对刚刚接触的人有一点帮助.     我在本文涉及到一个很简单的系统,利用DataGridView实现数据库数据的批量增.删.改,不是什么强大的功能.     如果有人感兴趣,可以在看完我的这篇文章之后用ADO.NET实现同样的功能,看看到底会比使用LINQ to SQL多多少时间

一些有用的sql语句整理 推荐收藏

1.说明:创建数据库 CREATE DATABASE database-name 2.说明:删除数据库 drop database dbname 3.说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始 备份 BACKUP DATABASE pubs TO testBack 4.说明:创建新表 cr

Oracle SQL_TRACE和10046事件的优化SQL实例

一数据库版本 LEO1@LEO1>select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release11.2.0.1.0 - Production CORE  

Oracle中日期方面的SQL实例

№1:取得当前日期是本月的第几周 SQL> select to_char(sysdate,'YYYYMMDD W HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'YY ------------------- 20030327 4 18:16:09 SQL> select to_char(sysdate,'W') from dual; T - 4 №2:取得当前日期是一个星期中的第几天,注意星期日是第一天 SQL> select sysdate,to_ch

Linux系统中df命令的一些使用实例整理汇总

  df 即"可用磁盘"disk free,用于显示文件系统的磁盘使用情况.默认情况下 df 命令将以每块 1K 的单位进行显示所有当前已挂载的文件系统,如果你想以人类易读的格式显示 df 命令的输出,像这样"df -h"使用 -h 选项. 在这篇文章中,我们将讨论 df 命令在 Linux 下11种不同的实例. 在 Linux 下 df 命令的基本格式为: 代码如下: # df {options} {mount_point_of_filesystem} 在 df