MySQL学习笔记

vim /etc/my.cnf
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
#注意要设置为支持的字符集

显示所有支持字符集 show character set;
查看当前使用字符集 show variables like ‘character%‘;
查看所有排序 show collation；
查看当前使用排序 show variables like ‘collation%‘;

#子查询
select stuid,name,age from students where age > (select avg(age) from students);
#联合查询
select * from teachers union all select * from teachers;
#交叉连接，产生笛卡尔乘积数的数据量
select * from students cross join teachers;
#内连接
select * from students s  inner join teachers t on s.teacherid=t.tid;
#完全外连接
select s.stuid s_id,s.name s_name,s.age s_age,t.tid t_id,t.name t_name,t.age t_age 
   from 
   students s left join teachers t on s.teacherid=t.tid
   union  
   select s.stuid s_id,s.name s_name,s.age s_age,t.tid t_id,t.name t_name,t.age t_age 
   from  students s right join teachers t on s.teacherid=t.tid;
#完全外连接扩展
select s.stuid s_id,s.name s_name,s.age s_age,t.tid t_id,t.name t_name,t.age t_age 
   from 
   students s left join teachers t on s.teacherid=t.tid where t.tid is null 
   union  
   select s.stuid s_id,s.name s_name,s.age s_age,t.tid t_id,t.name t_name,t.age t_age 
   from  students s right join teachers t on s.teacherid=t.tid where s.teacherid is null;