数据库复习:1

数据库复习:1

基础概念

SQL主要分成四部分：
（1）数据定义。（DDL）用于定义SQL模式、基本表、视图和索引的创建和撤消操作。
（2）数据操纵。（DML）数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
（3）数据控制。（DCL）包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。

（4）嵌入式SQL的使用规定。（TCL）涉及到SQL语句嵌入在宿主语言程序中使用的规则。

sql指令回忆

  1. group by:GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

    我们拥有下面这个 "Orders" 表：

    O_Id OrderDate OrderPrice Customer
    1 2008/12/29 1000 Bush
    2 2008/11/23 1600 Carter
    3 2008/10/05 700 Bush
    4 2008/09/28 300 Bush
    5 2008/08/06 2000 Adams
    6 2008/07/21 100 Carter

    现在，我们希望查找每个客户的总金额（总订单）。

    我们想要使用 GROUP BY 语句对客户进行组合。

    我们使用下列 SQL 语句：

    SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

    结果集类似这样：

    Customer SUM(OrderPrice)
    Bush 2000
    Carter 1700
    Adams 2000

  2. having:对查询及统计结果再做一次筛选。注意，having通常和group by一起用，但是不是必须和它一起，只要有select就可以用

    我们拥有下面这个 "Orders" 表：

    O_Id OrderDate OrderPrice Customer
    1 2008/12/29 1000 Bush
    2 2008/11/23 1600 Carter
    3 2008/10/05 700 Bush
    4 2008/09/28 300 Bush
    5 2008/08/06 2000 Adams
    6 2008/07/21 100 Carter

    现在，我们希望查找订单总金额少于 2000 的客户。

    我们使用如下 SQL 语句：

    SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

    结果集类似：

    Customer SUM(OrderPrice)
    Carter 1700

  3. order by:用于根据指定的列对结果集进行排序。

    原始的表 (用在例子中的)：

    Orders 表:

    Company OrderNumber
    IBM 3532
    W3School 2356
    Apple 4698
    W3School 6953

    实例 1

    以字母顺序显示公司名称：

    SELECT Company, OrderNumber FROM Orders ORDER BY Company

    结果：

    Company OrderNumber
    Apple 4698
    IBM 3532
    W3School 6953
    W3School 2356

  4. truncate和drop指令的区别:

    DROP TABLE会清除整个table，而TRUNCATE TABLE只是清楚表中的所有数据

  5. 储存过程

    存储过程 Procedure 是一组为了完成特定功能的 SQL 语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。

    下述代码创建了一个被称为 “LatestTasks” 的存储过程。

    它接受一个参数名为 @Count. 当调用这个存储过程，通过 @count 参数，它决定你想要多少行返回。

    代码如下：

    CREATE PROCEDURE LatestTasks @Count int AS
SET ROWCOUNT @Count
SELECT TaskName AS LatestTasks, DateCreated
FROM Tasks
ORDER BY DateCreated DESC

  6. count: COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）

    注意这里是列的数目，有值重复的列是没关系的，依然计数

    可以看一下这道题:

    表结构如下：

    CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sno` int(11) NOT NULL,
`cno` tinyint(4) NOT NULL,
`score` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;

    以下查询语句结果一定相等的是（）

    A.SELECT sum(score) / count(*) FROM score WHERE cno = 2;

    B.SELECT sum(score) / count(id) FROM score WHERE cno = 2;

    C.SELECT sum(score) / count(sno) FROM score WHERE cno = 2;

    D.SELECT sum(score) / count(score) FROM score WHERE cno = 2;

    E.SELECT sum(score) / count(1) FROM score WHERE cno = 2;

    F.SELECT avg(score) FROM score WHERE cno = 2;

    正确答案是:A,B,C,E/D,F

    注意这里的count(1):count(1)，其实就是计算一共有多少符合条件的行。1并不是表示第一个字段，而是表示一个固定值。

    而A,B,C,E中各个count的列(或者1)都有一个特点，就是不会置null,所以count出来的结果一样。而score是没有设置NOT NULL的，所以算出来的结果可能和ABCE不一样，但是它和F一样

例题

select语句完整语法：

  1. select 目标表的列名或列表达式序列

  2. from 基本表名和（或）视图序列

  3. [where 行条件表达式]

  4. [group by 列名序列]

[having 组条件表达式]

  1. [order by 列名[asc | desc]]，则sql语句的执行顺序是

正确答案是:2),3),4),1) ,5):先看是哪个表，然后使用where的条件筛选，之后对筛选后的结果使用group by分组，再用having筛选。再使用select选出要显示的列，然后order by决定排序

  1. 请取出 BORROW表中日期(RDATE字段)为当天的所有记录？(RDATE字段为datetime型，包含日期与时间)。SQL语句实现正确的是：（ ）

    错误答案是:select * from BORROW where RDATE=getdate()

    正确答案是:select * from BORROW where datediff(dd,RDATE,getdate())=0

    这是为什么呢，因为getdate()得到的结果是date类型的，而题中说了RDATE是datetime类型，还有个time,所以不能直接比

    DATEDIFF() 函数返回两个日期之间的时间。

    语法

    DATEDIFF(datepart,startdate,enddate)

    datediff（dd,RDDATE,getdate()）==0，就是要求一个以日为单位（dd），和当前日期（getdate）相差为0日的RDDATE

  2. 一个复杂的sql:

    有一个名为app的MySQL数据库表，其建表语句如下：

    create table `app`(`app_id` int(10) default 0,`version_code` int(10) default 0,`download_count` int(10) default 0)engine=InnoDB default charset=utf8mb4;

    当前表中数据记录如下，一条记录表示某个应用的某个版本的下载量记录：
    +--------+--------------+----------------+
    | app_id | version_code | download_count |
    +--------+--------------+----------------+
    | 1 | 10 | 90 |
    | 1 | 11 | 100 |
    | 1 | 10 | 20 |
    | 2 | 15 | 10 |
    | 2 | 16 | 15 |
    | 2 | 17 | 30 |
    | 2 | 16 | 5 |
    | 3 | 2 | 50 |
    +--------+--------------+----------------+

    sql语句如下，想自己试的同学可以复制试一下:

    insert into	`app` values(1,10,90);
insert into	`app` values(1,11,100);
insert into	`app` values(1,10,20);
insert into	`app` values(2,15,10);
insert into	`app` values(2,16,15);
insert into	`app` values(2,17,30);
insert into	`app` values(2,16,5);
insert into	`app` values(3,2,50);

    问： 下面那个MySQL语句可以查出每个应用中总下载量最大的版本号和次数（ ）？

    下面的方法被证明是不可行的:

    正确答案:

    select t.app_id, t.version_code, max(t.download_sum) from (select app_id, version_code, sum(download_count) download_sum from app
group by app_id, version_code order by download_sum desc) as t group by t.app_id;

    解析:先对原始数据加和(有版本号和app_id重复的项，要加在一起)，然后以app_id分类，再加一个条件是在每类中选max(download_sum),留下的tuple自然是每个应用中总下载量最大的版本号和次数

https://blog.csdn.net/l1028386804/article/details/54657412

这样的语句是非SQL标准的，虽然能够在MySQL数据库中执行成功，但返回的却是未知的
（如果sql_mode开启了only_full_group_by，则不会执行成功。）
可能ip字段会取uid分组前的第一个row的值，显然不是所需信息

原文中处理这个问题的方法也是再写一个子查询，这里也可以这样做，虽然会让语句非常复杂:

select t.app_id,t.version_code,t.download_sum from (select app_id,version_code,sum(download_count) download_sum from app group by app_id,version_code order by download_sum desc) as t  where (t.app_id,t.download_sum)in ( select t.app_id,max(t.download_sum) max from(select app_id,version_code,sum(download_count) download_sum from app group by app_id,version_code order by download_sum desc) as t group by t.app_id ) order by t.app_id;

btw,这里正好验证一下根据多个表项进行group by的效果:

select app_id,version_code,sum(download_count) download_sum from app group by app_id,version_code order by download_sum desc;

执行结果是:

+--------+--------------+--------------+
| app_id | version_code | download_sum |
+--------+--------------+--------------+
| 1 | 10 | 110 |
| 1 | 11 | 100 |
| 3 | 2 | 50 |
| 2 | 17 | 30 |
| 2 | 16 | 20 |
| 2 | 15 | 10 |
+--------+--------------+--------------+

由此可见，各个表项是等价的，就是每个特殊情况就展开一次

posted @ 2020-03-02 22:14  别再闹了  阅读(354)  评论(2)    收藏  举报