2.5. DML 语句
2.5.1. INSERT 语句
功能:一次插入一行或多行数据
语法:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE #如果重复更新之
col_name=expr
[, col_name=expr] ... ]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
简化语法
INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)
举例
#向student表中添加三条数据
insert student(name,age) values('haha',20),('lala',18); #因为id和gender一个是自动生成一个是有默认值,所以可以不用指定
insert student values(null,'heihei',22,'F'); #如果不加字段名,就需要填写每个字段的信息,因为id为自动填写我们可以填写null即可
2.5.2. UPDATE 语句
语法:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
注意:一定要有限制条件,否则将修改所有行的指定字段
可利用mysql 选项避免此错误:
[root@centos8 ~]#vim /etc/my.cnf
[mysql]
safe-updates
举例
#修改表student中lala的gender值,修改为F
update student set gender='F' where id=11;
#注意:一定要有限制条件(where id =11),否则将修改所有行的指定字段
2.5.3. DELETE 语句
删除表中数据,但不会自动缩减数据文件的大小。
语法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
#可先排序再指定删除的行数
注意:一定要有限制条件,否则将清空表中的所有数据
如果想清空表,保留表结构,也可以使用下面语句,此语句会自动缩减数据文件的大小。
TRUNCATE TABLE tbl_name;
缩减表大小
OPTIMIZE TABLE tb_name
举例
#删除id为11的信息
delete from student where id=11;
2.6. DQL 语句
2.6.1. 导入数据库
方法一
方法二
2.6.2. ★★单表操作★★
语法
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]
说明
#字段显示可以使用别名:
col1 AS alias1, col2 AS alias2, ...
#WHERE子句:指明过滤条件以实现"选择"的功能:
过滤条件:布尔型表达式
算术操作符:+, -, *, /, %
比较操作符:=,<=>(相等或都为空), <>, !=(非标准SQL), >, >=, <, <=
范例查询: BETWEEN min_num AND max_num
不连续的查询: IN (element1, element2, ...)
空查询: IS NULL, IS NOT NULL
DISTINCT 去除重复行,范例:SELECT DISTINCT gender FROM students;
模糊查询: LIKE 使用 % 表示任意长度的任意字符 _ 表示任意单个字符
RLIKE:正则表达式,索引失效,不建议使用
REGEXP:匹配字符串可用正则表达式书写模式,同上
逻辑操作符:NOT,AND,OR,XOR
#GROUP BY:根据指定的条件把查询结果进行"分组"以用于做"聚合"运算
常见聚合函数: count(), sum(), max(), min(), avg(),注意:聚合函数不对null统计
HAVING: 对分组聚合运算后的结果指定过滤条件
一旦分组 group by ,select语句后只跟分组的字段,聚合函数
#ORDER BY: 根据指定的字段对查询结果进行排序
升序:ASC
降序:DESC
#LIMIT [[offset,]row_count]:对查询的结果进行输出行数数量限制,跳过offset,显示row_count行,offset默为值为0
#对查询结果中的数据请求施加"锁"
FOR UPDATE: 写锁,独占或排它锁,只有一个读和写操作
LOCK IN SHARE MODE: 读锁,共享锁,同时多个读操作
举例
#字段别名————指定列输出并且为输出字段重命名,不改变表中信息是指以指定方式显示
MariaDB [hellodb]> select *from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
MariaDB [hellodb]> select stuid 学员ID ,name 姓名,gender as 性别 from students;
+----------+---------------+--------+
| 学员ID | 姓名 | 性别 |
+----------+---------------+--------+
| 1 | Shi Zhongyu | M |
| 2 | Shi Potian | M |
| 3 | Xie Yanke | M |
#order by————指定字段排序输出
MariaDB [hellodb]> select *from students order by name desc limit 2; #name字段降序输出前两个(0,1)
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
+-------+---------------+-----+--------+---------+-----------+
2 rows in set (0.000 sec)
MariaDB [hellodb]> select *from students order by name desc limit 1,2; #name字段降序输出从1开始输出两位(1,2)
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
+-------+---------------+-----+--------+---------+-----------+
2 rows in set (0.000 sec)
MariaDB [hellodb]> select *from students order by name desc limit 25;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
#where
MariaDB [hellodb]> select *from students where stuid>2 and stuid <4;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
+-------+-----------+-----+--------+---------+-----------+
1 row in set (0.001 sec)
MariaDB [hellodb]> select *from students where between 2 and 4;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'between 2 and 4' at line 1
MariaDB [hellodb]> select *from students where stuid between 2 and 4;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
+-------+------------+-----+--------+---------+-----------+
3 rows in set (0.001 sec)
MariaDB [hellodb]> select *from students where stuid <3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.002 sec)
MariaDB [hellodb]> select *from students where gender='m';
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
15 rows in set (0.000 sec)
MariaDB [hellodb]> select *from students where gender is null;
Empty set (0.000 sec)
MariaDB [hellodb]> select *from students where gender is not null;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.000 sec)
MariaDB [hellodb]> select *from students where name like 't%'
-> ;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 13 | Tian Boguang | 33 | M | 2 | NULL |
+-------+--------------+-----+--------+---------+-----------+
1 row in set (0.000 sec)
MariaDB [hellodb]> select *from students where name like '%t';
Empty set (0.000 sec)
MariaDB [hellodb]> select *from students where name like '%t%';
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
+-------+--------------+-----+--------+---------+-----------+
3 rows in set (0.001 sec)
MariaDB [hellodb]> select *from students where name rlike '.*[lo].*';
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+--------------+-----+--------+---------+-----------+
13 rows in set (0.001 sec)
MariaDB [hellodb]> select *from students where classid in (2,4,7);
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.000 sec)
MariaDB [hellodb]> select *from students where classid not in (2,4,7);
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
+-------+---------------+-----+--------+---------+-----------+
13 rows in set (0.000 sec)
#判断是否为nullMariaDB [hellodb]> select *from students where classid = null; #直接等于不行,需要用<=>Empty set (0.000 sec)MariaDB [hellodb]> select *from students where classid is null;+-------+-------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+-------------+-----+--------+---------+-----------+| 24 | Xu Xian | 27 | M | NULL | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL |+-------+-------------+-----+--------+---------+-----------+2 rows in set (0.000 sec)MariaDB [hellodb]> select *from students where classid <=> null;+-------+-------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+-------------+-----+--------+---------+-----------+| 24 | Xu Xian | 27 | M | NULL | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL |+-------+-------------+-----+--------+---------+-----------+2 rows in set (0.000 sec)#ifnull函数判断指定的字段是否为空值,如果空值则使用指定默认值MariaDB [hellodb]> select stuid,name,ifnull(classid,'无班级') from students where classid is null;+-------+-------------+-----------------------------+| stuid | name | ifnull(classid,'无班级') |+-------+-------------+-----------------------------+| 24 | Xu Xian | 无班级 || 25 | Sun Dasheng | 无班级 |+-------+-------------+-----------------------------+2 rows in set (0.000 sec)
#记录去重MariaDB [hellodb]> select distinct gender from students;+--------+| gender |+--------+| M || F |+--------+2 rows in set (0.000 sec)MariaDB [hellodb]> select gender from students;+--------+| gender |+--------+| M || M || M || M || M || M || F || F || F || F || M || F || M || F || M || M || M || M || F || F || F || F || M || M || M |+--------+25 rows in set (0.000 sec)#将age和gender多个字段重复的记录去重MariaDB [hellodb]> select distinct age,gender from students;+-----+--------+| age | gender |+-----+--------+| 22 | M || 53 | M || 32 | M || 26 | M || 46 | M || 19 | F || 17 | F || 20 | F || 23 | M || 33 | M || 19 | M || 21 | M || 25 | M || 18 | F || 22 | F || 27 | M || 100 | M |+-----+--------+17 rows in set (0.000 sec)MariaDB [hellodb]> select age,gender from students order by age;+-----+--------+| age | gender |+-----+--------+| 17 | F || 17 | F || 18 | F || 19 | F || 19 | F || 19 | F || 19 | M || 19 | F || 20 | F || 20 | F || 21 | M || 22 | M || 22 | F || 22 | M || 23 | M || 23 | M || 23 | M || 25 | M || 26 | M || 27 | M || 32 | M || 33 | M || 46 | M || 53 | M || 100 | M |+-----+--------+25 rows in set (0.000 sec)
#sql注入攻击(如果感兴趣之后写一篇文章介绍一下)select * from user where name='admin' and password='' or '1'='1';select * from user where name='admin' and password='' or '1=1';select * from user where name='admin'; -- ' and password='asdasd3';select * from user where name='admin'; # ' and password='asdadjkdf3';
#分页查询#只取前3个mysql> select * from students limit 0,3;mysql> select * from students limit 3;# 查询第n页的数据,每页显示m条记录mysql> select * from students limit (n-1) * m,m;
#聚合函数MariaDB [hellodb]> select sum(age) from students where gender ='M'; #计算性别为M的年龄和+----------+| sum(age) |+----------+| 495 |+----------+1 row in set (0.000 sec)MariaDB [hellodb]> select count(*) from students where gender ='M'; #计算性别为M的人数+----------+| count(*) |+----------+| 15 |+----------+1 row in set (0.000 sec)MariaDB [hellodb]> select sum(age)/count(*) from students where gender ='M'; #计算性别为M的平均年龄+-------------------+| sum(age)/count(*) |+-------------------+| 33.0000 |+-------------------+1 row in set (0.001 sec)
#分组统计MariaDB [hellodb]> select classid,count(*) 数量 from students group by classid; #统计每个班有多少人+---------+--------+| classid | 数量 |+---------+--------+| NULL | 2 || 1 | 4 || 2 | 3 || 3 | 4 || 4 | 4 || 5 | 1 || 6 | 4 || 7 | 3 |+---------+--------+8 rows in set (0.000 sec)MariaDB [hellodb]> select classid,avg(age) 平均年龄 from students where classid >3 group by classid having 平均年龄 > 30; #显示classid大于3,平均年龄大于30的classID和平均年龄+---------+--------------+| classid | 平均年龄 |+---------+--------------+| 5 | 46.0000 |+---------+--------------+1 row in set (0.001 sec)MariaDB [hellodb]> select gender,avg(age) 平均年龄 from students group by gender having gender="M"; #显示性别为男的平均年龄+--------+--------------+| gender | 平均年龄 |+--------+--------------+| M | 33.0000 |+--------+--------------+1 row in set (0.000 sec)#下面两个例子都是显示以classid和gender分组统计,即显示每个班级的男生和女生的人数,哪个在前就以哪个为主进行排序MariaDB [hellodb]> select classid,gender,count(*) 数量 from students group by classid,gender; #以classid进行排序显示+---------+--------+--------+| classid | gender | 数量 |+---------+--------+--------+| NULL | M | 2 || 1 | F | 2 || 1 | M | 2 || 2 | M | 3 || 3 | F | 3 || 3 | M | 1 || 4 | M | 4 || 5 | M | 1 || 6 | F | 3 || 6 | M | 1 || 7 | F | 2 || 7 | M | 1 |+---------+--------+--------+12 rows in set (0.000 sec)MariaDB [hellodb]> select classid,gender,count(*) 数量 from students group by gender,classid; #以性别进行排序显示+---------+--------+--------+| classid | gender | 数量 |+---------+--------+--------+| 1 | F | 2 || 3 | F | 3 || 6 | F | 3 || 7 | F | 2 || NULL | M | 2 || 1 | M | 2 || 2 | M | 3 || 3 | M | 1 || 4 | M | 4 || 5 | M | 1 || 6 | M | 1 || 7 | M | 1 |+---------+--------+--------+12 rows in set (0.000 sec)#group_concat函数实现分组信息的集合MariaDB [hellodb]> select gender,group_concat(name) from students group by gender;+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| gender | group_concat(name) |+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| F | Lin Daiyu,Yue Lingshan,Ren Yingying,Wen Qingqing,Xi Ren,Diao Chan,Huang Yueying,Xiao Qiao,Lu Wushuang,Xue Baochai || M | Hua Rong,Shi Zhongyu,Ma Chao,Xu Xian,Lin Chong,Xu Zhu,Duan Yu,Tian Boguang,Yuan Chengzhi,Shi Qing,Yu Yutong,Ding Dian,Xie Yanke,Shi Potian,Sun Dasheng |+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.000 sec)# with rollup 分组后聚合函数统计后再做汇总MariaDB [hellodb]> select gender,count(*) from students group by gender with rollup;+--------+----------+| gender | count(*) |+--------+----------+| F | 10 || M | 15 || NULL | 25 |+--------+----------+3 rows in set (0.000 sec)
注意:一旦使用分组group by,在select 后面的只能采用分组的列和聚合函数,其它的列不能放在select后面,否则根据系统变量SQL_MODE的值不同而不同的结果
MariaDB [hellodb]> select classid,count(*) 数量 from students group by classid;+---------+--------+| classid | 数量 |+---------+--------+| NULL | 2 || 1 | 4 || 2 | 3 || 3 | 4 || 4 | 4 || 5 | 1 || 6 | 4 || 7 | 3 |+---------+--------+8 rows in set (0.000 sec)MariaDB [hellodb]> select classid,count(*),stuid 数量 from students group by classid; #stuid重命名为数量显示,但是这个毫无意义,显示的其实是该classid对应的第一个学生的stuid而已。而且在现在较新的版本中执行此命令会直接报错+---------+----------+--------+| classid | count(*) | 数量 |+---------+----------+--------+| NULL | 2 | 24 || 1 | 4 | 2 || 2 | 3 | 1 || 3 | 4 | 5 || 4 | 4 | 4 || 5 | 1 | 6 || 6 | 4 | 9 || 7 | 3 | 8 |+---------+----------+--------+8 rows in set (0.000 sec)#以下为MySQL8.0.26 的执行结果mysql> \s;--------------mysql Ver 8.0.26 for Linux on x86_64 (Source distribution)Connection id: 8Current database: hellodbCurrent user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 8.0.26 Source distributionmysql> select classid,count(*),stuid 数量 from students group by classid;ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hellodb.students.StuID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
#排序MariaDB [hellodb]> select * from students where classid is not null order by gender desc, age asc ; #注意必须先过滤在排序+-------+---------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+---------------+-----+--------+---------+-----------+| 15 | Duan Yu | 19 | M | 4 | NULL || 16 | Xu Zhu | 21 | M | 1 | NULL || 1 | Shi Zhongyu | 22 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 18 | Hua Rong | 23 | M | 7 | NULL || 23 | Ma Chao | 23 | M | 4 | NULL || 11 | Yuan Chengzhi | 23 | M | 6 | NULL || 17 | Lin Chong | 25 | M | 4 | NULL || 5 | Yu Yutong | 26 | M | 3 | 1 || 4 | Ding Dian | 32 | M | 4 | 4 || 13 | Tian Boguang | 33 | M | 2 | NULL || 6 | Shi Qing | 46 | M | 5 | NULL || 3 | Xie Yanke | 53 | M | 2 | 16 || 8 | Lin Daiyu | 17 | F | 7 | NULL || 14 | Lu Wushuang | 17 | F | 3 | NULL || 19 | Xue Baochai | 18 | F | 6 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 10 | Yue Lingshan | 19 | F | 3 | NULL || 20 | Diao Chan | 19 | F | 7 | NULL || 12 | Wen Qingqing | 19 | F | 1 | NULL || 9 | Ren Yingying | 20 | F | 6 | NULL || 22 | Xiao Qiao | 20 | F | 1 | NULL || 21 | Huang Yueying | 22 | F | 6 | NULL |+-------+---------------+-----+--------+---------+-----------+23 rows in set (0.000 sec)MariaDB [hellodb]> select * from students order by gender desc, age asc where classid is not null ; #先排序在过滤直接报错ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where classid is not null' at line 1#多列排序MariaDB [hellodb]> select * from students order by gender desc, age asc; #gender反向排序,age正向排序+-------+---------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+---------------+-----+--------+---------+-----------+| 15 | Duan Yu | 19 | M | 4 | NULL || 16 | Xu Zhu | 21 | M | 1 | NULL || 1 | Shi Zhongyu | 22 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 11 | Yuan Chengzhi | 23 | M | 6 | NULL || 18 | Hua Rong | 23 | M | 7 | NULL || 23 | Ma Chao | 23 | M | 4 | NULL || 17 | Lin Chong | 25 | M | 4 | NULL || 5 | Yu Yutong | 26 | M | 3 | 1 || 24 | Xu Xian | 27 | M | NULL | NULL || 4 | Ding Dian | 32 | M | 4 | 4 || 13 | Tian Boguang | 33 | M | 2 | NULL || 6 | Shi Qing | 46 | M | 5 | NULL || 3 | Xie Yanke | 53 | M | 2 | 16 || 25 | Sun Dasheng | 100 | M | NULL | NULL || 14 | Lu Wushuang | 17 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL || 19 | Xue Baochai | 18 | F | 6 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 20 | Diao Chan | 19 | F | 7 | NULL || 10 | Yue Lingshan | 19 | F | 3 | NULL || 12 | Wen Qingqing | 19 | F | 1 | NULL || 22 | Xiao Qiao | 20 | F | 1 | NULL || 9 | Ren Yingying | 20 | F | 6 | NULL || 21 | Huang Yueying | 22 | F | 6 | NULL |+-------+---------------+-----+--------+---------+-----------+25 rows in set (0.000 sec)
2.6.3. 多表查询
多表查询,即查询结果来自于多张表
子查询:在SQL语句嵌套着查询语句,性能较差,基于某语句的查询结果再次进行的查询
联合查询:UNION
交叉连接:笛卡尔乘积 CROSS JOIN
内连接:
等值连接:让表之间的字段以”等值”建立连接关系
不等值连接
自然连接:去掉重复列的等值连接 , 语法: FROM table1 NATURAL JOIN table2;
外连接:
左外连接:FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接:FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
完全外连接: FROM tb1 FULL OUTER JOIN tb2 ON tb1.col=tb2.col 注意:MySQL 不支持此SQL语法
自连接:本表和本表进行连接查询
2.6.3.1★★子查询★★
子查询 subquery 即SQL语句调用另一个SELECT子句,可以是对同一张表,也可以是对不同表,主要有以下四种常见的用法.
- 用于比较表达式中的子查询;子查询仅能返回单个值
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM teachers);update students set Age=(SELECT avg(Age) FROM teachers) where stuid=25;
- 用于IN中的子查询:**子查询应该单独查询并返回一个或多个值重新构成列表****
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
- 用于EXISTS 和 Not EXISTS
参考链接:https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html
EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS 内部有一个子查询语句(SELECT … FROM…), 将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空,返回一个布尔值。将外查询表的每一行,代入内查询作为检验,如果内查询返回的结果为非空值,则EXISTS子句返回TRUE,外查询的这一行数据便可作为外查询的结果行返回,否则不能作为结果MariaDB [hellodb]> select *from students s where (select *from teachers t where s.teacherid=t.tid);ERROR 1241 (21000): Operand should contain 1 column(s)MariaDB [hellodb]> select *from students s where exists (select *from teachers t where s.teacherid=t.tid);+-------+-------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+-------------+-----+--------+---------+-----------+| 1 | Shi Zhongyu | 22 | M | 2 | 3 || 4 | Ding Dian | 32 | M | 4 | 4 || 5 | Yu Yutong | 26 | M | 3 | 1 |+-------+-------------+-----+--------+---------+-----------+3 rows in set (0.001 sec)#说明:1、EXISTS (或 NOT EXISTS) 用在 where之后,且后面紧跟子查询语句(带括号)2、EXISTS (或 NOT EXISTS) 只关心子查询有没有结果,并不关心子查询的结果具体是什么3、上述语句把students的记录逐条代入到Exists后面的子查询中,如果子查询结果集不为空,即说明存在,那么这条students的记录出现在最终结果集,否则被排除
- 用于FROM子句中的子查询
使用格式:SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;
MariaDB [hellodb]> SELECT s.ClassID,s.aage FROM (SELECT ClassID,avg(Age) AS aage FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;+---------+---------+| ClassID | aage |+---------+---------+| 2 | 36.0000 || 5 | 46.0000 |+---------+---------+2 rows in set (0.001 sec)
#子查询:select 的执行结果,被其它SQL调用MariaDB [hellodb]> select stuid,name,age from students where age> avg(age);ERROR 1111 (HY000): Invalid use of group functionMariaDB [hellodb]> select stuid,name,age from students where age> (select avg(age) from students);+-------+--------------+-----+| stuid | name | age |+-------+--------------+-----+| 3 | Xie Yanke | 53 || 4 | Ding Dian | 32 || 6 | Shi Qing | 46 || 13 | Tian Boguang | 33 || 25 | Sun Dasheng | 100 |+-------+--------------+-----+#子查询用于更新表MariaDB [hellodb]> update teachers set age=(select avg(age) from students) where tid=4;Query OK, 1 row affected (0.001 sec)Rows matched: 1 Changed: 1 Warnings: 0MariaDB [hellodb]> select *from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 27 | F |+-----+---------------+-----+--------+4 rows in set (0.001 sec)MariaDB [hellodb]> select avg(age) from students;+----------+| avg(age) |+----------+| 27.4000 |+----------+1 row in set (0.000 sec)
2.6.3.2★★联合查询★★
联合查询 Union 实现的条件,多个表的字段数量相同,字段名和数据类型可以不同,但一般数据类型是相同的.
SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
MariaDB [hellodb]> select *from teachers union select *from students; #直接联合两张表因为字段数不同无法显示ERROR 1222 (21000): The used SELECT statements have a different number of columnsMariaDB [hellodb]> select tid as id ,name,age,gender from teachers union select stuid,name,age,gender from students; #指定相同的字段数量就可以正常显示了+----+---------------+-----+--------+| id | name | age | gender |+----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 27 | F || 1 | Shi Zhongyu | 22 | M || 2 | Shi Potian | 22 | M || 3 | Xie Yanke | 53 | M || 4 | Ding Dian | 32 | M || 5 | Yu Yutong | 26 | M || 6 | Shi Qing | 46 | M || 7 | Xi Ren | 19 | F || 8 | Lin Daiyu | 17 | F || 9 | Ren Yingying | 20 | F || 10 | Yue Lingshan | 19 | F || 11 | Yuan Chengzhi | 23 | M || 12 | Wen Qingqing | 19 | F || 13 | Tian Boguang | 33 | M || 14 | Lu Wushuang | 17 | F || 15 | Duan Yu | 19 | M || 16 | Xu Zhu | 21 | M || 17 | Lin Chong | 25 | M || 18 | Hua Rong | 23 | M || 19 | Xue Baochai | 18 | F || 20 | Diao Chan | 19 | F || 21 | Huang Yueying | 22 | F || 22 | Xiao Qiao | 20 | F || 23 | Ma Chao | 23 | M || 24 | Xu Xian | 27 | M || 25 | Sun Dasheng | 100 | M |+----+---------------+-----+--------+29 rows in set (0.001 sec)MariaDB [hellodb]> select *from teachers union select *from teachers; #默认去重等价于select distinct *from teachers union select *from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 27 | F |+-----+---------------+-----+--------+4 rows in set (0.000 sec)MariaDB [hellodb]> select * from teachers union all select * from teachers; #不去重+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 27 | F || 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 27 | F |+-----+---------------+-----+--------+8 rows in set (0.000 sec)
2.6.3.3交叉连接(基本不用)
cross join 即多表的记录之间做笛卡尔乘积组合,并且多个表的列横向合并相加, “雨露均沾”
比如: 第一个表3行4列,第二个表5行6列,cross join后的结果为3*5=15行,4+6=10列
交叉连接生成的记录可能会非常多,建议慎用
#横向合并,交叉连接(横向笛卡尔)
MariaDB [hellodb]> select * from students cross join teachers;
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Zhang Sanfeng | 94 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 4 | Lin Chaoying | 27 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | Song Jiang | 45 | M |
| 2 | Shi Potian | 22 | M | 1 | 7 | 2 | Zhang Sanfeng | 94 | M |
| 2 | Shi Potian | 22 | M | 1 | 7 | 3 | Miejue Shitai | 77 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | 4 | Lin Chaoying | 27 | F |
2.6.3.4★内连接★
inner join 内连接取多个表的交集
MariaDB [hellodb]> select *from students inner join teachers on students.teacherid=teachers.tid; #取学生表的teacherid等于老师表的tid的部分显示
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 27 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.001 sec)
#如果表定义了别名,原表名将无法使用
MariaDB [hellodb]> select stuid, s.name studentname, tid, t.name teachername from students as s inner join teachers t on s.teacherid=t.tid;
+-------+-------------+-----+---------------+
| stuid | studentname | tid | teachername |
+-------+-------------+-----+---------------+
| 5 | Yu Yutong | 1 | Song Jiang |
| 1 | Shi Zhongyu | 3 | Miejue Shitai |
| 4 | Ding Dian | 4 | Lin Chaoying |
+-------+-------------+-----+---------------+
3 rows in set (0.000 sec)
#内连接后过滤数据
MariaDB [hellodb]> select * from students s inner join teachers t on s.teacherid=t.tid and s.age > 30;
+-------+-----------+-----+--------+---------+-----------+-----+--------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-----------+-----+--------+---------+-----------+-----+--------------+-----+--------+
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 27 | F |
+-------+-----------+-----+--------+---------+-----------+-----+--------------+-----+--------+
1 row in set (0.000 sec)
自然连接
- 当源表和目标表共享相同名称的列时,就可以在它们之间执行自然连接,而无需指定连接列。
- 在使用纯自然连接时,如没有相同的列时,会产生交叉连接(笛卡尔乘积)
- 语法:(SQL:1999)SELECT table1.column, table2.column FROM table1 NATURAL JOIN table2;
MariaDB [hellodb]> create table t1 ( id int,name char(20));Query OK, 0 rows affected (0.004 sec)MariaDB [hellodb]> create table t2 ( id int,title char(20));Query OK, 0 rows affected (0.005 sec)MariaDB [hellodb]> insert t1 values(1,'mage'),(2,'wang'),(3,'zhang');Query OK, 3 rows affected (0.001 sec)Records: 3 Duplicates: 0 Warnings: 0MariaDB [hellodb]> insert t2 values(1,'ceo'),(2,'cto');Query OK, 2 rows affected (0.001 sec)Records: 2 Duplicates: 0 Warnings: 0MariaDB [hellodb]> select * from t1;+------+-------+| id | name |+------+-------+| 1 | zhang || 2 | li || 3 | wang |+------+-------+3 rows in set (0.000 sec)MariaDB [hellodb]> select * from t2;+------+-------+| id | title |+------+-------+| 1 | ceo || 2 | cto |+------+-------+2 rows in set (0.000 sec)MariaDB [hellodb]> select *from t1 NATURAL JOIN t2;+------+-------+-------+| id | name | title |+------+-------+-------+| 1 | zhang | ceo || 2 | li | cto |+------+-------+-------+2 rows in set (0.000 sec)
2.6.3.5★★左和右外连接★★
左连接: 以左表为主根据条件查询右表数据﹐如果根据条件查询右表数据不存在使用null值填充
右连接: 以右表为主根据条件查询左表数据﹐如果根据条件查询左表数据不存在使用null值填充
MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid;+-------+---------------+-----+-----------+------+---------------+------+| stuid | name | age | teacherid | tid | name | age |+-------+---------------+-----+-----------+------+---------------+------+| 1 | Shi Zhongyu | 22 | 3 | 3 | Miejue Shitai | 77 || 2 | Shi Potian | 22 | 7 | NULL | NULL | NULL || 3 | Xie Yanke | 53 | 16 | NULL | NULL | NULL || 4 | Ding Dian | 32 | 4 | 4 | Lin Chaoying | 27 || 5 | Yu Yutong | 26 | 1 | 1 | Song Jiang | 45 || 6 | Shi Qing | 46 | NULL | NULL | NULL | NULL || 7 | Xi Ren | 19 | NULL | NULL | NULL | NULL || 8 | Lin Daiyu | 17 | NULL | NULL | NULL | NULL || 9 | Ren Yingying | 20 | NULL | NULL | NULL | NULL || 10 | Yue Lingshan | 19 | NULL | NULL | NULL | NULL || 11 | Yuan Chengzhi | 23 | NULL | NULL | NULL | NULL || 12 | Wen Qingqing | 19 | NULL | NULL | NULL | NULL || 13 | Tian Boguang | 33 | NULL | NULL | NULL | NULL || 14 | Lu Wushuang | 17 | NULL | NULL | NULL | NULL || 15 | Duan Yu | 19 | NULL | NULL | NULL | NULL || 16 | Xu Zhu | 21 | NULL | NULL | NULL | NULL || 17 | Lin Chong | 25 | NULL | NULL | NULL | NULL || 18 | Hua Rong | 23 | NULL | NULL | NULL | NULL || 19 | Xue Baochai | 18 | NULL | NULL | NULL | NULL || 20 | Diao Chan | 19 | NULL | NULL | NULL | NULL || 21 | Huang Yueying | 22 | NULL | NULL | NULL | NULL || 22 | Xiao Qiao | 20 | NULL | NULL | NULL | NULL || 23 | Ma Chao | 23 | NULL | NULL | NULL | NULL || 24 | Xu Xian | 27 | NULL | NULL | NULL | NULL || 25 | Sun Dasheng | 100 | NULL | NULL | NULL | NULL |+-------+---------------+-----+-----------+------+---------------+------+25 rows in set (0.000 sec)MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students as s right outer join teachers as t on s.teacherid=t.tid;+-------+-------------+------+-----------+-----+---------------+-----+| stuid | name | age | teacherid | tid | name | age |+-------+-------------+------+-----------+-----+---------------+-----+| 1 | Shi Zhongyu | 22 | 3 | 3 | Miejue Shitai | 77 || 4 | Ding Dian | 32 | 4 | 4 | Lin Chaoying | 27 || 5 | Yu Yutong | 26 | 1 | 1 | Song Jiang | 45 || NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 |+-------+-------------+------+-----------+-----+---------------+-----+4 rows in set (0.000 sec)
2.6.3.6 完全外连接
MySQL 不支持完全外连接full outer join语法
2.6.3.7自连接
自连接, 即表自身连接自身
MariaDB [hellodb]> select * from emp;+------+----------+----------+| id | name | leaderid |+------+----------+----------+| 1 | mage | NULL || 2 | zhangsir | 1 || 3 | wang | 2 || 4 | zhang | 3 |+------+----------+----------+4 rows in set (0.00 sec)MariaDB [hellodb]> select e.name,l.name from emp as e inner join emp as l one.leaderid=l.id;+----------+----------+| name | name |+----------+----------+| zhangsir | mage || wang | zhangsir || zhang | wang |+----------+----------+3 rows in set (0.00 sec)MariaDB [hellodb]> select e.name,IFNULL(l.name,'无上级') from emp as e leftjoin emp as l on e.leaderid=l.id;+----------+----------+| name | name | +----------+----------+| zhangsir | mage || wang | zhangsir || zhang | wang || mage | NULL |+----------+----------+4 rows in set (0.00 sec)MariaDB [hellodb]> select e.name emp,IFNULL(l.name,'无上级') leader from emp as eleft join emp as l on e.leaderid=l.id;+----------+----------+| emp | leader |+----------+----------+| zhangsir | mage || wang | zhangsir || zhang | wang || mage | NULL |+----------+----------+4 rows in set (0.000 sec)
2.6.4select语句处理的顺序
查询执行路径中的组件:查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎
SELECT语句的执行流程:
FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER BY --> LIMIT
练习
导入hellodb.sql生成数据库
- 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄
select name,age from students where age >25;
- 以ClassID为分组依据,显示每组的平均年龄
select classid,avg(age) from students group by classid
- 显示第2题中平均年龄大于30的分组及平均年龄
select classid,avg(age) from students group by classid having avg(age) >30;
- 显示以L开头的名字的同学的信息
select *from students where name like "l%";
- 显示TeacherID非空的同学的相关信息
select *from students where teacherid is not null;
- 以年龄排序后,显示年龄最大的前10位同学的信息
select *from students order by age desc limit 10;
- 查询年龄大于等于20岁,小于等于25岁的同学的信息
select *from students where 25>=age and age>=20;
- 以ClassID分组,显示每班的同学的人数
select classid,count(*) 人数 from students group by classid;
- 以Gender分组,显示其年龄之和
select gender,sum(age) from students group by gender;
- 以ClassID分组,显示其平均年龄大于25的班级
select classid ,avg(age) from students group by classid having avg(age) > 25;
- 以Gender分组,显示各组中年龄大于25的学员的年龄之和
select gender,sum(age) from students where age>25 group by gender;
- 显示前5位同学的姓名、班级、老师信息
select name,classid,teacherid from students limit 5;
- 显示年级大于20的同学的名称及班级
select name,classid from students where age>20;
- 显示其年龄大于平均年龄的同学的名字
select name,age from students where age> (select avg(age) from students);
- 显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
select * from students as a inner join (select classid,count(stuid),avg(age) as aage from students where classid >0 group by classid having count(stuid)>=3) as b on a.classid=b.classid where a.age>b.aage;