【数据库】MySQL笔记

Codingemon

已于 2024-02-22 10:46:54 修改

阅读量474

点赞数 4

分类专栏：数据库文章标签： mysql 数据库 database

于 2021-12-26 13:31:16 首次发布

本文链接：https://blog.csdn.net/qq_51000584/article/details/122154444

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL

数据类型

整型
tinyint 1字节 -128~127
tinyint(1) 相当于布尔类型 括号中数字表示二进制位数5
smallint 2字节 -32768~32767
mediumint 3字节
int 4字节 -2147483648~2147483647
integer 4字节 和 int没区别 
bigint 8字节

浮点型
float 4字节
double 8字节

字符串型
char(n) n代表字符个数 占用n位
varchar(n) 占用实际字符个数
text 占用实际字符个数
mediumtext 占用实际字符个数
longtext 占用实际字符个数

位型
bit(m) 类型允许存储m位值，m取1~64


日期
date(储存形式是 年-月-日)

建立xs、kc、xs_kc表/*直接允许，方便后面代码练习*/
Create table xs(
学号 char(6) not null,
姓名 char(8) not null,
专业名 char(10) null,
性别 tinyint(1) not null,
出生日期 date not null,
总学分 tinyint(1) null,
照片 blob null,
备注 text null,
primary key(学号)
);

Create table kc(
课程号 char(3) not null,
课程名 char(16) not null,
开课学期 tinyint(1) not null,
学时 tinyint(1) not null,
学分 tinyint(1) null,
primary key(课程号)
);

Create table xs_kc(
学号 char(6) not null,
课程号 char(3) not null,
成绩 tinyint(1) null,
学分 tinyint(1) null,
primary key(课程号,学号)
);

insert into xs values('081101','王林','计算机','1','1994-02-10','50','null','null');
insert into xs values('081102','程明','计算机','1','1995-02-01','50','null','null');
insert into xs values('081103','王燕','计算机','0','1993-10-06','50','null','null');
insert into xs values('081104','韦严平','计算机','1','1994-08-26','50','null','null');
insert into xs values('081106','李方方','计算机','1','1994-11-20','50','null','null');
insert into xs values('081107','李明','计算机','1','1994-05-01','54','null','null');
insert into xs values('081108','林一帆','计算机','1','1993-08-05','52','null','null');
insert into xs values('081109','张强民','计算机','1','1993-08-11','50','null','null');
insert into xs values('081110','张蔚','计算机','0','1995-07-22','50','null','null');
insert into xs values('081111','赵琳','计算机','0','1994-03-18','50','null','null');
insert into xs values('081113','严红','计算机','0','1993-08-11','48','null','null');
insert into xs values('081201','王敏','通信工程','1','1993-06-10','42','null','null');
insert into xs values('081202','王林','通信工程','1','1993-01-29','40','null','null');
insert into xs values('081204','马琳琳','通信工程','0','1993-02-10','42','null','null');
insert into xs values('081206','李计','通信工程','1','1993-09-20','42','null','null');
insert into xs values('081210','李红庆','通信工程','1','1993-05-01','44','null','null');
insert into xs values('081216','孙祥欣','通信工程','1','1993-03-09','42','null','null');
insert into xs values('081218','孙研','通信工程','1','1994-10-09','42','null','null');
insert into xs values('081220','吴薇华','通信工程','0','1994-03-18','42','null','null');
insert into xs values('081221','刘燕敏','通信工程','0','1993-11-12','42','null','null');
insert into xs values('081241','罗林琳','通信工程','0','1994-01-30','50','null','null');

insert into kc values('101','计算机基础','1','80','5');
insert into kc values('102','程序设计与语言','2','68','4');
insert into kc values('206','离散数学','4','68','4');
insert into kc values('208','数据结构','5','68','4');
insert into kc values('209','操作系统','6','68','4');
insert into kc values('210','计算机原理','5','85','5');
insert into kc values('212','数据库原理','7','68','4');
insert into kc values('301','计算机网络','7','51','3');
insert into kc values('302','软件工程','7','51','3');

insert into xs_kc values('081101','101','80','5');
insert into xs_kc values('081101','102','78','4');
insert into xs_kc values('081101','206','76','4');
insert into xs_kc values('081102','102','78','4');
insert into xs_kc values('081102','206','78','4');
insert into xs_kc values('081103','101','62','5');
insert into xs_kc values('081103','102','70','4');
insert into xs_kc values('081103','206','81','4');
insert into xs_kc values('081104','101','90','5');
insert into xs_kc values('081104','102','84','4');
insert into xs_kc values('081104','206','65','4');
insert into xs_kc values('081106','101','65','5');
insert into xs_kc values('081106','102','71','4');
insert into xs_kc values('081106','206','80','4');
insert into xs_kc values('081107','101','78','5');
insert into xs_kc values('081107','102','80','4');
insert into xs_kc values('081107','206','68','4');
insert into xs_kc values('081108','101','85','5');
insert into xs_kc values('081108','102','64','4');
insert into xs_kc values('081108','206','87','4');
insert into xs_kc values('081109','101','66','5');
insert into xs_kc values('081109','102','83','4');
insert into xs_kc values('081109','206','70','4');
insert into xs_kc values('081110','101','95','5');
insert into xs_kc values('081110','102','90','4');
insert into xs_kc values('081110','206','89','4');
insert into xs_kc values('081111','101','91','5');
insert into xs_kc values('081111','102','70','4');
insert into xs_kc values('081111','206','76','4');
insert into xs_kc values('081113','101','63','5');
insert into xs_kc values('081113','102','79','4');
insert into xs_kc values('081113','206','60','4');
insert into xs_kc values('081201','101','80','5');
insert into xs_kc values('081202','101','65','5');
insert into xs_kc values('081203','101','87','5');
insert into xs_kc values('081204','101','91','5');
insert into xs_kc values('081210','101','76','5');
insert into xs_kc values('081216','101','81','5');
insert into xs_kc values('081218','101','70','5');
insert into xs_kc values('081220','101','82','5');
insert into xs_kc values('081221','101','76','5');
insert into xs_kc values('081241','101','90','5');

MySQL语言

create database 库名;/*建库*/

show databases;/*展示已建数据库*/

show tables;/*展示当前库中表*/

drop database 库名;/*删库*/

describe 表名;/*展示表结构*/

use 库名;/*打开数据库*/

create table 表名/*建表*/
(
    属性名 数据类型 是否允许空 键,
    ... ,
    属性名 数据类型 是否允许空
)

/*例如：*/
create table xs
(
学号 char(6) not null primary key,/*标明主键*/
姓名 char(8) not null,
性别 tinyint(1) not null,
年龄 tinyint(1) not null,
专业 char(10) not null,
系别 char(8) not null/*最后一句没有逗号，切记*/
);

/*或者*/
create table xs
(
学号 char(6) not null,
姓名 char(8) not null,
性别 tinyint(1) not null,
年龄 tinyint(1) not null,
专业 char(10) not null,
系别 char(8) not null,
primary key(学号)/*标明主键*/
);

/*当有多个主键时，只能使用第二种方法，如：*/
create table kc
(
学号 char(6) not null,
课程号 char(3) not null,
成绩 tinyint(1) null,
学分 tinyint(1) null,
primary key(课程号,学号)
);

/*改表结构 add change modify rename drop */
1.增加列
alter table 表名 add 列名 数据类型 是否允许空;

2.改列名和数据类型
alter table 表名 change 旧列名 新列名 新数据类型;

3.改数据类型
alter table 表名 modify 列名 新数据类型 是否允许空值;

4.删除列名
alter table 表名 drop 列名;

5.改表名
alter table 表名 rename 旧表明 新表名;

6.删表
drop table 表名;

/*表记录*/
1.插入
insert into 表名 valuies('数据1','数据2','数据3',...,'');

2.改记录
update 表明 set 列名 = ‘新值’;

3.删除记录
delete from 表名 where 条件;/*删除单行记录*/

delete xs,xs1 from xs,xs1 where xs.学号 = xs1.学号;

/*select语句查询*/
1.
查询全部表中内容
select * from 表名;
select 要查询的列 from 表名;
select 要查询的列 from 表名 where 条件 ;
2.替换查询结果中数据
select 学号,姓名,总学分,
(case/*这里的括号很重要，书上没写括号，运行会报错*/
	when 总学分 is null then '尚未选课'
	when 总学分 < 50 then '不及格'
	when 总学分 >= 50 and 总学分 <= 52 then '合格'
	else '优秀'
end )
as 等级
from xs
where 专业名='计算机';
运行结果如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SiWtxBr3-1640496286825)(MySQL.assets/1638500339415.png)]

计算列值：
select 学号,课程号,成绩*1.20 as 成绩120
from xs_kc
where 学号='081101';
结果如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ebbiodtk-1640496286827)(MySQL.assets/1638500614164.png)]

消除重复行
select 专业名,总学分/*未消除重复行*/
from xs;
select distinct 专业名,总学分/*消除重复行*/
from xs;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j0PM1E0L-1640496286827)(MySQL.assets/1638500740744.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OdAYxC1i-1640496286828)(MySQL.assets/1638500749707.png)]

函数

count/*求组中项数*/
max/*求最大值*/
min/*求最小值*/
sum/*返回所有值的和*/
avg/*返回组中平均值*/
std/*标准差*/
variance/*方差*/
使用方法都一样，下面只展示几个

mysql> select count(*) as ‘学生总数’ from xs;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HElsRBaL-1640496286828)(MySQL.assets/1638501070611.png)]

select count(总学分) as ‘总学分50以上的人’ from xs where 总学分>50;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0piWUi3J-1640496286829)(MySQL.assets/1638501126920.png)]

select sum(成绩) as ‘总成绩’ from xs_kc where 学号=‘081101’;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ea9qmXLV-1640496286829)(MySQL.assets/1638501228460.png)]

from

select * from 表名;/*查询整个表情况*/

select kc.课程名,xs_kc.课程号/*查询某个表的某一列*/
from kc,xs_kc
where kc.课程号=xs_kc.课程号;

join连接：
select distinct 课程名,xs_kc.课程号
from kc join xs_kc/*此处表示连接表xs_kc*/
on (kc.课程号 = xs_kc.课程号);/*连接条件*/

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vToUAE9U-1640496286830)(MySQL.assets/1638535809235.png)]

连接多个表的情况:

select xs.学号,姓名,课程名,成绩
from xs join xs_kc on xs.学号=xs_kc.学号
join kc on xs_kc.课程号=kc.课程号
where 课程名='计算机基础' and 成绩>=80;

using

如果要连接的表中有列名相同，并且连接的条件就是列名相同，那么on可以换成using

select 课程名
from kc join xs_kc
using(课程号);/*查询结果为xs_kc表中所有出现的课程号对应的课程名*/

等于运算符 <=> （当两个表达式彼此相等或都等于空值时，它的值是true，否则是false）

where语句

####　1.模式匹配like

%代表0个或多个字符
_代表单个字符
/*当要匹配的字符串中含有和特殊符号'_'和'%'相同的字符时，需要进行转义*/
转义语句 escape
1.
select 学号,姓名,性别
from xs
where 姓名 like '王%';/*搜索王姓的人*/
2.
select 学号,姓名,性别
from xs
where 学号 like '%0_';/*搜索学号倒数第二个数字为0的学生*/
3.
select 学号,姓名
from xs
where 学号 like '%#_%' escape '#';/*此时'_'字符就只表示字符含义，失去了语言的含义*/

2.regexp

^ 匹配字符串的开始部分

$匹配字符串的结束部分

.匹配字符串的任意一个字符

*匹配星号之前的0个或多个字符的任意序列

+匹配加号之前的1个或多个字符的任意序列

?匹配问号之前的0个或多个字符

1.^
select 学号,姓名,专业名
from xs 
where 姓名 regexp '^李';/*查询李姓*/

2.[]
select 学号,姓名,专业名
from xs 
where 学号 regexp '[4,5,6]';/*查询学号里包含4、5、6的学生*/

3.    *   .    ^
select 学号,姓名,专业名
from xs 
where 学号 regexp '^08.*08$';/*查询学号以08开头，08结尾的学生*/

3.between and

1.
select 学号,姓名,专业名,出生日期
from xs 
where 出生日期 not between '1993-1-1' and '1993-12-31';/*查询不在1993年出生的学生*/

4.in

select *
from xs 
where 专业名 in ('计算机','通信工程','无线电');
它等价于
select *
from xs 
where 专业名 = '计算机' or  专业名 = '通信工程' or  专业名 = '无线电';

判断是否为空用is null语句

select *
from xs
where 专业名 is null;

select *
from xs
where 专业名 is not null;

5.子查询

select 姓名,学号/*子查询也是参照完整性的体现*/
from xs 
where 学号 in
(select 学号/*在另一个表中查询学号进行匹配*/
 from xs_kc 
 where 课程号 = '206'/*同理还可以进行嵌套式子查询的子查询*/
);

select 学号,姓名,专业名,出生日期/*找出比所有计算机系的学生年领都大的学生*/
from xs
where 出生日期 < all
(select 出生日期
 from xs
 where 专业名 = '计算机'
);

select 学号/*查找课程号为206的成绩不低于课程号为101的最低成绩的学生学号*/
from xs_kc
where 课程号 = '206' and 成绩 >= any
(
    select 成绩
    from xs_kc
    where 课程号 = '101'
);

any表达式只要与子查询结果集中的某个值满足比较关系式就返回true

group by

用于分组

1.输出各专业名
select 专业名
from xs
group by 专业名;
2.
select 专业名,count(*) as '学生人数'
from xs
group by 专业名;
3.
select 课程号,avg(成绩) as '平均成绩',count(学号) as '选修人数'
from xs_kc
group by 课程号;
4.
select 专业名,性别,count(*) as'人数'
from xs
group by 专业名,性别;
5.重点 rollup
select 专业名,性别,count(*) as '人数'
from xs
group by 专业名,性别
with rollup;/*使用了rollup后会对group by子句的进行逆次序汇总*/

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AaT7v8jH-1640496286830)(MySQL.assets/1638544197462.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5phl0Mym-1640496286831)(MySQL.assets/1638544204817.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nHvroQzK-1640496286832)(MySQL.assets/1638544325616.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3x55tk8H-1640496286832)(MySQL.assets/1638544433294.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wGjfwoOZ-1640496286832)(MySQL.assets/1638544597626.png)]

having

having语句和where类似，不同的是where用在from后，having用在group后

select 学号
from xs_kc
where 成绩 >= 80
group by 学号
having count(*)>2;

order by

该语句用来排序

select 学号,姓名,专业名,出生日期
from xs
where 专业名 = '通信工程'
order by 出生日期;/*给哪列排序，就order by 哪列*/

注意，order by 是升序，要是降序加上 desc

select 学号,姓名,专业名,出生日期
from xs
where 专业名 = '通信工程'
order by 出生日期 desc;/*降序*/

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ngh1Pz1q-1640496286833)(MySQL.assets/1638545032512.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fvhO5vL2-1640496286833)(MySQL.assets/1638545123833.png)]

limit

用于限制select返回的行数

select * from xs
order by 学号
limit 5;/*只显示最靠前的5行*/

select * from xs
order by 学号
limit 3,5;/*展示从第四位学生开始的五位学生信息*/

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tOWCd0FI-1640496286833)(MySQL.assets/1638545441200.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YsxaT4ll-1640496286834)(MySQL.assets/1638545448031-1638621061239.png)]

union

union可以把许多select语句的结果组合到一个集合

select 学号,姓名,专业名,性别,出生日期,总学分
from xs
where 学号='081101'
union
select 学号,姓名,专业名,性别,出生日期,总学分
from xs
where 学号='081210';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KDZ37lSl-1640496286834)(MySQL.assets/1638545624980.png)]

视图

/*视图view*/
1.创建视图
create or repalce view 视图名
as
select 列名
from 表名
where 条件
with check option;
代码:
create or replace view cs_kc
as
select xs.学号,xs_kc.课程号,xs_kc.成绩
from xs,xs_kc
where xs.学号 = xs_kc.学号 and xs.专业名 = '计算机'
with check option;


2.查询视图
视图建立后，就可以把它当成表来操作，所有查询功能和表完全一致
 select * from cs_kc;/*自己查一个看看去吧*/
 
 3.更新视图
 和改表记录一样
 也是
 update 视图名 set 列名 = 新值 where 条件
 插入数据就是insert和表一样
 4.删也是delete
 一样的一样的，自己看前面表操作去
 5.改也是alter
 一样的一样的，自己看前面表操作去
 6.一句话，视图建好以后，你就把它当成表，一点毛病没有

index

1.创建索引（create alter 建表时创建）
create index 索引名
on 表名(索引列);

栗：
1.
create index xh_xs
on xs(学号(5)asc);
2.
alter table xs
add index xs_xm using btree(姓名);
3.
create table xs
(
学号 char(6) not null,
姓名 char(8) not null,
性别 tinyint(1) not null,
年龄 tinyint(1) not null,
专业 char(10) not null,
系别 char(8) not null,
    primary key(学号),
    index cj(成绩)
);
/*展示表中的索引---此处建议全屏看*/
show index from xs;

2.删除索引
drop index 索引名 on 表名;


数据完整性约束

代替键 unique
unique表示该列值是唯一的不能重复

/*变量*/
语法:
set @变量名 = 表达式,@变量 = 表达式,……;

栗子:
set @name = '王琳';
select @name;

这部分内容非常简单，看MySQL实用教程书上85-96页，相信你十分钟就能看完

存储过程

所谓存储过程，就是把多条SQL语句结和成一个整体来同时执行，并在其中可以加入一些局部变量和传参

如果学过C/C++的话，可以把存储过程理解为void类型的函数，而后边的存储函数就是有返回值的函数

/*存储过程*/
1.Delimiter语句
delimiter/*用来更改结束符，正常情况语句结束符为';'*/
由于存储过程会有很多语句，而当遇到;时就会停止输入，所以存储代码时先将结束符改为其他，一般为$$

use test;
Delimiter $$
Create procedure tes(out number1 integer)/*括号中的number1你可以理解成c语言中函数的传参*/
Begin
Declare number2 integer;/*声明一个整型变量*/
Set number2=(select count(*) from xs);/*其值存为学生人数*/
Set number1=number2;/*赋值给要输出的变量*/
End$$
Delimiter ;/*存储后要把结束符改回来*/

call tes(@number);/*调用*/
select @number;/*查看结果*/

1. case 变量 when then ... end case
delimiter $$
create procedure  result(in str varchar(4),out sex varchar(4))/*str是传进来的参数,sex是输出的变量*/
begin
case str /*捕获str对其进行判断*/
when 'm' then set sex = '男';/*若str是'm'*/
when 'f' then set sex = '女';/*若str是'f'*/
else set sex = '无';
end case;/*结束case*/
end$$/*结束存储过程*/
delimiter ;/*把结束符改回来*/


call result('m',@s);/*调用一下试试*/
/*call语句用来调用一个存储过程*/
/*'m'是你传进去的参数，而@s是一个要存放输出结果的变量，你可以自己随便起名字，其类型都是varchar(4)*/

select @s;/*查看结果*/

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-27bnmM3l-1640496286834)(MySQL.assets/1638617954052.png)]

2.if then  elseif then else
delimiter $$/*修改结束符号*/
create procedure compar(in k1 integer,in k2 integer,out k3 char(6))
/*str是传进来的参数,sex是输出的变量*/
begin
if k1>k2 then set k3 = '大于';
elseif k1=k2 then set k3 = '等于';
else set k3 = '小于';/*else处不需要then*/
end if;/*结束if语句*/
end$$/*结束存储过程*/
delimiter ;/*把结束符改回来*/


call compar('5','6',@k);/*调用,将结果存在@k中*/
查看结果：
select @k;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-olwRYfEf-1640496286835)(MySQL.assets/1638623624637.png)]

3.where和局部变量
delimiter $$/*修改结束符号*/
create procedure dowhile()
begin
declare v1 int default 5;/*声明一个局部变量v1,数据类型为int，默认值为5*/
while v1 > 0 do
set v1 = v1 -1;
end while;/*结束while语句*/
end$$/*结束存储过程*/
delimiter ;/*把结束符改回来*/

/*该程序实现了循环五次,理解就行，不用调试了*/

4.存储过程的删除
drop procedure 存储过程名;

栗：
drop procedure compa;

存储函数

存储函数和存储过程很相似，但也有一些区别：

1.存储函数不能有输出参数，因为存储函数本身就是输出参数

2.不能使用CALL语句来调用存储函数

3.存储函数必须包括return语句，并且该语句不能包含于存储过程中

函数这部分因为一些原因无法运行，你就看懂理解了就好了

1.创建函数 create function 函数名()

delimiter $$
create function name_of_stu(xh char(6))  
returns char(8)/*返回值的类型*/ 
begin
return (select 姓名 from xs where 学号 = xh);/*返回学生表中学生人数*/
end$$/*结束函数*/
delimiter ;

2.函数调用
select name_of_stu('081101');/*直接会显示该函数的结果*/

3.删除
drop function 函数名;

触发器

1.创建一个触发器
delimiter $$/*修改结束符号*/
create trigger 触发器名 时刻 触发事件/*时刻包括after before*/
on 表名 for each  row
begin
执行的操作
end$$
delimiter ;/*把结束符改回来*/


delimiter $$/*修改结束符号*/
create trigger xs_delete after delete
on xs for each  row/*当xs表中学生信息被删除后触发*/
begin
delete from xs_kc where 学号=old.学号;/*执行操作为删除xs_kc表中被删除的信息的学号*/
end$$
delimiter ;

触发器可以敲一下，现在来验证一下上面的触发器

select * from xs_kc;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uJ3YBzzD-1640496286835)(MySQL.assets/1638634432575.png)]

现在081106的信息还在