6月12日多表笔记

6月12日多表笔记

1、mysql数据库的权限管理：

在linux系统通过命令:

mysqladmin -uroot password  '123456'

=====给用户设置密码。

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

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

use mysql；=====使用并进入mysql库

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

select host,user,password from user;

=====查看user表的host,user,password这三个字段的数据。

给mysql增加一个新的用户：

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

=====要在mysql库中执行的

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

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用户，创建这样一个用户才能让Navicat连接数据库

flush privileges;  

===刷新权限

其他的操作：

update user set password=password('12345')  where user='dcs30' and host='localhost';

===》给dcs30用户修改密码。

revoke all on *.* from 'dcs30'@'localhost';

===》取消dcs30用户全部权限操作

delete from user where user='dcs30';

==>可以直接在user表进行用户的删除，注意不要乱删root用户

2、使用Navicat连接数据库

Navicat是一个数据库连接工具，一个直接连接服务器里面的数据库管理工具的工具，图像化界面；3306,是MySQL的默认端口号，一般是使用3306~3309；使用Navicat连接数据库需要注意服务器的mysql服务有没有启动，以及防火墙有没有关闭。

3、在Navicat里面的查询：

运行方式有两种：

（1）直接点击运行图标；

（2）选择sql语句，右击‘运行已选择的’。

注释：

（1）“#”代表注释，被注释的内容不会被程序识别和执行，使用#号可以做单行注释。

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

（3）多行注释可以使用/* .....*/

注意：

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

（2）这是一个英文的分号';'   这是一个中文的分号‘；’

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

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

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

4、多表连接

select *from aa;=====查询表aa里的数据

select *from cc;=====查询表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

左连接和右连接的区别：

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

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

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

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

=====基本连接aa,cc,ff三个表

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

=====建ff表

insert into ff values(1001,2130);

select *from ff;

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

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

=====连接aa表cc表及grade的 sid,name字段

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

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

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

（1）临时表方法

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')

(2)嵌套方法  ‘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

----作业的前2题答案----       

作业的表：

create table dept (dept1 int(10) PRIMARY key,dept_name VARCHAR(20));

desc dept;

INSERT INTO dept VALUES(101,'财务');

INSERT into dept VALUES(102,'销售');

INSERT into dept VALUES(103,'IT技术');

INSERT into dept VALUES(104,'行政');

SELECT * from dept;

create table emp(sid int(10) PRIMARY key,name VARCHAR(20),age int(20),worktime_start date,incoming int(20),dept2 int(20));

desc emp;

INSERT INTO emp VALUES(1789,'张三',35,"1980-01-01",4000,101);

INSERT into emp VALUES(1674,'李四',32,"1983-04-01",3500,101);

INSERT into emp VALUES(1776,'王五',24,"1990-07-01",2000,101);

INSERT into emp VALUES(1568,'赵六',57,"1970-10-11",7500,102);

INSERT into emp VALUES(1564,'荣七',64,"1963-10-11",8500,102);

INSERT into emp VALUES(1879,'牛八',55,"1971-10-20",7300,103);

SELECT * from emp;

-- 1.列出每个部门的平均收入及部门名称;

条件：每个部门   结果：平均收入、部门名称

第一种方法 左连接：

select dept.dept_name,avg(emp.incoming) from dept left join emp on dept.dept1=emp.dept2 group by dept.dept_name

第二种方法 临时表：

select *from dept,emp where dept.dept1=emp.dept2 ==>临时表t

select t.dept_name,avg(t.incoming) as '平均收入' from (select *from dept,emp where dept.dept1=emp.dept2)t group by t.dept1

-- 2.财务部门的收入总和；

条件：财务   结果：收入总和 sum(incoming)

方法一 临时表：

select *from dept,emp where dept.dept1=emp.dept2 ==>临时表t

select sum(incoming) from (select *from dept,emp where dept.dept1=emp.dept2)t where t.dept_name='财务'

方法二 嵌套：

select dept1 from dept where dept_name='财务' ===》财务部门对应的部门编号

select sum(incoming) from emp where dept2 in (select dept1 from dept where dept_name='财务')

方法三 基本连接：

select sum(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='财务'