MySql的使用方法

一.什么是MySql

MySql是一种数据库管理系统，是用来存储数据的，可以有效的管理数据，数据库的存储介质为硬盘和内存。

和文件相比，它具有以下优点：

文件存储数据是不安全的，且不方便数据的查找和管理，在程序中控制不方便，不利于海量存储数据。所以数据库的水平用来衡量一个程序水平的重要指标。

二.数据库的基本使用语法

连接数据库

输入：mysql -h 127.0.0.1 -P 3306 -u root -p

没有-h 默认的是本地连接，没有-p 默认是3306端口号。

退出数据库：quit

创建数据库：

语法：

create database [if not exists] db_name [create_specification ,[create_specification]…]

create_specification:
[delault] character set charset_name
[delault] collate collation_name

说明：

小写的表示关键字
[] 是可选项
character set: 指定数据库采用的字符集
collate: 指定数据库字符集的校验规则

db_name:数据库的名字

创建数据库的案列：

creatre database sss;

说明：

当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则是：utf8_ general_ ci

创建一个使用utf8字符集的数据库：

create database sss charset=utf8;

创建一个使用utf字符集，并带校对规则的数据库:

create database sss charset=uft8 collate_utf8_general_ci;

查看数据库的字符集和效验规则

查看系统默认字符集以及校验规则：

show variables like “character_set_database”;

show variables like “collaiton_database”;

查看数据库支持的字符集：

show charset;

字符集主要是控制用什么语言。比如utf8就可以使用中文

查看数据库支持的字符集校验规则

show collation;

效验规则对数据库的影响：

• 不用区分大小写

创建一个数据库，校验规则使用utf8_ general_ ci[不区分大小写]:

create database sss collation utf8_general_ci;

• 区分大小写：

创建一个数据库，校验规则使用utf8_ bin[区分大小写]：

createdatabase sss collation utf8_bin;

操作数据库：

查看数据库：

show databases;

显示创建语句:

show create database 数据库名字;

实例：

说明：

MySQL 建议我们关键字使用大写，但是不是必须的。
数据库名字的反引号“,是为了防止使用的数据库名刚好是关键字
/*!40100 default…. */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话

修改数据库

语法：

alter database db_name

[alter_spacification [,alter_spacification]…]

alter_spacification:
[default] character set charset_name
[default] collate collation_name

说明：

对数据库的修改主要指的是修改数据库的字符集，校验规则。

数据库的删除

drop database db_name;

执行删除之后的结果:
数据库内部看不到对应的数据库
对应的数据库文件夹被删除，级联删除，里面的数据表全部被删

注意：不能随便删除数据库！！！

备份和恢复

备份

语法：

# mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径

示例：将mytest库备份到文件（退出连接）

# mysqldump -P3306 -u root -p123456 -B sss > D:/mytest.sql;

这时，可以打开看看mytest.sql 文件里的内容，其实把我们整个创建数据库，建表，导入数据的语句都装载这个文件中。

还原

语法：

mysql> source D:/mysql-5.7.22/mytest.sql;

注意：

如果备份的不是整个数据库，而是其中的一张表，怎么做？

# mysqldump -u root -p 数据库名 表名1 表名2 > D:/mytest.sql；

同时备份多份数据库

# mysqldump -u root -p -B 数据库名1 数据库名2 … > D:/mytest.sql;

如果备份一个数据库时，没有带-B参数，在恢复数据库时，需要先创建一个空的数据库，然后使用数据库，在使用source来还原。

查看连接情况:

show processlist

• Id列：一个标识，可以在MySQL中通过kill id杀死指定id的线程。
• User列：显示当前用户，如果不是root，这个命令就只显示你权限范围内的SQL语句。
• Host列：显示这个语句是从哪个IP的哪个端口上发出的，可用来追踪出问题语句的用户。
• db列：当前执行的命令是在哪一个数据库上，如果没有指定数据库，则该值为NULL。
• Command列：显示当前连接执行的命令，一般就是休眠（Sleep）、查询（Query）和连接（Connect）。
• Time列：表示该线程处于当前状态的时间，单位是秒。
• State列：显示使用当前连接的SQL语句的状态。
• Info列：一般记录的是线程执行的语句，默认只显示前100个字符，如果要看全部信息，需要使用show full processlist。

可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登陆的，很有可能你的数据库被人入侵了。以后大家发现自己数据库比较慢时，可以用这个指令来查看数据库连接情况。

三.数据库表的操作

创建表

语法：

create table db_name(

field1 datatype,
field2 datatype,
field3 datatype

)character set 字符集 collate 校验规则 engine 存储引擎;

说明：

field 表示列名
datatype 表示列的类型
character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准
collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准

engine 存储引擎（后面说明）

案例：

create table user(

name varchar(20),

age int,

birday varchar(20),

sex char(4)

)character set utf8 engine MyISAM;

说明：

不同的搜索引擎·，创建表的文件不一样，user表存储引擎是MyISAM ，在数据目中有三个不同的文件，分别是：
user.frm：表结构
users.MYD：表数据
users.MYI：表索引

备注：创建一个engine是innodb的数据库，观察存储目录

create table person(

name varchar(20),

age int,

birday varchar(20),

sex char(4)

)character set utf8 engine innodb;

查看表结构：

desc db_name

案例

修改表：

添加类型

alter table table_name add(column datatype [default expr]…..)

修改类型

alter table table_name modify(column datatype)

删除类型

alter table table_name drop (column);

删除表

drop [temporary]] table [if exits] table_name

插入内容

insert into table_name values ()

四.表的类型

说明：

• 在mysql中插入数据时一定不能越界，否则就会导致插入错误。
• 在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。
• 可以通过UNSIGNED来说明某个字段是无符号的。
• 注意：尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不
  下，与其如此，还不如设计时，将int类型提升为bigint类型。
• bit字段在显示时，是按照ASCII码对应的值显示，如果我们有这样的值，只存放0或1，这时可以定义bit(1)。这样可以节省空间。
• decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10。建议：如果希望小数的精度高，推荐使用decimal。
• 关于varchar(len),len到底是多大，这个len值，和表的编码密切相关：
  varchar长度可以指定为0到65535之间的值，但是有1 – 3 个字节用于记录数据大小，所以说有效字节数是65532。当我们的表的编码是utf8时，varchar(n)的参数n最大值65532/3=21844[因为utf中，一个字符占用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766,在gbk中一个字符大小为2。

如何选择定长或变长字符串？

如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5

• 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。
• 定长的磁盘空间比较浪费，但是效率高。
• 变长的磁盘空间比较节省，但是效率低。
• 定长的意义是，直接开辟好对应的空间。
• 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多。

五：表的约束

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。
表的约束很多，这里主要介绍如下几个： null/not null,default, comment, zerofill，primary
key，auto_increment，unique key。

空属性：

两个值：null（默认的）和not null(不为空)
数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。

默认值：

某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。

默认值的生效：数据在插入的时候不给该字段赋值，就使用默认值。

注意：只有设置了default的列，才可以在插入值的时候，对列进行省略。

列描述：

comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。

注意：not null和defalut一般不需要同时出现，因为default本身有默认值，不会为空。

zerofill:

如果宽度小于设定的宽度（这里设置的是5），自动填充0。要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1。

主键：

primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。

创建表的时候·直接在字段上指出主键。

主键约束：主键对应的字段中不能重复，一旦重复，操作失败。

当表创建好以后但是没有主键的时候，可以再次追加主键，也可以删除主键。

在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。

自增长：

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。
自增长的特点:

• 任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
• 自增长字段必须是整数
• 一张表最多只能有一个自增长

索引：
在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。
数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。、

唯一键(unique)：

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。
唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。
关于唯一键和主键的区别：
我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。

外键：

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。
语法：

foreign key (字段名) references 主表(列)

如何理解外键约束：

首先我们承认，这个世界是数据很多都是相关性的。
理论上，上面的例子，我们不创建外键约束，就正常建立学生表，以及班级表，该有的字段我们都有。此时，在实际使用的时候，可能会出现什么问题？
有没有可能插入的学生信息中有具体的班级，但是该班级却没有在班级表中？
比如比特只开了比特100班，比特101班，但是在上课的学生里面竟然有比特102班的学生(这个班目前并不存在)，这很明显是有问题的。
因为此时两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题。

解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给mysql去审核了，提前告诉mysql表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql不允许你插入。

六.内置函数

日期函数：

字符串函数：

数学函数：

其他函数：