MySQL数据库从入门到精通

基本操作

数据库基本操作

创建数据库： create database 数据库名称；

查看数据库： show databases；（查看系统中所有数据库）

查看已经创建的数据库信息： show create database 数据库名称；

切换到某一数据库（使用数据库）： use 数据库名；

查看当前使用的是哪个数据库 ：select database（）；

修改数据库的编码： alter database 数据库名称 default character set 编码方式 collate 编码方式_bin;

(alter 改动 ；default 默认；character set 字符集；collate 校对 校正；)

删除数据库：drop database 数据库名称；

表的基本操作

创建数据表： create table 表名（字段名1 数据类型，字段名2 数据类型 … 字段名n 数据类型）；

查看数据表是否创建成功： show tables；

查看之前创建的数据表： show create table 表名；

查看之前创建的数据表并格式化：show create table 表名\G；

（可用于查看字段默认值）

只查看表中列的相关信息： describe 表名；

（describe 描述）

一般采用简写： desc 表名；

修改表名：alter table 原表名 rename 新表名；

修改字段：alter table 表名 change 原字段名 新字段名 新数据类型；

修改字段的数据类型： alter table 表名 modify 字段名 数据类型；

（modify 修改）

添加字段：alter table 表名 add 新字段名 数据类型；

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

修改字段的排列顺序：alter table 表名 modify 字段名1 数据类型 first|after 字段名2；

删除数据表： drop table 表名；

表中数据的基本操作

插入数据

向表中插入数据：

在insert 语句中指定字段名：insert into 表名（字段名1，字段名2，…） values（值1，值2，…）；

（insert 插入，into 到…里，values 值）

在insert 语句中不指定字段名：insert into 表名 values（值1，值2…）；

（此时值1，值2等表示每个字段需要添加的数据，每个值的顺序、类型必须和表中字段的顺序、类型都对应。）

查看表中数据：select * from 表名；（* 代表全部）

为指定列插入数据：insert into 表名（字段名1，字段名2,…） values （值1，值2…)；

批量插入数据：insert into 表名[(字段名1，字段名2，…)]

values（值1，值2，…），（值1，值2，…），（值1，值2，…）…,（值1，值2，…）;

更新数据：

update 表名 set 字段名1=值1[，字段名2=值2，…]

[where条件表达式]；

删除数据

使用delete删除数据

delete from 表名 [where 条件表达式]；

删除全部数据: delete from 表名；

删除部分数据：delete from 表名 where 字段名=值；

单表查询

基础查询

查询所有字段： select * from 表名；

查询指定字段： select 字段名1，字段名2，…，字段名n from 表名；

条件查询

带关系运算符的查询

select 字段名1，字段名2，…from 表名

where 条件表达式；

AND关键字查询

select 字段名1，字段名2，…from 表名

where 条件表达式1 and 条件表达式2；

OR关键字查询

select 字段名1，字段名2，…from 表名

where 条件表达式1 or 条件表达式2；

IN 或 NOT IN 关键字查询

select 字段名1，字段名2，…from 表名

where 字段名[NOT] IN （元素1，元素2，…）；

IS NULL 或IS NOT NULL 关键字查询

select 字段名1，字段名2，…from 表名

where 字段名 is [not] null；

BETWEEN AND关键字查询

select 字段名1，字段名2，…from 表名

where 字段名 [not] between 值1 and 值2；

LIKE 关键字查询（模糊查询）

select 字段名1，字段名2，…from 表名

where 字段名 [not] like ‘匹配字符串’；

（匹配字符串用来指定要匹配的字符串，通常用’%’ 和 ‘_’, %表示任意0-n个字符，下划线表示任意的一个字符）

DISTINCT 关键字查询

select distinct 字段名 from 表名；

(select 字段名1，字段名2，…from 表名

where 条件表达式；)

高级查询

排序查询：select 字段名1，字段名2，…from 表名

order by 字段名1 [ASC|DESC],字段名2[ASC|DESC]…；

（ASC和DESC参数是可选的，其中ASC代表升序排序，DESC代表降序排序，如果不写该参数，默认升序排序）

（asc ascending的缩写，升序排列；desc descend的缩写，降序排列）

聚合函数

COUNT函数：select count（*|1|列名） from 表名；

（count（）函数有三个可选参数，其中count（*）是返回行数，包含NULL；count（列名）是返回特定列的值具有的行数，不包含NULL,count（1）与count（*）返回结果相同，如果数据表没有主键，则count（1）效率会高一些）

为列名起别名：select count（*|1|列名） as 别名 from 表名；

（在起别名时，as可以忽略不写；count函数后可跟where，其余函数同）

SUM函数：select sum（字段名）from 表名；

（sum函数用于计算指定列的数值和，如果指定列的类型不是数值类型，那么计算结果为零）

AVG函数：select avg（字段名） from 表名；

（avg函数用于计算指定列的平均值，如果指定列的类型不是数值类型，那么计算结果为零）

MAX函数：select max（字段名） from 表名；

（max函数用于计算指出列的最大值，如果指定列是字符串类型，那么使用字符串排序运算）MIN函数同MAX

分组查询

select 字段名1，字段名2，... from 表名

group by 字段名1，字段名2，...；
1
2
3

（group by 后面的字段名是对查询结果分组的依据）

数据的完整性

实体完整性

主键约束

主键（primary key）用于唯一地标识表中的某一条记录。在两个表的关系中，主键用来在一个表中引用来自于另一个表的特定记录。主键可以由多个关键词共同组成。

为已存在的表设置主键：alter table 表名 add primary key（列名）；

添加多字段的主键约束：

create table 表名（

字段名 1 数据类型，

字段名 2 数据类型，

...

primary key（字段名1，字段名2，字段名n）

）；
1
2
3
4
5
6
7
8
9
10
11

唯一约束

唯一约束用于保证数据表中字段值的唯一性，在MySQL中使用unique 关键字添加唯一约束。（unique 唯一）

在创建表时添加唯一约束：

create table 表名（

字段名 数据类型 unique，

...

）；
1
2
3
4
5
6
7

自动增长列

使某一字段的值自动增加。在MySQL中使用AUTO_INCREMENT关键字设置表字段值自动增加。(auto 自动，increment （连续的）增长)

在创建表时将某个字段的值设置为自动增长：

create table 表名（

字段名 数据类型 auto_increment，

...

）；
1
2
3
4
5
6
7

为已经创建完成的表字段设置自动增长列：

alter table 表名 modify 字段名 数据类型 primary key auto_increment;

索引

普通索引

创建表时创建普通索引：

create table 表名（

字段名 数据类型，

...

index[索引名]（字段名[（长度）]）

）；
1
2
3
4
5
6
7
8
9

index表示字段的索引，索引名是可选值，括号中的字段名是创建索引的字段，参数长度是可选的，用于表示索引的长度

唯一索引

普通索引允许被索引的数据列包括重复的值，但有些值是不能重复的。

在创建表时创建唯一索引：

create table 表名（

字段名 数据类型，

...

unique index[索引名]（字段名[（长度）]）

）；
1
2
3
4
5
6
7
8
9

域完整性

非空约束

create table 表名（

字段名 数据类型 not null，

...

）；
1
2
3
4
5
6
7

默认值约束

create table 表名（

字段名 数据类型 default 默认值，

...

）；
1
2
3
4
5
6
7

引用完整性

外键

外键是指引用另一个表中的一列或多列，被引用的列应该具有主键约束或唯一约束。外键用于建立和加强两个表数据之间的连接。

添加外键约束

alter table 表名

add foreign key （外键字段名） references 主表表名（主键字段名）；

删除外键约束

alter table 表名 drop foreign key 外键名；

多表查询

合并结果集

UNION：select * from 表1 union select * from 表二；

当两张表中有重复数据时，UNION会去除重复的数据，但union all 不会

连接查询

在数据库中建立数据表时不必确定各个数据之间的关系，通常将每个实体的所有信息存放在一个表中。当两个或者多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。

笛卡尔积

笛卡尔积在sql中的实现方式是交叉连接，所有连接方式都会先生成临时笛卡尔积表。笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合。

交叉查询语法格式：select 查询字段 from 表1 cross join 表2；

（cross jion 用于连接两个需要查询的表，可以查询两个表中的所有数据组合）

内连接

内连接语法格式：

select 查询字段 from 表1 [inner] join 表2

on 表1.关系字段=表2.关系字段 where 查询条件；

（inner join 用于连接两个表，其中inner可以省略，因为mysql默认的连接方式就是内连接，on用来指定连接条件，类似于 where 关键字，内连接作用与笛卡尔积（交叉连接）相似，交叉连接可以理解为方言）

外连接

内连接的查询返回的结果只包含符合查询条件和连接条件的数据，然而有时还需要包含没有关联的数据，返回的查询结果中不仅包含符合条件的数据，还包含左表或右表或两个表中的所有数据。

左外连接是以坐标中的数据为基准，若左表中有数据且右表中没有数据，则显示坐标中的数据，右表中的数据显示为空。

左外连接语法格式：

select 查询字段 from 表1 left [outer] join 表2

on 表1.关系字段=表2.关系字段 where 查询条件；

（left join 表示返回左表中的所有记录以及右表中符合连接条件的记录，outer 可省略）

右外连接 right

多表连接

select 查询字段 from 表1 [别名1] 

join 表2 [别名] on 表1.关系字段=表2.关系字段

join 表m on...;
1
2
3
4
5

自然连接：select 查询字段 from 表1[别名] natural join 表2[别名]；

（自动版内连接）

自然连接左右连接：

select 查询字段 from 表1[别名]

natural [left|right] join 表2[别名]；

自连接：select 查询字段 from 表名[别名1]，表名[别名2] where 查询条件；

（通过起不同别名实现自链接）

子查询

子查询就是嵌套查询，即在select中包含select。子查询可以在where关键字后面作为查询条件，也可以在from后面作为表来使用。