新手入门MySQL——篇1

Onssd

已于 2022-04-07 20:26:42 修改

阅读量780

点赞数

分类专栏： MYSQL 文章标签：数据分析 python 数据库开发 dba

于 2022-03-28 17:43:10 首次发布

仅限个人学习使用

本文链接：https://blog.csdn.net/Onssd/article/details/123744548

版权

MYSQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

DDL——数据类型及案例

### 1、设计一张职工信息表 
create table emp (
id int COMMENT '编号',
workno VARCHAR(10) COMMENT '工号',
name VARCHAR(10) COMMENT '姓名',
gender CHAR(1) COMMENT '性别',
age TINYINT UNSIGNED COMMENT '年龄',
idcard CHAR(18) COMMENT '身份证号',
entrydate date COMMENT '入职时间'
) COMMENT '员工表';
DESC emp;

 ### 2、表操作——修改&删除       
语法1：ALTER TABLE 表名  ADD 字段名     type(长度)  [COMMENT 注释][约束]            -- [添加新的字段]
例子1：ALTER TABLE emp  ADD nickname  VARCHAR(20) COMMENT '昵称';   
语法2：ALTER TABLE 表名 MODIFY 字段名 type(长度) [COMMENT 注释][约束]               -- [修改字段类型]
语法3：ALTER TABLE 表名 CHANGE 旧名称    新名称    type(长度)  [COMMENT 注释][约束]  -- [修改字段名称]
例子3：ALTER TABLE emp CHANGE nickname username VARCHAR(30) COMMENT '用户名';
语法4：ALTER TABLE 表名 DROP 字段名称           -- [删除字段]
例子4：ALTER TABLE emp DROP username;
语法5：ALTER TABLE 旧表名 RENAME TO 新表名;     -- [修改表名]
例子5：ALTER TABLE emp   RENAME TO employee;
语法6：DROP TABLE [IF EXISTS] 表名             -- [删除表]
语法7：TRUNCATE TABLE 表名                     -- [删除表，并重新创建该表；保留表结构，没有数据]

DML——数据类型及案例

语法1：INSERT INTO 表名(字段1, 字段2, ...) VALUES(值1, 值2, ...) -- [指定字段添加数据]
例子1：INSERT INTO employee(id, workno, name, gender, age, idcard, entrydate) values(1, '1', 'itcast', '女', 10, '568776564545', '2021-09-10');
语法2：INSERT INTO 表名(字段1, 字段2, ...) VALUES(值1, 值2, ...),(值a, 值b,  ...) -- [批量添加指定数据]
语法3：INSERT INTO 表名 VALUES(值1, 值2, ...) -- [全部字段添加数据]
语法4: INSERT INTO 表名 VALUES(值1, 值2, ...), (值a, 值b, ...) -- [批量添加全部数据]

索引——性能分析

语法1: show [session|global] status  -- [查看执行频率]
例子1: show global status like 'Com_______';
语法2: show variables like 'slow_query_log';  -- 开启并查看[慢查询日志]
例子2: slow_query_log = 1; long_qurey_time = 2 超过2秒，视为慢查询，记录日志
语法3: show profiles;                       -- 开启[profile详情]
例子3: select @@profiling;  show profiles   -- 显示query_id和执行时长
语法4: show profile cpu for qurey query_id; -- [查看耗时在哪里]
语法5: desc|explain select 字段列表 from 表名 where 条件;    -- [explain执行计划]
例子5: 结果重点关注字段含义 id, select_type, type, ref, extra

索引——使用规则

语法1: 验证索引效率
例子1: create index 索引名 on 表名(字段1, 字段2, ...)
语法2: 最左前缀法则(针对联合索引); 如果跳过某一列,后面的索引失效;最左边的列不存在,索引失效,位置顺序无关  -- [验证索引效率]
语法3: 范围查询(针对联合索引); 出现范围查询(<,>), 查询范围右侧的列索引失效      -- [范围查询——索引失效1]
语法4: 索引列运算(索引列上运算,索引失效);为phone添加索引，使用字串截取函数,索引失效
例子4: explain select * from tb_user where substring(phone, 5, 2) = '15'; -- [索引列运算——索引失效2]
语法5: 字符串不加引号,索引失效
例子5: explain select * from tb_user where phone = 18727097810; -- [字符串没有使用引号——索引失效3]
语法6: 模糊匹配  前面%,索引失效(后面不失效)
例子6: explain select * from tb_user where name like '%华';      -- [前面模糊匹配——索引失效4]
语法7: or连接的条件(都有索引，索引才会生效果)
例子7: explain select * from tb_user where id = 4 or name = '胡丽';  -- [name无索引——索引失效5]
语法8: 如果MYSQL评估使用索引比全表更慢,则不使用索引         -- [数据分布影响——不用索引6]
语法9: SQL提示(加入人为的提示,优化操作)
例子9: explain select * from tb_user (use|ignore|force) index(xxx) where phone='1567110';  -- [指定索引]
语法10: 聚集索引(id,挂所有数据*)和辅助索引(其他字段的索引,挂id)            -- [回表查询——覆盖索引]   
例子10: NULL,using index condition(回表); using where,using index(不回表) explain select * from tb_user where name = '张三';   -- name建立二级索引,叶子节点挂的是id,需要再根据id去获取数据 (回表查询)
语法11: 字符串的一部分前缀,建立索引,节约索引空间,提高索引效率             -- [前缀索引]
例子11: create index 索引名 on 表名(字段名(n)); -- 字符串的前n个字符串,建立索引count(distinct xxx)/count(*) 选择
语法12: 联合索引与单个索引 MYSQL优化器会评估哪个字段的索引效率更高,选择该索引完成本次查询  --[单索引与联合索引]
语法13:     --- [索引设计原则]
1-数据量大,查询比较频繁的表建立索引 
2-常作为查询条件where,order by, grouy by的字段建立索引 
3-尽量选择区分度高的列作为索引,尽量建立唯一索引 
4-字符串类型且长度较长,可以建立前缀索引 
5-尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节约索引空间,避免回表,提高查询效率
6-空值索引的数量,索引并不是越多越好;越多,结构的代价越大,影响增删改的效率
7-如果索引不能存储NULL,在建表的时候使用NOT NULL来约束;优化器知道每列是否包含NULL时,更好确定使用哪个索引最有效地查询