python开发面试准备之mysql的总结

python开发面试准备之mysql总结

增删改查

DML：数据操作语言—>基础的增删改查，insert delete updata select
1）安装
2）服务管理

• 启动：/etc/init.d/mysql start
  停止：stop参数
  查看状态：status参数
• 查看端口：netstat -an | grep 3306
  3）库管理
• 查看库：show databases;
• 进入库：use 库名;
• 创建库：create DATABASE 库名 [字符集];
• 查看库中的表：show tables;
  4）表管理
• 创建表
  CREATE TABLE 表名(
  字段1 类型(长度)
  字段2 类型(长度)
  id INT PRIMARY KEY auto_increment,
  …
  );
• 查看表结构：desc 表名;
• 查看建表语句：show CREATE TABLE 表名

5）数据管理

• 插入
  INSERT INTO orders
  values(‘20180101’,‘C0001’,now(),1,1,100)

  INSERT INTO orders(order_id, cust_id)
  values(‘20180101’,‘C0001’)

  INSERT INTO orders(order_id, cust_id)
  VALUES(‘20180101’,‘C0001’),
  (‘20180102’,‘C0002’);

• 查询
  SELECT * FROM orders;
  SELECT order_id, cust_id FROM orders;
  SELECT * FROM orders WHERE cust_id=‘C0001’;

  SELECT * FROM orders
  WHERE cust_id = ‘C0001’
  AND status = 1;
  修改记录

1. 语法
   UPDATE 表名
   SET 字段1 = 值1,
   字段2 = 值2,
   …
   WHERE 条件表达式;
2. 示例

• 修改某个订单的状态
  UPDATE orders SET status = 2
  WHERE order_id = ‘201801010001’;

• 修改订单的商品数量和订单金额
  UPDATE orders
  SET products_num = 2,
  amt = 400
  WHERE order_id = ‘201801010002’;

3. 语法
   DELETE FROM 表名 WHERE 条件表达式;

4. 示例
   – 删除201801010002订单的信息
   DELETE FROM orders
   WHERE order_id = ‘201801010002’;

5. 特别注意
   1）进行严格条件限定，如果不带条件删除所有
   2）真实项目中，删除、修改数据之前一定要备份

6. 带比较操作符的查询：>,<,>=,<=,<>(或!=)
   示例1：查询所有订单金额大于200元的订单
   SELECT * FROM orders WHERE amt > 200;
   示例2：查询所有状态不为2的订单
   SELECT * FROM orders WHERE status <> 2;

7. 联合查询

   1）什么是联合查询：也叫连接查询，将多个表中的
   数据进行连接，得到一个查询结果集
   2）什么情况下使用联合查询：当从一个表无法查询
   到所有想要的数据时，使用联合查询
   前提：联合的表之间一定要有逻辑上的关联性
   SELECT a.order_id, a.amt,
   b.cust_name, b.tel_no
   FROM orders a, customer b
   WHERE a.cust_id = b.cust_id;

   3）笛卡尔积（联合查询的理论依据）

   • 什么是笛卡尔积：两个集合的乘积，产生一个
     新的集合。表示两个集合所有的可能的组合情况
   • 笛卡尔积和关系：笛卡尔积中，去掉没有意义
     或不存在的组合，就是关系（规范的二维表）

   4）连接查询

   • 内连接(INNER Join)：没有关联到的数据不显示
     示例：查询订单编号、金额、客户名称、客户电话
     – 方式一：where进行条件关联
     SELECT a.order_id, a.amt,
     b.cust_name, b.tel_no
     FROM orders a, customer b
     WHERE a.cust_id = b.cust_id;
     – 方式二：利用Inner join关键字
     SELECT a.order_id, a.amt,
     b.cust_name, b.tel_no
     FROM orders a INNER JOIN customer b
     ON a.cust_id = b.cust_id;
   • 外连接(OUTER Join)：没有关联到的数据也要
     显示到结果集
     1)左连接：以左边为基准，右表的数据进行关联
     左表数据全部显示，右表中的字段
     如果没有关联到，则显示NULL
     LEFT JOIN 实现
     SELECT a.order_id, a.amt,
     b.cust_name, b.tel_no
     FROM orders a LEFT JOIN customer b
     ON a.cust_id = b.cust_id;
     2)右连接：以右边为基准，左表的数据进行关联
     右表数据全部显示，左表中的字段
     如果没有关联到，则显示NULL
     right JOIN 实现
     SELECT a.order_id, a.amt,
     b.cust_name, b.tel_no
     FROM orders a right JOIN customer b
     ON a.cust_id = b.cust_id;

索引

1. 什么是索引：提高查询效率的一种技术
2. 原理：根据某一列（字段）进行分段、排序，通过避免
   全表扫描提高查询效率
3. 索引分类
   1）普通索引、唯一索引
   普通索引：MySQL基本类型，字段值可以重复
   唯一索引：字段的值不能重复（可以为空）
   2）单列索引、组合索引
   单列索引：一个索引只包含一个字段
   组合索引：一个索引包含多个字段
   3）聚集索引、非聚集索引
   聚集索引(Cluster Index): 索引的键值顺序和数据
   顺序是一致的
   非聚集索引：索引的键值顺序和数据
   顺序不一致的
   4）如何创建索引

• 语法：index | UNIQUE | PRIMARY KEY
• 示例：创建index_test表，在name字段上建立普通索引
  在cert_no上建立唯一索引
  CREATE TABLE index_test(
  id INT PRIMARY key,
  cert_no VARCHAR(32),
  name VARCHAR(32),
  UNIQUE(cert_no), INDEX(name)
  );
  查看索引：show INDEX FROM index_test;
  INSERT INTO index_test
  VALUES(1,‘0001’, ‘Jerry’);
  INSERT INTO index_test
  VALUES(2,‘0001’, ‘Jerry’); – cert_no违反约束

5）删除索引

• 语法：drop INDEX 索引名称 ON 表名称;
• 示例：
  – 删除cert_no
  DROP INDEX cert_no ON index_test;
  – 删除名称为name的索引
  DROP INDEX name ON index_test;
  6）修改表的方式添加索引
• 语法: CREATE 索引类型 索引名称 ON 表(字段)
• 示例：
  – 在index_test表cert_no字段创建唯一索引
  CREATE UNIQUE INDEX idx_cert_no
  ON index_test(cert_no);

7）实验：索引效率测试（20分钟）
第一步：利用现有的orders表，插入10万笔数据
执行insert_orders_many.py文件
第二步：在没有索引的情况下查询，条件如下：
order_id = ‘2018010100000002’
order_id = ‘2018010100055555’
order_id = ‘2018010100099996’
第三步：给orders表添加索引
CREATE INDEX idx_order_id ON orders(order_id)
再执行第二步的查询，查看执行时间
备注: 如果执行文件报错，检查连接参数
核对字段的名称、顺序、类型
pymysql导入出错，因为缺少了pymysql模块，更换
到教学机上执行

8）索引的优缺点

• 优点
  提高查询效率
  唯一索引能够保证数据的唯一性
  可能提高分组、排序的效率

• 缺点
  降低增、删、改的效率（调整索引结构的开销）
  对表中的数据进行增删改操作，需要调整索引结构

  需要增加额外的存储空间

9）索引使用注意事项

• 总体原则
  在合适的字段上，建立合适的索引
  索引不能太多，过多的索引会降低增删改效率
• 适合使用索引的情况
  在经常进行查询、排序、分组的字段上建立索引
  数据分布比较均匀、连续的字段，适合建立索引
  查询操作较多的表，适合建立索引
• 不适合建立索引的情况
  数据量太少的表不适合建立索引
  增删改操作较多的表，不适合建立较多的索引
  某个字段取值范围很少，不适合建索引
  某个字段很少用作查询、排序、分组，不适合建索引
  二进制字段不适合建立索引

10）索引失效的SQL语句
索引失效：表中有索引，但是查询时候没有使用

• 没有使用索引字段作为条件，会导致放弃使用索引
• 条件判断中使用了<>符号，会导致放弃使用索引
• 条件判断语句中使用了null值判断，会导致放弃使用索引
• 模糊查询%前置，会导致放弃使用索引
• 对字段做运算，会导致放弃使用索引
  存储引擎

1. 什么是存储引擎：表的物理实现方式，由于物理
   实现不一样，决定不同存储引擎类型的技术特性
   不一样，例如：存储机制，索引机制，锁定方式
2. 查看存储引擎
   1）查看MySQL支持的存储引擎:show engines;
   2）查看某个表的存储引擎
   show CREATE TABLE 表名称;
   3）修改表的存储引擎
   ALTER TABLE 表名称 engine = 引擎名称;
   4）示例：创建表，并修改存储引擎
   CREATE TABLE t3 (
   id INT PRIMARY key,
   name VARCHAR(32)
   ) engine = InnoDB;
   ALTER TABLE t3 engine=MyISAM;
   查看：show CREATE TABLE t3;

常用存储引擎特点

1）InnoDB(MySQL5.5及以后的版本默认)

• 特点：支持事务、支持行级锁、支持外键、
  共享表空间

• 文件构成：
  *.frm: 表的结构、索引
  *.ibd: 表的数据

• 实验：查看表的存储文件
  说明：通过下面的指令可以查看数据存储目录
  show global variables like ‘%datadir%’;

如果权限不够，使用sudo -i 进入root用户，
进入上面的目录查看

cd /var/lib/mysql
ls orders.* (查看orders表的存储文件)

• 什么时候选用InnoDB:
  更新(增删改)操作密集的表；
  要求支持数据库事务、外键；
  自动灾备和恢复；
  要求支持自动增长(auto_increment)字段；

• MyISAM
  特点：支持表级锁定；不支持事务、外键、行锁定
  独占表空间；该类存储引擎容易损坏，所以
  灾备、恢复性能不佳
  文件构成：
  *.frm: 表结构
  *.myd: 数据
  *.myi: 表索引
  适用场合：
  查询请求较多；
  数据一致性要求较低；
  没有外键约束要求；

• Memory
  特点：表结构存与磁盘，数据存在内存中
  服务器重启或断电后，表中的数据丢失
  文件：.frm 表结构
  使用场合：数据量小；数据需要快速访问；
  数据丢失不会造成损失；
  实验：
  第一步：修改t3的存储引擎为Memory
  ALTER TABLE t3 engine=Memory;
  第二步：查看文件
  sudo -i
  cd /var/lib/mysql/eshop
  ls t3.
  第三步：插入一条数据，查询(可以看到数据)
  insert into t3 values(1,‘Jerry’);
  第四步：重启服务，再查询（数据消失）
  /etc/init.d/mysql restart

硬性知识点：

1.三表的内连接+group by + 子查询的综合查询

2.三范式

第一范式(确保每列保持原子性)
第二范式(确保表中的每列都和主键相关)
第三范式(确保每列都和主键列直接相关,而不是间接相关)
具体见：https://www.cnblogs.com/qingping-xv/p/10813104.html

3.4级隔离级别

具体见：https://blog.csdn.net/u011861874/article/details/81539306

4.innodb和myisam引擎的区别，

上面有

5.多对多表的反范式优化

增加冗余字段来增加查询的效率。

6.索引本质是二分法查找。