SQL基础
这里只会简单介绍一下增删改查，具体可以看看我的博客
https://sunxiang.net/
数据库的基础使用
安装数据库
这里为了省事直接使用kali来安装，kali使用的是Ubuntu系统
┌──(root㉿kali)-[/etc/mysql]
└─# apt install -y mysql*
开启数据库
┌──(root㉿kali)-[/etc/mysql]
└─# systemctl start mysql
#检查数据库是否成功启动（3306端口）
┌──(root㉿kali)-[/etc/mysql]
└─# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 100 192.168.239.132:3000 0.0.0.0:*
LISTEN 0 128 127.0.0.1:6010 0.0.0.0:*
LISTEN 0 4096 127.0.0.1:6789 0.0.0.0:*
LISTEN 0 80 127.0.0.1:3306 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 128 [::1]:6010 [::]:*
登录数据库
┌──(root㉿kali)-[/etc/mysql]
└─# mysql -uroot -p'root' #这里可以使用交互式，先不指定密码，然后再输入密码，自行尝试
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 47
Server version: 10.5.12-MariaDB-1 Debian 11
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
注意：
在数据库中执行语句必须以为因为模式的;来结束
所有的标点符号都要用英文模式
查看数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
创建数据库
#语法
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
CHARACTER SET 'character set name'
COLLATE 'collate name';
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.000 sec)
命令执行成功会提示Query ok
#查看指定的数据库信息
MariaDB [(none)]> show create database test;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.000 sec)
1 row in set表示有一行结果显示
删除数据库
#创建数据库haha
MariaDB [(none)]> create database haha;
Query OK, 1 row affected (0.000 sec)
#删除数据库haha
MariaDB [(none)]> drop database haha;
Query OK, 0 rows affected (0.001 sec)
使用数据库
MariaDB [(none)]> use test
Database changed
MariaDB [test]>
DDL语句——数据定义语言
创建表
#创建students表，有id，name，age，gender四列内容。其中id为主键且自动生成，gender进行枚举，只有M和F两个选项默认为M
MariaDB [test]> create table students(
-> id int auto_increment primary key,
-> name varchar(20) not null,
-> age tinyint unsigned,
-> gender enum('M','W')default 'M');
Query OK, 0 rows affected (0.014 sec)
查看表
#查看所有表
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students |
+----------------+
1 row in set (0.000 sec)
#查看指定表
MariaDB [test]> show create table students;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`gender` enum('M','W') DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
#查看表的类型
MariaDB [test]> desc students;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','W') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
修改表
#删除表中字段
MariaDB [test]> alter table students drop age;
Query OK, 0 rows affected (0.004 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> desc students;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| gender | enum('M','W') | YES | | M | |
+--------+---------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)
#在表中添加字段
MariaDB [test]> alter table students add class int;
Query OK, 0 rows affected (0.001 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> desc students;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| gender | enum('M','W') | YES | | M | |
| class | int(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
#修改字段名
MariaDB [test]> alter table students change class age tinyint(3);
Query OK, 0 rows affected (0.005 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> desc students;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| gender | enum('M','W') | YES | | M | |
| age | tinyint(3) | YES | | NULL | |
+--------+---------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
#修改表名
MariaDB [test]> alter table students rename as yinhe;
Query OK, 0 rows affected (0.001 sec)
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| yinhe |
+----------------+
1 row in set (0.000 sec)
DML语句——数据操纵语言
插入数据
INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)
#向表yinhe中添加两条数据
MariaDB [test]> insert yinhe(name,age) values('zhangsan','20'),('lisi','21');
Query OK, 2 rows affected (0.012 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> select * from yinhe;
+----+----------+--------+------+
| id | name | gender | age |
+----+----------+--------+------+
| 1 | zhangsan | M | 20 |
| 2 | lisi | M | 21 |
+----+----------+--------+------+
2 rows in set (0.000 sec)
虽然表中有四个字段，但是我只指定了两个字段，这是为什么？
如果指定的是id，和gender可以吗？为什么？
点击查看
MariaDB [test]> insert yinhe(id,gender) values(11,’F’),(21,”M”);
ERROR 1364 (HY000): Field ‘name’ doesn’t have a default value
#如果不指定字段就要对所有字段进行填写
MariaDB [test]> insert yinhe values('wangwu','W',22);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
MariaDB [test]> insert yinhe values(3,'wangwu','W',22);
Query OK, 1 row affected (0.001 sec)
修改数据
#修改表yinhe中lisi的性别为W
MariaDB [test]> update yinhe set gender='W' where name='lisi';
Query OK, 1 row affected (0.012 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> select * from yinhe;
+----+----------+--------+------+
| id | name | gender | age |
+----+----------+--------+------+
| 1 | zhangsan | M | 20 |
| 2 | lisi | W | 21 |
| 3 | wangwu | W | 22 |
+----+----------+--------+------+
3 rows in set (0.000 sec)
注意：必须要加限制条件，这里是通过name字段来进行限制的，如果不加where来限制会出现以下情况
#修改表中年龄为20
MariaDB [test]> update yinhe set age=20;
Query OK, 2 rows affected (0.001 sec)
Rows matched: 3 Changed: 2 Warnings: 0
MariaDB [test]> select *from yinhe;
+----+----------+--------+------+
| id | name | gender | age |
+----+----------+--------+------+
| 1 | zhangsan | M | 20 |
| 2 | lisi | W | 20 |
| 3 | wangwu | W | 20 |
+----+----------+--------+------+
3 rows in set (0.000 sec)
因为没有做指定，所以整张表中的所有用户的年龄都被改为了20，属于高危操作
删除数据
#添加一个用户用于删除
MariaDB [test]> insert yinhe values(11,'孙悟空','M',1000);
ERROR 1264 (22003): Out of range value for column 'age' at row 1 #思考为什么会报错
MariaDB [test]> insert yinhe values(11,'孙悟空','M',100);
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> select *from yinhe;
+----+-----------+--------+------+
| id | name | gender | age |
+----+-----------+--------+------+
| 1 | zhangsan | M | 20 |
| 2 | lisi | W | 20 |
| 3 | wangwu | W | 20 |
| 11 | 孙悟空 | M | 100 |
+----+-----------+--------+------+
4 rows in set (0.000 sec)
#删除用户孙悟空
MariaDB [test]> delete from yinhe where id=11;
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> select *from yinhe;
+----+----------+--------+------+
| id | name | gender | age |
+----+----------+--------+------+
| 1 | zhangsan | M | 20 |
| 2 | lisi | W | 20 |
| 3 | wangwu | W | 20 |
+----+----------+--------+------+
3 rows in set (0.000 sec)
温馨提示：
对于我们安全工作从业人员来说，只需要知道查询语句就够了，删除，修改千万千万不要记住，创建记住也就记住了。所以下面的内容才是关键，上面的有个了解就行
DQL语句——数据查询语言
导入数据库
#将数据库hellodb导入本地库中
┌──(root㉿kali)-[/etc/mysql]
└─# mysql < hellodb_innodb.sql
#查看库名
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.001 sec)
#使用hellodb数据库
MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
#查看表名
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
表单操作
#字段显示可以使用别名：
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
#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 |
上面是正经的DBA需要会的知识，下面说说咱们信息安全从业者需要用到的知识点
MariaDB [hellodb]> select *from students order by 5;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.000 sec)
MariaDB [hellodb]> select *from students order by 6;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | 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 |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 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 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.001 sec)
MariaDB [hellodb]> select *from students order by 7;
ERROR 1054 (42S22): Unknown column '7' in 'order clause'
students表中有六个字段，我们可以通过order by指定以第几个字段进行排序，小于7的时候都是正常的，但是超过6就会出现报错，所以我们在进行sql注入的时候可以通过此方式来判断出表中的字段数有多少
where
#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)
多表查询
子查询
子查询 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 function
MariaDB [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 |
+-------+--------------+-----+
5 rows in set (0.012 sec)
#子查询用于更新表
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: 0
MariaDB [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.000 sec)
联合查询
联合查询 Union 实现的条件,多个表的字段数量相同,字段名和数据类型可以不同,但一般数据类型是相同的.
#查询老师表和学生表序号为1的用户信息
MariaDB [hellodb]> select name,age,gender from students where stuid=1 union select name,age,gender from teachers where tid=1;
+-------------+-----+--------+
| name | age | gender |
+-------------+-----+--------+
| Shi Zhongyu | 22 | M |
| Song Jiang | 45 | M |
+-------------+-----+--------+
2 rows in set (0.000 sec)
说了半天正经知识，终于到我们的骚知识了：
上面我们介绍了可以通过order by来获取字段数，大家都知道不是每一个字段都会在页面上显示出来的，有些网页只会显示id字段，有些只会显示name字段，那么我们如何确定显示的字段是表格中的第几个字段呢？并不是所有的表都会将id放在第一个字段，name放在第二个字段的
MariaDB [hellodb]> select 1,2,3,4 union select * from teachers where tid=1;
+---+------------+----+------+
| 1 | 2 | 3 | 4 |
+---+------------+----+------+
| 1 | 2 | 3 | 4 |
| 1 | Song Jiang | 45 | M |
+---+------------+----+------+
2 rows in set (0.001 sec)
通过order by获取到老师表有四个字段，那么我们直接用查询语句获取老师表信息，然后再用我们自己构建的1，2，3，4来联合查询，如果页面显示2那我们就知道第二个字段是可以在网页上进行显示的字段。知道这个有什么用？这是我们后面的正式内容这里容我卖个关子。
and与or
MariaDB [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.000 sec)
#年龄大于30且性别为男
MariaDB [hellodb]> select * from teachers where age>30 and gender='M';
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
+-----+---------------+-----+--------+
2 rows in set (0.000 sec)
#年龄大于三十或性别为男
MariaDB [hellodb]> select * from teachers where age>30 or gender='M';
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
+-----+---------------+-----+--------+
3 rows in set (0.000 sec)
冷知识
这是数据库自带的数据库的使用
#查看当前数据库的库名
MariaDB [hellodb]> select database();
+------------+
| database() |
+------------+
| hellodb |
+------------+
1 row in set (0.000 sec)
#默认库information_schema中的tables表中的table_schema字段的值为各个库的库名
#通过查询默认库information_schema中的tables表，查询hellodb表中的表名
MariaDB [hellodb]> select table_name from information_schema.tables where table_schema='hellodb';
+------------+
| table_name |
+------------+
| toc |
| scores |
| coc |
| students |
| courses |
| teachers |
| classes |
+------------+
7 rows in set (0.000 sec)
#通过查询默认库information_schema中的columns表，查询hellodb表中的teachers表的字段名
MariaDB [hellodb]> select column_name from information_schema.columns where table_schema='hellodb' and taable_name='teachers';
+-------------+
| column_name |
+-------------+
| TID |
| Name |
| Age |
| Gender |
+-------------+
4 rows in set (0.001 sec)
练习
导入hellodb.sql生成数据库
-
在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
-
以ClassID为分组依据，显示每组的平均年龄
-
显示第2题中平均年龄大于30的分组及平均年龄
-
显示以L开头的名字的同学的信息
-
显示TeacherID非空的同学的相关信息
-
以年龄排序后，显示年龄最大的前10位同学的信息
-
查询年龄大于等于20岁，小于等于25岁的同学的信息
-
以ClassID分组，显示每班的同学的人数
-
以Gender分组，显示其年龄之和
-
以ClassID分组，显示其平均年龄大于25的班级
-
以Gender分组，显示各组中年龄大于25的学员的年龄之和
-
显示前5位同学的姓名、班级、老师信息
-
显示年级大于20的同学的名称及班级
-
显示其年龄大于平均年龄的同学的名字