kekobin/blog

数据库之mysql

Opened this issue · 0 comments

kekobin commented

概念

MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:
image

1.主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
2.外键：外键用于关联两个表。
3.复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
4.索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

安装

MAC OS（windows类似）

下载网站：https://dev.mysql.com/downloads/mysql/， 默认安装路径: /usr/local/mysql/bin/mysql
MAMP：https://www.mamp.info/en/downloads/， 默认安装路径：/Application/MAMP/mysql/bin/mysql

可能的问题(Mac)：
安装完后终端输入: mysql提示未找到命令.说明未配置到全局环境变量中，要手动配置.
解决: ln -s 库安装目录bin路径 /usr/bin,如: ln -s /usr/local/mysql/bin/mysql /usr/bin
如果还是不行，接着使用:alias mysql=/usr/local/mysql/bin/mysql

对于实际开发来说，在发布到正式时，一般需要先手动进行一些数据库的操作，比如:
mysql -h103.211.111.55 -P3306 -uroot -ppassword database_name
通过'mysql -hmysql服务器ip -Pmysql服务器端口 -umysql服务器用户 -pmysql服务器密码 数据库> > 名'，链接上mysql，然后进行一些前提数据的插入

基本命令

  • 查询有哪些数据库：show databases;
  • 更换当前使用的数据库：use databaseName;
  • 返回当前数据库下的所有表的名称：show tables;
  • 查看表结构：desc 表名;

基本操作

连接mysql服务器

mysql -u root -p

查看数据库

show databases;

创建数据库

CREATE DATABASE 数据库名;

删除数据库

drop database <数据库名>;

连接数据库

mysql -u root -p 
连接mysql后，然后:
use 数据库名

所有的数据库名，表名，表字段都是区分大小写的。所以你在使用SQL命令时需要输入正确的名称。

数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

常用的:

  • 数值型：INT、BIGINT、FLOAT
  • 日期和时间类型：DATE、DATETIME
  • 字符串类型：CHAR、VARCHAR

创建数据表

创建一个数据表，需要有：表名、表字段名、定义每个表字段

CREATE TABLE table_name (column_name column_type);

一个完整例子如下：

CREATE TABLE IF NOT EXISTS `test_table`(
   `table_id` INT UNSIGNED AUTO_INCREMENT,
   `table_title` VARCHAR(100) NOT NULL,
   `table_author` VARCHAR(40) NOT NULL,
   `table_date` DATE,
   PRIMARY KEY ( `table_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

在操作数据库时如果输入该字段的数据为NULL ，就会报错，所以不想为NULL时，可以设置为NOT NULL。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码(现在一般设置成utf8mb4，已支持emoji表情存储)。

删除数据表

DROP TABLE table_name ;

插入数据

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

具体实例如:

INSERT INTO test_table ( table_id, table_title,table_author,table_date )
   VALUES
   ( 123, '表头','kebin', '2019-06-30' );

查询数据

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

如:

select * from test_table;

WHERE 子句

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。 如:

select * from test_table where table_author='kebin1';

UPDATE 更新

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

如:

update test_table set table_title='表头1_1' where table_id=124;

DELETE 语句

DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。

LIKE 子句

SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

如:

select * from test_table where table_date like '2019%';
  • 模糊查询一般只能查从库，因为这类查询容易导致db满查询。

修改表字段

ALTER TABLE account MODIFY Account_name VARCHAR(20) NOT NULL;

添加表字段

alter table `MyClass` add column `passtest` int(4) default '0';

添加索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

排序

可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。默认生序.

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

如:

select * from test_table order by table_id desc;

GROUP BY 语句

根据一个或多个列对结果集进行分组(即列的值有多少种类)。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

image

1.使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：
image

2.使用 WITH ROLLUP
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：
image

NULL的处理

查找数据表中某一 列是否为 NULL，必须使用 IS NULL 和 IS NOT NULL:

SELECT * FROM test_table WHERE table_author IS NULL;

查看表的建表语句

SHOW CREATE TABLE table_name;

正则表达式

SELECT table_author FROM test_table WHERE name REGEXP '^bin';       
SELECT * FROM test_table WHERE path REGEXP '^/[^/]+$'

上面第二个是从test_table中查找以‘/’开头，到结尾，中间都没有'/'的path，如：可以查到/test.png,
查不到/test/test.png

处理重复数据

可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。否则默认会允许出现多条重复记录.

CREATE TABLE test_table
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

如上设置表中字段 first_name，last_name 数据不能重复，你可以设置双主键模式来设置数据的唯一性， 如果你设置了双主键，那么那个键的默认值不能为 NULL，可设置为 NOT NULL。

另一种设置数据的唯一性方法是添加一个 UNIQUE 索引：

CREATE TABLE test_table
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   UNIQUE (last_name, first_name)
);

primary key VS unique

一个表只能有一个主键，但是可以有好多个UNIQUE，而且UNIQUE可以为NULL值，如员工的电话号码一般就用UNIQUE，因为电话号码肯定是唯一的，但是有的员工可能没有电话。

primary key = unique +  not null

unique 就是唯一，当你需要限定你的某个表字段每个值都唯一,没有重复值时使用。比如说,如果你有一个person 表，并且表中有个身份证的column，那么你就可以指定该字段为unique。 从技术的角度来看，Primary Key和Unique Key有很多相似之处。但还是有以下区别： 
1.作为Primary Key的域/域组不能为null，而Unique Key可以。
2.在一个表中只能有一个Primary Key，而多个Unique Key可以同时存在。

导入数据

很多时候我们在开发机开发好了，等要部署到线上机时，又要做一次建表操作，所以最好是一开始就通过图形化工具(如phpadmin等)建立好数据库和表，然后导出sql文件，这样，在程序中启动时直接导入这个sql文件即导入刘数据库。

mysql -u用户名    -p密码    <  要导入的数据库数据(test.sql)

node操作mysql

mysql.createConnection

const mysql = require('mysql')

// 连接 mysql 服务器
const connection = mysql.createConnection({
    host: 'localhost',
    port: '3306',
    user: 'root',
    password: ''
})

// 执行SQL
connection.query(sql, function (err, result) {
})

// 销毁连接
connection.destroy()

mysql.createPool

用 createConnection 创建 Mysql 连接，每执行一次 connection.query 都是一个全新的连接，会造成一个资源的极大浪费，降低性能。

连接池是另外的一种执行方法，它一次性的创建了多个连接，然后根据客户端的查询，自动的 分发、复用、管理 这些连接。

const mysql = require('mysql')

// 链接池：创建多个链接、复用与分发链接
const pool = mysql.createPool({
  host: 'localhost',
  port: '3306',
  user: 'root',
  password: ''
})

// 封装
query = function(sql,callback){  
  pool.getConnection(function(err,connection){  
    connection.query(sql,function(err,results){  
      callback(err, results) // 结果回调
        connection.release() // 释放连接资源 | 跟 connection.destroy() 不同，它是销毁
     })
    }  
  })
}

// 随机分配一个连接
pool.query(sql, function (err, result) {
  // ...
}

更优雅的封装：

const mysql = require('mysql');
const MYSQL_CONFIG = {
  user: 'root',
  database: 'test_db', //需要先在mysql中创建这个数据库
  host,
  port,
  password,
  multipleStatements: true//允许多条sql同时执行
};

// 创建连接池
const pool = mysql.createPool(MYSQL_CONFIG);

const query = (sql, values) => {
  return new Promise((resolve, reject) => {
    pool.getConnection((err, connection) => {
      if (err) {
        reject(err);
      } else {
        connection.query(sql, values, (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve(rows);
          }
          connection.release();
        })
      }
    })
  })
};

module.exports = {
  query
}

对于query的用法，现实中容易踩坑，因为sql的语法很容易出问题，正确的打开方式如下:

//1 插入     
const sql = `INSERT INTO table1(a1, a2, a3) VALUES (?,?,?)`
const value = [1,2,3]    
//2 更新     
const sql = `UPDATE table1 set a1=? WHERE a2=? AND a3=?`     
const value = [1,2,3]    
//3 查找    
const sql = `SELECT * FROM table1 WHERE a1=(?)`
const value = [1]    

// 然后统一使用     
db.query(sql, value)

添加白名单权限

// 先linux登录mysql，然后
admin_add redip 'xxx.xxx.xxx.xxx';

SQL 注入

在写 SQL 语句的时间尽量不要使用 SQL 拼装，因为很容易被 SQL注入，从而引发安全问题，如果数据和 SQL 语句需要分离，那么请使用 占位符 的方式。

connection.query("select * from users where id = ? and name = ?", [1, 'jmjc'], (err, result)=>{}) // 这种方式 mysql 模块内部会调用 escape 方法，过滤掉一些非法的操作

/*
  当前我们也可以自己使用 escape 方法
*/
connection.query('select * from users where id = ' + connection.escape(userId), (err, result) => {})

/*
 或者 format 方法
*/
const sql = "select * from ?? where ?? = ?"
const inserts = ['users', 'id', 1]
sql = mysql.format(sql, inserts) // select * from users where id = 1

nodejs sequelize库防注入测试

MySQL 数据类型

数值类型

image

日期和时间类型

虽然可以直接存，但是一般实际业务中存的是时间戳(INTEGER(11))。
image

字符串类型

char(n) 和 varchar(n) 中括号中 **n 代表字符的个数，并不代表字节个数，**比如 CHAR(30) 就可以存储 30 个字符。
image

INT(5) 和INT(10)区别

其实没啥区别，能存的位数是一样：最大10位，里面的5和10指的是数据库中显示的位数。如INT(5)，如果你存的是12，则显示成00012，即不足5位的用0填充：
image

UNION 操作符

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

image

效率高的查询方式

1.查询大数据时使用：主键偏移

select xx,xx from table where id > lastId limit 1000 order by id asc

lastId一开始为0，然后查到一批后，里面最大的id最为下一批的lastId

索引

索引(key)是存储引擎用于快速找到记录的一种数据结构。它和一本书中目录的工作方式类似——当要查找一行记录时，先在索引中快速找到行所在的位置信息，然后再直接获取到那行记录。
在MySql中，索引是在存储引擎层而不是服务器层实现的，所以不同的存储引擎对索引的实现和支持都不相同。

B-TREE索引

B-TREE索引是使用最多的索引，InnoDB则按照原数据格式进行存储。
下面是一个使用B-Tree索引的例子，有如下数据表：

CREATE TABLE People (
    last_name varchar(50) not null,
    first_name varchar(50) not null,
    dob date not null,
    gender enum('m','f') not null,
    key(last_name,first_name,dob)
)

这个建表语句在last_name、first_name、dob列上建立了一个联合索引，下图展示了该索引的存储结构。

image
以看到，联合索引中的索引项会先根据第一个索引列进行排序，第一个索引列相同的情况下，会再按照第二个索引列进行排序，依次类推。根据这种存储特点，B-Tree索引对如下类型的查找有效：

  • 全值匹配：查找条件和索引中的所有列相匹配

  • 匹配最左前缀：查找条件只有索引中的第一列

  • 匹配列前缀：只匹配某一列值的开头部分。这里并不一定只能匹配第一个索引列的前缀。例如在确定第一个索引列的值时，也可以在第二个索引列上匹配列前缀。在上面例子中，对于查找姓为Allen，名为J开头的人，也可以应用到索引。

  • 匹配范围值，或者精确匹配某一列并范围匹配另外一列：例如查找姓在Allen和Barrymore之间的人，或者查找姓为Allen，名字在某一个范围内的人。

  • 只访问索引的查询，即要查询的值在索引中都包含，只需要访问索引就行了，无需访问数据行。这种索引被称作覆盖索引。

  • 对于上面列出的查询类型，索引除了可以用来查询外，还可以用来排序。

下面是B-Tree索引的一些限制：

  • 如果不是从索引的最左列开始查找，则无法使用索引。例如直接查找名字为Bill的人，或查找某个生日的人都无法应用到上面的索引，因为都跳过了索引的第一个列。此外查找姓以某个字母结尾的人，也无法使用到上面的索引。

  • 不能在中间跳过索引中的某个列，例如不能查找姓为Smith，生日为某个特定日期的类。这样的查询只能使用到索引的第一列。

  • 如果查询中有某个列的范围查询，则该列右边的所有列都无法使用索引优化查找。例如有查询WHERE last_name='Smith' AND first_name LIKE 'J%' AND dob='1976-12-23'，这个查询只能使用到索引的前两列，而不能使用整个索引。

联合索引列的顺序
将选择性最高的列放到索引的最前列虽然是一条重要的参考准则，但通常不如避免随机IO和排序那么重要。所以在设计索引时，还要考虑到WHERE子句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能造成非常大的影响。

利用索引做排序

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能使用索引对结果进行排序。一般情况ORDER BY子句和查找型查询的限制是一样的，都需要满足索引的最左前缀要求。但有一种特殊情况，如果在WHERE子句或JOIN子句中对索引前导列指定了常量，则order by子句可以不满足索引的最左前缀的要求。
在表rental中，有一个在列(rental_date,inventory_id,customer_id)上建立的联合索引rental_date，下图描述了rental表的结构：

image
可以利用rental_date索引为下面的查询做排序:
image

虽然这里的order by子句不满足索引的最左前缀的要求，但由于在WHERE子句中指定了索引rental_date的第一列为一个常量，所以仍然可以用于排序。

下面这个查询也可以利用rental_date索引进行排序，因为ORDER BY子句中使用的两列就是索引的最左前缀。

. . .WHERE rental_date>'2005-05-25' ORDER BY rental_date,inventory_id;

MySQL 索引类型

  • primary / unique 唯一性索引，二者略有区别
  • key/index 最常见的索引，非唯一性
  • fulltext 全文索引

常见的索引案例

where a=xx order by b

推荐 (a,b) 组合索引

//联合索引： KEY `key1` (a,b,c,id)
select * from table_name where a = 1 and b = 2 and c = 3 and id > 10

这里面id在where子句中的顺序不会影响整个的速度，只要where子句中的条件个数，跟联合索引的个数保持一致就行。而且前面的几个索引是常量的情况下，默认会按照最后一个id进行排序。

mysql使用存储过程插入千万条测试数据

例子：创建100万

CREATE TABLE `data` 
(
  `id`         bigint(20) NOT NULL      AUTO_INCREMENT,
  `datetime`   timestamp  NULL          DEFAULT CURRENT_TIMESTAMP,
  `channel`    int(11)                  DEFAULT NULL,
  `value`      float                    DEFAULT NULL,

  PRIMARY KEY (`id`)
);

DELIMITER $$
CREATE PROCEDURE generate_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 1000000 DO
    INSERT INTO `data` (`datetime`,`value`,`channel`) VALUES (
      FROM_UNIXTIME(UNIX_TIMESTAMP('2014-01-01 01:00:00')+FLOOR(RAND()*31536000)),
      ROUND(RAND()*100,2),
      1
    );
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

CALL generate_data();

HAVING语法

having字句可以让我们筛选分组之后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。
而having子句在聚合后对组记录进行筛选。我的理解就是真实表中没有此数据，这些数据是通过一些函数产生的。
一、显示每个地区的总人口数和总面积．

SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region

先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中
的不同字段（一或多条记录）作运算。

二、 显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。

SELECT region, SUM(population), SUM(area)       
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000

在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。
相反，having子句可以让我们筛选分组后的各组数据

三、 having单独使用，与where类似
eg: 查询单笔订单充值金额大于1000的

SELECT regagent,amount FROM `cy_pay_ok`  having amount>1000 ;
SELECT regagent,amount FROM `cy_pay_ok`  where amount>1000 ;

两个查询结果一样

例子：
image

有一student表，要求查询出挂科数目多于两门（包含两门）的前两名学生的姓名，如果挂科数目相同按学生姓名升序排列。
实现步骤：
1.查询出挂科的学生姓名：

select name from student where grade < 60;

2.在上面的基础上，查询挂科多余两门的学生(即需要对挂科的学生结果进行分组，然后每组计算出name的数量)

select name, COUNT(name) AS num from student where grade < 60 GROUP BY name

然后针对学生数量通过HAVING处理多于两门这个条件

select name, COUNT(name) AS num from student where grade < 60 GROUP BY name HAVING num >= 2;

3.排序，取前两名

select name, COUNT(name) AS num from student where grade < 60 GROUP BY name HAVING num >= 2 ORDER BY num DWSC, name ASC limit 0, 2;