Mysql查询

1、判断是否为空
is null:当列的值为NULL时，此条件为真。
is not null:当列的值不为NULL时，此条件为真。

create table t1(id int, name varchar(10));
insert into t1(id) values(1),(2);
insert into t1 values(3,'sunwukong');

select * from t1 where name is null;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
|    2 | NULL |
+------+------+
2 rows in set (0.01 sec)

select * from t1 where name is not null;
+------+-----------+
| id   | name      |
+------+-----------+
|    3 | sunwukong |
+------+-----------+
1 row in set (0.00 sec)

2、逻辑判断
and：与，需要同时满足where子句中的条件。
or：或，只需要匹配多个where子句中的一个条件。
not：非，一般与in、between and、exists一起使用，表示取反。

select * from students where 16<=age and age<=21;
+-----------+---------+------+------------+
| number    | name    | age  | birth      |
+-----------+---------+------+------------+
| 202404001 | Qiye    |   16 | 2002-01-01 |
| 202404002 | JackLee |   17 | 2001-01-02 |
| 202404003 | Julia   |   18 | 2000-01-03 |
| 202404004 | Stefer  |   19 | 2001-01-04 |
| 202404005 | Steven  |   20 | 2000-01-05 |
| 202404006 | Mark    |   21 | 1999-01-06 |
| 202404012 | wanger  |   20 | 1991-01-10 |
| 202404013 | mazi    |   21 | 1993-01-13 |
| 202404017 | laowang |   18 | 1997-08-10 |
+-----------+---------+------+------------+
9 rows in set (0.01 sec)

select * from students where age<=16 or age>=22;
+-----------+----------+------+------------+
| number    | name     | age  | birth      |
+-----------+----------+------+------------+
| 202404001 | Qiye     |   16 | 2002-01-01 |
| 202404007 | Stark    |   22 | 1999-01-07 |
| 202404008 | Tonny    |   23 | 1999-01-08 |
| 202404009 | Jarvis   |   24 | 1999-01-09 |
| 202404010 | ZhangSan |   25 | 1999-01-10 |
| 202404011 | lisi     |   23 | 1990-04-10 |
| 202404014 | xiaoxing |   80 | 1995-01-22 |
| 202404015 | hundan   |   36 | 1998-05-10 |
| 202404016 | xiaowang |   30 | 1999-07-10 |
+-----------+----------+------+------------+
9 rows in set (0.01 sec)

select * from students where not age=16;
+-----------+----------+------+------------+
| number    | name     | age  | birth      |
+-----------+----------+------+------------+
| 202404002 | JackLee  |   17 | 2001-01-02 |
| 202404003 | Julia    |   18 | 2000-01-03 |
| 202404004 | Stefer   |   19 | 2001-01-04 |
| 202404005 | Steven   |   20 | 2000-01-05 |
| 202404006 | Mark     |   21 | 1999-01-06 |
| 202404007 | Stark    |   22 | 1999-01-07 |
| 202404008 | Tonny    |   23 | 1999-01-08 |
| 202404009 | Jarvis   |   24 | 1999-01-09 |
| 202404010 | ZhangSan |   25 | 1999-01-10 |
| 202404011 | lisi     |   23 | 1990-04-10 |
| 202404012 | wanger   |   20 | 1991-01-10 |
| 202404013 | mazi     |   21 | 1993-01-13 |
| 202404014 | xiaoxing |   80 | 1995-01-22 |
| 202404015 | hundan   |   36 | 1998-05-10 |
| 202404016 | xiaowang |   30 | 1999-07-10 |
| 202404017 | laowang  |   18 | 1997-08-10 |
+-----------+----------+------+------------+
16 rows in set (0.01 sec)

3、排序
order by：用于对结果集进行排序。

# 正序
select * from students order by age;
+-----------+----------+------+------------+
| number    | name     | age  | birth      |
+-----------+----------+------+------------+
| 202404001 | Qiye     |   16 | 2002-01-01 |
| 202404002 | JackLee  |   17 | 2001-01-02 |
| 202404003 | Julia    |   18 | 2000-01-03 |
| 202404017 | laowang  |   18 | 1997-08-10 |
| 202404004 | Stefer   |   19 | 2001-01-04 |
| 202404005 | Steven   |   20 | 2000-01-05 |
| 202404012 | wanger   |   20 | 1991-01-10 |
| 202404006 | Mark     |   21 | 1999-01-06 |
| 202404013 | mazi     |   21 | 1993-01-13 |
| 202404007 | Stark    |   22 | 1999-01-07 |
| 202404008 | Tonny    |   23 | 1999-01-08 |
| 202404011 | lisi     |   23 | 1990-04-10 |
| 202404009 | Jarvis   |   24 | 1999-01-09 |
| 202404010 | ZhangSan |   25 | 1999-01-10 |
| 202404016 | xiaowang |   30 | 1999-07-10 |
| 202404015 | hundan   |   36 | 1998-05-10 |
| 202404014 | xiaoxing |   80 | 1995-01-22 |
+-----------+----------+------+------------+
17 rows in set (0.02 sec)

# 倒序
select * from students order by age desc;
+-----------+----------+------+------------+
| number    | name     | age  | birth      |
+-----------+----------+------+------------+
| 202404014 | xiaoxing |   80 | 1995-01-22 |
| 202404015 | hundan   |   36 | 1998-05-10 |
| 202404016 | xiaowang |   30 | 1999-07-10 |
| 202404010 | ZhangSan |   25 | 1999-01-10 |
| 202404009 | Jarvis   |   24 | 1999-01-09 |
| 202404008 | Tonny    |   23 | 1999-01-08 |
| 202404011 | lisi     |   23 | 1990-04-10 |
| 202404007 | Stark    |   22 | 1999-01-07 |
| 202404006 | Mark     |   21 | 1999-01-06 |
| 202404013 | mazi     |   21 | 1993-01-13 |
| 202404005 | Steven   |   20 | 2000-01-05 |
| 202404012 | wanger   |   20 | 1991-01-10 |
| 202404004 | Stefer   |   19 | 2001-01-04 |
| 202404003 | Julia    |   18 | 2000-01-03 |
| 202404017 | laowang  |   18 | 1997-08-10 |
| 202404002 | JackLee  |   17 | 2001-01-02 |
| 202404001 | Qiye     |   16 | 2002-01-01 |
+-----------+----------+------+------------+
17 rows in set (0.00 sec)

4、限制
limit：限制select查询返回的行数

# 展示5条数据
select * from students limit 5;
+-----------+---------+------+------------+
| number    | name    | age  | birth      |
+-----------+---------+------+------------+
| 202404001 | Qiye    |   16 | 2002-01-01 |
| 202404002 | JackLee |   17 | 2001-01-02 |
| 202404003 | Julia   |   18 | 2000-01-03 |
| 202404004 | Stefer  |   19 | 2001-01-04 |
| 202404005 | Steven  |   20 | 2000-01-05 |
+-----------+---------+------+------------+
5 rows in set (0.01 sec)

# 从第4行数据开始输出3条
select * from students limit 3,3;
+-----------+--------+------+------------+
| number    | name   | age  | birth      |
+-----------+--------+------+------------+
| 202404004 | Stefer |   19 | 2001-01-04 |
| 202404005 | Steven |   20 | 2000-01-05 |
| 202404006 | Mark   |   21 | 1999-01-06 |
+-----------+--------+------+------------+
3 rows in set (0.00 sec)

5、去重
distinct：过滤重复数据

select distinct subject_number from grades;
+----------------+
| subject_number |
+----------------+
| 0001           |
| 0002           |
+----------------+
2 rows in set (0.00 sec)

6、模糊查询
like：根据制定的模式过滤数据

# 查询name以xi开头的数据，%任意多个
select * from students where name like 'xi%';
+-----------+----------+------+------------+
| number    | name     | age  | birth      |
+-----------+----------+------+------------+
| 202404014 | xiaoxing |   80 | 1995-01-22 |
| 202404016 | xiaowang |   30 | 1999-07-10 |
+-----------+----------+------+------------+
2 rows in set (0.02 sec)

# _匹配单个字符，用于匹配任意单个字符
select * from students where name like 'li_';
select * from students where name like 'li__';
+-----------+------+------+------------+
| number    | name | age  | birth      |
+-----------+------+------+------------+
| 202404011 | lisi |   23 | 1990-04-10 |
+-----------+------+------+------------+
1 row in set (0.00 sec)

7、范围查询
betweent：用于选择介于两个值之间的数据，常与and连起来使用
in：返回在指定范围内的数据

# age 在（1,16,17,22）这里面的就全部展示
select * from students where age in(1,16,17,22);
+-----------+---------+------+------------+
| number    | name    | age  | birth      |
+-----------+---------+------+------------+
| 202404001 | Qiye    |   16 | 2002-01-01 |
| 202404002 | JackLee |   17 | 2001-01-02 |
| 202404007 | Stark   |   22 | 1999-01-07 |
+-----------+---------+------+------------+
3 rows in set (0.01 sec)

# 一个区间内所有的数据，16到22之间所有的数据
select * from students where age between 16 and 22;
+-----------+---------+------+------------+
| number    | name    | age  | birth      |
+-----------+---------+------+------------+
| 202404001 | Qiye    |   16 | 2002-01-01 |
| 202404002 | JackLee |   17 | 2001-01-02 |
| 202404003 | Julia   |   18 | 2000-01-03 |
| 202404004 | Stefer  |   19 | 2001-01-04 |
| 202404005 | Steven  |   20 | 2000-01-05 |
| 202404006 | Mark    |   21 | 1999-01-06 |
| 202404007 | Stark   |   22 | 1999-01-07 |
| 202404012 | wanger  |   20 | 1991-01-10 |
| 202404013 | mazi    |   21 | 1993-01-13 |
| 202404017 | laowang |   18 | 1997-08-10 |
+-----------+---------+------+------------+
10 rows in set (0.00 sec)

8、聚合函数
count(*)：统计所有的记录，包括null
count(字段)：统计该字段的所有信息
sum(字段)：统计表中该字段的和
avg(字段)：统计表中该字段的平均值

# 统计该表中总共有多少行数据
select count(*) from students;
+----------+
| count(*) |
+----------+
|       17 |
+----------+
1 row in set (0.01 sec)

# 统计该表中有多少个name
select count(name) from students;
+-------------+
| count(name) |
+-------------+
|          17 |
+-------------+
1 row in set (0.00 sec)

#  统计该表中所有age的和
select sum(age) from students;
+----------+
| sum(age) |
+----------+
|      433 |
+----------+
1 row in set (0.01 sec)

# 统计该表中所有age的平均数
select  avg(age) from students;
+----------+
| avg(age) |
+----------+
|  25.4706 |
+----------+
1 row in set (0.01 sec)

9、分组
group by 列名 having 条件表达式
group by：可用于单个字段分组也可用于多个字段分组。
having 条件表达式：用于过滤分组后的数据

# 通过subject_number,grade进行分组，然后以成绩大于80（包含80）做和筛选
select subject_number,grade,count(*) from grades group by subject_number,grade having grade>=80;
+----------------+-------+----------+
| subject_number | grade | count(*) |
+----------------+-------+----------+
| 0001           |    81 |        1 |
| 0001           |    82 |        1 |
| 0001           |    83 |        1 |
| 0001           |    84 |        1 |
| 0001           |    85 |        1 |
| 0001           |    86 |        1 |
| 0001           |    87 |        1 |
| 0001           |    88 |        1 |
| 0001           |    89 |        1 |
| 0001           |    90 |        1 |
| 0001           |    91 |        8 |
| 0002           |    80 |        1 |
+----------------+-------+----------+
12 rows in set (0.01 sec)

10、子查询

select * from (select * from students order by age limit 5) as sunwukong where age <18;
+-----------+---------+------+------------+
| number    | name    | age  | birth      |
+-----------+---------+------+------------+
| 202404001 | Qiye    |   16 | 2002-01-01 |
| 202404002 | JackLee |   17 | 2001-01-02 |
+-----------+---------+------+------------+
2 rows in set (0.01 sec)

11、作业

# 统计出0001和0002分别有多少人
select count(*) from grades where subject_number='0001';
select count(*) from grades where subject_number='0002';
# 统计出0001中age 大于18岁的人数
select count(*) from grades m,students n where m.subject_number='0001' and m.student_number=n.number and n.age>18;

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 202,607评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,047评论 2赞 379
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 149,496评论 0赞 335
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,405评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,400评论 5赞 364
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,479评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,883评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,535评论 0赞 256
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,743评论 1赞 295
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,544评论 2赞 319
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,612评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,309评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,881评论 3赞 306
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,891评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,136评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,783评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,316评论 2赞 342

Mysql查询

推荐阅读更多精彩内容