数据库之mysql #11

kekobin · 2019-07-24T13:28:38Z

概念

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

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;

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

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

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 数据类型

数值类型

日期和时间类型

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

字符串类型

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

INT(5) 和INT(10)区别

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

UNION 操作符

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

效率高的查询方式

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列上建立了一个联合索引，下图展示了该索引的存储结构。

以看到，联合索引中的索引项会先根据第一个索引列进行排序，第一个索引列相同的情况下，会再按照第二个索引列进行排序，依次类推。根据这种存储特点，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表的结构：

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

虽然这里的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 ;

两个查询结果一样

例子：

有一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;

The text was updated successfully, but these errors were encountered:

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

数据库之mysql #11

数据库之mysql #11

kekobin commented Jul 24, 2019 •

edited

Loading

数据库之mysql #11

数据库之mysql #11

Comments

kekobin commented Jul 24, 2019 • edited Loading

概念

安装

MAC OS（windows类似）

基本命令

基本操作

连接mysql服务器

查看数据库

创建数据库

删除数据库

连接数据库

数据类型

创建数据表

删除数据表

插入数据

查询数据

WHERE 子句

UPDATE 更新

DELETE 语句

LIKE 子句

修改表字段

添加表字段

添加索引

排序

GROUP BY 语句

NULL的处理

查看表的建表语句

正则表达式

处理重复数据

primary key VS unique

导入数据

node操作mysql

mysql.createConnection

mysql.createPool

添加白名单权限

SQL 注入

MySQL 数据类型

数值类型

日期和时间类型

字符串类型

INT(5) 和INT(10)区别

UNION 操作符

效率高的查询方式

索引

B-TREE索引

利用索引做排序

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

HAVING语法

kekobin commented Jul 24, 2019 •

edited

Loading