Linux_mysql查询语句

最新推荐文章于 2023-09-08 17:29:39 发布

Perhaps#

最新推荐文章于 2023-09-08 17:29:39 发布

阅读量1.4k

点赞数 1

分类专栏： Linux_mysql

本文链接：https://blog.csdn.net/weixin_45920625/article/details/107721333

版权

Linux 同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

Linux_mysql

3 篇文章 0 订阅

订阅专栏

mysql

一、按条件查询
二、按升序降序查询
三、多表去重或查全查询
四、分组查询
五、聚合函数查询
六、连表查询
七、多表查询
八、自关联查询

一、按条件查询

1.1
公式：select * from table_name where 条件

where的条件： in、not in、and、or、>、>=、<、<=
is、is not、常用语判断是否是null值
between…and 介于两者

1.2
查找教师表所有内容

mysql> select * from teachers;
+-----+--------+------+---------------------+-----------+-----------------+
| tno | tname  | tsex | tbirthday           | prof      | depart          |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系        |
| 856 | 张旭   | 男   | 1969-03-12 00:00:00 | 讲师      | 电子工程系      |
| 825 | 王萍   | 女   | 1972-05-05 00:00:00 | 助教      | 计算机系        |
| 831 | 刘冰   | 女   | 1977-08-14 00:00:00 | 助教      | 电子工程系      |
+-----+--------+------+---------------------+-----------+-----------------+
4 rows in set (0.00 sec)

1.3
查找prof=‘助教’的老师名字

mysql> select tname,prof from teachers where prof='助教';
+--------+--------+
| tname  | prof   |
+--------+--------+
| 王萍   | 助教   |
| 刘冰   | 助教   |
+--------+--------+

2 rows in set (0.00 sec)

1.4
查找分数表的所有内容

mysql> select * from scores;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
| 101 | 3-105 |     64 |
| 107 | 3-105 |     91 |
| 108 | 3-105 |     78 |
| 101 | 6-166 |     85 |
| 107 | 6-106 |     79 |
| 108 | 6-166 |     81 |
+-----+-------+--------+
12 rows in set (0.00 sec)

1.5
查找分数大于90分的班级

mysql> select sno,degree from scores
    -> where 
    -> degree>90
    -> ;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 |     92 |
| 107 |     91 |
+-----+--------+

2 rows in set (0.00 sec)

1.6
查找分数大于70并且小于90

mysql> select sno,degree from scores 
    -> where
    -> degree>70
    -> and 
    -> degree<90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 |     86 |
| 105 |     75 |
| 105 |     88 |
| 109 |     76 |
| 108 |     78 |
| 101 |     85 |
| 107 |     79 |
| 108 |     81 |
+-----+--------+
8 rows in set (0.00 sec)

二、按升序降序查询

2.1
将分数按升序排序

mysql> select degree from scores 
    -> order by
    -> degree asc
    -> ;
+--------+
| degree |
+--------+
|     64 |
|     68 |
|     75 |
|     76 |
|     78 |
|     79 |
|     81 |
|     85 |
|     86 |
|     88 |
|     91 |
|     92 |
+--------+
12 rows in set (0.00 sec)

2.2
将分数按降序排序

mysql> select degree from scores
    -> order by
    -> degree desc
    -> ;
+--------+
| degree |
+--------+
|     92 |
|     91 |
|     88 |
|     86 |
|     85 |
|     81 |
|     79 |
|     78 |
|     76 |
|     75 |
|     68 |
|     64 |
+--------+
12 rows in set (0.00 sec)

三、多表去重或查全查询

3.1
union：去重查询
union all：查询全部
distinct()：单表查询某字段下列不重复的内容

3.2
student表

mysql> select * from student;
+----+--------+------+-------+
| id | name   | age  | class |
+----+--------+------+-------+
|  1 | 小明   |   13 | 1-601 |
|  2 | 小红   |   18 | 1-601 |
|  3 | mary   |   19 | 1-601 |
|  4 | rose   |   18 | 1-601 |
|  5 | jery   |   17 | 1-602 |
+----+--------+------+-------+
5 rows in set (0.00 sec)

3.3
scores表

mysql> select * from scores;
+----+--------+-------+-------+
| id | name   | score | class |
+----+--------+-------+-------+
|  1 | 小明   |    90 | 1-601 |
|  2 | 小红   |    98 | 1-601 |
|  3 | mary   |    98 | 1-601 |
|  4 | rose   |   100 | 1-602 |
+----+--------+-------+-------+
4 rows in set (0.00 sec)

3.4
union：去重查询

mysql> select class from student
    -> union
    -> select class from scores
    -> ;
+-------+
| class |
+-------+
| 1-601 |
| 1-602 |
+-------+
2 rows in set (0.01 sec)

3.5
union all：查全

mysql> select class from student
    -> union all
    -> select class from scores
    -> ;
+-------+
| class |
+-------+
| 1-601 |
| 1-601 |
| 1-601 |
| 1-601 |
| 1-602 |
| 1-601 |
| 1-601 |
| 1-601 |
| 1-602 |
+-------+
9 rows in set (0.00 sec)

'''#查找所有内容不去重'''
mysql> select * from student
    -> union
    -> select * from scores
    -> ;
+----+--------+------+-------+
| id | name   | age  | class |
+----+--------+------+-------+
|  1 | 小明   |   13 | 1-601 |
|  2 | 小红   |   18 | 1-601 |
|  3 | mary   |   19 | 1-601 |
|  4 | rose   |   18 | 1-601 |
|  5 | jery   |   17 | 1-602 |
|  1 | 小明   |   90 | 1-601 |
|  2 | 小红   |   98 | 1-601 |
|  3 | mary   |   98 | 1-601 |
|  4 | rose   |  100 | 1-602 |
+----+--------+------+-------+
9 rows in set (0.00 sec)

3.6
distinct()

mysql> select distinct(class) from student;
+-------+
| class |
+-------+
| 1-601 |
| 1-602 |
+-------+
2 rows in set (0.00 sec)

四、分组查询

4.1
group by：分组
having：有什么…，与where的主要区别在于，
放在where条件之后，where条件后不可接聚合函数，
having后可以接聚合函数；where不可以使用别名，
having可有使用别名；使用group by having查询时，
条件在select查询中。
limit：限制查询，默认从第一组数据开始。
offset：选择开始查询的位置。
limit和offset可用于分页。

4.2
对degree进行分组，并对其计算一一对应的degree

mysql> select degree,count(degree) from scores
    -> group by
    -> degree
    -> ;
+--------+---------------+
| degree | count(degree) |
+--------+---------------+
|     64 |             1 |
|     68 |             1 |
|     75 |             1 |
|     76 |             1 |
|     78 |             1 |
|     79 |             1 |
|     81 |             1 |
|     85 |             1 |
|     86 |             1 |
|     88 |             1 |
|     91 |             2 |
|     92 |             1 |
+--------+---------------+
12 rows in set (0.00 sec)

4.3 having()

'''
#给degre取个别名为d
#对degree进行分组
#过滤出分数大于90，并计算其个数
#只有having可以使用别名，where不可以使用别名
'''
mysql> select count(degree),degree as d from scores
    -> group by degree
    -> having d>90
    -> ;
+---------------+----+
| count(degree) | d  |
+---------------+----+
|             2 | 91 |
|             1 | 92 |
+---------------+----+
2 rows in set (0.00 sec)

4.4 limit()

#查找一行的数据
mysql> select count(degree),degree as d from scores
    -> group by degree
    -> having d<90
    -> limit 1
    -> ;
+---------------+----+
| count(degree) | d  |
+---------------+----+
|             1 | 64 |
+---------------+----+
1 row in set (0.00 sec)

4.5 offset()

#从第二行开始查找一行的数据
mysql> select count(degree),degree as d from scores
    -> group by degree
    -> having d<90
    -> limit 1
    -> offset 2
    -> ;
+---------------+----+
| count(degree) | d  |
+---------------+----+
|             1 | 75 |
+---------------+----+
1 row in set (0.00 sec)

'''
#另一种写法
#limit 第二行位置，显示第二行位置开始的第一行
'''
mysql> select count(degree),degree as d from scores
    -> group by degree
    -> having d<90
    -> limit 2,1
    -> ;
+---------------+----+
| count(degree) | d  |
+---------------+----+
|             1 | 75 |
+---------------+----+
1 row in set (0.00 sec)

小伙伴们发现了吗，对分组后的数据都按默认升序排序了

mysql> select count(degree),degree as d from scores
    -> group by degree
    -> having d<90
    -> ;
+---------------+----+
| count(degree) | d  |
+---------------+----+
|             1 | 64 |
|             1 | 68 |
|             1 | 75 |
|             1 | 76 |
|             1 | 78 |
|             1 | 79 |
|             1 | 81 |
|             1 | 85 |
|             1 | 86 |
|             1 | 88 |
+---------------+----+
10 rows in set (0.00 sec)

五、聚合函数查询

一般用于查一个字段的列
常用的函数有：
count()：查询个数
sum()：求和
max()：求最大值
min()：求最小值
avg()：求平均值
group_concat()：分组拼接
5.1
查询表所有内容

mysql> select * from scores;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
| 101 | 3-105 |     64 |
| 107 | 3-105 |     91 |
| 108 | 3-105 |     78 |
| 101 | 6-166 |     85 |
| 107 | 6-106 |     79 |
| 108 | 6-166 |     81 |
| 107 | 3-105 |     91 |
+-----+-------+--------+
13 rows in set (0.00 sec)

5.2
count()：查询个数

'''查询分数等于91的个数'''
mysql> select count(degree) from scores 
    -> where
    -> degree=91
    -> ;
+---------------+
| count(degree) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

5.3
sum()：求和

'''查询分数在70和90之间的分数和'''
mysql> select sum(degree) from  scores
    -> where
    -> degree>70
    -> and 
    -> degree<90
    -> ;
+-------------+
| sum(degree) |
+-------------+
|         648 |
+-------------+
1 row in set (0.01 sec)

5.4
max()：求最大值

mysql> select max(degree) from  scores;
+-------------+
| max(degree) |
+-------------+
|          92 |
+-------------+
1 row in set (0.00 sec)

5.5
min()：求最小值

mysql> select min(degree) from  scores;
+-------------+
| min(degree) |
+-------------+
|          64 |
+-------------+
1 row in set (0.00 sec)

5.6
avg()：求平均值

mysql> select avg(degree) from  scores;
+-------------+
| avg(degree) |
+-------------+
|     81.0769 |
+-------------+
1 row in set (0.00 sec)

5.7
group_concat()

#将degree相同的cno拼接起来
#给degre取个别名为d
#对degree进行分组
#过滤出分数大于90，并计算其个数
mysql> select group_concat(cno),count(degree),degree as d from scores
    -> group by degree
    -> having d>90
    -> ;
+-------------------+---------------+----+
| group_concat(cno) | count(degree) | d  |
+-------------------+---------------+----+
| 3-105,3-105       |             2 | 91 |
| 3-105             |             1 | 92 |
+-------------------+---------------+----+
2 rows in set (0.00 sec)

六、连表查询

在同一数据库下才可行，并且一般都存在关系。

6.1
teachers表

mysql> select * from teachers;
+----+----------+-------+
| id | name     | class |
+----+----------+-------+
|  1 | missrose | 1-601 |
|  2 | misshong | 1-601 |
|  3 | missmary | 1-601 |
|  4 | missli   | 1-602 |
+----+----------+-------+
4 rows in set (0.00 sec)

6.2
students表

mysql> select * from student;
+----+--------+------+-------+
| id | name   | age  | class |
+----+--------+------+-------+
|  1 | 小明   |   13 | 1-601 |
|  2 | 小红   |   18 | 1-601 |
|  3 | mary   |   19 | 1-601 |
|  4 | rose   |   18 | 1-601 |
|  5 | jery   |   17 | 1-602 |
+----+--------+------+-------+
5 rows in set (0.00 sec)

6.3
分数表

mysql> select * from scores;
+----+--------+-------+-------+
| id | name   | score | class |
+----+--------+-------+-------+
|  1 | 小明   |    90 | 1-601 |
|  2 | 小红   |    98 | 1-601 |
|  3 | mary   |    98 | 1-601 |
|  4 | rose   |   100 | 1-602 |
+----+--------+-------+-------+
4 rows in set (0.00 sec)

6.4
查rose的老师是谁

mysql> select name from teachers
    -> where
    -> class=
    -> (
    -> select class from student where name='rose'
    -> )
    -> ;
+----------+
| name     |
+----------+
| missrose |
| misshong |
| missmary |
+----------+
3 rows in set (0.00 sec)

6.5
join…on：连表查询(内连接)

mysql>  select * from
    -> teachers join student on
    ->  student.class!=teachers.class
    -> ;
+----+----------+-------+----+--------+------+-------+
| id | name     | class | id | name   | age  | class |
+----+----------+-------+----+--------+------+-------+
|  4 | missli   | 1-602 |  1 | 小明   |   13 | 1-601 |
|  4 | missli   | 1-602 |  2 | 小红   |   18 | 1-601 |
|  4 | missli   | 1-602 |  3 | mary   |   19 | 1-601 |
|  4 | missli   | 1-602 |  4 | rose   |   18 | 1-601 |
|  1 | missrose | 1-601 |  5 | jery   |   17 | 1-602 |
|  2 | misshong | 1-601 |  5 | jery   |   17 | 1-602 |
|  3 | missmary | 1-601 |  5 | jery   |   17 | 1-602 |
+----+----------+-------+----+--------+------+-------+
7 rows in set (0.00 sec)

七、多表查询

7.1
创建人物表

create table renwu( id int primary key
                    auto_increment, 
                    name varchar(10) 
                    );

7.2
插入数据

insert into renwu 
    (id,name)
    values
    (1,'火女')
    (2,'德玛西亚' )

7.3
查看人物表内容

#人物表
mysql> select * from renwu;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 火女         |
|  2 | 德玛西亚     |
+----+--------------+
2 rows in set (0.00 sec)

7.4
创建装备表

create table zhuangbei( id int primary  key
                        auto_increment, 
                        name varchar(10) 
                        );

7.5
插入数据

insert into zhuangbei 
        (id,name) 
        values 
        (1,'反伤甲'),
        (2,'帽子');

7.6
查看表内容

#装备表
mysql> select * from zhuangbei;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 反伤甲    |
|  2 | 帽子      |
+----+-----------+

7.7
创建关系表并且创建外键

create table guanxi( 
          #创建字段和属性
          id int primary key auto_increment, 
          renwu_id int, 
          zhuangbei_id int, 
          '''constraint创建键名，
          将fordign key 内容作为外键，
          references关联人物表id'''
          constraint guanxi_di foreign key(renwu_id)
          references renwu(id), 
          '''constraint创建键名，
          将fordign key 内容作为外键，
          references关联装备表id'''
          constraint guanxi_id
          foreign key(zhuangbei_id) references     
          zhuangbei(id)
          );

7.8
插入数据

insert into guanxi
     values
    (1,1,1),
    (2,1,2),
    (3,2,1),
    (4,2,2);

7.9
查询关系表内容

#关系表
mysql> select * from guanxi;
+----+----------+--------------+
| id | renwu_id | zhuangbei_id |
+----+----------+--------------+
|  1 |        1 |            1 |
|  2 |        1 |            2 |
|  3 |        2 |            1 |
|  4 |        2 |            2 |
+----+----------+--------------+
4 rows in set (0.00 sec)

7.10
查询火女穿了什么装备

mysql> select renwu.name,zhuangbei.name
    -> from renwu,zhuangbei,guanxi
    -> where
    -> renwu.name='火女'
    -> and
    -> renwu.id=guanxi.renwu_id
    -> and 
    -> guanxi.zhuangbei_id=zhuangbei.id
    -> ;
    
#查询结果
+--------+-----------+
| name   | name      |
+--------+-----------+
| 火女   | 反伤甲    |
| 火女   | 帽子      |
+--------+-----------+

八、自关联查询

8.1
创建表

create table department(
		id int primary key auto_increment,
		name varchar(10),
		up_dep_no int
		);

8.2
插入数据

insert into department      
        values
        (1,'CEO',null),
        (2,'总经办',1),
        (3,'行政处',2),
        (4,'后勤部',2),
        (5,'销售部',1),
        (6,'财务部',1);

8.3
查询表内容

mysql> select * from department;
+----+-----------+-----------+
| id | name      | up_dep_no |
+----+-----------+-----------+
|  1 | CEO       |      NULL |
|  2 | 总经办    |         1 |
|  3 | 行政处    |         2 |
|  4 | 后勤部    |         2 |
|  5 | 销售部    |         1 |
|  6 | 财务部    |         1 |
+----+-----------+-----------+

8.4
查询行政处的上一级

mysql> select name  from department 
        where 
        id=(
        select up_dep_no from  department 
        where name='行政处'
        );

#查询结果
+-----------+
| name      |
+-----------+
| 总经办    |
+-----------+
1 row in set (0.00 sec)