11.25 笔记

11.25
1、连接满足的条件：
账号   root
密码   123456
端口   3306
域名、IP地址  ifconfig查出的ip
数据库需要重启 service mysqld restart
防火墙需要关闭 service iptables stop
1、先进入数据库 use mysql
2、再查询即可 select host，user from user；
且对应的账号要拥有远程访问的权限（%）百分号权限
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\438aa7ac44d546c58d72198fd5b70759\clipboard.png
3、如果没有%号权限需要通过指令来赋予：
grant all privileges on *.* to 'root'@'%' identified by '123456';
[img=414,0]C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\306852dca5034ec1b180e66f2608c5da\5j34[j0x4w7_~[9nl1]m3oh.png[/img]

C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\2c013e409efd476c8d6ab093b63b44a3\clipboard.png

C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\b134b5217d1d457eb6de223912474e35\$e}%xg8$w`j]9ftfzmzrono.png

C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\e482e779c7d04b4fa3af6f4aa9bd3b61\clipboard.png

C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\587c0c718e8545c5bd8c1241ce5115b1\hi8_3@4(xp6%n{}lg$qxs%x.png

[img=386,0]C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\38c3d8c76c8b4b67b673f7f8bd375213\sq(edr1t3rc@nri7wgv5h02.png[/img]
ctrl +s保存查询的内容

4、创建用户不赋予权限
新增加一个用户赋予本地访问权限，用户名称是qian
insert into user (host,user,password)values('localhost','wang',password('123456'));
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\16e9f1123719494ab0aa1856e8c482b2\68__uph2ty(sqw9}t6n_](x.png
创建用户后进行授权（方法二）
grant select,update,delete,drop on *.* to 'wang'@'localhost' identified by '123456'
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\ae2f23844a694e7498c2ca5d84810e72\u%~4ug642$@3j8`j}9e@~2h.png

[img=459,0]C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\f2b676f98fa44ce6a9d1411e0e40aefb\s0%`0l$907bdt_f%d[g30ho.png[/img]
查询指定用户拥有哪些权限
show grants for 'qian'@'localhost';
[img=620,0]C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\548ef969921749ecbb0c8457091b4f28\xy(q}_15w(hpfv`b@2b84h3.png[/img]
取消置顶用户的权限
revoke all on *.* from 'qian'@'localhost';
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\093cfc89b9da4e249d3a4045e96d46a0\@m`{{0m075ft}n4emj8u14a.png
删除用户
delete from user where user=‘qian’ and host=‘localhost’
[img=620,0]C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\e2a95b1df1644ba49c3b2f583531a9ae\)@1k`stui$9o[vo0@tm$iy0.png[/img]
改用户密码
在user表中把root用户的密码改为123456
update user set password=password('123456') where user='root';


多表查询的方法（重点）
A  姓名表                                                               c  成绩表

id	name	s_ID	score
1	a	1	100
2	c	2	20
3	d	1	30

查找张三的成绩
select * from a；
1、多表的条件：必须要关联的表中有相同的字段
2、有哪些方法可以把表和表进行关联？
基本连接：
select * from aa，cc where aa.id=cc.s_id;  #表关联
select * from aa，cc where id=s_id;  #表关联
select * from cc，aa where s_id=id;  #from后面表是可以随意排序的
查询出来的结果置灰显示两个表中有的值。
两两为真为真
一真一假，一假一真，两两为假为假

内连接：inner join on
select * from aa inner join cc on aa.id=cc.s_id;
select * from aa inner join cc on id=id;
select * from cc inner join aa on cc.id=aa.s_id;
[img=620,0]C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\01671c15693d4837aae89d0dbcbab574\4qz29qrvl6$p]fv[brm39_p.png[/img]

C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\ece0090ddc844c0cbae853c3ce513a70\1d)3n69rf)}1]8a_cs%_c~u.png

左连接方法：左边的表为主表，右表为子表
left join   on
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\355cac3a348f4a1f9279084d0a765df5\(g}atcg3%de2_`1qzs}~qg3.png

C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\12eb30db8f414e8ab8437bdff554814e\408fn(677gk)s4f7_kcq57v.png
from后面是左边，on前面是右边
解释：
左连接以左边表为基准
如果主表的值是大于子表的值，字表缺省的值自动补齐null
如果主表的值是小于字表的值，只取和主表相同的值其他不显示
[img=620,0]C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\aca400c80b094e1299dbe5a8b32e4e84\g@wp9~10z}[)9z1{)dse9d5.png[/img]
右链接：右表的表为主表，左表为子表
right join on
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\4147f66ba9434726aa7bab33a837863c\ttria7~rex{q%f6gj$cwsie.png
解释：
右连接是以右表为主表，左表为子表
如果主表的值是大于子表的值，字表缺省的值自动补齐null
如果主表的值是小于子表的值，只取和主表相同的值其他不显示

MySQL面试常问，左右连接的区别？

union连接方法
select * from aa union select * from cc；
此方法连接是必须多个表字段要相同
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7VjkF7D0fkf5I42C4SdbpFAiI\7c831b004c6f4856ba185d73dc4160fb\clipboard.png

举一反三：左右连接内连接
select * from aa,cc where id=s_id; #两个表进行拼接
| 1001 | zhangsan || 1001 | 99    |
select * from aa,cc where id=s_id and name='zhangsan';
| 1001 | zhangsan || 1001 | 99    |
select score from aa,cc where id=s_id and name='zhangsan';
| 99    |

临时表写法：
select * from aa,cc where id=s_id；
+------+----------+------+-------+
| id   | name     | s_id | score |
+------+----------+------+-------+
| 1001 | zhangsan | 1001 | 99    |
+------+----------+------+-------+
select * from (select * from aa,cc where id=s_id）a；
+------+----------+------+-------+
| id   | name     | s_id | score |
+------+----------+------+-------+
| 1001 | zhangsan | 1001 | 99    |
+------+----------+------+-------+
select score from (select * from aa,cc where id=s_id）a where a.name=‘zhnagsan’

嵌套

select * from aa where name='zhangsan';张三的所有信息
| 1001 | zhangsan |
select id from aa where name='zhangsan';
| 1001 |
select * from cc;
| 1001 | 99    |
select * from cc where s_id in(select id from aa where name='zhangsan');
| 1001 | 99    |
select score from cc where s_id in(select id from aa where name='zhangsan');
| 99    |