前言:
上一篇博客中,总结了在 Node 中通过 mysql 库提供的一系列 API 方法来操作数据库,实现日常的开发需求。操作数据库,API 方法已经写好,直接引用库文件方法即可,困难的地方可能就在于 SQL 语句的编写 。本篇博客就对 mysql 中常用语法 做一个总结,增删改查等操作。
前置知识:
- SQL 语句中运算符
比较运算符:
等于 =
大于 >
大于等于 >=
小于 <
小于等于 <=
不等于 != 或 <>
逻辑运算符:
and
or
not
模糊查询:
like
% 表示多个任意字符
_ 表示一个任意字符
-
范式与关系
范式:数据库的规范,帮助我们更好的建表(高效简洁)
分类:
第一范式(1NF):表中的列含有原子性(不可再分)的值
第二范式(2NF):要同时满足,第一范式和没有部分依赖(分表,去除依赖)
第三范式(3NF):要同时满足,第二范式和没有传递依赖
标准:一张表只做一件事,只有一个关系
-
视图
问题:对于复杂的查询,在多次使用后,维护是一件非常麻烦的事情
解决:定义视图(视图本质是对查询的一个封装)
定义视图:create view 新建表名称 as
视图的用途:视图的用途就是查询,查询视图用途等同于查询表 select * from新建的表名称
实例:
-- 定义视图 create view stuscore as select students.*,scores.score from scores inner join students on scores.stuid=student.id -- 使用视图 select * from stuscore
- 事务
事务:当一个业务逻辑需要多个sql完成时,如果其中某条sql语句出错,则希望整个操作都回退,使用事务可以完成退回的功能,保证业务逻辑的正确性。
事务的四大特性(简称ACID):
原子性:事务中的全部操作在数据库中是不可分割的,要么全部完成,要么均不执行。
一致性:几个并行执行的事务,其执行结果必须与按某一顺序串行执行的结果一致。
隔离性:事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的。
持久性:对于任意已提交事务,系统必须保证该事务对数据库改变不丢失,即使数据库出现故障,
使用要求:表的类型必须是innodb或bdb类型,才可以对此表使用事务
常见使用:
查看表的创建语句:show create table students
修改表的类型:alter table '表名' engine=innodb
事务语句: 开启 begin; 提交 commit; 回滚 rollback;
查询:
- 整表查询
语法:select * from 表名
实例:
-- select * from 表名 SELECT * from book
- 条件精准查询
概念:使用 where 子句对表中的数据筛选,结果为 ture 的行会出现在结果集中。
语法:
- select * from 表名 where 条件;(单个条件)
select * from 表名 where between 条件 and 条件1;(组合条件)
select * from 表名 where 条件1 and 条件2;(组合条件)
select * from 表名 where 条件 is NULL;(判断为空)
select * from 表名 where 条件 in (条件1,条件2);(组合条件)
select * from 表名 where 条件1 or 条件2;(或)
实例:
-- select * from 表名 where 条件;(单个条件) SELECT * from book where viewcount > 1000; -- select * from 表名 where 条件 and 条件l;(组合条件) SELECT * from book where viewcount > 1000 and viewcount < 1100; -- select * from 表名 where between 条件 and 条件1;(组合条件) SELECT * from book where viewcount BETWEEN 3000 AND 4000; -- select * from 表名 where 条件 in (条件1,'条件2);(组合条件) select * from studetnew where name in ('wuhao','localhost') -- select * from 表名 where 条件1 or 条件2 select * from studetnew where name='wuhao' or name='localhost'
注意:mysql注释为 -- 空格 (两杠加一个空格)。
- 模糊查询
概念:模糊查询会用到 like,%(表示多个任意字符),_ (表示一个任意字符)等符号。
语法:
- select * from 表名 where 字段 like 条件
- select * from 表名 where 字段 like 条件%
- select * from 表名 where 字段 like 条件_
实例:
-- select * from 表名 where 字段 like 条件 SELECT * from book where name like 'wuhao'; -- select * from 表名 where 字段 like 条件% SELECT * from book where name like '%hao'; -- select * from 表名 where 字段 like 条件_ SELECT * from book where name like 'wuh__';
- 连表查询
语法:
- 表A inner join 表B on 条件;表A与表B匹配的行会出现在结果中
- 表A left join 表B on 条件;表A与表B匹配的行会出现在结果中,外加表 A 中独有的数据,未对应的数据使用 null 填充
- 表A right join 表B on 条件;表A与表B匹配的行会出现在结果中,外加表 B 中独有的数据,未对应的数据使用 null 填充
注意:
- 在查询或条件中推荐使用 '表名.列名' 的语法
- 如果多个表中列名不重复,可以省略 '表名.'
- 如果表的名称太长,可以在表名后面使用 'as 简写名' 或 '简写名',为表起个临时的简写名称
实例:
-- 连接表 select * from author inner join authorbook on author.id=authorbook.id -- 条件查询 -- 表A inner join 表B on条件;表 A 与表 B 匹配的行会出现在结果中 select * from author inner join authorbook on author.id=authorbook.id where author.name = "neizi"
-
自关联查询和子查询
语法:
- select * from 表名 as r1 inner join 表名as 连接条件 where 条件 ;(自关联查询)
- select * from 表名1 inner join 表名2 on 连接条件 where 条件 in (select 字段 from 表名 where 条件);(子查询)
- select * from 表名where exists (select 字段 from 表名 where 条件);条件查询(是否存在)
实例:
-- 三表连接 select * from score inner join student on student.id = score.stuid inner join project on project.id = score.projectid where project.project='语文' -- 三表连接(数据筛选) select score.id,student.studentname,score.score from score inner join student on student.id = score.stuid inner join project on project.id = score.projectid where project.project='语文' -- 省--市--区(自关联查询) select * from region as r1 inner join regoin as r2 on re.id = r2.pid where r1.name = '广东省' -- 子查询 select * from score inner join student on score.stuid = student.id where student.studentname in (select student.name from student where student.age < 20) -- 条件查询(是否存在) select * from teacher where exists (select studentname from student where studentage > 50)
更新:
更新:MYSQL UPDATE 更新,如果我们需要修改或更新MYSQL中的数据,我们可以使用SQL UPDATE 命令来操作 。
语法:UPDATE table_name SET field1=new-value1,field2=new-value2 [WHERE Clause] 。
特点:可以同时更新一个或者多个字段,可以在WHERE子句中指定任意条件,可以在一个单独表中同时更新数据 。
删除:
删除:MYSQL DELETE 语句,可以使用SQL的DELETE FROM命令来删除MYSQL数据库表中的记录
语法:
- DELETE FROM 表名 [WHERE Clause]
DELETE FROM 表名 (如果没有指定的WHERE子句,MYSQL表中的所有记录将被删除)
DROP TABLE 表名 (删除表)
DROP DATABASE 库名 (删除库)
注意:删除一般分为逻辑删除和物理删除。物理删除,直接删除,找不到。逻辑删除,逻辑上删除,设置字段登录不了,信息还在(实质为更新操作)。
插入:
语法:
- INSERT INTO 表名 (插入的字段) value (插入字段对应的值)
CREATE DATABASE 库名; (创建库)
CREATE TABLE 表名 (sql语句);(创建表)
实例:
// 1,插入数据 let strSql6 = 'insert into studetNew (id,name,password) value (2,"localhost","root123")' con.query(strSql6,(err,results) =>{ if(err){ console.log(err) }else{ console.log('插入数据操作成功') } }) // 2,插入数据(数据不写死,?做占位符) let strSql7 = 'insert into studetNew (id,name,password) value (?,?,?)' con.query(strSql7,[3,'闻言','wenyan'],(err,results) =>{ if(err){ console.log(err) }else{ console.log('插入数据操作成功') } })