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,不然就会默认为NULLcreate 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 | 五班 |
+-----+--------+-------+---------+------+----------+------+