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)
==》重新创建表就可以看到中文了
|
|