MySQL数据库.md · 李梅馨/动物园

# MySQL数据库

### C(create):创建：

```mysql
创建数据库：create  chdatabase 数据库名称
创建数据库判断不存在，再创建：create database 数据库名称 character set gbk;
创建数据库，并指定字符集：create database 数据库名称 character set 字符集名
```

##### 语法：

```mysql
creat table 表名{
列名1 数据类型1,
列名2 数据类型2,
...
列名n 数据类型n//注意最后一行不加逗号
}
```

##### 数据库类型：

```mysql
int：整数类型     age.int,
double:小数类型    score double(5,2)[5位数，两位小数]
data：日期，只包含年月日，yyyy-MM-dd
datatime:日期，包含年月日时分秒，yyyy-MM-dd HH-mm-ss
timestamp:时间错类型  包含年月日时分秒  yyyy-MM-dd HH-mm-ss，若不给这个字段赋值，系统默认使用当前时间赋值
varchar：字符串     name varchar(20):姓名最大20个字符
```

```mysql
创建表
create table student(
	id int,
    name varchar(32),
    age int,
    birthday data,
    insert_time timestamp
);
复制表
create table 表名 like 被复制的表名；
```

### R：(retrieve):查询：

```mysql
查询所有数据库的名称：show databases;
查询某个数据库的字符集：查询某个数据库的创建语句：show create database 数据库名称；
查询某个数据库中所有的表名称：show tables;
查询表结构：desc 表名；
```

### U(update):修改：

```mysql
修改数据库的字符集：alter database 数据库名称 character set 字符集名称;
修改表名：alter table 表名 rename to 新的表名
修改表的字符集：alter table 表名 character set 字符集名称；
添加一列：alter table 表名 add 列名 数据类型；
修改列名称 类型：alter table 表名 change 列名 新列别 新数据类型；
			  alter table 表名 modify 列名 新数据类型；
删除列：alter table 表名 drop 列名；
```

### D(delete)删除：

```mysql
删除数据库：drop database 数据库名称；
判断数据库存在，存在再删除：drop database if exits 数据库名称；
删除表：drop table 表名；
	   drop table if exists 表名；
去除重复记录：distinct关键字,只能出现在所有字段的最前面，
```

### 使用数据库

```mysql
查询当前正在使用的数据库名称：select database();
使用数据库：use 数据库名称（）；
```

## DML:增删改表中数据，语法：

```mysql
1.添加数据：
insert into 表名（列名1，列名2，...列名n）
如果表名后不定义列名：
insert into 表名 values(值1，值2...值n)
2.删除数据
delete from 表名 [where 条件]
删除大表：truncate table emp1;
3.修改数据
update 表名 set 列名1 = 值1，列名2 = 值2，...[where 条件]；
4.表的复制：
create table 表名 as select语句
5.将查询结果加到一张表里
insert into dept1 select * from dept;
select * from dept1;
```

## DQL：查询表中的记录

##### 语法：

```mysql
select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定
```

##### 基础查询：

```mysql
1.多个字段查询
select 字段名1，字段名2...from 表名；//如果查询所有字段可以使用*来代替字段列表
2.去除重复：distinct
3.计算列：一般可以使用四则运算计算一些列的值
ifnull(表达式1【判断哪个字段是null】，表达式2)：null参与的运算，计算结果都为null
4.起别名：as：as也可以忽略
```

##### 条件查询：

```mysql
select 字段  from 表名 where 条件[先from，然后where，最后select]
字符串用单引号括起来
1.where子句后跟条件
2.条件运算符：> , < , <= , >= , = , <> 或者！= 不等于 , BETWEEN...AND ,
IN(集合) ：等同于or，直接用in（'xxx''xxx'）不是区间，是具体的值；not in 不在这几个值当中
LIKE（模糊查询）_:单个任意字符，查第二个字是马的"_马%"；查询名字是三个字的——；%：多个任意字符,查名字包含马字'%马%'
判断是否为（不为）空：IS（not）NULL,and 或者 &&，or 或者 ||,not 或者！
and or联合用，找出薪资大于1000的并且部门编号是20或30部门的员工：
select 字段 from 表名 where 条件 and （字段 or 字段）
ifnull空处理函数的意思是：例子：ifnull(english,0)如果english的值是null那么让它的值为0，
```

##### 排序查询：

```mysql
语法：
order by 排序方式
order by 排序方式1，排序方式2，...,排序字段2，排序方式2...
排序方式：ASC:升序（默认）；DESC:降序；//如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件【直接用逗号连接】
```

##### 聚合函数：将一列数据作为整体，进行纵向的计算（排除null值）

```mysql
count:计算个数   select  count(列名) from 表名
count(*):不是统计某个字段中的数据，而是统计总记录条数
count(列)：表示统计不为null的数据总个数
max:计算最大值	select  max(列名) from 表名
min:计算最小值 	select  min(列名) from 表名
sum:求和  	  select  sum(列名) from 表名
avg:计算平均值	select  avg(列名) from 表名
解决排除null：1.选择不包含非空的列进行计算2.ifnull函数
```

##### 分组查询：

```mysql
语法：group by 分组字段；按照字段分组【不用group by，所有数据自成一组】
   1.分组之后查询的字段：分组字段，聚合函数
   2.where和having的区别：where在分组之前进行限定，如果不满足，则不参与，having在分组之后进行限定，不满足结果，则不会被查询出来。where后不可以跟聚合函数，having可以进行聚合函数的判断
例子：select sex,avg(math),count(id) from student where math>70 group by sex having count（id）>2;
     按照性别分组，分别查询男，女的平均数，要求：分数要低于70分的人不参与分组，分组之后人数要大于2；
     分组函数不能直接出现在where句子中，因为group by在where后执行
```

##### 分页查询：

```mysql
语法：limit 开始的索引，每页查询的条数；
开始的索引 = （当前的页码-1）*每页显示的条数
例子：select*from 表名 limit 0,3;--第一页
     select*from 表名  limit 3,3;--第二页
```

##### 连接查询：

###### 内连接：

等值连接：条件时等量关系

```mysql
语法：...
      A
      join
      B
      on
      连接条件
```

非等值连接：连接条件的关系是非等量between。。and。。。

自连接：一张表看成两个表，假设A和B进行连接，使用内连接的话，凡是A和B能够匹配上的记录查询出来，

###### 外连接：

一张表看成两个表，假设A和B进行连接，有一张是主表，一张是副表，主要查询主表的数据，当副表中的数据没有和主表匹配，副表自动模拟null值匹配。join前加left

左外连接：左边主表left

右外连接：右边主表right

三张表连接查询：

A join b join C,A表先和B表连接，之后A表继续和C表连接

##### 子查询：

```mysql
1.select语句当中嵌套select语句，用括号括起来
2.where 【后面不能直接用分组函数】
ex：select * from emp where sal>(select avg(sal) from emp);//找出高于平均薪资的员工的信息
3.from 后面嵌套子句
ex：找出每个部门平均薪水的薪资等级
select 
	t.*,s.grade
from
	(select deptno,avg(sal) as avg(sal) from emp group by deptno) t//找每个部门的平均薪水
join
	salgrade s
on
	t.avg(sal) between s.losal and s.hisal;
4.union （可以将查询结果集相加，作用和or，in差不多，但是union可以将两个不相干的表连接在一起）
select ...
union
select ...
```

## 约束：

对表中的数据进行限定，保证数据的正确性，有效性和完整性

##### 主键约束（primary kry）：

```mysql
1.含义：非空且唯一 ; 一张表只能有一个字段为主键 ; 主键就是表中记录的唯一标识
在创建表时，添加主键约束
create table 表名(
	列名 int primary key,--给id主键约束
    列名 varchar(20)
)
2.删除主键
alter table 表名 modify 列名 int primary key;
3.自动增长：如果某一列是数值类型的。使用auto_increment可以来完成值得自动增长
  在创建表时，添加主键约束，并且完成自我增长。
create table 表名(
	列名 int primary key auto_increment,--给id主键约束
    列名 varchar(20)
)
4.删除自动增长
alter table 表名 modify 列名 int;
5.添加自动增长
alter table 表名 modify 列名 int auto_increment;
```

##### 非空约束（not null）：值不能为null

```mysql
1.创建表是添加约束
create table 表名(
	列名 int,
	列名 varchar(20) not null--列名为非空
) 
2.创建表完后，添加非空约束
alter table 表名 modify 列名 varchar(20) not null;
3.删除非空约束
alter table 表名 modify 列名 varchar(20);
```

##### 唯一约束（unique）：值不能重复

```mysql
创建表时，添加唯一约束
create table 表名(
	列名 int,
    列名 varchar(20) unique--添加了唯一约束
);
--注意mysql中，唯一约束限定的列的值可以有多个null
--删除唯一约束
alter table 表名 modify 列名 varchar(20);
alter table 表名 drop index 列名;
--在创建表之后，添加唯一的约束
alter table 表名 modify 列名 varchar(20) unique;
```

##### 外键约束（foreign key）：

外键:foreign key，外面的键(键不在自己表中),如果一张表中有一个字段(非主键)指向另外一张表中的主键，那么该字段称之为外键.

```mysql
1.在创建表时，可以添加外键
create table 表名（
    ...
    外键列
    construct 外键名称 foreign key 外键列名称 reference 主表名称（主表列名称）
）；
2.删除外键
alter table 表名 drop foreign key 外键名称
3.创建表之后，添加外键、
alter table 表名 add constraint 外键名称 (外键字段名称) references 主表名称（主表列名称）;
4.级联操作
添加级联的操作
  alter table 表名 add constraint 外键名称
  foreign key (外键字段名称) references 主表名称（主列表名称） on update cascade on delete cascade;
  分类：
  1.级联更新：on update cascade
  2.级联删除:on delete cascade
```

## 多表关系

```mysql
1.一对一
实现方式：可以在任意一方添加唯一（unique）外键指向另一方的主键,建议合成一张表
2.一对多
实现方式：在多的一方建立外键，指向一的一方的主键。
3.多对多
实现方式：需要借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
联合主键：primary key(sid,cid)
```

## 事务：

```mysql
一个事务是一个完整的业务单元
要想保证以上两条DML语句同时成功或者同时失败，需要使用事务机制
和事务相关语句只有DML
特性：
原子性：事务时最小的工作单元，不可再分
一致性：必须保证数据DML语句同时成功，同时失败
隔离性：A和B之间具有隔离

1.读未提交（read uncommitted），存在脏读现象：表示读到了脏的数据
设置事务隔离级别：set global transaction_isolation level read uncommitted;
查看事务的全局隔离级别：select @@global.transaction_isolation
2.读已提交（read committed）,不可重复读，解决脏读现象
3.可重复（repeatable read），读取到的数据是幻想。
4.序列化读/串行化读
持久性：最终数据必须持久化到硬盘文件，事务才成功
演示事务：
会自动提交DML语句，只要执行一次就提交一次
关闭自动提交：start transaction;
```

## 索引：会自动排序

查询一个表有两种方式：1.全表扫描     2.根据索引检索   3.经常出现在where语句

主键和具有nuique约束的字段自动添加索引，所以这两种索引效率较高

什么时候添加：数据量庞大；该字段很少DML操作；添加索引是给某个字段或某些字段

```mysql
语法：创建索引对象：create index 索引名 on 表名 （字段名）；
     删除索引对象：drop index 索引对象 on 表名；
```

索引实现原理：

通过B tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的

```mysql
select ename from emp where ename = 'smith';
通过所以索引转换为
select ename from emp where 物理地址  = 0x3;
```

索引的分类：单一索引【给单个字段添加索引】

复合索引【给多个字段联合起来添加1个索引】

主键索引【主键上会自动添加索引】

唯一索引【有unique约束的字段上会自动添加索引】

索引失效：select ename from emp where ename like = '%';

模糊查询时，第一个通配符使用的时候是%，这个时候索引是失效的

## 视图：

同一张表的数据，通过不同的角度去看待

可以隐藏表的实现细节，保密级别较高的系统。

```mysql
create view myview as select ename,ename from emp;
drop view myview;
只有DQL语句才能以视图的方式创建出来。但可以使用CRUD
对视图进行增删改查会影响原来的数据
```

## 三范式：

设计范式：不出现数据冗余

```mysql
三范式：
1.任何一张表都应该有主键，并且每一个字段原子性不可再分
2.建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖
多对多？三张表关系表两个外键
3.建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖
一对多？两张表，多的表加外键。
```

#### DCL:

管理用户：

1.添加用户：create user '用户名'@'主机名' identified by '密码'

删除用户：drop user '用户名'@'主机名'

修改密码：

update user set password = password('新密码') where user = '用户名'；

set password for '用户名'@'主机名' = password('新密码')

#### 一对一设计：主键共享，外键唯一

李梅馨/动物园

简介

发行版

贡献者

近期动态

李梅馨/动物园 .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

李梅馨/动物园