MySQL
一、MySQL数据库相关概念
1.1 什么是MySQL?
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。
1.2 MySQL的优点?
数据库体积小、速度快、总体拥有成本低、开放源代码,其有着广泛的应用,一般中小型网站的开发都选择 MySQL 作为网站数据库。由于其社区版的性能卓越,因此搭配 PHP 和 Apache 服务器可组成良好的开发环境。
二、MySQL的分类
SQL分类:
- DDL(Data Definition Language)数据定义语言,用来操作数据库对象:数据库、表、列等
- DML(Data Manipulation Language)数据操作语言,用来对数据库中表的数据进行增删改
- DQL(Data Query Language)数据查询语言,用来查询数据库中表的记录(数据)
- DCL(Data Control Language)数据控制语言,用来定义数据库的访问权限和安全级别,以及创建用户
补充:
mysql cmd远程连接命令
mysql -u用户名 -p密码 -h要连接的mysql服务器的ip地址(默认127.0.0.1) -P端口号(默认3306)
mysql注释
- 单行注释:
-- 注释内容 或者 # 注释内容(Mysql特有)
- 多行注释:
/* 注释 */
2.1 DDL–操作数据库
- 添加数据库
CREATE DATABASE IF NOT EXISTS 数据库名;
- 删除数据库
DROP DATABASE IF EXISTS 数据库名;
- 使用数据库
USE 数据库名;
- 查看当前使用的数据库
SELECT DATABASE();
2.2 DDL-- 操作表(CRUD)
- 创建表(Create)
create table 表名(
字段名1 数据类型1,
字段名2 数据类型2,
...
字段名n 数据类型n
);
注意:最后一行末尾,不能加逗号
- 查询表(Retrieve)
SHOW TABLES
DESC 表名; # 查看表结构
- 修改表(Update)
ALTER TABLE 表名 RENAME TO 新的表名;# 修改表名
ALTER TABLE 表名 ADD 列名 数据类型;# 添加一列
ALTER TABLE 表名 MODIFY 列名 新数据类型;# 修改数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;# 修改列名和数据类型
ALTER TABLE 表名 DROP 列名; # 删除列
- 删除表(Delete)
DROP TABLE 表名;
DROP TABLE IF EXISTS 表名;
2.3 DML对数据的操作
- 查看数据
SELECT 字段 FROM 表名 [WHERE 条件]
- 添加数据
INSERT INTO <表名> [ <列名1> [ , … <列名n>] ] VALUES (值1) [… , (值n) ];
- 修改数据
UPDATE 表名 SET 列名1=值1 列名2=值2 [WHERE 条件];
UPDATE stu SET sex='男' WHERE `name`=1;
- 删除数据
DELETE FROM 表名 [WHERE 条件];
DELETE FROM stu WHERE NAME='7';
2.4 DQL 对数据进行查询操作
- 基础查询,查询多个字段
SELECT 字段列表 FROM 表名;
SELECT * FROM 表名;
- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
- 起别名
AS
SELECT name,math AS 数学成绩,english AS 英语成绩 FROM stu;
- 条件查询(WHERE)
- 排序查询(ORDER BY)
- 排序方式:
- ASC:升序排列(默认值)
- DESC:降序排列
- 注意:如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名 排序方式
- 聚合函数:将一列数据作为一个整体,进行纵向计算
SELECT 聚合函数名(列名) FROM 表;
注意:null值不参与所有聚合函数运算
- 分组查询(GROUP BY)
1、分组查询语法
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义
where和having区别:
- 执行时机不一样:where是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过滤。
- 可判断的条件不一样:where不能对聚合函数进行判断,having可以
执行顺序:where>聚合函数>having
- 分页查询(LIMIT)
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数;
- 起始索引:从0开始
- 计算公式:起始索引=(当前页码-1)*每条显示的条数
三、约束
3.1 概念和分类
-
概念
- 约束时作用于表中列上的规则,用于限制加入表的数据
- 约束的存在保证了数据库中数据的正确性、有效性和完整性
-
分类
约束名称 | 描述 | 关键字 |
---|---|---|
非空约束 | 保证列中所有数据不能有null值 | NOT NULL |
唯一约束 | 保证列中所有数据各不相同 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
检查约束 | 保证列中的值满足某一条件 | CHECK |
默认约束 | 保存数据时,未指定值则采用默认值 | DEFAULT |
外键约束 | 外键用来让两个表的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
3.2 非空约束
概念:非空约束用于保证列中所有数据不能有NULL值
语法:
(1)添加约束
CREATE TABLE 表名(
列名 数据类型 NOT NULL
);
建完表后添加非空约束
ALTER TABLE 表名 MODiFY 字段名 数据类型 NOT NULL;
(2)删除约束
ALTER TABLE 表名 MODIFY 字段名 数据类型;
3.3 外键约束
概念:外键用来让两个表的数据之间建立链接,保证数据的一致性和完整性
语法:
(1)添加约束
CREATE TABLE 表名(
列名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES 主表(主表列名);
(2)删除约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
四、数据库设计
4.1 数据库设计概念
- 数据库设计就是根据业务系统的具体需求,结合我们所选用的DBMS,为这个业务系统构造出最优的数据存储模型。
- 建立数据库中的表结构以及表与表之间的关联关系的过程。
- 有哪些表?表里有哪些字段?表和表之间有什么关系?
4.2 数据库设计的步骤
- 需求分析(数据是什么?数据具有哪些属性?数据与属性的特点是什么)
- 逻辑分析(通过ER图对数据库进行逻辑建模,不需要考虑我们所选用的数据库管理系统)
- 物理设计(根据数据库自身的特点把逻辑设计转换为物理设计)
- 维护设计(对新的需求进行建表、表优化)
4.3 表关系
- 一对一
身份证和人(一个身份证对应一个人,一个人对应一个身份证)
用户和用户详情(一对一关系多用于表拆分,将一个实体中经常使用的字段放一张表,不经常使用的字段放另一张表,用于提升查询性能)
- 一对多
部门和员工(一个部门对应多个员工,一个员工对应一个部门)
- 多对多
商品和订单(一个商品对应多个订单,一个订单包含多个商品)
实现方式
1、一对多实现方式
- 在多的一方建立外键关联一的一方主键
2、多对多实现方式
- 建立第三张中间表
- 中间表至少包含2个外键,分别关联双方主键
3、一对一实现方式
- 在任意一方建立外键,关联对方主键,并设置外键唯一
五、多表查询
5.1 什么是多表查询?
多表查询:从多张表查询数据
SELECT * FROM 表名1,表名2... [WHERE 条件];
5.2 连接查询分类
-
- 内连接:相当于查询A B交集数据
- 外连接:
- 左外连接:相当于查询A表所有数据和交集部分数据
- 右外连接:相当于查询B表所有数据和交集部分数据
- 子查询
5.3 内连接
内连接分为隐式内连接和显示内连接
- 隐式内连接
SELECT 字段列表 FROM 表1,表2... WHERE 条件;
- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
5.4 外连接:
- 左外连接:相当于查询A表所有数据和交集部分数据(日常使用)
SELECT 字段列表 表1 LEFT [OUTER] JOIN 表2 ON 条件
- 右外连接:相当于查询B表所有数据和交集部分数据
SELECT 字段列表 表1 RIGHT [OUTER] JOIN 表2 ON 条件
5.5 子查询:
查询中嵌套查询,称嵌套查询为子查询。
子查询根据查询结果不同,作用不同,分为三类:
- 单行单列:作为条件值,使用=、!=、>、<等进行条件判断
SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);
查询工资高于某个员工的其他员工
SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name = "员工name");
- 多行单列:作为条件值,使用in等关键字进行条件判断
SELECT 字段列表 FROM 表 WHERE 字段名 in(子查询);
查询财务部和市场部所有员工的信息
SELECT * FROM emp WHERE dep_id = (SELECT did FROM dept WHERE dname ='财务部' OR dname = '市场部');
- 多行多列:作为虚拟表
SELECT 字段列表 FROM (子查询) WHERE 条件;
查询入职日期是2011-11-11之后的员工信息和部门信息
SELECT * FROM (SELECT * FROM emp WHERE join_date > '2011-11-11') t1,dept WHERE t1.dep_id = dept.did;
六、事务
6.1 事务简介
- 数据库的事务是一种机、一个操作序列,包含了一组数据库操作命令
- 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求。即这一组数据库命令要么同时成功,要么同时失败
- 事务是一个不可分割的工作逻辑单元
-- 开启事务
START TRANSACTION;
或者 BEGIN;
-- 开启事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 提交事务
commit;
6.2 事务四大特征
- 原子性(Atomicity):事务是不可分割的最小操作单位,要么同时成功,要么同时失败
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态
- 隔离性(Isolation):多个事务之间,操作的可见性
- 持久性(Durability):事务一旦提交或者回滚,它对数据库中的数据的改变就是永久的
-- 1.查询事务的默认提交方式
SELECT @@autocommit;
-- 2.修改事务的提交方式 手动提交
SET @@autocommit = 0;
在你坚持不住的时候,记得告诉自己:想一千次,不如去做一次。华丽地跌倒,胜过无谓地徘徊。任何时候你不放弃,一切都还有可能。只要你满怀希望,就会所向披靡。