MySQL基础和SQL入门
数据库的基本概念
什么是数据库
1、数据库(DataBase) 就是存储和管理数据的仓库
2、本质上是一个文件系统,还是以文件的方式,将数据保存在电脑上
为什么使用数据库
数据存储方式的比较
存储方式 | 优点 | 缺点 |
---|---|---|
内存 | 速度快 | 不能够永久保存,数据是临时状态 |
文件 | 数据是可以永久保存 | 使用IO流操作文件,不方便 |
数据库 | 1.数据可以永久保存 2.方便存储和管理数据 3.使用统一的方式操作数据库(SQL) | 占用资源,有些数据库需要付费(比如Oracle数据库) |
通过上面的比较,我们可以看出,使用数据库存储数据,用户可以非常方便对数据库中的数据进行增加,删除,修改及查询操作。
常见的数据库
数据库名 | 介绍 |
---|---|
MySql数据库 | 开源免费的数据库 因为免费开源、运作简单的特点,常作为中小型的项目的数据库首选。MySQL1996年开始运作,目前已经被Oracle公司收购了.MySQL6.x开始收费 |
Oracle数据库 | 收费的大型数据库,Oracle公司的核心产品。 安全性高 |
DB2 | IBM公司的数据库产品,收费的超大型数据库。常在银行系统中使用 |
SQL Server | MicroSoft 微软公司收费的中型的数据库。 C#、.net等语言常使用。但该数据库只能运行在windows机器上,扩展性、稳定性、安全性、性能都表现平平。 |
MySQL的安装及配置
安装MySQL
卸载MySQL
MySQL环境变量配置
MySQL的启动与关闭
方式一 : window服务启动 MySQL
-
右键此电脑 --> 管理
-
选择服务–> 找到MysQL服务
-
右键选择 --> 启动或停止
方式二: DOS 命令方式启动
-
首先以管理员身份 打开命令行窗口
-
启动MySql — net start mysql57
-
关闭MySql — net stop mysql57
命令行登录数据库
MySQL是一个需要账户名密码登录的数据库,登陆后使用,它提供了一个默认的root账号,使用安装时设置的密码即可登录。
命令 | 说明 |
---|---|
mysql -u 用户名 -p 密码 | 使用指定用户名和密码登录当前计算机中的MySQL数据库 |
mysql -h 主机IP -u 用户名 -p 密码 | -h 指定IP方式,进行登录 |
命令演示:
mysql -uroot -p123456
mysql -h127.0.0.1 -uroot -p123456
退出命令:
exit/quit
SqlYog的使用
简介
SQLyog是业界著名的Webyog公司出品的一款简洁高效、功能强大的图形化MySQL数据库管理工具。使用 SQLyog 可以快速直观地让您从世界的任何角落通过网络来维护远端的 MySQL 数据库。
MySql的目录结构
MySQL安装目录
MySql的默认安装目录在 C:\Program Files\MySQL\MySQL Server 5.7
目录 | 目录内容 |
---|---|
bin | 放置一些可执行文件(如:mysql.exe) |
docs | 文档 |
include | 包含(头)文件 |
lib | 依赖库 |
share | 用于存放字符集、语言等信息 |
MySQL配置文件 与 数据库及 数据表所在目录
默认数据文件在C:\ProgramData\MySQL\MySQL Server 5.7
1、my.ini 文件 是 mysql 的配置文件,一般不建议去修改
2、Data<目录> Mysql管理的数据库文件所在的目录(数据库和数据表信息)
3、几个概念
数据库: 文件夹
数据表: 文件
数据: 文件中的记录
数据库管理系统
什么是数据库管理系统 ?
数据库管理系统(DataBase Management System,DBMS):指一种操作和管理维护数据库的大型软件。
MySQL就是一个数据库管理系统软件,安装了Mysql的电脑,我们叫它数据库服务器。
数据库管理系统的作用
用于建立、使用和维护数据库,对数据库进行统一的管理。
数据库管理系统、数据库 和表之间的关系
MySQL中管理着很多数据库,在实际开发环境中一个数据库一般对应一个应用,数据库当中保存着多张表,每一张表对应着不同的业务,表中保存着对应业务的数据。
数据库表
数据库中以表为组织单位存储数据,表类似我们Java中的类,每个字段都有对应的数据类型。
使用Java程序来与关系型数据对比:
类 -----> 表
类中属性 ----> 表中字段
对象 ---> 数据记录
SQL(重点)
SQL的概念
结构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。
SQL 的作用
1、是所有关系型数据库的统一查询规范,不同的关系型数据库都支持SQL
2、所有的关系型数据库都可以使用SQL
3、不同数据库之间的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(Data Control Language),用来定义数据库的访问权限和安全级别,及创建用户(了解) |
DDL 操作数据库
创建数据库
命令 | 说明 |
---|---|
create database 数据库名; | 创建指定名称的数据库 |
create database 数据库名 character set 字符集; | 创建指定名称的数据库,并且指定字符集(一般都指定utf-8) |
/*
对数据库操作的分类
CRUD
C create 创建
R retrieve 查询
U update 修改
D delete 删除
使用数据库
*/
/*
创建数据库 方式1:指定名称的数据库
latin1 编码
*/
CREATE DATABASE db1;
/*
方式2:指定字符集创建数据库
utf8
*/
CREATE DATABASE db1_1 CHARACTER SET utf8;
查看/选择数据库
命令 | 说明 |
---|---|
use 数据库 | 切换数据库 |
select database(); | 查看当前正在使用的数据库 |
show databases; | 查看Mysql中都有哪些数据库 |
show create database 数据库名; | 查看一个数据库的定义信息 |
/*
查看数据库
*/
# 切换数据库
USE db1_1;
# 查询当前正在使用的数据库
SELECT DATABASE();
# 查询MySQL中都有哪些数据库
SHOW DATABASES;
# 查看一个数据库的定义信息
SHOW CREATE DATABASE db1;
修改数据库
命令 | 说明 |
---|---|
alter database 数据库名 character set 字符集; | 数据库的字符集修改操作 |
-- 修改数据库字符集
-- 语法格式: alter database 数据库名 character set utf8
ALTER DATABASE db1 CHARACTER SET utf8;
-- 查询当前数据库的基本信息
SHOW CREATE DATABASE db1;
删除数据库
命令 | 说明 |
---|---|
drop database 数据库名 | 从MySql中永久的删除某个数据库 |
-- 删除数据库
-- 语法格式:drop database 数据库名称 将数据库从MySql中永久删除
DROP DATABASE db1_1; -- 慎用
DDL 操作数据表
MySQL常见的数据类型
1、常用的数据类型:
类型 | 描述 |
---|---|
int | 整型 |
double | 浮点型 |
varchar | 字符串型 |
data | 日期类型,给是为 yyyy-MM-dd ,只有年月日,没有时分秒 |
/*
创建表的语法格式
create table 表名(
字段名称 字段类型(长度),
字段名称2 字段类型,
字段名称3 字段类型 最后一个字段不要加逗号
);
MySQL中常见的数据类型
int 整型
double 浮点型
varchar 字符串类型
date 日期类型 只显示年月日 没有时分秒 yyyy-MM-dd
datetime 年月日时分秒 yyyy-MM-dd HH:mm:ss
char 类型 在MySQL中char 类型也代表字符串
*/
2、详细的数据类型(了解即可)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GktxCd5r-1620996416221)(E:\MarkDown\拉勾笔记\MySQL数据类型)]
3、注意:
MySQL中的 char类型与 varchar类型,都对应了Java中的字符串类型,区别在于:
char类型是固定长度的: 根据定义的字符串长度分配足够的空间。
varchar类型是可变长度的: 只使用字符串长度所需的空间
比如:保存字符串 “abc”
x char(10) 占用10个字节
y varchar(10) 占用3个字节
4、使用场景
char类型适合存储 固定长度的字符串,比如 密码 ,性别一类
varchar类型适合存储 在一定范围内,有长度变化的字符串
创建表
1、语法格式:
CREATE TABLE 表名(
字段名称1 字段类型(长度),
字段名称2 字段类型 注意 最后一列不要加逗号
);
2、需求1: 创建商品分类表
表名:category
表中字段:
分类ID :cid ,为整型
分类名称:cname,为字符串类型,指定长度20
3、SQL实现
-- 创建商品分类表
/*
表名 category
cid int 分类id
cname varchar 分类的名称
*/
-- 选择要使用的数据库
USE db1;
-- 创建分类表
CREATE TABLE category(
cid INT,
cname VARCHAR(20)
);
4、需求2: 创建测试表
表名: test1
表中字段:
测试ID : tid ,为整型
测试时间: tdate , 为年月日的日期类型
5、SQL实现
-- 创建测试表
/*
表名 test1
tid int
tdate date
*/
CREATE TABLE test1(
tid INT,
tdate DATE
);
6、需求3: 快速创建一个表结构相同的表(复制表结构)
语法格式:
create table 新表明 like 旧表名
-- 快速创建一个表结构相同的表(复制表结构)
-- 语法结构 create table 新表明 like 旧表名
-- 创建一个与test1表结构相同的 test2表
CREATE TABLE test2 LIKE test1;
-- 查看表结构
DESC test2;
查看表
命令 | 说明 |
---|---|
show tables; | 查看当前数据库中的所有表名 |
desc 表名; | 查看数据表的结构 |
-- 查看表
-- 查看当前数据库中所有的表
SHOW TABLES;
-- 查看创建表的 sql语句
SHOW CREATE TABLE category;
-- 查看表结构
DESC category;
删除表
命令 | 说明 |
---|---|
drop table 表名; | 删除表(从数据库中永久删除某一张表) |
drop table if exists 表名; | 判断表是否存在, 存在的话就删除,不存在就不执行删除 |
/*
表的删除
语法格式
drop table 表名; 从数据库中永久删除一张表
drop table if exists 表名; 判断表是否存在,如果存在就删除,不存在就不执行删除
*/
-- 删除test1表
DROP TABLE test1;
-- 使用先判断后删除方式
DROP TABLE IF EXISTS test2;
修改表
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 列名;
/*
修改表的名称
修改表的字符集
修改标中的某一项(数据类型 名称 长度)
向表中添加一列
删除表中的某一项
*/
-- 修改表名称 语法格式:rename table 旧表名 to 旧表名
RENAME TABLE category TO category1;
-- 修改表的字符集为 gbk
-- 语法格式:alter table 表名 character set 字符集
ALTER TABLE category1 CHARACTER SET gbk;
-- 向表中添加一个字段 关键字:add
-- 语法格式:alter table 表名 add 字段名称 字段类型(长度)
-- 添加分类描述字段
ALTER TABLE category1 ADD cdesc VARCHAR(`category1`20);
-- 修改表中列的类型或长度
-- 语法格式 alter table 表名 modify 字段名称 字段类型
-- 修改cdesc 字段长度为 50
ALTER TABLE category1 MODIFY cdesc VARCHAR(50); -- 修改字段长度
ALTER TABLE category1 MODIFY cdesc CHAR(20); -- 修改字段类型
-- 修改列的名称 关键字:change
-- 语法格式:alter table 表名 change 旧列名 新列明 类型(长度)
-- 修改cdesc字段 名称改为 description varchar(30)
ALTER TABLE category1 CHANGE cdesc description VARCHAR(30);
-- 删除列 关键字:drop
-- 语法格式:alter table 表名 drop 列名
ALTER TABLE category1 DROP description;
DML 操作表中数据
SQL中的DML 用于对表中的数据进行增删改操作
插入数据
语法格式:
insert into 表名 (字段名1,字段名2...) values(字段值1,字段值2...);
1、代码准备,创建一个学生表:
表名:student
表中字段:
学员ID, sid int
姓名, sname varchar(20)
年龄, age int
性别, sex char(1)
地址, address varchar(40)
2、向学生表中添加数据,3种方式
方式1: 插入全部字段, 将所有字段名都写出来
方式2: 插入全部字段,不写字段名
方式3:插入指定字段的值
3、注意:
a、值与字段必须要对应,个数相同&数据类型相同
b、值的数据大小,必须在字段指定的长度范围内
c、varchar char date类型的值必须使用单引号,或者双引号 包裹
d、如果要插入空值,可以忽略不写,或者插入null
e、如果插入指定字段的值,必须要上写列名
/*
DML 对表中数据进行 增删改
增加
语法格式:insert into 表名 (字段名1,字段名2...) values(字段值1,字段值2...)
*/
-- 创建学生表
CREATE TABLE student(
sid INT,
sname VARCHAR(20),
age INT,
sex CHAR(1),
address VARCHAR(40)
);
-- 向学生表中插入数据
-- 方式1:插入全部字段, 将所有字段名都写出来
INSERT INTO student (sid,sname,age,sex,address) VALUES(1,'孙悟空',18,'男','花果山');
-- 方式2:插入全部字段,不写字段名
INSERT INTO student VALUES(2,'孙悟饭',5,'男','地球');
-- 方式3:插入指定字段的值
INSERT INTO student (sid,sname) VALUES(3,'蜘蛛精');
-- 注意事项
-- 1.值与字段必须对应,个数、数据类型、长度都必须一致
INSERT INTO student (sid,sname,age,sex,address) VALUES(4,'孙悟空',18,'男','花果山');
-- 2.在插入 varchar char date 类型的时候,必须要使用 单引号 或者双引号进行包裹
INSERT INTO student (sid,sname,age,sex,address) VALUES(4,'孙悟空',18,'男','花果山');
-- 3.如果要插入空值,可以忽略不写,或者写null
INSERT INTO student (sid,sname) VALUES(5,'唐僧');
INSERT INTO student (sid,sname,age,sex,address) VALUES(6,'八戒',NULL,NULL,NULL);
更改数据
语法格式1:不带条件的修改
update 表名 set 列名 = 值
语法格式2:带条件的修改
update 表名 set 列名 = 值 [where 条件表达式:字段名 = 值 ]
/*
修改操作
语法格式1:update 表名 set 列名 = 值
语法格式2:update 表名 set 列名 = 值 [where 条件表达式:字段名 = 值 ]
*/
-- 修改表中的所有学生性别为女
UPDATE student SET sex = '女'; -- (慎用!!)
-- 带条件的修改 将sid为 1的数据,性别改为男
UPDATE student SET sex = '男' WHERE sid = 1;
-- 一次性修改多个列
-- 修改sid为 5的这条数据,年龄改为20,地址改为 大唐
UPDATE student SET age = 20, address = '大唐' WHERE sid = 5;
删除数据
语法格式1:删除所有数据
delete from 表名
语法格式2: 指定条件 删除数据
delete from 表名 [where 字段名 = 值]
/*
删除
语法格式1:delete from 表名
语法格式2:delete from 表名 [where 字段名 = 值]
*/
-- 删除sid为 6的数据
DELETE FROM student WHERE sid = 6;
-- 删除所有数据
DELETE FROM student;
-- 删除所有数据的方式:两种
-- 1.delete from 表; 不推荐,对表中的数据逐条删除,效率低
-- 2.truncate table 表; 推荐,删除整张表然后再创建一个一模一样的新表
INSERT INTO student VALUES(1,'孙悟空',20,'男','花果山');
TRUNCATE TABLE student;
DQL 查询表中数据
准备数据
-- 员工表 emp
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','财务部');
简单查询
查询不会对数据库中的数据进行修改,只是一种显示数据的方式 SELECT
语法格式
select 列名 from 表名
/*
DQL
简单查询
select 列名 from 表名;
*/
-- 查询emp 表中的所有数据
SELECT * FROM emp; -- * 表示所有的列
-- 查询所有数据 只显示 id 和 name
SELECT eid, ename FROM emp;
-- 查询所有的数据,然后给列名 改为中文
SELECT * FROM emp;
-- 别名查询 使用关键字 as
SELECT
eid AS '编号',
ename AS '姓名',
sex AS '性别',
salary AS '薪资',
hire_date AS '入职时间',
dept_name '部门名称' -- as 可以省略
FROM emp;
-- 查询一共有几个部门
SELECT dept_name FROM emp;;
-- 去重操作 关键字:distinct
SELECT DISTINCT dept_name FROM emp;
-- 将员工薪资数据 +1000
SELECT ename, salary+1000 AS salary 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 | 不成立,取反 |
3、模糊查询 通配符
通配符 | 说明 |
---|---|
% | 表示匹配任意多个字符串 |
_ | 表示匹配一个字符 |
/*
条件查询
语法格式:select 列名 from 表名 where 条件表达式
比较运算符
> < <= >= = <> !=
BETWEEN ...AND...
IN(集合)
LIKE
IS NULL
逻辑运算符
AND &&
Or ||
Not
*/
# 查询员工姓名为黄蓉的员工信息
-- 1.查哪张表 2.查哪些字段 3.查询条件
SELECT * FROM emp WHERE ename = '黄蓉';
# 查询薪水价格为5000的员工信息
SELECT * FROM emp WHERE salary = 5000;
# 查询薪水价格不是5000的所有员工信息
SELECT * FROM emp WHERE salary != 5000;
SELECT * FROM emp WHERE salary <> 5000;
# 查询薪水价格大于6000元的所有员工信息
SELECT * FROM emp WHERE salary > 6000;
# 查询薪水价格在5000到10000之间所有员工信息
SELECT * FROM emp WHERE salary BETWEEN 5000 AND 10000;
SELECT * FROM emp WHERE salary >= 5000 AND salary <= 10000;
# 查询薪水价格是3600或7200或者20000的所有员工信息
SELECT * FROM emp WHERE salary = 3600 OR salary = 7200 OR salary = 20000;
SELECT * FROM emp WHERE salary IN(3600,7200,20000);
/*
like '%精%'
% 通配符,表示匹配任意多个字符串
_ 通配符,表示匹配你一个字符
*/
# 查询含有'精'字的所有员工信息
SELECT * FROM emp WHERE ename LIKE '%精%';
# 查询以'孙'开头的所有员工信息
SELECT * FROM emp WHERE ename LIKE '孙%';
# 查询第二个字为'兔'的所有员工信息
SELECT * FROM emp WHERE ename LIKE '_兔%';
# 查询没有部门的员工信息
-- SELECT * FROM emp WHERE dept_name = null; 错误方式
SELECT * FROM emp WHERE dept_name IS NULL;
# 查询有部门的员工信息
SELECT * FROM emp WHERE dept_name IS NOT NULL;
-- 条件查询 先取出表中的每条数据,满足条件就返回,不满足的就过滤
MySQL单表&约束&事务
DQL操作单表
创建数据库、复制表
1、创建一个新的数据库 db2
2、将db1数据库中的emp表复制到当前db2数据库
排序
通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示效果,不会影响真实数据)
语法结构:
SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]
ASC 表示升序排序(默认)
DESC 表示降序排序
排序方式
1、单列排序
只按照某一个字段进行排序,就是单列排序
2、组合排序
同时对多个字段进行排序,如果第一个字段相同就按照第二个字段进行排序,以此类推
/*
排序
使用 order by 字句
语法结构:select 字段名 from 表名 [where 字段名 = 值] order by 字段名称 [ASC/DESC]
ASC:升序排序(默认)
DESC:降序排序
*/
-- 单列排序 按照某一个字段进行排序
-- 使用salary 字段 对emp表进行排序
SELECT * FROM emp ORDER BY salary; -- 默认升序
SELECT * FROM emp ORDER BY salary DESC; -- 降序排序
-- 组合排序 同时对东哥字段进行排序
-- 在薪资的平思绪基础上,再取使用 id字段进行排序
SELECT * FROM emp ORDER BY salary DESC ,eid DESC;
-- 组合排序的特点:如果第一个字段值相同,就按照第二个字段进行排序
聚合函数
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,它是对某一列的值进行计算,然后返回一个单一的值(另外聚合函数会忽略null空值);
语法结构:
SELECT 聚合函数(字段名) FROM 表名;
常用5个聚合函数
聚合函数 | 作用 |
---|---|
count(字段) | 统计指定列不为NULL的记录行数 |
sum(字段) | 计算指定列的数值和 |
max(字段) | 计算指定列的最大值 |
min(字段) | 计算指定列的最小值 |
avg(字段) | 计算指定列的平均值 |
/*
聚合函数
作用:将一列数据作为一个整体,进行纵向计算
常用聚合函数
count(字段) 统计记录数
sum(字段) 求和操作
max(字段) 求最大值
min(字段) 求最小值
avg(子弹) 求平均值
语法格式:select 聚合函数(字段名) from 表名 [where 条件]
*/
#1 查询员工总数
SELECT COUNT(*) FROM emp;
SELECT COUNT(1) FROM emp;
SELECT COUNT(eid) FROM emp;
-- count函数在统计时会忽略空值
-- 注意不要使用带空值的列进行 count
SELECT COUNT(dept_name) FROM emp;
#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
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) FROM emp WHERE dept_name = '市场部';
分组
分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组
语法格式:
SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];
where 与 having的区别
过滤方式 | 特点 |
---|---|
where | where 进行分组前的过滤 where 后面不能写 聚合函数 |
having | having 是分组后的过滤 having 后面可以写 聚合函数 |
/*
分组查询 使用 group by 语句
语法格式:select 分组字段/聚合函数 from 表名 group by 分组字段
*/
-- select * from emp group by sex; 没有意义,将男女分为两组后返回每组的第一个数据
-- 注意:分组的目的就是为了统计,所以一般分组会和聚合函数一起使用,单独进行分组是没有意义的
# 通过性别字段 进行分组,求各组的平均薪资
SELECT sex, AVG(salary) FROM emp GROUP BY sex;
#1.查询所有部门信息
SELECT dept_name AS '部门名称' FROM emp GROUP BY dept_name;
#2.查询每个部门的平均薪资
SELECT dept_name, AVG(salary) 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的部门
-- 1.首先要分组求平均薪资
-- 2.求出 平均薪资大于6000的部门
-- 在分组之后 进行条件过滤 使用:having 判断条件
SELECT
dept_name,
AVG(salary)
FROM emp
WHERE dept_name IS NOT NULL
GROUP BY dept_name HAVING AVG(salary) > 6000;
limit关键字
1、limit 关键字的作用
a、limit是限制的意思,用于限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)
b、limit 语法是 MySql的方言,用来完成分页
2、语法结构
SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;
3、参数说明
a、limit offset , length; 关键字可以接受一个 或者两个 为0 或者正整数的参数
b、offset 起始行数, 从0开始记数, 如果省略 则默认为 0
c、length 返回的行数
/*
limit 通过limit去指定要查询的数据的条数 行数
语法格式:
select 字段 from 表名 limit offset, length;
参数说明:
offset:起始行数,默认从 0开始计数
length:返回的行数(要查询几条数据)
*/
# 查询emp表中的前 5条数据
SELECT * FROM emp LIMIT 0, 5;
SELECT * FROM emp LIMIT 5;
# 查询emp表中 从第4条开始,查询6条
SELECT * FROM emp LIMIT 3, 6;
-- limit 分页操作,每页显示3条
SELECT * FROM emp LIMIT 0, 3; -- 第一页
SELECT * FROM emp LIMIT 3, 3; -- 第二页
SELECT * FROM emp LIMIT 6, 3; -- 第三页 3-1=2*3=6
-- 分页公式:起始行数 = (当前页码 - 1) * 每页显示条数
SQL约束
主键约束
特点:不可重复 唯一 非空
作用:用来表示数据库中的每一条记录
/*
约束
约束是指对数据进行一定的限制,来保证数据的完整性 有效性 正确性
常见的约束
主键约束 primary key
唯一约束 unique
非空约束 not null
外键约束 foreign key
*/
添加主键约束
语法格式:
字段名 字段类型 primary key
删除主键约束
删除表中的主键约束 (了解)
/*
主键约束
特点:不可重复 唯一 非空
作用:用来表示数据库中的每一条记录
语法格式:
字段名 字段类型 primary key
*/
-- 方式1:创建一个带有主键的表
CREATE TABLE emp2(
-- 设置主键 唯一 非空
eid INT PRIMARY KEY,
ename VARCHAR(20),
sex CHAR(1)
);
DESC emp2;
-- 方式2:创建一个带主键的表
DROP TABLE emp2; -- 删除表
CREATE TABLE emp2(
eid INT,
ename VARCHAR(20),
sex CHAR(1),
PRIMARY KEY(eid) -- 指定eid为主键
);
-- 方式3:创建表之后,再添加主键
CREATE TABLE emp2(
eid INT,
ename VARCHAR(20),
sex CHAR(1)
);
-- 通过DDL语句 添加主键约束
ALTER TABLE emp2 ADD PRIMARY KEY(eid);
-- 删除主键 DDL语句
ALTER TABLE emp2 DROP PRIMARY KEY;
主键的自增
注: 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
关键字:
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
修改主键自增的起始值
默认地 AUTO_INCREMENT 的开始值是 1,如果希望修改起始值,请使用下面的方式
/*
主键的自增
关键字:auto_increment 主键的自动增长(字段类型必须是整数类型)
*/
-- 创建主键自增的表
CREATE TABLE emp2(
-- 主键自增
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
);
# 添加数据 观察主键变化
INSERT INTO emp2(ename,sex) VALUES('张三','男');
INSERT INTO emp2(ename,sex) VALUES('李四','男');
INSERT INTO emp2 VALUES(NULL, '翠花', '女');
INSERT INTO emp2 VALUES(NULL, '秋艳', '女');
-- 修改主键自增的起始值
-- 重新创建主键自增的表,自定义自增的起始位置
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对自增长的影响
delete删除表中所有数据,将表中所有数据逐条删除
TRUNCATE删除表中所有数据,将整个表删除,然后再创建一个结构相同表
*/
-- delete 方式删除所有数据
DELETE FROM emp2; -- delete 删除对自增没有影响
INSERT INTO emp2(ename,sex) VALUES('张三','男'); -- 102
INSERT INTO emp2(ename,sex) VALUES('李四','男'); -- 103
-- TRUNCATE 删除所有数据
TRUNCATE TABLE emp2; -- 自增从1开始
INSERT INTO emp2(ename,sex) VALUES('张三','男'); -- 1
INSERT INTO emp2(ename,sex) VALUES('李四','男'); -- 2
非空约束
非空约束的特点: 某一列不予许为空
语法格式:
字段名 字段类型 not null
/*
非空约束
特点:某一列不予为空
语法格式:
字段名 字段类型 not null
*/
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
-- 将ename字段添加了非空约束
ename VARCHAR(20) NOT NULL,
sex CHAR(1)
);
唯一约束
唯一约束的特点: 表中的某一列的值不能重复( 对null不做唯一的判断 )
语法格式:
字段名 字段值 unique
主键约束与唯一约束的区别:
1、主键约束,唯一且不能够为空
2、唯一约束,唯一但是可以为空
3、一个表中只能有一个主键,但是可以有多个唯一约束
/*
唯一约束
特点:表中的某一列不能重复(对null值不做唯一判断)
语法格式:
字段名 字段类型 unique
*/
-- 创建 emp3表 为ename添加唯一约束
CREATE TABLE emp3(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) UNIQUE,
sex CHAR(1)
);
-- 测试唯一约束
INSERT INTO emp3 VALUES(1, '张百万', '女');
-- Duplicate entry '张百万' for key 'ename' 不能重复
INSERT INTO emp3 VALUES(2, '张百万', '女');
-- 唯一约束的值 可以为null
INSERT INTO emp3 VALUES(2,NULL, '女');
-- 主键约束与唯一约束的区别
-- 主键约束,唯一且不能够为空
-- 唯一约束,唯一但是可以为空
-- 一个表中只能有一个主键,但是可以有多个唯一约束
外键约束
FOREIGN KEY 表示外键约束,将在多表中学习。
默认值
默认值约束 用来指定某列的默认值
语法格式:
字段名 字段类型 DEFAULT 默认值
/*
默认值
特点:用来指定某列的默认值
语法格式:
字段名 字段类型 default 默认值
*/
-- 创建emp4表,指定sex默认为女
CREATE TABLE emp4(
eid INT PRIMARY KEY,
ename VARCHAR(20),
sex CHAR(1) DEFAULT '女'
);
INSERT INTO emp4(eid,ename) VALUES(1, '杨幂');
INSERT INTO emp4(eid,ename) VALUES(2, '柳岩');
-- 不适用默认值
INSERT INTO emp4(eid,ename,sex) VALUES(3, '蔡徐坤','男');
数据库事务
什么是事务
事务是一个整体,由一条或者多条SQL语句组成,这些SQL语句要么都执行成功,要么都执行失败,只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败。
比如: 银行的转账业务,张三给李四转账500元 , 至少要操作两次数据库, 张三 -500, 李四 + 500,这中间任何一步出现问题,整个操作就必须全部回滚, 这样才能保证用户和银行都没有损失.
回滚
即在事务运行的过程中发生了某种故障,事务不能继续执行,系统将事务中对数据库的所有已完成的操作全部撤销,滚回到事务开始时的状态。(在提交之前执行)
模拟转账操作
-- 创建账户表
CREATE TABLE bankaccount(
-- 主键
id INT PRIMARY KEY AUTO_INCREMENT,
-- 姓名
NAME VARCHAR(10),
-- 余额
money DOUBLE
);
-- 添加两个用户
INSERT INTO bankaccount(NAME,money) VALUES('tom', 1000), ('jack', 1000);
-- tom账户 -500元
UPDATE bankaccount SET money = money - 500 WHERE NAME = 'tom';
出错了
-- Jack账户 +500元
UPDATE bankaccount SET money = money + 500 WHERE NAME = 'jack';
MySQL事务操作
MYSQL 中可以有两种方式进行事务的操作:
a、手动提交事务
b、自动提交事务
手动提交事务
语法格式
功能 | 语句 |
---|---|
开启事务 | start transaction; 或者 BEGIN; |
提交事务 | commit; |
回滚事务 | rollback; |
START TRANSACTION
这个语句显式地标记一个事务的起始点。
COMMIT
表示提交事务,即提交事务的所有操作,具体地说,就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中,事务正常结束。
ROLLBACK
表示撤销事务,即在事务运行的过程中发生了某种故障,事务不能继续执行,系统将事务中对数据库的所有已完成的操作全部撤销,回滚到事务开始时的状态。
手动提交事务流程
a、执行成功的情况: 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
b、执行失败的情况: 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚
成功案例 演示
use db2;
show tables;
---------------+
| Tables_in_db2 |
+---------------+
| bankaccount |
| emp |
| emp2 |
| emp3 |
| emp4 |
+---------------+
start transaction;
update bankaccount set money = money - 500 where name = 'tom';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
update bankaccount set money = money + 500 where name = 'jack';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
commit;
Query OK, 0 rows affected (0.13 sec)
事务回滚演示
如果事务中,有某条sql语句执行时报错了,我们没有手动的commit,那整个事务会自动回滚。
自动提交事务
MySQL 默认每一条 DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,语句执行完毕 自动提交事务,MySQL 默认开始自动提交事务。
MySQL默认是自动提交事务
自动提交事务演示
update bankaccount set money = money + 500 where name = 'tom';
SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
取消自动提交
MySQL默认是自动提交事务,设置为手动提交
set @@autocommit=off;
SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
update bankaccount 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 | ✘ | ✘ | ✘ |
隔离级别相关命令
/*
MySQL的事务隔离级别
务在操作时的理想状态: 所有的事务之间保持隔离,互不影响。因为并发操作,多个用户同时访问同一个数据。可能引发并发访问的问题
通过设置隔离级别,来解决对应的问题
并发访问问题
脏读:一个事务读取到了另一个事务中尚未提交的数据
不可重复读:一个事务中两次读取的数据内容不一致
幻读:一个事务中,一次查询的结果无法支撑后续的业务操作
设置隔离级别
read uncommitted:读未提交
可以防止哪些问题:无
read committed:读已提交 (Oracle默认隔离级别)
可以防止:脏读
repeatable:可重复读 (MySQL默认的隔离级别)
可以防止:脏读,不可重复读
serializable:串行化
可以防止:脏读,不可重复读,幻读
注意:隔离级别 从小到大 安全性是越来越高的,但是效率是越来越低的
根据不同的情况选择不同的隔离级别
*/
/*
隔离级别相关命令
1.查看隔离级别
select @@tx_isolation;
2.设置隔离级别
set global transaction isolation level 级别名称;
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化
*/
-- 查看
SELECT @@tx_isolation;
-- 设置
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
隔离性问题演示
脏读演示
1、打开窗口登录 MySQL,设置全局的隔离级别为最低
2、关闭窗口,开一个新的窗口A ,再次查询隔离级别
3、再开启一个新的窗口B
解决脏读问题
脏读非常危险的,比如张三向李四购买商品,张三开启事务,向李四账号转入 500 块,然后打电话给李四说钱 已经转了。李四一查询钱到账了,发货给张三。张三收到货后回滚事务,李四的再查看钱没了。
解决方案
将全局的隔离级别进行提升为: read committed
不可重复读演示
解决不可重复读问题
将全局的隔离级别进行提升为: repeatable read
幻读演示
解决幻读问题
将事务隔离级别设置到最高 SERIALIZABLE ,以挡住幻读的发生
MySQL多表&外键&数据库设计
多表
多表简述
实际开发中,一个项目通常需要很多张表才能完成。
单表的缺点
冗余,同一个字段中出现大量的重复数据
-- 创建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,'销售部','深圳');
解决方案
设计为两张表
-- 创建两张表
-- 一方,主表
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,数据依然可以添加,显然这是不合理的
INSERT INTO employee (ename,age,dept_id) VALUES('无名',35,3);
外键约束
什么是外键
外键指的是在从表中与主表的主键对应的那个字段
使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性
主表: 主键id所在的表,约束别人的表
从表: 外键所在的表,被约束的表
创建外键约束
- 创建新表时添加外键
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
/*
外键约束
作用:使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性
外键
外键指的是在从表中与主表的主键对应的字段
主表和从表
主表: 主键id所在的表,约束别人的表,一的一方
从表: 外键所在的表,被约束的表,多的一方
添加外键约束的语法格式
1.创建表的时候添加外键
create table 表名(
字段...
[constraint] [外键约束名] foreign key(外键字段名) references 主表(主键字段)
);
*/
-- 创建员工表,添加外键
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不存在)
-- 添加外键约束之后,会产生一个强制的外键约束检查,保证数据完整性和一致性
INSERT INTO employee (ename, age, dept_id) VALUES ('错误', 18, 3);
删除外键约束
/*
删除外键约束
语法格式:
alter table 从表 drop foreign key 外键约束的名称
*/
-- 删除employee表的外键约束
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
-- 2.创建表之后添加外键约束
-- 语法格式:alter table 从表 add [constraint] [外键约束名] foreign key(外键字段名) references 主表(主键字段)
-- 简写 不写外键约束名 自动生产成本的外键约束名:employee_ibfk_1
ALTER TABLE employee ADD FOREIGN KEY(dept_id) REFERENCES department(id);
外键约束的注意事项
1.从表外键类型必须与主表主键类型一致,否则创建失败
2.添加数据时,应该先添加主表中的数据
3.删除数据时,应该先删除从表中的数据
/*
外键约束的注意事项
1.从表外键类型必须与主表主键类型一致,否则创建失败
2.添加数据时, 应该先添加主表中的数据
3.删除数据时,应该先删除从表中的数据
*/
-- 添加一个新的部门
INSERT INTO department(dep_name,dep_location) VALUES('市场部','北京');
-- 添加一个属于市场部的员工
INSERT INTO employee(ename,age,dept_id) VALUES('老胡',24,3);
级联删除操作(了解)
指删除主表数据的同时,也可以删除预制相关的从表数据
语法格式:
ON DELETE CASCADE
/*
级联删除
指删除主表数据的同时,也可以删除预制相关的从表数据
语法格式:
ON DELETE CASCADE
*/
-- 重新创建添加级联操作
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;
多表关系设计
表与表之间的三种关系
1、一对多关系(1:n 常见):班级和学生 部门和员工
2、多对多关系(n:n 常见):学生与课程 演员和角色
3、一对一关系(1:1 了解):身份证和人
一对多关系(1:n 常见)
建表原则:在多的一方(从表)建立外键,指向一的一方(主表)的主键
多对多关系(n:n 常见)
建表原则:需要创建第三张表,中间表中至少两个字段,是两张表中的主键字段,作为中间表的外键
一对一关系(1:1 了解)
建表原则:可以在任意一方建立外键指向另一方的主键
设计省&市表
一对多关系
主表:省
从表:市
-- 常见省表 主表
CREATE TABLE province(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
description VARCHAR(20)
);
-- 创建市表 从表 外键字段指向主表的主键
CREATE TABLE city(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20),
description VARCHAR(20),
-- 创建外键添加外键约束
pid INT,
FOREIGN KEY(pid) REFERENCES province(id)
);
设计演员与角色表
多对多关系型
-- 多对多关系 演员与角色
-- 演员表
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,
-- aid 字段指向actor表的主键
aid INT,
-- rid 字段指向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:查询多张表,获取到需要的数据
/*
多表查询的语法
select 字段列表 from 表名列表;
*/
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;
SELECT * FROM products, category;
多表查询分类
内连接查询
特点:通过指定的条件去匹配两张表中的内容,匹配不上的不显示
1、隐式内连接
语法格式:
select 字段名... from 左表,右表 where 连接条件
-- 1.查询所有商品信息和对应的分类信息
-- 隐式内连接查询
SELECT * FROM products, category WHERE category_id = cid;
-- 2.查询商品表的商品名称和价格,以及商品的分类信息
-- 多表查询中可以使用给表其别名的方式 简化查询
SELECT
p.pname,
p.price,
c.cname
FROM products p, category c WHERE p.category_id = c.cid;
-- 3.查询格力空调是属于哪一分类下的商品
SELECT
p.pname,
c.cname
FROM products p, category c WHERE p.category_id = c.cid AND p.pid = 'p002';
2、显示内连接
语法格式:
select 字段名... from 左表 [inner] join 右表 on 连接条件
-- 1.查询所有商品信息和对应的分类信息
-- 显示内连接查询
SELECT * FROM products p
INNER JOIN category c ON p.category_id = c.cid;
-- 查询鞋服分类下,价格大于500的商品名称和价格
/*
查询之前要确定几件事
1.查询几张表
2.表的连接条件 p.category_id = c.cid; 从表.外键 = 主表.主键
3.查询的字段 商品名称 价格
4.查询条件 分类 = 鞋服, 价格 > 500
*/
SELECT
p.pname,
p.price
FROM products p
INNER JOIN category c ON p.category_id = c.cid
WHERE p.price > 500 AND c.cname = '鞋服';
外连接查询
1、左外连接
语法格式:
关键字:left [outer] join
select 字段名 from 左表 left join 右表 on 连接条件
左外连接特点:
以左表为基准,匹配右表中的数据,如果能匹配上就显示
如果匹配不上,左表中的数据正常展示,右表数据显示为null
-- 左外连接查询
SELECT
*
FROM category c
LEFT JOIN products p ON c.cid = p.category_id;
-- 查询每个分类下的商品个数
/*
1.查询的表
2.查询条件 分组 统计
3.查询的字段 分类 分类下商品个数信息
4.表的连接条件
*/
SELECT
c.cname,
COUNT(p.pid)
FROM
-- 表连接
category c LEFT JOIN products p ON c.cid = p.category_id
-- 分组
GROUP BY c.cname;
2、右外连接
语法格式:
关键字:right [outer] join
select 字段名 from 左表 right join 右表 on 连接条件
右外连接特点:
以右表为基准,匹配左表中的数据,如果能匹配上就显示
如果匹配不上,右表中的数据正常展示左表数据显示为null
-- 右外连接查询
SELECT * FROM products p RIGHT JOIN category c ON p.category_id = c.cid;
各种连接方式的总结
内连接: inner join,只获取两张表中交集部分的数据
左外连接: left join,以左表为基准,查询左表的所有数据,以及与右表有交集的部分
右外连接: right join,以右表为基准,查询右表的所有的数据,以及与左表有交集的部分
子查询 (SubQuery)
什么是子查询
1、子查询概念
一条select 查询语句的结果,作为另一条 select 语句的一部分
2、子查询的特点
子查询必须要放在小括号中
子查询作为父查询的条件使用(更多的时候)
3、子查询分类
where型子查询:将子查询的结果,作为父查询的比较条件使用
from型子查询:将子查询的结果作为一张表使用
exists型子查询:查询结果是单列多行的情况,可以将子查询的结果作为父查询的 in函数中的条件使用
子查询的结果作为查询条件(where型)
语法格式:
SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
-- 查询价格最高的商品信息
-- 1.查询除最高的价格
SELECT MAX(price) FROM products; -- 5000
-- 2.根据最高价格查询商品信息
SELECT * FROM products WHERE price = 5000;
-- 使用一条SQL完成 子查询方式
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);
-- 子查询作为查询条件
-- 1.查询化妆品分类下的 商品名称 商品价格
-- 查询除华章品分类的 id
SELECT cid FROM category WHERE cname = '化妆品'; -- c003
-- 2.根据化妆品id 查询对应商品信息
SELECT
p.pname,
p.price
FROM products p
WHERE p.category_id = (SELECT cid FROM category WHERE cname = '化妆品');
-- 查询小于平均价格的商品信息
-- 1.求出平均价格
SELECT AVG(price) FROM products; -- 1866
-- 2.获取小于平均价格的商品信息
SELECT
*
FROM products
WHERE price < (SELECT AVG(price) FROM products);
子查询的结果作为一张表(from型)
语法格式:
SELECT 查询字段 FROM (子查询)表别名 WHERE 条件;
-- 子查询结果作为一张表使用
-- 查询商品中,价格大于500的商品信息,包括 商品名称 商品价格 商品所属分类名称
SELECT * FROM category;
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型)
语法格式:
SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
/*
子查询结果是单列多行,作为父查询的 in函数中的条件使用
语法格式
selecct 字段名 from 表名 where 字段 in(子查询);
*/
-- 查询价格小于两千的商品,来自于哪些分类(名称)
-- 1.查询出小于2000的商品的 分类id
SELECT DISTINCT category_id FROM products WHERE price < 2000;
-- 2.根据分类 id 查询分类信息
SELECT * FROM category
WHERE cid IN
(SELECT DISTINCT category_id FROM products WHERE price < 2000);
-- 查询家电类 与 鞋服类下面的全部商品信息
-- 1.首先要获取 家电类和鞋服类的 分类id
SELECT cid FROM category WHERE cname IN('家电','鞋服');
-- 2.根据 分类id 查找商品信息
SELECT
*
FROM products WHERE category_id IN
(SELECT cid FROM category WHERE cname IN('家电','鞋服'));
子查询总结
1.子查询如果是一个字段(单列),那么就放在where后面做条件
2.如果是多个字段(多列),就当做一张表使用(要起别名)
数据库设计
数据库三范式(空间最省)
三范式指的就是数据库设计的一个规则
作用:为了创建冗余较小,结构合理的数据库
范式:设计数据库的要求(规范)
第一范式(1NF) :满足最低要求的范式
第二范式(2NF):在满足第一范式的基础之上,进一步满足更多的规范
第三范式(3NF):以此类推
第一范式(1NF)
列具有原子性,设计列要做到列不可拆分
数据库表里面字段都是单一属性的,不可再分,如果数据表中每个字段都是不可再分的最小数据单元,则满足第一范式。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E8gII3MU-1620996416248)(E:\MarkDown\拉勾笔记\数据库第一范式(1NF))]
第二范式(2NF)
一张表只能描述一件事
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g4oKVBKK-1620996416258)(E:\MarkDown\拉勾笔记\数据库设计第二范式(2NF))]
第三范式(3NF)
消除传递依赖
表中信息如果能被推导出来,就不要设计一个字段单独来记录
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-90bG5Wkk-1620996416272)(E:\MarkDown\拉勾笔记\数据库设计第三范式(3NF))]
三范式就是空间最省原则
数据库反三范式
1、概念
指通过增加冗余或者重复数据,来提高数据库的性能
浪费存储空间,节省查询时间(以空间换时间)
2、冗余字段
某一个字段,属于一张表,但是它又在多张表中都有出现
3、示例
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AnNDPJNT-1620996416290)(E:\MarkDown\拉勾笔记\数据库设计反三范式)]
冗余字段name
4、总结
1.尽量根据三范式规则去设计数据库
2.可以合理的增加冗余字段,减少join操作,让数据库执行得更快
MySQL索引&视图&存储过程
MySQL 索引
概念
通过对数据表中的字段创建索引,来提高查询速度。
常见索引分类
1、主键索引(primary key):主键是一种唯一性索引,每个表只能有一个主键,用于标识数据表中的每一条记录
2、唯一索引(unique):索引列的所有值都只能出现一次,必须唯一
3、普通索引(index):最常见的索引,作用就是提高对数据的访问速度
表对应的索引都保存在同一个索引文件中,如果对数据进行增删改操作,MySql都会自动的更新索引。
主键索引 (PRIMARY KEY)
1、创建表的时候,直接添加主键
2、创建表之后添加索引,使用DDL
CREATE TABLE demo01(
did INT,
dname VARCHAR(20),
hobby VARCHAR(30)
);
/*
主键索引
1.创建表的时候,直接添加主键
2.创建表之后添加索引,使用DDL
*/
-- 为demo01表添加主键索引
ALTER TABLE demo01 ADD PRIMARY KEY(did);
唯一索引(UNIQUE)
/*
唯一索引的创建
create unique index 索引名 on 表名(列名(长度))
*/
-- 为demo01表的hobby字段添加唯一索引
CREATE UNIQUE INDEX ind_hobby ON demo01(hobby);
-- 添加唯一索引的列,列的所有值都只能出现一次
INSERT INTO demo01 VALUES(1,'tom','篮球');
-- Duplicate entry '篮球' for key 'ind_hobby'
-- 唯一索引保证数据的唯一性,同时也提升了查询的效率
INSERT INTO demo01 VALUES(2,'jack','篮球');
普通索引 (INDEX)
/*
普通索引
1.create index 索引名 on 表名(列名[长度])
2.ALTER TABLE 表名 ADD INDEX 索引名 (列名)
*/
-- 为demo01表中的dname 字段添加普通索引
ALTER TABLE demo01 ADD INDEX ind_dname(dname);
删除索引
/*
删除索引
ALTER TABLE 表名 DROP INDEX index_name;
*/
-- 删除demo01表上dname字段上的索引
ALTER TABLE demo01 DROP INDEX ind_dname;
索引性能测试
/*
索引性能测试
导入 test_index SQL脚本
*/
-- 表中有 500万条数据
SELECT COUNT(*) FROM test_index;
-- 通过id 查询一条数据(添加了索引) 0.001秒
SELECT * FROM test_index WHERE id = 100001;
-- 通过dname 查询一条数据(没有添加索引) 耗时4秒
SELECT * FROM test_index WHERE dname = 'name5200';
-- 执行分组查询 dname没有添加索引 1分28秒
SELECT * FROM test_index GROUP BY dname;
-- 为dname 添加索引
ALTER TABLE test_index ADD INDEX dname_index(dname);
-- dename字段添加索引后 0.08秒
SELECT * FROM test_index GROUP BY dname;
索引总结
1、创建索引原则
优先选择为经常出现在 查询条件或者排序分组 后面的字段创建索引
2、索引的优点
1.可以大大的提高查询速度
2.减少查询中分组和排序时间
3.通过创建唯一索引保证数据的唯一性
3、索引的缺点
1.创建和维护索引需要时间,数据量越大,时间越长
2.表中的数据进行增删改操作时,索引也需要进行维护,降低了维护的速度
3.索引文件需要占据磁盘空间
MySQL 视图
概念
视图是由查询结果形成的一张虚拟的表
作用
如果某个查询的结果出现十分频繁,并且查询语法比较复杂,那么这个时候就可以根据这条查询语句构建一张视图,方便查询
视图使用
1、语法格式:
create view 视图名[字段列表] as select 查询语句;
view 表示视图
字段列表一般跟后面的查询语句相同
as select 查询语句 表示给视图提供数据的查询语句
2、创建视图
-- 创建视图
-- 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;
-- 操作视图就相当于操作一张 只读表
SELECT * FROM products_category_view;
2、通过视图进行查询
-- 使用视图记性处查询操作
-- 查询各个分类下的商品平均价格
/*
1.查询那些表:分类表 商品表
2.查询条件:分组操作
3.查询字段:平均价格 分类名
4.多表的连接条件 category_id = cid
*/
-- 使用多表方式查询
SELECT
c.cname,
AVG(p.price)
FROM products p LEFT JOIN category c ON p.category_id = c.cid
GROUP BY c.cname;
-- 通过视图查询
SELECT
pc.cname,
AVG(pc.price)
FROM products_category_view pc GROUP BY pc.cname;
-- 查询鞋服分类下最贵的商品的全部信息
-- 多表查询
-- 1.查询鞋服分类中 最高的商品价格
SELECT
MAX(p.price)
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(p.price)
FROM products p LEFT JOIN category c ON p.category_id = c.cid
WHERE c.cname = '鞋服'
);
-- 通过视图查询
SELECT
*
FROM products_category_view pc
WHERE pc.cname = '鞋服' AND pc.price =
(
-- 子查询:求出鞋服分类下的最高价格
SELECT MAX(pc.price) FROM products_category_view pc
WHERE pc.cname = '鞋服');
视图与表的区别
1、视图是建立在表的基础之上
2、通过视图,不要进行增删改操作,视图主要就是用来简化查询的
3、删除视图表不受影响,但是删除表视图就不再起作用了
MySQL 存储过程(了解)
概念
存储过程其实就是一堆SQL语句的合并。中间加入了一些逻辑控制。
优缺点
1、优点:
a.调试完成就可以稳定运行(在业务需求相对稳定情况)
b.存储过程可以减少业务系统与数据库的交互
2、缺点:
a.互联网项目中较少使用存储过程,因为业务需求变化太快
b.存储过程的移植十分困难
创建方式
准备数据
# 商品表
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 存储过程名称() -- 声明存储过程
begin -- 开始编写存储过程
-- 要执行的SQL
end $$ -- 存储过程结束
-- 编写存储过程,查询所有商品信息
DELIMITER $$
CREATE PROCEDURE goods_proc()
BEGIN
-- 查询商品数据
SELECT * FROM goods;
END $$;
-- 调用存储过程 call
CALL goods_proc;
方式二
创建一个接收参数的存储过程
语法格式:
create procedure 存储过程名(in 参数名 参数类型)
-- 创建存储过程
-- 接收一个商品id,根据id删除数据
DELIMITER $$
CREATE PROCEDURE goods_proc02(IN goods_id INT)
BEGIN
-- 根据id删除数据
DELETE FROM goods WHERE gid = goods_id;
END $$
-- 调用存储过程 传递参数
CALL goods_proc02(1);
方式三
格式:
1.变量的赋值
SET @变量名 = 值
2.OUT 输出参数
OUT 变量名 数据类型
-- 向订单表插入一条数据,返回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);
-- 设置 out_num值为1
SET @out_num = 1;
-- 返回out_num
SELECT @out_num;
END $$
-- 调用存储过程,获取返回值
CALL orders_proc(1,2,50,@out_num);
MySQL触发器(了解)
概念
当我们执行一条sql语句的时候,这条sql语句的执行会自动去触发执行其他的sql语句。
触发器创建的四个要素
1、监视地点(table)
2、监视事件(insert/update/delete)
3、触发时间(before/after)
4、触发事件(insert/update/delete)
创建触发器
语法结构:
delimiter $ -- 自定义结束符号
create trigger 触发器名
after/before(insert/update/delete) -- 触发的时机和监视的事件
on 表名 -- 触发器所在的表
for each row -- 固定写法,表示行触发器
begin
-- 被触发的事件
end $
-- 向商品中添加一条数据
INSERT INTO goods VALUES(4,'book',40);
-- 需求:在下订单的时候,对应的商品的库存量要相应的减少,卖出商品之后减少库存量
/*
监视的表:orders
监视的事件:insert
触发的时间:after
触发的事件:update
*/
-- 1.修改结束符号
DELIMITER $
-- 2.创建触发器
CREATE TRIGGER t1
-- 3.设置触发的时间,监视的事件以及监视的表
AFTER INSERT ON orders
-- 4.行触发器
FOR EACH ROW
-- 5.触发后要执行的操作
BEGIN
-- 执行库存修改操作 订单+1,库存-1
UPDATE goods SET num = num - 1 WHERE gid = 4;
END $
-- 向orders表插入一个订单
INSERT INTO orders VALUES(1,4,25);
DCL(数据控制语言)
创建用户
语法格式:
create user '用户名'@'主机名' identified by '密码';
-- 创建 admin1 用户,只能在localhost这个服务器登录mysql服务器,密码为123456
CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';
-- 创建 admin2 用户可以在任何电脑上登录 mysql 服务器,密码为 123456
CREATE USER 'admin2'@'%' IDENTIFIED BY '123456'; -- %表示在任意用户电脑都能登陆
用户授权
语法格式:
grant 权限1,权限2 ... on 数据库名.表 to '用户名'@'主机名';
-- 给 admin1 用户分配对 db4 数据库中 products 表的 操作权限:查询
GRANT SELECT ON db4.products TO 'admin1'@'localhost';
-- 给 admin2 用户分配所有权限,对所有数据库的所有表
GRANT ALL ON *.* TO 'admin2'@'%';
-- 查询商品表
SELECT * FROM products;
-- 插入商品数据
-- INSERT command denied to user 'admin1'@'localhost' for table 'products'
-- admin1 用户只有查询权限
INSERT INTO products VALUES('p010','小鸟伏特加',3000,NULL);
查看权限
语法格式:
show grants for '用户名'@'主机名';
-- 查看root 用户的权限
SHOW GRANTS FOR 'root'@'localhost';
-- 查看admin1用户权限
SHOW GRANTS FOR 'admin1'@'localhost';
删除和查询用户
-- 删除用户
DROP USER 'admin1'@'localhost';
-- 查询用户
SELECT * FROM USER;
数据库备份&还原
SQLYog 数据备份&恢复
命令行备份&恢复
语法格式:
备份:mysqldump -u用户名 -p密码 数据库名 > 文件路径
还原:source sql文件地址
备份:
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump -uroot -p123456 db2 > G:/db02.sql
还原:
mysql> use db2;
Database changed
mysql> source G:db02.sql;