mysql最全能编码_MySQL的一些不为人知的注意点

数据库

数据库名称

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

自定义函数