接上篇 基础操作
此篇一样仅挑选自己常用的来讲(基础入门
并且所说的知识点并不完整
MySql
子查询
子查询例子
已知两表,人员信息表c,工资表w。
mysql> select * from c;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | tom | 20 |
| 2 | Ajie | NULL |
| 3 | ajie | 35 |
| 4 | ahua | 41 |
| 5 | ago | 21 |
+----+------+------+
5 rows in set (0.00 sec)
mysql> select * from w;
+----+-------+
| id | wages |
+----+-------+
| 1 | 20000 |
| 2 | 996 |
| 3 | 30000 |
| 4 | 27000 |
| 5 | 23000 |
+----+-------+
5 rows in set (0.00 sec)
查询工资最高员工的名称
可以这样构筑语句:查询 c 表名称,查询条件为 id 等于 w 表工资字段最大值的id
我这里使用排序配合限制返回数量,来完成子查询
mysql> select name from c where
-> id=(select id from w order by wages desc limit 1);
+------+
| name |
+------+
| ajie |
+------+
1 row in set (0.00 sec)
要是不嫌麻烦还能写的更长点,嵌套两个子查询
别提效率什么的,仅用于演示
mysql> select name from c where
-> id=(select id from w where
-> wages=(select max(wages) from w));
+------+
| name |
+------+
| ajie |
+------+
1 row in set (0.00 sec)
由比较运算符号引发的子查询
如:select 字段 from 表名 where 字段>=(子查询语句)
此时,若是子查询,返回了多个结果,数据库就会报错,这时就需要用
any、some、all 对子查询进行修饰
以下是这三个关键字对子查询信息进行筛选的结果
any | some | all | |
---|---|---|---|
>、>= | 最小值 | 最大值 | 最大值 |
<、<= | 最大值 | 最大值 | 最小值 |
= | 任意值 | 任意值 | |
<>、!= | 任意值 |
测试:
mysql> select wages from w where
-> id < (select id from c where name='ajie');
ERROR 1242 (21000): Subquery returns more than 1 row
查询 id 小于 名字为 阿杰 员工id 的工资,此时有两位员工名字叫阿杰,返回了id 1、2和5,就报错了
(嗯,这句子看起来很奇怪,仅用于演示)
mysql> select id from w where wages<25000;
+----+
| id |
+----+
| 1 |
| 2 |
| 5 |
+----+
3 rows in set (0.00 sec)
此时就可以用到上面三个关键字进行修饰
--any
mysql> select name from c where
-> id < any (select id from w where wages<25000);
+------+
| name |
+------+
| tom |
| Ajie |
| ajie |
| ahua |
+------+
4 rows in set (0.00 sec)
这里子查询返回值有三个 加上了 any 等同于
-> id < 返回值1 or < 返回值2 or < 返回值3 即 id < 1 or id < 2 or id < 5
配合上面的表格和数据来看,也就是小于最大值即可,其他同理
多表查询
笛卡尔乘积
多表查询的语法为
select 字段,字段... from 表1,表2... [where 条件]
! 但是如果不加条件直接进行查询,则会出现以下效果,这种结果我们称之为笛卡尔乘积
mysql> select * from c,w;
+----+------+------+----+-------+
| id | name | age | id | wages |
+----+------+------+----+-------+
| 1 | tom | 20 | 1 | 20000 |
| 2 | Ajie | NULL | 1 | 20000 |
| 3 | ajie | 35 | 1 | 20000 |
| 4 | ahua | 41 | 1 | 20000 |
| 5 | ago | 21 | 1 | 20000 |
| 1 | tom | 20 | 2 | 996 |
| 2 | Ajie | NULL | 2 | 996 |
| 3 | ajie | 35 | 2 | 996 |
| 4 | ahua | 41 | 2 | 996 |
| 5 | ago | 21 | 2 | 996 |
| 1 | tom | 20 | 3 | 30000 |
| 2 | Ajie | NULL | 3 | 30000 |
| 3 | ajie | 35 | 3 | 30000 |
| 4 | ahua | 41 | 3 | 30000 |
| 5 | ago | 21 | 3 | 30000 |
| 1 | tom | 20 | 4 | 27000 |
| 2 | Ajie | NULL | 4 | 27000 |
| 3 | ajie | 35 | 4 | 27000 |
| 4 | ahua | 41 | 4 | 27000 |
| 5 | ago | 21 | 4 | 27000 |
| 1 | tom | 20 | 5 | 23000 |
| 2 | Ajie | NULL | 5 | 23000 |
| 3 | ajie | 35 | 5 | 23000 |
| 4 | ahua | 41 | 5 | 23000 |
| 5 | ago | 21 | 5 | 23000 |
+----+------+------+----+-------+
25 rows in set (0.00 sec)
笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积
就造就了一大串部分正确,部分错误的数值
多表联合查询
这个时候就需要找到两张表关联的字段,作为条件进行查询,称为多表联合查询
mysql> select * from c,w where c.id = w.id;
+----+------+------+----+-------+
| id | name | age | id | wages |
+----+------+------+----+-------+
| 1 | tom | 20 | 1 | 20000 |
| 2 | Ajie | NULL | 2 | 996 |
| 3 | ajie | 35 | 3 | 30000 |
| 4 | ahua | 41 | 4 | 27000 |
| 5 | ago | 21 | 5 | 23000 |
+----+------+------+----+-------+
5 rows in set (0.00 sec)
多表链接查询
语法
select * from 表1 inner|left|right join 表2 on 表1.字段 = 表2.字段;
inner 内连接查询
示意图:
select * from 表1 inner join 表2 on 表1.字段 = 表2.字段;
内连接查询的效果与多表联合查询的效果一样
left 左外连接查询
示意图:
select * from 表1 left join 表2 on 表1.字段 = 表2.字段;
表1数据全部显示,表2符合条件数据显示,不符合条件的会以 null 进行填充。
right 左外连接查询
示意图:
select * from 表1 right join 表2 on 表1.字段 = 表2.字段;
表2数据全部显示,表1符合条件数据显示,不符合条件的会以 null 进行填充。
natural 自然连接
查询效果等同于inner即内联查询,作用是会自动匹配字段相同的列,连接时候不需要使用on或者using关键字
测试
mysql> select * from c natural join w;
+----+------+------+-------+
| id | name | age | wages |
+----+------+------+-------+
| 1 | tom | 20 | 20000 |
| 2 | Ajie | NULL | 996 |
| 3 | ajie | 35 | 30000 |
| 4 | ahua | 41 | 27000 |
| 5 | ago | 21 | 23000 |
+----+------+------+-------+
5 rows in set (0.00 sec)
索引
本文只提索引的增删查
索引的目的:进行查询性能优化。
查看索引
语法
--查看表里的索引
select index from 表名;
!注:通常会在后面加一个 \G 方便查看
创建索引
索引的创建有三种方式
create直接添加索引:
create index 索引名 on 表名(字段名)
alter 修改表 添加索引:
alter table 表名 add index 索引名(字段名)
或者在创建表的时候直接添加索引:
create table 表名(
字段 数值类型 约束,
字段 数值类型 约束,
index 索引名 (字段)
);
主键索引
主键索引是一种特殊的唯一索引
在创建表时,若是有主键,那么mysql会自动为主键添加一个主键索引。
主键索引不允许有空值,
且无法通过 create 来创建,
可使用 alite 添加主键,来使其自动添加主键索引
普通索引
最基本的索引,它没有任何限制。
语法:
create index 索引名 on 表名(字段名)
alter table 表名 add index 索引名(字段名)
create table 表名(
字段 数值类型 约束,
字段 数值类型 约束,
index 索引名 (字段)
);
唯一索引
索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
语法:
create unique index 索引名 on 表名(字段名)
alter table 表名 add unique 索引名(字段名)
create table 表名(
字段 数值类型 约束,
字段 数值类型 约束,
unique 索引名 (字段)
);
全文索引
语法:
create fulltext index 索引名 on 表名(字段名)
alter table 表名 add fulltext 索引名(字段名)
create table 表名(
字段 数值类型 约束,
字段 数值类型 约束,
fulltext 索引名 (字段)
);
全文索引的使用
全文索引有自己的语法格式,使用 match 和 against 关键字
如:
select * from 表名 where match(字段,字段) against('xxx xxx');
注意: match() 函数中指定的列必须和全文索引中指定的列完全相同,否则就会报错,无法使用全文索引,这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引,请单独为该列创建全文索引。
多列索引
多列建索引比对每个列分别建索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。
另外建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高。
语法:
create index 索引名 on 表名(字段名,字段名)
alter table 表名 add index 索引名(字段名,字段名)
create table 表名(
字段 数值类型 约束,
字段 数值类型 约束,
index 索引名 (字段,字段)
);
删除索引
语法:
drop index 索引名称 on 表名;
视图
视图本身是一张虚拟的表
视图的作用:
1.提高了重用性,对数据库重构,却不影响程序的运行,
2.提高了安全性,可以对不同的用户,让数据更加清晰,特别是查询操作,减少复杂的SQL语句,
3.增强可读性;更加安全,数据库授权命令不能限定到特定行和特定列,但是通过合理创建视图,可以把权限限定到行列级别;
如:
有信息表和工资表两张表,
信息表有员工的:员工号,姓名,年龄,家庭住址等信息
工资表有员工的:员工号,工资
此时,我想方便快捷的查看员工姓名和对应的工资,就可以创建这两个字段是视图,以后想查看时就可以直接查看此视图
创建视图
语法:
create view 视图名
as
select 列名1,列名2,...
from 表名1,表名2,表名...
where 表连接条件;
使用视图
使用视图与查询语句相同
select * from 视图名;
删除视图
drop view 视图名;
存储过程
存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
如:我想知道工资表里工资大于平均值的员工的员工号,就可以创建一个存储过程,储存一个查询的过程,然后每次需要的时候,我只要调用此存储过程即可。
因为在写存储过程时,需要用到 “ ; ” 分号,而mysql检查你输入分号且回车时,会自动执行语句,所以在写存储过程时,需要先用 delimiter 来临时更改结束符,或者简写为\d
delimiter语法:
delimiter * ( * 为你自定的字符)
\d *
创建存储过程
语法:
delimiter (自定义字符,临时更改结束符)
create procedure 存储过程名(输入参数|或输出参数|或为空)
begin
select 字段 from 表名 where 条件;
end 自定义字符
例子:
此存储过程,可同时向两张表插入数据
mysql> desc staff_info;
+-------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(6) | NO | | NULL | |
| age | int | YES | | NULL | |
| sex | enum('男','女') | YES | | NULL | |
+-------+-------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc wages;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(6) | NO | | NULL | |
| wages | int | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> \d $
mysql> create procedure entry (in id int,in name varchar(6),in age int,in sex enum('男','女'),in wages int)
-> begin
-> insert into staff_info values(id,name,age,sex);
-> insert into wages values(id,name,wages);
-> end $
Query OK, 0 rows affected (0.03 sec)
查看存储过程
语法:
show create procedure 存储过程名;
调用存储过程
语法:
call 存储过程名(输入参数|或输出参数|或为空);
删除存储过程
语法:
drop procedure 存储过程名;
触发器
触发器本身就是特殊的存储过程,用特定的操作去触发
如:员工 a 离职,那么需要在多个表里都删除他的信息,就可以写一个触发器,在信息表删除 a 的信息时,触发 触发器,触发器自动把其他表里 a 的信息也删除掉。
创建触发器
语法:
create trigger 触发器名称 after 事件(delete\insert\update)
on 触发事件的表名
for each row --固定不变的
begin
触发语句
end $$
如:
create trigger a_delete after delete
on a
for each row --固定不变的
begin
(语句)
end $$
此触发器,是在 a 表进行了删除事件后触发语句。
查看触发器
语法:
show create trigger 触发器名;
删除触发器
语法:
drop trigger 触发器名;