MySQL入门

MySQL基础

目录

• MySQL基础
  • 创建数据库
  • 查看数据库
  • 修改数据库信息
  • 删除数据库
  • 选择数据库
  • 存储引擎
  • 数据类型
  • 创建数据表
  • 查看表结构
  • 修改数据表
    • 添加字段
    • 修改字段数据类型
    • 删除字段
    • 修改字段名称
    • 修改表名
  • 删除数据表
  • 主键(primary key)
  • 外键约束
  • 唯一约束
  • 检查约束
  • 默认值
  • 非空约束
  • 查看表中的约束
  • 数据表查询语句(SELECT)
  • 过滤重复数据
  • 设置别名
  • 限制查询结构的记录条数
  • 对查询结果进行排序
  • 条件查询(WHERE)
  • 常用运算符
    • 算术运算符
    • 比较运算符
    • 逻辑运算符
    • 位运算符
  • 内连接查询
  • 外连接查询
  • 子查询
  • 分组查询
  • 指定过滤条件(HAVING)
  • 正则表达式
  • 插入数据
  • 修改数据(更新数据UPDATE)
  • 删除数据
  • 视图简介
    • 修改视图
    • 删除视图
  • 自定义函数
    • 修改自定义函数
    • 删除自定义函数
  • 存储过程
    • 创建存储过程
    • 修改存储过程
    • 删除存储过程
  • 触发器
    • 删除触发器
  • 创建索引
    • 查看索引
    • 修改和删除索引
  • 创建新用户
    • 修改用户
    • 用户授权
    • 删除用户权限
  • 事务(transaction)
  • 数据库备份
  • 数据库恢复

创建数据库

create database if not exists test
default character set utf8
default collate utf8_general_ci;

解释:

• if not exists:若数据库存在,则不创建数据库.
• character set: 数据库的字符集.
• collate: 数据库的校对规则.

查看数据库

show databases; -- 查看所有数据库

show databases like '%test'; -- 查看所有结尾为test的数据库

show databases like '%test%'; -- 查看所有中间含test的数据库

show create database 数据库名; -- 显示数据库相关的信息

修改数据库信息

alter database 数据库名
default character set 字符集
default collate 校验规则;

删除数据库

drop database if exists 数据库 -- 若数据库存在,删除数据库

选择数据库

use 数据库; -- 选择数据库

存储引擎

• ISAM:读取数据方面速度快,不占用大量的内存和存储资源,不支持事务处理,不支持外来键,不能容错,不支持索引.
• MyISAM:提供索引和字段管理.使用一种表格锁定的机制来优化多个并发的读写操作,需运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间.用来修复数据库文件的MyISAMChk工具和用来恢复浪费空间的 MyISAMPack工具.强调了快速读取操作，主要用于高负载的select.
  • 三种不同的存储结构：静态型、动态型、压缩型.
  • 静态型：指定义的表列的大小是固定.性能高,不管列中的值有多大，都会以最大值为准.
  • 动态型：如果列（即使只有一列）定义为动态的.占用较少的空间,性能的降低,会导致碎片的产生.
  • 压缩型：如果在数据库中创建在整个生命周期内只读的表，则应该使用MyISAM的压缩型表来减少空间的占用。
• MEMORY: 在内存中创建临时表来存储数据.磁盘文件只存储表的结构，其数据存储在内存中.这种表拥有极高的插入、更新和查询效率.默认使用哈希（HASH）索引，其速度比使用B-+Tree型要快.MySQL进程发生异常、重启或计算机关机等等都会造成这些数据的消失.
• CSV:data文件夹中生成一个CSV文件.不支持索引，没有主键列.不允许表中的字段为null.
• BLACKHOLE:支持事务，而且支持mvcc的行级锁,用于做日志记录或同步归档的中继存储,不适合使用.
• ARCIVE:适合存储大量独立的、作为历史记录的数据.提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引.
• PERFORMANCE_SCHEMA:收集数据库服务器性能参数.提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息.
• InnoDB:提供了ACID事务支持、系统崩溃修复能力和多版本并发控制（即MVCC Multi-Version Concurrency Control）的行级锁;该引擎支持自增长列（auto_increment）,自增长列的值不能为空.支持外键（foreign key） ,外键所在的表称为子表而所依赖的表称为父表.
• FEDERATED:将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库.适合数据库分布式应用.

|功能|MyISAM|MEMORY|InnoDB|Archive|
|---|---|---|---|---|---|
|存储限制|256TB|RAM|64TB|None|
|支持事务|No|No|Yes|No|
|支持全文索引|Yes|No|No|No|
|支持树索引|Yes|Yes|Yes|No|
|支持哈希索引|No|Yes|No|No|
|支持数据缓存|No|N/A|Yes|No|
|支持外键|No|No|Yes|No|

选择参考:

• 需要提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控 --> InnoDB
• 需要数据表主要用来插入和查询记录，较高的处理效率 --> MyISAM
• 只是临时存放数据，数据量不大，并且不需要较高的数据安全性，临时表，存放查询的中间结果 --> MEMORY
• 如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

set default_storage_engine=存储引擎; -- 临时修改数据库默认存储引擎(重启失效)

数据类型

分类:

• 整数
• 浮点数
• 定点数
• 日期和时间
• 字符串
• 二进制

数值类型:

• 整数: tinyint, smallint, mediumint, int, bigint.
• 浮点数: float, double.

日期或时间:
year, time, date, datetime, timestamp.

字符串:
char, varchar, binary, varbinary, blob, text, enum.

二进制:
bit, binary, varbinary, tinyblob, blob, mediumblob, longblob.

创建数据表

create table 数据库.表名(
    字段 数据类型,
    字段 数据类型,
    name varchar(10),
    id int(10)
);

> use 数据库;
> create table 表名(
    字段 数据类型,
    字段 数据类型,
    name varchar(10),
    id int(10)
);

查看表结构

describe 表名;
-- 或者:
desc 表名;
-- 或者
show create table 表名\G;

修改数据表

添加字段

alter table 表名 add column 新字段 数据类型 [first|after];
alter table db_test add column id int first; --first:新字段位于为首个字段
alter table db_test add column sex int after name; -- after:新字段在指定字段后

修改字段数据类型

alter table 表名 modify 字段名 数据类型;
alter table db_test modify name varchar(20);

删除字段

alter table 表名 drop 字段名;
alter table db_test drop name;

修改字段名称

alter table 表名 change 旧字段名 新字段名 数据类型;
alter table db_test change id student_id int(10);

修改表名

alter table 旧表名 rename 新表名;
alter table db_test rename test;

删除数据表

drop table if exists 表名1, 表名2...;

主键(primary key)

主键(PRIMARY KEY)
主键约束,一列或列的组合.其值能唯一标识表中的每一行.

create table时指定主键

create table test (
    id int(10) primary key,
    name varchar(20),
    salary float
);

定义所有列之后设置主键

create table test (
    id int(10),
    name varchar(20),
    salary float,
    primary key (id)
);

创建表时设置复合主键

create table test(
    id int(20),
    name varchar(20),
    salary float,
    primary key (id, salary)
);

修改表时添加主键

alter table 表名 add primary key (字段名);

外键约束

规则:

• 父表必须已经存在于数据库中，或者是当前正在创建的表。
• 必须为父表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。
• 在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。
• 外键中列的数目必须和父表的主键中列的数目相同。
• 外键中的列和父表主键中对应列的数据类型相同。

创建表时设置外键

create table 表名(
    列名 类型 primary key,
    列名 类型,
    ...,
    constraint 约束名
    foreign key (列名) references 父表(主键列名)
);

修改表时添加外键约束

alter table 表名 add constraint 约束名 foreign key (列名) references 父表(主键列名);

删除外键约束

alter table 表名 drop foreign key 约束名;

唯一约束

唯一约束:
要求该列唯一,可以为空,但是只能有一行为空.确保指定的列中没有重复的值.

创建表时指定

create table 表名(
    id int primary key,
    name varchar(20) unique,
    ...
);

修改表时添加唯一约束

alter table 表名 add constraint 约束名 unique (列名);

删除唯一约束

alter table 表名 drop index 约束名;

检查约束

更新表数据时,检查更新的数据是否满足限定条件.

创建时设置检查约束

create table 表名(
    列名 类型,
    类名 类型,
    ...,
    constraint 限定名 check (限定条件)
);

修改表时添加检查约束

alter table 表名 add constraint 约束名 check (约束条件);

删除检查约束

alter table 表名 drop constraint 约束名;

默认值

设置某列未设置时的默认值.

创建表时设置默认值

create table 表名(
    列名 数据类型,
    列名 数据类型 default 默认值,
    ...
);

修改表时添加默认值

alter table 表名 change column 列名 列名 数据类型 default 新默认值;

删除默认值

alter table 表名 change column 列名 列名 数据类型 default null;

非空约束

约束某列的取值不能为空.

在建表时设置非空约束

create table 表名(
    列名 数据类型 not null,
    列名 数据类型,
    ...
);

在修改表时添加非空约束

alter table 表名 change column 列名 列名 数据类型 not null;

删除非空约束

alter table 表名 change column 列名 列名 数据类型 null;

查看表中的约束

show create table 表名;

数据表查询语句(SELECT)

说明:

select 字段 from 表名 where 表达式 group by 字段 order by 字段 limit 数据数;

查询表中所有字段

select * from 表名;

查询表中指定字段

select 字段 from 表名;

过滤重复数据

select distinct 列名 from 表名;

设置别名

为表指定别名

select 别名.列名 from 表名 as 别名;

为列名指定别名

select 列名 as 新列名from 表名;

限制查询结构的记录条数

使用limit限制返回结果集中的记录的范围.

select 列名 from 表名 limit 起始行,结果个数;

注:

• limit是对查询结果集进行过滤.
• 结果集中的第一个结果为第0个.
• 若limit后只有一个整数,则表示从第0个结果开始,返回指定数量的结果.

对查询结果进行排序

注意:

• 若排序中存在空值,则作为最小值对待.
• 多个列进行排序时,从左到右依次排序.
• 若未指定,则按照插入的顺序进行排序.
• desc: 按降序排序; asc: 按升序排序.
• 多个列排序时,每个列都要加排序关键词.

select 列名 from 表名 order by 列名 desc, 列名 asc;

条件查询(WHERE)

多条件查询

select 列名 from 表名 where 约束条件 and 约束条件;

使用LIKE的模糊查询

select 列名 from 表名 where 列名 like '模糊匹配';
select 列名 from 表名 where 列名 like 'a%'; -- 选择所有以a开头的项
select 列名 from 表名 where 列名 like '%a%'; -- 选择所有中间包含a的项
select 列名 from 表名 where 列名 like '_a'; -- 选择所有以a结尾的两个字符组成的项

注:

• % : 用来匹配0个或多个字符.
• _ : 用来匹配一个字符.
• % 和 _ 都不能匹配空值.
• 尾空格可能导致匹配失败,一般在尾部添加 % 减少由于自动添加空格导致的匹配失败.

日期的查询语句

select 列名 from 表名 where 列名 between '日期一' and '日期二'; -- 返回在日期一和日期二之间的结果

常用运算符

算术运算符
加,减,乘,除.
比较运算符
大于,小于,等于,不等于.
like,in,between and,is null.
逻辑运算符
按位与,按位或,按位取反,按位异或,按位左移,按位右移.

算术运算符

运算符	说明
+	加
-	减
*	乘
/	除
%	取余

比较运算符

运算符	说明
=	等于
<	小于
<=	小于等于
>	大于
>=	大于等于
<=>	安全等于(不返回unknown)
is null	是否为null
is not null	是否非null
least	最小值
greatest	最大值
between and	是否在两者之间
in	是否是列表中的值
not in	是否不在列表之中
like	通配符匹配
regexp	正则表达式匹配

逻辑运算符

运算符	说明
not/!	非
and/&&	与
or/`
XOR	异或

位运算符

运算符	说明
`	`
&	按位与
^	按位异或
<<	按位左移
>>	按位右移
~	按位取反

内连接查询

内连接:
先确定第一个主表作为结果集,然后巴其他表有选择的连接到结果集中.

内连接只返回同时存在于两张表的行数据.

select 表1.列名, 表2.列名,... from 表1 inner join 表2 on 表1.字段 = 表2.字段;

外连接查询

左外连接
对于左表存在而右表没有的行,右表中的值设为null.

右外连接
对于又表存在而左表没有的行,左表中的值设为null.

select 列名,... from 左表 left join 右表 on 匹配条件;(左表.字段 = 右表.字段)

select 列名,... from 左表 right join 右表 on 匹配条件;(左表.字段 = 右表.字段)

子查询

一个查询语句嵌套在另一个查询语句内部.
子查询先计算,然后其结果作为外不查询的过滤条件.

常用操作符:
any, all, in, exists.

常用运算符:

• in:判断一个给定值是否存在与子查询的结果集.
• all,some,any:用于指定对比运算的限制.all:所有值都满足为true;some,any:只要某个值满足条件则返回true.
• exists:判断子查询的结果集中是否为空.

select 列名 from 表名 where 列名 in (子查询语句);
select 列名 from 表名 where 列名 exists (子查询语句);

分组查询

使用group by对行根据选择的列进行分组汇总.

• group by中选择的列必须是select选择的列中的一项.
• select若存在其他列,则需要对其处理保证分组匹配.

select 列名1, group_concat(列名2) as 新列名,操作符(列名3) from 表名 group by 列名; -- group_concat: 将group by相同的行整合.

指定过滤条件(HAVING)

having用来过滤分组.

• having用于过滤分组,where用来过滤数据行.
• having可以包含聚合函数,where不可以包含聚合函数.
• having在过滤分组后过滤,where在过滤分组前过滤.

select 列名 from 表名 group by 列名 having 过滤条件;

正则表达式

根据指定的匹配模式匹配要求的特殊字符串.

常用匹配列表

符号	说明	示例
^	匹配文本的开始字符	'^a'匹配以a开头的字符串
\(|匹配文本的结束字符|'a\)'匹配以a结尾的字符串
.	匹配任何单个字符	'a.a'匹配a和a之间有一个字符的字符串
*	匹配零个或多个前面的字符	'a *'匹配包含零个或多个a的字符串
+	匹配一个或多个前面字符的字符串	'a+'匹配至少有一个a的字符串
'..'	匹配指定字符的字符串	'ab'匹配包含ab的字符串
'[...]'	匹配含有字符集中的字符串	'[ab]'匹配含有a或b的字符串
'[^..]'	匹配含有字符集中不存在的字符的字符串	'[^ab]'匹配含有非a或非b的字符串
	匹配前面字符至少出现n个的字符串	'a{2}'匹配至少含有两个a的字符串
	匹配前面的字符至少出现n次,至多出现m次	'a{2,3}'匹配a至少出现a两次,之多出现三次的字符串
`	`	分隔不同的字符串

select 列名 from 表名
where 列名 regexp '正则表达式';

插入数据

两种格式:

1. insert into 表名 (列名...) values (值...);
2. insert into 表名 set 列名1 = 值1, 列名2 = 值2...;

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

insert into 表名 values (值,...);

insert into 表名 from 表名; --需要对应字段相同

修改数据(更新数据UPDATE)

使用update修改,更新一个或多个表.

update 表名 set 字段=值; -- 将表中的某个字段设为指定值

update 表名 set 字段=值 where 过滤条件; -- 指定条件下对字段进行更新

删除数据

删除表中的所有数据

delete from 表名;

根据条件删除表中的数据

delete from 表名 where 过滤条件;

视图简介

视图:

• 一个虚拟表,其内容由查询定义.
• 没有物理记录,不是以数据集的形式存储在数据库中.
• 视图的建立和删除只影响视图本身,不对对应的表产生影响.
• 视图建立后,其数据源的数据发生改变(update),视图也会相应发生改动.相应的,视图中的数据发生改动,对应的数据源也发生相应的改动.

create view 视图名
(新字段...)
as select 列名 from 表名
where 过滤条件;

修改视图

alter view 视图名
as select 列名 from 表名;

update 表名 set 列名=新值 where 过滤条件;

对视图中的数据进行修改,就是对对应的数据源进行修改.

删除视图

drop view if exists 视图名;

自定义函数

格式一

create function 函数名(参数 类型,...)
returns 返回类型 return 表达式;

格式二

delimiter $$ -- 首先重载标识符;为$$

-- 然后创建自定义函数
create function 函数名(参数 类型,...) returns 返回值类型 deterministic
begin --函数体,如
declare res int;
select 字段 from 表名 where 特定字段过滤条件 into res;
return res;
end
$$

delimiter ; -- 重载标识符为;

修改自定义函数

使用alter function修改自定义函数的相关参数.

删除自定义函数

drop function if exists 函数名;

存储过程

• 在数据库中创建并保存,适用于在不同的平台或程序中实现系统的特定功能.
• 可以被多次调用.
• 调用存储过程时,网络只传送调用语句,降低网络负载.
• 一次执行,产生的二进制代码保存在缓冲区,效率高.
• 安全性高.

创建存储过程

创建不带参数的存储过程

delimiter $$ -- 重载标识符 ; 为 $$

create procedure 过程名
begin
--具体的执行语句
end
$$

delimiter ; -- 将重载的标识符重设为 ;

call 过程名(); -- 调用存储过程

创建带参数的存储过程

delimiter $$ -- 重载标识符 ; 为 $$

create procedure 过程名
(in 列名 数据类型)
begin
-- 执行的语句
end
$$

delimiter ; -- 将重载后的标识符重设为 ;

修改存储过程

alter procedure 存储过程名 特征;

特征:

指令	说明
contains sql	表示子程序包含 SQL 语句，但不包含读或写数据的语句
no sql	子程序中不包含sql语句
reads sql data	子程序中包哈读数据的语句
modifies sql data	子程序中包含写数据的语句
`sql security {definer	invoker}`
comment '注释'	注释信息

• definer:定义者自己才能执行.
• invoker:调用者可以执行.

注:

• 若要修改存储过程的内容,可删除原存储过程,再重新创建.

删除存储过程

drop procedure if exists 过程名;

触发器

create 触发器名
{before|after} {insert|update|delete} on 表名
for each row
触发语句;

注:

• 触发器在满足定义的条件时触发,并执行触发器中定义的语句.
• insert:新行插入时触发;delete:从表中删除一行时触发;update:更改某一行时触发.
• before:在激发语句之前触发;after:在激发语句之后触发.
• for each row:对每一行都要激活触发器.
• 同一张表在同一触发条件下只能有一个触发器.

before型触发器

create trigger 触发器名
before insert on 表名
for each row
触发语句; -- 其中使用 new.列名 引用表中的列名

after型触发器

create trigger 触发器
after insert on 表名
for each row 
触发语句;

显示已有的触发器

show triggers\G;

删除触发器

drop trigger if exists [数据库] 触发器名;

• 数据库名为可选项,为未指定,默认为当前数据库.

创建索引

使用create index创建索引

create [unique|fulltext|spatial] index 索引名 on 表名(字段);

使用create table创建索引

create table 表名(
    列名 数据类型,
    ...,
    constraint primary key(列名,...),
    index [索引名] ( 列名,... ),
    unique index [索引名] (列名,...),
    foreign key [索引名] (列名,...)
);

使用alter table创建索引

alter table 表名 add [index|primary key|unique index|foreign key] [索引名] (列名,...);

• primary key: 表示创建该表的主键.
• index:创建该表的索引.
• unique index: 创建该表的唯一性索引.
• foreign key:创建该表的外键.

查看索引

show index from 表名 [from 数据库名]\G;

修改和删除索引

使用drop index删除索引

drop index 索引名 on 表名;

alter table 表名 change 列名 列名 数据类型 --删除主键primary key要删除指定列的自增长

alter table 表名 drop primary key; -- 再删除主键

创建新用户

create user '用户名'@'localhost' identified by '密码';

create user '用户名'@'localhost'; -- 创建无密码用户

修改用户

以下指令只能在root权限下执行.

修改用户名

rename user 旧用户@localhost to 新用户@localhost;

修改用户口令

set password for 用户名@localhost = '新密码';

删除用户

drop user 用户名@localhost;

删除用户不会影响其创建的表,索引.

用户授权

显示指定用户的所有权限

select * from mysql.user where User='指定的用户'\G;

用户授权

grant 权限 on 数据库.数据表 to 用户名@localhost;

权限:

• select: 访问权限.
• insert: 添加数据的权限.
• delete: 删除数据的权限.
• update: 更新数据库的权限.
• references: 创建外键的权限.
• create: 创建新表的权限.
• alter: 修改所有数据的权限.
• show view: 查看视图的权限.
• create routine: 创建存储过程的权限.
• alter routine: 更新或删除存储过程的权限.
• index: 定义或删除索引的权限.
• drop: 删除表或试图的权限.
• create temporary tables: 创建临时表的权限.
• create view: 创建新的视图的权限.
• execute routine: 调用存储过程的权限.
• lock tables: 锁定已有数据表的权限.
• all或all privileges: 所有权限.
• create user: 创建新用户的权限.
• show databases: 查看数据库的权限.

删除用户权限

删除指点的权限

revoke 权限,... on 数据库.数据表 from 用户名@localhost;

删除所有权限

revoke all on 数据库.数据表 from 用户名@localhost;

事务(transaction)

事务:
用户一系列的数据库操作序列.

特性:

• 原子性(atomicity):操作要么全部执行,要么全都不执行.
• 一致性(consistency):事务前后数据库保持一致性的状态.
• 隔离性(isolation):事务不受其他并发执行的事务的影响.
• 持久性(durability):一个已完成的事务对数据的变动在系统中时永久有效的.

begin; --开始事务

-- 具体的操作

commit; -- 提交事务

begin;

-- 具体的操作

rollback; -- 撤销事务
commit; -- 提交事务

数据库备份

将表数据导出到一个文本文件.
只导出数据内容,不包括表结构.

select * from 表名
into outfile '目录/文件'
fields terminated by '分隔符'
lines terminated by '分隔符';

数据库恢复

load data infile
'文件目录'
into table 新空表名
fields terminated by '分隔符'
optionally enclosed by '分隔符'
lines terminated by '分隔符';