[视图]
1、什么是视图
视图是一个虚拟的表，它不在数据库中以存储数据的 形式保存，
是在使用视图的时候动态生成。
2、视图的特点
2.1视图是由基本表产生的虚表
2.2视图的更新和删除会影响基础表
2.3基础表的更新和删除也会影响到视图
3、创建视图
select * from test;
create view test1 as (select id,name from test); ==》创建一个test1视图
show tables; ==》查询所有的视图或者表
show create view test1; ==>查询创建视图
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `test1` AS
(select `test`.`id` AS `id`,`test`.`name` AS `name` from `test`)
select * from test1; ==》查询视图test1的数据
update test1 set name = 'duan' where id = 1; ==》把视图test1中第一条数据的姓名改为duan，基础表test也会改
update test SET name = 'xiaoduan' where id = 1; ==》把基础表test中第一条数据的姓名改为xiaoduan，视图test1也会改
drop view test1; ==》删除视图
DDL //数据库定义语言 （对表结构和表字段进行操作）==》全称：data definition language
ALTER table create drop
DML //数据库操作语言 （对表数据进行操作） ==》全称：data manipulation language
select delete update insert into
[外键 约束] 外键的主要作用是：保持数据的一致性、完整性
数据库约束
主键约束 ==》primary key
非空约束 ==》not null
默认值约束 ==》default
自增长约束 ==》auto_increment
外键 约束 ==》foreign key ==》表与表之间的约束
show create table test; ==》查询创建表的语句
CREATE TABLE `test` (
`id` int(10) DEFAULT NULL,
`score` int(10) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ==》engine ：引擎 innodb ：存储引擎
是mysql 提供外键约束的引擎 ，默认是myisam
create table dcs1(id int(10) primary key,name varchar(10))engine=INNODB; ==》创建dcs1这个表（主表）
show create table dcs1; ==》查询创建表的语句
CREATE TABLE `dcs1` (
`id` int(10) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
create table dcs2(sid int(10) primary key,sname varchar(10),CONSTRAINT fk_sid foreign key (sid) references dcs1(id))engine=INNODB;
解释：给子表创建一个外键，外键名字叫fk_sid 并且子表的外键字段为sid，
对应主表的主键名 字为id
FK_***：是外键名
--对应dcs2表中的：fk_sid foreign key(子表的外键字段)
--对应dcs2表中的：sid references 父表的表名（父表的主键的字段名）
---父表名对应dcs1，父表的主键的 字段名对应dcs1中的id字段
外键之增加数据：
1、如果表1和表2之间有外键约束 且表dcs1是主表 表dcs2为子表 如果主表不存在的数据 在任何
一张子表是无法插入跟该数据相关的任何数据。
2、如果要删除主表的数据，需要现删除与主表相关的任何子表的数据（id需要对应）
show create table dcs2; ==》查询创建表的语句
CREATE TABLE `dcs2` (
`sid` int(10) NOT NULL,
`sname` varchar(10) DEFAULT NULL,
PRIMARY KEY (`sid`),
CONSTRAINT `fk_sid` FOREIGN KEY (`sid`) REFERENCES `dcs1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into dcs2 values(1,'xiaoduan'); ==》报错，原因是主表dcs1 没有数据，要加先在dcs1主表添加
insert into dcs1 values(1,'xiaoduan'); ==》主表dcs1可以添加数据
insert into dcs2 values(1,'xiaoduan'); ==》给子表dcs2添加数据
delete from dcs1 where id= 1; ==》清除主表的数据，报错，原因是子表存在数据，要删除先删除子表的数据
delete from dcs2 where sid= 1; ==》删除子表的数据可以删除
delete from dcs1 where id= 1; ==》现在可以删除主表的数据
alter table dcs2 drop foreign key fk_sid; ==》删除dcs2这个表的外键
show create table dcs2; ==》查询创建表的语句
CREATE TABLE `dcs2` (
`sid` int(10) NOT NULL,
`sname` varchar(10) DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ==》删除了外键说明dcs1 和dcs2 没有关系了，互不影响
alter table dcs2 add CONSTRAINT fk_sid foreign key (sid) references dcs1(id); ==》给dcs2 sid 字段添加一个外键名为fk_sid
show create table dcs2; ==》查询创建表的语句
CREATE TABLE `dcs2` (
`sid` int(10) NOT NULL,
`sname` varchar(10) DEFAULT NULL,
PRIMARY KEY (`sid`),
CONSTRAINT `fk_sid` FOREIGN KEY (`sid`) REFERENCES `dcs1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
[存储过程]
1、什么是存储过程
存储过程是完成特定功能的sql语句集合。通过编译后存储在数据 库中，
通过指定的存储过程名称调用执行它。 存储过程=sql语句集合+控制语句
2、使用存储过程的优点
1）存储过程创建可以多次调用，不需要重新编写存储过程语句。
2）假如要往表中插入大量的测试数据，用insert into语法执行速 度太慢，效率较低，那么这个时候可以通过编写存储过程，
把需要 插入的数据sql集合放入存储过程的函数体当中，创建存储过程， 下次即可调用。存储过程
3）存储过程加快程序的运行速度
4）存储过程增加sql语句的功能和灵活性、创建的存储过程可以重复使用。
3、创建存储过程
第一步 创建一个存储过程
create procedure +存储过程名（参数 数据库类型）
BEGIN
存储过程体
end
call +存储过程名（）; ==》调用村粗过程
begin .... end ==》表示存储过程的开始和结束
第二步 删除一个存储过程
drop procedure 存储过程名
drop procedure if EXISTS 存储过程名 ==》加强代码的健壮性
第三步：如果表存在就删除
drop TABLE if EXISTS mm; ==》增强存储的健壮性
第四步： 不用参数查询数据
例子1：不用参数的存储过程
drop procedure if exists dcs7; #==》如果dcs7这个存储存在就删除 加强代码的健壮性
create procedure dcs7() #==》创建一个存储过程名为dcs7
begin
#SQL 语句集合
drop table if exists n; #==》如果这个表存在就删除，增强存储的健壮性
create table n(id int(10) primary key auto_increment,score int(10));
insert into n values (1,66),(2,88),(3,89),(4,68);
select * from n where id = 4;
desc n;
end
call dcs7(); #==》调用存储过程
第五步：在定义存储的时候带参数，用参数查询
基本格式：create procedure 存储过程名字(n INT)
drop procedure if exists dcs7; #==》如果dcs7这个存储存在就删除 加强代码的健壮性
create procedure dcs7(m int) #==》创建一个存储过程名为dcs7 m表示形式参数，int整型 这里定义了参数调用一定需要接参数
begin
#SQL 语句集合
drop table if exists n; #==》如果这个表存在就删除，增强存储的健壮性
create table n(id int(10) primary key auto_increment,score int(10));
insert into n values (1,66),(2,88),(3,89),(4,68);
select * from n where id = m;
desc n;
end
call dcs7(2); #==》调用存储过程，这里需要填一个实际参数 m = ?如果定义了形式参数、不传入实际参数会报错
第六步：
往存储中加入if语句，判断语句 if语句的格式： （有多少的if 就需要写多少个end if）
1、单分支
if 条件 THEN
执行sql
else
执行sql
end if;
drop procedure if exists dcs7; #==》如果dcs7这个存储存在就删除 加强代码的健壮性
create procedure dcs7(m int) #==》创建一个存储过程名为dcs7 m表示形式参数，int整型 这里定义了参数调用一定需要接参数
begin
#SQL 语句集合
drop table if exists n; #==》如果这个表存在就删除，增强存储的健壮性
create table n(id int(10) primary key auto_increment,score int(10));
insert into n values (1,66),(2,88),(3,89),(4,68);
if m = 0 then #==》当m = 0时，执行查询全表数据
select * from n;
ELSE #==》当m 不等于时 执行统计
select count(*) from n;
end if;
end
call dcs7(0); #==》调用存储过程，这里需要填一个实际参数 m = ?如果定义了形式参数、不传入实际参数会报错
2.多分支 （有多少的if 就需要写多少个end if）
if 条件 THEN
执行sql
else if 条件 THEN
执行sql
else if 条件 THEN
执行sql .............
else
执行sql
end if;
end if;
end if;
drop procedure if exists dcs7; #==》如果dcs7这个存储存在就删除 加强代码的健壮性
create procedure dcs7(m int) #==》创建一个存储过程名为dcs7 m表示形式参数，int整型 这里定义了参数调用一定需要接参数
begin
#SQL 语句集合
drop table if exists n; #==》如果这个表存在就删除，增强存储的健壮性
create table n(id int(10) primary key auto_increment,score int(10));
insert into n values (1,66),(2,88),(3,89),(4,68);
if m = 0 then #==》当m = 0时，执行查询全表数据
select * from n;
else if m >0 and m<4 then #==》当m大于0 小于4执行查询id 等于4的数据
select * from n where id = 2;
else if m>=4 then #==>当m大于等于4执行查询表结构
desc n;
ELSE #==》当m 不等于时 执行统计
select count(*) from n;
end if;
end if;
end if;
end
call dcs7(-1); #==》调用存储过程，这里需要填一个实际参数 m = ?如果定义了形式参数、不传入实际参数会报错
第七步：declare i int DEFAULT语句可以自动检测统计数据行数
drop procedure if exists dcs7; #==》如果dcs7这个存储存在就删除 加强代码的健壮性
create procedure dcs7(m int) #==》创建一个存储过程名为dcs7 m表示形式参数，int整型 这里定义了参数调用一定需要接参数
begin
declare i int(10) default(select count(*)from n); #定义一个变量i 默认值是n表的统计数 i = 4
drop table if exists n; #==》如果这个表存在就删除，增强存储的健壮性
create table n(id int(10) primary key auto_increment,score int(10));
insert into n values (1,66),(2,88),(3,89),(4,68);
if m = 0 then #==》当m = 0时，执行查询全表数据
select * from n;
else if m >0 and m<i then #==》当m大于0 小于i执行查询id 等于i的数据
select * from n where id = i;
else if m>=i then #==>当m大于等于i执行查询表结构
desc n;
ELSE #==》当m 不等于时 执行统计
select count(*) from n;
end if;
end if;
end if;
end
call dcs7(5); #==》调用存储过程，这里需要填一个实际参数 m = ?如果定义了形式参数、不传入实际参数会报错
第八步：往存储过程中加while语句 如果传入得参数大于表中实际总记录的数量，则往表中补齐
3、往存储过程中加while语句（循环语句）
while 语句的格式：
while 条件 do
执行循环体（sql）
end while；
注意：
什么时候进入循环：当条件成立时，进入循环
什么时候退出循环：当条件不成立时，退出循环
drop procedure if exists dcs7; #==》如果dcs7这个存储存在就删除 加强代码的健壮性
create procedure dcs7(m int) #==》创建一个存储过程名为dcs7 m表示形式参数，int整型 这里定义了参数调用一定需要接参数
begin
declare i int(10) default(select count(*)from n); #定义一个变量i 默认值是n表的统计数 i = 4
/* drop table if exists n; #==》如果这个表存在就删除，增强存储的健壮性
create table n(id int(10) primary key auto_increment,score int(10));
insert into n values (1,66),(2,88),(3,89),(4,68); */
if m = 0 and m <=i then #==》当m = 0时，执行查询全表数据
select * from n;
else
while m>i DO
insert into n(score)values(88);
set i = i+1; #每插入一条数据，表中的行数加一
end while;
select * from n order by score desc;
end if;
end
call dcs7(10); #==》调用存储过程，这里需要填一个实际参数 m = ?如果定义了形式参数、不传入实际参数会报错
第九步：补充的数据在最小或者最大的成绩上逐次加1分
drop procedure if exists dcs7; #==》如果dcs7这个存储存在就删除 加强代码的健壮性
create procedure dcs7(m int) #==》创建一个存储过程名为dcs7 m表示形式参数，int整型 这里定义了参数调用一定需要接参数
begin
declare i int(10) default(select count(*)from n); #定义一个变量i 默认值是n表的统计数 i = 4
declare j int(10) default (select max(score) from n); #定义一个最大值变量j j = 89
/* drop table if exists n; #==》如果这个表存在就删除，增强存储的健壮性
create table n(id int(10) primary key auto_increment,score int(10));
insert into n values (1,66),(2,88),(3,89),(4,68); */
if m = 0 and m <=i then #==》当m = 0时，执行查询全表数据
select * from n;
else
while m>i DO
set j = j +1; #每循环一次对j进行加一
insert into n(score)values(j);
set i = i+1; #每插入一条数据，表中的行数加一
end while;
select * from n order by score desc;
end if;
end
call dcs7(10); #==》调用存储过程，这里需要填一个实际参数 m = ?如果定义了形式参数、不传入实际参数会报错
当 i= 5 时，进入while 循环，j = j+1 = 89 +1
当 i= 6 时，进入while 循环 j = j+1 = 90 +1
练习题
存储过程题目：现在有一个user用户表,需要往user表中插入1000个登录 用户要求如下：
1、在插入用户前先判断是否存在1000个登录用户,如果存在则统计表中实 际的行数、如若不存在则自动补齐剩余的数据
2、表名为user,存储过程名字随意取,表中字段有id user_name user_pwd verify 格式如下(1,user1,123456,W4E38J),
且id、用户名不能重复,verify 验证码字段为随机生成6位数验证码 CONCAT函数 可以把2个字符串进行连接。
第一步
drop table if exists test;
create table test(id int(10) primary key auto_increment,user_name varchar(10),user_pwd int(10) default'123456',verify varchar(10));
INSERT INTO test VALUES (1,'user1','123456','W4E38J');
drop procedure if exists dcs7;
create procedure dcs7(m int(10))
BEGIN
DECLARE i int(10) default(SELECT count(*) from test);
DECLARE user_name varchar(10) default '';
DECLARE verify varchar(10) default '';
while m > i do
set i = i + 1;
INSERT INTO test(user_name,verify) values ((SELECT concat('user',i)),(SELECT substring(md5(rand()),1,6)));
end WHILE;
SELECT * FROM test;
END
CALL dcs7(12);
select CONCAT('user',2)
select substring(md5(rand()),1,6);
|
|
236
0
多测师社区 
