数据库基础02-基本SQL查询语言

数据库基础02-基本SQL查询语言

使用MYSQL作为示范

安装过程感谢菜鸟教程

Ps:在上一篇笔记中,发现MarkDown自带的表格实在是不适合放这种数据表,因此用代码块代替

文章目录

数据库及表的创建

开始创建数据库

mysql> CREATE DATABASE CAP;                                                         
Query OK, 1 row affected (0.01 sec)

现在我们创建了一个叫做CAP的数据库

选中该数据库

mysql> use CAP;                                                                       
Database changed 

接着,创建数据库中的四张表customers, agents, orders, products(关于CAP数据库中各表的含义见上一篇笔记

mysql> CREATE TABLE customers(                                                         
    -> cid CHAR(4) NOT NULL,                                                           
    -> cname VARCHAR(13),                                                             
    -> city VARCHAR(20),                                                               
    -> discnt FLOAT,                                                                   
    -> PRIMARY KEY(cid)                                                               
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;                                           
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> CREATE TABLE agents(                                                           
    -> aid CHAR(3) NOT NULL,                                                           
    -> aname VARCHAR(13),                                                             
    -> city VARCHAR(20),                                                               
    -> percent SMALLINT,                                                               
    -> PRIMARY KEY(aid) 
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;    
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> CREATE TABLE products(                                                         
    -> pid CHAR(3) NOT NULL,                                                           
    -> pname VARCHAR(13),                                                             
    -> city VARCHAR(20),                                                               
    -> quantity INTEGER,                                                               
    -> price DOUBLE,                                                                   
    -> PRIMARY KEY(pid)                                                               
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;                                           
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> CREATE TABLE orders(                                                           
    -> ordno INT NOT NULL,                                                             
    -> month CHAR(3),                                                                 
    -> cid CHAR(4),                                                                   
    -> aid CHAR(3),                                                                   
    -> pid CHAR(3),                                                                   
    -> qty INT,                                                                       
    -> dollars DOUBLE,                                                                 
    -> PRIMARY KEY(ordno)                                                             
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;                                           
Query OK, 0 rows affected, 1 warning (0.04 sec)

建表语句中,以建立customers表的语句为例

cid为表中第一列的列名

NOT NULL表示cid列不允许是空值

*CHAR(4)*表示cid列的值必须为4个字符的定长字符串

VARCHAR(13)表示cname列的值必须为13个字符以内的可变长字符串

FLOAT表示discnt列的值为单精度浮点数

*PRIMARY KEY(cid)*表示customers表的主键为cid(即该表中的各行以cid值为唯一标识

最后一行的ENGINE=InnoDB DEFAULT CHARSET=utf8则是在规定存储引擎和编码~~(不看也行)~~

现在,我们的CAP数据库里已经有了四张空表

mysql> SHOW TABLES;                                                                   
+---------------+                                                                     
| Tables_in_cap |                                                                     
+---------------+                                                                     
| agents        |                                                                     
| customers     |                                                                     
| orders        |                                                                     
| products      |                                                                     
+---------------+                                                                     
4 rows in set (0.01 sec)

接下来,我们向表中插入数据

mysql> INSERT INTO customers                                                           
	-> (cid, cname, city, discnt)                                                     
	-> VALUES                                                                         
	-> ("c001", "TipTop", "Duluth", 10.00);                                           
Query OK, 1 row affected (0.02 sec)

如此,便向customers表中插入了一条数据

(关于如何修改或是删除行==>W3school

现在的customers表内容为

mysql> select * from customers;                                                       
+------+--------+--------+--------+                                                   
| cid  | cname  | city   | discnt |                                                   
+------+--------+--------+--------+                                                   
| c001 | TipTop | Duluth |     10 |                                                   
+------+--------+--------+--------+                                                   
1 row in set (0.00 sec)     

实际上,如果完整插入一条数据,可以直接 INSERT INTO 表名称 VALUES (值1, 值2,…)

若干要向某些特定的列插入数据,则应使用 INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)

显而易见地,这样子插入数据显得非常麻烦,即使是我们只有几十行的示范数据

因此,对于剩下的数据,我们直接从本地csv文件导入数据库~~(命令还是一如既往地易读)~~

mysql> load data local infile "C:/Users/mi/Desktop/customers.csv" into table customers fields terminated by ',';        
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

第一次尝试导入失败,原因是本地文件加载被限制

mysql> set global local_infile = 1;                                                   
Query OK, 0 rows affected (0.01 sec)

解除本地文件加载限制

mysql> load data local infile "C:/Users/mi/Desktop/customers.csv" into table customers fields terminated by ',';        
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access. 

第二次尝试导入失败,原因未知~~(懒得找)~~

经过面向搜索引擎编程,得知需要加上*–local-infile=1*参数重新登入

mysql -u root -p --local-infile=1 

重新登入后,继续第三次尝试

mysql> use CAP;                                                                       
Database changed                                                                       
mysql> load data local infile "C:/Users/mi/Desktop/customers.csv" into table customers fields terminated by ',';        
Query OK, 4 rows affected, 3 warnings (0.01 sec)                                       
Records: 4  Deleted: 0  Skipped: 0  Warnings: 3 

(记得先选中CAP数据库)

现在查看customers表

mysql> select * from customers;                                                       
+------+--------+--------+--------+                                                   
| cid  | cname  | city   | discnt |                                                   
+------+--------+--------+--------+                                                   
| c001 | TipTop | Duluth |     10 |                                                   
| c002 | Basics | Dallas |     12 |                                                   
| c003 | Allied | Dallas |      8 |                                                   
| c004 | ACME   | Duluth |      8 |                                                   
| c006 | ACME   | Kyoto  |      0 |                                                   
+------+--------+--------+--------+                                                   
5 rows in set (0.00 sec)

导入成功,接下来如法炮制,导入其他三张表如下

agents表:

mysql> select * from agents;                                                           
+-----+-------+----------+---------+                                                   
| aid | aname | city     | percent |                                                   
+-----+-------+----------+---------+                                                   
| a01 | Smith | New York |       6 |                                                   
| a02 | Jones | Newark   |       6 |                                                   
| a03 | Brown | Tokyo    |       7 |                                                   
| a04 | Gray  | New York |       6 |                                                   
| a05 | Otasi | Duluth   |       5 |                                                   
| a06 | Smith | Dallas   |       5 |                                                   
+-----+-------+----------+---------+                                                   
6 rows in set (0.00 sec)  

products表:

mysql> select * from products;                                                         
+-----+--------+---------+----------+-------+                                         
| pid | pname  | city    | quantity | price |                                         
+-----+--------+---------+----------+-------+                                         
| p01 | comb   | Dallas  |   111400 |   0.5 |                                         
| p02 | brush  | Newark  |   203000 |   0.5 |                                         
| p03 | razor  | Duluth  |   150600 |     1 |                                         
| p04 | pen    | Duluth  |   125300 |     1 |                                         
| p05 | pencil | Dallas  |   221400 |     1 |                                         
| p06 | folder | Dallas  |   123100 |     2 |                                         
| p07 | case   | Netwark |   100500 |     1 |                                         
+-----+--------+---------+----------+-------+                                         
7 rows in set (0.00 sec) 

orders表:

mysql> select * from orders;                                                           
+-------+-------+------+------+------+------+---------+                               
| ordno | month | cid  | aid  | pid  | qty  | dollars |                               
+-------+-------+------+------+------+------+---------+                               
|  1011 | jan   | c001 | a01  | p01  | 1000 |     450 |                               
|  1012 | jan   | c001 | a01  | p01  | 1000 |     450 |                               
|  1013 | jan   | c002 | a03  | p03  | 1000 |     880 |                               
|  1014 | jan   | c003 | a03  | p05  | 1200 |    1104 |                               
|  1015 | jan   | c003 | a03  | p05  | 1200 |    1104 |                               
|  1016 | jan   | c006 | a01  | p01  | 1000 |     500 |                               
|  1017 | feb   | c001 | a06  | p03  |  600 |     540 |                               
|  1018 | feb   | c001 | a03  | p04  |  600 |     540 |                               
|  1019 | feb   | c001 | a02  | p02  |  400 |     180 |                               
|  1020 | feb   | c006 | a03  | p07  |  600 |     600 |                               
|  1021 | feb   | c004 | a06  | p01  | 1000 |     460 |                               
|  1022 | mar   | c001 | a05  | p06  |  400 |     720 |                               
|  1023 | mar   | c001 | a04  | p05  |  500 |     450 |                               
|  1024 | mar   | c006 | a06  | p01  |  800 |     400 |                               
|  1025 | apr   | c001 | a05  | p07  |  800 |     720 |                               
|  1026 | may   | c002 | a05  | p03  |  800 |     704 |                               
+-------+-------+------+------+------+------+---------+                               
16 rows in set (0.00 sec)

在导入该表时,发现数据库会按照主键从小到大自动排列(orders表原本是杂乱排列的)

现在我们已经基本了解了MYSQL的增删改,下面则是重点——如何查询到数据库中指定内容

数据查询

select

select是最基本的查询语句,可以查询所需求的一列或者多列

mysql> select aid from agents;                                                         
+-----+                                                                               
| aid |                                                                               
+-----+                                                                               
| a01 |                                                                               
| a02 |                                                                               
| a03 |                                                                               
| a04 |                                                                               
| a05 |                                                                               
| a06 |                                                                               
+-----+                                                                               
6 rows in set (0.00 sec)                                                                                                                                                     
mysql> select aid, city from agents;                                                   
+-----+----------+                                                                     
| aid | city     |                                                                     
+-----+----------+                                                                     
| a01 | New York |                                                                     
| a02 | Newark   |                                                                     
| a03 | Tokyo    |                                                                     
| a04 | New York |                                                                     
| a05 | Duluth   |                                                                     
| a06 | Dallas   |                                                                     
+-----+----------+                                                                     
6 rows in set (0.00 sec)

select查询默认为打印所有满足条件的行,无论是否重复,如果想要获得不重复的结果,可以使用distinct

mysql> select city from agents;
+----------+                    
| city     |                       
+----------+                     
| New York | 
| Newark   |  
| Tokyo    |   
| New York |    
| Duluth   |    
| Dallas   |    
+----------+    
6 rows in set (0.00 sec)   

mysql> select distinct city from agents;   
+----------+                              
| city     |      
+----------+           
| New York |             
| Newark   |            
| Tokyo    |           
| Duluth   |           
| Dallas   |           
+----------+            
5 rows in set (0.01 sec)  

注意:以select aid, city from agents;为例,加上distinct后我们保证的是(aid, city)这一对值的唯一性,即distinct保证每一行的唯一性,所以(a05, Duluth), (a06,Dallas)可以共存

如果想要查询所有的列,即查询整张表,可以使用 *

mysql> select * from agents;                                                           
+-----+-------+----------+---------+                                                   
| aid | aname | city     | percent |                                                   
+-----+-------+----------+---------+                                                   
| a01 | Smith | New York |       6 |                                                   
| a02 | Jones | Newark   |       6 |                                                   
| a03 | Brown | Tokyo    |       7 |                                                   
| a04 | Gray  | New York |       6 |                                                   
| a05 | Otasi | Duluth   |       5 |                                                   
| a06 | Smith | Dallas   |       5 |                                                   
+-----+-------+----------+---------+                                                   
6 rows in set (0.00 sec)  

where

where是最基本的对查询附加条件的方式

mysql> select aid from agents where city = 'New York';     
+-----+                                    
| aid |                                    
+-----+                                      
| a01 |                                      
| a04 |                                       
+-----+                                           
2 rows in set (0.00 sec)   

这个查询语句的含义是:从agents表中查询city为New York的行,然后将满足条件的行的aid值打印出来

from后面所跟的表可以不止一个

-- 找出至少被两个顾客订购的产品的pid
-- 注:这是mysql中注释的存在形式
mysql> select distinct x1.pid from orders x1, orders x2 where x1.pid = x2.pid and x1.cid < x2.cid;   
+------+           
| pid  |            
+------+             
| p03  |              
| p05  |               
| p01  |               
| p07  |               
+------+          
4 rows in set (0.00 sec)

from orders x1, orders x2:将两份orders表分别命名为x1, x2,然后在x1 × x2(x1与x2作笛卡尔积)形成的新表中进行查询

x1.pid:由于查询的表中有属于x1的pid,也有属于x2的pid,因此此处要指明是哪个

where x1.pid = x2.pid and x1.cid < x2.cid:where后面跟的查询条件显然也是可以由多个条件叠加的

in

in谓词是子查询的基本方法

-- 求出通过住在Duluth或Dallas的代理商订了货的顾客的cid
-- 首先,我们找出 住在Duluth或Dallas的代理商
mysql> select aid from agents where city = 'Duluth' or city = 'Dallas';     
+-----+                                              
| aid |                                             
+-----+                                             
| a05 |                                             
| a06 |                                             
+-----+                                            
2 rows in set (0.00 sec)
-- 注:很显然,每个select语句的结果都是一张新表,而同时我们知道,select查询语句中from后面的内容也是一张表
-- 因此,我们有了一个大胆的想法,我们或许可以尝试进行select查询的嵌套
-- 反正都是表,凭什么有名字的表能放在from后面,select出的就不行?
-- 王侯将相宁有种乎!(划掉)
-- 因此我们有了这样的查询
mysql> select distinct cid from orders where aid in (select aid from agents where city = 'Duluth' or city = 'Dallas'); 
+------+     
| cid  |               
+------+                 
| c001 |                
| c004 |                 
| c006 |                
| c002 |                 
+------+                 
4 rows in set (0.00 sec) 

这里的in就表示嘤语中”在“的意思,而in后面跟的那个select查询就是子查询 (很容易理解应该)

由于我们不知道aid应该=什么,所以只能使用in

当然,in后面跟的也可以是一个已知的集合

select * from agents where city in ('Duluth', 'Dallas');也是可以的,而这句查询就等价于select * from agents where city = 'Duluth' or city = 'Dallas';

而in前面的也可以是多个值组成的序列(也许叫序列?)

如要检索由住在Duluth的顾客和住在New York的代理商组成的所有订货记录的ordno值select ordno from orders where (cid, aid) in (select cid, aid from customers c, agents a where c.city = 'Duluth' and a.city = 'New York');

显然,子查询可以多层嵌套,而在子查询的嵌套中,就产生了三种情况:

  1. 子查询是独立的,没有使用任何外层信息,如select distinct cid from orders where aid in (select aid from agents where city = 'Duluth' or city = 'Dallas');这里面的子查询只从自带的agents表中获取信息组成新表,这种子查询被称为是非相关子查询
  2. 子查询使用了外层的数据,如:找出订购了产品p05的顾客的名字,一种复杂写法为select distinct cname from customers where 'p05' in (select pid from orders where cid = customers.cid);这里面的子查询就使用了外层的customers表,这种子查询被称为相关子查询
  3. 外层尝试使用内层的数据,这种行为是被禁止的(有点函数作用域的感觉)

此外,既然有in谓词,自然也有not in谓词,它的意思也很显然,此处就不赘述了

all

all表示所有,见栗子↓

-- 找出佣金百分率最少的代理商的aid
mysql> select aid from agents where percent <= all (select percent from agents); 
+-----+           
| aid |            
+-----+             
| a05 |            
| a06 |          
+-----+           
2 rows in set (0.00 sec)

含义很显然

some

some表示“某些”“部分”

-- 找出与住在Dallas或Boston的顾客拥有相同折扣的所有顾客
mysql> select cid, cname from customers where discnt = some (select discnt from customers where city = 'Dallas' or city = 'Boston');   
+------+--------+                               
| cid  | cname  |                         
+------+--------+                      
| c002 | Basics |                  
| c003 | Allied |                    
| c004 | ACME   |                   
+------+--------+             
3 rows in set (0.00 sec) 

这里可以看出,谓词*=some和谓词in*是等价的

exists

exists表示“存在”,它的正向形式如下

-- 求出既订购了p01又订购了p07的顾客的cid
select distinct cid from orders x where pid = 'p01' and exists (select * from orders where cid = x.cid and pid = 'p07');
-- 而这个查询不使用exists也能完成(而且更简单)
select distinct x.cid from orders x, orders y where x.pid = 'p01' and x.cid = y.cid and y.pid = 'p07';

exists的正向形式不是查询所必须的,所以我们一般不使用

下面考虑not exists

-- 检索没有通过代理商a05订货的所有顾客的名字
-- 使用not exists
select distinct c.cname from customers c where not exists (select * from orders x where c.cid = x.cid and x.aid = 'a05');
-- 使用not in
select distinct c.cname from customers c where c.cid not in (select cid from orders where aid - 'a05');
-- 使用<>all
select distinct c.cname from customers c where c.cid <>all (select cid from orders where aid - 'a05');
-- 这三者是等价的(看上去not exists还是莫得什么太大用处...)

而这个检索可以等价为:所有顾客的名字 - 通过代理商a05订货的所有顾客的名字

not exists的用处就在于此——它可以实现兼容表之间的减操作

-- 表R,S兼容,Head(R) = Head(S) = A1A2...An
-- 则R-S就可以表示为
select A1, A2...., An from R where not exists (select * from S where S.A1 = R.A1 and ... and S.An = R.An);

union

union代表的是并操作

mysql> select city from customers union select city from agents;     
+----------+                
| city     |              
+----------+             
| Duluth   |             
| Dallas   |             
| Kyoto    |           
| New York |            
| Newark   |          
| Tokyo    |          
+----------+           
6 rows in set (0.01 sec) 

而union all则不会去除重复的行,也就是说它会在执行并操作的过程中,完整保留其后的表(即直接把union all前面的表和后面的表堆在一起)

mysql> select city from  customers union all select city from agents;   
+----------+                              
| city     |                               
+----------+                   
| Duluth   |              
| Dallas   |            
| Dallas   |         
| Duluth   |        
| Kyoto    |        
| New York |        
| Newark   |       
| Tokyo    |      
| New York |     
| Duluth   |     
| Dallas   |     
+----------+     
11 rows in set (0.00 sec)    

此外,union显然是可以嵌套的,优先级由括号决定

除法

令人悲痛的是,我们没有专用的除法谓词,因此,要实现除法,只能通过一系列元素的拼凑

数据库基础02-基本SQL查询语言

现在考虑如下检索:找出通过住在New York的所有代理商订了货的顾客的cid

关系代数表达(ORDERS[cid, aid] ÷ (AGENTS where city = ‘New York’)[aid]

我们假设这个订了货的顾客的cid为c.cid

①那么先找到它的反例,即找到一个代理商的aid,我们称为a.aid,而c.cid对应的顾客没有在这个代理商处订货,我们将其命名为cod1

cond1: select * from agents a where a.city = 'New York' and not exists (select * from orders o where o.cid = c.cid and o.aid = a.aid)

②现在,我们需要让这个反例不存在,即not exists cond1

③接着,我们需要找出让这个反例不存在的cid

select c.cid from customers c where not exists cod1;

写成完整版就是

select c.cid from customers c where not exists (
select * from agents a where a.city = 'New York' and not exists (
select * from orders o where o.cid = c.cid and o.aid = a.aid));

梅开二度:

-- 求出住在New York或Duluth并订购了价格超过一美元的所有产品的代理商的aid
-- 假设这个代理商为a.aid
-- 假设有一个没有被该代理商订购的产品p.pid,则反例为
-- cond1:select p.pid from products p where p.price > 1.0 and not exists (select * from orders o where o.pid = p.pid and o.aid = a.aid)
-- 反例不存在 not exists cond1
-- 最终的查询 select a.aid from agents a where (a.city = 'New York' or a.city = 'Duluth') and not exists cond1;
-- 完整版为
select a.aid from agents a where (a.city = 'New York' or a.city = 'Duluth') and not exists (
select p.pid from products p where p.price > 1.0 and not exists (
select * from orders o where o.pid = p.pid and o.aid = a.aid));

注:在我们的假设中,比如我们将第二题中的代理商假设为a.aid,但实际上更全面的选择是假设为?.aid以允许包含除了agents外的其他表

高级SQL

前面介绍的mysql实现的操作已经是完备的了,但为了使操作更加简便鬼畜,我们还有更高级的sql语法,但是一个很严重的问题是,这些语法在mysql中都没有实现因此懒得写了

//todo

集合函数

sum

mysql> select sum(qty) as TOTAL from orders where pid = 'p01';          
+-------+                    
| TOTAL |                
+-------+         
|  4800 |          
+-------+          
1 row in set (0.00 sec)    
-- 这句查询做的事情是:找到orders中所有pid为p01的行,然后将它们的qty值相加,最后将和放在一个名字叫做TOTAL的只有一行的新表中
-- 注:如果没有 as TOTAL 的话,生成的新表的名字就会叫做 sum(qty)

count

mysql> select count(cid) as COUNT from customers;   
+-------+                                       
| COUNT |                                       
+-------+                                     
|     5 |                                    
+-------+                                       
1 row in set (0.00 sec) 
-- 表示查询cid出现的值的数目(会忽略空值(虽然说实际上cid也不会是空值))

mysql> select count(distinct city) from customers;    
+----------------------+                             
| count(distinct city) |   
+----------------------+  
|                    3 |  
+----------------------+  
1 row in set (0.01 sec)   
mysql> select count(distinct city) as COUNT from customers;     
+-------+                    
| COUNT |                    
+-------+               
|     3 |              
+-------+          
1 row in set (0.00 sec)    
-- 表示查询不同城市的数目,很显然这里需要distinct来去除重复的城市(顺便演示了下as的用处)

max, min

mysql> select max(qty) from orders;     
+----------+                       
| max(qty) | 
+----------+
|     1200 | 
+----------+  
1 row in set (0.00 sec)   

mysql> select min(qty) from orders;   
+----------+                        
| min(qty) |   
+----------+  
|      400 |  
+----------+  
1 row in set (0.00 sec)

-- 一目了然,没啥好说的

avg

求平均值

mysql> select avg(qty) from orders; 
+----------+   
| avg(qty) | 
+----------+ 
| 806.2500 |  
+----------+  
1 row in set (0.00 sec)     

关于它们的用法,有需要注意的地方

select cid from customers where discnt < max(discnt);显然是错的

正确的写法应该是

select cid from customers where discnt < (select max(discnt) from customers);

一个集合函数不能出现在where中,除非它在一个子查询的选择列表中

举个其他函数的使用栗子

找出被至少2个顾客订购的所有产品

select p.pid from products p where 2 <= (select count(distinct cid) from orders where pid = p.pid);

这就比之前我们使用的方法要明了很多

关于空值

在插入数据时,缺省值为NULL

空值会导致用于选择的where语句在该行的值不是True也不是False而是Unknown,如果遇到这种情况,则该行无法被查询到(假设有一行的qty为NULL,则where qty > 10 or qty <= 10无法查询到它)

不是很严谨,将就着理解吧↑

集合函数会自动跳过空值

未完待续 2021.4.6

上一篇:数据库查询题目1-10


下一篇:sql常见题型