MySQL总结

在Node.js中使用MySQL

• 在Node.js中连接数据库

• 使用数据库连接池

• 使用Promise

• 使用预处理

在执行sql语句时，有时会遇到大量结构相同仅部分变量不同的语句，直接执行这些语句消耗的时间是巨大的，而预处理则是为了这种情况准备的语法

一种减轻服务器压力的技术

传统SQL语句处理流程：

--在客户端准备sql语句

select * from student where id =1

--发送sql语句到mysql服务器

--mysql服务器对sql语句进行解析-编译-执行

--将执行结果返回给客户端

传统的弊端：

--即使多次传递的语句大部分内容都是相同的，每次还是要重复传递

--即使多次传递的语句大部分内容都是相同的，每次执行之前还是要先解析、编译之后才能执行

预处理的处理流程：

--在客户端准备预处理的SQL语句

--发送预处理SQL语句进行解析-编译，但不执行

--在客户端准备相关数据

--MySQL服务器对数据和预处理SQL编译，然后执行该SQL语句

--服务器将执行结果返回给客户端

--预处理优点：只对SQL语句进行了一次解析；重复内容大大减少(网络传输更快)

如何使用预处理

-在客户端准备预处理的SQL语句

prepare  ppstmt from 'select * from student where id = ?;';

--发送预处理SQL语句进行解析-编译，但不执行

--在客户端准备相关数据

set @id = 1;

--MySQL服务器对数据和预处理SQL编译，然后执行该SQL语句

execute ppstmt using @id

--服务器将执行结果返回给客户端

--多表查询

直接查询：只需要在单表查询的基础上增加一张表即可，返回的结果是多张表的表数据个数的乘积，会有冗余数据

select * from student，class

select * from student，class where student.class_id = class.id

连接查询：

内连接查询(INNER JOIN 或 JOIN)

select * from student join class on student.class_id = class.id;

select student.id class.name  from student join class on student.class_id = class.id;

select stu .id cls.name  from student stu join class cls on stu .class_id = cls.id;   --- 取别名

外连接查询： 

左外连接查询(LEFT OUTER JOIN 或 LEFT JOIN)

左边的表是不看条件的，无论条件是否满足，都会返回左边中的所有数据，只有右边的表会看条件，对于右表，只有满足条件，才会返回

select * from student left join class on student.class_id = class.id;

右外连接查询(LEFT OUTER JOIN 或 LEFT JOIN)

右边的表是不看条件的，无论条件是否满足，都会返回右边中的所有数据，只有左边的表会看条件，对于左表，只有满足条件，才会返回

select * from student right join class on student.class_id = class.id;

UNION 查询

在纵向上将多张表的查询结果拼接起来返回  注意：必须保证多张表查询的字段个数一致(id,name)

select id,name from student union select id,name,from class

子查询：将一个查询语句查询的结果作为另外一张表的查询条件来使用

select class_id from student where id=3

select class_id from student where id>=3

select name from class where id = (select class_id from student where id=3)

select name from class where id = (select class_id from student where id>=3) -- 报错

select name from class where id in (select class_id from student where id>=3) -- 正确

--分组查询 GROUP BY: 在对数据进行分组的时候，select后面必须是分组字段或者聚合函数

select * from student;

select class from student group by class;

select class, avg(age) from student group by class;

select gender, avg(age) avgAge from student group by gender;

--HAVING条件查询:  WHERE 是取数据表中查询符合条件的数据返回结果集，HAVING是取结果集中查询符合条件的数据，可以对分组之后查询到的结果进行筛选

select class , avg(age) from student group by class;

select class , avg(age) from student group by class where avg(age) <= 19 ; --报错

select class , avg(age) avg from student group by class having avg(age) <= 19 ; --正确  在结果集中筛选

-- 聚合函数: 对表中的数据进行统计和计算，一般结合分组(GROUP BY)来使用，用于统计和计算分组数据

COUNT() 计算查询到的多少条数据

SUM() 计算查询结果中所有指定字段的和

AVG() 计算查询结果中所有指定字段的平均值

MAX() 求查询结果中指定字段的最大值

MIN() 求查询结果中指定字段的最小值

select * from student;

select count(*) from student;

select count(id) from student;

select sum(age) from student;

select avg(age) from student;

select max(age) from student;

select min(age) from student;

--给查询出来的字段起别名  两种写法同效果

select count(*) as total from student;  

select count(*) total from student;

---排序

select * from student order by age asc; -- 默认就是升序 ascending

select * from student order by age desc; -- 默认就是降序 descending

select * from student order by age desc , id asc; -- 先按照年龄降序，再按id升序

--分页查询

limit -- 一次查询的条数 offset 偏移量

limit --  偏移量，一次查询的条数

select * from student limit 10 offset 0;

select * from student limit 10 offset 10;

select * from student limit 10 offset 20;

select * from student limit 0, 10;  偏移量0查询10条

--1、 基础查询

select id, name from student; -- 查询指定的字段

select * from student -- 查询所有字段

--2、WHERE 条件查询

select * from student  where id=4;

select * from student  where id>=4;

select * from student  where id!=4;

select * from student  where age >=19 and  gender ='男';

select * from student  where age >=19 or gender ='男';

select * from student  where not age >=19;

select * from student  where id in(1, 2, 4); -- 范围限定在指定的值中

select * from student  where id between 1 and 4; -- 在一个范围内，包含边界

select * from student  where  class  is null; -- 查 class 为null的数据

select * from student  where  class  is not null; -- 查 class 不为null的数据

select * from student  where  name like '张%';   ----  %百分号是匹配任意个任意字符

select * from student  where  name like '张_';   ----    _下划线匹配一个任意字符

select * from student  where  name like '%三';   ----    查询‘三’结尾，前面任意个任意字符

select * from student  where  name like '%三%';   ----   查询包含‘三' 的数据

-- 1、插入数据(增)

insert into user (username) values('Alex');

insert into user (username,gender) values ('张三', '男');

insert into user (username,gender) values ('张三', '男'), ('李四', '男');

---2、删除数据(删)

delete from user; --- 删除所有的数据

delete from user where id=6;

---3、更新数据(改)

update user set username='赵六'; --- 修改所有的

update user set username='Alex', gender='男' where id=5; -- 修改id=5指定的数据

预处理

create table if not EXISTS user (

id int UNSIGNED PRIMARY KEY auto_increment,

username VARCHAR(20),

gender ENUM('男','女', '保密') DEFAULT '保密'

);

show TABLES;

DESC user;

-- 1、插入

insert INTO user (username) VALUES ('Alex');

insert INTO user (username, gender) VALUES ('张三', '男');

insert INTO user (username, gender) VALUES ('李四', '男'), ('王五', '男');

-- 2、删除

DELETE from user WHERE id = 2;

-- 3、更新

update user set username='mali', gender='女' where user.id = 1;

create table if not EXISTS student (

id int UNSIGNED PRIMARY KEY auto_increment,

name VARCHAR(20) not NULL,

gender ENUM('男','女', '保密') DEFAULT '保密',

class VARCHAR(20),

age INT NOT NULL,

createAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

updateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

INSERT INTO student (name, age, gender, class) values ('王五',21,'女','二班');

INSERT INTO student (name, age, gender, class) values ('赵柳',22,'男','一班');

INSERT INTO student (name, age, gender, class) values ('孙琦',20,'女','三班');

INSERT INTO student (name, age, gender) values ('粥吧',19,'男');

INSERT INTO student (name, age, gender, class) values ('及时',18,'女','二班');

INSERT INTO student (name, age, gender, class) values ('李盼',18,'女','一班');

INSERT INTO student (name, age, gender) values ('冯吉', 19,'男');

-- 1. 基础查询

SELECT id,name from student;

SELECT * from student;

-- 2. where条件查询

-- >、>=、<、<=、=、!=

-- AND、OR、NOT

-- IN(值1，值2,...)、BETWEEN...AND

-- IS NULL、IS NOT NULL

-- LIKE 模糊查询

SELECT * from student WHERE student.id=4;

SELECT * from student WHERE student.age>=19 AND gender='男';

SELECT * from student WHERE student.age IN(19,20,21);

SELECT * from student WHERE student.age BETWEEN 19 AND 21;

SELECT * from student WHERE student.class IS NULL;

SELECT * from student WHERE student.name LIKE '王%'; -- % 表示任意个数的任意字符

SELECT * from student WHERE student.name LIKE '王_'; -- _ 表示一个任意字符

SELECT * from student WHERE student.name LIKE '%五%'; -- _ 表示一个任意字符

-- 3.排序

SELECT * FROM student ORDER BY age asc; -- 默认就是升序 ascending

SELECT * FROM student ORDER BY age desc; -- 降序 descending

SELECT * FROM student ORDER BY age desc, id asc; -- 先按照年龄降序，如果年龄相同则按照id升序排列

-- 4.分页查询

-- LIMIT 一次查询的条数 OFFSET 偏移量

-- LIMIT 偏移量,一次查询的条数

SELECT * FROM student LIMIT 2 OFFSET 0;

SELECT * FROM student LIMIT 2 OFFSET 2;

SELECT * FROM student LIMIT 2 OFFSET 4;

SELECT * FROM student LIMIT 0, 2;

-- 5.聚合函数

-- 例如函数 SELECT DATABASE() 非聚合函数;

-- 对表中的数据进行统计和计算，一般结合分组（GROUP BY）来使用,用于统计和计算分组数据

-- COUNT() 计算查询到了多少条数据

-- SUM() 计算查询结果中所有指定字段的和

-- AVG() 计算查询结果中所有指定字段的平均值

-- MAX() 查询结果中指定字段的最大值

-- MIN() 查询结果中指定字段的最小值

SELECT * FROM student;

SELECT COUNT(*) FROM student;

SELECT SUM(age) FROM student;

SELECT AVG(age) FROM student;

SELECT MAX(age) FROM student;

SELECT MIN(age) FROM student;

-- 给查询出来的字段起别名

SELECT COUNT(*) totalRecord FROM student;

-- 6.分组查询 GROUP BY

SELECT * FROM student;

-- 在对数据进行分组的时候, SELECT 后面必须是分组字段或者聚合函数

-- SELECT * FROM student GROUP BY class; -- 错误案例

SELECT class FROM student GROUP BY class;

SELECT class, avg(age) FROM student GROUP BY class;

SELECT gender,AVG(age) avgAge FROM student GROUP BY gender;

-- 7.HAVING 条件查询

-- WHERE 是去数据表中查询符合条件的数据返回结果集

-- HAVING 是去结果集中查询符合条件的数据，可以对分组之后查询到的结果进行筛选

-- SELECT class,AVG(age) avgAge FROM student GROUP BY class WHERE avgAge<=19.5; -- where不行会报错

SELECT class,AVG(age) avgAge FROM student GROUP BY class HAVING avgAge<=19.5;

-- 8. 多表查询

-- 8.0.准备

create table if not EXISTS class ( -- 班级表

id TINYINT UNSIGNED PRIMARY KEY auto_increment,

name VARCHAR(20) NOT NULL,

`desc` VARCHAR(255),

createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

undateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

INSERT into class (name, `desc`) VALUES('一班', '火箭班');

INSERT into class (name, `desc`) VALUES('二班', '飞机班');

INSERT into class (name, `desc`) VALUES('三班', '高铁班');

INSERT into class (name, `desc`) VALUES('四班', '汽车班');

CREATE TABLE IF NOT EXISTS student ( -- 学生表

id INT UNSIGNED PRIMARY KEY auto_increment,

name VARCHAR(20) NOT NULL,

age TINYINT UNSIGNED NOT NULL,

gender ENUM('男', '女', '未知') DEFAULT '未知',

class_id TINYINT UNSIGNED,

createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

undateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY(class_id) REFERENCES class(id) ON UPDATE CASCADE ON DELETE SET NULL

);

INSERT INTO student(name, age,gender, class_id) values('张三', 20, '男', 1);

INSERT INTO student(name, age,gender, class_id) values('李四', 21, '女', 3);

INSERT INTO student(name, age,gender, class_id) values('王五', 19, '女', 2);

INSERT INTO student(name, age,gender, class_id) values('赵六', 16, '男', 1);

INSERT INTO student(name, age,gender, class_id) values('宋七', 19, '女', 2);

INSERT INTO student(name, age,gender) values('康良', 19, '女');

-- 8.10 直接查询

-- 秩序在单表查询基础上增加一张表即可，返回的结果是多张表数据局条数的乘积

SELECT * FROM student, class WHERE student.class_id = class.id;

-- 8.2 连接查询

-- 8.2.1 内连接（INNER JOIN 或 JOIN）

-- 内连接的查询结果和和直接查询的结果是一样的

SELECT * FROM student JOIN class; -- SELECT * FROM student,class;

SELECT * FROM student JOIN class ON student.class_id = class.id;

SELECT student.id id,student.name,class.name,class.id cid FROM student JOIN class ON student.class_id = class.id;

-- 8.2.2 外连接查询

-- 8.2.2.1 左外连接查询（LEFT OUTER JOIN 或 LEFT JOIN）

SELECT * FROM student LEFT JOIN class on student.class_id = class.id;

-- 8.2.2.2 右外连接查询 （RIGHT OUTER JOIN 或 RIGHT JOIN）

SELECT * FROM student RIGHT JOIN class on student.class_id = class.id;

-- 8.3 UNION 查询

-- 在纵向上将多张表的查询结果拼接起来返回

-- 必须保证查询的多张表的字段是一致的

SELECT id,name FROM student UNION SELECT id,name from class;

-- 8.4 子查询

-- 8.4.1 将一个查询语句查询的结果作为另一个查询语句的条件来使用

SELECT name, id from class WHERE id = (SELECT class_id FROM student WHERE id = 3)

SELECT name, id from class WHERE id IN (SELECT class_id FROM student WHERE id >= 3)

-- 8.4.2 讲一个查询语句的结果作为另一个查询语句的表来使用

SELECT name from class WHERE id >= 2;

SELECT * FROM (SELECT name from class WHERE id >= 2) t; -- 必须给子查询起个别名

select class,avg(age) from student group by class;

111111biao