第7讲 数据库
(1001)","marks":[]}]}],"state":{}},{"type":"block","id":"rmVh-1654169034427","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"XSzG-1654169034426","leaves":[{"text":"2）内连接==》2个表当中有相同字段值可以进行连接","marks":[]}]}],"state":{}},{"type":"block","id":"1sZK-1654169034429","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"HHzp-1654169034428","leaves":[{"text":"select * from aa inner join cc on aa.id = cc.s_id;","marks":[]}]}],"state":{}},{"type":"block","id":"hgtj-1654169034431","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"wSs5-1654169034430","leaves":[{"text":"3）左连接 ==》以左表为主，展示左表全部的数据，右表没有的数据以null填充","marks":[]}]}],"state":{}},{"type":"block","id":"YAwe-1654169034433","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"3pjn-1654169034432","leaves":[{"text":"SELECT * from aa left join cc on aa.id = cc.s_id;","marks":[]}]}],"state":{}},{"type":"block","id":"FmJv-1654169034435","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"IfGW-1654169034434","leaves":[{"text":"4）右连接 ==》以右表为主，展示右表的全部数据，左表多的数据不展示","marks":[]}]}],"state":{}},{"type":"block","id":"iBpU-1654169034437","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"S0Mr-1654169034436","leaves":[{"text":"select * from aa right join cc on aa.id = cc.s_id;","marks":[]}]}],"state":{}},{"type":"block","id":"sNkW-1654169034439","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"Yjah-1654169034438","leaves":[{"text":"5）硬件连接 ==》2张表需要相同的字段","marks":[]}]}],"state":{}},{"type":"block","id":"qYXp-1654169034441","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"gpCC-1654169034440","leaves":[{"text":"select * from aa union select * from cc;","marks":[]}]}],"state":{}},{"type":"block","id":"nQc1-1654169034444","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"96rv-1654169034443","leaves":[{"text":"6）基本法+临时表","marks":[]}]}],"state":{}},{"type":"block","id":"WFOn-1654169034446","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"AuAI-1654169034445","leaves":[{"text":"求出张三的成绩","marks":[]}]}],"state":{}},{"type":"block","id":"pJut-1654169034448","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"l1LD-1654169034447","leaves":[{"text":"select * from aa,cc where aa.id=cc.s_id;t","marks":[]}]}],"state":{}},{"type":"block","id":"9IVh-1654169034450","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"NPGs-1654169034449","leaves":[{"text":"select score from (select * from aa,cc where aa.id=cc.s_id)t where name = 'zhangsan';","marks":[]}]}],"state":{}},{"type":"block","id":"wkzh-1654169034452","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"FFtD-1654169034451","leaves":[{"text":"7）嵌套法 =（= 和 in）","marks":[]}]}],"state":{}},{"type":"block","id":"TEio-1654169034454","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"tshH-1654169034453","leaves":[{"text":"select id from aa where name = 'zhangsan'; ==>1001","marks":[]}]}],"state":{}},{"type":"block","id":"IMBW-1654169034456","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"INUL-1654169034455","leaves":[{"text":"select score from cc where s_id = (select id from aa where name = 'zhangsan');","marks":[]}]}],"state":{}},{"type":"block","id":"8v4C-1654169034458","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"FNqe-1654169034457","leaves":[{"text":"8）嵌套法 in","marks":[]}]}],"state":{}},{"type":"block","id":"tlVN-1654169034460","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"xPZi-1654169034459","leaves":[{"text":"select id from aa where name = 'zhangsan'; ==>1001","marks":[]}]}],"state":{}},{"type":"block","id":"JChR-1654169034462","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"pwex-1654169034461","leaves":[{"text":"select score from cc where s_id in(select id from aa where name = 'zhangsan');","marks":[]}]}],"state":{}},{"type":"block","id":"DK4Q-1654169034464","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"GHwc-1654169034463","leaves":[{"text":"求出谁没参加考试？","marks":[]}]}],"state":{}},{"type":"block","id":"PXzj-1654169034466","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"PsKm-1654169034465","leaves":[{"text":"1、左连接","marks":[]}]}],"state":{}},{"type":"block","id":"mkgT-1654169034468","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"ugKq-1654169034467","leaves":[{"text":"SELECT name from aa left join cc on aa.id = cc.s_id where score is null;","marks":[]}]}],"state":{}},{"type":"block","id":"2m1l-1654169034470","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"vLX6-1654169034469","leaves":[{"text":"2、嵌套法","marks":[]}]}],"state":{}},{"type":"block","id":"ERLE-1654169034472","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"iTme-1654169034471","leaves":[{"text":"select s_id from cc; ==>1001","marks":[]}]}],"state":{}},{"type":"block","id":"ht8V-1654169034474","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"oi9Z-1654169034473","leaves":[{"text":"select name from aa where id not in (select s_id from cc);","marks":[]}]}],"state":{}},{"type":"block","id":"NOM9-1654169034476","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"QwMk-1654169034475","leaves":[{"text":"【处理mysql数据库中文乱码的问题】","marks":[]}]}],"state":{}},{"type":"block","id":"erp5-1654169034478","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"dewn-1654169034477","leaves":[{"text":"1、vim /etc/my.cnf==》数据库的配置文件","marks":[]}]}],"state":{}},{"type":"block","id":"AbDd-1654169034480","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"Bpo5-1654169034479","leaves":[{"text":"2、加入这行character_set_server=utf8","marks":[]}]}],"state":{}},{"type":"block","id":"FG4a-1654169034482","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"nSec-1654169034481","leaves":[{"text":"3、重启数据库","marks":[]}]}],"state":{}},{"type":"block","id":"xR7K-1654169034484","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"Xjj7-1654169034483","leaves":[{"text":"4、删除dept和emp表==》重新创建发现还是乱码","marks":[]}]}],"state":{}},{"type":"block","id":"Mwww-1654169034486","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"NB25-1654169034485","leaves":[{"text":"5、dcs这个库的数据库属性把Latin改为utf8编码格式 （1.字符集：utf8 -- UTF-8 Unicode 2.排序规则：utf8_general_ci）","marks":[]}]}],"state":{}},{"type":"block","id":"1bF0-1654169034488","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"OoU4-1654169034487","leaves":[{"text":"==》重新创建表就可以看到中文了","marks":[]}]}],"state":{}},{"type":"block","id":"8izE-1654324940913","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"35vv-1654324940797","leaves":[{"text":"#求出每个班英语成绩最高的那个人的姓名和班级名称","marks":[]}]}],"state":{}},{"type":"block","id":"zmH9-1654324942507","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"6T8a-1654324942506","leaves":[{"text":"#","marks":[]}]}],"state":{}},{"type":"block","id":"bk5B-1654324942509","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"hIxy-1654324942508","leaves":[{"text":"select class,max(english)g from grade group by class; ==》先求出每个班英语最高分然后和grade 表继续连表","marks":[]}]}],"state":{}},{"type":"block","id":"efSI-1654324942512","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"9FaP-1654324942510","leaves":[{"text":"select * from grade;","marks":[]}]}],"state":{}},{"type":"block","id":"cfe4-1654324942514","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"fYEi-1654324942513","leaves":[{"text":"select t.name,t.class from grade t,(select class,max(english)g from grade group by class)b where t.class =b.class and t.english = b.g;","marks":[]}]}],"state":{}}]'>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）
==》重新创建表就可以看到中文了
#求出每个班英语成绩最高的那个人的姓名和班级名称
#
select class,max(english)g from grade group by class; ==》先求出每个班英语最高分然后和grade 表继续连表
select * from grade;
select t.name,t.class from grade t,(select class,max(english)g from grade group by class)b where t.class =b.class and t.english = b.g;
页:
[1]