【MySQL】数据的操作

操作数据库

查看所有数据库

show databases;

创建数据库

create database [if not exists] `数据库名`;

更改数据库的字符集类型

alter database books character set gbk;

• 查看创建数据库时用的语句

show create database `数据库名`;

使用数据库

use `数据库名`;

• 查看正在使用的数据库

select database();

删除数据库

drop database [if exists] `数据库名`;

查看数据库版本

select version();

操作数据表

查看当前数据库中的表

show tables;

创建表

create table [if not exists] `tableName` (
`字段名1` 数据类型[长度] [约束] [注释],
# ...
`字段名n` 数据类型[长度] [约束] [注释],
)[表类型] [表字符集] [注释];

• [] 内的为可选项
• 字段之间用逗号 , 隔开

eg：创建 Book 表

create table book(
	id int 			   comment'编号',
	bName varchar(20) 	comment'图书号',
	price double 	  	comment'价格',
	authorId int 	  	comment'作者编号',
	publishDate datetime comment'出版日期' -- 最后一行没有 ,
);

• 查看创建表时用的语句

show create table `tbName`;

复制表的结构 + 数据

create table newTable 
select *
from author;

当然，也可以只复制 author 表的指定字段

复制表结构

create table newTable like tableName;

复制的表 newTable 中没有数据，只是把表 tableName 的关系模式复制过来了

• 复制一部分结构

create table newTable 
select id, name, grade 
from student
where 0; -- 写个恒不成立的条件，创建的表中就没有数据啦（ 0 → false ）

查看表的描述信息

desc `tbName`;
describe `tbName`;

删除表

drop table [if exists] `tbName`;

修改表名

alter table 旧表名 rename [to] 新表名;

操作字段

添加字段

alter table `tbName` add `字段` 类型 [约束];

• 可以通过 first / after 关键字控制字段添加的位置

alter table 表名 add 字段名 类型 [约束] first; -- 添加到第一个
alter table 表名 add 字段名 类型 [约束] after 字段名2; -- 添加到字段2的后面

修改字段

alter table 表名 alter column 字段名 类型 [约束];

alter table 表名 modify 字段名 类型 [约束]; -- 不修改字段名

alter table 表名 change 字段名 新字段名 类型 [约束]; -- 修改字段名

• 数据类型不对应 / 字符串长度不够 → 不能成功改名
• 修改的字段为主键时，不需要重复设置 primary key

通过 alter 设置自增列的默认属性值

# 使 auto_increment 从 10 开始
alter table `students` auto_increment = 10;

• auto_increment 一般只能为 key 设置
• 一个表中只有一个自增列

删除字段

alter table 表名 drop 字段名;

操作记录

插入记录

insert into ... set 方法插入记录

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

insert into ... values 方法插入记录

1. 插入值与字段的类型要一致（兼容）
2. 插入值与字段的个数要一致

insert into 表名 values(值1, 值2); -- 插入整行

insert into 表名(字段1, 字段2) values(值1, 值2); -- 插入指定字段

values 的多行插入

# 插入单条记录 (使用得比较多，效率低一些，但是容错率较高)
insert into 表名 [(字段1, 字段2)] values (值1, 值2);

# 插入多条记录
insert into 表名 [(字段1, 字段2)] values 
(值1, 值2),
(值1, 值2),
(值1, 值2);

使用子查询插入记录

insert into beauty(id, name, phone)
    select id, name, '123456'
    from girls where id < 3;

这样就可以在一张表中快速导入另一张表中的数据啦

insert into beauty(id, name, phone)
select 1, 'superman', '123456' union
select 2, 'superwomen', '123567' union
select 3, 'superchild', '234567';

null 字段的插入

• 字段 & 值都省略
• 字段写上，值使用 null

修改记录

单表修改

update 表名 set 字段1=值1, 字段2=值2 where 条件;

要加条件，不然整个表都会被修改

eg：修改 boys 表中 id 为 2 的名称为 ‘张飞’，魅力值为 10

update boys
set name = '张飞', usercp = 10
where id = 2;

多表修改

其实就是把几张表连接起来，再一起修改

update 表1
inner|left|right join 表2
on 连接条件
set 字段1=值1,  字段2=值2, ... ;

eg：修改张无忌的女朋友的手机号码为 114

update boys b
inner join beauty be
on b.id = be.boyfriendId
set be.phone = '114'
where b.boyName = '张无忌';

删除记录

单表删除

delete from `tbName` where 条件;

要加条件，不然整个表的记录都会被删除。

eg：删除所有姓赵的同学

delete from `students` where `stuName` like '赵%'; --  %:任意数量的字符  _:任意一个字符

多表删除

其实就是把几张表连接起来，再一起删除

delete 表1的别名, 表2的别名
from 表1 别名
	inner|left|right 表2 别名
	on 连接条件
where 筛选条件;

eg：删除张无忌的女朋友的信息

delete be -- 这里写的是需要删除的记录所在的表
from beauty be
inner join boys b on be.boyfriendId = b.Id
where b.name = '张无忌';

eg：删除黄晓明的信息以及他女朋友的信息

delete be, b -- 这里写的是需要删除的记录所在的表
from beauty be
inner join boys b
on be.boyfriendId = b.id
where b.name = '黄晓明';

truncate & delete

1. truncate 后面没有 where 条件！！！
2. truncate 的效率比 delete 高一些

truncate table `tableName`;
delete from table `tableName` [where condition];

3. truncate 删除后没有返回值；delete 删除后有返回值
4. truncate 删除不能回滚；delete 删除可以回滚

• delete 不会重置 auto_increment 的数值
• truncate 会重置 auto_increment 的数值

注意

1. MySQL 运行在 safe-updates 模式下，会导致非主键条件下无法执行 update/ delete 命令

# 查看模式开关
show variables like 'SQL_SAFE_UPDATES';

# 修改数据库模式
SET SQL_SAFE_UPDATES = 0;

2. 存在外键关系的时候，也可能会造成数据删除失败（需要先删除子表，再删除父表）这时我们可以删除外键关系

# 删除 `Students` 表中的外键 `keyName`
alter table `Students` drop foreign key `keyName`;

索引

• 索引是加快数据检索的一种工具
• 一个基本表可建立多个索引，从不同角度加快查询速度
• 聚集索引可以极大地提高查询速度，但是给数据的修改带来困难
• 索引的类型有：聚集 / 非聚集
  • 非聚集索引就是普通索引，一个基本表 → 多个普通索引
  • 一个基本表 → 一个聚集索引
• 建立了聚集索引的基本表一般仅执行查询操作，很少进行更新操作

创建索引

create [unique] [ clustered | nonclustered ] index <索引名>
on <表名> ( <字段名1> [ asc | desc ] [, <字段名2> [asc | desc] ... ] );

• unique：建立唯一索引
• clustered | nonclustered ：建立聚集 / 非聚集索引（默认）

删除索引

• 建立索引后，不需要用户管理，由系统自动维护
• 可删除那些不经常使用的索引

drop index <索引名> on <表名>;

数据的查询