MySQL 01(SQL基础、约束、多表、事务)
MySQL 01
一、MySQL基础
数据库概述
- 数据库：用于存储和管理数据的仓库
- 数据库的特点：
- 持久化存储数据（数据库就是一个文件系统）
- 方便存储和管理数据
- 使用统一方式操作数据库（使用SQL）
配置MySQL
-
MySQL服务启动（Windows）
-
方法一：
手动启动或关闭：打开computer management->Services->MySQL
快捷打开Services
win+R->services.msc
-
方法二：
通过cmd.exe命令行：首先使用管理员打开cmd.exe
net start mysql启动服务net stop mysql关闭服务
-
-
MySQL的登录和退出
根据实际情况替换以下USERNAME、PASSWORD、IPADDRESS
-
本地连接：使用用户名和密码登录
-
直接输入用户名和密码
mysql -uUSERNAME -pPASSWORD -
后输入密码
mysql -uUSERNAME -p
-
-
远程连接：使用用户名和密码登录
-
直接输入用户名和密码（两种方式）
mysql -hIPADDRESS -uUSERNAME -pPASSWORDmysql --host=IPADDRESS --user=USERNAME --password=PASSWORD -
后输入密码
mysql -hIPADDRESS -uUSERNAME -p
-
-
退出
exitquit
-
数据库的备份与还原
-
备份
cmd命令
mysqldump -u用户名 -p密码 数据库名 > 保存的路径 -
还原
步骤：
-
登录数据库
-
创建数据库
-
使用数据库
-
执行文件
source 文件路径
-
MySQL目录结构
-
MySQL安装目录
配置文件：my.ini
-
MySQL数据目录
数据库：文件夹
表：文件
数据：文件中存储的数据
SQL概述
-
SQL(Structured Query Language)：结构化查询语言
定义了操作所有关系型数据库的规则，每一种数据库操作的方式存在不一样的地方，称为“方言”
-
SQL通用语法
- SQL语句可以单行或多行进行书写，以分号结尾
- 可以使用空格和缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
- 注释方法：
- 单行注释：
--空格注释内容或#注释内容 - 多行注释：
/*注释内容*/
- 单行注释：
-
SQL分类
-
DDL(Data Definition Language)
用来定义数据库对象：数据库，表，列
关键字：create, drop, alter
-
DML(Data Manipulation Language)
用来对数据库表中的数据进行增删改
关键字：insert, delete, update
-
DQL(Data Query Language)
用来查询数据库中表的记录（数据）
关键字：select, where
-
DCL(Data Control Language)
用来定义数据库的访问权限和安全级别，及创建用户
关键字：GRANT, REVOKE
-
操作数据库、表(DDL)
-
数据库的操作：CRUD
-
C(Create)：创建
-
创建数据库
create database 数据库名称; -
创建数据库，判断不存在，再创建
create database if not exists 数据库名称; -
创建数据库，并指定字符集
create database 数据库名称 character set 字符集名; -
示例：创建数据库，判断是否存在，并指定 字符集为gbk
create database if not exists db1 character set gbk;
-
-
R(Retrieve)：查询
-
查询所有数据库的名称
show databases; -
查询某个数据库的字符集：查询某个数据库的创建语句
show create database 数据库的名称;
-
-
U(Update)：修改
-
修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
-
-
D(Delete)：删除
-
删除数据库
drop database 数据库的名称; -
判断数据库存在，存在就删除
drop database if exists 数据库名称;
-
-
使用数据库
-
查询当前正在使用的数据库名称
select database(); -
使用数据库
use 数据库名称;
-
-
-
表的操作：CRUD
-
C(Create)：创建
-
创建表
create table 表名( 列名1 数据类型1, 列名2 数据类型2, 列名3 数据类型3, ... 列名n 数据类型n );注意：最后一列不加逗号
-
复制表
create table 表名dst like 表名src ; -
SQL中常见的数据类型：
-
int：整数类型
age int -
double：小数类型
score double(总共的数字个数, 小数点后的数字个数) -
date：日期，只包含年月日,
yyyy-MM-dd -
datetime：日期，包含年月日时分秒,
yyyy-MM-dd HH:mm:ss -
timestamp：时间戳，包含年月日时分秒,
yyyy-MM-dd HH:mm:ss如果将来不给timestamp这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
-
varchar：字符串
name varchar(最大字符个数)
-
-
创建表案例
create table student ( id int, name varchar(32), age int, score double(3, 1), birthdate date, insert_time timestamp );
-
-
R(Retrieve)：查询
-
查询某个表的的字符集：查询某个表的创建语句
show create table 表名; -
查询某个数据库中所有的表名称
show tables; -
查询表结构
desc 表名;
-
-
U(Update)：修改
-
修改表名
alter table 表名 rename to 表名; -
修改表的字符集
alter table 表名 character set 字符集; -
添加一列
alter table 表名 add 列名 数据类型; -
修改列名称和类型
alter table 表名 change 列名src 列名new 新数据类型;同时改一个指定列名的列名和表名
alter table 表名 modify 列名src 新数据类型;只改指定列名的数据类型
-
删除列
alter table 表名 drop 列名;
-
-
D(Delete)：删除
-
删除表
drop table 表名; -
先判断，再删除
drop table if exists 表名;
-
-
增删表中的数据(DML)
-
添加数据
添加一行数据
insert into 表名 (列名1,列明2...列名n) values(值1, 值2...值n);注意：
-
列名和值要一一对应
-
如果表名后，不定义列名，则默认给所有列添加值
insert into 表名 values(值1, 值2...值n); -
除了数字类型，其他类型需要使用引号（单双引号都可以）
-
添加多行数据格式
insert into 表名 (列名1,列明2...列名n) values (值1, 值2...值n), (值1, 值2...值n), (值1, 值2...值n), ... (值1, 值2...值n);
-
-
删除数据
delete from 表名 where 条件;注意：
delete from 表名;可以直接删除所有的数据（但是不推荐，有多少条记录就会有多少次删除操作）TRUNCATE TABLE 表名;先删除表，然后再创建一张一样的表（推荐使用，相当于删除全部数据的操作） -
修改数据
update 表名 set 列名1 = 值1, 列名2 = 值2...where 条件;注意：
如果不加任何的条件，则会将表中的所有的记录全部修改
查询表中的记录(DQL)
-
查询语法概述
select -- 字段列表 from -- 表名列表 where -- 条件列表 group by -- 分组字段 having -- 分组之后的条件 order by -- 排序 limit -- 分页限定 -
基础查询
-
多个字段查询
select 字段名1, 字段名2, ...from 表名注意：
如果查询所有的字段，则可以使用
*来代替字段列表select * from 表名 -
去除重复
select后加distinct注意：
只有两个得到的结果请求的各个字段完全一样才能去重复
-
计算列
一般可以使用四则运算计算一些列的值（一般只会进行数值型的计算），
若参与运算的字段中有非数值型的数据（如果有null则默认的运算结果都是null）则对这个字段使用：
ifnull(表达式1, 表达式2)其中表达式1判断指定字段是否为null，表达式2表示若该字段为null后的替换值 -
起别名
字段名+as+别名as可省略，用空格代替 -
基础查询示例Code
SHOW databases; USE db1; SHOW TABLES; DESC student; TRUNCATE TABLE student; INSERT INTO student (id, name, score) VALUES(01, "Jeff", 75); INSERT INTO student (id, name, score) VALUES(02, "Kim", 84); INSERT INTO student (id, name, score) VALUES(03, "Mohammed", 96); INSERT INTO student (id, name, score) VALUES(04, "Tim", 65); INSERT INTO student (id, name, score) VALUES(05, "Kim", 86); SELECT * FROM student; SELECT DISTINCT name,id FROM student; SELECT name, id, score, id + score FROM student; SELECT *, id + score idScore, id + ifnull(birthdate, 0) AS total_id FROM student;
-
-
条件查询
-
格式：
where后跟条件（字段+运算符） -
条件查询相关的运算符
-
>,<,<=,>=,=,<>SQL中判断相等用
=<>作用同!= -
between ... and...n bwtween a and b作用相当于n >= a && n <= b -
in()n in (a, b, c)作用相当于n = a or n = b or n = c -
like模糊查询-
like中的占位字符_单个任意字符%多个任意字符 -
模糊查询示例Code
SELECT * FROM student WHERE name LIKE "_i%"; /*查询名字中第二个字母是i的行*/ SELECT * FROM student WHERE name LIKE "____";/*查询名字中有四个字母组成的行*/ SELECT * FROM student WHERE name LIKE "%m%";/*查询名字里包含m的行*/ SELECT * FROM student WHERE name LIKE "J%";/*查询名字首字母是J的行*/
-
-
is null判断数值的条件可以用
=,如果是null需要用is null来判断 -
and,&& -
or,|| -
not,!
-
-
条件查询示例Code
SHOW databases; USE db1; SHOW TABLES; DESC student; TRUNCATE TABLE student; INSERT INTO student (id, name, score) VALUES(01, "Jeff", 75); INSERT INTO student (id, name, score) VALUES(02, "Kim", 84); INSERT INTO student (id, name, score) VALUES(03, "Mohammed", 96); INSERT INTO student (id, name, score) VALUES(04, "Tim", 65); INSERT INTO student (id, name, score) VALUES(05, "Kim", 86); -- DELETE FROM student WHERE score = 65; SELECT * FROM student WHERE id > 2 and id < 4; SELECT * FROM student WHERE id > 2 and id < 4; SELECT * FROM student WHERE id BETWEEN 2 AND 4; SELECT * FROM student WHERE id = 1 OR id = 4 OR id = 5; SELECT * FROM student WHERE id IN (1, 3, 5); UPDATE student set score = null WHERE name = 'Jeff'; SELECT * FROM student WHERE score IS NOT NULL;
-
-
排序查询
-
order by格式：order by 排序字段1 排序方式1， 排序字段2 排序方式2...多个字段排序，当前面字段的条件值一样时，才会判断后面的条件
-
排序方式：
ASC：升序（默认升序）DESC：降序 -
排序查询示例Code
SELECT * FROM student ORDER BY id desc, score desc;
-
-
聚合函数
-
概述
聚合函数是将一列数据作为一个整体，进行纵向的计算
聚合函数的计算会排除null值, 可以使用
ifnull()跳过排除聚合函数一般选择非空的列（主键）
-
聚合函数分类
-
count计算个数
count(*)表示表中包含所有的行数 -
max计算最大值
-
min计算最小值
-
sum计算和
-
avg计算平均值
-
-
聚合函数示例Code
SELECT max(score) FROM student; SELECT avg(score) FROM student;/*平均值按照非null的个数计算*/ SELECT max(ifnull(score, 0)) FROM student; SELECT avg(ifnull(score, 0)) FROM student;/*平均值仍然按照总个数计算*/
-
-
分组查询
-
group by格式group by 分组字段 -
分组之后查询的字段（select后写的字段）：分组字段、聚合函数
-
where和having的区别where在分组之前进行限定，如果不满足条件，则不参与分组，having在分组之后进行限定，如果不满足条件，则不会被查询出来where后不可以跟聚合函数，having可以进行聚合函数的判断
-
分组查询示例Code
SELECT * FROM student;
#将分数大于80和不大于80的所有行分组(1为大于80， 0为不大于80) select score > 80, avg(score), count(id) from student group by score > 80;
#先选出id大于1的行，再将分数大于80和不大于80的所有行分组 select score > 80, avg(score), count(id) from student where id > 1 group by score > 80;
#先选出id大于1的行，再将分数大于80和不大于80的所有行分组，最后选出两组中行数大于2的一组 select score > 80, avg(score), count(id) from student where id > 1 group by score > 80 having count(id) > 2;
#效果同上 先选出id大于1的行，再将分数大于80和不大于80的所有行分组，最后选出两组中行数大于2的一组 #对聚合函数字段进行起别名，方便查看 select score > 80, avg(score), count(id) as pcount from student where id > 1 group by score > 80 having pcount > 2; select score > 80, avg(score), count(id) pcount from student where id > 1 group by score > 80 having pcount > 2;
-
-
分页查询
-
格式
limit 开始的索引，每页查询的条数; -
公式
开始的索引 = (当前的页码-1)*每页显示的条数
-
示例Code
#每页查询3条数据，从索引0开始 select * from student limit 0, 3;#第一页，从索引0开始 select * from student limit 3, 3;#第二页，从索引3开始 select * from student limit 6, 3;#第三页，从索引6开始 -
limit关键字是MySQL独有的关键字，在其他数据库中分页查询的关键字是不同的
-
管理用户，授权(DCL)
-
管理用户
-
添加用户
创建用户格式：
CREATE USER '用户名'@'主机名' IDENTIFIED BY 密码;主机名使用通配符
%表示可以在任意主机使用用户登录数据库 -
删除用户
删除用户格式：
DROP USER '用户名'@'主机名'; -
修改用户密码
修改用户密码方式1：
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';修改用户密码方式2：
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码'); -
MySQL中忘记了root用户密码，解决方案
-
使用cmd命令停止mysql服务
net stop mysql需要管理员运行该cmd
-
使用无验证方式启动mysql服务:
mysql --skip-grant-tables -
再打开一个新的cmd窗口，直接输入
mysql，回车就可以登录 -
use mysql; -
update user set password = password('新密码') where user = ‘root’; -
关闭两个窗口
-
打开任务管理器，手动结束mysqld.exe的进程
-
启动mysql服务
-
使用新密码登录
-
-
查询用户
步骤：
-
切换到MySQL数据库
-
查询user表
通配符
%表示可以在任意主机使用用户登录数据库
-
-
-
授权
-
查询权限
SHOW GRANTS FOR '用户名'@'主机名'; -
授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';权限列表使用
all表示使用所有的权限，数据库和表名使用*.*表示使用所有的数据库和所有的表 -
撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
-
二、MySQL约束
约束概述
- 约束是对表中的数据进行限定，保证数据的正确性、有效性和完整性
- 约束的分类：
- 主键约束：primary key
- 非空约束：not null
- 唯一约束：unique
- 外键约束：foreign key
非空约束
-
非空约束：某一列的值不能为null
-
非空约束关键字：
not null -
添加非空约束
-
创建表时添加非空约束
示例Code
show DATABASES; use db1; show tables; CREATE TABLE stu( id INT, name varchar(20) not null ); -
创建完表后，添加非空约束
示例Code
show DATABASES; use db1; show tables; CREATE TABLE stu( id INT, name varchar(20) ); ALTER TABLE stu MODIFY name varchar(20) not null;#使用更改表中的字段数据类型进行添加非空约束 INSERT INTO stu VALUES(01, null);#ERROR select * from stu; -
删除非空约束
示例Code
show DATABASES; use db1; show tables; CREATE TABLE stu( id INT, name varchar(20) not null ); ALTER TABLE stu MODIFY name varchar(20);#使用更改表中的字段数据类型进行删除非空约束
-
唯一约束
-
唯一约束：某一列的值不能重复
添加唯一约束后，字段值可以是null，但不能再重复使用字段值null
-
唯一约束关键字：
unique -
添加唯一约束
-
创建表时，添加唯一约束
示例Code
show DATABASES; use db1; show tables; CREATE TABLE stu( ID INT, PHONE_NUMBER varchar(20) UNIQUE ); INSERT INTO stu VALUES(01, "8717"); INSERT INTO stu VALUES(02, "8717");#ERROR -
创建表后，添加唯一约束
示例Code
show DATABASES; use db1; show tables; CREATE TABLE stu( ID INT, PHONE_NUMBER VARCHAR(20) ); #注意：先要删除之前字段值重复的行才能添加唯一约束 ALTER TABLE stu MODIFY PHONE_NUMBER VARCHAR(20) UNIQUE;#使用更改字段数据类型来添加唯一约束 INSERT INTO stu VALUES(01, "8718"); INSERT INTO stu VALUES(02, "8718");#ERROR
-
-
删除唯一约束
-
使用
DROP INDEX关键字 -
示例Code
show DATABASES; use db1; show tables; CREATE TABLE stu( ID INT, PHONE_NUMBER VARCHAR(20) UNIQUE ); ALTER TABLE stu MODIFY PHONE_NUMBER VARCHAR(20);#使用更改字段数据类型的方法，无法删除唯一约束 ALTER TABLE stu DROP INDEX PHONE_NUMBER;#使用DROP INDEX关键字删除唯一约束 INSERT INTO stu VALUES(01, "8718"); INSERT INTO stu VALUES(02, "8718");
-
主键约束
-
主键约束：非空且唯一
注意：
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识
-
主键约束的关键字：
primary key -
添加主键约束
-
创建表时，添加主键
示例Code
show DATABASES; use db1; show tables; CREATE TABLE stu( ID INT PRIMARY KEY, PHONE_NUMBER VARCHAR(20) ); INSERT INTO stu VALUES(01, "8718"); INSERT INTO stu VALUES(01, "8718");#ERROR INSERT INTO stu VALUES(null, "8718");#ERROR -
创建表后，添加主键
示例Code
show DATABASES; use db1; show tables; CREATE TABLE stu( ID INT PRIMARY KEY, PHONE_NUMBER VARCHAR(20) ); #注意：先要删除之前字段值重复的行才能添加主键约束 ALTER TABLE stu MODIFY ID INT PRIMARY KEY; INSERT INTO stu VALUES(01, "8718"); INSERT INTO stu VALUES(01, "8718");#ERROR INSERT INTO stu VALUES(null, "8718");#ERROR
-
-
删除主键约束
-
使用关键字
DROP PRIMARY KEY -
示例Code
show DATABASES; use db1; show tables; CREATE TABLE stu( ID INT PRIMARY KEY, PHONE_NUMBER VARCHAR(20) ); ALTER TABLE stu MODIFY ID INT;#使用更改字段数据类型无法删除主键 ALTER TABLE stu DROP PRIMARY KEY;#使用DROP PRIMARY KEY关键字删除主键 INSERT INTO stu VALUES(01, "8718"); INSERT INTO stu VALUES(01, "8718"); INSERT INTO stu VALUES(null, "8718");
-
自动增长
-
自动增长经常与主键一起使用
-
如果某一列是数值类型的，使用
auto_increment可以来完成自动增长 -
创建表时，添加自动增长：
- 在主键约束后面添加自动增长关键字
auto_increment
- 在主键约束后面添加自动增长关键字
-
示例Code
``` sql show DATABASES; use db1;show tables;
CREATE TABLE stu( ID INT PRIMARY KEY AUTO_INCREMENT, PHONE_NUMBER VARCHAR(20) ); INSERT INTO stu VALUES(01, "8718"); INSERT INTO stu VALUES(111, "8718"); #给主键约束后的字段加AUTO_INCREMENT后，该字段值可以是null，写入的表的值是根据上一个int值的自增1后的结果 INSERT INTO stu VALUES(null, "8718"); ```-
案例演示：
在字段ID值为1和111后加入数个ID值为null的结果
-
-
创建表后，添加和删除自动增长
-
使用更改字段数据类型的方法添加和删除自动增长，格式
alter table 表名 modify 列名src 新数据类型; -
示例Code
show DATABASES; use db1; show tables; CREATE TABLE stu( ID INT PRIMARY KEY AUTO_INCREMENT, PHONE_NUMBER VARCHAR(20) ); #创建表后，删除自动增长，不用再写一遍主键约束关键字 ALTER TABLE stu MODIFY ID INT; #创建表后，添加自动增长，不用再写一遍主键约束关键字 ALTER TABLE stu MODIFY ID INT AUTO_INCREMENT; INSERT INTO stu VALUES(01, "8718"); INSERT INTO stu VALUES(111, "8718"); INSERT INTO stu VALUES(null, "8718");
-
外键约束
-
外键约束：让表与表之间产生关系，从而保证数据的正确性
-
外键约束的关键字：
foreign key -
创建表时添加外键
create table 表名( ... 外键列 constraint 外键名称 foreign key(外键列名称) references 主表名称(主表列名称)#constraint+外键名称，可以省略 ); -
创建表后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES 主表名称(主表列名称); -
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; -
级联操作
-
两个表通过外键约束，主表字段的值默认不能再更新或删除，但是可以通过开启级联更新和级联删除来更改主表字段值同时和子表外键字段值同步
-
级联跟新
ON UPDATE CASCADE -
级联删除
ON DELETE CASCADE -
示例Code
#同时开启级联跟新和级联删除 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
-
三、MySQL多表
多表关系
-
一对一
举例：人和身份证
分析：一个人只有一个身份证，一个身份证只能对应一个人
实现方式：
一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键
注意：一对一关系的情况通常会将两张表合成一张表来使用，单纯地实现一对一关系并不常见
-
一对多（多对一）
举例：部门和员工
分析：一个部门有多个员工，一个员工只能对应一个部门
实现方式：在“多”的一方（员工表）建立外键，指向“一”的一方（部门表）的主键
-
多对多
举例：学生和课程
分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
实现方式：多对多关系实现需要借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
多表案例
-
实现目标：旅游网站中线路分类和线路之间的是一对多的关系，线路和用户之间是多对多的关系
-
示例Code
SHOW DATABASES; USE db1; SHOW TABLES; #线路分类的表 CREATE TABLE tab_category ( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(100) NOT NULL UNIQUE ); #线路表 CREATE TABLE tab_route ( rid INT PRIMARY KEY AUTO_INCREMENT, rname VARCHAR(100) NOT NULL UNIQUE, price DOUBLE, rdate DATE, cid INT, FOREIGN KEY (cid) REFERENCES tab_category(cid) ); #用户表 CREATE TABLE tab_user ( uid INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(100) UNIQUE NOT NULL, birthdate DATE, telephone VARCHAR(11), email VARCHAR(100) ); #用户和线路的中间表 CREATE TABLE tab_intermediate ( uid INT, rid INT, PRIMARY KEY(uid, rid),#联合主键 FOREIGN KEY (rid) REFERENCES tab_route(rid), FOREIGN KEY (uid) REFERENCES tab_user(uid) );
数据库设计的范式
-
设计数据库时， 需要遵循一些规范。要遵循后边的范式要求，必须先遵守前边的所有的范式要求
-
三大范式：
-
第一范式(1NF)：每一列都是不可分割的原子数据项
-
第二范式(2NF)：在1NF的基础上，非主属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)
-
函数依赖：A->B，如果通过A属性（属性组）的值，可以确定唯一B属性的值。则称B依赖于A
学号可以唯一确定学生姓名，所以姓名依赖于学号
学号和课程名称可以确定唯一的分数，所以分数依赖于学号和课程名称这个属性组
-
完全函数依赖：A->B，如果A是一个属性组，则B属性值的确定，需要依赖于A属性组中所有的属性值
(学号，课程名称)->分数，分数依赖于学号和课程名称这个属性组
-
部分函数依赖：A->B，如果A是一个属性组，则B属性值的确定，只需要依赖于A属性组中的某一些值即可
(学号，课程名称)->学生姓名，学生姓名仅依赖于这个属性组中的学号
-
传递函数依赖：A->B，B->C，如果通过A属性（属性组）的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称C传递函数依赖于A
学号->系名，系名->系主任
-
码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性值）为该表的码
该表的码是学号和课程名称这个属性组
-
主属性：码属性组中的所有属性
-
非主属性：除过码属性组的属性
-
-
第三范式(3NF)：在2NF的基础上，任何非主属性不依赖于其他非主属性(在2NF的基础上消除传递依赖)
-
多表查询
-
笛卡尔积：有两个集合A和B，取这两个集合的所有的组合情况
要完成多表查询，需要消除无用的数据
-
内连接查询
-
内连接查询的思路：
- 从哪些表中查询数据
- 查询的条件
- 需要查询的字段
显式和隐式两种内连接方式，使用的作用相同，只是格式不同
-
隐式内连接
-
使用where条件消除无用数据
-
示例Code
SHOW DATABASES; USE db1; SHOW TABLES; #创建部门表 CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32) ); #插入部门名称信息 INSERT INTO dept(name) VALUES ('R&D'), ('marketing'), ('financial'); SELECT * FROM dept; #创建员工表 CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32), gender CHAR(1), salary DOUBLE, join_date DATE, dept_id INT, FOREIGN KEY (dept_id) REFERENCES dept (id) ); #插入员工信息 INSERT INTO emp(name, gender, salary, join_date, dept_id) VALUES('Jeff', 'm', 5000, '2021-5-3', 01); INSERT INTO emp(name, gender, salary, join_date, dept_id) VALUES('Lucy', 'f', 4000, '2021-6-3', 02); SELECT * FROM emp; SELECT * FROM dept, emp; SELECT * FROM emp, dept; #使用隐式内连接进行查询 #------------------ #不指定字段查询，显示查询到的子表和主表的所有字段信息 SELECT * FROM dept, emp WHERE dept.id = emp.dept_id; #指定字段查询，显示指定字段的对应信息 SELECT dept.name, emp.name, emp.gender FROM dept, emp WHERE dept.id = emp.dept_id; #同样是使用隐式内连接进行查询 #推荐格式：关键字换行写+变量使用重命名 SELECT t1.name, t2.name, t2.gender FROM dept t1, emp t2 WHERE t1.id = t2.dept_id;
-
-
显式内连接
-
格式：
select 字段列表 from 表名1 inner join 表名2 on 条件inner可以省略
-
示例Code
#显式内连接查询结果同隐式内连接 SELECT dept.name, emp.name, emp.gender FROM emp INNER JOIN dept ON dept.id = emp.dept_id; SELECT dept.name, emp.name, emp.gender FROM emp JOIN dept ON dept.id = emp.dept_id;
-
-
-
外连接查询
-
外连接查询的作用：若左表中存在外键列的某一值为null的，使用内连接查询无法查询到值为null的那一行信息，可以使用外连接来解决
-
两种外连接(左、右)方式，本质上是一样的，交换左右表在查询语句中的顺序相当于是更换了左或右的连接方式
-
左外连接
-
查询的是左表的所有数据+交集的部分(两表的内连接)
-
格式：
select 字段列表 from 表1(左表) left outer join 表2(右表) on 条件outer可以省略
-
-
右外连接
-
查询的是右表的所有数据+交集的部分(两表的内连接)
-
格式：
select 字段列表 from 表1(左表) right outer join 表2(右表) on 条件outer可以省略
-
-
-
子查询
-
子查询：查询中嵌套查询，称嵌套查询为子查询
-
子查询的结果是单行单列的
子查询的结果可以使用运算符去判断
可使用的运算符>, >=, =, <, <=
#查询员工表中工资最高的员工信息 SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);#查询员工表中小于平均工资的员工信息 SELECT * FROM emp WHERE emp.salary < (SELECT avg(salary) FROM emp); -
子查询的结果是多行单列的
子查询的结果可以使用运算符去判断
可用的运算符in(xx,xx,xx)相当于xx or xx or
#查询部门表中指定的两个部门id，查询结果是一个多行单列的结果 #SELECT id FROM dept WHERE name = 'R&D' OR name = 'marketing'; #SELECT id FROM dept WHERE name in ('R&D', 'marketing'); #查询员工表中指定部门的员工信息 SELECT * FROM emp WHERE emp.dept_id in (SELECT id FROM dept WHERE name in ('R&D', 'marketing')); -
子查询的结果是多行多列的
子查询可以作为一张虚拟表
#查询入职日期大于某个日期的员工表和部门表信息 SELECT * FROM emp, dept WHERE emp.dept_id = dept.id AND emp.join_date > '2021-5-5'; #使用子查询，将子查询结果作为一张虚拟表 #效果同上，查询入职日期大于某个日期的员工表和部门表信息 SELECT * FROM (SELECT * FROM emp WHERE emp.join_date > '2021-5-5') t1, dept t2 WHERE t1.dept_id = t2.id;
-
四、MySQL事务
事务概述
-
事务：
如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败
-
事务提交的两种方式：
-
自动提交
一条DML语句会自动提交一次事务
MySQL是自动提交的
-
手动提交
需要先开启事务，再提交
Oracle数据库是默认手动提交的
-
-
查看提交方式：
SELECT @@autocommit;得到的结果1表示自动提交，0表示手动提交
-
设置提交方式：
SET @@autocommit = 0;设置为手动提交 -
事务的操作：
-
开启事务：
start transaction使用开启事务，对数据进行持久化更新，就要进行手动提交
-
回滚：
rollback -
提交：
commit使用DML语句操作后，会自动提交，数据会持久化更新
-
-
示例Code
SHOW DATABASES; USE db2; SHOW TABLES; CREATE TABLE account( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32), balance DOUBLE ); INSERT INTO account (name, balance) VALUES ('Jeff', 1000), ('George', 2000); SELECT * FROM account; #若同时执行开始事务语句和以下的两条修改语句，只有在commit之后才能发生修改 #中间发生错误，暂存的数据可手动使用rollback恢复 START TRANSACTION; UPDATE account SET balance = balance - 500 WHERE name = 'Jeff'; AAA UPDATE account SET balance = balance + 500 WHERE name = 'George'; COMMIT; ROLLBACK;
事务的四大特征
- 四大特征：
- 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
- 持久性：当事务提交或回滚后，数据库会持久化的保存数据
- 隔离性：多个事务之间，相互独立
- 一致性：事务操作前后，数据总量不变
事务的隔离级别
-
事务隔离级别作用：多个事务之间是隔离的，相互独立的，但是如果多个事务操作同一批数据，会引发一些问题，设置不同的隔离级别就可以解决这些问题
-
存在的问题：
- 脏读：一个事务，读取到另一个事务中没有提交的数据
- 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样
- 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
-
隔离级别:
-
read uncommitted：读未提交
产生问题：脏读、不可重复读、幻读
-
read committed：读已提交(Oracle默认)
产生问题：不可重复读、幻读
-
repeatable read：可重复读(MySQL默认)
产生问题：幻读
可以解决虚读的问题，在两边的事务都提交的情况下才会，两次读到不一样的数据
-
serializable：串行化
可以解决所有的问题
一个事务操作数据表，另一个数据不可以操作这个数据表，只有当锁打开后才能操作
注意：隔离级别从小到大安全性越来越高，但是效率额越来越低
-
-
数据库查询和设置隔离级别：
查询隔离级别
select @@tx_isolation;设置隔离级别
set global transaction isolation level 级别字符串;
浙公网安备 33010602011771号