MySQL基础

=======================================database=======================================
//创建数据库
create database dbName;
//选择数据库
use dbName;
//显示库信息
show create database dbName;
//删除数据库
drop database dbName;

=======================================table=======================================

约束有  primary key    not null   unique    auto_increment
create table table_name(
字段名1  数据类型 约束 默认值,
字段名2  数据类型 约束 默认值,
...
[表级别的约束]
);

//主键
create table tmp1(
id int(11) primary key
);
create table tmp1(
id int(11),
name varchar(25),
primary key (id,name)
);

//外键
create table tmp2(
id int(11),
name varchar(25),
tmp1_id int,
constraint tmp1_tmp2_fk foreign key tmp1_id
references tmp1(id)
);
//查看数据结构
desc table_name;
show create table table_name;  //查看详细信息
//修改数据表
alter 关键字
alter table table_oldname rename to table_newname;    //修改表名
alter table table_name modify 字段名 new类型;    //修改字段数据类型
alter table table_name change old字段名  new字段名 数据类型;  // 修改字段名称
alter table table_name add 字段名 数据类型 [约束条件][first|after 已存在的字段名];  //添加字段
alter table table_name drop 字段名;  //删除字段
alter table table_name modify 字段名 数据类型 first|after 已存在的字段  // 修改字段的排列顺序
alter table table_name engine = 更改后的存储引擎 InnoDb  MyISAM;  //修改表的存储引擎
show create table table_name;
    alter table table_name drop foreign key ‘外键名称’;   删除表的外键约束

=======================================数据类型=======================================
数值型     unsigned 无符号     signed 有符号
 整型： tinyint   smallint  mediumint  int  bigint
              1        2        3       4     8
浮点型: float  double   decimal       float(5,1)  长度，小数点后位数
          4     8        m+2
日期时间型： date time datetime timestamp  year
字符串型：char       固定长度字符串
         varchar     长度可变字符串     L <= 255
                          tinytext  text   mediumtext  longtext
                          255B      65K       16M  4GB
varchar(n)  根据存储内容的规则，设置该值的大小
MySQL定义行的长度不能超过65535字节
所谓单行最大限制指的就是一张表中所有字段的所设置的长度不得超过65535字节，

======================================增删改查======================================
//插入
insert into table_name (column_list)  values(value_list);
//修改
update table_name set column1=value1,column2=value2 where condition;
//删除
delete from table_name [where (condition)]
清空表数据
delete from table_name;
truncate table table_name; 删除重新创建
//查询
select {*|字段列表} from 表列表 where 表达式
group by
having
order by
limit
//使用正则表达式查询
select * from fruits where f_name regex ‘y$’;

内连接 inner join
select suppliers.s_id,s_name,f_name from fruits,suppliers where fruits.s_id = suppiers.s_id;
select suppliers.s_id,s_name,f_name from fruits inner join suppliers on fruits.s_id = suppiers.s_id;
外连接 right join left join
select count(*)  from t_order_mainorder                                                                             61450
select count(*)  from t_ins_product                                                                                 480
select count(*) from t_order_mainorder m , t_ins_product pro WHERE m.ProductCode = pro.ProductCode;                 50745
select count(*) from t_order_mainorder m INNER JOIN t_ins_product pro on m.ProductCode = pro.ProductCode;           50745
select count(*) from t_order_mainorder m LEFT JOIN t_ins_product pro on m.ProductCode = pro.ProductCode;            61405
select count(*) from t_order_mainorder m right JOIN t_ins_product pro on m.ProductCode = pro.ProductCode;           51003




子查询
any、some  满足其中任一条件
select num1 from tbl1 where num1>any (select num2 from tbl2);
all关键字
select num1 from tbl1 where num1>all(select num2 from tbl2);
exists关键字
select * from fruits where exists (select s_name from suppliers where s_id =107);
in / not in关键字
select * from orders where o_num in (3003,3005);
带比较运算符的子查询
合并查询结果  union  union all

======================================index======================================
普通索引和唯一索引
单列索引和组合索引      最左前缀原则
全文索引  FULLTEXT,在char varchar text 类型上创建，只有  MyISAM 支持

索引的设计原则
a、索引不是越多越好。索引占用磁盘空间，会影响 insert update delete的性能。
b、索引中的列尽量少。
c、数据量小的表最好不要使用索引。
d、经常用到不同值比较多的列上建索引
e、当唯一性是某种数据本身特征的时，指定唯一索引，提高效率
f、在频繁进行排序或分组（group by或order by 操作）的列上进行索引，如果待排序的列有多个，可以建立组合索引

方式一：创建表时添加索引
create table table_name(
col_name 类型  约束，
col_name 类型  约束，
[unique|fulltext|spatial]  index  index_name (col_name  [length]) [asc| desc]
);
length 只有字符串类型的字段才能指定索引长度。



方式二:在已存在的表上添加索引
使用alter table 语句
alter table table_name add [unique|fulltext|spatial]  index index_name (col_name (length)) [asc|desc]

show index from table_name;

使用 create index on 语句
create [unique | fulltext |spatial] index on table_name (col_name(length)) [asc|desc];

删除索引
alter table table_name drop index index_name;
drop index index_name on table_name;


======================================explain======================================
explain 查询sql
 MySQL Explain详解，explain查询结果每列含义详细解释  https://blog.csdn.net/boss_way/article/details/91416887

1）select_type 行指定所使用的SELECT 查询类型，SIMPLE 表示简单查询，不使用UNION或子查询。其他可能PRIMARY / UNION / UNION RESULT / SUBQUERY等
2) table 行指定数据库读取的数据表的名字，它们按读取的先后顺序排列
3) type 行指定本数据表与其他数据表的关联关系，可能取值有system / const /eq_ref /ref /range/index/ALL
4) possible_keys 给出了MySQL在搜索记录时可选用的各个索引
5) key  MySQL实际选用的索引
6) key_len 行给出索引按字节计算的长度，key_len数值越小，表示越快
7) ref 行给出了关联关系中另一个数据表里的数据列的名字
8) rows 行是MySQL 在执行这个查询时预计会从这个数据表里读取的数据行的个数
9) extra 行提供了与关联操作有关的信息

======================================数据备份与恢复======================================
########备份
方法一：使用MySQLdump 命令进行备份 (推荐)
mysqldump -u user -h host -p  dbname [tbname,tbname...] >filename.sql
备份多个数据库 使用 --databases
mysqldump -u user -h host -p --databases dbname [tbname,tbname...] >filename.sql   必须指定至少一个dbname
mysqldump -u user -h host -p --all-databases > filename.sql

其他常用命令 参考 MySQL5.7从入门到精通@www.java1234.com.pdf 第14章 数据备份与恢复

方法二：直接复制 整个数据库目录
windows 下默认路径为：C:\Program Files (x86)\MySQL\MySQL Server 5.5\data
linux 下默认路径为： /var/lib/MySQL/
这是一种简单、快速、有效的备份方式。要想保持备份的一致性，备份前需要对相关表执行
LOCK TABLES操作，然后对表执行FLUSH TABLES。这样当复制数据库目录中的文件时，
允许其他客户继续查询表。需要FLUSH TABLES 语句来确保开始备份前将所有激活的索引页写入硬盘。
当然，也可以停职MySQL服务再进行备份操作。
这种方法对InnoDB存储引擎的表不适用。

方法三：使用MySQLhotcopy工具快速备份 MyISAM类型的表

########恢复
方法一：
mysql -u user -p [dbname] < filename.sql
方法二：
mysql> use database;
mysql> source D:\clock.sql
方法三：
直接将备份文件复制到数据库目录 InnoDB表不可用
方法四：
MYSQLhotcopy快速恢复

推荐方法一、方法二

自动备份


======================================MySQL日志======================================
MYSQL日志四种：二进制日志，错误日志，通用查询日志，慢查询日志
着重错误日志和慢查询日志 的查询。 启动和设置相关命令参考   参考 MySQL5.7从入门到精通@www.java1234.com.pdf 第15章 MySQL日志
show variables like 'log_error';    //查看错误日志