本帖最后由 DCS63_王争荣 于 2021-7-21 23:46 编辑
=================================
一、安装Navicat for mysql:
1、下载Navicat for mysql
2、安装Navicat
3、选择试用版
4、安装完成后,点击Navicat界面的左上角“连接”,选择“MySQL”
5、在“MySQL新建连接”界面填写信息
【ip或者域名】,【mysql端口号3306~3309】,【账号和密码】
6、填写完成,点击“测试连接”,显示成功即完成Navicat连接。
注意:Navicat连接失败时请进行如下操作
1、重启数据库:service mysqld restart
2、关闭防火墙:service iptables stop
3、检查当前用户有没有百分号“%”权限,如果没有的话就去添加“%”权限
4、如果没有,那就赋予权限--【 grant all privileges on *.* to 'root'@'%' identified by '123456'; 】
5、赋予权限后刷新一下数据库--【 flush privileges;】
6、再到Navicat"新建连接”界面重新连接即可。
===============================================================
============================================================
二、新增用户,权限,取消权限,删除用户:
1、进入mysql数据库--ues mysql
2、给user用户表增加用户:
eg:在用户表中新增加一个用户,名叫wang,且只具备本地访问权限localhost,密码为123456。
【insert into user(host,user,password)values('localhost','wang',password('123456'))】
(如果创建时没有设置密码,那么就不需要输入密码)
创建完成后进入:mysql -uwang -p 然后“回车”即可进入。
3、创建新用户后需要刷新一下:flush privileges
4、上班后,如果不是骨干成员,那么基本上只会有一个权限:select * from
5、如果你要链接其他工具,且需要远程访问权限时:(指令如下)
grant all privileges on *.* to 'dcs'@'%' identified by '123456';
注释:创建一个用户,名为dcs,密码为123456,且此用户拥有远程访问的权限。
6、查看用户'wang'所拥有的权限:
show grants for 'wang'@'localhost';
7、取消用户wang的所有权限:
revoke all on *.* from 'wang'@'localhost';
8、删除用户wang:(删除后,该用户就不存在了)
delete from user where user='wang' and host='localhost';
9、把user表中的root用户的密码,改为:‘’123456‘’
update user set password=password('123456') where user='root';
10、查询user用户表中的用户信息:
select host,user from user;
11、赋予root用户远程访问权限:
【grant all privileges on *.* to 'root'@'%' identified by '123456';】
=============================================================================
=============================================================================
三、多表查询:
1、多表查询(重点,有哪些连接方法,面试官经常会问多表查询)
2、主键约束、外键等约束
3、视图,索引
4、存储过程
sql==测试:select from (单表查询,多表查询)
多表连接的要求:两张表中必须要有相同的字段属性
依据下图两表讲解内连接,左链接,右连接,基本连接,连接
1、内连接: inner join on
select * from cc inner join aa on aa.id=cc.sid;
注释:把aa表和cc表进行关联,因为两表中的id和sid是一样的,所以允许连接。
注意:内连接连接后的现实规则为如下
(两两为真,显示真 1001,zhangsan。1001,90 (1002不显示) )
(一真一假,不显示)
(一假一真,不显示)
(两两为假,不显示)
2、左连接:left join on
select * from aa lfte join cc on aa.id=cc.sid;
select * from cc lfet join aa on aa.id=cc.sid;
-----左连接就是以左表为主表(from 后面是左, join后面是右)
注意:如果主表数据多于右表,那么左表显示正常所有,右表少的值自动补齐Null
如果主表数据小于右表,那么只显示相同的值,右边不相同的值不显示
3、右连接:right join on
select * from aa right join cc on aa.id=cc.sid;
select * from cc right join aa on aa.id=cc.sid;
------右连接就是以右表为主(from后面是左,join后面是右)
注意:如果右表主表的数据是多于子表左表,那么显示的结果是以右表为准右表全部显示,左表少的值自动补Null ;
如果右表主表的数据是小于子表左表,那么显示的结果是右表和左表相同的字段,不相同的字段不显示;
4、基本连接:(使用的比较多)
select * from aa,cc where aa.id=cc.sid;
select * from cc,aa where aa.id=cc.sid;
注意:from 后面是“主表”,逗号后面是“子表”
---基本连接方法:只显示两表之间的相同字段,不相同就不显示(参考内连接)。
5、“union”连接
select * from aa union select * from cc;
注意:两个表必须要有相同的字段数量才可以进行竖向显示连接,否则失败。
=============================================================================
=============================================================================
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vji3MvoEO_ypVO9VAqGktiyc\dc5859fa27ca403980712b296462845d\z6puu_o6`(ft.png
多表查询练习一:
求出张三的 成绩
select * from aa; #姓名表
select * from cc; #成绩表
#常规写法
#左连接,右连接一样
select * from aa left join cc on aa.id=cc.s_id where name='zhangsan';
select score from aa left join cc on aa.id=cc.s_id where name='zhangsan';
#基本连接
select * from aa,cc where aa.id=cc.s_id;
select * from aa,cc where aa.id=cc.s_id and name='zhangsan';
select score from aa,cc where aa.id=cc.s_id and name='zhangsan';
#临时表方法
#左连接方法
select * from aa left join cc on aa.id=cc.s_id;
select * from(select * from aa left join cc on aa.id=cc.s_id)t;
select * from(select * from aa left join cc on aa.id=cc.s_id)t where t.name='zhangsan';
select * from(select * from aa left join cc on aa.id=cc.s_id)t where name='zhangsan';
select score from(select * from aa left join cc on aa.id=cc.s_id)t where name='zhangsan';
select * from aa left join cc on aa.id=cc.s_id where name='zhangsan';
select * from (select * from aa left join cc on aa.id=cc.s_id where name='zhangsan')t;
select score from (select * from aa left join cc on aa.id=cc.s_id where name='zhangsan')t;
#基本连接方法
select * from aa,cc where aa.id=cc.s_id;
select * from(select * from aa,cc where aa.id=cc.s_id)t;
select * from(select * from aa,cc where aa.id=cc.s_id)t where name='zhansan';
select score from(select * from aa,cc where aa.id=cc.s_id)t where name='zhansan';
select * from aa,cc where aa.id=cc.s_id and name='zhangsan';
select * from(select * from aa,cc where aa.id=cc.s_id and name='zhangsan')t;
select score from(select * from aa,cc where aa.id=cc.s_id and name='zhangsan')t;
多表查询练习二:
#嵌套方法 求出张三的成绩
select * from aa; #姓名表
select * from cc; #成绩表
select * from aa where name='zhangsan';
select id from aa where name='zhangsan';
select * from cc where s_id=(select id from aa where name='zhangsan');
select score from cc where s_id=(select id from aa where name='zhangsan');
select score from cc where s_id in(select id from aa where name='zhangsan');
求出谁没参加 考试?
select * from aa; #姓名表
select * from cc; #成绩表
select * from aa,cc where aa.id=cc.s_id;
select id from aa,cc where aa.id=cc.s_id;
select * from aa where id not in(select id from aa,cc where aa.id=cc.s_id);
select name from aa where id not in(select id from aa,cc where aa.id=cc.s_id);
select * from aa left join cc on aa.id=cc.s_id;
select * from (select * from aa left join cc on aa.id=cc.s_id)t;
select * from (select * from aa left join cc on aa.id=cc.s_id)t where score is null;
select name from (select * from aa left join cc on aa.id=cc.s_id)t where score is null;
select * from aa left join cc on aa.id=cc.s_id where score is null;
select name from aa left join cc on aa.id=cc.s_id where score is null;
select * from cc;
select s_id from cc; #有成绩的id号
select * from aa where id not in(select s_id from cc);
select name from aa where id not in(select s_id from cc);
|
|