MYSQL入门day02
ok,今天继续mysql入门。我们在昨天基础上,再创建一个owner表。那么现在就有两张表如下:
pet表:
owner表:
注意,这两个表有关联的字段就是pet表的owner和owner表的uname!!!
(1)连接
①内连接:
select p.name as '宠物昵称',p.owner as '主人',p.species as '宠物品种',o.phone as '主人联系方式' from pet p inner join owner o on p.owner=o.uname;
可以看出,查询出来的这6行,没有一行出现主人字段为Harold的,因为owner表中没有一行uname是Harold的。所以,内连接只显示所有有关联的数据。
②左连接(显示关键词left join左边表中的所有数据,右边表数据少了补NULL值,数据多了不显示)
比如左边表pet有Harold信息,但右边表owner没有Harold信息,所以右边少了补NULL值。
select * from pet p left join owner o on p.owner=o.uname;
③同理,右连接是以右边为参照,左边少了补NULL,多了删除。
比如右边表owner有David信息,但左边表pet没有他的宠物信息,所以左边全部补NULL。左边本来还有Harold的信息,但右边表没有,所以删除。
select * from pet p right join owner o on p.owner=o.uname;
(2)约束
①使用not null:
create table `adopt`(
`id` int(10),
`pname` varchar(20) not null,
`uname` varchar(20) not null,
`price` float check(price>0),
`date` datetime,
primary key(`id`)
);
②使用check:
drop table if exists `adopt`;
create table `adopt`(
`id` int(10),
`pname` varchar(20) not null,
`uname` varchar(20) not null,
`price` float check(price>0),
`date` datetime,
primary key(`id`)
);
③主键primary key
drop table if exists `adopt`;
create table `adopt`(
`id` int(10),
`pname` varchar(20) not null,
`uname` varchar(20) not null,
`price` float check(price>0),
`date` datetime,
primary key(`id`)
);
或者:
alter table `adopt` add primary key(id);
④外键foreign key
DROP TABLE IF EXISTS `adopt`;
CREATE TABLE `adopt` (
`id` int(10) NOT NULL,
`pname` varchar(20) NOT NULL,
`uname` varchar(20) NOT NULL,
`price` float DEFAULT NULL,
`date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_pn` (`pname`),
KEY `fk_un` (`uname`),
CONSTRAINT `fk_pn` FOREIGN KEY (`pname`) REFERENCES `pet` (`name`),
CONSTRAINT `fk_un` FOREIGN KEY (`uname`) REFERENCES `owner` (`uname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
(3)视图view
视图(view)是一种虚拟存在的表,是一个逻辑表,本身并不包含数据。作为一个select语句保存在数据字典中的。通过视图,可以展现基表的部分数据。视图数据来自定义视图的查询中使用的表,使用视图动态生成。
基表:用来创建视图的表叫做基表。
现在创建一个视图(其实语法就是create view `view_name` as+select语句):
create view `petsview` as
select p.name as '宠物昵称',p.owner as '主人',p.species as '宠物品种',o.phone as '主人联系方式'
from pet p
inner join owner o
on p.owner=o.uname;
创建完毕,查看该视图:
desc petsview;
然后查询该视图的数据:
select * from petsview;
删除视图:
drop view if exists `petsview`;
(4)索引index
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
创建索引时,你需要确保该索引是应用在SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
总的来说,当数据量很庞大的时候,需要用到索引来优化查询速度,倘若数据基数太小,这样,建立索引既浪费磁盘空间,又相当于线性查询速度,没必要,所以建立索引需慎重。
创建索引:(pet是表名、name_index是索引名、name是pet表中的一个字段)
alter table pet add index name_index(name);
删除索引:
drop index `name_index` on `pet`;
(5)存储过程
什么是存储过程:就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法。
存储过程跟触发器有点类似,都是一组SQL集,但是存储过程是主动调用的,且功能比触发器更加强大,触发器是某件事触发后自动调用。
特性:有输入输出参数,可以声明变量,有if/else, case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能;
函数的普遍特性:模块化,封装,代码复用;
速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤。
首先创建一个简单的存储过程:
打开Navicat For MySQL:
先看一下我们的owner表:
接下来创建的存储过程,是输入一个变量s,如果s的值为1,返回男的人数,如果s的值为非1,返回女的人数:
将存储过程命名为“proc01”,点击运行,输入参数:
运行结果:
可以看到输出变量@male的值为3。
当然你也可以新建一个查询,创建并运行存储过程:
①创建:
DROP PROCEDURE IF EXISTS `proc01`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc01`(IN s int,OUT num int)
BEGIN
IF s=1 THEN
select count(*) into num from owner where sex='m';
ELSE
select count(*) into num from owner where sex='f';
END IF;
END
;;
DELIMITER ;
MySQL默认以";"为分隔符,如果没有声明分割符,则编译器会把存储过程当成SQL语句进行处理,因此编译过程会报错,所以要事先用“DELIMITER ;;”声明当前段分隔符,让编译器把两个";;"之间的内容当做存储过程的代码,不会执行这些代码;“DELIMITER ;”的意为把分隔符还原。
存储过程根据需要可能会有输入、输出、输入输出参数,如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT:
IN参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值OUT:该值可在存储过程内部被改变,并可返回
INOUT:调用时指定,并且可被改变和返回
②运行:
call proc01(1,@male);
select @male;
③结果:
④删除
drop PROCEDURE proc01
(6)存储函数
新建一个查询,创建一个名为findOwner的函数:
DROP FUNCTION IF EXISTS `findOwner`;
DELIMITER ;;
CREATE FUNCTION `findOwner`(p VARCHAR(20))
RETURNS varchar(20)
BEGIN
RETURN (SELECT uname
FROM owner
WHERE phone=p);
END
;;
DELIMITER ;
然后调用该函数:
select findOwner('95533');
结果: