目录
分组:group by前面要么匹配分组的属性,要么匹配聚合函数,否则没有意义。
order by排序:null的默认优先级最高,正序时默认出现在前面
1、准备环境
mysql> source hellodb_innodb.sql;
mysql> use hellodb;
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
简单查询
起别名
[hellodb]> select stuid 学生ID,name 姓名,gender 性别 from students;
过滤条件:布尔型表达式
<=> 相等或都为空
<> 不等
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL; 或者SELECT * FROM students WHERE gender <=> NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
SELECT * FROM students ORDER BY name DESC LIMIT 2;
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4
SELECT * FROM students WHERE BETWEEN 2 AND 4
SELECT * FROM students WHERE name LIKE ‘t%’
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
select * from students where classid in (1,3,5);
select * from students where classid not in (1,3,5);
去重
[hellodb]> select distinct gender from students;
+--------+
| gender |
+--------+
| M |
| F |
+--------+
模糊查找(不区分大小写)
:42: (root@localhost) [hellodb]> SELECT * FROM students where name like 'x%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
6 rows in set (0.00 sec)
分组:group by前面要么匹配分组的属性,要么匹配聚合函数,否则没有意义。
[hellodb]> select gender,avg(age),max(age),min(age) from students group by gender;
多次分组:先对班级进行分组,再对性别分组。
[hellodb]> select classid,gender,avg(age),max(age),min(age) from students gr
oup by classid,gender;
+---------+--------+----------+----------+----------+
| classid | gender | avg(age) | max(age) | min(age) |
+---------+--------+----------+----------+----------+
| NULL | F | 30.0000 | 30 | 30 |
| NULL | M | 63.5000 | 100 | 27 |
| 1 | F | 19.5000 | 20 | 19 |
| 1 | M | 21.5000 | 22 | 21 |
| 2 | M | 36.0000 | 53 | 22 |
| 3 | F | 18.3333 | 19 | 17 |
| 3 | M | 26.0000 | 26 | 26 |
| 4 | M | 24.7500 | 32 | 19 |
| 5 | M | 46.0000 | 46 | 46 |
| 6 | F | 20.0000 | 22 | 18 |
| 6 | M | 23.0000 | 23 | 23 |
| 7 | F | 18.0000 | 19 | 17 |
| 7 | M | 23.0000 | 23 | 23 |
+---------+--------+----------+----------+----------+
先分组再筛选where需要替换成having
:12: (root@localhost) [hellodb]> select classid,avg(age) from students group by classid where classid is not null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where classid is not null' at line 1
:12: (root@localhost) [hellodb]> select classid,avg(age) from students group by classid having classid is not null;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+----------+
7 rows in set (0.00 sec)
可以先筛选在分组,where要在group by之前
:13: (root@localhost) [hellodb]> select classid,avg(age) from students where classid is not null group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+----------+
7 rows in set (0.00 sec)
order by排序:null的默认优先级最高,正序时默认出现在前面
:17: (root@localhost) [hellodb]> select classid,avg(age) from students group by classid order by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| NULL | 52.3333 |
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+----------+
8 rows in set (0.00 sec)
:17: (root@localhost) [hellodb]> select classid,avg(age) from students group by classid order by -classid desc;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
| NULL | 52.3333 |
+---------+----------+
8 rows in set (0.00 sec)
limit:
:21: (root@localhost) [hellodb]> select name,age from students order by age limit 3;
+-------------+-----+
| name | age |
+-------------+-----+
| Lu Wushuang | 17 |
| Lin Daiyu | 17 |
| Xue Baochai | 18 |
+-------------+-----+
3 rows in set (0.00 sec)
# 跳过前两个,显示后三个
:21: (root@localhost) [hellodb]> select name,age from students order by age limit 2,3;
+--------------+-----+
| name | age |
+--------------+-----+
| Xue Baochai | 18 |
| Wen Qingqing | 19 |
| Xi Ren | 19 |
+--------------+-----+
3 rows in set (0.00 sec)
按照时间过滤
MariaDB [testdb]>create table testdate (id int,date timestamp DEFAULT
CURRENT_TIMESTAMP );
MariaDB [testdb]> insert testdate (id) values(1);
MariaDB [testdb]> insert testdate (id) values(2);
MariaDB [testdb]> select * from testdate;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2020-06-03 15:21:03 |
| 2 | 2020-06-03 15:21:12 |
| 3 | 2020-06-03 15:21:14 |
| 4 | 2020-06-03 15:21:17 |
| 5 | 2020-06-03 18:27:39 |
| 6 | 2020-06-03 18:27:44 |
+----+---------------------+ 6 rows in set (0.001 sec)
MariaDB [testdb]> select * from testdate where date between '2020-06-03
15:21:12' and '2020-06-03 18:27:40'; +----+---------------------+
| id | date |
+----+---------------------+
| 2 | 2020-06-03 15:21:12 |
| 3 | 2020-06-03 15:21:14 |
| 4 | 2020-06-03 15:21:17 |
| 5 | 2020-06-03 18:27:39 |
+----+---------------------+ 4 rows in set (0.000 sec)
MariaDB [testdb]> select * from testdate where date >= '2020-06-03 15:21:12'
and date <= '2020-06-03 18:27:40';+----+---------------------+
| id | date |
+----+---------------------+
| 2 | 2020-06-03 15:21:12 |
| 3 | 2020-06-03 15:21:14 |
| 4 | 2020-06-03 15:21:17 |
| 5 | 2020-06-03 18:27:39 |
+----+---------------------+ 4 rows in set (0.001 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='magedu123';
select * from user where name='admin'; # ' and password='magedu123';