SQLCookbook中文版:第1章 检索记录,第2章 查询结果排序

链接：https://pan.baidu.com/s/1YHRLPhNFcFIWq3xLr_w6dA

提取码：6yam

数据表

数据库: sqlcookbook, Table EMP and DEPT of SQL Cookbook for MySQL,引自 https://gist.github.com/YujiShen/39f6ef573ada22b87998

# Thanks to http://justinsomnia.org/2009/04/the-emp-and-dept-tables-for-mysql/

DROP TABLE IF EXISTS emp;

CREATE TABLE emp (
  empno decimal(4,0) NOT NULL,
  ename varchar(10) default NULL,
  job varchar(9) default NULL,
  mgr decimal(4,0) default NULL,
  hiredate date default NULL,
  sal decimal(7,2) default NULL,
  comm decimal(7,2) default NULL,
  deptno decimal(2,0) default NULL
);

DROP TABLE IF EXISTS dept;

CREATE TABLE dept (
  deptno decimal(2,0) default NULL,
  dname varchar(14) default NULL,
  loc varchar(13) default NULL
);

INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20');
INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30');
INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30');
INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20');
INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30');
INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30');
INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10');
INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-09','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10');
INSERT INTO emp VALUES ('7844','TURNER','SALESMAN','7698','1981-09-08','1500.00','0.00','30');
INSERT INTO emp VALUES ('7876','ADAMS','CLERK','7788','1983-01-12','1100.00',NULL,'20');
INSERT INTO emp VALUES ('7900','JAMES','CLERK','7698','1981-12-03','950.00',NULL,'30');
INSERT INTO emp VALUES ('7902','FORD','ANALYST','7566','1981-12-03','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7934','MILLER','CLERK','7782','1982-01-23','1300.00',NULL,'10');

INSERT INTO dept VALUES ('10','ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES ('20','RESEARCH','DALLAS');
INSERT INTO dept VALUES ('30','SALES','CHICAGO');
INSERT INTO dept VALUES ('40','OPERATIONS','BOSTON');

DROP TABLE IF EXISTS emp_bonus;
CREATE TABLE emp_bonus (
  empno decimal(4,0)  NOT NULL,
  received date NOT NULL,
  type char(10) NOT NULL
);

INSERT INTO emp_bonus VALUES ('7369','2005-05-14','1');
INSERT INTO emp_bonus VALUES ('7900','2005-05-14','2');
INSERT INTO emp_bonus VALUES ('7788','2005-05-14','3');

python 操作mysql

# 驱动安装参考:
# https://dev.mysql.com/downloads/connector/python/
# https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html
import mysql.connector
cnx = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='sqlcookbook')
cursor = cnx.cursor()

sql = 'select * from emp;'
cursor.execute(sql)
results = cursor.fetchall()

cursor.close()
cnx.close()

一个打印sql执行结果的示例:

import mysql.connector
cnx = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='sqlcookbook')
cursor = cnx.cursor()

sql = """
Select ename, sal,  
                    case when sal <= 2000 then 'UNDERPRAID'
                         when Sal >= 4000 then 'OVERPAID'
                         else 'OK'
                    end as Status
from emp
"""
cursor.execute(sql)
results = cursor.fetchall()
col_name_list = [tuple[0] for tuple in cursor.description]

cursor.close()
cnx.close()

# 打印列名称
for c in col_name_list:
    print(c,end=' ')
print('\t')

for r in results:
    for d in r:
        if d is None:
            print('NULL',end=' ')
        else:
            print(str(d),end=' ')
    print('\t')

第1章 检索记录

1.1 检索所有行和列

选择所有的行和列在表结构修改等情况下会有使用上的问题,返回的行的字段会有变化, 显示指定列名称是明智的

select * from emp;
select name,id from emp;

1.2 检索满足特定条件的行: where 子句

select * from emp where id=1

支持关系运算 >,>=,<,<=,=,!=,<>

1.3 满足多个条件, 使用逻辑运算 and, or, 圆括号

Select * from emp
where (   deptno = 10 or  comm is  not null or sal <= 2000) and deptno=20;

1.4 从表中检索部分列

(略)

1.5 为列取有意义的名称:可以理解为使用别名

只是改变了查询结果中的列名，可以按这种格式使用AS 关键字:

原名 AS 新名

select sal as salary,comm as commission   from emp

1.6在WHERE子句中引用取别名的列

这里涉及到各个子句的执行顺序问题:

  • from 子句: 这里用取别名的查询作为内联视图,保证别名在from执行时已经存在
  • where子句
  • select
Select salary, commission from (select sal as salary,comm as commission   from emp) X
where salary<5000;

X为内联视图的别名.

1.7连接多列字符串

mysql用concat函数

select concat (ename,' WORKS AS RAR ',job) as msg from emp where deptno=10

输出:

msg
CLARK WORKS AS RAR MANAGER
KING WORKS AS RAR PRESIDENT
MILLER WORKS AS RAR CLERK

使用CONCAT 函数连接来自 多个列的值, 在DB2、Oracle 和PostgreSQL 中, “||”是CONCAT 函数的简写方式,，“+ ”是 SQL Server 中的简写方式。

1.8 在select 使用条件逻辑: case语句

类似 if-else 语句, 只是case每种情况对应一个计算结果, 这个结果作为case语句别名对应的列的值输出. 所以 case语句对应一个列

Select ename, sal,  
                    case when sal <= 2000 then 'UNDERPRAID'
                         when Sal >= 4000 then 'OVERPAID'
                         else 'OK'
                    end as Status
from emp

结果:

ename sal Status
SMITH 800.00 UNDERPRAID
ALLEN 1600.00 UNDERPRAID
WARD 1250.00 UNDERPRAID
JONES 2975.00 OK
MARTIN 1250.00 UNDERPRAID
BLAKE 2850.00 OK
CLARK 2450.00 OK
SCOTT 3000.00 OK
KING 5000.00 OVERPAID
TURNER 1500.00 UNDERPRAID
ADAMS 1100.00 UNDERPRAID
JAMES 950.00 UNDERPRAID
FORD 3000.00 OK
MILLER 1300.00 UNDERPRAID

1.9 限制返回的行数

MySQL,PG 用limit

select * from emp limit 10;

1.10 随机返回n条记录

mysql用rand函数, PG用random函数

select * from emp order by random() limit 10;

1.11 查找某列为空的值: IS NULL

select * from emp where comm is null;

1.12 用实际值代替空值: coalesce函数

select ename, coalesce(comm,0) from emp ;

输出:

ename coalesce(comm,0)
SMITH 0.00
ALLEN 300.00
WARD 500.00
JONES 0.00
MARTIN 1400.00
BLAKE 0.00
CLARK 0.00
SCOTT 0.00
KING 0.00
TURNER 0.00
ADAMS 0.00
JAMES 0.00
FORD 0.00
MILLER 0.00

注意列名为 coalesce(comm,0)

1.13 按模式搜索

  • in: 字段完全匹配某一项
  • like字符串匹配: %代表任何字符串,_匹配单个字符

在部门 10和部门 20 中, 名字中有一个“I” 或者职务 (job title)以“ER ”结尾:

select ename,job,deptno from emp where deptno in (10,20) and (ename like '%I%' or job like '%ER');

输出:

ename job deptno
SMITH CLERK 20
JONES MANAGER 20
CLARK MANAGER 10
KING PRESIDENT 10
MILLER CLERK 10

第2章 查询结果排序

2.1 以指定的次序返回结果:默认升序ASC,降序为 DESC

select ename ,job,sal
from emp         
where deptno ='10'
Order by sal DESC

结果:

ename job sal
KING PRESIDENT 5000.00
CLARK MANAGER 2450.00
MILLER CLERK 1300.00

不一定要指定排序所基于的列名，也可以给出表示这列的编号。这个编号从 1开始,但是用列名称更明确:

select ename ,job,sal
from emp         
where deptno ='10'
Order by 3 DESC

2.2 按多个字段排序: 每个字段可以指定升序或降序

在EMP表中，首先按照 DEPTNO 的升序排序行，然后按照工资的降序排列

select deptno, sal,ename from emp order by deptno asc,sal desc

结果:

deptno sal ename
10 5000.00 KING
10 2450.00 CLARK
10 1300.00 MILLER
20 3000.00 FORD
20 3000.00 SCOTT
20 2975.00 JONES
20 1100.00 ADAMS
20 800.00 SMITH
30 2850.00 BLAKE
30 1600.00 ALLEN
30 1500.00 TURNER
30 1250.00 MARTIN
30 1250.00 WARD
30 950.00 JAMES

2.3 按某列的子字符串排列:使用substr等函数

MySQL,PG:

Select ename, job,substr(job,length(job)-2) from emp
Order by substr(job,length(job)-2)

结果:

ename job substr(job,length(job)-2)
KING PRESIDENT ENT
SMITH CLERK ERK
JAMES CLERK ERK
ADAMS CLERK ERK
MILLER CLERK ERK
BLAKE MANAGER GER
CLARK MANAGER GER
JONES MANAGER GER
MARTIN SALESMAN MAN
TURNER SALESMAN MAN
WARD SALESMAN MAN
ALLEN SALESMAN MAN
FORD ANALYST YST
SCOTT ANALYST YST

2.4 对字母数字混合的数据列排序

(略)

2.5 处理排序空值

增加一列 is_null 用于决定空值排前面还是后面.下列示例中, comm有空值

另外计算一个附加的列,用于排序

select ename,sal,comm from (
    select ename,sal,comm,
        case 
            when comm is null then 0 
            else 1 
        end 
        as is_null
        from emp
) x
order by is_null desc,comm

降序,空值排最后,结果:

ename sal comm
TURNER 1500.00 0.00
ALLEN 1600.00 300.00
WARD 1250.00 500.00
MARTIN 1250.00 1400.00
SMITH 800.00 NULL
FORD 3000.00 NULL
JAMES 950.00 NULL
ADAMS 1100.00 NULL
KING 5000.00 NULL
SCOTT 3000.00 NULL
CLARK 2450.00 NULL
BLAKE 2850.00 NULL
JONES 2975.00 NULL
MILLER 1300.00 NULL

升序,空值排前:

select ename,sal,comm from (
    select ename,sal,comm,
        case 
            when comm is null then 0 
            else 1 
        end 
        as is_null
        from emp
) x
order by is_null asc,comm

结果:

ename sal comm
SMITH 800.00 NULL
FORD 3000.00 NULL
JAMES 950.00 NULL
ADAMS 1100.00 NULL
KING 5000.00 NULL
SCOTT 3000.00 NULL
CLARK 2450.00 NULL
BLAKE 2850.00 NULL
JONES 2975.00 NULL
MILLER 1300.00 NULL
TURNER 1500.00 0.00
ALLEN 1600.00 300.00
WARD 1250.00 500.00
MARTIN 1250.00 1400.00

2.6 根据设置的条件排序:使用case语句

如下,如果是 salesman, 根据 comm排序, 否则根据sal排序

select ename,job,sal,comm from emp order by job desc,
    case when job='SALESMAN' then comm end desc,
    case when job!='SALESMAN' then  sal end asc

结果:

ename job sal comm
MARTIN SALESMAN 1250.00 1400.00
WARD SALESMAN 1250.00 500.00
ALLEN SALESMAN 1600.00 300.00
TURNER SALESMAN 1500.00 0.00
KING PRESIDENT 5000.00 NULL
CLARK MANAGER 2450.00 NULL
BLAKE MANAGER 2850.00 NULL
JONES MANAGER 2975.00 NULL
SMITH CLERK 800.00 NULL
JAMES CLERK 950.00 NULL
ADAMS CLERK 1100.00 NULL
MILLER CLERK 1300.00 NULL
FORD ANALYST 3000.00 NULL
SCOTT ANALYST 3000.00 NULL

相关文章：

  1. SQLCookbook中文版:第3章 操作多个表

  2. SQLCookbook中文版:第12章 报表和数据仓库运算

  3. SQLCookbook中文版:第8章 日期运算,第9章 日期操作, 第10章 范围处理,第11章 高级查找

  4. SQLCookbook中文版:第6章 使用字符串,第7章 使用数字

  5. SQLCookbook中文版:第4章 插入,更新和删除; 第5章 元数据查询

正文完
 0
评论（没有评论）
文章搜索
随机文章
<p>科技爱好者周刊（第 114 期）：U 盘化生存和 Uber-job</p>

科技爱好者周刊（第 114 期）：U 盘化生存和 Uber-job

这里记录每周值得分享的科技内容，周五发布。 本杂志开源（GitHub: ruanyf/weekly），欢迎提交...
<p>科技爱好者周刊：第 105 期</p>

科技爱好者周刊：第 105 期

这里记录每周值得分享的科技内容，周五发布。（由于明天是假期，本期提前一天发布。） 本杂志开源（GitHub: ...
<p>科技爱好者周刊（第 219 期）：如何防止帐号被黑</p>

科技爱好者周刊（第 219 期）：如何防止帐号被黑

这里记录每周值得分享的科技内容，周五发布。 本杂志开源（GitHub: ruanyf/weekly），欢迎提交...
<p>科技爱好者周刊（第 268 期）：生产力是形容机器，不是形容人</p>

科技爱好者周刊（第 268 期）：生产力是形容机器，不是形容人

这里记录每周值得分享的科技内容，周五发布。 本杂志开源，欢迎投稿。另有《谁在招人》服务，发布程序员招聘信息。合...
<p>每周分享第 24 期</p>

每周分享第 24 期

这里记录过去一周，我看到的值得分享的东西，每周五发布。 （题图：浦东滨江，上海，2018。） Hacker N...
标签云
C语言DebianechartshugoHyper-Vsqlitewordpresswsl列表