mysql学习记录

RDBMS(Relational Database Management System)—关系数据库管理系统，是建立在关系模型基础上的数据库，借助集合代数等数学概念和方法来处理数据库中的数据
关系型数据库主要产品：
oracle：在以前的⼤型项⽬中使⽤,银⾏,电信等项⽬mysql：web时代使⽤最⼴泛的关系型数据库ms sql server：在微软的项⽬中使⽤

sqlite：轻量级数据库，主要应⽤在移动

关系型数据库与非关系型数据库的区别：

1、关系型数据库最典型的结构是表，由二位表及其之间的联系所组成的一个数据组织
2、非关系型数据库严格上来说不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等
   非关系型数据库分类：
   （a）文档型  CouchDB、MongoDB 
   （b）key-value型 Redis、Voldemort、Oracle BDB
   （c）列式数据库  Cassavdra、HBase、Riak
   （d）图形数据库 InfoGrid、Infinite、Graph

关系型数据库的核心元素：

• 数据行
• 数据列
• 数据表
• 数据库

mysql是一个小型关系数据库管理系统，与其他的大型数据库例如Oracle、DB2、SQL Server等相比功能较弱，但其也拥有如下特点：

• 可以处理上千万条记录的大型数据
• 支持常见的sql语句规范
• 可移植行高，安装简单小巧
• 良好的运行效率，有丰富信息的网络支持
• 调试、管理、优化较简单

mysql的数据类型：
mysql支持多种类型，大致可以分为三类：数值、日期/时间、字符串(字符)类型
mysql支持所有标准sql数值数据类型
这些类型包括严格数值数据类型(integer、smallint、decimal、numeric)、以及近似数值数据类型(float、real、double percision)

数值类型

日期与时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR

字符串类型

一个汉字占多少长度与编码有关：
UTF－8：一个汉字＝3个字节
GBK：一个汉字＝2个字节
2、varchar(n) 表示 n 个字符，无论汉字和英文，Mysql 都能存入 n 个字符，仅是实际字节长度有所区别
3、MySQL 检查长度，可用 SQL 语言来查看：select LENGTH(fieldname) from tablename

---

mysql表的别名
可以使用别名为表添加不同的名称，使用as关键词
select id as ‘numid’ from test1;
mysql消除重复行
select * from test1 where time in (select time from test1 group by time having count(time)>1);

distinct消除重复行：
select distinct time,id,age from test2 where id in (select distinct min(id) from test2 group by time);
mysql模糊查询
like、not like、instr、locate、position

• select * from test2 where time like ‘%15650588%’;
• select * from test2 where time not like ‘%15650588%’;
• select * from test2 where instr(time,‘15650588’)>0;
• select * from test2 where locate(‘1565058’,time)>0;
• select * from test2 where position(‘1565’ in time);

mysql范围查询

• where、in、not in、between and、not between and 、null、or、and

• select * from test2 where id not in (1,2);

• select * from test2 where id between 1 and 2;

• select * from test2 where id not between 1 and 2;

• select * from test2 where id=null;

• select * from test2 where id=2 or age=2;

• select * from test2 where id=2 or age=1;
  mysql排序查询

• order by

• desc(倒排) asc(正排)
  mysql聚合查询
  常用的聚合查询有：sum() avg() max() min() count() round()

• select sum(id) as ‘idsum’ from test2;

• select avg(id) as ‘avg’ from test2;

• select max(id) as ‘max’ from test2;

• select min(id) as ‘max’ from test2;

• select count(id) as ‘max’ from test2;

• select count(id) as ‘idcount’ from test2;

• select round(avg(id),2) as ‘avg’ from test2; #四舍五入，保留两位小数
  mysql分组查询
  group by
  -select * from test2 group by time\G;

mysql分页查询
MySQL一般使用 LIMIT 实现分页，基本语句为
SELECT … FROM … WHERE … ORDER BY … LIMIT …

• select * from test2 order by id limit 2\G；
• 随着数据量的增加，页数会越来越多，查看后几页的SQL就可能类似下面这种
  select * from table order by id limit m, n; 该语句的意思为，查询m+n条记录，去掉前m条，返回后n条记录。无疑该查询能够实现分页功能，但是如果m的值越大，查询的性能会越低（越后面的页数，查询性能越低），因为MySQL同样需要扫描过m+n条记录- `
• 可以看出，越往后分页，LIMIT 语句的偏移量就会越大，速度也会明显变慢。此时，我们可以通过子查询的方式来提高分页效率(因为子查询是在索引上完成的，而普通的查询时在数据文件上完成的，通常来说，索引文件要比数据文件小得多，所以操作起来也会更有效率)
  ``
• SELECT * FROM articles WHERE id >= (SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) AND category_id = 123 ORDER BY id LIMIT 10;

还可以通过join的方式实现分页查询
SELECT * FROM articles AS t1
JOIN (SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) AS t2
WHERE t1.id >= t2.id AND t1.category_id = 123 ORDER BY t1.id LIMIT 10;

**mysql主键与外键**
 - .什么是主键
 　　数据库主键，指的是一个列或多列的组合，其值能唯一地标识表中的每一行，通过它可强制表的实体完整性。主键主要是用于其他表的外键关联，以及本记录的修改与删除
 - 主键必须唯一且非空！
 - 什么是外键
　　如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。
　　
 - foreign key (emp_id) references employees (id)
 

python操作mysql

1、连接MySQL数据库
db=pymysql.connect(host="127.0.0.1",user="root",passwd="root",port=3306,db="test")
2、使用cursor()方法创建一个游标对象
cursor=db.cursor()
3、创建一个表
sqll='CREATE TABLE renren(title varchar(40),amount int,month int,interest float)'
4、使用cursor()方法执行sql语句
cursor.execute(sqll)

mysql视图

1、视图封装了对多张基本表的复杂操作，简化用户操作
2、视图只是一个虚表,并不存储任何基本表的表数据,当用户使用视图的时候 视图会从基本表中取出
3、通过视图可以对用户展示指定字段从而屏蔽其他字段数据，更加安全

mysql事务

事务Transaction，是指作为一个基本工作单元执行的一系列SQL语句的操作，要么完全地执行，要么完全地都不执行
事务的四大特性ACID
1、原子性(Atomicity)
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
2、一致性(Consistency)
数据库总是从一个一致性的状态转换到另一个一致性的状态。
3、隔离性(Isolation)
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的
4、持久性(Durability)
一旦事务提交，则其所做的修改会永久保存到数据库

mysql索引

什么是索引

 - 能够快速查询数据的线索就称之为索引
 - 创建索引：
 - create index 索引名称 on 表名(字段名称(长度))   - 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致    - 字段类型如果不是字符串，可以不填写长度部分
 - 查看表中索引：
 -show index from 表名
 -删除索引： 
 - drop index 索引名称 on 表名;
 - 索引小结：
 - 索引可以明显提高某些字段的查询效率
 - 但不是所有的表都需要建立索引
 - 如果表中数据很少，没有必要建立索引
 - 如果一个表中的数据增删很频繁，不能建立索引 ，因为只要数据发生增减，索引就要重新建立。增加了系统开销，反而慢了
 - 索引只适合查询操作频繁的表