正式决定当程序员的第十一～十六天

1、理论学习
这几天学习了mysql的多表连接查询（交叉连接查询、内连接查询、外连接【左外/右外】查询和子查询，多多练习提高代码效率，再看看还有没有更进一步的操作学习内容，并补充学习了数据库理论知识，b+树MySQL索引 - 底层实现 - B+树/Hash索引MySQL索引底层实现原理
1）索引是帮助MySQL高效获取数据的数据结构。由于优化诞生例如二分查询、二叉树查询等（需要自我补充学习时间/空间复杂度）
2）二叉排序树/平衡二叉树
3）B树。事实上是一种平衡的多叉查找树，m阶b树。
条件：每个节点最多可以拥有m棵子树
根节点，只有至少有两个节点
非根非叶的节点至少有的Ceil（m/2）个子树（Ceil表示向上取整，图中5阶B树，每个节点至少有3个子树，也就是至少有3个叉）
非叶节点中的信息包括[n,A0,K1,A1,K2,A2,…,Kn,An]，，其中n表示该节点中保存的关键字个数，K为关键字且Ki<Ki+1，A为指向子树根节点的指针。
从根到叶子的每一条路径都有相同的长度，也就是说，叶子节在相同的层，并且这些节点不带信息，实际上这些节点就表示找不到指定的值，也就是指向这些节点的指针为空。（解释：叶子节点不含有任何信息）

BTree_Search(node, key) {
    if(node == null) return null;
    foreach(node.key)
    {
        if(node.key[i] == key) return node.data[i];
            if(node.key[i] > key) return BTree_Search(point[i]->node);
    }
    return BTree_Search(point[i+1]->node);
}

data = BTree_Search(root, my_key);

ps：我太喜欢博主的这个解释代码啦
4）B+/-树，我由于缺少算法的知识，还需要进一步学习常用的算法和理论知识（I/O渐进复杂度、红黑树等等）

2、后续规划
1）做一个通用爬取解析的代码，后续经常会用到
2）做一个email自动回复的代码，简历还是差很多但是网站上又不能放太多的附件哇，难难。
3）开始学习django库

3、错误汇总
error code1630:函数与括号分开产生的错误，类似于sum （）【false】/sum()【right】
error code1175:老问题了，SET SQL_SAFE_UPDATES = 0解决
error code1140:搜到的解决方案如下mysql-ERROR 1140 (42000)错误解决办法但是并没有解决，还需要更进一步，由于系统不同有些问题还是不通用的

4、联系代码

select count(*),departmentnumber from employee group by departmentnumber;
select count(*) ,departmentnumber from employee where departmentnumber>1001 group by departmentnumber;
select sum(salary) ,departmentnumber from employee group by departmentnumber having sum(salary) >8000;
select * from student order by age asc;
select * from student order by age desc;
select * from student as stu;
select name as '姓名',id from student;
drop table if exists student;
drop table if exists class;
create table class(
cid int(4) not null primary key,
cname varchar(30)
);
create table student(
sid int(8) not null primary key,
sname varchar(30),
classid int(8) not null
);

alter table student add constraint fk_student_classid foreign key(classid) references class(cid);
insert into class(cid,cname)values(1,'Java');
insert into class(cid,cname)values(2,'python');
insert into student(sid,sname,classid)values(1,'tome',1);
insert into student(sid,sname,classid)values(2,'lucy',1);
insert into student(sid,sname,classid)values(3,'lili',2);
insert into student(sid,sname,classid)values(4,'domi',2);
select * from student where classid=(select cid from class where cname='Java');
delete from student where classid = (select cid from class where cname = 'Java');
delete from class where cname = 'Java';
set sql_safe_updates = 0; 
drop database if exists mydb;
create database mydb;
use mydb;
create table department(
did int(4) not null primary key,
dname varchar(20)
);

create table employee(
eid int(4) not null primary key,
ename varchar(20),
eage int(2),
departmentid int (4) not null
);
insert into department values(1001,'财务部');
insert into department values(1002,'技术部');
insert into department values(1003,'行政部');
insert into department values(1004,'生活部');

insert into employee values(1,'张三',19,1003);
insert into employee values(2,'李四',18,1002);
insert into employee values(3,'王五',20,1001);
insert into employee values(4,'赵六',20,1004);

select employee.ename,department.dname from department inner join employee on department.did=employee.departmentid;

drop database if exists mydb;
create database mydb;
use mydb;
create table class(
cid int(4) not null primary key,
cname varchar(20)
);
create table student(
sid int (4) not null primary key,
sname varchar(20),
sage int(2),
classid int(4) not null);
insert into class values(1001,'Java');
insert into class values(1002,'C++');
insert into class values(1003,'Python');
insert into class values(1004,'PHP');
insert into student values(1,'张三',20,1001);
insert into student values(2,'李四',21,1002);
insert into student values(3,'王五',24,1002);
insert into student values(4,'赵六',23,1003);
insert into student values(5,'Jack',22,1009);

select class.cid,class.cname,student.sname from class left outer join student on class.cid = student.classid;
select class.cid,class.cname,student.sname from class right outer join student on class.cid=student.classid;

DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS class;

-- 创建班级表
CREATE TABLE class(
  cid int (4) NOT NULL PRIMARY KEY, 
  cname varchar(20)
);

-- 创建学生表
CREATE TABLE student (
  sid int (4) NOT NULL PRIMARY KEY, 
  sname varchar (20), 
  sage int (2), 
  classid int (4) NOT NULL
);

-- 向班级表插入数据
INSERT INTO class VALUES(1001,'Java');
INSERT INTO class VALUES(1002,'C++');
INSERT INTO class VALUES(1003,'Python');
INSERT INTO class VALUES(1004,'PHP');
INSERT INTO class VALUES(1005,'Android');

-- 向学生表插入数据
INSERT INTO student VALUES(1,'张三',20,1001);
INSERT INTO student VALUES(2,'李四',21,1002);
INSERT INTO student VALUES(3,'王五',24,1003);
INSERT INTO student VALUES(4,'赵六',23,1004);
INSERT INTO student VALUES(5,'小明',21,1001);
INSERT INTO student VALUES(6,'小红',26,1001);
INSERT INTO student VALUES(7,'小亮',27,1002);

select * from class where cid = (select classid from student where sname = '张三');
select * from class where cid > (select classid from student where sname = '张三');
select * from class where exists (select * from student where sname='王五');
select * from class where cid > any (select classid from student
);
select * from class where cid > all (select classid from student);