0


MySQL——基础篇


一、数据库概述

1.数据库的特点

● 实现数据持久化到本

● 使用完整的管理系统统一管理,可以实现结构化
查询,方便管理

2.数据库的概念

数据库(DataBase)为了方便数据的存储和管理,它将数据按照特定的规则存储在磁盘上,就是一个存储数据的仓库

**●DB:数据库(DataBase) **

存储数据的容器,它保存了一系列有组织的数据

**●DBMS:数据库管理系统(DataBase Management System) **

又称为数据库软件或数据库产品,用于创建或管理DB

3.常见的数据库产品

国外:

MySQL 快捷、可靠 开源、免费

Oracle: 功能强大,收费.

SQL Server(微软): 只能安装在Windows操作系统

DB2 (IBM): 适合处理海量数据,收费

国内:

南大通用GBASE: 天津南大通用数据技术股份有限公司

达梦: 武汉达梦数据库股份有限公司

人大金仓: 北京人大金仓信息技术股份有限公司

神通: 神舟通用公司

二、MySQL数据库

1.Mysql数据库概述

MySQL是一个关系型数据库管理系统由瑞典MySQL AB 公司开发,目前属于Oracle旗下产品,MySQL流行的关系型数据库管理系统。

2.Mysql数据库特点

● MySql是一种关系数据库管理系统。

● MySql软件是一种开放源码软件,你可以修改源码来开发自己的 Mysql 系统。

● MySql数据库服务器具有快速、可靠和易于使用的特点。

● MySql使用标准的sql语言,并且支持多种操作系统,支持多种语言.

** mysql分为商业版与社区版 **

● MySQL商业版是由MySQL AB公司负责开发与维护,需要付费才能使用

● MySQL社区版是由分散在世界各地的MySQL开发者、爱好者一起开发与维护,可以免费使用

3.MySQL的安装

①MySQL安装过程

下载链接:

MySQL :: Download MySQL Community Serverhttps://dev.mysql.com/downloads/mysql/

●** 解压:**

** **解压路径自己选择

** **

●** 配置mysql环境变量:**

在刚才保存的文件夹中,点击到bin目录,复制此路径D:\ProgramFiles\mysql-8.0.20-winx64\bin

在环境变量中选定path进行编辑,新建粘粘刚才复制的路径

●** mysql初始化和启动:**

以管理员身份运行cmd:

cd到mysql的安装目录:

 ![](https://i-blog.csdnimg.cn/blog_migrate/fcbc05aa35d30bd5e1efa8b41f214b4b.png)

初始化MySQL数据库:

命令:mysqld --initialize-insecure
作用:初始化数据库,并设置默认root密码为空

命令执行后,在mysql安装目录会生成一个data目录

为Windows系统安装MySQL服务

命令:mysqld install
作用:为Windows系统安装MySQL服务,默认服务名为:mysql

输入sc query mysql,查看一下名为mysql的服务:

启动MySQL服务
命令:net start mysql
作用:启动MySQL服务,注意此时的“mysql”表示服务名

到此mysql8安装完成


删除服务命令:sc delete mysql 卸载时使用

●** 为root账号设置密码**

上面已经将root账号设置为空,可以使用root用户直接登录,然后修改密码

②命令行方式连接mysql

登录:mysql [-hlocalhost -P3306](本机可省略) -uroot -p(可以直接写密码,不能有空 格)

-h:主机名 

-P:端口号 

-u:用户名 

-p:密码 

退出:exit

4.MySQL的常用命令

查看当前所有的数据库:show databases;

选择指定的库:use 库名

查看当前的所有表:show tables;

查看其他库的所有表:show tables from 库名;

查看mysql版本 select version();

5.安装可视化客户端工具

SQLyog / Navicat

三、SQL

1.SQL概述

结构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。

2.SQL优点

不是某个特定数据库供应商专有的语言,几乎所有DBMS都支持SQL ,简单易学,灵活使用,可以进行非常复杂和高级的数据库操作

四、SQL--DDL

1.DDL语言概述

数据(结构)定义语言DDL(Data Definition Language),是用于创建和修改数据库表结构的语言。

2.常用的语句

create ,alter,drop,rename

3.创建,删除数据库

-- 创建数据库 并设置字符集编码
CREATE DATABASE  schooldb CHARSET utf8;
--  创建数据库 并设置字符集编码   并判断数据库是否存在
CREATE DATABASE IF NOT EXISTS test CHARSET utf8;

-- 删除数据库
DROP DATABASE test;

-- mysql数据库一旦创建不能修改,只能修改字符集编码
ALTER DATABASE test CHARSET utf8;

创建数据库并设置编码格式

**CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8] **

删除数据库

**DROP DATABASE 数据库名 / [IF EXISTS数据库名]; **

修改字符集

ALTER DATABASE 数据库名 CHARSET gbk;

4.数据库表的基本概念

①数据表

表(table)是数据存储的最常见和最简单的形式,是构成关系型数据库基本元素

表的最简单形式是由行和列组成,分别都包含着数据。 每个表都有一个表头和表体,表头定义表名和列名 。

表中的被看作是文件中的记录,表中的被看作是这些记录的字段

②字段

字段是表里的一列,用于保存每条记录的特定信息。

**如客户订单表的字段包括“订单 ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。 **

数据表的一列包含了特定字段的全部信息。

③记录

记录也被称为一行数据,是表里的一行数据

5.设计表

①对于具体的某一个表,在创建之前,需要确定表的下列特征:

● 表名(表信息)

● 表中的字段

● 字段的数据类型和长度

● 哪些约束

②设计表(数据类型)

char(n) 长度为n的定长字符串,最大长度255个字符

varchar(n) 最大长度为n的可变长字符串

date 日期,包含年月日

datetime 年月日 时分秒


整数

  signed              有符号 默认是有符号 

unsigned 无符号


**浮点 **

decimal

数据类型(M,D)

M: 精度,数据的总长度;

D: 标度,小数点后的长度;


**TEXT列字符字符串 **

有4种TEXT类型:

TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT

它的长度:

TINYTEXT: 最大长度255个字符(2^8-1)

TEXT: 最大长度65535(2^16-1)

MEDIUMTEXT: 最大长度16777215(2^24-1)

LONGTEXT : 最大长度4294967295(2^32-1)

**主键: **

在一张表中代表唯一的一条记录,不能为空,不能重复

**约束: **

PRIMARY KEY 设置主键约束

NOT NULL 不能为空约束

UNIQUE 唯一性约束

检查约束 设置条件

外键约束

主键自动增长,设置为自动增长时,只能为整数类型:

AUTO_INCREMENT 

默认值:

DEFAULT default_value

**字段注释: **

comment '注释'

③创建表语法

**CREATE TABLE 表名(列名 数据类型 [约束] [默认值] [ 注释],......) **

**CREATE TABLE t_user( **

** id INT [PRIMARY KEY NOT NULL AUTO_INCREMENT], **

** number INT(5) NOT NULL, **

** stuname VARCHAR(10) NOT NULL, **

** age INT(3) CHECK(age>18), **

** birthday DATE, **

** weight DOUBLE, **

** opertime datetime, **

** [CONSTRAINT 约束名 约束规则] **

)

④删除表,修改表名

删除表

**DROP TABLE [if exists ]表名 **

修改表名

**RENAME TABLE 旧表名 TO 新表名 **

 复制表结构 

CREATE TABLE 新表名 LIKE 被复制表名

CREATE TABLE student(
   num INT PRIMARY KEY AUTO_INCREMENT  COMMENT '学号 主键',
   NAME VARCHAR(10) NOT NULL COMMENT '姓名',
   gender CHAR(1) NOT NULL  DEFAULT '男'  COMMENT '性别',
   birthday DATE,
   phone CHAR(11) NOT NULL  UNIQUE,
   address VARCHAR(30),
   height DECIMAL(3,2) CHECK(height<2.60),
   reg_time  DATETIME
)
-- 删除表
DROP TABLE student

-- 修改表名
RENAME TABLE student TO stu
RENAME TABLE stu TO student

-- 复制表结构
CREATE TABLE stu LIKE student

五、SQL--DML

1.DML语言概述

数据操纵语言DML(Data Manipulation Language)

2.常用语句

insert,delete,update

3.插入数据的四种方式

方式1:

**INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n); **

方式2:

**INSERT INTO 表名 set 列名1=值1,..列名n=值n; **

方式3:

**INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2…..,值n); **

方式4:

INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)

INSERT INTO student(NAME,gender,birthday,phone)VALUES('张三','男','2003-3-5','15233336666')
INSERT INTO student(NAME,gender,birthday,phone,address,height,reg_time)
       VALUES('张三','男','2003-3-5','15233336688','汉中',1.75,NOW())
       
INSERT INTO student(NAME,gender,birthday,phone,address,height,reg_time)
       VALUES('张三丰','男','2003-3-5','15233336677','汉中',1.75,NOW()),
             ('李四','女','2003-3-3','15233336699','汉中',1.85,NOW()),
             ('王五','男','2003-4-3','15233336609','汉中',1.95,NOW())        
            
INSERT INTO student SET NAME = '赵六',gender='女',phone='15777778888'

INSERT INTO stu(NAME,gender,birthday,phone,address,height,reg_time) 
    SELECT NAME,gender,birthday,phone,address,height,reg_time FROM student

4.修改数据

UPDATE 表名 SET 列名 = ‘新值’WHERE 条件

5.删除数据

DELETE FROM 表名 WHERE 条件

TRUNCATE TABLE 表名;清空整张表

 -- 修改语句      需要注意条件的准确性, 否则修改所有的数据
 UPDATE student SET address='陕西西',height=1.65,reg_time = '2003-4-5 14:20:10' WHERE num = 1
 
 -- 删除语句
 DELETE FROM stu -- 没有条件 删除所有的数据
 DELETE FROM student WHERE num  = 13 

六、SQL--DQL

1.DQL语言概述

DQL(Data Query Language)数据查询语言查询是使用频率最高的一个操作,可以从一个表中查询数据,也可以从多个表中查询数据。

2.基础查询

**● 语法: **

select 查询列表 from 表名

**● 特点: **

查询列表可以是:表中的字段、常量、表达式、函数

查询的结果是一个虚拟的表格

**● 查询结果处理: **

特定列查询:select column1,column2 from table

全部列查询: select * from table

算数运算符:+ - * /

排除重复行: select distinct column1,column2 from table

查询函数:select 函数; / 例如version()

-- 查询特定的列
 SELECT num,NAME,gender FROM student
 
 -- 查询所有的列, 在开发中一般不建议, 使用哪些列,查询哪些
 SELECT * FROM student
 
 -- sql中+ - * / 只能做算数运算  +不能连接字符串
 SELECT num+100,NAME FROM student
 
 -- 字符串函数  连接多个字符串
 SELECT num,CONCAT(NAME,':',gender)AS NAME FROM student
 
 SELECT VERSION();
 
 -- 去除查询结果中重复数据,  何为重复数据: 所有的列都相同
 SELECT DISTINCT NAME,gender,birthday FROM student
 

3.函数

①函数概述

类似于java中的方法,将一组逻辑语句事先在数据库中定义好,可以直接调用

②分类

单行函数:如concat、length、ifnull等

分组函数:做统计使用,又称为统计函数、聚合函数、组函数

③单行函数

**● 字符函数 **

length(): 获取参数值的字节个数

char_length(): 获取参数值的字符个数

concat(str1,str2,.....): 拼接字符串

upper()/lower(): 将字符串变成大写/小写

substring(str,pos,length):截取字符串 位置从1开始

instr(str,指定字符): 返回子串第一次出现的索引,如果找不到返回0

trim(str):去掉字符串前后的空格或子串,trim(指定子串 from 字符串)

lpad(str,length,填充字符):用指定的字符实现左填充将str填充为指定长度

rpad(str,length,填充字符):用指定的字符实现右填充将str填充为指定长度

replace(str,old,new): 替换,替换所有的子串

-- length():获取参数值的字节个数
 SELECT NAME,LENGTH(NAME) FROM student

 -- char_length()获取参数值的字符个数
  SELECT NAME,CHAR_LENGTH(NAME)AS NAME,gender FROM student
 
 -- concat(str1,str2,.....):拼接字符串  as 别名
 SELECT num,CONCAT(NAME,':',gender)AS NAME FROM student
 
-- upper()/lower():将字符串变成大写/小写
SELECT UPPER(NAME),LOWER(NAME) FROM student

-- substring(str,pos,length):截取字符串 位置从1开始
SELECT SUBSTRING(NAME,2,3) FROM student

-- instr(str,指定字符):返回子串第一次出现的索引(indexof()),如果找不到返回0
SELECT INSTR(NAME,'三') FROM student

-- trim(str):去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT CHAR_LENGTH(TRIM(NAME)) FROM student
SELECT  TRIM('张' FROM NAME)  FROM student
-- lpad(str,length,填充字符):用指定的字符实现左填充将str填充为指定长度
SELECT LPAD(NAME,5,'a') , RPAD(NAME,5,'b')FROM student
-- rpad(str,length,填充字符):用指定的字符实现右填充将str填充为指定长度

-- replace(str,old,new):替换,替换所有的子串
SELECT REPLACE(NAME,'i','I') FROM student

** ****● ** **逻辑处理 **

case when 条件 then 结果1 else 结果2 end; 可以有多个when

ifnull(被检测值,默认值)函数检测是否为null,如果为null,则返回指定的值,否则返回

原本的值

if函数:if else的效果 if(条件,结果1,结果2)

SELECT NAME,
       (CASE WHEN height>=1.80 THEN '高个子' ELSE '正常身高' END)AS height,
       gender 
       FROM student
       
 
SELECT NAME,
       (CASE WHEN height>=1.80 THEN '高个子'
            WHEN height>=1.60 THEN '正常身高'
            ELSE '低个子' END) AS height,
       gender 
       FROM student
         

SELECT NAME,IFNULL(address,'暂未录入')AS address FROM student

SELECT NAME,IF(height>=1.80,'高个子','正常身高') FROM student

**● 数学函数 **

round(数值): 四舍五入

ceil(数值): 向上取整,返回>=该参数的最小整数

floor(数值): 向下取整,返回<=该参数的最大整数

truncate(数值,保留小数的位数): 截断,小数点后截断到几位

mod(被除数,除数): 取余,被除数为正,则为正;被除数为负,则为负

rand(): 获取随机数,返回0-1之间的小数

-- round(数值):四舍五入
SELECT NAME,ROUND(height) FROM student
-- 指定保留小数位数
SELECT NAME,ROUND(height,1) FROM student

-- ceil(数值):向上取整,返回>=该参数的最小整数
 SELECT NAME,CEIL(height),FLOOR(height) FROM student

-- floor(数值):向下取整,返回<=该参数的最大整数
-- truncate(数值,保留小数的位数):截断,小数点后截断到几位, 不会四舍五入
SELECT NAME,TRUNCATE(height,1) FROM student

-- mod(被除数,除数):取余,被除数为正,则为正;被除数为负,则为负
SELECT NAME, MOD(num,3) FROM student

-- rand():获取随机数,返回0-1之间的小数
SELECT NAME, RAND() FROM student

**● 日期函数 **

now(): 返回当前系统日期+时间

curdate(): 返回当前系统日期,不包含时间

curtime(): 返回当前时间,不包含日期

可以获取指定的部分,年、月、日、小时、分钟、秒:

YEAR(日期列),MONTH(日期列),DAY(日期列) ,HOUR(日期列) ,MINUTE(日期列)

SECOND(日期列)

str_to_date(字符串格式日期,格式): 将日期格式的字符转换成指定格式的日期

date_format(日期列,格式): 将日期转换成字符串

datediff(big,small): 返回两个日期相差的天数

-- 日期函数
-- now()

SELECT NAME, NOW(),CURDATE(),CURTIME() FROM student

-- 日期格式化
SELECT NAME, YEAR(reg_time),MONTH(reg_time),DAY(reg_time) FROM student

-- select count(*) from student group by year(birthday)

-- 将字符串格式化为日期类型
SELECT STR_TO_DATE('2001-2-2','%Y-%m-%d') FROM student 
 
 -- 将日期格式化为指定的字符串
SELECT DATE_FORMAT(birthday,'%Y-%m') FROM student

-- 计算两个日期之间相差的天数
SELECT DATEDIFF(CURDATE(),birthday) FROM student

SELECT DATEDIFF(STR_TO_DATE('2020-1-1','%Y-%m-%d'),birthday) FROM student

④分组函数

** ● 功能:**用作统计使用,又称为聚合函数或统计函数或组函数

** ● 分类:**sum 求和、avg 平均值、max 最大值、min 最小值、count 计数 (非空)

1.sum,avg一般用于处理数值型max,min,count可以处理任何类型

2.以上分组函数都忽略null值

3.count函数的一般使用count(*)用作统计行数

4.和分组函数一同查询的字段要求是group by后的字段

4.条件查询

①条件查询概述

使用WHERE 子句,将不满足条件的行过滤掉,WHERE 子句紧随 FROM 子句

②语法

select <结果> from <表名> where <条件>

- 条件查询  select 结果列 from 表名  where  条件

SELECT * FROM student WHERE num = 1

比较

=, != 或<>, >, <, >=, <=

逻辑运算

and 与

or 或

not 非

-- and 所有条件都需要满足
SELECT * FROM student WHERE gender = '男' AND height>=1.70 AND address = '汉中'

-- or 满足一个条件即可
SELECT * FROM student WHERE gender = '男' OR height>=1.70  

-- 不等于
SELECT * FROM student WHERE gender != '男'
SELECT * FROM student WHERE gender <> '男'

③模糊查询

LIKE :是否匹配于一个模式 一般和通配符搭配使用,可以判断字符型数值或数值型

通配符:% 任意多个字符

-- 模糊查询  like  %字符%  通配符
SELECT * FROM student WHERE NAME LIKE '张%'

SELECT * FROM student WHERE NAME LIKE '%三%'

between and 两者之间,包含临界值;

in 判断某字段的值是否属于in列表中的某一项

IS NULL(为空的)或 IS NOT NULL(不为空的)

SELECT * FROM student WHERE height>=1.70 AND height<=2.0

-- BETWEEN 1.70 AND 2.0 == height>=1.70 AND height<=2.0
SELECT * FROM student WHERE height BETWEEN 1.70 AND 2.0
-- 查询身高 1.75 1.85 1.95
SELECT * FROM student WHERE height=1.75 OR height=1.85 OR height = 1.95
SELECT * FROM student WHERE height IN(1.75,1.85,1.95)

SELECT * FROM student WHERE height NOT IN(1.75,1.85,1.95)

-- 查询值为空的数据
SELECT * FROM student WHERE address  IS NULL
-- 查询值不为空的数据
SELECT * FROM student WHERE address  IS NOT NULL

** ④UNION与UNION ALL**

** ● **UNION 的语法如下:

[SQL 语句 1]

UNION

[SQL 语句 2]

** ● **UNION ALL 的语法如下:

[SQL 语句 1]

UNION ALL

[SQL 语句 2]

当使用union 时,mysql 会把结果集中重复的记录删掉,而使用union all ,mysql 会把所有的记录返回,且效率高于union 。

-- union 合并多个查询的结果,可以去除重复的数据

SELECT num,NAME,gender FROM student WHERE gender = '男'
 UNION  
SELECT num,NAME,gender FROM student WHERE gender = '女'

SELECT num,NAME,gender FROM student WHERE gender = '男'
 UNION  ALL
SELECT num,NAME,gender FROM student WHERE gender = '女'

SELECT num,NAME,gender FROM student WHERE gender = '男'
 UNION  
SELECT num,NAME,gender FROM student WHERE  height = 1.65

-- nuion all 合并多个查询结果  只是合并,不去重
SELECT num,NAME,gender FROM student WHERE gender = '男'
 UNION  ALL
SELECT num,NAME,gender FROM student WHERE  height = 1.65
  UNION ALL
SELECT num,NAME,gender FROM student WHERE  height = 1.75

5. 排序

查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC

asc代表的是升序,desc代表的是降序,如果不写,默认是升序

order by子句中可以支持单个字段、多个字段

• 数量限制

 limit子句:对查询的显示结果限制数目 (sql语句最末尾位置) 

SELECT * FROM table LIMIT offset rows;

SELECT * from table LIMIT 0,5;

-- 排序
-- 按身高排序, 默认是升序排序
SELECT * FROM student ORDER BY height 
SELECT * FROM student ORDER BY birthday ASC

-- ASC 升序(默认)  DESC降序
SELECT * FROM student ORDER BY birthday DESC
SELECT * FROM student ORDER BY height DESC

SELECT * FROM student WHERE num>0 ORDER BY height DESC

-- 多个排序条件
SELECT * FROM student WHERE num>0 ORDER BY  birthday DESC,height ASC

-- 什么排序都没有,默认按主键 升序排列
SELECT * FROM student

-- 数量限制  limit 开始位置(开始位置为0),查询的数量
-- 实际使用场景, 数据分页显示   一次只查询一部分数据, 提高查询效率
SELECT * FROM  student LIMIT 0,2   -- 1   当前页码 n
SELECT * FROM  student LIMIT 2,2   -- 2
SELECT * FROM  student LIMIT 4,2   -- 3
-- limit (n-1)*2,2

-- limit 在sql的最末尾
SELECT * FROM student WHERE num>0  ORDER BY height DESC LIMIT 0,2

6.分组查询

①语法

select 分组函数,列(要求出现在group by的后面)

from 表

[where 筛选条件]

group by 分组的列表

[having 分组后的筛选]

[order by 子句]

②注意要点

查询列表比较特殊,要求是分组函数和group by后出现的字段

分组查询中的筛选条件分为两类:

                    数据源                        源位置                    关键字 

分组前筛选 原始表 group by子句的前面 where

分组后筛选 分组后的结果集 group by的后面 having

-- 分组查询
-- 将某类数据分到一个组中进行处理  例如按性别查询  
-- 查询男生  女生各有多少人(统计数量)  例如按性别分组      每月销量
-- GROUP BY 分组条件(列名)

SELECT COUNT(*),gender  FROM student  GROUP BY gender 
SELECT SUM(height),gender  FROM student  GROUP BY gender 
SELECT AVG(height),gender  FROM student  GROUP BY gender 
SELECT MAX(height),gender  FROM student  GROUP BY gender 

-- 统计每年出生的人数
SELECT COUNT(*),DATE_FORMAT(birthday,'%Y') FROM student GROUP BY DATE_FORMAT(birthday,'%Y')

SELECT COUNT(*),gender  FROM student WHERE num>0  GROUP BY gender 

-- 分组统计名字数量  查询出哪些名字重复了.
-- where 是对原始表中的数据进行过滤
-- HAVING 在group by 后面使用  为分组的数据添加过滤条件
SELECT COUNT(*) n,NAME  FROM student GROUP BY NAME HAVING n>1

7.子查询

①含义

出现在其他语句中的select语句,称为子查询或内查询;

外部的查询语句,称为主查询或外查询

②分类

** ● **按子查询出现的位置:

from后面:支持表子查询

where:支持标量子查询,列子查询

** ● **按功能、结果集的行列数不同:

标量子查询(结果集只有一行一列)

列子查询(结果集只有一列多行)

表子查询(结果集一般为多行多列)

 -- 查询身高最高的学生   
 SELECT * FROM student WHERE height =  (SELECT MAX(height) FROM student)
 
 
 SELECT * FROM student WHERE height IN (SELECT height FROM student WHERE height = 1.65 OR height=1.75)   
 
 -- 查询姓名重复的学生    
  SELECT * FROM (SELECT NAME,COUNT(*)c FROM student GROUP BY NAME) t WHERE  t.c>1
   
   
 -- 查询身高大于平均身高的学生
  SELECT * FROM student WHERE height > (SELECT AVG(height) FROM student)

 -- 查询与李四同专业的学生
 SELECT * FROM student WHERE  majorid = (SELECT majorid FROM student WHERE NAME = '李四')

 -- 查询每个专业中身高最高的学生信息
 
 SELECT * FROM student s INNER JOIN (SELECT  MAX(height)maxheight,majorid FROM student GROUP BY majorid)t
                       ON s.height = t.maxheight AND s.majorid = t.majorid

七、多表设计

1. 数据库设计范式

①第一范式(确保每列保持原子性)

第一范式是最基本的范式,如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一范式。

**列保证原子性(列不可再分解)
联系方式: 电话/邮箱/qq...

         电话,qq,邮箱 不可分解**

②第二范式

第二范式就是要有主键,要求其他字段都依赖于主键

• 没有主键就没有唯一性,没有唯一性在集合中就定位不到这行记录,所以要主键。

• 其他字段为什么要依赖于主键?因为不依赖于主键,就找不到他们。更重要的是,其

他字段组成的这行记录和主键表示的是同一个东西,而主键是唯一的,它们只需要依

赖于主键,也就成了唯一的

总结--要有主键 其他字段必须依赖于某个列(主键列)
通过主键可以精确的定位到某行数据

③第三范式

确保每列都和主键列直接相关,而不是间接相关,要求一个数据库表中不包含已在其它表中包含的非主关键字信息

**确保每列都和主键列直接相关
不同的信息放在不同表中
如果两张表有关系的话,只需要在一张表放置另一个表的主键进行关联,关联表中其他的非主键信息不需要了 **

2. 外键

引用另外一个数据表的某条记录

** 注意!!!**

外键列类型与主键列类型保持一致

数据表之间的关联/引用关系是依靠具体的主键(primary key)和外键 (foreign key)建立起来的。

create table student(

id int not null auto_increment primary key,

num int,

name varchar(10)

majorid int,

CONSTRAINT 约束名 foreign key(majorid ) references major(id)

);

**约束名规则: **

例:FK_ForeignTable_PrimaryTable_On_ForeignColumn

/*
  学生选课关系
  
    多对多关系
  
  课程表
  
   由于一个学生可以选择多个课程,所以在学生表中添加课程外键就行不通了.
   
   需要再设计一个学生选课表(只要存储学生和课程的编号(主键)即可), 一般把这种表也称为关系表
*/

CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
)

CREATE TABLE student_course(
  id INT PRIMARY KEY AUTO_INCREMENT,
  student_num INT,
  courseid  INT,
  CONSTRAINT fk_student_student_course_on_student_num FOREIGN KEY(student_num) REFERENCES student(num),
  CONSTRAINT fk_course_student_course_on_courseid FOREIGN KEY(courseid) REFERENCES course(id)
)

3.需要注意的点

** ● **当主表中没有对应的记录时,不能将记录添加到从表

** ● **不能更改主表中的值而导致从表中的记录孤立

** ● **从表存在与主表对应的记录,不能从主表中删除该行

** ● **删除主表前,先删从表

八、关联查询

1.含义

又称多表查询,当查询的字段来自于多个表时,就会用到连接查询

2.笛卡尔乘积现象

表1有m行,表2有n行,结果=m*n

发生原因:没有有效的连接条件

如何避免:添加有效的连接条件

3.按功能分类

** ● **自连接

** ● **内连接

** ● **外连接

左外连接

右外连接

①内连接(inner join)

把满足了条件的两张表中的交集数据查询出来

语法:

Select 结果 from

表1 inner join 表2

on 表1.column1 = 表2.column2

-- 内连接, 只把满足条件的筛选出来
   SELECT * FROM student,major WHERE majorid = id
   
   SELECT 
      s.num,
      s.name,
      s.gender,
      m.name
    FROM student s INNER JOIN  major m ON s.majorid = m.id
    WHERE s.gender = '男'

②左外连接(left join)

语法:

select 结果 from

表1 left join 表2

on 表1.column1 = 表2.column2

-- 外连接  左外连接    把左边表中所有的数据查询出来, 右边表中只会查询出满足条件的
     SELECT 
      *
      FROM student s LEFT JOIN major m ON s.majorid = m.id    

③右外连接(right join)

语法:

select 结果 from

表1 right join 表2

on 表1.column1 = 表2.column2

--  右外连接 把右边所有的数据查询出来, 左边表中只会查询出满足条件的
    SELECT
      *
    FROM
      student s
      RIGHT JOIN major m
        ON s.majorid = m.id  
标签: mysql 数据库

本文转载自: https://blog.csdn.net/m0_70830675/article/details/138924556
版权归原作者 404_NOT_FOUND@ 所有, 如有侵权,请联系我们删除。

“MySQL——基础篇”的评论:

还没有评论