第七讲

13、[数据库权限]
进入mysql ： mysql -uroot -p123456

use mysql; --》使用mysql 数据库

select host,user from user;  --》查看mysql数据库有那些用户 （localhost和127.0.0.1 代表的是本地用户；%  代表的是具有远程访问权限的用户）

insert into user（host,user,passwrod）values('localhost','dcs7',password("123456"));  --》插入新用户，不具有权限

show grants for 'dcs7'@'localhost'; --》查看dcs7 用户是否具有权限
执行后：ERROR 1141 (42000): There is no such grant defined for user 'dcs7' on host 'localhost'
flush privileges;---》刷新权限表
show grants for 'dcs7'@'localhost';再次查看下没有权限
执行后：GRANT USAGE ON *.* TO 'dcs7'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

grant select,update,delete,drop on *.* to 'dcs7'@'localhost' identified by '123456';  --》 对创建的用户进行授权

flush privileges;---》授权后需要刷新权限

show grants for 'dcs7'@'localhost';---》刷新权限后查看权限



14、[连接数据的客户端navicat]

1）navicat如果连接不上：
①、service mysqld restart  重启数据库
②、service iptables stop  关闭防火墙
③、检查是用户是否有远程的权限 %  (grant all privileges on *.* to 'root'@'%' identified by '123456')

2)数据库的默认端口3306 （3306-3309）  Oracle 默认端口1521

3）快捷键
ctrl+q   新建一个窗口
ctrl +w  关闭一个窗口
ctrl +r   运行语句

4）注释
#  ==》单行注释

多行注释   /*            */

段注释   /*


15、[多表]
1）基本连接 ==》2个表当中有相同字段值可以进行连接
select * from aa,cc where aa.id = cc.s_id;  #==>(1001)

2）内连接  ==》2个表当中有相同字段值可以进行连接
select * from aa inner join cc on aa.id = cc.s_id;

3）左连接 ==》以左表为主，展示左表全部的数据，右表没有的数据以null填充
SELECT * from aa left join cc on aa.id = cc.s_id;

4）右连接 ==》以右表为主，展示右表的全部数据，左表多的数据不展示
select * from aa right join cc on aa.id = cc.s_id;

5）硬件连接 ==》2张表需要相同的字段
select * from aa union select * from cc;

6）基本法+临时表
求出张三的成绩
select * from aa,cc where aa.id=cc.s_id;  t
select score from (select * from aa,cc where aa.id=cc.s_id)t where name = 'zhangsan';

7）嵌套法 =（= 和 in）
select id from aa where name = 'zhangsan';   ==>1001
select score from cc where s_id = (select id from aa where name = 'zhangsan');


8）嵌套法 in
select id from aa where name = 'zhangsan';   ==>1001
select score from cc where s_id in(select id from aa where name = 'zhangsan');



求出谁没参加考试？
1、左连接
SELECT name from aa left join cc on aa.id = cc.s_id where score is null;

2、嵌套法
select s_id from cc;   ==>1001
select name from aa where id not in (select s_id from cc);


【处理mysql数据库中文乱码的问题】
1、vim /etc/my.cnf  ==》数据库的配置文件
2、加入这行character_set_server=utf8  
3、重启数据库
4、删除dept和emp表==》重新创建发现还是乱码
5、dcs这个库的数据库属性把Latin改为utf8编码格式 （1.字符集：utf8 -- UTF-8 Unicode 2.排序规则：utf8_general_ci）
==》重新创建表就可以看到中文了

[视图]
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