mysql + nodejs mysql篇（2）

关键字最好选择大写，可提高阅读性,和维护更直观
一般使用navcat可视化化工具创建表，后增删改查

创建键并进入数据库

let create_data_segi = `CREATE DATABASE 'Segi' IF NOT EXISTS
USE segi`

新建一个表，并声明列

let create_table_test = CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT `文章的id`,
  `create_time` datetime NOT NULL COMMENT '创建的时间',
  `title` varchar(255) NOT NULL COMMENT '标题名称',
  `view_number` int(11) NOT NULL DEFAULT '0' COMMENT '浏览次数',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='文章表' AUTO_INCREMENT=28 ;

增加数据

let insert_test = INSERT INTO `test` (`create_time`, `title`, `view_number`) values 
('2018-12-19 16:39:39', '你好世界', 5),
('2018-12-19 16:39:39', '你好世界2', 5);

删除数据

修改数据

查找分页

let result = {
  pageinator: {
    pageLength,
    pageNo
  }
}
let start = (pageNo - 1) * pageLength
let count = pageLength
let sql = `SELECT article.id  article_id, test.id test_id From article, test WHERE article.id = test.id AND artivle.status In(1) LIMIT ${start}, ${count}`

排序

// 升序  asc 
let sql1 = `SELECT * FROM user ORDER BY user_id ASC`
// 降序 desc
let sql2 = `SELECT * FROM user ORDER BY user_id DESC`

模糊查询

let sql = `SELECT article.name, test.title FROM article,test WHERE article.id=test.id AND article.name LIKE '%${title}%' AND status IN(1) LIMIT ${start}, ${count}`

分组查询

参考链接

// 查询各个部门最高薪资
let sql = `SELECT dept, MAX(SALARY) AS MAXIMUM FROM STAFF GROUP BY DEPT` 
//将 WHERE 子句与 GROUP BY 子句一起使用
//分组查询可以在形成组和计算列函数之前具有消除非限定行的标准 WHERE 子句。必须在GROUP BY 子句之前指定 WHERE 子句
let sql = `SELECT DEPT, EDLEVEL, MAX( SALARY ) AS MAXIMUM
FROM staff WHERE HIREDATE > '2010-01-01'
GROUP BY DEPT, EDLEVEL
ORDER BY DEPT, EDLEVEL`
// 可以在ground by 之后使用having，and，or来限定返回分组
// 此时返回是以组为单位整组过滤
let sql = `SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM
FROM staff
GROUP BY DEPT
HAVING COUNT( * ) >2
ORDER BY DEPT`

左联LEFT JOIN与 WHERE

左联的理解是，在没有匹配到值的时候也会放回null
where是过滤的意思，在没有匹配到值时会过滤掉
看如下代码

let create_table_user = `
CREATE TABLE IF NOT EXISTS 'user'(
'id' int(11) NOT NULL AUTO_INCREMENT COMMENT '用户的id',
'name' varchar(12)  NOT NULL COMMENT '用户名',
‘score’ varchar (3) NOT NULL COMMENT '分数'，
PRMARY KEY ('id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='用户表 AUTO_INCREMENT=28 ;
`
let create_table_city =   `
CREATE TABLE IF NOT EXISTS 'city'(
'id' int(11) NOT NULL AUTO_INCREMENT COMMENT '城市记录id',
'user_id' int(11)  NOT NULL COMMENT '用户id',
‘city’ varchar (3) COMMENT '用户城市'，
PRMARY KEY ('id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='用户城市表' AUTO_INCREMENT=28 ;
`
let insert_user = INSERT INTO user ('id','name','score') VALUES (1,'张三'，89)， (2,'李四'，88)， (3,'王五'，99),(4,'赵六'，99)；
let insert_city = INSERT INTO city ('id','user_id','city') VALUES (1，2，'湖南’)， (2，2，'上海')， (3,1,null)；

left join 查询（此时赵6也会返回,city为null）
let select_leftJoin = `SELECT id,name,city from user LEFT JOIN city on(user.id = city.user_id)`

where 查询 （此时赵6 不会会返回）
let select_where= `SELECT id,name,city from user ，city WHERE user.id=city.user_id`

Mysql中natural join和inner join的区别

nner join----
SELECT * FROM TableA INNER JOIN TableB USING (Column1)
SELECT * FROM TableA INNER JOIN TableB ON TableA.Column1 = TableB.Column1
natural join--它会去除重复的列名
SELECT * FROM TableA NATURAL JOIN Table

函数

1. count(*) / count(1) 总数在分页查询用的较多
2. 去重distinct(name)
3. IFNULL() 没有返回空，如下取第二高成绩人名 select IFNULL((select distinct(Salary) from Employee order by Salary desc limit 1,1),null) as SecondHighestSalary
4. sum( SALARY ) 求某一列的总和
5. MAX( SALARY ) 最大值
6. MIN( SALARY ) 最小值