一、MySQL基础
1.概念
①数据库概念
定义
● 数据库(DataBase) 就是存储和管理数据的仓库
● 其本质是一个文件系统, 还是以文件的方式,将数据保存在电脑上
数据存储方式的比较
命令行登录数据库
MySQL默认安装目录
MySQL配置文件与数据文件所在目录
注意：my.ini 文件 是 mysql 的配置文件，一般不建议去修改
②数据库管理系统
定义
● 数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理维护数据库的大型软件。
● MySQL就是一个 数据库管理系统软件, 安装了Mysql的电脑,我们叫它数据库服务器。
数据库管理系统的作用
● 用于建立、使用和维护数据库，对数据库进行统一的管理。
数据库管理系统、数据库 和表之间的关系
● MySQL中管理着很多数据库，在实际开发环境中 一个数据库一般对应了一个的应用，数据库当中保存着多张表，每一张表对应着不同的业务，表中保存着对应业务的数据。
③数据库表
● 数据库中以表为组织单位存储数据。
● 表类似我们Java中的类,每个字段都有对应的数据类型。
Java程序与关系型数据的关系:
类 -----> 表
类中属性 ----> 表中字段
对象 —> 数据记录
④MySQL自带数据库介绍
2.SQL
①概念
定义
结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
SQL 的作用
● 是所有关系型数据库的统一查询规范，不同的关系型数据库都支持SQL.
● 所有的关系型数据库都可以使用SQL.
● 不同数据库之间的SQL 有一些区别.
②SQL通用语法
1.SQL语句可以单行 或者 多行书写，以分号 结尾.
2.可以使用空格和缩进来增加语句的可读性.
3.MySql中使用SQL不区分大小写,一般关键字大写,数据库名 表名列名 小写。
4.注释方式：
单行注释 ：-- 注释
多行注释：/* 注释 */
MySQL特有单行注释：#注释
③SQL的分类
3.DDL操作数据库
①创建数据库
#方式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;
4.DDL操作数据表
①MySQL的数据类型
注意：
MySQL中的 char类型与 varchar类型，都对应了 Java中的字符串类型，区别在于：
● char类型是固定长度的： 根据定义的字符串长度分配足够的空间。
● varchar类型是可变长度的： 只使用字符串长度所需的空间
例如：
x char(10) 占用10个字节
y varchar(10) 占用3个字节
● char类型适合存储 固定长度的字符串，比如 密码 ，性别一类
● varchar类型适合存储 在一定范围内，有长度变化的字符串
②创建表
语法格式：
CREATE TABLE 表名(
字段名称1 字段类型（长度）,
字段名称2 字段类型 #注意 最后一列不要加逗号
);
#创建表
CREATE TABLE category(
cid INT,
cname VARCHAR(20)
);
快速创建一个表结构相同的表（复制表结构）
create table 新表明 like 旧表名
#创建一个表结构与category相同的category1表
CREATE TABLE category1 LIKE category;
③查看表
#查看当前数据库中的所有表名
SHOW TABLES;
#显示当前数据表的结构
DESC category;
#查看创建表的SQL语句
SHOW CREATE TABLE category;
④删除表
#直接删除category表
DROP TABLE category;
#先判断 再删除category表
DROP TABLE IF EXISTS category;
⑤修改表
1.修改表名
rename table 旧表名 to 新表名;
#将category表 改为 category1
RENAME TABLE category TO category1;
2.修改表的字符集
alter table 表名 character set 字符集;
#将category表的字符集 修改为gbk
alter table category character set gbk;
3.向表中添加列， 关键字 ADD
alert table 表名 add 字段名称 字段类型;
#为分类表添加一个新的字段为 分类描述 cdesc varchar(20)
ALTER TABLE category ADD cdesc VARCHAR(20);
4.修改表中列的 数据类型或长度 ， 关键字 MODIFY
alter table 表名 modify 字段名称 字段类型;
#对分类表的描述字段进行修改，类型varchar(50)
ALTER TABLE category MODIFY cdesc VARCHAR(50);
5.修改列名称 , 关键字 CHANGE
alter table 表名 change 旧列名 新列名 类型(长度);
#对分类表中的 desc字段进行更换, 更换为 description varchar(30)
ALTER TABLE category CHANGE cdesc description VARCHAR(30);
6.删除列 ，关键字 DROP
alter table 表名 drop 列名;
#删除分类表中description这列
ALTER TABLE category DROP description;
5.DML 操作表中数据
①插入数据
insert into 表名 （字段名1，字段名2...） values(字段值1，字段值2...);
#创建学生表
CREATE TABLE student(
sid INT,
sname VARCHAR(20),
age INT,
sex CHAR(1),
address VARCHAR(40)
);
向学生表中添加数据
#插入全部字段， 将所有字段名都写出来
INSERT INTO student (sid,sname,age,sex,address)
VALUES(1,'孙悟空',20,'男','花果山');
#插入全部字段，不写字段名
INSERT INTO student VALUES(2,'孙悟饭',10,'男','地球');
#插入指定字段的值
INSERT INTO category (cname) VALUES('白骨精');
注意:
①值与字段必须要对应，个数相同&数据类型相同.
②值的数据大小，必须在字段指定的长度范围内.
③varchar char date类型的值必须使用单引号，或者双引号包裹.
④如果要插入空值，可以忽略不写，或者插入null.
⑤如果插入指定字段的值，必须要上写列名.
②更改数据
#不带条件的修改
update 表名 set 列名 = 值;
#带条件的修改
update 表名 set 列名 = 值 [where 条件表达式：字段名 = 值];
③删除数据
#删除所有数据
delete from 表名;
#指定条件 删除数据
delete from 表名 [where 字段名 = 值];
#删除 sid 为 1 的数据
DELETE FROM student WHERE sid = 1;
#删除所有数据
DELETE FROM student;
#如果要删除表中的所有数据,有两种做法
#1.delete from 表名; 不推荐 有多少条记录就执行多少次删除操作.效率低
#2.truncate table 表名; 推荐 先删除整张表, 然后再重新创建一张一模一样的表. 效率高
truncate table student;
6.DQL 查询表中数据
#创建员工表
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 列名 from 表名;
#查询emp中的 所有数据
SELECT * FROM emp; -- 使用 * 表示所有列
#查询emp表中的所有记录，仅显示id和name字段
SELECT eid,ename FROM emp;
#将所有的员工信息查询出来，并将列名改为中文
# 使用 AS关键字,为列起别名
SELECT
eid AS '编号',
ename AS '姓名' ,
sex AS '性别',
salary AS '薪资',
hire_date '入职时间', -- AS 可以省略
dept_name '部门名称'
FROM emp;
#查询一共有几个部门
#使用distinct 关键字,去掉重复部门信息
SELECT DISTINCT dept_name FROM emp;
#将所有员工的工资 +1000 元进行显示
SELECT ename , salary + 1000 FROM emp;
②条件查询
select 列名 from 表名 where 条件表达式;
运算符
比较运算符
逻辑运算符
模糊查询 通配符
#查询员工姓名为黄蓉的员工信息
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;
#查询含有'精'字的所有员工信息
SELECT * FROM emp WHERE ename LIKE '%精%';
#查询第二个字为'兔'的所有员工信息
SELECT * FROM emp WHERE ename LIKE '_兔%';
二、MySQL单表&约束&事务
1.DQL操作单表
①排序
#通过ORDER BY子句,可以将查询出的结果进行排序
#ASC 表示升序排序(默认)
#DESC 表示降序排序
SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC];
a.单列排序
只按照某一个字段进行排序, 就是单列排序
#使用 salary 字段,对emp 表数据进行排序 (升序/降序)
#默认升序排序 ASC
SELECT * FROM emp ORDER BY salary;
#降序排序
SELECT * FROM emp ORDER BY salary DESC;
b.组合排序
同时对多个字段进行排序, 如果第一个字段相同 就按照第二个字段进行排序,以此类推
#在薪水排序的基础上,再使用id进行排序, 如果薪水相同就以id 做降序排序
#组合排序
SELECT * FROM emp ORDER BY salary DESC, eid DESC;
②聚合函数
SELECT 聚合函数(字段名) FROM 表名;
#1 查询员工的总数
#统计表中的记录条数 使用 count()
SELECT COUNT(eid) FROM emp; -- 使用某一个字段
SELECT COUNT(*) FROM emp; -- 使用 *
SELECT COUNT(1) FROM emp; -- 使用 1,与 * 效果一样
#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
-- sum函数求和, max函数求最大, min函数求最小, avg函数求平均值
SELECT
SUM(salary) AS '总薪水',
MAX(salary) AS '最高薪水',
MIN(salary) AS '最低薪水',
AVG(salary) AS '平均薪水'
FROM emp;
③分组
分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组
SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];
#1.查询有几个部门
SELECT dept_name AS '部门名称' FROM emp GROUP BY dept_name;
#2.查询每个部门的平均薪资, 部门名称不能为null
SELECT
dept_name AS '部门名称',
AVG(salary) AS '平均薪资'
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name;
#3.查询平均薪资大于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的区别
④limit关键字
limit 关键字的作用
● limit是限制的意思,用于 限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)
● limit 语法是 MySql的方言,用来完成分页
SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;
-- offset 起始行数, 从0开始记数, 如果省略 则默认为 0.
-- length 返回的行数
#查询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条数据
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; -- 第三页
#分页公式 起始索引 = (当前页 - 1) * 每页条数
2.SQL约束
约束的作用
对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性. 违反约束的不正确数据,将无法插入到表中。
常见的约束
①主键约束
字段名 字段类型 primary key
#方式1 创建一个带主键的表
CREATE TABLE emp1(
-- 设置主键 唯一 非空
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 创建一个带主键的表
CREATE TABLE emp2(
eid INT ,
ename VARCHAR(20),
sex CHAR(1)
)
#创建的时候不指定主键,然后通过 DDL语句进行设置
ALTER TABLE emp2 ADD PRIMARY KEY(eid);
可以作为主键的字段
● 通常针对业务去设计主键,每张表都设计一个主键id
● 主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复就好,比如 身份证就可以作为主键.
②删除主键约束
#使用DDL语句 删除表中的主键
ALTER TABLE emp2 DROP PRIMARY KEY;
③主键的自增
#创建主键自增的表
CREATE TABLE emp2(
#关键字 AUTO_INCREMENT,主键类型必须是整数类型
#AUTO_INCREMENT 的开始值是 1
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
);
#创建主键自增的表,自定义自增其实值
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)AUTO_INCREMENT=100;
DELETE和TRUNCATE对自增长的影响
④非空约束
字段名 字段类型 not null
# 非空约束
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)
);
3.数据库事务
①概念
事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败。
②模拟转账操作
#创建账户表
CREATE TABLE account(
#主键
id INT PRIMARY KEY AUTO_INCREMENT,
#姓名
NAME VARCHAR(10),
#余额
money DOUBLE
);
#添加两个用户
INSERT INTO account (NAME, money) VALUES ('tom', 1000), ('jack', 1000);
#模拟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 中可以有两种方式进行事务的操作：
● 手动提交事务
● 自动提交事务
a.手动提交事务
START TRANSACTION
● 这个语句显式地标记一个事务的起始点。
COMMIT
● 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。
ROLLBACK
● 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态
手动提交事务流程
执行成功的情况： 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
执行失败的情况： 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚
注意
如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。
b.自动提交事务
MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕 自动提交事务，MySQL 默认开始自动提交事务
MySQL默认是自动提交事务
取消自动提交
SET @@autocommit=off;
c.事务的四大特性 ACID
④Mysql 事务隔离级别
一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库. 数据库的相同数据可能被多个事务同时访问,如果不采取隔离措施,就会导致各种问题, 破坏数据的完整性。
a.四种隔离级别
b.隔离级别相关命令
#查看隔离级别
select @@tx_isolation;
设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化
set global transaction isolation level 级别名称;
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化
#修改隔离级别为 读未提交
set global transaction isolation level read uncommitted;
三、MySQL多表&外键&数据库设计
1.多表
单表的问题
解决方案
设计为两张表
department 部门表 : id, dep_name, dep_location
employee 员工表: eid, ename, age, dep_id
● 员工表中有一个字段dept_id 与部门表中的主键对应,员工表的这个字段就叫做 外键
● 拥有外键的员工表 被称为 从表 , 与外键对应的主键所在的表叫做 主表
①外键约束
● 外键指的是在 从表 中 与 主表 的主键对应的那个字段,比如员工表的 dept_id,就是外键
● 使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性
②创建外键约束
#新建表时添加外键
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
#已有表添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主键字段名);
#创建 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)
)
③删除外键约束
alter table 从表 drop foreign key 外键约束名称;
#删除employee 表中的外键约束,外键约束名 emp_dept_fk
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
#添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
#可以省略外键名称, 系统会自动生成一个
ALTER TABLE employee ADD [CONSTRAINT emp_dept_fk] FOREIGN KEY (dept_id) REFERENCES department (id);
④级联删除
如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作。
#级联删除
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
);
2.多表关系设计
实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系。
①一对多关系
● 一对多关系（1:n）
例如：班级和学生，部门和员工，客户和订单，分类和商品
● 一对多建表原则
在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
②多对多关系
● 多对多（m:n）
例如：老师和学生，学生和课程，用户和角色
● n 多对多关系建表原则
需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。
③一对一关系
● 一对一（1:1）
在实际的开发中应用不多.因为一对一可以创建成一张表。
● 一对一建表原则
外键唯一 主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE
④设计 省&市表
省和市之间的关系是 一对多关系,一个省包含多个市
#创建省表 (主表,注意: 一定要添加主键约束)
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)
);
⑤设计 演员与角色表
演员与角色 是多对多关系, 一个演员可以饰演多个角色, 一个角色同样可以被不同的演员扮演
#创建演员表
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);
3.多表查询
数据准备
#分类表 (一方 主表)
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 category , products;
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
②多表查询的分类
a.内连接查询
内连接的特点
● 通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示
● 比如通过: 从表的外键 = 主表的主键 方式去匹配
隐式内连接
from子句 后面直接写 多个表名 使用where指定连接条件的 这种连接方式是 隐式内连接.使用where条件过滤无用的数据.
SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;
#查询所有商品信息和对应的分类信息
#隐式内连接
SELECT * FROM products,category WHERE category_id = cid;
#查询商品表的商品名称和价格,以及商品的分类信息
#通过给表起别名的方式, 方便我们的查询
SELECT
p.`pname`,
p.`price`,
c.`cname`
FROM products p , category c WHERE p.`category_id` = c.`cid`;
显式内连接
使用 inner join …on 这种方式, 就是显式内连接
#inner 可以省略
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
#查询所有商品信息和对应的分类信息
#显式内连接查询
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;
b.外连接查询
左外连接
● 左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略
● 左外连接的特点
以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
如果匹配不到, 左表中的数据正常展示, 右边的展示为null.
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件;
#左外连接查询
SELECT * FROM category c LEFT JOIN products p ON c.`cid`= p.`category_id`;
c.右外连接
● 右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略
● 右外连接的特点
以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据
如果匹配不到，右表中的数据正常展示, 左边展示为null
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件;
#右外连接查询
SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;
各种连接方式的总结
● 内连接: inner join , 只获取两张表中 交集部分的数据.
● 左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分
● 右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分
4.子查询 (SubQuery)
①子查询概念
● 一条select 查询语句的结果, 作为另一条 select 语句的一部分
特点
● 子查询必须放在小括号中
● 子查询一般作为父查询的查询条件使用
子查询常见分类
● where型 子查询: 将子查询的结果, 作为父查询的比较条件
● from型 子查询 : 将子查询的结果, 作为 一张表,提供给父层查询使用
● exists型 子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
②子查询的结果作为查询条件
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
# 通过子查询的方式, 查询价格最高的商品信息
#1.先查询出最高价格
SELECT MAX(price) FROM products;
#2.将最高价格作为条件,获取商品信息
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);
③子查询的结果作为一张表
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;
注意：当子查询作为一张表的时候，需要起别名，否则无法访问表中的字段。
④子查询结果是单列多行
子查询的结果类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
# 查询价格小于两千的商品,来自于哪些分类(名称)
#先查询价格小于2000 的商品的,分类ID
SELECT DISTINCT category_id FROM products WHERE price < 2000;
#在根据分类的id信息,查询分类名称
#报错: Subquery returns more than 1 row
#子查询的结果 大于一行
SELECT * FROM category
WHERE cid IN (SELECT DISTINCT category_id FROM products WHERE price < 2000);
⑤子查询总结
● 子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用.
● 子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名).
5.数据库设计
①数据库三范式
概念: 三范式就是设计数据库的规则.
● 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式
● 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF） ， 其余范式以此类推。一般说来，数据库只需满足第三范式(3NF）就行了。
②第一范式 1NF
概念:
● 原子性, 做到列不可拆分
● 第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分, 如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。
③第二范式 2NF
概念:
● 在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。
● 一张表只能描述一件事.
④第三范式 3NF
概念:
● 消除传递依赖
● 表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放
⑤数据库反三范式
● 反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
● 浪费存储空间,节省查询时间 (以空间换时间)
● 设计数据库时，某一个字段属于一张表，但它同时出现在另一个或多个表，且完全等同于它在其本来所属表的意义表示，那么这个字段就是一个冗余字段
使用场景
当需要查询“订单表”所有数据并且只需要“用户表”的name字段时, 没有冗余字段 就需要去join连接用户表,假设表中数据量非常的大, 那么会这次连接查询就会非常大的消耗系统的性能.这时候冗余的字段就可以派上用场了, 有冗余字段我们查一张表就可以了.
总结
创建一个关系型数据库设计，我们有两种选择
● 尽量遵循范式理论的规约，尽可能少的冗余字段，让数据库设计看起来精致、优雅、让人心醉。
● 合理的加入冗余字段这个润滑剂，减少join，让数据库执行性能更高更快
四、MySQL索引&视图&存储过程
1.MySQL 索引
①概念
● 在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询和运行更加高效。
● 如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
常见索引分类
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 涉及的列上建立索引。
索引的优点
● 大大的提高查询速度
● 可以显著的减少查询中分组和排序的时间。
索引的缺点
● 创建索引和维护索引需要时间，而且数据量越大时间越长
● 当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度
2.MySQL 视图
①概念
1.视图是一种虚拟表。
2.视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
3.向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
4.视图向用户提供基表数据的另一种表现形式
②视图的作用
● 权限控制时可以使用
比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的作用
● 简化复杂的多表查询
视图 本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)
视图主要就是为了简化多表的查询
③视图的使用
a.创建视图
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`;
b.通过视图进行查询
#查询各个分类下的商品平均价格
#通过 多表查询
SELECT
cname AS '分类名称',
AVG(p.`price`) AS '平均价格'
FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`
GROUP BY c.`cname`;
# 通过视图查询 可以省略连表的操作
SELECT
cname AS '分类名称',
AVG(price) AS '平均价格'
FROM products_category_view GROUP BY cname;
c.视图与表的区别
● 视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示
● 通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列 经过计算得到的结果,不允许更新）
● 删除视图，表不受影响，而删除表，视图不再起作用
3.MySQL 存储过程
①概念
● MySQL 5.0 版本开始支持存储过程。
● 存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
● 简单理解: 存储过程其实就是一堆 SQL 语句的合并。中间加入了一些逻辑控制。
②存储过程的优缺点
● 优点:
存储过程一旦调试完成后，就可以稳定运行，（前提是，业务需求要相对稳定，没有变化）
存储过程减少业务系统与数据库的交互，降低耦合，数据库交互更加快捷（应用服务器，与数据库服务器不在同一个地区）
● 缺点:
在互联网行业中，大量使用MySQL，MySQL的存储过程与Oracle的相比较弱，所以较少使用，并且互联网行业需求变化较快也是原因之一
尽量在简单的逻辑中使用，存储过程移植十分困难，数据库集群环境，保证各个库之间存储过程变更一致也十分困难。
阿里的代码规范里也提出了禁止使用存储过程，存储过程维护起来的确麻烦；
③存储过程的创建方式
a.方式1
创建商品表与订单表
#商品表
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 -- 开始编写存储过程
-- 要执行的操作
END $$ -- 存储过程结束
DELIMITER $$
CREATE PROCEDURE goods_proc()
BEGIN
select * from goods;
END $$
调用存储过程
call 存储过程名
#调用存储过程 查询goods表所有数据
call goods_proc;
b.方式2
1.IN 输入参数：表示调用者向存储过程传入值
CREATE PROCEDURE 存储过程名称(IN 参数名 参数类型)
2.创建接收参数的存储过程
DELIMITER $$
CREATE PROCEDURE goods_proc02(IN goods_id INT)
BEGIN
DELETE FROM goods WHERE gid = goods_id ;
END $$
3.调用存储过程 传递参数
#删除id为2的商品
CALL goods_proc02(2);
c.方式3
1.变量赋值
SET @变量名=值
2.OUT 输出参数：表示存储过程向调用者传出值
OUT 变量名 数据类型
3.创建存储过程
#创建存储过程 接收参数插入数据, 并返回受影响的行数
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 $$
4.调用存储过程
# 调用存储过程插入数据,获取返回值
CALL orders_proc(1,2,30,@out_num);
#返回值为1，表示插入成功
4.MySQL触发器
①概念
触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行。
②触发器创建的四个要素
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
$ -- 结束标记
向商品中添加一条数据
# 向商品中添加一条数据
INSERT INTO goods VALUES(1,'book',40);
在下订单的时候，对应的商品的库存量要相应的减少，卖出商品之后减少库存量。
-- 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$
向订单表中添加一条数据
INSERT INTO orders VALUES(1,1,25);
goods表数据随之减一
5.DCL(数据控制语言)
MySql默认使用的都是 root 用户，超级管理员，拥有全部的权限。除了root用户以外，我们还可以通过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';
% 表示 用户可以在任意电脑登录 mysql服务器.
②用户授权
GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
#给admin1用户分配对db数据库中products表的 操作权限：查询
GRANT SELECT ON db.products TO 'admin1'@'localhost';
#给admin2用户分配所有权限，对所有数据库的所有表
GRANT ALL ON *.* TO 'admin2'@'%';
③查看权限
SHOW GRANTS FOR '用户名'@'主机名';
#查看root用户的权限
SHOW GRANTS FOR 'root'@'localhost';
④删除用户
DROP USER '用户名'@'主机名';
#删除 admin1 用户
DROP USER 'admin1'@'localhost';
⑤查询用户
#选择名为 mysql的数据库, 直接查询 user表即可
#查询用户
SELECT * FROM USER;
6.数据库备份&还原
备份的应用场景 在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。 这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。
1.进入到Mysql安装目录的 bin目录下, 打开DOS命令行.
mysqldump -u 用户名 -p 密码 数据库 > 文件路径
2.执行备份, 备份db中的数据 到D盘的 db.sql 文件中
mysqldump -uroot -p123456 db > D:/db.sql
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
