mysql(2)
目录
一. DQL
1. 条件查询
条件查询:使用where将不满足条件的行过滤掉,where一般紧跟在from子句后面
语法: select <结果> from <表名> where <条件>
1.1 比较条件
(1) 等于 =
从学生表中查询学号等于1的学生信息
-- 从学生表中查询学号为1的学生信息
SELECT * FROM student WHERE num = 1(2) 不等于 != 或 <>
从学生表中查询,性别不等于男的学生信息
-- 从学生表中查询,性别不等于男的学生信息
SELECT * FROM student WHERE gender != '男'
SELECT * FROM student WHERE gender <> '男'(3) 大于 >
从学生表中查询身高大于1.70的学生信息
-- 从学生表中查询身高大于1.70的学生信息
SELECT * FROM student WHERE height > 1.70(4) 小于 <
从学生表中查询身高小于1.70的学生信息
-- 从学生表中查询身高大于1.70的学生信息
SELECT * FROM student WHERE height < 1.70(5) 大于等于 >=
从学生表中查询身高大于等于1.80的学生信息
-- 从学生表中查询身高大于等于1.80的学生信息
SELECT * FROM student WHERE height >=1.80(6) 小于等于 <=
从学生表中查询身高大于等于1.80的学生信息
-- 从学生表中查询身高大于等于1.80的学生信息
SELECT * FROM student WHERE height <=1.801.2 逻辑运算条件
(1) 与 and (所有条件都需要满足)
从学生表中查询性别为男,并且身高大于1.80的学生信息
-- 从学生表中查询性别为男,并且身高大于1.80的学生信息
SELECT * FROM student WHERE gender = '男' AND height>=1.80(2) 或 or (只需满足一个条件即可)
从学生表中查询性别为男或者身高大于等于1.70
-- 从学生表中查询性别为男或者身高大于等于1.70
SELECT * FROM student WHERE gender = '男' OR height>=1.70(3)非 not (取反)
从学生表中查询身高不在1.75,1.85,1.95之间的学生信息
注意:IN()查询只包含括号里的数据
-- 从学生表中查询身高不在1.75,1.85,1.95之间的学生信息
SELECT * FROM student WHERE height NOT IN(1.75,1.85,1.95)1.3 模糊查询
模糊查询:通过like %字符% 来查询数据,%是通配符
-- 查询名字以张开头的学生信息
SELECT * FROM student WHERE NAME LIKE '张%'
-- 查询名字中包含三的学生信息
SELECT * FROM student WHERE NAME LIKE '%三%'1.4 between and 条件
between and就相当于大于等于和小于等于的关系(值1 <= 数据 <= 值2),包含临界值
查询身高在1.70和2.0之间的学生信息
-- BETWEEN 1.70 AND 2.0 == height>=1.70 AND height<=2.0
SELECT * FROM student WHERE height BETWEEN 1.70 AND 2.01.5 in 条件
in:判断某字段的值是否是in列表中的某一项(相当于or)
查询身高在1.75,1.85,1.95之间的学生信息
-- 查询身高 1.75 1.85 1.95
SELECT * FROM student WHERE height=1.75 OR height=1.85 OR height = 1.95
SELECT * FROM student WHERE height IN(1.75,1.85,1.95)查询身高不在1.75,1.85,1.95之间的的学生信息
-- 查询身高不在1.75,1.85,1.95之间的的学生信息
SELECT * FROM student WHERE height NOT IN(1.75,1.85,1.95)1.6 is null 或 is not null 条件
查询地址为空的学生信息
-- 查询地址为空的学生信息
SELECT * FROM student WHERE address IS NULL查询地址不为空的学生信息
-- 查询地址不为空的学生信息
SELECT * FROM student WHERE address IS NOT NULL2. 查询合并
2.1 union和union all
union用于将多条SQL语句的结果表合并为一张表,要求多个SQL语句的结果表的列是相同的
union all和union的用法相同,但两者有区别
区别:
union会将两张表中都出现的学生信息去重
union all不会去重,只合并
-- 将性别为男和性别为女的学生信息合并
SELECT num,NAME,gender FROM student WHERE gender = '男'
UNION
SELECT num,NAME,gender FROM student WHERE gender = '女'
-- 将性别为男和性别为女的学生信息合并
SELECT num,NAME,gender FROM student WHERE gender = '男'
UNION ALL
SELECT num,NAME,gender FROM student WHERE gender = '女'
-- 将性别为男和身高等于1.65的学生信息合并
SELECT num,NAME,gender FROM student WHERE gender = '男'
UNION
SELECT num,NAME,gender FROM student WHERE height = 1.65
-- nuion all 合并多个查询结果 只是合并,不去重
-- 将性别为男,身高为1.65和1.75的学生信息合并
SELECT num,NAME,gender FROM student WHERE gender = '男'
UNION ALL
SELECT num,NAME,gender FROM student WHERE height = 1.65
UNION ALL
SELECT num,NAME,gender FROM student WHERE height = 1.753. 排序
3.1 order by
在MySQL中可以利用order by来对查询的结果进行排序,默认是升序,也可以指定升序(ASC)和降序(DESC)
(1) 按身高进行排序(升序)
-- 按身高进行排序,默认是升序
SELECT * FROM student ORDER BY height
-- 也可以用ASC指定升序
SELECT * FROM student ORDER BY birthday ASC(2) 按身高进行排序(降序)
-- 利用DESC实现降序排序
SELECT * FROM student ORDER BY height DESC(3)对身高1.80以上的人,先按生日升序排列,生日相同按身高降序排列(多条件排序)
-- 对身高1.80以上的人,先按生日升序排列,生日相同按身高降序排列
SELECT * FROM student WHERE height>1.80 ORDER BY birthday ASC,height DESC注意:
什么排序方式都没有,默认按主键升序排列
4. 数量限制
在MySQL中可以利用limit来对查询的结果进行数量限制(一次只看部分查询结果),limit一般跟在SQL的末尾
使用方法:limit 0,2从结果的第0条数据开始看,看两条数据
一般可以利用limit实现分页操作,如下
-- 数量限制 limit 开始位置(开始位置为0),查询的数量
-- 实际使用场景, 数据分页显示 一次只查询一部分数据, 提高查询效率
SELECT * FROM student LIMIT 0,2 -- 1 当前页码 n
SELECT * FROM student LIMIT 2,2 -- 2
SELECT * FROM student LIMIT 4,2 -- 3
-- limit (n-1)*2,25.分组查询
分组查询:
将某类数据分到一个组中进行处理,就是利用group by对表进行分组,查询出自己想要的结果,还可对结果继续处理,在分组查询中还可以利用分组函数
语法:
select 分组函数,列(必须出现在 group by后面)
from 表名
[where 筛选条件]
group by分组列表
[having 分组后的筛选]
[order by 子句]
(1) 统计男生女生人数
-- 统计男生女生人数
SELECT gender,COUNT(*) FROM student GROUP BY gender(2) 分别计算男女的身高总和,平均身高,身高的最大值
--分别计算男女的身高总和,平均身高,身高的最大值
--身高总和
SELECT gender,SUM(height) FROM student GROUP BY gender
-- 身高平均值
SELECT gender,AVG(height) FROM student GROUP BY gender
-- 身高最大值
SELECT gender,MAX(height),gender FROM student GROUP BY gender
(3) 统计每年出生人数,并按照年份从小到大输出
-- 统计每年出生人数,并按照年份从小到大输出
SELECT YEAR(birthday) ,COUNT(*)FROM student GROUP BY YEAR(birthday) ORDER BY YEAR(birthday)5.1 having 条件
having用于对分组后的表进行条件筛选,注意和where区分
where:对原始表进行条件限制,筛选出符合条件的信息
having:对分组后的表进行条件限制,筛选出符合条件的信息
二. 多表设计
1. 数据库设计范式
设计好的数据库事半功倍,所以数据库的设计一般遵循以下三个范式
第一范式:表中的每一列都不可再分(原子性)
第二范式:每个表中都要有主键
第三范式:当两张表或多张表之间有关系时,应该将一个表的主键设置到另一张表中,而其他信息不用添加,以免重复
2. 外键
外键:就是在一个表中设置一列用来存储另一个表中的主键信息(两表相关联),就称该列为外键
注意:
(1)一个表中的外键必须存储的是另一个表中的主键,也就是外键必须与主键相关联,且数据类型一致
(2)有了外键就不需要在该表中存储另一张表的其他信息,因为可以通过外键找到另一张表的信息
3. 添加外键约束
为什么要添加外键约束,这是因为,我们有了外键,就表示两张表之间有了关系,那么就不能随意的对表中的信息进行删除.比如
有学生表和专业表且两者之间有关联,很多学生都选了这个专业,那我们就不能直接将该专业删除掉,不然那些学生就不能通过外键找到与之对应的专业了,要想删除专业,必须确保该专业没有人选,才能删除
综上,我们添加外键约束的原因,主要是为了保持数据的完整性,避免随意删除数据
3.1 通过修改表结构添加外键约束
ALTER TABLE student ADD CONSTRAINT fk_student_major_on_majorid FOREIGN KEY(majorid) REFERENCES major(id)解释:
(1)ALTER TABLE 表名 ADD CONSTRAINT
是固定格式,表示要修改某张表,为其添加约束
(2)fk_student_major_on_majorid
是外键约束名,需要程序员自己起
外键约束名一般要遵循命名规则,程序员默认命名规则是
fk_foreignTable_primaryTable_on_foreignColumn
foreignTable是外键表的名字
primaryTable是主键表的名字
foreignColumn是外键名
(3)FOREIGN KEY(majorid) REFERENCES major(id)
原格式:FOREIGN KEY(外键) REFERENCES Table(主键)
FOREIGN KEY(majorid)表示是给外键majorid添加的外键约束,REFERENCES major(id)表示外键约束和major表中的id关联
注意:
(1) 添加外键约束时,要保证外键和主键的数据类型一致
(2) 添加外键约束时,外键里的值必须在主键中都存在
不满足上面两点外键约束加不上去
3.2 在创建表时添加外键约束
我们通过一个学生选课案例来说明.现在有一个学生表,一个课程表,由于一个学生可以选择多个课程,多个课程也可以被不同学生选择,所以这是多对多的关系,我们不能通过上面那种简单的给学生信息表中加外键的方式解决(这种方式只能解决一对多或多对一的关系),而是要再专门创建一个学生课程表,存储学生id和课程id,因为学生id和课程id在学生表和课程表中都是主键,所以在学生课程表中就是外键,为了保持学生信息的完整性,给学生id和课程id添加外键约束
CREATE TABLE student_course(
id INT PRIMARY KEY AUTO_INCREMENT,
student_num INT,
course_id INT,
CONSTRAINT fk_student_course_student_on_student_num FOREIGN KEY(student_num)REFERENCES student(num),
CONSTRAINT fk_student_course_course_on_course_id FOREIGN KEY(course_id) REFERENCES course(id)
)注意:在创建表时添加外键,只用写CONSTRAINT和后面的内容,不用写前面修改表结构的部分
三. 关联查询
关联查询:又称多表查询,当我们想要的数据来自多张表时,就要用到关联查询,否则会发生笛卡尔积的现象(表一有m行,表二有n行,结果=m*n行)
发生原因:没有有效的连接条件
如何解决:添加有效的连接条件
关联查询按功能分
1.自连接
2.内连接
3.外连接,外连接又分为左外连接和右外连接
3.1 内连接
内连接:只把满足条件的两张表的交集求出来,不满足条件的不输出
语法:select * from 表1 inner join 表2 on 表1.column1=表2.column2
-- 内连接,只把满足条件的筛选出来
-- 在学生表和专业表中查询学生id和专业id相等的学生信息,也就是哪个学生是哪个专业的
--写法一
SELECT * FROM student,major WHERE majorid=id
--写法二
SELECT
s.num,
s.name,
s.gender,
m.name
FROM student s INNER JOIN major m ON s.majorid = m.id3.2 左外连接
左外连接:把左边表中所有的数据查询出来,右边表中只会查询出满足条件的
语法:select * from 表1 left join 表2 on 表1.column1=表2.column2
-- 左外连接 把左边表中所有的数据查询出来, 右边表中只会查询出满足条件的
-- 在学生表和专业表中查询学生id和专业id相等的学生信息,也就是哪个学生是哪个专业的
-- 包含没选专业的学生
SELECT
s.num,
s.name,
s.gender,
m.name
FROM student s LEFT JOIN major m ON s.majorid=m.id3.3 右外连接
右外连接:把右边表中所有的数据查询出来,左边表中只会查询出满足条件的
语法:select * from 表1 right join 表2 on 表1.column1=表2.column2
3.4 自连接
自连接就是这张表自己和自己用笛卡尔积的形式进行拼接,我们可以用条件筛选出我们想要的数据
-- 根据中国地域表,查询汉台区的市和省
SELECT a1.name,a2.name,a3.name FROM AREA a1 INNER JOIN AREA a2 ON a1.pid = a2.id
INNER JOIN AREA a3 ON a2.pid = a3.id
WHERE a1.name = '汉台区'四. 子查询
子查询:在一个查询语句中又出现了查询语句
1. 标量子查询
标量子查询:结果集是一行一列的
查询身高最高的学生
SELECT * FROM student WHERE height=(SELECT MAX(height) FROM student)2. 列子查询
列子查询:结果集是一列多行的
查询身高是1.75,1.85,1.95的学生信息
SELECT * FROM student WHERE height IN (SELECT height FROM student WHERE height = 1.75 OR height = 1.85 OR height = 1.95)
3. 表子查询
表子查询:结果集是多行多列的,是一张表
查询姓名重复的学生
SELECT * FROM (SELECT NAME,COUNT(*)AS c FROM student GROUP BY NAME)AS t WHERE t.c>1一般where后面跟标量子查询和列子查询,from后跟表子查询
原文地址：https://blog.csdn.net/qq_64782704/article/details/140414175
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！