#注释 --注释 --查询所有 大小写不敏感 SELECT * FROM user --查询username和--password select username,password from user
--插入语句 insert into user(username,password) values('laoli','123') --一次插入多条数据 insert into user(username,password) values('laoli','123'),('laozhang','234')
--更新语句 update user set username = 'dalaoli' where id = 1 --更新多个字段 update user set username = 'dalao',password = '666666' where id = 3 --注：不加where条件更新所有数据 update user set password = '999999' --username设置了不能重复所以没加条件就只能给第一个更新数据 update user set username = '999999'
--删除语句 delete from user where id = '3' 注：不加条件是删除整张表
--where条件语句 --条件id大于2 select * from user where id > 2 --条件id为1 update user set password = '222' where id = 1 --删除id为6 delete from user where id = 5 --多个条件 --同时满足and 满足其中一个or --同时满足id大于2且username不等于zs；两者全满足 select * from user where id > 2 and username <> 'zs' --满足status等于0或者username不为fdfd;两者满足一个就可 select * from user where status = 0 or username <> 'fdfd'
--排序 对 结果集 按照指点的字段升序或者降序默认升序 不写asc升序、desc降序；就默认是升序 select * from user order by id select * from user order by id desc select * from user where id > 2 order by status
--多重排序;逗号相隔， select * from user order by status desc,id desc
--计算查询结果集中的总条数 select count() from user 总条数结果集字段起别名、不叫count() select count(*) as total from user
select username as uname,password as pwd from user
-------js操作数据库 const mysql = require('mysql') const db = mysql.createPool({ host: '127.0.0.1',//数据库所在主机；ip地址 user: 'root',//数据库用户名 password: 'root',//数据库密码 database: 'my_db_01',//数据库名称 })
db.query('select 1',(err,results) => { if(err) return console.log(err.message); console.log(results); })
第二个参数放数据 db.query('select 1',[],(err,results) => { if(err) return console.log(err.message); console.log(results); }) 查询 const sqlStr = 'select * from user where id = ? and username=?' 查询+占位符 const sqlStr = 'insert into user(username,password) values(?,?)' 查询的便捷写法 // 第一步准备数据对象；属性名和字段名保持一致 const user = { username: 'insert', password: 'eee' } // 第二步insert语句的便捷写法 const sqlStr = 'insert into user set ?'
update的便捷写法 const sqlStr = 'update user set username = ?,password = ? where id = 1' const sqlStr = 'update user set ? where id = ?'
删除 const sqlStr = 'delete from user where id = ?' // 当占位符只有一个时数组可以省略直接写数据；
