mysql function select 赋值_MySql 进阶

多表查询

修改表poetry_item，增加 user_id字段

mysql> ALTER table `poetry_item` ADD `user_id` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '用户id' after `type`;

mysql> INSERT INTO user_info (user_id, username, phone, gender, avatar, email, address, description) VALUES(9000, '李白', '16969819999', 1, 'https://default.png', 'libai@126.com', 'changan', '君不见黄河之水天上来，奔流到海不复回。...');...

mysql> UPDATE `poetry_item` SET `user_id`=9000 WHERE poetry_author='李白';...

Note 由于前期导入的数据都没有 user_id 字段，可能通过脚本的方式把 poetry_item 表有多少 poetry_author 作者，全部需要添加到 user_info 表中；

使用 node.js 去批量处理数据

const mysql = require("mysql");const Mock = require("mockjs");const pinyin = require("pinyin");const fs = require("fs");// node.js msyql 连接const connection = mysql.createConnection({  host: "localhost",  port: 3306,  user: "root",  password: "123456",  database: "shicimingju",});connection.connect();connection.query(  "SELECT DISTINCT poetry_author FROM poetry_item WHERE user_id IS NULL;",  function (error, results, fields) {    if (error) throw error;    for (let index = 0; index < results.length; index++) {      // 使用 Mockjs，模拟 phone 字段      // `npm install mockjs`      let phone = Mock.mock(/^1[385][1-9]\d{8}/);      let userId = 9000 + index;      let poetryNameCn = results[index].poetry_author;      let pinyinNames = pinyin(poetryNameCn, {        style: pinyin.STYLE_NORMAL,      });      let poetryNamePy = pinyinNames.join().replace(",", "");      connection.query(        `SELECT poetry_name FROM poetry_item WHERE poetry_author='${poetryNameCn}' LIMIT 0, 1;`,        function (error, results, fields) {          console.log(error, results, fields);          let poetryName = results[0].poetry_name;          // 插入Sql          // let insertSql = `INSERT INTO user_info (user_id, username, phone, gender, avatar, email, address, description) VALUES(${userId}, '${poetryNameCn}', '${phone}', 1, 'https://default.png', '${poetryNamePy}@gmail.com', '', '${poetryName}');\n`;          // fs.appendFileSync("./batch_insert.sql", insertSql);          // 更新Sql          // let updateSql = `UPDATE poetry_item SET user_id=${userId} WHERE poetry_author='${poetryNameCn}';\n`;          // fs.appendFileSync("./batch_update.sql", updateSql);          // 直接使用 mysql 连接更新          let insertSql = `INSERT INTO user_info (user_id, username, phone, gender, avatar, email, address, description) VALUES(${userId}, '${poetryNameCn}', '${phone}', 1, 'https://default.png', '${poetryNamePy}@gmail.com', '', '${poetryName}');`;          connection.query(insertSql);          let updateSql = `UPDATE poetry_item SET user_id=? WHERE poetry_author=?;`;          connection.query(updateSql, [userId, poetryNameCn]);        }      );    }  });// connection.end();

# 中间出现错误，清空表，再次重试mysql> truncate table <table_name>mysql> delete FROM <table_name>

上手操作

去重

# 当查询表中有很多字段重复了，可以使用 DISTINCT 去除重复值mysql> SELECT DISTINCT poetry_author FROM poetry_item WHERE user_id is null;

别名

# 不使用别名mysql> SELECT t1., t1., t2.FROM t1, t2 WHERE t1.=<value> AND t1.=<value>

# AS u 把 user_info 取别名mysql> SELECT * FROM user_info AS u WHERE username='李白';# 不用 AS 直接用空格隔开mysql> SELECT * FROM user_info u WHERE username='李白';# 只查询 username user_id phone 三个字段mysql> SELECT u.username, u.user_id, u.phone FROM user_info AS u WHERE username='李白';username  user_id  phone        --------  -------  -------------李白        9006     18663786831

IN

# IN (...) ()可以有多个值，用逗号隔开mysql> SELECT * FROM user_info WHERE user_id IN (9026, 9036)

BETWEEN ... AND

# SELECT  FROM  WHERE  BETWEEN value1 AND value2mysql> SELECT * FROM user_info WHERE user_id BETWEEN 9000 AND 9002;

排序 ORDER BY

# ORDER BY 排序关键字# ASC 升序 DESC 降序mysql> SELECT * FROM poetry_item WHERE poetry_author="李白" ORDER BY poetry_num ASC;

连表 JOIN

mysql> SELECT p.poetry_name, p.user_id, u.username FROM poetry_item p, user_info u WHERE u.user_id = p.user_id LIMIT 0, 2; poetry_name           user_id  username  --------------------  -------  ----------《关雎》                  9000     诗经    《蒹葭》                  9000     诗经

内连接(INNER JOIN)

# 语法 SELECT column_name(s) FROM table_name1 INNER JOIN table_name2  ON table_name1.column_name=table_name2.column_name# INNER JOIN 与 JOIN 是相同的mysql> SELECT * FROM poetry_item p INNER JOIN user_info u ON p.user_id=u.user_id AND poetry_author='江景' ORDER BY id DESC LIMIT 0, 10;

左连接(LEFT JOIN )

# 语法 SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2  ON table_name1.column_name=table_name2.column_name# LEFT JOIN 从左表 (table_name1) 返回所有的行，即使在右表 (table_name2) 中没有匹配的行mysql> SELECT * FROM poetry_item p LEFT JOIN user_info u ON p.user_id=u.user_id AND poetry_author='江景' ORDER BY id DESC LIMIT 0, 10;

右连接(RIGHT JOIN )

# 语法 SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name# RIGHT JOIN 从右表 (table_name2) 返回所有的行，即使在左表 (table_name1) 中没有匹配的行mysql> SELECT * FROM poetry_item p RIGHT JOIN user_info u ON p.user_id=u.user_id AND poetry_author='江景' ORDER BY id DESC LIMIT 0, 10;

全连接(FULL JOIN)

# 语法 SELECT column_name(s) FROM table_name2 FULL JOIN table_name1 ON table_name2.column_name=table_name1.column_name# FULL JOIN 关键字会从左表 (table_name1) 和右表 (table_name2)返回所有的行。如果 "table_name1" 中的行在表 "table_name2" 中没有匹配，或者如果 "table_name2" 中的行在表 "table_name1" 中没有匹配，这些行同样会被查询出mysql> SELECT * FROM poetry_item p FULL JOIN user_info u ON p.user_id=u.user_id AND poetry_author='江景' LIMIT 0, 10; # 不支持 FULL JOIN, 可以使用  UNION ALLmysql> SELECT * FROM poetry_item     LEFT JOIN user_info     ON poetry_item.user_id = user_info.user_idUNION ALL     SELECT *      FROM user_info     RIGHT JOIN poetry_item     ON user_info.user_id = poetry_item.user_id

多对多

多对多查询需要创建中间表进行关联

mysql> CREATE TABLE `role`(    `role_id` int primary key not null auto_increment comment '角色ID',  `role_name` varchar(20) not null COMMENT '角色名字',  `role_desc` varchar(500) not null COMMENT '角色描述') ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT '角色表';mysql> insert into `role` (`role_name`, `role_desc`) values("诗人","诗人，就一般意义来讲，通常是指写诗的人，但从文学概念上讲，则应是在诗歌(诗词)创作上有一定成就的写诗的人和诗作家。");mysql> insert into `role` (`role_name`, `role_desc`) values("词人","词，是中国古代诗歌的一种，始于梁代，形成于唐代而极盛于宋代，故名“宋词”。宋词是中国古代文学皇冠上光辉夺目的巨钻，历来与唐诗并称 “双绝”。");mysql> insert into `role` (`role_name`, `role_desc`) values("剑客","精于剑术的人；剑侠。");

mysql> CREATE TABLE `user_role_rela` (  `urr_id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '用户角色关联ID',  `user_id` INT NOT NULL COMMENT '用户ID',  `role_id` INT NOT NULL COMMENT '角色ID') ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户角色关联表';mysql> INSERT INTO `user_role_rela` (`user_id`, `role_id`) VALUES(9006, 1);mysql> INSERT INTO `user_role_rela` (`user_id`, `role_id`) VALUES(9006, 3);

mysql> SELECT u.`username`, r.`role_name`, r.`role_desc` FROM user_info u, role r, user_role_rela urr WHERE u.`username`="李白" AND u.user_id=urr.`user_id` AND r.`role_id` = urr.`role_id`;