MySQL是一款免费开源、小型、关系数据库管理系统。当然,也有收费的功能更强大的商用版。
常用基础概念
表中的每一行称为记录(Record);
表的每一列称为字段(column);
主键(primary key):一个表(table)只有一个主键,用来唯一标识表中的某一条记录;可以由一个或多个字段,且为主键的字段不能为空(not null);
外键(foreign key):在表1中,通过某个字段,可以把数据与另一张表2关联起来,这种列称为外键;
索引(index):创建索引可以加快查询效率。
常用操作
操作 | 命令 | 备注 |
创建数据库 | create database db_name | 库名为db_name。创建的时候可以通过if not exits判断一下,如果不存在则创建,存在不创建。 |
查库 | show databases | 查看所有库 |
用库 | use db_name | |
删库 | drop database db_name | |
建表 | create table t_name ( field1 type1 [not null] [primiry key], field2 type2 [ot null], ...... ) | 表名为t_name,添加对应的表字段和字段的类型(int/varchar.....),是否能为空,主键等。 |
查表 | show tables | 查看所有表 |
用表 | use t_name | |
删表 | drop table t_name | |
查表结构 | describe t_name | 展示表的字段 |
查询 | select * from t_name where id=1 | 查询表t_name中id=1的数据 |
排序 | select field1,field2 from t_name order by field2 | 查询结果通过field2排序,默认为升序(ASC)。可以通过添加“DESC”指定为降序。 |
分页 | select field1,field2 from t_name order by field2 limit 10, 5 |
通常使用
order by limit m,n
来实现分页查询。
limit 10,5 就是 limit 5 offset 10的简写;limit限制每页展示的结果数量,offset是偏移量,就是从第几个开始展示。这里就是每页展示5条数据,展示第三页的5条数据。
|
分组 | select count(field1),field2 from t_name group by field2 | 根据field2进行分组展示查询的field1的总数。可以对多个列分组,即group by field1,field2...。 常用的聚合查询方法有:count/sum/avg/max/min,其中sum和avg对应列字段类型必须是数值型。 |
连接查询 | select t_a.f1, t_a.f2, t_b.f1 from t_a inner join t_b on t_a.f1 = t_b.f1 | 主表是from后接的表,join后接的连接表。 连接查询包括内连接inner join、外连接outer join(左连接left join、右连接right join和全连接full outer join)。 内连接取2个表的交集,右连接取右表存在的数据,左连接取左表存在的数据,没有数据的填充NULL,全连接取2个表的并集。 |
插入 | insert into t_name (field1, field2, field3) values (v1, v2, v3) | 插入的字段类型需要一一对应。如果字段值为关键字需要使用 ` ` 括起来。有默认值的字段不用插入。 |
更新 | update t_name set f1=v1, f2=v2 where id=1 | 需要注意,不使用where条件的话就是更新了整个表。可以更新一条或者多条。 |
删除 | delete from t_name where id=1 | 需要注意,不使用where条件的话就是删除了整个表。 |
查询的常用方法: | ||
distinct | select distinct field from t_name | 查询t_name表中不重复的field列 |
between...and | select * from t_name where field between 1 and 100 | 查询t_name表中field范围1到100的记录 |
in | select * from t_name where field in (‘2’,‘30’,‘88’) | 查询t_name表中field为2,30,88的记录 |
常用where判断条件
判断 | 说明 | 备注 |
= | 等于,where a = b | |
< | 小于,where a < b | |
> | 大于,where a > b | |
<= | 小于等于,where a <= b | |
>= | 大于等于,where a >= b | |
<> | 不等于,where a <> b | |
like | 匹配,where a like '%b%'; where a like '_b_'; | %任意匹配,含有b的数据就OK。如,'ab','bc','abc','abcd'; _单字符匹配。如,‘abc’; |
is not null | 不为空,where a is not null | 有时候字段为空查询会有问题,可以通过这个判断。 |
后台运行操作
Windows: 安装完mysql之后,可以通过 service mysqld start 启动服务,service mysql status 查看运行状态, service mysqld stop 停止服务,service mysql restart 重启服务。
MAC OS:mysql.server start启动服务,mysql.server status 查看运行状态,mysql.server stop 停止服务。
当mysql服务正常启动后,输入 mysql -uroot -p,然后输入密码进入。远程服务的话通过mysql -h 1.1.1.1 -uroot -p 指定IP。输入exit退出连接。
查看服务ps -ef | grep mysqld。
ALTER命令
修改数据表名或者字段时,使用alter命令。
添加字段(类型为字符串):alter table t_name add f1 varchar;
删除字段:alter table t_name drop f1;
修改字段(将f1的类型由varchar变为char):alter table t_name modify f1 char;
(将f1字段名改为f1_1,类型为char):alter table t_name change f1 f1_1 char;(change后接要修改的字段名字,然后指定新的字段名和类型)。
修改表名:alter table t_name rename t_name1;
修改表结构添加索引:alter table t_name add index index_name (f1);
添加主键:alter table t_name add primary key (f1);
删除外键:alter table t_name drop foreign key (f1);
还有挺多的,但是基本格式就是上面这样。
查看SQL执行计划
数据库查询性能问题,要求我们应该尽量减少慢sql。
- 出现慢sql的几种常见原因
- 无索引,索引失效导致慢查询:单表大数据量,没有建立索引,或者查询没有命中索引
- 锁等待: 等待大事务
- 不恰当的sql语句:select *,limit m, n
在要执行的sql语句前加explain
执行计划输出结果对应字段说明:
id:每个执行计划都会有个id,如果是联合查询会有多个id;
select_type:表示查询类型,常见的有simple(普通查询即没有联合查询、子查询)、primary(主查询)、union、 subQuery(子查询)等;
table:当前执行计划查询的表,如果起了别名就显示别名
partitions:访问表分区信息
type:表示从表中查询到行所执行的方式,查询方式是sql优化中一个很重要的指标,结果值从好到差依次:system > const > eq_ref > ref > range > index --> all
- system/const 表示只有一行数据匹配,根据索引查询一次就能获取数据
- eq_ref 使用唯一索引扫描,常见于多表连接中使用主键和唯一索引作为关联关系
- ref 非唯一索引扫描,还可以于唯一所有最左原则匹配扫描
- range 索引范围扫描 ,如 <,> between and
- index 索引扫描全表,遍历整个索引树
- all 表示全表扫描
possible_key:可能用到的索引
key:实际用到的索引
key_len:索引长度
ref:关联id等信息
row:查找到记录所扫描的行数
filtered:查询到所需记录占总扫描记录的比例
extra:额外信息