MySQL笔记1

已于 2023-04-10 16:52:13 修改
阅读量805 收藏 1
点赞数 1
分类专栏： 笔记 文章标签： mysql 数据库
于 2023-04-07 16:53:30 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lizyviking_/article/details/130015942
版权

MySQL1

1.登录

mysql -uroot -p密码
mysql -uroot -pMySQL12345;

或者

mysql -uroot -p

Enter Password:******

查看数据库

show databases;

+------------------+
|information_schema|
|mysql |
|performance_schema|
|sys |
+------------------+
这四个库是系统自带的，不能删除

查看表

show tables;

查看表结构

desc t_student;

+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| sno | int | NO | PRI | NULL | auto_increment |
| sname | varchar(255) | YES | | NULL | |
| grade | int | YES | | NULL | |
| subject | varchar(255) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+

查看表编码格式

show create table t_student;

在这里插入图片描述

2.创建数据库、创建表

drop database if exists study;
create database study;
use study;

查看表

show tables;

创建表

drop table if exists t_student;

需要学生表，包括：学号sno（主键）、姓名、成绩、学科

create table t_student(
    sno int primary key auto_increment,
    sname varchar(255),
    grade int,
    subject varchar(255)
) character set 'utf8mb4';
指定表的编码格式创建

1.关于primary key（主键约束）

sno后面加primary key，表示sno是主键，主键是这张表中，这一行数据的唯一标识
在一张表中，主键数据不可以为空，也不可以为NULL

2.MySQL当中常见的数据类型

int 整数型（java中的int）
bigint 长整型（java中的long）
float 浮点型（java中的float double）
char 定长字符串（String)
varchar	变长字符串（StringBuffer/StringBuilder）#用的最多
varchar 65535字节 = 65535B = 64KB

date 	日期类型（对应Java中的java.sql.Date类型）
BLOB	二进制大对象（存储图片、视频等流媒体信息）Binary Large OBject（对应java中的object）
CLOB	字符大对象（存储较大文本，比如，可以存储4G的字符串）Character Large OBject（对应java中的Object）

# 在实际开发中，如果某个字段的数据长度确定时，定长的，应该用char，例如性别、生日等。
# 当数据长度不确定时，采用varchar，例如简介、姓名等。

3.插入数据（行）

1.插入一条数据（一行）

insert into t_student(sname) values ('李虎');
insert into t_student(sname,grade,subject) values ('陈狗',90,'数学');

因为mysql提供主键值自增，在学号主键后面添加auto_increment即可实现这个功能
（即在插入某一行数据时可以不设置sno，自动维护一个自增的数字，从1开始，以1递增）

insert into t_student(sno,sname,grade,subject) values (3, '陈狗' ,85, '英语');
insert into t_student values (4, '陈狗', 89, '语文');

select * from t_student;

+-----+-------+-------+---------+
| sno | sname | grade | subject |
+-----+-------+-------+---------+
| 1 | 李虎 | NULL | NULL |
| 2 | 陈狗 | 90 | 数学 |
| 3 | 陈狗 | 85 | 英语 |
| 4 | 陈狗 | 89 | 语文 |
+-----+-------+-------+---------+

2.插入多条数据（多行）
（1）一条条插入
（2）批量插入

插入时不指定sno（auto_increment提供自增）

insert into t_student(sname,grade,subject)
values
('谢逊', 70, '语文'),
('谢逊', 40, '英语'),
('谢逊', 100, '数学')
;

金一全部缺考，成绩为NULL

insert into t_student(sname,subject)
values
('金一', '语文'),
('金一', '数学'),
('金一', '英语')
;

select * from t_student;
+-----+-------+-------+---------+
| sno | sname | grade | subject |
+-----+-------+-------+---------+
| 1 | 李虎 | NULL | NULL |
| 2 | 陈狗 | 90 | 数学 |
| 3 | 陈狗 | 85 | 英语 |
| 4 | 陈狗 | 89 | 语文 |
| 5 | 谢逊 | 70 | 语文 |
| 6 | 谢逊 | 40 | 英语 |
| 7 | 谢逊 | 100 | 数学 |
| 8 | 金一 | NULL | 语文 |
| 9 | 金一 | NULL | 数学 |
| 10 | 金一 | NULL | 英语 |
+-----+-------+-------+---------+
未设置的值会自动填充NULL

ps:

在创建表时可以加一句default a，表示该数据默认值为a，不然就会默认为NULL

create table t_xxx(

​	...

​	grade int default 0,	

​	...

) character set 'utf8mb4';

select database();查看当前使用的是哪个数据库

\c结束命令

exit退出mysql

@表结构的修改

select * from t_xueke;

+-----+---------+
| xno | subject |
+-----+---------+
| 1 | 数学 |
| 2 | 英语 |
| 3 | 语文 |
| 4 | 武术 |
| 5 | 唱歌 |
| 6 | 舞蹈 |
| 7 | 篮球 |
| 8 | NULL |
+-----+---------+

alter table t_name
{
  新增一列：add 新列名称 类型;

  修改某一列：change 原列名称 新列名称 新列类型;

  修改表名称：旧表名 rename 新表名;

  修改表编码：convert to character set 新编码;

  删除一列：drop column 列名;
}

1.增加一列新属性’学科类型’，数据类型为varchar

alter table t_xueke add subject_type varchar(255);

select * from t_xueke;

+-----+---------+--------------+
| xno | subject | subject_type |
+-----+---------+--------------+
|   1 | 数学    | NULL         |
|   2 | 英语    | NULL         |
|   3 | 语文    | NULL         |
|   4 | 武术    | NULL         |
|   5 | 唱歌    | NULL         |
|   6 | 舞蹈    | NULL         |
|   7 | 篮球    | NULL         |
|   8 | NULL    | NULL         |
+-----+---------+--------------+

2.修改学科类型这一列的列名为学科类型

alter table t_xueke change subject_type 学科类型 varchar(255);

select * from t_xueke;

+-----+---------+----------+
| xno | subject | 学科类型 |
+-----+---------+----------+
|   1 | 数学    | NULL     |
|   2 | 英语    | NULL     |
|   3 | 语文    | NULL     |
|   4 | 武术    | NULL     |
|   5 | 唱歌    | NULL     |
|   6 | 舞蹈    | NULL     |
|   7 | 篮球    | NULL     |
|   8 | NULL    | NULL     |
+-----+---------+----------+

3.修改表名为xueke

alter table t_xueke rename xueke;

show tables;

+-----------------+
| Tables_in_study |
+-----------------+
| t_indentity     |
| t_student       |
| xueke           |
+-----------------+

4.修改表编码格式为utf8mb4

alter table xueke convert to character set 'utf8mb4';

show create table xueke;

在这里插入图片描述

复原

alter table xueke rename t_xueke;

alter table t_xueke drop column 学科类型;

4.单表查询

(1).一条完整的sql语句

select         5
...
from           		1
...
where          		2 
...
group by(分组)       3
...
having         		4
...
order by(升asc/降序desc)       6
...
limit          7
...;

找出成绩排名在前三名的学生？

select sname,grade,subject 
from t_student
order by grade desc limit 1,3;

+-------+-------+---------+
| sname | grade | subject |
+-------+-------+---------+
| 陈狗 | 90 | 数学 |
| 陈狗 | 89 | 语文 |
| 陈狗 | 85 | 英语 |
+-------+-------+---------+

ps: sql语句不区分大小写
任何一条sql语句以;结尾

(2).关系运算符

大于> 
小于 <
大于等于>= 
小于等于<= 
不等于!=、<>

1.找出成绩高于80

select * from t_student where grade > 80;

2.找出成绩小于等于90

select * from t_student where grade <=90;

3.找出成绩不等于80

select * from t_student where grade <> 80;

4.找出成绩在70到90之间，包括70和90

select * from t_student where grade>=70 and grade <=90;
select * from t_student where grade between 70 and 90;

上述结果表示[70,90] 如果between 'a' and 'c' 则是左闭右开[a,c)

5.找出哪些人成绩(不)为NULL

select * from t_student where grade is (not) null;

将成绩为NULL的转换为0

select ename, ifnull(grade,0) subject from t_student;

使用ifnull(a,b)函数

6.找出哪些人没有成绩（可能为0，可能为NULL，先判断null）

select * from t_student where grade is null or grade = 0;
or表示符合这两个条件其中一个就可以

7.找出科目是数学和英语的学生分数

select * from t_student where subject='数学' or subject='英语';
select * from t_student where subject in ('数学','英语');

in后面的值不是区间，是具体的值。

select * from t_student where grade in(null, 0);

找出成绩大于80以及科目是数学和英语的学生分数

select * from t_student where grade>80 and subject in ('数学','英语');
//and表示必须同时满足两个条件
select * from t_student where grade>80 and (subject='数学' or subject='英语');

(3).分组函数

分组函数
count(),sum(),avg(),max(),min()

这些函数都是在group by之后才执行的
如果没有写group by，整张表自成一组

查询每一科的平均成绩？

select subject'科目', avg(grade)'平均分' from t_student group by subject;

+------+---------+
| 科目 | 平均分 |
+------+---------+
| NULL | NULL |
| 数学 | 95.0000 |
| 英语 | 62.5000 |
| 语文 | 79.5000 |
+------+---------+

找出总人数？

select count(*) from t_student;

ps:

分组函数一共5个。

分组函数还有另一个名字：多行处理(聚合)函数。

多行处理函数的特点：输入多行，最终输出的结果是1行。分组函数自动忽略NULL。

在Oracle语法中，当一条语句有group by时，select后只允许跟分组函数和参与分组的字段。

（在mysql中，允许跟其他的字段，但是不推荐，因为不规范）

(4).having过滤

having是对group by分组之后的数据进行第二次过滤，在group by之后执行
找出每个学科的最高分数，只显示分数大于80的数据

select subject,max(grade) max_grade from t_student
group by subject
having max_grade>80;

+---------+-----------+
| subject | max_grade |
+---------+-----------+
| 数学 | 100 |
| 英语 | 85 |
| 语文 | 89 |
+---------+-----------+
ps:

where是在group by分组之前执行的，即先对集合用where进行筛选，然后对筛选后的数据进行分组

而having是对groupby之后的数据再次进行过滤

只有group by之后才会有max(grade)，所以where后面不能直接跟分组函数

select		    5
	..
from			1	
	..
where			2
	..
group by		3
	..
having		    4
	..
order by		6
	..

select之后数据内容不会再改变了，所以select是放在筛选之后，最后做一个汇总

order by仅仅是对这个结果进行排序

order by max_grade asc(升序) / desc(降序);

@其实还有limit，更是在order by之后

limit 0,n : 表示从第0行取n行数据（在使用limit时，行数是从0开始的）
例1：取最好的三个成绩

select * from t_student order by grade desc limit 0,3;

+-----+-------+-------+---------+
| sno | sname | grade | subject |
+-----+-------+-------+---------+
| 7 | 谢逊 | 100 | 数学 |
| 2 | 陈狗 | 90 | 数学 |
| 4 | 陈狗 | 89 | 语文 |
+-----+-------+-------+---------+
例2：取成绩的第3名到第6名

select * from t_student order by grade desc limit 2,4;

+-----+-------+-------+---------+
| sno | sname | grade | subject |
+-----+-------+-------+---------+
| 4 | 陈狗 | 89 | 语文 |
| 3 | 陈狗 | 85 | 英语 |
| 5 | 谢逊 | 70 | 语文 |
| 6 | 谢逊 | 40 | 英语 |
+-----+-------+-------+---------+
例3：从每个学生的最高分中查询第二名和第三名
第二名即第’1’行数据，从第’1’行开始取两行

select sname,max(grade) as max_grade from t_student group by sname order by max_grade desc limit 1,2;

或

select sname,max(grade) as max_grade from t_student group by sname order by max_grade desc limit 2 offset 1;

+-------+-----------+
| sname | max_grade |
+-------+-----------+
| 陈狗 | 90 |
| 李虎 | NULL |
+-------+-----------+

(5).模糊查询 like

先插入一些数据

insert into t_student(sname,grade,subject)
values
('David', 90, '英语'),
('Bruce_Lee', 101, '武术'),
('Beyond', 99, '唱歌'),
('Jones', 80, '舞蹈'),
('Scott', 70, '数学'),
('James', 80, '篮球'),
('Martin', 60, '数学')

;

学生表全部数据

select * from t_student;

+-----+-----------+-------+---------+
| sno | sname | grade | subject |
+-----+-----------+-------+---------+
| 1 | 李虎 | NULL | NULL |
| 2 | 陈狗 | 90 | 数学 |
| 3 | 陈狗 | 85 | 英语 |
| 4 | 陈狗 | 89 | 语文 |
| 5 | 谢逊 | 70 | 语文 |
| 6 | 谢逊 | 40 | 英语 |
| 7 | 谢逊 | 100 | 数学 |
| 8 | 金一 | NULL | 语文 |
| 9 | 金一 | NULL | 数学 |
| 10 | 金一 | NULL | 英语 |
| 11 | David | 90 | 英语 |
| 12 | Bruce_Lee | 101 | 武术 |
| 13 | Beyond | 99 | 唱歌 |
| 14 | Jones | 80 | 舞蹈 |
| 15 | Scott | 70 | 数学 |
| 16 | James | 80 | 篮球 |
| 17 | Martin | 60 | 数学 |
+-----+-----------+-------+---------+
ps: 在模糊查询当中，必须掌握两个特殊的符号，一个是%，一个是_
%代表任意多个字符，_代表任意1个字符
寻找名字中含有o的？

select sname from t_student where sname like '%o%';

+--------+
| sname |
+--------+
| Beyond |
| Jones |
| Scott |
+--------+

寻找名字中第二个字母是a的？

select sname from t_student where sname like '_a%';

+--------+
| sname |
+--------+
| David |
| James |
| Martin |
+--------+

寻找名字中有下划线的？

select sname from t_student where sname like '%_%';

（这样写肯定是不行的，因为在这里下划线代表的是1个字符，而不是它的本意，需要转义字符进行转义）

select sname from t_student where sname like '%\_%';

+-----------+
| sname |
+-----------+
| Bruce_Lee |
+-----------+

寻找名字中最后一个字母为s的？

select sname from t_student where sname like '%s';

+-------+
| sname |
+-------+
| Jones |
| James |
+-------+

(6).查询结果集的去重 distinct

select distinct subject from t_student;

+---------+
| subject |
+---------+
| NULL |
| 数学 |
| 英语 |
| 语文 |
| 武术 |
| 唱歌 |
| 舞蹈 |
| 篮球 |
+---------+

ps:

distinct只能出现在所有字段的最前面

统计学生的数量？

select count(distinct sname) from t_student;

+-----------------------+
| count(distinct sname) |
+-----------------------+
| 11 |
+-----------------------+

(7).union查询结果集相加

例：查询一班和二班的学生有哪些

select name,class from t_indentity where class='一班' or class='二班';

select name,class from t_indentity where class in ('一班', '二班');

使用union

select name,class from t_indentity where class='一班'

union

select name,class from t_indentity where class='二班';

例：两张不相干的表中的数据拼接在一起显示？

select name from t_indentity

union

select subject from t_xueke;

+-----------+
| name      |
+-----------+
| 李虎      |
| 陈狗      |
| 谢逊      |
| 金一      |
| David     |
| Bruce_Lee |
| Beyond    |
| Jones     |
| Scott     |
| James     |
| Martin    |
| 数学      |
| 英语      |
| 语文      |
| 武术      |
| 唱歌      |
| 舞蹈      |
| 篮球      |
| NULL      |
+-----------+

(8).子查询

单行子查询：

子查询/嵌套的SQL语句返回单条记录，根据返回的单条记录筛选外部的条件 = 或 !=

多行子查询：

1.使用in关键字

子查询返回多条记录，根据返回的多条记录外部筛选条件，可以使用in或not in

查询出课程编号为1和3这两门课的课程成绩。

a.先查询出课程编号为1和3对应的课程名称

​ => 返回两条记录，分别对应着subject

select subject from t_xueke where xno in(1,3);
+---------+
| subject |
+---------+
| 数学    |
| 语文    |
+---------+

b.再根据查询出来的课程名查询这两门课的成绩

select sname,grade,subject from t_student where subject in
(select subject from t_xueke where xno in(1,3)
);
+--------+-------+---------+
| sname  | grade | subject |
+--------+-------+---------+
| 陈狗   |    90 | 数学    |
| 陈狗   |    89 | 语文    |
| 谢逊   |    70 | 语文    |
| 谢逊   |   100 | 数学    |
| 金一   |  NULL | 语文    |
| 金一   |  NULL | 数学    |
| Scott  |    70 | 数学    |
| Martin |    60 | 数学    |
+--------+-------+---------+

第一种方式in，执行过程就是两次查询，先根据内部子查询得到课程名称，t_xueke.subject，

然后拿着这个课程名去外部条件中过滤

2.使用exists关键字
select grade from t_student where exists(
	select subject from t_xueke where xno in(1,3)
    and
    t_student.subject = t_xueke.subject
);
+-------+
| grade |
+-------+
|    90 |
|    89 |
|    70 |
|   100 |
|  NULL |
|  NULL |
|    70 |
|    60 |
+-------+

第二种方式先执行外部查询（即直接从t_student表查询grade），

依次取出外部查询中的每一条语句去和内部查询匹配，一旦匹配上就保留结果，否则就丢弃

3.使用表连接的方式
select t1.sname,t1.grade,t1.subject
from t_student t1
left join t_xueke t2
on t1.subject = t2.subject
where t2.xno in (1,3);
+--------+-------+---------+
| sname  | grade | subject |
+--------+-------+---------+
| 陈狗   |    90 | 数学    |
| 陈狗   |    89 | 语文    |
| 谢逊   |    70 | 语文    |
| 谢逊   |   100 | 数学    |
| 金一   |  NULL | 语文    |
| 金一   |  NULL | 数学    |
| Scott  |    70 | 数学    |
| Martin |    60 | 数学    |
+--------+-------+---------+

5.多表查询（连接查询）

EMP表
+--------+--------+
| ename | deptno |
+--------+--------+
| SMITH | 20 |
| ALLEN | 30 |
| WARD | 30 |
| JONES | 20 |
| MARTIN | 30 |
| BLAKE | 30 |
| CLARK | 10 |
| SCOTT | 20 |
| KING | 10 |
| TURNER | 30 |
| ADAMS | 20 |
| JAMES | 30 |
| FORD | 20 |
| MILLER | 10 |
+--------+--------+
DEPT表
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+

例：查询每个员工的姓名和部门名称

select ename,dname from emp,dept;

+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
| SMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | RESEARCH |
| ALLEN | SALES |
| ALLEN | OPERATIONS |
............
56 rows in set (0.00 sec)

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

我们可以对笛卡尔积进行过滤，但是记录的匹配次数有不会变，只是显示了我们需要的数据

(1).内连接

a.等值连接

等值连接就是：连接条件是等量关系

即on后面跟的条件

查询每个员工的部门名称，要求显示员工名和部门名

SQL99：

select 
	e.ename,d.dname
from
	emp e
inner join
	dept d
on
	e.deptno = d.deptno;

// inner可以省略的，带着inner目的是可读性好一些。

+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+

b.非等值连接

非等值连接就是：连接条件不是等值关系

案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。

select ename,sal from emp;

+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

select * from salgrade;

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+

select 
	e.ename,e.sal,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+

自连接：即这两张表都是它自己和自己相连，进行查询。

(2).外连接

1.内连接时：两张表是平等的，没有主副之分，只有在两张表都匹配时才会查询出来

2.外连接时：两张表分为主表和副表，不管副表有没有那个数据，主表中的所有数据都会匹配出来

当副表没有数据时自动填充NULL，并合并成一张表

左连接时，left join 之前的是主表

右连接时，right join 之后的是主表

左连接可以写成右连接，右连接也可以写成左连接

select a.id, b.name

​	from a

​	left (outer) join b

on a.id = b.id;

等同于

select a.id, b.name

​	from b

​	right join a

on a.id = b.id;

(3).全连接

即满外连接，两张表中所有没有的数据都会继续查询，并填充NULL

在这里插入图片描述

(4).三张表连在一起查询

1.创建表，用于连接查询

2.插入数据

向学科表中插入学生表中的那几个学科，而且编号

向分数表中插入学生表的学生姓名，新增性别、班级两列

学科表

create table t_xueke(
	xno int primary key auto_increment,
	subject varchar(255)
) character set 'utf8mb4';

+-----+---------+
| xno | subject |
+-----+---------+
| 1 | 数学 |
| 2 | 英语 |
| 3 | 语文 |
| 4 | 武术 |
| 5 | 唱歌 |
| 6 | 舞蹈 |
| 7 | 篮球 |
| 8 | NULL |
+-----+---------+

身份表

create table t_indentity(
	ino int primary key auto_increment,
	name varchar(255),
	sex varchar(255),
	class varchar(255)
);

+-----+-----------+------+-------+
| ino | name | sex | class |
+-----+-----------+------+-------+
| 1 | 李虎 | 男 | 一班 |
| 2 | 陈狗 | 男 | 一班 |
| 3 | 谢逊 | 男 | 二班 |
| 4 | 金一 | 女 | 二班 |
| 5 | David | 男 | 三班 |
| 6 | Bruce_Lee | 男 | 四班 |
| 7 | Beyond | 男 | 四班 |
| 8 | Jones | 女 | 三班 |
| 9 | Scott | 女 | 四班 |
| 10 | James | 男 | 三班 |
| 11 | Martin | 男 | 五班 |
+-----+-----------+------+-------+

学生表

+-----+-----------+-------+---------+
| sno | sname | grade | subject |
+-----+-----------+-------+---------+
| 1 | 李虎 | NULL | NULL |
| 2 | 陈狗 | 90 | 数学 |
| 3 | 陈狗 | 85 | 英语 |
| 4 | 陈狗 | 89 | 语文 |
| 5 | 谢逊 | 70 | 语文 |
| 6 | 谢逊 | 40 | 英语 |
| 7 | 谢逊 | 100 | 数学 |
| 8 | 金一 | NULL | 语文 |
| 9 | 金一 | NULL | 数学 |
| 10 | 金一 | NULL | 英语 |
| 11 | David | 90 | 英语 |
| 12 | Bruce_Lee | 101 | 武术 |
| 13 | Beyond | 99 | 唱歌 |
| 14 | Jones | 80 | 舞蹈 |
| 15 | Scott | 70 | 数学 |
| 16 | James | 80 | 篮球 |
| 17 | Martin | 60 | 数学 |
+-----+-----------+-------+---------+

习题：

注意：三表连接时
	....
		A
	join
		B
	join
		C
	on
		...
表示：A表和B表先进行表连接，连接之后的新表继续和C表进行连接。

1.查询所有学生的各个学科成绩和所在班级

select t1.*, t2.class'班级'

from t_student t1

join t_indentity t2

on t1.sname = t2.name;

2.查询所有男生的所有成绩

select t1.*, t2.sex'性别'

from t_student t1

join t_indentity t2

on t1.sname = t2.name

where t2.sex='男';

3.查询所有女生的所有成绩

select t1.*, t2.sex'性别'

from t_student t1

join t_indentity t2

on t1.sname = t2.name

where t2.sex='女';

4.查询一班有哪些学生，以及他们的成绩

select t1.*, t2.class'班级'

from t_student t1

join t_indentity t2

on t1.sname = t2.name

where t2.class='一班';

5.查询所有男生的数学成绩，以及数学科目学科编号

(1)先查询出所有男生的数学成绩

用左连接，查询每一个男生

select t1.*, t2.sex'性别'

from t_student t1

left join t_indentity t2

on t1.sname = t2.name

where t1.subject = '数学' and t2.sex = '男';

+-----+--------+-------+---------+------+
| sno | sname  | grade | subject | 性别 |
+-----+--------+-------+---------+------+
|   2 | 陈狗   |    90 | 数学    | 男   |
|   7 | 谢逊   |   100 | 数学    | 男   |
|  17 | Martin |    60 | 数学    | 男   |
+-----+--------+-------+---------+------+

(2)继续查询它们的学科编号

select t1.*, t2.sex'性别', t3.xno'学科编号'

from t_student t1

left join t_indentity t2

on t1.sname = t2.name

left join t_xueke t3

on t1.subject = t3.subject

where t1.subject = '数学' and t2.sex = '男';

+-----+--------+-------+---------+------+----------+
| sno | sname  | grade | subject | 性别 | 学科编号 |
+-----+--------+-------+---------+------+----------+
|   2 | 陈狗   |    90 | 数学    | 男   |        1 |
|   7 | 谢逊   |   100 | 数学    | 男   |        1 |
|  17 | Martin |    60 | 数学    | 男   |        1 |
+-----+--------+-------+---------+------+----------+

**6.**查询各个学科最高分的学生，以及他们的班级和学科编号

(1).先查询各个学科的最高分

select subject, max(grade) max_grade from t_student

group by subject;

在这里插入图片描述

因为在使用分组函数之后，会先按照group by后面的的字段对整张表进行分组，然后再求每一个组里面的最高分，在mysql中，使用了这种函数之后，select后面就只能跟分组函数和参与分组的字段了。

(2).所以采用表连接的方式来获取姓名

得到这些成绩都是谁的，将上述查询结果作为一个新表

select t1.sname, t2.*

from t_student t1

join (select subject, max(grade) max_grade from t_student group by subject) t2

on t1.subject = t2.subject

where t1.grade = t2.max_grade;

+-----------+---------+-----------+
| sname | subject | max_grade |
+-----------+---------+-----------+
| 谢逊 | 数学 | 100 |
| David | 英语 | 90 |
| 陈狗 | 语文 | 89 |
| Bruce_Lee | 武术 | 101 |
| Beyond | 唱歌 | 99 |
| Jones | 舞蹈 | 80 |
| James | 篮球 | 80 |
+-----------+---------+-----------+

但是还是没有得到班级和学科编号，班级在t_indentity表中，学科编号在t_xueke中

(3).继续连接查询

a.将上述查询结果当成一张表

select t1.*, t2.class'班级', t3.xno'学科编号'

from (select t1.sname, t2.*
from t_student t1
join (select subject, max(grade) max_grade from t_student group by subject) t2
on t1.subject = t2.subject
where t1.grade = t2.max_grade) t1

join t_indentity t2

on t1.sname = t2.name

join t_xueke t3

on t1.subject = t3.subject;

在这里插入图片描述

b.也可以四表相连

7.查询总成绩表，按照从大到小进行排序，以及每个学生的班级

先查询出总成绩表，并排序

select sname, sum(grade) from t_student group by sname order by sum(grade) desc;

在这里插入图片描述

然后再查询班级

select t1.*, t2.class

from (select sname,sum(grade)'总分' from t_student group by sname order by 
      sum(grade) desc) t1
      
join t_indentity t2

on t1.sname = t2.name;

在这里插入图片描述

8.查询成绩为NULL的学生，以及他们的班级，和成绩为NULL的那个学科的编号

select t1.*, t2.xno'学科编号', t3.class'班级'

from t_student t1

left join t_xueke t2

on t1.subject = t2.subject

join t_indentity t3

on t1.sname = t3.name

where t1.grade is null;

因为李虎没有学科也没有成绩，左连接时不会忽略任何一个来自于t_student表的学生

+-----+-------+-------+---------+----------+------+
| sno | sname | grade | subject | 学科编号 | 班级 |
+-----+-------+-------+---------+----------+------+
|   1 | 李虎  |  NULL | NULL    |     NULL | 一班 |
|  10 | 金一  |  NULL | 英语    |        2 | 二班 |
|   9 | 金一  |  NULL | 数学    |        1 | 二班 |
|   8 | 金一  |  NULL | 语文    |        3 | 二班 |
+-----+-------+-------+---------+----------+------+

9.合并三个表（查询所有人的姓名，班级，成绩，学科，学科编号）

select t1.*, t2.xno'学科编号', t3.class'班级'

from t_student t1

left join t_xueke t2

on t1.subject = t2.subject

join t_indentity t3

on t1.sname = t3.name;

这里为什么用left join去连接t2呢？

因为不是每一个学生都有学科，用左连接不管有没有学科，都查出来

+-----+-----------+-------+---------+----------+------+
| sno | sname | grade | subject | 学科编号 | 班级 |
+-----+-----------+-------+---------+----------+------+
| 1 | 李虎 | NULL | NULL | NULL | 一班 |
| 2 | 陈狗 | 90 | 数学 | 1 | 一班 |
| 3 | 陈狗 | 85 | 英语 | 2 | 一班 |
| 4 | 陈狗 | 89 | 语文 | 3 | 一班 |
| 5 | 谢逊 | 70 | 语文 | 3 | 二班 |
| 6 | 谢逊 | 40 | 英语 | 2 | 二班 |
| 7 | 谢逊 | 100 | 数学 | 1 | 二班 |
| 8 | 金一 | NULL | 语文 | 3 | 二班 |
| 9 | 金一 | NULL | 数学 | 1 | 二班 |
| 10 | 金一 | NULL | 英语 | 2 | 二班 |
| 11 | David | 90 | 英语 | 2 | 三班 |
| 12 | Bruce_Lee | 101 | 武术 | 4 | 四班 |
| 13 | Beyond | 99 | 唱歌 | 5 | 四班 |
| 14 | Jones | 80 | 舞蹈 | 6 | 三班 |
| 15 | Scott | 70 | 数学 | 1 | 四班 |
| 16 | James | 80 | 篮球 | 7 | 三班 |
| 17 | Martin | 60 | 数学 | 1 | 五班 |
+-----+-----------+-------+---------+----------+------+

10.查询名字中第二个字母为a的学生的基本信息（姓名，性别，成绩，学科，学科编号，班级）

select t1.*, t3.sex'性别', t2.xno'学科编号', t3.class'班级'

from t_student t1

left join t_xueke t2

on t1.subject = t2.subject

join t_indentity t3

on t1.sname = t3.name

where t1.sname like '_a%';

+-----+--------+-------+---------+------+----------+------+
| sno | sname | grade | subject | 性别 | 学科编号 | 班级 |
+-----+--------+-------+---------+------+----------+------+
| 11 | David | 90 | 英语 | 男 | 2 | 三班 |
| 16 | James | 80 | 篮球 | 男 | 7 | 三班 |
| 17 | Martin | 60 | 数学 | 男 | 1 | 五班 |
+-----+--------+-------+---------+------+----------+------+

_小树
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
Mysql笔记1
08-08
MySQL是世界上最流行的关系型数据库管理系统之一，用于永久性存储数据。关系型数据库基于关系模型，其中数据被组织成二维表格，这些表格代表实体及其之间的联系。在MySQL中，每个二维表被称为一个“表”，每行代表一...
华为云征文｜Flexus云服务X实例应用，通过QT连接华为云MySQL，进行数据库的操作，数据表的增删改查
Arya的博客，专注后端领域
09-03 1133
4核12G-100G-3M规格的Flexus X实例使用测评第3弹：Flexus云服务X实例应用，通过QT连接华为云MySQL，进行数据库的操作，数据表的增删改查
【大数据】Canal实现MySQL数据增量同步至Kafka：原理与配置解析
一个不断前行的程序者
09-04 1182
Canal是一款开源的数据库增量日志解析组件，主要用于监控数据库数据变更，并将变更数据同步到其他存储介质。Canal通过模拟MySQL Slave的交互协议，实时获取数据库的增量更新，从而实现数据同步。本文将介绍如何使用Canal将MySQL的数据通过监听Binlog，增量发送到Kafka。Binlog（Binary Log）是MySQL数据库的二进制日志，记录了所有对数据库数据的修改操作。开启Binlog后，MySQL会实时将数据变更记录到Binlog文件中。
策略规划：在MySQL中实现数据恢复的全面指南
2401_85339615的博客
09-04 1031
数据恢复是数据库管理中至关重要的一环，它确保在发生数据丢失或损坏的情况下，能够迅速且准确地恢复数据。在MySQL中，实现有效的数据恢复策略规划需要综合考虑备份策略、备份类型、存储管理、故障转移机制以及恢复流程。本文将深入探讨如何在MySQL中进行数据恢复的策略规划，包括策略制定、技术实现和最佳实践。
MySQL的 where 1=1会不会影响性能
最新发布
ning的博客
09-07 434
通过分析和实验，我们可以得出结论：在现代的MySQL版本中，使用where 1=1并不会对查询性能产生显著影响，因为MySQL的优化器会对其进行常量折叠优化。然而，选择最合适的方法还需要考虑你的具体使用场景和ORM框架。在任何情况下，编写清晰、可维护的代码始终是最重要的。版权声明：本博客内容为原创，转载请保留原文链接及作者信息。参考文章MySQL中where 1=1真的会影响性能么？MySQL的 where 1=1会不会影响性能？看完官方文档就悟了！
【MySQL】Ubuntu22.04安装MySQL8.0.39及修改默认用户名和密码
2301_76435948的博客
09-04 1137
记录下在ubuntu22.04上安装mysql，以及修改默认用户名和密码的操作。
构建专业团队：MySQL数据恢复策略培训指南
2401_85812026的博客
09-04 539
在企业数据管理中，数据库的安全性和稳定性是至关重要的。MySQL作为广泛使用的数据库系统之一，其数据恢复策略的有效实施对于保障数据安全和业务连续性极为重要。而实现有效数据恢复的关键在于拥有一支经过专业培训的团队。本文将深入探讨如何在MySQL环境中实施数据恢复策略的培训，包括培训的目标、内容、方法和评估。
MySQL数据备份的存储管理：策略、实践与自动化
2401_85339615的博客
09-04 532
确定备份文件的保留时间，以满足业务需求和法规要求。
【重学 MySQL】十四、显示表结构
CSDN全栈领域优秀博主，阿里云博客专家，工信部权威认证物联网技术开发工程师，曾30分钟高分通过C语言二级，两天手撸代码完成EasyX飞机大战，靠接软件私活一个月换苹果电脑
09-06 510
以上就是在MySQL中显示表结构的几种常用方法。DESCRIBE或DESC命令和命令提 供了快速查看表列信息的简便方式，而查询数据库则提供了更详细、更灵活的信息获取方式。命令则适用于需要查看或复制表完整结构的情况。
mysql（查询）
m0_64245578的博客
09-02 1042
【代码】mysql（查询）
【MySQL】MySQL Workbench下载安装、环境变量配置、基本MySQL语句、新建Connection
gyeolhada的博客
09-04 634
【MySQL】MySQL Workbench下载安装、环境变量配置、基本MySQL语句、新建Connection （手把手，包会）
【重学MySQL】四、关系型数据库设计规则
CSDN全栈领域优秀博主，阿里云博客专家，工信部权威认证物联网技术开发工程师，曾30分钟高分通过C语言二级，两天手撸代码完成EasyX飞机大战，靠接软件私活一个月换苹果电脑
09-03 1070
当一个表的每个记录（行）与另一个表的某个记录（行）具有唯一对应关系时，就存在一对一关系。在这种关系中，每个表的主键和外键都可以互相对应。
Mysql(一) - 数据库操作, 表操作, CRUD
nmbg11的博客
09-03 1321
mysql中 对数据库的操作, 对数据表的操作, 以及 基本的CRUD (增 查 改 删).
mysql SQL 错误 [1064] [42000]:
qq_36636312的博客
09-04 367
SQL 错误 [1064] [42000]: 同样的sql，其中一个报错
Mysql中的锁机制详解
weixin_44991038的博客
09-03 1045
mysql的锁机制详解，共享锁、排它锁、乐观锁、悲观锁、间隙锁、临键锁、插入意向锁
go解析YAML文件多文档
xsw164711368的博客
09-03 1239
本文介绍了YAML文档的特点，并用代码演示了GO语言对YAML文档的处理，特别是描述了当YAML文件中存在多个YAML文档时如何处理的方法。
Mysql--基础知识点--000--Mysql面试
Chasing__Dreams的博客
09-04 286
relay log: 主从复制时从节点中的日志，可以从。binlog日志是server层处理的。哈希索引解决冲突的方法：链地址法。适合使用哈希索引适合的情况。分表：MyIsam分表。
ABAP正则表达式 &特殊字符处理
woniu_maggie的博客
09-04 822
ABAP正则表达式 &特殊字符处理
"Mysql笔记1：关系型数据库的原理与应用
MySQL是一个关系型数据库管理系统，用于永久性保存数据的仓库。与之相对应的是PHP的临时变量，它在PHP脚本执行周期内临时保存变量的概念。关系型数据库是基于关系模型建立的数据存储系统，它利用二维表的方式去描述...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

_小树

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值