更过MySQL使用操作,点击查看专栏: MySQL数据库_命令详解
一.子查询
创建database,表格及导入数据
#设置编码
set names gbk;
#选择数据库
create database test1;
use test1;
#创建学生表
create table stu(
id int unsigned primary key auto_increment,
name varchar(45) not null,
age tinyint(3) not null default 0,
sex char(1) not null default '男',
city varchar(10) not null default '',
class_id int not null comment '关联院系表class_id'
)engine=myisam charset=utf8;
insert into stu
values
(null,'邢飞',18,'男','江西',1),
(null,'杨大传',36,'男','山西',2),
(null,'许娜',28,'女','北京',1),
(null,'王磊',44,'女','上海',3),
(null,'江荣华',32,'女','河南',3),
(null,'赵龙',20,'男','河北',1); #插入错误就用set names utf8;
#创建院系表(class)
create table class(
id int unsigned primary key auto_increment,
name varchar(45) not null,
room varchar(45) not null,
tel varchar(12) not null
)engine=myisam charset=utf8;
insert into class
values
(null,'计算机系','行政楼302','010-11111111'),
(null,'数学系','科研楼108','010-22222222'),
(null,'物理系','行政楼305','010-33333333');
#分数表
create table score (
stu_id int unsigned comment '关联学生表ID',
id int unsigned primary key auto_increment comment '分数表ID',
math int unsigned not null comment '数学',
chinese int unsigned not null comment '语文',
english int unsigned not null comment '英语'
)engine=myisam charset=utf8;
#分数数据插入
insert into score
values
(1,null,80,58,99),
(2,null,11,99,29),
(3,null,32,53,89),
(4,null,99,28,99),
(5,null,77,68,19),
(6,null,33,18,29);
mysql> select * from stu;
+----+-----------+-----+-----+--------+----------+
| id | name | age | sex | city | class_id |
+----+-----------+-----+-----+--------+----------+
| 1 | 邢飞 | 18 | 男 | 江西 | 1 |
| 2 | 杨大传 | 36 | 男 | 山西 | 2 |
| 3 | 许娜 | 28 | 女 | 北京 | 1 |
| 4 | 王磊 | 44 | 女 | 上海 | 3 |
| 5 | 江荣华 | 32 | 女 | 河南 | 3 |
| 6 | 赵龙 | 20 | 男 | 河北 | 1 |
+----+-----------+-----+-----+--------+----------+
mysql> select * from class;
+----+--------------+--------------+--------------+
| id | name | room | tel |
+----+--------------+--------------+--------------+
| 1 | 计算机系 | 行政楼302 | 010-11111111 |
| 2 | 数学系 | 科研楼108 | 010-22222222 |
| 3 | 物理系 | 行政楼305 | 010-33333333 |
+----+--------------+--------------+--------------+
mysql> select * from score;
+--------+----+------+---------+---------+
| stu_id | id | math | chinese | english |
+--------+----+------+---------+---------+
| 1 | 1 | 80 | 58 | 99 |
| 2 | 2 | 11 | 99 | 29 |
| 3 | 3 | 32 | 53 | 89 |
| 4 | 4 | 99 | 28 | 99 |
| 5 | 5 | 77 | 68 | 19 |
| 6 | 6 | 33 | 18 | 29 |
+--------+----+------+---------+---------+
1.标量子查询
返回单个值(一行一列)
mysql> select name from stu where id=3;
+--------+
| name |
+--------+
| 许娜 |
+--------+
2.列子查询
返回多行一列
mysql> select name from stu;
+-----------+
| name |
+-----------+
| 邢飞 |
| 杨大传 |
| 许娜 |
| 王磊 |
| 江荣华 |
| 赵龙 |
+-----------+
3.行子查询
返回一行多列
mysql> select * from stu where id = 1;
+----+--------+-----+-----+--------+----------+
| id | name | age | sex | city | class_id |
+----+--------+-----+-----+--------+----------+
| 1 | 邢飞 | 18 | 男 | 江西 | 1 |
+----+--------+-----+-----+--------+----------+
4.表子查询
返回多行多列
mysql> select * from stu;
+----+-----------+-----+-----+--------+----------+
| id | name | age | sex | city | class_id |
+----+-----------+-----+-----+--------+----------+
| 1 | 邢飞 | 18 | 男 | 江西 | 1 |
| 2 | 杨大传 | 36 | 男 | 山西 | 2 |
| 3 | 许娜 | 28 | 女 | 北京 | 1 |
| 4 | 王磊 | 44 | 女 | 上海 | 3 |
| 5 | 江荣华 | 32 | 女 | 河南 | 3 |
| 6 | 赵龙 | 20 | 男 | 河北 | 1 |
+----+-----------+-----+-----+--------+----------+
5.exists和not exists
语法: select 列 form 表名 where exists (子查询)
含义: 判断指定查询语句是否有数据,有则执行主查询,反之则不执行
- 如果有人超过了语文80分就显示所有的学生
mysql> select * from stu where exists (select * from score where chinese > 80);
+----+-----------+-----+-----+--------+----------+
| id | name | age | sex | city | class_id |
+----+-----------+-----+-----+--------+----------+
| 1 | 邢飞 | 18 | 男 | 江西 | 1 |
| 2 | 杨大传 | 36 | 男 | 山西 | 2 |
| 3 | 许娜 | 28 | 女 | 北京 | 1 |
| 4 | 王磊 | 44 | 女 | 上海 | 3 |
| 5 | 江荣华 | 32 | 女 | 河南 | 3 |
| 6 | 赵龙 | 20 | 男 | 河北 | 1 |
+----+-----------+-----+-----+--------+----------+
6.特殊关键词(some,any,all)
some=any查询的结果中,满足任意一个给定的条件即可,相当于or.
all,查询的结果中,满足所有给定的条件,相当于and.
- 查询 ID大于(‘邢飞’, ‘杨大传’, ‘赵龙’)对应的id号的学生信息
mysql> select * from stu where id > any (select id from stu where name i('邢飞', '杨大传', '赵龙'));
+----+-----------+-----+-----+--------+----------+
| id | name | age | sex | city | class_id |
+----+-----------+-----+-----+--------+----------+
| 2 | 杨大传 | 36 | 男 | 山西 | 2 |
| 3 | 许娜 | 28 | 女 | 北京 | 1 |
| 4 | 王磊 | 44 | 女 | 上海 | 3 |
| 5 | 江荣华 | 32 | 女 | 河南 | 3 |
| 6 | 赵龙 | 20 | 男 | 河北 | 1 |
+----+-----------+-----+-----+--------+----------+
mysql> select * from stu where id > all (select id from stu where name i('邢飞', '杨大传', '赵龙'));
Empty set (0.00 sec)
二.视图(view)
反复使用的复杂sql语句,可以通过视图进行简化查询.
视图(view)是存在数据库中虚拟的表(只是结构没有数据,数据来源于其他表)
1.创建视图
语法: create view 视图名 as SQL语句
- 查找所有人对应的id,name,classname和room
create view v_stuclass as
select stu.id,stu.name,class.name as classname,class.room from stu left joiclass ostu.class_id = class.id;
# 因为两个表的name重复,class的名字起别名classname
mysql> show tables;
+-------------------+
| Tables_in_test1 |
+-------------------+
| class |
| score |
| stu |
| v_stuclass |
+-------------------+ #这里多了一个 v_stuclass 的table
mysql> select * from v_stuclass;
+----+-----------+--------------+--------------+
| id | name | classname | room |
+----+-----------+--------------+--------------+
| 1 | 邢飞 | 计算机系 | 行政楼302 |
| 3 | 许娜 | 计算机系 | 行政楼302 |
| 6 | 赵龙 | 计算机系 | 行政楼302 |
| 2 | 杨大传 | 数学系 | 科研楼108 |
| 4 | 王磊 | 物理系 | 行政楼305 |
| 5 | 江荣华 | 物理系 | 行政楼305 |
+----+-----------+--------------+--------------+
注意: 视图不保存数据!
一般为了区分统一给视图名字加前缀.
2.修改视图
语法: alter view 视图名 as 新SQL语句
mysql> alter view v_stuclass as select name from stu;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_stuclass;
+-----------+
| name |
+-----------+
| 邢飞 |
| 杨大传 |
| 许娜 |
| 王磊 |
| 江荣华 |
| 赵龙 |
+-----------+
3.删除视图
语法: drop view [if exists] 视图名;
mysql> drop view v_stuclass;
Query OK, 0 rows affected (0.00 sec)
4.显示视图
语法: show create view 视图名\G