数据库SQL笔记

一. 创建数据库和表

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),
    xdh varchar(10) 
)  
  
--创建学生表主键:学号  
alter table xs  
    add constraint  
pk_xs primary key(id)  
  
  
--创建表学生表外键:系代号   
alter table xs  
    add constraint  
fk_xs foreign key(xdh)  
references xb (xdh)  

3.插入数据

insert into xs   
    (id, name, xb, birthday, xy, jg, xdh)   
values('201530310038', '苏国强', '男', '1993-09-11', '信息学院', '江苏省','021');   

二. 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;

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.

3.查询字符串匹配like和多值属性判断

--匹配姓名以"黄"开头的学生  
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 子句中规定多个值，换种说法就是替换

--用'河北省'替换'河南省'2 select * from xs where jg in ('河北省','河南省');  

4.查询输出某列属性中的某个特定值
 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;

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设置成自定义值的方法
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函数更强大，同样它也可以将输入参数为空时转换为一特定值。

PS：而SQL Server中没有函数decode，但是其实质可以通过case when来实现和替代。

8.Oracle计算百分比trunc方法
核心SQL语句如下：to_char(trunc(NUM/ALL_NUM*100, 2)) || '%
9.Oracle查询除法/运算
使用trunc主要是小数点保留两位有效数字。

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 

运行结果如下所示：

 11.Oracle使用CASE WHEN替代子查询

--子查询统计人数  
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;  

在Oracle中如果想减少代码量或者不适用子查询，可以CASE WHEN语句进行替代。

SELECT COUNT(*) AS 总人数,  
    COUNT(CASE WHEN HIGHEST_DEGREE='博士'THEN 1 END) AS 博士人数,   
    COUNT(CASE WHEN HIGHEST_DEGREE='硕士'THEN 1 END) AS 硕士人数,   
    COUNT(*)-COUNT(CASE WHEN HIGHEST_DEGREE='博士'THEN 1 END)-COUNT(CASE WHEN HIGHEST_DEGREE='硕士'THEN 1 END) AS 其他学历  
FROM T_WSTB_FULL_TIME_TEACHER   
WHERE IS_FOREGOER='是' and YEAR_START=to_char(sysdate,'yyyy')-1;  

这段SQL语句表示求教师表IS_FOREGOER学科带头人且入校年份为2015年的总人数、博士人数、硕士人数和其他学历的人数。
COUNT(CASE WHEN HIGHEST_DEGREE='博士' THEN 1 END) AS NUM2 表示当最高学历HIGHEST_DEGREE字段为'博士'时，统计数量加1。
当然如果需要计算学院各个班级的总人口，可以采用使用下面的SQL：COUNT(CASE WHEN DW_NAME='软件学院' THEN NUM_STU END) AS NUM2
也可以使用提到的CASE防止除法计算分母为0，ZS总数、SHSJ社会实践人数。即：round((case when ZS!=0 then SHSJ/ZS else 0 end),3) as bl

12.Oracle子查询中使用a.* 统计所有字段
这里是使用a.*统计所有字段，其中a表示子查询重命名的结果，给人以很清新的感觉，虽然有点华而不实吧！
PS: 这部分代码是网上看到的，感觉非常不错，自愧不如，学习之~

SELECT a.*, b.*   
FROM   
(SELECT SUM(DOMESTIC_TRAIN) + SUM(OVERSEA_TRAIN_TOTAL) AS zj,   
    SUM(DEGREE_PHD) AS qzgdbsx,   
    SUM(DOMESTIC_TRAIN) AS jnjxrcs,   
    SUM(OVERSEA_TRAIN_TOTAL) AS jwjxrcs   
 FROM TRAIN_INTERFLOW   
 where YEAR_START=to_char(sysdate,'yyyy')-2  
) a,   
(SELECT SUM(PARTICIPANT_NUMBER) AS cyjglxkrcs   
 FROM EDU_REVOLUTION   
 where YEAR_START=to_char(sysdate,'yyyy')-2  
) b;  

13.Oracle使用decode函数防止除法分母为0
解决方法：使用函数decode，当分母为0时直接返回0，否则进行除法运算。
select a/b from c; 修改成如下即可：select decode(b, 0, 0, a/b) from c;

select decode  
(  
    (select sum(BYSJY_BYS_NUM) from T_WSTB_YJBKBYSFZYBYJYQK t   
     where t.BYSJY_XNZY_CODE=T_WSTB_ZYJBQK.XNZY_CODE),  
    0,  
    0,  
    (select sum(BYSJY_JY_NUM) from T_WSTB_YJBKBYSFZYBYJYQK t   
     where t.BYSJY_XNZY_CODE=T_WSTB_ZYJBQK.XNZY_CODE) * 1.0  
        /  
    (select sum(BYSJY_BYS_NUM) from T_WSTB_YJBKBYSFZYBYJYQK t   
     where t.BYSJY_XNZY_CODE=T_WSTB_ZYJBQK.XNZY_CODE)  
) * 100 as JYL   
from T_WSTB_ZYJBQK  

当然也可以使用前面11提到的CASE防止分母为0，即：
round((case when ZS!=0 then SHSJ/ZS else 0 end),3) as bl

三. 总结
最后希望文章对你有所帮助，这是一篇我的在线笔记，同时后面结合自己实际项目和SQL性能优化，将分享一些更为专业的文章~

 

2016-10-30  14:24:46