MySql基础篇

àì夳堔傛蜴生んèń 2023-10-13 13:28 159阅读 0赞

**目录**

1.数据库概述

2.MySQL数据库

3.SQL--DDL

数据库表的基本概念:

主键

对表的基本操作：

创建表语法:

删除表，修改表名

4.SQL--DML

插入数据

修改数据

删除数据

5.SQL--DQL

函数:

字符函数

逻辑处理

数学函数

日期函数

分组函数

条件查询

分组查询

6.多表设计\_关联查询

数据库设计范式

外键

关联查询

内连接(inner join)

左外连接(left join)

右外连接(right join)

子查询

--------------------

## 1.数据库概述 ##

学习数据库的目的：

1.实现数据持久化到本地；  
2.使用完整的管理系统统一管理，可以实现结构化查询，方便管理；

数据库（DataBase）为了方便数据的存储和管理，它将数据按照特定的规则存储在磁盘上,就是个存储数据的仓库。通过数据库管理系统，可以有效的组织和管理存储在数据库中的数据。  
数据库的相关概念  
DB：数据库（DataBase）  
存储数据的容器，它保存了一系列有组织的数据。  
DBMS：数据库管理系统（DataBase Management System）  
又称为数据库软件或数据库产品，用于创建或管理DB。

根据不同的存储类型可以分为：  
1.关系型数据库  
关系型数据库管理系统称为RDBMS  
Oracle:功能强大,收费.  
MySQL 快捷、可靠 开源、免费  
SQL Server(微软): 只能安装在Windows操作系统  
DB2 (IBM):适合处理海量数据,收费.  
2.非关系型数据库  
MongdoDB  
Redis

##    2.MySQL数据库 ##

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目  
前属于 Oracle旗下产品。MySQL 流行的关系型数据库管理系统。  
● MySql是一种关系数据库管理系统。  
● MySql软件是一种开放源码软件,你可以修改源码来开发自己的 Mysql 系统。  
● MySql数据库服务器具有快速、可靠和易于使用的特点。  
● MySql使用标准的sql语言,并且支持多种操作系统,支持多种语言.  
● mysql商业版与社区版  
● MySQL商业版是由MySQL AB公司负责开发与维护,需要付费才能使用  
● MySQL社区版是由分散在世界各地的MySQL开发者、爱好者一起开发与维护，可以免费使用

MySQL的安装(参考安装资料)  
命令行方式连接mysql  
登录：mysql \[-hlocalhost -P3306\]（本机可省略） -uroot -p（可以直接写密码，不能有空格）  
\-h：主机名  
\-P：端口号  
\-u：用户名  
\-p：密码  
退出：exit

##    3.SQL--DDL ##

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统.  
SQL优点：  
不是某个特定数据库供应商专有的语是言，几乎所有DBMS都支持SQL简单易学，灵活使用可以进行非常复杂和高级的数据库操作

数据(结构)定义语言DDL(Data Definition Language)，是用于创建和修改数据库表结构的语言。  
常用的语句：create ,alter,drop,rename

创建数据库并设置编码格式  
CREATE DATABASE \[if not exists\] 数据库名 \[ CHARSET utf8\]  
删除数据库  
DROP DATABASE 数据库名 / \[IF EXISTS数据库名\];  
修改字符集  
ALTER DATABASE 数据库名 CHARSET gbk;

数据库存储数据的特点:  
● 将数据放到表中，表再放到库中  
● 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有  
唯一性。  
● 表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中“类”的  
设计。  
● 表由列组成，我们也称列为字段。所有表都是由一个或多个列组成的，每一列  
类似java中的”属性”  
● 表中的数据是按行存储的，每一行类似于Java中的“对象”。

### **数据库表的基本概念:** ###

1、数据表  
表（table）是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素。  
表的最简单形式是由行和列组成，分别都包含着数据。 每个表都有一个表头和表体，表头定  
义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。  
2、字段  
字段是表里的一列，用于保存每条记录的特定信息。如客户订单表的字段包括“订单  
ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。  
数据表的一列包含了特定字段的全部信息。  
3、记录  
记录也被称为一行数据，是表里的一行。在关系型数据库的表里，一行数据是指一条完  
整的记录。

**设 计 表**  
 对于具体的某一个表，在创建之前，需要确定表的下列特征：  
1.表名(表信息)  
2.表中的字段  
3.字段的数据类型和长度  
4.哪些约束

**表中的数据类型：**  
char（n） 长度为n的定长字符串，  
varchar(n） 最大长度为n的可变长字符串，  
date 日期， 包含年月日，  
datetime 包含 年月日 时分秒，

![30fd27bdbfb14359b732eb7c47cb43be.png][]

![ce178009b01a4a4693edf0ca493b4da5.png][]

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)

### **主键** ###

在一张表中唯一的代表一条记录,不能为空,不能重复。  
**约束:**  
1.PRIMARY KEY 设置主键约束  
2.NOT NULL 不能为空约束  
3.UNIQUE 唯一性约束  
4.检查约束（check） 设置条件  
5.外键约束 foreign key  
6.主键自动增长： AUTO\_INCREMENT  
7.默认值： DEFAULT default\_value  
8.字段注释: 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 被复制表名;

##    4.SQL--DML ##

**数据操纵语言DML（Data Manipulation Language）  
常用语句： insert,delete,update**

### **插入数据** ###

方式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 dept (dept_name, dept_desc, dept_date)
    VALUES
      (
        '研发部',
        '开发软件',
        '2014-12-14'
      ),
      (
        '财务部',
        '发工资',
        '2014-10-14'
      ),
      (
        '市场部',
        '销售软件',
        '2015-11-02'
      )

### **修改数据** ###

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

### **删除数据** ###

DELETE FROM 表名 WHERE 条件  
TRUNCATE TABLE 表名;清空整张表

##    5.SQL--DQL ##

**DQL（Data Query Language）**数据查询语言查询是使用频率最高的一个操作，可以从一个表中查询数据，也可以从多个表中查询数据。  
基础查询  
语法：  
select 查询列表 from 表名;  
特点：  
查询列表可以是：表中的字段、常量、表达式、函数。  
查询的结果是一个虚拟的表格。

查询结果处理：  
特定列查询:select column1,column2 from table  
全部列查询: select \* from table  
算数运算符:\+ - \* /  
排除重复行: select distinct column1,column2 from table  
查询函数：select 函数; / 例如version()

### **函数:** ###

类似于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
      LENGTH (NAME)
    FROM
      student
    
    -- char_length()获取参数值的字符个数
    SELECT
      CHAR_LENGTH (NAME)
    FROM
      student
    
    -- upper()/lower()：将字符串变成大写/小写
    SELECT
      UPPER (NAME),
      LOWER (NAME)
    FROM
      student
    
    -- substring(str,pos,length)：截取字符串 位置从1开始
    SELECT
      SUBSTRING (NAME, 1, 2)
    FROM
      student
    
    -- instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0
    SELECT
      INSTR (NAME, 'i')
    FROM
      student
    
    -- trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
    SELECT
      TRIM (NAME)
    FROM
      student
      SELECT
        TRIM ("a" FROM NAME)
      FROM
        student
    
    -- lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度
    SELECT
      LPAD (NAME, 5, 'a')
    FROM
      student
    
    -- rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度
    SELECT
      RPAD (NAME, 5, 'a')
    FROM
      student
    
    -- 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)

-- 逻辑处理
    -- case when 条件 then 结果1 else 结果2 end; 可以有多个when
    SELECT
      id,
      NAME,
      (
        CASE
    WHEN height >= 1.9
      THEN "是大高个"
    WHEN height >= 1.7 AND height < 1.9
      THEN "是高个"
      ELSE "不是高个"
    END
    )AS '身高'
    FROM
      student
    
    -- ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值
    SELECT
      IFNULL (address, '未输入数据')
    FROM
      student
      
    -- if函数：if else的 效果 if(条件,结果1,结果2)
    SELECT
      IF (
        height > 1.9,
        "是大高个",
        "不是高个"
      )
    FROM
      student

#### **数学函数** ####

round(数值)：四舍五入  
ceil(数值)：向上取整，返回>=该参数的最小整数  
floor(数值)：向下取整，返回<=该参数的最大整数  
truncate(数值,保留小数的位数)：截断，小数点后截断到几位  
mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负  
rand()：获取随机数，返回0-1之间的小数

-- 数字函数
    -- round(数值)：四舍五入
    SELECT
      ROUND (height)
    FROM
      student
      
    -- ceil(数值)：向上取整，返回>=该参数的最小整数
    SELECT
     CEIL (height)
    FROM
      student
      
    -- floor(数值)：向下取整，返回<=该参数的最大整数
    SELECT
    FLOOR (height)
    FROM
      student
      
    -- truncate(数值,保留小数的位数)：截断，小数点后截断到几位
    SELECT
     TRUNCATE (height,1)
    FROM
      student
      
    -- mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负
    SELECT
     MOD (height,1)
    FROM
      student
    
    -- rand()：获取随机数，返回0-1之间的小数
    SELECT
     RAND(height)AS '随机数'
    FROM
      student

#### **日期函数** ####

now()：返回当前系统日期+时间  
curdate()：返回当前系统日期，不包含时间  
curtime()：返回当前时间，不包含日期  
可以获取指定的部分，年、月、日、小时、分钟、秒  
YEAR(日期列),MONTH(日期 列 ),DAY(日期 列 ) ,  
HOUR(日期 列 ) ,MINUTE(日期 列 ) SECOND(日期 列 )  
str\_to\_date：将日期格式的字符转换成指定格式的日期  
date\_format：将日期转换成字符串  
datediff(big,small)：返回两个日期相差的天数

-- 日期函数
    -- now()：返回当前系统日期+时间
    SELECT NOW()
    
    -- curdate()：返回当前系统日期，不包含时间
    SELECT CURDATE()
    
    -- curtime()：返回当前时间，不包含日期
    SELECT CURTIME()
    
    -- YEAR(日期列),MONTH(日期 列 ),DAY(日期 列 )
    SELECT YEAR(birthday),MONTH(birthday),DAY(birthday) FROM student
    
    -- HOUR(日期 列 ) ,MINUTE(日期 列 ) SECOND(日期 列 )
    SELECT HOUR(rig_time),MINUTE(rig_time),SECOND(rig_time) FROM student
    
    -- str_to_date：将日期格式的字符转换成指定格式的日期
    SELECT STR_TO_DATE('2023-2-3','%Y-%m-%d')
    
    -- date_format：将日期转换成字符串
    SELECT DATE_FORMAT(birthday,'%Y-%m-%d')FROM student
    
    -- datediff(big,small)：返回两个日期相差的天数
    SELECT DATEDIFF(NOW(),birthday)FROM studl cxfgent

![87c8381a244c4323a5104fc2bf7cfbfc.png][]

####  **分组函数** ####

功能：用作统计使用，又称为聚合函数或统计函数或组函数  
分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数  
（非空）  
1.sum，avg一般用于处理数值型max，min，count可以处理任何类型  
2.以上分组函数都忽略null值  
3.count函数的一般使用count（\*）用作统计行数  
4.和分组函数一同查询的字段要求是group by后的字段

-- 分组函数
    -- sum 求和、avg 平均值、max 最大值、min 最小值、count 计数（非空）
    SELECT
      SUM(height),
      AVG(height),
      MAX(height),
      MAX(NAME),
      MIN(height),
      COUNT(*),
      COUNT(address)
    FROM
      student

### **条件查询** ###

使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。  
语法:select <结果> from <表名> where <条件>

比较运算  
=, != 或<>, >, <, >=, <=  
逻辑运算  
and 与  
or 或  
not 非

模糊查询  
LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值或数值型.  
通配符： % 任意多个字符，包含0个字符 \_ 任意单个字符。  
between and 两者之间,包含临界值；  
in 判断某字段的值是否属于in列表中的某一项。  
IS NULL（为空的）或 IS NOT NULL（不为空的）。

-- 逻辑运算 and 与 or 或 not 非
      SELECT * FROM student WHERE gender='男' AND id>3
      SELECT * FROM student WHERE gender='男' OR id>3
      SELECT * FROM student WHERE (gender='男' OR id>3) AND height>=1.7
      SELECT * FROM student WHERE gender<>'男'
      
      -- LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值或数值型.
      SELECT * FROM student WHERE NAME LIKE '张%'
      SELECT * FROM student WHERE NAME LIKE '%三'
      SELECT * FROM student WHERE NAME LIKE '%三%'
      SELECT * FROM student WHERE NAME LIKE '张_'  
      
      -- between and 两者之间,包含临界值
      SELECT * FROM student WHERE height BETWEEN 1.65 AND 1.80 
    
      -- in 判断某字段的值是否属于in列表中的某一项
      SELECT * FROM student WHERE height IN(1.78,1.80,1.98)
      SELECT * FROM student WHERE height NOT IN(1.78,1.80,1.98)
      
      -- IS NULL（为空的）或 IS NOT NULL（不为空的）
      SELECT * FROM student WHERE address IS NULL
      SELECT * FROM student WHERE address IS NOT NULL

1.UNION 的语法如下：  
\[SQL 语句 1\]  
UNION  
\[SQL 语句 2\]  
2、UNION ALL 的语法如下：  
\[SQL 语句 1\]  
UNION ALL  
\[SQL 语句 2\]  
当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，  
mysql 会把所有的记录返回，且效率高于union 。

-- UNION
      SELECT *FROM student WHERE gender='男'
      UNION
       SELECT *FROM student WHERE gender='女' 
      UNION
       SELECT *FROM student WHERE gender='女' 
      -- UNIONALL 
       SELECT *FROM student WHERE gender='男'
      UNION ALL
       SELECT *FROM student WHERE gender='女' 
      UNION ALL
       SELECT *FROM student WHERE gender='女'

排序  
查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC。  
asc代表的是升序，desc代表的是降序，如果不写，默认是升序。  
order by子句中可以支持单个字段、多个字段。

SELECT * FROM student ORDER BY height
     SELECT * FROM student ORDER BY height DESC  LIMIT 0,3;

数量限制  
limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)。  
SELECT \* FROM table LIMIT offset rows;  
SELECT \* from table LIMIT 0,5;

### **分组查询** ###

语法：  
select 分组函数，列（要求出现在group by的后面）  
from 表  
\[where 筛选条件\]  
group by 分组的列表  
\[having 分组后的筛选\]  
\[order by 子句\]  
注意：查询列表比较特殊，要求是分组函数和group by后出现的字段

分组查询中的筛选条件分为两类:  
数据源 源位置 关键字  
分组前筛选 原始表 group by子句的前面 where  
分组后筛选 分组后的结果集 group by的后面 having

-- 分组查询
     SELECT SUM(height),gender FROM student GROUP BY gender
     SELECT COUNT(height)c,gender FROM student GROUP BY gender HAVING c>=3 
     SELECT * FROM(SELECT COUNT(height)c,gender FROM student GROUP BY gender)m WHERE c>=3

##    6.多表设计\_关联查询 ##

使用多表关联的原因是解决数据冗余的问题。

###  **数据库设计范式** ###

● 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。  
● 目前关系数据库有5种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。  
● 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了。

数据库设计范式  
1．第一范式(确保每列保持原子性)  
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就  
说明该 数据库表满足了第一范式。

2． 第二范式就是要有主键，要求其他字段都依赖于主键。  
没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。  
其他字段为什么要依赖于主键？因为不依赖于主键，就找不到他们。更重要的是，其  
他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依  
赖于主键，也就成了唯一的。

3.第三范式就是要消除传递依赖，方便理解，可以看做是“消除冗余”。

### **外键** ###

引用另外一个数据表的某条记录。  
外键列类型与主键列类型保持一致.  
数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键（foreign key）建立起来  
的。  
create table score(  
cid int not null auto\_increment primary key,  
score int,  
sid int,  
CONSTRAINT 约束名 foreign key(sid) references student(sid)  
);  
 约束名规则:  
例：FK\_ForeignTable\_PrimaryTable\_On\_ForeignColumn

外键约束：

1、当主表中没有对应的记录时，不能将记录添加到从表。  
2、不能更改主表中的值而导致从表中的记录孤立。  
3、从表存在与主表对应的记录，不能从主表中删除该行。  
4、删除主表前，先删从表

### **关联查询** ###

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。  
笛卡尔乘积现象：表1有m行，表2有n行，结果=m\*n。  
发生原因：没有有效的连接条件。  
如何避免：添加有效的连接条件。  
按功能分类：  
内连接  
外连接  
左外连接  
右外连接

####  **内连接(inner join)** ####

把满足了条件的两张表中的交集数据查询出来  
语法：Select 结果 from 表1，表2 where 表1.column1 = 表2.column2

![52bfd8434e0b4b50ad47dbb93f15b90e.png][]

#### **左外连接(left join)** ####

select 结果 from  
表1 left join 表2 on  
表1.column1 = 表2.column2

![1927a0ff4ed04f4da3bbe632d6fdfbc9.png][]

#### **右外连接(right join)** ####

select 结果 from  
表1 right join 表2 on  
表1.column1 = 表2.column2

![67eac9fa04904ba38ce38d7387316cbe.png][]

### **子查询** ###

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询.  
分类：  
按子查询出现的位置：  
select后面：支持标量子查询  
from后面：支持表子查询  
where：支持标量子查询，列子查询

按功能、结果集的行列数不同：  
标量子查询（结果集只有一行一列）  
列子查询（结果集只有一列多行）  
表子查询（结果集一般为多行多列）

-- 2.查询出工资最高的员工，并显示员工信息 
    SELECT *
    FROM customer
    WHERE money =(SELECT MAX(money) FROM customer)
    
    -- 3.查询出每个部门工资最高的员工信息
    SELECT id,NAME,gender,age,money,deptid
    FROM (SELECT MAX(money)s FROM customer GROUP BY deptid)a,customer
    WHERE a.s=customer.money
    
    select id ,
    name,
    (select name from major where id=1)a
    from student
    where id=1

[30fd27bdbfb14359b732eb7c47cb43be.png]: https://img-blog.csdnimg.cn/30fd27bdbfb14359b732eb7c47cb43be.png
[ce178009b01a4a4693edf0ca493b4da5.png]: https://img-blog.csdnimg.cn/ce178009b01a4a4693edf0ca493b4da5.png
[87c8381a244c4323a5104fc2bf7cfbfc.png]: https://img-blog.csdnimg.cn/87c8381a244c4323a5104fc2bf7cfbfc.png
[52bfd8434e0b4b50ad47dbb93f15b90e.png]: https://img-blog.csdnimg.cn/52bfd8434e0b4b50ad47dbb93f15b90e.png
[1927a0ff4ed04f4da3bbe632d6fdfbc9.png]: https://img-blog.csdnimg.cn/1927a0ff4ed04f4da3bbe632d6fdfbc9.png
[67eac9fa04904ba38ce38d7387316cbe.png]: https://img-blog.csdnimg.cn/67eac9fa04904ba38ce38d7387316cbe.png