SQL 基础(一)

之前学过SQL，但是前因后果，语句的扩展等还不是很熟悉。于是总结这篇文章以巩固，还有一些知识点尚待后续补充。博文的目录是这样的，浏览的小伙伴先喵喵目录，感兴趣的话再往下翻。

 

一、数据库和SQL：

1. SQL语句分类：

DDL： 创建或者删除存储数据用的数据以及数据库中的表对象等

CREATE:创建数据库和表等对象

DROP :删除数据库和表等对象

ALTER:修改数据库和表等对象

DML（通常使用最多）: 用来查询或者变更表中的记录

SELECT:查询表中的数据

INSERT:向表中插入数据

UPDATE:变更表的数据

DELETE:删除表中的数据

DCL：确认或取消对数据库中的数据进行的变更，还可以设置用户是否有权限操作数据库中的对象

COMMIT: 确认对数据库中的数据进行的变更

ROLLBACK: 取消对数据库中的数据进行的变更

GRANT: 赋予用户操作权限

REMOVE: 取消用户的操作权限

 

2.  SQL 的基本书写规则：

1）SQL语句以分号结尾

2）关键字不区分大小写

3）字符串和日期常数要用''括起来。数字不用单引号

4）单词之间需要使用半角空格或者换行进行分割

 

3.  表的创建和操作：

数据库的创建：

CREATE DATABASE <数据库名>;

表的创建：

CREATE TABLE <表名>(

<列名1> <数据类型> <该列所需约束>,

<列名2> <数据类型> <该列所需约束>,

<列名3> <数据类型> <该列所需约束>

);

命名规则：

 1.数据库名称、表名、和列名等可以使用以下三种字符：

半角英文、半角数字、和下划线（_）

 2.名称必须以半角英文字母开头

 3. 名称不能重复（列名和字段名）

 

数据类型的指定：

INTEGER:指定用来存储整数的列 不能存储小数

CHAR:用来存储字符串类型

VARCHAR： 用来存储字符串型。但长度可以变 oracle中不推荐使用

DATE:用来存储日期（年月日）的日期型数据 oracle中还包含时分秒

NOT NULL 不能为空

PRAMARY KEY(id) 列设定的主键约束

 

表的删除和更新：

表的删除：DROP TABLE <表名>;  删除的表是无法恢复的

表定义的更新：ALTER TABLE <表名>

 

添加列的ALTER TABLE:

ALTER TABLE <表名>  ADD COLUMN <列的定义>

在oracle和SQL server 中不用写COLUMN :ALTER TABLE <表名> ADD <列名>

另外在oracle添加多列的时候可以如下：ALTER TABLE <表名> ADD （<列名>,<列名>...）;

删除列的ALTER TABLE 语句：

ALTER TABLE <表名> DROP COLUMN <列名>;

 

在oracle和SQL server 中不用写COLUMN:ALTER TABLE <表名> DROP <列名>;

另外在oracle删除列时：ALTER TABLE <表名> DROP（<列名>,<列名>...）;

 

二、SELECT 语句基础：

1. 列的查询：

SELECT <列名> FROM <表名>;

 

查询表中所有的列的数据：

SELECT * FROM <表名>;

使用*号查询就无法设定列的显示顺序，按照创建表的时的顺序结果显示

 

为列名设定别名：

SELECT id as id,name as name FROM student;

别名可以使用汉语，要使用""括起来

 

从查询结果中删除重复行：

SELECT DISTINCT <列名> FROM <表名>;

注意：在使用DISTINCT 时，NULL值也被视为一类数据（即有多行的数据为NULL 只会保存一行）

将DICTINCT放在所有列前面表示将多个列的数据进行组合，将重复的数据结合为一条，例如：

SELECT DISTINCT id,name FROM student 表示id+name 都相同的才算作重复

根据WHERE语句来选择记录：

首先通过where子句查询出符合指定条件的 再选取SELECT 语句指定的列

SELECT id,name FROM student WHERE id ='1';

2、注释的书写方法：

单行注释：'--' 只能写在同一行

多行注释：'/* */'

 

3、算术运算符和比较运算符：

算术运算符： + - * /

select count_*5 from student;

需要注意NULL：

一个数字和NULL做任何运算，结果都为NULL

比较运算符：

等于：=

不等于：<>

大于等于：>=

大于：>

小于等于：<=

小于：<

 对字符串使用不等号时的注意事项：

'2' >'11' 因为字符串类型的数据原则是按照字典排序的，不能以数字的大小顺序混淆

不能对NULL使用比较运算符：

查询某个字段为NULL： IS NULL ，反之，IS NOT NULL

4、逻辑运算符：

NOT运算符：

select id,name from student where id >10;可以改写为：

select id,name from student where not id <=10;

AND 运算符和OR运算符：

AND在其两侧的条件查询都成立时整个查询条件才成立

OR 在其两侧的查询条件一个成立时整个查询条件就成立

 

为了得到预期的结果应使用括号进行强化

三、聚合和排序：

1、对表进行聚合查询：

聚合函数：将多行汇总为一行

COUNT:COUNT函数的结果根据参数的不同而不同，count(*) 会得到包含NULL的数据行数，而COUNT(<列名>) 会得到NULL之外的数据行数

SUM: 即使包含NULL也可以计算出合计值，因为以列名为参数，在计算之前就 将NULL值排除在外了。

AVG: 与SUM函数相同，会事先删除NULL再进行计算：

AVG(cou) 假如cou字段有NULL的，平均值的除数不算作在内  加上NULL 有5条 为NULL的有1条 那么平均值应为（SUM（cou）/4）

MAX:与SUM函数相同，会事先删除NULL再进行计算

MIN:与SUM函数相同，会事先删除NULL再进行计算

SUM、AVG 只能对数值类型的列使用。而MAX/MIN 函数原则上可以适用于任何数据类型的列。如果能排序的就有最值。

使用聚合函数删除重复值（关键字DISTINCT） 计算去除重复数据后的数据行数：

SELECT COUNT(DISTINCT code) FROM student;

2、对表进行分组：

使用聚合函数和GROUP BY子句时，需要注意的点：

只能写在SELECT子句中

GROUP BY子句中不能使用SELECT子句中列的别名

GROUP BY子句中的聚合结果是无序

WHERE 子句中不能使用聚合函数

使用GROUP BY子句进行聚合：

SELECT <列名1>,<列名2>,<列名3>

FROM <表名>

WHERE <条件语句>

GROUP BY  <列名1>,<列名2>,<列名3>;

聚合键中包含NULL的情况，结果会以"不确定" 行（空行）的形式表现出来

 

使用WHERE子句时，GROUP BY的执行结果

先根据WHERE子句对记录进行过滤，再进行聚合处理

GROUP BY和WHERE共用时，SELECT语句的执行顺序：

FROM -> WHERE -> GROUP BY -> SELECT

与聚合函数和GROUP BY子句有关的常见错误：

1）把聚合键之外的列名书写在SELECT子句之中：

select name,type,count(*) from student group by type;

-- 其中name列必须包含在GROUPBY 子句中,但是名称和聚合键不一定是一对一

2）在GROUP BY子句中写了列的别名：

select type as t,count(*) from student group by t;

3）在GROUP BY 子句中的结果是无序的

4）在WHERE 子句中使用聚合函数

实际上，只能在SELECT，HAVING，ORDER BY 中能够使用COUNT聚合函数

DISTINCT 和GROUP BY 能够实现相同的功能：

SELECT DISTINCT type from student;  <=> SELECT type from student group by type ;

 

3、为聚合结果指定条件：

使用COUNT函数等对表中数据进行聚合操作时，为其指定条件的不是WHERE语句，而是HAVING子句

HAVING子句要写在GROUP BY子句之后

WHERE子句用来指定数据行的条件，HAVING子句用来指定分组的条件

 

WHERE用来指定行的条件，

HAVING用来指定组的条件

使用HAVING子句时SELECT语句的顺序：

SELECT -> FROM -> WHERE -> GROUP BY -> HAVING

例如：

select type,AVG(cou) from student group by type having AVG(cou)>90;

HAVING子句的构成要素：

常数，聚合函数，GROUP BY子句中指定的列名（聚合键）

在思考HAVING子句的使用方法时，把GROUP BY的结果列出来，作为HAVING子句的起始点。

相对于HAVING子句，更适合写在WHERE子句中的条件：

有些条件既可以放在HAVING中也可以放在WHERE子句中，这些条件就是聚合键所对应的条件。

select type,count(*) from student group by type having type='1';

select type,count(*) from student where type='1' group by type; 即使如此，"行"对应的条件还是应该在WHERE子句中。

归结为以下：

WHERE子句 = 指定行所对应的条件

HAVING子句 = 指定组所对应的条件

 

条件写在WHERE子句中要比写在HAVING子句中快，返回时间所需更短。

只有尽可能减少排序的行数，才能增加处理速度。

通过WHERE子句中指定条件时，由于排序之前就对数据进行了过滤，所以能够减少排序的数据量。但HAVING子句是在排序后才对数据进行分组。

也是因为可以对WHERE子句指定条件所对应的列创建索引。

4、对查询结果进行排序

ORDER BY 子句：

select <列名1>,<列名2>

from <表名>

ORDER BY <排序基准列1>,<排序基准列2>

 

使用HAVING子句时，SELECT语句的顺序：

SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY

 

指定升序和降序：

ASC (ascendent 上升的)DESC(descendent 下降的)

指定多个排序键：

select id, name, score

from student

order by score,name

表示如果score有相同值再按照name排序

排序时注意NULL值的顺序，会在最开头或者末尾，顺序不固定

在排序键中使用显示用列名的别名：

SELECT子句执行的顺序在GROUP BY子句后，在ORDER BY之前，因此在执行GROUP BY子句的时候 SELECT子句定义的别名无法被识别。

ORDER BY子句中可以使用的列：

select id,name

from student

order by age asc

四、数据更新

1、数据插入

INSERT 语句的基本语法：

insert into <表名> (列名1,列名2,列名3) values(值1,值2,值3);  -- 列名和值数量和类型要一致。

可以单行插入也可以一次性插入多行。但是多行插入不适用于Oracle

Oracle的多行插入：

insert all into student values('001','zhangsan',25)

into student values('002','lisi',27)

select * from dual;  -- dual 是Oracle安装时的一种临时表

插入NULL：直接将插入的值赋予NULL

但是要插入的NULL列一定不能设置NOT NULL约束。

插入默认值：使用default 关键字。如：default 0;

 

2、 数据删除

DROP TABLE 语句和DELETE语句：

DROP TABLE是将表完全删除

DELET语句是留下表，删除表中的全部数据

 

DELETE语句的基本语法：

DELETE FROM <表名>;  --这样是删除了表中所有的数据，但是表还是存在

指定删除对象的DELETE语句：

DELETE FROM <表名>

WHERE <条件>;

 

DRUNCATE 只能删除表中所有的语句，并且不能接where条件语句

DELETE FROM <表名>(可接WHERE) <——> TRUNCATE FROM  <表名>(不可接WHERE);

 

3、数据的更新

UPDATE 语法：

UPDATE <表名> SET <列名> = <表达式>;

 

指定条件的UPDATE 语句：

UPDATE <表名> SET <列名> = <表达式>

WHERE <条件>;

4、事务

事务就是在同一个处理单元中执行的一系列更新处理的集合

创建事务：

事务开始语句：

业务SQL语句1;

业务SQL语句2;

事务结束语句（COMMIT 或者 ROLLBACK）

事务的开始语句和结束和用的数据库有关：

SQL Server,PostgreSQL 用的开启事务语句是：BEGIN TRANSACTION

MySQL 用的是START TRANSACTION ,事务结束语句都是COMMIT

Oracle ,DB2 则没有

但是在平时使用时，则不需要专门写事务开始和结束语句，而是在数据库连接建立之后就已经开始了事务。

事务的四大特性：

ACID（原子性，Atomicity）,一致性(Consistency)，隔离性 Isolation,持久性（Durability）

五、复杂查询

1、视图
视图和表：

视图和表的区别：是否保存了实际数据

我们通过INSERT 语句将数据保存到数据库中，而数据库的数据实际是存储在计算机的存储设备。

但是使用视图时，不会将数据保存到其它任何地方，实际上视图保存的是SELECT语句。我们从视图读取数据时，

视图会在内部执行该SELECT语句并创建出一张临时表

 

视图的优点：

1.可以节省空间。实际的业务量很大，使用视图所节省的空间就非常可观

2.保存SELECT语句为视图，每次不用频繁书写SQL (计算合计时)

创建视图的方法：

CREATE VIEW 视图名称(<视图列名1>,<视图列名2>,...)

AS <SELECT 语句>;

也可以在Navicat中找到视图，右键新建 写入查询语句即可

 

 

使用视图：

视图和表一样可以放在SELECT语句中的FROM子句后面。

在视图中可以再创建视图，但是应该避免这样做，因为多重视图会降低SQL的性能

视图的限制1：定义视图时不能使用ORDER BY语句

视图的限制2：对视图进行更新

视图和表需要同时进行更新，否则通过聚合得到的视图无法进行更新

能够更新的视图：不是通过聚合得到的视图

 

加上聚合函数创建视图语句：

CREATE VIEW wview21(sno,sname,num)

AS (select sno,sname,count(*) as num from student group by sname);

 

删除视图：

drop view 视图名称;

2、子查询

子查询和视图：

子查询就是将用来定义视图的SELECT语句直接用于FROM子句中，

 

SELECT tt.sno,tt.sname,tt.num from (

SELECT sno,sname,count(*) as num from student GROUP BY sname

)as tt;

实际上，SELECT语句包含嵌套的结构，首先会执行FROM子句中的SELECT语句，才会执行外层的SELECT语句。在Oracle的FROM子句中，不能使用AS 。多层嵌套子查询会让性能越差，尽量避免多层嵌套查询。

标量子查询：必须而且只能返回1行1列的结果：

SELECT AVG(score) from student;  

标量子查询的书写位置：

能够使用常数或者列名的地方，无论是SELECT语句、GROUP BY语句、HAVING语句、还是ORDER BY语句，几乎所有的地方都可以使用

使用标量子查询时的注意事项：该子查询绝对不能返回多行结果

3、关联子查询

创建商品表的SQL语句：

DROP TABLE IF EXISTS `item`;

CREATE TABLE `item` (

  `id` varchar(128) NOT NULL,

  `name` varchar(128) DEFAULT NULL,

  `type` varchar(32) DEFAULT NULL,

  `price` int(11) DEFAULT NULL,

  `notedate` timestamp(6) NULL DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

-- ----------------------------

-- Records of item

-- ----------------------------

INSERT INTO `item` VALUES ('001', 'T恤', '衣服', '1000', '2018-06-26 15:58:41.000000');

INSERT INTO `item` VALUES ('002', '打孔器', '办公用品', '200', '2018-06-26 16:00:32.000000');

INSERT INTO `item` VALUES ('003', '运动T恤', '衣服', '1500', '2018-06-20 16:03:56.000000');

INSERT INTO `item` VALUES ('004', '菜刀', '厨具', '3000', null);

INSERT INTO `item` VALUES ('005', '高压锅', '厨具', '6800', '2018-06-25 16:01:47.000000');

INSERT INTO `item` VALUES ('006', '叉子', '厨具', '250', '2018-06-26 16:04:24.000000');

INSERT INTO `item` VALUES ('007', '圆珠笔', '办公用品', '200', '2018-06-26 16:02:35.000000');

 

 

子查询时作为别的查询语句的一部分，例如：

Select id,name from student(select * from student where score>90);

但是在一些特殊场景时，子查询不能满足业务需求：

求出商品价格大于同类型的商品的平均值：

SELECT id,name,price

from item

where price > (SELECT type,AVG(price) from item  group by type)

这样会出现错误，因此我们使用关键子查询：

SELECT id, type,name,price

from item t1

where price > (SELECT AVG(price) from item t2 where t1.type = t2.type group by type)

其中where t1.type = t2.type 就是关联条件

六、函数、谓词、CASE表达式

1、SQL中的函数

函数分为：算术函数，字符串函数，日期函数，转换函数，聚合函数

1.1算术函数

ABS(绝对值函数)select (-5) -> 5

MOD(求余函数)  select MOD(3,5) -> 3  (SQL Server 不支持该函数)

ROUND(四舍五入函数) select ROUND(0.75) -> 1

1.2字符串函数

CONCAT(str1,str2)拼接函数

LENGTH(str) 求字符串的长度

LOWER(str) 转换为小写  UPPER(str) 转换为大写

1.3日期函数

SELECT CURRENT_DATE() 获取当前日期

SELECT CURRENT_TIME() 获取当前时间

SELECT CURRENT_TIMESTAMP() 获取当前日期和时间

EXTRACT 截取日期元素

SELECT CURRENT_TIMESTAMP(),

EXTRACT(YEAR FROM CURRENT_TIMESTAMP()) as year,

EXTRACT(MONTH FROM CURRENT_TIMESTAMP()) as month,

EXTRACT(DAY FROM CURRENT_TIMESTAMP()) as day,

EXTRACT(HOUR FROM CURRENT_TIMESTAMP()) as hour,

EXTRACT(MINUTE FROM CURRENT_TIMESTAMP()) as minute,

EXTRACT(SECOND FROM CURRENT_TIMESTAMP()) as second;

在不同数据库中的语句查询语句不同：

Oracle中：

SELECT CURRENT_TIMESTAMP(),

    EXTRACT(YEAR FROM CURRENT_TIMESTAMP()) as year,

    EXTRACT(MONTH FROM CURRENT_TIMESTAMP()) as month,

    EXTRACT(DAY FROM CURRENT_TIMESTAMP()) as day,

    EXTRACT(HOUR FROM CURRENT_TIMESTAMP()) as hour,

    EXTRACT(MINUTE FROM CURRENT_TIMESTAMP()) as minute,

    EXTRACT(SECOND FROM CURRENT_TIMESTAMP()) as second

FROM DUAL;

2、谓词

主要谓词：LIKE、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS

LIKE：模糊匹配查询

前方一致："ddd%" 中间一致："%ddd%"  后方一致："%ddd"  %表示0字符以上的任意字符串

BETWEEN：区间查询

SELECT * from item where price BETWEEN 100 AND 2000;（结果中会包含100 和2000这2个临界值

）

IS NULL、IS NOT NULL ：判断是否为NULL

为了查询时NULL值的数据，不能使用等号（=）只能使用is null

OR :

SELECT * from item where price = 1000 or price =200 or name ='叉子'

IN  在指定的数值内：

SELECT * from item where price in (200,250)

与OR相对的：NOT IN  

使用IN 或者 NOT IN都是无法选取出NULL数据的

EXISTS:判断是否存在满足某种条件的记录：

如果符合条件的记录存在就返回TRUE，不存在就返回FALSE

通常指定关联子查询作为EXISTS的参数

SELECT id,name,price from item

where EXISTS (SELECT * from item where id='002')

如果有id=’002’的商品存在，则SQL语句就相当于select id,name,price from item where 1=1 即查询出所有的数据

NOT EXISTS ：

SELECT * from student tt

where  NOT EXISTS (SELECT * from dept dt where tt.sdept = dt.id)-- 返回表中没有对应关系的数据

3、CASE表达式

Case是在区分情况时使用，在程序中被称为：条件分歧

CASE表达式的语法(搜索式)：(格式很重要！！！)

CASE  WHEN  <判断表达式> THEN  <表达式>

WHEN  <判断表达式> THEN  <表达式>

WHEN  <判断表达式> THEN  <表达式>

...

ELSE <表达式>

END

SELECT name, case when type='衣服' then CONCAT('A ',type)

when type='厨具' then CONCAT('B ',type)

when type ='办公用品' then CONCAT('C ',type)

ELSE 'other'

END as 'things'

from item

 

在这里，ELSE语句可以不写，自动默认为”ELSE NULL” 一定不要漏掉”END”

简单CASE表达式的格式：

CASE <表达式>  WHEN  <表达式> THEN  <表达式>

WHEN  <表达式> THEN  <表达式>

WHEN  <表达式> THEN  <表达式>

...

ELSE <表达式>

END

 

改造以上的写法：

SELECT name, case type when '衣服' then CONCAT('A ',type)

when '厨具' then CONCAT('B ',type)

when '办公用品' then CONCAT('C ',type)

ELSE 'other'

END as 'things'

from item

 

在Oracle中使用DECODE来代替CASE表达式：

SELECT name, DECODE(type,

’衣服’,CONCAT('A ',type),

’厨具’,CONCAT('B ',type),

’办公用品’,CONCAT('C ',type)) as ‘things’

from item

 

实例：

SELECT count(case when price<=1000 then 'lower_price' END) as lower_price,

count(case when price >1000 and price <=3000 then 'mid_price' end) as mid_price,

count(case when price >3000 then 'high_price' end) as high_price

from item

 

 

将上述得到的结果转换一下：

 

SELECT case when price<=1000 then 'lower_price'

    when price>1000 and price <=3000 then 'mid_price'

 when price >3000 then 'high_price'

 END

as 'stage',count(*)

from item

GROUP BY stage

七、集合运算

1、表的加减法

表的加法：UNION:

SELECT id,name from item UNION  SELECT id,name from  item2 ;

就是数学中的并集，相同的部分只显示一份

集合运算的注意事项：

注意事项1：作为运算对象的记录的列数必须相同

注意事项2：作为运算对象的记录中的列的类型必须一致

注意事项3：可以使用任何SELECT 语句，但ORDER BY子句只能在最后使用一次。

包含重复行的集合运算-ALL选项（保留重复行）

SELECT id,name from item UNION ALL SELECT id,name from  item2;

选取表中公共部分——INTERSECT（MySQL 不支持）

SELECT id,name from item INTERSECT  SELECT id,name from item2;

MySQL 可以使用如下方法求出公共部分：

SELECT item.id,item.name from item,item2

where item.id =item2.id and item.name = item2.name

记录的减法：EXCEPT(MySQL 不支持，Oracle使用的MINUS运算符)

SELECT id,name from item EXCEPT SELECT id,name from item2

求出item 表中和除去和item2表相同的部分

2、联结（以列为单位对表进行联结）

UNION和INTERSECT都是以行方向为单位进行操作。而联结JOIN是以列方向为单位进行操作

2.1、内联结-INNER JOIN（选取同时存在于两张表的数据）

SELECT ti.id,ti.name,ts.id,ts.name,ti.price

FROM item as ti

INNER JOIN store as ts on ts.item_id = ti.id

内联结的要点：

1. FORM子句 ：使用关键字INNER JOIN就可以将两张表联结在一起

2. 内联结要点：ON子句作为联结条件

3. SELECT子句：查询的列名，如果表有指定别名，在写列名时：表别名.字段名

条件等价查询相当于内联结：查询结果都一致

SELECT tt.name, tt.price

FROM item as tt, store as ts

WHERE ts.item_id = tt.id;

2.2、外联结-OUTER JOIN（只要数据存在于主表就选取）

SELECT tt.name,tt.price

from item tt RIGHT OUTER JOIN store ts on tt.id = ts.item_id

order by tt.price desc

外联结要点：

1. 选取出单张表全部的信息 2.主表：使用LEFT时，左边的表是主表，使用RIGHT 则右边的表是主表

在上述SQL 中store 为主表。

2.3交叉联结 CROSS JOIN (笛卡尔积)

在实际业务中很少使用，只是声明这种用法