数据库概述
为什么要学习数据库
因为数据库可以实现数据持久化到本地；使用完整的管理系统统一管理，可以实现结构化查询，方便管理。
数据库（DB全程为DataBase）是存储数据的容器，将数据通过特定的规则存储在磁盘上。通过数据库管理系统来组织和管理存储在数据库中的数据。
数据库管理系统（DataBase Management System）又称为数据库软件和数据库产品，用于创建或管理数据库（DB）。
结构化查询语言（Structure Query Language）也就是SQL语句。
根据不同的存储类型，可以将数据库分为关系型数据库和非关系型数据库，MySql就属于关系型数据库，关系型数据库管理系统统称为RDBMS，还有Oracle(功能强大、收费)、SQL Server(微软)：只能在Windows系统上运行。非关系型数据库有Redis、MongoDB等等。
MySQL的语法规范
- 不区分大小写，建议关键字大写，表名、列名小写
- 每句话用;
MySQL的注释要求
- 单行注释：#注释文字
- 单行注释：-- 注释文字（要有空格）
- 多行注释：/* 注释文字 */
MySQL启动
net start mysql80
net stop mysql80
MySQL客户端连接
MySQL自带的客户端命令行
mysql [-h 127.0.0.1] [-p 3306] -u root -p
SQL
SQL是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库。基本上全部的DBMS都支持SQL，并且它简单易学。
数据库存储数据的特点
- 数据放到表中，表在放到数据库中
- 一个数据库中可以有多个表，每个表都有自己唯一的名字，用来标识自己。
- 表具有一些特性，定义了这些数据要在表中如何储存，和Java中构造类时设计类类似。
- 表是由列组成的，列也可以称为字段，一个表中有一个或多个列组成。
- 数据库中的数据都是以表存放的，表中的数据都是以行来存储的。
DDL——数据定义语言，用来定义数据库对象（数据库、表、字段）
数据库操作
查询
查询所有数据库
SHOW DATABASES;
查询当前数据库
SELECT DATABASE();
创建
CREATE DATABASES [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]；
删除
DROP DATABASE 数据库名 / [IF EXISTS数据库名];使用
USE 数据库名；
设计表
设计表的时候需要先确定表名，确定字段名，再规定该字段的数据类型和长度，该表中有的约束有哪些分别在哪些字段上。
除了确定字段的数据类型，对表的创建、修改、删除以及更新（都是对表结构的操作）等都属于DDL操作。
查询当前所有数据库的表
SHOW TABLES;
查询表结构
DESC 表名;
查询指定表的建表语句
SHOW CREATE TABLE 表名;
数据类型
| 分类 | 类型 | 大小 | 有符号（SIGNED）范围 | 无符号范围（SIGNED）范围 | 描述 |
| 数 值 类 型 | TINYINT | 1byte | （-128，127） | （0，255） | 小整数值 |
| SMALLINT | 2bytes | （-32768， 32767） | （0，65535） | 大整数值 | |
| MEDIUMINT | 3bytes | （-8388608， 8388607） | （0，16777215） | 大整数值 | |
| INT或INTEGER | 4bytes | （-2147483648， 2147483647） | （0，4294967295） | 大整数值 | |
| BIGINT | 8 bytes | (-3. 402823466 E+38， 3.402823466351 E+38) | (0，2^64-1) | 极大整数值 | |
| FLOAT | 4 bytes | (-3. 402823466 E+38， 3.402823466351 E+38) | 0和(1.175494351 E-38， 3.402823466 E+38) | 单精度浮点数值 | |
| DOUBLE | 8 bytes | (-1.7976931348623157 E+308， 1. 7976931348623157 E+308) | 0和(2.2250738585072014 E-308， 1.7976931348623157 E+308) | 双精度浮点数值 | |
| DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M (精度)和D (标度)的值 | 小数值（精确定点数） |
CHAR和VARCHAR的区别：CHAR是定长字符串，VARCHAR是变长字符串，他们的大小范围也是不一样的，CHAR比如给定的长度是10，当存入一个字符时也是占用了10（会用空格进行补位），但VARCHAR给定的长度是10存入一个字符就是占用一个字符的长度。比如存储用户的用户名时，最好还是使用VARCHAR，而性别就占用一个字符，所以使用CHAR就可以。
TINYBLOB存储的是不超过255字符长度的二进制数据，BOLB存储的是二进制形式的长文本数据，像视频、音频都属于二进制数据。
时期类型中有DATE、TIME、YEAR、DATETIME、TIMESTAMP，其中DATE的长度大小为3，范围1000-01-01 至9999-12-31，格式是YYYY-MM- -DD，指的是时期值。TIME指的是时间值或者持续时间，长度也是3，格式是HH :MM:SS 。DATETIME就是将DATE和TIME相结合，是混合日期和时间值，格式为YYYY- -MM- _DD HH:MM:SS。
DML——数据库操纵语言
常用的语法就是insert、update、delete，也就是在表中插入、修改、删除数据。
DQL——数据查询
数据查询包括基本查询、条件查询（where）、聚合函数、分组查询（group by）、排序查询（order by）和分页查询(limit)。
基本查询
select 字段1，字段2，字段3…… from 表名 ;select * from 表名;设置别名select 字段1 [as别名1],字段2 [as别名2] .. from表名;去除重复记录
select distinct字段列表from表名;
select 字段1，字段2，字段3…… from 表名 where条件列表;
| 比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <或!= | 不等于 |
| BETWEEN ... AND .. | 在某个范围之内(含最小、最大值) |
| IN(..) | 在in之后的列表中的值，多选一 |
| LIKE占位符 | 模糊匹配(匹配单个字符，%匹配任意个字符) |
| IS NULL | IS NULL |
| 逻辑运算符 | 功能 |
| AND或&& | 并且(多个条件同时成立) |
| OR或| | 或者(多个条件任意-个成立) |
| NOT或! | 非，不是 |
-- 查询年龄在20、21、22的女同学的信息
SELECT * FROM student WHERE gender='女'AND age IN(20,21,22,23);
-- 查询年龄在19-21之间，姓名为三个字的男同学
SELECT * FROM student WHERE gender='男' AND (age BETWEEN 19 AND 21) AND LIKE'___';
-- 查询姓刘且三个字的同学（如果只是姓刘就可以使用%）
SELECT * FROM student WHERE LIKE'刘__';聚合函数
- case when 条件 then 结果1 else 结果2 end; 可以有多个when
- ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回
- 原本的值
- if函数：
- if else的 效果 if(条件,结果1,结果2)
字符函数
- 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)：替换，替换所有的子串
-- 学生的学号，统一为五位数，目前不足五位数的统一向前补零
UPDATE student SET studentId=LPAD(studentId,10,0);
-- 通过数据库的函数，生成一个六位数的随机码（rand()生成的是0-1的随机数）
rpad(round(rand()*1000000, 0), 6, 0);- round(数值)：四舍五入，前面输入数值，后面输入保留几位小数
- ceil(数值)：向上取整，返回>=该参数的最小整数
- floor(数值)：向下取整，返回<=该参数的最大整数
- truncate(数值,保留小数的位数)：截断，小数点后截断到几位
- mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负
- rand()：获取随机数，返回0-1之间的小数
- now()：返回当前系统日期+时间
- curdate()：返回当前系统日期，不包含时间
- curtime()：返回当前时间，不包含日期
- 可以获取指定的部分，年、月、日、小时、分钟、秒
- YEAR(日期),MONTH(日期),DAY(日期) ,HOUR(日期) ,MINUTE(日期)
- SECOND(日期)
- str_to_date：将日期格式的字符转换成指定格式的日期
- date_format：将日期转换成字符串
- datediff(big,small)：返回两个日期相差的天数
-- 查询学生的在校天数，并按照降序来排列
SELECT NAME,DATEDIFF(CURDATE(),entrydate) AS 'entrydays' FROM student ORDER BY entrydays DESC;
分组函数
- 功能：用作统计使用，又称为聚合函数或统计函数或组函数
- 分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数 （非空）
- 以上的分组函数都忽略null值
- count函数一般使用count（*）来统计行数
流程控制函数
- case when 条件 then 结果1 else 结果2 end; 可以有多个when
- ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回
- 原本的值
- if函数：
- if else的 效果 if(条件,结果1,结果2)
分组查询
SELECT字段列表FROM表名[WHERE 条件] GROUP BY分组字段名[ HAVING分组后过滤条件];
-- 根据性别分组，统计男同学和女同学的数量
select gender,count(*) from student group by gender;
-- 根据性别分组，统计男同学和女同学的平均年龄
select gender,avg(age) from student group by gender;
-- 选出学生年龄大于20，且学生按照学院分类，人数>=3的学院
select department,count(*) depart_count from student where age>20 group by department having depart_count>=3;
where和having的区别
- 执行的时机不同，where是分组之前进行过滤，having是在分组之后进行过滤，如果where过滤条件没过也不能进入分组。
- 判断条件不同，where不能对聚合函数进行判断，但是having可以，就比如说上述的SQL语句，使用count来统计学院人数 ，然后再进行过滤大于三个人的学院。
排序查询
排序查询语法
SELECT字段列表FROM表名ORDER BY字段1排序方式1 , 字段2排序方式2;
排序方式
- 升序（asc）是默认的
- 降序(desc)
如果是多字段排序，第一个字段相同时，第二个字段才会排序
-- 根据年龄对男同学进行升序处理(查询结果如果年龄为null,会显示null)
select name,gender,age from student order by age;
-- 根据同学们的年龄进行降序处理，如果年龄相同，再根据学号进行升序处理
select name,gender,age,studentID from student order by age desc,studentID asc;分页查询
SELECT字段列表FROM表名LIMIT 起始索引，查询记录数;
注意：
- 最初的起始索引从0开始，起始索引=（查询页码-1）*想查询的数据数量。
- 分页查询不同的数据库有不同的实现，再MySQL中是limit。
- 如果想要查询的是第一页数据，可以直接省略，写limit10即可。
-- 一页数据量为5，查询第二页的数据
SELECT * FROM student LIMIT 5,5;
-- 一页数据为10，查询第一页的数据
SELECT * FROM student LIMIT 10;SQL查询语句的执行顺序
先执行from查看来自于哪张表，再通过where的条件过滤数据，根据group by进行分组并对分组后的数据使用having进行过滤，通过select选择显式的字段对应的数据，使用order by进行排序，limit进行分页。
DCL——数据控制语言
DCL数据控制语言，用来管理数据库用户、控制数据库的访问权限。
查询用户
USE mysql;
SELECT * FROM user;
创建用户
CREATE USER '用户名'@'主机名’IDENTIFIED BY '密码' ;
修改用户密码
ALTER USER '用户名’@'主机名’ IDENTIFIED WITH mysql native_ _password BY新密码’ ;
删除用户
DROP USER '用户名'@'主机名' ;
DCL——用户管理
-- 创建用户summer，只能在当前主机localhost访问，密码是123456
CREATE USER 'summer'@'localhost' IDENTIFIED BY'123456';
-- 创建用户spring，可以在任意主机访问该数据库，密码是123456
CREATE USER 'spring'@'%' IDENTIFIED BY'123456';
-- 修改spring的访问密码
ALTER USER 'spring'@'%' IDENTIFIED WITH mysql_native_password BY '666666';
-- 删除spring
DROP USER 'spring'@'%';
-- 主机名可以使用%通配DCL——权限控制
查询权限
SHOW GRANTS FOR '用户名’@'主机名’ ;
授予权限
GRANT权限列表ON数据库名表名TO '用户名’@'主机名';
撤销权限
REVOKE权限列表 ON数据库名.表名FROM ‘用户名'@'主机名';
- 多个权限之间可以用，来进行分隔
- 授权时数据库名和表名可以使用*通配，代表所有
约束
外键约束
添加外键的两种方式
CREATE TABLE表名(
字段名数据类型,
……
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名ADD CONSTRAINT 外键名称FOREIGN KEY (外键字段名) REFERENCES主表(主表列名);
外键的删除和更新行为
| 行为 | 说明 |
|---|---|
| NO ACTION | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与 RESTRICT一致) |
| RESTRICT | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与 NO ACTION一致) |
| CASCADE | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。 |
| SET NULL | 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null (这就要求该外键允许取null)。 |
| SET DEFAULT | 父表有变更时，子表将外键列设置成一一个默认的值(Innodb不支持) |
ALTERTABLE表名ADD CONSTRAINT外键名称FOREIGN KEY (外键字段)
REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
多表查询
因为业务之间互相关联，涉及到多张表之间的关系问题，各种表之间也存在着各种联系，主要为多对多、一对多（多对一）、一对一。
- 在多对多的实现中，建立一张中间表来联系另外的两张表，中间表中需要包含两个各自表中的外键。
- 在一对多（多对一）的实现中，将外键创建在字段较多的那一张，指向一方的主键。
- 在一对一的情况下，将单表拆分，将表中的基础字段放在一张表，详细字段再放到另一张表中，提高查找的效率。
741
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
