MySQL数据库：数据库表的一些基本操作

本文链接：https://blog.csdn.net/qq_62078665/article/details/129651527

表的创建

创建一个数据库


create database QAQ default character set utf8;#创建了一个名为QAQ的数据库并将其默认字符集设置为utf8

利用show databases来查看创建的数据库


show databases;

数据库创建成功

用use qaq来进入该数据库并创建表


use qaq;#进入该数据库

#创建了一个名为Student的表，其中有两个字段，学号和长度为20个字符的名字。
#这里说一下char(10)，char会固定占用10个字符的空间，而varchar(10),则不会固定占用10个字符#的空间，如果名字只有3个字符则只会占用3个字符和一个结束字符
create table Student(
    Sid int primary key auto_increment comment '学号',
    Sname char(20) not null comment '姓名',#comment为字段注释方便理解代码
    Sex enum ('男','女'),#enum单选
    Slike set ('唱','跳','rap') #set枚举类型，多选
)

#创建了一个学生成绩表，其中的学号引用了学生表的学号并用了级联删除（on delete cascade）和级联修改（on update cascade）这两个的作用是当在父表做修改或删除时子表也会自动修改
create table Grades(
    Sid int primary key,
    Score decimal(5,2) comment '学生成绩',#decimal(5,2),表示总共5位保留两位小数
    foreign key (Sid) references Student(Sid) on delete cascade on update cascade
)

表结构的修改

首先来查看表的结构，利用show create table 表名;


show create table student;

#以下是查询结果，其中会补上写的时候省略的内容
CREATE TABLE `student` (
  `Sid` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `Sname` char(20) NOT NULL COMMENT '姓名',
  `Sex` enum('男','女') DEFAULT NULL,
  `Slike` set('唱','跳','rap') DEFAULT NULL,
  PRIMARY KEY (`Sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 #引擎为InnoDB，字符型为utf8mb3, 若没有写字符型将和数据库一样



show create table Grades;

#以下是查询结果，其中会补上写的时候省略的内容
CREATE TABLE `grades` (
  `Sid` int NOT NULL,
  `Score` decimal(5,2) DEFAULT NULL COMMENT '学生成绩',
  PRIMARY KEY (`Sid`),
  CONSTRAINT `grades_ibfk_1` FOREIGN KEY (`Sid`) REFERENCES `student` (`Sid`) 
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

 #`grades_ibfk_1`为外键的名字，创建表时没写的话系统会自动生成，可以按上面的格式来创建有名字的外键约束

表名的修改


#alter table 表名 rename to 新表名
#rename table 表明 to 新表名
两个都可以

字段的修改

添加字段


#alter table 表名 add 字段及其相关属性，如下
alter table student add ss int not null default 0;

删除字段


alter table 表名 drop 删除的字段名

#注意：如果要删除的字段与其它表有联系则无法删除，如student表中的sid被grades表中的sid引用所以无法删除，要先删除外键约束（alter table 表名 drop 外键名）或取消外键约束（set foreign_key_checks = 0  等于1则恢复外键约束）

字段修改


alter table 表名 change 原字段名 新字段名 新数据类型 #当只想修改数据类型或字段名时不变的地方照抄就好了

alter table 表名 modify 字段名 新数据类型  #用法同上但不能修改字段名

#修改字段的排序位置
alter table 表名 change 字段名1 字段名1 数据类型 first|after 字段名2

约束的修改

删除约束


alter table 表名 drop 约束名

#删除外键约束时，当父表的约束被删除了。那么子表和父表的联系就断了，因此要将子表的约束也一起删掉

#删除唯一约束
alter table 表名 drop index 唯一约束名（如创建表时没写则系统自动生成）

#删除默认约束值,以下两个都可以
alter table 表名 change 原字段名 原字段名 原数据类型 default null;

alter table 表名 modify 原字段名 原数据类型 default null;

#删除非空约束，以下两个都可以
alter table 表名 change 原字段名 原字段名 原数据类型 null;

alter table 表名 madify 原字段名 原数据类型 null;

增加约束


alter table 表名 add 要添加的约束

#添加主键约束
alter table 表名 add primary key (列名)

#添加外键约束
alter table 表名 add constraint 外键名 foreign key (字表字段名) references aa (父表字段名)

#添加唯一约束
alter table 表名 add constraint 唯一约束名 unique(列名)

#添加默认约束
alter table 表名 change 字段名 字段名 数据类型 default 默认值

alter table 表名 modify 字段名 数据类型 default 默认值

#添加非空约束
alter table 表名 change 字段名 字段名 数据类型 not null

alter table 表名 modify 字段名 数据类型 not null

选项的修改


#修改存储引擎
alter table 表名 engine = 新的引擎类型

#修改默认字符集
alter table 表名 default charset = 新的字符集

#修改索引关键字压缩方式的语法规则
alter table 表名 pack_keys = 新的压缩类型

#修改自增字段的初始值
alter table 表名 auto_increment = 新的初始值

其他操作


#查看当前数据库中的所有表
show tables;

#查看指定表的表结构
desc 表名

#查看指定表的详细信息
 show create table 表名

#复制表
#将原表的结构复制到新表中，不包含数据
create table 新表名 like 原表名

#将原表的数据和结构复制到新表中
create table 新表名 select * from 原表名

#删除表
#要注意如果要删除的表是某个表的父表时，要先解除外键约束或删除外键约束
drop 表名

数据的处理

插入数据


#插入完整的一条数据
insert into 表名 (字段名1,字段名2...) values (字段一的值,字段二的值...);
#以上方的学生表为例

#插入不完整的数据要写下字段并插入的值要按照字段顺序来
insert into student (Sid,Sanme) values (123,'张山');

#插入整条数据字段可以不写，但顺序必须按照表中的字段顺序来添加
insert into student values (123,'张三','男','唱,跳,rap'); 

#插入多条数据，每条数据间用逗号隔开
insert into student values
(456,'阿莹','女','唱'),
(789,'阿晴','女','跳,rap');

#插入查询结果（这里不多解释，等到查询的时候就可以看懂了）
#只插入原表的数据
insert into 新表名 (新表字段名1,新表字段名2...) select (旧表字段名1,旧表字段名2...) from 原表名

#将原表的数据和结构插入到新表中
create table 新表名 select * from 原表名

#用replace来插入数据（这里只写语法）,要注意replace是先将原有的数据删除了再插入新的数据，所以若原先的字段与其他的字段有联系（外键），则要先解除联系，若原先没有数据则直接插入

replace into 表名 (字段名1,字段名2...) values (字段1的值,字段2的值...);

replace into 目标表名 (目标表字段名1,目标表字段名2...) select (旧表字段名1,旧表字段名2...) from 旧表名 where 条件

replace into 表名 set 字段名1 = 字段1的值,字段名2 = 字段2的值...;

修改数据


#修改特定数据
update 表名 set 字段名1 = 字段1的值,字段名2 = 字段2的值... where 限制条件

#修改所有数据（因为是所有数据所以没有限制条件）
update 表名 set 字段名1 = 字段1的值,字段名2 = 字段2的值...;

删除数据


#删除特定数据
delete from 表名 where 限制条件

#删除所有数据
delete from 表名;

#采用truncate语句清空数据记录
truncate 表名;

数据查询

selsct语句的概述


select 字段列表
from 表名
where 表的限制条件
group by 字段列表 having 对分组结果的限制条件
order by 字段名 [asc|desc]
limit 开始位 显示个数


先来讲解一下上面的代码
select后面是要查询的表中的字段，如果要查询全部字段则可以用*来代替

where是对表中的限制

group by 是分组查询的内容，而havign是对分组结果的限制条件

order by是对查询结果的升序显示（asc）或降序显示（desc）

limit 是显示查询结果的某一段结果，如我的查询结果有30条，但我只想显示第5条到第10条的内容就可以写为limit 4 5;(前面的4为第5条结果的下标，5为往下的个数)

单表查询


#查询特定的字段
select 字段1 as 别名1,字段2 as 别名2... from 表名 #(其中的as可以省略)
select sid 学号,sname 学生姓名 from student;

#查询表中所有数据
select * from 表名;

#以上的查询结果会存在一定的重复性，如我要查询所有学生所选的课，而学生1选了课1和课2，学生2选了课2和课3，那么查询结果就会是课1,课2,课2,课3,其中课2是存在重复的，此时就可以用 distinct

select distinct 字段列表 from 表名;


#限制显示记录数
select * from 表名 limit 5; #若前一位不写则默认从下标为0的第一个开始

#where 条件查询
#如查询学生表中女生的信息
select * from student where sex = '女'; #where后后跟的是一个条件表达式，因此可以用and  or  &&  ||  >  >= 等相关符号;

#确定集合的查询
#查询在集合中的值
select 字段列表 from 表名 where 字段 in (值1,值2...); #如果在集合中就写出来
#查询不在集合中的值
select 字段列表 from 表名 where 字段 not in (值1,值2...);


#模糊查询
#查询名字中含荧的学生
select * from student where sname like '%荧%'; #其中%表示0个后无穷个字符

#查询姓王的三个字的学生
select * from student where sname like '王__'; #这里有两个下划线，只是显示的不是很清楚

#转意字符\,可以将\后面的含有特殊意义的单个字符变成普通字符
#查询名字中含有%的学生
select * from student where sname like '%\%%'; #其中前后两个%含有特殊含义，而\后面的%就只是一个%

#查询名字中含有\的学生，因为\原先是有特殊含义的，而且又不能用它本身来转义，所以要暂时指定另一个字符为转义字符
select * from student where sname like '%@\%' escape '@'; #这里将@符号指定为转义字符了;

#空值的查询
 select * from student where sname is null; #查询非空值只需将is null改为not null 就好了


#对查询结果进行排序
select * from student order by [asc|desc];


#聚合查询 (以下都可加distinct，均为去掉重复值是可以省略不写的)
count([distinct] *)  #返回行数，不管是否为NULL，列名就是字段名
count(列名) #返回指定列中非NULL值的个数
sum(列名) #返回指定列中非NULL值的之和
avg(列名) #返回指定列中非NULL值的平均值
max(列名) #返回指定列中非NULL值的最大值
min(列名) #返回指定列中非NULL值的最小值

###聚合函数通常是用在select后，不能出现在where之后


#分组查询（group by）
#查询男女学生的人数，那么结果就是一个二维表。
select sex,count(*) from student group by sex; #其中count(*)是用来返回行数的

#查询选修了3门以上课程的学生学号
select sid,count(*) from register group by sid having count(*) > 3; #having是对分组查询的结果进行限制输出


#这里提一下分组查询的一个错误
#错误代码
SELECT book.*, lend.LendDate, lend.BackDate, COUNT(lend.BookID) AS BorrowCount
FROM book LEFT OUTER JOIN lend 
ON book.BookID = lend.BookID
GROUP BY book.BookID;
#其中是基于book表对lend表中的lend.bookid进行分组查询，但lend.LendDate, lend.BackDate也在lend表中，违反了ONLY_FULL_GROUP_BY模式，该模式要求在SELECT列表中的所有列都必须在GROUP BY子句中列出，或者在SELECT列表中使用聚合函数进行计算。


所以改进的代码如下
SELECT book.*, lend.LendDate, lend.BackDate, COUNT(lend.BookID) AS BorrowCount
FROM book LEFT OUTER JOIN lend 
ON book.BookID = lend.BookID
GROUP BY book.BookID, lend.LendDate, lend.BackDate;
#但它的查询结果有一个缺点，会将图书按book.BookID, lend.LendDate, lend.BackDate;进行分组显然是有重复且不合适的。


#再改进的代码
SELECT book.*, L.lend_count, L.LendDate, L.BackDate
FROM book 
LEFT JOIN (
    SELECT BookID, COUNT(BookID) AS lend_count, MIN(LendDate) AS LendDate, MAX(BackDate) AS BackDate
    FROM lend GROUP BY BookID
) AS L ON book.BookID = L.BookID;
#这回没有重复项了，但又出现问题了，就是如果这本书被借阅了两次或更多，它的借阅日期仅最早借阅日期以及最晚归还日期。（可能是我要查询的信息出了逻辑上的问题。。。QAQ~~~）



###查询的代码灵活性还是比较高的只要不违背select概述里的大概顺序就可以，更多的组合可以自行了解，这里不过多讲述

多表查询

这里要先引入表做例子


#多表查询是用于要查询的结果在多个表中的情况，因此连接条件（主、外键）至少是表数减一


#内连接查询（可以在where的末尾外添加分组查询）
#格式一
select [distinct] 字段列表 from 表名1,表名2... where 连接条件和一些限制条件;

#格式二（这里仅作了解，感兴趣的可自行深入）
select [distinct] 字段列表 from 表名1 join 表名2 on 连接条件 
join 表名3 on 连接条件 ... where 表的一些限制条件

#外连接：具体参内连接，只是添加了一个left/right/full
select 字段列表 from 表1 left/right/full 表名2 on 连接条件 where 表的一些限制条件;
#其中表1为左表，表2为有右表，而left是除了满足连接条件的结果外还包括左表中不满足连接条件的结果，right则反之。

#联合查询（union[all],其中all表示将所有行显示出来，不加all则去掉重复的行），主要是将两个查询结果合并增加其行数，如查询计算机专业的一二班的学生，其中一二班的表的格式是一样的（而且需要显示的列数也要一样），所有可以用联合查询。
select * from 专业表 where 专业名 = '计算机' and 班级 = '一班' union
select * from 专业表 where 专业名 = '计算机' and 班级 = '二班'

子查询（重点！！！）即查询的嵌套

不相关子查询：子查询和父查询是不相关的，因此查询是由内而外的层层进行


#以下代码仅作为讲解列子
#查询选了计算机专业的学生信息
select * from 学生表 where 学号 in (select 学号 from 专业表 where 专业名 = '计算机')

#由内而外的查询，先在子查询中从专业表中找到选了计算机这个专业的学生的学号，而因为专业表中没有学生信息，所以再从学生表中去查询学生信息。由于子查询的结果是一个集合，所以用in;

相关子查询：子查询的查询条件是依赖于父查询的


#同一个表的两次查询时，要取别名来区分
#查询成绩比该课程平均成绩高的学生成绩表，包括学号，课程号，成绩
#注册表包含学号，课程号，成绩和选课时间
select 学号,课程号,成绩 from 注册表 r1 where 成绩 > (select avg(成绩) from 注册表 
r2 where 课程号 = r1.cid)

#浅浅分析一下，首先在子查询中，课程号 = r1.cid，是r2表中的课程号等于r1的课程号，在表1中选取一个学生，用这个学生的课程号去匹配表2，找到表2中该课程号的平均成绩（avg(成绩)），子查询就返回这个平均成绩，然后到父查询中用表1中该学生的学号去匹配它的成绩，并与平均成绩进行比较，若符合则输出该学生的信息，否则进行下一位学生的查询，如此循环直到表1中的学生都查询完毕。

这块的内容相对比较简单，就不过多解释了，只要尝试去用一下就能理解了。。。

后面补的一些内容


#这里添加点东西哈

update lend,reader set lend.BackDate = '2018-1-1' where reader.ReaderName = '赵青青' and lend.BookID = 'B004' and lend.ReaderID = reader.ReaderID
#这段代码表明，连接还可以用再修改上，因为修改的信息跨越了两张表，所以用连接。那么再一次体现了代码的灵活性，还是得多写写才理解啊QwQ~~~