mysql数据库常见问题 mysql数据库相关知识

转载

mob64ca1405a060 2023-08-08 15:45:17

文章标签 mysql数据库常见问题数据库 mysql sql 字段 文章分类 MySQL 数据库

数据库概述

为什么要学习数据库

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

数据库（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，也就是在表中插入、修改、删除数据。

插入数据

方式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) 查询语句(查询的列数与插入列数匹配)

修改数据

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

删除数据

DELETE FROM 表名 WHERE 条件

TRUNCATE TABLE 表名;清空整张表

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表名;

当然as的使用并不是必要的，as是可以省略的。

条件查询

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不支持)