第七讲 3

navicat  的应用

快捷键
ctrl+q   新建一个窗口
ctrl+w   关闭窗口
ctrl+r   运行语句


navicat 如果连接不上
1）service mysqld restart    ==》重启数据库
2）service iptables stop    ==》关闭防火墙
3）grant all privileges on *.* to  'root'@'%' identified  by '123456';  ==》开放远程权限

注释
#   ==》单行注释

连续多行注释   /*     */

/*    ==>段注释
/*create table aa(id int(1) PRIMARY key,name char(20));
create table cc(s_id int(1) PRIMARY key,score char(20));
insert into aa(id,name)values(1001,'zhangsan'),(1002,'lisi');
insert into cc(s_id,score)values(1001,'99'); */

1、基本连接 ==》2个表中有字段值相同则可以进行连接,结果只展示相同字段的数据
select *from aa,cc where aa.id = cc.s_id;

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、右连接  ==》以右表为主，展示右表的全部数据，左表没有的数据以null填充
select * from aa right join cc on aa.id = cc.s_id;

5、硬链接  ==》2张表的字段必须相同
select * from aa union select * from cc;

求zhansan 的成绩？
select score from aa,cc where aa.id = cc.s_id and name = 'zhangsan';

6、临时表法
select * from aa,cc where aa.id = cc.s_id  t临时表
select t.score from (
select * from aa,cc where aa.id = cc.s_id)t where t.name = 'zhangsan';

7、嵌套法  =
select id  from aa where name = 'zhangsan';  ==>1001  

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

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


求出没有参加考试
select id from aa;   ==>1001 1002
select name from aa where id not in(select s_id from cc); ==>1001

select name from aa LEFT JOIN cc on aa.id = cc.s_id where score is null;

select name from cc RIGHT JOIN aa on aa.id = cc.s_id where score is null;

【处理msyql数据库中文乱码的问题】
1）vim /etc/my.cnf   ==>数据库的配置文件
2）加入这行 character_set_sercer=utf8
3）重启数据库
4）dcs这个库右键属性把latin 为utf8编码格式（utf8--UTF-8 Unicode）
   排序规则 utf8_general_ci