发布日期: 2021-10-30

更新日期: 2021-12-03

文章字数: 8.6k

阅读时长: 32 分

阅读次数:

初识MySQL

数据库结构

DB	DBMS	SQL
DataBase（数据库，数据库实际上在硬盘上以文件的形式存在）	DataBase Management System（数据库管理系统，常见的有：MySQL Oracle DB2 Sybase SqlServer…）	结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。 SQL属于高级语言。只要能看懂英语单词的，写出来的sql语句，可以读懂什么意思。 SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行sql。（sql语句的编译由DBMS完成。）

DB：数据库
DBMS：数据库管理系统：DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。
SQL：数据库执行语句

执行过程：SQL(语句) -(输入)-> DBMS-(执行SQL语句)-> DB(通过SQL语句管理数据库)

语句构成

DQL（数据查询语言）	DML（数据操作语言）	DDL（数据定义语言）	TCL（事务控制语言）	DCL（数据控制语言）
查询语句，凡是select语句都是DQL。	insert、delete、update，对表当中的数据进行增删改。	create、drop、alter，对表结构的增删改。	commit提交事务，rollback回滚事务。(TCL中的T是Transaction)	grant授权、revoke撤销权限等。

SQL执行顺序

扩展

增删改查有一个术语：CRUD操作
分别代表：Create（增） Retrieve（检索） Update（修改） Delete（删除）
数据库默认端口号：3306

表结构

表：table是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强。
行和列：
- 行：被称为数据/记录(data)
- 列：被称为字段(column)
字段包括属性：
- 字段名
- 数据类型
- 相关的约束

创建、修改、复制、删除表

创建表

语法：

CREATE TABLE 表名 ( 
    属性名 数据类型 [完整性约束条件],
    属性名 数据类型 [完整性约束条件],
    ...
    属性名 数据类型 [完整性约束条件]
);

数据类型

关键字

数据类型	说明
int	整数型(Java中的int)
bigint	长整型(Java中的long)
float	浮点型(Java中的float double)
char	定长字符串(String)
varchar	可变长字符串(Java中的StringBuffer/StringBuilder)
date	日期类型(对应Java中的Java.,sql.Date类型)
blob	二进制大对象(存储图片、视频等流媒体信息) Binary Large OBject(对应java中的 Object)
clob	字符大对象(存储较大的文本，比如，可以存储4G的字符串) Character Large OBject(对应java中的 Object)
…

char与varchar的区别：
在实际开发中，当某个字串中的数据长度不发生改变的时候，是定长的，例：性别、生日都是采用char，当一个字段数据长度不确定，例如：简介、姓名都是采用varchar。

注意：像BLOB与CLOB这种数据类型是不可以在吗MySQL中直接输入的，需要使用Java中的IO流。

约束

概述

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

列级约束：
列级约束：当前字段添加的某个约束。
表级约束：
表级约束：多个字段联合起来添加某个约束。

关键字

关键字	说明
primary key	主键
foreign key	外键
not null	非空约束
unique	唯一索引
auto_increment	自增增加
default	默认值(主键自增与默认值互斥)
unsigned	无符号
check	检查约束：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

主键约束

主键作用：

表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
主键的作用：主键值是这行记录在这张表当中的唯一标识。(就像一个人的身份证号一样)。

主键相关术语：

主键约束：primary key
主键字段：id字段添加primary key之后，id叫做主键字段
id字段中的每一个值都是主键值

主键字段数量划分：

单一主键(常用，推荐) 又名：列级约束
复合主键(多个字段联合起来添加一个主键约束) 又名：表级约束。
注：复合主键不建议使用，因为复合主键违背三范式。

主键性质划分：

自然主键：在主键字段中不和系统的业务挂钩，称为自然主键。
业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键,拿着身份证号码作为主键。(不推荐用)注：最好不要拿着和业务挂钩的字段作为主键，因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生改变，但有的时候没办法发生变化。

注意：一张表的主键约束只能有一个。

外键约束

主键作用：

外键约束用于保证在一张表中某个字段数据与另一张表中的数据的一致性。

主键相关术语：

外键约束：foreign key
外键字段：添加有外键约束的字段
外键值：外键字段中的每一个值。

顺序要求：

创建表时，先创建父表，在创建子表。
删除表时，先删除子表，在删除父表。
删除数据时，先删除子表，在删除父表。
添加数据时，先添加父表，在添加子表。

注意：子表绑定父表中的字段最低要求必须是：绑定的父表中的字段可以不是主键但是至少是父表中的唯一标识。

例：

业务背景：
请设计数据库表,用来维护学生和班级的信息?
第一种方案: 一张表存储所有数据
no(pk)    name    classno        classname
----------------------------------------------
1       zs1      101        衡水一中高中一班
2       zs2      102        衡水一中高中一班
3       zs3      103        衡水一中高中一班
4       zs4      104        衡水一中高中二班
5       zs5      105        衡水一中高中二班
以上表存在缺点：冗余。

第二种方案: 两张表(班级表和学生表)
-- 班级表
create table tb_class (
     cno int primary key auto_increment,
     cname varchar(255)
     );
-- 学生表
create table tb_student (
     sno int primary key auto_increment,
     sname varchar(25),
     cno int,
     foreign key(cno) references tb_class(cno)    
       -- 绑定班级表中cno字段
     );
-- 两表查询结果
select s.sname'学生姓名',c.cname'班级' from tb_student s join tb_class c on s.cno = c.cno;
+--------------+--------------+
| 学生姓名     | 班级         |
+--------------+--------------+
| 张三         | 高中一班     |
| 李四         | 高中二班     |
+--------------+--------------+

插入表数据

语法1：

insert into 表名 values(值1,值2,值3,....);

注意：字段可以省略不写，但是后面插入的内容顺序都要与表的顺序与数量一致。

语法2：

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

注意：字段的数量和值的数量相同，并且数据类型要对应相同。

删除表数据

语法1：

delete from 表名 where 条件;

注意：没有条件则整张表的内容全部更新。

语法2：

truncate table 表名;

注意：此语句是针对数据库中数据量较大时使用且表被截断不可回滚，永久丢失！！！

修改表数据

语法：

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

注意：没有条件则整张表的内容全部更新。

复制表数据

语法1：

create table 表名 as select 查询语句;

注意：将查询结果当做表结构创建出来。

语法2：

insert into 插入表名 select 查询语句;
-- 将查询结果插入到表中

注意：插入表明必须与查询表中的字段一致。

删除表

语法：

drop table if exists 表名;        
-- 如果表存在则删除

表查询

关键字

关键字	说明
=	等于
!=或<>	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between … and …	两值之间，等同于 >= and <= (闭区间)
is null	为null(is not null 不为空)
and	并且
or	或者
in(条件1,条件2…)	包含，相当于多个or(not in不在这个范围中)
not	not可以取非，主要用在is或in中
like	like称为模糊查询，支持%(百分号)_(下划线)匹配
\	转义符(将后面字符转换为任意字符)

条件查询

语法：

select 字段名1 , 字段名2 from 表名 where 查询条件;

注意：

函数不能在where中使用，因为函数是在where之后执行的。

     2. 当一条语句中有 group by 的话, select 后面只能跟分组函数和参与分组的字段。

模糊查询

语法：

select 字段名1 ‘别名’, 字段名2 ‘别名’ from 表名 where 字段名 like 查询条件;

关键字	说明
_t%	查询第二个为t的数据
_/%	查询第二个为/的数据(其中”/“为转义符)
%	任意位置

分组查询/函数

聚合函数

函数	说明
ount()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值
rand()	可以在0和1之间产生一个随机数

注意：

以上函数会自动忽略null值。
SQL语法规定：任何数值与null运算均为null！
以上函数不能不出现在where关键字条件中。

count(*)/count(字段名)区别：

count(*)：不是统计某个字段中的个数，而是统计总记录数。
count(字段名)：表示统计此字段非null的总数量。

分组查询

语法：

select 函数(字段名),字段名...  from 表名 group by 字段名 having 过滤条件;

group by：将要以什么字段分组。
having：过滤使用分组查询出来的数据(可以使用where过滤实现尽量不要使用having过滤数据，性能较差)。

数据排序

语法：

-- 默认不写为升序
select 字段名1 , 字段名2 from 表名 order by 字段名;
-- 升序
select 字段名1 , 字段名2 from 表名 order by 字段名 asc;  
-- 降序
select 字段名1 , 字段名2 from 表名 order by 字段名 desc;

关键字

关键字	说明
asc	升序
desc	降序

其他关键字

distinct

distinct：关键字去除重复记录

语法1：

select distinct 字段名 from 表名;
-- distinct关键字可以放在函数里面
select avg(distinct 字段名) from 表名;
-- 以上表示对字段名去重然后在求平均数

if null(字段名 , 数值)

if null(字段名 , 数值)：判断该字段是否为空，若为空代替为什么数值。

语法：

select sum(if null(字段名1,0),字段名2) from 表名;
-- 如果此字段为空则将此字段看作零，然后在与字段名2求和运算

union

union：可以合并结果集。

语法：

select 字段名1 from 表名 from 表名 union select 字段名2 from 表名;

注意：合并结果集的时候，但需要查询字段对应个数相同。

limit

limit ：主要用于提取前几条或者中间某几行数据。

语法：

-- 以下两种语法显示结果相同
select 字符名 from 表名 limit 5;
select 字符名 from 表名 limit 0,5;

多表查询

概述：

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。

SQL语法：

SQL92：

select 字段名1,字段名2... from 表1 , 表2  where 筛表条件;

优点：
1. 条件和表链接写在一处方便简介；
2. 多表链接操作简单易行
缺点：
1. 条件和链接混合，易读性交叉
2. 链接条件支持有限，不支持左连接右连接等高级操作

SQL99(常用)：

select 字段名1,字段名2 from 表1 join 表2 on 筛表条件;

优点：
1. 功能强大，左右链接外链接等强力支持。
2. 条件链接分开，易读性好。
缺点：
1. 多表链接时需要仔细选择表顺序和链接条件。
2. 链接语句很长，书写麻烦。

表别名

语法：

select 字段名1,字段名2 from 表1 '别名1' join 表2 '别名2' on 筛表条件;

优点：

执行效率高，在mysql查询时直接去找指定表中的字段不用挨个去寻找。
可读性好。

连接方式

笛卡尔乘积现象

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积，这一种现象被称为：笛卡尔积现象。（笛卡尔乘积现象）

注意：如果避免了笛卡尔积现象，并不会减少记录的匹配次数，匹配次数还是原来匹配次数，只不过显示的是有效记录。

内连接

等值连接

特点：条件是等量关系。

-- 找出员工与之对应的部门名称并显示与之对应的员工姓名、部门名称
select e.员工姓名,m.部门名称 from 员工信息表 e , 部门表 m where e.部门编号 = m.部门编号;
+---------------------+--------------+
| 员工姓名            | 部门名称     |
+---------------------+--------------+
| 史密斯              | 研发部       |
| 艾伦                | 销售部       |
| 约翰逊              | 销售部       |
| 布朗                | 研发部       |
| 戴维斯              | 销售部       |
| 米勒                | 销售部       |
| 加西亚              | 会计部       |
| 威尔逊              | 研发部       |
| 摩尔                | 会计部       |
| 安德森              | 销售部       |
| 泰勒                | 研发部       |
| 托马斯              | 销售部       |
| 琼斯                | 研发部       |
| 杰克                | 会计部       |
| 阿富汗_勇格尔       | 研发部       |
| 西德尔_索泰         | 研发部       |
+---------------------+--------------+

非等值连接

特点：连接条件中的关系是非等量关系。

-- 找出员工与之对应的工资等级并显示员工姓名、薪资、工资等级
select e.员工姓名,e.薪资,m.等级'工资等级' from 员工信息表 e join 工资等级表 m on e.薪资 between m.最低值 and m.最高值;
+---------------------+---------+--------------+
| 员工姓名            | 薪资    | 工资等级     |
+---------------------+---------+--------------+
| 史密斯              |  800.00 |            1 |
| 艾伦                | 1600.00 |            3 |
| 约翰逊              | 1250.00 |            2 |
| 布朗                | 2975.00 |            4 |
| 戴维斯              | 1250.00 |            2 |
| 米勒                | 2850.00 |            4 |
| 加西亚              | 2450.00 |            4 |
| 威尔逊              | 3000.00 |            4 |
| 摩尔                | 5000.00 |            5 |
| 安德森              | 1500.00 |            3 |
| 泰勒                | 1100.00 |            1 |
| 托马斯              |  950.00 |            1 |
| 琼斯                | 3000.00 |            4 |
| 杰克                | 1300.00 |            2 |
| 阿富汗_勇格尔       |  820.00 |            1 |
| 西德尔_索泰         | 3090.00 |            5 |
+---------------------+---------+--------------+

全连接(未学很少用)

外连接

特点：不会忽略null字段的查询，查询到的行数只和表的前后位置相关。

-- 左连接
select 字段名1,字段名2 from 表1 left join 表2 on 筛表条件; 
-- 右连接
select 字段名1,字段名2 from 表1 right join 表2 on 筛表条件;

关键字

关键字	说明
left	左连接
right	右连接

注意：每个左连接都可以写成右连接，每个右连接可以写成左连接。

例子：

-- 查询所有员工的上级领导
select e.* from 部门表 e left join 员工信息表 m on m.部门编号=e.部门编号 where m.员工编号 is null;
+--------------+--------------+--------------+
| 部门编号     | 部门名称     | 所在地区     |
+--------------+--------------+--------------+
|           40 | 运维部       | 波士顿       |
+--------------+--------------+--------------+

多表查询

语法：

select 字段名1,字段名2 from 表1 join 表2 on 筛表条件 join 表3 on 筛表条件 ... ;

例子：

-- 找出每一个员工的部门名称、工资等级、以及上级领导
select a.员工姓名,d.员工姓名'上级领导',b.部门名称,c.等级'工资等级' from 员工信息表 a join 员工信息表 d on a.上级领导编号 = d.员工编号 join 部门表 b on a.部门编号 = b.部门编号 join 工 资等级表 c on  a.薪资 between c.最低值 and c.最高值;
+---------------------+--------------+--------------+--------------+
| 员工姓名            | 上级领导     | 部门名称     | 工资等级     |
+---------------------+--------------+--------------+--------------+
| 史密斯              | 琼斯         | 研发部       |            1 |
| 艾伦                | 米勒         | 销售部       |            3 |
| 约翰逊              | 米勒         | 销售部       |            2 |
| 布朗                | 摩尔         | 研发部       |            4 |
| 戴维斯              | 米勒         | 销售部       |            2 |
| 米勒                | 摩尔         | 销售部       |            4 |
| 加西亚              | 摩尔         | 会计部       |            4 |
| 威尔逊              | 布朗         | 研发部       |            4 |
| 安德森              | 米勒         | 销售部       |            3 |
| 泰勒                | 威尔逊       | 研发部       |            1 |
| 托马斯              | 米勒         | 销售部       |            1 |
| 琼斯                | 布朗         | 研发部       |            4 |
| 杰克                | 加西亚       | 会计部       |            2 |
| 阿富汗_勇格尔       | 琼斯         | 研发部       |            1 |
| 西德尔_索泰         | 布朗         | 研发部       |            5 |
+---------------------+--------------+--------------+--------------+

存储引擎

引例

    CREATE TABLE `tb_Demo` (
        `dno` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    -- 完整的建表语句

观察上表中的语句可得：

MySQL默认使用的存储引擎是InnoDB方式。
默认采用的字符集是UTF8。

概述

存储引擎这个名字只有在MySQL中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，就是“表的存储方式”），mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

语法

查看当前MySQL支持的存储引擎：

show engines \G

查看一下表的创建语句(含此表存储引擎)：

show create table 表名;

修改已存在的表的存储引擎：

alter table 表明 engine = 存储引擎名称;

存储引擎介绍(常见)

MyISAM

MyISAM这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。
MyISAM采用三个文件组织一张表：
1. xxx.frm（存储格式的文件）
2. xxx.MYD（存储表中数据的文件）
3. xxx.MYI（存储表中索引的文件）
优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点：不支持事务。

InnoDB

表的结构存储在xxx.frm文件中
数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。
InnoDB支持级联删除和级联更新。
优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

MEMORY

缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
优点：查询速度最快。
拓展：以前叫做HEPA引擎。

事务

概述

事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。

事务具有四个特征(ACID)：

A 原子性(Atomicity)
整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。
C 一致性(Consistency)
在事务开始之前与结束之后，数据库都保持一致状态。
I 隔离性((Isolation))
一个事务不会影响其他事务的运行。
D 持久性((Durability)

命令语句

命令语句	说明
select @@autocommit;	查看默认提交的状态是否是自动提交：0手动提交 1 自动提交
set @@autocommit=0或1;	开启或关闭事务
rollback	回滚事务
commit	提交事务

当执行DML语句是其实就是开启一个事务。
关于事务的回滚需要注意：只能回滚 insert、delete和update语句，不能回滚select(回滚
select没有任何意义)，对于 create、drop、 alter这些无法回滚，事务只对DML有效果。

注意：rollback，或者commit后事务就结束了。

自动提交模式

概述：自动提交模式用于决定新事务如何及何时启动。
启用自动提交模式：
1. 如果自动提交模式被启用，则单条 DML 语句将缺省地开始一个新的事务。
2. 如果该语句执行成功，事务将自动提交，并永久地保存该语句的执行结果。
3. 如果语句执行失败，事务将自动回滚，并取消该语句的结果。
4. 在自动提交模式下，仍可使用 start transaction语句来显式地启动事务。
  这时，一个事务仍可包含多条语句，直到这些语句被统一提交或回滚。
禁用自动提交模式：
1. 如果禁用自动提交，事务可以跨越多条语句。
2. 在这种情况下，事务可以用commit和rollback语句来显式地提交或回滚。

自动提交模式可以通过服务器变量autocommit来控制。

例子：

mysql> SET AUTOCOMMIT = OFF；
mysql> SET AUTOCOMMIT = ON；
或
mysql> SET SESSION AUTOCOMMIT = OFF；
mysql> SET SESSION AUTOCOMMIT = ON；
show variables like '%auto%'; -- 查看变量状态

事务隔离级别

事务的隔离级别决定了事务之间可见的级别，当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：

脏读取(Dirty Read)
一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提
交，这就出现了脏读取。
不可重复读(Non-repeatable Read)
在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就
是不可重复读。
幻像读(Phantom Read)
幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。

隔离级别

InnoDB(存储引擎)实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发
的事务：

级别	关键字	说明
一级	read umcommitted(读未提交)	允许一个事务可以看到其他事务未提交的修改。
二级	read committed(读已提交)	允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。
三级	repeatable read(可重复读)	确保如果在一个事务中执行两次相同的 SELECT 语句，都能得到相同的结果，不管其他事务是否提交这些修改。 (银行总账) 该隔离级别为 InnoDB 的缺省设置。
四级	serializable(串行化) 【序列化】	将一个事务与其他事务完全地隔离。

隔离级别与一致性问题的关系

隔离级别	脏读取	不可重复读取	幻想读取
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读取	不可能	不可能	对InnoB不可能
串行化	不可能	不可能	不可能

设置隔离级别命令


select @@transaction_isolation;	查看隔离级别
set session transaction isolation level repeatable 隔离级别;	设置事务隔离级别

索引

概述

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。

索引特点

BTREE 索引， HASH 索引

优点：提高查询，联合查询，分级和排序的时间
缺点：索引占空间，维护（创建，更新，维护）索引时需要耗费时间

索引的分类

普通索引
不加任何限制条件
唯一性索引
使用 UNIQUE 参数
全文索引
使用 FULLTEXT 参数，叧能创建在 CHAR、VARCHAR、TEXT 类型的字段上，叧有MyISAM 存储引擎支持全文索引。
单列索引
在一个字段上建立的普通索引，唯一性索引或全文索引。
多列索引
在多个字段上建立的普通索引，唯一性索引或全文索引。
空间索引
使用 SPATIAL参数，只有 MyISAN存储引擎支持空间索引，必须建立在空间数据类型上，且必须非空。

索引的设计原则

选择唯一性索引
为经常需要排序、分组和联合操作的字段建立索引
如 ORDER BY、 GROUP BY、 DISTINCT， UNION 等操作的字段，特删是排序
为常作为查询条件的字段建立索引
限制索引的数目
避免过多地浪费空间
尽量使用数据量少的索引
尽量使用前缀来索引
如索引TEXT类型字段的前 N 个字符。
Oracle 中有函数数索引，这个是是相当于 left(field, n)式的函数索引
删除不在使用或者很少使用的索引

创建索引

语法：

查看该查询是否使用索引：

explain select 查询语句;

创建表时创建索引

create table 表名 (
    属性名 数据类型 [完整约束条件],
    属性名 数据类型 [完整约束条件],
    …
    [unique|fulltext|spatial index|key [别名] (属性名 1 [(长度)] [asc|desc])
);

创建普通索引

create table index1 (
    id int,
    name varchar(20),
    sex boolean,
    index(id)
);
show create table index1\g;

创建唯一性索引

create table index2(
    id int unique,
    name varchar(20),
    unique index index2_id(id asc)
);
show create table index2\g;
-- 看到在字段 id 上建立了两个唯一索引 id 和 index2_id，当然这样是没有必要的。

创建全文索引

create table index3 (id int,
    info varchar(20),
    fulltext index index3_info(info)
) engine=myisam;

创建单列索引

create table index4 (
    id int,
    subject varchar(30),
    index index4_st(subject(10))
);

注意：令索引subject前10 个字符

创建多列索引

create table index5 (
    id int,
    name varchar(20),
    sex char(4),
    index index5_ns(name, sex)
)
explain select * from index5 where name=’ 123’ \g;
explain select * from index5 where name=’ 123’ and sex=’ n’ \g;

创建空间索引

create table index6 (
    id int,
    space geometry not null,
    spatial index index6_sp(space)
)engine=myisam;

已经存在的表上创建索引

create [unique|fulltext|spatial] index 索引名 on 表名 (属性名[(长度)] [asc|desc]);

创建普通索引

create index index7_id on example0(id);

创建唯一性索引

create unique index index_8_id on index8(course_id);

创建全文索引

create fulltext index index9_info on index9(info);

创建单列索引

create index index10_addr on index10(address(4));

创建多列索引

create index index11_na on index11(name, address);

创建空间索引

create spatial index index12_line on index12(line);

用alter table语句来创建索引

alter table 表名 add [unique|fulltext|spatial] index 索引名 (属性名[(长度)] [asc|desc]);

创建普通索引

alter table example0 add index index12_name(name(20));

创建唯一性索引

 alter table index14 add unique index index14_id(course_id);

创建全文索引

alter table index15 add index index15_info(info);

创建单列索引

alter table index 16 add index index16_addr(address(4));

创建多列索引

alter table index17 add index index17_na(name, address);

创建空间索引

alter table index18 add index index18_line(line);

删除索引

drop index 索引名 on 表名;

视图

概述

视图是一种根据查询(也就是select表达式)定义的数据库对象，用于获取想要看到和使用的局部数据。

视图有时也被成为“虚拟表”。
视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。
相对于从基表中直接获取数据，视图有以下好处：
1. 访问数据变得简单
2. 可被用来对不同用户显示不同的表的内容
3. 用来协助适配表的结构以适应前端现有的应用程序

视图作用

提高检索效率
隐藏表的实现细节【面向视图检索】

创建视图

语法：

create view 视图名 as select 字段名1,字段名2 from 表名;

删除视图

语法：

drop view 视图名;

DBA命令

新建用户

语法：

create user username identified by 'password';

说明：

username：你将创建的用户名

password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器。
例如：

create user p361 identified by '123';
-- 可以登录但是只可以看见一个库 information_schema

授权

语法：

grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;

说明：

dbname=*：表示所有数据库
tbname=*：表示所有表
login ip=%：表示任何 ip
password为空，表示不需要密码即可登录
with grant option：表示该用户还可以授权给其他用户

细粒度授权

root 用户登入mysql输入语法：

grant select,insert,update,delete on *.* to p361 @localhost Identified by "123";
-- 如果希望该用户能够在任何机器上登陆 mysql，则将localhost改为"%"

粗粒度授权

root 用户登入mysql输入语法(测试用户一般使用该命令授权)：

grant all privileges on *.* to 'p361'@'%' identified by "123";

注意：用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令：

grant all privileges on *.* to 'p361'@'%' identified by "123" with grant option;

privileges 包括：

alter：修改数据库的表
create：创建新的数据库或表
delete：删除表数据
drop：删除数据库/表
index：创建/删除索引
insert：添加表数据
select：查询表数据
update：更新表数据
all：允许任何操作
usage：只允许登录

回收权限

语法：

-- 撤销权限
revoke privileges on dbname[.tbname] from username;
-- 撤销所有权限
revoke all privileges on *.* from username;

修改密码：

update user set password = password('qwe') where user = 'p646';
-- 刷新权限
flush privileges;

导入导出

导出数据库

语法：

在 windows 的 dos 命令窗口中执行以下全部命令：

mysqldump 表名>输出路径+文件名.sql -u用户名 -p密码

例如：

mysqldump exercise_02>D:\exercise_02.sql -uroot -p123

导出指定库下的指定表

在 windows 的 dos 命令窗口中执行以下全部命令：

mysqldump 数据库名 表名>输出路径+文件名.sql -u用户名 –p密码

导入

source sql脚本绝对路径

数据库设计三范式

第一范式

数据库表中不能出现重复记录，每个字段是原子性的不能再分。

不符合第一范式的示例：

学生编号	学生姓名	联系方式
1001	张三	zs@gmail.com,1359999999
1002	李四	ls@gmail.com,13699999999
1001	王五	ww@163.net,13488888888

以上表存在问题：

最后一条记录的学生编号和第一条重复(不唯一，没有主键)
联系方式字段可以再分，不是原子性的。

解决方案如下：

学生编号(pk)	学生姓名	email	联系电话
1001	张三	zs@gmail.com	1359999999
1002	李四	ls@gmail.com	13699999999
1003	王五	ww@163.net	13488888888

关于第一范式，每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本要求，主要通常采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利行可能就采用一个字段了。

第二范式

第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖。

示例：

学生编号	学生姓名	教师编号	教师姓名
1001	张三	001	王老师
1002	李四	002	赵老师
1003	王五	001	王老师
1001	张三	002	赵老师

确定主键：

学生编号(pk)	教师编号(pk)	学生姓名	教师姓名
1001	001	张三	王老师
1002	002	李四	赵老师
1003	001	王五	王老师
1001	002	张三	赵老师

以上虽然确定了主键，但此表会出现大量的冗余，主要涉及到的冗余字段为“学生姓名”和“教师姓名”，出现冗余的原因在于，学生姓名部分依赖了主键的一个字段学生编号，而没有依赖教师编号，而教师姓名部门依赖了主键的一个字段教师编号，这就是第二范式部分依赖。

解决方案如下：

学生信息表

学生编号（ PK）	学生姓名
1001	张三
1002	李四
1003	王五

教师信息表

教师编号（ PK）	教师姓名
001	王老师
002	赵老师

教师和学生的关系表

学生编号(PK) fk→学生表的学生编号	教师编号(PK) fk→教师表的教师编号
1001	001
1002	002
1003	001

如果一个表是单一主键，那么它就复合第二范式，部分依赖和主键有关系
以上是一种典型的”多对多”的设计

第三范式

建立在第二范式基础上的，非主键字段不能传递依赖于主键字段。（不要产生传递依赖）

学生编号（ PK）	学生姓名	班级编号	班级名称
1001	张三	01	一年一班
1002	李四	02	一年二班
1003	王五	03	一年三班
1004	赵六	03	一年三班

从上表可以看出，班级名称字段存在冗余，因为班级名称字段没有直接依赖于主键，班级名称字段依赖于班级编号，班级编号依赖于学生编号，那么这就是传递依赖，解决的办法是将冗余字段单独拿出来建立表，如：

学生信息表

学生编号（ PK）	学生姓名	班级编号（ FK）
1001	张三	01
1002	李四	02
1003	王五	03
1004	赵六	03

班级信息表

班级编号（ PK）	班级名称
01	一年一班
02	一年二班
03	一年三班

以上设计是一种典型的一对多的设计，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一的一方的主键。

三范式总结

第一范式：有主键，具有原子性，字段不可分割。

第二范式：完全依赖，没有部分依赖。

第三范式：没有传递依赖。

注意：数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需求。

一对一设计，有两种设计方案：
第一种设计方案：主键共享

    t_user_login  用户登录表
    id(pk)        username            password
    --------------------------------------
    1                zs                    123
    2                ls                    456

    t_user_detail 用户详细信息表
    id(pk+fk)    realname            tel            ....
    ------------------------------------------------
    1                张三                1111111111
    2                李四                1111415621

第二种设计方案：外键唯一

    t_user_login  用户登录表
    id(pk)        username            password
    --------------------------------------
    1                zs                    123
    2                ls                    456

    t_user_detail 用户详细信息表
    id(pk)       realname            tel                userid(fk+unique)....
    -----------------------------------------------------------
    1                张三                1111111111        2
    2                李四                1111415621        1

一对多设计方案：

口诀：一对多，两张表，多的表加外键。

    班级t_class
    cno(pk)            cname
    --------------------------
    1                    班级1
    2                    班级2

    学生t_student
    sno(pk)            sname                classno(fk)
    ---------------------------------------------
    101                张1                1
    102                张2                1
    103                张3                2
    104                张4                2
    105                张5                2

多对多设计方案：

口诀：多对多，三张表，关系表，两个外键。

    t_student学生表
    sno(pk)        sname
    -------------------
    1                张三
    2                李四
    3                王五
    
    t_teacher 讲师表
    tno(pk)        tname
    ---------------------
    1                王老师
    2                张老师
    3                李老师

    t_student_teacher_relation 学生讲师关系表
    id(pk)        sno(fk)        tno(fk)
    ----------------------------------
    1                1                3
    2                1                1
    3                2                2
    4                2                3
    5                3                1
    6                3                3

小靳同学

https://xrebirth.github.io/posts/874fa88f.html

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源小靳同学 !

MySQL

动态代理(01)

动态代理概述代理，在我们日常生活之中就有体现，代购，中介，换IP，商家等等，比如有一家美国的大学，可以对全世界招生留学中介(代理 ) 留学中

2021-10-31 Java

Java基础

SQL语句(常用)(02)

SQL语句(常用)Mysql自带数据库：performance_schema 主要用于收集数据库服务器性能参数提供进程等待的详细信息，包括

2021-10-30 MySQL

MySQL

数据库笔记(01)

初识MySQL

数据库结构

语句构成

扩展

表结构

创建、修改、复制、删除表

创建表

语法：

数据类型

关键字

约束

概述

关键字

主键约束

主键作用：

主键相关术语：

主键字段数量划分：

主键性质划分：

外键约束

主键作用：

主键相关术语：

顺序要求：

例：

插入表数据

语法1：

语法2：

删除表数据

语法1：

语法2：

修改表数据

语法：

复制表数据

语法1：

语法2：

删除表

语法：

表查询

关键字

条件查询

语法：

模糊查询

语法：

分组查询/函数

聚合函数

分组查询

语法：

数据排序

语法：

关键字

其他关键字

distinct

语法1：

if null(字段名 , 数值)

语法：

union

语法：

limit

语法：

多表查询

概述：

SQL语法：

SQL92：

SQL99(常用)：

表别名

语法：

优点：

连接方式

笛卡尔乘积现象

内连接

等值连接

非等值连接

全连接(未学很少用)

外连接

关键字

例子：

多表查询

语法：

例子：

存储引擎