mysql操作笔记2

mysql数据库的权限管理：

在linux系统通过命令:

mysqladmin -uroot password  '123456'

mysql的权限管理涉及到 mysql库，里面包含的表用来定义mysql工具的一些配置、权限

需要修改或增加权限都要进入mysql库：

use mysql；

查看当前mysql工具一些用户情况：

select host,user,password from user;

给mysql增加一个新的用户：

insert into user(host,user,password) values('localhost','dcs30',password('123456'));

每一次执行权限操作之后，需要进行 刷新权限

flush privileges;

赋予 select,insert,delete,update 在所有库里所有表 给到'dcs30'@'localhost'  识别密码 123456

grant select,insert,delete,update on *.* to 'dcs30'@'localhost' identified by '123456';

每一次执行权限操作之后，需要进行 刷新权限

flush privileges;

查看指定用户的数据库权限：

show grants for 'dcs30'@'localhost';

简化的操作：

use mysql；  ===进入mysql库

grant all privileges on *.* to 'root'@'%' identified by '123456';   ===赋予所有的权限跟到一个具有远程操作权限的root用户

flush privileges;  ===刷新权限

其他的操作：

update user set password=password('12345')  where user='dcs30' and host='localhost'; ===》修改密码

revoke all on *.* from 'dcs30'@'localhost'; ===》取消相关用户的权限操作

delete from user where user='dcs30'; ==>可以直接在user表进行用户的删除，注意不要乱删root用户

使用Navicat连接数据库

Navicat是一个数据库连接工具，一个直接连接服务器里面的数据库管理工具的工具，图像化界面

3306,是MySQL的默认端口号，一般是使用3306~3309

使用Navicat连接数据库需要注意服务器的mysql服务有没有启动，以及防火墙有没有关闭

以下是在Navicat里面的查询写的笔记：

#运行方式有两种：

#1，直接点击运行

#2，选择sql语句，右击‘运行已选择的’

# “#”代表注释，被注释的内容不会被程序识别和执行

# 使用#号可以做单行注释

-- 单行注释也可以使用快捷键 ctrl+/  在所在行的行首增加--

-- 多行注释可以使用/* .....*/

/*

这是多行注释的内容

这是多行注释的内容

这是多行注释的内容

*/

--  只写一条sql语句可以不加分号，但是两条或以上就要加分号‘;’

--  这是一个英文的分号';'   这是一个中文的分号‘；’

-- 使用快捷键 ctrl+/进行注释的行，也可以使用快捷键 ctrl+/ 进行取消注释

/*

在Navicat中新建的查询时临时的，可以保存，是保存在Navicat这个工具里，并不是保存在MySQL

我们在Navicat中的查询写好的SQL语句记得进行保存，下次在进入Navicat时，就可以直接重复使用

*/

----------多表查询-讲解--------

select *from aa;

select *from cc;

1,内连接 inner join  对两张表当中有字段的值是相同的，就可以通过内连接进行连接

select * from aa inner join cc on aa.id=cc.s_id;

select * from aa join cc on aa.id=cc.s_id;   #这是一个内连接的简写

2，左连接  left join  以左边的表为主，显示完整的左边表，右边表只会展示匹配上的数据

select * from aa left join cc on aa.id=cc.s_id

3，右连接  right join 以右边的表为主，显示完整的右边表，左边表只会展示匹配上的数据

select * from aa right join cc on aa.id=cc.s_id

左连接和右连接的区别：

1，左连接展示完整的左边表信息，右连接展示完整的右边表信息

2，左连接的查询查询速度比右连接的查询速度要快

4，基本连接  对于多张表当中字段的值相同就可以通过基本连接的方法进行连接（结果类似于内连接，主要区别在于语法上）

select * from aa,cc,ff where aa.id=cc.s_id and ff.fid=aa.id

create table ff(fid int(10),class int(10));

insert into ff values(1001,2130);

select *from ff;

5，硬连接（机械拼接，做上下拼接的） union 主要是对字段相同的表进行拼接，要求拼接的表字段要一致,拼接出来的结果表字段名以第一个表为主

select * from aa union select sid,name from grade union select * from cc;

多表查询的一些常用方法：

上述的内连接，左连接，右连接，基本连接是常见的一些方法

还有临时表方法、嵌套方法

临时表方法

select *from aa inner join cc on aa.id=cc.s_id  ===这是一张临时表t

求张三的成绩：

select t.score from (select *from aa inner join cc on aa.id=cc.s_id) as t where t.name = 'zhangsan';

求张三的成绩：

嵌套方法： 把求出的结果的sql，给到下一个表的条件去使用

嵌套方法  ‘=’方法：

1，先求通过aa表求出zhangsan对应的id（先通过单表的方法求出一个结果）

select id from aa where name='zhangsan'   ===得出结果 1001

2，把前面步骤求出的结果作为下一个表的条件使用

select score from cc where s_id = (select id from aa where name='zhangsan')

嵌套方法  ‘in’方法：

1，先求通过aa表求出zhangsan对应的id（先通过单表的方法求出一个结果）

select id from aa where name='zhangsan'   ===得出结果 1001

2，把前面步骤求出的结果作为下一个表的条件使用

select score from cc where s_id in (select id from aa where name='zhangsan')

嵌套方法 的 ‘=’方法和‘in’方法的区别：

‘=’方法只能对一个值使用，'in'方法可以对一个值或多个值生效，所以直接用in方法即可

求出谁没有参加考试？

条件：没有参加考试（score为空）  结果：谁--名字name

方法一 基本连接+嵌套：

select name from aa,cc where aa.id=cc.s_id  ==求出参加考试的人

select name from aa where name not in (select name from aa,cc where aa.id=cc.s_id) ==求出不包括参加考试的人

方法二 左连接：

select name from aa left join cc on aa.id=cc.s_id where cc.score is null

方法三 右连接：

select name from cc right join aa on cc.s_id=aa.id where cc.score is null

方法四 临时表：

select *from aa left join cc on aa.id=cc.s_id  ===>临时表t

select name from (select *from aa left join cc on aa.id=cc.s_id) t where t.score is null

数据库的索引：index

一般是添加一张表中某些频繁使用的字段上，添加索引的意义是在于提高数据查询的速度，不能对整一张表所有的字段都加索引。

注意：

1，创建索引是在大量的数据前提下去做，添加索引之后查询速度明显可以提高

2，不是每个表的字段都需要加索引，每个字段都索引就没有了实际的意义了，即使是添加了也会消耗系统的大量内存，通常之后对经常使用的字段进行添加索引

3，索引的优先级：PRI>UNI>MUL

索引分3种：

普通索引 index MUL:一张表中可以创建多个普通索引

唯一索引 unique index UNI ：也可以创建多个唯一索引，要求添加唯一索引的列不能存在重复的值

主键索引 primary PRI:在定义主键约束时，同时也是对该字段定义一个主键索引，主键索引是主键约束一个特征，不能为空不能重复

创建普通索引：

create index cc on grade(name); -- 创建普通索引aa在 grade表的name字段

desc grade;

show index from grade;  -- 展示索引 来源 grade表

alter table grade drop index aa; -- 修改表 grade 删除索引 aa;

create index aa on grade(age); -- 给多个字段创建普通索引

创建唯一索引：

create unique index cc on grade(name);

-- 创建 唯一索引 cc 在 grade表的name字段

desc grade;

show index from grade;  -- 展示索引 来源 grade表

create unique index ff on grade(class); -- 执行报错，因为class字段存在重复的值，所以不能添加唯一索引

select *from grade

insert into grade(name) values('lisi'); -- 执行报错，因为name字段已经被定义了唯一索引，所以导致插入数据时，重复数据不允许被插入

alter table grade drop index cc;  -- 删除唯一索引和删除普通索引的方式是一样的

alter table grade drop index aa,drop index cc;  -- 同时删除多个索引（普通索引或者唯一索引）

创建主键索引 ：

alter table grade add primary key(sid); -- 修改表 grade表 增加主键在sid字段上

删除主键：

alter table grede drop primary key; -- 删除主键（如果有自增长，需要先删除自增长）

视图 view：视图是一张基于实体表的虚表，这个实体表也叫实体的基础表，视图的数据时基于实体表，两者之间数据的改变都会影响到对方

为什么要使用视图？

因为数据库中有些查询是非常复杂的，而且这些查询是经常使用，所有可以使用视图直接查看这些频繁使用的查询语句；另外还有出于安全的考虑，一些数据的脱敏处理（防止一些敏感信息的暴露）

视图的缺点：

1，性能差，因为视图是由其他表的select语句生成，对视图进行操作时其实是进行了两次操作，先是对视图进行操作，然后视图也会对基础表进行操作

2，存在修改限制，对结构简单视图是很方便操作，但是对结构复杂（生成视图的SQL复杂）的视图，是不允许进行修改

select * from grade;

创建视图：

create view dcs as(select sid,name from grade);

show tables;

show create view dcs; -- 查看dcs视图的创建方式 查看视图的结构

/* CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `dcs` AS (select `grade`.`sid` AS `sid`,`grade`.`name` AS `name` from `grade`)  这是通过查看视图结构获取的结果*/

show  table status where comment='view';  -- 展示表的状态，当类型是视图的

对视图进行操作：

select *from dcs;

update dcs set name='lisan' where sid=2;

insert into dcs(name) values('xiaomai');

delete from dcs where sid =1;

对多表查询定义为一个视图

create view join1 as (select * from emp,dept where emp.dept2=dept.dept1);

select *from join1;

删除视图：

drop view dcs;   -- 删除视图 dcs视图

表内约束：

主键约束：不能为空，不能重复，是唯一的

非空约束：不能为空

自增长约束：通常是结合主键约束一起使用的，在插入数据的时候，不对定义了自增长约束的字段进行数据插入，会直接的在该字段最大的值进行+1插入

默认值约束：对列值定义一个默认值，在不对默认约束的列插入数据时，会直接插入一个默认值

外键约束 也叫外码(foreign key)：约束的是表与表之间的关系

主要作用：保持数据的一致性、完整性

1，创建两个表dcs1、dcs2,设计时，给dcs1表加入一个外键，这个外键加在dcs2的sid字段上

创建主表dcs1:

create table dcs1(id int(4) primary key,name varchar(10)) engine=innodb;

创建从表dcs2:

create table dcs2(sid int(4) primary key,sname varchar(10),constraint fk_sid foreign key(sid) references dcs1(id)) engine=innodb;

-- 创建表 dcs2 包含字段 sid 类型为int(4) 主键，sname 类型为varchar(10), 创建约束 命名为fk_sid 外键定义在sid字段上，引用dcs1表中的id字段，数据引擎为innodb

解释：

dcs1 ==>主表（父表）

dcs2 ==>从表（子表）

constraint fk_sid ：约束的名称为 fk_sid

foreign key(sid) :外键定义在sid字段上

references dcs1(id)：引用的是dcs1表的id字段

engine=innodb ：在mysql建表，默认的数据引擎是MyISAM，在使用外键的表都要使用innodb的数据引擎类型

插入数据：只能先对父表进行数据插入，才可以对子表进行对应的数据插入

insert into dcs1 values(1,'马桶盖');  -- 这是父表

insert into dcs2 values(1,'马桶');   -- 这是子表

select *from dcs1;

select *from dcs2;

删除数据：只能先对子表进行删除，才可以对父表的对应数据进行删除

delete from dcs2 where sid=1;  -- 这是子表

delete from dcs1 where id=1;   -- 这是父表

注意：外键约束中，父表和子表的外键是互相影响的，子表存在的数据不能再父表直接删除；父表不存在的数据，不能在子表进行增加；

父表和子表的数据更新需要关注对方表是否存在这

删除外键：

alter table dcs2 drop foreign key fk_sid;

-- 修改表 dcs2 删除外键 fk_sid

删除外键之后，就可以不再受约束的去操作父表和子表

添加外键约束:

alter table dcs2 add constraint fk_sid foreign key(sid) references dcs1(id);

添加外键约束需要注意的三点：

1，需要关注添加外键约束的子表和父表的数据是否都存在关联关系

2，需要关注子表和父表的数据引擎是否为innodb

3，定义外键名的时候，不需要加引号

什么是存储过程？

存储过程是实现某个特定功能的sql语句的集合，编译后的存储过程会保存在数据库中，通过存储过程的名称可以反复的调用执行。

存储过程的优点：

1.存储过程创建后，就可以反复的调用和使用，不需要重新写复杂的sql语句

2.创建，修改存储过程不会对数据有任何的影响

3.存储过程可以通过输入参数返回输出值

4.通过存储过程中加入控制语句，可以加强sql语句的功能和灵活性

5.对于单个的增删改查语句(insert,delete,update,select)可以直接封装在一个函数体当中(或者说封装在一个集合当中),存储过程一旦创建可以直接调用,且可以重复的去调用

6. 单个的SQL语句每次执行都需要数据库进行编译,而存储过程被创建只需要编译一次,后续即可调用。

7.创建的存储过程,可以重复进行调用,可以减少数据库开发人员的工作量

8.防止SQL注入 f(x)=x+y

9、造数据（重点）

二、基本格式：

drop procedure if exists bb; #如果存在bb存储过程就删除bb，增强程序的健壮性

create procedure bb()；  ——创建存储过程，命名为bb

begin —开始——后面跟相关的sql语句结合，重复执行多条sql语句

drop table if exists bb ——如果存在bb表就删除，用来增加程序的健壮性，

sql语句 ；——执行的语句

end —结束

// 分隔符

call 存储过程名();    调用存储过程

删除一个存储过程

drop procedure +存储过程名称——删除存储过程不需要加（）里面的参数

查看单个存储过程的详情

show create procedure +存储过程名称——查看时可以添加参数查看参数内容

查看所有已经创建好的存储过程详情

show procedure status

查询数据库里创建了哪些存储过程

格式：

show procedure status where db=“数据库名称”

in 输入参数

创建一个in有输入参数的存储过程

#in 表示输入参数

create procedure bb ( x int )——创建一个带参数的存储过程bb（参数命名，参数类型）在括号里写入参数，这个参数叫做形式参数（形参）注意：定义形参时，需要说明参数的数据类型

Begin

select * from bb where dept2=x;——形参X是可以sql语句的集合使用的

end；

Call bb(参数)

if判断语句

if 条件 THEN

执行sql1

else

执行sql2

end if;

if 多分支

在if判断语句中，有几个判断分支 if ，就有几个结束语 end if

while循环语句：

while+条件+do；

执行sql循环语句；

end while；

当条件成立时，进入循环

条件不成立时，退出循环

666

顶礼膜拜