开发者社区> 小珞珞> 正文

[数据库] SQL语句select简单记录总结

简介:
+关注继续查看

最近SQL语句写得比较多,也发现了自己的很多不足之处。在此先写一篇关于SQL语句的在线笔记,方便大家学习和后面的工作,SQL Server、MySQL、Oracle基本语法都类似,接下来我需要阅读《SQL Server性能优化与管理的艺术》。

最后,希望这篇文章对你有所帮助吧!重点是select语句的用法。
目录如下:

  • 一.创建数据库和表
    • 1.创建数据库
    • 2.创建表并设置主键
    • 3.插入数据
  • 二.select查询操作
    • 1.通过日期计算年龄
    • 2.获取某列所有不同的属性值 group by
    • 3.查询字符串匹配like和多值属性判断in
    • 4.查询输出某列属性中某个特定值
    • 5.子查询统计不同阶段学生总数
    • 6.使用子查询按行动态输出学院相关信息
    • 7.Oracle数据库null设置成自定义值的方法
    • 8.Oracle计算百分比方法
    • 9.Oracle查询除法运算
    • 10.Oracle统计某个属性逗号分隔值的个数


一. 创建数据库和表


1.创建数据库
--创建数据库
create database StudentMS

--使用数据库
use StudentMS

--删除数据库
--drop database StudentMS

2.创建表并设置主键(外键类似)
--创建学生表 (属性:姓名、学号(pk)、学院、出生日期、性别、籍贯)
create table xs
(
	name varchar(10) not null,
	id varchar(10) not null,
	xy varchar(10),
	birthday datetime,
	xb char(2),
	jg varchar(8)
)

--创建学生表主键:学号
alter table xs
	add constraint
pk_xs primary key(id)


--创建表学生表外键:系代号 此表中xdh已被省略
alter table xs
	add constraint
fk_xs foreign key(xdh)
references xb (xdh)

3.插入数据
insert into xs 
	(id, name, xb, birthday, xy, jg) 
values('1160001', '刘备', '男', '1991-11-5', '软件学院', '河北省'); 
总共插入10个学生的数据,其中如birthday可为null,如下:


二. select查询操作


1.通过日期计算年龄
通过 (当前日期-出生日期) 两种方法:
        1). year(getdate()) - year(birthday)
        2). datediff(YY, birthday, getdate()) 

代码如下:
select id as 学号, name as 姓名, year(getdate())-year(birthday) as 年龄, birthday as 出生日期
from xs;

select id as 学号, name as 姓名, datediff(YY,birthday,getdate()) as 年龄, birthday as 出生日期
from xs;
输出如下所示,后面也可以计算不同年龄段的人数:

注意:Oracle会报错“ORA-00904: 'DATEDIFF' invalid identifier”, 它的方法如下:
          Trunc(MONTHS_BETWEEN(SYSDATE, BIRTH_DATE)/12)
          函数Trunc在这里对带有小数位数的数字取整数部分,SYSDATE为oracle的获取当前日期的函数,BIRTH_DATE为我自己的数据库表中存储生日日期的字段。
          判断年份等于当前年份方法:YEAR=to_char(sysdate, 'yyyy')

2.获取某列所有不同的属性值 group by
它的功能包括:获取学院列所有学院信息,也可以用来统计所有学生同名的人数。
--方法1:group by 列分组
select xy from xs group by xy;

--方法2:列出不同的值
select distinct xy from xs;
输出结果:

统计不同学院的人数信息:
select xy as 学院, count(*) as 总人数 from xs group by xy;

PS:如果需要排序可以添加order by xy,而统计重名学生可通过having count(*)>1。
推荐:http://www.cnblogs.com/rainman/archive/2013/05/03/3058451.html

3.查询字符串匹配like和多值属性判断in
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式:SQL LIKE 操作符 - w3school
--匹配姓名以"黄"开头的学生
select * from xs where name like '黄%';

--匹配学院包含"计算机"的学生
select * from xs where xy like '%计算机%';

--匹配姓名以"尚香"结尾的学生
select * from xs where name like '%尚香';
输出结果:

LIKE匹配某个字段的变量的方法:DL_BHXNZYMC like '%' || ZY_NAME ||'%'
select T_WSTB_DLPYJBQKB.DL_BHXNZYMC, ZY_NAME 
from T_WSTB_ZYJBQK, T_WSTB_DLPYJBQKB 
where T_WSTB_DLPYJBQKB.DL_BHXNZYMC like '%' || ZY_NAME ||'%'
输出如下所示,也可以某个字段包含的个数:

IN 操作符允许我们在 WHERE 子句中规定多个值,换种说法就是替换or:
select * from xs where jg in ('河北省','河南省');
输出结果:

4.查询输出某列属性中的某个特定值
比如我希望输出软件学院这个值,可以使用group by分组再定义这个值。其缺点是这个值必需是定义存在的,当然如果C#或JAVA可以定义变量连接这个值。
select xy as 学院名称 from xs where xy='软件学院' group by xy;
输出结果:

5.子查询统计不同阶段学生总数
使用子查询统计不同学院总人数、不同性别总人数和河北/河南学生总人数。

--子查询统计人数
select a.a_num as 软院人数, b.b_num as 计院人数, c.c_num as 自动化人数, 
       d.d_num as 男生人数, e.e_num as 女生人数, f.f_num as 河北河南人数
from
(select count(*) as a_num from xs where xy='软件学院') a,
(select count(*) as b_num from xs where xy='计算机学院') b,
(select count(*) as c_num from xs where xy='自动化学院') c,
(select count(*) as d_num from xs where xb='男') d,
(select count(*) as e_num from xs where xb='女') e,
(select count(*) as f_num from xs where jg in ('河北省','河南省')) f;
输出结果:

PS:当时需要设计一条SQL,统计各个学院的教师总数、高级职称教师总数、35岁以下青年教师总数、教授教师总数;而且输出是一行,每个学院共5个值,例如:

上面这种显示方法非常局限,不能实现动态的查询,如果增加新的学院,你SQL语句中 where xy='软件学院' 需要相应修改,如果是连接前端建议使用逗号连接查询。当然,SQL语句更理想的输出如下:(参考6)


6.使用子查询按行动态输出学院相关信息
获取每个学院总人数、男生总人数、小于等于25岁的总人数和生源地河北河南人数。
这种方法通常是多个表之间的夸表查询,首先创建一个学院表:学院名称和学院代码。
--创建学院表 
create table table_xy
(
	name varchar(10) not null,
	id varchar(10) not null
);

--插入数据
insert into table_xy(id, name) values('001', '软件学院');
insert into table_xy(id, name) values('002', '计算机学院');
insert into table_xy(id, name) values('003', '自动化学院');
insert into table_xy(id, name) values('004', '法学院');
输出如下,这里插入一个法学院,它的统计结果都为空:

然后,子查询SQL语句如下:

select distinct name as 学院名称, 
(select count(*) from xs where xs.xy=table_xy.name) as 总人数,
(select count(*) from xs where xs.xy=table_xy.name and xs.xb='男') as 男生总数,
(select count(*) from xs where xs.xy=table_xy.name and datediff(YY,birthday,getdate())<=25) as 二十五岁人数,
(select count(*) from xs where  xs.xy=table_xy.name and xs.jg in ('河北省','河南省')) as 河北河南生源地
from table_xy;
输出结果:

7.Oracle数据库null设置成自定义值的方法
方法包括主要有两个,参考:http://www.soso.io/article/72986.html

1). nvl(expr1, expr2)
若EXPR1是NULL,則返回EXPR2,否則返回EXPR1。nvl(person_name,“未知”)表示若person_name字段值为空时返回“未知”,如不为空则返回person_name的字段值。通过这个函数可以定制null的排序位置。 |

2). decode(DEPARTMENT_NAME, null, 'NULL', DEPARTMENT_NAME)
如果部门名称在表中值为null,则用NULL替代,也可设置为"空"各种自定义字符串。decode函数比nvl函数更强大,同样它也可以将输入参数为空时转换为一特定值。
decode(person_name,null,“未知”, person_name)表示当person_name为空时返回“未知”,如不为空则返回person_name的字段值。 

PS:而SQL Server中没有函数decode,但是其实质可以通过case when来实现和替代。
参考:http://blog.csdn.net/hu_shengyang/article/details/10533865

8.Oracle计算百分比方法
核心SQL语句如下:to_char(trunc(NUM/ALL_NUM*100, 2)) || '%
其中NUM除以ALL_NUM总数,并且保留两位有效数字,如下图所示:


9.Oracle查询除法运算
主要语句:select a/b from c;
如:1.0*男生人数/ 人总数*100,使用trunc主要是小数点保留两位有效数字。
如果出现错误:
[Err]ORA-01476: divisor is equal to zero,可修改为:select decode(b,0,0,a/b) from c。
select t1.ZFJGSL as 数量,
		trunc( 1.0 * (select ITEM_VALUE from T_WSTB_YJBKBYSJYQK
			where RECORD_YEAR=(to_char(sysdate, 'yyyy')-2) and FIRST_NUM='2' and SECOND_NUM='2') /
			(select ITEM_VALUE from T_WSTB_YJBKBYSJYQK
			where RECORD_YEAR=(to_char(sysdate, 'yyyy')-2) and FIRST_NUM='2' and SECOND_NUM='1') * 100,
		2) as 比例
from 
(select ITEM_VALUE as ZFJGSL from T_WSTB_YJBKBYSJYQK
 where RECORD_YEAR=(to_char(sysdate, 'yyyy')-2) and FIRST_NUM='2' and SECOND_NUM='2') t1;
输出如下所示:

10.Oracle统计某个属性逗号分隔值的个数
如下图所示,学科大类中包括各个学科专业名称,通过逗号分隔,如何统计个数呢?

SQL代码如下,Orcale使用length,其他是len函数:
select DL_NAME as num1,
	length(DL_BHXNZYMC)-length(replace(DL_BHXNZYMC,',',''))+1 as num2
from T_WSTB_DLPYJBQKB
运行结果如下所示:




三. 总结


最后希望文章对你有所帮助,这是一篇我的在线笔记,同时后面结合自己实际项目和SQL性能优化,将分享一些更为专业的文章~
最近真的太忙了,做自己的毕设、学校的项目、帮别人毕设或项目解惑,虽然累,但感觉还是非常充实的;买了本《邓肯专·永不退场》,有机会再品味吧!同时每天刀两把,周末搞个小火锅,这也是生活啊!(*^__^*) 嘻嘻.......

(By:Eastmount 2016-01-17 深夜3点   http://blog.csdn.net//eastmount/ )


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Mysql如何去掉数据库中重复记录?
对于常规的MySQL数据表中可能存在重复的数据,有些情况是允许重复数据的存在,有些情况是不允许的,这个时候我们就需要查找并删除这些重复数据,以下是具体的处理方法! 方法一:防止表中出现重复数据 当表中未添加数据时,可以在MySQL数据表中设置指定的字段为PRIMARY KEY(主键) 或者 UNIQUE(唯一) 索引来保证数据的唯一性。
831 0
你会不会用mysql查询近7个月的数据?没有记录默认为空
你会不会用mysql查询近7个月的数据?没有记录默认为空
39 0
从SQLSERVER/MYSQL数据库中随机取一条或者N条记录
原文:从SQLSERVER/MYSQL数据库中随机取一条或者N条记录 从SQLSERVER/MYSQL数据库中随机取一条或者N条记录 很多人都知道使用rand()函数但是怎麽使用可能不是每个人都知道 建立测试表 USE [sss] GO CREATE TABLE RANDTEST(...
1379 0
使用Insert Select实现同时向多个表插入记录
使用Insert Select实现同时向多个表插入记录一、无条件 INSERT ALL-----------------------------------------------------...
588 0
简单实用SQL脚本Part:查找SQL Server 自增ID值不连续记录
原文:简单实用SQL脚本Part:查找SQL Server 自增ID值不连续记录        在很多的时候,我们会在数据库的表中设置一个字段:ID,这个ID是一个IDENTITY,也就是说这是一个自增ID。
1329 0
mysql 数据库导入\导出(总结备忘)
<div style="font-family:'lucida Grande',Verdana,'Microsoft YaHei'; font-size:14px; line-height:23.8px"> <div class="showContent" style="padding:0px; margin:20px 15px 8px; line-height:2; border-bo
3811 0
Select Top在不同数据库中的使用
1. oracle数据库 SELECT * FROM TABLE1 WHERE ROWNUM
734 0
【转】SQL删除重复记录,只保留其中一条
SQL:删除重复数据,只保留一条用SQL语句,删除掉重复项只保留一条在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢  1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 select * from people where peopleId...
662 0
+关注
168
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载