mysql

初体验

增

create database testdb; 创建数据库

INSERT INTO students(id,name) VALUES (5,"lisi");向表格内插入数据

删

drop database testdb; 删除数据库

drop table students; 删除数据表

update students set isdel = 'true' where id = 1;逻辑删除字段

delete from price where id = 2；物理删除字段

改

use testdb; 使用testdb数据库

update teacher set sex = 0,subject = 'JavaScript' where id = 1 ； 更新id为1的老师表数据 修改值为sex=0,subject='javascript'

查

show databases; --查看所有数据库

select * from students; --查询数据学生表

select * from book inner join author on book.authorId = author.id where author.name = '郭敬明'; --连表查询 关联book表和author表 过滤出作者为郭敬明的数据

select * from author left join book on book.authorId = author.id ；-- 连表查询 以左边为主查询 没有的字段以null填充

select area.id,area.pid,area.name from address as city inner join address as area on city.id = area.pid where area.name in ( select name from address where pid = 2)

--子查询 先查询后边条件 然后再判断where

视图

create view testView as select * from book ；--根据查询出来的数据创建新视图，视图的查询跟表是相同的

范式

原子性（不可再分）

没有部分依赖

事务

一起提交 出错则停止

begin; insert into students (name,age) values ('小起',20); insert into teacher (name,sex,subject) values ('丛老师',0,'业务'); commit;

 

node使用

 

var mysql = require('mysql');
​
const option = {
    host     : 'localhost',
    user     : 'root',
    password : '123456',
    database : 'testdb'
}
const connection = mysql.createConnection(option)
//连接
connection.connect((err)=>{
    if(err) throw err
})
//查询
connection.query('select * from students',(err,data,file)=>{
    if(err) throw err
    console.log(data);
    // console.log(file);
})
​
//动态增加
var query = "INSERT INTO stu(name,age) VALUES (?,?) "
connection.query(query,['lisi',18],(err,data,file)=>{
    if(err) throw err
    console.log(data);
    // console.log(file);
})