mysql数据库内容相关操作
第一：介绍
mysql数据内容的操作主要是：
INSERT实现数据的插入
UPDATE实现数据的更新
DLETE实现数据的删除
SELECT实现数据的查询。
第二：增（insert）
1.插入完整的数据，顺序插入
insert
into 表 (列名,列名) values (值,值)示例：
mysql> select * from userinfo; +-----+------+----------+ | nid | name | password | +-----+------+----------+ | 1 | root | 123 | +-----+------+----------+ 1 row in set (0.00 sec) mysql> insert into userinfo(name,password) values('zzl',123); Query OK, 1 row affected (0.00 sec) mysql> select * from userinfo; +-----+------+----------+ | nid | name | password | +-----+------+----------+ | 1 | root | 123 | | 4 | zzl | 123 | +-----+------+----------+ 2 rows in set (0.00 sec)
2.插入多条：
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
示例：
mysql> select * from userinfo; +-----+------+----------+ | nid | name | password | +-----+------+----------+ | 1 | root | 123 | | 4 | zzl | 123 | +-----+------+----------+ 2 rows in set (0.00 sec) mysql> insert into userinfo(name,password) values('zl',123),('cy',123); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from userinfo; +-----+------+----------+ | nid | name | password | +-----+------+----------+ | 1 | root | 123 | | 4 | zzl | 123 | | 9 | zl | 123 | | 10 | cy | 123 | +-----+------+----------+ 4 rows in set (0.00 sec)
3.从其他表中查询后插入：
insert into 表 (列名,列名...) select (列名,列名...) from 表
示例：
mysql> select * from userinfo; +-----+------+----------+ | nid | name | password | +-----+------+----------+ | 1 | root | 123 | | 4 | zzl | 123 | | 9 | zl | 123 | | 10 | cy | 123 | +-----+------+----------+ 4 rows in set (0.00 sec) mysql> select * from user; Empty set (0.00 sec) mysql> insert into user(name,password) select name,password from userinfo; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> mysql> select * from user; +----+------+----------+ | id | name | password | +----+------+----------+ | 1 | root | 123 | | 2 | zzl | 123 | | 3 | zl | 123 | | 4 | cy | 123 | +----+------+----------+ 4 rows in set (0.00 sec)
第三：删
delete from 表名 where name='root';
示例：
mysql> select * from user; +----+------+----------+ | id | name | password | +----+------+----------+ | 1 | root | 123 | | 2 | zzl | 123 | | 3 | zl | 123 | | 4 | cy | 123 | +----+------+----------+ 4 rows in set (0.00 sec) mysql> mysql> delete from user where name='root'; Query OK, 1 row affected (0.01 sec) mysql> select * from user; +----+------+----------+ | id | name | password | +----+------+----------+ | 2 | zzl | 123 | | 3 | zl | 123 | | 4 | cy | 123 | +----+------+----------+ 3 rows in set (0.00 sec)
第四：改
update 表 set name='cyy' where id=1
示例：
mysql> select * from user; +----+------+----------+ | id | name | password | +----+------+----------+ | 2 | zzl | 123 | | 3 | zl | 123 | | 4 | cy | 123 | +----+------+----------+ 3 rows in set (0.01 sec) mysql> update user set name='cyy' where id=1 -> ; Query OK, 0 rows affected (0.01 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> select * from user; +----+------+----------+ | id | name | password | +----+------+----------+ | 2 | zzl | 123 | | 3 | zl | 123 | | 4 | cy | 123 | +----+------+----------+ 3 rows in set (0.00 sec)
第五：查
一.单表查询
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.00 sec) mysql> select * from user; +----+------+----------+ | id | name | password | +----+------+----------+ | 2 | zzl | 123 | | 3 | zl | 123 | | 4 | cy | 123 | +----+------+----------+ 3 rows in set (0.00 sec) mysql> select * from user where id > 3; +----+------+----------+ | id | name | password | +----+------+----------+ | 4 | cy | 123 | +----+------+----------+ 1 row in set (0.00 sec) 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)
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.00 sec) 2.多条件查询： mysql> select id,name from user where name='zl' and id > 1; +----+------+ | id | name | +----+------+ | 3 | zl | +----+------+ 1 row in set (0.01 sec) 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.00 sec) 不包括3和4 mysql> select id,name from user where id not between 3 and 4; +----+------+ | id | name | +----+------+ | 2 | zzl | +----+------+ 1 row in set (0.00 sec) 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.00 sec) 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.00 sec) 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.00 sec) 关键字like模糊匹配： mysql> select id,name from user where name like 'z%'; +----+------+ | id | name | +----+------+ | 2 | zzl | | 3 | zl | +----+------+ 2 rows in set (0.00 sec) 关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS) mysql> select id,name from user where name is null; Empty set (0.01 sec) 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 from score group by num; +-----+ | num | +-----+ | 9 | | 11 | | 22 | | 43 | | 55 | | 65 | | 66 | | 67 | | 68 | | 77 | | 87 | | 88 | | 99 | | 100 | +-----+ 14 rows in set (0.00 sec) mysql> select sid,num from score 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.00 sec) 结合where使用 mysql> select sid,num from score where sid > 50 group by sid,num; +-----+-----+ | sid | num | +-----+-----+ | 52 | 87 | | 54 | 65 | | 55 | 65 | | 56 | 65 | | 57 | 65 | +-----+-----+ 5 rows in set (0.00 sec) 结合where 和 order by使用 mysql> select sid,num from score where sid > 50 group by sid,num order by sid; 从小到大排序 +-----+-----+ | sid | num | +-----+-----+ | 52 | 87 | | 54 | 65 | | 55 | 65 | | 56 | 65 | | 57 | 65 | +-----+-----+ 5 rows in set (0.00 sec) mysql> select sid,num from score where sid > 50 group by sid,num order by sid desc; 从大到小排序 +-----+-----+ | sid | num | +-----+-----+ | 57 | 65 | | 56 | 65 | | 55 | 65 | | 54 | 65 | | 52 | 87 | +-----+-----+ 5 rows in set (0.00 sec) 与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.01 sec) 与having ，order by结合使用： mysql> select sid,num from score where sid > 50 group by sid,num having sid > 55 order by sid desc; +-----+-----+ | sid | num | +-----+-----+ | 57 | 65 | | 56 | 65 | +-----+-----+ 2 rows in set (0.00 sec)
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)
3.4:排序（order by):
mysql> select sid,num from score 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.00 sec) mysql> select sid,num from score 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.00 sec) mysql> select sid,num from score 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.00 sec) 若果num相同，则按照sid排序 mysql> select sid,num from score 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.01 sec) #从第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.00 sec) #从第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)
3.6:正则匹配查询（通配符）
mysql> select * from teacher where tname REGEXP '^苍'; +-----+------------+ | tid | tname | +-----+------------+ | 1 | 苍井空老师 | +-----+------------+ 1 row in set (0.01 sec) mysql> select * from teacher where tname REGEXP '师$'; +-----+----------------+ | tid | tname | +-----+----------------+ | 1 | 苍井空老师 | | 2 | 波多野结衣老师 | | 3 | 饭岛爱老师 | | 4 | 小泽玛利亚老师 | | 5 | 武藤兰老师 | +-----+----------------+ 5 rows in set (0.00 sec) 注：也可以用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)
1.2 内连接：只连接匹配的行：
mysql> select * from class; +-----+------------+ | cid | caption | +-----+------------+ | 1 | 三年级二班 | | 2 | 一年级三班 | | 3 | 三年级一班 | +-----+------------+ 3 rows in set (0.00 sec) mysql> select * from student; +-----+--------+----------+-------+ | 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.00 sec) #找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果 #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.00 sec) 外链接之左连接：优先显示左表全部记录 以左表为准，即找出所有学生信息，当然包括没有班级的学生 #本质就是：在内连接的基础上增加左边有右边没有的结果 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.00 sec) 外链接之右连接：优先显示右表全部记录 显示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.00 sec) 全外连接：显示左右两个表全部记录 在内连接的基础上增加左边有右边没有的和右边有左边没有的结果 注意：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.00 sec) mysql> select * from student where exists (select * from score where num=0); Empty set (0.00 sec)
