MySQL学习笔记

最新推荐文章于 2023-06-09 00:13:43 发布

Dylan_r

最新推荐文章于 2023-06-09 00:13:43 发布

阅读量898

点赞数

分类专栏：笔记文章标签： mysql

本文链接：https://blog.csdn.net/weixin_46819313/article/details/119296130

版权

MySQL

javaEE:企业级java开发 Web

前端（页面：展示数据！）

后台（连接点：连接数据库JDBC，Mybatis，连接前端（servlet、Spring））控制视图跳转，和给前端传数据

数据库（存数据）

Why Learning Database？

1、岗位需求

2、大数据时代，得数据者得天下

3、被迫需求：存数据

4、数据库是所有软件体系中最核心得存在

What is a Database？

数据库（DB）

概念：数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在[计算机内的、有组织的、可共享的、统一管理的大量数据的集合

作用：存储数据、管理数据

Database classification

关系型数据库（SQL）：

MySQL 、Oracle、Sql Server、DB2、SQLite 。。。。。。
通过表和表之间，行和列之间的关系进行数据的存储，学员表、考勤表…

非关系型数据库（NOSQL）(Not Only)：

Redis、MongDB
对象存储，通过对象自身的属性来决定。

DBMS(数据库管理系统)

数据库的管理软件，科学有效的管理我们的数据，维护和获取数据；
MySQL(关系型数据库管理系统)

在这里插入图片描述

MySQL 简介

MySQL是一个关系型数据库管理系统

由瑞典[MySQL AB](https://baike.baidu.com/item/MySQL AB/2620844) 公司开发，属于 Oracle 旗下产品

MySQL 是最流行的关系型数据库管理系统之一，

MySQL是开放源码软件，体积小、速度快，因此可以大大降低总体拥有成本。

中小型网站、大型网站，集群！

教程：https://www.cnblogs.com/xa-xiaochen/p/14684423.html

Sqyog软件安装：https://pan.baidu.com/s/1hK-YaUH2TjYJlVcUsHyLXA#list/path=%2F

创建数据库：

在这里插入图片描述

每一个sqlyog的执行操作，本质就是对应了一条sql语句，可以在历史记录查看

非常好用！

建表：

在这里插入图片描述

命令行

create database databaseName ; //创建一个数据库

DROP DATABASE databaseName;//删除数据库

mysql -uroot -p;//连接数据库

show databases;//查看所有数据库

use databaseName//使用/切换数据库

show tables;//查看数据库中的所有表

describe tableName;//查看数据库中该表的描述信息

SQL注释： --（单行注释）  /*多行注释*/

CREATE TABLE `teacher`( `id` INT(10) NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL, `age` INT(3) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; //创建表

ALTER table tableName RENAME AS newTableName//修改表名

ALTER table tableName ADD age int(11) //增加字段

ALTER table tableName MODIFY age varcher(11)//修改字段类型（约束）

ALTER table tableName CHANGE age  ageNew//修改字段名（重命名）

ALTER table tableName DROP age //删除表的字段

 DROP table  IF EXISTS tableName //删除表

字段最好用 `` （Tab键上面）包起来

数据库的列类型：

数值

数据类型	描述	大小
tinyint	十分小的数据	1Byte
smallint	较小的数据	2Byte
mediumint	中等大小的数据	3Byte
int	标准的数据	4Byte
bigint	较大的整数	8Byte
float	浮点数	4Byte
double	浮点数	8Byte
decimal	字符串形式的浮点数（金融计算）	数字型，128bit

字符串

数据类型	描述	大小
char	固定大小字符串	0-255
varchar	可变字符串	0-65535
tinytext	微型文本	2^8 -1
text	文本串(大文本)	2^16 -1

时间日期

类型	格式	描述
data	YYYY-MM-DD	日期格式
time	HH:mm:ss	时间格式
datatime	YYYY-MM-DD HH:mm:ss	常用时间格式
timestamp	1970.1.1到现在的毫秒数	时间戳
year	-	年份表示

数据库的字段属性

Unsigned：

无符号的整数
声明了该列不能为负数

zerofill：

使用零来填充
不足的位数使用零来填充 int 设置长度为3， 5就填充为005

自增：

自动在上一条的基础上加1（默认）
填充用来设计唯一的主键（必须是整数类型）
可以自定义设计主键的起始值和步长

非空: NOT NULL

设置为NOT NULL，不赋值就会报错
NULL 如果不填写值，默认就是NULL

默认：

设置默认的值
sex，默认值为男，如果不指定值就为男

/*
每一个表，都必须存在以下五个字段！
id  主键
version  	乐观锁
is_delete 	伪删除
gmt_create  创建时间
gmt_update  修改时间
*/

建表语句：

 student2  CREATE TABLE `student2` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `address` varchar(100) DEFAULT 'NULL' COMMENT '家庭住址',
  `email` varchar(50) DEFAULT 'NULL' COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

格式

create table tableName(
	`字段名` 列类型 [属性] [索引] [注释]
	`字段名` 列类型 [属性] [索引] [注释]
    ......
	`字段名` 列类型 [属性] [索引] [注释]
    
)[表类型][字符集设置][注释]

常用命令：

show create database databaseName//查看创建数据库的语句

show create table tableName //查看创建数据表的语句
    
desc tableName//显示表的结构

数据表的类型

/*数据库引擎：

INNODB(默认使用)

MYISAM（早年间）
*/

	INNODB	MYISAM
事务支持	支持	不支持
数据行锁定	支持（行锁）	不支持（表锁）
外键	支持	不支持
全文索引	不支持	支持
表空间大小	较大	较小

常规使用操作：

MYISAM:节约空间、速度较快
INNODB：安全性高、支持事务的处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都在data目录下，一个文件夹对应一个数据库

本质还是文件存储！

MySQL 引擎在物理文件上的区别

INNODB 在数据库表中只有一个 *.frm文件以及上级目录下的 ibdata1 文件
MYISAM:
- *.frm文件（表结构的定义文件），
- *.MYD文件（数据文件）
- *.MYI文件（索引文件）

设计数据库表的字符集编码

CHARSET=utf8

不设置的话，会是mysql默认的字符集编码（不支持中文！）

MySQL默认编码是Latin1，不支持中文

在 my.ini 中配置默认的编码

character-set-server=utf8

MySQL数据管理

外键

方式一：创建表时增加约束

CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT
 COMMENT '年级id',
 `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
 PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8`grade`

CREATE TABLE `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',`student`
  `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
  `address` VARCHAR(100) DEFAULT 'NULL' COMMENT '家庭住址',
  `email` VARCHAR(50) DEFAULT 'NULL' COMMENT '邮箱',
   PRIMARY KEY (`id`),
   KEY  `FK_gradrid` (`gradeid`) ,
   CONSTRAINT `FK_gradrid` FOREIGN KEY (`gradeid`) REFERENCES      `grade` (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表的时候，必须要先删除引用别人的表，再删除被引用的表

方式二

-- 建表的时候没有外键关系  建表完成后添加外键关系
/*  ALTER TABLE `student`
    ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`)       	  REFERENCES `grade`(`gradeid`);
*/

格式：

ALTER TABLE tableName ADD CONSTRAINT 约束名 FOREIGN KEY （字段名）

REFERENCES targeTableName（字段名）

以上操作都是物理外键，数据库级别的外键，我们不建议使用（避免数据过多造成困扰）

最佳实现：

数据库就是单纯的表，只用来存数据，只有行和列
想使用多张表的数据，程序实现

DML语言

DML语言：数据操作语言

insert
update
delete

添加

insert into tableName(字段名，2，3...)values(值1，2，3...);
//根据字段名匹配值（一一对应）

insert into tableName values（值1，2，3...）
//根据字段名顺序匹配 值
    
insert into tableName values（值1，2，3...），（值1，2，3...）...
//插入多行

修改


update tableName SET colnum_name='xxx' WHERE 条件;//修改（带条件）

update tableName SET colnum_name1='xxx'，
colnum_name2='xxx' ...
WHERE 条件;//修改多个（带条件）

//谨慎使用！
update tableName SET name='xxx' ;//修改名字（不带条件）修改全部名字

/*条件：
  where    字句    运算符
  
  运算符：
  基本运算符   =  <= .....
  特殊运算符 BETWEEN ... AND ...   代表范围    （ BETWEEN 2 AND 5）
  【闭合区间】
  
//通过多个条件定位数据
update tableName SET colnum_name='xxx' WHERE 条件1 and 条件2 and 条件3...;

删除

delete from tableName  //避免这样写，会全部删除

delete from tableName  where [条件] //删除一条记录（一行）

truncate tableName  //清空表

相同点：都能删除数据，都不会删除表结构
不同点
- truncate 重新设置自增列，计数器会清零 delete不会
- truncate 不会影响事务

DELETE的问题：删除完毕后，重启数据库，现象：

INNODB：自增列从1开始（存在内存中，断电即失）
MYISAM：继续从上一个自增列开始（存在文件中，不会丢失）

DQL查询数据

所有的查询语句都用它

select * from  tableName   //查询表的全部信息

select clonum_name1,clonum_name2  from  tableName
    //查询指定字段

select clonum_name1 AS xxx,clonum_name2 AS xxx from  tableName AS newTableName
//给查询结果取别名,也可以给表取别名

//函数:
Concat(a,b)//拼接字符串 => ab

select concat('需要追加的字符串',clonum_name) AS newClonum_name from  tableName;

select distinct 字段名 from tableName//发现重复数据 去重

模糊查询（比较运算符）

运算符	语法	描述
IS NULL	a is null	如果操作符为null，结果为真
IS NOT NULL	a is not null	如果操作符为not null，结果为真
BETWEEN	a between b and c	若a在b和c之间，结果为真
LIKE	a like b	a匹配b
IN	a in (a1,a2,a3…)	a在这些里面(其中一个)

select clonumName1,clonumName2 form tableName where
clonumName2  like '刘%'
    //查询姓刘的人
    
select clonumName1,clonumName2 form tableName where
clonumName2  like '刘_' 
    //查询姓刘的人，名字只有一个字的
  select clonumName1,clonumName2 form tableName where
clonumName2  like '刘__' 		//（两个下划线）
    //查询姓刘的人，名字只有两个字的  
    
   select clonumName1,clonumName2 form tableName where
clonumName2  like '%刘%' 		//（两个下划线）
    //查询名字中带有’ 刘 ‘的人

in 具体的一个或多个值

   select clonumName1,clonumName2 form tableName where
clonumName1  in (1001,1002,1003) ;	//范围	
    //查询1001,1002,1003 号学员

   select clonumName1,clonumName2 form tableName where
clonumAddressName1  in ('安徽') ;	//范围	
    //查询地址在安徽的学员

联表查询

分析需求，分析查询的字段来自哪些表（连接查询）？

确定使用那种连接查询？

表的内连接

select s.studentNo,studentName,SubjectNo,studentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo = r.studentNo
    
 //查询学生学号、名字、学科、成绩，因为学生表里面没有成绩和学科，所以要关联成绩表，它们之间的交叉点是 学生学号  这叫内联查询

左连接

select s.studentNo,studentName,SubjectNo,studentResult
FROM student s
LEFT JOIN result r
ON s.studentNo=r.studentNo
    
 //左外连接

这里写图片描述

select s.studentNo,studentName,SubjectNo,studentResult
FROM student s
RIGHT JOIN result r
ON s.studentNo=r.studentNo
    
 //右外连接

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回行，即使右表中没有匹配
right join	会从右表中返回行，即使左表中没有匹配

//查询缺考的同学
select s.studentNo,studentName,SubjectNo,studentResult
FROM student s
LEFT JOIN result r
ON s.studentNo=r.studentNo
WHERE studentResult IS NULL

join（连接的表） on （判断的条件）：连接查询

where 等值查询

E：查询了参加考试的学生信息：学号、学生姓名、科目名、分数

在三张不同的表里面

select s.studentNo,studentName,SubjectName studentResult
from student s
right join result r
on r.studentNo=s.studentNo
//先查学生表 和 考试表  right join  以考试表为基准
    
inner join subject sub
on  r.studentNo=sub.studentNo;

自连接

自己的表和自己的表连接核心：一张表拆为两张表

在这里插入图片描述

父类：

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类：

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息(把一张表看成两张一模一样的表)
SELECT a.categoryname AS '父栏目',b.categoryname AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`;

结果：

在这里插入图片描述

-- 分页和排序  升序：ASC  降序 DESC
-- 排序 
 order by clonumName  ASC/DESC
 
 -- 分页：
 limit 0-pageSize;

-- 查询数据库1的所有考试结果（学号、科目编号、成绩），降序排序

-- 方式一 使用连接查询
select studentNo,r.subjectNo,studentResult
from result r
inner join subject sub
on r.subjectNo=sub.subjectNo
where subjectName='数据库结构1'
order by studentResult DESC;

-- 方式二 子查询
select studentNo,subjectNo,studentResult
from result
where subjectNo=(
select subjectNo from subject
    where subjectName='数据库结构1'
)order by studentResult DESC;

-- 分数不小于80分的学生学号和姓名(连接查询)
select s.studentNo,studentName
from student s
inner join result r
on s.studentNo=r.studentNo
where studentResult>=80;


-- 高等数学 分数不小于80分的学生学号和姓名(子查询)
select s.studentNo,studentName
from student s
inner join result r
on r.subjectNo=s.subjectNo
where studentResult>=80 and subjectNo=(
select subjectNo from subject 
where subjectName='高等数学'
);

-- 高等数学 分数不小于80分的学生学号和姓名(连接查询)
select s.studentNo,studentName
from student s
inner join result r
on s.subjectNo=r.subjectNo
inner join subject sub
on r.subjectNo=sub.subjectNo
where subjectName='高等数学' and studentResult>=80;

-- 继续改造(由里及外执行)
select studentNo,studentName from student where studentNo in(   
	select studentNo from result where studentResult>=80 and subjectNo =(
	 select subjectNo from subject where subjectName='高等数学'
	)
);

-- c语言 前5名同学的成绩信息（学号、姓名、分数）
select studentNo,studentName,studentResult
from student where studentNo in(
select studentNo from result 
where  studentResult in(
	select studentResult from result
    order by 0-5 DESC and subjectName=(
    		select  subjectName from subject
        	where subjectName='c语言'
    	)
	)
);

MySQL函数

一、数学函数

ABS(x)   返回x的绝对值

BIN(x)   返回x的二进制（OCT返回八进制，HEX返回十六进制）

CEILING(x)   返回大于x的最小整数值

EXP(x)   返回值e（自然对数的底）的x次方

FLOOR(x)   返回小于x的最大整数值

GREATEST(x1,x2,...,xn)返回集合中最大的值

LEAST(x1,x2,...,xn)      返回集合中最小的值

LN(x)                    返回x的自然对数

LOG(x,y)    返回x的以y为底的对数

MOD(x,y)                 返回x/y的模（余数）

PI()返回pi的值（圆周率）

RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数

生成器生成一个指定的值。

ROUND(x,y)返回参数x的四舍五入的有y位小数的值

SIGN(x) 返回代表数字x的符号的值

SQRT(x) 返回一个数的平方根

TRUNCATE(x,y)            返回数字x截短为y位小数的结果

二、聚合函数(常用于GROUP BY从句的SELECT查询中)

AVG(col)返回指定列的平均值

COUNT(col)返回指定列中非NULL值的个数

MIN(col)返回指定列的最小值

MAX(col)返回指定列的最大值

SUM(col)返回指定列的所有值之和

GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

三、字符串函数

ASCII(char)返回字符的ASCII码值

BIT_LENGTH(str)返回字符串的比特长度

CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串

CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔

INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字

符串instr，返回结果

FIND_IN_SET(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果

LEFT(str,x)返回字符串str中最左边的x个字符

LENGTH(s)返回字符串str中的字符数

LTRIM(str) 从字符串str中切掉开头的空格

POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置

QUOTE(str) 用反斜杠转义str中的单引号

REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果

REVERSE(str) 返回颠倒字符串str的结果

RIGHT(str,x) 返回字符串str中最右边的x个字符

RTRIM(str) 返回字符串str尾部的空格

STRCMP(s1,s2)比较字符串s1和s2

TRIM(str)去除字符串首部和尾部的所有空格

UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果

四、日期和时间函数

CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
DATE_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(date,fmt)  依照指定的fmt格式格式化日期date值
DATE_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date

最低0.47元/天解锁文章

Dylan_r

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习笔记

MySQLjavaEE:企业级java开发 Web前端（页面：展示数据！）后台（连接点：连接数据库JDBC，Mybatis，连接前端（servlet、Spring））控制视图跳转，和给前端传数据数据库（存数据）Why Learning Database？1、岗位需求2、大数据时代，得数据者得天下3、被迫需求：存数据4、数据库是所有软件体系中最核心得存在What is a Database？数据库（DB）概念：数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在
复制链接

扫一扫