sql_study_05

第五部分：脱颖而出——数据类型、设计数据库、索引、保护

数据类型

其他的基本的就不过多介绍了，都是常用的，用过就知道了。

1. JSON

   1. SET JSON

      直接set列名为JSON字符串

      -- SET JSON
      -- 直接赋值
      UPDATE products SET properties = '{
          "dimensions" : [1,2,3],
          "weight" : 10,
          "manufacture" : {
              "name" : "sony"
          }
      }'WHERE product_id = 1;
      

      使用MySQL自带的函数

      -- 使用MySQL自带的函数
      UPDATE products
      SET properties = JSON_OBJECT(
          'dimension',JSON_ARRAY(1,2,3),
          'weight',10,
          'manufacture',JSON_OBJECT(
              'name','apple'
              )
          )
      WHERE product_id = 2;
      

      结果：

      

   2. 读取JSON中的属性值

      1. JSON_EXTRACT

         JSON_EXTRACT(列名,'属性路径：$.manufacture.nam{e}^{\prime })，$为当前的JSON

         -- 读取JSON中的属性值
         -- $表示的当前JSON文档
         SELECT
             name,
             JSON_EXTRACT(properties,'$.manufacture.name') AS manufacture
         FROM products
         WHERE product_id = 1;
         

         结果：

         

      2. 使用列路径运算符

         ->

         -- ->
         SELECT
             name,
             properties->'$.dimensions[0]' AS dimension
         FROM products
         WHERE product_id = 1;
         

         结果：

         

         ->> 可以获取字符串的内容，如果使用->会带引号"apple"

         -- ->>
         SELECT
             name,
             properties->>'$.manufacture.name' AS manufacture
         FROM products
         WHERE properties->>'$.manufacture.name' = 'apple';
         

         结果：

         

   3. 修改JSON中的属性值

      1. JSON_SET

         JSON_SET(列名 , ‘字段对应的JSON路径表达式’ , ‘要修改的值’)；有就更新，没有就新增

         -- JSON_SET
         -- 已有的属性修改，没有的新增
         UPDATE products
         SET properties = JSON_SET(
             properties,
             '$.age',10,
             '$.weight',30
         )
         WHERE product_id = 1;
         

         结果：

         

      2. JSON_REMOVE

         JSON_REMOVE(列名 , ‘JSON字段对应的JSON路径表达式’)

         -- JSON_REMOVE
         UPDATE products
         SET properties = JSON_REMOVE(
             properties,
             '$.age'
         )
         WHERE product_id = 1;
         

         结果：

         

设计数据库

设计规范，看看就行了，不用刻意遵守，为了满足规范而设计数据库表，还是那句好能满足需求就可以了，程序和你有一个能跑就行了

-- 第一范式，每一行数据中的每一列都有唯一的值，不允许有重复的列
-- 第二范式, 一张表中的每一列都在描述该表代表的实体
-- 第三范式, 表中的列不应派生自其他列

一些DDL SQL语句

-- CREATE DATABASE
CREATE DATABASE IF NOT EXISTS sql_store2;
-- DROP DATABASE
DROP DATABASE IF EXISTS sql_store2;
-- CREATE TABLE
USE sql_store2;

-- DROP TABLE
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customer;

CREATE TABLE IF NOT EXISTS customer(
  -- 表名（列名, 类型名, 约束名）
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL ,
    points INT DEFAULT 0 NOT NULL ,
    email VARCHAR(255) UNIQUE NOT NULL
);
-- ALTER TABLE
ALTER TABLE customer
    ADD COLUMN last_name VARCHAR(50) NOT NULL AFTER first_name,
    MODIFY COLUMN first_name VARCHAR(50) DEFAULT '',
    DROP COLUMN points;

-- 添加外键约束
CREATE TABLE IF NOT EXISTS orders(
    order_id INT PRIMARY KEY ,
    customer_id INT NOT NULL ,
        CONSTRAINT fk_orders_customers FOREIGN KEY(customer_id)
        REFERENCES customer(customer_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

-- 更改主键和外键约束
-- 所有语句都写在一个alter里面会出现操作顺序问题
-- [HY000][1826] Duplicate foreign key constraint name 'fk_orders_customers'
ALTER TABLE orders
    DROP PRIMARY KEY ,
    ADD PRIMARY KEY (order_id),
    DROP FOREIGN KEY fk_orders_customers,
    ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id)
        REFERENCES customer(customer_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT;
        
-- 分开写没什么问题
ALTER TABLE orders
    DROP FOREIGN KEY fk_orders_customers;
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id)
        REFERENCES customer(customer_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT;
        
-- 修改字符集
-- 在数据库级别上修改
CREATE DATABASE db_name
    CHARACTER SET latin1;
    
-- 在表级别上修改
ALTER TABLE orders
    CHARACTER SET latin1;
    
-- 在特定列上修改字符集
CREATE TABLE IF NOT EXISTS customer(
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) CHARACTER SET latin1 NOT NULL ,
    points INT DEFAULT 0 NOT NULL ,
    email VARCHAR(255) UNIQUE NOT NULL
);

-- 在表中修改存储引擎
-- 5之前是MyISAM 不支持事务，不支持外键约束,如级联删除或更新;使用表级锁试用于多读,在对表进行操作时会锁定整张表，因此在写操作频繁时可能会造成性能瓶颈，适合读操作较多的应用场景。
-- 8之后是InnoDB支持事务；使用行级锁适用于多写,允许在高并发的情况下进行更细粒度的控制。多个事务可以并发地操作同一张表中的不同行，而不互相阻塞，适合写操作较多的应用场景。
ALTER TABLE orders
ENGINE = InnoDB;

索引

1. EXPLAIN

   帮助我们了解查询的执行过程，方便看到使用的索引和预估要查询查询的行数

   -- EXPLAIN
   EXPLAIN SELECT customer_id FROM customers WHERE state = 'CA';
   

   结果:

   

2. CREATE INDEX

   -- CREATE INDEX '索引名' ON 表名（列名）
   CREATE INDEX idx_state ON customers(state);
   

3. SHOW INDEX

   -- SHOW INDEX
   SHOW INDEXES IN customers;
   ANALYZE TABLE customers; -- 更新表结构
   SHOW INDEXES IN customers;
   

   结果：

   

   除主键外的其他都是二级索引，每一个二级索引都包含了customer_id，查找是先去查找二级索引对应的customer_id，然后通过customer_id在聚集索引（一级索引）找到对应的行

4. 前缀索引

   针对一些长文本内容，截取前一部分字符串作为索引，减少索引大小的同时，保持唯一性

   -- 前缀索引
   -- 针对一些长文本内容，截取前部分字符串作为索引，减少索引大小的同时，保持其唯一性
   CREATE INDEX idx_lastname ON customers(last_name(20));
   

   判断最佳索引----增加长度，判断其唯一性

   -- 判断最佳索引
   SELECT
      COUNT(DISTINCT LEFT(last_name,1)), -- 25
      COUNT(DISTINCT LEFT(last_name,5)) , -- 966 5为最佳索引长度
      COUNT(DISTINCT LEFT(last_name,10)) -- 996
   FROM customers;
   

5. 全局索引

   用于检测一些比较长度字符串，常用于全文的索引，和搜索引擎的搜索优化

   1. CREATE FULLTEXT INDEX

      USE sql_blog;
      -- 全局索引
      CREATE FULLTEXT INDEX idx_title_body ON posts(title,body);
      

   2. MATCH(列名1, 列名2) AGAINST(‘指定要搜索的关键字’ [字符串模式] )

      默认是自然语言模式

      1. 自动分词：MySQL会自动将输入的搜索关键字分成多个词语，并在指定的全文索引列中搜索这些词。
      2. 忽略停用词：在搜索中会忽略停用词（the , is , and等）。
      3. 相关性排序：MATCH子句返回的就是相关系数。
      4. 支持复合搜索：如果搜索词中包含多个单词，MySQL 会搜索包含这两个单词的记录，包含一个的也会查出来，并按相关性进行排序。

      -- MATCH全文检索(要检索的列名)
      -- AGAINST 指定搜索的关键字
      -- AGAINST('react redux) 自然语言模式 结果会按照相关性排序
      SELECT *,MATCH(title,body) AGAINST('react redux')
      FROM posts WHERE MATCH(title,body) AGAINST('react redux');
      

      

      布尔模式

      1. AGAINST 布尔模式，允许使用特殊字符来控制搜索行为 + - *，+必须包含，-不包含，*类似%

         -- AGAINST 布尔模式，允许使用特殊字符来控制搜索行为 + - *
         -- +必须包含，-不包含，*类似%
         SELECT *,MATCH(title,body) AGAINST('react redux')
         FROM posts WHERE MATCH(title,body) AGAINST('+react -redux' IN BOOLEAN MODE );
         -- 搜索指定短语
         SELECT *,MATCH(title,body) AGAINST('react redux')
         FROM posts WHERE MATCH(title,body) AGAINST('"handling a form"' IN BOOLEAN MODE );
         

6. 复合索引

   在涉及多列查询的时候使用，如果单列索引和多列索引都存在的话，MySQL会自动选择性能更优的

   -- 复合索引
   USE sql_store;
   -- 使用的是idx_state索引 选择性能更优的索引
   CREATE INDEX idx_state ON customers(state);
   EXPLAIN SELECT * FROM customers WHERE state = 'CA' AND points > 1000;
   

   结果：使用的idx_state_lastname,讲道理我觉得应该是idx_state_poinsts来着

   

   1. 创建复合索引

      -- 复合索引 最多可以包含16列
      CREATE INDEX idx_state_points ON customers(state,points);
      DROP INDEX idx_points ON customers;
      DROP INDEX idx_state ON customers;
      SHOW INDEX IN customers;
      

      

   2. 复合索引的特点

      1. 顺序性

         复合索引的顺序很重要。复合索引按列的顺序存储，因此对索引的有效使用必须遵循该顺序。例如，如果有一个 (A, B, C) 的
         复合索引，查询 WHERE A = 1 AND B = 2 会有效利用索引，但查询 WHERE B = 2 AND C = 3 则不会，因为没有从第一列（A）开始。

      2. 前缀原则

         当查询条件匹配复合索引的前缀时，索引仍然可以被使用。例如，复合索引 (A, B, C) 可以用于以下查询：

         ​ • WHERE A = 1

         ​ • WHERE A = 1 AND B = 2

         ​ • WHERE A = 1 AND B = 2 AND C = 3

         但不能用于只涉及后面列的查询，比如 WHERE B = 2 或 WHERE C = 3。

      3. 覆盖索引

         如果查询的所有列都包含在复合索引中，那么该索引可以成为覆盖索引，从而避免访问数据表，进一步提高查询性能。

   3. 复合索引中的列顺序

      一般是按照常用的顺序来排，还有列的独特性

      -- 复合索引中的列顺序
      -- 按照常用的顺序
      -- 按照列的独特性
      SELECT
          COUNT(DISTINCT state),
          COUNT(DISTINCT last_name)
      FROM customers;
      SHOW INDEX IN customers;
      CREATE INDEX idx_lastname_state ON customers(last_name,state);
      CREATE INDEX idx_state_lastname ON customers(state,last_name);
      

      

      查询条件都是=，就可以充分利用各列的独特性，因为last_name的独特性为996，所以使用的索引为idx_lastname_state,可能你会说这违反了顺序性原则，但是大多数现代数据库查询优化器会自动调整条件的顺序，以便最有效地利用可用的索引。所以即使你在查询中写 WHERE state = ‘CA’ AND last_name = ‘Smith’，优化器通常会识别出复合索引idx_lastname_state可以被利用，并调整查询执行计划。

      -- 查询条件是特定的state和last_name,可以充分利用各列的独特性
      -- idx_lastname_state
      DROP INDEX idx_lastname ON customers;
      EXPLAIN SELECT * FROM customers WHERE state = 'CA' AND last_name = 'Smith';
      

      

      然后就是如果有查询条件里面有模糊查询，情况是怎么样的呢

      都是一样的，先去找精确查询的索引，然后根据索引筛选出来的结果继续根据第二列索引顺序查找，反正那个精确度高先查哪个

   4. USE INDEX( )

      强制规定使用哪个索引，也可以用他来判断那个索引效率高点的

      EXPLAIN SELECT * FROM customers USE INDEX (idx_lastname_state) WHERE state = 'CA' AND last_name LIKE 'A%'; -- 40row
      EXPLAIN SELECT * FROM customers USE INDEX (idx_state_lastname) WHERE state = 'CA' AND last_name LIKE 'A%'; -- 7row
      

保护

就是写用户权限的管理，很简单的东西，不分开讲了，直接在代码里面注释下吧

-- create user 用户名 [@规定可以从哪里访问的地址] [IDENTIFIFED  BY  密码]；
CREATE USER study00 IDENTIFIED BY '1234'; -- IDENTIFIFED  BY设置密码
CREATE USER study01@127.0.0.1;
CREATE USER study03@xiongyan.site;
CREATE USER study02@'%.xiongyan.site'; -- %通配符

-- views users
SELECT * FROM mysql.user;

-- DROP users
DROP USER study01@127.0.0.1;

-- changes password
-- FOR 用户名 = 新密码
SET PASSWORD FOR study00 = '12345';

-- grant power
-- GRANT 允许的操作 ON 哪个数据库的哪张表 TO 用户名
GRANT INSERT,UPDATE,SELECT,DELETE,EXECUTE
ON sql_store.*
TO study00;

-- grant admin power
GRANT ALL
ON *.*
TO study01@127.0.0.1;

-- view privileges
SHOW GRANTS ;
-- view privileges for user
SHOW GRANTS FOR study00;

-- revoke privileges
-- 赋予是 TO
GRANT CREATE VIEW ON sql_store.* TO study00;
SHOW GRANTS FOR study00;
-- 移除是 FROM
REVOKE CREATE VIEW ON sql_store.* FROM study00;