Linux学习-MySQL之SQL语句(三)

查询语句类型

• 简单查询
• 多表查询
• 子查询

简单查询

select * from tb_name;
select field1,field2... from tb_name;				投影(选择指定字段)
select * from tb_name where qualification;		选择(根据where条件选择满足条件记录)

FROM子句

要查询的关系				表(简单查询)，多个表(多表查询)，其它SELECT语句(子查询)

WHERE子句

布尔关系表达式[=|>|>=|<|<=|<>]
逻辑表达式[and(&&) | or(||) | not ]
between... and ...
like 'char%' 以char开头
	%：表示任意长度任意字符
	_：表示任意一个字符
regexp | rlike 正则表达式
IN
IS NULL | IS NOT NULL

ORDER BY FIELD_NAME ｛ASC | DESC}

字段别名：AS

LIMIT子句： LIMIT[offset,]count —offset（偏移量）

聚合：AVG（平均）MAX（最大）MIN（最小）SUM（和）COUNT（统计个数）

GROUP BY：分组用来做聚合计算

HAVING QUALITATION 对分组结果再进行过滤

SELECT语句执行次序

单表查询

#基础表信息
#---------------------------------创建表
mysql> create table tutors (TID tinyint(4) not null auto_increment primary key,Tname varchar(30),Gender enum('M','F'),age tinyint(4));
Query OK, 0 rows affected (0.01 sec)

mysql> create table student(sid tinyint(4) not null auto_increment primary key,Name varchar(30),Age tinyint(4),Gender enum('M','F'),CID1 tinyint(4),CID2 tinyint(4),TID tinyint(4),CreateTime datetime);
Query OK, 0 rows affected (0.00 sec)
mysql> create table courses(CID tinyint(4) not null auto_increment primary key,Cname varchar(30),TID tinyint(4));
Query OK, 0 rows affected (0.01 sec)
#-------------------------------插入基础数据
mysql> insert into student(Name,Age,Gender,CID1,CID2,TID,CreateTime)values('GuoJing',19,'M',2,7,3,now()),('YangGuo',17,'M',2,3,1,now()),('DingDian',25,'M',6,1,7,now()),('HuFei',31,'M',8,10,5,now()),('HuangRong',16,'F',5,9,9,now()),('YeLingShan',18,'F',8,4,NULL,now()),('ZhangWuJi',20,'M',1,7,NULL,now()),('XuZhu',26,'M',2,4,NULL,now()),('LingHuChong',22,'M',11,NULL,NULL,now()),('YiLin',19,'F',18,NULL,NULL,now());
#通过txt文件导入数据
mysql> load data infile '/tmp/tutors.txt' into table students.tutors fields terminated by ','(Tname,Gender,age);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0
[root@lotus ~]# cat /tmp/tutors.txt
HongQigong,M,93
HuangYaoshi,M,63
Miejueshitai,F,72
OuYangfeng,M,76
YiDeng,M,90
YuCanghai,M,56
Jinlunfawang,M,67
HuYidao,M,42
NingZhongze,M,49
#通过csv导入数据
mysql> load data infile '/tmp/courses.csv' into table students.courses fields terminated by ',';
Query OK, 10 rows affected (0.01 sec)
[root@lotus ~]# cat /tmp/courses.csv
1,Hamagong,2
2,Taijiquan,3
3,Yiyangzhi,6
4,Jinshejianfa,1
5,Qianzhuwandushou,4
6,Qishangquan,5
7,Qiankundanuoyi,7
8,Wanliduxing,8
9,Pixiejianfa,3
10,Jiuyinbaiguzhua,7
#老师表
mysql> desc tutors;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| TID    | tinyint(4)    | NO   | PRI | NULL    | auto_increment |
| Tname  | varchar(30)   | YES  |     | NULL    |                |
| Gender | enum('M','F') | YES  |     | NULL    |                |
| Age    | tinyint(4)    | YES  |     | NULL    |                |
+--------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
#学生表
mysql> desc student;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| sid        | tinyint(4)    | NO   | PRI | NULL    | auto_increment |
| Name       | varchar(30)   | YES  |     | NULL    |                |
| Age        | tinyint(4)    | YES  |     | NULL    |                |
| Gender     | enum('M','F') | YES  |     | NULL    |                |
| CID1       | tinyint(4)    | YES  |     | NULL    |                |
| CID2       | tinyint(4)    | YES  |     | NULL    |                |
| TID        | tinyint(4)    | YES  |     | NULL    |                |
| CreateTime | datetime      | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
#课程表
mysql> desc courses;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| CID   | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| Cname | varchar(30) | YES  |     | NULL    |                |
| TID   | tinyint(4)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
#学生表数据
mysql> select * from student;
+-----+-------------+------+--------+------+------+------+---------------------+
| sid | Name        | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing     |   19 | M      |    2 |    7 |    3 | 2021-06-21 00:00:00 |
|   2 | YangGuo     |   17 | M      |    2 |    3 |    1 | 2021-06-21 00:00:00 |
|   3 | DingDian    |   25 | M      |    6 |    1 |    7 | 2021-06-21 00:00:00 |
|   4 | HuFei       |   31 | M      |    8 |   10 |    5 | 2021-06-21 00:00:00 |
|   5 | GuangRong   |   16 | F      |    5 |    9 |    9 | 2021-06-21 00:00:00 |
|   6 | YueLingshan |   18 | F      |    8 |    4 | NULL | 2021-06-21 00:00:00 |
|   7 | ZhangWuji   |   20 | M      |    1 |    7 | NULL | 2021-06-21 00:00:00 |
|   8 | Xuzhu       |   26 | M      |    2 |    4 | NULL | 2021-06-21 00:00:00 |
|   9 | LingHuchong |   22 | M      |   11 | NULL | NULL | 2021-06-21 00:00:00 |
|  10 | YiLin       |   19 | F      |   18 | NULL | NULL | 2021-06-21 00:00:00 |
+-----+-------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)
#老师表数据
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
+-----+--------------+--------+------+
9 rows in set (0.00 sec)
#课程表数据
mysql> select * from courses;
+-----+------------------+------+
| CID | Cname            | TID  |
+-----+------------------+------+
|   1 | Hamagong         |    2 |
|   2 | Taijiquan        |    3 |
|   3 | Yiyangzhi        |    6 |
|   4 | Jinshejianfa     |    1 |
|   5 | Qianzhuwandushou |    4 |
|   6 | Qishangquan      |    5 |
|   7 | Qiankundanuoyi   |    7 |
|   8 | Wanliduxing      |    8 |
|   9 | Pixiejianfa      |    3 |
|  10 | Jiuyinbaiguzhua  |    7 |
+-----+------------------+------+
10 rows in set (0.00 sec)

布尔表达式

• 【=】
• 【<】
• 【>】
• 【<=】
• 【>=】

逻辑运算符

• AND
• OR
• NOT
• BETWEEN…AND…

#查询年龄大于19的用户
mysql> select * from student where age>19;
+-----+-------------+------+--------+------+------+------+---------------------+
| sid | Name        | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------------+------+--------+------+------+------+---------------------+
|   3 | DingDian    |   25 | M      |    6 |    1 |    7 | 2021-06-21 00:00:00 |
|   4 | HuFei       |   31 | M      |    8 |   10 |    5 | 2021-06-21 00:00:00 |
|   7 | ZhangWuji   |   20 | M      |    1 |    7 | NULL | 2021-06-21 00:00:00 |
|   8 | Xuzhu       |   26 | M      |    2 |    4 | NULL | 2021-06-21 00:00:00 |
|   9 | LingHuchong |   22 | M      |   11 | NULL | NULL | 2021-06-21 00:00:00 |
+-----+-------------+------+--------+------+------+------+---------------------+
5 rows in set (0.00 sec)
#查询年龄小于18并且Gender为'F'
mysql> select * from student where age<18 and Gender='F';
+-----+-----------+------+--------+------+------+------+---------------------+
| sid | Name      | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-----------+------+--------+------+------+------+---------------------+
|   5 | GuangRong |   16 | F      |    5 |    9 |    9 | 2021-06-21 00:00:00 |
+-----+-----------+------+--------+------+------+------+---------------------+
1 row in set (0.00 sec)
mysql> select Name,Age,Gender from student where age>20 or Gender='M';
+-------------+------+--------+
| Name        | Age  | Gender |
+-------------+------+--------+
| GuoJing     |   19 | M      |
| YangGuo     |   17 | M      |
| DingDian    |   25 | M      |
| HuFei       |   31 | M      |
| ZhangWuJi   |   20 | M      |
| XuZhu       |   26 | M      |
| LingHuChong |   22 | M      |
+-------------+------+--------+
7 rows in set (0.00 sec)
#age不大于20
mysql> select Name,Age,Gender from student where not age>20;
+------------+------+--------+
| Name       | Age  | Gender |
+------------+------+--------+
| GuoJing    |   19 | M      |
| YangGuo    |   17 | M      |
| HuangRong  |   16 | F      |
| YeLingShan |   18 | F      |
| ZhangWuJi  |   20 | M      |
| YiLin      |   19 | F      |
+------------+------+--------+
6 rows in set (0.00 sec)
#Gender不为'M'
mysql> select Name,Age,Gender from student where not Gender='M';
+------------+------+--------+
| Name       | Age  | Gender |
+------------+------+--------+
| HuangRong  |   16 | F      |
| YeLingShan |   18 | F      |
| YiLin      |   19 | F      |
+------------+------+--------+
3 rows in set (0.00 sec)

#查询年龄在18至25之间的用户
mysql> select * from student where age between 18 and 25;
+-----+-------------+------+--------+------+------+------+---------------------+
| sid | Name        | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing     |   19 | M      |    2 |    7 |    3 | 2021-06-21 00:00:00 |
|   3 | DingDian    |   25 | M      |    6 |    1 |    7 | 2021-06-21 00:00:00 |
|   6 | YueLingshan |   18 | F      |    8 |    4 | NULL | 2021-06-21 00:00:00 |
|   7 | ZhangWuji   |   20 | M      |    1 |    7 | NULL | 2021-06-21 00:00:00 |
|   9 | LingHuchong |   22 | M      |   11 | NULL | NULL | 2021-06-21 00:00:00 |
|  10 | YiLin       |   19 | F      |   18 | NULL | NULL | 2021-06-21 00:00:00 |
+-----+-------------+------+--------+------+------+------+---------------------+
6 rows in set (0.00 sec)

通配符查找

• %表示任意0个或多个字符
• _表示任意一个字符

#查询Name以Y开头的用户
mysql> select * from student where Name like 'Y%';
+-----+-------------+------+--------+------+------+------+---------------------+
| sid | Name        | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------------+------+--------+------+------+------+---------------------+
|   2 | YangGuo     |   17 | M      |    2 |    3 |    1 | 2021-06-21 00:00:00 |
|   6 | YueLingshan |   18 | F      |    8 |    4 | NULL | 2021-06-21 00:00:00 |
|  10 | YiLin       |   19 | F      |   18 | NULL | NULL | 2021-06-21 00:00:00 |
+-----+-------------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)
#查询以Y开头，且Y后面有4个字符
mysql> select * from student where Name like 'Y____';
+-----+-------+------+--------+------+------+------+---------------------+
| sid | Name  | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------+------+--------+------+------+------+---------------------+
|  10 | YiLin |   19 | F      |   18 | NULL | NULL | 2021-06-21 00:00:00 |
+-----+-------+------+--------+------+------+------+---------------------+
1 row in set (0.00 sec)

基于正则表达式查找

• 使用regex | rlike
• 使用正则表达式查找时，索引就不起作用

#基于正则表达式查找以Y开头的名字信息
mysql> select Name from student where Name regexp '^Y';
+-------------+
| Name        |
+-------------+
| YangGuo     |
| YueLingshan |
| YiLin       |
+-------------+
3 rows in set (0.00 sec)

IN操作符

#通过IN关键字查找集合内的数据
mysql> select * from student where Age in (25,19,18);
+-----+-------------+------+--------+------+------+------+---------------------+
| sid | Name        | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing     |   19 | M      |    2 |    7 |    3 | 2021-06-21 00:00:00 |
|   3 | DingDian    |   25 | M      |    6 |    1 |    7 | 2021-06-21 00:00:00 |
|   6 | YueLingshan |   18 | F      |    8 |    4 | NULL | 2021-06-21 00:00:00 |
|  10 | YiLin       |   19 | F      |   18 | NULL | NULL | 2021-06-21 00:00:00 |
+-----+-------------+------+--------+------+------+------+---------------------+
4 rows in set (0.00 sec)

ISNULL 和IS NOT NULL

#通过IS NULL判断是否为空值
mysql> select * from student where CID2 is null;
+-----+-------------+------+--------+------+------+------+---------------------+
| sid | Name        | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------------+------+--------+------+------+------+---------------------+
|   9 | LingHuchong |   22 | M      |   11 | NULL | NULL | 2021-06-21 00:00:00 |
|  10 | YiLin       |   19 | F      |   18 | NULL | NULL | 2021-06-21 00:00:00 |
+-----+-------------+------+--------+------+------+------+---------------------+
2 rows in set (0.00 sec)
mysql> select Name,Age,Gender from student where CID2 <=> NULL;
+-------------+------+--------+
| Name        | Age  | Gender |
+-------------+------+--------+
| LingHuChong |   22 | M      |
| YiLin       |   19 | F      |
+-------------+------+--------+
2 rows in set (0.00 sec)
#IS NOT NULL判断非空
mysql> select * from student where CID2 is not null;
+-----+-------------+------+--------+------+------+------+---------------------+
| sid | Name        | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing     |   19 | M      |    2 |    7 |    3 | 2021-06-21 00:00:00 |
|   2 | YangGuo     |   17 | M      |    2 |    3 |    1 | 2021-06-21 00:00:00 |
|   3 | DingDian    |   25 | M      |    6 |    1 |    7 | 2021-06-21 00:00:00 |
|   4 | HuFei       |   31 | M      |    8 |   10 |    5 | 2021-06-21 00:00:00 |
|   5 | GuangRong   |   16 | F      |    5 |    9 |    9 | 2021-06-21 00:00:00 |
|   6 | YueLingshan |   18 | F      |    8 |    4 | NULL | 2021-06-21 00:00:00 |
|   7 | ZhangWuji   |   20 | M      |    1 |    7 | NULL | 2021-06-21 00:00:00 |
|   8 | Xuzhu       |   26 | M      |    2 |    4 | NULL | 2021-06-21 00:00:00 |
+-----+-------------+------+--------+------+------+------+---------------------+
8 rows in set (0.00 sec)

order by

#order by 实现排序，默认为升序排序
mysql> select * from student order by Age;
+-----+-------------+------+--------+------+------+------+---------------------+
| sid | Name        | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------------+------+--------+------+------+------+---------------------+
|   5 | GuangRong   |   16 | F      |    5 |    9 |    9 | 2021-06-21 00:00:00 |
|   2 | YangGuo     |   17 | M      |    2 |    3 |    1 | 2021-06-21 00:00:00 |
|   6 | YueLingshan |   18 | F      |    8 |    4 | NULL | 2021-06-21 00:00:00 |
|   1 | GuoJing     |   19 | M      |    2 |    7 |    3 | 2021-06-21 00:00:00 |
|  10 | YiLin       |   19 | F      |   18 | NULL | NULL | 2021-06-21 00:00:00 |
|   7 | ZhangWuji   |   20 | M      |    1 |    7 | NULL | 2021-06-21 00:00:00 |
|   9 | LingHuchong |   22 | M      |   11 | NULL | NULL | 2021-06-21 00:00:00 |
|   3 | DingDian    |   25 | M      |    6 |    1 |    7 | 2021-06-21 00:00:00 |
|   8 | Xuzhu       |   26 | M      |    2 |    4 | NULL | 2021-06-21 00:00:00 |
|   4 | HuFei       |   31 | M      |    8 |   10 |    5 | 2021-06-21 00:00:00 |
+-----+-------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)
#根据年龄降序排序
mysql> select * from student order by Age desc;
+-----+-------------+------+--------+------+------+------+---------------------+
| sid | Name        | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------------+------+--------+------+------+------+---------------------+
|   4 | HuFei       |   31 | M      |    8 |   10 |    5 | 2021-06-21 00:00:00 |
|   8 | Xuzhu       |   26 | M      |    2 |    4 | NULL | 2021-06-21 00:00:00 |
|   3 | DingDian    |   25 | M      |    6 |    1 |    7 | 2021-06-21 00:00:00 |
|   9 | LingHuchong |   22 | M      |   11 | NULL | NULL | 2021-06-21 00:00:00 |
|   7 | ZhangWuji   |   20 | M      |    1 |    7 | NULL | 2021-06-21 00:00:00 |
|   1 | GuoJing     |   19 | M      |    2 |    7 |    3 | 2021-06-21 00:00:00 |
|  10 | YiLin       |   19 | F      |   18 | NULL | NULL | 2021-06-21 00:00:00 |
|   6 | YueLingshan |   18 | F      |    8 |    4 | NULL | 2021-06-21 00:00:00 |
|   2 | YangGuo     |   17 | M      |    2 |    3 |    1 | 2021-06-21 00:00:00 |
|   5 | GuangRong   |   16 | F      |    5 |    9 |    9 | 2021-06-21 00:00:00 |
+-----+-------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)

字段别名AS

#通过AS给字段起别名
mysql> select Name as student_name from student;
+--------------+
| student_name |
+--------------+
| GuoJing      |
| YangGuo      |
| DingDian     |
| HuFei        |
| GuangRong    |
| YueLingshan  |
| ZhangWuji    |
| Xuzhu        |
| LingHuchong  |
| YiLin        |
+--------------+
10 rows in set (0.00 sec)
mysql> select 2+1 as SUM;
+-----+
| SUM |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)

LIMIT子句

#LIMIT显示前两行数据
mysql> select * from student limit 2;
+-----+---------+------+--------+------+------+------+---------------------+
| sid | Name    | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+---------+------+--------+------+------+------+---------------------+
|   1 | GuoJing |   19 | M      |    2 |    7 |    3 | 2021-06-21 00:00:00 |
|   2 | YangGuo |   17 | M      |    2 |    3 |    1 | 2021-06-21 00:00:00 |
+-----+---------+------+--------+------+------+------+---------------------+
2 rows in set (0.00 sec)
#LIMIT[offset,]count,偏移offset个，显示count个
mysql> select * from student limit 2,3;
+-----+-----------+------+--------+------+------+------+---------------------+
| sid | Name      | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-----------+------+--------+------+------+------+---------------------+
|   3 | DingDian  |   25 | M      |    6 |    1 |    7 | 2021-06-21 00:00:00 |
|   4 | HuFei     |   31 | M      |    8 |   10 |    5 | 2021-06-21 00:00:00 |
|   5 | GuangRong |   16 | F      |    5 |    9 |    9 | 2021-06-21 00:00:00 |
+-----+-----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)

聚合函数

#聚合函数使用
mysql> select avg(Age) from student;
+----------+
| avg(Age) |
+----------+
|  21.3000 |
+----------+
1 row in set (0.00 sec)
mysql> select count(Age) from student;
+------------+
| count(Age) |
+------------+
|         10 |
+------------+
1 row in set (0.00 sec)
mysql> select max(Age) from student;
+----------+
| max(Age) |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)

mysql> select min(Age) from student;
+----------+
| min(Age) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)

mysql> select sum(Age) from student;
+----------+
| sum(Age) |
+----------+
|      213 |
+----------+
1 row in set (0.00 sec)
#分组计算平均年龄
mysql> select avg(Age) from student group by Gender;
+----------+
| avg(Age) |
+----------+
|  22.8571 |
|  17.6667 |
+----------+
2 rows in set (0.00 sec)
#判断课程的选课人数
mysql> select count(CID1) as 人数,CID1 from student group by CID1;
+--------+------+
| 人数   | CID1 |
+--------+------+
|      1 |    1 |
|      3 |    2 |
|      1 |    5 |
|      1 |    6 |
|      2 |    8 |
|      1 |   11 |
|      1 |   18 |
+--------+------+
7 rows in set (0.00 sec)
#Having结合Group By 使用，对Group by的结果进行再次过滤
mysql> select count(CID1) as persons,CID1 from student group by CID1 having persons>1;
+---------+------+
| persons | CID1 |
+---------+------+
|       3 |    2 |
|       2 |    8 |
+---------+------+
2 rows in set (0.00 sec)

多表查询

连接：
	交叉连接：笛卡尔乘积
	自然连接：将两张表
	外连接：
		左外连接【...left join ... on ...】
		右外连接【...right join... on ...】
	自连接

#基础表信息
#CID1表示学生选择的第1门课，CID2表示学生选择的第2门课
mysql> select * from student;
+-----+-------------+------+--------+------+------+------+---------------------+
| sid | Name        | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing     |   19 | M      |    2 |    7 |    3 | 2021-06-21 00:00:00 |
|   2 | YangGuo     |   17 | M      |    2 |    3 |    1 | 2021-06-21 00:00:00 |
|   3 | DingDian    |   25 | M      |    6 |    1 |    7 | 2021-06-21 00:00:00 |
|   4 | HuFei       |   31 | M      |    8 |   10 |    5 | 2021-06-21 00:00:00 |
|   5 | GuangRong   |   16 | F      |    5 |    9 |    9 | 2021-06-21 00:00:00 |
|   6 | YueLingshan |   18 | F      |    8 |    4 | NULL | 2021-06-21 00:00:00 |
|   7 | ZhangWuji   |   20 | M      |    1 |    7 | NULL | 2021-06-21 00:00:00 |
|   8 | Xuzhu       |   26 | M      |    2 |    4 | NULL | 2021-06-21 00:00:00 |
|   9 | LingHuchong |   22 | M      |   11 | NULL | NULL | 2021-06-21 00:00:00 |
|  10 | YiLin       |   19 | F      |   18 | NULL | NULL | 2021-06-21 00:00:00 |
+-----+-------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)
#查询学生表中的学生学习的第一门课程即课程名称【自然连接】
mysql> select student.Name,courses.Cname from student,courses where student.CID1=coursess.CID;
+-------------+------------------+
| Name        | Cname            |
+-------------+------------------+
| GuoJing     | Taijiquan        |
| YangGuo     | Taijiquan        |
| DingDian    | Qishangquan      |
| HuFei       | Wanliduxing      |
| GuangRong   | Qianzhuwandushou |
| YueLingshan | Wanliduxing      |
| ZhangWuji   | Hamagong         |
| Xuzhu       | Taijiquan        |
+-------------+------------------+
8 rows in set (0.00 sec)
mysql> select s.Name,c.Cname from student as s,courses as c where s.CID1=c.CID; 
+-------------+------------------+
| Name        | Cname            |
+-------------+------------------+
| GuoJing     | Taijiquan        |
| YangGuo     | Taijiquan        |
| DingDian    | Qishangquan      |
| HuFei       | Wanliduxing      |
| GuangRong   | Qianzhuwandushou |
| YueLingshan | Wanliduxing      |
| ZhangWuji   | Hamagong         |
| Xuzhu       | Taijiquan        |
+-------------+------------------+
8 rows in set (0.00 sec)
#left...join...on,通过on来指定条件
mysql> select s.Name,c.Cname from student as s left join courses as c on s.CID1=c.CID; 
+-------------+------------------+
| Name        | Cname            |
+-------------+------------------+
| GuoJing     | Taijiquan        |
| YangGuo     | Taijiquan        |
| DingDian    | Qishangquan      |
| HuFei       | Wanliduxing      |
| GuangRong   | Qianzhuwandushou |
| YueLingshan | Wanliduxing      |
| ZhangWuji   | Hamagong         |
| Xuzhu       | Taijiquan        |
| LingHuchong | NULL             |
| YiLin       | NULL             |
+-------------+------------------+
10 rows in set (0.00 sec)
mysql> select s.Name,c.Cname from student as s right join courses as c on s.CID1=c.CID; 
+-------------+------------------+
| Name        | Cname            |
+-------------+------------------+
| GuoJing     | Taijiquan        |
| YangGuo     | Taijiquan        |
| DingDian    | Qishangquan      |
| HuFei       | Wanliduxing      |
| GuangRong   | Qianzhuwandushou |
| YueLingshan | Wanliduxing      |
| ZhangWuji   | Hamagong         |
| Xuzhu       | Taijiquan        |
| NULL        | Yiyangzhi        |
| NULL        | Jinshejianfa     |
| NULL        | Qiankundanuoyi   |
| NULL        | Pixiejianfa      |
| NULL        | Jiuyinbaiguzhua  |
+-------------+------------------+
13 rows in set (0.00 sec)
#自连接
mysql> mysql> select c.Name as Student,s.Name as Teacher from student as c,student as s where c.tid=s.sid;
+-----------+-------------+
| Student   | Teacher     |
+-----------+-------------+
| GuoJing   | DingDian    |
| YangGuo   | GuoJing     |
| DingDian  | ZhangWuji   |
| HuFei     | GuangRong   |
| GuangRong | LingHuchong |
+-----------+-------------+
5 rows in set (0.00 sec)

子查询

• 比较操作中使用子查询，子查询只能返回单个值
• IN中使用子查询
• FROM使用子查询

#查询年龄中大于所有同学平均年龄的同学
mysql> select * from student where Age > (select avg(age) from student);
+-----+-------------+------+--------+------+------+------+---------------------+
| sid | Name        | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+-------------+------+--------+------+------+------+---------------------+
|   3 | DingDian    |   25 | M      |    6 |    1 |    7 | 2021-06-21 00:00:00 |
|   4 | HuFei       |   31 | M      |    8 |   10 |    5 | 2021-06-21 00:00:00 |
|   8 | Xuzhu       |   26 | M      |    2 |    4 | NULL | 2021-06-21 00:00:00 |
|   9 | LingHuchong |   22 | M      |   11 | NULL | NULL | 2021-06-21 00:00:00 |
+-----+-------------+------+--------+------+------+------+---------------------+
4 rows in set (0.00 sec)
#From子查询
mysql> select Name,Age from (select Name,Age from student) as t  where t.age >20 ;
+-------------+------+
| Name        | Age  |
+-------------+------+
| DingDian    |   25 |
| HuFei       |   31 |
| Xuzhu       |   26 |
| LingHuchong |   22 |
+-------------+------+
4 rows in set (0.00 sec)
#挑选出courses表中没有被students中的CID2学习的课程的课程名称(注：not in)
	mysql> select Cname from courses where CID not in(select distinct CID2 from student where CID2 is not null);
+------------------+
| Cname            |
+------------------+
| Taijiquan        |
| Qianzhuwandushou |
| Qishangquan      |
| Wanliduxing      |
+------------------+
4 rows in set (0.00 sec)
#挑选出没有教授任何课程的老师
mysql> select Tname from tutors where TID not in (select  distinct TID from courses);
+-------------+
| Tname       |
+-------------+
| NingZhongze |
+-------------+
1 row in set (0.00 sec)
#找出students表中CID1有两个或两个以上同学学习了的同一门课程的课程名称
mysql> select Cname from courses as c, (select count(CID1) as CIDsum,CID1 from student group by CID1 having CIDsum>1) as t where t.CID1=c.CID;
+-------------+
| Cname       |
+-------------+
| Taijiquan   |
| Wanliduxing |
+-------------+
2 rows in set (0.00 sec)

#查询课程表中，学生选择的第一门课中没有选择的课程名称
mysql> select c.Cname,c.CID from courses as c where c.CID not in (select CID1 from studeent);
+-----------------+-----+
| Cname           | CID |
+-----------------+-----+
| Yiyangzhi       |   3 |
| Jinshejianfa    |   4 |
| Qiankundanuoyi  |   7 |
| Pixiejianfa     |   9 |
| Jiuyinbaiguzhua |  10 |
+-----------------+-----+
5 rows in set (0.00 sec)
#显示每一位老师及其所教授的课程：没有教授的课程保持为NULL
mysql> select t.Tname,c.Cname from tutors as t left join courses as c on c.TID=t.TID;
+--------------+------------------+
| Tname        | Cname            |
+--------------+------------------+
| HuangYaoshi  | Hamagong         |
| Miejueshitai | Taijiquan        |
| YuCanghai    | Yiyangzhi        |
| HongQigong   | Jinshejianfa     |
| OuYangfeng   | Qianzhuwandushou |
| YiDeng       | Qishangquan      |
| Jinlunfawang | Qiankundanuoyi   |
| HuYidao      | Wanliduxing      |
| Miejueshitai | Pixiejianfa      |
| Jinlunfawang | Jiuyinbaiguzhua  |
| NingZhongze  | NULL             |
+--------------+------------------+
11 rows in set (0.00 sec)
#显示每一个课程及其相关的老师，没有老师教授的课程将其老师显示为空
mysql> select c.Cname,t.Tname from courses as c left join tutors as t on c.TID=t.TID;  
+------------------+--------------+
| Cname            | Tname        |
+------------------+--------------+
| Hamagong         | HuangYaoshi  |
| Taijiquan        | Miejueshitai |
| Yiyangzhi        | YuCanghai    |
| Jinshejianfa     | HongQigong   |
| Qianzhuwandushou | OuYangfeng   |
| Qishangquan      | YiDeng       |
| Qiankundanuoyi   | Jinlunfawang |
| Wanliduxing      | HuYidao      |
| Pixiejianfa      | Miejueshitai |
| Jiuyinbaiguzhua  | Jinlunfawang |
+------------------+--------------+
#显示每位同学CID1课程的课程名及其讲授了相关课程的老师的名称
mysql>  select m.Cname as CourseName,t.Tname as TeacherName from (select s.CID1,c.Cname,c.TID from student as s,courses as c where s.CID1=c.CID) as m,tutors as t where t.TID=m.TID group by CourseName;
+------------------+--------------+
| CourseName       | TeacherName  |
+------------------+--------------+
| Hamagong         | HuangYaoshi  |
| Qianzhuwandushou | OuYangfeng   |
| Qishangquan      | YiDeng       |
| Taijiquan        | Miejueshitai |
| Wanliduxing      | HuYidao      |
+------------------+--------------+
5 rows in set (0.00 sec)

#查看创建表语句
mysql> show create table student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                           |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `sid` tinyint(4) NOT NULL AUTO_INCREMENT,
  `Name` varchar(30) DEFAULT NULL,
  `Age` tinyint(4) DEFAULT NULL,
  `Gender` enum('M','F') DEFAULT NULL,
  `CID1` tinyint(4) DEFAULT NULL,
  `CID2` tinyint(4) DEFAULT NULL,
  `TID` tinyint(4) DEFAULT NULL,
  `CreateTime` datetime DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=gbk |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

联合查询UNION

mysql> (select Name,Age from student) union (select Tname,Age from tutors);
+--------------+------+
| Name         | Age  |
+--------------+------+
| GuoJing      |   19 |
| YangGuo      |   17 |
| DingDian     |   25 |
| HuFei        |   31 |
| HuangRong    |   16 |
| YeLingShan   |   18 |
| ZhangWuJi    |   20 |
| XuZhu        |   26 |
| LingHuChong  |   22 |
| YiLin        |   19 |
| HongQigong   |   93 |
| HuangYaoshi  |   63 |
| Miejueshitai |   72 |
| OuYangfeng   |   76 |
| YiDeng       |   90 |
| YuCanghai    |   56 |
| Jinlunfawang |   67 |
| HuYidao      |   42 |
| NingZhongze  |   49 |
+--------------+------+
19 rows in set (0.00 sec)

视图— 存储下来的SELECT语句

基于基表的查询结果

mysql> create view sct as select name,cname,tname from student,courses,tutorstors where student.CID1=courses.CID and courses.TID=tutors.TID;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+--------------------+
| Tables_in_students |
+--------------------+
| courses            |
| sct                |
| student            |
| test               |
| test_courses       |
| tutors             |
+--------------------+
6 rows in set (0.00 sec)

mysql> desc sct;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
| cname | varchar(30) | YES  |     | NULL    |       |
| tname | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from sct;
+------------+------------------+--------------+
| name       | cname            | tname        |
+------------+------------------+--------------+
| GuoJing    | Taijiquan        | Miejueshitai |
| YangGuo    | Taijiquan        | Miejueshitai |
| DingDian   | Qishangquan      | YiDeng       |
| HuFei      | Wanliduxing      | HuYidao      |
| HuangRong  | Qianzhuwandushou | OuYangfeng   |
| YeLingShan | Wanliduxing      | HuYidao      |
| ZhangWuJi  | Hamagong         | HuangYaoshi  |
| XuZhu      | Taijiquan        | Miejueshitai |
+------------+------------------+--------------+
8 rows in set (0.01 sec)

MySQL客户端命令

# -e 客户端执行mysql语句
[root@mail ~]# mysql -uroot -p -e 'create database edb';
Enter password: 
[root@mail ~]# mysql -uroot -p -e 'show databases';
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| edb                |
| extmail            |
| filemanager        |
| mydb               |
| mysql              |
| performance_schema |
| students           |
| test               |
| vsftpd             |
+--------------------+
#通过shell脚本动态向student表中插入数据
#!/bin/bash
while true
do
    random_str=$(($RANDOM%100))
    student_name="student$random_str"
    random_CID1=$(($RANDOM%10))
    random_CID2=$(($RANDOM%10))
    random_TID=$(($RANDOM%10))
    gender_str='F'
    if [ $random_str%2 == 0 ]; then
        gender_str='M'
    fi
    sql_info="insert into students.student(Name,Age,Gender,CID1,CID2,TID) values ('$student_name',$random_str,'$gender_str',$random_CID1,$random_CID2,$random_TID);"
    mysql -uroot -p123456 -e "$sql_info" 2>/dev/null
    sleep 2
done

错误

#导入csv文件时，报错
mysql> load data infile '/root/tutors.csv' into table students.tutors;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

解决方法

mysql> show variables like 'secure%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_auth      | ON    |
| secure_file_priv | NULL  |
+------------------+-------+
2 rows in set (0.00 sec)
#在/etc/my.cnf(mysql配置文件中)，[mydqld]下添加以下行,并重新启动mysqld服务
secure_file_priv=''