MySQL的CRUD操作

从Terminal中，可以对数据库进行链接，无需GUI界面就可以对数据库进行相关操作。对于Linux、Windows、MacOS，也可以使用可视化软件Navicat、MySQL Workbench进行GUI操作。

数据库命令行部分(使用终端操作数据库)：

使用Terminal可以进行CRUD操作，即对数据进行增删改查操作。CRUD=Create增、Retrieve查、Update改、Delete删。在下面的小节中，将不会按照市面上大部分文章中按照关键字的使用进行书写，而会按照语句的功能所对应的CRUD操作进行梳理，更便于今后使用的查找。因此，CRUD操作之间可能会存在重复。

用户及设置Log in &Settings：

登录数据库服务器：使用命令mysql -uroot -pPASSWORD可以利用终端登录数据库服务器。登陆后，Terminal中会显示“mysql>”来表示用户在和mysql进行交互。可以用长参数--host=localhost --user=root --password=PASSWORD来代替短参数-t -u -p。

增加新用户：不想使用root这么高的权限，但是又需要分配一个新用户给普通使用者，这时候可以使用grant进行分配。

如果想增加一个用户user1，密码是passwd1，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限，在root用户连入MySQL后，键入以下命令：

grant select,insert,update,delete on *.* to user1@"%" identified by "passwd1";

grant后可以加select、insert、update、delete、create、drop、index、alter、grant、references、reload、shutdown、process、file这14个权限，如果想要加上全部权限，用all或者all privileges； *.*表示数据库.数据表，可以指定可访问的数据库中的数据表；"%"表示任何连接地址，可以使localhost、ip地址、机器名字、域名，但不能为空。

但这样的坏处是，任何知道这个用户的人，只要可以连接上你的主机，就可以远程登录，修改你任何的数据库中数据表中的内容。我们常使用下面的命令增加普通用户：

grant select,insert,update,delete on Programme.* to Jack@localhost identified by "HiJack";

这样，用户Jack就只能在本机修改Programme数据库中的相关内容了。另外，也可以不设置密码，只要将原来输入密码的地方改成空字符串""即可。

修改密码：使用mysqladmin -uXXX -pPASSWORD password NEWPASSWORD可以对用户的密码进行修改。

查看用户权限：使用show grants for "XXX"@"localhost"可以查看用户XXX的权限，localhost表示连接地址。

解除用户权限：使用revoke privileges on 数据库名[.表名] from 用户名对此用户在某数据库中的某权限进行解除。

删除用户：在root登录MySQL后，可以使用drop user 'XXX'@'localhost';来删除数据库中的本地用户XXX。删除用户不会删除此用户操作过的数据库数据表。

查看关键字帮助文档：登录MySQL后，可以使用? KEYWORD来查看关键字的帮助文档。

六张自动创建的数据表：在数据库中，有四张表（是数据表还是数据库）是自动创建的，分别是：

INFORMATION_SCHEMA：信息数据库，保存了MySQL服务器所维护的所有其他数据库的信息。
PERFORMANCE_SCHEMA：主要用于收集数据库服务器的性能参数、提供进程等待的详细信息（锁、互斥变量、文件信息）、保存历史事件的汇总信息、可以容易的新增和删除监控事件点和MySQL服务器监控周期。
MYSQL：保存MySQL的权限、参数、对象和状态信息（权限、DB参数、插件、主从等）
SYS：可以快速的了解系统的元数据信息。
SAKILA：是MySQL的一个样本数据库，里面都是一些例子。

查看存储引擎:使用命令show engines;可以查看系统支持的存储引擎。

选中数据库：使用命令use DATABASE1;可以对 DATABASE1进行操作，如果成功选中，mysql会回复Database changed。如果没有这张数据表，会显示Empty set

退出数据库服务器：使用命令exit;可以退出数据库服务器，停止用户和mysql的交互。

MySQL增操作Create：

在数据库服务器中创建数据库：使用命令create database NAME1;可以在数据库服务器中创建一个名为NAME1的数据库。一旦创建成功，mysql会回复Query OK

创建数据表：使用命令create table TABLENAME1(name varchar(20), species varchar(20), birth date, ......);可以在当前数据库中创建出一张名为TABLENAME1，表头（即数据字段）为name、species、birth等项目的一张空数据表。其中规定了name列中需要的数据是varchar类型，最多20字符；species列中需要的数据是varchar类型，最多20字符；birth列中需要date类型的数据。创建成功后，mysql会回复Query OK。

当然，每个字段都可以更为详细，如create table TABLENAME1 (COLNAME1 TYPE CONSTRAINT DEFAULTVALUE);

要注意：字段名之间要用逗号进行隔开，逗号后要添加空格，最后一个字段后不加逗号。

数据表插入新字段（新增列）：使用alter table NAME add column COLUMN_NAME TYPE;可以在名为NAME的数据表最后添加一个名为COLUMN_NAME、类型为TYPE的字段。如果在数据表的中间加入新字段（新列），使用alter table NAME add column COLUMN_NAME TYPE after ORIGIN_COLUMN;可以将新字段添加到指定原字段ORIGIN_COLUMN的后面，如果要插入在表格最前面，使用first来替代after ORIGIN_COLUMN即可。

向数据表中插入数据记录（新增数据记录）：使用命令insert into TABLE1 values('Peter','Edward','2020-02-02',....);可以向数据库中的数据表中添加数据记录。插入的数据需要按照创建数据表时的顺序和类型要求，进行输入。如果插入数据记录是全的，可以用上面一种形式，如果插入乱序或者不全的数据记录，使用insert into TABLE1(field1,field2,......,fieldN) values(value1,value2,......,valueN);，其中只需前后括号中的字段和字段值的N相等即可，N和数据表的字段总数可以不一致（前提是不书写的字段可以默认是空）（即插入数据记录时，只需填写不能为空的字段）。

也可以使用set关键字进行数据插入，使用insert into TABLE1 set field1=value1, field2=value2,......,fieldN=valueN;。

set和insert into的主要区别在于：insert能一次插入多条数据记录：insert into TABLE1(field1,field2,......,fieldN) values(value1,value2,......,valueN①),(value1,value2,......,valueN②),(value1,value2,......,valueN③);

复制数据表（新增数据记录）：复制已有的数据表，用两个语句实现：①create table TABLE2 like TABLE1;复制数据表格式；②insert into TABLE2 select * from TABLE1;复制数据表数据记录。如果数据表在不同的数据库中，在use DB2;的情况下，将上文的TABLE1改成DB1.TABLE1即可。

如果需要再已有空表中复制进另一张数据表，可以先删除表，再用之前的名字新建一张数据表。

MySQL查操作Retrieve：

查询数据库服务器中的数据库：使用命令show databases;可以将数据库服务器中的数据库列在终端上。

进一步，要知道数据库使用的字符编码，使用show create database DB01;就可以看到DB01使用的字符编码。

查看数据库中的数据表：使用命令show tables;可以得到选中数据库中所有的数据表。

查看数据表单的详情：使用命令describe TABLE1;或desc TABLE1可以查看创建好的数据表的结构。其中数据表中的字段和其相应属性等会列在输出中。Field表示字段，Type表示变量类型，Null表示是否支持空值，Key表示约束条件相关内容，Default表示默认值，Extra表示备注。

另外，可以使用show create table TABLE1 \G;查看更全面的表定义信息，如数据库存储引擎和字符集编码。

查询表单内容（查数据记录）：使用命令select * from TABLE1 [where header1=1];可以在选中数据库后查看数据库中的表单。中括号中可以添加查询条件，也可以不加。（这里的一个*表示一条数据记录的全部内容）如果只需要部分字段的字段值，可以将*改成指定的字段名，多个字段名之间用逗号分隔。

利用as或空格可以进行别名。如select COL1 A1,COL2 A2 from TABLE1;或select COL1 as A1,COL2 as A2 from TABLE1;就可以将COL1和COL2列的字段名称变成A1和A2。

完整的select语法如下：select [all/distinct] COL/* [as XXX] from TABLE where CONDITION group by CONDITION having CONDITION order by CONDITION limit CONDITION;要按照相对顺序进行书写，不然会报错。如果别名有MySQL保留关键字或者有空格，必须加引号，MySQL中单引号和双引号等效。XXX表示字段别名，并不修改字段名，只是将字段或者字段表达式在显示时使用别名。同样，更改显示顺序也只需要将COL1和COL2等字段的顺序在上述查询语句中掉换即可。

下面重点说说select查操作的附加条件子句：

COL1和COL2这些字段可以进行运算，换而言之，select后可以跟的不止字段本身，也可以是和字段有关或无关的字段表达式（及函数）。
all/distinct：表示查询显示的数据记录是否显示重复数据，distinct表示去重；all或者不加此选项表示不去重，显示重复数据。
order by COL asc/desc：表示展示数据的顺序是按照COL字段进行升序/降序排列，desc表示降序排列，asc或只用order by表示（默认）升序排列。
limit：表示展示数据记录的条数，limit的语法为limit offset_start,row_count两个参数表示从第offset_start行开始，取row_count条数据记录。如果只写一个参数，表示取row_count条数据记录。
where：表示条件，select所展示的数据必须是满足where后面条件的数据记录，不满足的数据记录将会被直接过滤掉。条件的书写必须能得到一个布尔值。

MySQL改操作Update：

为数据库改名：登录MySQL后，可以使用rename database OldName to NewName;将数据库OldName改名为NewName。请注意，在进入数据库（use）后无法进行数据库改名操作。

对现存数据表改名：使用alter table NAME1 rename to NAME2;就可以把原来名为NAME1的列改为NAME2。

对现存表的列改名（修改字段名）：使用alter table TABLENAME change OLD_COLUMN_NAME NEW_COLUMN_NAME TYPE;可以把名为TABLENAME的数据表中的列OLD_COLUMN_NAME改为NEW_COLUMN_NAME，其中列的类型为TYPE。

设定字段默认值（修改字段默认值）：可以使用alter table TABLE1 modify COLUMN_NAME TYPE default DEFAULT_VALUE;

修改字段数据类型：可以使用alter table TABLE1 change NAME1 NAME2 TYPE_TOBE;对现有数据表TABLE1的NAME1字段进行字段名和数据类型的修改，（如果NAME1=NAME2，则只进行数据类型的修改）将数据类型改为TYPE_TOBE，将字段名改为NAME2

也可以使用alter table TABLE1 modify NAME1 TYPE_TOBE表示将TABLE1数据表中的NAME1字段改为TYPE_TOBE

修改数据表中字段的顺序（修改列的顺序）：可以使用alter table TABLE1 modify COL1 TYPE1 after ORIGIN_COL;可以将原有的COL1字段移动到原有字段ORIGIN_COL的后面，如果需要移动到最前面，使用first替代after ORIGIN_COL，如果在最后，直接省略after ORIGIN_COL即可。

修改数据记录中的字段值（修改单元格的值）：使用update TABLE1 set COL1="VALUE1" where CONDITION;表示在TABLE1中，如果满足CONDITION1的条件，将COL1的字段改成VALUE1。如果有多个需要修改的单元格，将所有需要改的字段都写在set后面，如update TABLE1 set COL1="VALUE1" , COL2="VALUE2",...... where CONDITION;

如果根据不同的CONDITION决定同一列中的字段值，使用下面的语句进行修改：

update TABLE1 set COL1=(

    case

        when CONDITION1 then x

        when CONDITION2 then y

        [else null]

    end);

批量修改字段值（辅助变量批量改字段值）：如果对摘取出的数据记录中某字段值进行重新编号，而原字段中的字段值是无序的，可以使用辅助变量@i来批量生成新值。用户可以使用set @i=0; update TABLE1 set COL1=(select @i:=@i+1 as nid);表示更新TABLE1中的COL1字段中的值，更新为@i，其中@i作为循环变量被递增，直至将数据表中的每一条数据记录的COL1字段值update完。

修改存储引擎：使用alter table TABLE1 engine=ENGINE_NAME将TABLE1数据表中的存储引擎换为ENGINE_NAME引擎。MySQL支持的引擎有MyISAM（选择密集型&插入密集型）、CSV（文本文件）、InnoDB（支持事务&高并发读取）、Archive（压缩归档&存储仓库）、Memory（频繁访问&临时表）、blackhole（接收但不存储）等

MySQL删操作Delete：

删除数据库：用户使用drop batabase DB01;即可把MySQL中名为DB01的数据库成功删除，内部数据表也会被同时删除

删除数据表：使用drop table [if exists] TABLE1,TABLE2,......;可以将数据库中的数据表TABLE1，TABLE2等（一个或多个数据表）进行删除；特殊地，如果此数据表是外键约束的主表，需要先用alter table TABLE1 drop foreign key FOREIGNKEY_NAME;将外键进行删除，才能删除主表。

删除表的列（删字段）：使用alter table NAME drop COLUMN_NAME;可以把名为NAME的数据表中的COLUMN_NAME列（字段）删除。

删除数据记录：利用delete from TABLE1 where CONDITION1可以将TABLE1中符合CONDITON1的数据记录进行删除。如果想同时删除多个表中的数据，使用delete t1,t2 from TABLE1 t1,TABLE2 t2 where CONDITION1;其中，t1和t2是TABLE1和TABLE2的别名。

如果需要清空整个列表，可以使用truncate [table] TABLE1;表示将TABLE1数据表中的数据记录全部删除。

部分操作实例：

巴特西

MySQL_CRUD_In_Terminal