题记:
文章内容输出来源:拉勾教育大数据开发高薪训练营
本篇文章是java学习课程中的一部分笔记。
本博文主要是记录一些基础的知识点,通过实操更容易理解
这章主要讲的是mysql的使用,
MySQL基础&SQL入门
什么是数据库1. 数据库(DataBase) 就是存储和管理数据的仓库
2. 其本质是一个文件系统, 还是以文件的方式,将数据保存在电脑上
为什么使用数据库数据存储方式的比较
存储方式
优点
缺点
内存
速度快
不能够永久保存,数据是临时状态的
文件
数据是可以永久保存的
使用IO流操作文件, 不方便
数据库
- 数据可以永久保存
- 方便存储和管理数据
- 使用统一的方式操作数据库
(SQL)
占用资源,有些数据库需要付费(比如Oracle数据库)
通过上面的比较,我们可以看出,使用数据库存储数据, 用户可以非常方便对数据库中的数据进行增加, 删除, 修改及查询操作。
开发中常见的数据库
数据库名
介绍
MySql数据库
开源免费的数据库
因为免费开源、运作简单的特点,常作为中小型的项目的数据库首选。
MySQL1996年开始运作,目前已经被Oracle公司收购了. MySQL6.x开始收费
Oracle数据库
收费的大型数据库,Oracle公司的核心产品。安全性高
DB2
IBM公司的数据库产品,收费的超大型数据库。常在银行系统中使用
SQL Server
MicroSoft 微软公司收费的中型的数据库。
C#、.net等语言常使用。
但该数据库只能运行在windows机器上,扩展性、稳定性、安全性、性能都表现 平平。
为什么选择MySQL ?
- 功能强大,足以应付web应用开发
- 开源, 免费
MySql 的目录结构1) MySQL 安装目录MySql的默认安装目录在 C:\Program Files\MySQL\MySQL Server 5.7
目录 目录内容 bin 放置一些可执行文件 docs 文档 include 包含(头)文件 lib 依赖库 share 用于存放字符集、语言等信息。
2) MySQL 配置文件 与 数据库及 数据表所在目录
my.ini 文件 是 mysql 的配置文件,一般不建议去修改
data< 目录 > Mysql 管理的数据库文件所在的目录几个概念数据库: 文件夹
表: 文件
数据: 文件中的记录
数据库管理系统1) 什么是数据库管理系统 ?
数据库管理系统(DataBase Management System,DBMS):指一种操作和管理维护数据库的大型软件。
MySQL就是一个 数据库管理系统软件, 安装了Mysql的电脑,我们叫它数据库服务器.
2) 数据库管理系统的作用
用于建立、使用和维护数据库,对数据库进行统一的管理。
3)数据库管理系统、数据库 和表之间的关系
MySQL中管理着很多数据库,在实际开发环境中 一个数据库一般对应了一个的应用,数据库当中保存着多张表,每一张表对应着不同的业务,表中保存着对应业务的数据。
数据库表数据库中以表为组织单位存储数据表类似我们Java中的类,每个字段都有对应的数据类型
那么我们使用熟悉的Java程序来与关系型数据对比,就会发现以下关系:
![]()
类 -----> 表
类中属性 ----> 表中字段
对象 ---> 数据记录
SQL( 重点 )SQL 的概念1) 什么是SQL ?
结构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。
2) SQL 的作用
是所有关系型数据库的统一查询规范,不同的关系型数据库都支持SQL
所有的关系型数据库都可以使用SQL
不同数据库之间的SQL 有一些区别 方言
SQL 通用语法1) SQL语句可以单行 或者 多行书写,以分号 结尾 ; (Sqlyog中可以不用写分号)
2) 可以使用空格和缩进来增加语句的可读性。
3) MySql中使用SQL不区分大小写,一般关键字大写,数据库名 表名列名 小写。
4) 注释方式
注释语法 说明 -- 空格 单行注释 /* */ 多行注释 # MySql特有的单行注释 # show databases; 单行注释 -- show databases; 单行注释 /* 多行注释 show databases; */
SQL 的分类
分类
说明
数据定义语言
简称DDL(Data Definition Language),用来定义数据库对象:数据库,表,列等。
数据操作语言
简称DML(Data Manipulation Language),用来对数据库中表的记录进行更新。
数据查询语言
简称DQL(Data Query Language),用来查询数据库中表的记录。
数据控制语言
简称DCL(Date Control Language),用来定义数据库的访问权限和安全级别,及创建用户。(了解)
注: 我们重点学习 DML 与 DQL!
DDL操作 数据库
创建数据库
命令
说明
create database 数据库名;
创建指定名称的数据库。
create database 数据库名 character set字符集;
创建指定名称的数据库,并且指定字符集(一般都指定utf-8)
查看/选择数据库
命令
说明
use 数据库
切换数据库
select database();
查看当前正在使用的数据库
show databases;
查看Mysql中 都有哪些数据库
show create database 数据库名;
查看一个数据库的定义信息
修改数据库
修改数据库字符集
命令
说明
alter database 数据库名 character set 字符集;
数据库的字符集修改操作
删除数据库
命令 说明 drop database 数据库名
从MySql中永久的删除某个数据库 /*方式1 直接指定数据库名进行创建默认数据库字符集为:latin1*/ CREATE DATABASE db1; /*方式2 指定数据库名称,指定数据库的字符集一般都指定为 utf8,与Java中的编码保持一致*/ CREATE DATABASE db1_1 CHARACTER SET utf8; --切换数据库 从db1 切换到 db1_1 USE db1_1; --查看当前正在使用的数据库 SELECT DATABASE(); --查看Mysql中有哪些数据库 SHOW DATABASES; -- 查看一个数据库的定义信息 SHOW CREATE DATABASE db1_1; -- 将数据库db1 的字符集 修改为 utf8 ALTER DATABASE db1 CHARACTER SET utf8; -- 查看当前数据库的基本信息,发现编码已更改 SHOW CREATE DATABASE db1; -- 删除某个数据库 DROP DATABASE db1_1;
DDL 操作 数据表
MySQL常见的数据类型
1)常用的数据类型:
描述
int
整型
double
浮点型
varchar
字符串型
date
日期类型,给是为 yyyy-MM-dd ,只有年月日,没有时分秒
2) 详细的数据类型(了解即可)
类型名称 显示长度 数据库类型 JAVA类型 JDBC类型
索引(int)
VARCHAR L+N VARCHAR java.lang.String 12 CHAR N CHAR java.lang.String 1 BLOB L+N BLOB java.lang.byte[] -4 TEXT 65535 VARCHAR java.lang.String -1 INTEGER 4 INTEGER UNSIGNED java.lang.Long 4 TINYINT 3 TINYINT UNSIGNED java.lang.Integer -6 SMALLINT 5 SMALLINT UNSIGNED java.lang.Integer 5 MEDIUMINT 8 MEDIUMINT UNSIGNED java.lang.Integer 4 BIT 1 BIT java.lang.Boolean -7 BIGINT 20 BIGINT UNSIGNED java.math.BigInteger -5 FLOAT 4+8 FLOAT java.lang.Float 7 DOUBLE 22 DOUBLE java.lang.Double 8 DECIMAL 11 DECIMAL java.math.BigDecimal 3 BOOLEAN 1 同TINYINT ID 11 PK (INTEGER UNSIGNED) java.lang.Long 4 DATE 10 DATE java.sql.Date 91 TIME 8 TIME java.sql.Time 92 DATETIME 19 DATETIME java.sql.Timestamp 93 TIMESTAMP 19 TIMESTAMP java.sql.Timestamp 93 YEAR 4 YEAR java.sql.Date 91 注意:MySQL中的 char类型与 varchar类型,都对应了 Java中的字符串类型,区别在于:
char类型是固定长度的: 根据定义的字符串长度分配足够的空间。
varchar类型是可变长度的: 只使用字符串长度所需的空间
比如:保存字符串 "abc"
x char(10) 占用10个字节
y varchar(10) 占用3个字节
适用场景:
char类型适合存储 固定长度的字符串,比如 密码 ,性别一类
varchar类型适合存储 在一定范围内,有长度变化的字符串
创建表
语法格式:
CREATE TABLE 表名(
字段名称1 字段类型(长度),字段名称2 字段类型 注意 最后一列不要加逗号
);
快速创建一个表结构相同的表(复制表结构)
语法格式: create table 新表明 like 旧表名
查看表
命令
说明
show tables;
查看当前数据库中的所有表名
desc 表名;
查看数据表的结构
删除表
命令
说明
drop table 表名;
删除表(从数据库中永久删除某一张表)
drop table if exists 表名;
判断表是否存在, 存在的话就删除,不存在就不执行删除
修改表
1)修改表名
语法格式 rename table 旧表名 to 新表名
2) 修改表的字符集
语法格式 alter table 表名 character set 字符集
3)向表中添加列, 关键字 ADD
语法格式:alert table 表名 add 字段名称 字段类型
4)修改表中列的 数据类型或长度 , 关键字 MODIFY
语法格式:alter table 表名 modify 字段名称 字段类型
5)修改列名称 , 关键字 CHANGE
语法格式alter table 表名 change 旧列名 新列名 类型(长度);
6)删除列 ,关键字 DROP
语法格式alter table 表名 drop 列名;
7)查看表结构
语法格式DESC 表名
/* 需求1: 创建商品分类表 表名:category 表中字段: 分类ID :cid ,为整型 分类名称:cname,为字符串类型,指定长度20 */ --创建表 CREATE TABLE category( cid INT, cname VARCHAR(20) ); --创建相同表结构 CREATE TABLE categorylike LIKE category; 直接删除 test1 表 DROP TABLE test1; 先判断 再删除test2表 DROP TABLE IF EXISTS test2; 修改表名category为category1 RENAME TABLE category TO category1; 需求: 将category表的字符集 修改为gbk alter table category character set gbk; # 为分类表添加一个新的字段为 分类描述 cdesc varchar(20) ALTER TABLE category ADD cdesc VARCHAR(20); 需求:对分类表的描述字段进行修改,类型varchar(50) ALTER TABLE category MODIFY cdesc VARCHAR(50); 需求: 对分类表中的 desc字段进行更换, 更换为 description varchar(30) ALTER TABLE category CHANGE cdesc description VARCHAR(30); 需求: 删除分类表中description这列 ALTER TABLE category DROP description;
DML 操作表中数据
SQL中的DML 用于对表中的数据进行增删改操作
插入数据
语法格式:insert into 表名 (字段名1,字段名2...) values(字段值1,字段值2...);
代码准备,创建一个学生表:表名:student 表中字段: 学员ID, sid int 姓名, sname varchar(20) 年龄, age int 性别, sex char(1) 地址, address varchar(40) # 创建学生表 CREATE TABLE student( sid INT, sname VARCHAR(20), age INT, sex CHAR(1), address VARCHAR(40) ); #向学生表中添加数据,3种方式 方式1: 插入全部字段, 将所有字段名都写出来 INSERT INTO student (sid,sname,age,sex,address) VALUES(1,'孙悟空',20,'男','花果山'); 方式2: 插入全部字段,不写字段名 INSERT INTO student VALUES(2,'孙悟饭',10,'男','地球'); 方式3:插入指定字段的值 INSERT INTO category (cname) VALUES('白骨精');
注意:
1) 值与字段必须要对应,个数相同&数据类型相同
2)值的数据大小,必须在字段指定的长度范围内
3)varchar char date类型的值必须使用单引号,或者双引号包裹。
4)如果要插入空值,可以忽略不写,或者插入null
5) 如果插入指定字段的值,必须要上写列名
更改数据
语法格式1:不带条件的修改update 表名 set 列名 = 值,修改多个列名用逗号分隔
语法格式2:带条件的修改update 表名 set 列名 = 值 [where 条件表达式:字段名 = 值 ]
1)不带条件修改,将所有的性别改为女(慎用!!) UPDATE student SET sex = '女'; 2)带条件的修改,将sid 为3的学生,性别改为男 UPDATE student SET sex = '男' WHERE sid = 3; 3)一次修改多个列, 将sid为 2 的学员,年龄改为 20,地址改为 北京 UPDATE student SET age = 20,address = '北京' WHERE sid = 2;
删除数据
语法格式1:删除所有数据
delete from 表名
语法格式2: 指定条件 删除数据
delete from 表名 [where 字段名 = 值]
1)删除 sid 为 1 的数据 DELETE FROM student WHERE sid = 1; 2) 删除所有数据 DELETE FROM student; 或者 truncate table student;#效率高
如果要删除表中的所有数据,有两种做法
delete from 表名; 不推荐. 有多少条记录 就执行多少次删除操作. 效率低
truncate table 表名: 推荐. 先删除整张表, 然后再重新创建一张一模一样的表. 效率高
DQL 查询表中数据
准备数据
#创建员工表 表名 emp 表中字段: eid 员工id,int ename 姓名,varchar sex 性别,char salary 薪资,double hire_date 入职时间,date dept_name 部门名称,varchar #创建员工表 CREATE TABLE emp( eid INT, ename VARCHAR(20), sex CHAR(1), salary DOUBLE, hire_date DATE, dept_name VARCHAR(20) ); #添加数据 INSERT INTO emp VALUES(1,'孙悟空','男',7200,'2013-02-04','教学部'); INSERT INTO emp VALUES(2,'猪八戒','男',3600,'2010-12-02','教学部'); INSERT INTO emp VALUES(3,'唐僧','男',9000,'2008-08-08','教学部'); INSERT INTO emp VALUES(4,'白骨精','女',5000,'2015-10-07','市场部'); INSERT INTO emp VALUES(5,'蜘蛛精','女',5000,'2011-03-14','市场部'); INSERT INTO emp VALUES(6,'玉兔精','女',200,'2000-03-14','市场部'); INSERT INTO emp VALUES(7,'林黛玉','女',10000,'2019-10-07','财务部'); INSERT INTO emp VALUES(8,'黄蓉','女',3500,'2011-09-14','财务部'); INSERT INTO emp VALUES(9,'吴承恩','男',20000,'2000-03-14',NULL); INSERT INTO emp VALUES(10,'孙悟饭','男', 10,'2020-03-14','财务部');INSERT INTO emp VALUES(11,'兔八哥','女', 300,'2010-03-14','财务部');
简单查询
查询不会对数据库中的数据进行修改.只是一种显示数据的方式 SELECT
语法格式select 列名 from 表名
别名查询,使用关键字 as
使用去重关键字 distinct
运算查询 (查询结果参与运算)
需求1: 查询emp中的 所有数据SELECT * FROM emp; -- 使用 * 表示所有列 需求2: 查询emp表中的所有记录,仅显示id和name字段 SELECT eid,ename FROM emp; 需求3: 将所有的员工信息查询出来,并将列名改为中文 # 使用 AS关键字,为列起别名 SELECT eid AS '编号', ename AS '姓名' , sex AS '性别', salary AS '薪资', hire_date '入职时间', -- AS 可以省略 dept_name '部门名称' FROM emp; 需求4:查询一共有几个部门 使用去重关键字 distinct -- 使用distinct 关键字,去掉重复部门信息 SELECT DISTINCT dept_name FROM emp; 需求5: 将所有员工的工资 +1000 元进行显示 SELECT ename , salary + 1000 FROM emp;
条件查询
语法格式 select 列名 from 表名 where 条件表达式
* 先取出表中的每条数据,满足条件的数据就返回,不满足的就过滤掉
运算符
1) 比较运算符
运算符
说明
> < <= >= = <> !=
大于、小于、大于(小于)等于、不等于
BETWEEN ...AND...
显示在某一区间的值
例如: 2000-10000之间: Between 2000 and 10000
IN(集合)
集合表示多个值,使用逗号分隔,例如: name in (悟空,八戒)
in中的每个数据都会作为一次条件,只要满足条件就会显示
LIKE '%张%'
模糊查询
IS NULL
查询某一列为NULL的值, 注: 不能写 = NULL
2)逻辑运算符
运算符
说明
And &&
多个条件同时成立
Or ||
多个条件任一成立
Not
不成立,取反。
模糊查询 通配符
通配符 说明 %% 表示匹配任意多个字符串, _ 表示匹配 一个字符 # 查询员工姓名为黄蓉的员工信息 SELECT * FROM emp WHERE ename = '黄蓉'; # 查询薪水价格为5000的员工信息 SELECT * FROM emp WHERE salary = 5000; # 查询薪水价格不是5000的所有员工信息 SELECT * FROM emp WHERE salary != 5000; or SELECT * FROM emp WHERE salary <> 5000; # 查询薪水价格大于6000元的所有员工信息 SELECT * FROM emp WHERE salary > 6000; # 查询薪水价格在5000到10000之间所有员工信息 SELECT * FROM emp WHERE salary BETWEEN 5000 AND 10000; # 查询薪水价格是3600或7200或者20000的所有员工信息 -- 方式1: or SELECT * FROM emp WHERE salary = 3600 OR salary = 7200 OR salary = 20000; -- 方式2: in() 匹配括号中指定的参数 SELECT * FROM emp WHERE salary IN(3600,7200,20000); # 查询含有'精'字的所有员工信息 SELECT * FROM emp WHERE ename LIKE '%精%'; # 查询以'孙'开头的所有员工信息 SELECT * FROM emp WHERE ename LIKE '孙%'; # 查询第二个字为'兔'的所有员工信息 SELECT * FROM emp WHERE ename LIKE '_兔%'; # 查询没有部门的员工信息 SELECT * FROM emp WHERE dept_name IS NULL; -- SELECT * FROM emp WHERE dept_name = NULL; #错误写法 # 查询有部门的员工信息 SELECT * FROM emp WHERE dept_name IS NOT NULL;
MySQL单表&约束&事务
DQL操作单表
排序
通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示效果,不会影响真实数据)
ASC 表示升序排序(默认)
DESC 表示降序排序
语法结构SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]
排序方式
1) 单列排序 只按照某一个字段进行排序, 就是单列排序
2) 组合排序 同时对多个字段进行排序, 如果第一个字段相同 就按照第二个字段进行排序,以此类推
使用 salary 字段,对emp 表数据进行排序 (升序/降序) -- 默认升序排序 ASC SELECT * FROM emp ORDER BY salary; -- 降序排序 SELECT * FROM emp ORDER BY salary DESC; 在薪水排序的基础上,再使用id进行排序, 如果薪水相同就以id 做降序排序 SELECT * FROM emp ORDER BY salary DESC, eid DESC;
聚合函数
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,它是对某一列的值进行计算,然后返回一个单一的值(另外聚合函数会忽略null空值。);
语法结构SELECT 聚合函数(字段名) FROM 表名;
5个聚合函数
聚合函数
作用
count(字段)
统计指定列不为NULL的记录行数
sum(字段)
计算指定列的数值和
max(字段)
计算指定列的最大值
min(字段)
计算指定列的最小值
avg(字段)
计算指定列的平均值
#1 查询员工的总数 -- 统计表中的记录条数 使用 count() SELECT COUNT(eid) FROM emp; -- 使用某一个字段 SELECT COUNT(*) FROM emp; -- 使用 * SELECT COUNT(1) FROM emp; -- 使用 1,与 * 效果一样 下面这条SQL 得到的总条数不准确,因为count函数忽略了空值 所以使用时注意不要使用带有null的列进行统计 SELECT COUNT(dept_name) FROM emp; #2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值 -- sum函数求和, max函数求最大, min函数求最小, avg函数求平均值 SELECT SUM(salary) AS '总薪水', MAX(salary) AS '最高薪水', MIN(salary) AS '最低薪水', AVG(salary) AS '平均薪水' FROM emp; #3 查询薪水大于4000员工的个数 SELECT COUNT(*) FROM emp WHERE salary > 4000; #4 查询部门为'教学部'的所有员工的个数 SELECT COUNT(*) FROM emp WHERE dept_name = '教学部'; #5 查询部门为'市场部'所有员工的平均薪水 SELECT AVG(salary) AS '市场部平均薪资' FROM emp WHERE dept_name = '市场部';
分组
分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组
语法格式 SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];
注意:
分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作. * 查询其他字段没有意义
#1.查询所有部门信息#2.查询每个部门的平均薪资#3.查询每个部门的平均薪资, 部门名称不能为null
#1. 查询有几个部门 SELECT dept_name AS '部门名称' FROM emp GROUP BY dept_name; #2.查询每个部门的平均薪资 SELECT dept_name AS '部门名称', AVG(salary) AS '平均薪资' FROM emp GROUP BY dept_name; #3.查询每个部门的平均薪资, 部门名称不能为null SELECT dept_name AS '部门名称', AVG(salary) AS '平均薪资' FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name;
# 查询平均薪资大于6000的部门.
分析:
需要在分组后,对数据进行过滤,使用 关键字 hiving
分组操作中的having子语句,是用于在分组后对数据进行过滤的,作用类似于where条件。
# 查询平均薪资大于6000的部门 -- 需要在分组后再次进行过滤,使用 having
SELECT dept_name , AVG(salary) FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name HAVING AVG(salary) > 6000 ;
where 与 having的区别
过滤方式
特点
where
where 进行分组前的过滤
where 后面不能写 聚合函数
having
having 是分组后的过滤
having 后面可以写 聚合函数
limit关键字
limit 关键字的作用: limit是限制的意思,用于 限制返回的查询结果的行数 (可以通过limit指定查询多少行数据) limit 语法是 MySql的方言,用来完成分页
语法结构 SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;
参数说明
offset 起始行数, 从0开始记数, 如果省略 则默认为 0.
length 返回的行数
#查询emp表中的前 5条数据 #查询emp表中 从第4条开始,查询6条
# 查询emp表中的前 5条数据 -- 参数1 起始值,默认是0 , 参数2 要查询的条数 SELECT * FROM emp LIMIT 5; SELECT * FROM emp LIMIT 0 , 5; # 查询emp表中 从第4条开始,查询6条 -- 起始值默认是从0开始的. SELECT * FROM emp LIMIT 3 , 6;
# 分页操作 每页显示3条数据
分页公式 起始索引 = (当前页 - 1) * 每页条数
-- 分页操作 每页显示3条数据 SELECT * FROM emp LIMIT 0,3; -- 第1页 SELECT * FROM emp LIMIT 3,3; -- 第2页 2-1=1 1*3=3 SELECT * FROM emp LIMIT 6,3; -- 第三页
SQL约束
约束的作用:
对表中的数据进行进一步的限制,从而保证数据的正确性、有效性、完整性.
违反约束的不正确数据,将无法插入到表中
常见的约束
约束名
约束关键字
主键
primary key
唯一
unique
非空
not null
外键
foreign key
主键约束
特点
不可重复 唯一 非空
作用
用来表示数据库中的每一条记录
添加主键约束
语法格式 字段名 字段类型 primary key
#创建一个带主键的表
CREATE TABLE emp2( -- 设置主键 唯一 非空 eid INT PRIMARY KEY, ename VARCHAR(20), sex CHAR(1) ); -- 方式2 创建一个带主键的表 CREATE TABLE emp2( eid INT , ename VARCHAR(20), sex CHAR(1), -- 指定主键为 eid字段 PRIMARY KEY(eid) ); 方式3 创建一个带主键的表#创建的时候不指定主键,然后通过 DDL语句进行设置 CREATE TABLE emp2( eid INT , ename VARCHAR(20), sex CHAR(1) ) 创建的时候不指定主键,然后通过 DDL语句进行设置 ALTER TABLE emp2 ADD PRIMARY KEY(eid);
哪些字段可以作为主键 ?
通常针对业务去设计主键,每张表都设计一个主键id
主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复就好,比如 身份证就可以作为主键.
删除主键约束
语法格式ALTER TABLE 表明 DROP PRIMARY KEY;
主键的自增
注: 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值.
关键字:
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
#创建主键自增的表
创建主键自增的表 CREATE TABLE emp2( 关键字 AUTO_INCREMENT,主键类型必须是整数类型 eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), sex CHAR(1) );
修改主键自增的起始值
默认地 AUTO_INCREMENT 的开始值是 1,如果希望修改起始值,请使用下面的方式,在括号后添加
创建主键自增的表,自定义自增其实值
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20),
sex CHAR(1) )AUTO_INCREMENT=100;
DELETE和TRUNCATE对自增长的影响
删除表中所有数据有两种方式
清空表数据的方式
特点
DELETE
只是删除表中所有数据,对自增没有影响
TRUNCATE
truncate 是将整个表删除掉,然后创建一个新的表
自增的主键,重新从 1开始
delete清空表再后添加数据
truncate清空表再后添加数据
非空约束
非空约束的特点: 某一列不予许为空
语法格式 字段名 字段类型 not null
# 为ename字段添加非空约束
CREATE TABLE emp2( eid INT PRIMARY KEY AUTO_INCREMENT, -- 添加非空约束, ename字段不能为空 ename VARCHAR(20) NOT NULL, sex CHAR(1) );
唯一约束
唯一约束的特点: 表中的某一列的值不能重复( 对null不做唯一的判断 )
语法格式字段名 字段值 unique
#创建emp3表 为ename 字段添加唯一约束
CREATE TABLE emp3( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20) UNIQUE, sex CHAR(1) );
主键约束与唯一约束的区别:
- 主键约束 唯一且不能够为空
- 唯一约束,唯一 但是可以为空
- 一个表中只能有一个主键 , 但是可以有多个唯一约束
外键约束
FOREIGN KEY 表示外键约束
默认值
默认值约束 用来指定某列的默认值
语法格式 字段名 字段类型 DEFAULT 默认值
-- 创建带有默认值的表 CREATE TABLE emp4( eid INT PRIMARY KEY AUTO_INCREMENT, -- 为ename 字段添加默认值 ename VARCHAR(20) DEFAULT '奥利给', sex CHAR(1) ); 2) 测试 添加数据使用默认值 -- 添加数据 使用默认值 INSERT INTO emp4(ename,sex) VALUES(DEFAULT,'男'); INSERT INTO emp4(sex) VALUES('女'); -- 不使用默认值 INSERT INTO emp4(ename,sex) VALUES('艳秋','女');
数据库事务
什么是事务
事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败
比如: 银行的转账业务,张三给李四转账500元 , 至少要操作两次数据库, 张三 -500, 李四 + 500,这中间任何一步出现问题,整个操作就必须全部回滚, 这样才能保证用户和银行都没有损失.
回滚
即在事务运行的过程中发生了某种故障,事务不能继续执行,系统将事务中对数据库的所有已完成的操作全部撤销,滚回到事务开始时的状态。(在提交之前执行)
模拟转账操作
1) 创建 账户表 -- 创建账户表 CREATE TABLE account( -- 主键 id INT PRIMARY KEY AUTO_INCREMENT, -- 姓名 NAME VARCHAR(10), -- 余额 money DOUBLE ); -- 添加两个用户 INSERT INTO account (NAME, money) VALUES ('tom', 1000), ('jack', 1000); 2) 模拟tom 给 jack 转 500 元钱,一个转账的业务操作最少要执行下面的 2 条语句: -- tom账户 -500元 UPDATE account SET money = money - 500 WHERE NAME = 'tom'; -- jack账户 + 500元 UPDATE account SET money = money + 500 WHERE NAME = 'jack';
注:
假设当tom 账号上 -500 元,服务器崩溃了。jack 的账号并没有+500 元,数据就出现问题了。
我们要保证整个事务执行的完整性,要么都成功, 要么都失败. 这个时候我们就要学习如何操作事务.
MySQL事务操作
MYSQL 中可以有两种方式进行事务的操作:
- 手动提交事务
- 自动提交事务
手动提交事务
语法格式
功能
语句
开启事务
start transaction; 或者 BEGIN;
提交事务
commit;
回滚事务
rollback;
- START TRANSACTION 这个语句显式地标记一个事务的起始点。
- COMMIT 表示提交事务,即提交事务的所有操作,具体地说,就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中,事务正常结束。
- ROLLBACK 表示撤销事务,即在事务运行的过程中发生了某种故障,事务不能继续执行,系统将事务中对数据库的所有已完成的操作全部撤销,回滚到事务开始时的状态
手动提交事务流程
执行成功的情况: 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
执行失败的情况: 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚
#事务实现转账
#开启事务 start transaction; #tom账户 -500 update account set money = money - 500 where name = 'tom' #jack账户 +500 update account set money = money + 500 where name = 'jack'
#此时查看表,发现数据并没有改变
#在控制台执行 commit 提交事务
commit;
#再次查看, 发现数据在事务提交之后,发生改变
事务回滚演示
如果事务中,有某条sql语句执行时报错了,我们没有手动的commit,那整个事务会自动回滚
1) 命令行 开启事务 start transaction; 2) 插入两条数据 INSERT INTO account VALUES(NULL,'张百万',3000); INSERT INTO account VALUES(NULL,'有财',3500); 3) 不去提交事务 直接关闭窗口,发生回滚操作,数据没有改变
注:如果事务中 SQL 语句没有问题,commit 提交事务,会对数据库数据的数据进行改变。
如果事务中 SQL 语句有问题,rollback 回滚事务,会回退到开启事务时的状态。
自动提交事务
MySQL 默认每一条 DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,语句执行完毕 自动提交事务,MySQL 默认开启自动提交事务(默认在命令行执行一句即一个事务)
取消自动提交
MySQL默认是自动提交事务,可设置为手动提交.
1)查看autocommit状态。
状态说明:
on :自动提交
off : 手动提交
SHOW VARIABLES LIKE 'autocommit';
2) 把 autocommit 改成 off;
SET @@autocommit=off;
3) 再次进行增删改操作,需要提交之后才生效
将jack 账户金额 -500元
#选择数据库 use db2; #修改数据 update account set money = money - 500 where name = 'jack'; -- 手动提交 commit;
事务的四大特性 ACID
特性
含义
原子性
每个事务都是一个整体,不可再拆分,事务中所有的 SQL 语句要么都执行成功, 要么都失拜。
一致性
事务在执行前数据库的状态与执行后数据库的状态保持一致。如:转账前2个人的 总金额是 2000,转账后 2 个人总金额也是 2000.
隔离性
事务与事务之间不应该相互影响,执行时保持隔离的状态.
持久性
一旦事务执行成功,对数据库的修改是持久的。就算关机,数据也是要保存下来的.
MySQL 事务隔离级别(了解)
数据并发访问
一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库. 数据库的相同数据可能被多个事务同时访问,如果不采取隔离措施,就会导致各种问题, 破坏数据的完整性
并发访问会产生的问题
事务在操作时的理想状态: 所有的事务之间保持隔离,互不影响。因为并发操作,多个用户同时访问同一个 数据。可能引发并发访问的问题
并发访问的问题 说明
脏读 一个事务读取到了另一个事务中尚未提交的数据 不可重复读 一个事务中两次读取的数据内容不一致, 要求的是在一个事务中多次读取时数据是一致的. 这是进行 update 操作时引发的问题 幻读 一个事务中,某一次的 select 操作得到的结果所表征的数据状态, 无法支撑后续的业务操作. 查询得到的数据状态不准确,导致幻读 四种隔离级别
通过设置隔离级别,可以防止上面的三种并发问题. MySQL数据库有四种隔离级别 上面的级别最低,下面的级别最高。
✔ 会出现问题 ✘ 不会出现问题
级别
名字
隔离级别
脏读
不可重复
读
幻读
数据库的默认隔离级别
1
读未提交
read
uncommitted
✔
✔
✔
2
读已提交
read committed
✘
✔
✔
Oracle和SQLServer
3
可重复读
repeatable read
✘
✘
✔
MySql
4
串行化
serializable
✘
✘
✘
隔离级别相关命令
1) 查看隔离级别
select @@tx_isolation;
如果报错: Unknown system variable 'tx_isolation',
解决办法:
由于mysql数据库的更新,在旧版本中tx_isolation是作为transaction_isolation的别名被应用的,新版本已经弃用了,所以输入会显示未知变量,把tx_isolation换成transaction_isolation,就可以显示结果select @@transaction_isolation
2) 设置事务隔离级别,需要退出 MySQL 再重新登录才能看到隔离级别的变化
set global transaction isolation level 级别名称;
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化
例如: 修改隔离级别为 读未提交
set global transaction isolation level read uncommitted;
隔离性问题演示
脏读演示
脏读: 一个事务读取到了另一个事务中尚未提交的数据,即读到了尚未commit的数据操作
脏读问题
脏读非常危险的,比如张三向李四购买商品,张三开启事务,向李四账号转入 500 块,然后打电话给李四说钱 已经转了。
李四一查询钱到账了,发货给张三。
张三收到货后回滚事务,李四的再查看钱没了。
解决方案
将全局的隔离级别进行提升为: read committed
set global transaction isolation level read committed;
不可重复读演示
不可重复读: 同一个事务中,进行查询操作,但是每次读取的数据内容是不一样的
不可重复读问题
比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客 户,
结果在一个事务中针对不同的输出目的地进行的两次查询不一致,
导致文件和屏幕中的结果不一致,银行工作 人员就不知道以哪个为准了
解决不可重复读问题
将全局的隔离级别进行提升为: repeatable read
set global transaction isolation level repeatable read;
同一个事务中为了保证多次查询数据一致,必须使用 repeatable read 隔离级别
幻读演示
select 某记录是否存在,不存在,准备插入此记录,但执行 insert 时发现此记录已存在,无法插入,此时就发生了幻读。
幻读问题
将事务隔离级别设置到最高 SERIALIZABLE ,以挡住幻读的发生
如果一个事务,使用了SERIALIZABLE——可串行化隔离级别时,在这个事务没有被提交之前 , 其他的线程,只能等到当前操作完成之后,才能进行操作,这样会非常耗时,而且,影响数据库的性能,数据库不会使用这种隔离级别
set global transaction isolation level SERIALIZABLE;
总结:
serializable 串行化可以彻底解决幻读,但是 事务只能排队执行,严重影响效率,数据库不会使用这种隔离级别
MySQL多表&外键&数据库设计
多表多表简述
实际开发中,一个项目通常需要很多张表才能完成。
例如一个商城项目的数据库,需要有很多张表:用户表、分类表、商品表、订单表....
单表的缺点数据准备
CREATE DATABASE db3 CHARACTER SET utf8; -- 创建emp表 主键自增 CREATE TABLE emp( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), age INT , dep_name VARCHAR(20), dep_location VARCHAR(20) ); -- 添加数据 INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('张百万', 20, '研发 部', '广州'); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('赵四', 21, '研发 部', '广州'); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('广坤', 20, '研发 部', '广州'); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('小斌', 20, '销售 部', '深圳'); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('艳秋', 22, '销售 部', '深圳'); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('大玲子', 18, '销售 部', '深圳');
单表的问题
冗余, 同一个字段中出现大量的重复数据
解决方案
设计为两张表
多表方式设计
department 部门表 : id, dep_name, dep_location
employee 员工表: eid, ename, age, dep_id
-- 创建部门表 -- 一方,主表 CREATE TABLE department( id INT PRIMARY KEY AUTO_INCREMENT, dep_name VARCHAR(30), dep_location VARCHAR(30) ); -- 创建员工表 -- 多方 ,从表 CREATE TABLE employee( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), age INT, dept_id INT ); -- 添加2个部门 INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳'); SELECT * FROM department; -- 添加员工,dep_id表示员工所在的部门 INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1); INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2); INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); SELECT * FROM employee;
表关系分析
部门表与员工表的关系
1) 员工表中有一个字段dept_id 与部门表中的主键对应,员工表的这个字段就叫做 外键
2) 拥有外键的员工表 被称为 从表 , 与外键对应的主键所在的表叫做 主表
多表设计上的问题
当我们在 员工表的 dept_id 里面输入不存在的部门id ,数据依然可以添加 显然这是不合理的. 所以需要用到外键约束
外键约束
什么是外键
外键指的是在 从表 中 与 主表 的主键对应的那个字段,比如员工表的 dept_id,就是外键
使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性
注意:主键是为保证数据库的实体完整性,取值非空唯一;外键是保证数据库的参照完整性,值可以为空
创建外键约束
语法格式:
新建表时添加外键
[CONSTRAINT] [外键约束名称,自定义名称忽略时,会自动创建] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
已有表添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);
-- 先删除 employee表 DROP TABLE employee; -- 重新创建 employee表,添加外键约束 CREATE TABLE employee( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), age INT, dept_id INT, -- 添加外键约束 CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id) );
添加外键约束,就会产生强制性的外键数据检查, 从而保证了数据的完整性和一致性
-- 正常添加数据 (从表外键 对应主表主键) INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1); INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2); INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); -- 插入一条有问题的数据 (部门id不存在) -- Cannot add or update a child row: a foreign key constraint fails INSERT INTO employee (ename, age, dept_id) VALUES ('错误', 18, 3);
删除外键约束
语法格式 alter table 从表 drop foreign key 外键约束名称
#删除employee 表中的外键约束,完成后在添加外键约束
-- 删除employee 表中的外键约束,外键约束名 emp_dept_fk ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk; --ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名); -- 可以省略外键名称, 系统会自动生成一个 ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department (id);
外键约束的注意事项
1) 从表外键类型必须与主表主键类型一致 否则创建失败.
2) 添加数据时, 应该先添加主表中的数据.
3) 删除数据时,应该先删除从表中的数据.
级联删除操作(了解)
- 如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作
级联删除 ON DELETE CASCADE
# employee表,重新创建,添加级联删除
-- 重新创建添加级联操作 CREATE TABLE employee( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), age INT, dept_id INT, CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id) -- 添加级联删除 ON DELETE CASCADE ); -- 添加数据 INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1); INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2); INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); -- 删除部门编号为2 的记录 DELETE FROM department WHERE id = 2; #从表.员工表中 外键值是2的记录,也被删除了
多表关系设计
实际开发中,一个项目通常需要很多张表才能完成。例如:一个商城项目就需要分类表(category)、 商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系,接下来我们一起学习一下多表关系设计方面的知识
表与表之间的三种关系一对多关系: 最常见的关系, 学生对班级,员工对部门 多对多关系 : 学生与课程 , 用户与角色一对一关系: 使用较少,因为一对一关系可以合成为一张表 一对多关系(常见)
一对多关系(1:n)
例如:班级和学生,部门和员工,客户和订单,分类和商品
一对多建表原则
在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
多对多关系(常见)
多对多(m:n)
例如:老师和学生,学生和课程,用户和角色
多对多关系建表原则
需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的 主键。
一对一关系(了解)
一对一(1:1)
在实际的开发中应用不多.因为一对一可以创建成一张表。
一对一建表原则
外键唯一 主表的主键和从表的外键(唯一),形成主外键关系,外键唯一 UNIQUE
设计 省&市表
1) 分析: 省和市之间的关系是 一对多关系,一个省包含多个市
#创建省表 (主表,注意: 一定要添加主键约束) CREATE TABLE province( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), description VARCHAR(20) ); #创建市表 (从表,注意: 外键类型一定要与主表主键一致) CREATE TABLE city( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), description VARCHAR(20), pid INT, -- 添加外键约束 CONSTRAINT pro_city_fk FOREIGN KEY (pid) REFERENCES province(id) );
设计 演员与角色表
1) 分析: 演员与角色 是多对多关系, 一个演员可以饰演多个角色, 一个角色同样可以被不同的演员扮演
#创建演员表 CREATE TABLE actor( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); #创建角色表 CREATE TABLE role( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); #创建中间表 CREATE TABLE actor_role( -- 中间表自己的主键 id INT PRIMARY KEY AUTO_INCREMENT, -- 指向actor 表的外键 aid INT, -- 指向role 表的外键 rid INT ); -- 为中间表的aid字段,添加外键约束 指向演员表的主键 ALTER TABLE actor_role ADD FOREIGN KEY(aid) REFERENCES actor(id); -- 为中间表的rid字段, 添加外键约束 指向角色表的主键 ALTER TABLE actor_role ADD FOREIGN KEY(rid) REFERENCES role(id);
多表查询
什么是多表查询
DQL: 查询多张表,获取到需要的数据
比如 我们要查询家电分类下 都有哪些商品,那么我们就需要查询分类与商品这两张表
数据准备 (创建分类表和商品表)
-- 创建 db3_2 数据库,指定编码 CREATE DATABASE db3_2 CHARACTER SET utf8; #分类表 (一方 主表) CREATE TABLE category ( cid VARCHAR(32) PRIMARY KEY , cname VARCHAR(50) ); #商品表 (多方 从表) CREATE TABLE products( pid VARCHAR(32) PRIMARY KEY , pname VARCHAR(50), price INT, flag VARCHAR(2), #是否上架标记为:1表示上架、0表示下架 category_id VARCHAR(32), -- 添加外键约束 FOREIGN KEY (category_id) REFERENCES category (cid) ); #分类数据 INSERT INTO category(cid,cname) VALUES('c001','家电'); INSERT INTO category(cid,cname) VALUES('c002','鞋服'); INSERT INTO category(cid,cname) VALUES('c003','化妆品'); INSERT INTO category(cid,cname) VALUES('c004','汽车'); #商品数据 INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','小米电视 机',5000,'1','c001'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','格力空 调',3000,'1','c001'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','美的冰 箱',4500,'1','c001'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','篮球 鞋',800,'1','c002'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','运动 裤',200,'1','c002'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','T 恤',300,'1','c002'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','冲锋 衣',2000,'1','c002'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','神仙 水',800,'1','c003'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','大 宝',200,'1','c003');
笛卡尔积(不会用到的)
交叉连接查询,因为会产生笛卡尔积,所以 基本不会使用
语法格式:SELECT 字段名 FROM 表1, 表2;
多表查询的分类
内连接查询
内连接的特点:
通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示
比如通过: 从表的外键 = 主表的主键 方式去匹配
隐式内连接
from子句 后面直接写 多个表名 使用where指定连接条件的 这种连接方式是 隐式内连接. 使用where条件过滤无用的数据
语法格式:SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;
#查询 格力空调是属于哪一分类下的商品SELECT p.`pname`,c.`cname` FROM products p , category c WHERE p.`category_id` = c.`cid` AND p.`pid` = 'p002';
显式内连接
使用 inner join ...on 这种方式, 就是显式内连接
语法格式:SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件;
-- inner 可以省略
# 查询鞋服分类下,价格大于500的商品名称和价格
-- 我们需要确定的几件事 -- 1.查询几张表 products & category -- 2.表的连接条件 从表.外键 = 主表的主键 -- 3.查询的条件 cname = '鞋服' and price > 500 -- 4.要查询的字段 pname price SELECT p.pname, p.price FROM products p INNER JOIN category c ON p.category_id = c.cid WHERE p.price > 500 AND cname = '鞋服';
外连接查询
左外连接
左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略
语法格式: SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
左外连接的特点
以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
如果匹配不到, 左表中的数据正常展示, 右边的展示为null.
-- 左外连接查询 SELECT * FROM category c LEFT JOIN products p ON c.`cid`= p.`category_id`;
右外连接
右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略
语法格式:SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
右外连接的特点
以右表为基准,匹配左边表中的数据,如果能匹配到,展示匹配到的数据
如果匹配不到,右表中的数据正常展示, 左边展示为null
-- 右外连接查询 SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;
各种连接方式的总结
- 内连接: inner join , 只获取两张表中 交集部分的数据.
- 左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分
- 右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分
子查询 (SubQuery)子查询概念
一条select 查询语句的结果, 作为另一条 select 语句的一部分
子查询的特点
子查询必须放在小括号中
子查询一般作为父查询的查询条件使用
子查询常见分类
where型 子查询: 将子查询的结果, 作为父查询的比较条件
from型 子查询 : 将子查询的结果, 作为 一张表,提供给父层查询使用
exists型 子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
子查询的结果作为查询条件(where型)
语法格式:SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
#查询化妆品分类下的 商品名称 商品价格 -- 先查出化妆品分类的 id SELECT cid FROM category WHERE cname = '化妆品'; -- 根据分类id ,去商品表中查询对应的商品信息 SELECT p.`pname`, p.`price` FROM products p WHERE p.`category_id` = (SELECT cid FROM category WHERE cname = '化妆品');
子查询的结果作为一张表(from型)
语法格式:SELECT 查询字段 FROM (子查询)表别名 WHERE 条件;
查询商品中,价格大于500的商品信息,包括 商品名称 商品价格 商品所属分类名称
-- 1. 先查询分类表的数据 SELECT * FROM category; -- 2.将上面的查询语句 作为一张表使用 SELECT p.`pname`, p.`price`, c.cname FROM products p -- 子查询作为一张表使用时 要起别名 才能访问表中字段 INNER JOIN (SELECT * FROM category) c ON p.`category_id` = c.cid WHERE p.`price` > 500;
注意: 当子查询作为一张表的时候,需要起别名,否则无法访问表中的字段。
子查询结果是单列多行(exists型)
子查询的结果类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
语法格式:SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
# 查询家电类 与 鞋服类下面的全部商品信息 -- 先查询出家电与鞋服类的 分类ID SELECT cid FROM category WHERE cname IN ('家电','鞋服'); -- 根据cid 查询分类下的商品信息 SELECT * FROM products WHERE category_id IN (SELECT cid FROM category WHERE cname IN ('家电','鞋服'));
子查询总结
1. 子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用.
2. 子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名).
数据库设计
数据库三范式(空间最省)
概念: 三范式就是设计数据库的规则.
- 为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库,必须满足一定的范式满足最低要求的范式是第一范式(1NF)。
- 在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF) , 其余范式以此类推。一般说来,数据库只需满足第三范式(3NF)就行了
第一范式 1NF
概念:
- 原子性, 做到列不可拆分
- 第一范式是最基本的范式。数据库表里面字段都是单一属性的,不可再分, 如果数据表中每个字段都是不可再分的最小数据单元,则满足第一范式。
示例:
地址信息表中, contry这一列,还可以继续拆分,不符合第一范式
第二范式 2NF
概念:
- 在第一范式的基础上更进一步,目标是确保表中的每列都和主键相关。
- 一张表只能描述一件事.
示例:
学员信息表中其实在描述两个事物 , 一个是学员的信息,一个是课程信息
如果放在一张表中,会导致数据的冗余,如果删除学员信息, 成绩的信息也被删除了
第三范式 3NF
概念:
- 消除传递依赖
- 表的信息,如果能够被推导出来,就不应该单独的设计一个字段来存放
示例
通过number 与 price字段就可以计算出总金额,不要在表中再做记录(空间最省)
数据库反三范式
概念
- 反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
- 浪费存储空间,节省查询时间 (以空间换时间)
什么是冗余字段 ?
设计数据库时,某一个字段属于一张表,但它同时出现在另一个或多个表,且完全等同于它在其本来所属表的意义表示,那么这个字段就是一个冗余字段
反三范式示例
两张表,用户表、订单表,用户表中有字段name,而订单表中也存在字段name。
使用场景
- 当需要查询“订单表”所有数据并且只需要“用户表”的name字段时, 没有冗余字段 就需要去join
- 连接用户表,假设表中数据量非常的大, 那么会这次连接查询就会非常大的消耗系统的性能.
- 这时候冗余的字段就可以派上用场了, 有冗余字段我们查一张表就可以了.
总结
创建一个关系型数据库设计,我们有两种选择
1,尽量遵循范式理论的规约,尽可能少的冗余字段,让数据库设计看起来精致、优雅、让人心醉。
2,合理的加入冗余字段这个润滑剂,减少join,让数据库执行性能更高更快。
MySQL索引&视图&存储过程
MySQL 索引什么是索引
- 在数据库表中,对字段建立索引可以大大提高查询速度。通过善用这些索引,可以令MySQL的查询和运行更加高效。
- 如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。
- 拿汉语字典的目录页(索引)打比方,我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到需要的字
常见索引分类
索引名称说明 主键索引 (primary key) 主键是一种唯一性索引 , 每个表只能有一个主键 , 用于标识数据表中的每一条记录唯一索引 (unique) 唯一索引指的是 索引列的所有值都只能出现一次, 必须唯一. 普通索引 (index) 最常见的索引,作用就是 加快对数据的访问速度 MySql将一个表的索引都保存在同一个索引文件中, 如果对中数据进行增删改操作,MySql都会自动的更新索引.
主键索引 (PRIMARY KEY)
特点: 主键是一种唯一性索引,每个表只能有一个主键,用于标识数据表中的某一条记录。
一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL。
语法格式
创建表的时候直接添加主键索引 (最常用)
CREATE TABLE 表名(
-- 添加主键 (主键是唯一性索引,不能为null,不能重复,)
字段名 类型 PRIMARY KEY,
);
修改表结构 添加主键索引
ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )
唯一索引(UNIQUE)
特点: 索引列的所有值都只能出现一次, 必须唯一.
唯一索引可以保证数据记录的唯一性。事实上,在许多场合,人们创建唯一索引的目的往往 不是为了提高访问速度,而只是为了避免数据出现重复。语法格式
创建表的时候直接添加唯一索引CREATE TABLE 表名 (列名 类型( 长度 ),-- 添加唯一索引UNIQUE [ 索引名称 ] ( 列名 ));使用create语句创建: 在已有的表上创建索引create unique index 索引名 on 表名(列名(长度))修改表结构添加索引ALTER TABLE 表名 ADD UNIQUE ( 列名 )普通索引 (INDEX)
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件(WHERE column=)或排序条件(ORDERBY column)中的数据列创建索引。
语法格式
使用create index 语句创建: 在已有的表上创建索引
create index 索引名 on 表名(列名[长度])
修改表结构添加索引
ALTER TABLE 表名 ADD INDEX 索引名 (列名)
删除索引
由于索引会占用一定的磁盘空间,因此,为了避免影响数据库的性能,应该及时删除不再使用的索引
语法格式
ALTER TABLE table_name DROP INDEX index_name;
索引的优缺点总结
添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。
索引的优点
- 1. 大大的提高查询速度
- 2. 可以显著的减少查询中分组和排序的时间。
索引的缺点
- 1. 创建索引和维护索引需要时间,而且数据量越大时间越长
- 2. 当对表中的数据进行增加,修改,删除的时候,索引也要同时进行维护,降低了数据的维护速度
MySQL 视图什么是视图
- 1. 视图是一种虚拟表。
- 2. 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
- 3. 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
- 4. 视图向用户提供基表数据的另一种表现形式
视图的作用
- 权限控制时可以使用 :比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的作用
- 简化复杂的多表查询:
视图 本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)
视图主要就是为了简化多表的查询
视图的使用
创建视图
语法格式
create view 视图名 [column_list] as select语句; view: 表示视图 column_list: 可选参数,表示属性清单,指定视图中各个属性的名称,默认情况下,与SELECT语句中查询 的属性相同 as : 表示视图要执行的操作 select语句: 向视图提供数据内容
创建一个视图
#1. 先编写查询语句 #查询所有商品 和 商品的对应分类信息 SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`; #2.基于上面的查询语句,创建一张视图 CREATE VIEW products_category_view AS SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;
#查询鞋服分类下最贵的商品的全部信息
#通过连表查询 #1.先求出鞋服分类下的最高商品价格 SELECT MAX(price) AS maxPrice FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid` WHERE c.`cname` = '鞋服' #2.将上面的查询 作为条件使用 SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid` WHERE c.`cname` = '鞋服' AND p.`price` = (SELECT MAX(price) AS maxPrice FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid` WHERE c.`cname` = '鞋服'); #通过视图查询 SELECT * FROM products_category_view pcv WHERE pcv.`cname` = '鞋服' AND pcv.`price` = (SELECT MAX(price) FROM products_category_view WHERE cname = '鞋服')
视图与表的区别
- 视图是建立在表的基础上,表存储数据库中的数据,而视图只是做一个数据的展示
- 通过视图不能改变表中数据(一般情况下视图中的数据都是表中的列 经过计算得到的结果,不允许更新)
- 删除视图,表不受影响,而删除表,视图不再起作用
MySQL 存储过程 ( 了解 )什么是存储过程
- MySQL 5.0 版本开始支持存储过程。
- 存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
简单理解: 存储过程其实就是一堆 SQL 语句的合并。中间加入了一些逻辑控制。
存储过程的优缺点
优点:
存储过程一旦调试完成后,就可以稳定运行,(前提是,业务需求要相对稳定,没有变化)
存储过程减少业务系统与数据库的交互,降低耦合,数据库交互更加快捷(应用服务器,与 数据库服务器不在同一个地区)
缺点:
在互联网行业中,大量使用MySQL,MySQL的存储过程与Oracle的相比较弱,所以较少使用,并且互联网行业需求变化较快也是原因之一
尽量在简单的逻辑中使用,存储过程移植十分困难,数据库集群环境,保证各个库之间存储过程变更一致也十分困难。
阿里的代码规范里也提出了禁止使用存储过程,存储过程维护起来的确麻烦;
存储过程的创建方式
数据准备 :创建商品表 与 订单表
# 商品表 CREATE TABLE goods( gid INT, NAME VARCHAR(20), num INT -- 库存 ); #订单表 CREATE TABLE orders( oid INT, gid INT, price INT -- 订单价格 ); # 向商品表中添加3条数据 INSERT INTO goods VALUES(1,'奶茶',20); INSERT INTO goods VALUES(2,'绿茶',100); INSERT INTO goods VALUES(3,'花茶',25);
创建简单的存储过程
语法格式
DELIMITER $$ -- 声明语句结束符,可以自定义 一般使用$$ CREATE PROCEDURE 过程名称() -- 声明存储过程,可不添加参数 CREATE PROCEDURE 存储过程名称(IN 参数名 参数类型,OUT 变量名 数据类型 ) --可添加参数,IN为输入参数,OUT为输出参数 BEGIN -- 开始编写存储过程 -- 要执行的操作 --返回值,不需要返回值可忽略 SET @变量名=值; --变量赋值 SELECT @变量名; -- 返回 变量名的值 -- END $$ -- 存储过程结束
需求: 编写存储过程, 查询所有商品数据
DELIMITER $$ CREATE PROCEDURE goods_proc() BEGIN select * from goods; END $$
调用存储过程
语法格式
call 存储过程名 (--例: 调用存储过程 查询goods表所有数据 call goods_proc;)
需求: 向订单表 插入一条数据, 返回1,表示插入成功
# 创建存储过程 接收参数插入数据, 并返回受影响的行数 DELIMITER $$ CREATE PROCEDURE orders_proc(IN o_oid INT , IN o_gid INT ,IN o_price INT, OUT out_num INT) BEGIN -- 执行插入操作 INSERT INTO orders VALUES(o_oid,o_gid,o_price); -- 设置 num的值为 1 SET @out_num = 1; -- 返回 out_num的值 SELECT @out_num; END $$ # 调用存储过程插入数据,获取返回值 CALL orders_proc(1,2,30,@out_num);
MySQL 触发器 ( 了解 )什么是触发器
触发器(trigger)是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作(insert,delete, update)时就会激活它执行。——百度百科
简单理解: 当我们执行一条sql语句的时候,这条sql语句的执行会自动去触发执行其他的sql语句。
触发器创建的四个要素
- 1. 监视地点(table)
- 2. 监视事件(insert/update/delete)
- 3. 触发时间(before/after)
- 4. 触发事件(insert/update/delete)
创建触发器
语法格式
delimiter $ -- 将Mysql的结束符号从 ; 改为 $,避免执行出现错误 CREATE TRIGGER Trigger_Name -- 触发器名,在一个数据库中触发器名是唯一的 before/after(insert/update/delete) -- 触发的时机 和 监视的事件 on table_Name -- 触发器所在的表 for each row -- 固定写法 叫做行触发器, 每一行受影响,触发事件都执行 begin -- begin和end之间写触发事件 end $ -- 结束标记
需求: 在下订单的时候,对应的商品的库存量要相应的减少,卖出商品之后减少库存量
编写触发器 -- 1.修改结束标识 DELIMITER $ -- 2.创建触发器 CREATE TRIGGER t1 -- 3.指定触发的时机,和要监听的表 AFTER INSERT ON orders -- 4.行触发器 固定写法 FOR EACH ROW -- 4.触发后具体要执行的事件 BEGIN -- 订单+1 库存-1 UPDATE goods SET num = num -1 WHERE gid = 1; END$
DCL(数据控制语言)
MySql默认使用的都是 root 用户,超级管理员,拥有全部的权限。除了root用户以外,我们还可以通过DCL语言来定义一些权限较小的用户, 分配不同的权限来管理和维护数据库。
创建用户
语法格式:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
参数说明 用户名 创建的新用户,登录名称 主机名 指定该用户在哪个主机上可以登陆,本地用户可用 localhost如果想让该用户可以 从任意远程主机登陆,可以使用通配符 %密码 登录密码 1) 创建 admin1 用户,只能在 localhost 这个服务器登录 mysql 服务器,密码为 123456
CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';
创建的用户在名字为 mysql的 数据库中的 user表中
2) 创建 admin2 用户可以在任何电脑上登录 mysql 服务器,密码为 123456
CREATE USER 'admin2'@'%' IDENTIFIED BY '123456';
- % 表示 用户可以在任意电脑登录 mysql服务器.
用户授权
创建好的用户,需要进行授权
语法格式:GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
参数说明 权限 授予用户的权限,如 CREATE 、 ALTER 、 SELECT 、 INSERT 、 UPDATE 等。如果要授 予所有的权限则使用 ALLON 用来指定权限针对哪些库和表。 TO 表示将权限赋予某个用户。 1) 给 admin1 用户分配对 db4 数据库中 products 表的 操作权限:查询
GRANT SELECT ON db4.products TO 'admin1'@'localhost';
2) 给 admin2 用户分配所有权限,对所有数据库的所有表
GRANT ALL ON *.* TO 'admin2'@'%';
3) 使用admin1用户登录数据库 测试权限
4) 发现数据库列表中 只有db4, 表只有 products.
5) 执行查询操作
-- 查询account表 SELECT * FROM products;
6) 执行插入操作,发现不允许执行,没有权限
-- 向 products 表中插入数据 -- 不允许执行 INSERT INTO products VALUES('p010','小鸟伏特加',1000,1,NULL);
查看权限
语法格式 :SHOW GRANTS FOR '用户名'@'主机名';
1) 查看root用户权限
-- 查看root用户的权限 SHOW GRANTS FOR 'root'@'localhost';
- GRANT ALL PRIVILEGES 是表示所有权限
删除用户
语法格式 :DROP USER '用户名'@'主机名';
1) 删除 admin1 用户
DROP USER 'admin1'@'localhost';
查询用户
选择名为 mysql的数据库, 直接查询 user表即可
-- 查询用户 SELECT * FROM USER;
数据库备份&还原
备份的应用场景 在服务器进行数据传输、数据存储和数据交换,就有可能产生数据故障。比如发生意外停机或存储介质损坏。 这时,如果没有采取数据备份和数据恢复手段与措施,就会导致数据的丢失,造成的损失是无法弥补与估量的。
命令行备份
进入到Mysql安装目录的 bin目录下, 打开DOS命令行.
1) 语法格式 :mysqldump -u 用户名 -p 密码 数据库 > 文件路径
2) 执行备份, 备份db2中的数据 到 H盘的 db2.sql 文件中
mysqldump -uroot -p123456 db2 > H:/db2.sql
命令行恢复
source sql文件地址