SQL基本操作

基本操作：CRUD（Create增、Retrieve查、Update改、Delete删）
SQL基本操作根据操作对象可分为：库操作、表操作（字段）、数据操作

库操作

对数据库的增删改查

1.新增数据库

基本语法：Create database 数据库名字 [库选项]；
库选项：用来约束数据库，分为两个选项：

• 字符集设定：charset/character set具体字符集（数据存储的编码格式），常用字符集：GBK和UTF8
• 校对集设定：collate具体校对集（数据比较的规则）

单行注释：-- 双中划线+空格或者使用#号
在命令行输入：

-- 创建数据库
create database mydatabase charset utf8;

注意：数据库名字不能用关键字或者保留字，如若非要使用，必须使用反引号符号，例：

create database `database` charset utf8;

数据库名是中文也是可以的，但要保证服务器能够识别（建议不用）例：

set names gbk;
create database 中国 charset utf8;

(上述代码第一行是告诉服务器当前中文的字符集是什么）

当创建数据库的SQL语句执行之后，发生了什么？

• 在数据库系统中，增加了对应的数据库信息
• 会在保存数据的文件下：Data目录，创建一个对应数据库名字的文件夹。

2.查看数据库

• 查看所有数据库

show databases;

• 查看指定部分的数据库：模糊查询
  show databases like 'pattern'; -- pattern是匹配模式
  %：表示匹配多个字符
  _：表示匹配单个字符
  例：要求查询以information_开头的数据库
  错误写法：

show databases like 'information_%';

正确写法：

show databases like 'information\_%';

_需要被转义！

• 查看数据库的创建语句

show create database mydatabase;

数据库在执行SQL语句之前会优化SQL，系统保存的结果是优化后的结果，所以你会发现查到的创建语句不是原来自己写的那样。

3.更新数据库

数据库名字不可以修改。
数据库的修改仅限库选项：字符集和校对集（校对集依赖字符集）
基本语法：Alter database 数据库名字 [库选项];
charset/character set [=] 字符集
collate 校对集
例：要求修改informationtest数据库的字符集

alter database informationtest charset GBK;

4.删除数据库

Drop database 数据库名字;

drop database informationtest;

当删除数据库语句执行之后，发生了什么？

• 在数据库内部看不到对应的数据库
• 在对应的数据库存储的文件夹内，数据库名字对应的文件夹也被删除（级联删除，里面的数据表全部删除）

注意：不要随意删除数据库，应该先进行备份后删除（删除不可逆）。

表操作

表与字段是密不可分的。

1.新增数据表

Create table [if not exists]表明(字段名字 数据类型， 字段名字 数据类型）[表选项]；
表选项：控制表的表现

• 字符集：charset/character set 具体字符集; -- 保证表中数据存储的字符集
• 校对集：collate 具体校对集;
• 存储引擎：engine 具体的存储引擎(innodb和myisam)

注意：这么写的话，一定要指定数据库！！！任何一个表的设计都必须指定数据库！！！
两种方式：

• 显示的指定表所属的数据库
  Create table 数据库.表名();如：

create table if not exists mydatabase.student(
    name varchar(10),
    gender varchar(10),
    number varchar(10)
    age int
)charset utf8;

• 隐式的指定表所属数据库：先进入到某个数据库环境，然后这样创建的表自动归属到某个指定的数据库。
  进入数据库环境：use 数据库名字

-- 进入数据库
use mydatabase;
create tabele class(
  name varchar(10),
  room varchar(10)
)charset utf8;

当创建数据表的SQL指令执行之后，到底发生了什么？

• 指定数据库下创建了对应的表
• 在数据库对应的文件夹下，会产生表的结构文件（跟存储引擎有关系）

2.查看数据表

• 查看所有的表：show tables;
• 查看部分表：模糊匹配
  show tables like 'pattern';
• 查看表的创建语句
  show create table 表名

show create table student\g; -- \g相当于分号
show create table student\G; -- \G将查到的结构 旋转90度，变成纵向

• 查看表结构：查看表中的字段信息
  desc/describe/show columns from 表名;

desc class;
describe class;
show columns from class;

以上三条代码执行效果一样

3.修改数据表

表的修改分为两个部分：修改表本身和修改字段

• 修改表本身
  表本身可以修改：表名和表选项
  修改表名：rename table 老表名 to 新表名;

rename table student to my_student;

修改表选项：字符集、校对集和存储引擎
alter table 表名 表选项 [=] 值;

• 修改字段
  字段操作有很多： 新增(add)，修改(modify)，重命名(change)，删除(drop)
  新增字段：Alter table 表名 add[column] 字段名 数据类型 [列属性] [位置];
  位置：字段名可以存放表中的任意位置
  First：第一个位置
  After：在哪个字段之后：after 字段名；默认是在最后一个字段之后。

alter table my_student add id int first;

修改字段：通常是修改数据类型，属性和位置
Alter table 表名 modify 字段名 数据类型 [属性] [位置]；

alter table my_student modify number char(10) after id;

重命名字段：
Alter table 表名 change 旧字段名 新字段名 数据类型 [属性] [位置];

alter table my_student change gender sex varchar(10);

删除字段：
Alter table 表名 drop 字段名；

alter table my_student drop age;

4.删除数据表

Drop table 表名1,表名2,..; -- 可以一次性删除多个表

数据操作

1.新增数据

有两种方案：

• 方案1：给全表字段插入数据，不需要指定字段列表，但是要求数据的值出现的顺序必须与表中设计字段的顺序一致，凡是非数值数据，使用引号（建议单引号）包裹。
  Insert into 表名 values(值列表)[,(值列表)]; -- 一次性可插入多条记录。
  例：

-- 插入数据
insert into my_student values(1, 'itcast001', 'Jim', 'male'),
 (2, 'itcast002', 'Mary', 'female');

• 方案2：给部分字段插入数据，需要选定字段列表，字段列表出现的顺序与字段的顺序无关，但是值列表的顺序必须与选定的字段列表的顺序一致
  Insert into 表名(字段列表) values(值列表)[,(值列表);
  例：

insert into my_student(number, sex, name, id) values ('itcast003', 'male', 'Tom', 3)
,('itcast004', 'female', 'Lily', 4);

2.查看数据

• 查看所有数据：
  select * from 表名 [where条件];

select * from my_student where id = 1;

• 查看指定字段，指定条件的数据
  select 字段列表 from 表名 [where条件];

select sex, name, number from my_student where id = 2;

3.更新数据

Update 表名 set 字段 = 值 [where条件];

update my_student set sex = 'female' where name = 'Jim';

4.删除数据

Delete from 表名 [where条件];

delete from my_student where sex = 'male';

中文数据问题

如果直接插入中文数据，会报错。打开命令行窗口，看属性，会发现，客户端字符集为GBK，而服务器认为数据是UTF8，所以会报错。

-- 查看服务器到底识别哪些字符集
show character set;

（会发现服务器可以识别39种字符集，所以服务器基本是万能的，什么字符集都支持）
既然服务器识别这么多，总有一种是服务器默认跟客户端打交道的字符集。

-- 查看服务器默认的对外处理的字符集
show variables like 'character_set%';

会发现服务器默认的客户端来的数据的字符集是utf8：character_set_client = utf8;
服务器默认的给外部数据的字符集也是utf8：character_set_results = utf8;

问题根源：客户端数据只能是GBK，而服务器认为是UTF8
解决方案：改变服务器，默认的接收字符集为GBK，并且给外部的数据也是GBK。

set character_set_client = gbk;
set character_set_results = gbk;

注意：set 变量 = 值; 修改只是会话级别（当前客户端，当次连接有效，关闭失效）
所以设置服务器对客户端的字符集的认识，可以使用快捷方式：
set names 字符集;
所以

set names gbk;

等价于

character_set_client = gbk;
character_set_results = gbk;
character_set_connection = gbk;

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

校对集问题

校对集：数据比较的方式
校对集有三种格式：

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

查看数据库所支持的校对集：
show collaton;
校对集应用：当数据产生比较的时候，校对集才会生效。

比较：根据某个字段进行排序：order by 字段名 [asc/desc]; asc升序，desc降序。（默认升序）

select * from my_student order by id;

注意：校对集必须在没有数据之前声明好，如果有了数据，那么再进行校对集修改，修改无效。