mysql笔记手写_MySQL 笔记（一）

VARCHAR(M)是一种比CHAR更加灵活的数据类型，同样用于表示字符数据，但是VARCHAR可以保存可变长度的字符串。其中M代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。因此，对于那些难以估计确切长度的数据对象来说，使用VARCHAR数据类型更加明智。MySQL4.1以前,VARCHAR数据类型所支持的最大长度255,5.0以上版本支持65535字节长度,utf8编码下最多支持21843个字符(不为空)  ------ 来自百度百科

数据库设计范式

如何能设计出优秀的数据库呢？专家们经过多年研究终于·····  一般按照范式来进行数据库设计就得到良好的数据库，范式的目的在于：消除数据冗余和编程便利。

第一范式就是得到纯二维表，// 只有纯二维的表才能装进数据库

第二范式是消除非主键依赖关系，// “有些列并不直接依赖于主键”，就是说和主键没关系的东西应该扔到另外一张表中去。

第三范式是消除函数依赖关系。// 能算出来的就别额外拿一列装了。。。

做范式的主要手段是“拆表”，但是要它有副作用，······，所以要在数据冗余和编码容易之间寻找平衡点，到了第三范式，基本上就是平衡点了。  ------ 《 Java 就该这样学》

What is InnoDB and MyISAM in MySQL ?

InnoDB and MYISAM, are storage engines for MySQL.

These two differ on their locking implementation: InnoDB locks the particular row in the table, and MyISAM locks the entire MySQL table.

You can specify the type by giving MYISAM OR InnoDB while creating a table in DB.   ------ by Siva

为什么手写 DDL (Data Definition Language)？

schema.sql(demo):

--数据库初始化脚本

--创建数据库

CREATE DATABASEchat;--使用数据库

usechat;--创建表

CREATE TABLE user(user_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户id',

usernameVARCHAR(40) NOT NULL COMMENT '用户名(邮箱)',

passwordVARCHAR(40) NOT NULL COMMENT '密码',

nicknameVARCHAR(20) NOT NULL COMMENT '昵称',

otherVARCHAR(120) COMMENT '其他',PRIMARY KEY (user_id)

)ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT='用户基本信息';CREATE TABLEfriend_relation(user_id BIGINT NOT NULL COMMENT '用户id',

friend_idBIGINT NOT NULL COMMENT '好友id')ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='好友关系表';CREATE TABLEgroup_relation(user_id BIGINT NOT NULL COMMENT '用户id',,

group_idBIGINT NOT NULL COMMENT '群组id')ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='群组关系表';--初始化数据

INSERT INTO

'user'('username', 'password', 'nickname', 'other')VALUES('11122@gmail.com', 123456, '老大', '这人很懒，啥也没写'),

('jhgm49@163.com', 123456, '老二', '平平淡淡才是真');--INSERT INTO--'friend_relation'('user_id', 'friend_id')--VALUES

-- 为什么手写 DDL ( Data Definition Language )

-- 记录每次上线的 DDL 修改

-- 上线 V 1.1

-- xxx

-- 上线 V 1.2

-- xxx x x

MySQL 如果表存在就删掉：

DROP TABLE IF EXISTS tbl_name;

Data access object (DAO) in Java

Q:

I was going through a document and I came across a term called DAO. I found out that it is a Data Access Object. Can someone please explain me what this actually is?

I know that it is some kind of an interface for accessing data from different types of sources, in the middle of this little research of mine I bumped into a concept called data source or data source object, and things got messed up in my mind.

I really want to know what a DAO is programmatically in terms of where it is used. How it is used? Any links to pages that explain this concept from the very basic stuff is also appreciated.

A:

The Data Access Object is basically an object or an interface that provides access to an underlying database or any other persistence storage.

Maybe a simple example can help you understand the concept:

Let's say we have an entity to represent an employee:

public classEmployee {private intid;privateString name;public intgetId() {returnid;

}public void setId(intid) {this.id =id;

}publicString getName() {returnname;

}public voidsetName(String name) {this.name =name;

}

}

The employee entities will be persisted into a corresponding Employee table in a database. A simple DAO interface to handle the database operation required to manipulate an employee entity will be like:

interfaceEmployeeDAO {

ListfindAll();

ListfindById();

ListfindByName();booleaninsertEmployee(Employee employee);booleanupdateEmployee(Employee employee);booleandeleteEmployee(Employee employee);

}

Next we have to provide a concrete implementation for that interface to deal with SQL server, and another to deal with flat files, etc. ------ by Rami