MySQL篇 ---- 连接查询与函数

最新推荐文章于 2024-04-12 13:34:10 发布

幼稚范2r°

最新推荐文章于 2024-04-12 13:34:10 发布

阅读量219

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/xiaoaofeng_/article/details/133382786

版权

1.表关系说明

2.连接查询

内连接
- 把两张表相同的地方查询出来

左连接
- 包括了内连接,同时还查询左表特有的内容

右连接
- 包括了内连接,同时还查询右表特有的内容

3.内连接

语法:
- select * from 表1 inner join 表2 on 表1.字段 = 表2.字段
- 内连接最重要的是,找对两张表要关联的字段

SELECT * from a INNER JOIN b on a.id = b.id;

students表和scores内连接查询结果

SELECT * from students INNER JOIN scores on students.studentNo = scores.studentNo;

隐式内连接语法
- 语法:select * from 表1,表2 where 两个表的连接条件

-- 隐式内连接
SELECT * from students, scores where students.studentNo = scores.studentNo;

内连接查询,显示指定的字段

-- students表与socres内连接,只显示name 课程号 成绩
SELECT name, courseNo, score from students
INNER JOIN scores on students.studentNo = scores.studentNo;

表的别名在查询中的使用

SELECT name 姓名, courseNo 课程编号, score 成绩 from students st
INNER JOIN scores sc on st.studentNo = sc.studentNo;

带有where的内连接
- 语法select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 where 条件

-- 例 5：查询王昭君的信息，要求只显示姓名、课程号、成绩
select name, courseNo, score from students s1 
INNER JOIN scores s2 on s1.studentNo = s2.studentNo
where s1.name = '王昭君';

带有and的where条件

-- 例 6：查询姓名为’王昭君’,并且成绩小于 90 的信息，要求只显示姓名、成绩
select name, score from students s1 
INNER JOIN scores s2 on s1.studentNo = s2.studentNo
where s1.name = '王昭君' and s2.score < 90;

多表内连接

-- 例 7：查询学生信息和成绩以及成绩对应的课程名称
SELECT * from students inner join scores on students.studentNo = scores.studentNo
inner join courses on scores.courseNo = courses.courseNo;

4.写SQL三步法

搭框架
- 基本的select语句框架搭建起来,如果有多表,把相应的多表也联合进来
看条件
- 决定where后面的具体条件
显示的字段
- select后面到底要显示什么字段

-- 查询所有学生的’linux’课程成绩，要求只显示姓名、成绩、课程名
-- 第一步:搭框架
-- SELECT * from students INNER JOIN scores 
-- on students.studentNo = scores.studentNo
-- INNER JOIN courses on scores.courseNo = courses.courseNo;
-- 第二步:看条件
-- SELECT * from students INNER JOIN scores 
-- on students.studentNo = scores.studentNo
-- INNER JOIN courses on scores.courseNo = courses.courseNo
-- where courseName = 'linux';
-- 第三步:返回字段名
SELECT name, score, courseName from students INNER JOIN scores 
on students.studentNo = scores.studentNo
INNER JOIN courses on scores.courseNo = courses.courseNo
where courseName = 'linux';

带有order by的联合查询

-- 例 8：查询成绩最高的男生信息，要求显示姓名、课程名、成绩
SELECT name, score, courseName from students INNER JOIN scores 
on students.studentNo = scores.studentNo
INNER JOIN courses on scores.courseNo = courses.courseNo
where sex = '男'
order by score desc LIMIT 1;

5.左连接

语法
- select * from 表1 left join 表2 on 表1.字段 = 表2.字段

-- 例 1：查询所有学生的信息以及成绩，包括没有成绩的学生
SELECT * from students left JOIN scores ON
students.studentNo = scores.studentNo;

6.右连接

语法
- select * from 表1 right join 表2 on 表1.字段 = 表2.字段

-- 例 1：查询所有课程的信息，包括没有成绩的课程
SELECT * from scores RIGHT JOIN courses ON
scores.courseNo = courses.courseNo;

7.多表联合查询,同名字段的处理方式

如果一条select要用到多个表,表中有同名字段.就需要表名.字段名加以区分

select students.studentNo from students INNER JOIN scores ON
students.studentNo = scores.studentNo;

8.自关联

-- 查询一共有多少个省
SELECT count(*) from areas where pid is null;
-- 查询有多少市
SELECT count(*) from areas where pid is not null;

自关联,是同一张表做连接查询,
自关联下,一定找到同一张表可关联的不同字段

-- 例 2：查询广东省的所有城市
SELECT * from areas a1 INNER JOIN areas a2
on a1.id = a2.pid
WHERE a1.name = '广东省';

9.子查询

子查询是嵌套到主查询里面的
子查询做为主查询的数据源或者条件
子查询是独立可以单独运行的查询语句
主查询不能独立独立运行,依赖子查询的结果

-- 例 1：查询大于平均年龄的学生记录
-- SELECT avg(age) from students;
-- 
-- select * from students where age > 30.1667;

-- 用子查询实现
select * from students where age > (SELECT avg(age) from students);

标量子查询------子查询返回结果只有一行,一列

-- 例 2：查询 30 岁的学生的成绩
-- 1,查询30岁学生的studentNO
-- select studentNo from students where age = 30;
-- 
-- SELECT * from scores where studentNo in ('001', '003', '011');

-- 用子查询实现
SELECT * from scores where studentNo in 
(select studentNo from students where age = 30);

列子查询------子查询返回一列多行

-- 例 3：用子查询，查询所有女生的信息和成绩
-- 用内连接实现
SELECT * from students INNER JOIN scores ON
students.studentNo = scores.studentNo
where sex = '女';
-- 用子查询实现
select * from (SELECT * from students where sex = '女') stu
INNER JOIN scores sc on stu.studentNo = sc.studentNo;

表级子查询------子查询返回结果为多行,多列

课件提问问题及答案

-- 查询各个年龄段学生的数量,按照数量从大到小排序
select age, count(*) from students GROUP BY age ORDER BY count(*) desc;

-- 查找年龄大于等于25,小于等于30的男同学
select * from students where (age BETWEEN 25 and 30) and sex = '男';

-- 显示2班和3班的女同学
SELECT * from students where sex = '女' and class in ('2班', '3班');

-- 显示所有有效的card学生记录,null和''都是无效的
SELECT * FROM students where card is not null and card != '';


-- 查找老家不在河北和北京的同学的age总和
SELECT sum(age) from students where hometown <> '北京' and hometown <> '河北'; 
SELECT sum(age) from students where not hometown in ('北京', '河北');

-- 查找不姓'白'的同学
SELECT * from students where not name like '白%';

-- 查询每个班有分别多少同学
SELECT class, count(*) from students GROUP BY class;

-- 这个sql能不能显示年龄最大的同学的姓名
select name,max(age) from students;

-- 显示年龄最大的同学的姓名
SELECT name from students order by age desc limit 1;
SELECT name from students where age = (select max(age) from students);

-- 查询年龄最大的女同学和年龄最小的女同学差多少岁
SELECT max(age) -  min(age) from students where sex = '女';

-- 分别求男女同学的平均年龄
select sex,avg(age) from students GROUP BY sex;

-- 只显示班级人数为3个同学的班级名称
SELECT class from students GROUP BY class HAVING count(*) = 3; 

--  查询姓名只有2个字的女同学的数量
SELECT count(*) from students where sex = '女' and name like '__';

-- 查询年龄大于25,老家不在河北的男同学
SELECT * from students where age > 25 and hometown != '河北' and sex = '男' ;

-- 查询1班加3班所有同学的年龄总和
SELECT sum(age) from students where class = '1班' or class = '3班';

10.concat拼接字符串函数

concat(参数1, 参数2, 参数3, 参数n)
- 参数可以是数字,也可以是字符串
- 把所有的参数连接成一个完整的字符串

-- 例 1：把 12,34，’ab’拼接为一个字符串’1234ab’
select concat(12, 34, 'ab');

11.length返回字符串字符的个数

一个utf8格式的汉字,length返回3

-- 例 2：计算字符串’abc’的长度
select length('abc');

-- 例 3：计算字符串’我和你’的长度
SELECT length('我和你');


-- 例 4：计算字符串’我和you’的长度
SELECT length('我和you');

12.mysql内置函数可以在where条件后面使用

-- 例 4：查询表 students 中 name 长度等于 9（三个 utf8 格式的汉字）的学生信息
SELECT * from students where length(name) = 9;

13.left从字符串左侧截取指定数量字符

left(字符串, n)
- n代表从字符串左侧截取n个字符

-- 例 5：截取字符串’我和你abc’的左端 3 个字符
select left('我和你abc', 3);

-- 例 6：截取字符串’我和你abc’的左端 4 个字符
select left('我和你abc', 4);

-- 例 6：截取字符串’abc我和你’的左端 4 个字符
select left('abc我和你', 4);

14.right从字符串右侧截取指定数量的字符

rigth(字符串, n)
- n代表从字符串右侧截取n个字符

-- 例 5：截取字符串’我和你abc’的右端 3 个字符
select right('我和你abc', 3);

-- 例 5：截取字符串’我和你abc’的右端 4 个字符
select right('我和你abc', 4);

15.substring从字符串指定位置截取指定数量字符

substring(字符串, 起始位置, n)
- 起始位置从1开始
- n代表截取的数量

-- 例 7：截取字符串’我和你abc’从第 2 个字符开始的 3 个字符
select substring('我和你abc', 2, 3);


-- 例 7：截取字符串’我和你abc’从左侧开始的 3 个字符
select substring('我和你abc', 1, 3);

-- 例 7：截取字符串’我和你abc’从第 4 个字符开始的 1 个字符
select substring('我和你abc', 4, 1);

16.内置函数可以用在select显示的字段名中

-- 例 8：截取 students 表中所有学生的姓
SELECT left(name, 1) from students;
SELECT substring(name, 1, 1) from students;

17.ltrim去除字符串左侧空格

ltrim(带空格的字符串)

-- 例 1：去除字符串' abcd '左侧空格

SELECT ltrim('         abcd ');

18.rtrim去除字符串右侧空格

rtrim(带空格的字符串)

-- 例 1：去除字符串' abcd     '右侧空格
SELECT rtrim(' abcd     ');
select concat(rtrim(' abcd     '), '测试字符');

19.trim去除字符串两侧空格

trim(带空格的字符串)

-- 例 1：去除字符串'     abcd     '两侧空格
SELECT trim('     abcd     ');

20.round四舍五入

round(数字, d)
- d代表要保留的小数位,省略d默认为0

-- 例 1：1.653 四舍五入，保留整数位
SELECT round(1.653);


-- 例 2：1.653 四舍五入，保留2位小数
SELECT round(1.653, 2);

-- 例 3：查询 students 表中学生的平均年龄，并四舍五入,保留两位小数
SELECT round(avg(age), 2) from students;

21.rand随机数

rand()
- 每次运行会产生一个从0到1之间的浮点数

经常用rand对一个张进行随机排序

order by rand()

select rand();


-- 小技巧：从学生表中随机抽出一个学生
SELECT * from students order by rand() LIMIT 1;

22.current_date返回系统日期

current_date()

23.current_time返回系统时间

current_time()

24.返回系统日期与时间

now()

select current_date();

select current_time();

select now();

日期和时间函数的案例

-- 插入记录时,插入系统当前时间
create table a (id int, indate datetime);
insert into a values (1, '2010-09-10 12:01:02');
insert into a values (1, now());
select * from a;

25.存储过程

-- 例 1：创建存储过程 stu()，查询 students 表所有学生信息
CREATE PROCEDURE stu()
BEGIN
	SELECT * from students;
end

-- 调用存储过程stu
call stu();

-- 删除存储过程,删除的时候不用写名字后面的()
DROP PROCEDURE stu;
drop PROCEDURE if EXISTS stu;

26.视图

视图就是对select语句的封装
视图可以理解为一张只读的表,针对视图只能用select,不能用delete和update

-- 创建一个视图,查询所有男生信息

CREATE VIEW stu_nan as 
SELECT * from students where sex = '男';

-- 使用视图
SELECT * from stu_nan INNER JOIN scores 
on stu_nan.studentNo = scores.studentNo;

-- 删除视图
drop VIEW stu_nan;
DROP view if EXISTS stu_nan;

27.事务

事务是多条更改数据操作的sql语句集合
一个集合数据有一致性,要么就都失败,要么就都成功回滚
begin ----开始事务
rollback ----回滚事务,放弃对表的修改
commit ---- 提交事务,对表的修改生效

没有写begin代表没有事务,没有事务的表操作都是实时生效.

如果只写了begin, 没有rollback,也没有commit, 系统推出,结果是rollback

回滚事务

-- 例 1：开启事务，
-- 删除 students 表中 studentNo 为 001 的记录，
-- 同时删除 scores 表中 studentNo 为 001 的记录, 
-- 回滚事务，两个表的删除同时放弃
-- 开始事务
begin;
DELETE from students where studentNo = '001';
DELETE from scores where studentNo = '001';
-- 回滚事务,放弃更改
ROLLBACK;

如果开始一个事务,执行了begin,之后,没有rollback也没有commit,中间系统出问题了.默认会执行rollback

提交事务

-- 例 2：开启事务，
-- 删除 students 表中 studentNo 为 001 的记录，
-- 同时删除 scores 表中 studentNo 为 001 的记录, 
-- 提交事务，使两个表的删除同时生效

begin;
DELETE from students where studentNo = '001';
DELETE from scores where studentNo = '001';
-- 提交事务,一旦提交事务,两个删除操作同时生效
commit;

28.索引

index
给表建立索引,目的是加快select查询的速度
如果一个表记录很少,几十条,或者几百条,不用索引
表的记录特别多,如果没有索引,select语句效率会非常低

创建索引
- create index 索引名 on 表名(字段)
- 如果字段为字符串,需要写明创建表字段的时候字符串的长度

-- 例 1：为表 students 的 age 字段创建索引，名为 age_index
CREATE index age_index on students (age);

-- 例 2：为表 students 的 name 字段创建索引，名为 name_index
CREATE INDEX name_index on students (name(10));

调用索引
- 不需要显示的写调用索引的语句,只要where条件后面用到的字段建立了索引,那么系统会自动调用

-- where条件后面的字段,数据库系统会自动查找是否有索引
-- 这里会自动调用age_index
select * from students where age = 30;
-- 自动调用name_index
SELECT * from students where name = '李白';
-- 不会调用任何索引,因为sex字段没有索引
SELECT * from students where sex = '女';

查看索引
- show index from 表名
- 对于主键,系统会自动建立索引

-- 查看students表的索引
show index from students;

删除索引
- drop index 索引名 on 表名

show index from students;

-- 删除索引age_index
drop index age_index on students;
-- 删除索引name_index
drop index name_index on students;

索引的优缺点
- 提高select的查询速度
- 降低update,delete和insert语句的执行速度
- 项目中80%以上是select,所以index必须的
- 在实际工作中如果涉及到大量的数据修改操作,修改之前可以把索引删除,修改完成后再把索引建立起来

29.基于命令行的mysql

mysql -h mysql服务器的地址 -u 用户名 -p
- -h 如果是使用本机的mysql,-h可以省略

mysql登录之后的常用命令

show databases
- 显示系统所有的数据库
use 数据库名
- 使用指定的一个数据库

-- 使用mydb数据库
use mydb

show tables
- 查看指定数据库有多少表
如果命令行默认字符集与数据库默认字符集不同
- 在windows默认字符集是gbk
- set names gbk
  - 告诉mysql,客户端用的字符集是gbk

-- 连接到mysql
mysql -u root -p
-- 查看数据库
show databases
-- 使用mydb数据库
use mydb
-- 查看所有表
show tables
-- 设置客户端字符集为gbk
set names gbk
-- 查询students表
select * from students;

在命令行中每条sql语句用;结尾
可以通过desc 表名查看一个表的字段结构
- desc students
- 查看students每个字段的定义

在命令行下创建和删除数据库
- create database 数据库名 default charset 字符集

-- 创建一个数据库mytest,默认字符集为utf8
create database mytest default charset utf8;
-- 删除数据库mytest
drop database mytest
drop database if exists mytest;

幼稚范2r°

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL篇 ---- 连接查询与函数

如果只写了begin, 没有rollback,也没有commit, 系统推出,结果是rollback。没有写begin代表没有事务,没有事务的表操作都是实时生效.一个集合数据有一致性,要么就都失败,要么就都成功回滚。rollback ----回滚事务,放弃对表的修改。commit ---- 提交事务,对表的修改生效。一个utf8格式的汉字,length返回3。事务是多条更改数据操作的sql语句集合。经常用rand对一个张进行随机排序。begin ----开始事务。
复制链接

扫一扫