You are here
马哥 33_03 _MySQL系列之七——单表查询、多表查询和子查询 有大用
DML:
select
insert into
delete
update
select 按需要,按标准,按条件,在对应的关系上挑选对应的字段或对应行的一种命令(一种工具)
select select-list from tb where qualification
查询语句类型:
简单查询(单表查询):
多表查询:
交叉连接: (笛卡尔乘积)(太大)
自然连接: (逐一比较,保留等值)
外连接:
内外连接
左外连接
右外连接
全外连接
自连接: 自己这张表 连接 自己这张表
子查询:也称为嵌套查询
比较操作(如:大于等于号)中使用子查询:子查询只能返回单值
IN():使用子查询
在from中使用子查询: 大多数都可以改为复合查询(多条件查询)
联合查询: union
select * from tb_name; # 星号是通配符,表示所有字段
select field1,field2............. from tb_name; #显示所有行的指定字段;投影
select * from tb_name where qualification; #根据搜索条件(搜索码)(过滤规则)找出符合条件的所有行的所有字段;选择
select [ distinct ] * from tb_name where qualification; # distinct 表示只取不同的值(相同的值只取一次)
FROM子句: (要查询的关系)表,多个表,其它的select语句
where子句: 布尔关系表达式
=,>,>=,<,<=
在做数值比较的时候,数值不能加引号(为什么我这边可以加引号)
在做字符串比较的时候,字符中必须加单引号,双引号或反引号(一般是单引号)
逻辑关系:
AND
OR
NOT
BETWEEN ... AND ...
LIKE ''
通配符:
%: 百分号,任意长度,任意字符
_: 下划线,一个长度,任意字符
REGEXP, RLIKE 正则表达式
IN 在里面
IS NULL 为空
IS NOT NULL 不为空
order by field_name {ASC|DESC} 默认ASC升级
如果数据量大的话,最好存储的时候就排好序
字段别名: AS
LIMIT子句: LIMIT [offset,] Count
聚合: sum(),min(),max(),avg(),count()
GROUP BY:分组
HAVING qualification (对group by 后的结果进行过滤) (having 只能跟group by 一起用)
多表查询
[root@mail ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use jiaowu
Database changed
mysql>
mysql> show tables;
+------------------+
| Tables_in_jiaowu |
+------------------+
| students |
+------------------+
1 row in set (0.00 sec)
mysql>
mysql> select * from students;
+-----+--------------+------+--------+------+------+------+---------------------+
| sid | name | age | gender | cid1 | cid2 | tid | createTime |
+-----+--------------+------+--------+------+------+------+---------------------+
| 1 | GuoJing | 19 | M | 2 | 7 | 3 | 2012-04-06 10:10:10 |
| 2 | YangGuo | 17 | M | 2 | 3 | 1 | 2012-04-06 10:10:10 |
| 3 | DingDian | 25 | M | 6 | 1 | 7 | 2012-04-06 10:00:00 |
| 4 | HuFei | 31 | M | 8 | 10 | 5 | 2012-04-06 10:00:00 |
| 5 | HuangRong | 16 | F | 5 | 9 | 9 | 2012-04-06 10:00:00 |
| 6 | YueLingshang | 18 | F | 8 | 4 | NULL | 2012-04-06 10:10:10 |
| 7 | ZhangWuji | 20 | M | 1 | 7 | NULL | 2012-04-06 10:00:00 |
| 8 | Xuzhu | 26 | M | 2 | 4 | NULL | 2012-04-06 10:00:00 |
| 9 | LiHuchong | 22 | M | 11 | NULL | NULL | 2012-04-06 10:10:10 |
| 10 | YiLin | 19 | F | 18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)
mysql>
mysql> select name,age from students;
+--------------+------+
| name | age |
+--------------+------+
| GuoJing | 19 |
| YangGuo | 17 |
| DingDian | 25 |
| HuFei | 31 |
| HuangRong | 16 |
| YueLingshang | 18 |
| ZhangWuji | 20 |
| Xuzhu | 26 |
| LiHuchong | 22 |
| YiLin | 19 |
+--------------+------+
10 rows in set (0.00 sec)
mysql>
mysql> select * from students where age>=20;
+-----+-----------+------+--------+------+------+------+---------------------+
| sid | name | age | gender | cid1 | cid2 | tid | createTime |
+-----+-----------+------+--------+------+------+------+---------------------+
| 3 | DingDian | 25 | M | 6 | 1 | 7 | 2012-04-06 10:00:00 |
| 4 | HuFei | 31 | M | 8 | 10 | 5 | 2012-04-06 10:00:00 |
| 7 | ZhangWuji | 20 | M | 1 | 7 | NULL | 2012-04-06 10:00:00 |
| 8 | Xuzhu | 26 | M | 2 | 4 | NULL | 2012-04-06 10:00:00 |
| 9 | LiHuchong | 22 | M | 11 | NULL | NULL | 2012-04-06 10:10:10 |
+-----+-----------+------+--------+------+------+------+---------------------+
5 rows in set (0.00 sec)
mysql>
mysql> select name,age from students where age>=20;
+-----------+------+
| name | age |
+-----------+------+
| DingDian | 25 |
| HuFei | 31 |
| ZhangWuji | 20 |
| Xuzhu | 26 |
| LiHuchong | 22 |
+-----------+------+
5 rows in set (0.00 sec)
mysql>
mysql> select name,age from students where gender='M';
+-----------+------+
| name | age |
+-----------+------+
| GuoJing | 19 |
| YangGuo | 17 |
| DingDian | 25 |
| HuFei | 31 |
| ZhangWuji | 20 |
| Xuzhu | 26 |
| LiHuchong | 22 |
+-----------+------+
7 rows in set (0.02 sec)
mysql>
mysql> select name,age from students where age>='20';
+-----------+------+
| name | age |
+-----------+------+
| DingDian | 25 |
| HuFei | 31 |
| ZhangWuji | 20 |
| Xuzhu | 26 |
| LiHuchong | 22 |
+-----------+------+
5 rows in set (0.00 sec)
mysql>
mysql> select gender from students;
+--------+
| gender |
+--------+
| M |
| M |
| M |
| M |
| F |
| F |
| M |
| M |
| M |
| F |
+--------+
10 rows in set (0.00 sec)
mysql>
<=> 就算有空值,也能进行正确比较的,它也是等于号
[root@mail ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use jiaowu
Database changed
mysql> select name,age from students where age+1>'20'; #算术运算后(算术运算表达式的方式)作为判断条件;;这种算术运算表达式的方式无法有效的使用索引
+-----------+------+
| name | age |
+-----------+------+
| DingDian | 25 |
| HuFei | 31 |
| ZhangWuji | 20 |
| Xuzhu | 26 |
| LiHuchong | 22 |
+-----------+------+
5 rows in set (0.00 sec)
mysql>
mysql> select name from students where age>20 and gender='M';
+-----------+
| name |
+-----------+
| DingDian |
| HuFei |
| Xuzhu |
| LiHuchong |
+-----------+
4 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where age>20 and gender='M';
+-----------+------+--------+
| name | age | gender |
+-----------+------+--------+
| DingDian | 25 | M |
| HuFei | 31 | M |
| Xuzhu | 26 | M |
| LiHuchong | 22 | M |
+-----------+------+--------+
4 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students 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 |
| LiHuchong | 22 | M |
+-----------+------+--------+
7 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where not age>20 ;
+--------------+------+--------+
| name | age | gender |
+--------------+------+--------+
| GuoJing | 19 | M |
| YangGuo | 17 | M |
| HuangRong | 16 | F |
| YueLingshang | 18 | F |
| ZhangWuji | 20 | M |
| YiLin | 19 | F |
+--------------+------+--------+
6 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where not age>20 and not gender='M' ;
+--------------+------+--------+
| name | age | gender |
+--------------+------+--------+
| HuangRong | 16 | F |
| YueLingshang | 18 | F |
| YiLin | 19 | F |
+--------------+------+--------+
3 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where not (age>20 or gender='M') ; # 同样是 年龄不大于20 且 性别不为男性
+--------------+------+--------+
| name | age | gender |
+--------------+------+--------+
| HuangRong | 16 | F |
| YueLingshang | 18 | F |
| YiLin | 19 | F |
+--------------+------+--------+
3 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where age>=20 and age<=25; #年龄在20-25之间
+-----------+------+--------+
| name | age | gender |
+-----------+------+--------+
| DingDian | 25 | M |
| ZhangWuji | 20 | M |
| LiHuchong | 22 | M |
+-----------+------+--------+
3 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where age between 20 and 25; #同样年龄在20-25之间
+-----------+------+--------+
| name | age | gender |
+-----------+------+--------+
| DingDian | 25 | M |
| ZhangWuji | 20 | M |
| LiHuchong | 22 | M |
+-----------+------+--------+
3 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where name like 'Y%'; # Y开头的
+--------------+------+--------+
| name | age | gender |
+--------------+------+--------+
| YangGuo | 17 | M |
| YueLingshang | 18 | F |
| YiLin | 19 | F |
+--------------+------+--------+
3 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where name like 'Y____'; # Y后面跟四个字符的
+-------+------+--------+
| name | age | gender |
+-------+------+--------+
| YiLin | 19 | F |
+-------+------+--------+
1 row in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where name like '%ing%'; # 包含 ing 的
+--------------+------+--------+
| name | age | gender |
+--------------+------+--------+
| GuoJing | 19 | M |
| DingDian | 25 | M |
| YueLingshang | 18 | F |
+--------------+------+--------+
3 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where name like 'M%' or name like 'N%' or name like 'Y%';
# M开头 或 N开头 或 Y开头
+--------------+------+--------+
| name | age | gender |
+--------------+------+--------+
| YangGuo | 17 | M |
| YueLingshang | 18 | F |
| YiLin | 19 | F |
+--------------+------+--------+
3 rows in set (0.00 sec)
mysql>
REGEXP 或 RLIKE:查询条件支持正则表达式
使用正则表达式作模式匹配的时候,索引不好用了
NOT 或 !
AND 或 &&
OR 或 ||
XOR : 表示异或 (也叫半加运算;;; 如果a、b两个值不相同，则异或结果为1。如果a、b两个值相同，异或结果为0。)
mysql> select name,age,gender from students where name rlike '^[MNY].*$'; # 也是 M 或 N 或 Y 开头的
+--------------+------+--------+
| name | age | gender |
+--------------+------+--------+
| YangGuo | 17 | M |
| YueLingshang | 18 | F |
| YiLin | 19 | F |
+--------------+------+--------+
3 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where name rlike '^[XY].*$'; # 也是 X 或 Y 开头的
+--------------+------+--------+
| name | age | gender |
+--------------+------+--------+
| YangGuo | 17 | M |
| YueLingshang | 18 | F |
| Xuzhu | 26 | M |
| YiLin | 19 | F |
+--------------+------+--------+
4 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where age=20 or age=18 or age=25; # 年龄为 20或18或25
+--------------+------+--------+
| name | age | gender |
+--------------+------+--------+
| DingDian | 25 | M |
| YueLingshang | 18 | F |
| ZhangWuji | 20 | M |
+--------------+------+--------+
3 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where age in (18,20,25); #同样的年龄在18或20或25
+--------------+------+--------+
| name | age | gender |
+--------------+------+--------+
| DingDian | 25 | M |
| YueLingshang | 18 | F |
| ZhangWuji | 20 | M |
+--------------+------+--------+
3 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where cid2=null; #判断为空,不能这样写
Empty set (0.00 sec)
mysql> select name,age,gender from students where cid2 is null; #判断为空,要这样写
+-----------+------+--------+
| name | age | gender |
+-----------+------+--------+
| LiHuchong | 22 | M |
| YiLin | 19 | F |
+-----------+------+--------+
2 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where cid2 is not null; #判断不为空
+--------------+------+--------+
| name | age | gender |
+--------------+------+--------+
| GuoJing | 19 | M |
| YangGuo | 17 | M |
| DingDian | 25 | M |
| HuFei | 31 | M |
| HuangRong | 16 | F |
| YueLingshang | 18 | F |
| ZhangWuji | 20 | M |
| Xuzhu | 26 | M |
+--------------+------+--------+
8 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where cid2 is not null order by name; # 排序 (默认升序)
+--------------+------+--------+
| name | age | gender |
+--------------+------+--------+
| DingDian | 25 | M |
| GuoJing | 19 | M |
| HuangRong | 16 | F |
| HuFei | 31 | M |
| Xuzhu | 26 | M |
| YangGuo | 17 | M |
| YueLingshang | 18 | F |
| ZhangWuji | 20 | M |
+--------------+------+--------+
8 rows in set (0.00 sec)
mysql>
mysql> select name,age,gender from students where cid2 is not null order by name desc; # 降序
+--------------+------+--------+
| name | age | gender |
+--------------+------+--------+
| ZhangWuji | 20 | M |
| YueLingshang | 18 | F |
| YangGuo | 17 | M |
| Xuzhu | 26 | M |
| HuFei | 31 | M |
| HuangRong | 16 | F |
| GuoJing | 19 | M |
| DingDian | 25 | M |
+--------------+------+--------+
8 rows in set (0.00 sec)
mysql>
mysql> select name as student_name from students; # 字段重命名,取别名,,表其实也可以取别名
+--------------+
| student_name |
+--------------+
| GuoJing |
| YangGuo |
| DingDian |
| HuFei |
| HuangRong |
| YueLingshang |
| ZhangWuji |
| Xuzhu |
| LiHuchong |
| YiLin |
+--------------+
10 rows in set (0.00 sec)
mysql>
mysql> select 2+1;
+-----+
| 2+1 |
+-----+
| 3 |
+-----+
1 row in set (0.00 sec)
mysql> select 2+1 as sum; # sum 为别名
+-----+
| sum |
+-----+
| 3 |
+-----+
1 row in set (0.00 sec)
mysql>
mysql> select name from students limit 2; # limit 只显示前几个
+---------+
| name |
+---------+
| GuoJing |
| YangGuo |
+---------+
2 rows in set (0.00 sec)
mysql>
mysql> select name from students limit 2,3; # 从第1个开始(第一个就是第0个),取3个值
+-----------+
| name |
+-----------+
| DingDian |
| HuFei |
| HuangRong |
+-----------+
3 rows in set (0.00 sec)
mysql>
mysql> select avg(age) from students; #求平均年龄
+----------+
| avg(age) |
+----------+
| 21.3000 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> select max(age) from students; #得到最大年龄
+----------+
| max(age) |
+----------+
| 31 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> select min(age) from students; #得到最小年龄
+----------+
| min(age) |
+----------+
| 16 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> select sum(age) from students; #得到年龄之和
+----------+
| sum(age) |
+----------+
| 213 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> select count(age) from students; #得到多少个年龄(有多少个条目)
+------------+
| count(age) |
+------------+
| 10 |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> select avg(age) from students where gender='M'; # 男人平均年龄
+----------+
| avg(age) |
+----------+
| 22.8571 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> select avg(age) from students where gender='F'; # 女人平均年龄
+----------+
| avg(age) |
+----------+
| 17.6667 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> select age,gender from students;
+------+--------+
| age | gender |
+------+--------+
| 19 | M |
| 17 | M |
| 25 | M |
| 31 | M |
| 16 | F |
| 18 | F |
| 20 | M |
| 26 | M |
| 22 | M |
| 19 | F |
+------+--------+
10 rows in set (0.00 sec)
mysql>
mysql> select age,gender from students group by gender; # 只显示男的第一个,女的第一个
+------+--------+
| age | gender |
+------+--------+
| 19 | M |
| 16 | F |
+------+--------+
2 rows in set (0.00 sec)
mysql>
mysql> select avg(age) from students group by gender; #分组后平均年龄
+----------+
| avg(age) |
+----------+
| 22.8571 |
| 17.6667 |
+----------+
2 rows in set (0.00 sec)
mysql>
mysql> select avg(age),gender from students group by gender;
+----------+--------+
| avg(age) | gender |
+----------+--------+
| 22.8571 | M |
| 17.6667 | F |
+----------+--------+
2 rows in set (0.00 sec)
mysql>
mysql> select count(cid1),cid1 from students group by cid1; #分组后个数
+-------------+------+
| count(cid1) | cid1 |
+-------------+------+
| 1 | 1 |
| 3 | 2 |
| 1 | 5 |
| 1 | 6 |
| 2 | 8 |
| 1 | 11 |
| 1 | 18 |
+-------------+------+
7 rows in set (0.00 sec)
mysql>
mysql> select count(cid1) as persons,cid1 from students group by cid1; #再取个别名
+---------+------+
| persons | cid1 |
+---------+------+
| 1 | 1 |
| 3 | 2 |
| 1 | 5 |
| 1 | 6 |
| 2 | 8 |
| 1 | 11 |
| 1 | 18 |
+---------+------+
7 rows in set (0.00 sec)
mysql>
mysql> select count(cid1) as persons,cid1 from students group by cid1 having persons>=2;
# 得到 选课的个数和大于等于2的课程
+---------+------+
| persons | cid1 |
+---------+------+
| 3 | 2 |
| 2 | 8 |
+---------+------+
2 rows in set (0.00 sec)
mysql>
mysql> help select
Name: 'SELECT'
Description:
Syntax:
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
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}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
SELECT is used to retrieve rows selected from one or more tables, and
can include UNION statements and subqueries. See [HELP UNION], and
http://dev.mysql.com/doc/refman/5.5/en/subqueries.html.
The most commonly used clauses of SELECT statements are these:
o Each select_expr indicates a column that you want to retrieve. There
must be at least one select_expr.
o table_references indicates the table or tables from which to retrieve
rows. Its syntax is described in [HELP JOIN].
o The WHERE clause, if given, indicates the condition or conditions
that rows must satisfy to be selected. where_condition is an
expression that evaluates to true for each row to be selected. The
statement selects all rows if there is no WHERE clause.
In the WHERE expression, you can use any of the functions and
operators that MySQL supports, except for aggregate (summary)
functions. See
http://dev.mysql.com/doc/refman/5.5/en/expressions.html, and
http://dev.mysql.com/doc/refman/5.5/en/functions.html.
SELECT can also be used to retrieve rows computed without reference to
any table.
URL: http://dev.mysql.com/doc/refman/5.5/en/select.html
mysql>
mysql> select * from courses;
+-----+------------------+------+
| cid | cname | tid |
+-----+------------------+------+
| 1 | Hamagong | 2 |
| 2 | TiaJiquan | 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)
mysql>
mysql> select * from students,courses; 笛卡尔乘积,太多数据
mysql> select * from students,courses where students.cid1=courses.cid;
+-----+--------------+------+--------+------+------+------+---------------------+-----+------------------+------+
| sid | name | age | gender | cid1 | cid2 | tid | createTime | cid | cname | tid |
+-----+--------------+------+--------+------+------+------+---------------------+-----+------------------+------+
| 1 | GuoJing | 19 | M | 2 | 7 | 3 | 2012-04-06 10:10:10 | 2 | TiaJiquan | 3 |
| 2 | YangGuo | 17 | M | 2 | 3 | 1 | 2012-04-06 10:10:10 | 2 | TiaJiquan | 3 |
| 3 | DingDian | 25 | M | 6 | 1 | 7 | 2012-04-06 10:00:00 | 6 | Qishangquan | 5 |
| 4 | HuFei | 31 | M | 8 | 10 | 5 | 2012-04-06 10:00:00 | 8 | Wanliduxing | 8 |
| 5 | HuangRong | 16 | F | 5 | 9 | 9 | 2012-04-06 10:00:00 | 5 | Qianzhuwandushou | 4 |
| 6 | YueLingshang | 18 | F | 8 | 4 | NULL | 2012-04-06 10:10:10 | 8 | Wanliduxing | 8 |
| 7 | ZhangWuji | 20 | M | 1 | 7 | NULL | 2012-04-06 10:00:00 | 1 | Hamagong | 2 |
| 8 | Xuzhu | 26 | M | 2 | 4 | NULL | 2012-04-06 10:00:00 | 2 | TiaJiquan | 3 |
+-----+--------------+------+--------+------+------+------+---------------------+-----+------------------+------+
8 rows in set (0.00 sec)
mysql>
mysql> select name,cname from students,courses where students.cid1=courses.cid;
+--------------+------------------+
| name | cname |
+--------------+------------------+
| GuoJing | TiaJiquan |
| YangGuo | TiaJiquan |
| DingDian | Qishangquan |
| HuFei | Wanliduxing |
| HuangRong | Qianzhuwandushou |
| YueLingshang | Wanliduxing |
| ZhangWuji | Hamagong |
| Xuzhu | TiaJiquan |
+--------------+------------------+
8 rows in set (0.00 sec)
mysql>
mysql> select students.name,courses.cname from students,courses where students.cid1=courses.cid;
+--------------+------------------+
| name | cname |
+--------------+------------------+
| GuoJing | TiaJiquan |
| YangGuo | TiaJiquan |
| DingDian | Qishangquan |
| HuFei | Wanliduxing |
| HuangRong | Qianzhuwandushou |
| YueLingshang | Wanliduxing |
| ZhangWuji | Hamagong |
| Xuzhu | TiaJiquan |
+--------------+------------------+
8 rows in set (0.00 sec)
mysql>
mysql> select s.name,c.cname from students as s ,courses as c where s.cid1=c.cid; #表别名
+--------------+------------------+
| name | cname |
+--------------+------------------+
| GuoJing | TiaJiquan |
| YangGuo | TiaJiquan |
| DingDian | Qishangquan |
| HuFei | Wanliduxing |
| HuangRong | Qianzhuwandushou |
| YueLingshang | Wanliduxing |
| ZhangWuji | Hamagong |
| Xuzhu | TiaJiquan |
+--------------+------------------+
8 rows in set (0.00 sec)
mysql>
mysql> select s.name,c.cname from students as s left join courses as c on s.cid1=c.cid; #左外连接
+--------------+------------------+
| name | cname |
+--------------+------------------+
| GuoJing | TiaJiquan |
| YangGuo | TiaJiquan |
| DingDian | Qishangquan |
| HuFei | Wanliduxing |
| HuangRong | Qianzhuwandushou |
| YueLingshang | Wanliduxing |
| ZhangWuji | Hamagong |
| Xuzhu | TiaJiquan |
| LiHuchong | NULL |
| YiLin | NULL |
+--------------+------------------+
10 rows in set (0.00 sec)
mysql>
mysql> select s.name,c.cname from students as s right join courses as c on s.cid1=c.cid; # 右外连接
+--------------+------------------+
| name | cname |
+--------------+------------------+
| ZhangWuji | Hamagong |
| GuoJing | TiaJiquan |
| YangGuo | TiaJiquan |
| Xuzhu | TiaJiquan |
| NULL | Yiyangzhi |
| NULL | Jinshejianfa |
| HuangRong | Qianzhuwandushou |
| DingDian | Qishangquan |
| NULL | Qiankundanuoyi |
| HuFei | Wanliduxing |
| YueLingshang | Wanliduxing |
| NULL | Pixiejianfa |
| NULL | Jiuyinbaiguzhua |
+--------------+------------------+
13 rows in set (0.00 sec)
mysql>
mysql> select s.name as xusheng_name,t.name as teacher_name from students as s,students as t where s.sid=.t.tid; #自连接 自己连接自己
+--------------+--------------+
| xusheng_name | teacher_name |
+--------------+--------------+
| DingDian | GuoJing |
| GuoJing | YangGuo |
| ZhangWuji | DingDian |
| HuangRong | HuFei |
| LiHuchong | HuangRong |
+--------------+--------------+
5 rows in set (0.00 sec)
mysql>
mysql> select name from students where age > (select avg(age) from students); #大于平均年龄的人
+-----------+
| name |
+-----------+
| DingDian |
| HuFei |
| Xuzhu |
| LiHuchong |
+-----------+
4 rows in set (0.00 sec)
mysql>
mysql> select name from students where age > (select age from tutors); # 子查询只能返回单值
ERROR 1242 (21000): Subquery returns more than 1 row
mysql>
mysql> select name from students where age in (select age from tutors); #子查询可用IN
Empty set (0.00 sec)
mysql>
mysql> select name,age from (select name,age from students) as t where t.age>=20; # from中使用子查询 +-----------+------+
| name | age |
+-----------+------+
| DingDian | 25 |
| HuFei | 31 |
| ZhangWuji | 20 |
| Xuzhu | 26 |
| LiHuchong | 22 |
+-----------+------+
5 rows in set (0.00 sec)
mysql>
mysql> select name,age from students union select tname,age from tutors; # 联合查询,为表清晰思路,最好用括号
像这样 ( select name,age from students ) union ( select tname,age from tutors );
+--------------+------+
| name | age |
+--------------+------+
| GuoJing | 19 |
| YangGuo | 17 |
| DingDian | 25 |
| HuFei | 31 |
| HuangRong | 16 |
| YueLingshang | 18 |
| ZhangWuji | 20 |
| Xuzhu | 26 |
| LiHuchong | 22 |
| YiLin | 19 |
| HongQigong | 93 |
| HuangYaoshi | 63 |
| Miejueshitai | 72 |
| OuYangfend | 76 |
| YiDeng | 90 |
| YuCanghai | 56 |
| Jinlunfawang | 67 |
| HuYidao | 42 |
| NingZhongze | 49 |
+--------------+------+
19 rows in set (0.00 sec)
mysql>
找出 cid2 中没有被学生选修的课程
