mysql连接数据了的dep_MySQL数据库基础学习笔记（二）

一、数据库表的约束

1、分类

约束名

关键字

含义非空约束

not null

值不能为空

唯一约束

unique

值不能重复

主键约束

primary key

值非空且唯一

外间约束

foreign key

让表于表产生关系，从而保证数据的正确性

2、详细使用

非空约束

创建表时，添加非空约束create table 表名(

字段名 字段类型 not null -- 对该字段添加了非空约束

);

创建后，如何添加非空约束alter table 表名 modify 字段名 字段类型 not null;

创建后，如何删除非空约束alter table 表名 modify 字段名 字段类型;

唯一约束

创建表时，添加唯一约束create table 表名(

字段名 字段类型 unique

);

创建后，如何添加唯一约束alter table 表名 modify 字段名 字段类型 unique;

创建后，如何删除唯一约束alter table 表名 drop index 字段名;

主键约束

注意：

1、一张表只能有一个字段为主键。

2、主键就是表中记录的唯一标识。

3、自增长只能用在主键上

创建表时，添加主键约束create table 表名(

字段名 字段类型 primary key

);

创建后，如何添加主键约束alter table 表名 modify 字段名 字段类型 primary key;

创建后，如何删除主键约束alter table 表名 drop primary key;

自动增长

如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

创建表时，添加主键约束，并且完成主键自增长create table 表名(

字段名 字段类型 primary key auto_increment

);

创建后，如何添加自动增长alter table 表名 modify 字段名 字段类型 auto_increment;

创建后，如何删除自动增长alter table 表名 modify 字段名 字段类型;

设置默认值

create table 表名(

字段名 字段类型 default 默认值

);

外键约束

简单例子-- 部门表 和 员工表 (1 对 多的关系)

-- 部门表(主表：一方，用来约束别人的表)

create table department(

id int primary key auto_increment,

dep_name varchar(20),

dep_location varchar(20)

);

-- 员工表(从表：多方，被别人约束的表)

create table employee(

id int primary key auto_increment,

name varchar(20),

age int,

dep_id int, -- 外键对应主表的主键

-- 创建外键约束

foreign key (dep_id) references department(id)

)

创建表时，添加外键约束create table 从表名(

外键字段名 字段类型, -- 外键对应主表的主键

[constraint] [外键名称] foreign key (外键列名称)

references 主表名称(主表列名称)

);

创建后，如何添加外键约束alter table 从表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

创建后，如何删除外键约束alter table 表名 drop foreign key 外键名称;

级联操作

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

注意：只能是创建表的时候创建级联关系。

-- 员工表

create table employee4(

id int primary key auto_increment,

name varchar(20),

age int,

dep_id int, -- 外键对应主表的主键

-- 创建外键约束

constraint emp_depid_fk2 foreign key (dep_id) references department(id) on update cascade on delete cascade

)

级联操作语法

描述on update cascade

级联更新，更新主表中的主键，从表中的外键列也自动同步更新

on delete cascade

级联删除

二、数据库的设计

1、表与表之间的关系

表与表之间的三种关系：

1、一对多：最常用的关系 部门和员工

键表原则：在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

2、多对多：学生选课表 和 学生表， 一门课程可以有多个学生选择，一个学生选择多门课程

键表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

3、一对一：相对使用比较少，可以创建成一张表。公民表 护照表

建表原则：可以在任意一方添加唯一外键指向另一方的主键

2、数据规范化(三大范式)

设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求

目前关系数据库有六种范式：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF，又称完美范式)。范式

特点1NF

原子性：表中每列不可再拆分。

2NF

不产生局部依赖，一张表只描述一件事情

3NF

不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。

三、数据库的备份和还原

命令行方式：

备份： mysqldump -u用户名 -p密码 需要备份的数据库名称 > 保存的路径

还原：登录-->创建-->使用(use 数据库名称)--->执行文件( source 文件路径)

图形化工具

四、多表查询

笛卡尔积

有两个集合A,B 取这两个集合的所有组成情况

要完成多表查询，需要消除无用的数据

1、多表查询的分类

graph LR

A[多表查询] -->B[内连接]

A[多表查询] -->C[外连接]

B --> D[隐式内连接]

B --> E[显式内连接]

C --> F[左外连接]

C --> G[右外连接]

隐式内连接

看不到 join 关键字，条件使用 where 指定

语法select 字段名 from 表1, 表2 where 条件;

显式内连接

使用 inner join ... on 语句, 可以省略 inner

语法select 字段列表 from 表1 [inner] join 表2 on 条件;

左外连接

查询的是左表所有数据以及其交集部分

语法select 字段列表 from 表1 left [outer] join 表2 on 条件;

右外连接

查询的是右表所有数据以及其交集部分

语法select 字段列表 from 表1 right [outer] join 表2 on 条件;

子查询

查询中嵌套查询，称嵌套查询为子查询。

子查询的不同情况：

1、子查询的结果是单行单列的时候

子查询的结果是一个值的时候，可以作为条件，使用运算符去判断。 运算符： > >= < <= =SELECT 查询字段 FROM 表 WHERE 字段=(子查询)

2、子查询结果是多行单列的时候

结果集类似于一个数组，父查询使用 in 运算符SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);

3、子查询的结果是多行多列的时候

子查询可以作为一张虚拟表参与查询SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件;

总结一下：

1、子查询结果是单列，则在 where 后面作为条件

2、子查询结果是多列，则在 from 后面作为表进行二次查询

五、事务

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

1、事务提交方式

修改事务的默认提交方式1、查看事务的默认提交方式:

SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交

2、修改默认提交方式：

set @@autocommit = 0;

手动提交事务 (Oracle数据库默认是手动提交事务)

首先要开启事务，再提交功能

SQL语句开启事务

start transaction;

提交事务

commit;

回滚事务

rollback;手动提交事务使用过程

graph TD

A[开启事务] --> B[执行SQL语句]

B --> | 成功 | C[提交事务]

B --> | 失败 | D[回滚事务]自动提交事务(mySQL默认自动提交)

一条DML(增删改)语句会自动提交一次事务。

2、事务的四大特征

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。

持久性：当事务提交或回滚后，数据库会持久化的保存数据。

隔离性：多个事务之间。相互独立。

一致性：事务操作前后，数据总量不变

3、事务的隔离级别(了解)

多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

存在问题

脏读：一个事务，读取到另一个事务中没有提交的数据

不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。

幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

隔离级别:

read uncommitted：读未提交产生的问题：脏读、不可重复读、幻读

read committed：读已提交 (Oracle默认)产生的问题：不可重复读、幻读

repeatable read：可重复读 (MySQL默认)产生的问题：幻读

serializable：串行化可以解决所有的问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低

数据库查询隔离级别select @@tx_isolation;

数据库设置隔离级别set global transaction isolation level 级别字符串;

六、DCL (管理用户，授权)

1、管理用户

查询用户1、切换到mysql数据库：use mysql;

2、查询user表：select * from user;

注：% 表示可以在任意主机使用用户登录数据库

添加用户create user '用户名'@'主机名' identified by '密码';

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

修改用户密码(2种方式)update user set password = password('新密码') where user = '用户名';

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

mysql中忘记了root用户的密码？1、管理员运行该cmd ---> net stop mysql --停止mysql服务

2、使用无验证方式启动mysql服务：mysqld --skip-grant-tables

3、打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功

4、use mysql

5、update user set password = password('你的新密码') where user = 'root';

6、关闭两个窗口

7、打开任务管理器，手动结束mysqld.exe 的进程

8、启动mysql服务，使用新密码登录

2、权限管理

查询权限show grants for '用户名'@'主机名';

授予权限grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

-- 给用户授予所有权限，在任意数据库的任意表上

GRANT ALL ON *.* TO '用户名'@'主机名';

撤销权限revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

内容来源于网络如有侵权请私信删除