Mysql必知必会总结

Mysql必知必会总结

基础概念

  1. sql（Structured query language）结构化查询语言

  2. 不区分大小写，对所有SQL关键字使用大写，而对所有列和表名使用小写便于阅读

  3. MySQL版本

    • 4 - innoDB引擎，增加事务处理，改进全文本搜索
    • 4.1 - 对函数库，子查询，集成帮助的支持
    • 5 - 存储过程，触发器，游标，视图

  4. mysqll中的数据类型

    • 串数据类型，存储如名字，地址，电话，邮编等串。

      1. 定长串，如char，创建表时指定
        image-20220529165733576
      2. 变长串，如TEXT，虽然灵活但是会影响性能
        image-20220529165720725

    • 数组数据类型，有符合，可以存储正/负，

      • decimal（精度可变浮动值）
      • double 双精度浮点值
      • boolea 0/1

    • 日期时间数据类型

      image-20220529165926397

    • 二进制数据类型，可存储任何数据，如图像，多媒体等
      image-20220529165944327

常用语句

建库删库/建表删表

  1. 建表，可以定义引擎，主键自增AUTO_INCREMENT

image-20220528191544531

  1. 给表添加/删除列
ALTER TABLE student ADD stu_id CHAR(20);
ALTER TABLE student DROP stu_id CHAR(20);

  1. 删表/重命名

     DROP TABLE STUDENT
 RENAME TABLE STUDENT TO STIDENT2

查询语句 select

  • 标准： select * from db_name （* 可为 row_name）

  1. distinct 检索不同的列 select DISTINCT id from db_name；

  2. limit 限制行数 select name from db LIMIT 5;(limit 1 , 5 😉

  3. 拼接字段Concat(),别名，算术计算

  4. 子句顺序

    select > from > where > group by > having > order by>limit

  • ORDER BY排序

    1. 例 SELECT name from db_name ORDER BY name;

      ORDER BY包含一个或多个列的名字

    2. 排序方向（升序/降序）ASC/DESC ，指定列排序

    SELECT id，name，price from DB ORDER BY price DESC,name

  • GROUP BY 分组

    所有列都必须在GROUP BY 子句中给出，必须在where之后，order by 之前

  • HAVING 过滤分组，类比where

  • WHERE 过滤行

    • 标准：

      SELECT id from db WHERE id = 1;

    • ORDER BY 位于 WHERE 之后

    • where子句操作符： = ，!= , <>=,between

    • and/or 操作符,存在优先级问题，优先处理and语句，可以使用括号解决。

    • in范围操作符,not

      where id in (1,10)
    • like，%，_ 通配符
      SELECT id,name from db WHERE name LIKE '%jet%';
where name LIKE '_id';
    • 正则表达式匹配关键字：PEGEXP
      WHERE name PEGEXP '[1-5]Ton';
^ $ {n} * 等

  • 函数

    1. 文本处理函数
      • Rtrim(),去重列值右边的空格
      • Upper()大写
      • Lenght（）串长度
    2. 日期时间处理函数数值处理函数

    image-20220528155004616

WHERE Date(order_date) ='2005-09-01'

        3.  数值处理函数 abs() cos() sin() sqrt() 等 
        3.  聚集函数 AVG() count() max() min() sum()

高级查询

  1. 子查询，列必须匹配，可以多层嵌套

image-20220528160653827

  1. 联结（join），要有where子句

image-20220528160842431

  1. 内联结，用inner join 代替where

    image-20220528161102098

可以使用别名来缩短联结的sql语句

image-20220528161432623

  1. 外联结，用right/left指定没有关联的行

    image-20220528161617691

  2. 组合查询 UNION/union all（包含所有行），可以使用order
    image-20220528162029983

  3. 全文本搜索Match()he Against(),需要在见表时启用

插入语句

  • insert，需要列出完整的列名

    intsert into db_name (name,email) VALUES ('ZXC','ZZZ');

    插入多行：提交多条insert或如果列名次序都相同，可以组合插入

    intsert into db_name (name,email) VALUsqES ('ZXC','ZZZ') VALUES ('aa','vv');

  • INSERT SELECT插入检索出来的数据

更新/删除数据

  1. UPDATE,设置多个列时，只用1给set，逗号隔开

    UPDATE db_name SET name = 'ccc' ,email = 'zzz' where id = 1;

    image-20220528191102357

  2. DELETE

image-20220528191133185

视图存储过程游标触发器

  1. 视图（大量部署会降低性能）

    • 作用：复用sql语句，简化sql，保护数据，更改数据格式
    • 创建：CREATE VIEW,删除：DROP VIEW view_name

    image-20220528192706684

  2. 存储过程

    • 简单，安全，高性能，（一定程度上的封装）

    • 创建

      CREATE PROCEDURE productprice1 ()  //无参
begin
   SELECT avg(price) from db_name ;
end

CREATE PROCEDURE productprice2 (  //有参
    IN onnumber INT,
    OUT ototal DECIMAL(8,2)
)  
begin
   SELECT avg(price) from db_name ;
end

    • 调用CALL

      CALL productprice2(@pricelow) 
CALL productprice1()

    • 删除DROP PROCEDURE productprice1；

  3. 游标(mysql 5后支持，mysql中只能用于存储过程)

    • 作用：用于交互，滚动或浏览结果集并且修改

    • 创建

      CREATE PROCEDURE productprice1 ()  
begin
   DECLARE ordernumber CURSOR FOR //创建
   SELECT avg(price) from db_name ;
   OPEN ordernumber
   CLOSE ordernumber
end

  4. 触发器(mysql 5之后支持，仅支持表)

    • 作用：在数据发生改地时自动处理，响应以下语句，delete，insert，update，保证数据一致性
    • 创建，需要给出1.唯一触发器名，关联的表，响应的语句，何时触发（before/after）
    • 给出了名，在insert之后执行，关联的表名
      image-20220529160910699
    • 删除 DROP TRIGGER newproduct

事务处理，安全，性能，维护

  1. 事务，维护数据完整性，要么完全执行，或完全不执行

    • MyISAM不支持明确的事务处理
    • InnoDB支持明确的事务
    • 相关概念，事务（transaction）一组sql语句 回退（rollback）撤销sql语句， 提交（commit）写入数据库，保留点（savepoint） 回退到指定位置（部分事务）

    image-20220529162031796

SET AUTOCOMMIT = 0;设置不自动提交

  1. 安全：登录，增加/删除用户，备份/还原

CREATE USER ben 
DROP USER ben
GRANT SELECT ON db.* TO ben//允许ben对db的所有表只有只读的权限
REVOKE SELECT ON db.* from ben //撤销操作
  • 备份，保证数据都写入再备份，用FLUSH TABLES 语句
    • 命令行mysqldump，转存到外部文件，备份前保证正常运行
    • mysql的BACKUP TABLES 或 SELECT INTO OUFILE 转存到外部文件。
  • 日志
    1. 错误日志：hostname.err,位于data目录
    2. 查询日志：记录所有的mysql活动，名为hostname.log
    3. 二进制日志hostname-bin,记录所有更新过数据的语句，在mysql 5 中添加，之前使用的是更新日志。
    4. 缓慢查询日志：hostname-slow.log 记录执行缓慢的查询，对优化有帮助

  1. 性能

    • 使用一段时间后需要调整内存分配，缓冲区大小等

      show variables; show status //查看当前设置

    • mysql是多用户多线程的DBMS，因此经常同时处理多个任务，当一个执行缓慢时，使用请求执行都会缓慢，可以用show processlist 显示所有进程，也可以用KILL结束某个进程

    • 一般而言，存储过程比一条条执行sql语句快

    • 不要检索不需要的数据，即要多少查多少

    • 建立索引，如果一个查询语句执行时间过长，可以根据其常使用的列建立索引。

    • 索引改善检索，但不利于删改增，根据表的常用操作判断是否添加索引

    • LIKE很慢，多用FULL TEXT

    • 重视采用的数据类型，错误的数据类型会严重影响性能

posted on 2022-06-04 14:09  LiberalAlice  阅读(168)  评论(0)    收藏  举报

博客园  ©  2004-2025
浙公网安备 33010602011771号 浙ICP备2021040463号-3