MySQL基础篇：02_SQL基本操作

---

基本操作：CRUD
将SQL的基本操作根据操作对象分为三类：库操作、表操作（字段操作）、数据操作

库操作: CRUD

• 库操作：对数据库的增删改查

新增数据库

基本语法：create database 数据库名字 [库选项];

• 库选项：用来约束数据库，分为两个选项
  （1）字符集：charset/character set 具体字符集：常见字符集UTF8与GBK
  （2）校对集：collate 具体校对集：数据比较的规则

create database mydatabase charset utf8;
create database mydatabase2 character set utf8;

• 数据库名称不能使用关键字与保留字

-- 数据库名称为关键字
create database database charset utf8;

• 如果一定要使用关键字或保留字作为数据库名称的话，那么必须使用反引号（就是ESC键下面的键在英文状态下的输出：`）

create database `database` charset utf8;

• 数据库名称为中文是允许的，但是必须保证服务器能够识别：不建议使用中文作为数据库的名称
• 当数据库服务器mysqld.exe执行了创建数据库的SQL语句之后，发生了什么?
  （1） 在数据库管理系统中增加了对应数据库的信息
  
  （2） 在数据库管理系统的保存数据的目录data目录下会创建一个对应数据库名称的目录
  
  （3） 每个数据库目录下都有一个db.opt文件（option文件）：保存了库选项
  
  

查看数据库

查看所有数据库：show databases;
查看指定部分数据库：show databases like 'pattern'; -- pattern匹配模式
				   %：表示匹配任意数量任意字符
				   _：表示匹配一个任意的字符
查看数据库的创建语句：show create database 数据库名字 [\G]; 默认是\g

• 查看所有数据库：show databases;
  
• 查看指定部分数据库：show databases like ‘pattern’; – pattern匹配模式
  
• 查看数据库的创建语句：show create database 数据库名字;
  

更新数据库

• 数据库的名字不可以修改：因为数据库名字的修改可能会带来一堆的问题
• 数据库的修改仅限于库选项：字符集与校对集（校对集依赖字符集）

alter database 数据库名字 库选项;
alter database 数据库名字 charset/character set [=] utf8;
alter database  数据库名字 collate 校对集;

删除数据库

drop database 数据库名称;

• 数据库服务器mysqld.exe执行删除数据库的SQL语句之后发生了什么？

1. 在数据库管理系统你不看不到对应的数据库：数据库管理系统删除了对应的数据库
2. 在数据库管理系统中的数据目录data目录下对应的数据库的目录也被删除，而且是级联删除，即对应数据库里面的所有数据表全部删除

• 删除是不可逆的！！！不要随意删除，删除需谨慎！！！
  

---

表操作:CRUD

• 表与字段是密不可分的

新增数据表

create table [if not exists] 数据表名(
	字段名称 数据类型 字段属性,
	......
	字段名称 数据类型 字段属性 -- 最后一行不需要逗号
) [表选项];

• if not exists：如果表名称不存在，那么就创建；否则不执行创建代码：检查功能
• 表选项：控制数据表的表现
  （1） 字符集：charset/character set 具体字符集；—保证表中数据存储的字符集
  （2） 校对集：collate 具体校对集;
  （3） 存储引擎：engine 具体存储引擎（innodb与myisam），默认的存储引擎就是innodb
  
• 任何一个表的设计都必须指定数据库
  （1） 方案1：显示指定表所属的数据库

create table [if not exists] 数据库名称.数据表名(
	字段名称 数据类型 字段属性,
	......
	字段名称 数据类型 字段属性 -- 最后一行不需要逗号
) [表选项];

（2）方案2：隐式的指定表所属的数据库：先进入到某个数据库环境，然后这样创建的表自动归属到制定的数据库中

进入到数据库环境：use 数据库名称; -- 可以不使用分号结束

• 当数据库服务器mysqld.exe执行了创建数据表的SQL语句之后发生了什么?
  （1） 指定数据库下已经存在对应的数据表
  
  （2） 在数据库对应的文件夹下，会产生对应表的结构文件：跟存储引擎有关
  

查看数据表

• 数据库能够查看的方式都能用在数据表的查看
• 查看所有表：show tables;
  
• 查看指定部分表：show tables like ‘pattern’;
  
• 查看表的创建语句：show create table 表名称 [\G]; – 默认的是\g
  
• 查看表结构：查看表中的字段信息：desc/describe/show columns from 表名称；
  
  

修改数据表

• 表的修改包含两部分：修改表本身与修改字段

修改表本身

• 表本身可以修改：表名称与表选项
  （1） 修改表名称

rename table 旧表名称 to 新表名称;

（2） 修改表选项：字符集、校对集、存储引擎

alter table 表名称 表选项 [=] 值;

修改字段

• 字段操作有很多：新增、修改、重命名、删除
• 新增字段

alter table 表名称 add [column] 字段名称 数据类型 [列属性] [位置];
位置：新增字段可以在任何位置，默认是在最后一个字段的后面新增
	first：第一个位置
	after 字段名称：在指定字段名称的后面新增字段，默认是在最后一个字段的后面新增

• 例如：

alter table stu add id int unsigned  not null comment 'id编号' first;
alter table stu add hight int unsigned  not null default 170 comment '身高' after number;

• 修改字段：凡是能够使用modify的地方就能使用change

alter table 表名称 modify 字段名称 数据类型 [列属性] [位置];
alter table 表名称 change 旧字段名称 新字段名称  数据类型 [列属性] [位置];

（1） modify： 主要是用来修改字段的数据类型、列属性以及位置
（2） change：主要是用来修改字段的名称，字段的名称不能单独修改，必须指定数据类型、类属性以及位置

• 删除字段：如果表中已经存在数据，删除字段会清空该字段的所有数据：删除不可逆

alter table 表名称 drop 字段名称;

删除数据表

drop table 表名称, 表名称, ...; -- 可以一次删除多个数据表

• 数据库服务器mysqld.exe执行了删除数据表的SQL语句后发生了什么？
  （1） 在数据库中没有指定的表，当然数据也没有了
  （2） 在数据库对应的文件夹下，表对应的文件（与存储引擎有关）也会被删除

---

简单数据操作:CRUD

新增数据

• 新增数据有两种方案
  （1）方案一：不指定字段列表，即给全表字段插入数据：要求数据的值出现的顺序必须与表中设计的字段出现的顺序一致：凡是非数值数据，都需要使用引号包裹，建议使用单引号（如果字段类型是数值类型，插入数据的时候也可以给字符串数值，mysqld.exe会自己转换）

insert into 表名称 values(值列表) [,(值列表)]; -- 可以一次性插入多条记录

（2）方案二：给部分字段插入数据，需要指定字段列表：字段列表出现的顺序与字段设计的顺序无关：但是值列表的顺序必须与选定的字段列表的顺序一致

insert into 表名称(子列列表) values(值列表) [,(值列表) ];

查询数据

select [select选项]  */字段列表 from 表名称 [where条件]; 
select选项：all与distinct
	all：保留查询出来的所有记录，默认就是all
	distinct：将查询出来的记录中重复的记录剔除掉，即查询出来的记录不存在重复记录

更新数据

update 表名称 set 字段名称 = 值, 字段名称 = 值, ... [where条件]; -- 建议带上where条件，要不然更新所有记录

• 数据更新不一定成功：如果没有真正要更新的数据
  

删除数据

delete from 表名称 [where条件]; -- 删除是以记录为单位

---

中文数据问题

• 中文数据问题的本质是字符集问题
• 计算机只能识别二进制，而人类更多的是识别符号：需要有个二进制与符号的对应关系
• 字符编码：二进制与符号的对应关系称之为符号编码/字符编码
• 字符集：所有字符编码放在一个构成一个集合，称之为字符集，即字符编码的集合
  
• 小黑屏客户端向数据库服务器插入数据
  
• 插入中文不成功的原因：因为在小黑屏只支持GBK字符集，即在小黑屏下发送的SQL语句是经过GBK编码发送给数据库服务器，但是数据库服务器mysqld.exe采用UTF-8字符集进行解码：GBK使用2个字节来对中文进行编码，而UTF-8字符集是使用三个字节来对中文进行编码
  
  
• 所有的数据库服务器表现的一些特性都是通过服务器端的变量来保存：系统先读取自己的变量，看看应该怎么表现
• 查看数据库服务器到底支持哪些字符集：基本上服务器是万能的，什么字符集都支持
  
• 既然数据库服务器能够识别这么多：总有一种是服务器默认的跟客户端打交道字符集
  
• 如果使用的是小黑屏客户端，那么在使用中文数据之前必须设置相关的字符集

set character_set_client = gbk; -- 因为小黑屏只支持GBK编码，所以服务器必须使用GBK来解码
set character_set_result = gbk; -- 服务器对结果集使用gbk编码，小黑屏使用gbk解码，所以查询出来的结果集才不会乱码

• 插入中文
  
• 修改set character_set_result = gbk来解决结果集乱码的问题
  
• set 变量 = 值：这种修改只是会话级别的：当前客户端，当次连接有效：关闭后无效
  
• 设置数据库服务器对客户端的字符集的认知：可使用快捷方式：一个顶三个

set names gbk; -- 等价于
set character_set_client = gbk;
set character_set_results = gbk;
set character_set_connection = gbk;

• 连接层字符集是字符集转变的中间者，如果统一了效率更高，不统一也没问题

---

校对集问题

• 校对集：数据比较的方式
• 校对集有3中格式

_bin：binary，二进制比较：取出二进制位，一位一位的比较，区分大小写
_cs：case sensitive：大小写敏感，区分大小写
_ci：case insensitive：大小写不敏感，不区分大小写

• 查看数据库服务器所支持的校对集：mysql5.6支持219中校对集
  
• 校对集应用：只有当数据产生比较的时候校对集才会生效
  
• 插入数据
  
• 查询数据：数据不进行比较
  
• 查询数据：数据进行比较
  
• 校对集必须在表中还没有数据的时候声明好：如果表中已有了数据再声明校对集的话，那么校对集无效！