跟前端一起来学数据库系列(2)

上篇文章我们讲了怎样设计数据库表,这次我们来了解一些基本的SQL语句写法。虽然在实际工作可能中我们用ORM库来操作数据库比较多,但ORM最终也是生成SQL语句来与数据库进行交互,了解一些基本的SQL语句,能让我们遇到一些问题时不至于搞不清发生了什么情况。

结构化查询语言(Structured Query Language)简称SQL是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。

结构化查询语言是高级的非过程化编程语言,允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法,也不需要用户了解具体的数据存放方式,所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套,这使它具有极大的灵活性和强大的功能。

结构化查询语言包含6个部分:

  • 数据查询语言(DQL)
  • 数据操作语言(DML)
  • 事务处理语言(TPL)
  • 数据控制语言(DCL)
  • 数据定义语言(DDL)
  • 指针控制语言(CCL)

本次我们主要介绍DQL、DML和DDL。


既然要学习一门新的编程语言,首先我们要看一下这个语言的书写规范,避免遇到一些低级书写错误。SQL语言的书写规范很简单,可以归纳为一下几点:
  • 保留字:有固定语义 不能跨行(如:SELECT、CREATE、DELETE等)
  • 语句不区分大小写,但建议保留字大写
  • 字符数据的拼写必需和数据库里的保持一致
  • 结尾可以加结束符(一般是分号)标识结束,但不是必需

了解了书写规范,接下来我们就可以愉快的书写语句了。我们先来看下DML和DQL有哪些东西:
  • SELECT 用来查询数据库中的数据
  • INSERT 用来向表中加入数据行
  • UPDATE 用来更新表中的数据
  • DELETE 用来从表中删除数据

其中SELECT语句较其它几个语句比较复杂,它包含分组(group)、排序(order)、聚合(aggregate)、多表连接等。


SELECT语句的一般形式如下:

此处输入图片的描述

其中一些名称的解释如下:

  • columnExpression表示一个列名或一个表达式
  • newName是给定的用来显示的列标题名称
  • TableName是想访问的已存在的数据库表或视图名称
  • alias是TableName的可供选择的缩写名称

以下步骤显示 SELECT 语句的逻辑处理顺序(即绑定顺序)。 此顺序确定在一个步骤中定义的对象何时可用于后续步骤中的子句:

  1. FROM 指定用到的一个或多个表
  2. WHERE 按照某些条件过滤行数据
  3. GROUP BY 按相同的列值将行分成组
  4. HAVING 按照某些条件过滤组数据
  5. SELECT 指定在输出结果中出现的列
  6. DISTINCT 用于返回唯一不同的值
  7. ORDER BY 指定输出的排列顺序

其中,各子句顺序不能改变,且只有前两个字句是必需的(SELECT和FROM)。每个SELECT语句将产生一个查询结果表, 由一个或多个列0行或多行结果组成。

各个字句的用法见下面用例:

SELECT wms_car_id , car_status FROM `wms_car` ;-- 查询部分列

SELECT * FROM `wms_car`; -- 使用*查询全部列

SELECT car_status FROM `wms_car`;  
SELECT DISTINCT car_status FROM `wms_car`; -- 使用DISTINCT来消除重复数据

SELECT car_status, wms_car_id FROM `wms_car`;  
SELECT DISTINCT car_status, wms_car_id FROM `wms_car`; -- DISTINCT应用于多行

SELECT wms_car_id, odometer * 2 FROM `wms_car`;  
SELECT wms_car_id, odometer * 2 AS odometerT FROM `wms_car`; -- 计算字段 要求数值 加减乘除 括号都支持 可使用多个表中的列(join后)



-- WHERE
-- 比较: > < = <= >= <> NOT AND OR 括号
-- 范围: BETWEEN
-- 集合成员: IN   NOT IN
-- 模式匹配: LIKE   NOT LIKE
-- 空值: IS NULL  IS NOT NULL

SELECT wms_car_id, car_status, odometer FROM `wms_car` WHERE odometer > 10000;

SELECT wms_car_id, car_status, odometer FROM `wms_car` WHERE odometer > 10000 AND car_status = 2;

SELECT wms_car_id, car_status, odometer FROM `wms_car` WHERE odometer BETWEEN 20000 AND 30000;

SELECT wms_car_id, car_status, odometer FROM `wms_car` WHERE car_status NOT IN (2, 5);

SELECT wms_car_id, car_status, odometer FROM `wms_car` WHERE car_status IN (2, 5);

SELECT wms_car_id, car_status, warehouse_fullname FROM `wms_car` WHERE warehouse_fullname LIKE '__大头%'; -- %匹配0个或多个 _匹配一个 ESCAPE标识转义字符 LIKE '15#%' ESCAPE '#'

SELECT wms_car_id, car_status, warehouse_fullname FROM `wms_car` WHERE warehouse_fullname IS NULL;



-- ORDER BY
SELECT wms_car_id, car_status, odometer FROM `wms_car` WHERE odometer > 10000 ORDER BY wms_car_id ASC, odometer DESC;


-- 聚合函数 在表的一列上进行操作 并返回单一值
-- COUNT SUM AVG MIN MAX
-- COUNT MIN MAX可用在数值和非数值 AVG SUM只能用在数值上
-- 聚合函数只能在SELECT和HAVING中使用 如果SELECT中包含了聚合函数并且没有用GROUP BY子句 那么SELECT中不能包含对任何列的引用
-- 除非该列在聚合函数中
SELECT avg(DISTINCT odometer) FROM `wms_car` WHERE odometer > 10000; -- 返回最大的里程的记录 在一个查询中DISTINCT只能指定一次

SELECT count(*) as count FROM `wms_car`


-- GROUP BY分组
-- SELECT 中所有列名都要出现在GROUP BY中 除非只在聚合函数中使用 反之则不然 出现在GROUP BY中的列名不一定出现在SELECT中

select car_status, count(*) FROM `wms_car` GROUP BY car_status

-- HAVING字句
-- 用法和WHERE差不多 目的却不一样 用来过滤分组后各个分组的数据 并可以应用聚合函数
-- HAVING中使用的列名必须在GROUP BY中 或者聚合函数中
-- 如果HAVING中一个聚合函数都不包含 常常这个条件可以移动到WHERE中
-- HAVING不必要 任何用了HAVING的语句都可以转换成不包含HAVING的

select car_status, count(*) FROM `wms_car` GROUP BY car_status HAVING count(*) > 500  

讲完了SELECT这个庞大的语句,后面的三个就简单很多了,它们的条件部分(WHERE字句)和SELECT里的是一样的,所以,这里就列举一下它们的基本语法和一些注意事项就行了。

先来看INSERT语句,它的一般形式如下: 此处输入图片的描述

其中,columnList可省略,如果省略将按照建表的顺序排列, 也可以省略部分,如果这样做,那么省略的列要允许设置为空。 如果列没设置Default的话,那么就要求columnList里的项数和表里的实际列数一样,一一对应,且数据格式兼容。

INSERT说完了,后面的UPDATE和DELETE就没啥好说的了,直接放语法吧:
此处输入图片的描述 此处输入图片的描述

注意,执行UPDATE和DELETE时一定要指定WHERE字句,不然就变成操作全部记录了,这可能不是你想要的。


经过上面的学习,我们学会了书写基本的SQL查询语句来操作数据库表的数据,但是怎么创建数据库表我们还没有提及。因为现在大部分情况下都不是手写SQL来创建表,就算需要SQL语句来创建表也基本是通过可视化工具创建完成之后再导出SQL语句来完成,我个人也建议尽量使用可视化工具来建表和修改表结构,这样速度更快而且减少出错。所以最后我主要列举一下DDL里的CREATE TABLE的基本语法并附上一个小例子来帮助大家理解语法:

此处输入图片的描述

CREATE TABLE `act_activity_tag` (  
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(180) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `meta_url` varchar(500) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `priority` int(11) NOT NULL DEFAULT '0',
  `bg_color` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `line_color` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  `is_show` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_bin;