Mysql数据库——高阶语句(上)

x33g5p2x  于2021-10-28 转载在 Mysql  
字(12.3k)|赞(0)|评价(0)|浏览(256)

前言

  • 准备模板表
mysql -uroot -p123123

create database puxin;
#创建库

create table xjj (id int(10) primary key not null auto_increment,name varchar(20),score decimal(5,2),address varchar(40),hobby varchar(20));
#创建表

insert into xjj values(1,'liuyi',80,'beijing',2);
insert into xjj values(2,'wangwu',90,'shengzheng',2);
insert into xjj values(3,'lisi',60,'shanghai',4);
insert into xjj values(4,'tianqi',99,'hangzhou',5);
insert into xjj values(5,'jiaoshou',98,'laowo',3);
insert into xjj values(6,'hanmeimei',10,'nanjing',3);
insert into xjj values(7,'lilei',11,'nanjing',5);
insert into xjj values(8,'caicai',16,'nanjing',5);
#插入表

mysql> select * from xjj;
+----+-----------+-------+------------+-------+
| id | name      | score | address    | hobby |
+----+-----------+-------+------------+-------+
|  1 | liuyi     | 80.00 | beijing    | 2     |
|  2 | wangwu    | 90.00 | shengzheng | 2     |
|  3 | lisi      | 60.00 | shanghai   | 4     |
|  4 | tianqi    | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou  | 98.00 | laowo      | 3     |
|  6 | hanmeimei | 10.00 | nanjing    | 3     |
|  7 | lilei     | 11.00 | nanjing    | 5     |
|  8 | caicai    | 16.00 | nanjing    | 5     |
+----+-----------+-------+------------+-------+
8 rows in set (0.00 sec)

常用查询

  • 增、删、改、查
  • 对MySQL数据库的查询,除了基本的查询外,有时候需要对查询的结果集进行处理,例如只取 10 条数据、对查询结果进行排序或分组等等

一、按关键字排序

  • 使用select语句可以将需要的数据从mysql数据库中查询出来,如果对查询的结果进行排序操作,可以使用order by 语句完成排序,并且最终将排序后的结果返回给客户
  • 类比于windows任务管理器
select 字段 from 表名 order by 字段 ASC|DESC

ASC|DESC
ASC是按照升序进行排名的,默认的排序方式,可省略

DESC是按照降序的方式进行排序的
*
order by 也可以通过 where 子语句对查询结果进行进一步的过滤
*
可进行多字段的排序

1.单字段排序

#按分数排序,默认不指定升序排列 asc
mysql> select id,name,score from xjj order by score;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  6 | hanmeimei | 10.00 |
|  7 | lilei     | 11.00 |
|  8 | caicai    | 16.00 |
|  3 | lisi      | 60.00 |
|  1 | liuyi     | 80.00 |
|  2 | wangwu    | 90.00 |
|  5 | jiaoshou  | 98.00 |
|  4 | tianqi    | 99.00 |
+----+-----------+-------+
8 rows in set (0.00 sec)

#按分数排序,使用desc降序
mysql> select id,name,score from xjj order by score desc;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  4 | tianqi    | 99.00 |
|  5 | jiaoshou  | 98.00 |
|  2 | wangwu    | 90.00 |
|  1 | liuyi     | 80.00 |
|  3 | lisi      | 60.00 |
|  8 | caicai    | 16.00 |
|  7 | lilei     | 11.00 |
|  6 | hanmeimei | 10.00 |
+----+-----------+-------+
8 rows in set (0.00 sec)

2.条件查询

mysql> select name,score from xjj where address='hangzhou' order by score desc;
+--------+-------+
| name   | score |
+--------+-------+
| tianqi | 99.00 |
+--------+-------+
1 row in set (0.00 sec)

3.多字段排序

#原则:order by之后的参数,使用 “,”分割,优先级是按先后顺序而定

mysql> select id,name,hobby from xjj order by hobby desc,id asc;
+----+-----------+-------+
| id | name      | hobby |
+----+-----------+-------+
|  4 | tianqi    | 5     |
|  7 | lilei     | 5     |
|  8 | caicai    | 5     |
|  3 | lisi      | 4     |
|  5 | jiaoshou  | 3     |
|  6 | hanmeimei | 3     |
|  1 | liuyi     | 2     |
|  2 | wangwu    | 2     |
+----+-----------+-------+
8 rows in set (0.00 sec)

小结:order by之后的第一个参数只有在出现相同的数值,第二个字段才有意思

4.区间判断 AND/OR ——且/或

mysql> select * from xjj where score > 70 and score <=90;
+----+--------+-------+------------+-------+
| id | name   | score | address    | hobby |
+----+--------+-------+------------+-------+
|  1 | liuyi  | 80.00 | beijing    | 2     |
|  2 | wangwu | 90.00 | shengzheng | 2     |
+----+--------+-------+------------+-------+

mysql> select * from xjj where score > 70 or score <=90;
+----+-----------+-------+------------+-------+
| id | name      | score | address    | hobby |
+----+-----------+-------+------------+-------+
|  1 | liuyi     | 80.00 | beijing    | 2     |
|  2 | wangwu    | 90.00 | shengzheng | 2     |
|  3 | lisi      | 60.00 | shanghai   | 4     |
|  4 | tianqi    | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou  | 98.00 | laowo      | 3     |
|  6 | hanmeimei | 10.00 | nanjing    | 3     |
|  7 | lilei     | 11.00 | nanjing    | 5     |
|  8 | caicai    | 16.00 | nanjing    | 5     |
+----+-----------+-------+------------+-------+
8 rows in set (0.00 sec)

mysql> select * from xjj where score > 70 or (score <60 and score >15);
+----+----------+-------+------------+-------+
| id | name     | score | address    | hobby |
+----+----------+-------+------------+-------+
|  1 | liuyi    | 80.00 | beijing    | 2     |
|  2 | wangwu   | 90.00 | shengzheng | 2     |
|  4 | tianqi   | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou | 98.00 | laowo      | 3     |
|  8 | caicai   | 16.00 | nanjing    | 5     |
+----+----------+-------+------------+-------+
5 rows in set (0.00 sec)

5.查询不重复记录

select distinct 字段 from 表名;

distinct 必须放在最开头

distinct 只能使用需要去重的字段进行操作

distinct 去重多个字段,含义是:几个字段同时重复时才能被过滤

mysql> select distinct hobby from xjj;
+-------+
| hobby |
+-------+
| 2     |
| 4     |
| 5     |
| 3     |
+-------+
4 rows in set (0.00 sec)

mysql> select name,hobby from xjj where hobby in (select distinct hobby from xjj);
+-----------+-------+
| name      | hobby |
+-----------+-------+
| liuyi     | 2     |
| wangwu    | 2     |
| lisi      | 4     |
| tianqi    | 5     |
| jiaoshou  | 3     |
| hanmeimei | 3     |
| lilei     | 5     |
| caicai    | 5     |
+-----------+-------+
8 rows in set (0.00 sec)

二、对结果进行分组

  • 通过SQL查询出来的结果,还可以对其进行分组,使用group by 语句来实现
  • group by 通常都是结合聚合函数一起使用的,常用的聚合函数包括:计数(count)、求和(sum)、请平均数(avg)、最大值(max)、最小值(min),group by 分组的时候可以按一个或者多个字段对结果进行分组处理
select 字段,聚合函数 from 表名 (where 字段名(匹配) 数值) group by 字段名;
#对xjj进行分组,筛选范围/条件是score大于等于45的 'name',score相同的会默认分在一个组
mysql> select count(name),score from xjj where score>=45 group by score;
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           1 | 60.00 |
|           1 | 80.00 |
|           1 | 90.00 |
|           1 | 98.00 |
|           1 | 99.00 |
+-------------+-------+
5 rows in set (0.00 sec)

#对xjj表中所有address相同的内容进行分组
mysql> select count(id),address from xjj group by address;
+-----------+------------+
| count(id) | address    |
+-----------+------------+
|         1 | beijing    |
|         1 | hangzhou   |
|         1 | laowo      |
|         3 | nanjing    |
|         1 | shanghai   |
|         1 | shengzheng |
+-----------+------------+
6 rows in set (0.00 sec)

#基于上一条操作,结合order by把统计的id数量进行按降序序排列
mysql> select count(id), hobby from xjj group by hobby order by count(id)
+-----------+-------+
| count(id) | hobby |
+-----------+-------+
|         3 | 5     |
|         2 | 3     |
|         2 | 2     |
|         1 | 4     |
+-----------+-------+
4 rows in set (0.00 sec)

#结合where语句,筛选分数大于等于70的分组,计算学生个数按降序排列
mysql> select count(name),score,hobby from xjj where score>=70 group by hobby order by count(name) desc;
+-------------+-------+-------+
| count(name) | score | hobby |
+-------------+-------+-------+
|           2 | 80.00 | 2     |
|           1 | 99.00 | 5     |
|           1 | 98.00 | 3     |
+-------------+-------+-------+
3 rows in set (0.00 sec)

三、限制结果条目(limit)

  • 在使用mysql select 语句进行查询操作时,结果集返回的是所有匹配的记录,有时候仅需返回第一行或前几行,这时候就需用到limit 子句
select 字段 from 表名 limit [offset,] number
  • LIMIT 的第一个参数是位置偏移量(可选参数),是设置 MySQL 从哪一行开始显示
  • 如果不设定第一个参数,将会从表中的第一条记录开始显示
  • 需要注意的是,第一条记录的 位置偏移量是 0,第二条是 1,以此类推,第二个参数是设置返回记录行的最大数目
#查询所有信息显示前4行记录
mysql> select * from xjj limit 3;
+----+--------+-------+------------+-------+
| id | name   | score | address    | hobby |
+----+--------+-------+------------+-------+
|  1 | liuyi  | 80.00 | beijing    | 2     |
|  2 | wangwu | 90.00 | shengzheng | 2     |
|  3 | lisi   | 60.00 | shanghai   | 4     |
+----+--------+-------+------------+-------+
3 rows in set (0.00 sec)

#从第4行开始,往后显示3行内容
mysql> select * from xjj limit 3,3;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  4 | tianqi    | 99.00 | hangzhou | 5     |
|  5 | jiaoshou  | 98.00 | laowo    | 3     |
|  6 | hanmeimei | 10.00 | nanjing  | 3     |
+----+-----------+-------+----------+-------+
3 rows in set (0.00 sec)

#结合order by语句,按id的大小升序排列显示前三行
mysql> select id,name from xjj order by id limit 3;
+----+--------+
| id | name   |
+----+--------+
|  1 | liuyi  |
|  2 | wangwu |
|  3 | lisi   |
+----+--------+
3 rows in set (0.00 sec)

#输出最后三行
mysql> select id,name from xjj order by id desc limit 3;
+----+-----------+
| id | name      |
+----+-----------+
|  8 | caicai    |
|  7 | lilei     |
|  6 | hanmeimei |
+----+-----------+
3 rows in set (0.00 sec)

四、设置别名(alias——>as)

  • 在mysql查询时,当表的名字比较长或者表内某些字段比较长时,为了方便书写或者多次使用相同的表时,可以给字段列或表设置别名
  • 方便操作,增强可读性
列的别名
select 字段 as 字段别名 表名

表的别名
select 字段 from 表名 as 别名 

as 可以省略
#列别名设置示例:
mysql> select name as 姓名,score as 成绩 from xjj;
+-----------+--------+
| 姓名      | 成绩   |
+-----------+--------+
| liuyi     |  80.00 |
| wangwu    |  90.00 |
| lisi      |  60.00 |
| tianqi    |  99.00 |
| jiaoshou  |  98.00 |
| hanmeimei |  10.00 |
| lilei     |  11.00 |
| caicai    |  16.00 |
+-----------+--------+
8 rows in set (0.00 sec)

#不用as也可以,一样显示
mysql> select count(*) number from xjj;
+--------+
| number |
+--------+
|      8 |
+--------+
1 row in set (0.00 sec)

使用场景

对复杂的表进行查询的时候,别名可以缩短查询语句

多表相连查询的时候(通俗易懂、简短SQL语句)

此外,AS 还可以作为连接语句的操作符
创建yyy表,将xjj表的查询记录全部插入yyy表
mysql> create table yyy as select * from xjj;

#此处AS起到的作用:
1、创建了一个新表yyy 并定义表结构,插入表数据(与xjj表相同)
2、但是'约束'没有被完全'复制'过来 
#但是如果原表设置了主键,那么附表的:default字段会默认设置一个0
相似:
#克隆、复制表结构
create table yyy (select * from xjj);

#也可以加入where 语句判断
mysql> create table aaa as select * from xjj where score >=70;

在为表设置别名时,要保证别名不能与数据库中的其他表的名称冲突
列的别名是在结果中有显示的,而表的别名在结果中没有显示,只在执行查询时使用

五、通配符

主要用于替换字符串中的部分字符,通过部分字符的匹配将相关结果查询出来
*
通配符都是跟like(模糊查询)一起使用,并协同where自居共同来完成查询任务
*
常用的: %:百分号表示零个、一个或多个字符 _ :下划线表示单个字符

#查询名字是c开头的记录
mysql> select id,name from xjj where name like 'c%';
+----+--------+
| id | name   |
+----+--------+
|  8 | caicai |
+----+--------+
1 row in set (0.00 sec)

#查询名字里是c和i中间有一个字符的记录
mysql> select id,name from xjj where name like 'c_ic_i';
+----+--------+
| id | name   |
+----+--------+
|  8 | caicai |
+----+--------+
1 row in set (0.00 sec)

#查询名字中间有g的记录
mysql> select id,name from xjj where name like '%g%';
+----+--------+
| id | name   |
+----+--------+
|  2 | wangwu |
+----+--------+
1 row in set (0.00 sec)

#通配符“%”和“_”不仅可以单独使用,也可以组合使用
mysql> select id,name from xjj where name like 'w%_';
+----+--------+
| id | name   |
+----+--------+
|  2 | wangwu |
+----+--------+
1 row in set (0.00 sec)

六、子查询

  • 子查询也被称作内查询或嵌套查询,是指一个查询语句里面嵌套着另一个查询语句
  • 子查询语句是先于主查询语句被执行的,其结果作为外层的条件返回给主查询进行下一步的查询过滤
  • PS: 子语句可以与主语句所查询的表相同,也可以是不同表
mysql> select name,score from xjj where id in (select id from xjj where score > 70);
+----------+-------+
| name     | score |
+----------+-------+
| liuyi    | 80.00 |
| wangwu   | 90.00 |
| tianqi   | 99.00 |
| jiaoshou | 98.00 |
+----------+-------+
4 rows in set (0.00 sec)
#主语句:select name,score from info where id
子语句(集合): select id from info where score >80
PS:子语句中的sql语句是为了,最后过滤出一个结果集,用于主语句的判断条件
in: 将主表和子表关联/连接的语法
  • 子查询不仅可以在select 中使用,在insert、update、delete中同样可以使用
  • 支持多层嵌套
  • in语句是用来判断某个值是否在给定的集合内(结果集),in往往和select搭配使用
  • 可以使用 not in 来进行对结果集取反
mysql> create table ttt(id int(4));
mysql> insert into ttt values(1),(2),(3);

#多表查询
mysql> select id,name,score from xjj where id in (select * from ttt); 
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | liuyi  | 80.00 |
|  2 | wangwu | 90.00 |
|  3 | lisi   | 60.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

1.子查询—exists

exists 这个关键字在子查询时,主要用于判断exists之后的条件是否成立,如果成立,则正常执行主语句的匹配,如不成立,则不会执行主语句查询,
如子查询结果集不成立的话,输出为null

count为计数,sum为求和,使用sum求和结合exists,如子查询结果集不成立的话,输出为null

#查询如果存在分数等于80的记录则计算xjj的字段数
mysql> select count(*) from xjj where exists(select id from xjj where score=80);
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

#查询如果存在分数小于50的记录则计算xjj的字段数,xjj表没有小于50的,所以返回0
mysql> select count(*) from xjj where exists(select id xjj where score<60);
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

2.子查询—as

例:
select name,hobby from xjj;
以上命令可以查询一张表的name和hobby信息

将结果作为“表”进行查询的时候,我们也需要用到别名
比如我们输入:select hobby from (select name,hobby from xjj);
会报错,因为 select name,hobby from xjj 得到的是一个结果集,而不是表,mysql 是不能识别的
所以我们需要对结果集设置一个别名,这样mysql就能将结果集视为一张表

mysql> select a.id from (select id,name from xjj) a;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
+----+
8 rows in set (0.00 sec)
相当于
select xjj.id,name from xjj;
select 表.字段,字段 from 表;

3.视图

  • 视图是从一个或多个表中导出来的表,是一种虚拟存在的表
  • 数据库中只存放了视图的定义,而没有存放视图中的数据,这些数据存放在原来的表中
  • 使用视图查询数据时,数据库系统会从原来的表中取出对应的数据
  • 视图中的数据依赖于原来表中的数据,一旦表中数据发生改变,显示在视图中的数据也会发生改变
  • 数据库中的虚拟表,这张虚拟表中不包含真实数据,只是做了映射
    镜花水月/倒影,动态保存结果集(数据)
    作用:
    1.使操作简单化,可以对经常使用的查询定义一个视图,使用户不必为同样的查询操作指定条件

2.增加数据的安全性,通过视图,用户只能查询和修改指定的数据

3.提高表的逻辑独立性,视图可以屏蔽原有表结构变化带来的影响

  • 使用视图的大部分情况时为了保障数据安全性,提高查询效率
  • 根据筛选条件创建了一张虚拟表,当修改表中数据时,原表中的数据不再满足筛选条件时,视图中的表将不会再显示此数据
需求:满足80分的学生展示在视图中
PS:这个结果会动态变化,同时可以给不同的人群(例如权限范围)展示不同的视图
#创建视图
create view v_score as select * from info where score>=80;

show table status\G

#查看视图
mysql> select * from v_score;
+----+----------+-------+------------+-------+
| id | name     | score | address    | hobby |
+----+----------+-------+------------+-------+
|  1 | liuyi    | 80.00 | beijing    | 2     |
|  2 | wangwu   | 90.00 | shengzheng | 2     |
|  4 | tianqi   | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou | 98.00 | laowo      | 3     |
+----+----------+-------+------------+-------+
4 rows in set (0.00 sec)

#修改原表数据
update info set score='60' where name='wangwu';

#查看视图
mysql> select * from v_score;
+----+----------+-------+----------+-------+
| id | name     | score | address  | hobby |
+----+----------+-------+----------+-------+
|  1 | liuyi    | 80.00 | beijing  | 2     |
|  4 | tianqi   | 99.00 | hangzhou | 5     |
|  5 | jiaoshou | 98.00 | laowo    | 3     |
+----+----------+-------+----------+-------+
3 rows in set (0.00 sec)

4.NULL值

null值与空值的区别(空气与真空)
null,不占空间
空字符和0,占用空间
is null无法判断空值
空值使用"=“或者”<>"来处理(!=)
count()计算时,NULL会忽略,空值会加入计算

插入一条记录,分数字段输入null,显示出来就是null

#验证:
 alter table xjj add column addr varchar(50);

update xjj set addr='nj' where score >=70;

#统计数量:检测null是否会加入统计中
select count(addr) from xjj;

#将info表中其中一条数据修改为空值''
update xjj set addr='' where name='wangwu';

#统计数量,检测空值是不会被添加到统计中
mysql> select count(addr) from xjj;
+-------------+
| count(addr) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

#查询null值
mysql> select * from xjj where addr is null;
+----+-----------+-------+----------+-------+------+
| id | name      | score | address  | hobby | addr |
+----+-----------+-------+----------+-------+------+
|  1 | liuyi     | 80.00 | beijing  | 2     | NULL |
|  3 | lisi      | 60.00 | shanghai | 4     | NULL |
|  4 | tianqi    | 99.00 | hangzhou | 5     | NULL |
|  5 | jiaoshou  | 98.00 | laowo    | 3     | NULL |
|  6 | hanmeimei | 10.00 | nanjing  | 3     | NULL |
|  7 | lilei     | 11.00 | nanjing  | 5     | NULL |
|  8 | caicai    | 16.00 | nanjing  | 5     | NULL |
+----+-----------+-------+----------+-------+------+
#空值数据: select count(*) from YourTable where Your Column Name is null

#查询不为空的值
mysql> select * from xjj where addr is not null;
+----+--------+-------+------------+-------+------+
| id | name   | score | address    | hobby | addr |
+----+--------+-------+------------+-------+------+
|  2 | wangwu | 60.00 | shengzheng | 2     |      |
+----+--------+-------+------------+-------+------+
#非空值数据: select count(*) from YourTable where YourColumnName is not null

相关文章