第一:介绍
mysql数据内容的操作主要是:
INSERT实现数据的插入
UPDATE实现数据的更新
DLETE实现数据的删除
SELECT实现数据的查询。
第二:增(insert)
1.插入完整的数据,顺序插入
insert into 表 (列名,列名)values (值,值)
示例:
mysql> select * fromuserinfo;+-----+------+----------+
| nid | name | password |
+-----+------+----------+
| 1 | root | 123 |
+-----+------+----------+
1 row in set (0.00sec)
mysql> insert into userinfo(name,password) values('zzl',123);
Query OK,1 row affected (0.00sec)
mysql> select * fromuserinfo;+-----+------+----------+
| nid | name | password |
+-----+------+----------+
| 1 | root | 123 |
| 4 | zzl | 123 |
+-----+------+----------+
2 rows in set (0.00 sec)
View Code
2.插入多条:
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
示例:
mysql> select * fromuserinfo;+-----+------+----------+
| nid | name | password |
+-----+------+----------+
| 1 | root | 123 |
| 4 | zzl | 123 |
+-----+------+----------+
2 rows in set (0.00sec)
mysql> insert into userinfo(name,password) values('zl',123),('cy',123);
Query OK,2 rows affected (0.01sec)
Records:2 Duplicates: 0 Warnings: 0mysql> select * fromuserinfo;+-----+------+----------+
| nid | name | password |
+-----+------+----------+
| 1 | root | 123 |
| 4 | zzl | 123 |
| 9 | zl | 123 |
| 10 | cy | 123 |
+-----+------+----------+
4 rows in set (0.00 sec)
View Code
3.从其他表中查询后插入:
insert into 表 (列名,列名...) select (列名,列名...) from 表
示例:
mysql> select * fromuserinfo;+-----+------+----------+
| nid | name | password |
+-----+------+----------+
| 1 | root | 123 |
| 4 | zzl | 123 |
| 9 | zl | 123 |
| 10 | cy | 123 |
+-----+------+----------+
4 rows in set (0.00sec)
mysql> select * fromuser;
Emptyset (0.00sec)
mysql> insert into user(name,password) select name,password fromuserinfo;
Query OK,4 rows affected (0.01sec)
Records:4 Duplicates: 0 Warnings: 0mysql>mysql> select * fromuser;+----+------+----------+
| id | name | password |
+----+------+----------+
| 1 | root | 123 |
| 2 | zzl | 123 |
| 3 | zl | 123 |
| 4 | cy | 123 |
+----+------+----------+
4 rows in set (0.00 sec)
View Code
第三:删
delete from 表名 where name='root';
示例:
mysql> select * fromuser;+----+------+----------+
| id | name | password |
+----+------+----------+
| 1 | root | 123 |
| 2 | zzl | 123 |
| 3 | zl | 123 |
| 4 | cy | 123 |
+----+------+----------+
4 rows in set (0.00sec)
mysql>mysql> delete from user where name='root';
Query OK,1 row affected (0.01sec)
mysql> select * fromuser;+----+------+----------+
| id | name | password |
+----+------+----------+
| 2 | zzl | 123 |
| 3 | zl | 123 |
| 4 | cy | 123 |
+----+------+----------+
3 rows in set (0.00 sec)
View Code
第四:改
update 表 set name='cyy' where id=1
示例:
mysql> select * fromuser;+----+------+----------+
| id | name | password |
+----+------+----------+
| 2 | zzl | 123 |
| 3 | zl | 123 |
| 4 | cy | 123 |
+----+------+----------+
3 rows in set (0.01sec)
mysql> update user set name='cyy' where id=1
->;
Query OK,0 rows affected (0.01sec)
Rows matched:0 Changed: 0 Warnings: 0mysql> select * fromuser;+----+------+----------+
| id | name | password |
+----+------+----------+
| 2 | zzl | 123 |
| 3 | zl | 123 |
| 4 | cy | 123 |
+----+------+----------+
3 rows in set (0.00 sec)
View Code
第五:查
一.单表查询
1.单表查询的语法及其优先级:
语法:
SELECT 列名 FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
优先级:
from:找到具体表where:拿着where指定的约束条件,去表中取出一条或者多条记录
group by:将取出的一条或者多条记录进行分组,如果没有group by,则整体作为一组
having:将分组的结果进行having过滤select:执行查询
distinct:去重
order by:将结果按照条件排序
limit:限制取出数据
2.简单查询:
select * from表名;select * from 表名 where id > 3;select id,name as new_name from 表名 where id > 2;
示例:
mysql>desc user;+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| password | varchar(15) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00sec)
mysql> select * fromuser;+----+------+----------+
| id | name | password |
+----+------+----------+
| 2 | zzl | 123 |
| 3 | zl | 123 |
| 4 | cy | 123 |
+----+------+----------+
3 rows in set (0.00sec)
mysql> select * from user where id > 3;+----+------+----------+
| id | name | password |
+----+------+----------+
| 4 | cy | 123 |
+----+------+----------+
1 row in set (0.00sec)
mysql> select id,name as new_name from user where id > 2;+----+----------+
| id | new_name |
+----+----------+
| 3 | zl |
| 4 | cy |
+----+----------+
2 rows in set (0.00 sec)
View Code
3.复杂查询:
3.1:条件查询(where):
介绍:where字句中可以使用以下运算符
1. 比较运算符:> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. like 'egon%'
pattern可以是%或_,
%表示任意多字符
_表示一个字符
5. 逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not
6.is null 是否为空
示例:
运算符:1.单条件查询:
mysql> select name from user where name='zzl';+------+
| name |
+------+
| zzl |
+------+
1 row in set (0.00sec)2.多条件查询:
mysql> select id,name from user where name='zl' and id > 1;+----+------+
| id | name |
+----+------+
| 3 | zl |
+----+------+
1 row in set (0.01sec)
between...and...:1.获取between and之间的,包括3也包括4
mysql> select id,name from user where id between 3 and 4;+----+------+
| id | name |
+----+------+
| 3 | zl |
| 4 | cy |
+----+------+
2 rows in set (0.00sec)
不包括3和4
mysql> select id,name from user where id not between 3 and 4;+----+------+
| id | name |
+----+------+
| 2 | zzl |
+----+------+
1 row in set (0.00sec)in:
id不是2和3的
mysql> select id,name from user where id not in(2,3);+----+------+
| id | name |
+----+------+
| 4 | cy |
+----+------+
1 row in set (0.00sec)
id是2和3的
mysql> select id,name from user where id in(2,3);+----+------+
| id | name |
+----+------+
| 2 | zzl |
| 3 | zl |
+----+------+
2 rows in set (0.00sec)
id是2和3的
mysql> select id,name from user where id=2 or id=3;+----+------+
| id | name |
+----+------+
| 2 | zzl |
| 3 | zl |
+----+------+
2 rows in set (0.00sec)
关键字like模糊匹配:
mysql> select id,name from user where name like 'z%';+----+------+
| id | name |
+----+------+
| 2 | zzl |
| 3 | zl |
+----+------+
2 rows in set (0.00sec)
关键字IS NULL(判断某个字段是否为NULL不能用等号,需要用IS)
mysql> select id,name from user where name is null;
Emptyset (0.01sec)
mysql> select id,name from user where name is not null;+----+------+
| id | name |
+----+------+
| 2 | zzl |
| 3 | zl |
| 4 | cy |
+----+------+
3 rows in set (0.00 sec)
3.2:分组(group by):
怎样用分组:
#1、首先明确一点:分组发生在where之后,即分组是基于where之后得到的记录而进行的
#2、分组指的是:将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等
示例:
mysql> select num fromscore group by num;+-----+
| num |
+-----+
| 9 |
| 11 |
| 22 |
| 43 |
| 55 |
| 65 |
| 66 |
| 67 |
| 68 |
| 77 |
| 87 |
| 88 |
| 99 |
| 100 |
+-----+
14 rows in set (0.00sec)
mysql> select sid,num fromscore group by sid,num;+-----+-----+
| sid | num |
+-----+-----+
| 2 | 9 |
| 5 | 55 |
| 8 | 68 |
| 9 | 99 |
| 11 | 66 |
| 12 | 87 |
| 13 | 99 |
| 15 | 11 |
| 16 | 67 |
| 17 | 100 |
| 19 | 11 |
| 20 | 67 |
| 21 | 100 |
| 23 | 100 |
| 24 | 67 |
| 25 | 100 |
| 27 | 100 |
| 28 | 67 |
| 29 | 88 |
| 31 | 100 |
| 32 | 67 |
| 33 | 88 |
| 35 | 88 |
| 36 | 67 |
| 37 | 22 |
| 39 | 77 |
| 40 | 43 |
| 41 | 87 |
| 43 | 77 |
| 44 | 43 |
| 45 | 87 |
| 47 | 77 |
| 48 | 43 |
| 49 | 87 |
| 52 | 87 |
| 54 | 65 |
| 55 | 65 |
| 56 | 65 |
| 57 | 65 |
+-----+-----+
39 rows in set (0.00sec)
结合where使用
mysql> select sid,num from score where sid > 50group by sid,num;+-----+-----+
| sid | num |
+-----+-----+
| 52 | 87 |
| 54 | 65 |
| 55 | 65 |
| 56 | 65 |
| 57 | 65 |
+-----+-----+
5 rows in set (0.00sec)
结合where 和 order by使用
mysql> select sid,num from score where sid > 50group by sid,num order by sid; 从小到大排序+-----+-----+
| sid | num |
+-----+-----+
| 52 | 87 |
| 54 | 65 |
| 55 | 65 |
| 56 | 65 |
| 57 | 65 |
+-----+-----+
5 rows in set (0.00sec)
mysql> select sid,num from score where sid > 50group by sid,num order by sid desc; 从大到小排序+-----+-----+
| sid | num |
+-----+-----+
| 57 | 65 |
| 56 | 65 |
| 55 | 65 |
| 54 | 65 |
| 52 | 87 |
+-----+-----+
5 rows in set (0.00sec)
与having结合使用:
mysql> select sid,num from score where sid > 50 group by sid,num having sid > 55;+-----+-----+
| sid | num |
+-----+-----+
| 56 | 65 |
| 57 | 65 |
+-----+-----+
2 rows in set (0.01sec)
与having ,order by结合使用:
mysql> select sid,num from score where sid > 50 group by sid,num having sid > 55order by sid desc;+-----+-----+
| sid | num |
+-----+-----+
| 57 | 65 |
| 56 | 65 |
+-----+-----+
2 rows in set (0.00 sec)
View Code
3.3:去重(having):
介绍:
执行优先级从高到低:where > group by > having
1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。
2. Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
示例:
mysql> select sid,num from score where sid>40 group by sid,num having sid > 50;+-----+-----+
| sid | num |
+-----+-----+
| 52 | 87 |
| 54 | 65 |
| 55 | 65 |
| 56 | 65 |
| 57 | 65 |
+-----+-----+
5 rows in set (0.00 sec)
View Code
3.4:排序(order by):
mysql> select sid,num fromscore order by num;+-----+-----+
| sid | num |
+-----+-----+
| 2 | 9 |
| 19 | 11 |
| 15 | 11 |
| 37 | 22 |
| 40 | 43 |
| 48 | 43 |
| 44 | 43 |
| 5 | 55 |
| 54 | 65 |
| 55 | 65 |
| 56 | 65 |
| 57 | 65 |
| 11 | 66 |
| 16 | 67 |
| 20 | 67 |
| 24 | 67 |
| 36 | 67 |
| 28 | 67 |
| 32 | 67 |
| 8 | 68 |
| 39 | 77 |
| 43 | 77 |
| 47 | 77 |
| 12 | 87 |
| 41 | 87 |
| 45 | 87 |
| 49 | 87 |
| 52 | 87 |
| 29 | 88 |
| 33 | 88 |
| 35 | 88 |
| 9 | 99 |
| 13 | 99 |
| 17 | 100 |
| 21 | 100 |
| 23 | 100 |
| 27 | 100 |
| 25 | 100 |
| 31 | 100 |
+-----+-----+
39 rows in set (0.00sec)
mysql> select sid,num fromscore order by num asc;+-----+-----+
| sid | num |
+-----+-----+
| 2 | 9 |
| 19 | 11 |
| 15 | 11 |
| 37 | 22 |
| 40 | 43 |
| 48 | 43 |
| 44 | 43 |
| 5 | 55 |
| 54 | 65 |
| 55 | 65 |
| 56 | 65 |
| 57 | 65 |
| 11 | 66 |
| 16 | 67 |
| 20 | 67 |
| 24 | 67 |
| 36 | 67 |
| 28 | 67 |
| 32 | 67 |
| 8 | 68 |
| 39 | 77 |
| 43 | 77 |
| 47 | 77 |
| 12 | 87 |
| 41 | 87 |
| 45 | 87 |
| 49 | 87 |
| 52 | 87 |
| 29 | 88 |
| 33 | 88 |
| 35 | 88 |
| 9 | 99 |
| 13 | 99 |
| 17 | 100 |
| 21 | 100 |
| 23 | 100 |
| 27 | 100 |
| 25 | 100 |
| 31 | 100 |
+-----+-----+
39 rows in set (0.00sec)
mysql> select sid,num fromscore order by num desc;+-----+-----+
| sid | num |
+-----+-----+
| 31 | 100 |
| 17 | 100 |
| 21 | 100 |
| 23 | 100 |
| 25 | 100 |
| 27 | 100 |
| 9 | 99 |
| 13 | 99 |
| 35 | 88 |
| 33 | 88 |
| 29 | 88 |
| 12 | 87 |
| 41 | 87 |
| 45 | 87 |
| 49 | 87 |
| 52 | 87 |
| 39 | 77 |
| 43 | 77 |
| 47 | 77 |
| 8 | 68 |
| 16 | 67 |
| 32 | 67 |
| 20 | 67 |
| 24 | 67 |
| 28 | 67 |
| 36 | 67 |
| 11 | 66 |
| 57 | 65 |
| 54 | 65 |
| 55 | 65 |
| 56 | 65 |
| 5 | 55 |
| 48 | 43 |
| 40 | 43 |
| 44 | 43 |
| 37 | 22 |
| 15 | 11 |
| 19 | 11 |
| 2 | 9 |
+-----+-----+
39 rows in set (0.00sec)
若果num相同,则按照sid排序
mysql> select sid,num fromscore order by num,sid;+-----+-----+
| sid | num |
+-----+-----+
| 2 | 9 |
| 15 | 11 |
| 19 | 11 |
| 37 | 22 |
| 40 | 43 |
| 44 | 43 |
| 48 | 43 |
| 5 | 55 |
| 54 | 65 |
| 55 | 65 |
| 56 | 65 |
| 57 | 65 |
| 11 | 66 |
| 16 | 67 |
| 20 | 67 |
| 24 | 67 |
| 28 | 67 |
| 32 | 67 |
| 36 | 67 |
| 8 | 68 |
| 39 | 77 |
| 43 | 77 |
| 47 | 77 |
| 12 | 87 |
| 41 | 87 |
| 45 | 87 |
| 49 | 87 |
| 52 | 87 |
| 29 | 88 |
| 33 | 88 |
| 35 | 88 |
| 9 | 99 |
| 13 | 99 |
| 17 | 100 |
| 21 | 100 |
| 23 | 100 |
| 25 | 100 |
| 27 | 100 |
| 31 | 100 |
+-----+-----+
39 rows in set (0.00 sec)
排序
3.5:限制(limit):
#默认初始位置为0
mysql> select sid,num from score order by num,sid limit 3;+-----+-----+
| sid | num |
+-----+-----+
| 2 | 9 |
| 15 | 11 |
| 19 | 11 |
+-----+-----+
3 rows in set (0.01sec)
#从第0开始,即先查询出第一条,然后包含这一条在内往后查3条
mysql> select sid,num from score order by num,sid limit 0,3;+-----+-----+
| sid | num |
+-----+-----+
| 2 | 9 |
| 15 | 11 |
| 19 | 11 |
+-----+-----+
3 rows in set (0.00sec)
#从第3开始,即先查询出第3条,然后包含这一条在内往后查5条
mysql> select sid,num from score order by num,sid limit 3,8;+-----+-----+
| sid | num |
+-----+-----+
| 37 | 22 |
| 40 | 43 |
| 44 | 43 |
| 48 | 43 |
| 5 | 55 |
| 54 | 65 |
| 55 | 65 |
| 56 | 65 |
+-----+-----+
8 rows in set (0.00 sec)
limit
3.6:正则匹配查询(通配符)
mysql> select * from teacher where tname REGEXP '^苍';+-----+------------+
| tid | tname |
+-----+------------+
| 1 | 苍井空老师 |
+-----+------------+
1 row in set (0.01sec)
mysql> select * from teacher where tname REGEXP '师$';+-----+----------------+
| tid | tname |
+-----+----------------+
| 1 | 苍井空老师 |
| 2 | 波多野结衣老师 |
| 3 | 饭岛爱老师 |
| 4 | 小泽玛利亚老师 |
| 5 | 武藤兰老师 |
+-----+----------------+
5 rows in set (0.00sec)
注:也可以用like的,上面已经用过,这里不再举例了
二.多表查询
1.多表连接查询
外链接语法
SELECT 字段名...
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段= 表2.字段;
1.1交叉连接:不使用任何匹配条件:
mysql> select * from class,teacher;+-----+------------+-----+----------------+
| cid | caption | tid | tname |
+-----+------------+-----+----------------+
| 1 | 三年级二班 | 1 | 苍井空老师 |
| 2 | 一年级三班 | 1 | 苍井空老师 |
| 3 | 三年级一班 | 1 | 苍井空老师 |
| 1 | 三年级二班 | 2 | 波多野结衣老师 |
| 2 | 一年级三班 | 2 | 波多野结衣老师 |
| 3 | 三年级一班 | 2 | 波多野结衣老师 |
| 1 | 三年级二班 | 3 | 饭岛爱老师 |
| 2 | 一年级三班 | 3 | 饭岛爱老师 |
| 3 | 三年级一班 | 3 | 饭岛爱老师 |
| 1 | 三年级二班 | 4 | 小泽玛利亚老师 |
| 2 | 一年级三班 | 4 | 小泽玛利亚老师 |
| 3 | 三年级一班 | 4 | 小泽玛利亚老师 |
| 1 | 三年级二班 | 5 | 武藤兰老师 |
| 2 | 一年级三班 | 5 | 武藤兰老师 |
| 3 | 三年级一班 | 5 | 武藤兰老师 |
+-----+------------+-----+----------------+
15 rows in set (0.00 sec)
View Code
1.2 内连接:只连接匹配的行:
mysql> select * from class;+-----+------------+
| cid | caption |
+-----+------------+
| 1 | 三年级二班 |
| 2 | 一年级三班 |
| 3 | 三年级一班 |
+-----+------------+
3 rows in set (0.00sec)
mysql> select * fromstudent;+-----+--------+----------+-------+
| sid | gender | class_id | sname |
+-----+--------+----------+-------+
| 1 | 男 | 1 | 理解 |
| 2 | 女 | 1 | 钢蛋 |
| 3 | 男 | 1 | 张三 |
| 4 | 男 | 1 | 张一 |
| 5 | 女 | 1 | 张二 |
| 6 | 男 | 1 | 张四 |
| 7 | 女 | 2 | 铁锤 |
| 8 | 男 | 2 | 李三 |
| 9 | 男 | 2 | 李一 |
| 10 | 女 | 2 | 李二 |
| 11 | 男 | 2 | 李四 |
| 12 | 女 | 3 | 如花 |
| 13 | 男 | 3 | 刘三 |
| 14 | 男 | 3 | 刘一 |
| 15 | 女 | 3 | 刘二 |
| 16 | 男 | 5 | 刘四 |
+-----+--------+----------+-------+
16 rows in set (0.00sec)
#找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果
#class没有5这个班级,因而student表中关于16这条学生信息没有匹配出来
inner: 内连接:只连接匹配的行
mysql> select * from student inner join class on class.cid =student.class_id;+-----+--------+----------+-------+-----+------------+
| sid | gender | class_id | sname | cid | caption |
+-----+--------+----------+-------+-----+------------+
| 1 | 男 | 1 | 理解 | 1 | 三年级二班 |
| 2 | 女 | 1 | 钢蛋 | 1 | 三年级二班 |
| 3 | 男 | 1 | 张三 | 1 | 三年级二班 |
| 4 | 男 | 1 | 张一 | 1 | 三年级二班 |
| 5 | 女 | 1 | 张二 | 1 | 三年级二班 |
| 6 | 男 | 1 | 张四 | 1 | 三年级二班 |
| 7 | 女 | 2 | 铁锤 | 2 | 一年级三班 |
| 8 | 男 | 2 | 李三 | 2 | 一年级三班 |
| 9 | 男 | 2 | 李一 | 2 | 一年级三班 |
| 10 | 女 | 2 | 李二 | 2 | 一年级三班 |
| 11 | 男 | 2 | 李四 | 2 | 一年级三班 |
| 12 | 女 | 3 | 如花 | 3 | 三年级一班 |
| 13 | 男 | 3 | 刘三 | 3 | 三年级一班 |
| 14 | 男 | 3 | 刘一 | 3 | 三年级一班 |
| 15 | 女 | 3 | 刘二 | 3 | 三年级一班 |
+-----+--------+----------+-------+-----+------------+
15 rows in set (0.00sec)
外链接之左连接:优先显示左表全部记录
以左表为准,即找出所有学生信息,当然包括没有班级的学生
#本质就是:在内连接的基础上增加左边有右边没有的结果
mysql> select * from student left join class on class.cid =student.class_id;+-----+--------+----------+-------+------+------------+
| sid | gender | class_id | sname | cid | caption |
+-----+--------+----------+-------+------+------------+
| 1 | 男 | 1 | 理解 | 1 | 三年级二班 |
| 2 | 女 | 1 | 钢蛋 | 1 | 三年级二班 |
| 3 | 男 | 1 | 张三 | 1 | 三年级二班 |
| 4 | 男 | 1 | 张一 | 1 | 三年级二班 |
| 5 | 女 | 1 | 张二 | 1 | 三年级二班 |
| 6 | 男 | 1 | 张四 | 1 | 三年级二班 |
| 7 | 女 | 2 | 铁锤 | 2 | 一年级三班 |
| 8 | 男 | 2 | 李三 | 2 | 一年级三班 |
| 9 | 男 | 2 | 李一 | 2 | 一年级三班 |
| 10 | 女 | 2 | 李二 | 2 | 一年级三班 |
| 11 | 男 | 2 | 李四 | 2 | 一年级三班 |
| 12 | 女 | 3 | 如花 | 3 | 三年级一班 |
| 13 | 男 | 3 | 刘三 | 3 | 三年级一班 |
| 14 | 男 | 3 | 刘一 | 3 | 三年级一班 |
| 15 | 女 | 3 | 刘二 | 3 | 三年级一班 |
| 16 | 男 | 5 | 刘四 | NULL | NULL |
+-----+--------+----------+-------+------+------------+
16 rows in set (0.00sec)
外链接之右连接:优先显示右表全部记录
显示class表的全部相关记录
mysql> select * from student right join class on class.cid =student.class_id;+------+--------+----------+-------+-----+------------+
| sid | gender | class_id | sname | cid | caption |
+------+--------+----------+-------+-----+------------+
| 1 | 男 | 1 | 理解 | 1 | 三年级二班 |
| 2 | 女 | 1 | 钢蛋 | 1 | 三年级二班 |
| 3 | 男 | 1 | 张三 | 1 | 三年级二班 |
| 4 | 男 | 1 | 张一 | 1 | 三年级二班 |
| 5 | 女 | 1 | 张二 | 1 | 三年级二班 |
| 6 | 男 | 1 | 张四 | 1 | 三年级二班 |
| 7 | 女 | 2 | 铁锤 | 2 | 一年级三班 |
| 8 | 男 | 2 | 李三 | 2 | 一年级三班 |
| 9 | 男 | 2 | 李一 | 2 | 一年级三班 |
| 10 | 女 | 2 | 李二 | 2 | 一年级三班 |
| 11 | 男 | 2 | 李四 | 2 | 一年级三班 |
| 12 | 女 | 3 | 如花 | 3 | 三年级一班 |
| 13 | 男 | 3 | 刘三 | 3 | 三年级一班 |
| 14 | 男 | 3 | 刘一 | 3 | 三年级一班 |
| 15 | 女 | 3 | 刘二 | 3 | 三年级一班 |
+------+--------+----------+-------+-----+------------+
15 rows in set (0.00sec)
全外连接:显示左右两个表全部记录
在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
注意:union与union all的区别:union会去掉相同的纪录
mysql> select * from student left join class on class.cid = student.class_id union select * from student right join class on class.cid =student.class_id;+------+--------+----------+-------+------+------------+
| sid | gender | class_id | sname | cid | caption |
+------+--------+----------+-------+------+------------+
| 1 | 男 | 1 | 理解 | 1 | 三年级二班 |
| 2 | 女 | 1 | 钢蛋 | 1 | 三年级二班 |
| 3 | 男 | 1 | 张三 | 1 | 三年级二班 |
| 4 | 男 | 1 | 张一 | 1 | 三年级二班 |
| 5 | 女 | 1 | 张二 | 1 | 三年级二班 |
| 6 | 男 | 1 | 张四 | 1 | 三年级二班 |
| 7 | 女 | 2 | 铁锤 | 2 | 一年级三班 |
| 8 | 男 | 2 | 李三 | 2 | 一年级三班 |
| 9 | 男 | 2 | 李一 | 2 | 一年级三班 |
| 10 | 女 | 2 | 李二 | 2 | 一年级三班 |
| 11 | 男 | 2 | 李四 | 2 | 一年级三班 |
| 12 | 女 | 3 | 如花 | 3 | 三年级一班 |
| 13 | 男 | 3 | 刘三 | 3 | 三年级一班 |
| 14 | 男 | 3 | 刘一 | 3 | 三年级一班 |
| 15 | 女 | 3 | 刘二 | 3 | 三年级一班 |
| 16 | 男 | 5 | 刘四 | NULL | NULL |
+------+--------+----------+-------+------+------------+
16 rows in set (0.01 sec)
2.符合条件连接查询
以内连接的方式查询student和class表,并且student表中的gender字段是男生的,即找出男生姓名以及所有男生所在的班级
inner:
mysql> select student.sname,class.caption from student inner join class on class.cid = student.class_id where student.gender='男';+-------+------------+
| sname | caption |
+-------+------------+
| 理解 | 三年级二班 |
| 张三 | 三年级二班 |
| 张一 | 三年级二班 |
| 张四 | 三年级二班 |
| 李三 | 一年级三班 |
| 李一 | 一年级三班 |
| 李四 | 一年级三班 |
| 刘三 | 三年级一班 |
| 刘一 | 三年级一班 |
+-------+------------+
9 rows in set (0.00 sec)
3.子查询
#1:子查询是将一个查询语句嵌套在另一个查询语句中。
#2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
#3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4:还可以包含比较运算符:= 、 !=、> 、
3.1 带IN关键字的子查询
mysql> desc student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| gender | char(1) | NO | | NULL | |
| class_id | int(11) | NO | MUL | NULL | |
| sname | varchar(32) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> desc score;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| student_id | int(11) | NO | MUL | NULL | |
| course_id | int(11) | NO | MUL | NULL | |
| num | int(11) | NO | | NULL | |
+------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc course;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| cid | int(11) | NO | PRI | NULL | auto_increment |
| cname | varchar(32) | NO | | NULL | |
| teacher_id | int(11) | NO | MUL | NULL | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc teacher;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| tid | int(11) | NO | PRI | NULL | auto_increment |
| tname | varchar(32) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
查询学过“小泽玛利亚”老师所教的所有课的同学的学号、姓名;
select * from student where sid in (
select student_id from score where score.course_id in (
select cid from teacher left join course on teacher.tid = course.cid where tname = "小泽玛利亚老师"
)
);
3.2 带比较运算符的子查询
查询有课程成绩小于60分的同学的学号、姓名;select sid,sname from student where sid in (select student_id from score where num < 60);
3.3 带EXISTS关键字的子查询
解释:EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询
mysql> select * from student where exists (select * from score where num=100);+-----+--------+----------+-------+
| sid | gender | class_id | sname |
+-----+--------+----------+-------+
| 1 | 男 | 1 | 理解 |
| 2 | 女 | 1 | 钢蛋 |
| 3 | 男 | 1 | 张三 |
| 4 | 男 | 1 | 张一 |
| 5 | 女 | 1 | 张二 |
| 6 | 男 | 1 | 张四 |
| 7 | 女 | 2 | 铁锤 |
| 8 | 男 | 2 | 李三 |
| 9 | 男 | 2 | 李一 |
| 10 | 女 | 2 | 李二 |
| 11 | 男 | 2 | 李四 |
| 12 | 女 | 3 | 如花 |
| 13 | 男 | 3 | 刘三 |
| 14 | 男 | 3 | 刘一 |
| 15 | 女 | 3 | 刘二 |
| 16 | 男 | 5 | 刘四 |
+-----+--------+----------+-------+
16 rows in set (0.00sec)
mysql> select * from student where exists (select * from score where num=0);
Emptyset (0.00 sec)