《SQL必知必会》阅读笔记

原创

Honyelchak 2023-01-03 11:46:59 博主文章分类：数据库 ©著作权

©著作权归作者所有：来自51CTO博客作者Honyelchak的原创作品，请联系作者获取转载授权，否则将追究法律责任

【自言自语】对于数据库sql语言，之前都是用的时候再查，没有系统的了解，用了一天半把这个书看完了，所以需要总结一下，不过，我没有写游标、存储过程这些，因为我觉得书中讲的过浅了。等下一本书看完，或许有更好的理解。到时候，再写关于那几个方面的我觉得是不错的。这段时间抓紧时间啃书啊，书越借越多。

SQL和Java、C不一样，他只有很少的词。他不是某个特定数据库提供商专有的语言。几乎所有重要的DBMS都支持SQL，但是有些细节地方还是要去看该DBMS的详细文档。

数据库增删改查操作

一、创建数据库

CREATE DATABASE test;

二、查看数据库

SHOW DATABASES;
查看当前数据库

SELECT DATABASE();直接可以看出数据库名称
SHOW TABLES;查看所有表操作也可以显示出数据库名称

三、切换指定数据库

USE test;

四、创建表

CREATE TABLE client( id INT, age INT, name VARCHAR(20), gender VARCHAR(1) );

五、查看表数据

DESC client;

六、增删改查

1. 增

INSERT INTO client VALUES(1,20,'zhangsan','female'); 插入所有字段
INSERT INTO client(id,name) VALUES(2,'zhansgan'); 插入部分字段

省略的列需要被定义为允许NULL值(无值或空值)
在表中给出默认值。表示如果不给出值，将使用默认值。

INSERT INTO client(id,name) SELECT id,name from oldClient;

插入检索出的语句，它可以用一条INSERT插入多行，不管SELECT返回多少行，都将被INSERT插入。

Create TABLE CustCopy AS SELECT * from Customers;

创建一个新表，将Customers表的整体内容复制到新表中。

2. 改

UPDATE client SET gender='female'; 修改所有数据（使用较少），修改范围较大。
UPDATE client SET gender='mael' WHERE id=1; 修改部分数据（使用较多），修改范围较小
UPDATE client SET gender='male' , age=30 WHERE id=2;修改多个字段，中间用英文逗号分隔。

3. 删

DELETE FROM client;删除该表的所有数据,可以带条件删除。

若执行该指令，只会删除table的所有数据，但是不能删除表的约束。例如自增长的继续增长。
使用该指令，删除的数据可以回滚。

DELETE FROM client WHERE id=2;删除带条件的数据。
TRUNCATE TABLE client;全表删除，不能带条件删除。

4. 查询数据（基于一张表）

查询出来的顺序未必是按照存放的顺序。

4.1 查询所有列

SELECT * FROM client查询所有列 *代表所有
``

4.2 查询制定列

SELECT id,name,FROM client;SELECT id, AS '编号',name AS '姓名' FROM client AS 's';查询时指定别名（as）,再多表查询时，经常使用！

4.3 查询时添加常量列

SELECT id,name ,'常量列' AS ‘常数列的表头’ FROM cilent;

4.4 查询是合并列

SELECT id,name （a2+a3） AS '新项目' FROM client;只能合并数值类型

4.5 查询时去除重复记录

SELECT DISTINCT gender FROM client;SELECT DISTINCT(gender) FROM client;

**PS：**不能部分使用DISTINCT:

该关键字作用于所有的列，不仅仅是跟在其后的那一列。
例如你指定select distinct id,name，除非指定的两列完全相同，否则所有的行都会被检索出来。

4.6 条件查询

逻辑条件 and or
SELECT * FROM client WHERE id=2 AND name='zhangsan';两个字段以上的之间一定会有逻辑关系！
比较条件 > 、<、 >=、 <=、 ==、 <>或!=(不等于) between and
SELECT * FROM client WHERE 指定项>=70 AND 指定项2<=90;SELECT * FROM client WHERE 制定项 BETWEEN 70 AND 95SELECT * FROM client WHERE gender <> 'male';寻找性别不为男的数据。
SELECT * FROM client WHERE age in (16,18)

IN:Where子句中用来指定要匹配值的清单的关键字，功能和OR相当。
IN操作符一般比一组OR操作符执行的更快。

判空（null / 空字符串） is null /is not null
NULL ：没有值
空字符串：有值。
SELECT * FROM client WHERE address IS NULL ;判断NULL
SELECT * FROM client WHERE address='';判断空字符串
SELECT * FROM client WHERE address IS NULL OR address='';包括NULL和空字符串。
SELECT * FROM client WHERE address IS NOT NULL AND address<>'';
模糊查询
只适用于字符串类型。
通常使用以下替换标记

%：表示任意字符出现任意次数(可以是0次)
_：(下划线)表示一个字符。
[]：指定一个字符集，
SELECT * FROM client WHERE name LIKE 'zhang%';SELECT * FROM client WHERE name LIKE 'zhang_' ;

4.7 聚集函数

常用的聚集函数：sum() avg() max() min() count()。

这些函数都很高效，它们返回的结果一般比你在自己的客户端应用程序中计算要快得多。

AVG()返回某列的平均值

select avg(prod_price) AS avg_price from products;
该函数忽略列值为NULL的行。

COUNT()返回某列的行数

使用count(*)对表中的行数目进行计数。(不忽略NULL值)
使用count(列名)同样也是对表中该列的行数目进行计数。(忽略NULL值)

MAX()返回某列的最大值

用于文本数据时，MAX()返回该列排序后的最后一行。
忽略值为NULL的行

MIN()返回某列的最小值

用于文本数据时，MAX()返回该列排序后的最前面的行。
忽略值为NULL的行

SUM()返回某列值之和

忽略值为NULL的行
select sum(item_price*quantity) AS total_price from OrderItems where order_num = 20005;

4.8 分页查询（limit 起始行,查询几行）

起始行从0开始
select * from client limit 0，2;

4.9 查询排序（`order by`）

4.9.1 按多个列排序

select prod_id,prod_price,prod_name from products order by prod_price,prod_name; 先按照 prod_price进行排序，然后按照prod_name进行排序。

4.9.2 按列位置排序

select prod_id,prod_price,prod_name from products order by 2,3; select清单中指定的是列的相对位置而不是列名。
order by 2.3表示先按prod_price排序，再按照prod_name进行排序。

4.9.3 指定排序方向

**PS：**如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。
asc：顺序，反序。数值：递增，字母：自然顺序。
desc：反序。

4.11分组查询（group by）

分组筛选后查询

七. 数据处理

1. 创建计算字段

拼接字段(例如：拼接两个字符串)

select province + '('+county+')' AS userAddress from address order by county;

执行算术计算

select id,count,price,count*price AS money from OrderItems;
算术操作符有四种。+、-、*、/。

2. 使用函数处理数据

由于每个DBMS都有自己的特性，所以这里就不列出每个DBMS的函数。只举例说明如何运用。

2.1 文本处理函数

UPPER()将文本转换成大写。

select vend_name,UPPER(vend_name) AS vend_name_upcase from vendors order by vend_name

RTRIM()去掉字符串右边的空格。
LTRIM()去掉字符串左边的空格。
TRIM()去掉字符串中的空格。

2.2数值处理函数

abs()返回一个数的绝对值
cos()返回一个角度的余弦
exp()返回一个数的指数值
PI()返回圆周率

2.3 日期处理函数

Year()返回日期的年份。(Mysql)

3 分组数据

3.1 创建分组

select vend_id,count(*) as num_prods from products group by vend_id;

Group By子句可以包含任意数目的列，因而可以对分组进行嵌套。
Group By子句必须出现在Where子句之后，Order By子句之前。1

3.2 过滤分组

select vend_id,count(*) AS orders from Orders Group By cust_id HAVING count(*) >=2

用Having来过滤分组，上一句SQL语句的意思是对小于两个订单的分组进行过滤。

where与having的区别：

前者是在数据分组前进行过滤，后者实在数据分组后进行过滤。
where排除的行不在这些分组中。
使用HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。

3.3 分组和排序

Group by和ORDER BY经常完成相同的工作，但他们非常不同。

前者对行进行分组，但输出可能不是分组的顺序；后者对产生的输出排序。
一般在使用GROUP BY 子句时，应该也给出ORDER BY子句，这是保证数据正确排序的唯一方法。

select order_num , count(*) AS items from orderItems group by oredr_num having count(*) >=3 order by items,oredr_num;

3.4 SELECT 子句的顺序

select(必) .....from.....where ....group by .... having .....order by

4. 子查询

sql中允许子查询，即嵌套在其他查询中的查询。

在SELECT语句中，子查询总是从内向外处理。

select cust_id from orders where order_num IN (
  select order_num from orderItems where prod_id = 'RGAN01');

select cust_id from orders where order_num IN (
  select order_num from orderItems where prod_id = 'RGAN01');

在实际的DBMS中，实际上执行了两个操作。

再比如：

select cust_name,cust_contact from customers where cust_id IN(
    select cust_id from Orders where order_num IN(
        select order_num from OrderItems where prod_id = '45274'
    )
);

select cust_name,cust_contact from customers where cust_id IN(
    select cust_id from Orders where order_num IN(
        select order_num from OrderItems where prod_id = '45274'
    )
);

上述sql代码执行了三个 select子语句。

对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

PS:

作为子查询的select语句只能查询单个列。
使用子查询并不总是执行这类数据检索的最有效的方法。

select cust_name,cust_state,(
  select count(*) from orders where orders.cust_id = Customers.cust_id) As orderNUM
) from customers order by cust_name;

select cust_name,cust_state,(
  select count(*) from orders where orders.cust_id = Customers.cust_id) As orderNUM
) from customers order by cust_name;

order是一个计算字段，她是由圆括号中的子查询建立的。该子查询对检索出的每个顾客执行一次。

cuwstomers.cust_id加.是全限定列名。

5. 联结表

关于什么是联结，请上百度搜索，百度比我讲的清楚。我只简单的点一下。

数据存储在多个表中，有些业务需要将他们联系起来，检索出来。

联结是一种机制，用来在一条SELECT语句中关联表，因此称为联结。

使用特殊的语法，可以联结多个表返回一组输出。

5.1 创建联结

简单写法：

select vend_name,prod_name,prod_price from vendors,Products where 
  vendors.vend_id = Products.vend_id;

select vend_name,prod_name,prod_price from vendors,Products where 
  vendors.vend_id = Products.vend_id;

**PS:**在引用的列名会产生歧义时，必须使用完全限定列名。

上边使用的联结方式称为等值联结，它基于两个表之间的相等测试，这种联结也成为内联结。

另外一种标准写法：

select vend_name,prod_name,prod_price from vendors INNER JOIN Products
  on vendors.vend_id = Products.vend_id;

select vend_name,prod_name,prod_price from vendors INNER JOIN Products
  on vendors.vend_id = Products.vend_id;

两个表之间的关系是以INNER JOIN指定的部分FROM子句。在使用这种语法时，联结条件用特定的ON子句而不是WHERE。

至于选用哪种方法就需要看具体的DBMS文档。

5.2 联结多个表

select prod_name,vend_name,prod_price,quantity from OrderItems,Products,Vendors Where
  Products.vend_id = Vendors.vend_id
And OrderItems.prod_id = Products.prod_id
And order_num = 20007;

select prod_name,vend_name,prod_price,quantity from OrderItems,Products,Vendors Where
  Products.vend_id = Vendors.vend_id
And OrderItems.prod_id = Products.prod_id
And order_num = 20007;

不要联结不必要的表，联结的表越多，性能下降的越厉害。

6 创建高级联结

6.1 使用表别名

使用表别名 from 表名 AS 别名表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户端。

6.2 使用不同类型的联结

自联结
通常作为外部语句哟个来替代从相同表中检索数据的子查询语句。虽然结果相同，但是使用联结要比使用子查询效率高很多。

使用子查询：select id, name,address from user where address = (select address from user where age = 15);
使用自联结：select c1.name from user as c1,user as c2 where c1.address = c2.address and c2.age = 15;

自然联结

实际上，我们建立的每个内联结都是自然联结。

外联结
有两种外联结形式，它们之间的唯一差别是所关联的表的顺序。

左外联结LEFT OUTER JOIN 包含其所有行的表在左边
右外联结RIGHT OUTER JOIN包含其所有行的表在右边

八、使用视图

1. 视图是什么？

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

他不包含任何列和数据，包含的只是一个查询。

2. 视图有什么作用？

简化复杂的SQL操作。在编写查询业务时，直接重用，不需要知道实现的细节。
保护数据(可以授权用户访问表的特定部分的权限，而不是整个表的访问权限)
更改数据格式和表示。

3. 如何创建视图？

那先说一下视图的规则和限制。(详细的还是要根据所使用的DBMS来决定)

与表一样，视图必须唯一命名。
创建视图，必须有足够的访问权限

创建视图：

CREATE VIEW ProductCustomers AS Select cust_name,cust_contact,prod_id from customers,Orders,OrderItems where customers.cust_id = Orders.cust_id and OrderItems.order_num = Orders.order_num;

CREATE VIEW ProductCustomers AS Select cust_name,cust_contact,prod_id from customers,Orders,OrderItems where customers.cust_id = Orders.cust_id and OrderItems.order_num = Orders.order_num;

这个叫ProductCustomers的视图，他联结了三个表，返回已订购了任意产品的所有顾客的列表。

比如，我们检索订购了产品id为0001的纪梵希的顾客。

select cust_name,cust_contact from ProductCustomers where prod_id = '0001';

select cust_name,cust_contact from ProductCustomers where prod_id = '0001';

实际执行上边的sql语句，视图中的select会先执行。

用视图重更新格式化检索出的数据

create view viewTest AS select RTRIM(province) + '('+RTRIM(county)+')' AS address from User;

create view viewTest AS select RTRIM(province) + '('+RTRIM(county)+')' AS address from User;

该试图将用户的省份和国籍进行格式化输出。

End

SQL基本的会用之后，学习原理就相对来说简单，更有针对性。

上一篇：PAT-1025 PAT Ranking (25 分)（结构体排序）

下一篇：【软件配置】MySQL5.6 打包版安装教程

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯