小白的 MySQL 笔记（一）

来自 stackoverflow 的内容居多。

3- What is InnoDB and MyISAM in MySQL ?

MySQL VARCHAR size?

I’m wondering, if I have a VARCHAR of 200 characters and that I put a string of 100 characters, will it use 200 bytes or it will just use the actual size of the string?

100 characters.

This is the var (variable) in varchar: you only store what you enter (and an extra 2 bytes to store length upto 65535)

If it was char(200) then you’d always store 200 characters, padded with 100 spaces

See the docs: “The CHAR and VARCHAR Types” ——- by gbn

补充:

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 DATABASE chat;
-- 使用数据库
use chat;-- 创建表
CREATE TABLE user(
  user_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户id',
  username VARCHAR(40) NOT NULL COMMENT '用户名（邮箱）',
  password VARCHAR(40) NOT NULL COMMENT '密码',
  nickname VARCHAR(20) NOT NULL COMMENT '昵称',
  other VARCHAR(120) COMMENT '其他',
  PRIMARY KEY (user_id)
)ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT='用户基本信息';CREATE TABLE friend_relation(
  user_id BIGINT NOT NULL COMMENT '用户id',
  friend_id BIGINT NOT NULL COMMENT '好友id'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='好友关系表';CREATE TABLE group_relation(
  user_id BIGINT NOT NULL COMMENT '用户id',,
  group_id BIGINT 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

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. —— by Vasanth Nag K V

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

That definition from: http://en.wikipedia.org/wiki/Data_access_object

Check also the sequence diagram here: http://www.oracle.com/technetwork/java/dataaccessobject-138824.html

Maybe a simple example can help you understand the concept:

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

public class Employee {    private int id;
    private String name;    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(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:

interface EmployeeDAO {    List<Employee> findAll();
    List<Employee> findById();
    List<Employee> findByName();
    boolean insertEmployee(Employee employee);
    boolean updateEmployee(Employee employee);
    boolean deleteEmployee(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

MySQL VARCHAR size?

数据库设计范式

What is InnoDB and MyISAM in MySQL ?

为什么手写 DDL （Data Definition Language）？

Data access object (DAO) in Java

个人收藏笔记记录

开通VIP

What is `InnoDB` and `MyISAM` in `MySQL` ?