MySQL系列(四)：SQL操作

SQL(Structured Query Language)， 结构化查询语言(数据以查询为主，百分之九十几是在进行查询操作)。

SQL是关系型数据库的操作指令,结构化查询语言。SQL是一种约束，但不强制(类似W3C)，不同的数据库产品(Oracle，mysql，sqlserver等)可能内部会有一些细微的区别。

一、分类

• DDL(Data Definition Language)：数据定义语言。用来维护存储数据的结构(数据库、表)，如：create，drop，alter等。
• DML(Data Manipulation Language)：数据操作语言。用来对数据进行操作(数据表中的内容)，如：insert， delete，update等。
• DQL(Data Query Language)：数据查询语言。如：select。
• DCL(Data Control Language)：数据控制语言。主要是负责权限管理(用户)， 如： grant，revoke等.

二、库操作

1.增

create database 数据库名 [库选项];

库选项是用来约束数据库的，分为两个选项：

字符集设定：charset/character set 具体字符集(数据存储的编码格式GBK、UTF8等)

校对集设定：collate 具体校对集(数据比较的规则)

• 数据库名字不能使用关键字(已经被使用的字符)或保留字(将来可能会用到的)
• 如果非要使用关键字或者保留字，那么必须使用反引号(`)
• 保证服务器能够识别的前提下，可以使用中文数据库(一般不使用)

当创建数据库的SQL语句执行之后：

• 在数据库系统中，增加了对应的数据库信息
• 会在保存数据的文件夹下(data目录)，创建一个对应数据库名字的文件夹
• 每个数据库下都有一个opt文件，保存了库选项

create database `my_db` default charset utf8 collate utf8_general_ci;

2.删

drop database 数据库名;

当删除数据库语句执行之后：

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

drop database `my_db`;

Tips：数据库的删除不是闹着玩的，不要随意删除，应该先进行备份后操作（删除不可逆）。

3.改

数据库名字不可以修改。

数据库的修改仅限于库选项，字符集和校对集(校对集依赖字符集)。

alter database 数据库名  [库选项];

库选项字符集：charset/ character set [=] 字符集

库选项校对集：collate 校对集

4.查

查看所有数据库：show databases;

查看数据库的创建语句：show create database 数据库名字;

查看指定部分的数据库(模糊查询)：show databases like 'pattern';    -- pattern是匹配模式

• %：匹配多个字符
• _： 匹配单个字符

三、表操作

1.增

create table [if not exists] 表名(
     字段名字 数据类型,
     字段名字 数据类型
)[表选项];

if not exists：如果表名不存在，那么就创建，否则不执行创建代码。

表选项：
字符集：charset/character set 具体字符集
校对集：collate 具体校对集
存储引擎：engine 具体的存储引擎(如innodb、myisam)
初始自增长：auto_increment

任何一个表的设计都必须指定数据库。

1、显示的指定表所属的数据库
create table 数据库名.表名();

2、隐式的指定表所属数据库
use 数据库名;

当创建数据表的SQL指令执行之后：

• 指定数据库下已经存在对应的表
• 在数据库对应的文件夹下，产生对应表的结构文件(跟存储引擎有关系)

2.删

drop table 表名1,表名2...;    -- 删除一张或者多张表，删除不可逆

当删除数据表的SQL指令执行之后：

• 在表空间中，没有了指定的表(数据也没有了)
• 在数据库对应的文件夹下，表对应的文件(与存储引擎有关)也会被删除

3.改

修改表本身：

--修改表名
rename table 旧表名 to 新表名;

--修改表选项: 字符集，校对集和存储引擎等
alter table 表名 表选项 [=] 值;

修改字段：

--新增字段
alter table 表名 add [column] 字段名 数据类型 [列属性] [位置];
--位置: 字段名可以存放表中的任意位置
--first：第一个位置
--after：after 字段名;在某个字段之后，默认的是在最后一个字段后边



--修改字段: 修改通常是修改属性或者数据类型
alter table 表名 modify 字段名 数据类型 [属性] [位置];



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



--删除字段，如果表中已经存在数据,那么删除字段会清空该字段的所有数据(不可逆)
alter table 表名 drop 字段名;

4.查

--查看所有表
show tables;


--查看部分表(模糊匹配)
show tables like 'pattern';


--查看表的创建语句
show create table 表名;


--查看表结构
show columns from/desc/describe 表名;
desc 表名;
describe 表名;

四、数据操作

1.增

1)、给全表字段插入数据，不需要指定字段列表。要求数据的值出现的顺序必须与表中设计的字段出现的顺序一致。凡是非数值数据，都需要使用引号(建议单引号)包裹。

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

2)、 给部分字段插入数据，需要选定字段列表。字段列表出现的顺序与字段的顺序无关。但是值列表的顺序必须与选定的字段的顺序一致。

insert into 表名(字段列表) values(值列表)[,(值列表)];

在数据插入的时候，如果主键对应的值已经存在，那么插入一定会失败!（主键值的唯一）

主键冲突：

更新和替换两种方案。

--更新操作
insert into 表名[(字段列表:包含主键)] values(值列表) on duplicate key update 字段 = 新值;

insert into xxx values('PHP0810','B205') /*冲突处理*/on duplicate key update/*冲突处理*/ /*更新*/room = 'B205'/*更新*/;



--替换
replace into 表名 [(字段列表:包含主键)] values(值列表);

replace into xxx values('PHP0710','A203');
replace into xxx values('PHP0910','B207');

蠕虫复制：

从已有的数据中去获取数据，然后将数据又进行新增操作，数据成倍的增加。

从已有表创建新表(复制表结构)：

--create table 表名 like 数据库.表名;
create table my_copy like my_utf8;

先查出数据，然后将查出的数据新增一遍：

--insert into 表名[(字段列表)] select 字段列表/* from 数据表名;
insert into my_copy select * from my_copy;
insert into my_copy select * from my_collate_bin;

• 从已有表拷贝数据到新表中（也可以从自己的表中复制数据到自己表中，也就是自我复制，但是复制的时候不要复制主键，会引起主键冲突）
• 可以迅速的让表中的数据膨胀到一定的数量级，测试表的压力以及效率

2.删

删除是不可逆。

delete from 表名 [where条件];
delete from 表名 [where条件] [limit 数量];
truncate 表名;    -- 清空表数据，不会改变表结构，但是会重置自增长

• 如果表中存在主键自增长，那么当删除之后，自增长不会还原。
• 数据的删除是不会改变表结构，只能删除表后重建表。

3.改

如没有真正要更新的数据，更新不一定会成功。

update 表名 set 字段 = 值 [where条件];
update 表名 set 字段 = 值 [where条件] [limit 更新数量];

如果不给where条件，将会更新全部数据。

4.查

select */字段列表 from 表名 [where条件];
select [select选项] 字段列表[字段别名]/* from 数据源 [where条件子句] [group by子句] [having子句] [order by子句] [limit 子句];

1)select选项：

select对查出来的结果的处理方式。

all：默认的，保留所有的结果

distinct：去重，查出来的结果，将重复给去除(所有字段都相同)

2)字段别名：

当数据进行查询出来的时候，有时候名字并一定就满足需求(多表查询的时候, 会有同名字段)。

需要对字段名进行重命名：别名。

字段名 [as] 别名;

3)数据源：

数据的来源，关系型数据库的来源都是数据表。实际上只要保证数据类似二维表，最终都可以作为数据源。

数据源分为单表数据源，多表数据源，查询语句等。

--单表数据源
select * from 表名;


--多表数据源：从一张表中取出一条记录，去另外一张表中匹配所有记录，而且全部保留(记录数和字段数),将这种结果称为笛卡尔积(交叉连接)。应该尽量避免笛卡尔乘积。
select* from 表名1,表名2...;


--子查询：数据的来源是一条查询语句(查询语句的结果是二维表)
select * from (select 语句) as 表名;

4)where子句：

where子句： 用来判断数据，筛选数据。返回0(false)或者1(true)。

 

判断条件：

比较运算符： >, <, >=, <= ,!= ,<>, =, like, between and, in/not in等

逻辑运算符：&&(and)，||(or)，!(not)

 

where原理：where是唯一一个直接从磁盘获取数据的时候就开始判断的条件。从磁盘取出一条记录，开始进行where判断，判断的结果如果成立保存到内存，如果失败直接放弃。

5)group by子句：

分组，根据某个字段进行分组。相同的放一组，不同的分到不同的组，一般为了统计数据。

a)统计函数：

• count()：统计分组后的记录数，每一组有多少记录。可以使用两种参数*代表统计记录或者使用字段名代表统计对应的字段(NULL不统计)。
• max()：统计每组中最大的值
• min()：统计最小值
• avg()：统计平均值
• sum()：统计和

b)分组排序：

根据分组字段，默认升序。

group by 字段 [asc|desc];    -- 对分组的结果合并之后的整个结果进行排序

c)多字段分组：

先根据一个字段进行分组，然后对分组后的结果再次按照其他字段进行分组。

d)回溯统计： with rollup ，任何一个分组后都会有一个小组，根据当前分组的字段，最后都需要向上级分组进行汇报统计段，就是回溯统计。回溯统计的时候会将分组字段置空。

考虑第一层分组会有此回溯：第二次分组要看第一次分组的组数，组数是多少，回溯就是多少，然后加上第一层回溯即为多字段分组回溯统计。

-- 回溯统计
select cid,count(*) from my_student group by cid with rollup;


-- 分组多字段排序
select cid,sex,count(*),group_concat(name) from my_student group by cid,sex;


-- 多字段分组回溯统计
select cid,sex,count(*),group_concat(name) from my_student group by cid,sex with rollup;

6)having子句：

having子句与where子句一样，用于进行条件判断的。

• where是针对磁盘数据进行判断，进入到内存之后，会进行分组操作，分组结果就需要having来处理。
• having能做where能做的几乎所有事情，但是where却不能做having能做的很多事情。
• 分组统计的结果或者说统计函数都只有having能够使用。
• having能够使用字段别名而where不能。where是从磁盘取数据，而名字只可能是字段名，别名是在字段进入到内存后才会产生。

select cid,count(*) from my_student group by cid having count(*) >= 2;
select cid,count(*) from my_student where count(*) >= 2 group by cid ;


select cid,count(*) as total from my_student group by cid having total >= 2;
select cid,count(*) as total from my_student where total >= 2 group by cid ;

7)roder by子句：

排序，根据某个字段或多个字段进行升序或者降序， 依赖校对集。

order by 字段名 [asc|desc];      -- asc是升序(默认)，desc是降序

排序可以进行多字段排序，先根据某个字段进行排序，然后排序好的内部，再按照某个数据进行再次排序。

8)limit子句：

limit子句是一种限制结果的语句，限制数量。

两种使用方式：

a)只用来限制长度(数据量)： limit 数据量;

b) 限制起始位置，限制数量：limit 起始位置,长度;

方式2主要用来实现数据的分页: 为用户节省时间，提交服务器的响应效率，减少资源的浪费。

对于用户来讲：可以点击的分页按钮：1,2,3,4.....或者上拉(上滑)下拉(下滑)

对于服务器来讲：根据用户选择的页码来获取不同的数据：limit offset,length;

length：每页显示的数据量，基本不变，页码大小

offset：offset = (页码 - 1) * 页码大小

五、中文数据问题

六、校对集问题

七、web乱码问题

转载于:https://my.oschina.net/programs/blog/1789523