mysql基础_数据库

mysql基础

MySQL 概述

1、数据库相关概念

主流的关系型数据库：

总结
1)数据库:数据存储的仓库
2)数据库管理系统:操纵和管理数据库的大型软件
3)SQL:操作关系型数据库的编程语言，是一套标准

2、MySQL数据库下载及安装

版本

MySQL官方提供了两种不同的版本:
社区版(MySOLCommunity Server) =>免费，MySQL不提供任何技术支持

商业版(MySQLEnterprise Edition)=>收费，可以试用30天，官方提供技术支持

下载
下载地址:https://dev.mysql.com/downloads/windows/installer/8.0.htm
安装后WinDos下的数据默认保存路径
C:\ProgramData\MySQL\MySQL Server 8.0\Data

3、启动和停止

MySQL在安装时默认是开机自动C++md

windos下：用管理员身份启动命令
net start mysql80
net stop mysql80

4、客户端连接

//系统自带的命令行工具
mysql [-h 127.0.0.1][-P 3306] -u root -p//需要配置环境变量Path C:\Program Files\MySQL\MySQL Server 8.0\bin

关系型数据库(补充)

概念:建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
特点:
1、使用表存储数据，格式统一，便于维护
2、使用SQL语言操作，标准统一，使用方便

SQL

1、SQL的通用语法

SQL语句可以单行或多行书写，以分号结尾。
SQL语句可以使用空格/缩进来增强语句的可读性。(空格和缩进不受个数影响)
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
注释:
单行注释: -- 注释内容或 #注释内容(MySQL特有--后面要有空格)
多行注释: /注释内容/

2、SQL语句的分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

数据库操作

查询:
SHOW DATABASES;//查询所有数据库
SELECT DATABASE();//查询当前数据库
创建数据库:
CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];
IF NOT EXISTS - 如果不存在在进行创建
DEFAULT CHARSET - 如utf8，推荐使用utf8mb4可以占用4个字节
删除:
DROP DATABASE [IF EXISTS]数据库名
IF EXISTS - 如果存在
使用:
USE 数据库名;//切换数据库

表操作

查询:
SHOW TABLES;//查询当前数据库下的所有表
DESC 表名;//查询表结构
SHOW CREATE TABLE 表名;查询指定表的建表语句
创建:
CREATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释]，
字段2 字段2类型[COMMENT 字段2注释]，
字段3 字段3类型[COMMENT 字段3注释]，
字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];//创建表

数据类型
mysql中主要的数据类型分为三类:数值类型，字符串类型，日期时间类型
修改

ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT 注释][约束];//添加字段
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);//修改字段数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度)[COMMENT 注释][约束];//修改字段名称和数据类型
ALTER TABLE 表名 RENAME TO 新表名;//修改表名

删除

ALTER TABLE 表名 DROP 字段名;//删除字段
DROP TABLE [IF EXISTS]表名;//删除表
TRUNCATE TABLE 表名;//删除指定表，并重新创建该表

DML(增删改)

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作。

添加-inseter

INSERT INTO 表名 (字段名1，字段名2，..) VALUES(值1，值2，….);//给全部字段添加数据’
INSERT INTO 表名 VALUES (值1，值2，….);//批量添加数据
//批量添加数据
INSERT INTO 表名 (字段名1，字段名2，..) VALUES(值1，值2，...)，(值1，值2，.)，(值1，值2，.);
INSERT INTO 表名 VALUES (值1，值2，….)，(值1，值2，….)(值1，值2，…);

注意:
插入数据时，指定的字段顺序需要与值的顺序是-一对应的。
字符串和日期型数据应该包含在引号中。
插入的数据大小，应该在字段的规定范围内。

修改-update

UPDATE 表名 SET 字段名1=值1，字段名2=值2，[WHERE 条件];//修改数据
注意:修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除-delete


DELETE FROM 表名「WHERE 条件]
注意:
DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
DELETE 语句不能删除某一个字段的值(可以使用UPDATE)。

DQL(重点)

语法:
SELECT//字段列表
FROM//表名列表
WHERE//条件列表
GROUP BY//分组字段列表
HAVING//分组后条件列表
ORDER BY//排序字段列表
LIMIT//分页参数

- 基本查询 
SELECT 字段1，字段2，字段3 ...FROM 表名;//查询指定字段
SELECT * FROM 表名;//查询全部字段
SELECT 字段1「AS 别名1]，字段2「AS 别名2] …FROM 表名;//查询并设置别名
SELECT DISTINCT 字段列表 FROM 表名;去除重复记录

- 条件查询  (where)

语法:
SELECT 字段列表 FROM 表名 WHERE 条件列表；

条件:

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	2
<>或!=	不等于
between...and	在xxx范围内(含最小值和最大值)
in(...)	在...范围内
like 占位符	模糊匹配，_匹配单个字符。%匹配任意字符
is null	是null

逻辑运算符	功能
and或'&&'	并且
or	或者
not或'!'	非

聚合查询(count、max、min、avg、sum)
介绍:
将一列数据作为一个整体，进行纵向计算。

常见的聚合函数:

逻辑运算符	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

//系统自带的命令行工具
mysql [-h 127.0.0.1][-P 3306] -u root -p//需要配置环境变量Path C:\Program Files\MySQL\MySQL Server 8.0\bin
0

分组查询

//系统自带的命令行工具
mysql [-h 127.0.0.1][-P 3306] -u root -p//需要配置环境变量Path C:\Program Files\MySQL\MySQL Server 8.0\bin
1

注意:
执行顺序: where >聚合函数>having
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义**

排序查询

//系统自带的命令行工具
mysql [-h 127.0.0.1][-P 3306] -u root -p//需要配置环境变量Path C:\Program Files\MySQL\MySQL Server 8.0\bin
2

分页查询

//系统自带的命令行工具
mysql [-h 127.0.0.1][-P 3306] -u root -p//需要配置环境变量Path C:\Program Files\MySQL\MySQL Server 8.0\bin
3

执行顺序

//系统自带的命令行工具
mysql [-h 127.0.0.1][-P 3306] -u root -p//需要配置环境变量Path C:\Program Files\MySQL\MySQL Server 8.0\bin
4

DCL

DCL英文全称是Data ControlLanguage(数据控制语言)，用来管理数据库用户、控制数据库的访问权限

管理用户

//系统自带的命令行工具
mysql [-h 127.0.0.1][-P 3306] -u root -p//需要配置环境变量Path C:\Program Files\MySQL\MySQL Server 8.0\bin
5

权限控制

权限	说明
ALL，ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/试图
CREATE	创建数据库/表

//系统自带的命令行工具
mysql [-h 127.0.0.1][-P 3306] -u root -p//需要配置环境变量Path C:\Program Files\MySQL\MySQL Server 8.0\bin
6

函数

函数是指一段可以直接被另一段程序调用的程序或代码

字符串函数-常用

函数	功能
CONCAT(S1，S2，...Sn)	字符串拼接，将S1，S2，..Sn拼接成一个字符串
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将字符串str全部转为大写
LPAD(str，n，pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str，n，pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str，start，len)	返回从字符串str从start位置起的len个长度的字符串

数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x，y)	返回x/y的模，模的意味x除y取余
RAND()	返回0~1内的随机数
ROUND(x，y)	求参数x的四舍五入的值，保留y位小数

日期函数:

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE ADD(date， INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1，date2)	返回起始时间date1 和结束时间date2之间的天数

流程控制

函数	功能
IF(value ，t ， f)	如果value为true，则返回t，否则返回f
IFNULL(value1 ，value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [val1 ]THEN [res1].. ELSE [default]END	如果val1为true，返回res1，…否则返回default默认值
CASE [expr ] WHEN [val1 ] THEN [res1]..ELSE[ default] END	如果expr的值等于val1，返回res1，..否则返回default默认值

约束

概念:
约束是作用于表中字段上的规则，用于限制存储在表中的数据
目的:
保证数据库中数据的正确、有效性和完整性

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY(自增:AUTO INCREMENT)
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本之后)	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

约束是作用于字段的，可以在创建或修改表时添加约束，对于一个字段可以添加多个约束

字段名	含义	字段类型	约束条件	约束关键字
id	id唯一标识	int	主键自动增长	5
name	姓名	varchar(10)	非空且唯一	5
age	年龄	int	大于0小于等于120	5
status	状态	char(1)	默认值为1	5
gender	性别	char(1)	无	5

//系统自带的命令行工具
mysql [-h 127.0.0.1][-P 3306] -u root -p//需要配置环境变量Path C:\Program Files\MySQL\MySQL Server 8.0\bin
7

外键约束
概念
外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

注意:目前上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的

//系统自带的命令行工具
mysql [-h 127.0.0.1][-P 3306] -u root -p//需要配置环境变量Path C:\Program Files\MySQL\MySQL Server 8.0\bin
8

外键的删除和更新行为:

行为	说名
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与RESTRICT一致)
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除更新。(与 NO ACTON一致)
CASCADE(级联)	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为nul(这就要求该外键允许取nul)
SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持)

实例:
ALTERTABLE 表名 ADD CONSTRAINT 外键名称 FOREIGNKEY(外键字段)REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE，

多表查询

多表关系
概念
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联。
所以各个表结构之间也存在着各种联系，基本上分为三种:
一对多(多对一)
案例:部门与员工
关系:一个部门对应多个员工，一个员工对应一个部门
实现:在多的一方建立外键，指向一的一方的主键

多对多
案例: 学生与课程
关系:一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现:建立第三张中间表，中中间表至少包含两个外键，分别关联两方主键

一对一
案例: 用户与用户详情的关系
关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现:在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

多表查询概述
概述: 指从多张表中查询数据
笛卡尔积:笛卡尔乘积是指在数学中，两个集合A集合和 B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)
内连接
相当于查询A、B交集部分数据

//系统自带的命令行工具
mysql [-h 127.0.0.1][-P 3306] -u root -p//需要配置环境变量Path C:\Program Files\MySQL\MySQL Server 8.0\bin
9

外连接

查询:
SHOW DATABASES;//查询所有数据库
SELECT DATABASE();//查询当前数据库
创建数据库:
CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];
IF NOT EXISTS - 如果不存在在进行创建
DEFAULT CHARSET - 如utf8，推荐使用utf8mb4可以占用4个字节
删除:
DROP DATABASE [IF EXISTS]数据库名
IF EXISTS - 如果存在
使用:
USE 数据库名;//切换数据库
0

自连接
当前表与自身的连接查询，自连接必须使用表别名

查询:
SHOW DATABASES;//查询所有数据库
SELECT DATABASE();//查询当前数据库
创建数据库:
CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];
IF NOT EXISTS - 如果不存在在进行创建
DEFAULT CHARSET - 如utf8，推荐使用utf8mb4可以占用4个字节
删除:
DROP DATABASE [IF EXISTS]数据库名
IF EXISTS - 如果存在
使用:
USE 数据库名;//切换数据库
1

自连接查询，可以是内连接查询，也可以是外连接查询

联合查询union、union all
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
区别:
union all会把查询的结果直接进行叠加这样可能会导致重复数据的产生，union则会对查询出的结果进行去重。

查询:
SHOW DATABASES;//查询所有数据库
SELECT DATABASE();//查询当前数据库
创建数据库:
CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];
IF NOT EXISTS - 如果不存在在进行创建
DEFAULT CHARSET - 如utf8，推荐使用utf8mb4可以占用4个字节
删除:
DROP DATABASE [IF EXISTS]数据库名
IF EXISTS - 如果存在
使用:
USE 数据库名;//切换数据库
2

子查询
概念: SQL语句中嵌套SELECT语句，称为嵌套查询又称子查询。
子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个
根据子查询结果不同可分为:标量子查询、列子查询、行子查询、表子查询

1、标量子查询(子查询结果为单个值)
SELECT*FROM t WHERE column1=(SELECT column1 FROM t2);
子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询成为标量子查询。
常用的操作符:= 、<> 、> 、>= 、< 、<=

查询:
SHOW DATABASES;//查询所有数据库
SELECT DATABASE();//查询当前数据库
创建数据库:
CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];
IF NOT EXISTS - 如果不存在在进行创建
DEFAULT CHARSET - 如utf8，推荐使用utf8mb4可以占用4个字节
删除:
DROP DATABASE [IF EXISTS]数据库名
IF EXISTS - 如果存在
使用:
USE 数据库名;//切换数据库
3

2、列子查询(子查询结果为一列)
子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询
常用的操作符:IN、NOTIN、ANY、SOME、ALL

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

查询:
SHOW DATABASES;//查询所有数据库
SELECT DATABASE();//查询当前数据库
创建数据库:
CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];
IF NOT EXISTS - 如果不存在在进行创建
DEFAULT CHARSET - 如utf8，推荐使用utf8mb4可以占用4个字节
删除:
DROP DATABASE [IF EXISTS]数据库名
IF EXISTS - 如果存在
使用:
USE 数据库名;//切换数据库
4

3、行子查询(子查询结果为一行)
子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询常用的操作符:=、<>、IN、NOT IN

查询:
SHOW DATABASES;//查询所有数据库
SELECT DATABASE();//查询当前数据库
创建数据库:
CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];
IF NOT EXISTS - 如果不存在在进行创建
DEFAULT CHARSET - 如utf8，推荐使用utf8mb4可以占用4个字节
删除:
DROP DATABASE [IF EXISTS]数据库名
IF EXISTS - 如果存在
使用:
USE 数据库名;//切换数据库
5

4、表子查询(子查询结果为多行多列)
概念:子查询返回的结果是多行多列，这种子查询称为表子查询

查询:
SHOW DATABASES;//查询所有数据库
SELECT DATABASE();//查询当前数据库
创建数据库:
CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];
IF NOT EXISTS - 如果不存在在进行创建
DEFAULT CHARSET - 如utf8，推荐使用utf8mb4可以占用4个字节
删除:
DROP DATABASE [IF EXISTS]数据库名
IF EXISTS - 如果存在
使用:
USE 数据库名;//切换数据库
6

MySQL概述