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] joinWHEREGROUP BY -> HAVINGSELECT -> distinctORDER 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_idSELECT 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] = resconsole.log(val);
}).catch(err => {console.log(err);
})