前言

准备模板表

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

Mysql数据库——高阶语句（上）

前言