MYSQL-数据库基本操作

数据库基本操作

操作数据库→操作数据库中的表→操作数据库中表的数据。

操作数据库

• 创建数据库：create database [if not exists] 数据库名 [character set 指定字符集 collate 指定数据库排序规则];，创建指定数据库【如果它不存在】【设置指定字符集和指定数据库排序规则】。
• 查看数据库：show databases;，查看所有的数据库。
• 使用数据库：use 数据库名; ，使用指定的数据库。
• 修改数据库：alter database 数据库名 character set 修改的字符集 [collate 数据库排序规则];，修改指定数据库的字符集【和数据库排序规则】（这里不能改变数据库名）。
• 删除数据库：drop database [if exixts] 数据库名;，删除指定数据库【如果它存在】。
• 查看创建数据库的语句：show create database 数据库名;。

操作数据库中的表

数据库的数据类型（列的类型）

以下列出常见的数据类型（未全部列举）：

• 数值

类型	描述	大小
tinyint	十分小的整数	1个字节
smallint	较小的整数	2个字节
mediumint	中等大小的整数	3个字节
int	标准的整数	4个字节
bigint	较大的整数	8个字节
float	单精度浮点数	4个字节
double	双精度浮点数	8个字节
decimal	字符串形式的浮点数	一般在金融计算时使用

• 字符串

类型	描述	大小
char	固定大小的字符串	0~255
varchar	可变长度大小的字符串	0~65535
tinytext	微型文本	28-1
text	文本字符串	216-1

• 时间和日期

类型	描述
data	YYYY-MM-DD，日期格式
time	HH:mm:ss，时间格式
datatime	YYYY-MM-DD HH:mm:ss，最常用的时间格式
timestamp	YYYY-MM-DD HH:mm:ss，时间戳
year	年份表示

• null：没有值，未知，最好不要使用null做运算，因为其运算后的值也是null。

数据库的字段属性

• unsigned：无符号的整数，声明后该列不能声明为负数。
• zerofill：0填充的，不足位数的，使用0来填充（比如：int(3)，5就会存为005）。
• 自增（auto_increment）：通常理解为自增，自动在上一条基础上+1（默认），类型必须为整数。
• 非空（not null）：如果设置为非空，添加数据时，不给其赋值就会出错。
• 默认（default）：设置默认值，如果不指定该列的值，就会自动填充为默认值。

创建表

-- 注意：这里的符号都需要使用英文
create table [if not exists] 表名(
	列名 类型 [字段属性] [索引] [注释],
	列名 类型 [字段属性] [索引] [注释],
	...
	列名 类型 [字段属性] [索引] [注释]
)[引擎设置] [字符集设置] [排序规则设置];

比如创建一个学生表，包含学号（int 非空 主键 自增），密码（varchar(20) 非空），姓名（varchar(10) 非空），性别（varcahr(2) 默认为男），生日（datatime），住址（varchar()），电话（varchar(11)）。

create table if not exists student(
	id int not null primary key auto_increment comment '学号',
	password varchar(20) not null comment '密码',
	name varchar(10) not null comment '姓名',
	sex varchar(2) default '男' comment '性别',
	birthday datetime comment '出生日期',
	address varchar(100) comment '地址',
	phone varchar(11) comment'电话'
)engine=INNODB charset=utf8 collate=utf8_general_ci;

查看创建表的语句：show create table 表名;。
查看表的结构：desc 表名;。

引擎中INNODB和MYISAM的区别

	MYISAM（5.5以前默认）	INNODB（5.5至今默认）
事务支持	不支持	支持
数据行锁定	不支持（表锁）	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为MYISAM的两倍
优点	节约空间，速度较快	安全性高，事务处理，多表多用户操作

字符集设置
MYSQL默认的编码是Latin1，不支持中文，我们一般设置为uft8。

查看表

show tables; -- 查看当前数据库下的所有表

修改表

• 修改表名

alter table 原表名 rename as 新表名;

• 增加表的字段

alter table 表名 add 字段名 类型 [属性];

• 修改表的字段

alter table 表名 modify 字段名 列属性; -- 只能修改列的属性
alter table 表名 change 原字段名 新字段名 [列属性]; -- 可修改字段名 也可修改列属性

• 修改表的属性

alter table 表名 [引擎设置] [字符集设置];

• 删除表的字段

alter table 表名 drop 字段名; -- 删除指定列

• 删除表

drop table [if exists] 表名; --删除指定表

操作数据库表中的数据

DML：数据操作语言

• 添加数据

-- 给指定表中插入对应字段的值 注意字段和数据值的一一对应
-- 插入单条数据
insert into 表名([字段名1,字段名2,字段名3...]) values ('值1','值2','值3',...);
-- 插入多条数据
insert into 表名([字段名1,字段名2,字段名3...]) values ('值1','值2','值3',...),('值1','值2','值3',...),...;
-- 省略字段插入 会与默认顺序对应插入（不建议使用）
insert into 表名 values ('值1','值2','值3',...);

• 修改数据（修改数据一定要指定条件，否则会将表中的数据全部修改）

-- 修改指定表中的满足条件的字段的数据
-- 修改一个字段
update 表名 set 修改的字段名 = '修改值' where 条件;
-- 修改多个字段
update 表名 set 修改的字段名 = '修改值',修改的字段名 = '修改值',... where 条件;

• 删除数据

--删除指定条件的行数据（如果没有条件会全部删除）
delete from 表名 where 条件;

-- 完全清空一个数据库表
truncate table 表名;

delete和truncate的区别：两者都都清空一张表的数据，而且不会删除表的结构，但是truncate会重新设置自增列，计数器会归零，而且truncate不会影响事务，而delete就无此功能了。delete是一条一条的数据删除，而truncate是先删除表，然后重新建一张与之前表一致的表，所以truncate在清空表时更为高效。

DQL：数据查询语言

模板：

select [all | distinct ] -- 全部查询或者去重 默认all
{* | table.* | [table.field1 [as alias1][,table.field2 [as alias2]][,...]]}
from table_name [as table_alias] -- as 起别名 可省略
	[left | right | inner join table_name2] -- 联合查询
	[where ...] -- 指定结果满足的条件
	[group by ...] -- 结果按照哪些字段分组
	[having ...] -- 过滤分组需要满足的条件
	[order by ...] -- 指定查询记录按一个或多个条件排序 asc升序默认 desc降序
	[limit {[offset,] row_count | row_countOffset}] --指定查询的记录从哪条到哪条

-- 查询出指定表的所有数据(* 表示通配符，全部匹配)
select * from 表名;

-- 查询指定表中的指定字段
select 字段名1,字段名2,... from 表名;

-- 查询指定数据并为指定字段起别名
select 字段名1 as 别名1,字段2 as 别名2,... from 表名;

-- 去重查询 对于重复的数据 只显示一条
select distinct 字段名 from 表名;

-- 查询指定条件下表中的数据
select * from 表名 where 条件;

-- 查看数据库版本
select version(); -- 函数
-- 计算
select 20*8-1 as 计算结果; -- 计算表达式
-- 变量
select @@auto_increment_increment; -- 查询变量

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量…

-- 查询出指定表中表达式的值
select 表达式 from 表名;

where条件：where条件可以使用and（与）或者or（或）连接，条件就是满足或者不满足。

操作符	描述
=	等于
!=或<>	不等于
>=	大于等于
<=	小于等于
not	非
between…and…	在…和…之间
in()	在什么集合范围中
is null	是不是为空
is not null	是不是非空
like	像，sql匹配，结合%和_使用进行模糊查询

分页（limit）和排序（order by）
分页作用：缓解数据库压力，有更好的用户体验。
排序规则：

• desc：降序
• asc：升序(默认)

//通过指定字段降序查询表中数据
select * from 表名 order by 字段 desc;
//通过指定字段升序查询表中数据
select * from 表名 order by 字段 asc;
//按照默认的升序查询表中的 起始值+1条到起始值+展示数据条数的记录
select * from 表名 limit 起始值,展示数据条数;
//查询表中的总数据条数
select count(*) from 表名;

分页展示时的计算规律(每页展示m条)：

• 第一页：limit 0,m;
• 第二页：limit m,m;
• 第三页：limit 2*m,m;
• …
• 第n页：limit (n-1)*m,m;