数据库：MySQL 基础（二）

最新推荐文章于 2021-02-07 15:35:55 发布

Roe D.

最新推荐文章于 2021-02-07 15:35:55 发布

阅读量363

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/roe_d/article/details/104460183

版权

数据库专栏收录该内容

9 篇文章 1 订阅

订阅专栏

DQL查询

排序查询：order by
- select * from student order by english desc, math desc
- 先安照英语成绩排名，如果英语成绩一样，再按数学成绩排名
- asc :升序，默认的排序规则，可以省略
- desc：降序
聚合函数：对某一列进行纵向计算
- 计算一列个数：count(列名)，count(*)
  - 如果数据为空即NULL，那么不会进行统计
    解决方法：
    - count(*) 只要这一列的数据有一个不为空，就算进去
    - count(不可能为null的列（主键）)
    - ifnull()
- 最大值：max(列名)
- 最小值：min(列名)
- 平均值：avg（列名）
- 求和:sum(列名)

SELECT COUNT(*) FROM student2;
SELECT SUM(math) FROM student2;

分组查询:group by 分组字段
- 注意：
  - 使用分组之后，查询的字段一般只包含：分组字段，聚合函数
    - 如果不是分组字段，那么就会取每个分组的第一条数据
  - 可以使用多个聚合函数
  - where和having的区别
    - where：分组前进行筛选，后面的条件不是聚合函数
    - having：分组后进行筛选，面的条件一般都是聚合函数

在这里插入图片描述

分页查询：’‘是MySQL方言’’
- 目的：提高用户体验，提高传输效率（响应效率）

-- limit 起始索引,每页显示条数;
    		
SELECT * FROM student2 LIMIT 0,3; -- 第1页：0，1，2
    		
SELECT * FROM student2 LIMIT 3,3; -- 第2页：3，4，5
    		
SELECT * FROM student2 LIMIT 6,3; -- 第3页：6，7，8
    		
-- 第10页：（10-1）*3=27
SELECT * FROM student2 LIMIT 27,3;
-- 公式：（当前页码-1）*每页显示条数

查询英语成绩前3的同学

  SELECT * FROM student3 ORDER BY english DESC LIMIT 0,3;
  SELECT * FROM student3 ORDER BY english DESC LIMIT 3;

在这里插入图片描述

2 约束

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。
主键约束:primary key
- 一般叫主键索引，主键

注意：
1）. 含义：非空且唯一
2）. 一般情况下一张表只能有一个字段为主键
而且一般主键的列名通常为id
3）. 主键就是表中记录的唯一标识
在创建表时，添加主键约束【常用】

create table stu(
    			id int primary key,-- 给id添加主键约束
    			name varchar(20)
    		);

删除主键

-- 错误 alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;

创建完表后，添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;

非空约束：not null：某一列的值，不能为空

1. 创建表时添加约束【常用】
    		CREATE TABLE stu(
    			id INT,
    			NAME VARCHAR(20) NOT NULL -- name为非空
    		);
    	2. 创建表完后，添加非空约束
    		ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
    
    	3. 删除name的非空约束
    		ALTER TABLE stu MODIFY NAME VARCHAR(20);

唯一约束：unique：某一列的值不能重复
- 唯一约束：一般叫唯一索引
- 注意：列可以有null值，但是只能有一个null的记录

在创建表时，添加唯一约束【常用】

CREATE TABLE stu(
    			id INT,
    			phone_number VARCHAR(20) not null UNIQUE -- 手机号
    		);

删除唯一约束[注意]

ALTER TABLE stu DROP INDEX phone_number;【唯一索引】

在表创建完后，添加唯一约束

ALTER TABLE stu MODIFY phone_number VARCHAR(20)  UNIQUE;

自动增长：用于数值类型的数值增长,一般结合主键使用
自动增长是根据上一个值来增长

在创建表时，添加主键约束，并且完成主键自增长【常用】
create table stu(
    id int primary key auto_increment,-- 给id添加主键约束
     name varchar(20)
 );

在这里插入图片描述

删除自动增长：

ALTER TABLE student3 MODIFY id INT;

表创建完后添加自动增长

ALTER TABLE student3 MODIFY id INT AUTO_INCREMENT;

外键约束
- 注意：
  - 外键不是必须建立的约束，当表与表的关系比较多时部建议建立外键（10表）
  - 外键缺点：影响性能
  - 有些公司的项目没有设置外键，一般都是为了确保性能不受影响
  - 建议：进入公司后，先观察之前的模块有没有建立外键的习惯，没有就不使用，有就使用。

在创建表时，可以添加外键[常用]
外键一般关联的都是主表（部门表）的主键（id）
* 语法：

create table 表名(
    ....
    外键列 --部门id
    constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
    );


CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(30),-- 部门名称
dep_location VARCHAR(30) -- 部门地址
);

CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT ,
dep_id INT ,-- 外键对应主表的主键
CONSTRAINT emp FOREIGN KEY (dep_id) REFERENCES department(id)
);

INSERT INTO department VALUES(1,'研发部','广州'),(2,'销售部','深圳'),(3,'经理','上海');
INSERT INTO employee(NAME,age,dep_id)VALUES
('小明',20,1),('小章',24,1),
('小開',18,2),('小烂',20,3),
('小类',21,2),('小然',29,1);

employee表
在这里插入图片描述
department表

将employee表中的 dep_id 进行外键约束，跟 department表中的 id 列进行关联，所以想要删除 department 的数据是不行的，且employeet表添加数据必须根据department表的值限制，比如将dep_id 改成 5 那是不行的
在这里插入图片描述

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
ALTER TABLE employee DROP FOREIGN KEY emp

创建表之后，添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

级联操作

添加级联操作：

语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
    FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;

分类：
1. 级联更新：ON UPDATE CASCADE
2. 级联删除：ON DELETE CASCADE

3 索引【拓展】

索引添加一般是针对数据量比较大的表，（上万条数据）
目的：提高查询效率，对增加了索引的列进行条件查询效率更高。
目：100页
先查索引文件：
- name 设置了索引
- 查询张三：100条
主键索引
- 包含主键约束的概念同时可以作为索引，如果对主键索引的字段进行条件查询时，效率更高。
- select * from student where id=1;
- select * from student where name=‘张三’;
- 第一个sql的查询效率高于第二个sql
- ```
-- 添加
ALTER TABLE student ADD PRIMARY KEY (id);【常用】
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
-- 删除
ALTER TABLE student drop PRIMARY KEY;
```
唯一索引
- 包含唯一约束的概念同时可以作为索引，如果对唯一索引的字段进行条件查询时，效率更高。
- select * from student where name=‘张三’;
- ```
-- 添加
ALTER TABLE student ADD UNIQUE (name);【常用】
ALTER TABLE stu MODIFY name INT UNIQUE;
-- 删除
ALTER TABLE student drop INDEX name;
```

普通索引

仅仅只是提高查询效率

-- 添加
ALTER TABLE student ADD INDEX (name);【常用】
-- 删除
ALTER TABLE student drop INDEX name;

组合索引
- 多个字段组合为一个索引，当以组合索引的全部字段为条件或者以最左边的字段的为条件进行查询时效率更高
- select * from student where name =‘张三’ and age =18;
- select * from student where name =‘张三’ ;
- select * from student where age =18 ;
- ```
-- 添加
ALTER TABLE student ADD INDEX inx_name_age(name,age);【常用】
-- 删除
ALTER TABLE student drop INDEX inx_name_age;
```
查询效率高到低：主键索引>唯一索引>普通索引>组合索引

4 多表关系

数据库设计：设计表【开发人员需要做的一件事情】
- 一对一（了解）：
  - 如：人，身份证
  - 建表原则：在任意一方建立唯一外键指向另一方的主键
- 一对多
  - 如：部门和员工
  - 建表原则：在多的一方建立外键指向少的一方的主键【重点重点】
- 多对多
  - 如：学生，选课
  - 建表原则：需要建立中间表，至少有两个字段，作为外键指向两个主表的主键

案例：
在这里插入图片描述

--创建分类表
CREATE TABLE tab_catagory(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100) NOT NULL UNIQUE
);

-- 创建线路表
CREATE TABLE teb_route(

rid INT PRIMARY KEY AUTO_INCREMENT,
rname VARCHAR(100) NOT NULL UNIQUE,
price DOUBLE,
rdate DATE,
cid INT,
rdata DATE,
cid2 INT,
FOREIGN KEY (cid2) REFERENCES tab_catagory(cid)
);

-- 创建用户表
CREATE TABLE tab_user(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) UNIQUE NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
NAME VARCHAR(100),
birthday DATE,
sex CHAR(1) DEFAULT'男',-- 默认值男
telephone VARCHAR(11),
mail VARCHAR(100)
);
-- 创建中间表
CREATE TABLE tab_middle(
rid INT ,-- 线路id
DATE DATETIME,
uid INT,-- 用户id
PRIMARY KEY(rid,uid),-- 创建复合主键
FOREIGN KEY(rid) REFERENCES tab_route(rid),
FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

5 三大范式

函数依赖：A属性值可以唯一确定 B属性的值，则称，B依赖于A
完全函数依赖：如果A是一个属性组，则B属性值的确认需要依赖A属性组中所有的属性值，则B完全依赖A：分数-----》（学号，课程名称）
部分函数依赖：A属性组，其中的部分值可以确定B属性值，
传递函数依赖：A—》B，B—》C
码：如果在一张表中，一个属性或者属性组，被其他所有属性完全依赖，则称这个属性称（属性组）为该表的码，例：学号和课程，
主属性：码属性组中的所有属性
非主属性：除码属性组的属性

此时码《学号，课程》，分数完全依赖于属性组《学号，课程》，姓名，系名，系主任部分依赖《学号，课程》系主任传递依赖系名
在这里插入图片描述

范式：设计数据库的规范。
分类：
第一范式(1NF)
- 列原子的不可拆分的，没有合并列
- 所有的表都满足第一范式
第二范式(2NF)：基于第一范式，且非码属性必须完全依赖于候选码（即消除部分依赖）
- 也是指表中不能有冗余（重复）数据，例：姓名，系名，系主值部分依赖主属性（学号），所以需要操作变成下图：
第三范式((3NF)：在第二范式基础上，任何非主属性不依赖于其他非主属性（即消除传递依赖），例：系主任没有依赖主属性学号，而是传递依赖系名
- 如果某个数据可以单独进行管理（增删改），那么这些数据应该为一个独立表
注意：
- 有些时候公司的表没有完全满足第二或者第三范式，一般是由特殊的业务要求。