mysql与msql2数据驱动

mysql基本使用

数据库操作（DDL）

-- 数据考操作
-- 1.查询所有数据库
SHOW DATABASES;

-- 2.选择数据库
USE learn_mysql;

-- 3.当前正在使用的数据库
SELECT DATABASE();

-- 4.创建数据库
CREATE DATABASE IF NOT EXISTS learn_mysql;

-- 5.删除数据库
DROP DATABASE IF EXISTS test_database;

-- 6.修改数据库
ALTER DATABASE test_database CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

表结构操作（DDL）

-- 表操作
-- 1.查询所有表
SHOW TABLES;

-- 2.查看表结构
DESC `user`;

-- 3.创建表
CREATE TABLE IF NOT EXISTS `user` (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(10) UNIQUE NOT NULL,
	age INT DEFAULT 1
);

-- 4.删除表
DROP TABLE IF EXISTS `user`;

-- 5.修改表
-- 5.1修改表名
ALTER TABLE `t_user` RENAME TO `user`;
-- 5.2增加表字段
ALTER TABLE `user` ADD createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
-- ALTER TABLE `user` ADD updateTime TIMESTAMP;
-- 修改表格数据后，根据当前时间戳更新updateTime
ALTER TABLE `user` ADD updateTime TIMESTAMP DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
-- 5.3修改表字段名及类型
ALTER TABLE `user` CHANGE createTime createAt DATETIME;
-- 5.4只修改表字段类型
ALTER TABLE `user` MODIFY id BIGINT AUTO_INCREMENT;
-- 5.5删除字段
ALTER TABLE `user` DROP createAt;

表数据操作（DML）

-- 1.向表增加数据
INSERT INTO `user` ( `name`, `age`) VALUES ('cjc', 100);
INSERT INTO `user` ( `name`, `age`) VALUES ('ccc', 999);
INSERT INTO `user` ( `name`, `age`) VALUES ('aaa', 111);

-- 2.删除数据
-- 删除所有数据
DELETE FROM `user`
DELETE FROM `user` WHERE `name` = 'aaa' 

-- 3.修改数据
UPDATE `user` SET `name` = 'CJC',`age` = 10000 WHERE `name` = 'cjc'
-- 修改数据时，手动加上时间的更新
UPDATE `user` SET `name` = 'CCC',`age` = 99999,`updateTime` = CURRENT_TIMESTAMP WHERE `name` = 'ccc'

-- 修改了数据，根据当前时间戳更新updateTime
ALTER TABLE `user` ADD `updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

表数据查询（DQL）

-- 1.条件查询
SELECT * FROM `user`
-- %任意字符0个或多个 _任意字符1个
SELECT `name` AS `user_name` FROM `user` WHERE `name` LIKE 'C%' 
-- 第二个字符为c
SELECT * FROM `user` WHERE `name` LIKE '_c%' 

-- 2.分页查询
-- 偏移1条数据后，查询前20条数据
SELECT * FROM `user` LIMIT 20 OFFSET 1

查询语句执行顺序

SELECT 
	tagname as "tag1",
	tagname2 as "tag2",
	[聚合函数]...
	
FROM table1
[LEFT] JOIN table2
on xxx
[LEFT] JOIN table3
on xxx
WHERE 不含聚合函数的条件
GROUP BY tag1,tag2...等所有非聚合函数字段
HAVING 含聚合函数的条件

ORDER BY tag1,tag2 DESC
LIMIT [偏移量],显示的记录数;  # LIMIT 显示的记录数 OFFSET 偏移量;

/*
    筛选 分组 查看 排序
	FROM  -> ON -> [left/right] join
	WHERE
	GROUP BY -> HAVING
	
	SELECT -> distinct
	
	ORDER BY  -> LIMIT 
*/

多表查询

多对多关系

-- 1.创建表
-- 学生表
CREATE TABLE IF NOT EXISTS `student` (
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(10) UNIQUE NOT NULL
);

-- 课程表
CREATE TABLE IF NOT EXISTS `course` (
	id INT PRIMARY KEY AUTO_INCREMENT,
	course_name VARCHAR(20) UNIQUE NOT NULL
);

-- 关系表
-- 多对多，一个学生能选择多门课程，一门课程被多个学生选择
CREATE TABLE IF NOT EXISTS `student_course` (
	id INT PRIMARY KEY AUTO_INCREMENT,
	-- 也可以使用联合主键	
	-- 	student_course_pk PRIMARY KEY(userId,hobbyId),
	
	-- 外键约束，更新/删除时，相关联的表也同步更新/删除	
	student_id INT NOT NULL,
	course_id INT NOT NULL,
	FOREIGN KEY (student_id) REFERENCES student(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (course_id) REFERENCES course(id) ON UPDATE CASCADE ON DELETE CASCADE,
	 
	-- 记录创建时间及更新时间	
	createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updateTime TIMESTAMP DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);


-- 2.插入数据
INSERT INTO `student` ( `name`) VALUES ('a');
INSERT INTO `student` ( `name`) VALUES ('b');
INSERT INTO `student` ( `name`) VALUES ('c');
INSERT INTO `student` ( `name`) VALUES ('d');

INSERT INTO `course` ( `course_name`) VALUES ('计算机网络');
INSERT INTO `course` ( `course_name`) VALUES ('数据结构');
INSERT INTO `course` ( `course_name`) VALUES ('操作系统');
INSERT INTO `course` ( `course_name`) VALUES ('计算机组成原理');
INSERT INTO `course` ( `course_name`) VALUES ('没人选的课程');

INSERT INTO `student_course` ( `student_id`,`course_id`) VALUES (1,1);
INSERT INTO `student_course` ( `student_id`,`course_id`) VALUES (2,1);
INSERT INTO `student_course` ( `student_id`,`course_id`) VALUES (2,2);
INSERT INTO `student_course` ( `student_id`,`course_id`) VALUES (3,1);
INSERT INTO `student_course` ( `student_id`,`course_id`) VALUES (3,2);
INSERT INTO `student_course` ( `student_id`,`course_id`) VALUES (3,3);
INSERT INTO `student_course` ( `student_id`,`course_id`) VALUES (4,1);
INSERT INTO `student_course` ( `student_id`,`course_id`) VALUES (4,2);
INSERT INTO `student_course` ( `student_id`,`course_id`) VALUES (4,3);
INSERT INTO `student_course` ( `student_id`,`course_id`) VALUES (4,4);


-- 3.多表查询
-- 所有学生的选课情况
SELECT student.`name`,course.course_name FROM `student`
LEFT JOIN `student_course` ON student.id = student_course.student_id
LEFT JOIN `course` ON course.id = student_course.course_id


SELECT student.`name`,COUNT(*) '课程数量' FROM `student`
LEFT JOIN `student_course` ON student.id = student_course.student_id
LEFT JOIN `course` ON course.id = student_course.course_id
GROUP BY student.`name`

将查询结果转化为JSON

将查询到的课程信息转化为json格式:

-- 使用聚合函数
JSON_OBJECT([key, val[, key, val] ...])

将查询结果转化为数组

-- 使用聚合函数
JSON_ARRAYAGG(col_or_expr)

mysql2数据库驱动

基本使用

const mysql = require('mysql2')

// 1.创建一个连接
const connect = mysql.createConnection({
  host: 'localhost',
  port: 13306,
  user: 'root',
  password: 'root',
  database: 'learn_mysql'
})

// 2.定义sql语句
const statement = 'SELECT * FROM `student`'

// 3.执行sql语句
connect.query(statement, (err, val, fields) => {
  if (err) {
    console.log(err);
    return
  }

  // 打印查询结果
  console.log(val);
})

预处理语句

1.提高性能
将语句模块发送给mysql编译、优化、转换，然后存储它但不执行。之后传入实参时，真正执行。多次执行，也只编译一次。

2.防止sql注入

const mysql = require('mysql2')

// 1.创建连接池
const connectionPool = mysql.createPool({
  host: 'localhost',
  port: 13306,
  user: 'root',
  password: 'root',
  database: 'koa-apis',
  connectionLimit: 5
})

// 2.测试是否连接成功
connectionPool.getConnection((err, connection) => {
  if (err) {
    console.log('数据库连接失败', err);
    return
  }

  connection.connect(err => {
    if (err) {
      console.log('和数据库交互成功', err);
    } else {
      console.log('和数据库交互成功');
    }
  })
})

// 3.定义预处理语句
// const statement = 'SELECT * FROM `student` WHERE id > 2 AND name LIKE "%c%"'
const statement = 'SELECT * FROM `student` WHERE id > ? AND name LIKE ?'

// 4.执行sql语句
// 使用promise语法
const connection = connectionPool.promise()
connection.execute(statement, [2, '%c%']).then(res => {
  const [val, fields] = res
  console.log(val);
}).catch(err => {
  console.log(err);
})