[p2]mysql基础实践

介绍

mysql, 关系型数据库。

1. 语法

操作库

• 创建 CREATE DATABASE test;
• 删除 DROP DATABASE test;
• 修改
• 查询 SHOW DATABASES;
• 切换 USE test;

操作表

• 创建
• 查看 SHOW TABLES;
• 查看（结构）DESC students;
• 查看（语句）SHOW CREATE TABLE students;
• 删除 DROP TABLE students;

操作条目

• 插入 INSERT INTO
• 删除 DELETE FROM — WHERE id=—;
• 修改 UPDATE — SET — WHERE id=—;
• 查询 SELECT * FROM —;

2. 业务

数据库搭建

DOS模式

EXIT // 退出

安装

参见： https://www.runoob.com/mysql/mysql-install.html

查看

注意， 必须以分号结尾

版本

mysqladmin --version

端口

mysql -u root -p
show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

用户

select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

查看数据库

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| book               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

查询数据

use book;
select * from book_list;
+----+-----------+-----------------+------------+-------+
| id | name      | author          | date       | price |
+----+-----------+-----------------+------------+-------+
|  1 | 小王子    | 埃克苏佩里      | 1941-10-01 |   100 |
+----+-----------+-----------------+------------+-------+
1 row in set (0.00 sec)

命令行操作

连接

连接数据库 mysql -u root -p

创建数据库

CREATE DATABASE BOOK;

创建数据表

CREATE TABLE IF NOT EXISTS `book_list`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   `author` VARCHAR(40) NOT NULL,
   `date` DATE,
   `price` FLOAT,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO book_list ( name, author, date, price)
                       VALUES
                       ( '小王子', '埃克苏佩里', '1941-10-1', 100 );

nodejs操作数据库

nodejs 连接

var mysql = require('mysql')
var co = mysql.createConnection({
    host: '119.45.34.210',
    user: 'root',
    password: '123456',
    port: '3306',
    database: 'book'
})
co.connect();
co.query('SELECT * FROM book_list', (err, result, filelds) => {
    // console.log(err, result, filelds)
    if (err) throw err
    console.log('book_list:', result.length, result)
});
co.end((err) => {
    if (err) throw err;
    console.log('[connenct end] succeed!')
});

增删改查

const addSql = `INSERT INTO book_list ( name, author, date, price)
                       VALUES
                       ( ?, ?, ?, ? );`;
const addSqlParams = ['钢铁是怎样练成的', '高尔基', '1989-10-23', 65];
co.query(addSql, addSqlParams, (err, result, filelds) => {});
const updateSql = 'UPDATE book_list SET price = ? where id = ?';
const updateSqlParams = [25, 2];