MySQL数据库和SQL语言

数据库基本内容

1. 什么是数据库：数据库是用于存储和管理数据的系统，类似于一个电子化的数据仓库。它建立在文件系统基础之上，但提供了更高效的数据组织方式。访问数据库通常需要使用结构化查询语言（SQL），这是操作数据库的标准编程语言。
2. 数据库的作用：数据库的核心功能是数据存储。无论是开发何种应用程序，数据库都是不可或缺的组成部分。
3. 关系型的数据库：是基于关系模型构建的数据库系统，数据库中保存的都是实体与实体之间的关系，是目前应用最广泛的一类数据库管理系统。
4. 常见的数据库
   Oracle数据库（甲骨文）：大型的数据库，商业收费软件；
   MySQL数据库：中小型的数据库，开源免费，被Oracle收购了（在6.x版本下开始收费了）；
   SQLServer：微软的数据库，Windows平台主流数据库解决方案；
   DB2：IBM公司产品，企业级大型数据库，商业收费软件；
   SyBASE：现已退出主流市场，相关产品是PowerDesigner（数据库设计工具）。

MySQL的安装路径下的my.ini配置文件内包含安装路径basedir以及数据存放位置datadir

MySQL 测试：打开cmd窗口，进入到 mysql bin目录的路径下，输入mysql -u root -p，回车输入密码，即可进入MySQL的服务器

修改密码：
首先需要停止mysql服务：services.msc 进入到服务界面
在cmd>输入一个命令: mysqld ‐‐skip‐grant‐tables (开启一个mysql服务，不需要进行认证)
新打开一个cmd窗口：mysql ‐u root ‐p 不需要输入密码，可以进入
输入命令 show databases; 查看数据库，输入命令 use mysql; 使用mysql数据库。
修改密码的语句：update user set authentication_string=password(‘root’) WHERE user=‘root’;
将两个窗口都关闭，任务管理器中结束（mysqld）进程，重启mysql服务

MySQL数据库备份和恢复：
备份数据库表中的数据：mysqldump ‐u 用户名 ‐p 数据库名 > 文件名.sql 回车后再输入密码
恢复数据库（前提创建空的数据库，并且use）：mysql –u 用户名 p 数据库名 < 文件名.sql 回车后再输入密码
注意不是在数据库的登陆状态下，该命令后没有分号结束，注意 > 符号的方向，恢复数据库使用的命令是mysql，而不是mysqldump

SQL语言

结构化查询语言（Structured Query Language，简称SQL）是非过程性编程语言
过程性语言的特点在于语句执行依赖于前一条或前几条语句的执行结果，而非过程性语言的特点是每条语句都对应一个独立的返回结果。
SQL作为基础查询语言被广泛应用，Oracle数据库则使用专有的PL/SQL语言，该语言只能在Oracle环境中运行。

SQL的分类

1. DDL 数据定义语言
   创建数据库、创建表、创建视图、创建索引、修改数据库、删除数据库、修改表、删除表
   create ‐‐ 创建 alter ‐‐ 修改 drop ‐‐ 删除
2. DML 数据操作语言
   操作数据、插入数据(insert)、修改数据(update)、删除数据(delete)
3. DCL 数据控制语言
   逻辑判断 if else while
4. DQL 数据查询语言
   从表中查询数据（select）

数据库的操作（CURD）

一个MySQL数据库的服务器中包含多个数据库，一个数据库中有多张表，一个表中包含多个字段（字段和JavaBean的属性是对应的），表中存放是数据，一行数据和一个JavaBean实体对象是对应的

• 创建数据库
  基本的语法：create database 数据库名称;
  带字符集和排序规则的创建：create database 数据库名称 character set 编码 collate 校对规则;
• 查看数据库
  查看当前数据库服务器中所有已创建的数据库列表：show databases;
  切换到指定的数据库：use 数据库名称;
  查看创建指定数据库时的完整 SQL 语句：show create database 数据库名称;
  查询当前会话中正在使用的数据库名称：select database();
• 删除数据库
  drop database 数据库名称;
• 修改数据库
  alter database 数据库名称 character set ‘gbk’ collate ‘校对规则’;

表结构操作（CURD）

创建一张表结构：

注意：创建表的时候，后面用小括号，最后是分号；编写字段，字段与字段之间使用逗号，最后一个子段不能使用逗号；如果声明字符串数据的类型，长度是必须指定的，不指定数据的长度，是有默认值的，其中int类型的默认长度是11

数据库的数据类型

• 数值型
  TINYINT：1 字节，适合存储小数值；SMALLINT：2 字节，适合中等范围整数；
  INT：4 字节，最常用的整数类型；BIGINT：8 字节，范围极大，适合超大数据；
  FLOAT：4 字节，单精度浮点数；DOUBLE：8 字节，双精度浮点数。
• 字符串型
  VARCHAR(N)：可变长度字符串类型，N 表示该字段允许存储的最大字符数。适用于存储长度不固定的文本数据，系统会自动去除多余空间；
  CHAR(N)：固定长度字符串，N 为最大长度（1~255），不足时用空格填充，适合长度固定的数据。
• 大数据类型
  BLOB：二进制类型，用于存储图片、文件、视频等二进制数据
  TEXT：大文本类型，用于存储超长文本内容
• 逻辑类型
  BIT，二进制位类型，专门用于存储二进制位序列（0 和 1），适合需要高效存储多个布尔状态或二进制标志的场景
• 日期类型
  DATE：只包含日期（年月日）
  TIME：只包含时间（时分秒）
  DATETIME：包含日期和时间（年月日时分秒），如果插入数据的字符值为空，字段的值就是空；
  TIMESTAMP：包含日期和时间（年月日时分秒），如果插入数据设置字段的值为空，则系统默认获取当前时间存入字段中。

单表约束
单表约束是指针对单个数据表中的字段设置的规则，用于保证表中数据的完整性、一致性和有效性，避免重复值、空值、无效范围值等的插入

• 主键约束（primary key）
  唯一、非空、被引用
  一个表只能有一个主键，唯一标识表中的每一条记录，主键字段的值不能重复，且不能为空，可以被引用，主键会自动创建索引，提升查询效率
• 唯一约束（unique）
  确保字段的值在表中唯一，但允许为 NULL
• 非空约束（not null）
  强制字段不能为 NULL，必须填入具体值，适用表中 “必填” 字段

声明主键列的方式：

多表外键的约束
外键是用于建立两个或多个表之间关联的约束，其核心作用是保证数据的一致性和完整性。当多个表之间存在业务关联时，外键约束能确保子表中的数据必须与父表中的数据保持对应关系。
一对多：外键仅在从表中设置，引用主表的主键 / 唯一键
多对多：需通过中间表实现
一对一：外键在从表中设置，引用主表的主键

表的增删改查
删除表语法：drop table 表名;
查询表的信息：desc 表名;
查看当前数据库中所有的标签：show tables;
查看表的创建的信息：show create table 表名;
添加列：alter table 表名 add 新列名 类型(长度) 约束;
删除列：alter table 表名 drop 列名;
修改列的类型或者约束：alter table 表名 modify 列名 类型(长度) 约束;
修改列名：alter table 表名 change 旧列名 新列名 类型(长度) 约束;
修改表的名称：rename table 表名 to 新表名;
修改表的字符集：alter table 表 名 character set utf8;

数据的操作（CRUD）

控制客户端与服务器之间的数据传输编码
指定客户端发送给服务器的 SQL 语句使用的字符集：character_set_client=utf8
指定服务器返回给客户端的结果使用的字符集：character_set_results=utf8

插入数据（insert）
insert into 表名 (字段1,字段2,字段3) values (值1,值2,值3);
insert into 表名 values (值1,值2,值3);
注意：插入的数据与字段类型必须是相同的，数据的大小范围在字段范围内，值与字段一一对应，字符串或者日期类型数据需要使用单引号

修改数据（update）
update 表名 set 字段1=值,字段2=值 where 条件;
如果没有where条件语句，默认更新所有的数据，如果有where条件，默认更新符合条件的记录。

删除数据（delete）
delete from 表名 where 条件;
delete from 表名; 即没有where条件，逐行删除表中所有数据，但保留表结构；
drop table 表名; 直接删除整个表，包括表结构和所有数据；
truncate 表名; 先删除整个表，再立即创建一个和结构相同的空表；

查询数据（select）
select * from 表名;
select 字段1,字段2,字段3 from 表名;
DISTINCT：用于去除查询结果中完全重复的行的关键字，仅保留结果集中唯一的记录；
UNION：合并两个 SELECT 查询的结果集（要求字段数和类型一致），自动去除重复记录；
UNION ALL：合并两个 SELECT 查询的结果集（要求字段数和类型一致），保留所有记录，不进行去重操作。

查询语句中使用运算和别名
在所有学生分数上加10分特长分：select name,(math+10) m,(english+10) e,(chinese+10) c from stu;
统计每个学生的总分：select name,(math+english+chinese) 总分 from stu;
使用别名表示学生分数：select name,(math+english+chinese) 总分 from stu;

使用where条件过滤
查询姓名为聪聪的学生成绩：select name,math,chinese from stu where name = ‘聪聪’;
查询英语成绩大于90分的同学：select name,english from stu where english > 20;
查询总分大于200分的所有同学：select name,math+english+chinese from stu where (math+english+chinese) > 200;

where子句中出现的运算：

• > < <= >= = <> 大于、小于、大于(小于)等于、不等于
  判断字段值与指定值（或表达式）的关系，符合条件返回 true 不符合条件返回 false
• in 用于指定范围查询的运算符
  例如：select * from stu where math in (78,18,99);
• like 模糊查询，通常与 WHERE 子句配合使用，查找符合模糊的条件的数据，通过通配符来匹配字符串中的部分字符
  例如：select * from stu where name like ‘%张%’;
• and与 or或者 not非
  其中 and 的优先级高于 or，这是产生 sql 注入的根本
• 判断某一个字段记录是否为空
  ISNULL(expr)：判断表达式是否为 NULL
  判断表达式 expr 的结果是否为 NULL，返回布尔值（1表示是NULL，0表示不是NULL）；
  IFNULL(expr1, expr2)：替换 NULL 为指定值
  如果第一个表达式 expr1 是 NULL，则返回第二个表达式 expr2，否则返回第一个表达式 expr1（用于替换NULL值为指定内容）；
  NULLIF(expr1, expr2)：两值相等则返回 NULL
  如果两个表达式的值相等，则返回NULL，否则返回第一个表达式 expr1（用于在特定相等条件下返回NULL）。

order by 对查询的结果进行排序
order by 子句用于对查询结果按照一个或多个列进行排序，可以指定升序(ASC)或降序(DESC)排列，如果不指定排序方向，默认是升序排列。
select * from 表名 where 条件 order by 列名 升序/降序;
order by 子句必须出现在select语句的末尾。

聚合函数
聚合函数用于对一组数据进行计算并返回单个结果，常用于统计分析场景
count()：计算行数，sum()：计算数值总和，avg()：计算平均值，max()：获取最大值，min()：获取最小值
select 后只能出现两种内容：group by 中指定的分组列或聚合函数

group by 分组条件查询
当需要按某个维度对数据进行分组统计时，使用 group by 子语句，group by 会将相同值的行归为一组，然后对每组应用聚合函数
过滤分组后的结果用 having 子句

总结：select … from … where … group by … having … order by …
固定的顺序，如果没有上述的条件，把关键字去掉就可以

多表查询之内链接
前提条件需要两个表有联系，通过外键关联

• 普通内链接
  语法：关键字 … inner join … on 条件;
  注意：在inner join关键字之前写表1，在inner join关键字之后写表2，on 后面写条件
  select * from dept inner join emp on dept.did = emp.dno;
• 隐式内链接（用的最多的）
  语法：select … from 表1,表2 where 表1.字段 = 表2.字段;
  语句：select * from dept,emp where dept.did = emp.dno;
  别名：select * from dept d,emp e where d.did = e.dno;
  指定字段： select d.dname,e.ename,e.sal from dept d,emp e where d.did = e.dno;

多表查询之外链接

• 左外链接
  语法：… 表1 left outer join 表2 on 表1.字段 = 表2.字段
  语句： select * from dept left outer join emp on dept.did = emp.dno;
  特点：看左表，默认把左表中的全部数据都查询出来，再查询出有关联的数据。
• 右外链接
  语法：… 表1 right outer join 表2 on 表1.字段 = 表2.字段
  特点：看右表，默认把右表中的全部数据都查询出来，再查询出有关联的数据。

子查询
子查询是嵌套在其他 SQL 语句中的查询语句，也称为嵌套查询，它的核心作用是通过内层查询的结果作为外层查询的条件或数据源，实现复杂的逻辑查询。