MySQL基础
目录
创建数据库
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} | 匹配前面字符至少出现n个的字符串 | 'a{2}'匹配至少含有两个a的字符串 |
| {n,m} | 匹配前面的字符至少出现n次,至多出现m次 | 'a{2,3}'匹配a至少出现a两次,之多出现三次的字符串 |
| |
分隔不同的字符串 | 'aa |
select 列名 from 表名
where 列名 regexp '正则表达式';
插入数据
两种格式:
insert into 表名 (列名...) values (值...);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 triggersG;
删除触发器
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 '分隔符';