操作数据库
查看所有数据库
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
方法插入记录
- 插入值与字段的类型要一致(兼容)
- 插入值与字段的个数要一致
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
truncate
后面没有where
条件!!!truncate
的效率比delete
高一些
truncate table `tableName`;
delete from table `tableName` [where condition];
truncate
删除后没有返回值;delete
删除后有返回值truncate
删除不能回滚;delete
删除可以回滚
delete
不会重置auto_increment
的数值truncate
会重置auto_increment
的数值
注意
- MySQL 运行在 safe-updates 模式下,会导致非主键条件下无法执行
update
/delete
命令
# 查看模式开关
show variables like 'SQL_SAFE_UPDATES';
# 修改数据库模式
SET SQL_SAFE_UPDATES = 0;
- 存在外键关系的时候,也可能会造成数据删除失败(需要先删除子表,再删除父表)这时我们可以删除外键关系
# 删除 `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 <表名>;