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

╰半夏微凉° 2023-02-09 14:10 62阅读 0赞

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

### 1、安装 ###

npm install mysql			// 安装

### 2、连接数据库 ###

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

### 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;					// 结束事务

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