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
函数
count(*) / count(1)
总数在分页查询用的较多- 去重
distinct(name)
- IFNULL() 没有返回空,如下取第二高成绩人名
select IFNULL((select distinct(Salary) from Employee order by Salary desc limit 1,1),null) as SecondHighestSalary
- sum( SALARY ) 求某一列的总和
- MAX( SALARY ) 最大值
- MIN( SALARY ) 最小值