mysql学习笔记

基础篇

数据库的使用及调动

        打开数据库:USE//最好使用大写，以示区分数据库 ;(每段语句需要用分号终结)

        选择：SELECT：custom_id(某列) 

                                     *（所有）

                                       如果使用*访问所有，后续想访问某个具体的表可采用FROM custom

                                进一步进行数据筛选：WHERE custom_id=1；

                                进一步进行数据排列：ORDER BY first_name (DESC:降序)

        ——破折号表示注释，在语句前面

        SELECT篇：

                                first_name ,

                                point,

                                point+10 AS discount——可以直接进行简单运算、重新命名

        WHERE篇:

                                point>10——筛选分值大于10的数据

                                （符号：大于>  大于等于>=  小于<  小于等于<=  不等于！=或者是<>）

                                povince='陕西'——处理字符串时，加引号，不分大小写，int型不需要加引号

                                povince<>'陕西'——筛选不在陕西的数据

         

        运算符篇：AND:并列    OR：或者     NOT:否定   运算顺序AND>OR

                *多个条件判断时，例如WHERE province=‘陕西’ OR province=‘山西’需要用多个表达式

               

                IN: 同一系列值多个条件判断时，可以采用IN：WHERE province IN(‘陕西’ OR ‘山西’)

                BETWEEN:WHERE point BETWEEN 100 AND 300(等价于>=100 AND <=300)

                LIKE(搜寻):WHERE  NAME LIKE 'b%'（b开头的，后面任意字符）

                                                             '%b%'(文本中出现b，前后可有任意字符)

                                                              '%b'(b结尾，开头任意字符)

                                        *%代表任意字符，_代表单字符(可以类似搭配：B___y)

                REGEXP（regular expression正则表达：高级版LIKE):

                                ^：开头 $：结尾 |：多个搜寻模式 [fmg]：已给条件前后包含任一特定单字符

                                 WHERE NAME REGEXP 'Brush' （等同于%Brush%）

                                 WHERE NAME REGEXP '^Brush'(等同于Brush%)

                                 WHERE NAME REGEXP 'Brush$'(等同于%Brush)

                                 WHERE NAME REGEXP 'Brush|Mac'(等同于%Brush% AND %Mac%)

                                 WHERE NAME REGEXP '[a-h]b'（字符中包含b，并且b字符前面包含a-h任一字符）

                 NULL (空值)：WHERE phone_number IS (NOT) NULL

                 ORDER BY ：排序 如果需要使用降序 格式为 ORDER BY points DESC

                 ORDER BY 的排序不受SELECT的影响，即只选中某个表里面的两列，依旧可以采用未显示出的某一列进行排序，并且可以采用虚拟赋值变量进行排列，即可以在SELECT中可以建立虚拟变量，然后在ORDER BY中采用该变量进行排序

                LIMIT：LIMIT 30（限制显示前30条数据） 

                改写成：LIMIT 10(偏移量，忽略前10条),5（显示五条数据，即从第三页开始显示）

                             —— page 1：1 - 5（分页面显示，每页五条数据）

                             —— page 2： 6 -10

                             —— page 3： 11-15

       * 语句顺序：SELECT>FROM>WHERE>ORDER BY>LIMIT(不能改变顺序，会报错)

        内连接篇

        内连接与外连接区别：内连接只有保证两个表所有的显示行都满足连接条件，否则不显示

                                外连接除了显示符合条件的记录外，还会显示表中的记录，包括空值、null

          INNER  JOIN customers

                                ON order.customer_id=customers.customer_id

                                (确保order表和customers表中customer.id是相同的)

        跨数据库连接：两个数据库 order_item sql_inventory有相同的表 product ，连接在一起

          SELECT *

          FROM order_item oi(oi即是order_item这个的缩写，后面可以替代)

          JOIN sql_inventory.product(p是sql_inventory.product这个表的缩写)

                  ON oi.product_id=p. product_id(连接id)     

        自连接（完整版例子）

        USE   sql.hr

        SELECT *

        FROM employees e

        JOIN employees m

                ON e.report_to=m.employee_id

        复合连接（即根据多个条件识别连接，用于没有唯一id对应，使用多个条件对应的）：

         JOIN  

                ON

                AND

        外连接篇：左连接和右连接

          LEFT JOIN(左连接)：即从FROM选定的表会保留所有数据（尽量选择左连接）

         RIGHT JOIN(右连接)：即从JOIN开始加入的表会保留所有数据

        多表外连接：如果直接用JOIN ON 而有一些值是空值null，就会导致无法正确返回含空置项，建议使用左连接，左连接的表包含有空值的项

                eg.Kelly null

                     Mike male

                     Kelly  123

                     Mike   456

        自连接就是  Mike male 456   

        左连接则是 Kelly null 123 

                   Mike male 456

        USING子句：如果连接的两个表，有变量的名称是相同的，可以使用USING子句

                eg.JOIN shippers sh

                                USING(shipper_id)——在两个表中shipper_id是相同的名称；多个条件用逗号

        交叉连接：CROSS JOIN(用于排列组合，得到所有的可能)——显性语法

                         FROM——隐性语法

        联合:UNIONS(可以合并多段查询的记录）在第二个SELECT前使用

        *查询返回的列必须一模一样，否则会报错

        列属性

                字符类型：varchar（5）(可变字符，如果只有五个字符，那么数据库只保留五个字符)

                                char（50）（字符串，如果只有五个字符，数据库会自动填补剩下45个字符）

                PK:主键（Primary key）

                NN: 非空值(Not null)

                AI:自动递增（Auto_increment）

                插入数据：INSERT INTO 表格 (需要填入的表格）

                                  VALUES(需要填入的值1),

                                                (需要填入的值2)，……

                                       eg.INSERT INTO customers（想插入值的列）

                                            VALUES(default,'john','Smith',……)

                   上一笔插入数据ID:LAST_INSERT_ID()

                   如果要查询SELECT LAST_INSERT_ID()

                   如果要使用，直接（LAST_INSERT_ID()，……）

                创建表复制：

                     1、创建新表：CREAT TABLE order_archived AS

                     2、SELECT * FROM orders（记得刷新）

                      *复制的表没有主键和递增列

                右键表格名称，truncate table截断表（删除表中所有行）——记得刷新

                更新单行 UPDATE invoices

                设定值 SET payment_total=10,payment_date="2024-03-23"

                查询设定更新后的发票：WHERE invoice_id=1(然后点击刷新按钮)

                子查询用WHERE client_id=(SELECT FROM WHERE4)

                删除行 DELETE FROM

                            WHERE{可以列入子条件}

                恢复数据库-sql工作台上方file-脚本-执行

聚合函数

        MAX()、MIN()、AVE()、SUM()、COUNT()

        GROUP BY子句用于WHERE后，分组函数，需要多个分组逗号隔开

        HAVING 条件子句 类似WHERE ，用于对分组后的数据进行筛选

        WITH ROLLUP运算符 用于聚合值的加总，可以用在GROUP BY后面

                eg.GROUP BY client_id WITH ROLLUP

编写复杂查询

       WHERE(中间可以插入子条件，比如高于平均值之类的)

       ALL（） eg.where invoice_total>ALL()  大于所有的

      ANY() SOME() 任意值              =ANY() 在条件中的任何一个都会被返回 和IN等效

      相关子查询 

                 SELECT *

                FROM 表的名字 s

                WHERE score>=(SELECT AVE(score) FROM  表的名字 WHERE id=s.id) 理解看图

                EXISTS运算符：存在  只显示存在符合条件的数据

                        eg.SELECT *

                              FROM client c

                              WHERE EXITS(

                                                        SELECT client_id

                                                        FROM invoices

                                                        WHERE client_id=c.client_id

                                                          )

                在FROM中使用子查询语句时，必须要用别名 这是必选项

内置函数

        ROUND(5.73，1) 四舍五入 后面是精度，结果是5.7

        TURNCAT()用于截断数字

         CEILING(5.7)上限函数 返回大于等于这个数的最小整数

        ABS()绝对值

        RAND()生成0-1之间的随机浮点数

        字符串函数

        LENGTH() 返回字符串字数

        UPPER() LOWER() 转换为大小写

        删除字符中不用的空格

                LTRIM()  左修整：移除字符串左侧空白字符或者是其他预定义字符

                RTRIM() 右修整：移除字符串左侧空白字符或者是其他预定义字符

                TRIM()    删除所有前导或者尾随空格

                LEFT()、RIGHT()返回左边/右边指定数量字符

                SUBSTR(字符串，指定位置，返回字符数量) 字符截取函数 第三个参数如果不加，直接返回到最后一个字符

                LOCATE(需要搜索字符，字符串) 返回第一个字符或者是一串字符匹配位置，不区分大小写，没有该字符则会返回0

                REPLACE(字符串，需要替换的字符串，替换后的字符串)替换

                CONCA() 串联字符串

        日期函数

                NOW()调用当前函数

                CURDATE()当前日期  CURTIME()   YEAR()提取年份、MONTH、DAY

                DAYNAME()返回字符串格式的星期数  MONTHNAME

                EXTRACT(DAY FROM NOW) 提取出NOW 中需要的日

                格式化日期和时间        DATE_FORMAT(NOW(),"格式字符串 eg.%y——24 %Y_2024")

                        %H:小时    %i:分钟     %p：am/pm

                更多参考mysql reference mannual

                计算日期和时间         DATE_ADD给日期时间值添加日期成分

               DATE_ADD(NOW(),INTERVAL 1(也可以是负数) DAY/MONTH/YEAR) 计算延后一天

               DATE_SUB=DATE_ADD 中负数

                计算两个日期之间的间隔 DATEDIFF('日期1'，'日期2') 只返回天数，不返回小时或分钟

                计算两个时间之间的间隔 

                        TIME_TO_SEC('时间1')返回从0点开始的秒数- TIME_TO_SEC('时间2')       

        IFNULL和COALESCE     

             IFNULL(shipper_id,'Not assigned') 把单元格中的null改成Not assigned

             COALESCE(shipper_id,comments，'Not assigned')先返回shipper_id，为空返回comment，comment为空返回Not assigned 即返回第一个非空值，如果没有非空，返回最后一个

         IF函数

                IF(条件，真值，假值)：单一表达式

         CASE函数

                CASE :多个表达式

                        CASE

                                      WHEN 条件1 THEN 结果1

                                       WHEN 条件2 THEN 结果2

                                        ELSE （其余结果）结果3

                         END AS category（别名）

视图        

               创建视图

                         CREATE VIEW 别名 AS 后接查询语句  类似虚拟表，视图不储存数据

                更改/删除视图

                          DROP VIEW 别名 删除视图

                          CREATE OR REPLACE 别名  更改视图

                更新视图

                        视图中没有任何这些函数：DISTINCT、聚合函数、GROUP BY、HAVING、UNION 那么称为可更新视图 可以在INSERT、UPDATE、DELETE中使用

               WITH OPTION CHECK  

                      在视图最后一句加WITH OPTION CHECK ，防止UPDATE或者DELETE删除视图中的行

存储

        创建存储

                        方法一：

                        DELIMITER //或者$$    改变默认分隔符变成//或者$$ 把这段语句打包

                        CREATE PROCEDURE get_clients()

                        BEGIN

                                SELECT * FROM clients 

   在BEGIN和END之间会自动生成括号，括号中主体内容就是创建的储存，每条语句用分号终结，只用在MySQL中这样，sql server不需要 如果用了改默认分隔符，begin括号最后一句用分号终结

                        END$$

                        DELIMITER ；然后需要改回来

                        调用存储过程

                         CALL get_clients()

         使用mysql工作台创建存储过程

                        导航面板，点击 stored_procedures右键

                        create store procedures 右下角应用和撤销

         删除存储过程

                        DROP PROCEDURE IF EXISTS get_clients

         参数

                        DELIMITER $$    

                        CREATE PROCEDURE get_clients_by_state

                        (

                           state CHAR(2)——CA   ,多个参数用逗号分隔CHAR(2)t表示两个字符 VARCHAR可变字符串

                        )

                        BEGIN

                                SELECT * FROM clients 

                        

                TINYINT占1个字节的内存，可以用来储存0-255的数字，INT占用4个字节

                DICIMAL(9,2)含有小数数据类型 含有字符位数为9，小数点后两位

          参数验证

                定义变量 SET @invoice_count=0 设定初始值为0

                DECLARE语句声明变量 risk_factor DICIMAL(9,2) DEFAULT 0

          创建函数

                CREAT FUNCTION get_risk_factor_for_client

                (

                  client_id INT

                )

                RETURNS INTEGER

                DETERMINISTIC 给函数一组同样的数，会一直返回同样的值

                READS SQL DATA 读取数据

                MODIFIES SQL DATA  修改数据

                BEGIN

                RETURN 1

                END

触发器

        创建触发器

                        DELIMITER $$    

                       CREATE TRIGGER payments_after_insert   表名_时间_触发器动作

                                AFTER INSERT ON payments

                                FOR EACH ROW

                        BEGIN

                                UPDATE invoices

                                SET payment_total=total_payment+NEW.amount(新插入的行.单独属性，OLD 就是原数据)

                                WHERE invoice_id=NEW.invoice_id;

                        END     

                        DELIMITER ；

         查看触发器  

                        SHOW TRIGGERS 

                        如果是具体的触发器，以payment开头的

                        SHOW TRIGGERS LIKE ' payment%'

        删除触发器

                         DROP TRIGGER IF EXITS 触发器名称

        使用触发器进行审计（保留修改记录）

                        END前面插入（INSERT可以改成DELETE

                        INSERT INTO payments_audits

                        VALUES(NEW.clients，NEW.date,'Insert'，NOW())

        事件

                        SHOW VARIABLES;(查看系统所有的变量)  LIKE 'event%'；

                        SET GLOBAL event_scheduler=ON/OFF

                        CREATE EVENT 事件名

                        ON SCHEDULE

                                AT '2019-05-01' 一次执行

                                EVERY  1 DAY STARTS '2019-05-01' ENDS '2020-05-01'（非必须

                                DO  BEGIN

                                DELETE FROM payment_audit

                                WHERE action_date< NOW()- INTERVAL 1 YEAR

                                END $$

                                DELIMITER ; 

         查看、删除、更改数据

                        SHOW EVENTS；

                        DROP EVENT IF EXITS yearly_delete_stale_audits_rows;

                        ALTER EVENT yearly_delete_stale_audits_rows DISABLE/ENABLE禁用/启用

事务

         创建事务

                START TRANSACTION

                INSERT INTO orders(customer_id,order_date,status)

                VALUES(1,'2019-01-01',1);

                INSERT INTO orders_items

                VALUES(LAST_INSERT_ID(),1,1,1);

                COMMIT; 关闭该事务//ROLLBACK; 退回事务并且撤销更改

                SHOW VARIABLES LIKE 'autocommit' 查看自动提交系统

        并发和锁定

              隔离级别

                                MySQL中默认隔离级别是可重复读取

	丢失更新值	脏数据读取	不可重复读取	幻读
读取未提交READ UNCOMMITED
读取提交 READ COMMITED		1
可重复读取 REPEATABLE READ	1	1	1
可序化SERIALIZABLE	1	1	1	1

             查看事务隔离级别

                SHOW VARIABLES LIKE 'transaction_isolation';

             更改事务隔离级别

                SET (SESSION/GLOBAL设定系列/全局都是这个值)TRANSACTION ISOLATION LEVEL SERIALIZABLE;

              死锁

                多个事务同时发送，等待对方完成 1：AB 2:BA 此时系统会撤销

数据类型

               字符串、整数、定点数和浮点数、布尔类型、枚举和集合类型、日期和时间类型、Blob类型、JSON类型

        

        创建JSON

                SET properties=JSON_OBJECT(

                        'weight' ,10,

                        'dimensions',JASON_ARRAY(1,2,3)

                        'manufacturer',JSON_OBJECT('name','sony')                                

                )

        读取JSON键值 

                SELECT product_id，JSON_EXTRACT(properities,'$.weight')$表示当前表，后加.加键值，表示读取特定键值

或者       SELECT product_id，properities->'$.weight',如果访问的是维度，$.dimension[0]0表示第一个个值  $.manufacturer.name 在后面加.加单个属性 采用-->去掉属性中的双引号

                FROM products

                WHERE product_id=1;

        

        移除JSON

                SET properties=JSON_REMOVE(

设计数据库

        数据建模

             利用UML图来理清业务逻辑与路径

        实体关系

              Mysql工作台-file-New mobel

        

        外键

                        

         外键约束

                

        标准化

        第一范式：一行中的每个单元格都应该有单一值，并且不能出现重复列

        第二范式：必须符合第一范式，每个表都应该有单一目的，有且仅能代表一种实体类型

        第三范式：实体符合第二范式，表中的列不应该派生自其他列，即不随其他列变动而变动

        重心放在消除冗杂，减少重复，而不是关注是否违反了第几范式，从概念和模型开始入手，而不是从创建表开始，也不要什么都建模！

        创建/删除数据库

        CREATE/DROP DATABASE IF (NOT  如果是drop就没有not) EXITS sql_store1

        创建/删除表

        CREATE TABLE customers

        （

                customer_id INT PRIMARY KEY AUTO_INCREMENT，

                first_name VARCHAR（50）NOT NULL

                points  NOT NULL DEFAULT 0

                email varchar（255） NOT NULL UNIQUE

           ）;

        修改表

        ALTER TABLE customers

                ADD last_name VARCHAR(50) AFTER first_name

                MODIFY first_name VARCHAR(55) DEFAULT ' '

                DROP points

        创建关系

                延续上面创建表的格式

        CREATE TABLE customers

                （

                           order_id INT PRIMARY KEY

                           customer_id INT NOT NULL

                           FOREIGN KEY fk_orders_customers(customer_id)

                                REFERENCES customer(customer_id)

                                ON UPDATE CASCADE/SET NULL/NO ACTION

                                ON DELETE NO ACTION

           ）;

       更改主键/外键

                ALTER TABLE orders

                        ADD PRIMARY KEY(order_id)

                        DROP PRIMARY KEY

                        DROP FOREIGN KEY k_orders_customers,

                        ADD FOREIGN KEY fk_orders_customers(customer_id)

                                REFERENCES customer(customer_id)                

                                        ON UPDATE CASCADE

                                                        ON DELETE NO ACTION

索引

        创建索引

                        EXPLAIN SELECT customer_id FROM customers WHERE state=‘VA’

                        CREAT INDEX idx_state ON customers(state);

        查看索引

                        SHOW INDEXES IN customers；

        

                        ANALYZE TABLE customers；查看表的统计信息

        全文索引  

                        CREATE FULLTEXT INDEX id_title_body ON post(title,body);

                        SELECT *,MATCH(title,body)  AGAINST('react redux')相关性得分

                        FROM post

                        WHERE MATCH(title,body) AGAINST('react（- 代表排除后面这个）- redux +form（+代表包括这个）'IN BOOLEAN MODE用布尔模式搜索)

用户及用户权限

        创建用户

                        CREATE USER sarah@127.0.0.1  //  localhost  //  '%.(%.表示任何子网络)codewithmosh.com'（@是限制条件，后面是IP地址/主机名称/域名）

                        CREATE USER sarah IDENTIFIED BY '1234';

        查看用户

              SELECT * FROM sql.user;

        删除用户

                DROP USER sarah@127.0.0.1；

        更改密码

                SET PASSWORD (FOR sarah)='2345' /给当前登录的用户更改密码就不要括号内的

                或者在导航面板 USER AND PRIVILEGES

        授予权限

                1、限制/部分权限 假设应用程序叫moon

                        CREATE USER moon_app IDENTIFIED BY '1234'；先创建用户

                        GRANT SELECT, INSERT ,UPDATE,DELETE,EXECUTE 

                        ON 数据库名称 eg.sql_store.*

                        TO moon_app;

                2、管理员权限

                        GRANT ALL

                       ON 数据库名称 eg.*.*

                        TO moon_app;

        查看权限

                        SHOW GRANT （FOR Sarah）；查看当前登录账号权限去掉括号

                        或者在导航面板 USER AND PRIVILEGES——Administrative ROLES

                        SCHEMA PRIVILEGES 看特定数据库权限

        撤销权限

                        REVOKE SELECT, INSERT ,UPDATE,DELETE,EXECUTE 

                        ON sql_store.*

                        FROM moon_app;