mysql数据库的增删改查(进阶篇)

最新推荐文章于 2024-05-04 23:20:08 发布

小凯凯呢

最新推荐文章于 2024-05-04 23:20:08 发布

阅读量1k

点赞数

本文链接：https://blog.csdn.net/qq_44778109/article/details/106262400

版权

数据库约束

首先来了解一下数据库约束
在这里插入图片描述

NOT NULL - 指示某列不能存储 NULL 值。

UNIQUE - 保证某列的每行必须有唯一的值。

DEFAULT - 规定没有给列赋值时的默认值。

--指定插入数据时，name列为空，默认值unkown
 name VARCHAR(20) DEFAULT 'unkown';

PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

--对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大 值+1。
id INT PRIMARY KEY auto_increment;

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

外键用于关联其他表的主键或唯一键
语法:
foreign key (字段名) references 主表(列)

例:

DROP TABLE IF EXISTS classes; 
CREATE TABLE classes (    
id INT PRIMARY KEY auto_increment,-- id为主键    
name VARCHAR(20)
 );
--创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键， classes_id为外键，关键班级表id
DROP TABLE IF EXISTS student; 
CREATE TABLE student (    
id INT PRIMARY KEY auto_increment,    
sn INT UNIQUE,    
name VARCHAR(20) DEFAULT 'unkown',    
qq_mail VARCHAR(20),    
classes_id int,
--外键约束    
FOREIGN KEY (classes_id) REFERENCES classes(id) );

CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略 CHECK子句。

表设计的方式

1.一对一
例: 人和自己的身份证号
2.一对多
例:班级和学生,一个班级有多个学生,一个学生属于一个班级
3.多对多
例:学生和课程, 一个学生学习多门课程,一门课程有很多学生

下面为后面操作需要的数据库,表:

--创建班级表
DROP TABLE IF EXISTS classes; 
CREATE TABLE classes (    
id INT PRIMARY KEY auto_increment,    
name VARCHAR(20),    
`desc` VARCHAR(100) );

--创建学生表
DROP TABLE IF EXISTS student; 
CREATE TABLE student (    
id INT PRIMARY KEY auto_increment,    
sn INT UNIQUE,    
name VARCHAR(20) DEFAULT 'unkown',    
qq_mail VARCHAR(20),    
classes_id int,    
FOREIGN KEY (classes_id) REFERENCES classes(id) 
);

-- 创建课程表 
DROP TABLE IF EXISTS course; 
CREATE TABLE course (    
id INT PRIMARY KEY auto_increment,    
name VARCHAR(20) 
);
-- 创建课程学生中间表：考试成绩表 
DROP TABLE IF EXISTS score; 
CREATE TABLE score (   
id INT PRIMARY KEY auto_increment,    
score DECIMAL(3, 1),    
student_id int,    
course_id int,    
FOREIGN KEY (student_id) REFERENCES student(id),    FOREIGN KEY (course_id) REFERENCES course(id) );

create table emp(    
id int primary key auto_increment,    
name varchar(20) not null,    
role varchar(20) not null,    
salary numeric(11,2) 
);
insert into emp(name, role, salary) values 
('马云','服务员', 1000.20), 
('马化腾','游戏陪玩', 2000.99), 
('孙悟空','游戏角色', 999.11), 
('猪无能','游戏角色', 333.5), 
('沙和尚','游戏角色', 700.33), 
('隔壁老王','董事长', 12000.66
);
insert into classes(name, `desc`) values 
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'), 
('中文系2019级3班','学习了中国传统文学'), 
('自动化2019级5班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values 
('09982','黑旋风李逵','xuanfeng@qq.com',1), ('00835','菩提老祖',null,1), 
('00391','白素贞',null,1), 
('00031','许仙','xuxian@qq.com',1), 
('00054','不想毕业',null,1), 
('51234','好好说话','say@qq.com',2), ('83223','tellme',null,2), 
('09527','老外学中文','foreigner@qq.com',2);

insert into course(name) values 
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

insert into score(score, student_id, course_id) values 
-- 黑旋风李逵 
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6), 
-- 菩提老祖 
(60, 2, 1),(59.5, 2, 5), 
-- 白素贞 
(33, 3, 1),(68, 3, 3),(99, 3, 5), 
-- 许仙 
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6), 
-- 不想毕业 
(81, 5, 1),(37, 5, 5),
-- 好好说话 
(56, 6, 2),(43, 6, 4),(79, 6, 6), 
-- tellme 
(80, 7, 2),(92, 7, 6);

下面进行操作:

1.新增
插入查询结果:
INSERT INTO table_name [(column [, column …])] SELECT …
例:创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的学生数据复制进来，可以复制的字段为name、qq_mail

-- 创建用户表 
DROP TABLE IF EXISTS test_user; 
CREATE TABLE test_user (    
id INT primary key auto_increment,    
name VARCHAR(20) comment '姓名',    
age INT comment '年龄',    
email VARCHAR(20) comment '邮箱',    
sex varchar(1) comment '性别',    
mobile varchar(20) comment '手机号' );
-- 将学生表中的所有数据复制到用户表 
insert into test_user(name, email) select name, qq_mail from student;

可见将学生表中的数据插入进了新创建的用户表
在这里插入图片描述

2.查询

聚合查询

1.聚合函数:
在这里插入图片描述

例:

COUNT:

-- 统计班级共有多少同学 
SELECT COUNT(*) FROM student; 
SELECT COUNT(0) FROM student;
-- 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果 
SELECT COUNT(qq_mail) FROM student;

例:

SUM:

-- 统计数学成绩总分 SELECT SUM(math) FROM exam_result;
-- 不及格 < 60 的总分，没有结果，返回 NULL SELECT SUM(math) FROM exam_result WHERE math < 60;

例:

AVG:

-- 统计平均总分 
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;

例:

MAX:

-- 返回英语最高分 
SELECT MAX(english) FROM exam_result;

例:

MIN:

-- 返回 > 70 分以上的数学最低分 
SELECT MIN(math) FROM exam_result WHERE math > 70;

2.GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。

select column1, sum(column2), … from table group by column1;

例:

--查询每个角色的最高工资、最低工资和平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role;

在这里插入图片描述

3.HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING

例:

select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;

在这里插入图片描述

联合查询

往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：
在这里插入图片描述

1.内连接

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
–或者
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

例:

--查询许仙同学的成绩
select sco.score from student stu inner join score sco on stu.id=sco.student_id and stu.name='许仙'; 
-- 或者 
select sco.score from student stu, score sco where stu.id=sco.student_id and stu.name='许仙';

--查询所有同学的总成绩，及同学的个人信息;
-- 成绩表对学生表是多对1关系，查询总成绩是根据成绩表的同学id来进行分组的 
SELECT    
stu.sn,    
stu.NAME,    
stu.qq_mail,    
sum( sco.score ) 
FROM    
student stu   
JOIN score sco ON stu.id = sco.student_id
GROUP BY    
sco.student_id;

--查询所有同学的成绩，及同学的个人信息：
--学生表、成绩表、课程表3张表关联查询 
SELECT    
stu.id,    
stu.sn,    
stu.NAME,    
stu.qq_mail,    
sco.score,    
sco.course_id,    
cou.NAME FROM    
student stu    
JOIN score sco ON stu.id = sco.student_id    
JOIN course cou ON sco.course_id = cou.id 
ORDER BY
stu.id;

2.外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接

– 左外连接，表1完全显示 select 字段名 from 表名1 left join 表名2 on 连接条件;
– 右外连接，表2完全显示 select 字段 from 表名1 right join 表名2 on 连接条件;

例:

--查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示
-- “老外学中文”同学 没有考试成绩，也显示出来了 
select * from student stu left join score sco on stu.id=sco.student_id; 
-- 对应的右外连接为： 
select * from  score sco right join student stu on stu.id=sco.student_id;
-- 学生表、成绩表、课程表3张表关联查询 
SELECT    
stu.id,    
stu.sn,    
stu.NAME,    
stu.qq_mail,    
sco.score,    
sco.course_id,    
cou.NAME FROM    
student stu    
LEFT JOIN score sco ON stu.id = sco.student_id    LEFT JOIN course cou ON sco.course_id = cou.id 
ORDER BY    
stu.id;

3.自连接

自连接是指在同一张表连接自身进行查询。

例:

-- 先查询“计算机原理”和“Java”课程的id 
select id,name from course where name='Java' or name='计算机原理';
-- 再查询成绩表中，“计算机原理”成绩比“Java”成绩 好的信息 
SELECT    
s1.* 
FROM    
score s1,    
score s2 
WHERE    
s1.student_id = s2.student_id 
 AND s1.score < s2.score     
 AND s1.course_id = 1     
 AND s2.course_id = 3;

4.子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

单行子查询:
例:
返回一行记录的子查询查询与“不想毕业” 同学的同班同学;

select * from student where classes_id=(select classes_id from student where name='不想毕业');

多行子查询：
例:
返回多行记录的子查询查询“语文”或“英文”课程的成绩信息

IN关键字同理也有NOT IN 用法相同

select * from score where course_id in (select id from course where name='语文' or name='英文');

EXISTS关键字同理也有NOT EXISTS 用法相同

select * from score sco where exists (select sco.id from course cou where (name='语文' or name='英文') and cou.id = sco.course_id);

5.合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION 和UNION ALL时，前后查询的结果集中，字段需要一致。

union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

例:

select * from course where id<3 
union 
select * from course where name='英文';
-- 相当于 
select * from course where id<3 or name='英文';

union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

小凯凯呢

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫