11.25笔记+课后练习

连接navicat
一.满足条件
1.正确账号、密码
2.输入数据库端口号：3306-3309
3.输入ip
4.重启数据库
5.关闭防火墙
6.账号%权限
二.步骤
1.use mysql     进入数据库
2.select host,user from user; 查询
    如果没有权限：grant all privileges on *.* to 'root'@'%' identified by '123456';
3.创建用户不赋予权限（新增加一个用户赋予本地权限，用户名是qian）
   insert into user (host,user,password)values('localhost','qian',password('123456'));
4.创建用户后进行授权（方法二）
  grant select,update,delete,drop on *.* to 'qian'@'localhost' identified by '123456'
5.查询指定用户拥有哪些权限
  show grants for 'qian'@'localhost';
6.取消指定用户的权限
  revoke all on *.* from 'qian'@'localhost';
7.删除用户
  delete from user where user ='qian' and host='localhost';
8.改用户密码（在user 表中把root用户的密码改为123456）
  update user set  password=password('123456') where user='root';


多表查询的方法（重点）
条件：关联的表必须有相同的数据

1。基本连接：常用的方法
select * from aa,cc where aa.id=cc.s_id;
select * from aa,cc where id=s_id;

2.内连接：inner join ...on
select * from aa inner join cc on aa.id=cc.s_id;
select * from aa inner join cc on id=s_id;

3.左连接：left join ...on
左边的表为主表，右边的表为子表
select * from aa left join cc on id=s_id;
select * from aa left join cc on id=S_id;
左连接以左边表为基准
如果主表的值是大于子表的值，字表缺省的值自动补齐null
如果主表的值是小于子表的值，只取和主表相同的值其它不显示

4.右连接：ringht join...on
右边的表为主表，左边的表为子表
select * from aa right join cc on id=s_id;
select * from cc right join cc on s_id=id;
解释：
右连接是以右表为主表
如果主表的值是大于子表的值，子表缺省的值自动补齐null
如果主表的值是小于子表的值，只取和主表相同的值其它不显示

MySQL面试题经常问的一个问题：左右连接的区别？

• 左连接以左边的表为主表，右边的表为子表，右连接相反；
• 当主表的值大于子表的值，子表缺省的值自动补齐null。
• 当主表的值小于子表的值，只取和主表的值，其他不显示。
  

5.union连接方法(两表字段相同方可连接）
select* from aa union select * from cc;






课后练习：求谁没有参加考试？aa cc

内连接Inner join...on : select name from aa where id not in(select id from(select *from aa inner join cc on id=s_id)a);

一般连接: select name from aa where id not in (select id from(select *from aa,cc where id=s_id)a);

左连接：select name from(select *from aa left join cc on id=s_id)a where score is null;

右连接：select name from aa where name not in(select name from(select *from aa right join cc on id=s_id)a);

Union : select name from(select *from aa union select *from cc)a where id not in(select s_id from cc);

内连接Inner join...on : select name from aa where id not in(select id from aa inner join cc on id=s_id);

一般连接: select name from aa where id not in (select id from aa,cc where id=s_id);

左连接：select name from(select *from aa left join cc on id=s_id)a where score is null;

右连接：select name from aa where name not in(select name from aa right join cc on id=s_id);

Union : select name from(select *from aa union select *from cc)a where id not in(select s_id from cc);