sql必知必会(第5版_部分知识点总结)

目录

1.了解SQL

2.检索数据

3.排序检索数据

4.过滤数据

5.用通配符进行过滤

6.创建计算字段

7.函数

8.汇总数据

9.分组数据

11.使用子查询

12.联结表

13.组合查询

14.插入数据

15.更新和删除数据

16.创建和操纵表

17.使用视图

18.高级SQL特性

---

1.了解SQL

数据库（database）

表（table）同一数据库表名唯一

模式（schema）

列（column）表由列组成。列存储表中某部分的信息。 字段（field）

数据类型 int,char(),varchar(),datetime...

行（row） == 数据库记录（record）

主键（primary key） 唯一，is not null

子句（clause）

NULL无值（no value） 它与字段包含0、空字符串或仅仅包含空格不同。

确定值是否为NULL，不能简单地检查是否等于NULL。

操作符（operator） 用来联结或改变WHERE子句中的子句的关键字，也称为逻辑操作符（logical operator）。

通配符（wildcard） 用来匹配值的一部分的特殊字符。

搜索模式（search pattern） 由字面值、通配符或两者组合构成的搜索条件。

MySQL - 落雪千寒 - 博客园

2.检索数据

 select 字段名,字段名,... from 表名;
 select * from 表名;

distinct(去重) 它指示数据库只返回不同的值,作用于所有的列，不仅仅是跟在其后的那一列

 select distinct 字段名 from 表名;

limit 限制输出结果 limit offset,length ---> offset代表起始页数，length代表输出行数（默认offset是0）

 select 字段名 from 表名 limit length;
 select 字段名 from 表名 limit offset,length;

3.排序检索数据

order by （默认升序排序）(asc == ascending) 位于子句最后

desc == descending（降序排序） 只应用到直接位于其前面的列名

 select * from table order by 字段名,字段名,...;
 首先按第一个字段名进行排序,只有第一个字段名的结果有多个值相同时才会按第二个字段名排序
 select * from table order by 字段名 desc;

4.过滤数据

where

5.用通配符进行过滤

百分号（%）通配符 在搜索串中，%表示任何字符出现任意次数。

下划线（_）通配符 只匹配单个字符，而不是多个字符。

6.创建计算字段

拼接字段

一、concat()函数

1、功能：将多个字符串连接成一个字符串。

2、语法：concat(str1, str2,...)

说明：返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

3、举例：select concat (id, name, score) as 别名 from 表名；

二、concat_ws()函数

1、功能：和concat()一样，但是可以指定分隔符（concat_ws就是concat with separator）

2、语法：concat_ws(separator, str1, str2, ...)

说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

3、举例：select concat ('#',id, name, score) as 别名 from 表名；

三、group_concat()函数

1、功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

2、语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator] )

说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator分隔符是一个字符串值，默认为一个逗号。

3、举例：select name,group_concat(id order by id desc separator '#') as 别名 from 表名 group by name；

四、concat_ws()和group_concat()联合使用

题目：查询以name分组的所有组的id和score

举例：select name,group_concat(concat_ws('-',id,score) order by id) as 别名 from 表名 group by name；

trim函数

1、用于返回删除字符串str两侧所有remstr字符串之后的子串，remstr默认为空格。

remstr为可选项，在未指定情况下，可删除空格。

2、返回字符串str，其中所有remstr前缀和/或后缀都已被删除。

语法

 trim([{both|leading|trailing} [removed_str]] from str)

实例

 select trim(' 123 ');
 select ltrim(' 123 ');
 select rtrim(' 123 ');
 select trim(leading 'jia' from 'jiashanlingjia');
 select trim(trailing 'jia' from 'jiashanlingjia');      

select curdate()；

使用Curdate()函数返回当前日期和时间。

7.函数

文本处理函数（常见）

数值处理函数（常见）

8.汇总数据

聚集函数（aggregate function）对某些行运行的函数，计算并返回一个值。

SQL聚集函数

 avg()
     AVG()函数忽略列值为NULL的行。
 count()
 •   如果指定列名，则COUNT()函数会忽略指定列的值为NULL的行，但如果COUNT()函数中用的是星号（＊），则不忽略。
 max(),min()
     要求指定列名,忽略列值为NULL的行。
 sum()
     忽略列值为NULL的行。

9.分组数据

group by()

 GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

WHERE过滤指定的是行而不是分组。

having

HAVING和WHERE的差别
	WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。	 WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

SELECT子句及其顺序

11.使用子查询

作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误

12.联结表

笛卡儿积（cartesian product）

由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

    select vend_name, prod_name, prod_price
    from Vendors, Products;

内联结（inner join）

    select vend_name, prod_name, prod_price
    from Vendors
    inner join Products on Vendors.vend_id = Products.vend_id;

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

自联结（self-join）、自然联结（natural join）和外联结（outer join）

有两种基本的外联结形式：左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。换句话说，调整FROM或WHERE子句中表的顺序，左外联结可以转换为右外联结。
另一种外联结，就是全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行。(mysql不支持)

13.组合查询

并（union）或复合查询（compound query）

多数SQL查询只包含从一个或多个表中返回数据的单条SELECT语句。但是，SQL也允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回。

UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
使用UNION ALL,DBMS不取消重复的行。
在用UNION组合查询时，只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句之后。

14.插入数据

insert

1.插入完整的行

insert into 表名 列名 值;
insert into 表名 值;

2.插入行的一部分

如果表的定义允许，则可以在INSERT操作中省略某些列。
省略的列必须满足以下某个条件。
❑ 该列定义为允许NULL值（无值或空值）。
❑ 在表定义中给出默认值。这表示如果不给出值，将使用默认值。（default）	

3.插入某些查询的结果

insert into 表名 列名 select 表名 from 列名;
前后列名不必相同（数据类型需要相同）

从一个表复制到另一个表

create table customers_copy as select * from customers;
* 可以换成 customers 中的一部分列名

15.更新和删除数据

update

update 表名 set 更新内容,更新内容,... (where 条件);

（空字符串用’’表示，是一个值），而NULL表示没有值。

delete

delete from 表名 (where 条件);

truncate

truncate table;
删除整个表中所有的行比 delete 更快

16.创建和操纵表

创建表

drop table if exists 表名;
create table 表名 (列名(唯一) 类型,列名 类型,...,列名 类型,列名 类型);

不要把NULL值与空字符串相混淆。
NULL值是没有值，不是空字符串。
如果指定’'（两个单引号，其间没有字符），这在NOT NULL列中是允许的。
空字符串是一个有效的值，它不是无值。
NULL值用关键字NULL而不是空字符串指定。

指定默认值

default

更新表

    alter table Vendors
    add vend_name char(20);

删除表

drop table 表名;
永久删除整个表

重命名表

alter table 旧表名 rename [to/as] 新表名;
rename table 新表名 to 旧表名;

17.使用视图

视图为虚拟的表。

它们包含的不是数据而是根据需要检索数据的查询。

视图提供了一种封装SELECT语句的层次，可用来简化数据处理，重新格式化或保护基础数据。

视图内部不能使用order by子句。

CREATE VIEW CustomersWithOrders AS
SELECT Customers.cust_id,
 Customers.cust_name,
 Customers.cust_address,
 Customers.cust_city,
 Customers.cust_state,
 Customers.cust_zip,
 Customers.cust_country,
 Customers.cust_contact,
 Customers.cust_email
FROM Customers
JOIN Orders ON Customers.cust_id = Orders.cust_id;

SELECT * FROM CustomersWithOrders; 

18.高级SQL特性

约束

主键：唯一且非空

primary key

外键

唯一约束：数据唯一

unique

检查约束：用来保证一列（或一组列）中的数据满足一组指定的条件

check

索引

create index 索引名 on 表名(列名);