Mysql查询总结

作者: wangze | 来源:发表于2021-06-14 21:57 被阅读0次

MySQL 索引及查询优化总结-2018-03-20
MySQL慢查询日志总结
MySQL--基础二
MySQL查询性能优化总结
MySql查询-总结
Mysql查询总结
17.MySQL优化
深入学习MySQL优化
MySQL索引知多少
CentOS下mysql数据库常用命

mysql

第一部分：准备数据

1 创建学生表


create table student (
    student_number varchar(20) primary key,
    student_name varchar(20) not null,
    student_sex varchar(20) not null default "men",
    check(student_sex='men' OR student_sex='women'),
    check(student_number>99 AND student_number<200),
    student_birthday datetime,
    class varchar(20)
);


扩展（check约束）
student_number 大于99，小于200
student_sex 值为 men 或者 women

2 插入数据


insert into student values('101','zhao-san','women','1992-01-01','95034');
insert into student values('102','wan-san','men','1993-01-01','95033');
insert into student values('103','sun-san','women','1994-01-01','95033');
insert into student values('104','li-san','men','1996-01-01','95033');
insert into student values('105','zhou-san','women','1991-04-01','95033');
insert into student values('106','fen-san','women','1991-11-01','95033');
insert into student values('107','chen-san','women','1994-06-02','95033');
insert into student values('108','chu-san','women','1951-06-01','95033');

3 创建教师表


create table teacher(
    teacher_number varchar(20) primary key,
    teacher_name varchar(20) not null,
    teacher_sex varchar(20) not null,
    teacher_birthday datetime,
    prof varchar(20) not null,
    depart varchar(20) not null
);

4 插入数据


insert into teacher values('201','kobe','men','1978-4-1','legend','basketball3');
insert into teacher values('202','james','men','1988-4-4','actor','basketball2');
insert into teacher values('203','jordan','women','1988-4-4','legend','basketball4');
insert into teacher values('204','durant','women','1981-4-4','legends','basketball1');
insert into teacher values('205','paul','men','1983-11-4','defense','basketball5');
insert into teacher values('206','yao','men','1983-11-4','defense','basketball7');
insert into teacher values('207','yi','men','1983-11-4','defense','basketball8');
insert into teacher values('208','allen','men','1983-11-4','defense','basketball9');

5 创建课程表


create table course(
    course_number varchar(20) primary key,
    course_name varchar(20) not null,
    teacher_number varchar(20) not null,
    foreign key (teacher_number) references teacher(teacher_number)
);

6 插入数据


insert into course values('303-1','english','201');
insert into course values('303-2','math','202');
insert into course values('303-3','python','203');
insert into course values('303-4','java','204');
insert into course values('303-5','c++','205');
insert into course values('303-6','auto','206');

7 创建成绩表


create table score(
    student_number varchar(20) not null,
    course_number varchar(20) not null,
    degree decimal,
    foreign key (student_number) references student(student_number),
    foreign key (course_number) references course(course_number),
    primary key(student_number,course_number)
);

# 联合主键: primary key(student_number,course_number)

8 插入数据


insert into score values('101','303-3','88');
insert into score values('102','303-4','89');
insert into score values('103','303-3','87');
insert into score values('104','303-3','86');
insert into score values('107','303-5','99');
insert into score values('107','303-2','92');
insert into score values('105','303-1','100');
insert into score values('106','303-1','82');
insert into score values('102','303-2','87');
insert into score values('101','303-6','95');

第二部分 : 查询练习

1 查询student表中所有记录

select * from student;

查询结果
mysql> select * from student;
+----------------+--------------+-------------+---------------------+-------+
| student_number | student_name | student_sex | student_birthday    | class |
+----------------+--------------+-------------+---------------------+-------+
| 101            | zhao-san     | women       | 1992-01-01 00:00:00 | 95034 |
| 102            | wan-san      | men         | 1993-01-01 00:00:00 | 95033 |
| 103            | sun-san      | women       | 1994-01-01 00:00:00 | 95033 |
| 104            | li-san       | men         | 1996-01-01 00:00:00 | 95033 |
| 105            | zhou-san     | women       | 1991-04-01 00:00:00 | 95033 |
| 106            | fen-san      | women       | 1991-11-01 00:00:00 | 95033 |
| 107            | chen-san     | women       | 1994-06-02 00:00:00 | 95033 |
| 108            | chu-san      | women       | 1951-06-01 00:00:00 | 95033 |
+----------------+--------------+-------------+---------------------+-------+
8 rows in set (0.01 sec)

2 查询select 表中所有记录的student_number，student_sex及class字段（列）。

select student_name,student_sex,class from student;

查询结果
mysql> select student_name,student_sex,class from student;
+--------------+-------------+-------+
| student_name | student_sex | class |
+--------------+-------------+-------+
| zhao-san     | women       | 95034 |
| wan-san      | men         | 95033 |
| sun-san      | women       | 95033 |
| li-san       | men         | 95033 |
| zhou-san     | women       | 95033 |
| fen-san      | women       | 95033 |
| chen-san     | women       | 95033 |
| chu-san      | women       | 95033 |
+--------------+-------------+-------+
8 rows in set (0.00 sec)

3 查询教师表：查询教师所有不重复的单位（depart列去重)

select distinct depart from teacher;

# distinct 排除重复

查询结果
mysql> select distinct depart from teacher;
+-------------+
| depart      |
+-------------+
| basketball3 |
| basketball2 |
| basketball4 |
| basketball1 |
| basketball5 |
| basketball7 |
| basketball8 |
| basketball9 |
+-------------+
8 rows in set (0.00 sec)

4 查询score表中成绩在80-90之间的所有记录


（1）select * from score where degree between 80 and 90;

查询区间 between 。。。 and 。。。

查询结果
mysql> select * from score where degree between 80 and 90;
+----------------+---------------+--------+
| student_number | course_number | degree |
+----------------+---------------+--------+
| 101            | 303-3         |     88 |
| 102            | 303-2         |     87 |
| 102            | 303-4         |     89 |
| 103            | 303-3         |     87 |
| 104            | 303-3         |     86 |
| 106            | 303-1         |     82 |
+----------------+---------------+--------+
6 rows in set (0.00 sec)

# 另一种方式：直接运算符比较
（2）select * from score where degree > 80 and degree < 90;

5 查询score表中成绩为 86、87、88的所有记录

表示或者的关系的 in

select * from score where degree in(86,87,88);

查询结果
mysql> select * from score where degree in(86,87,88);
+----------------+---------------+--------+
| student_number | course_number | degree |
+----------------+---------------+--------+
| 101            | 303-3         |     88 |
| 102            | 303-2         |     87 |
| 103            | 303-3         |     87 |
| 104            | 303-3         |     86 |
+----------------+---------------+--------+
4 rows in set (0.00 sec)

6 查询student表中为'95034'班或者 student_sex为'women'的所有记录

or表示或者

select * from student where class='95034' or student_sex='women';

查询结果
mysql> select * from student where class='95034' or student_sex='women';
+----------------+--------------+-------------+---------------------+-------+
| student_number | student_name | student_sex | student_birthday    | class |
+----------------+--------------+-------------+---------------------+-------+
| 101            | zhao-san     | women       | 1992-01-01 00:00:00 | 95034 |
| 103            | sun-san      | women       | 1994-01-01 00:00:00 | 95033 |
| 105            | zhou-san     | women       | 1991-04-01 00:00:00 | 95033 |
| 106            | fen-san      | women       | 1991-11-01 00:00:00 | 95033 |
| 107            | chen-san     | women       | 1994-06-02 00:00:00 | 95033 |
| 108            | chu-san      | women       | 1951-06-01 00:00:00 | 95033 |
+----------------+--------------+-------------+---------------------+-------+
6 rows in set (0.00 sec)

7 以class字段为标准按照降序查询student记录(默认升序)

降序查询
select * from student  order by class desc;

mysql> select * from student  order by class desc;
+----------------+--------------+-------------+---------------------+-------+
| student_number | student_name | student_sex | student_birthday    | class |
+----------------+--------------+-------------+---------------------+-------+
| 101            | zhao-san     | women       | 1992-01-01 00:00:00 | 95034 |
| 102            | wan-san      | men         | 1993-01-01 00:00:00 | 95033 |
| 103            | sun-san      | women       | 1994-01-01 00:00:00 | 95033 |
| 104            | li-san       | men         | 1996-01-01 00:00:00 | 95033 |
| 105            | zhou-san     | women       | 1991-04-01 00:00:00 | 95033 |
+----------------+--------------+-------------+---------------------+-------+
8 rows in set (0.00 sec)


升序查询
# 两种形式
select * from student order by class;
select * from student order by class asc;

8 以course_number升序，degree降序查询score表中所有记录


select * from score order by course_number asc,degree desc;

查询结果
mysql> select * from score order by course_number asc,degree desc;
+----------------+---------------+--------+
| student_number | course_number | degree |
+----------------+---------------+--------+
| 105            | 303-1         |    100 |
| 106            | 303-1         |     82 |
| 107            | 303-2         |     92 |
| 102            | 303-2         |     87 |
| 101            | 303-3         |     88 |
| 103            | 303-3         |     87 |
| 104            | 303-3         |     86 |
| 102            | 303-4         |     89 |
| 107            | 303-5         |     99 |
| 101            | 303-6         |     95 |
+----------------+---------------+--------+
10 rows in set (0.00 sec)

9 查询student表中 '95034'班的人数


统计 count
select count(*) from student where class='95034';

查询结果
mysql> select count(*) from student where class='95034';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

10 查询score表中的最高分的学生学号及课程号(子查询或者排序)


最大值  max

select student_number,course_number from score where degree=(select max(degree) from score);

mysql> select student_number,course_number from score where degree=(select max(degree) from score);
+----------------+---------------+
| student_number | course_number |
+----------------+---------------+
| 105            | 303-1         |
+----------------+---------------+
1 row in set (0.00 sec)

MySQL 索引及查询优化总结-2018-03-20
MySQL 索引及查询优化总结文章《MySQL查询分析》讲述了使用MySQL慢查询和explain命令来定位my...
MySQL慢查询日志总结
MySQL慢查询日志总结慢查询日志概念 MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MyS...
MySQL--基础二
本节总结MySQL的筛选条件，聚合与分组，子查询，连接查询。 MySQL的筛选条件 MySQL中的比较运算符：比...
MySQL查询性能优化总结
MySQL查询性能优化总结查询执行路径客户端发送一条查询给MySQL服务器服务器先检查缓存，如果命中了缓存，...
MySql查询-总结
查询的完整格式完整的select语句执行顺序为：from 表名where ....group by ...se...
Mysql查询总结
第一部分：准备数据 1 创建学生表 2 插入数据 3 创建教师表 4 插入数据 5 创建课程表 6 插入数据 7 ...
17.MySQL优化
《高性能MySQL》——这本书都有的 “字段”优化总结 “索引”优化总结索引的优化 “查询SQL”优化总结 “引...
深入学习MySQL优化
MySQL高性能优化实战总结 MySQL 的查询过程如下图，很多的查询优化工作实际上就是遵循一些原则。优化的哲学...
MySQL索引知多少
mysql索引总结关于mysql的索引，查询优化，SQL技巧等 1 索引类型 B-Tree索引 Hash索引 ...
CentOS下mysql数据库常用命
mysql数据库使用总结本文主要记录一些mysql日常使用的命令，供以后查询。 1.更改root密码 mysql...