Nodejs的学习Ⅴ（node连接mysql、MySQL的语句）

一、node连接mysql和一些简单的操作

1、安装

npm install mysql			// 安装

2、连接数据库

在以下实例中根据你的实际配置修改数据库用户名、及密码及数据库名：

const mysql = require('mysql');

let options = {
  host: 'localhost',
  port: '3306',                 					// 可选，默认是3306
  user: 'root',
  password: 'password_here',
  database: 'mysqldb'
};

let connection = mysql.createConnection(options);	// create链接

connection.connect();								// 连接数据库

function mysqlQuery(sql, arr) {
  return new Promise((resolve, reject, fields) => {
    connection.query(sql, arr, (err, result) => {	// 数据查询
      if(err) {
        reject(err)									// 查询失败
      }else {
      	console.log(fields);						// 与查询的字段相关的信息
        resolve(result)								// 查询结果
      }
    })
    connection.end()								// 关闭与数据库的连接
  })
}

module.exports = mysqlQuery							// 导出

3、创建表

let sqlStr = `create table student (
    id int(3) primary key auto_increment comment '学号',
    name varchar(10) not null default '',
    age int(3) default 0,
    gender varchar(10) not null,
    habbies varchar(100)
)engine=InnoDB default charset=utf8`;

connection.query(sqlStr, function (error, results, fields) {
  if (error) throw error;
  console.log('The result is: ', results);
  console.log(fields);
});

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 但不能在操作数据库时如果输入该字段的数据为NULL ，因为会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

4、插入数据

let sqlStr = `insert into student (id, name, age, gender, habbies) values (?, ?, ?, ?, ?)`;
let sqlData = [1, '李老板', 40, 'male', '装逼'];

connection.query(sqlStr, sqlData, function (error, results, fields) {
  if (error) throw error;
  console.log('The result is: ', results);
  // console.log(fields);
});

5、更改数据

let sqlStr = `update student set age = ? where id = ?`;
let sqlData = [20, 1];

6、删除数据表内的某条数据

delete from student where id = 6;

7、删除数据库

drop database <数据库名>;

8、删除数据表

drop table table_name ;

9、切换数据库

use mysqldb;

10、MySQL 数据类型

数值类型

tinyint：1 字节，有符号范围(-128，127)， 无符号范围（0，255）
smallint：2 字节，有符号范围(-32 768，32 767)， 无符号范围（0，65 535）
integer或int：4 字节，有符号范围(-2 147 483 648，2 147 483 647)， 无符号范围(0，4 294 967 295)
bigint：8 字节，有符号范围(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)， 无符号范围(0，18 446 744 073 709 551 615)
double：8 字节
float：4 字节

日期和时间类型

date：3字节，范围（1000-01-01/9999-12-31），格式YYYY-MM-DD
time：3字节，范围（ ‘-838:59:59’/‘838:59:59’），格式 HH:MM:SS
year：1字节，范围（ 1901/2155），格式YYYY
datetime：8字节，范围（ 1000-01-01 00:00:00/9999-12-31 23:59:59），格式YYYY-MM-DD HH:MM:SS

字符串类型

char：大小 0-255字节，用于定长字符串
varchar：大小0-65535 字节，用于 变长字符串
text：大小0-65 535字节，用于长文本数据
longtext：大小0-4 294 967 295字节，用于极大文本数据
blob：大小0-65 535字节，用于 二进制形式的长文本数据

11、查询语句

注意查询的结果是一个数组（不是类数组），而里面的每个值又为对象，所以传输数据时需要把数组转化为JSON格式的字符串

select * from student;

12、去重

distinct，要注意的是，DISTINCT短语的作用范围是所有列

/*正确写法*/
select DISTINCT cno, grade from sc;

/*错误写法*/
select DISTINCT cno, DISTINCT grade from sc;

二、SELECT的语句（WHERE）

where 用于 引出查询条件，常用的查询条件：
比较：=，>，<，>=，<=，!=，<>，!>，!<，not
确定范围：between … and，not between … and
确定集合：in，not in
字符匹配：like，not like
空值：is null，is not null
多重条件：and，or，not

条件

等于 = ，大于 >，大于等于 >=，小于 < ，小于等于 <= ，不等于 !=或<>

select * from students where sname <> '黄蓉';			// 查询姓名不是“黄蓉”的学生

逻辑运算符

and，or，not

select * from students where id>3 and gender=0;			// 查询编号大于3的女同学
select * from students where id<4 or isdelete=0;		// 查询编号小于4或没被删除的学生

模糊查询

like
% 表示任意多个任意字符
_ 表示一个任意字符
但是 当选要查询的内容本身就有“-”或者“%” 的时候就需要使用escape（逃避），可以理解为 “\”本身没有特殊含义 ，但当 “\” 出现在待查询内容时，会被当成普通字符，而不是转义字符，这时 需要使用escape来避免读取“\” ，以达到可以用于转义字符的目的

/*普通情况*/
select * from students where sname like '黄%';			// 查询姓黄的学生
select * from students where sname like '黄_';			// 查询姓黄并且名字是一个字的学生

/*当查询条件本身有特殊内容时*/
select * from coures where cname like 'DB\_%' escape '\'; // 查询以“DB_”开头的内容

范围查询

in表示在一个非连续的范围内， in中的子查询只能包含一个列
between … and … 表示在一个连续的范围内

select * from students where id in(1,3,8);				// 查询编号是1或3或8的学生
select * from students where id between 3 and 8;		// 查询学生是3至8的学生

select name from emp where dept_id in (select id from dept where name = "财务");		// 正确的写法
select name from emp where dept_id in (select * from dept where name = "财务");			//  错误的写法

空判断

注意：null与 空字符（‘’） 是不同的,判空is null,判非空is not null，而不能是“= null”

select * from students where hometown is null;			// 查询没有填写地址的学生
select * from students where hometown is not null;		// 查询填写了地址的学生

优先级

小括号，not，比较运算符，逻辑运算符,and比or先运算，如果同时出现并希望先算or，需要结合()使用

还要注意的是where中不能出现集函数

/*正确*/
select sno, avg(grade) from sc where grade group by sno;			 

/*错误*/
select sno, avg(grade) from sc where avg(grade) >= 80 group by sno;	// 出现了cno（非集函数或者分组属性）

三、SELECT的语句（ORDER BY）

排序

asc从小到大排列，即升序（默认）,desc从大到小排序，即降序

select * from 表名										// 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
order by 列1 asc|desc,列2 asc|desc,...

select * from students									// 查询未删除男生学生信息，按学号降序
where gender=1 and isdelete=0
order by id desc;

四、SELECT的语句（GROUP BY）

按照字段分组，表示此字段相同的数据会被放到一个组中，可以对分组后的数据进行统计，做聚合运算或筛选

select gender as 性别,count(*)							// 查询男女生各位多少
from students
group by gender;

select gender as 性别,count(*)							// 查询男生总人数（男生标识符位1）
from students
group by gender
having gender=1;

且当在使用了group by子句后，select子句的列名列表中只能出现分组属性和集函数

/*正确*/
select sno, avg(grade) from sc group by sno;			 

/*错误*/
select sno, cno, avg(grade) from sc group by sno;		// 在select后面出现了cno（非集函数或者分组属性）

还有在having后面只能出现集函数或者分组属性
且当select里面有having时，则必有group by

/*正确*/
select sno, avg(grade) from sc group by sno having avg(grade) >= 80;			 

/*错误*/
select sno, avg(grade) from sc group by sno having grade >= 80;		// 在having后出现了grade（非集函数或者分组属性）

五、MySQL的语法（函数）

当select语句未分组时，函数针对整个查询结果；但是当有分组时，函数将作用每个分组

聚合函数

count():表示计算总行数，括号中写星与列名，结果是相同的

select count(*) from students;							// 查询学生总数

max():表示求此列的最大值

select max(id) from students where gender=0;			// 查询女生的编号最大值

min():表示求此列的最小值

select min(id) from students where isdelete=0;			// 查询未删除的学生最小编号

sum():表示求此列的和

select sum(id) from students where gender=1;			// 查询男生的编号之后

avg():表示求此列的平均值

select avg(id) from students where isdelete=0 and gender=0;	// 查询未删除女生的编号平均值

六、MySQL的其他内容

mysql中的SQL语句执行顺序

from => on => join => where => group by => with => having => select => distinct => order by => limit
一条SQL会经过这11步的，中间的每一步都会生成一张虚拟表，后面的步骤都是在上一张虚拟表中进行筛选与查询的

别名

select ssex sgender form student;						// 给ssex设置别名为sgender
select ssex as sgender form mytable;					// 给ssex设置别名为sgender

对比where与having

where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
having是对group by的结果进行筛选

update和alter之间的区别

alter用来修改基本表,是对表的结构进行操作，比如对字段增加，删除，修改类型
update用来修改表中的数据，修改某一行某一列的值

delete 和drop 之间的区别

drop 会把表数据和表结构一起删除
delete 仅仅删除表数据

分页

当数据量过大时，在一页中查看数据是一件非常麻烦的事情，分页可以解决这个问题

select * from 表名										// 从start开始，获取count条数据，start索引从0开始
limit start,count

范式目的

为了更好的分开每个表的职能，减少数据冗余，以及避免发生修改异常，新增异常，删除异常等等
在 关系数据库模型 设计中，如果一个表具有良好的主外键设计，就应该是满足3NF的表。规范化带来的好处是通过减少数据冗余提高更新数据的效率，同时保证数据完整性。然而，我们在实际应用中也要防止过度规范化的问题。规范化程度越高，划分的表就越多，在查询数据时越有可能使用表连接操作。而如果连接的表过多，会影响查询性能。关键的问题是要依据业务需求，仔细权衡数据查询和数据更新关系，指定最合适的规范化程度。不要为了遵循严格的规范化规则而修改业务需求

第一范式(1NF)

表中的列（属性）只能含有原子性(不可再分)的值。

第二范式(2NF)

满足第一范式，没有部分依赖

第三范式(3NF)

满足第二范式，没有传递依赖

外键

为了保证关系列数据的有效性（保证字段关系的有效性），可以通过外键约束进行数据的有效性验证，也就是说外键是一种约束，主表数据改变，从表跟着改变

/* 表table的stuid列作为表外键（外键名称：stu_sco），以表 students做为主表，从表是scores，以其 id列 做为参照（references），
且联动删除/更新操作的级联操作（on delete/update cascade）
*/
alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade on update cascade;


// 在创建表时可以直接创建约束，这里主表是students，从表是scores
create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);

引擎必须为InnoDB，MyISAM不支持，级联操作的类型包括：restrict（限制）：默认值，抛异常；cascade（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删除；set null：将外键设置为空；no action：什么都不做

连接查询

连接查询分类如下：表A inner join 表B：表A与表B匹配的行会出现在结果中；表A left join 表B：表A与表B 匹配的行 会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充；表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充

select students.sname,avg(scores.score)						// 查询学生的姓名、平均分
from scores
inner join students on scores.stuid=students.id
group by students.sname;

select students.sname,avg(scores.score)						// 查询男生的姓名、总分
from scores
inner join students on scores.stuid=students.id
where students.gender=1
group by students.sname;

select subjects.stitle,avg(scores.score),max(scores.score)	// 查询未删除科目的名称、最高分、平均分
from scores
inner join subjects on scores.subid=subjects.id
where subjects.isdelete=0
group by subjects.stitle;

在查询或条件中推荐使用“表名.列名”的语法；如果多个表中列名不重复可以省略“表名.”部分；如果表的名称太长，可以在表名后面使用’ as 简写名’或’ 简写名’，为表起个临时的简写名称
有时自连接也可以解决问题

子查询

当一个查询是另一个查询的条件时,这个查询称之为子查询(内层查询)，首先明确子查询就是一个普通的查询, 当 一个查询需要作为子查询使用时,用括号包裹即可

select from emp where exists (select from emp where salary > 1000);	// 如果 后面（exists后面） 查询有结果时，前面（exists前面） 才会执行

exists后跟子查询，子查询有结果是为True然后外层执行，没有结果时为False外层不执行。

视图

视图是一张虚拟表，视图里面的数据还在原来的表内，用途就是便于多次查询，还可以对视图进行再次查询

create view stuscore as 								// 在scores与students的连接表内学生的所有信息和成绩
select students.*,scores.score from scores
inner join students on scores.stuid=students.id;

SELECT * from stuscore;						// 查询视图里面的内容就相当于查询实际表的内容

事务

当执行某条sql语句发生错误时，则可使用事务完成退回功能，保证业务逻辑的正确性，但要注意的是表的类型必须是innodb或bdb类型才可以使用事务
事务的四大特征：
原子性：事务中的全部操作在数据库内都是不可分割的，要么全部完成，要么都不完成
一致性：几个并行的事务，其执行结果必须与按某一顺序串行执行的结果一致
隔离性：事务的执行不受其他的事务干扰，事务执行的中间结果对其他事务必须时透明的
持久性：对任意提交的事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障

/* 这个事务要么全部完成，要么都不完成（只要有一条sql错误），且发生错误时会自动回滚（rollback）
*/
begin;					// 开始事务
insert into student values (value1, value2, ...);
insert into course values (value3, value4, ...);
insert into teacher values (value5, value6, ...);
commit;					// 结束事务

本文只用于个人学习与记录