数据库

数据库名称

1.如果数据框名是关键词，需要用反引号``括起来。查询like语句则需要用单引号''。
2.引用数据库名称时可以用反引号。
3.重命名数据库，打开数据库所在位置，重命名该数据库文件夹。

查看数据库物理位置

show global variables like "%datadir%";

表

查询

1.用show语句查看表属性的信息，以\G结尾比以；结尾，查询到的信息查看更清晰。
2.desc查看表的结构、数据。

表名

1.重命名rename table tbl_name to tbl_name1
2.rename table tbl_name1 to db_name.tbl_name1,tbl_name2 to db_name.tbl.name2;还可用于表的转移，转移到其他数据库。

列

1.添加列
alter table tbl_name add col_name type;
2.删除列
alter table tbl_name drop col_name;
3.定义列名
alter table tbl_name change col_name1 col_name2 type;#修改表名
4.定义列属性
alter table tb1_name modify col_name type;#修改表结构

简单操作——增删改查

1.增
insert into tbl_name (col_name1,col_name2,…) values (value1,value2)
2.查
select col_name… from tbl_name where …
3.改
update tbl_name set col_name='new_value',… where…
4.删
delete from tbl_name where …

校对规侧

1.查看
1.1show variables like 'character_set_%'#查看所用编码
1.2show collation#查看校对规则
gbk 国标 vs utf8 万国标
2个字节 vs 3个字节
2.设置
2.1设置编码character set … (gbk\utf8…)
2.2设置校对选项collate … (gbk\utf8…) #按照校验规则排序、显示

查看校验字符集 show collation;

整数型数据

create table tbl_name(col_name int(M) [unsigned] [zerofill])
1.显示数据宽度zerofill
2.无符号unsigned
3.M为最小显示位数

小数型数据

float单精度：默认精度为7个，容易出错（取决于CPU）
double双精度：默认精度为17个，通常认为为16个
整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

时间型数据

1.不显示时间格式，只显示数字
select 0+col_name from tbl_name
2.YEAR类型范围
1901-2155

文本型数据

1.char型最大长度255字节，utf8编码最大存放为85个字，其中一个字占3字节；gbk编码最大存放127个字节，其中一个字占2字节。
2.char的效率优于varchar，但varchar的存储空间优于char。

实体之间的关系

1.一对一 1：1
2.一对多 1：n
3.多对多 m : n

外键

作用：保证数据的完整性，建立表之间的连接，主表信息不能删除，除非先从从表中删除信息。总的来说是约束主表与从表的数据结构关系。
1.新建外键
alter table tbl_name从表 add foreign key (col_name) references tbl_name主表（col_name);
例如语法：
alter table i_student add foreign key (class_di) references i_class(class_id) on delete set null;
on delete set null表示删除主表时，从表外键设置为null
2.级联动作，即设置外键
on delete/update cascade:从表跟随主表执行相同操作
on delete/update restrict:从表拒绝跟随主表执行动作

存储引擎

image.png
1.mysql默认存储引擎为INNODB，唯一支持外键
2.myisam善于查询和插入，是表检索；innodb善于更新和删除，是行检索
其他存储引擎myisam、BDB……
image.png
3.更改存储引擎
alter table tbl_name engine …

正文

优化查询

order 1.排序order by col_name,默认升序，添加desc为降序
limit 2.限制查询结果 limit n或者limit x,n（x为开始值，n为显示数量）
distinct 3.显示非重复值 select distinct col_name from tbl_name;
union 4.联合查询 (select……) union (select……)需配合order by 和limit。
union all 5.7版本和union没有区别

子查询

一般语法select * from tbl_name where col_name=(select fun(col_id) from tbl_name);

联合查询

1.内连接语法
tbl_left inner join tbl_right on 连接条件，根据右表查询
2.外连接语法
tbl_left left outer join tbl_right on 连接条件，根据左表查询
3.using需要两个表列名一致，例…tbl_left inner join tbl_right using(col_name);
4.union和join的区别：
union是记录的组合，纵向组合；join是字段的组合，横向组合。

表的连接

1.内连接vs外连接
内连接：select * from tbl_left inner join tbl_right on/using…按照相同项连接
外连接
左外连接：select * from tbl_left left outer join tbl_right on/using…按照左表进行连接，右边缺失值默认为NULL
右外连接：select * from tbl_left right outer join tbl_right on/using…按照右表进行连接，左边缺失值默认为NULL
2.另：自然连接 第一列显示共同部分
自然内连接：select *from tbl_left nature join tbl_right;
左外连接：select *from tbl_left nature left join tbl_right;
右外连接：select *from tbl_left nature right join tbl_right;
3.全连接full join

导出文件outfile

select * from tbl_name into outfile '存储位置(例:e:/data/abc.txt)'
注意：57版本后的mysql导出数据需更改my.inI配置文件。
更改方式，查看隐藏文件夹，路径为C:\ProgramData\MySQL\MySQL Server 5.7，找到secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"，更改存储位置，保存，如果不能保存，则需要获得文件夹权限。重启mysql即可。
更改权限方式：属性——安全——编辑——Users——选中修改——确定
顺带一提，excel似乎是默认以gbk打开的，如果是utf8的表，导出为xls后可能会出现中文乱码。

备份/还原

备份：在mysql外运行，mysqldump -hlocalhost -P3306 -uroot -p tbl_name col_name1 col_name2 > e:/myde/i_t.sql(存储路径)
还原：source e:/myde/i_t.sql(存储路径)

视图

作用:缩小数据库的量
1.创建视图
create view view_name as select * from tbl-name
2.修改视图
alter view view_name as select * from tbl-name
3.删除视图
drop view [if exists] view_name

事物的使用

事物：一组sql语句操作单元，组内所有sql语句完成业务。
支持事物安全的数据库：INNODB、BDB
1.显示自动提交变量值 show variables like 'autocommit';
2.关闭自动提交 set autocommit=0;
3.打开自动提交 set autocommit=1;
4.关闭自动提交的条件下，提交commit; ，滚回rollback; 。
5.不更改变量的方法 start transaction/begin;
事物的特点：原子性、一致性、隔离性、持续性。

触发器

1.创建触发器
create trigger tri_name 事件执行时机 事件 执行条件
事件：insert 、delete 、update
事件执行时机：after 、before
2.显示触发器
show trigger tri_name\G
3.删除触发器
drop trigger tri_name;
注意：触发器不能同名；触发器只支持一类事件设置一个触发器。
4.老数据和新数据
update可用new old
delete可用old
insert可用new
5.多条sql语句组成的触发器
delimiter$$ create trigger tri_name after insert on col_name for each row begin sql语句1; sql语句2; …… end $$ delimiter;

SQL编程

1.注释：# 和 --[空格]
2.set 变量名=变量值
3.在@后设置变量名（有效期：会话结束）
select col_name from tbl_name where …into @…；
4.随机数
select rand();
select floor(5+(rand()*5));#[5,10)之间的数
5.小数格式
select format(N,D);#N为数字，D为保留小数位个数
6.时间戳
select unix_timestamp();#获得当前时间的时间戳
select from_unixtime(156);#从时间戳获得时间
select from_unixtime(unix_timestamp());#获得当前时间
7.字符串
select substring('iloveyou',3,4);#显示字符开始的位数和显示数量
select length('iloveyou');#显示字符长度
select lpad('hi',3,'??');#补充字符
8.加密
select md5(1);
select password(1);
select sha(1);#三种加密方式，最常用为md5

自定义函数