基础

MySQL不区分大小写
不同语句用";"分割,多个换行,间隔在sql执行时被忽略
string,日期必须用""/''封好
选择数据库:双击/USE+数据库名字
别写错单词,报错信息会比较模糊,不好找问题

第一部分:数据类型,增删改查,分组,子查询

通用

取别名
给列取别名,使用AS (points+10)*100 AS discount_factor 给表取别名,空格就成 customer c
去重 DISTINCT

数据类型

string types char fixed-length
varchar <64kb
Mediumtext <16MB
longtext <4GB
numeric types
整数
tinyint [-128,127]
mysql integer types
小数
DECIMAL(p,s) 小数点后有固定位数的数,用于$,￥...
FLOAT,DOUBLE 用于科学计算,近似值,不是准确值
boolean true,false
可枚举
ENUM('S','L','M') 不好用,尽量避免
更好的方法是单独建立一张规格表
SET
SET(...) 同ENUM,尽量避免,有更好的替代方案
date and time types
DATE
TIME
DATETIME 8b
TIMESTAMP √ 4个字节,存2038年前
YEAR
blob types 图像,视频,pdf,word文件....
tinyblob 255b
blob 65kb √
mediumblob 16m
longblob 4g
把图像从数据库提取出来,比从文件系统慢
JSON JSON 8.0.15以上版本才支持

  SET properties='{"key1":"value1","key2":"value2"...}'
  JSON_OBJECT(
    'weight':10,
    'dimensions':JSON_ARRAY(1,2,3)
  )
  JSON_EXTRACT(properties,'$.weight')
  properties->'$.weight'
  properties->>'$.manufacturer.name'  --获取去掉""的sony
  SET properties=JSON_SET(
    properties,
    '$.weight',20
  )
  JSON_REMOVE(
    properties,
    '$.age'
  )

spatial type 存储地理地区...

SELECT语句(查)

格式:SELECT 列名 FROM 表名 (WHERE 条件) (ORDER BY 列名)
WHERE,ORDER BY语句是可选的

WHERE语句(按条件筛选)

简单条件
> , >= , < , <= , = , != , <>
WHERE order_data>='1999-01-01'
复杂条件
AND, OR, NOT, IN, BETWEEN, ALL, SOME, ANY, EXISTS, LIKE, REGEXP
IS NULL, IS NOT NULL
优先级:AND>OR
BETWEEN 包含两端的值
EXISTS 类似IN,不返回具体结果,返回true/false,大表查询比IN更快
NOT 和其他条件搭配使用,NOT LIKE,NOT EXISTS... LIKE '%'匹配任何个数的任何字符,'_'匹配任何单个字符 REGEXP ^, $, |, [], - ...

ORDER语句(排序)

ORDER BY 字段/表达式 DESC/ASC
默认按升序排列
字段/表达式可以有多个,先按第一个字段/表达式排,再按第二个....
MySQL排序字段可以不在选择子句的字段中,也可以是别名
ORDER BY quantity*unit_price DESC

LIMIT语句(限制)

LIMIT 6,3 --跳过前6条数据,获取3条数据

INSERT(插入)

格式1: 表中的每个字段都要按顺序填
INSERT INTO customers VALUES (DEFAULT,..., NULL);
格式2: 只要按要填的字段的顺序填入数据即可
INSERT INTO customer(字段1,字段2) VALUES (..),(...)

创建新表

复制出来的新表,会忽略旧表主键,自增字段属性,默认值还是保留的

从旧表创建全新的表 CREATE TABLE orders_archived AS
SELECT * FROM orders --子查询
将子查询结果添加到有字段名的空表
INSERT INTO orders_archived
SELECT * from orders

update(更新)

更新一条或多条数据,取决于where查询能够找到多少条语句

  UPDATE invoices    
  SET payment_total=10,payment_date='2019-03-01'     
  WHERE invoices_id=1

  UPDATE invoices   
  SET    
    payment_total=invoice_total*0.5,   
    payment_date=due_date   
  WHERE client_id IN      --IN在此处使用真的绝了!!!!  
    (SELECT client_id    
    FROM clients     
    WHERE state IN ('ca','ny'))

DELETE(删)

DELETE FROM invoices WHERE invoice_id = (SELECT...)

GROUP BY (分组)

顺序: SELECT,FROM,WHERE 之后, ORDER BY之前
一般来讲,当选择语句中有聚合函数,对数据进行分组按selete语句中的所有字段

HAVING (按条件筛选)

WHERE 筛选分组前的数据,使用的字段可以不在SELECT语句里面 HAVING 筛选分组后的数据,使用的字段必须在SELECT语句里面

WITH ROLLUP

用于计算聚合值的列的总和, 计算结果添加到数据的最后一行
使用rollup运算符,不能在GROUP BY语句中使用列别名,需要使用真实的名字
GROUP BY state, city WITH ROLLUP 计算每个分组的聚合数据,并计算所有分组总的聚合数据

只在MySQL中有,不是标准的SQL语句

  SELECT    
    pm.name AS payment_method,   
    SUM(amount) AS total    
  FROM payments p   
  JOIN payment_methods pm   
    ON p.payment_method=pm.payment_method_id   
  GROUP BY payment_method WITH ROLLUP

子查询

子查询可用于WHERE,FROM,SELECT等等语句里面

  SELECT *   
  FROM employees e   
  WHERE salary > (  
    SELECT AVG(salary)
    FROM employees
    WHERE office_id=e.office_id
  )
  
  SELECT *
  FROM products p
  WHERE NOT EXISTS (
    SELECT product_id
    FROM order_items
    WHERE product_id=p.product_id
  )
  
  SELECT 
    invoice_id,invoice_total,
    (SELECT AVG(invoice_total)
      FROM invoices) AS invoice_average,
    invoice_total-(SELECT invoice_average) AS difference
  FROM invoices

第二部分连接

连接列:内连接,外连接(左连接,右连接),交叉连接
连接行:UNION

内连接

INNER JOIN 可以简写成JOIN

  SELECT order_id,o.customer_id,first_name    
  FROM orders o    
  JOIN customers c    
  ON o.customer_id=c.customer_id;

注意事项:

多张表有相同字段时,需要给字段加上前缀
给表取了别名,其他所有地方()一定要使用这个别名
连接条件可以是复合条件
ON oi.order_id=oin.order_id AND oi.product_id=oin.product_id
连接的两张表可以来自不同/相同的数据库,可以是同一张表.还可以连接多张表

跨数据库连接

  SELECT * 
  FROM order_item oi
  JOIN sql_inventory.products p
    ON oi.product_id=p.product_id

注意事项:
当前使用的数据库对应的表,可以不加前缀.但是,当前的数据库是会变的,最好都加上.

自连接

-- 找所有人的上级

  SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AS manager
  FROM employees e
  JOIN employees m
    ON e.reports_to=m.employee_id;

注意事项:

需要取不同的别名
选中的相同的字段最好取个别名,一张表里面有两个重复字段有些奇怪

多表连接

  SELECT 
    o.order_id,o.order_data,
    c.first_name,c.last_name,
    os.name AS status
  FROM order o
  JOIN customers c
    ON o.customer_id=c.customer_id
  JOIN order_statuses os
    ON o.statuus=os.order_status_id

外连接

使用内连接,只能看到满足条件的results,看不到不满足条件的
如下查询,只能看到下了订单的用户,看不到没下订单的用户

SELECT     
  c.customer_id,c.first_name,    
  o.order_id    
FROM customers c    
JOIN orders o     
  ON c.customer_id=o.customer_id  --c中不满足此条件的数据不会被返回   
ORDER BY c.customer_id

左连接

内连接:只有on的条件满足的result,才会被返回
左连接:无论on的条件是否满足,左表的所有记录都会被返回
右连接:无论on的条件是否满足,右表的所有记录都会被返回
左连右连调换表的顺序是一样的,为提高可读性,最好不要混着用.用左连即可
同内连接使用差不多,也可以多表连接,跨数据库连接...

SELECT 
  c.customer_id,c.first_name,
  o.order_id
FROM customers c
LEFT JOIN orders o
  ON c.customer_id=o.customer_id  
ORDER BY c.customer_id

USING语句

只能在连接条件不同表的字段名一样时代替ON使用
USING (customer_id) == ON c.customer_id=o.customer_id
可以有多个条件
USING(order_id,product_id)

交叉连接

应用场景:尺寸和颜色
表1的每个record和表2的每个record相连接,会有rs1*rs2个rs
SELECT *
FROM customers c
CROSS JOIN products p

UNION

两个片段的字段必须相同.表头由第一个片段决定

SELECT    
  order_id,order_date,'Archived' AS status     
FROM orders     
WHERE order_data<'2021-01-01'   
UNION    
SELECT     
  order_id,order_date,'Archived' AS status    
FROM orders    
WHERE order_data<'2021-01-01'

第三部分:MySQL内置函数

LAST_INSERT_ID() 获取最新插入的record的ID

聚合函数

MAX() , MIN() , AVG() , SUM() , COUNT()

数值,日期,字符串都可以用
只运行非空值,如果需要所有记录count(*)
()里面可以说字段名,也可以是表达式 COUNT(DISTINCR client_id)

numeric function

ROUND(num,n) 四舍五入,保留n位小数
TRUNCATE(num,n) 保留n位小数
CEILING(num)
FLOOR(num)
ABS(num)
RAND()

string function

LENGTH(str)
UPPER(str)
LOWER(str)
LTRIM(str),RTRIM(str),TRIM(str)
LEFT(str,n) 从左边拿n个字符
SUBSTRING(str,start,length)
LOCATE(char,str)
REPLACE(str,substr,substr1)
CONCAT(str1,str2)

date function

NOW() =CURDATE()+CURTIME()
CURDATE()
CURTIME()
YEAR(NOW()),MONTH,DAY,HOUR,SECOND... --返回数字
DAYNAME(NOW()),MONTHNAME(NOW()) --返回字符串
EXTRACT(YEAR FROM NOW())
DATE_FROMAT(NOW(),'%M %D %Y') --日期格式化函数,为了给人看.具体规则查文档
DATA_ADD(NOW(),INTERVAL 1 YEAR)
DATEDIFF(date1,date2) --返回天的差异 TIME_TO_SEC('09:00')-TIME_TO_SEC('09:02')

其他

IFNULL --如果第一个参数为空,用第二个参数替代
COALESCE --比IFNULL更灵活,第一个参数为NULL,用第二个参数替代,第二个为空,第三个替代...
IF(条件,条件满足,条件不满足)
CASE WHEN 相当于多级IF

  IFNULL(shipper_id,'NOT assigned') AS shipper 
  
  COALESCE(shipper_id,comments,'NOT assigned') 
  
  IF(YEAR(order_date)=YEAR(NOW()),'Active','Archived') AS category
  
  CASE
    WHEN YEAR(order_date)=YEAR(NOW()) THEN 'Active'
    WHEN ....
    ELSE 'Future'
  END

第四部分:高级用法

视图,存储过程,函数,触发器,事件,事务

VIEW(视图)

CREATE OR REPLACE VIEW table_name AS SELECT...

可更新视图

没有DISTINCT 没有Aggregate Functions (MIN,MAX,SUM...) 没有GROUP BY/HAVING 没有UNION

WITH CHECK OPTION 选项

如果操作导致可更新视图某些results消失,会给出报错提示

STORED PROCEDURE(存储过程)

目的:管理SQL,更快执行,有利于数据安全

新增存储过程. 可以从头开始写,也可以利用mysql提供的存储过程

  DELIMITER $$
  CREATE PROCEDURE `get_invoices_with_balance`(
    client_id INT(11)
  )
  BEGIN
    --设置默认值是CA
    IF state IS NULL THEN
      SET state='CA'
    END IF;
    --会执行的语句
    SELECT *
    FROM invoices i
    WHERE i.client_id=client_id;
    
    --设置默认值是全选
    SELECT * FROM clients c
    WHERE c.state=IFNULL(state,c.state)
  END$$
  DELIMITER ;

删除存储过程

  DROP PROCEDURE IF EXISTS get_clients;

参数校验

在用户输入信息的时候,就应该校验.而不是等到数据库校验

  BEGIN
    IF payment_amount<= 0 THEN
      SIGNAL SQLSTATE '22003'
        SET MESSAGE_TEXT='Invalid payment amount'
    END IF;
  ...

输出参数

OUT
可以有多个输出参数,函数只有一个返回值

函数

函数只有单一返回值;存储过程,多个返回值...

CREATE FUNCTION get_risk_factor_for_client(
  client_id INT
)
RETURNS INTERGER
--有多个特性可以选择
--DETERMINISTIC
READ SQL DATA
--MODIFIES SQL DATA
BEGIN
  DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
  DECLARE invoices_total DECIMAL(9,2);
  DECLARE invoices_count INT;

  SELECT COUNT(*),SUM(invoice_total)
  INTO invoice_count,invoices_total
  FROM invoices i
  WHERE i.client_id=client_id;

  SET risk_factor=invoices_total/invoices_count*5;
  RETURN risk_factor;
END

TRIGGER

  DELIMITER $$
  CREATE TRIGGER payments_after_insert()
    AFTER INSERT ON payments
    FOR EACH ROW 
  BEGIN
    UPDATE invoices
    SET payment_total=payment_total+NEW.amount
    WHERE invoice_id=NEW.invoice_id;

    --记录增删改查操作
    INSERT INTO payment_audit
    VALUES (OLD.client_id,OLD.date,OLD.amount,'DELETE',NOW());
  END$$
  DELIMITER ;
  
  
  SHOW TRIGGERS   --查看已有TRIGGERS
  DROP TRIGGER IF EXISTS payments_after_insert;  --删除某个TRIGGER

EVENT(事件)

  SHOW VARIABLES LIKE 'event%';
  SET GLOBAL event_scheduler=ON;
  
  DELIMITER $$
  CREATE EVENT yearly_delete_stale_audit_rows
  ON SCHEDULE
    --AT '2019-05-01'
    EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
  DO BEGIN
    DELETE FROM payments_audit
    WHERE action_date < NOW()-INTERVAL 1 YEAR;
    --DATEADD(NOW(),INTERVAL -1 YEAR)
  END $$
  DELIMITER ;
  
  
  SHOW EVENTS
  DROP EVENT IF EVENT yearly_delete_stale_audit_rows
  AFTER EVENT yearly_delete_stale_audit_rows DISABLE/ENABLE

TRANSACTION(事务)

ACID特性

创建事务

START TRANSACTION; .... --增删改查等等语句 COMMIT; --ROLLBACK 手动撤销

并发

导致的问题:lost updates,dirty reads,non-repeating reads,phantom reads

两个事务更新同一行,后提交的事务覆盖之前事务所做的修改
读取未提交事务
读取相同数据两次,得到不同结果
有另外一个事务正在执行,影响查询操作的准确性幻读解决方案:设置隔离等级
read committed, read committed, repeatable read, serializable

SHOW VARIABLES LIKE 'transaction_isolation'  --查看隔离等级

-- 设置下一个事务的隔离层级
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 设置全局/会话层面的隔离等级
SET GLOABLE/SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

死锁

不同事务握住了互相需要的锁,导致事务无法完成
永远无法真正消除,只能减小其可能性

遵照相同顺序.经常发生死锁的几个事务,参看执行顺序...
简化事务,缩小事务运行事件

第五部分:数据库设计,索引优化,权限管理

数据库设计

理解,分析业务需求查看现有表单,文档,应用程序,电子表格,数据库,收集信息
构建概念模型识别业务中实体/事物/概念以及它们之间的关系
用于不同领域的人交流的
构建逻辑模型新增table,字段
构建实体模型新增数据类型,默认值,主键, 视图,存储过程,触发器...

conceptual models

工具:microsoft visio,draw.io,lucidCharts
绘制entity relationship

logical models

多对多关系,分离出链接表

physical models

选主键、复合主键、外键,外键级连关系(CASCADE/RESTRICT/NO ACTION)

标准化

第一范式每一行的每个单元格都应该有单一值,且不能出现重复列
第二范式满足第一范式每张表都应该只有一个单一实体,表中的每一列都用于描述这个单一实体
第三范式满足第二范式表中的列不应该由表中的其他列派生得到(类似计算公式?)

模型的正向工程

根据模型生成数据库
Database->forwardEngineer
生成数据库后修改表
Database->Synchronize Model...

模型的反向工程

根据数据库生成模型

使用sql语句

创建库/表

  CREATE DATABASE IF NOT EXITS sql_store

  USE sql_store
  DROP TABLE IF EXISTS customers;
  CREATE TABLE customers(
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    --NOT NULL DEFAULET 0 UNIQUE
  )

修改表

  AFTER TABLE customers
    ADD last_name VARCHAR(50) NOT NULL AFTER first_name,
    MODIFY first_name VARCHAR(55) DEFAULT '',
    DROP points
    DROP FOREIGN KEY fk_orders_customers,
    ADD FOREIGN KEY fk_orders_customers (customer_id)
      REFERENCES customers (customer_id)
      ON UPDATE CASCADE
      ON DELETE NO ACTION
    ;

创建关系

  CREAT TABLE orders(
    order_id     INT PRIMARYKEY,
    customer_id  INT NOT NULL,
    FOREIGN KEY fk_orders_customers (customer_id)
      REFERENCES customers (customer_id)
      ON UPDATE CASCADE
      ON DELETE NO ACTION
  )

字符集和排序规则

SHOW CHARSET --获取所有字符集
default collation 默认排序规则

Schema inspector 鼠标操作不能在数据库级别更改字符集.可以查看..

--DATABASE/TABLE/COLUMN的字符集都可以设置
--设置数据库,表层面的字符集
ALTER/CREATE DATABASE/TABLE  db_name
  CHARACTER SET latin1
--设置列层面的字符集  
first_name VARCHAR(50) CHARACTER SET latin1 NO NULL...

存储引擎

表级别的,多个引擎可以共存在一个库
SHOW ENGINES --查看使用的存储引擎
InnoDB 常用,支持事务
MyISAM 过时,不支持事务、外键

ALTER TABLE customer ENGINE=InnoDB --修改期间用不了表

索引

cluster indexes 主键才有,一个表只能有一个
secondary indexes 外键自动添加,设置的索引也是二级索引.存储主键+次级索引列信息

  EXPLAIN SELECT customer_id FROM customer WHERE points>1000;
  CREATE INDEX idx_state ON customers (state);

  SHOW INDEXES IN customers
  ANALYZE TABLE customers   --重新生成表的统计信息,准确的
  DROP INDEX idx_points ON customers

  --Collation  A 升序,D 降序
  --Cardinality  估计索引中唯一值的数量   ANALYZE TABLE
 
  --type all 全表扫描
  --type index  扫描index.比全表扫描快,不涉及从磁盘读取每个记录

前缀索引

char,varchar,text,blob 为索引列数据类型,会增大存储空间,不利于提高性能
∴不以整体内容作为索引,只包含前几个字符

  CREATE INDEX idx_lastname ON customers (last_name(20));

  SELECT COUNT(DISTINCT LEFT(last_name,1)) FROM customers;

全文索引

  CREATE FULLTEXT INDEX idx_title_body ON posts (title,body);
  SELECT *,MATCH(title,body) AGAINST ('react redux')   --获取相关度排序
  FROM posts
  WHERE MATCH(title,body) AGAINST ('react redux');
  AGAINST ('react -redux +form' IN BOOLEAN MOOD);
  AGAINST ('"handling a form"' IN BOOLEAN MOOD);

复合索引

  CREATE INDEX idx_state_poinrs ON customers (state,points);

复合索引顺序

put the most frequently used columns first
put the columns with a higher cardinality(基数) first
put your queries into account 第2条不一定100%对,取决于查询条件,范围还是确确的数

解决索引失效

isolate your column
新增索引或者修改查询语句例如：or=>union

利用索引排序

(a,b)=>a, a,b, a DESC,b DESC
对于以a,b两个字段组成的复合索引,用于按a升/降排序, a,b同升/降排序,
利用索引排序，性能会得到优化

利用索引优化性

WHERE,ORDER BY,SELECT语句，能利用索引的尽量利用索引
创建新索引前,看看之前的索引是否可以利用.避免duplicate indexes,redundant indexes

用户管理

  --创建新用户
  CREATE USER john@127.0.0.1  IDENTIFIED BY '123456';
  --查看用户
  SELECT * FROM mysql.user
  --删除用户
  DROP USER bob@'%codewithmosh.com'

  --修改密码
  SET PASSWORD FOR john='12456';  --改别人的
  SET PASSWORD ='Q123342';  --改自己的

权限管理

web/desktop application

CREATE USER moon_app IDENTIFIED BY '1234';
GRANT SELECT,INSERT,DELETE,UPDETE,EXECUTE
  ON sql_store.*
  TO moon_app;

admin

GRANT ALL ON *.* TO john;
--查看权限
SHOW GRANTS FOR john
--撤销授权
REVOKE CREATE VIEW ON sql_store.* FROM moon_app

MySQL基础

基础