MySQL操作--单表操作

SQL语句大小写不敏感

注意：以下 [] 中的内容都是可选参数

1.操作数据库

---

1.1创建

create database [if not exists] 数据库名称 [库的选项];

1.2查看数据库

show databases
#查看特定数据库
show create database 数据库名称;

1.3修改数据库

alter database 数据库名 default characterset 编码方式 [collate 比较规则];

1.4使用数据库

use 数据库名;

1.5删除数据库

drop database 数据库名;

2.操作数据表

---

2.1创建

create table 表名
(
    字段1, 数据类型[完整性约束条件],
    字段2, 数据类型[完整性约束条件],
    字段3, 数据类型[完整性约束条件],
    .......
    字段n, 数据类型[完整性约束条件]
)

常用数据类型：

数值----int，bigint，float，double

时间----date（日期值：YYYY-MM-DD），time（时间：HH:MM:SS），datetime（日期值+时间值）

字符----char，varchar，text

约束条件：

primary key ---- 主键约束，用于唯一表示对应的记录

foreign key ---- 外键约束

not null ---- 非空约束

unique ---- 唯一性约束

default ---- 默认值约束

2.1.1 增加约束

#创建表时为多个字段增加约束需要在最后一个括号前写
约束条件(字段名1, ............)
#删除约束
alter table 表名 drop 约束条件;
#增加现有表的约束条件
alter table 表名 add 约束条件;

2.1.2特殊约束（default）

#default
#1.创表时候
字段名 数据类型 default 默认值;
#2.删除默认约束
alter table 表名 modify 字段 数据类型;
#3.增加现有表的约束
alter table 表名 modify 字段 数据类型 default 默认值;

2.1.3设置自动增长

注意：

−一个表中只能有一个自动增长字段；

−该字段的数据类型是整数类型；

--必须定义为键

#1.创表时候
字段名 数据类型 约束条件 auto_increment;
#2.修改自动增加值
alter table 表名 auto_increment= 新值;
#3.删除自动增加
alter table 表名 modify id int unsigned;
#4.重新为id添加自动增长，自动增长删除并重新添加后，自动增长的初始值会自动设为该列现有的最大值加1。
alter table 表名 modify id int unsigned auto_increment;

2.1.4修改编码方式

创建数据表时候

create table 表名
(
    字段1, 数据类型[完整性约束条件],
    字段2, 数据类型[完整性约束条件],
    字段3, 数据类型[完整性约束条件],
    .......
    字段n, 数据类型[完整性约束条件]
)default character set = gbk;

修改已创建的数据表中对应的字段

alter table 表名 modify 字段名 数据类型 character set=gbk;

2.2显示表结构

show create table 表名;
desc 表名;
show columns from 表名;

2.3修改表

2.3.1改表名

alter table 旧表名 rename 新表名;#改单表
rename table 旧表名1 to 新表名1, 旧表名2 to 新表名2,旧表名3 to 新表名3......;

2.3.2改字段

alter table 表名 change 旧字段名 新字段名 新数据类型;
alter table 表名 modify 字段名 新数据类型;                #只是改数据类型

2.3.3改字段位置

#放到第一个位置
alter table 表名 modify 字段名1 数据类型 first;
#放到特定字段后面
alter table 表名 modify 字段名1 数据类型 after 字段名2;

2.3.4添加字段

#添加单个
alter table 表名 add 新字段名 新数据类型 [约束条件] [first|after 已经存在字段名];
#添加多个 --- 不可指定字段位置，默认在最后
alter table 表名 add (新字段名1 新数据类型1, 新字段名2 新数据类型2,.......);

2.3.5删除字段

alter table 表名 drop 字段名;

2.4删除数据表

drop table 表名1,[表名2,表名3,表名4,....表名n];

3.索引

---

3.1创建

#1.创建表的时候
create table 表名
(
    字段1, 数据类型[完整性约束条件],
    字段2, 数据类型[完整性约束条件],
    字段3, 数据类型[完整性约束条件],
    .......
    字段n, 数据类型[完整性约束条件],
    [unique|fulltext|spatial] index|key [索引名] (字段名[(长度)][asc|desc]);
)
​
#2.在以及存在的表中创建，这里注意了必须要写索引名
create [unique|fulltext|spatial] index|key 索引名 on 表名(字段名[(长度)][asc|desc]);
​
alter table 表名 add [unique|fulltext|spatial] index|key 索引名(字段名[(长度)][asc|desc]);

UNIQUE**： 可选参数，表示唯一索引

FULLTEXT**： 可选参数，表示全文索引

SPATIAL**： 可选参数，表示空间索引

INDEX**和KEY： 用来表示字段的索引，二者选一即可

索引名： 可选参数，表示创建的索引的名称

字段名： 指定索引对应字段的名称

长度： 可选参数，用于表示索引的长度

ASC和DESC： 可选参数，ASC表示升序，DESC表示降序

3.2删除索引

alter table 表名 drop index 索引名;
​
drop index 索引名 on 表名;

4.数据增删改查

---

4.1增

单条数据

insert into 表名(字段名1, 字段名2,.......) values(值1, 值2,.......);

#如若不指定字段，则添加数据要于字段顺序一致
insert into 表名 values(值1, 值2,.........);

insert into 表名 set 字段名1 = 值1, 字段名2 = 值2, 字段名3 = 值3,.......;

多条数据

insert into 表名 (字段名1, 字段名2,.......) values   #同样可以去掉字段名，但要注意添加顺序
(值1, 值2,.........),
(值1, 值2,.........),
.........;

4.2查

简单查询

select * from 表名;                             #一般企业不建议，要是数据多的话那完蛋了
select 字段名1, 字段名2, 字段名3,  ... from 表名

条件查询

select 字段名1, 字段名2, 字段名3,  ... from 表名 where 字段名 = 值

4.3改

update 表名 set 字段1=值1, 字段2=值2, ...... [where 条件表达式];

4.4删除

delete from 表名 [where 条件表达式];
truncate 表名 #有父表则失败，常常用于删除所有数据

4.5快速创建与已经存在的表一样的结构

create table if not exists 表名 like 旧表名;

4.6快速添加与已经存在的表一样的数据

insert into 表名1 [字段列表]
select 字段列表 from 表2;

字段列表的意思是：字段名 或者 字段名1, 字段名2, 字段名3......

4.7创建临时表

create temporary table 表名 字段列表|select 字段列表 from 已经存在的表名;
#临时表只能通过alter table修改

5单表查询

select [distinct] 字段列表
from 表名
where 条件表达式
group by 字段列表 [having 条件表达式] #分组用的
order by 字段名1[asc|desc], 字段名2[asc|desc],.... # 排序用的 
limit[offset] 记录数;

5.1范围查询

select 字段列表 from 表名 where 字段名 [not] between 值1 and 值2;

5.2空值查询

select 字段列表 from 表名 where 字段名 is [not] null;

5.3查询后去除重复值

select distinct 字段列表 from 表名;

5.4匹配字符串

select 字段列表 from 表名 where 字段名 [not] like '需要匹配的字符串';

5.5查询多个条件

select 字段列表 from 表名 where 条件表达式1 and|or 条件表达式2 ........;  

and 和 or可以一起用，但and优先级大于or

5.6高级查询

5.6.1聚合函数

count() ---返回字段的数量

sum() --- 返回字段之合

avg() --- 返回参数字段的平均值

max() --- 返回参数字段的最大值

min() --- 返回字段的最小值

其他函数直接查文档太多了，有需要的时候再看，就像java的API一样

5.6.2回溯统计

select [select选项] 字段列表 from 表名 [where 条件表达式]
group by 字段名1[asc|desc], 字段名2[asc|desc],.... with rollup;

5.6.3取别名

#为表取别名
select 字段列表 from 表名 as 别名;
#为字段取别名
select 字段名1 as 别名, 字段名2 as 别名...... from 表名;