数据库（MYSQL）基础

数据库概述

● 为什么学习数据库：

● 实现数据持久化到本地；

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

● 数据库（DataBase）为了方便数据的存储和管理，它将数据按照特定的

规则存储在磁盘上,就是一个存储数据的仓库。

● 数据库的相关概念

DB：数据库（DataBase）

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

DBMS：数据库管理系统（DataBase Management System）

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

● 常见的数据库产品：

国外

MySQL 快捷、可靠 开源、免费

Oracle:功能强大,收费.

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

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

国内

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

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

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

神通:神舟通用公司

Mysql 数据库

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

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

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

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

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

● mysql商业版与社区版

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

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

• MySQL的常用命令与语言

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

选择指定的库：use 库名

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

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

查看mysql版本 select version();

• 安装可视化客户端工具

SQLyog / Navicat

sql

● 结构化查询语言(Structured Query Language)简称SQL，是一种特殊

目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以

及查询、更新和管理关系数据库系统.

SQL优点：

不是某个特定数据库供应商专有的语是言，几乎所有DBMS都支持SQL

简单易学，灵活使用可以进行非常复杂和高级的数据库操作

DDL

● 数据(结构)定义语言DDL(Data Definition Language)，是用于创

建和修改数据库表结构的语言。

● 常用的语句：create ,alter,drop,rename

创建,删除数据库

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

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

删除数据库

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

修改字符集

ALTER DATABASE 数据库名 CHARSET gbk;

数据库表的基本概念

1、数据表

表（table）是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素。

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

义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。

2、字段

字段是表里的一列，用于保存每条记录的特定信息。如客户订单表的字段包括“订单

ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。

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

3、记录

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

设计和使用表

● 对于具体的某一个表，在创建之前，需要确定表的下列特征：

● 表名(表信息)

● 表中的字段

● 字段的数据类型和长度

● 哪些约束

数据类型

整形

INTEGER：标准整数类型，通常为32位带符号整数。

SMALLINT：较小的整数类型，通常为16位带符号整数。

BIGINT：较大的整数类型，通常为64位带符号整数。

TINYINT：非标准小整数类型，通常为8位带符号整数，可用于存储较小的整数值。

INT：整数类型的别名，通常等同于INTEGER。

浮点

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)

创建表语法:

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 约束名 约束规则]

)

ALTER TABLE：表示修改表结构的关键字；

CONSTRAINT：添加约束

FOREIGN KEY：表示这是一个外键约束；

REFERENCES：关键字，用于指定关联的表和字段；

AUTO_INCREMENT 主键自增长

主键：

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

约束:

PRIMARY KEY 设置主键约束

NOT NULL 不能为空约束

UNIQUE 唯一性约束

检查约束 设置条件

外键约束

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

AUTO_INCREMENT

默认值

DEFAULT default_value

字段注释:

comment '注释'

删除表，修改表名

删除表

DROP TABLE [if exists ]表名

修改表名

RENAME TABLE 旧表名 TO 新表名

复制表结构

CREATE TABLE 新表名 LIKE 被复制表名

-- 删除表结构，删除后表中的数据
DROP TABLE student
​
-- 修改表名
RENAME TABLE student TO stu
​
-- 复制表结构，在备份时可用
CREATE TABLE student LIKE stu
​

● 数据操纵语言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) 查询语句(查询的列数与插入列数匹配)

方式1:
INSERT INTO student(name,gender,birthday,phone,height,reg_time)VALUES('詹姆斯','男','1985-2-3','1522521555',1.98,NOW())
​
方式2:
INSERT INTO student SET name='可比'，gender='男',birthday='1986-2-3',phone='1653251564'
​
方式3:
INSERT INTO student(name,gender,birthday,phone,height,reg_time)
VALUES('库里','男','1998-2-3','156515515',1.88,NOW()),
('欧文','男','1995-2-3','1565135145',1.98,NOW())
​
方式4:
INSERT INTO student(number,name,gender,birthday,phone,height,reg_time)SELECT * FROM student

修改数据

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

UPDATE stu SET name='小明'，height='1.70' WHERE number = 1

删除数据

DELETE FROM 表名 WHERE 条件

TRUNCATE TABLE 表名;清空整张表

DELETE FROME stu WHERE number = 2

查询数据

● DQL（Data Query Language）数据查询语言查询是使用频率最高的一个操作，

可以从一个表中查询数据，也可以从多个表中查询数据。

基础查询

● 语法：

select 查询列表 from 表名;

● 特点：

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

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

查询结果处理：

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

全部列查询: select * from table

算数运算符:+ - * /

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

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

--查询的结果是虚拟的表格，不能修改
--查询指定的列
SELECT name,gender,birthday,phone FROM student
​
--查询所有的列 *-所有的列
SELECT * FROM student
​
-- 查询的结果进行算数运算
SELECT name,height+1 FROM student
​
-- 去除重复数据，查询结果中所有的列都相同，视为重复数据
-- DISTINCT关键字去除重复数据
SELECT DISTINCT name,gender FROM student
​

条件查询

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

语法:select <结果> from <表名> where <条件>

比较:

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

逻辑运算:

and 与

or 或

not 非

-- select * FROM 表名 where 条件

SELECT * FROM student WHERE gender = '男' AND height > 1.80

SELECT * FROM student WHERE gender ！= '女'
SELECT * FROM student WHERE gender <> '女'
！= 和 <> 是一样的

模糊查询

LIKE

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

或数值型.

通配符： % 任意多个字符

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

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

IS NULL（为空的）或 IS NOT NULL（不为空的）

SELECT * FROM student WHERE name LIKE '库%'

SELECT * FROM student WHERE height>=1.65 AND height<=1.80
SELECT * FROM student WHERE height BETWEEN 1.65 AND 1.80

SELECT * FROM student WHERE height IN(1.78,1.88,1.98) -- 在这三个里

SELECT * FROM student WHERE height NOT IN(1.78,1.88,1.98) -- 不在这三个里  

SELECT * FROM student WHERE hight IS NULL
-- 身高为空  不能写 =NULL

UNION 的语法如下：

[SQL 语句 1]

UNION

[SQL 语句 2]

UNION ALL 的语法如下：

[SQL 语句 1]

UNION ALL

[SQL 语句 2]

当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，

mysql 会把所有的记录返回，且效率高于union 。

SELECT * FROM student WHERE gender='男'
  UNION
SELECT * FROM student WHERE gender='女'
-- union 将多个查询结果合并，合并时，列数必须一致
          重复的结果会去掉
-- union all 将多个查询结果合并,不会去掉重复的结果

• 排序

查询结果排序,使用 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 WHERE number>0 ORDER BY number ASC,reg_time ASC

-- 数量限制  实现分页查询  LIMIT 开始位置，查询的数量
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 0,3
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 3,3
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 6,3

分组查询

语法：

select 分组函数，列（要求出现在group by的后面）

from 表

[where 筛选条件]

group by 分组的列表

[having 分组后的筛选]

[order by 子句]

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

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

数据源 源位置 关键字

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

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

-- 分组查询
-- 分组--在一些统计场景中，按照某一个条件进行区分（分组）
-- 统计时，按照性别，班级，年级，部门，年份，月份

-- 查询 男生，女生各有多少人 每个年级多少人 每个部门各有多少人
   --男生组统计一次，女生组统计一次
SELECT count(*)，gender FROM student GROUP BY gender

SELECT COUNT(*) FROM student GROUP BY DATE_FORMAT(reg_time,'%Y-%m')

-- 统计男生女生各多少人
SELECT COUNT(*),gender FROM student GROUP BY gender

-- 男生和女生谁的人数大于2
-- HAVING COUNT(*)>2   having 条件 对分组后的结果进行条件筛选
SELECT COUNT(*),gender FROM student GROUP BY gender HAVING COUNT(*)>2

函数

--单行函数:会对查询的每一行数据进行处理

--分组函数

单行函数

字符函数

length()：获取参数值的字节个数
​
char_length()获取参数值的字符个数
​
concat(str1,str2,.....)：拼接字符串
​
upper()/lower()：将字符串变成大写/小写
​
substring(str,pos,length)：截取字符串 位置从1开始
​
instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0
​
trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
​
lpad(str,length,填充字符)：用指定的字符左填充‘到*指定长度
​
rpad(str,length,填充字符)：用指定的字符右填充‘到*指定长度
​
replace(str,old,new)：替换，替换所有的子串

-- length(列名) 以字节为单位
SELECT name,LENGTH(name),gender FROM student
​
-- CHAR_LENGTH(列名) 以字符为单位
SELECT name,CHAR_LENGTH(name),gender FROM student
​
--concat(str1,str2,.....) 连接多个字符串 as 别名
SELECT CONCAT(name,":",CHAR_LENGTH(name)) AS name,gender FROM student
​
-- upper()转大写 lower()转小写
SELECT UPPER(name),LOWER(name) FROM student
​
--substring(字符串,开始的位置,接取得长度)：截取字符串 位置从1开始
SELECT SUBSTRING(name,1,1) FROM student
​
--instr(字符串,指定字符)：返回指定字符首次出现的位置，如果找不到返回0
SELECT INSTR(name,'o') FROM student
​
--trim(字符串) 默认去掉字符串前后的空格
SELECT TRIM(name),name FROM student
​
--trim(指定子串 from 字符串) 可以去掉前后指定的子串
SELECT TRIM('a' FROM name),name FR

逻辑处理

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

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

原本的值

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

--case when 条件 then 条件成立结果 else 不成立结果 end
SELECT name,gender,(CASE WHEN height>=2 THEN '高个子' ELSE '矮个子' END)AS height
​
--ifnull(列名,'为空是默认信息')
SELECT IFNULL(height,'暂未录入')AS height
​
--if(条件，'结果1','结果2')
SELECT name,gender,IF(height>=2,'高个子','正常')AS height

数学函数

round(数值)：四舍五入

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

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

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

mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负

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

SELECT name,gender,ROUND(height) FROM student 

SELECT name,gender,TRUNCATE(height,1) FROM student

日期函数

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

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

curtime()：返回当前时间，不包含日期

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

YEAR(日期列),

MONTH(日期列),

DAY(日期列) ,

HOUR(日期列) ,

MINUTE(日期列)

SECOND(日期列)

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

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

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

● 日期格式

%Y 年，4 位

%m 月，数值(00-12)

%d 月的天，数值(00-31)

%H 小时 (00-23)

%i 分钟，数值(00-59)

%s 秒(00-59)

%f 微秒

%T 时间, 24-小时 (hh:mm:ss)

%j 年的天 (001-366)

%w 周的天 （0=星期日, 6=星期六）

SELECT NOW(),CURDATE(),CURTIME() FROM studentc

SELECT COUNT(*),YEAR(birthday) FROM student GROUP BY 

-- 查询生日在二月份的人
SELECT * FROM student WHERE MONTH(birthday)=2


SELECT STR_TO_DATA('2003-3-3','%Y-%m-%d') FROM student

分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数

（非空）

1.sum，avg一般用于处理数值型

max，min，count可以处理任何类型

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

3.count函数的一般使用count（*）用作统计行数

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

SELECT SUM(height) FROM student -- 求和

SELECT AVG(height) FROM student --平均

SELECT MAX(height) FROM student GROUP BY gender -- 根据性别分组

SELECT MIN(height) FROM student

SELECT COUNT(*) FROM student -- 统计总数

多表设计—关联查询

● 数据库设计范式

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

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就

说明该数据库表满足了第一范式。

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

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

• 其他字段为什么要依赖于主键？因为不依赖于主键，就找不到他们。更重要的是，其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的。

第三范式

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

多表例子

CREATE TABLE major(
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20)
)
​
-- 修改表  为表添加一个列
ALTER TABLE student ADD majorid INT

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

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

数据表之间的关联/引用关系是依靠具体的主键（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

ALTER TABLE student ADD CONSTRAINT fk_student_major_on_majorid FOREIGN KEY(外键) REFERENCES 关联的主表

1、当主表中没有对应的记录时，不能将记录添加到从表

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

3、从表存在与主表对应的记录，不能从主表中删除该行

4、删除主表前，先删从表

● 关联查询

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

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n

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

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

按功能分类：

自连接：

自连接是指数据库中一个表与其自身进行连接的操作。这种操作通常用于将表中的某些数据与该表中其他数据进行关联或连接。

假设有一个员工表employee，包含员工ID和员工姓名，以及直接上司的员工ID，现在需要查询每个员工的姓名和直接上司的姓名。可以使用自连接来实现：

SELECT e1.员工姓名, e2.员工姓名 as 直接上司姓名
  FROM employee e1
LEFT JOIN employee e2 ON e1.直接上司ID = e2.员工ID;

在这个例子中，我们为员工表employee指定了两个不同的表别名e1和e2，分别表示员工信息和他们的直接上司信息。通过LEFT JOIN操作，我们根据员工表中的直接上司ID字段和员工ID字段进行连接，并查询出每个员工的姓名以及他们的直接上司姓名。

内连接：

INNER JOIN 
​
select * from student,major where majorid = id
​
select * from student INNER join major ON majorid = id
​
-- 在关联查询时，select num,name,sno,name from student INNER join major ON majorid = id,两个表中会有相同的列名，这时会产生冲突，不知到是是谁的，此时student.name 
或 select num,name,sno,name from student s INNER join major m ON majorid = id
​
在表命后 student as s 或 student s 就可以 s.name

外连接：

左外连接：

把左边中所有的数据查询出来，右边表只会查询出满足条件的

LEFT JOIN
​
SELECT
*
FROM student s LEFT JOIN major m ON s.majorid = m.id

右外连接：

把右边中所有的数据查询出来，左边表只会查询出满足

RIGHT JOIN
​
-- 统计每个专业的人数
SELECT 
*
FROM student s RIGHT JOIN major m ON s.majorid = m.id

SELECT
s.num,
s.name,
s.gender,
m.name,
GROUP_CONCAT(c.name) cname
FROM student s LEFT JOIN major m ON s.majorid = m.id
LEFT JOIN student_course sc ON s.num = sc.student_num
GROUP BY s.num,s.gender,m.name
​
GROUP_CONCAT() 分组连接 把同一个组中，多个课程名连接起来

子查询

子查询是指在一个SQL语句内嵌套另一个SQL查询语句，并且内部的查询语句通常依赖外部查询的结果。子查询可以嵌套在SELECT、INSERT、UPDATE、DELETE语句的WHERE子句或HAVING子句中使用，用于过滤结果、进行计算或比较等操作。

举个简单的例子，假设有一个订单表order，包含订单号、订单金额和客户ID，我们需要查询客户ID为1的客户的订单总金额。可以使用子查询来实现：

SELECT SUM(订单金额) as 客户1订单总金额
FROM order
WHERE 客户ID = 1;

在这个例子中，子查询被嵌套在SELECT语句中，用于查询客户ID为1的客户的订单金额总和。通过在WHERE子句中使用子查询，我们可以根据外部查询条件来获取需要的结果。